Pipeline Contract Specification

Status: v1.69 (revised — see Change Log) Owner: datapipelines.co core Depends on: Type System spec Last updated: 2026-11-01

1. Purpose

This spec defines the structure and lifecycle of a Pipeline — the central artifact of datapipelines.co. A Pipeline is a versioned, declarative DAG of nodes that:

• Declares an input contract (parameters)
• References templates (versioned SQL generators) for each node
• References datasources by name (resolved per-environment)
• Declares where each node's output goes (tempdb staging, external datasource write-back, or caller return)
• Declares the dependency graph (which nodes wait for which)
• Declares execution settings (tempdb engine, etc.)
• Is fully portable across environments (no env-specific values embedded)

A Pipeline is authored by a human via the UI editor, or generated by an LLM via MCP, or imported from a JSON file. Once authored, it is immutable per version — editing creates a new version.

2. Design Principles

1. Environment-portable by construction. No env-specific values (hostnames, credentials, IDs, UUIDs) in the Pipeline JSON. Datasources are referenced by name and resolved per-environment. Node IDs and table names are stable, human-readable strings.
2. Declarative, not imperative. The Pipeline declares what runs and what depends on what, not how it runs. The executor decides parallelism, ordering, and lifecycle based on DAG topology.
3. Templates are first-class, separately versioned. SQL/FTL lives in template entities, not inline in the Pipeline. Pipelines reference templates by {id, version}.
4. Single shared Context at runtime. All nodes render their templates against the same Context map, initialized from the pipeline's input parameters. Future "calculators" extend the Context before/around node execution.
5. Node type drives executor behavior. DQL, DML, DDL declare what kind of SQL the template generates. The executor branches: stage ResultSet (DQL), record row count (DML), record success (DDL).
6. Output target is explicit per node — with one default. A DQL node's output can go to tempdb (staging), an external datasource (write-back), or the caller (result return). If the output block is omitted, the target is caller.
7. Stable, human-readable identifiers. Node IDs, table names, datasource references — all readable strings (fetch_orders, stg_orders, pg-prod). UUIDs are forbidden in the Pipeline JSON body.
8. Validate-on-write, universally. Nothing invalid ever reaches the database — pipelines, templates, datasources, every saved contract validates fully at create/update time. The executor never receives an invalid pipeline. (This principle is cross-cutting: Templates §7, Datasources §9 apply it to their own entities.)
9. The caller node is the result node — declared, not topology-derived. At most one node per pipeline resolves to output.target: "caller" (explicitly or by omission); that node's ResultSet is the pipeline's result. Zero caller nodes is legal: a pure write-back/ETL pipeline returns only execution stats. There is no terminal_node_id field and no topology-based auto-detection.

3. Top-Level Pipeline Schema

3.1 JSON structure

{
  "schema_version": 1,
  "id": "a1b2c3d4-...",
  "name": "acme/finance/monthly_revenue_report",
  "display_name": "Monthly Revenue Report",
  "description": "Joins PG orders with MySQL customers; aggregates by customer.",
  "version": 3,
  "owner": "user-uuid",
  "created_at": "2026-08-01T10:00:00Z",
  "updated_at": "2026-08-05T14:30:00Z",

  "settings": {
    "tempdb": {
      "engine": "H2",
      "config": {
        "max_memory_mb": 1024
      }
    }
  },

  "parameters": {
    "start_date": {
      "type": "DATE",
      "required": true,
      "description": "Inclusive start of the reporting period."
    },
    "end_date": {
      "type": "DATE",
      "required": true,
      "description": "Inclusive end of the reporting period."
    },
    "min_total": {
      "type": "BIGDECIMAL",
      "precision": 12,
      "scale": 2,
      "required": false,
      "default": "0.00",
      "description": "Filter customers with lifetime value below this."
    },
    "include_cancelled": {
      "type": "BOOLEAN",
      "required": false,
      "default": false,
      "description": "Whether to include cancelled orders."
    }
  },

  "nodes": [
    {
      "id": "fetch_orders",
      "description": "Pull orders in date range from PG.",
      "type": "DQL",
      "source": "pg-prod",
      "template": {"id": "acme/finance/fetch_orders.sql", "version": 2},
      "output": {"target": "tempdb", "table": "stg_orders"},
      "depends_on": []
    },
    {
      "id": "fetch_customers",
      "description": "Pull active customers from MySQL.",
      "type": "DQL",
      "source": "mysql-prod",
      "template": {"id": "acme/finance/fetch_customers.sql", "version": 1},
      "output": {"target": "tempdb", "table": "stg_customers"},
      "depends_on": []
    },
    {
      "id": "revenue_by_customer",
      "description": "Join orders + customers; aggregate revenue.",
      "type": "DQL",
      "source": "tempdb",
      "template": {"id": "acme/finance/join_revenue.sql", "version": 1},
      "output": {"target": "tempdb", "table": "int_revenue"},
      "depends_on": ["fetch_orders", "fetch_customers"]
    },
    {
      "id": "cache_to_warehouse",
      "description": "Materialize the revenue table to the reporting warehouse.",
      "type": "DQL",
      "source": "tempdb",
      "template": {"id": "acme/finance/select_revenue.sql", "version": 1},
      "output": {
        "target": "datasource",
        "datasource": "pg-warehouse",
        "table": "monthly_revenue_cache",
        "mode": "replace"
      },
      "depends_on": ["revenue_by_customer"]
    },
    {
      "id": "final_report",
      "description": "Final projection and filter for caller.",
      "type": "DQL",
      "source": "tempdb",
      "template": {"id": "acme/finance/final_report.sql", "version": 1},
      "output": {"target": "caller"},
      "depends_on": ["revenue_by_customer"]
    }
  ]
}

3.2 Field reference

Composed read shape vs portable body (2026-08-08). The JSON above is the shape a client READS. The portable pipeline body — what authors submit, what pipeline_versions.body_json stores, what exports carry — is the author-owned fields only: schema_version, name, display_name, description, settings, parameters, nodes, and the optional checks (§3.3, added 2026-09-14 — additive per §15.2). The server-assigned fields (id, version, owner, created_at, updated_at) live on the pipeline record and are composed into read responses; on input they are ignored entirely (absent from the inbound model — the strongest form of the write-protection rule; they cannot be smuggled in any casing). |

3.3 Release checks (checks[])

A pipeline body MAY carry checks: an optional list of read-only statements the server runs against a datasource the workspace can read, each paired with the value the author expects it to produce. Release of the version is gated on every check passing — the run outcomes and the refusal code are §13.17; the save-time rules are §12.12.

"checks": [
  {
    "id": "row_count",
    "name": "Orders row count matches the raw rollup.",
    "datasource": "pg-prod",
    "sql": "SELECT COUNT(*) FROM orders WHERE created_at >= :start_date",
    "expected": { "kind": "value", "value": 1023412, "tolerance": 0 }
  },
  {
    "id": "null_share",
    "name": "Null customer ids stay below one percent.",
    "datasource": "pg-warehouse",
    "sql": "SELECT COUNT(*) FILTER (WHERE customer_id IS NULL)::float / COUNT(*) FROM orders",
    "expected": { "kind": "range", "min": 0, "max": 0.01 }
  },
  {
    "id": "calendar_complete",
    "name": "No gap rows in the calendar spine.",
    "datasource": "pg-warehouse",
    "sql": "SELECT d FROM calendar d WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.day = d)",
    "expected": { "kind": "rows", "rows": 0 }
  }
]

Normative notes:

• checks is versioned with the body exactly like nodes: additive per §15.2, and body-hash neutral — an existing body (no checks key) deserializes to an empty list and serializes back byte-identically; an explicit "checks": [] canonicalizes to the same absent form.
• Every :name bind in sql must name a declared pipeline parameter (§6). The calculator Context (§7.2) is NOT available to a check — a check runs outside any execution. A run binds the declared parameters exactly as an execute does, and the release gate's run supplies no parameters: for any parameter a check binds, the declared DEFAULTS are what the gate proves — not every combination a caller can pass — while a check written against fixed literals proves exactly the baseline those literals name. Expectations are static — a fixed value, range or row count — so an expectation that is only true for one window is a baseline-specific statement, and the baseline belongs in the check's name.
• The author — agent or human — supplies the query and the expectation, never an observed value. observed exists only on the server's own run rows (pipeline_check_runs, metadata-db §4.20); the body's shape has no field that could carry one in.

3.3.1 The release's caller-output record (#328)

A release records the caller node's result columns — what the dashboard save-time input-contract check (§12.12's cousin on the dashboard side, dashboards.md §4.3) judges a SQL-sourced source against. This subsection is the source of truth for that record.

