Automations

Automations are ordered workflows for ingest, transforms, alerts, and agent tasks.

For a scheduled monitor that runs a SQL probe, asks an LLM whether to act, and then acts — see Autonomous Agents. An automation can make a one-shot LLM judgment too (the llm_decision step plus a when guard), but an agent additionally remembers across runs, has a cooldown, and feeds its run history back to the model.

V1 shape:

pipeline definition -> queued run -> ordered run steps -> logs + outputs

The API stores definitions and run history in Postgres. The job-runner Deployment polls Postgres, claims queued runs, and executes each run's steps in order. Runs execute concurrently — up to jobRunner.maxConcurrentRuns (default 3) at a time per runner pod — but never two runs of the same pipeline at once, so watermarks and sync state stay serial per pipeline.

Pipeline Definition

{
  "name": "Flight count alert",
  "description": "Query the lakehouse and notify Slack.",
  "definition": {
    "steps": [
      {
        "id": "summarize",
        "type": "sql",
        "config": {
          "sql": "SELECT COUNT(*) AS flights FROM flights"
        }
      },
      {
        "id": "notify",
        "type": "slack_webhook",
        "config": {
          "integration": "slack-mjr-onprem",
          "text": "Automation finished: {{ run.id }}. Rows: {{ steps.summarize.rows_json }}"
        }
      }
    ]
  }
}

Besides steps, a definition may set "lane": "backfill" to mark the pipeline's runs as long full-history syncs — see Concurrency, lanes, and cancelling runs. The default lane is default.

At the pipeline level, run_after_pipeline_id may name one upstream pipeline. Each successful upstream run then enqueues this pipeline; see Run-after dependencies.

Step Types

Full set: sql, python, slack_webhook, send_email, email_report, pg_sync, adbc_sync, mssql_sync, assert_unique, agent, llm_decision, maintenance, and data_app_export.

Three of these deliver something to a person, and picking the wrong one is the usual mistake:

All of them authenticate themselves from the pipeline. None of them takes an API token, and you never need to create one to schedule a report.

sql

Runs SQL against the lakehouse.

{
  "id": "transform_orders",
  "type": "sql",
  "config": {
    "sql": "CREATE OR REPLACE TABLE marts.orders AS SELECT * FROM raw.orders",
    "max_output_rows": 100
  }
}

python

Runs a Python script inside the job-runner container, or in a burst sandbox when config.compute_profile names a non-default profile. The script receives AUTOMATION_RUN_ID, AUTOMATION_STEP_ID, and AUTOMATION_CONTEXT_JSON plus anything declared on config.env.

Set config.attach_lake: true when the script needs definite_lakehouse.query(), write_table(), or direct DuckDB/DuckLake access. For compatibility with definitions created before this flag existed, scripts that import or mention definite_lakehouse are also treated as attached when the field is omitted. Set attach_lake: false to disable that inference.

The script body is specified in exactly one of three ways:

• script_id — reference a stored script by its hex id.
• script_name — reference a stored script by its unique name.
• script — inline source, useful for one-liners or ad-hoc steps.

Prefer script_id / script_name: scripts are managed via the /api/v1/automation-scripts endpoint and edited in the frontend with a Monaco editor, so non-trivial Python doesn't have to be JSON-escaped into the pipeline definition.

Every python step runs via uv run --python 3.12 --no-project in an ephemeral venv. When the step (or stored script) declares requirements, the executor writes them to a requirements.txt and adds --with-requirements <path>, so the listed packages are installed for that run only. Resolved wheels are cached on the job-runner's uv cache volume (see helm/values.yaml: jobRunner.uvCache), so the second run with the same deps starts in seconds.

Baked-in libs: pyarrow, duckdb, polars, httpx, requests, psycopg2. For pandas, scikit-learn, or any other lib, add it to your step's or stored script's requirements and uv will install it per-run.

Stored script (recommended)

{
  "id": "load_api",
  "type": "python",
  "config": { "script_name": "load_hubspot" }
}

The job-runner resolves the reference, runs the latest content, and installs the row's requirements list before execution. The step output includes script_id, script_name, script_version, script_requirements, resolved_requirements, and uv_command for traceability.

Inline script (escape hatch)

{
  "id": "load_api",
  "type": "python",
  "config": {
    "timeout_seconds": 900,
    "env": {
      "SOURCE_NAME": "hubspot"
    },
    "requirements": ["httpx>=0.27", "polars>=0.20"],
    "script": "import os, httpx\nprint('load', os.environ['SOURCE_NAME'])"
  }
}

config.requirements is optional. Each entry must be a non-empty string; the format is whatever uv pip install accepts (PEP 508 specifiers, VCS URLs, etc.). It's only valid on inline scripts — stored scripts carry their own requirements column.

Automation scripts API

GET    /api/v1/automation-scripts/              # list (no body content)
POST   /api/v1/automation-scripts/              # create
GET    /api/v1/automation-scripts/{id_or_name}  # full record, including content
PATCH  /api/v1/automation-scripts/{id_or_name}  # update content/description/requirements; bumps version
DELETE /api/v1/automation-scripts/{id_or_name}  # 409 if any pipeline references it
POST   /api/v1/automation-scripts/{id_or_name}/fork  # copy to a custom script, optionally repointing pipelines
GET    /api/v1/automation-scripts/{id_or_name}/versions  # placeholder for v1.5
GET    /api/v1/connector-catalog/{connector_id}/loader  # current shipped core connector loader

Create payload:

{
  "name": "hubspot_loader",
  "description": "Pull contacts from HubSpot into raw.hubspot_contacts.",
  "content": "import os\nprint('loading', os.environ['SOURCE_NAME'])\n",
  "requirements": ["requests==2.32.0"]
}

version bumps on every PATCH. The integer is included in each step's output payload so a run's logs can be traced back to the exact script body that executed. Names are unique — pick something stable, since pipeline definitions reference scripts by name (or id), and renaming a referenced script will break pipelines that use the old name.

Core connector scripts

Connector-catalog pipelines reference a deployment-managed script named connector_<id>_loader by default. On API startup, the shipped connector catalog is reconciled into automation_scripts rows with origin: "core_connector", managed: true, connector_id, and an upstream_hash. These rows are the blessed connector loaders for the deployment and receive updates when a new API image ships a changed catalog.

Managed core connector scripts are read-only: PATCH and DELETE return 409 with guidance to fork first. Use POST /api/v1/automation-scripts/{id_or_name}/fork to create a custom copy. The fork keeps the connector metadata, records forked_from_hash, clears managed, and can take pipeline_ids to repoint those pipeline steps from the core script to the forked script name in the same transaction.

After a fork, upstream updates no longer overwrite the custom script. Use GET /api/v1/connector-catalog/{connector_id}/loader to fetch the current blessed loader when comparing or manually merging upstream changes.

Runtime requirements

