Pipelines, Part 1: The Contract Before the Code.

A pipeline platform is easy to start and hard to keep coherent.

The first version of anything works. Trouble comes with the fortieth decision, made under pressure, that quietly contradicts the third. One error code gets reused for two different failures. A status called SUCCESS in one module is SUCCEEDED in another. A database hostname gets stored inside a definition, and the definition no longer moves between environments.

In Datapipelines, people won’t be the only authors. AI agents will create and change pipelines through a programmatic interface. An agent doesn’t remember an informal convention, and a person shouldn’t have to. Whatever the system means has to be written down precisely enough for a machine to obey and for a test to enforce.

So before writing code, we wrote the contract.

This is Part 1 of two. It covers the plan: what a pipeline is, how clients will reach it, how its history will be kept, and the rules we set for ourselves. Part 2 covers what we built from it, and what changed along the way.

Planning snapshot: this describes the Datapipelines specification set dated 5 August 2026, before any application code existed. Examples come from those specifications. They describe intended behavior, not a shipped release.

What is a pipeline? #

In our specification, a pipeline is a versioned, declarative DAG of nodes: a directed acyclic graph of steps, where each step runs only after the steps it depends on.

A pipeline definition does six things. It:

1. declares the input parameters it accepts
2. references templates that generate SQL
3. references datasources by name
4. declares where each node’s output goes
5. declares which nodes depend on which
6. declares runtime settings, such as the scratch database engine

It doesn’t contain connection strings, credentials, server names, or any other value that belongs to a particular environment. A definition authored in development should mean exactly the same thing in production.

Pipelines can be authored three ways: by a person in the editor, by an AI agent over MCP (the Model Context Protocol), or by importing JSON. The contract is the same for all three. Once a version is saved it is immutable. Editing creates a new version.

Nine design principles #

The pipeline contract opens with its principles. They come before the schema because they decide the arguments the schema would otherwise reopen.

Two principles carry most of the weight.

Validate on write means a broken definition is refused when it is saved, not discovered at 3 a.m. when a scheduled run fails. The executor can trust its input completely, which makes it much simpler.

Environment-portable by construction means promotion is a copy, not a rewrite. A pipeline references pg-prod by name, and each environment decides what pg-prod resolves to. The specification makes this mechanical: environment-specific values are a write-time error with a code of their own, pipeline.validation.forbidden_env_specific_value.

The shape of a definition #

Here is a condensed version of the specification’s own example. It joins orders from PostgreSQL with customers from MySQL, writes a cached copy to a warehouse, and returns a report to the caller.