• What is recorded (D1). At release, the flip copies the result_schema_json of the most recent pipeline_executions row for THIS (pipeline_id, pipeline_version) with status = 'SUCCESS', a non-null schema, and started_at > COALESCE(v.updated_at, v.created_at) of the version row — an execution that ran THIS body (updated_at moves on every draft write and never at release). Child executions (parent_execution_id non-null) are never candidates. No probe and no execution runs at release: a release with no qualifying execution still releases and records NULL (caller_output = not_observed in the response); a release is never refused for a missing run.
• Where it lives (D2). Two nullable JSONB columns (metadata-db §4.5/§4.6, migration V47): pipeline_executions.result_schema_json — every execution's caller-output schema, written by the same recordResult write that fills result_row_count; and pipeline_versions.caller_output_json — the release's copy, written in the flip's own statement. The JSON is an array of {name, type, nullable} objects: name is the column name as the driver reported it, type a §7 LogicalType wire value (DECIMAL entries may also carry precision/scale), nullable boolean — an absent or JSON-null nullable reads as true (unknown admits). A record past the bounds (256 columns, 128 characters per name — ExecutionRepository's constants), or one with a blank column name, is recorded as NULL with a warn: never a truncated array, never a failed execution. The record is OUTSIDE body_hash (like implements): recording changes no hash and opens no draft.
• The one word (D3). The release response (caller_output, rest-api §5.10) and the pipeline.version.released audit row carry exactly one word — recorded (the D1 read found the version's latest run), declared (a SCRIPT caller: the pinned contract is the answer), none (no caller node), not_observed (a SQL caller, nothing observed — today's validator behaviour continues for that release). Ids, names, versions and this word are all an audit row says; never the column list.
• Precedence in the reader (D4). The dashboard-save reader (PipelineReleaseFacts.releaseOf) answers a release's outputColumns in this order: no caller node → EMPTY; a SCRIPT caller → its contract's table columns (the record never overrides a declared contract); any other caller node → the version's caller_output_json parsed to columns, null when there is none. The promotion payload carries the record beside version and body_hash, validated at the receiver (shape, wire types, the bounds) and stored on the imported release row (§9.2/§10.4) — a malformed record refuses the batch and nothing unchecked is stored.

4. Node Schema

4.1 JSON structure (DQL node, staging to tempdb)

{
  "id": "fetch_orders",
  "description": "Pull orders in date range from PG.",
  "type": "DQL",
  "source": "pg-prod",
  "template": {"id": "acme/finance/fetch_orders.sql", "version": 2},
  "output": {"target": "tempdb", "table": "stg_orders"},
  "depends_on": []
}

4.2 JSON structure (DQL caller node, returns to caller)

{
  "id": "final_report",
  "description": "Final projection.",
  "type": "DQL",
  "source": "tempdb",
  "template": {"id": "acme/finance/final_report.sql", "version": 1},
  "output": {"target": "caller"},
  "depends_on": ["revenue_by_customer"]
}

4.3 JSON structure (DQL node, write-back to external datasource)

{
  "id": "cache_to_warehouse",
  "description": "Materialize revenue to reporting warehouse.",
  "type": "DQL",
  "source": "tempdb",
  "template": {"id": "acme/finance/select_revenue.sql", "version": 1},
  "output": {
    "target": "datasource",
    "datasource": "pg-warehouse",
    "table": "monthly_revenue_cache",
    "mode": "replace"
  },
  "depends_on": ["revenue_by_customer"]
}

4.4 JSON structure (DML node, side-effect only)

{
  "id": "update_last_run",
  "description": "Update last_run timestamp in metadata.",
  "type": "DML",
  "source": "pg-meta",
  "template": {"id": "acme/finance/update_last_run.sql", "version": 1},
  "depends_on": ["final_report"]
}

Note: no output block. DML's side effect IS the output.

4.5 JSON structure (DDL node, schema setup)

{
  "id": "create_index",
  "description": "Create index on staging table for join performance.",
  "type": "DDL",
  "source": "tempdb",
  "template": {"id": "acme/finance/create_idx_revenue.sql", "version": 1},
  "depends_on": ["revenue_by_customer"]
}

4.6 Field reference

4.7 output block reference

The output block declares where the node's ResultSet goes. Optional for DQL nodes — omitted means {"target": "caller"}; forbidden for DML/DDL. On a PIPELINE node the block declares where the child execution's caller result lands, and is permitted only when the pinned child has a caller node (§12.9 pipeline_output_on_sideeffect_child).

4.8 source field rules

• source is a string. Either:
  • A registered datasource name in this environment (validated against the datasource registry at write time), OR
  • The reserved literal "tempdb", meaning "run this SQL against the in-memory staging database for this execution."
• The reserved "tempdb" literal cannot also be a registered datasource name (validation rejects registering a datasource with that name).

4.9 JSON structure (PIPELINE node)

{
  "id": "revenue",
  "description": "Monthly revenue component.",
  "type": "PIPELINE",
  "pipeline": {"name": "acme/finance/monthly_revenue", "version": 4},
  "parameters": {"start_date": "${start_date}", "region": "EU"},
  "output": {"target": "tempdb", "table": "stg_revenue"},
  "depends_on": []
}

A PIPELINE node executes another pipeline — the version pinned by pipeline — as a real, separate child execution (§8.5) and consumes its result. Composition is by invocation, not inlining: the child keeps its own execution record, tempdb, stats, and SSE stream.

Field rules:

• pipeline — required: {name, version}. name per §3.2's path grammar (a child may live under any folder); version a positive integer pinning an existing live pipeline version (mutable DRAFT during authoring, immutable RELEASED for release/import). Self-reference (name = the containing pipeline's own name) is invalid. There is no "latest".
• source and template — forbidden (mirrors "output forbidden on DML/DDL"): the node runs a pipeline, not SQL.
• parameters — optional map filling the child's inputs: each key names a child declared parameter, a child CALCULATOR context_key or a child value-mode SCRIPT context_key (078 A5-composition — supplying the key skips the child's node exactly as a direct execute-time supply does, §4.10; child calculator and SCRIPT keys are optional, never required, so pipeline_parameter_unmapped still reads declared parameters only). Each value is either a typed literal obeying the target's §6.3 wire encoding, or the string form "${ref}" resolving against the PARENT's Context tiers: a parent declared parameter, a parent CALCULATOR context_key (typed by its kind's output), a scalar parent SCRIPT context_key (ANY at save), or an org/platform key (org always STRING, platform per §0.2's canonical types) — at the identical type. An ANY-output key on either side skips the type check: the value is typed only by the run. A child SCRIPT key is ANY at save time; a referenced scalar is encoded at run time and checked against the pinned script contract by the child's node. Object/list references do not cross this boundary. No expressions, no concatenation — a value is a literal or a reference, nothing in between (v1). No auto-passthrough: a parent and child calculator key spelled the same are NOT implicitly mapped — the mapping is always an explicit entry here, or the child's node computes its own value.
• output — standard §4.7 block, permitted only when the pinned child has a caller node. Zero-caller child ⇒ output must be absent; the node is side-effect-only and downstream depends_on gives ordering.
• depends_on — unchanged.

A pipeline whose entity is DISCARDED (every version discarded) still resolves existing pinned references — the pinned version keeps resolving — but blocks NEW references at save time (pipeline_reference_deleted, §12.9). This mirrors template deletion exactly. Draft authoring may pin a DRAFT or RELEASED child version (#459, owner requirement superseding 101/D58). Release and import require a RELEASED child (pipeline_reference_not_released); missing or DISCARDED child references remain refused.

4.10 JSON structure (CALCULATOR node)

{
  "id": "fiscal_q",
  "description": "The fiscal quarter this run reports on.",
  "type": "CALCULATOR",
  "kind": "fiscal_quarter",
  "inputs": {"date": "$current_date", "fiscal_start": "$org_fiscal_start_date"},
  "context_key": "run_fiscal_quarter",
  "depends_on": []
}

A CALCULATOR node evaluates one pure catalog function (Calculators) and writes typed values into the execution Context. It runs no SQL, touches no database, and produces no table. Downstream nodes read a value the way they read any Context key — :run_fiscal_quarter in a template, "$run_fiscal_quarter" in another calculator's inputs.

The node writes one value or a named set, decided by the kind, never by the author. A single-output kind (most of the catalog) writes its one value under context_key, as above. A multi-output kind declares a set of named outputs (period_bounds declares start and end), and the node maps EVERY output to a Context key through context_keys:

{
  "id": "window",
  "description": "The quarter this run reports on.",
  "type": "CALCULATOR",
  "kind": "period_bounds",
  "inputs": {"date": "$current_date", "unit": "quarter"},
  "context_keys": {"start": "window_start", "end": "window_end"},
  "depends_on": []
}

Field rules:

• kind — required: a name in the registry. The catalog is additive and a kind never changes meaning, because a kind is written into bodies that are versioned, exported and promoted. The catalog entry says whether the kind is single- or multi-output.
• inputs — required (an empty object is legal for a kind with only optional inputs). "$name" is a reference to a Context key; anything else is a literal typed against the kind's declared input type, which is what makes "fiscal_start": "09-15" a per-pipeline override with no config edit. An input the kind declares optional may be omitted, and the kind then applies its documented default.
• context_key — required on a single-output kind, per §6.1. It may shadow an org or platform key; it may never shadow a declared parameter (§12.10 calculator_output_collision), and no two nodes may write the same key.
• context_keys — required on a multi-output kind: an object mapping every declared output name to its Context key ({"start": "window_start", "end": "window_end"}). No partial mapping — a caller who needs one value still maps both, so no reader can bind a key the node never writes. Each mapped key obeys every rule context_key does: §6.1's name shape, no shadowing a declared parameter, one writer per key.
• context_key XOR context_keys — never both, never neither, and the field must fit the kind's shape (context_key on a multi-output kind or context_keys on a single-output one is refused, §12.10 calculator_output_shape_mismatch).
• source, template, output — forbidden, for the same reason source/template are forbidden on a PIPELINE node: this node is not the kind of thing they describe.
• depends_on — unchanged, and load-bearing in a way it is not elsewhere: sequencing is topology. A reference to another node's key, and a SQL node binding :that_key, are valid only from a node that depends on the producer, directly or transitively (§12.10 calculator_input_unordered). Binding only ONE of a multi node's keys still requires the edge. Array order means nothing.

At run time the node evaluates at its DAG position, writes its value (once, every key on a multi-output kind), and reports through SSE and history like any other node — rows_out: 0, plus context_key and context_value on a single node's stats (context_values, every key, on a multi node's) so the run detail page and executions_get show what it produced. A failure is the standard node failure record with pipeline.node.calculator_failed (§13.4).