The job-runner pod needs outbound network access to pypi.org (and files.pythonhosted.org) to install declared dependencies on the first run with a given dep set. The uvCache PVC keeps subsequent runs offline for the same versions. For air-gapped deployments, either point uv at a private index via UV_INDEX_URL (set in jobRunner.extraEnv) or omit requirements and bake the packages into a custom job-runner image.

Environment contract

The python subprocess receives an explicitly-constructed environment. Nothing else from the runner pod's environment leaks in. Scripts can rely on:

Anything else (e.g. AWS_SECRET_ACCESS_KEY or arbitrary operator-set vars on the runner pod) does NOT propagate. Put values you want available in either config.env for the step or jobRunner.extraEnv with a recognized prefix (today: UV_*). For sandboxed compute-profile steps, lake and store keys from config.env are still dropped unless attach_lake resolves to true.

Writing to the lakehouse

Every python step has definite_lakehouse preinstalled (the wheel is baked into the job-runner and fi-sandbox images and passed via uv run --with). The same wheel also provides connector_streaming — the connector-building layer the catalog loaders use. Custom sync scripts can import it instead of hand-rolling batching, checkpoint cursors, or OAuth token refresh:

from connector_streaming import write_stream, read_checkpoint
from connector_streaming import OAuthTokenProvider, request_with_retries
from definite_lakehouse import retry_commit_conflicts

write_stream writes a row iterable in bounded batches with commit-conflict retry; read_checkpoint/commit_checkpoint persist sync cursors in the queryable __definite_internal.connector_checkpoints table. Use the checkpoint helpers for sync cursors you want to inspect with SQL; use kv_get/kv_set (below) for small opaque state such as rotated tokens.

The common shape is to opt into lake access and then import the SDK:

{
  "id": "load_lake",
  "type": "python",
  "config": {
    "attach_lake": true,
    "script_name": "load_lake"
  }
}

import polars as pl
from definite_lakehouse import write_table, query

df = pl.DataFrame({"id": [1, 2, 3], "v": ["a", "b", "c"]})
write_table(df, "main.hello", mode="replace")  # or mode="append"

count = query("SELECT count(*) FROM main.hello").to_pylist()
print(count)

