How it works: ask it once. Build an answer you can reuse.

Versioned JSON contracts

Pipelines are declarative JSON — schema_version, nodes, depends_on, parameters, settings. Every update mints a new immutable integer version, and the contract is additive-only.

Freemarker SQL templates

A save-time scan walks the parsed template AST against a forbidden-construct list — ?eval, ?new, ?api, in-body imports — so SSTI vectors are rejected before a template is ever saved.

Declared parameters bind as :name on a prepared statement — never interpolated. Interpolating one into the SQL text instead is refused at save with template.validation.parameter_interpolated.

Template versions, pinned by hash

Every template version is immutable — body, imports, dialect and engine are frozen once stored, versions are never reused or renumbered, and each carries a content hash. Pipelines pin {name, version}, so a new template version is invisible to them until an author moves the pin.

Templates are typed: sql (dialect required) or html (dialect absent, auto-escaping on). The type is fixed at creation and a database constraint enforces the pair.

Names are paths, and a folder is mandatory

A template's or pipeline's name is its path — nyc/mobility/revenue_by_borough, 2 to 10 segments, and the first one is a folder: the root holds folders only. A flat name is refused with details.reason = "folder_required".

An agent that invents a NEW top-level root is refused until it says so on purpose: confirm_new_root, with the roots that already exist in the error's details. Asking a person before minting a namespace is a guarantee here, not advice.

Draft → release → discard

One draft per entity, at most. Releasing locks the version: we never modify a released pipeline — the next change copies it into a new draft. Discard drops the draft and returns the number to the pool.

The editor's version select is read-only for anything but the working version, and Edit reuses the existing draft or copies the selected version into a new one — the rule is visible on screen, not just enforced behind it.

Used-by: impact before you change

The reverse arrow, answered as two different questions. Who uses this version now? scans every pipeline's working version and names the pipeline, the node id and the version carrying the pin — a fact you can go and change, not a count.

Is it safe to delete? scans every version ever, because historical versions stay executable. Delete is refused with 409 template.in_use while any pipeline version pins it.

Eight engines, one contract

Postgres, Oracle, SQL Server, MySQL, H2, DuckDB, SQLite and dp-lake — dp-lake is object storage read in place. One pipeline contract over all of them.

Oracle and MySQL drivers are opt-in for license reasons (-Poracle/-Pmysql, or drop the jar in lib/); a missing driver fails at save with datasource.driver_not_loaded. Everything else is in the published image, the DuckDB extensions included — a hardened deployment needs no egress to an extension repository.

dp-lake: Parquet and Iceberg, read in place

A LAKE datasource queries Parquet and Apache Iceberg on S3 or any S3-compatible store with no warehouse and no load step. DuckDB is the engine; the catalog is the server's own registry, dp-catalog — tables are registered over REST or by an agent through the lake_tables_* tools.

Hive-partitioned Parquet is scanned with partition pruning, so a date predicate reads the days it names and not the table. It is a read connector: there is no write path to the bucket. The whole dp-lake story →

Cross-engine staging joins

Each execution gets its own isolated in-memory H2 tempdb — created at start, destroyed at end. Nodes stage ResultSets there, so downstream nodes join across engines.

Pipeline composition

A PIPELINE node executes a version-pinned child pipeline as a linked child execution — in-process, with lineage and family cancellation. The child keeps its own execution record, tempdb, stats and stream.

The pipeline editor

The DAG renders on a full-bleed canvas as node cards, with the facts inside the box: name, a type badge, datasource · dialect, template @ version, and after a run, elapsed time and rows.

One dock, four tabs — Details, Results, Errors, Events: a failure opens Errors, every event in arrival order.

It executes, streams, cancels and pages results. What it deliberately does not do is author: no edit mode in v1 — authoring is agent-first.

Failures you can actually read

A failed node carries the catalogued code, the message, the node's own facts (datasource, dialect, template @ version), the rendered SQL in :name form — never a bound value — and the original exception chain with frames, root cause last.

The same record, built once, reaches the node_failed SSE event, the stored error_json, the editor's error surface and the agent's executions_get. No surface strips what another shows.

Live execution streams, and a cancel that means it

POST /api/v1/pipelines/{id}/execute returns text/event-stream with per-milestone events and heartbeats. No stream resumption — a dropped connection cancels the execution rather than orphaning it.

Cancel reaches the executing instance through Redis from any instance, and a per-node latch refuses to enter the driver once it has fired. A cancelled execution cannot come back SUCCESS — the window where it could was closed and is covered by a test that injects the descheduling rather than racing it.