Every key a calculator node writes is also an implicit optional execute input (078, owner ruling 2026-09-05; extended to the named set 121). A caller may supply it in the execute request's parameters object, typed by the kind's output — an ANY-output kind (coalesce, if_null, map) accepts any JSON scalar. Supplied, the node is skipped: it does not evaluate, the supplied value is what downstream nodes bind, and the node's stats carry provided_by: "caller" so a run record shows where the value came from. Unsupplied (an explicit JSON null reads as unsupplied), the node runs and computes the value exactly as before. A supplied value that fails coercion is refused with pipeline.execution.invalid_parameter_type (§13.3), exactly like a declared parameter — to the caller there is no second kind of execute input. For a multi-output node the override is all-or-nothing: every key supplied and the node is skipped, none and it computes; a proper subset is refused before any node runs with pipeline.execution.calculator_keys_partial (§13.3).

4.11 settings.timeout_seconds / settings.query_timeout_seconds — a node's own deadline and statement budget

{
  "id": "scan_trips",
  "type": "DQL",
  "source": "lake",
  "template": { "id": "trips/scan", "version": 3 },
  "output": { "target": "tempdb", "table": "trips" },
  "depends_on": [],
  "settings": { "timeout_seconds": 600, "query_timeout_seconds": 300 }
}

Three budgets, one precedence (the same table appears in configuration.md §3.2 and dag-executor.md §5.3):

Read it downward: the execution deadline is the outermost, the statement timeout the innermost, and the node deadline is what closes the gap between them. The statement bound is the driver's, and drivers honour it unevenly — a node whose driver ignores it is stopped by the node deadline anyway, which is exactly why the middle row exists. A pipeline-level settings.execution.timeout_seconds is still §5.4 future work: the outermost bound is the operator's setting, not the pipeline's.