write_table accepts polars / pyarrow / pandas inputs (duck-typed — pandas is supported as input but isn't shipped in the image; declare it on config.requirements if your script also imports it directly). It inlines rows as a VALUES literal up to a 50k-row cap.

Using a stored integration — config.integrations

A python step can use a stored integration's credentials without fetching them over the API. Declare the integrations the step needs by name in config.integrations:

{
  "id": "ingest",
  "type": "python",
  "config": {
    "script_name": "hubspot-contacts-ingest",
    "attach_lake": true,
    "integrations": ["hubspot"]
  }
}

config.integrations is a list of integration names; it is only valid on python steps. At run time the job-runner resolves each declared integration in-process (decrypted, audited as an automation_run secret access — the same path pg_sync uses) and injects them into the subprocess as DEFINITE_INTEGRATIONS_JSON. The script reads them with get_integration():

from definite_lakehouse import get_integration, write_table

hs = get_integration("hubspot")
api_key = hs["secrets"]["api_key"]   # decrypted secret half
portal  = hs["public"]["portal_id"]  # non-secret config half
# ... call the HubSpot API, build an arrow table ...
write_table(tbl, "main.hubspot_contacts", mode="replace")

get_integration(name) returns {"name", "type", "public", "secrets"}. It raises a clear error if the step did not declare that integration in config.integrations. This replaces the old anti-pattern of passing an admin token through config.env and calling GET /api/v1/integrations/{id}/reveal from the script.

Durable step state — KV

Every Python step can persist small JSON values in the application Postgres without putting security-sensitive state in a queryable lakehouse table. Values are encrypted at rest with the deployment's rotatable application key. The runner gives each executing step a short-lived capability that authorizes only KV calls and revokes it when the step exits; no user API token or Postgres credential is exposed to the script.

For new code, read and write individual keys directly:

from definite_lakehouse import kv_get, kv_set

refresh_token = kv_get("xero-sync", "refresh_token")
# ... refresh OAuth credentials ...
kv_set("xero-sync", "refresh_token", rotated_refresh_token)

Values may be strings, numbers, booleans, null, lists, or objects, up to 64 KiB serialized. Namespaces are deployment-wide, so choose a stable, pipeline-specific name. Writes are last-write-wins; normal automation runs are already serialized per pipeline, so one namespace should have one pipeline as its writer.

Cloud SDK ports can keep the familiar buffered store pattern with a one-line construction change:

from definite_lakehouse import get_kv_store

state = get_kv_store("xero-sync")
token = state.get("refresh_token")
state["refresh_token"] = rotated_refresh_token
state.commit()

The compatibility store buffers assignments until commit(). It does not provide cloud store-wide optimistic versioning; each key is persisted with last-write-wins semantics.

Large writes — write_parquet_to_lake

For writes above the inline cap the SDK stages parquet to the object store and drives one CREATE … AS SELECT * FROM read_parquet('<uri>') on the lakehouse. This happens transparently — write_table auto-routes any call larger than 50k rows. Staging works out of the box: when jobRunner.staging.uri is left unset the chart derives a default (<lakehouse.dataPath>/_staging/), so it lives in the same bucket the lake already uses and the existing object-store credentials cover it. Set jobRunner.staging.uri explicitly to stage somewhere else.

You can also call the helper directly:

from definite_lakehouse import write_parquet_to_lake

write_parquet_to_lake(
    df,
    "main.big_table",
    mode="replace",   # or "append"
    # staging_prefix="gs://acme-lake/staging/",  # overrides LAKEHOUSE_STAGING_URI
    # cleanup=True,
)

Required env (Helm wires these from the lake's object-store config):

• LAKEHOUSE_STAGING_URI: a URI on the lake's own object store, matching lakehouse.storeType. gs://bucket/prefix/ for GCS, s3://bucket/prefix/ for S3/MinIO, or az://account.blob.core.windows.net/container/prefix/ (or legacy azure://…) for Azure Blob. Defaults to <lakehouse.dataPath>/_staging/; override with jobRunner.staging.uri.
• The lake's object-store credentials, the same ones the embedded DuckLake connection uses: GCS_HMAC_* for GCS, S3_* or IRSA for S3/MinIO, AZURE_STORAGE_* for Azure. The chart mounts them per lakehouse.storeType.

The parquet upload goes through DuckDB's native object-store client, using the same store_creds secret and signing path the lake reads parquet with, so it works on every store the lake supports, not just GCS. Staging files are not cleaned up inline (DuckDB's httpfs/azure extensions don't expose an object-store delete), so configure a lifecycle rule on the staging prefix (e.g. 1-day expiration) to reap files after the lake has loaded them.

slack_webhook

Posts a message to Slack.

{
  "id": "notify",
  "type": "slack_webhook",
  "config": {
    "integration": "slack-mjr-onprem",
    "text": "Finished run {{ run.id }}. Rows: {{ steps.summarize.rows_json }}"
  }
}

integration, integration_name, and integration_id all resolve a stored slack_webhook integration. For local/debug use, webhook_url_env and webhook_url are still supported, but stored integrations are preferred so secrets stay encrypted in Postgres.

Template variables in text

config.text is rendered as a Jinja2 template against the run context, so any expression you can write in Jinja works:

Standard Jinja filters (| tojson, | format, | upper, …) and control structures ({% if %}, {% for %}) are available. Unknown variables fail the step at render time rather than silently leak {{ … }} into the message — fix the typo and re-run.

The renderer uses Jinja2's SandboxedEnvironment (attribute access on private (_…) and dunder (__…) names is blocked), so step text is safe to evaluate even when authored by less-trusted users.

When to reach for a python step instead

Jinja in config.text is for pulling one or two values out of a prior step's first row. Anything beyond that belongs in a python step that queries the lakehouse and posts to Slack itself.

Signals that you're past what text should do:

• The message body branches ({% if %} deciding whether to alert, or which channel to hit).
• You're formatting a multi-row table, looping over rows, or accumulating state across them.
• You need values from more than one integration in the same message.
• You want to call an LLM, retry on failure, or rate-limit yourself.
• The SQL needs a follow-up call (enrichment, lookup, second query) before the message is built.
• You catch yourself reaching for | tojson and then parsing it back in your head to figure out what the message will look like.

In a python step you have the lakehouse, the integration's secrets, and the full standard library; there's no template language to fight.

The same alert, both ways

A "Stripe revenue dropped below $100k" alert as a SQL probe plus a templated slack_webhook. The probe's HAVING clause returns a row only when the total is below the threshold, so the notify step can guard on rows_nonempty:

{
  "steps": [
    {
      "id": "stripe_total",
      "type": "sql",
      "config": {
        "sql": "SELECT SUM(amount) / 100.0 AS total FROM stripe.charges WHERE status = 'succeeded' HAVING SUM(amount) / 100.0 < 100000"
      }
    },
    {
      "id": "notify",
      "type": "slack_webhook",
      "when": { "step": "stripe_total", "expr": "rows_nonempty" },
      "config": {
        "integration": "slack-mjr-onprem",
        "text": "Stripe revenue is ${{ '%.2f' | format(steps.stripe_total.rows[0].total) }}, below the $100k threshold."
      }
    }
  ]
}

That works, and for a single-value threshold it's the right tool. The moment the alert needs to also list the top failing customers, hit a second integration, or skip if you already pinged today, collapse it into one python step:

{
  "steps": [
    {
      "id": "stripe_alert",
      "type": "python",
      "config": {
        "attach_lake": true,
        "integrations": ["slack-mjr-onprem"],
        "script": "import requests\nfrom definite_lakehouse import query, get_integration\n\nTHRESHOLD = 100_000\nrows = query(\"SELECT SUM(amount) / 100.0 AS total FROM stripe.charges WHERE status = 'succeeded'\").to_pylist()\ntotal = rows[0]['total'] or 0\nif total >= THRESHOLD:\n    print(f'ok: ${total:,.2f}')\nelse:\n    slack = get_integration('slack-mjr-onprem')\n    url = slack['secrets']['webhook_url']\n    requests.post(url, json={'text': f'Stripe revenue is ${total:,.2f}, below the ${THRESHOLD:,} threshold.'}, timeout=10).raise_for_status()\n    print(f'alerted: ${total:,.2f}')\n"
      }
    }
  ]
}

definite_lakehouse is preinstalled, requests is baked into the image, and config.integrations decrypts the Slack webhook in-process — no template, no when clause, no second step to keep in sync. See python for the full environment contract and the stored-script workflow.

For a worked recipe that applies these pieces to data-freshness monitoring (alert when a table stops updating), see Staleness Checks.

send_email

Sends a plain templated email through the deployment's configured email service (EMAIL_MODE=smtp or definite_cloud; see the email configuration). Unlike the email_report step — which renders a data app to a PDF/PNG attachment — send_email just sends a message body, so it's the step to use for digests, alerts, and any pipeline that used to call the cloud SDK's send_email_message().

{
  "id": "notify",
  "type": "send_email",
  "config": {
    "to": ["alerts@example.com"],
    "subject": "Daily revenue: ${{ steps.total.rows[0].revenue }}",
    "html_body": "<p>Revenue today was <b>${{ steps.total.rows[0].revenue }}</b>.</p>",
    "text_body": "Revenue today was ${{ steps.total.rows[0].revenue }}."
  }
}

At least one of html_body / text_body is required; providing both sends a multipart message so clients that can't render HTML fall back to the text part.

subject, html_body, and text_body are rendered with the same Jinja2 context and SandboxedEnvironment as slack_webhook, so {{ steps.<id>.rows[0].my_col }}, {{ run.id }}, standard filters, and {% if %} / {% for %} all work, and an unknown variable fails the step at render time. If the deployment's email service is disabled or unconfigured, the step fails with a clear "requires configured email delivery" error rather than silently dropping the message.

To send an email from outside a pipeline (a script, an ad-hoc job, a migrated cloud service), use the POST /api/v1/email/send HTTP route instead — it delivers through the same email service.

email_report

Renders an existing data app in a headless browser and emails it as a PDF or PNG attachment. This is the step for "email me this dashboard every month": one step, no SQL, no scripting, and no API token.

{
  "id": "mail",
  "type": "email_report",
  "config": {
    "to": ["exec-team@example.com"],
    "app_slug": "second-look-trend",
    "subject": "Second Look dealer mix — {{ run.started_at }}",
    "format": "pdf",
    "pdf_orientation": "landscape",
    "viewport": { "width": 1440, "height": 1000 }
  }
}

Authentication is automatic, and this is the point of the step. The job-runner mints a short-lived render token for the pipeline's owner (the user who created the pipeline) and loads the app as them. Recipients get a flat attachment, not a link, so they need no Definite account. There is nothing to paste, store, or rotate — if a workflow is asking anyone for an API token in order to email a report, it has taken a wrong turn.

Because the render runs as the pipeline owner, the report shows exactly what that person can see. A pipeline owned by someone without access to the app's underlying tables produces an empty or partial report, so create the pipeline as a user who can open the app.

Prerequisites:

• The deployment has email delivery configured (EMAIL_MODE=smtp or definite_cloud). Otherwise the step fails with "email_report step requires configured email delivery". This is an admin setting; see email configuration.
• The data app already exists and renders. Build it first, open it once, then schedule it.
• The job-runner can reach the frontend. It uses EMAIL_REPORT_BASE_URL, FRONTEND_INTERNAL_BASE_URL, or PUBLIC_BASE_URL, in that order.

The renderer waits for the app's charts to finish loading before it captures the page, up to EMAIL_REPORT_READY_TIMEOUT_MS (default 120000). An app whose queries never resolve fails the step with "email_report render timed out" rather than mailing a picture of a loading spinner. If reports start timing out, the app itself is slow — profile its queries rather than raising the timeout.

Widening viewport.width fits more of a dense dashboard on the page; it does not re-flow the app to a narrower layout. For a tall dashboard prefer format: "png", which captures the full scroll height, over a PDF that has to pick one page size.

Recipe: email a data app every month

The whole pipeline, ready to POST /api/v1/automations/pipelines. 0 13 1 * * is 13:00 on the 1st of each month, in cron_timezone.

{
  "name": "Monthly Second Look report",
  "description": "PDF of the Second Look dealer-mix app, 1st of the month.",
  "enabled": true,
  "cron_schedule": "0 13 1 * *",
  "cron_timezone": "America/New_York",
  "definition": {
    "steps": [
      {
        "id": "mail",
        "type": "email_report",
        "config": {
          "to": ["exec-team@example.com"],
          "app_slug": "second-look-trend",
          "subject": "Second Look dealer mix — monthly",
          "format": "pdf",
          "pdf_orientation": "landscape"
        }
      }
    ]
  }
}

Weekly instead is 0 13 * * 1 (Mondays at 13:00). Use a real timezone rather than UTC so the send does not drift an hour across daylight saving.

Before trusting the schedule, trigger it once by hand with POST /api/v1/automations/pipelines/{id}/runs and open the attachment. That one run confirms the three things a schedule cannot: email delivery is configured, the pipeline owner can see the app, and the app renders.

If the report should reflect data that a sync or transform produces, do not race them on two crons. Put the email_report step last in the same pipeline, or point the report pipeline's run_after_pipeline_id at the pipeline that refreshes the data — see Run-after dependencies.

pg_sync

Pulls a single table from a stored postgres integration into the lakehouse. Runs in the job-runner's embedded DuckDB connection: it ATTACHes the source Postgres via DuckDB's postgres extension and streams rows directly into DuckLake (the Postgres-catalog lake).

{
  "id": "pull_orders",
  "type": "pg_sync",
  "config": {
    "integration": "pg-prod",
    "source_table": "public.orders",
    "destination_table": "main.orders",
    "mode": "incremental",
    "watermark_column": "updated_at",
    "primary_key": ["id"]
  }
}

Modes:

• full_refresh — drops and recreates the destination on every run. Simple, always correct, but rescans the entire source table each time.
• incremental — pulls rows where watermark_column > last_seen_value. If primary_key is provided, rows are upserted (DELETE-then-INSERT by key); otherwise they're appended. Watermarks are persisted in automation_pg_sync_state keyed by (integration_id, source_table, destination_table), so the next run resumes from the last successful sync. On the first run the watermark is null and the full table is loaded.

There is deliberately no separate "backfill" mode: a backfill is just the first run of an incremental sync (empty watermark state → full history, chunked and checkpoint-resumable), or a full_refresh. For a large initial load, configure incremental from day one, give the step a non-default compute_profile (it runs as a dedicated sync-executor Job — see Sync-executor Jobs), and put the pipeline in "lane": "backfill"; when the first run finishes, the watermark is already seeded and the same pipeline is your steady-state sync.

Full-refresh resume (#710): the chunked, key-windowed full_refresh path checkpoints each staged window in automation_pg_full_refresh_state. A run killed mid-backfill (pod OOM, node loss, helm roll) resumes from the last committed window on its next attempt instead of restarting — a crash costs at most one window. The checkpoint is fingerprinted to the extraction (source_sql, chunk key, chunk size); changing any of them starts clean. The staging table still swaps in atomically at the end, so the destination is never observed half-populated. The keyless (ctid-paged) fallback stays non-resumable — ctid order isn't stable across restarts.

Postgres type/customization controls:

• column_casts — optional object mapping source column names to Postgres cast types. pg_sync already casts Postgres enums and other source-defined types to text; use this for explicit choices like "column_casts": {"status": "text", "payload": "jsonb"}.
• source_sql — optional Postgres SELECT/WITH query to read instead of SELECT * FROM source_table. Use it for casts, joins, computed columns, or source-side filters. It must be a single semicolon-free read query. For incremental syncs, watermark_column and primary_key must refer to columns returned by this query.

Schema drift (applies to pg_sync, adbc_sync, and mssql_sync alike):

• A column added on the source is added to the destination automatically before the next incremental load (ALTER TABLE ... ADD COLUMN, logged, and reported in the step output as columns_added). Rows synced before the column existed hold NULL — run a one-off full_refresh to backfill them.
• A column dropped on the source stays on the destination; new rows hold NULL there. Synced data is never dropped automatically.
• A type change on an existing column is logged as a warning and left to the insert's implicit cast; an incompatible change fails the run with the column named. Switch the step to full_refresh once to rebuild.

Limitations:

• Deletes on the source are not detected. Use full_refresh for tables where rows can be hard-deleted.
• The customer's Postgres must be reachable from the job-runner pod. Lock down with NetworkPolicy + a least-privilege read-only role.
• Credentials live in the stored postgres integration (encrypted at rest); the connection string is interpolated into the SQL the job-runner runs, so the job-runner pod sees them in plaintext at run time.

adbc_sync

Pulls a single table from a stored snowflake integration into the lakehouse. The ADBC counterpart of pg_sync. There is no Snowflake DuckDB extension, so unlike pg_sync (which streams directly through DuckDB's postgres extension) this cannot stream in-engine: the job-runner opens an ADBC connection, fetches the result as Apache Arrow, stages it as a parquet file to the lake's object store, and reads it back into DuckLake with read_parquet() on its embedded DuckDB connection.

{
  "id": "pull_orders",
  "type": "adbc_sync",
  "config": {
    "integration": "snowflake-prod",
    "source_table": "ANALYTICS.ORDERS",
    "destination_table": "main.orders",
    "mode": "incremental",
    "watermark_column": "UPDATED_AT",
    "primary_key": ["ID"]
  }
}

Modes:

• full_refresh — replaces the destination on every run.
• incremental — pulls rows where watermark_column > last_seen_value. If primary_key is provided rows are upserted (DELETE-then-INSERT by key); otherwise they are appended. Watermarks are persisted in automation_adbc_sync_state keyed by (integration_id, source_table, destination_table). On the first run the watermark is null and the full table is loaded. An incremental run that finds no new rows is a no-op and leaves the watermark unchanged.

Customization:

• source_sql — optional Snowflake SELECT to read instead of SELECT * FROM source_table. Use it for casts, joins, computed columns, or quoted/lower-case identifiers. For incremental syncs watermark_column and primary_key must refer to columns it returns.

Limitations:

• source_table identifiers are emitted verbatim and unquoted — Snowflake folds them to upper case. Use source_sql for quoted or lower-case names.
• The whole result set is buffered as one Arrow table in the job-runner. Incremental deltas are small; a full_refresh of a very large table is the one heavy case (range-chunking it is a planned follow-on).
• Deletes on the source are not detected — use full_refresh for tables where rows can be hard-deleted.
• Requires object-store staging: LAKEHOUSE_STAGING_URI must be set (Helm sets it when jobRunner.staging.uri / the lake data path is configured). Staged parquet files are reaped by the object store's lifecycle rule.
• Credentials live in the stored snowflake integration (encrypted at rest). Password and key-pair (JWT) auth are both supported; key-pair is preferred for service accounts.

mssql_sync

Pulls a single table from a stored sqlserver integration into the lakehouse. SQL Server has no ADBC driver and no built-in DuckDB extension, so the job-runner opens an in-process DuckDB connection, attaches the source through the DuckDB mssql community extension, stages the result as parquet, and has the lakehouse read it back.

{
  "id": "pull_loans",
  "type": "mssql_sync",
  "config": {
    "integration": "sqlserver-prod",
    "source_table": "dbo.Loans",
    "destination_table": "main.loans",
    "mode": "incremental",
    "watermark_column": "updated_at",
    "primary_key": ["id"]
  }
}

Modes:

• full_refresh — replaces the destination on every run.
• incremental — pulls rows where watermark_column > last_seen_value. If primary_key is provided rows are upserted (DELETE-then-INSERT by key); otherwise they are appended. Watermarks are persisted in automation_mssql_sync_state keyed by (integration_id, source_table, destination_table).

Connection defaults:

• The sqlserver integration uses use_encrypt=true by default.
• Set trust_server_certificate=true only when the SQL Server presents a self-signed certificate or a certificate from an internal CA that the deployment does not trust. The test-connection path names this toggle when certificate validation fails.

Customization:

• source_sql — optional SELECT to read instead of SELECT * FROM source_table. It should reference the attached source as mssql_src.<schema>.<table>. For incremental syncs watermark_column and primary_key must refer to columns it returns.

Limitations:

• The whole result set is buffered as one Arrow table in the job-runner. Incremental deltas are small; a full_refresh of a very large table is the heavy case. Range-chunked full refresh is a planned follow-on.
• Deletes on the source are not detected — use full_refresh for tables where rows can be hard-deleted.
• Requires object-store staging: LAKEHOUSE_STAGING_URI must be set (Helm sets it when jobRunner.staging.uri / the lake data path is configured).
• Credentials live in the stored sqlserver integration (encrypted at rest).

assert_unique

Asserts that a lakehouse table holds at most one row per key. On a pass the step records the row/key counts and the run continues; on a violation the step (and the run) fails with the duplicate count and a sample of up to 10 offending keys, so a poisoned load is caught at write time instead of silently fanning out every downstream join.

{
  "id": "verify_keys",
  "type": "assert_unique",
  "config": {
    "table": "redshift_cleanid.transactiondata",
    "key": "transactionid"
  }
}

Config:

• table (required) — the table to check, as schema.table or a bare table name. Parts must be plain identifiers (letters, digits, underscores).
• key (required) — a single column name, or a list of column names for a composite key.

The check is one aggregate scan — count(*) vs count(DISTINCT (<key columns>)) — so it stays cheap even on 100M+-row tables. Only when the counts disagree does the step run a second GROUP BY <keys> HAVING count(*) > 1 probe to capture the worst offenders (key values plus their row counts) into the step error and logs. NULL key values count toward count(*) but not count(DISTINCT ...), so NULL keys also fail the assertion (a merge key must be non-null); when no key value occurs twice the error says the violation is NULLs.

Step output on a pass:

{
  "table": "redshift_cleanid.transactiondata",
  "key": ["transactionid"],
  "total_rows": 134300000,
  "distinct_keys": 134300000,
  "duplicate_rows": 0,
  "passed": true
}

The backfill invariant

The incremental syncs write merge-by-pk (DELETE incoming keys, then INSERT incoming rows), so a synced table holds one row per key going forward by construction — downstream models may rely on that instead of re-deduplicating. But bulk-import paths (replace+append staging loads, parquet manifest copies, chunked full-refresh backfills) bypass merge, and sources do not necessarily enforce primary-key uniqueness — Redshift, for one, does not enforce declared PKs at all. The merge maintains uniqueness for every key it pulls but cannot create it: a backfill that copies source-side duplicates poisons the table indefinitely, and no scheduled incremental run will ever repair it.

Every bulk-import or backfill must therefore end with an assert_unique step (or a dedup-repair) on the merge key. Chaining the step after the load is one cheap scan; scheduling it weekly on its own also works as insurance for tables whose downstream models depend on the invariant.

Repairing a table that already has duplicates

When assert_unique fails on an already-loaded table, repair just the duplicated keys — delete those keys, then reinsert exactly one surviving row per key — instead of re-running the whole backfill:

-- 1. Capture the offending keys (the step's failure log holds a sample;
--    this captures all of them).
CREATE OR REPLACE TABLE main.dupe_keys AS
SELECT transactionid
FROM redshift_cleanid.transactiondata
GROUP BY transactionid
HAVING count(*) > 1;

-- 2. Stash one surviving row per duplicated key. Pick a deterministic
--    tiebreak (a watermark column, or re-select from the source when it
--    holds the authoritative row).
CREATE OR REPLACE TABLE main.dupe_repair AS
SELECT *
FROM redshift_cleanid.transactiondata
WHERE transactionid IN (SELECT transactionid FROM main.dupe_keys)
QUALIFY row_number() OVER (
    PARTITION BY transactionid ORDER BY updated_at DESC
) = 1;

-- 3. Delete every row for those keys, then reinsert the deduped survivors.
DELETE FROM redshift_cleanid.transactiondata
WHERE transactionid IN (SELECT transactionid FROM main.dupe_keys);
INSERT INTO redshift_cleanid.transactiondata
SELECT * FROM main.dupe_repair;

DROP TABLE main.dupe_repair;
DROP TABLE main.dupe_keys;

Scoped to just the duplicated keys, the repair touches thousands of rows instead of rewriting a 100M+-row table. Chain an assert_unique step after the repair to prove it worked.

agent

Runs a Fi/Pi agent step when Fi is enabled.

{
  "id": "agent_check",
  "type": "agent",
  "config": {
    "prompt": "Inspect the mart tables and summarize anything unusual.",
    "timeout_seconds": 1800
  }
}

llm_decision

Makes one structured LLM call and returns the decision as the step output, so later steps can guard on it with a when clause. It is the building block for "run this SQL, ask an LLM whether it's worth alerting on, and only then post to Slack."

{
  "id": "judge",
  "type": "llm_decision",
  "config": {
    "instructions": "Decide if the flight count in `summarize` is anomalous.",
    "inputs": ["summarize"],
    "decision_integration": "anthropic-prod",
    "decision_model": "claude-sonnet-4-6"
  }
}

Config:

• instructions (required) — the system prompt; what the model should judge.
• inputs (optional) — a list of earlier step ids whose outputs are fed to the model. Omit it to pass every prior step's output.
• decision_integration (optional) — a stored anthropic / openai integration to call. Omitted, the step uses the deployment LLM (the same llm: block in config.yaml that Fi uses) — no second credential needed.
• decision_model (required only when decision_integration is set) — an integration carries just an API key, so the model must be named alongside it. With no integration, the model falls back to the deployment LLM_MODEL.

The step output is the decision dict:

{ "should_act": true, "reasoning": "...", "action_args": { "text": "..." }, "model": "..." }

A downstream step guards on it with "when": { "step": "judge", "expr": "output_truthy:should_act" }.

Unlike an agent, an llm_decision step is single-shot and memoryless — it sees no prior runs and keeps no durable state. Reach for an agent when you need cross-run memory, a cooldown, or run history fed back to the model.

maintenance

Runs one DuckLake maintenance operation against the lake.

{
  "id": "compact_orders",
  "type": "maintenance",
  "config": { "operation": "compact", "schema": "analytics", "table": "orders" }
}

config.operation is one of compact, rewrite, flush_inlined_data, checkpoint, expire_snapshots, cleanup_old_files, delete_orphaned_files, vacuum_catalog, or full. Table-scoped operations require schema and table together; compact accepts both or neither (neither compacts the whole lake).

Most deployments should not hand-author these. The product keeps a reserved ducklake-maintenance pipeline covering the whole schedule in one place — see Managed maintenance schedule. Write an explicit maintenance step only to attach one operation to a pipeline of your own, such as compacting a table right after the sync that rewrote it.

data_app_export

Internal. The product enqueues this step itself when someone exports a data app's data to CSV; it carries a pre-signed object_uri under data-app-exports/<slug>/ plus row and byte caps, and the URI format is validated on save.

Do not hand-author it, and do not reach for it to deliver a report — it writes a CSV into object storage and emails nobody. To email a dashboard use email_report; to email numbers in a message body run a sql step and pass its output to send_email.

Step guards — when

Any step may carry an optional when clause. A step whose when evaluates false is skipped — it is not run, not failed, and the run continues to the next step. Steps still execute in their declared order; when only short-circuits an individual step, it is not a jump target or a branch.

{
  "id": "notify",
  "type": "slack_webhook",
  "when": { "step": "summarize", "expr": "rows_nonempty" },
  "config": { "integration": "slack-ops", "text": "Found rows!" }
}

when.step must reference an earlier step's id. when.expr is a small, closed vocabulary evaluated against that step's output — not an expression language:

A failed expr is reserved but unreachable today: the executor aborts the whole run on a step failure, so no later step could ever observe a failed one. It is accepted by the definition validator (so a definition written against a future continue-on-error mode still validates) but a failed guard always evaluates false in the current linear executor.

A guard that references a step which itself was skipped (and so produced no output) evaluates false — the guarded step is skipped in turn.

Step status

Each run step ends in one terminal status:

• succeeded — the step ran and finished cleanly.
• failed — the step raised; the whole run is failed and stops here.
• skipped — the step's when guard evaluated false; the run continued.

Step timing

GET /api/v1/automations/runs/{run_id} returns each step row with its raw timing columns plus a computed duration_ms. The contract:

• started_at is the start of the step's last attempt. It is reset on every attempt, not just the first. A run whose job-runner died mid-step is automatically requeued up to the configured stale-recovery budget (see Runner death recovery below) and re-executes every step, so a re-run step's timestamps always bracket the attempt that actually produced its result — never the span across the requeue gap.
• attempts counts the starts. attempts > 1 means the step was re-executed by a requeued run.
• duration_ms = finished_at − started_at, i.e. the wall time of the last attempt. It is null while the step is unfinished: still running (compute live elapsed from started_at), still queued, or skipped (a when-skipped step never starts).
• An absent finished_at on a terminal run means the runner died mid-step and the run had exhausted its stale-recovery budget. The reaper stamps such steps failed with finished_at set at reap time, so their duration includes the ~3-minute detection window; until the reaper acts, a running step row under a dead runner is indistinguishable from live work except via the run's stale heartbeat_at.

Transformation runs additionally return model_durations — one record per transformation_model (each compiled step is one model), sorted by duration descending — so "which model is eating the run?" is one API call:

{
  "model_durations": [
    { "model": "fct_transactions_all", "step_id": "model_fct_transactions_all",
      "status": "succeeded", "duration_ms": 6222000, "attempts": 1 },
    { "model": "stg_payments", "step_id": "model_stg_payments",
      "status": "succeeded", "duration_ms": 41000, "attempts": 1 }
  ]
}

CLI status JSON

definite run automation status <run_id> --format json returns the API run payload inside the standard CLI envelope:

{
  "ok": true,
  "data": {
    "run": { "id": "run-1", "status": "succeeded" },
    "steps": [],
    "logs": []
  },
  "meta": { "elapsed_ms": 12 }
}

The canonical run status path is data.run.status (jq: .data.run.status). Polling code should read that nested path.

API

GET    /api/v1/automations/pipelines
POST   /api/v1/automations/pipelines
GET    /api/v1/automations/pipelines/{pipeline_id}
PATCH  /api/v1/automations/pipelines/{pipeline_id}
POST   /api/v1/automations/pipelines/{pipeline_id}/runs
POST   /api/v1/automations/pipelines/{pipeline_id}/trigger-schedule
POST   /api/v1/automations/pipelines/{pipeline_id}/runs/{run_id}/cancel
GET    /api/v1/automations/runs
GET    /api/v1/automations/runs/{run_id}
POST   /api/v1/automations/runs/{run_id}/cancel

All routes require the same bearer token auth as the rest of the API. trigger-schedule requires admin.

Concurrency, lanes, and cancelling runs

The job-runner executes up to jobRunner.maxConcurrentRuns (Helm value, default 3) runs at once on a thread pool; set it to 1 for the old strictly serial behavior. Two guarantees hold regardless of the setting:

• One active run per pipeline. A queued run whose pipeline already has a running or awaiting_external run is skipped (later runs of other pipelines still claim), so per-pipeline sync state never races. This also holds across multiple job-runner replicas.
• Backfills can't monopolize the pool. A pipeline definition (or a single POST .../runs body) may set "lane": "backfill" to mark long full-history syncs. With two or more slots, at most slots − 1 backfill in-runner runs execute at once — one slot is always reserved for normal runs. Profile-bound durable Jobs hold zero runner slots, so their number does not reduce business-as-usual runner capacity at all.

Cancel one run — instead of every non-terminal run of a pipeline — with either POST /api/v1/automations/runs/{run_id}/cancel (by run id) or the pipeline-scoped POST /api/v1/automations/pipelines/{pipeline_id}/runs/{run_id}/cancel, which 404s if the run doesn't belong to that pipeline. From the CLI: definite run automation cancel <run_id>. A queued run is never claimed; a running run stops between steps, and an in-flight python step is killed mid-flight — the runner polls the run status every few seconds and terminates the step subprocess (or tears down its burst sandbox) on cancel. A sync step running as a sync-executor Job (below) is also killed mid-flight: the runner deletes the Job, and the persisted chunk checkpoint survives for the next run. The run finishes as cancelled, the interrupted step is marked cancelled, and a log line records the termination. Other in-flight step types (sql, in-pod pg_sync, …) finish their current statement first and stop at the next between-steps checkpoint.

External compute-profile Jobs

A pg_sync / adbc_sync / mssql_sync step that requests a non-default compute_profile runs as a dedicated Kubernetes batch/v1 Job sized by that profile, instead of inside the job-runner pod or on a burst sandbox (#710). This is the execution surface for full-history backfills and any other long sync:

• Sized by the profile. The Job's pod carries the profile's resources, node_selector, and tolerations — the same scheduling triple burst sandboxes use, typically landing on a scale-to-zero burst node pool. The pod's embedded DuckDB is bounded by the profile's memory limit (the chart's downward-API sizing follows the executor container).
• No idle reaper, held RPC, or runner slot. The executor clones the job-runner's container (image, env, secrets, scratch volume) and re-enters it as python -m job_runner.sync_executor; progress flows through the automation_sync_jobs handshake row and the run log, not a long-lived HTTP connection. After launch the automation enters awaiting_external and releases its thread-pool slot. Sandbox idle reaping cannot kill it, and a 12-hour run consumes no job-runner execution capacity.
• Restart-safe. restartPolicy: OnFailure with a bounded backoffLimit (default 3) restarts a dead executor pod; the restarted attempt resumes from the persisted full-refresh checkpoint or incremental watermark. activeDeadlineSeconds (default 48 h) is the overall wall-clock cap. If the job-runner dies instead, the reaper requeues the run and the new the parked run and its deterministic Job remain intact; the new runner reconciles them without replaying the step.
• Visible. The executor writes chunk progress into the normal run log (automation status <run_id>), heartbeats the handshake row, and pod failures surface with their real reason (e.g. OOMKilled → raise the profile's memory).
• Works without Fi. The surface needs only core Kubernetes RBAC (shipped with the chart) — unlike burst sandboxes it does not require the agent-sandbox controller. It also lifts the burst path's portability restriction: SSH-bastion / pasted-cert sources work, because the tunnel opens inside the executor pod itself.

Operator knobs live under jobRunner.syncJobs in values.yaml (enabled, activeDeadlineSeconds, backoffLimit). With enabled: false — or in environments without cluster credentials — sync steps fall back to their previous behavior (in-pod, or pg_sync's burst sandbox path).

Python steps with a non-default profile can use the same durable lifecycle by setting jobRunner.pythonJobs.enabled: true. This is off by default for a backwards-compatible rollout. The Job uses the profile's CPU, memory, workspaceSize, node selector, and tolerations; arbitrary Python defaults to backoffLimit: 0 so Kubernetes never silently replays a non-idempotent script. Stored-script requirements are merged with step-level config.requirements, with the step's distribution pin winning. Stdout/stderr lines stream into the normal run log, and the final captured tails remain in step output. The user subprocess receives the existing allowlisted step environment rather than the executor pod's platform environment, so values such as POSTGRES_URL and APP_ENCRYPTION_KEY are not inherited. Cancellation deletes the Job.

Runner death recovery. Claimed runs carry a liveness lease: the runner heartbeats its in-flight runs every poll cycle, and a reaper (also ticked every cycle, replica-safe) recovers running runs whose heartbeat has gone stale for ~3 minutes — the signature of an OOM-killed pod, a lost node, or a helm roll mid-run. Recovery requeues the run up to jobRunner.staleRunMaxRequeues times (default 1; catalog connectors checkpoint-resume, so an interrupted backfill picks up where it left off); a run that keeps killing its runner fails terminally instead of crash-looping. jobRunner.runLeaseSeconds controls the stale detection window. The run log records each recovery.

Concurrent writes to the lake. DuckLake snapshot commits are global, so parallel runs writing the lake can conflict even on disjoint tables. The SDK raises DuckLake's internal retry budget (ducklake_max_retry_count = 100), the catalog connectors' shared checkpoint/write helpers retry commit conflicts with exponential backoff, and the SDK's own write_table / write_parquet_to_lake retry them too — including the race two concurrent first-writes have on auto-creating the same new schema. Concurrent connector runs and plain SDK writes are safe out of the box; only scripts issuing hand-written DDL through query() need their own TransactionException retry.

Scheduling

Pipelines can run on a cron schedule by setting cron_schedule (a standard 5-field expression) and optionally cron_timezone (IANA name, defaults to UTC) at create or update time. The job-runner runs a scheduler tick every ~5s on a dedicated thread, isolated from run claiming and execution — a multi-hour step occupying every run slot cannot delay a cron fire. Overdue scheduled pipelines are enqueued with trigger_type = "scheduled" (note: "scheduled", not "cron" — filter run history on that) and next_fire_at is advanced to the next future tick. Every tick logs one scheduler_tick: … last_ticked_at=… line with fired/skipped counts; that line going quiet in the job-runner's logs means the scheduler itself is wedged.

{
  "name": "Refresh hourly",
  "cron_schedule": "*/15 * * * *",
  "cron_timezone": "UTC",
  "definition": { "steps": [ /* ... */ ] }
}

Rules and limits:

• 5 fields only — minute hour day month weekday. 6-field (seconds-precision) expressions are rejected; the runner polls every ~5s, so the minimum useful interval is once per minute.
• Schedules that would fire more often than once per minute are rejected with a 400 at create/update.
• Invalid cron syntax or unknown IANA timezones return 400.
• Manual triggering still works via POST /pipelines/{id}/runs. It does not interfere with the next scheduled fire.
• No missed-run replay. If the job-runner is down and next_fire_at falls more than an hour behind, the next tick skips the backlog, logs a warning, and jumps next_fire_at to the next future tick.
• Re-enabling a scheduled pipeline recomputes next_fire_at from "now", even when the update carries no cron fields (clients that PATCH {"enabled": true} alone included) — a pipeline whose next_fire_at went stale while disabled fires again within one tick of being enabled, it never silently stays dead.
• Multiple job-runner replicas are safe — the tick uses FOR UPDATE SKIP LOCKED, so each scheduled fire enqueues exactly one run.
• POST /pipelines/{id}/trigger-schedule (admin-only) manually advances next_fire_at by one tick without enqueueing — useful for testing the schedule on a long-period cron.

Run-after dependencies

Set run_after_pipeline_id to run one pipeline after another succeeds. The relationship is success-only, single-parent, and fan-out: pipeline B can name pipeline A, and any number of other pipelines can also name A. Cron and manual triggers remain available alongside it.

{
  "name": "Build attributed events",
  "run_after_pipeline_id": "<upstream-pipeline-id>",
  "definition": { "steps": [ /* ... */ ] }
}

The API rejects missing or archived upstream pipelines and rejects direct or transitive cycles at save time. On success, the upstream run and downstream enqueue commit atomically. If the downstream pipeline already has a queued or running run, the event is coalesced instead of adding another queued run. Runs created this way have trigger_type = "upstream".

This chains whole linear pipelines; it does not add branching steps, loops, or multi-parent AND dependencies within a pipeline.

Managed maintenance schedule

A convenience endpoint for the single most common automation a lake needs: a periodic DuckLake maintenance pass. It owns a single reserved pipeline named ducklake-maintenance (one per deployment) so the entire schedule can be inspected and replaced with one request, no per-step pipeline authoring required.

GET  /api/v1/lakehouse/maintenance/schedule    # any user
PUT  /api/v1/lakehouse/maintenance/schedule    # admin only

GET returns { "schedule": null } if no schedule has been configured. Otherwise it returns the current cron, timezone, enabled flag, resolved next_fire_at, and the flattened steps list (one entry per maintenance op).

Auto-provisioning. On API startup the lifespan calls bootstrap_managed_maintenance_schedule: if no pipeline exists with the reserved name (any state — enabled, archived, even an operator-created collision) it inserts the default schedule (cron 0 2 * * 0 UTC, the default step list below, enabled). After first boot the operator owns the row: edit / disable / archive / delete are all respected — with one exception: an untouched schedule from a recognized prior default generation is upgraded in place on the next boot. This removes the former redundant leading CHECKPOINT while preserving customized schedules. A default schedule whose burst profiles were stripped (a pre-v0.1.121 PUT that omitted compute_profile cleared them) is also re-stamped on the next boot; to keep a deliberately cleared profile, change any step field so the schedule no longer matches the default shape. Any edited schedule no longer matches and is never touched. Wiping the row and restarting is the only other way back to the default.

PUT body:

{
  "cron": "0 3 * * *",
  "timezone": "UTC",
  "enabled": true,
  "steps": [
    { "operation": "checkpoint" },
    { "operation": "compact", "schema": "main", "table": "orders" },
    { "operation": "expire_snapshots", "older_than_days": 30 },
    { "operation": "cleanup_old_files", "older_than_days": 30 }
  ],
  "confirm_destructive": true,
  "compute_profile": "lakehouse-large"
}

Behaviour:

• Upsert by reserved name. Replaces the existing ducklake-maintenance pipeline if it exists, or creates it if not. An archived row is revived.
• Collision guard. A pipeline that already exists with the reserved name but was not written by this endpoint (i.e. its description lacks the [managed:maintenance-schedule] tag) returns 409; rename or delete it before re-PUTting the schedule.
• Cadence floor. Schedules that fire more often than once per day are rejected — maintenance ops can scan the whole lake, so a sub-daily cadence is almost always a misconfiguration. Run ad-hoc maintenance via POST /lakehouse/maintenance/runs if a one-off shorter cycle is needed. The general per-pipeline 1-minute floor still applies to other pipelines.
• Defaults. Omit steps entirely and the endpoint installs the canonical list: flush_inlined_data, expire_snapshots(older_than_days=30, dry_run=false), whole-lake compact (merge_adjacent_files), whole-lake rewrite(delete_threshold=0.95), cleanup_old_files(older_than_days=30, dry_run=false), delete_orphaned_files(older_than_days=30, dry_run=false), and vacuum_catalog (a plain VACUUM (ANALYZE) of the catalog Postgres). These are the operations DuckLake bundles into CHECKPOINT, expressed as individual steps so each has its own retry boundary, output, and settings. Destructive steps run live on purpose — a dry-run schedule previews-but-never-reclaims, which silently leaks storage. The confirm_destructive acknowledgment applies only to an explicit steps list that sets dry_run=false on a destructive op; requesting the defaults doesn't re-prompt.
• Burst profile. compute_profile applies to the schedule as a whole. A profile name routes each step to that burst tier, except vacuum_catalog — that step VACUUMs the catalog Postgres in-pod, and a burst sandbox has no Postgres credentials. If the field is absent, null, or empty, the stored profile stays: a cron-only PUT does not change burst routing. Only the explicit value "default" clears the profile, and all steps then run in the job-runner pod. The default schedule runs its heavy steps on lakehouse-large.
• Step shape. Each steps[i] is a maintenance step config (operation + the same per-op fields the ad-hoc /runs endpoint accepts: schema, table, older_than_days, delete_threshold, dry_run). The step is validated through the same validate_maintenance_config rule used by the AutomationStep model.
• enabled: false keeps the row + definition around but clears next_fire_at so the scheduler skips it. Re-enable by PUTting again with enabled: true.

The resulting pipeline is a regular automation_pipelines row — runs, logs, and history all show up under the same /api/v1/automations/ endpoints. The convenience is in the auto-naming, upsert semantics, and the maintenance-specific cron + confirmation rules.

UI. Settings → Maintenance ships a "Scheduled maintenance" card that calls the endpoints above: cron + timezone fields, three preset chips (daily 03:00 UTC, weekly Sun 02:00 UTC, monthly 1st 02:00 UTC), an enabled checkbox, a read-only summary of the current step list, and the human-readable cadence next to the cron input. Editing the step list (or toggling dry_run=false on a destructive op) still requires the PUT endpoint.

Current limits

• Steps execute in declared order; no jump targets, DAG, or fanout. A step may be skipped by a when guard, but the order is fixed. Concurrency is per-run (see Concurrency, lanes, and cancelling runs); steps within one run are always sequential.
• Python steps run in the shared job-runner pod unless they set a non-default compute_profile; memory-heavy workloads should use a burst profile. Concurrent in-pod python steps share the pod's CPU/memory limits — size resources.jobRunner.resources with jobRunner.maxConcurrentRuns.
• Mid-step cancellation covers python steps (in-pod and burst-sandbox). Other step types cancel at the next between-steps checkpoint.

Slack setup

Create a Kubernetes Secret in the deployment namespace and expose it to job-runner via Helm values:

kubectl create secret generic definite-automation-secrets \
  -n definite \
  --from-literal=slack-webhook-url="$SLACK_WEBHOOK_URL"

jobRunner:
  extraEnv:
    - name: SLACK_WEBHOOK_URL
      valueFrom:
        secretKeyRef:
          name: definite-automation-secrets
          key: slack-webhook-url

Do not commit actual Slack webhook URLs.