Promotion between environments

Push dev → staging → prod from the UI. The screen lists only released versions newer than the target's; drafts never leave the authoring environment, and templates and child pipelines travel first, as a dependency closure.

The credential is a server-kind API key, not a principal — no user account, so rotation and offboarding are independent of any person. Rotation is: mint a second, set it on the sender, revoke the first — no restart on either side.

Multi-instance ready

Instances are symmetric and stateless for API, MCP, UI and auth — no sticky sessions. Only a running execution is pinned to one JVM; cancel works from any instance through Redis, and results and history are readable from all of them.

Datasource pool invalidation rides a Redis channel: repoint or rotate a credential on one instance and every other evicts its pool and rebuilds from the row.

A crashed instance's in-flight executions are swept to ABORTED by an idempotent stale-execution sweep every replica runs.

One API section

Everything a program uses to talk to a workspace, on one screen: the endpoints you have published as GET /api/x/…, the keys that may call them, and the MCP connection block — the tool count rendered live from the catalog, never a literal.

It is honest about what it cannot show: no "calls in the last 24 h" column — every serve is audited, but nothing tallies them per endpoint — so the card states the absence instead of filling it from a sample.

Connection pools you can see and set

Eight HikariCP settings are on the datasource form, each prefilled with its effective default and labelled with where that default came from. A field left alone is not persisted, so a later change to a product default still reaches the datasource.

Out-of-range values are refused, not accepted — every floor restates a rewrite Hikari would otherwise perform silently, and the test that justifies them builds a real pool and watches it happen.

A saved datasource never kills a running query

Editing or deleting a datasource retires its pool instead of closing it: new leases build a fresh pool from the new row at once, idle connections close now, and a connection still in use closes when its borrower returns it — never mid-statement.

Delete asks the question first: the dialog runs the any-version usage scan before it offers anything, and lists pipeline › node (v3 released) for every reference. The 409 and the dialog cannot disagree — same scan.

Append-only audit log

Logins, key lifecycle, scope denials, user admin, workspace events, promotion accept/reject, every MCP tool call and each credential-decryption point — recorded with actor, cause and source IP.

Catalogued error contract

Failures arrive as stable {domain}.{entity}.{failure} codes in a uniform error envelope — a catalog enforced against the code by drift tests, and the same codes over MCP. Agents get errors they can branch on, not prose to parse.

Workspaces & team isolation

Every request resolves exactly one workspace; all authored content is workspace-scoped; API keys are pinned to one workspace; membership roles are additive flags — viewer, author, promoter, workspace admin and super admin.

The management UI ships: create, switch, manage members, a navbar switcher and add-by-email.

A key has a kind, and the kind decides its reach

Three kinds. A user key is an agent's key and a program's key — its authority is its scopes. An endpoint key authorises the published paths it is bound to. A server key opens the promotion routes and nothing else.

The confinement is decided centrally, before any handler's annotation is read — a new route cannot become quietly reachable to a scopeless key.

The agent never sees a credential

There is deliberately no tool that registers a datasource, and none that mints a key: a credential through a tool call transits the agent's context and transcript. A person adds the datasource in the UI; the agent uses it by name.

What the agent keeps is everything that needs no secret: list, get, test, schemas, tables, columns, preview rows — enough to author without a password.

OIDC SSO + scoped agent keys

Humans sign in through OIDC SSO. Agents get their own API keys with hierarchical scopes — separate principals, separate blast radii.

Local password accounts

No identity provider? Turn on local accounts and the whole product runs with zero external setup. Hashes are Argon2id through the same hasher API keys use; there is no self-registration — an admin creates the account, or config seeds the first admin only.

Every seeded or admin-reset credential is one-time and the app refuses every other screen until it is changed. Five consecutive failures lock the account — and the unknown-email path spends the same hashing memory, so timing cannot enumerate.

Read-only datasource governance

is_readonly forbids write-shaped uses — enforced at save time, at an executor backstop, and as Hikari readOnly. Admin-gated on globals. The flag is a contract, not containment: pair it with a SELECT-only DB user.

Encrypted datasource credentials

Passwords are AES-256-GCM encrypted with the row's name bound as additional authenticated data, so a ciphertext moved between rows fails to decrypt. The app refuses to start without a valid key.

Every credential carries a key version and datapipelines.db.key-provider selects where the key comes from — one contract test every provider passes.