{
  "schema_version": 1,
  "name": "monthly_revenue_report",
  "display_name": "Monthly Revenue Report",
  "settings": { "tempdb": { "engine": "H2", "config": { "max_memory_mb": 1024 } } },
  "parameters": {
    "start_date": { "type": "DATE", "required": true },
    "end_date":   { "type": "DATE", "required": true },
    "min_total":  { "type": "BIGDECIMAL", "precision": 12, "scale": 2,
                    "required": false, "default": "0.00" }
  },
  "nodes": [
    { "id": "fetch_orders", "type": "DQL", "source": "pg-prod",
      "template": { "id": "fetch_orders.sql", "version": 2 },
      "output": { "target": "tempdb", "table": "stg_orders" },
      "depends_on": [] },
    { "id": "fetch_customers", "type": "DQL", "source": "mysql-prod",
      "template": { "id": "fetch_customers.sql", "version": 1 },
      "output": { "target": "tempdb", "table": "stg_customers" },
      "depends_on": [] },
    { "id": "revenue_by_customer", "type": "DQL", "source": "tempdb",
      "template": { "id": "join_revenue.sql", "version": 1 },
      "output": { "target": "tempdb", "table": "int_revenue" },
      "depends_on": ["fetch_orders", "fetch_customers"] },
    { "id": "cache_to_warehouse", "type": "DQL", "source": "tempdb",
      "template": { "id": "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", "type": "DQL", "source": "tempdb",
      "template": { "id": "final_report.sql", "version": 1 },
      "output": { "target": "caller" },
      "depends_on": ["revenue_by_customer"] }
  ]
}

A few properties are worth noticing.

The two fetch nodes depend on nothing, so they may run at the same time. Parallelism isn’t declared anywhere. It follows from depends_on.

Data moves between nodes through tempdb: a private, in-memory scratch database created for each execution and destroyed when it ends. A node reads from a real datasource, stages rows into tempdb, and later nodes join across those staged tables. That lets one pipeline combine PostgreSQL and MySQL data without either database knowing about the other.

The template references are pinned. fetch_orders.sql version 2 means version 2, permanently. Editing the template creates version 3, and this pipeline keeps running version 2 until someone deliberately changes the pin.

Types that survive the wire #

Pipelines cross several boundaries: databases with their own type systems, a JSON API, and AI agents generating parameter values. Each boundary is a place where a number can lose precision or a date can change meaning.

The plan defines 11 canonical logical types: NULL, BOOLEAN, INTEGER, BIGINTEGER, DECIMAL, BIGDECIMAL, STRING, BINARY, DATE, TIME, and TIMESTAMP. Each database dialect maps its native types onto these.

The JSON encoding is decided by type, never by value. A BIGINTEGER always travels as a JSON string, even when a particular value would fit safely in a double. A client written against the contract never has to check a value’s magnitude to decide how to parse it. The specification’s roadmap records the alternatives we rejected, including encoding every number as a string and choosing the encoding per value.

Three ways in #

Datapipelines has three kinds of client: the browser, other programs, and AI agents. The plan gives each a deliberate interface.

REST: one envelope for every answer #

The REST API lives under /api/v1. Every success shares one envelope, and so does every failure. An error carries a stable machine-readable code, a developer-facing message, a separate user_message that is safe to show a person, structured details, and a doc_url.

Error codes follow one grammar, {domain}.{entity}.{failure}. The catalog is centralized and additive: a code is never reused for a different failure and never renamed. A client that handles pipeline.validation.cycle_detected today can rely on that meaning indefinitely.

SSE: watching an execution happen #

A pipeline can take seconds or minutes. The client should see it progress rather than wait on a silent request.

Executing a pipeline returns a Server-Sent Events (SSE) stream. The specification names the events: execution_started, node_started, node_completed, node_failed, pipeline_completed, pipeline_failed, and data_ready. Events carry monotonic IDs, and a heartbeat every 15 seconds keeps intermediaries from closing an idle connection.

The specification records why we chose SSE over WebSockets. Execution progress flows one way, from server to client. SSE runs over ordinary HTTP, so proxies, load balancers and authentication headers work without special handling. A WebSocket would need its own protocol, proxy configuration and authentication story to do the same job.

MCP: a first-class interface for agents #

We planned Datapipelines as MCP-native. Agents connect over the protocol’s Streamable HTTP transport at a single /mcp endpoint. The first tool set has 15 tools across pipelines, templates, datasources and executions: list, get, create, update and execute pipelines, render templates against sample parameters, test datasources, and read execution results.

The governing principle is that MCP is a thin adapter over REST. Every MCP tool maps to REST operations, and the MCP layer holds no business logic of its own. An agent and a person should get the same validation, the same errors, and the same behavior.

Agents authenticate with API keys of the form dpk_<id>.<secret>, stored as Argon2id hashes. A user creates one key per agent, so each agent’s actions are attributable and each key can be revoked on its own. The dpk_ prefix exists so secret scanners can recognize a leaked key. Every MCP tool declares a required scope: read, execute, author, or admin, from least to most capable.

Every change leaves a record #

Agents will change pipelines, so history isn’t optional. The plan makes attribution structural rather than a logging afterthought.

• Versions are immutable. Each saved pipeline version is a row keyed by (pipeline_id, version). The rule in the metadata specification is blunt: never UPDATE, only INSERT a new version. Each row records created_by.
• Executions record their origin. Each execution stores who triggered it and through which surface: UI, REST, or MCP. A database constraint limits the values.
• Security events have an audit log. An append-only audit_log records authentication and administrative events, with the API key involved and structured details, retained for 365 days.
• Agent calls are on the checklist. The MCP specification’s security checklist requires the audit log to record every tool call: the tool, the caller, the target pipeline, the time, and whether it succeeded.

That answers the first questions anyone asks after an unexpected change: which version is this, who created it, and which client did they use?

Security is part of the contract #

For security, the plan starts from deny:

• No stored passwords. People sign in through any OpenID Connect provider. After login, the server issues its own session token, valid for eight hours, in an HTTP-only cookie.
• Default-deny endpoints. Every route requires authentication except a short allowlist, and each public route records why it is public.
• Hardened templates. Templates are Freemarker files stored in the database, not loaded from the filesystem. The constructs that could reach into the Java runtime are disabled.
• No credentials in results. No MCP response returns a datasource password, and that requirement is on the security checklist.

Planning as an engineering discipline #

The specification set is 18 documents and about 10,600 lines. Most of its value isn’t in the volume. It’s in the conventions that keep that much text consistent.

One specification per module. The planned codebase has ten Gradle modules, from typesystem at the bottom to app at the top, with a strict dependency direction: no cycles and no upward references. Each module has a specification that owns it.

Registries are authoritative. One document is the registry of every enumerated value: statuses, node types, scopes, and more. Its rule is direct: if code or a specification disagrees with the registry, the code or specification is wrong. Its checklist says to update the registry first, then search the documents and the code for the old value.

Configuration has one home. Every configuration key is defined in one document, so nobody has to search fifteen specifications to learn what a setting does.

Every specification makes a stability promise. Changes are additive. Old JSON must keep loading. A non-additive change requires a new schema_version. Each document keeps a versioned change log.

Rejections are recorded with their reasons. The roadmap includes a table of ideas we considered and declined: a SQL parser such as Apache Calcite, a separate field for declaring parallelism, all-string numbers, and OAuth for the first release. Each has a recorded reason, so the same debate doesn’t restart six weeks later without the original argument.

Uncertainty is marked, not hidden. Where a specification depends on something not yet verified, such as the exact MCP protocol version or a library coordinate, it says so explicitly. Those claims are checked at implementation time rather than assumed.

Tests are planned before code. The module specification defines unit tests, integration tests against real databases in containers, smoke tests, and performance tests before there is anything to test.

What we deliberately left out #

A plan also has to say what it excludes. The roadmap sorts out-of-scope work into four groups:

• Next-release candidates: schema-introspection tools to help agents write SQL, DuckDB as an alternative scratch engine, and key management service integration.
• Larger work for later: calculators, non-SQL node types, conditional execution, retries, streaming, and scheduling.
• Operator responsibilities: TLS termination, backups, log aggregation, and alerting.
• Rejected ideas, with the reasoning above.

Keeping those lists explicit lets the first release stay small without losing track of what comes next.

From contract to code #

The next step is to build in dependency order: types first, then the pipeline contract and its validator, then templates, datasources, staging and authentication, then the executor, and finally the REST, SSE and MCP surfaces.

A specification is a hypothesis. Implementation will test it, and some of it will change. That’s expected. Because the decisions are written down with their reasons, a change will be deliberate rather than accidental.

Update, 8 November 2026: Part 2 covers what we built from this plan, including the draft-and-release lifecycle we added for agent changes, the event stream, and the MCP server as it stands today.