Python script actions (type: "python") — execution-time staging/glue steps in the DAG

[ihistand]

Summary

Add type: "python" script actions: execution-time Python steps in the DAG for file staging and glue — the ingestion slot Dataform/dbt don't have (dbt's Python models require warehouse-native compute, which Postgres/Supabase lack). Declared in actions.yaml pointing at a plain .py file (no new extension — .pyx collides with Cython). Design spec: sqlanvil-private/planning/specs/2026-07-04-python-script-actions-design.md.

Target shape (the mailing_list example's OpenAddresses chain becomes one sqlanvil run):

python: load_openaddresses.py → import (gs://…) → stg_addresses → addresses_cache → mart

Contract (the load-bearing decisions)

• Execution-time only. Compile never runs Python (hermetic/deterministic compile is a Cloud invariant). This is NOT a compile-time templating layer — that alternative is explicitly rejected in the spec.
• No warehouse credentials injected. Python actions stage files; import loads them. Preserves zero-credential-custody + single-write-warehouse.
• User owns the environment; sqlanvil validates, never installs. Config declares requirements:, pythonVersion:, optional venv:.
• Phase mapping:
  • compile — file-existence/config-shape only (deterministic).
  • validate / run --dry-run — interpreter version check; requirements satisfied via importlib.metadata + packaging (offline, nothing installed); script syntax via py_compile (the Python analog of EXPLAIN). FAILURE ⇒ downstream BLOCKED, like imports.
  • run — spawn <interpreter> <file> <args>, cwd = project dir, SA_VARS env, exit 0 = success, output captured, timeout.
• Hosted Cloud rejects python actions at compile (same pattern as local import/export paths) until the managed-execution strategy question is decided separately.

Deliverables

• Protos: language-neutral Script (core.proto, language field — "python" in v1) + generic ScriptSpec on ExecutionAction (execution.proto). HARD REQUIREMENT: nothing Python-named in the protos — a second language (likely TypeScript, probably via Deno: no node_modules, native TS, permission-flag sandbox, Supabase-aligned) must be a CLI resolver + config sugar, not a proto migration. type: "python" stays as actions.yaml sugar.
• Core: core/actions/python_script.ts + actions.yaml wiring + CompiledGraph.pythonScripts
• CLI: build.ts marker task; run.ts dispatch (injectable seam, mirrors duckdbImport); interpreter/env resolver + validate checker (py_compile, importlib.metadata)
• Hosted-mode compile rejection
• Tests: compile + dispatch-seam + validate units; integration (script writes CSV → import → assertion)
• Example: python-action variant of the OpenAddresses loader in examples/supabase_bigquery_mailing_list (in-DAG local alternative to mailing_list example: scheduled OpenAddresses staging loader on GCP Cloud Run #47's scheduled Cloud Run variant)
• Docs: python-actions guide, validate page, AGENTS.md + skill; whats-new
• Release: 1.20.0

Out of scope (v1)

Compile-time Python; sqlanvil-managed environments (pip/uv install); hosted execution; dataframe-return contracts (files are the boundary); additional language implementations (TypeScript/Deno — protos are language-neutral from v1, so these are follow-ups, not migrations).

Related: #47 (scheduled staging loader on the user's own Cloud Run — stays valid as the hosted/scheduled variant).

[ihistand]

Shipped in @sqlanvil/{core,cli} 1.20.0 (and 1.20.1 — see below). All deliverables landed:

• ✅ Protos — language-neutral Script (CompiledGraph), ScriptSpec (ExecutionAction), ScriptConfig (actions.yaml oneof). Nothing Python-named; python: is yaml sugar normalized to script: { language: "python", ... } before proto verification (core/main.ts normalizeScriptSugar). A second language (TS/Deno) = a CLI resolver + one sugar entry.
• ✅ Core — core/actions/script.ts + actions.yaml wiring + CompiledGraph.scripts. Compile stays hermetic: config shape, file existence against the compiler's own file index, PEP 440 well-formedness — never runs Python.
• ✅ CLI — build.ts marker task; run.ts dispatch (injectable scriptRun seam, mirrors duckdbImport); script_run.ts spawn contract (cwd = project dir, SA_VARS + SA_ACTION_NAME, no warehouse creds, tail-capped output, 30-min default timeout); script_env.ts validate checker under the resolved interpreter (version / requirements via importlib.metadata + packaging with pip-vendored fallback / syntax) — FAILURE blocks dependents.
• ✅ Hosted-mode rejection — the Cloud runner pre-flight (assertNoScriptActions, beside the local-file-target guard) rejects graphs with scripts before a branch is provisioned.
• ✅ Tests — compile suite (sugar, deps, missing-file/bad-specifier/unsupported-language errors), dispatch seam, real-spawn contract (env/exit/tail/timeout/venv), env-checker against real python3, validate classification, and a live script→import→assertion chain in postgres.spec.
• ✅ Example — the mailing_list OpenAddresses ingestion is fully in-DAG: python: fetch_openaddresses (bundled sample, or a real openaddresses.io download via the oa_source_url var) → import → staging → mart.
• ✅ Docs — new Python Script Actions guide, validate/imports/Cloud pages, whats-new, AGENTS.md delta Run the MVP acceptance checklist against 1.4.0 #17, skill sync, regenerated reference.

1.20.1 follow-up (found smoke-testing the published 1.20.0): relative local location: on imports/exports resolved against the CLI's cwd, while scripts run with cwd = project dir — so sqlanvil run <projectDir> from anywhere else couldn't find a file a script had just staged. Both now resolve against the project directory.

Verified end-to-end with the published packages: validate reports per-problem failures (interpreter version, missing requirements, syntax) and run executes script → import → table → assertion green from a foreign cwd.