Statement-timeout precedence in full (156, #2). The statement row above compresses five tiers into one cell; read individually: a node may override the pipeline's statement timeout, which may override the datasource's, which may override the operator's per-dialect default, which falls back to the flat application default. settings.query_timeout_seconds — at node level or pipeline level (§5.3) — is bounded by the SAME operator ceiling as node.settings.timeout_seconds: datapipelines.executor.node-query-timeout-max-seconds (default 900), refused rather than clamped at save (pipeline.validation.node_query_timeout_invalid / pipeline.validation.pipeline_query_timeout_invalid, §12.8). A node's own query_timeout_seconds must also not exceed that SAME node's effective wall-clock deadline (the Node row above) — a statement budget longer than the node's own lifecycle could ever reach is refused at save, naming both numbers. pipeline.node.query_timeout's failure detail (§13.4) names which tier resolved the effective value (source: node | pipeline | datasource | dialect | application), so an author can tell whether their own override took effect or an operator default did.

A node that exceeds its deadline fails with pipeline.node.timeout (§13.4, HTTP 504), whose details carry timeout_seconds, elapsed_ms and the phase the budget went in.

A timeout is a signal, not a wall to route around. The first response to a node that will not fit its budget is to make the work smaller — pre-aggregate, push the filter down, prune on the partition column. Slicing one scan into four quarterly nodes to dodge the deadline produces a pipeline that is slower, four times as likely to fail, and lies about what it does. When the scan is legitimately long, raise THIS node's timeout_seconds and say why.

A PIPELINE node that declares no timeout_seconds is not bounded by the node deadline: its work is a child execution, already bounded by execution-timeout-seconds one level down. One that declares it, is.

4.12 JSON structure (SCRIPT node)

{
  "id": "shape_orders",
  "description": "Order lines at reporting grain; cents to currency; refunds preserved.",
  "type": "SCRIPT",
  "template": { "id": "acme/shape/order_lines.jsonata", "version": 3 },
  "inputs": { "orders": "stg_orders", "tz": "$org_timezone", "min_total": "$min_total" },
  "output": { "target": "tempdb", "table": "order_lines", "rejects": "order_lines_rejected" },
  "strict": false,
  "depends_on": ["stage_orders"]
}

A SCRIPT node evaluates the pinned script template as a pure function over staged data and the Context (the transform-nodes design record §3). The language is the pinned template's type (jsonata or javascript, when its isolate is installed); the mode (row, table, value) and the input/output shapes are the pinned version's contract — never repeated on the node, which conforms to it at save time (§12.13). The function has no handle to tempdb, the network, the filesystem, the clock or a random source: iteration is the executor's job (row mode reads the single table input in result-batch-size batches), $now()/$millis() return the execution's current_timestamp, and $random(), $shuffle() and Math.random() are refused (pipeline.script.evaluation_failed). The output is a function of the template version and its inputs — every run is reproducible.

Every key a value-mode SCRIPT writes is an implicit optional execute input, exactly like a calculator's (§4.10): supplied by the caller, the node is skipped and its stats carry provided_by: "caller"; the supplied value is checked against the contract's declared output at the node (an object output accepts any JSON object under the max-value-bytes cap).

Object keys have one reader (R4). A key whose writer declares output.kind: object may be bound only by a SCRIPT inputs entry; a SQL node's :key, a calculator input or a PIPELINE node's parameters reference to it is refused at pipeline save with pipeline.validation.script_object_key_bound, naming the key, its writer and the offending node. A caller may supply it at execute time as any JSON object under the cap; it persists as jsonb in the execution's Context snapshot.

5. Settings

5.1 settings.tempdb — staging engine configuration

"settings": {
  "tempdb": {
    "engine": "H2",
    "config": {
      "max_memory_mb": 1024
    }
  }
}

If settings.tempdb is omitted entirely, defaults to H2 with default config.

5.2 Why pipeline-level (not global)

• Different pipelines have different needs: a small lookup-heavy pipeline fits in 256 MB; a large aggregation pipeline needs 4 GB.
• Pipeline authors declare the resource need upfront; operators budget accordingly.
• Settings travel with the pipeline across environments — same engine choice in dev and prod (the actual pg-prod connection differs, but the staging engine choice doesn't).

5.3 settings.query_timeout_seconds — the pipeline-wide SQL statement timeout

"settings": {
  "query_timeout_seconds": 300
}

Pipeline-level, not global, for the same reason §5.2 gives settings.tempdb: different pipelines scan different volumes against different engines, authors declare the resource need once for the whole pipeline instead of repeating it on every node, and the setting travels with the pipeline across environments. It does not raise or lower any node's own wall-clock deadline (node.settings.timeout_seconds) — the two settings are independent, and §12.8's invariant is checked per node against whichever statement timeout applies to it.

5.4 Future settings (out of scope for v1)

• settings.execution.parallelism — per-pipeline concurrency limit override (different from the global default).
• settings.execution.timeout_seconds — per-pipeline overall timeout.
• settings.templates.cache_size — override template cache size for this pipeline's executions.
• settings.output.default_format — default wire format for /execute (JSON / Arrow / CSV).

6. Parameters (Input Map Declaration)

6.1 Schema

parameters is an object whose keys are parameter names ([a-z_][a-z0-9_]*, length 1–63 — no leading digit, since a name Freemarker cannot reference is undeclarable; the 63-char cap matches the identifier caps elsewhere and stops an unbounded key becoming an unbounded error string) and whose values are parameter descriptors.

"parameters": {
  "param_name": {
    "type": "INTEGER" | "BIGINTEGER" | "DECIMAL" | "BIGDECIMAL" |
            "BOOLEAN" | "STRING" | "BINARY" | "DATE" | "TIME" | "TIMESTAMP",
    "required": true | false,
    "default": <value>,           // optional; only honored if required = false
    "precision": <int>,           // required for DECIMAL, BIGDECIMAL
    "scale": <int>,               // required for BIGDECIMAL; required for DECIMAL with exact-numeric semantics
    "description": "...",         // optional but recommended
    "constraints": {              // optional (#194) — value rules; a value that breaks one is REFUSED, never adjusted
      "min": <value>, "max": <value>,        // ordered types; wire-encoded in the parameter's own type; inclusive
      "min_length": <int>, "max_length": <int>, // STRING (characters) and BINARY (decoded bytes); non-negative
      "pattern": "<regex>"                     // STRING; anchored (the whole value); at most 256 characters
    },
    "cardinality": "SINGLE"       // optional (#194); SINGLE is the only value a pipeline accepts today (§6.2)
  }
}

constraints and cardinality are the parameter engine's declaration model (parameter-engine record §3.5, P28), shared so that a pipeline and a parameter set judge a value by the same ParameterValueValidator (typesystem). Both are omitted from a body that does not declare them, so bodies saved before 2026-09-26 serialise — and hash — unchanged.

6.2 Rules

• type is one of the canonical types from Type System §3, excluding NULL.
• required: true and default present is invalid (validation error: pipeline.validation.conflicting_required_default).
• precision / scale rules match the Type System §7.3 column descriptor rules.
• default must be valid JSON matching the wire encoding for the type:
  • INTEGER, DECIMAL → JSON number
  • BIGINTEGER, BIGDECIMAL, STRING, BINARY, DATE, TIME, TIMESTAMP → JSON string
  • BOOLEAN → JSON boolean
• Parameter values supplied at execution time must satisfy the schema. Type mismatch → pipeline.execution.invalid_parameter_type.
• One validator, one null policy (#194). Every value — supplied, or a default being applied — is judged by the shared ParameterValueValidator: the §6.3 coercion, then the declared precision and scale, then constraints. A value that breaks a rule is pipeline.execution.parameter_constraint_violation (§13.3) with details.reason naming it (min, max, min_length, max_length, pattern, pattern_budget, scale, precision). A STORED declaration save would refuse today — only a body saved before #194, when Parameter ignored unknown keys, can hold one — is never trusted where it is used: judging a value or a default against it is pipeline.execution.parameter_declaration_invalid (409, §13.3, #268) with details.reasons naming the save-time codes, never a 500; a stored cardinality: MULTI is refused the same way rather than bound as a list. That refusal is the SERVER's defect, so it outranks the caller's own (#298): when one binding carries both — a caller's bad value on one parameter and a stored declaration refused on another — the response is the 409 with both failures in details.failures, in the order found, whichever came first. Each judged declaration is compiled once per bind. A JSON null or an absent key is unsupplied — neither judged nor refused — and resolves as it always has: the default, then parameter_required, then null for an optional parameter. A published endpoint reaches the same binder, and a URL has no null: an absent query key is its only unsupplied form (?x= is an empty-string value).
• Precision and scale are enforced on values (since 2026-09-26, #194 — a deliberate break, REST API change log v2.37): a DECIMAL(12,2) / scale-2 BIGDECIMAL refuses 12.345 (reason: scale, nothing is rounded) and a DECIMAL(6,2) refuses 99999.5 (reason: precision — at most precision − scale integer digits). Trailing zeros carry no digits (12.500 fits scale 2 and binds as sent); a DECIMAL with no scale is approximate and has no places to check; a BIGDECIMAL with no precision is unbounded in its integer part. Before, both were descriptive only.
• constraints (#194) — min/max apply to INTEGER, BIGINTEGER, DECIMAL, BIGDECIMAL, DATE, TIME, TIMESTAMP, wire-encoded in the parameter's own type (a BIGDECIMAL bound is a JSON string) with min ≤ max; min_length/max_length apply to STRING (characters, counted as code points) and BINARY (decoded bytes), non-negative with min_length ≤ max_length — an undeclared max_length leaves the value unbounded, as before; pattern applies to STRING: a java.util.regex pattern matched against the WHOLE value, at most 256 characters, refused at save if it uses a backreference, lookahead/lookbehind, a possessive quantifier, an atomic group or the COMMENTS flag, and matched over a read-counting input with a budget of 100,000 reads (past it: reason: pattern_budget). A key outside the catalogue, a key on a type it does not apply to, a malformed bound or length, and an unsafe pattern are refused at save (§12.7). The default must satisfy the parameter's own rules (default_invalid).
• cardinality (#194) — SINGLE (the default when omitted) or MULTI. MULTI is refused (pipeline.validation.cardinality_unsupported) until the dashboard round adopts list binding: the field is the contract, shared with the parameter engine; the semantics land later. Any other value is refused with the same code.

6.3 Wire encoding of input parameter values

Input parameter values are submitted by clients (via REST API or MCP tool) as JSON. Their wire encoding follows the same rules as Type System §3.1 — BIGINTEGER and BIGDECIMAL parameters are sent as strings; INTEGER and DECIMAL (precision ≤ 15) as numbers; etc.

This is the symmetric contract: data flows in and out of the pipeline using the same type rules.

Coercion is strict — wrong wire encoding is rejected, never silently converted:

• A JSON number supplied where BIGDECIMAL/BIGINTEGER (string-on-wire) is declared → pipeline.execution.invalid_parameter_type. (Accepting it would silently lose precision for values beyond IEEE 754 safe range.)
• A JSON string supplied where INTEGER/DECIMAL/BOOLEAN (number/boolean-on-wire) is declared → pipeline.execution.invalid_parameter_type.
• TIMESTAMP parameter values MUST carry an explicit offset or Z; a zone-less timestamp string is rejected — the server never guesses the client's timezone.
• DATE/TIME values must be exact ISO 8601 (YYYY-MM-DD, HH:MM:SS[.ffffff]). The fractional part, when present, is 1–6 digits — sub-microsecond input is rejected, not silently truncated (2026-08-08: strictness applies on ingress exactly as on egress; leniency here would make §3.5's exact egress a silent transformation).
• BINARY parameter values must be PADDED standard base64 (RFC 4648 §4, length ≡ 0 mod 4) — the same alphabet and padding §3.5 mandates on egress; unpadded input is rejected (2026-08-08).
• Precision and scale bind values: a DECIMAL/BIGDECIMAL value with more decimal places than the declared scale, or more integer digits than precision − scale, is pipeline.execution.parameter_constraint_violation (details.reason: scale / precision) — never rounded. Until 2026-09-26 a declared precision/scale was not checked against supplied values (12.345 bound unchanged into a DECIMAL(12,2) parameter); enforcing it is the second deliberate break of #194 (REST API change log v2.37).
• Nothing is trimmed: a value with surrounding whitespace — " 12.50 " for BIGDECIMAL, " 12 " for BIGINTEGER — is pipeline.execution.invalid_parameter_type. Until 2026-09-26 the two BIG types were trimmed before parsing (and a published endpoint trimmed its INTEGER/DECIMAL/BOOLEAN query values too); that tolerance was retired as a deliberate break (REST API change log v2.36, #194). The coercion lives in typesystem since then, shared with the parameter engine (Type System §3.1).
• A textual BIGINTEGER/BIGDECIMAL value longer than 1024 digits is refused UNPARSED (too_many_digits under invalid_parameter_type) — on JDK 21 the parse of an n-digit string is O(n²), so a megabyte of digits is seconds of CPU from one value (#278, 2026-09-28). The length check is O(1) and runs before BigInteger/BigDecimal ever see the text; every caller that coerces through the shared paths — the execute API, pipelines_execute, a parameter set's textual default_value, an expression literal — inherits the same cap.

7. Execution Context (Runtime Construct)

The Context is a runtime in-memory map — never serialized in the Pipeline JSON. It is constructed per execution.

7.1 Lifecycle

1. Pipeline execution starts. The executor seeds the Context with the org tier — the deployment's datapipelines.org.* values (Configuration §3.21) — and then the platform tier: current_date, current_timestamp, execution_id.
2. Executor reads pipeline input parameters (from REST/MCP call).
3. Executor validates each parameter against the pipeline's parameters schema. Defaults applied for missing optional params.
4. Executor overlays the resolved parameters onto the Context: Map<String, Any?> where keys are context keys and values are typed Kotlin objects (Date, BigDecimal, Boolean, String, etc.). A declared parameter that spells an org or platform key the same way is the override.
5. For each node, in topological order: a. The template engine renders the node's template against the current Context, and the rendered SQL's :key binds resolve against it too. b. A CALCULATOR node (§4.10) evaluates its kind once and writes every key it declares — its context_key, or each mapped context_keys value — into the Context; every node that depends_on it, directly or transitively, sees them. c. The rendered SQL executes against the node's source. d. Behavior depends on type and output.target (see §8).
6. The caller node's ResultSet (the node resolving to output.target: "caller", if any) is the pipeline's result. Pipelines with no caller node return execution stats only.
7. The fully resolved Context — every tier, calculator outputs included — is persisted with the execution (DAG Executor §8).

7.2 What's in the Context — one namespace, five tiers

Every value a node can bind is a typed Context key matching §6.1's [a-z_][a-z0-9_]*. A node binds :key without knowing which tier supplied it; the tier only decides who wins when two of them spell a key the same way. Lowest precedence first:

A calculator output may shadow an org or platform key; it may never shadow a declared parameter, and one is refused at save time with pipeline.validation.calculator_output_collision (§12.10) — the same rule binds a value-mode SCRIPT's key (§12.13). An object-valued key (a SCRIPT whose contract declares output.kind: object) has ONE reader: another SCRIPT's inputs — a SQL bind, a calculator input or a composition parameter naming it is refused at save with pipeline.validation.script_object_key_bound (R4, §4.12). A calculator that RUNS writes over everything below tier 5 — including a caller-supplied value for a different key — but a key the caller supplied skips its node (§4.10), so the two never contest the same key in one run. Org and platform keys are deployment constants, so the save-time dry render knows them: a template binding :org_currency_symbol validates without the pipeline declaring anything. None of them is a secret.

7.3 What's NOT in the Context

• Upstream node outputs (those are tempdb tables OR external-datasource tables, referenced by name in SQL).
• Connection credentials or env-specific values.
• Execution metadata beyond the platform tier's three keys — timings, node stats and the result's location travel on the execution record, not in a namespace templates render against.

7.4 Template variable resolution

Templates use Freemarker syntax to reference Context keys. A key declared in parameters is a value and is referenced as a bind parameter — :name — which the executor binds on a prepared statement, so a caller-supplied STRING is never parsed as SQL (Templates §4.5). ${} interpolation is for structure (table names, dynamic fragments) and is refused for declared parameter names at save time (template.validation.parameter_interpolated, §12.6):

SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date BETWEEN :start_date AND :end_date
  AND total_amount >= :min_total

The template engine enforces that all referenced variables exist in the Context — referencing an undefined variable is a render failure (pipeline.node.template_render_failed).

This is also enforced at save time: pipeline validation dry-renders every referenced template against the pipeline's declared parameters (using defaults where present, type-appropriate sample values otherwise). A template variable with no corresponding pipeline parameter fails validation with pipeline.validation.template_parameter_undeclared — the error never waits for execution. Pipeline parameters is the single declaration point for template variables; templates do not declare their own parameter schemas (Templates spec).

8. Node Execution Behavior (per type and output.target)

Consistency: a write-back node commits on its own connection when it finishes, and nothing rolls it back if a later node fails — each node is atomic on its own database, the pipeline as a whole is not. The full model, the seams between databases, and how to design a write-back around it are DAG Executor §16.

8.1 DQL nodes

A DQL node runs a SELECT query and produces a ResultSet. Behavior depends on output.target:

8.2 DML nodes

A DML node runs INSERT, UPDATE, DELETE, or MERGE against its source. The executor:

1. Renders the template, executes the SQL.
2. Captures the affected row count.
3. Records row count in node_stats. No staging, no output block.

DML nodes are side-effect sinks. They typically appear as the last node(s) in a pipeline that needs to write back results — but write-back is more naturally modeled as a DQL node with output.target: "datasource" (let the framework handle the INSERT). DML is for cases where the SQL is too complex for the framework's auto-streaming (conditional logic, MERGE with custom conflict handling, etc.).

8.3 DDL nodes

A DDL node runs CREATE, ALTER, DROP, or TRUNCATE against its source. The executor:

1. Renders the template, executes the SQL.
2. Records success/failure.
3. No staging, no output block.

Use cases: create indexes on tempdb tables for join performance, create target tables in external datasources before a write-back DQL node, prepare schema state.

8.4 Type-based execution dispatch

when (node.type) {
    DQL -> executeDql(node, context, staging)
    DML -> executeDml(node, context)
    DDL -> executeDdl(node, context)
    PIPELINE -> executeSubPipeline(node, context)   // §8.5 — before render/source resolution
}

fun executeDql(node, context, staging) {
    val sql = render(node.template, context)
    val source = resolveSource(node.source, staging)
    val rs = source.executeQuery(sql)

    // omitted output block resolves to Caller at deserialization time
    when (val output = node.output ?: NodeOutput.Caller) {
        is NodeOutput.Tempdb -> staging.stage(rs, output.table)
        is NodeOutput.Caller -> materializeCallerResult(rs)   // → Redis result store, REST API §7
        is NodeOutput.Datasource -> streamToExternal(rs, output.datasource, output.table, output.mode)
    }
}

8.5 PIPELINE nodes

A PIPELINE node executes the pipeline pinned by its pipeline reference as a child execution: a real, separate execution with its own execution record, own tempdb, own stats, and own SSE stream, started through the internal execution service (never HTTP). The child runs under the parent's principal; authorization is checked on the parent only. The {name, version} reference resolves within the active workspace — cross-workspace references do not exist in v1. Dispatch happens before render/source resolution — a PIPELINE node carries neither a template nor a source.

• Parameters. The node's parameters map becomes the child's execution parameters: literals pass through as supplied; each "${ref}" reference resolves against the parent's live Context tiers, including scalar SCRIPT outputs. A child value-mode SCRIPT key takes a scalar reference as ANY, with output typing enforced by the child's pinned script contract.
• Result. The child's caller-node ResultSet streams directly to the parent executor (direct delivery — nothing is materialized to the result store, and the result is not re-fetchable afterwards; re-running is the recovery path) and lands per the node's output block, exactly like a DQL node's ResultSet (§8.1). A zero-caller child produces no stream: the parent waits for child completion, and success/failure is the node's outcome.
• Failure. A failed child fails the PIPELINE node fail-fast with pipeline.node.child_execution_failed; the detail carries the child's error code and execution id, so the debugging trail leads to a real execution record.
• Cancellation. Cancelling an ancestor cancels the whole family: the cancellation flag is honored for every execution sharing the family's root_execution_id (Metadata DB §4.6). A descendant stopped because an ancestor was cancelled — or because an ancestor's execution-timeout-seconds expired — ends ABORTED, never FAILED: it did not fail, and its own deadline did not expire. Only the execution whose own deadline fired reports pipeline.execution.timeout.
• Guards. Composition depth is bounded by datapipelines.pipelines.max-composition-depth (default 5), checked statically at save time (composition_too_deep, §12.9 — the current pinned bodies define the reference tree) and again at run time (pipeline.node.composition_depth_exceeded — a backstop; reaching it means save-time validation was bypassed). Both count the same unit: pipelines. A pipeline with no PIPELINE nodes is depth 1 and the bound is inclusive, so the default admits a chain of 5 pipelines (4 parent→child hops) and refuses the 6th — identically at save time and at run time. Mutable draft references can form cycles; the static walk and runtime depth bound terminate them. Release revalidates the current graph. Child executions do not take per-user concurrency slots — only root executions do; a waiting parent holding a slot while its children queue would deadlock.

9. The Caller Node (Result Node)

There is no terminal_node_id field and no topology-based auto-detection. The result node is simply the node that resolves to output.target: "caller" — explicitly, or by omitting its output block (§4.7).

9.1 Resolution

1. For each DQL or SCRIPT node, resolve its effective target: the declared output.target, or caller if output is omitted (DQL only — a SCRIPT's output is always explicit, §4.12).
2. At most one node may resolve to caller — this is the caller node, and its ResultSet is the pipeline's result.
3. Zero caller nodes is legal: the pipeline is a pure write-back/ETL pipeline. Execution returns stats only and emits no data_ready event.

DAG position is irrelevant: the caller node may be a sink, or sit mid-DAG (though mid-DAG caller nodes are unusual — a node whose data downstream nodes consume must stage to tempdb, so a caller node's data is consumed by nobody else).

9.2 Validation rules

Any combination of tempdb / datasource / caller targets across DQL nodes is otherwise legal — a DQL sink may write to a datasource, stage to tempdb for a later DML node, or return to the caller.

9.3 Multiple-sink example (legitimate)

A pipeline that returns data to the caller AND writes a side effect:

fetch_orders ─┐
              ├─→ revenue_by_customer ─┬─→ cache_to_warehouse  (DQL, output: datasource pg-warehouse)
fetch_customers ┘                       └─→ final_report        (DQL, output omitted → caller)

Two sinks: cache_to_warehouse (write-back) and final_report (caller return). One node resolves to caller → valid. ✓

9.4 Zero-caller example (pure ETL)

fetch_orders ─→ script ─→ write_to_warehouse  (DQL, output: datasource pg-warehouse)

No node resolves to caller. The pipeline returns execution stats only; no data_ready event is emitted. ✓

10. Output Table Naming Rules

10.1 Rules

• Format: [a-z0-9_]+, length 1–63 (H2 identifier limits).
• Uniqueness is per namespace: all tempdb output.table values must be unique among themselves (they share one staging database), and datasource output.table values must be unique per target datasource. A tempdb table and a write-back table may share a name; two write-backs to different datasources may share a name.
• Must not be tempdb or any name starting and ending with __ (reserved namespace).
• Recommended convention (not enforced, but LLM prompts and UI defaults should follow):
  • stg_* for tables staging raw source data
  • int_* for intermediate scripted tables
  • Cache tables written to external datasources: name them per the target's convention (often monthly_revenue_cache, daily_orders_materialized, etc.)
• Stable across versions and environments. Renaming is a breaking change requiring downstream template SQL updates — bump pipeline version.

10.2 Why stable names

Downstream SQL in templates references these names directly: SELECT * FROM stg_orders JOIN stg_customers ON .... The names are part of the pipeline's vocabulary. They must be:

1. Predictable for template authors (humans and LLMs).
2. Stable so a pipeline promoted from dev to prod doesn't break.
3. Traceable in logs — pipeline.node.fetch_orders.stg_orders is readable, pipeline.node.fetch_orders.tbl_a1b2c3d4 is not.

11. Environment Portability Rules

11.1 What's portable (in the Pipeline JSON)

• Pipeline id, name, display_name, description, version.
• All node IDs, descriptions, types.
• All output.table names.
• All template references ({id, version}).
• All source names (not connection details).
• All parameters declarations.
• All settings.

11.2 What's NOT portable (resolved per-environment)

• Datasource connections — the name pg-prod resolves to different JDBC URLs in dev vs. prod. The mapping is environment-specific config, stored separately (see Datasources spec).
• Template bodies — portable, but live in their own registry with their own version history.
• Pipeline UUID — stable across envs if exported and re-imported.

11.3 Promotion workflow

1. Export pipeline from dev: GET /pipelines/{id}/versions/{version} returns the JSON (see §14).
2. Optionally export referenced templates (separate call or bundled export).
3. Import to prod: POST /pipelines/import with the JSON. The import:
   • Checks if pipeline id already exists in prod.
   • If yes, increments version and stores the new version.
   • If no, creates new with version: 1.
   • Validates all source names (excluding tempdb) against the prod datasource registry — fails with pipeline.import.missing_datasource if any name is unresolvable.
   • Validates all output.datasource references (for write-back nodes) against the prod datasource registry.
   • Validates all template references against the prod template registry — fails with pipeline.import.missing_template if any referenced template version is missing.

11.4 Forbidden in Pipeline JSON

Hard validation errors at write-time. Validation code: pipeline.validation.forbidden_env_specific_value (listed in §12.1).

Scanned fields: every string value in nodes[].source, nodes[].output.datasource, nodes[].output.table, nodes[].id, and all keys/values under settings. (Template bodies are validated separately by the template registry; parameter values are runtime data and are not scanned.)

Detection heuristics applied to each scanned string:

• JDBC URL pattern: ^jdbc: prefix.
• Hostname pattern: contains a dot-separated name with a known TLD-like tail or matches [a-z0-9-]+\.[a-z0-9-.]+:[0-9]+ (host:port).
• IP address pattern: IPv4 dotted-quad or bracketed IPv6.
• UUID pattern: [0-9a-f]{8}-[0-9a-f]{4}-... (the pipeline-level id and owner fields are the only allowed UUIDs).
• Credential-shaped: password=, pwd=, token=, secret= key fragments; dpk_ API-key prefix.
• Absolute path: leading / or drive-letter pattern.
• Environment literal: exact-match dev, staging, prod, production as a whole value.

These heuristics are deliberately conservative (whole-value or prefix matches) — stg_orders containing "stg" is fine; a source of pg-prod is fine (it is a datasource name; the check applies to values that are not references into the datasource registry).

12. Validation Rules

All checks run at pipeline create/update time. A pipeline that fails any check is rejected with HTTP 400 and an error response.

12.1 Structural validations

12.2 DAG validations

Crash-safety (normative, 2026-08-08). Save-time validation runs on attacker-influenced JSON from an author-scoped principal and MUST NOT be crashable by input. Two specific requirements: (1) graph traversal (cycle detection, reachability) is iterative, never recursive in graph depth — a deep depends_on chain must not exhaust the JVM stack; (2) any value scanned by the §11.4 portability heuristics is length-bounded before regex evaluation (a scanned value longer than 512 chars cannot be a hostname/URL/UUID/credential and is passed through un-flagged), and the heuristic patterns are bounded in repetition depth. A StackOverflowError reaching the request thread from a 2 KB payload is a defect, not a medium.

12.3 Caller-node validations (see §9.2)

12.4 Node-type validations

12.5 Datasource validations

12.6 Template validations

042 B2, not a pipeline.validation.* row: beside the dry render, save also scans each resolved template's parse tree, and a declared parameter name found inside a ${} interpolation is refused with template.validation.parameter_interpolated (§13.9, HTTP 400) — declared parameters are values and bind as :name (Templates §4.5); the message names both forms. The scan is AST-based (Templates §4.2 reasoning), honours macro-parameter and loop-variable shadowing, and no spelling hides a live interpolation from it (pinned against Freemarker 2.3.34). The declared set is the pipeline's parameters block including every calculator output key (078 A1): every key a CALCULATOR node writes joins the set — a single node's context_key typed by its kind's output, each of a multi-output node's mapped keys typed by its own output (121) — and is refused in one more position a plain parameter is not — a conditional's test (<#if x??>, <#elseif x>) — because a derived value gating SQL structure is the same hole as an interpolated one, one directive earlier.

12.7 Parameter validations

12.8 Settings validations

12.9 Composition validations

The PIPELINE-node rules (§4.9). Everything here is computed against the exact pinned child bodies. A draft child is mutable, so its verdict describes its current content and release revalidates the graph. Released child bodies are immutable.

12.10 Calculator-node validations

The CALCULATOR-node rules (§4.10, calculators design §0.3). Every one of them is decided from the body alone — the registry is a deployment constant and the Context's org and platform tiers are configuration — so an author gets the whole verdict at save time, never at 3am.

12.11 MCP-surface entry-point gates (139)

Two save-time refusals that live only on the AGENT surface (pipelines_create / pipelines_update over MCP), beside §12.1's new_root_requires_confirmation: each reads something only the agent loop has (the caller's own mcp.tool.called audit rows), which is why REST, the UI and every non-MCP caller are unaffected. Both check BEFORE any write; both refuse with the fix named.

12.12 Release checks

The checks[] rules (§3.3, 140). One code covers every defect, because to the author they are one category — this check cannot stand as written; path (checks[i].<field>) names the offender. Run-time outcomes — a statement that returns two columns, a datasource that is down — are NOT validation: they are the fail / error verdicts of the server's own check run, reported under §13.17. The read-only / single-statement discipline is enforced at RUN time by the bounded probe the runner rides; save time has no SQL grammar to consult, exactly as for node bodies.

12.13 Script validation rules

The SCRIPT-node rules (§4.12, transform-nodes design §3.1, R3–R5). Each is decidable from the body plus the pinned version's contract, so an author gets all of them at save time — the dry-render analogue for scripts, where "the pipeline and the template agree" is proven, not hoped. Run-time refusals are §13.18's family.

calculator_input_unordered, calculator_output_collision and calculator_output_name_invalid (§12.10) apply unchanged to a value-mode SCRIPT's context_key and to $key bindings whose writer is another node.

13. Error Code Catalog

Error codes follow the format {domain}.{entity}.{failure}. Codes are lowercase, dot-separated, ASCII. Codes are additive — never reused, never renamed.

13.1 Pipeline validation (write-time)

(See §12 for the full validation catalog. All validation errors use HTTP 400.)

13.2 Pipeline import

13.3 Pipeline execution (run-time)

13.4 Node execution

13.5 Staging

13.6 Type mapping

13.7 Authentication / authorization

Defined and described in Auth §9; cataloged here as the single code registry.

13.8 Datasource

Defined and described in Datasources §9–10.

13.9 Template

Defined and described in Templates §7.

13.10 Result retrieval

Defined and described in REST API §7.

13.11 Rate limiting / idempotency

13.12 Workspace resolution

Raised by the workspace resolution layer (Auth §5) — the per-request DP-Workspace switch and API-key pinning — and by the workspace CRUD and membership surfaces.

The 404 rule governs (RBAC design D-R5). A workspace the caller cannot reach — unknown name, not a member, or deactivated — is ONE answer: workspace.not_found, the same status and body a genuinely missing row produces. Never a 403. workspace.membership_required survives in exactly one place, where no workspace was ADDRESSED at all (a principal with zero memberships), because there is no name there whose existence a 403 could leak.

13.13 Versioning / draft-release lifecycle / promotion

Defined and described in Versioning. Codes are additive; the draft/release lifecycle and the preserved-version import rules live there (this table is the catalog entry, Versioning is the semantics).

13.14 Published endpoints

The contract and the complete status table are REST API §19; the key kinds and the hierarchical bindings are Auth §7.7. This table is the catalog entry — it lands with the constants (a catalogued code split from its constant leaves the build red between the two), and the two sections above land with the surfaces that raise them.

The bare endpoint.* codes are two-segment: the domain has no entity dimension, the same shape as datasource.in_use and template.not_found. The endpoint.request.* family is the request validator's, and it is the only family a caller of a published endpoint can provoke by changing their query string — every one of its codes is reported inside one endpoint.request.invalid response whose details.errors[] names every defect at once.

13.15 Learned semantics

The learned semantic layer (design record §3.1, §7.1, O-4): what semantics_record / semantics_retire refuse. Two-segment codes, like datasource.not_found — the domain has no entity dimension; every code is about one fact. Landed with the constants (PipelineErrorCodes.Semantics, mirrored in modules/datasources's SemanticsErrorCodes because the recorder lives below pipeline-contract) and their ApiErrorCatalog rows.

13.16 MCP surface

The MCP server's own refusals (120). Two-segment, like semantics.not_found — the domain has no entity dimension. The resource surface's unknown-URI answer is deliberately absent here: it is the JSON-RPC protocol's RESOURCE_NOT_FOUND (MCP §9.1), which never travels in a §9.2 content envelope. Landed with the constant (PipelineErrorCodes.Mcp) and its ApiErrorCatalog row.

13.17 Release checks

The release-check run's refusals (140, §3.3). Checks are opt-in: a version with no checks[] can never produce one of these — the gate runs only where the body declares them. Save-time shape defects are §12.12's pipeline.validation.check_invalid; this section is the RUN-time family, raised on the release path against the server's own fresh run — the author never supplies an observed value, so there is nothing here a caller could fake.

13.18 Script

The SCRIPT node's execution-time refusals (7c, #7; the transform-nodes design record's §7). The HTTP column is the class the node failure record carries, as for every execution-time family in §13.4; through templates_evaluate and the save-time test suite these surface inside template.test_failed (400) or as the tool's own status. The save-time twins of these checks are §12.13's ten pipeline.validation.script_… rows.

13.19 Schedules

The scheduler's refusals (#9; Scheduler, the scheduler design revision's §5). Two families, like the rest of §13: schedule.validation.* is the caller's request (400 — details.field names the field), and the bare schedule.* codes are a state the schedule is in (409), except the three not-found rows. Nothing here is raised while a schedule FIRES: a run the scheduler could not start is a not_started run with a reason on its trail (Scheduler §5), never an error response. A Run-now replay with a different body answers §13.11's idempotency.key_reused_for_different_request, and a pipeline's parameter refusals are exactly an interactive run's (§13.4). Landed with the constants — the catalog's PipelineErrorCodes.Schedule and the scheduler's own ScheduleErrorCodes, pinned equal by SchedulerUnitTest — and their ApiErrorCatalog rows.

13.20 Parameter sets

The parameter engine's refusals (#194; the parameter-engine design record §10). parameter.validation.* is the author's document at save — the record's §4 order: shape, the §3 field rules, the graph, the expressions, the template pins, then the dry render and the metadata execution — every failure collected (§17.2's rule), details.path naming the field. parameter.evaluate.* is the runtime (the record's §5): a whole-request refusal carries its status; the rows marked "—" are never an error RESPONSE — they are reported per parameter in state.errors[] of a 200 evaluate, whose valid is then false. A parameter's state.errors[] can also carry another family's code when its selector failed — the selector runtime reports the owning subsystem's own code (the record's §17, C20): pipeline.execution.datasource_unreachable, pipeline.node.query_execution_failed (a refused read-only gate included, details.reason = read_only_gate), pipeline.node.query_timeout, pipeline.node.sql_parameter_missing, pipeline.node.template_render_failed / template_not_found, datasource.table_forbidden, datasource.not_found (a datasource no longer visible from the workspace). The parameter.version.* / release / import / authoring rows are the template.* twins of the lifecycle (Versioning §3.5). A selector template that interpolates a parent is refused with §13.9's existing template.validation.parameter_interpolated. parameter.in_use (409) — the consumer binding the record's §13 reserved — landed with #320: a dashboard that pins a set version refuses its purge and discard. Landed with the constants — ParameterErrorCodes in modules/parameters and its mirror PipelineErrorCodes.Parameters, pinned equal by reflection (ParameterErrorCodesTest), held to this table by ParameterErrorCodesSpecDriftTest — and their ApiErrorCatalog rows.

13.21 Request limits

The platform's transport-level refusals (#279; Configuration §3.31). A body over datapipelines.web.max-request-bytes is refused at a servlet filter on BOTH JSON surfaces — REST /api/v1 and MCP /mcp — before authentication and before any parser reads a byte of it, so no handler, tool or permission is involved and the refusal carries no principal data (the envelope's correlation_id and this code only). The cap is a byte count on the REQUEST body; a response's budget is a different key (parameter.evaluate.response_too_large above). Jackson's StreamReadConstraints (nesting depth 100, string length 4M chars, number length 1000 digits) are stated explicitly on every request-body mapper rather than inherited, so a body WITHIN the cap that is adversarial for the parser is refused by the parser as a 400 (details.reason: malformed_json), not answered from an inherited default that could change under a version bump. The filter is mapped to /api/v1/* and /mcp only — a form-encoded UI post is Tomcat's max-http-form-post-size to bound, and a body sent on a body-less method (GET/HEAD/OPTIONS) is not counted, only drained within server.tomcat.max-swallow-size. The stated Jackson constraints hold on the auto-configured REST mapper, the MCP transport's, and — since #291 — every route that parses its own @RequestBody String (templates create/update/lifecycle/render/evaluate/import, pipelines create/update/import, execute), each of which reads its body through a REQUEST copy of its domain mapper (RequestLimits.requestMapper) and answers a read failure with its family's malformed-body 400, never the 500 backstop. The domain mappers themselves are left alone: they also read stored rows, script values and JSON columns, where a document deeper than a request may be is legitimate data — a request limit is a statement about request bytes. One set of routes is not yet covered: the parameter-set routes under web/parameters parse with the parameters module's strict mapper under the body cap and Jackson's inherited depth (#300).

Scalar shapes (#333). Jackson binds a JSON number or boolean into a declared String as its text unless the mapper's Textual coercion config fails it, and ALLOW_COERCION_OF_SCALARS off never reaches that bind (the same mappers also parsed "1" into an integer and 1.5 into 1). The four strict contract readers carry the same lines ParameterSetJson and ArtifactJson do — ALLOW_COERCION_OF_SCALARS and ACCEPT_FLOAT_AS_INT off, Textual failing the Integer/Float/Boolean shapes, Boolean failing Integer — so the value is refused with its PATH and the expected shape, never the value (Jackson's own message quotes it): the pipeline reader (PipelineJson, PipelineDeserializer) as pipeline.validation.schema_version_unsupported with details.reason: "wrong_type"; the template reader and the script blocks (TemplateJson, ScriptBlocks) as template.contract_invalid with details.rule: "wrong_type"; and the five routes that bind a typed request DTO (POST /auth/api-keys, POST /endpoints, POST /endpoints/bindings, POST /promotion/push, POST /dashboards/bindings) as pipeline.execution.invalid_parameter_type with details.reason wrong_type / unknown_key / missing and details.path, because they take the body as a tree and bind it through one strict request mapper (StrictRequestBodies, built over RequestLimits.requestMapper). Unknown keys stay tolerated on the pipeline and template readers (a re-import carries lifecycle keys) and on the promotion batch (a cross-version wire, ignoreUnknown), and are refused on the two request DTOs. Every echoed path is clipped and its control characters replaced.

13.22 Visualizations

The visualization artifact's refusals (#10; the dashboard implementation spec §3.1, §11, §14; Dashboards §2.1). visualization.validation.* is the author's document at save — the strict reader's shape first, then the spec's §3.1 rules — every failure collected (§17.2's rule), details.path naming the field. The visualization.version.* / import / authoring rows are the parameter.* twins of the lifecycle (Versioning §3.5); release.* is the gate (D56) — tests_* and mechanical_failed are raised by the evidence gate the tests lane (L4) installs, and until then the module's default gate refuses every visualization release with tests_missing. test.* belongs to the test-session routes (L4). The spec's list gained eight rows at L1a — name_invalid, name_taken, the five version.* lifecycle refusals and version.pinned (the design record's §4.2: purge and discard guards keep referenced releases) — and authoring.disabled; name_taken takes the place of the mould's duplicate_name because the dashboard family's duplicate_name names its object namespace. Landed with the constants — VisualizationErrorCodes in modules/visualization and its mirror PipelineErrorCodes.Visualization, pinned equal by reflection (VisualizationErrorCodesTest), held to this table by VisualizationErrorCodesSpecDriftTest — and their ApiErrorCatalog rows.

13.23 Dashboards

The dashboard artifact's refusals (#10; the implementation spec §3.2, §8, §9, §14; Dashboards §2.2). dashboard.validation.* is the author's document at save, again at release and — against the pinned dependencies' CURRENT state — at every runtime configuration read; details.path names the field and a refusal names the object, never a value. The version.* / import.* / authoring.* rows are the lifecycle twins; release.dependency_not_released names every dependency a release needs released (D61). runtime.* and refresh.* belong to the server runtime (L2) and key.* to the dashboard key kind (L5); they are catalogued now so the families are complete. The spec's list gained ten rows at L1a — name_invalid, name_taken, dependency_not_found, the five version.* lifecycle refusals, import.missing_dependency and authoring.disabled. Landed with DashboardErrorCodes (modules/visualization) and its mirror PipelineErrorCodes.Dashboard, pinned by DashboardErrorCodesTest and DashboardErrorCodesSpecDriftTest.

13.24 Navigation trees

The session-only navigation trees' refusals (#460, #464; REST API §24). The ten routes — GET /api/v1/<family>/tree and /tree/search for pipelines, templates, dashboards, visualizations and parameter sets — answered every refusal through Spring's ResponseStatusException until #464, so a client met the stand-in pipeline.execution.aborted with details.reason: response_status and could not tell a stale continuation (retry from the beginning) from an outage of the promotion target (retry later). Each refusal now carries its own code. Every message is static: no cursor, root, parent or q is echoed, and the 503 for an unavailable view carries no details (the target's address and the transport reason stay in the server's log). That view 503 answers Retry-After: 5 (a constant) and is logged once at WARN without a stack: since #554 mounts the tree on every family page, a promoter whose target is down meets it on every page load, and that is someone else's outage, not a defect in this process. The budget 503 stays at ERROR with the stack, because one node over 1 MiB of metadata is a server-side defect. A search term over the shared bound remains request.query_too_long (§13.21). Listing liveness is the §3.2 derivation, not a soft-delete column (V19 dropped pipelines.is_deleted and templates.is_deleted): an artifact is listed through current_version, or through its newest DRAFT when no version is current, and only while that listed version is DRAFT or RELEASED. No pointer can name a DISCARDED version. In every family the one statement that discards recomputes the pointer when it named the discarded version, and every other pointer write checks the version it names in the same statement, all under the workspace writer guard (#517). An entity whose every version is DISCARDED is therefore absent from browse and search, exactly as the legacy ENTITY_LIVE rule leaves it.

14. Pipeline Lifecycle Operations

This section sketches the CRUD operations. Full HTTP details are in the REST API spec.

15. Stability Promise

The Pipeline Contract is versioned, additive-only.

15.1 What is frozen in v1.1

• The top-level Pipeline JSON shape (schema_version, id, name, nodes, settings, etc.).
• The Node JSON shape (id, type, source, template, output, depends_on). Gained the optional pipeline / parameters fields for PIPELINE nodes 2026-08-17 — additive per §15.2; readers that predate them ignore the keys.
• The type enum: DQL, DML, DDL. PIPELINE added 2026-08-17 as an additive value per §15.2 — old documents remain valid; a deserializer that predates it rejects it with type_invalid.
• The output.target enum: tempdb, caller, datasource.
• The output.mode enum: replace, append.
• The parameter descriptor shape.
• The reserved literal "tempdb".
• The error code format and all error codes defined in §13.
• The naming rules: pipeline name is the §3.2 path grammar (widened from [a-z0-9_]+, 1–63 in 2026-09-05 — see Template Hierarchy §14.2 for the one shape that stopped being legal and why it needs no migration); node ids, output.table and parameter keys keep the identifier rules they always had ([a-z0-9_]+, 1–63, and [a-z_][a-z0-9_]* respectively) — 067 widened the NAME rule only.
• The caller-node resolution rules (§9): omitted output → caller, at most one caller node, zero legal.

15.2 What is NOT frozen

• New optional fields may be added (e.g., tags, metadata) — non-breaking.
• New error codes may be added — non-breaking.
• New output.target values may be added (e.g., kafka, s3) — non-breaking for v1 clients.
• New type values may be added (e.g., EXPRESSION, HTTP) — non-breaking for v1 clients.
• New settings.tempdb.engine values may be added (e.g., DUCKDB) — non-breaking.

15.3 Evolution rules

• schema_version bumps on any non-additive change.
• New versions are documented with migration notes.
• Old pipeline JSON continues to load and execute under its original schema_version (we maintain backward-compatible readers).

15.4 Which version counter governs what

Three independent counters exist across the system — none of them is "the version" alone:

A schema_version bump in the Type System's schema envelope says nothing about Pipeline JSON, and vice versa — clients must interpret schema_version in the context of the document that carries it.

16. Worked Examples

16.1 Minimal pipeline: single-source read

{
  "schema_version": 1,
  "name": "acme/reporting/active_users",
  "display_name": "Active Users",
  "description": "List all active users.",
  "parameters": {},
  "nodes": [
    {
      "id": "active_users",
      "description": "Read active users from PG.",
      "type": "DQL",
      "source": "pg-prod",
      "template": {"id": "acme/reporting/active_users.sql", "version": 1},
      "depends_on": []
    }
  ]
}

Single DQL node, no output block → resolves to caller. This is the caller node. ✓

16.2 Two-source join (most common pattern)

See §3.1 for the full example.

16.3 Pipeline with write-back

{
  "name": "acme/finance/monthly_revenue_etl",
  ...
  "nodes": [
    {fetch_orders, DQL, pg-prod → tempdb stg_orders},
    {fetch_customers, DQL, mysql-prod → tempdb stg_customers},
    {revenue_by_customer, DQL, tempdb → tempdb int_revenue},
    {
      "id": "write_to_warehouse",
      "type": "DQL",
      "source": "tempdb",
      "template": {"id": "acme/finance/select_revenue.sql", "version": 1},
      "output": {
        "target": "datasource",
        "datasource": "pg-warehouse",
        "table": "monthly_revenue_cache",
        "mode": "replace"
      },
      "depends_on": ["revenue_by_customer"]
    },
    {
      "id": "return_report",
      "type": "DQL",
      "source": "tempdb",
      "template": {"id": "acme/finance/report.sql", "version": 1},
      "output": {"target": "caller"},
      "depends_on": ["revenue_by_customer"]
    }
  ]
}

Two sinks: write-back side effect + caller return. Validation passes (exactly one caller target). ✓

16.4 Pipeline with DDL index creation

{
  "id": "create_idx",
  "type": "DDL",
  "source": "tempdb",
  "template": {"id": "acme/finance/create_idx_revenue.sql", "version": 1},
  "depends_on": ["revenue_by_customer"]
}

Where the template is:

CREATE INDEX IF NOT EXISTS idx_int_revenue_customer
  ON int_revenue (customer_id)

A downstream JOIN node then benefits from this index when querying int_revenue.

16.5 Pipeline with DML audit record

{
  "id": "record_execution",
  "type": "DML",
  "source": "pg-meta",
  "template": {"id": "acme/finance/record_execution.sql", "version": 1},
  "depends_on": ["return_report"]
}

Template:

INSERT INTO pipeline_executions (pipeline_id, executed_at, status)
VALUES ('${pipeline_id}', CURRENT_TIMESTAMP, 'SUCCESS')

Side-effect only. No output block. No data returned. ✓

17. Implementation Notes (Non-Normative)

17.1 Where this lives in the codebase

The Pipeline Contract is implemented in the pipeline-contract Gradle module:

• Pipeline data class (top-level)
• Node data class
• NodeType enum (DQL, DML, DDL)
• NodeOutput sealed interface (Tempdb, Caller, Datasource implementations — matches DAG Executor §4)
• PipelineSettings data class (with TempdbSettings nested)
• Parameter data class
• TemplateRef data class ({id, version})
• PipelineValidator — runs all §12 validations
• PipelineSerializer / PipelineDeserializer — Jackson-based JSON ser/deser; the deserializer resolves an omitted output on DQL nodes to NodeOutput.Caller
• ExecutionContext — runtime mutable map (see §7)
• CallerNodeResolver — implements §9 resolution
• PipelineRepository — persistence via NamedParameterJdbcTemplate

17.2 Validation pipeline

JSON input
  → PipelineDeserializer (syntactic parse; omitted output → Caller)
  → PipelineValidator (semantic validation, §12 — includes template dry-render)
  → CallerNodeResolver (resolve the caller node via §9)
  → Pipeline entity (validated, ready to persist or execute)

Validation is exhaustive — all checks run, all failures collected, returned together (not fail-fast). This gives authors the full picture on a broken pipeline.

A handful of §12 codes are necessarily raised at the DESERIALIZATION step rather than by the validator — verdicts on wire values that have no typed representation (type_invalid, output_target_invalid, output_mode_invalid, parameter_type_invalid, tempdb_engine_unsupported). They are still collected exhaustively across the whole document before binding fails, preserving the full-picture guarantee (2026-08-08).

17.3 Persistence

Pipelines are persisted in the app's own Postgres metadata DB. The DDL authority is Metadata DB — tables pipelines, pipeline_versions, pipeline_executions. This spec does not restate column lists.

18. Open Questions / Future Additions

Out of scope for v1.1, tracked for future:

• Calculators (v2): pre-execution transformers that read Context and write additional keys (quarter from date, etc.).
• Non-SQL node types (v2): EXPRESSION nodes (no SQL, script data in/out via expression language), HTTP nodes (call external API, stage response).
• Conditional execution (v2): skip nodes based on a Context expression.
• Retries (v2): per-node retry policies.
• Streaming (v2): pipe rows between nodes instead of full materialization.
• Pipeline-level scheduling (v2): declare cron-style schedules in the pipeline.
• Additional output.target values (v2): kafka (publish to topic), s3 (write object), email (send report), webhook (POST result).
• Auto-create target table for write-back (output.auto_create: true) — v1.1 candidate.
• Additional settings.tempdb.engine values — DuckDB in v1.1 or v2.

Appendix A: Change Log