# MCP Server Specification **Status:** v1.77 (frozen contract — additive-only changes after this point) **Owner:** datapipelines.co core **Depends on:** [Type System spec](/docs/type-system.md), [Pipeline Contract spec](/docs/pipeline-contract.md), [REST API spec](/docs/rest-api.md), [Auth spec](/docs/auth.md), [Templates spec](/docs/templates.md) **Last updated:** 2026-10-25 --- ## 1. Purpose datapipelines.co is **MCP-native**. Agentic tools (Claude Desktop, GLM, Copilot, custom LangChain/LlamaIndex agents, etc.) connect to a datapipelines.co instance via the [Model Context Protocol](https://modelcontextprotocol.io) to discover pipelines, execute them, and read results — without writing custom integration code. This spec defines: - The **MCP transport** (Streamable HTTP) and how clients connect. - The **authentication model** (the user's MCP key — created on the Keys page with a role, one credential per agent). - The **tool surface** (functions the agent can call). - The **resource surface** (entities the agent can read as files). - The **prompt surface** (predefined workflows the agent can invoke). - The **error model** (how datapipelines errors map to MCP errors). --- ## 2. Design Principles 1. **MCP is a thin adapter over REST.** Every MCP tool maps to one or more REST endpoints defined in [REST API spec](/docs/rest-api.md). No business logic in the MCP layer — it's translation only. 2. **Tools for actions, resources for inspection.** If an agent needs to *do* something (execute a pipeline, create a template), it calls a tool. If it needs to *read* something (look at a pipeline definition), it reads a resource. We avoid duplicating read operations as both. 3. **API key, not OAuth.** Self-hosted, internal-users-only deployment model makes OAuth overkill. The user creates an MCP key on the Keys page with the role the agent should have (keys v2, [Auth §7.4](/docs/auth.md#74-issuance)), passes it to their agent, the agent uses it — in either the `DP-API-Key` header or an `Authorization: Bearer dpk_...` header. See [Auth §8.5](/docs/auth.md#85-mcp-endpoint-mcp). 4. **MCP versioning follows the protocol.** We commit to a specific MCP protocol version per datapipelines.co release, and document upgrade paths when the protocol evolves. 5. **Fail loudly, never silently.** MCP-level errors (transport, auth) and application errors (pipeline validation, datasource unreachable) both surface as structured errors the agent can act on. No silent fallbacks. 6. **Workspace-scoped by the key (workspaces design §5.2/§9).** Every tool and resource operates inside the workspace the API key is PINNED to at issuance — `DP-Workspace` is refused on MCP requests (`400 workspace.header_forbidden`), because a header-switchable agent key would make every leaked key a skeleton key across the user's workspaces. Pipelines, templates and executions of other workspaces are ABSENT (not hidden): their ids resolve as not-found. Datasources visible here are exactly the ones GRANTED to the pinned workspace (D-R7): there is no global datasource, and one that is not granted is ABSENT, not hidden. The `initialize` result's `instructions` field states this so an agent does not reason about invisible siblings. --- ## 3. Transport ### 3.1 Transport choice: Streamable HTTP We expose MCP via the protocol's **Streamable HTTP** transport: - Single endpoint: `POST /mcp` (and `GET /mcp` for server-to-client notifications/SSE). - Content types: `application/json` for single requests/responses, `text/event-stream` for streamed responses. - Works through standard HTTP infrastructure (proxies, load balancers, TLS terminators). - No WebSocket requirement (which would need custom proxy config). This is the protocol's network-native transport, appropriate for our self-hosted, network-resident deployment model. The stdio transport (used for local-tools) is not supported — our product is a server, not a local process. > **Implementation gate — RESOLVED at P6b (2026-08-10).** This spec is authored against the durable shape of MCP; the concrete protocol version string is a build-time input, not a frozen contract term. The checklist below was completed against the official specification and the shipped MCP SDK when the module was implemented: > > - [x] **Current protocol version string.** Pinned to `2025-06-18`, returned in `initialize.protocolVersion` (§5.1) and accepted in the `MCP-Protocol-Version` header (§3.2); a `PinnedTransport` decorator advertises it as the sole supported version. > - [x] **Version-negotiation rule.** The server negotiates **down** to its pinned version — a client offering a newer version is served `2025-06-18` (verified in-process). > - [x] **Streamable HTTP requirements.** The v1 server is **stateless**: `POST /mcp` accepts `application/json` + `text/event-stream`; `GET /mcp` for a server-initiated SSE stream is **optional and NOT served** (answered `405`) — so there are no server-to-client notifications in v1 (§5.1, §10), no `MCP-Session-Id` issuance, and no resumability headers. A stateful transport (session ids, the notification stream) is a v2 item ([ROADMAP §3.7](/docs/roadmap.md#37-mcp-server)). > - [x] **SDK coordinates.** `io.modelcontextprotocol:mcp-core` + `mcp-json-jackson2`, both `mcp-sdk = 2.0.0`, pinned in the version catalog ([Module Structure §8](/docs/module-structure.md)). > > These resolutions were additive corrections to §3 and §5.1 only — the tool, resource, and prompt surfaces did not depend on them. ### 3.2 Endpoint structure ``` POST {host}/mcp Headers: Content-Type: application/json Accept: application/json, text/event-stream DP-API-Key: dpk_. # OR: Authorization: Bearer dpk_. MCP-Protocol-Version: 2025-06-18 # placeholder — pinned by the §3.1 implementation gate MCP-Session-Id: {session-uuid} # optional; server may issue for stateful sessions ``` Exactly one credential carrier is required, and the two are equivalent: `DP-API-Key` (the REST convention, [REST API §3.6](/docs/rest-api.md#36-custom-header-registry)) or `Authorization: Bearer dpk_...` for MCP clients that can only set the standard Authorization header. Both route through the identical API-key validation path — see §4.1 and [Auth §8.5](/docs/auth.md#85-mcp-endpoint-mcp). Session JWTs (`dp_session` cookie, or a Bearer token that is not a `dpk_` key) are **not** accepted on `/mcp`. Server response: JSON for single-message exchanges, `text/event-stream` for streamed responses. **The request-body cap reaches `/mcp` first** (#279, pipeline-contract §13.21): the platform's cap filter is a servlet filter registered ahead of the transport servlet, so a body over `datapipelines.web.max-request-bytes` ([Configuration §3.31](/docs/configuration.md#331-web-request-limits)) is refused with the same `413 request.body_too_large` envelope REST answers — the MCP SDK reads the whole body before its dispatcher parses anything, so the filter is the surface's only cap. A declared `Content-Length` over the cap is refused unread; a chunked over-cap body is cut off mid-read by the counting stream (the SDK answers its own JSON-RPC error there — the body never parses, which is the cap's contract). The transport's JSON mapper carries the same stated Jackson `StreamReadConstraints` (nesting 100, string 4M chars, number 1000 digits) as the REST surface's, built from the same factory (`RequestLimits.jsonFactory`): a body the transport cannot read — malformed, or nested past 100 — is refused `400` with the SDK's JSON-RPC error (`-32600`, "Invalid message format"). **Since #291 that error carries no exception internals**: the SDK writes its `McpError` exception through the transport's mapper, and before #291 the body was the serialized throwable — `stackTrace` (every frame's class, file and line), `cause`, `suppressed`; the mapper now drops them for every throwable, leaving `jsonRpcError` and `message`. A chunked body over the cap is cut at the cap as well, but the SDK's transport catches the refusal inside its read loop and answers its own JSON-RPC error (HTTP 500), not the 413 envelope — a body with a declared `Content-Length`, every standard client's, gets the envelope. ### 3.3 Session lifecycle - **Stateless by default.** Each request carries full auth context. Server does not require session continuity. - **Optional session.** Server MAY issue an `MCP-Session-Id` for clients that want one. Session state = nothing important (cached auth, nothing else). --- ## 4. Authentication ### 4.1 Auth model `/mcp` is **API-key-only**. Every MCP request must carry a datapipelines API key in one of two equivalent headers: - `DP-API-Key: dpk_.` — the REST convention; the primary case. - `Authorization: Bearer dpk_.` — for MCP clients that can only set the standard Authorization header (Claude Desktop and several others). The filter recognizes the `dpk_` prefix and routes the token through the identical validation path. Both are validated by [Auth §7.3](/docs/auth.md#73-validation-flow) — same lookup, same Argon2id verification, same 60s-TTL revocation/liveness re-check ([Auth §11.4](/docs/auth.md#114-api-key-validation-cache)). Revocation or deactivation of the key's identity, its MCP creator or its pinned workspace takes effect within the auth cache TTL (60 s by default, #215 B5). The creator's deactivation remains a liveness check (A20); a later change to the creator's member role changes nothing for the key, whose authority is its own chosen role (keys v2, [Auth §7.5](/docs/auth.md#75-key-roles)). **Session JWTs are not accepted on `/mcp`.** There is no cookie auth and no non-`dpk_` Bearer token path — a browser-embedded MCP client must use an API key like any other agent. API keys of the `mcp` kind (keys v2 #233, A13–A19) are: - **Created on the Keys page by anyone holding `mcp_key.create`** (author, promoter, workspace admin) — never at sign-in any more: the login mint is retired (A15), and `mcp_key.create`'s SUBSET RULE (A14) decides which ROLES the creator may give the key (never `viewer`, never a super-admin role — B1). Rotation is create-a-new-one-and-revoke-the-old (`mcp_key.revoke_own`). - Expiring or not, creator-scoped to revoke; HTTP surface in [REST API §16.1](/docs/rest-api.md#161-api-keys-creation-is-on-the-keys-page--keys-v2). - Acting as their OWN identity, which holds the CHOSEN member role (`author` | `promoter` | `workspace_admin`) in the key's workspace ([Auth §7.5](/docs/auth.md#75-key-roles), A13): nothing is derived from a membership at request time and nothing is capped — the role IS the key's authority, and **no key is ever a super admin** (B1), so no key reaches an instance verb. **The MCP key connects an MCP client to `/mcp` and nothing else** (#215 B2, owner ruling 2026-09-24: "MCP key should be only MCP"): presented on any REST route, page or partial it is refused with `403 endpoint.key_kind_refused`, `details.reason = "mcp_key_off_surface"` — a program that calls REST uses a person's session, and a program that calls published endpoints uses an `endpoint` key. The other two kinds ([Auth §7.7](/docs/auth.md#77-key-kinds-and-published-endpoint-bindings)) do not reach `/mcp` at all — an `endpoint` key authorises published endpoints and a `server` key the promotion routes, and each is refused here with `403 endpoint.key_kind_refused` by `McpAuthFilter` (the interceptor never sees `/mcp`, which is a servlet, so the refusal is made again at the transport). Such a key could otherwise read the whole tool catalogue through `tools/list` without being able to call any of it. **Enforcement is the ROLE** ([Auth §7.6](/docs/auth.md#76-operation-matrix--the-permission-catalog-authoritative), [§11A](/docs/auth.md#11a-roles)). Each tool declares ONE catalog permission on its catalog entry (`McpToolCatalog.Entry.permission`) — this spec restates it in §6.2 for readability, but the catalog is authoritative on any conflict, and one function (`ScopeMatrix.allowedTool`) answers here and at the REST interceptor. A tool that declares none is refused (`auth.permission.undeclared`). **A key acts as its OWN identity, at the role chosen at creation** (keys v2, [Auth §7.5](/docs/auth.md#75-key-roles)): every request judges the key's OWN role against the tool's permission — nothing is derived from a membership at request time, so a later change to the creator's member role changes nothing for the key. What DOES reach the key within the 60 s validation-cache TTL is revocation and deactivation (§4.2): a revoked key, a deactivated key identity or MCP creator, or a deactivated workspace stops working within about a minute. A refusal is `auth.role_required` ([Auth §7.6](/docs/auth.md#76-operation-matrix--the-permission-catalog-authoritative)) — `details.required` names the permission and `details.held` the key's own role; the fix is a key whose role holds the permission (create one under the subset rule, revoke this one), never a member-role change. **A promoter's key sees through the promoter LENS (178, [Auth §11A.1](/docs/auth.md#11a1-the-404-rule)).** The role narrows what a READ returns, never who may read: `pipelines_list/get`, `templates_list/get/used_by`, `endpoints_list/get`, the resource catalogue and the pipeline/template resources answer a promoter with RELEASED objects newer than the promotion target's inventory entry ([Versioning §10.2](/docs/versioning.md#102-the-listing-rule-what-the-ui-shows)) and nothing else — a draft, an already-promoted object, or a pipeline behind the target is ABSENT for that key and resolves as not-found, exactly as another workspace's would; a visible object's pending DRAFT is invisible too — `pipelines_get` / `templates_get` default to the RELEASE for that key, carry no `draft` pointer, and an explicit draft `version` is not-found. The inventory is read through a per-workspace cache (`datapipelines.deployment.promotion.inventory-cache-ttl-seconds`); when the target cannot be read the lens fails CLOSED — lists are empty, gets are not-found, never a 502 to a read — and the server logs `pipeline.promotion.lens_unavailable` once per window. Every other role's key is unaffected and triggers no target call. A key's role can be `promoter` or `workspace_admin`, and such a key is limited by its ROLE — the permission catalog rows it holds and, for a `promoter`, the release lens above — never by a cap. Release, promote, workspace creation and membership have no MCP tool at all: they are human verbs, which is the same reason no key is ever a super admin. Registering, editing and deleting datasources remain UI/REST-only. **Datasource visibility is a GRANT** ([Auth §11A](/docs/auth.md#11a-roles)). A datasource not granted to the key's pinned workspace does not exist for it: `datasources_list` is the truth, and guessing a name gets the not-found envelope, never a "forbidden". **Security chain.** `/mcp` (both `POST` and `GET`) is an explicit matcher in the Spring Security filter chain: CSRF-exempt (no cookie auth to forge against), no session cookies accepted, the same permission enforcement as REST, same per-user rate limits ([REST API §12](/docs/rest-api.md#12-rate-limiting)). See [Auth §8.5](/docs/auth.md#85-mcp-endpoint-mcp). **The principal travels WITH the call, never on the thread (134).** `McpAuthFilter` resolves the key on the servlet thread and hands the principal to the MCP layer through the transport context (`McpToolContext`); the SDK then runs every tool handler on its own scheduler thread (`boundedElastic`), where Spring Security's thread-local context is **empty**. So a tool — and anything a tool calls — must take *who is asking* and *which workspace* as arguments from `McpToolContext`; a domain port or `@Bean` adapter that reads `SecurityContextHolder` answers correctly over REST and as "no principal" over MCP, with no error anywhere. The save-time datasource lookup was exactly that: a workspace-owned datasource validated as `pipeline.validation.unknown_datasource` over MCP while the same body was `201` over REST (measured 2026-09-14; the demo datasources are owner-less + granted, which is why the acceptance run never saw it). Since 134 the validator's datasource port takes the workspace explicitly, as the executor always did; `McpSaveWorkspaceDatasourceE2eTest` holds the line. ### 4.2 Unauthorized behavior Missing credential (no `DP-API-Key` header and no Bearer `dpk_` token): - HTTP `401 Unauthorized` with JSON body: ```json {"error": {"code": "auth.api_key.missing", "message": "..."}} ``` - MCP session is not established. Invalid, revoked or expired key: - HTTP `401 Unauthorized` with `auth.api_key.invalid` (or `auth.api_key.expired`). Deactivated identity or deactivated pinned workspace (180, [Auth §11A.3](/docs/auth.md#11a3-deactivation)) — judged at validation, so no MCP session is established and even `tools/list` is refused: - HTTP `401 Unauthorized` with `auth.principal_deactivated` (the key's IDENTITY is deactivated — with its key, keys v2 A17; reactivation restores the key); - HTTP `404 Not Found` with `auth.key_workspace_inactive` (the WORKSPACE the key is pinned to is deactivated). A role that does not hold the tool's permission (e.g., an `author`-role key calling nothing beyond itself — or a `promoter`-role key calling `pipelines_create`): - A tool result with `isError: true` carrying `auth.role_required` (§9.2; keys v2 A13 — the key is judged by its OWN role, so the issuer-demotion code is gone), `details.required` naming the permission and `details.held` the key's role — the call was dispatched and refused; nothing ran. The MCP ownership rules (`templates_purge_draft` on another person's draft, `executions_cancel` on a run another credential started) answer `auth.role_required` with `details.reason`. Codes follow the `{domain}.{entity}.{failure}` convention; the registry of record is [Pipeline Contract §13.7](/docs/pipeline-contract.md#137-authentication--authorization). ### 4.3 Why not OAuth OAuth adds: - Authorization server (to build/maintain) - Redirect flows (impossible for non-browser agents like Claude Desktop) - Token refresh logic (per agent) - Client registration (per agent) For self-hosted, internal-users-only deployment, API keys are simpler and sufficient. Future multi-tenant SaaS deployment would revisit this. --- ## 5. Server Metadata & Capabilities ### 5.1 `initialize` response ```json { "protocolVersion": "2025-06-18", "serverInfo": { "name": "datapipelines", "version": "1.0.0" }, "capabilities": { "tools": {"listChanged": false}, "resources": {"listChanged": false, "subscribe": false}, "prompts": {"listChanged": false} }, "instructions": "This server is workspace-scoped: every tool and resource operates inside the workspace the API key is pinned to. ..." } ``` - `instructions` (workspaces design §9) states the workspace context every agent reads first: content in other workspaces is absent (not hidden) — it resolves as not-found — and the key sees exactly the datasources granted to its workspace (D-R7). What follows is ranked for a client that truncates it (#241): where the manual lives, then the draft rule, the name grammar, "humans register datasources" and the three recoveries — all inside 1,843 characters, 10 % under the 2,048-character cap Claude Code applies by default. The full text is §15's Delivery 1 block and ships as `McpServerFactory.SERVER_INSTRUCTIONS`. - `tools.listChanged: false` — the tool surface is **static**: the same 61 tools (§6.1) for every caller, for the lifetime of the server. Advertising `true` would promise `notifications/tools/list_changed` messages the v1 server never sends. Dynamic per-pipeline tools (`pipeline_execute_{name}`, which would make the list genuinely mutable) are a v2 item — [ROADMAP §3.7](/docs/roadmap.md#37-mcp-server). When they land, this flips to `true` together with the notification implementation. - `resources.listChanged: false` — the *set of resource URIs* does change as pipelines and executions are created, but the v1 server sends no change notifications; clients re-fetch `resources/list` (§7.3) when they need a current view. - `resources.subscribe: false` — no live subscriptions in v1. Clients re-fetch resources as needed. - `prompts.listChanged: false` — the prompt surface (§8) is static in v1. - **No `logging` capability in v1.** The v1 transport is stateless (§3.3): it answers `GET /mcp` with `405`, so there is no server-to-client stream to carry `notifications/message`. Advertising `logging` would promise notifications no client can receive — the same reasoning as `listChanged: false`. Live progress during a blocking `pipelines_execute` (§6.2.3) is therefore not available in v1; the authoritative per-node record is the `node_stats` array in the tool's final result. Logging/progress notifications return with the stateful transport in v2 ([ROADMAP §3.7](/docs/roadmap.md#37-mcp-server)). `protocolVersion` is the placeholder pending the §3.1 implementation-gate check. `serverInfo.version` is the datapipelines.co release version. --- ## 6. Tool Surface ### 6.1 Tool naming convention Tools are named `{domain}_{action}`: - `pipelines_list` - `pipelines_get` - `pipelines_execute` - `pipelines_execute_node` - `pipelines_create` - `pipelines_update` - `templates_list` - `templates_get` - `templates_used_by` - `templates_create` - `templates_update` - `templates_render` - `templates_evaluate` - `templates_purge_draft` - `datasources_list` - `datasources_get` - `datasources_test` - `datasources_get_schemas` - `datasources_get_tables` - `datasources_get_columns` - `datasources_get_table_stats` - `datasources_preview_rows` - `sql_probe` - `executions_list` - `executions_get` - `executions_get_result` - `executions_cancel` - `calculators_list` - `calculators_get` - `endpoints_create` - `endpoints_list` - `endpoints_get` - `endpoints_delete` - `lake_tables_register` - `lake_tables_import` - `lake_tables_unregister` - `semantics_record` - `semantics_list` - `semantics_retire` - `docs_list` - `docs_get` - `pipelines_run_checks` - `parameter_sets_list` - `parameter_sets_get` - `parameter_sets_create` - `parameter_sets_update` - `parameter_sets_evaluate` - `parameter_sets_purge_draft` - `visualizations_list` - `visualizations_get` - `visualizations_create` - `visualizations_update` - `visualizations_purge_draft` - `visualizations_test_start` - `visualizations_test_submit` - `dashboards_list` - `dashboards_get` - `dashboards_create` - `dashboards_update` - `dashboards_purge_draft` - `dashboards_validate` A future enhancement: dynamically-generated per-pipeline tools (e.g., `pipeline_execute_monthly_revenue_report`) for pipelines the user wants to expose as named tools to agents. Marked for v2 ([ROADMAP §3.7](/docs/roadmap.md#37-mcp-server)) — this is why `tools.listChanged` is `false` in v1 (§5.1). ### 6.2 Tool definitions Every tool definition below carries a **Permission** row: the catalog permission the tool declares (`McpToolCatalog.Entry.permission`), which the calling key's ROLE must hold — the role chosen at creation ([Auth §7.5](/docs/auth.md#75-key-roles)). Those values are sourced from the [Auth §7.6 permission catalog](/docs/auth.md#76-operation-matrix--the-permission-catalog-authoritative), which is authoritative — if this doc and the catalog ever disagree, the catalog wins. Scopes were removed (#215): the role is the one axis, so a `promoter`- or `workspace_admin`-role key reaches farther than an `author`-role one — exactly as far as its catalog rows allow, and no farther. Every tool's result envelope, including its error shape, is §6.3. #### 6.2.1 `pipelines_list` List pipelines the caller has access to. ```json { "name": "pipelines_list", "description": "List the pipelines of the key's pinned workspace, filtered by owner, datasource, or text search. Returns metadata (id, name, display_name, description, version, status, updated_at) — version is the WORKING version and status says DRAFT or RELEASED, so an unreleased pipeline is visible as such. Not the full body. Use pipelines_get for the body; pipelines in other workspaces are absent from this listing and resolve as not-found by id. Pipeline names are FOLDER PATHS (finance/payments/daily_settlement): pass prefix to BROWSE one level of that tree — prefix:\"\" lists the roots, prefix:\"finance\" lists what is directly under finance — and q to SEARCH across full paths. Start with prefix:\"\" to see which roots this workspace already uses before creating a pipeline under a new one. A promoter's key sees only RELEASED pipelines newer than the promotion target's (the promoter lens); every other pipeline is absent for it and resolves as not-found by id.", "inputSchema": { "type": "object", "properties": { "owner": {"type": "string", "description": "Filter by owner user ID."}, "datasource": {"type": "string", "description": "Filter by datasource name."}, "q": {"type": "string", "description": "Case-insensitive substring search over name, display name and description (the whole path counts as the name); ignored when prefix is present — pass prefix to browse, q to search. At most 200 characters after trimming; a longer q is refused as invalid params."}, "prefix": {"type": "string", "description": "Browse ONE level of the folder tree instead of listing flat: returns that prefix's direct sub-folders (with counts) and its direct children. An empty string is the root. Use this to discover which roots and folders exist; use q to search across full paths."}, "limit": {"type": "integer", "default": 50, "maximum": 200} } } } ``` Returns: array of pipeline metadata objects. Datasource references are per-node and are read from the body via `pipelines_get` — the listing does not aggregate them. **`version` is the working version, and `status` names it** (D55/D56, 099): the draft's number when the pipeline has a draft, else the latest released one, with `status` = `DRAFT` or `RELEASED`. Since creation lands a DRAFT, a listing that reported the released pointer alone would show nothing at all for every freshly authored pipeline, and `version: 1` on its own could not tell a reviewed release from a draft nobody has looked at. Both fields are also `null` in the one case where a pipeline has no version at all — its sole draft was purged, which deletes the entity with it ([versioning §3.2](/docs/versioning.md#32-entity-status-is-derived-names-are-unique-forever)). **Two presentations, chosen by `prefix`** (067). Pipeline names are folder paths ([Pipeline Contract §3.2](/docs/pipeline-contract.md#32-field-reference), [Template Hierarchy §14](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)), so an agent needs to BROWSE as well as search: - **`prefix` absent** — the flat listing above, under `owner`/`datasource`/`q`. Unchanged. - **`prefix` present** (`""` is the ROOT) — **one level** of the tree, and the return shape is an object rather than an array: ```jsonc { "prefix": "nyc", "folders": [{"path": "nyc/mobility", "segment": "mobility", "pipeline_count": 6}], "pipelines": [ /* the level's own leaves, same metadata objects */ ], "total": 0, "has_more": false } ``` `folders` are the prefix's DIRECT sub-folders with their whole-subtree counts; `pipelines` are its direct children only. Never a subtree, never the whole list. `owner`, `datasource` and `q` are ignored while `prefix` is present — browse and search are different presentations. A prefix is a FOLDER PATH — 1 to 9 segments, not the 2-to-10 a NAME takes since 077 — so `nyc` browses; one that is not a legal folder path answers an empty level, not an error. **When to use which:** `prefix` to discover structure ("what roots exist? what is under `finance`?"), `q` to find something by name across full paths. Start a naming decision with `prefix: ""`. **Permission:** `pipeline.read`. #### 6.2.2 `pipelines_get` Fetch a full pipeline definition. ```json { "name": "pipelines_get", "description": "Get the full definition of a pipeline (the working version by default — the draft when unreleased edits exist, else the latest released version — or a specific version). Use this to read the pipeline body before executing or modifying it. The result carries the version, its status and body_hash — echo body_hash back as expected_hash on pipelines_update; a draft pointer is present when unreleased edits exist. When a node pins a template version that a newer released version outdates, an upgrade_available array names the node, the template and both versions — an offer to re-pin via pipelines_update, never an automatic change. A promoter's key sees only RELEASED pipelines newer than the promotion target's (the promoter lens); every other pipeline is absent for it and resolves as not-found by id.", "inputSchema": { "type": "object", "required": ["id"], "properties": { "id": {"type": "string", "format": "uuid", "description": "Pipeline ID."}, "version": {"type": "integer", "description": "Specific version. Defaults to the working version: the draft when one exists, else the latest released."} } } } ``` Returns: full pipeline JSON body (per [Pipeline Contract §3](/docs/pipeline-contract.md#3-top-level-pipeline-schema)) merged with the fields the version-lifecycle protocol needs (versioning §4.2, since 035): the version's `body_hash` and `status` — echo `body_hash` back as `expected_hash` on `pipelines_update` — plus `current_version` (the latest RELEASED version, what execute-default runs) and a `draft` pointer when unreleased edits exist. Since 039 the DEFAULT body is the **working version** (versioning §7): the DRAFT when one exists, else the latest released — an agent that read released while a draft was open would rebase on stale content and quietly discard the draft with its next write. The response always states which `version` and `status` it returned; an explicit `version` argument still wins. Since 078, the body's `parameters` also lists the pipeline's **derived execute inputs**: one entry per key a CALCULATOR node writes (121: every mapped `context_keys` value of a multi-output kind too) — `{"type": , "required": false, "derived": true}` — because a calculator key is an implicit optional input of `pipelines_execute` (pipeline-contract §4.10: supply it and the node is skipped). Declared parameters carry no `derived` flag; absence is the false. Derived on read, never stored — the entries must not be echoed back on `pipelines_update`. Since 040, the response also carries `upgrade_available` **whenever a node's pinned template has a newer RELEASED version** (040 D5): one `{node, template_id, pinned, latest_released}` row per outdating pin, computed from the very body being returned. Absent when no pin is outdated (omit-when-empty, the envelope convention). Surfaced, never applied — moving a pin is a pipeline edit (`pipelines_update`) and stays the caller's decision; a pin of a template DRAFT version is not an upgrade (the author is ahead of release, which is information, not a prompt). See [Templates §5.4](/docs/templates.md#54-used-by-the-reverse-arrow-v19-040). **Permission:** `pipeline.read`. #### 6.2.3 `pipelines_execute` Execute a pipeline. ```json { "name": "pipelines_execute", "description": "Execute a pipeline with the given input parameters. Returns execution events (node start/complete/fail) and the final result data. The result's schema describes column types; BIGINTEGER and BIGDECIMAL columns serialize as JSON strings — preserve them as strings when displaying or persisting to avoid precision loss. When the execution FAILS, the error result carries the full failure record (node context, rendered SQL, exception chain with the root cause last in caused_by) — the same object executions_get returns; quote its correlation_id when escalating. The server checks the loop: a DRAFT whose pinned draft template was updated after this key's last templates_render of it is refused pipeline.execution.template_unrendered — render, then run.", "inputSchema": { "type": "object", "required": ["id", "parameters"], "properties": { "id": {"type": "string", "format": "uuid"}, "version": {"type": "integer", "description": "Specific version to run. Defaults to the WORKING version: the draft when one exists, else the latest released. Never clamped — an unknown version is refused, not rounded to the latest."}, "parameters": { "type": "object", "description": "Object whose keys match the pipeline's declared parameters. Values must match the declared types (BIGINTEGER and BIGDECIMAL as strings, others as JSON native types).", "additionalProperties": true } } } } ``` Returns: an **execution result object** containing: - Execution metadata (`execution_id`, `pipeline_id`, `status`, `duration_ms`, `node_stats`). - Schema (array of column descriptors per [Type System §7](/docs/type-system.md#7-schema-envelope-structure)). - The **first page of rows** inline (up to `datapipelines.result.page-size-rows`), plus `total_rows`, `has_more`, `result_url`, and `expires_at`. - Warnings array (if any). The result shape mirrors the [REST `data_ready` event](/docs/rest-api.md#647-data_ready) exactly — same fields (`schema`, the inline `rows` first page, `row_count`, `total_rows`, `has_more`, `result_url`, `expires_at`, and **`ttl_seconds`** so the agent knows its paging window without diffing timestamps), same uniform delivery model. There is no inline-vs-claim-check split: every caller result is materialized in Redis before the tool returns ([REST API §7.1](/docs/rest-api.md#71-model)). For a result that fits in one page, the inline rows ARE the whole result and no follow-up call is needed; when `has_more` is `true`, page the remainder with `executions_get_result` (§6.2.15) within the TTL. A pipeline with **no caller node** ([Pipeline Contract §9](/docs/pipeline-contract.md#9-the-caller-node-result-node)) is legal — a pure write-back/ETL pipeline. Such an execution returns metadata, `node_stats`, and no `schema`/`rows`; this is success, not an error. **Long-running executions.** The tool call is a **single blocking request**: it returns when the execution reaches a terminal state (`SUCCESS`, `FAILED`, `ABORTED`) or when `datapipelines.executor.execution-timeout-seconds` (default 600) elapses and the execution is aborted. For a 3-minute pipeline, the agent experiences one tool call that takes ~3 minutes; the HTTP response for that call stays open for the duration and the server writes nothing to it until the result is ready. (The REST SSE heartbeat, [REST API §6.6](/docs/rest-api.md#66-heartbeat-keepalive), is an SSE-stream concept and does not apply here — an MCP tool call is not an event stream. Operators must therefore ensure proxy/load-balancer idle timeouts on `/mcp` exceed `execution-timeout-seconds`; see [Deployment](/docs/deployment.md).) MCP **progress notifications** for in-flight nodes are deliberately not implemented in v1 — the tool returns progress only as the final `node_stats`. Streaming execution events through the MCP transport is a v2 item ([ROADMAP §3.7](/docs/roadmap.md#37-mcp-server)). The v1 stateless transport delivers **no** server-to-client notifications of any kind (§5.1), so `node_stats` in the tool's final result is the authoritative and only per-node record. **If the agent abandons the call** (aborts the HTTP request, client crash): a blocking `POST /mcp` gives the servlet no disconnect callback, so the `datapipelines.sse.disconnect-grace-seconds` cancellation that a dropped **REST SSE** stream gets ([REST API §6.8](/docs/rest-api.md#68-client-disconnect)) does **not** apply to an abandoned tool call in v1 — the execution runs until it finishes or hits `datapipelines.executor.execution-timeout-seconds`. To stop an in-flight execution deterministically, cancel it: over MCP with `executions_cancel` (§6.2.35) when this key's own MCP calls started it, or out-of-band via `DELETE /api/v1/executions/{id}` ([REST API §10.4](/docs/rest-api.md#104-cancel-execution)) from any instance — in-flight statements are interrupted and the abandoned tool call returns an `ABORTED` result. There is no resumption path (a reconnecting agent must re-execute). **With no `version`, this runs the WORKING version** (D56, 099): the draft when one exists, else the latest release — "always run the LAST version". On a development server that may well be a draft (drafts have been executable since 039); on a hardened server `authoring-enabled=false` refuses every draft-creating write, so the working version is a RELEASED version by construction ([versioning §7.2](/docs/versioning.md#72-execute-with-no-version-runs-the-working-version-d56-099)). The execution record pins the version that actually ran and the executions screen marks a draft run, so a result is never ambiguous about what produced it. An explicit `version` is exact and never clamped. **Render before you run (139).** Executing a DRAFT version whose pinned template version is itself a DRAFT written after this key's last successful `templates_render` of it is refused — `pipeline.execution.template_unrendered`, with `details.templates` carrying each `{id, version, updated_at, last_render}`. RELEASED pins are exempt (they rendered before release and cannot change), and so is any execute of a RELEASED version: the check reads the caller's own audit rows, so it is an AGENT-surface rule — REST, the UI and a human's key keep their freedom. A `templates_update` therefore makes the next execute refuse again until a fresh render: the render-then-run loop the SKILL's steps 3 and 5 describe, enforced at the entry point instead of asked of the model a third time. `pipelines_execute_node` (§6.2.20) runs the same check on the one node it resolves. **Permission:** `pipeline.execute`. #### 6.2.4 `pipelines_create` Create a new pipeline. ```json { "name": "pipelines_create", "description": "Create a new pipeline. The body must satisfy the Pipeline Contract: nodes must form a DAG; at most one DQL node may resolve to output.target='caller' (a node that omits its output block resolves to 'caller' by default); zero caller nodes is legal for pure write-back pipelines; all datasource references must exist in this environment; all template references must exist and dry-render against the declared parameters. A node may also be type='CALCULATOR': it evaluates one catalog function and writes a typed value into the execution Context under context_key, which downstream nodes bind as :context_key — call calculators_list first for the kinds and their input names, and remember that a node referencing another node's context_key must depend_on it. A NEW top-level folder is refused until you confirm it: reuse an existing root, or ask the person first and then pass confirm_new_root: true. The server checks what you learned: a body whose template names a table this key never datasources_get_columns'd is refused pipeline.validation.table_not_learned with the clearing calls listed, and a raw-date door (two DATE parameters, no period parameter, no window calculator) is refused pipeline.validation.door_unacknowledged until you pass door_acknowledged: true. Returns the created pipeline with server-assigned id and version 1, which lands as a DRAFT: run it straight away, then STOP — a human releases it from the UI, and no tool releases anything.", "inputSchema": { "type": "object", "required": ["name", "display_name", "nodes"], "properties": { "name": {"type": "string", "pattern": "^[a-z0-9][a-z0-9_.-]{0,63}(/[a-z0-9][a-z0-9_.-]{0,63}){1,9}$", "description": "Machine name, and a FOLDER PATH: 2-10 lower-case '/'-separated segments (finance/payments/daily_settlement). A FOLDER IS REQUIRED — a bare 'daily_settlement' is refused with pipeline.validation.name_invalid and details.reason='folder_required'; put experiments under test/. The root segment says who owns it — list the existing roots with pipelines_list {prefix: ''} and reuse one; ASK before minting a new root. Keep a pipeline under the same prefix as the templates it uses. There is no rename: the name is the pipeline's identity, so choose the folder now."}, "display_name": {"type": "string"}, "description": {"type": "string"}, "parameters": {"type": "object", "description": "Declared pipeline parameters (name -> {type, required, default, description}). This is the ONLY parameter declaration point: the full parameter map, defaults applied, is the render context for every template the pipeline references."}, "settings": {"type": "object", "description": "Pipeline-level execution settings (e.g., tempdb engine)."}, "nodes": { "type": "array", "description": "Pipeline nodes. Each node has type (DQL/DML/DDL/PIPELINE), source, template ref, depends_on array, and — for DQL only — an optional output block. Omitting output on a DQL node means output.target='caller'; at most one node per pipeline may resolve to 'caller'. A node whose data downstream nodes query must declare output.target='tempdb' with a table name explicitly. A PIPELINE node instead carries a pipeline ref {name, version} pinning an existing pipeline version to execute as a child execution, an optional parameters map (typed literals, or '${parent_param}' to pass a parent parameter through), and an optional output block allowed only when the pinned child has a caller node; it declares neither source nor template." }, "checks": { "type": "array", "description": "Release checks (pipeline-contract §3.3): at most 20 objects, each {id, name, datasource, sql, expected} — id [a-z0-9_]{1,63} unique in the body, name 1-200 chars, sql ONE read-only statement. expected.kind is value (single numeric cell compared with absolute tolerance, default 0), range (the cell within min..max inclusive), or rows (the statement's row count equals rows). Every :name bind must name a DECLARED pipeline parameter (the calculator context is not available to a check); ${} interpolation is refused (a check has no rendering); tempdb is not a check datasource. You supply the query and the expectation, never an observed value — run them with pipelines_run_checks, and only the server's run produces observed." }, "confirm_new_root": {"type": "boolean", "description": "Set true ONLY after a person has agreed to a new top-level folder. A name whose root segment has no pipelines or templates under it yet is refused with details.existing_roots listing the roots that do exist — reuse one of those, or ask the person first and then pass this. 'test/' never needs it."}, "door_acknowledged": {"type": "boolean", "description": "Set true ONLY when the question truly fixes two dates. A pipeline whose parameters are two raw DATE inputs with no period parameter (year, quarter, month, *_year) and no window CALCULATOR node is refused pipeline.validation.door_unacknowledged — the door is a decision: prefer the period vocabulary of rule 13, and never pass this to silence the refusal."} }, "additionalProperties": false } } ``` Returns: created pipeline — `id`, `version: 1`, **`status: "DRAFT"`**, `body_hash` (carry it into your next `pipelines_update`), `current_version: null` (nothing released yet) and the `draft` pointer. **Creation lands a DRAFT (D55, 099).** `POST /pipelines` used to land version 1 RELEASED so that an MCP-authored pipeline was immediately executable; drafts have been executable since 039, so that justification bought nothing and cost a review — an agent following the old rule produced a released pipeline no human had looked at. The golden path for an agent is therefore: create → execute (no `version`, which runs your draft) → read the result → **stop and tell the person it is ready to review**. Releasing is a human action in the UI; there is no release tool and there will not be one (versioning D4). **A new ROOT folder needs the person's say-so (094).** The root segment says who owns a thing and there is no rename, so this tool REFUSES a name whose first segment has nothing under it yet — `pipeline.validation.new_root_requires_confirmation`, with `details.root` and `details.existing_roots` (the same one-level query `pipelines_list {"prefix": ""}` serves). Reuse one of those roots, or ask the person and retry with `confirm_new_root: true`. `test/` is always allowed. This is an AGENT-surface rule only: REST, the UI and `pipelines_update` are unaffected — a person choosing a folder in a form has already decided, and an update cannot change a name. It replaces an INSTRUCTION with a GUARANTEE: the schema and the SKILL already told an agent to list the roots and ask, and a model that did not, did not. **Learn before you write — the server now checks it (139).** A body whose template names a table THIS API key never read the columns of is refused — `pipeline.validation.table_not_learned`, with `details.tables` listing each `{datasource, table}` and the one `datasources_get_columns` call that clears it. The check reads the caller's own `mcp.tool.called` audit rows (any time the audit log still holds the row — a key learns a table once per [`datapipelines.audit.retention-days`](/docs/configuration.md#312-audit), default a year, since the rows it reads age out with the log ([Auth §10.3](/docs/auth.md#103-retention)); rows written before 139 carry no `table` and simply do not count), tokenises each pinned template's body the way the semantics fact check does, and compares against the source datasource's catalog listing. `tempdb` sources, `${…}` dynamic names and names the catalog does not list are exempt; `_get_table_stats` is NOT required — columns are correctness, stats are performance. Run it at `pipelines_create` and `pipelines_update`, because only there is the node's datasource known; the template tools themselves are unchecked (a template has a dialect, never a datasource). REST and the UI are unaffected. **The door is a decision (139).** A body whose parameters contain two DATE parameters with no INTEGER period parameter (`year`, `quarter`, `month`, `*_year`) and no CALCULATOR node whose kind writes a DATE — a raw-date door — is refused `pipeline.validation.door_unacknowledged`, naming the two parameters and rule 13's alternatives (a period parameter, or an anchor date with a `period_bounds` / `trailing_periods` node), unless the call carries `door_acknowledged: true`. The flag is the `confirm_new_root` shape: it forces the decision instead of a copy, and passing it to silence the refusal is precisely the miss it exists to catch. REST and the UI are unaffected. The whole pipeline is validated before it is stored — no invalid pipeline ever reaches the database ([Pipeline Contract §2](/docs/pipeline-contract.md#2-design-principles)). Validation failures come back as a tool result with `isError: true` carrying the pipeline validation code (§9.2); the agent should fix and retry rather than assume partial creation. **Permission:** `pipeline.create`. #### 6.2.5 `pipelines_update` Update an existing pipeline by writing its DRAFT (versioning §3.2/§7, since 035). The tool's shipped description (the one block of §6.2 with no `inputSchema` — the input is the prose below; `McpToolSurfaceSpecDriftTest` pins the string, #235): ```json { "name": "pipelines_update", "description": "Update an existing pipeline by writing its DRAFT — the first update after a release creates the draft (copy-on-write); later updates overwrite that same draft in place. Requires expected_hash: the body_hash you read (pipelines_get, or a previous update's result) for the version you based your edit on. The result carries status='DRAFT' — your work is NOT released; a human releases it from the UI. On pipeline.version.conflict someone modified it after you loaded it: re-read, rebase, retry; never retry blindly. The body takes the same node types as pipelines_create, CALCULATOR included (calculators_list has the kinds); no extra arguments are needed for one. The same entry-point checks apply: a body naming a table this key never datasources_get_columns'd is refused pipeline.validation.table_not_learned, and a raw-date door is refused pipeline.validation.door_unacknowledged until door_acknowledged: true." } ``` Same input as `pipelines_create` plus required `id` and required `expected_hash` — the `body_hash` of the version this edit is based on (`pipelines_get` or the previous update's result). The first update after a release creates the draft (copy-on-write); later updates overwrite that same draft in place. Same save-time validation applies — **and the same 139 entry-point checks**: a body naming a table this key never read the columns of is refused `pipeline.validation.table_not_learned`, and a raw-date door is refused `pipeline.validation.door_unacknowledged` until `door_acknowledged: true` (an update can change a body's parameters, unlike its name, so the door check belongs here too). That input includes the optional `checks[]` (§6.2.4's `checks` property, pipeline-contract §3.3) — the release checks the SERVER runs; run them with `pipelines_run_checks` (§6.2.42), and only the server's own run produces `observed`. Returns: the draft version — `version`, `status: "DRAFT"`, `body_hash` (carry this into the next write), `current_version` (the unmoved released pointer), and the `draft` pointer. **The update does NOT release**: an agent leaves the draft for a human to review and release from the UI (versioning D4). On `pipeline.version.conflict` someone else modified it after you loaded it — re-read, rebase, retry; never retry blindly. **Permission:** `pipeline.update`. #### 6.2.6 `templates_list` List templates. ```json { "name": "templates_list", "description": "List the templates of the key's pinned workspace. Templates are reusable generators referenced by id+version — sql/html bodies are authored in Freemarker, script bodies are script expressions; each has a fixed type — 'sql' renders SQL for pipeline nodes (and carries a dialect), 'html' renders escaped output and declares none, and the script types ('jsonata', 'javascript') evaluate the body as a pure function of its input and declare neither. Template ids are unique per workspace — another workspace's template resolves as not-found. A script row carries implements (the learned facts it cites) and needs_review (true when a cited fact was retired); implements= lists the scripts that implement that fact — reuse before writing. A promoter's key sees only RELEASED templates newer than the promotion target's (the promoter lens); every other template resolves as not-found.", "inputSchema": { "type": "object", "properties": { "dialect": {"type": "string", "enum": ["POSTGRES", "ORACLE", "MSSQL", "MYSQL", "H2", "DUCKDB", "SQLITE", "LAKE"]}, "type": {"type": "string", "enum": ["sql", "html", "jsonata", "javascript"], "description": "Filter by template kind: 'sql' (pipeline-referenced SQL), 'html' (rendered output), or a script type ('jsonata', 'javascript' — a pure function over its input, never rendered)."}, "q": {"type": "string", "description": "Case-insensitive substring search over name and display name (the whole path counts as the name); ignored when prefix is present — pass prefix to browse, q to search. At most 200 characters after trimming; a longer q is refused as invalid params."}, "prefix": {"type": "string", "description": "Browse ONE level of the folder tree instead of listing flat: returns that prefix's direct sub-folders (with counts) and its direct children. An empty string is the root. Use this to discover which roots and folders exist; use q to search across full paths."}, "is_library": {"type": "boolean", "description": "Filter to library templates (macro collections) or executable templates."}, "implements": {"type": "string", "format": "uuid", "description": "A learned fact id: only templates whose listed version cites it (implements) — the scripts that implement a workspace definition, exclusion or preference. A fact this workspace cannot see matches nothing. Ignored while prefix is present, like q."}, "limit": {"type": "integer", "default": 50, "maximum": 200} } } } ``` Returns: array of template metadata (`id`, `version`, `type`, `dialect`, `display_name`, `description`, `is_library`, `implements`, `needs_review`; `dialect` is null for `html` templates, since 046; `implements` is null on `sql`/`html` and the version's cited learned-fact ids on a script, since 7e). A template's `description` is the only place it can hint at the parameters it expects — templates declare none ([Templates §3.2](/docs/templates.md#32-field-reference)). **`implements=` — discovery (7e, [Templates §3.4](/docs/templates.md#34-implements-and-drift)).** Keeps the templates whose LISTED version (the same row the flat listing returns — the current release, or the draft of a never-released template) cites that learned fact: the scripts that implement a workspace `definition`, `exclusion` or `preference`. Reuse one before writing your own. The filter is a parameterised join through the template's own workspace, so an id another workspace owns — or no fact at all — matches nothing (the read floor's not-found semantics); a malformed id is `-32602`. Like `q`, it is ignored while `prefix` is present. The reverse question — which versions implement THIS fact — is `implemented_by` on [`semantics_list`](#6238-semantics_list) and on the `definitions` of `datasources_list`/`datasources_get`. `needs_review` is `true` when a cited fact has been retired (computed on read, never stored — §6.2.7). **Two presentations, chosen by `prefix`** (067). Template ids have been paths since 043 and the templates browser has rendered them as a tree since 047, but this tool had no way to browse a folder at all until now: - **`prefix` absent** — the flat listing above. Unchanged. - **`prefix` present** (`""` is the ROOT) — **one level**, the same shape `pipelines_list` returns with `prefix`, keyed `templates` instead of `pipelines` and `template_count` instead of `pipeline_count`: ```jsonc { "prefix": "nyc", "folders": [{"path": "nyc/mobility", "segment": "mobility", "template_count": 7}], "templates": [ /* the level's own leaves, same metadata objects */ ], "total": 0, "has_more": false } ``` `dialect` and `type` still narrow both halves, so a folder whose whole subtree is filtered out is absent rather than empty. `is_library` narrows the level's leaves only — a folder count is over the whole subtree, and quietly subtracting library templates from it would make the tree disagree with what expanding the folder shows. `q` is ignored while `prefix` is present. **When to use which:** `prefix` to browse, `q` to search. A pipeline and the templates it uses should share a prefix — see [Template Hierarchy §15](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md). **Permission:** `template.read`. #### 6.2.7 `templates_get` Fetch a template body. ```json { "name": "templates_get", "description": "Get the body and metadata of a template version, including its imports array (the library macros it can call). Defaults to the working version — the draft when unreleased edits exist, else the latest released. A script version carries implements (the learned facts it cites) and needs_review: true when a cited fact was retired — retired_facts names each one and its superseded_by; re-verify, then cite the successor with templates_update. A promoter's key sees only RELEASED templates newer than the promotion target's (the promoter lens); every other template resolves as not-found.", "inputSchema": { "type": "object", "required": ["id"], "properties": { "id": {"type": "string"}, "version": {"type": "integer", "description": "Specific version. Defaults to the working version: the draft when one exists, else the latest released."} } } } ``` **Permission:** `template.read`. The returned projection states its `version` and `status` — since 039 the default is the **working version** (versioning §7: the DRAFT when one exists, else the latest released), the template mirror of `pipelines_get`. **The semantic link on read (7e, [Templates §3.4](/docs/templates.md#34-implements-and-drift)).** A script version carries `implements` — the learned-fact ids it cites, sorted (`[]` when none; null on `sql`/`html`) — and every version carries `needs_review`: `true` when any cited fact has been RETIRED (a superseded fact is a retired one), computed on read by the version's own query and never stored. When it is true, `retired_facts` names each retired citation as `{fact_id, retired_reason, superseded_by}` — `superseded_by` is the fact that replaced it (null for a plain retirement); `retired_facts` is omitted when empty. The version is served marked, never edited and never re-mapped: re-verify, then clear it deliberately with `templates_update` citing the successor (or dropping the citation) — on a released version that write opens no draft (§6.2.36). A pipeline release pinning a `needs_review` version is not refused; its response carries a `pipeline.release.template_needs_review` warning ([Pipeline Contract §13.13](/docs/pipeline-contract.md#1313-versioning--draft-release-lifecycle--promotion)). #### 6.2.8 `templates_create` Create a new template. ```json { "name": "templates_create", "description": "Create a new template. Templates use Freemarker syntax. A template declares NO parameters of its own: the variables its body may reference are exactly the parameters declared by the pipeline that calls it, with defaults applied. Describe the variables you expect in 'description' — that free text is how humans and agents discover them. Macros from library templates are made available by listing them in 'imports'; the body must NOT contain import or include directives, they are synthesized from the imports array. The 'type' is chosen here and never changes afterwards: 'sql' (default) requires a dialect and is what pipeline nodes reference; 'html' takes no dialect and renders through an auto-escaping engine. A NEW top-level folder is refused until you confirm it: reuse an existing root, or ask the person first and then pass confirm_new_root: true. Version 1 lands as a DRAFT: a pipeline draft may pin it and render against it while you iterate, and a human releases it from the UI — a RELEASED pipeline may only pin RELEASED template versions, so the template is released first.", "inputSchema": { "type": "object", "required": ["display_name", "description", "body"], "properties": { "id": {"type": "string", "pattern": "^[a-z0-9][a-z0-9_.-]{0,63}(/[a-z0-9][a-z0-9_.-]{0,63}){1,9}$", "description": "Template id, and a FOLDER PATH: 2-10 lower-case '/'-separated segments (acme/finance/daily_orders.sql). A FOLDER IS REQUIRED — a bare 'daily_orders.sql' is refused with template.validation.id_invalid and details.reason='folder_required'; put experiments under test/, and shared macros under /lib/. Keep a template under the same prefix as the pipelines that read it. Optional; auto-generated if omitted. There is no rename, so choose the folder now."}, "engine": {"type": "string", "enum": ["freemarker", "none"], "default": "freemarker", "description": "Template engine, matched to the type: 'freemarker' for sql/html, 'none' for the script types ('jsonata'/'javascript' — the body is evaluated, never rendered). Any other pairing is refused with template.validation.engine_unsupported."}, "type": {"type": "string", "enum": ["sql", "html", "jsonata", "javascript"], "default": "sql", "description": "Template kind, fixed at creation and identical on every version: 'sql' renders SQL for pipeline nodes (requires 'dialect'); 'html' renders HTML through an auto-escaping engine (must have NO 'dialect'); 'jsonata' and 'javascript' are script types — a JSONata expression or the statements of main(input), evaluated as a pure function of its input, engine is 'none', dialect/imports/is_library are refused, and contract/invariants/tests blocks are required. JavaScript requires the installed isolate; otherwise save refuses with script.js.unavailable."}, "dialect": {"type": "string", "enum": ["POSTGRES", "ORACLE", "MSSQL", "MYSQL", "H2", "DUCKDB", "SQLITE", "LAKE"], "description": "SQL execution target. Required when type is 'sql' (the default); forbidden otherwise — html and the script types declare no dialect."}, "display_name": {"type": "string"}, "description": {"type": "string", "description": "Free text. State the variables the body expects and their types — the template declares none."}, "imports": { "type": "array", "description": "Library templates whose macros this body calls. Aliases must be unique within the template; each referenced template must exist at that exact version and be is_library=true.", "items": { "type": "object", "required": ["id", "version", "alias"], "properties": { "id": {"type": "string"}, "version": {"type": "integer"}, "alias": {"type": "string", "description": "Namespace the macros are bound to, e.g. 'dates' → <@dates.date_range .../>."} }, "additionalProperties": false } }, "is_library": {"type": "boolean", "default": false, "description": "true if this template exists to be imported by others. A library body contains only <#macro>/<#function> definitions — no output outside macro definitions. body is still required."}, "body": {"type": "string", "description": "Template source. Must not contain <#import> or <#include>."}, "contract": {"type": "object", "description": "Script contract (script types only — refused on sql/html with template.blocks_not_allowed): { mode: 'row'|'table'|'value', inputs: { name: { kind: 'table', columns: [{name, type, precision?, scale?, nullable?}] } or { kind: 'value', type, precision?, scale? } }, output: { kind: 'table'|'value'|'object', ... }, rejects?: boolean }. Types are LogicalType wire names; a row-mode contract requires exactly one table input."}, "invariants": {"type": "array", "description": "Script invariants: [{ name, expr, message }] — JSONata over { rows, rejects, inputs }, must be true on every test case and every real execution. May be empty but is required on a script type."}, "tests": {"type": "array", "description": "Script test cases: [{ name, input: { rows?, inputs?, meta?, now? }, expect: { output } or { refusal } }] — non-empty, at least one case whose every table input and rows are empty, expect is exactly one of output/refusal. Save runs the suite; release re-runs it."}, "implements": {"type": "array", "items": {"type": "string", "format": "uuid"}, "description": "Script types only (refused on sql/html with template.blocks_not_allowed): the learned facts this script implements — ids of WORKSPACE facts (definition, exclusion, preference) recorded in this workspace; semantics_list and the definitions on datasources_list show them. A citation is not content: it is outside body_hash. An id this workspace cannot cite is template.implements_unresolved. At most 50."}, "confirm_new_root": {"type": "boolean", "description": "Set true ONLY after a person has agreed to a new top-level folder. A name whose root segment has no pipelines or templates under it yet is refused with details.existing_roots listing the roots that do exist — reuse one of those, or ask the person first and then pass this. 'test/' never needs it."} }, "additionalProperties": false } } ``` **A new ROOT folder needs the person's say-so (094).** Same rule as [§6.2.4](#624-pipelines_create), same `confirm_new_root` argument, same `details.root` / `details.existing_roots` shape — the code is `template.validation.new_root_requires_confirmation` and the roots come from `templates_list {"prefix": ""}`. An OMITTED `id` is generated under `test/` and needs no confirmation. Save-time validation is **parse-only** — syntax, forbidden constructs, import resolution, and the type/dialect consistency rules (`sql` requires `dialect`, `html` forbids it; a payload trying to change an existing template's `type` is refused — [Templates §7.1](/docs/templates.md#71-save-time-validation-is-parse-only)). A template is never rendered against a sample context at save time, because it does not know its callers' parameters; the dry-render check happens when a *pipeline* referencing it is saved ([Templates §7.2](/docs/templates.md#72-the-dry-render-rule-owned-by-pipeline-validation)). An agent authoring a template should therefore call `templates_render` (§6.2.9) with a representative context to confirm the output it produces. **`implements` (7e, [Templates §3.4](/docs/templates.md#34-implements-and-drift)).** A script may cite the learned facts it implements — ids of WORKSPACE facts (`definition`, `exclusion`, `preference`) recorded in this workspace, at most 50. Each id must resolve to such a fact visible from the workspace, else `template.implements_unresolved` (400, `details.fact_id` + `details.reason` = `unknown` | `malformed` | `too_many` — one `unknown` answer for "no such fact", "another workspace's" and "a DATASOURCE fact", never "it exists but you cannot see it"); on `sql`/`html` the field is refused with `template.blocks_not_allowed`. A citation is not content: it is outside `body_hash`. The result is the stored projection, `implements` included. **Permission:** `template.create`. #### 6.2.9 `templates_render` Render a template against a supplied context (preview SQL). ```json { "name": "templates_render", "description": "Render a template against the provided context values and return the SQL it produces. Use this to preview generated SQL before creating a pipeline that references the template. The context is a free-form map: supply the same keys the calling pipeline would declare as parameters. Referencing a key absent from the context fails the render — that is the same failure a pipeline save would report.", "inputSchema": { "type": "object", "required": ["id", "context"], "properties": { "id": {"type": "string"}, "version": {"type": "integer", "description": "Defaults to latest."}, "context": { "type": "object", "description": "Render context: the parameter map a calling pipeline would provide, defaults already applied. Values follow the wire conventions of the Type System (BIGINTEGER/BIGDECIMAL as strings, TIMESTAMP with Z or offset).", "additionalProperties": true } }, "additionalProperties": false } } ``` Returns: rendered SQL string. This is a preview only — nothing is executed and nothing is stored. **Permission:** `template.render` (it is the authoring loop's preview step; see the Auth §7.6 matrix). #### 6.2.10 `datasources_list` List registered datasources (without credentials). ```json { "name": "datasources_list", "description": "List the datasources GRANTED to the key's pinned workspace. Visibility is the grant: a datasource registered elsewhere and not granted to this workspace is ABSENT, not hidden, and there is no such thing as a global datasource. This is the learn-first read: every entry carries name, dialect, description, the readonly flag, the workspace that REGISTERED it (omitted for an instance-level one), granted:true, connection metadata — never passwords — AND `facts`, the datasource-wide learned facts earlier sessions recorded (its time window, whether it is a sample), each with `trust` and evidence — read them before you assume coverage — AND `definitions`, this workspace's rules (definition, exclusion, preference) earlier pipelines chose on this datasource: a definition is a rule an earlier pipeline chose; read it before you choose yours, and reuse or supersede it, never re-choose. `datasources_get` is the same payload for ONE datasource — the refresh to call after recording facts with semantics_record.", "inputSchema": { "type": "object", "properties": { "dialect": {"type": "string"} } } } ``` **Permission:** `datasource.read`. (Registering, editing and deleting a datasource are UI/REST-only — [§6.2.22](#6222-removed--no-datasource-writes-on-this-surface): no credential travels through an agent.) The listing is scoped to the key's pinned workspace exactly like REST §9.2. **Returns:** one entry per granted datasource in the §6.2.11 per-datasource shape, `facts` **included** (126): the datasource-wide LEARNED FACTS (kinds `window` and `sampling`, the [learned-semantic-layer design](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-11-learned-semantic-layer-design.md) §7.2) in the §7A.5 fact shape — `[]` when none are recorded, an empty array never an absent key, so "nothing recorded" reads differently from "not served". Served as stored: the listing opens no connection, so there is nothing to recompute drift against (the 118 rule), and one enrichment read runs per listed datasource. **`definitions` included** (136 §A / T287): every WORKSPACE-scope fact (`definition`, `exclusion`, `preference`) visible to the reader that names this datasource — with refs on its tables or columns, or with none (§6.2.37, a rule that spans datasources) — in the same §7A.5 fact shape, newest last, `[]` when none; **a definition is a rule an earlier pipeline chose; read it before you choose yours**. `facts` keeps its datasource-wide meaning (`window`, `sampling`) — a rule is never in both. Both blocks come from the one enrichment read. **Every rule under `definitions` carries `implemented_by`** (7e, [Templates §3.4](/docs/templates.md#34-implements-and-drift)): `[{template_id, version}]`, the template versions that cite it as the script implementing it — `[]` when none — filtered by the key's template lens (a promoter's key lists RELEASED versions of the templates it may read). The `dialect` filter behaves as before. #### 6.2.11 `datasources_get` Fetch a single datasource (without password). ```json { "name": "datasources_get", "description": "Get metadata for a single datasource GRANTED to the key's pinned workspace: name, dialect, JDBC URL, the workspace that REGISTERED it (omitted for an instance-level one), granted:true, readonly flag, pool settings, `facts` — the datasource-wide learned facts agents recorded (its time window, whether it is a sample) — read them before you assume coverage — and `definitions`, this workspace's rules (definition, exclusion, preference) earlier pipelines chose on this datasource: a definition is a rule an earlier pipeline chose; read it before you choose yours. Credentials are never returned. A datasource that is not granted to this workspace resolves as not-found — the same answer a name that exists nowhere gets, so nothing about it can be probed.", "inputSchema": { "type": "object", "required": ["name"], "properties": { "name": {"type": "string"} } } } ``` **Permission:** `datasource.read`. **Returns:** `name`, `display_name`, `description`, `dialect`, `jdbc_url`, `username`, `query_timeout_seconds`, `pool` (the hikari map), `readonly` (boolean — the §5.7 flag, machine-readable so an agent can see BEFORE authoring that DML/DDL/output-datasource uses will be refused), `granted` (always `true` — you are seeing this row because your workspace holds a grant on it, D-R7), `workspace` (the name of the workspace that REGISTERED it, **omitted** when a super admin registered it at the instance level — it is never `null`, because the retired `null = global` reading would be the wrong answer to "who can see this?"; visibility is `granted`) — plus `introspection_include_schemas` ([Datasources §3.3](/docs/datasources.md#33-field-reference)) **when the allowlist is non-empty** (omitted when empty, the same envelope convention as REST §3.2), so an agent debugging why a schema is or isn't visible in the §6.2.16–18 introspection tools can see that an allowlist is active. Credentials are never returned. `datasources_list` (§6.2.10) emits the same per-datasource shape, `facts` included (126 — the listing is the learn-first read, so the facts ride the call every agent already makes): the datasource-wide LEARNED FACTS (kinds `window` and `sampling`, the [learned-semantic-layer design](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-11-learned-semantic-layer-design.md) §7.2), an array in the §7A.5 fact shape below, `[]` when none; served as stored, because this read opens no connection and has nothing to recompute drift against — and `definitions` (136 §A), the same block §6.2.10 describes: this workspace's rules (`definition`, `exclusion`, `preference`) on this datasource, newest last, `[]` when none; a definition is a rule an earlier pipeline chose; read it before you choose yours. #### 6.2.12 `datasources_test` Test that a datasource connection can be established. ```json { "name": "datasources_test", "description": "Test connectivity to a datasource. Returns success/failure and server version on success. Useful for diagnosing pipeline connection errors.", "inputSchema": { "type": "object", "required": ["name"], "properties": { "name": {"type": "string"} } } } ``` Returns: `{connected: bool, server_version: string?, error: string?}`. **Permission:** `datasource.test` — testing a connection opens a real pool against a production database; the test follows execute (ratified 2026-09-20), so every role that executes holds it (C1) and the promoter does not. #### 6.2.13 `executions_list` List recent executions. ```json { "name": "executions_list", "description": "List recent pipeline executions of the key's pinned workspace, optionally filtered by pipeline or status. The key acts as its own role: you see the runs you fired, and every run a schedule fired (a scheduled run is nobody's own — it ran as the system identity), plus every run of the workspace when the key's role holds execution.read_all (a workspace-admin-role key). Other members' own interactive runs are not listed.", "inputSchema": { "type": "object", "properties": { "pipeline_id": {"type": "string", "format": "uuid"}, "status": {"type": "string", "enum": ["RUNNING", "SUCCESS", "FAILED", "ABORTED"]}, "limit": {"type": "integer", "default": 50, "maximum": 200} } } } ``` **Permission:** `execution.read`. **Scheduled runs (#9 R3, #250):** the list reads the same `findVisible` predicate the REST listing does (rest-api §10.1) — own runs plus every `triggered_via = SCHEDULE` run of the workspace, in SQL, before the page is cut; `executions_get` and `executions_get_result` answer for a scheduled run the same way. The tool's own role decides (keys v2). #### 6.2.14 `executions_get` Fetch metadata for a specific execution (no rows). ```json { "name": "executions_get", "description": "Get metadata for a specific execution: status, timing, node_stats, parameters used. On a FAILED execution, error carries the full failure record: code, message, correlation_id, node context (datasource, dialect, pinned template), the rendered SQL (:name form, no bound values) and the exception chain with stack frames — read error.code first, then error.exception.caused_by (root cause LAST), then error.sql; quote error.correlation_id when escalating. To get the result rows, use executions_get_result. Visible for YOUR OWN runs (this key's own), every run a schedule fired, or any run of the workspace when the key's role holds execution.read_all; another member's own interactive execution is not found.", "inputSchema": { "type": "object", "required": ["execution_id"], "properties": { "execution_id": {"type": "string", "format": "uuid"} } } } ``` **Permission:** `execution.read`. **Response (057, on a `FAILED` execution):** `error` is the full failure record — the same object the SSE stream carried and `error_json` stores. (Plain fence: an example, not a tool definition — §6.2's `json` fences are exactly the input schemas `McpToolSurfaceSpecDriftTest` pins.) ``` { "status": "FAILED", "failed_node_id": "stage_daily_trips", "error": { "code": "pipeline.node.datasource_connection_failed", "message": "Failed to initialize pool", "details": {"phase": "connect", "node_id": "stage_daily_trips"}, "correlation_id": "1b0e6a52-…", "node": {"id": "stage_daily_trips", "type": "DQL", "datasource": "sample-trips", "dialect": "POSTGRES", "template": "nyc/mobility/sample_trips_daily.sql", "template_version": 1}, "sql": "SELECT * FROM trips WHERE borough = :borough", "exception": { "class": "java.lang.RuntimeException", "message": "Failed to initialize pool", "frames": ["…capped at 40 per level…"], "caused_by": [ {"class": "org.postgresql.util.PSQLException", "message": "FATAL: password authentication failed for user \"dp_demo_ro\"", "frames": ["…"]} ] } } } ``` Read `error.code` first, then `error.exception.caused_by` (outermost first — the **root cause is the LAST entry**), then `error.sql`. Quote `error.correlation_id` when escalating to a human; it is the field that joins the page to the server log. Under `datapipelines.executions.error-detail=structured` (Configuration §3.11) the `exception` and `sql` keys are absent; everything else stays. `pipelines_execute`'s failure result (§6.3) carries the same record inline, so a failed run's error needs no second call. #### 6.2.15 `executions_get_result` Fetch result rows (paginated) for a completed execution. ```json { "name": "executions_get_result", "description": "Fetch result rows for a completed execution, paginated via offset+limit. Returns schema + rows + pagination metadata. Works for ANY completed execution that produced a caller result, of any size, until its TTL expires (default 300s, set at execution time). Order is stable across pages. Reading pages does NOT extend the TTL — after expiry the result is gone and the pipeline must be re-run. Readable for YOUR OWN runs (this key's own), every run a schedule fired, or any run of the workspace when the key's role holds execution.read_all; another member's own interactive execution is not found.", "inputSchema": { "type": "object", "required": ["execution_id"], "properties": { "execution_id": {"type": "string", "format": "uuid"}, "offset": {"type": "integer", "default": 0, "minimum": 0}, "limit": {"type": "integer", "default": 1000, "minimum": 1, "maximum": 100000, "description": "Rows per page. Defaults to the server's result page size."}, "format": {"type": "string", "enum": ["json", "arrow", "csv"], "default": "json"} }, "additionalProperties": false } } ``` This tool is a thin adapter over the REST cursor, [REST API §7](/docs/rest-api.md#7-result-delivery) — **identical semantics, identical guarantees**: - `offset` / `limit` / `format` map one-to-one onto the cursor's query parameters. `offset` + `limit` paging over a result fully materialized in Redis before the cursor exists, so ordering is stable across pages. - Availability is uniform: every completed execution with a caller node has its result stored, regardless of size. There is no inline-vs-claim-check distinction to reason about (that split was removed in REST API v1.3). - TTL is fixed at result-write time (`datapipelines.result.ttl-default-seconds`, clamped between the min/max keys; a client may request one on the *execute* call via `DP-Result-TTL-Seconds`). Page reads never extend it. - Auth: `execution.result.read` **plus ownership** of the execution — `execution.read_all` (a workspace admin) may read any. Same rule as the REST cursor; the `result_url` is not a capability URL. **JSON format** returns `{schema, rows, row_count, offset, limit, total_rows, has_more, expires_at}` — same body as [REST §7.3](/docs/rest-api.md#73-response-json-format-default). **Binary columns and non-JSON formats.** `BINARY` column values in JSON results are base64 per the Type System's egress rules. For `format: "arrow"` or `"csv"`, and for any result whose encoded payload would exceed **1 MB**, the tool does **not** inline the bytes: it returns `{"result_url": "...", "expires_at": "...", "format": "...", "total_rows": N, "reason": "payload_exceeds_inline_cap"}` — the REST cursor URL, which the agent fetches with the same API key. Rationale: MCP tool results are model context; megabytes of base64 in a tool result poison an agent's window for no benefit. Payloads at or under the cap are inlined as base64 with their content type named. **Errors** mirror [REST §7.6](/docs/rest-api.md#76-endpoint-errors) exactly, returned as tool results with `isError: true` (§9.2) — registry of record [Pipeline Contract §13.10](/docs/pipeline-contract.md#1310-result-retrieval): | Code | Meaning for the agent | |---|---| | `result.execution_not_found` | Unknown execution id — check `executions_list`. | | `result.execution_incomplete` | Still running; wait or re-check with `executions_get`. | | `result.execution_failed` | The execution failed; there is no result. Use `executions_get` for the failure. | | `result.expired` | TTL elapsed. Re-run the pipeline — the result is unrecoverable. | | `result.format_unsupported` | Unknown `format` value. | **Permission:** `execution.result.read` (+ ownership). #### 6.2.16 `datasources_get_schemas` List a datasource's schemas — the entry point of the introspection flow. ```json { "name": "datasources_get_schemas", "description": "List the namespaces of a registered datasource by reading its live JDBC metadata, excluding the engine's own system schemas. The entry point of schema discovery: call this first, then get_tables(namespace), then get_columns for only the tables the SQL needs. Each entry carries an ordered `namespace` path and a `label`; on a two-level engine (catalog.schema, project.dataset) two entries can share a label and differ only by their outer segment, so pass the whole `namespace` back rather than the label. `schemas` repeats the labels for older clients. An empty list on a datasource with no namespaces is a valid answer. Read-only, for pipeline authoring.", "inputSchema": { "type": "object", "required": ["name"], "properties": { "name": {"type": "string", "description": "Datasource name."} } } } ``` Returns: `{"schemas": ["label", ...], "entries": [{"namespace": [...], "label": "..."}], "truncated": bool}` — the namespaces exactly as the driver reported them, as a page. **Read `entries`**: `namespace` is the ordered path (outermost first) to pass back to `datasources_get_tables`/`_get_columns`, and `label` is its last segment. On a two-level engine (`catalog.schema`, `project.dataset`) two entries can share a label and differ only by their outer segment, which is what the array form exists for; `schemas` repeats the labels for pre-087 clients and is kept for one release. `truncated: true` means the 2000-entry cap dropped some (on MySQL catalog routing the walk would otherwise span every database the server grants). On MySQL the databases arrive as JDBC catalogs (Connector/J defaults), so the listing reads them from `getCatalogs()` — the same vocabulary `datasources_get_tables` routes through; system schemas/databases (`information_schema`, `mysql`, `performance_schema`, `sys` on MySQL) are excluded on every dialect. **An empty list is a valid result** — a datasource with no namespace dimension (SQLite) has none to list. See [Datasources §7A](/docs/datasources.md#7a-schema-introspection). A connection failure against the datasource is the catalogued `pipeline.execution.datasource_unreachable` `isError` envelope — the same rule applies to §6.2.17/§6.2.18. **Permission:** `datasource.introspect` — introspection is reading (ratified 2026-09-20): every role holds it, the promoter included (C1). It opens a live connection against the datasource but returns metadata, never rows. #### 6.2.17 `datasources_get_tables` List a datasource's tables and views. ```json { "name": "datasources_get_tables", "description": "List the tables and views of a registered datasource by reading its live JDBC metadata. The listing spans namespaces — pass each table's reported `namespace` array to datasources_get_columns. A table carries `facts` when agents have recorded table-level learned facts on it (grain, sampling, window, a caveat) — read them before probing; a fact marked stale or needs_review is a warning, not a truth. For a LAKE datasource each table carries `partition_column`: the registered partition key, or `null` when none is registered. A compatible filter on a partition key can skip files; missing registration does not establish file count or rule out Parquet row-group skipping and column projection. Read-only, for pipeline authoring.", "inputSchema": { "type": "object", "required": ["name"], "properties": { "name": {"type": "string", "description": "Datasource name."}, "namespace": { "type": "array", "items": {"type": "string"}, "description": "Optional namespace filter, outermost first, as returned by datasources_get_schemas. An unknown namespace matches nothing." }, "schema": {"type": "string", "description": "Optional single-level filter; accepts the dotted 'catalog.schema' form. Superseded by namespace."} } } } ``` Returns: `{"tables": [{"namespace": [...], "schema", "name", "type", "remarks"?}], "truncated": bool}` — `namespace` is the containing path outermost-first and `schema` its last segment (kept for one release so a pre-087 client reads what it always read); `type` is the driver's raw JDBC table type (`TABLE`, `VIEW`, `BASE TABLE`, ...); `remarks` is the engine-stored table comment, omitted when the driver/database has none; `facts` (118) is the table's TABLE-GRAIN learned facts — refs with no column: `grain`, `window`, `sampling`, a table-level `caveat` — in the §7A.5 shape, omitted when there are none. For a LAKE datasource each table entry also carries `partition_column` (126): the registered partition column's name, or `null` when none is registered — the same registry value §6.2.33's stats payload reports, so the listing and the stats read cannot disagree; a compatible predicate on a partition key can skip files. Missing registration does not establish physical layout or file count; Parquet row-group skipping and column projection may still reduce reads. A pushed filter alone does not prove skipped files or bytes. Non-lake dialects carry no such key. Only a COMPLETE listing (no `namespace`/`schema` filter, not truncated) runs the §7A.5 drift mark for a fact whose table is no longer listed; a filtered listing proves nothing about what it did not list. The listing is capped at **2000 tables**; `truncated: true` means the cap dropped some. The `namespace` and `schema` filters are exact-match, not LIKE patterns, and a namespace deeper than the dialect's own matches nothing. Without a filter the listing **spans namespaces** — pass each table's reported `namespace` to `datasources_get_columns` (there, no namespace argument means the connection's current one only, and a datasource reporting **none** fails with the catalogued `pipeline.execution.parameter_required` rather than merging same-named tables' columns — the merge hazard lives in `datasources_get_columns` alone; a tables listing carries each row's own namespace and cannot merge, so it deliberately has no such guard and works unfiltered on those datasources too). **Permission:** `datasource.introspect` — introspection is reading (ratified 2026-09-20): every role holds it, the promoter included (C1). It opens a live connection against the datasource but returns metadata, never rows. #### 6.2.18 `datasources_get_columns` List one table's columns with canonical types. ```json { "name": "datasources_get_columns", "description": "List one table's columns with canonical types, read from the datasource's live JDBC metadata. Pass the table name exactly as datasources_get_tables returned it, and its `namespace` array with it. Without a namespace only the connection's current one is read; if the datasource reports none, an explicit namespace is required (list them with datasources_get_schemas). On a two-level engine an unqualified read can merge same-named tables from different catalogs, which is why the namespace is worth passing. Each column carries `facts` when agents have recorded learned facts on it — a unit, a time zone, what a coded value means, a join, a caveat — with trust and evidence: read them before probing, and treat stale or needs_review as a warning to re-verify, then record the superseding fact. An unknown table is refused as datasource.table_not_found, naming the nearest listed table when one is close; a LAKE datasource answers datasource.lake_table_not_found for a table its registry does not carry. Read-only, for pipeline authoring.", "inputSchema": { "type": "object", "required": ["name", "table"], "properties": { "name": {"type": "string", "description": "Datasource name."}, "table": {"type": "string", "description": "Table name as returned by datasources_get_tables."}, "namespace": { "type": "array", "items": {"type": "string"}, "description": "The table's namespace, outermost first, as datasources_get_tables reported it. An unknown namespace matches nothing." }, "schema": {"type": "string", "description": "Optional single-level filter; accepts the dotted 'catalog.schema' form. Superseded by namespace."} } } } ``` Returns: array of `{"name", "type", "precision", "scale", "nullable", "source_type", "warnings", "remarks"}` — `type` is the canonical Type System type, `source_type` the driver's own type name, `warnings` the ingress mapper's warning messages (empty when the mapping was clean), `remarks` the engine-stored column comment (omitted when there is none); `precision`/`scale`/`nullable`/`remarks` are omitted when the metadata does not report them; `facts` (118) is the column's learned facts in the §7A.5 shape — every fact with a ref on this column, a two-column `join` fact on both — omitted when there are none. This is the read that runs the §7A.5 drift check: each fact's fingerprint is recomputed from the columns just listed, and a demotion (`needs_review`, `stale`) is written back to the row before it is served. An unknown table is refused as **`datasource.table_not_found`** (123 §A): the message asserts "does not exist" on complete-catalog dialects and "does not exist, or the datasource's credentials cannot see it" on privilege-filtered ones, and names the nearest listed table when one is close — an existing table with zero readable columns still returns an empty list. `table` and `schema` are exact-match identifiers — JDBC metadata name matching is case-sensitive, `_`/`%` are not wildcards; pass the name `datasources_get_tables` returned. System-schema rows are excluded; without a `schema` argument the read defaults to the connection's current schema (routed per dialect, [Datasources §7A](/docs/datasources.md#7a-schema-introspection)) so same-named tables in different schemas cannot merge their columns — and a datasource that reports **no current schema** makes that default impossible, so the call fails with the catalogued `pipeline.execution.parameter_required` instead of silently merging (`datasources_get_schemas` lists the schemas to pass; schemaless datasources such as SQLite are the exception — there is nothing to merge). **Permission:** `datasource.introspect` — introspection is reading (ratified 2026-09-20): every role holds it, the promoter included (C1). It opens a live connection against the datasource but returns metadata, never rows. #### 6.2.18a The learned-fact block on introspection responses (118) The three introspection tools above, `datasources_list` (126) and `datasources_get` carry the facts agents recorded about the objects they return — and, since 136 §A, the workspace's rules under a second key, `definitions`, on the listing and on `datasources_get` (§6.2.10/§6.2.11) ([learned-semantic-layer design](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-11-learned-semantic-layer-design.md) D-S7, §7.2) — INLINE, so the fact is where the agent is already looking and there is no separate memory to forget to query. The REST twins (`GET /api/v1/datasources/{name}`, `.../tables`, `.../tables/{t}/columns`, [REST API §9.7A](/docs/rest-api.md#97a-learned-facts-on-the-introspection-endpoints-118)) carry the identical block from the same code. Each fact renders as: ```jsonc { "id": "…", "scope": "DATASOURCE", "kind": "unit", "fact": "value is already in the unit named by unit_col — never tenths", "trust": "observed", "drift": "column X no longer exists", "evidence_summary": "unit=°C, value=21.4 | unit=mm, value=0.8", "recorded_via": "mcp", "recorded_at": "2026-09-11T10:15:00Z", "from_this_workspace": true, "source_pipeline": {"id": "…", "name": "finance/revenue"}, "conflict": true } ``` `drift`, `evidence_summary`, `source_pipeline` and `conflict` are omitted-when-absent. A WORKSPACE-scope fact on the `definitions` block (and every WORKSPACE row of `semantics_list`, §6.2.38) also carries **`implemented_by`** (7e): `[{template_id, version}]`, the live template versions citing it as the script implementing it, under the key's template lens — `[]` when none; a DATASOURCE fact never carries the key, because nothing can cite it ([Templates §3.4](/docs/templates.md#34-implements-and-drift)). `trust` is `asserted` (no evidence) / `observed` (evidence ran at record time) / `verified` (a human confirmed) / `needs_review` (the table's column set changed around a still-resolving ref) / `stale` (a referenced column or table no longer exists); `retired` facts are never served here (`semantics_list` with `include_retired` lists them). **The drift check runs at read (design §6):** `datasources_get_columns` recomputes each fact's per-table fingerprint from the columns it just read and a complete `datasources_get_tables` listing checks each fact's tables — a demotion is written back to the row (idempotent, one-way; the alternative is serving a mark the server computed and then forgot). Nothing re-maps: a rename and a "drop + unrelated add" are indistinguishable to a machine, so a `stale` fact stays beside the current columns until an agent records the superseding fact with evidence (`semantics_record` with `supersedes`). **Conflicts coexist (D-S5):** two live facts of one kind on the same refs are both served, each `conflict: true`; the reader decides. **Provenance stops at the reader's visibility (D-S9):** a DATASOURCE fact recorded from another workspace arrives with its evidence and trust and `from_this_workspace: false`; `source_pipeline` is present only when the reader's workspace can read that pipeline — the same `findById(workspace, id)` predicate every pipeline read uses. #### 6.2.19 `datasources_preview_rows` Preview up to `limit` rows of one table's data — the counterpart to `datasources_get_columns`. ```json { "name": "datasources_preview_rows", "description": "Preview up to `limit` rows of one table's data, read live from the datasource. The counterpart to datasources_get_columns: this shows the DATA, that shows the shape. Without order_by the top-N is engine-arbitrary; pass order_by to see a chosen end of the data, e.g. direction DESC for the newest or largest rows. Read-only (SELECT); readonly datasources are valid targets. Values arrive wire-encoded: BIGINTEGER and BIGDECIMAL as strings, temporal as fixed-width ISO forms. An unknown table is refused as datasource.table_not_found naming the nearest listed table; a table the datasource's credentials cannot read is datasource.table_forbidden.", "inputSchema": { "type": "object", "required": ["name", "table"], "properties": { "name": {"type": "string", "description": "Datasource name."}, "table": {"type": "string", "description": "Table name exactly as datasources_get_tables returned it."}, "schema": {"type": "string", "description": "Optional schema qualifier. Omitted means the connection's current schema."}, "order_by": { "type": "array", "description": "Sort terms applied in order. Each is an object with a column and a direction, never a free SQL string.", "items": { "type": "object", "required": ["column"], "properties": { "column": {"type": "string", "description": "Column name exactly as datasources_get_columns returned it."}, "direction": {"type": "string", "enum": ["ASC", "DESC"], "default": "ASC"} } } }, "limit": {"type": "integer", "default": 50, "minimum": 1, "maximum": 50} } } } ``` Returns: `{"datasource", "table", "schema"?, "columns": [{"name", "type"}], "rows": [{column: value, ...}], "row_count", "truncated"}` — values wire-encoded per the result-cursor rules (`BIGINTEGER`/`BIGDECIMAL` as strings, temporal fixed-width ISO, `BINARY` base64). The server builds the ENTIRE statement and quotes every identifier with the dialect's quote character (backtick on MySQL, `[...]` on MSSQL, doubled `"` elsewhere) — the agent supplies identifiers only, and a blank identifier is `-32602`. `order_by` entries are `{column, direction}` objects; a free `"col DESC"` string is refused, not parsed. Without `order_by` the top-N is engine-arbitrary. The cap is applied in the dialect's own syntax (`LIMIT`, Oracle `FETCH FIRST n ROWS ONLY`, MSSQL `TOP (n)`) AND as JDBC `maxRows`/`fetchSize`. Readonly datasources are valid targets — the readonly refusal covers write-shaped node uses, and this is a SELECT. `tempdb` can never be a target: a datasource of that name cannot be registered (contract §4.8). The table is resolved before any statement runs (123 §A): an unknown table is **`datasource.table_not_found`**, naming the nearest listed table when one is close; a table the datasource's credentials cannot read — the SELECT refused with a permission SQLSTATE — is **`403 datasource.table_forbidden`**. A connection failure is the catalogued `pipeline.execution.datasource_unreachable`; any other refused statement is the catalogued `pipeline.node.query_execution_failed` carrying the bounded driver message. **Permission:** `datasource.preview_rows` — this returns arbitrary customer ROW DATA, not metadata; a role without it — viewer, promoter — does not acquire that reach (037 F). #### 6.2.20 `pipelines_execute_node` Runs ONE pipeline node's rendered SQL against its own datasource — a debug query, not an execution. ```json { "name": "pipelines_execute_node", "description": "Runs ONE pipeline node's rendered SQL against its own datasource and returns up to 50 decoded rows — a debug query for testing a node in isolation, NOT a pipeline execution. DML and DDL nodes execute FOR REAL against the datasource, leaving no execution history or trace. No ancestors run and no tempdb exists: a node whose source is tempdb is refused, and so is a SCRIPT node (use templates_evaluate). Parameters bind through the pipeline's declarations; unsupplied required parameters fall back to sample values and the response names them in sampled_parameters. Absent version runs the DRAFT if one exists, else the current released version; the response states which version and status ran. A draft whose pinned draft template was updated after this key's last templates_render of it is refused pipeline.execution.template_unrendered — render, then run.", "inputSchema": { "type": "object", "required": ["pipeline_id", "node_id"], "properties": { "pipeline_id": {"type": "string", "format": "uuid"}, "node_id": {"type": "string", "description": "Node id within the pipeline body."}, "version": {"type": "integer", "minimum": 1, "description": "Pipeline version to read. Omitted: the DRAFT if one exists, else the current released version."}, "parameters": { "type": "object", "description": "Values for the pipeline's declared parameters, keyed by name. Types follow the declarations (BIGINTEGER and BIGDECIMAL as strings).", "additionalProperties": true } } } } ``` Returns: `{"node_id", "node_type", "datasource", "version", "status", "sql", "sampled_parameters"?, "elapsed_ms"}` plus, for DQL nodes, `{"columns": [{"name", "type"}], "rows": [{column: value, ...}], "row_count", "truncated"}` (capped at 50 rows like `datasources_preview_rows`), or for DML/DDL nodes `{"affected_rows"}` — **DML/DDL execute for real**, with no execution row, no SSE and no idempotency record (ratified, 037 §A). `sql` is the rendered template output in its `:name` form; execution binds those names as statement parameters. `status` is the run version's lifecycle status (`DRAFT`/`RELEASED`), so the agent never infers which body it ran. Refusals, all before anything runs: an unknown node id → `pipeline.node.not_found` (404 semantics); a node whose `source` is `tempdb` → `pipeline.node.standalone_execution_refused` with `details.reason = tempdb_source` (the staging database exists only inside a full execution — use `pipelines_execute`); a PIPELINE node → the same code with `details.reason = pipeline_node` (it runs a child pipeline, not SQL); a missing pinned template → `pipeline.node.template_not_found`; a render failure → `pipeline.node.template_render_failed`; a supplied parameter failing §6.3 coercion → `pipeline.execution.invalid_parameter_type` naming every failure; a rendered `:name` the pipeline does not declare → `pipeline.node.sql_parameter_missing` (042). A DML/DDL node against a readonly datasource is refused with `pipeline.node.datasource_readonly`; a datasource invisible to the key's workspace resolves as `datasource.not_found`, and an unreachable datasource as `pipeline.execution.datasource_unreachable`; a refused statement is `pipeline.node.query_execution_failed`. **Permission:** `pipeline.execute_node` — this runs real SQL and returns arbitrary customer row data; the same 037 F reasoning as `datasources_preview_rows`. #### 6.2.21 `templates_used_by` Which pipelines pin a given template version — the reverse arrow of a node's `{id, version}` template pin (040). ```json { "name": "templates_used_by", "description": "Which pipelines, parameter sets AND visualizations pin a given template version in their working version (the draft when unreleased edits exist, else the latest released). Returns one reference per node — pipeline name and id, node id, and the pipeline version carrying the pin — plus the distinct pipeline count, and one row per pinning parameter set (#194: set name and id, the parameter, the set version carrying the pin), and one row per pinning visualization (#320: its `transform.template` pin — visualization name and id, the visualization version carrying the pin and its status). Use it before editing or retiring a template version to see who you would affect. It does not answer 'is it safe to delete' (that scan includes historical pipeline versions and lives in the delete refusal), and it never changes anything. A promoter's key sees only RELEASED templates newer than the promotion target's (the promoter lens); every other template resolves as not-found, and pinning pipelines, sets or visualizations it cannot see are left out of the answer.", "inputSchema": { "type": "object", "required": ["id", "version"], "properties": { "id": {"type": "string", "description": "Template id."}, "version": {"type": "integer", "description": "The pinned version to look for."} }, "additionalProperties": false } } ``` Returns: `{"template": {"id", "version"}, "scan": "working_version", "pipeline_count", "references": [{"pipeline", "pipeline_id", "node_id", "pipeline_version", "pipeline_version_status"}], "parameter_set_references": [{"parameter_set", "parameter_set_id", "parameter", "set_version", "set_version_status"}], "visualization_references": [{"visualization", "visualization_id", "visualization_version", "visualization_version_status"}]}` — since #320 a visualization whose `transform.template` pins this version is a real reference too (working-version scan, under the caller's visualization lens: a hidden visualization is never named, and a narrowing view sees RELEASED rows only) — one row per pinning NODE (pipelines) and per pinning PARAMETER (sets), so a pipeline with two nodes on the same version appears twice and `pipeline_count` stays the honest distinct count. Since #194 lane D the answer covers PARAMETER SETS too (the record's §8.4): a set whose selector pins this template version is a real reference, listed with the set name, the parameter and the set version carrying the pin. The scan reads each pipeline's **working version** (draft-if-exists, versioning §7), so a draft that just adopted the pin is already counted — the "who do I notify" answer an author needs before editing a template ([Templates §5.4](/docs/templates.md#54-used-by-the-reverse-arrow-v19-040)). `version` is required and never clamped (the [D2 rule](/docs/templates.md#54-used-by-the-reverse-arrow-v19-040): the question is per version). The delete-safety question — who pins ANY version, in ANY pipeline version ever, and since lane D in any parameter-set version ever — is a different scan and surfaces as the `template.in_use` delete refusal on the REST surface, not here. An unknown template id is the catalogued `template.not_found`; a known id with no such version is the same code with a `version` detail. **Permission:** `template.read` (040 D7) — reference structure a workspace reader may already see by reading the pipelines themselves; no customer row data. #### 6.2.22 (removed) — no datasource writes on this surface **No credential travels through an agent.** People add datasources in the UI; an operator uses `POST /api/v1/datasources` ([Datasources §3.1](/docs/datasources.md#31-json-structure-request--post-apiv1datasources)) or the bootstrap file ([Datasources §8A](/docs/datasources.md#8a-bootstrap-registration-config-declared-datasources)); agents use the datasources that already exist, **by name**. Creating, editing and deleting a datasource are UI/REST-only, without exception. 068 shipped a `datasources_create` tool here and wrote the hazard into its own description: a secret sent through a tool call transits the agent's context, its transcript and whatever logging the client does, so "prefer the UI for a real credential" was an instruction to the model. 094 (owner ruling 4) decided that hazard is not documentable away — the tool's own description is not a control — and removed it. The surface went 31 → 30 tools; this section number is kept so §6.2.23 onward do not shift. What stays is every datasource tool that needs no credential: `datasources_list`, `datasources_get`, `datasources_test`, `datasources_get_schemas`, `datasources_get_tables`, `datasources_get_columns`, `datasources_get_table_stats`, `datasources_preview_rows` and `sql_probe`. An agent can therefore still discover a connection, confirm it works, read its shape and statistics, probe a SELECT and preview rows — everything authoring a pipeline needs — without one ever being handed a password. The same reasoning is why there is no `api_keys_create`: see the §6.2.23 preamble. An agent that needs a datasource it cannot find should **ask the person** to add it in the UI, then read it back with `datasources_list`. #### 6.2.23 `endpoints_create` Publish a released pipeline as a `GET` endpoint at `/api///` (R-EP5; [REST API §19](/docs/rest-api.md#19-published-endpoints)). Calls the same service `POST /api/v1/endpoints` calls — the same read-only rule, the same reserved-category refusal, the same ambiguity refusal, the same audit (049's rule: two entry points, one validated path). ```json { "name": "endpoints_create", "description": "Publish a released pipeline as a GET endpoint at /api///. The category is your namespace (a business domain, a team); categories matching v and the literal 'api' are reserved to the product and refused with endpoint.path_reserved. The version is one free-form segment (v1 by convention); path variables come after it. The pipeline must have a RELEASED version and must be side-effect-free: every node DQL into tempdb or the caller, a DML/DDL node whose source is tempdb, transitively through PIPELINE nodes. A DML/DDL node against a registered datasource, or a DQL node writing back to a datasource, is refused with endpoint.pipeline_not_readonly naming the node and the datasource — that rule is what makes serving over GET safe, since GET is retried, preloaded and crawled. path is 3-10 segments, each a literal [a-z0-9][a-z0-9_.-]{0,63} or a {variable} naming a declared parameter; remaining parameters come from the query string. A leading /api prefix is stripped, not refused. A path that could match the same URL as an existing one is refused (endpoint.path_conflict) rather than resolved by precedence. Calling the endpoint needs an API key bound to it — create one with the api_caller role and bind it over REST or in the UI (auth.md §7.7); an endpoint bound to no key is served to no one, and an MCP key never reaches REST at all (it acts over /mcp only).", "inputSchema": { "type": "object", "required": [ "path", "pipeline" ], "additionalProperties": false, "properties": { "path": { "type": "string", "description": "e.g. /finance/v1/revenue/{region} — category, version, then the path; no /api prefix (one is stripped, not refused), no trailing slash." }, "pipeline": { "type": "string", "description": "The pipeline NAME. It must have a released version." }, "timeout_seconds": { "type": "integer", "description": "Clamped by datapipelines.endpoints.timeout-min-seconds/max-seconds. On timeout the endpoint answers 202 and the execution keeps running." }, "description": { "type": "string" } } } } ``` Returns the endpoint's wire shape: `path`, `pipeline` (by NAME), `timeout_seconds`, `description`, `enabled`, `path_variables` and the servable `url`. A legacy path (listed flagged by §6.2.24) resolves as **not-found** here — the single read answers the valid shape only. #### 6.2.24 `endpoints_list` The published endpoints of the key's pinned workspace. ```json { "name": "endpoints_list", "description": "List the published endpoints of the key's workspace: path, pipeline name, timeout, whether it is enabled, and the path variables it binds. A disabled endpoint answers 404 exactly like an unpublished one, so this listing is the only way to see that it exists. A promoter's key lists only the endpoints of pipelines it can see (the promoter lens: released, newer than the promotion target's). Legacy rows (#274) are listed last with \"legacy\": true and their \"reason\": a stored path saved before the current grammar that is retired — never served, never a conflict partner; endpoints_delete removes one.", "inputSchema": { "type": "object", "additionalProperties": false, "properties": {} } } ``` Returns `{endpoints: [...]}` in the shape §6.2.23 returns. Legacy rows (#274) are listed last, flagged: a stored path saved before the current grammar carries `"legacy": true`, its `"reason"` (the grammar's own refusal, bounded), `"enabled"` and the usual `path`/`pipeline`/`url` — no `path_variables`, because there is no parse. A legacy row never serves, never blocks a publish, and `endpoints_delete` removes it. #### 6.2.25 `endpoints_get` One published endpoint, by its path PATTERN. ```json { "name": "endpoints_get", "description": "One published endpoint by its path (the pattern, not a request URL — '/finance/v1/revenue/{region}'). For a promoter's key an endpoint over a pipeline it cannot see resolves as not-found.", "inputSchema": { "type": "object", "required": [ "path" ], "additionalProperties": false, "properties": { "path": { "type": "string", "description": "The published path PATTERN, e.g. /finance/v1/revenue/{region}." } } } } ``` Returns the §6.2.23 shape, or `endpoint.not_found`. #### 6.2.26 `endpoints_delete` Unpublish an endpoint. The pipeline is untouched; key bindings on that node are not removed, since a node may still carry other endpoints beneath it. ```json { "name": "endpoints_delete", "description": "Unpublish an endpoint by its path. The pipeline is untouched — only the URL stops answering. Key bindings on that path are NOT removed: they describe a node of the tree, which may still carry other endpoints beneath it. This is also the fix for a LEGACY row (#274) — a retired path listed with \"legacy\": true never serves under the current grammar, and deleting it removes the row.", "inputSchema": { "type": "object", "required": [ "path" ], "additionalProperties": false, "properties": { "path": { "type": "string", "description": "The published path PATTERN, e.g. /finance/v1/revenue/{region}." } } } } ``` Returns `{path, deleted: true}`, or `endpoint.not_found`. **Permission:** `endpoint.unpublish`. **Mutating** — declared `mutating` in the tool catalog, so every call writes `mcp.tool.called` **and** `mcp.tool.write` at the dispatcher's single audit choke point (§6.3). #### 6.2.23 `calculators_list` The catalog of calculator kinds a `CALCULATOR` node can evaluate ([Calculators §2](/docs/calculators.md), [Pipeline Contract §4.10](/docs/pipeline-contract.md#410-json-structure-calculator-node)). The tool an agent calls **before** authoring a calculator node: a `kind` and its input names are the two things it cannot guess, and getting them from a 400 one at a time is a slow way to learn a fixed list. ```json { "name": "calculators_list", "description": "The catalog of calculator kinds a CALCULATOR node can evaluate: every kind with its typed inputs (name, type, required, whether it takes a JSON array, and its default when optional), its output type (or, for a multi-output kind, the named `outputs` set a node maps through `context_keys`), one worked example, and `phrases` — the everyday phrases the kind answers. Call this before authoring a CALCULATOR node — the kind names and input names are not guessable — and match the question's words against `phrases` before you pick a kind: a relative time phrase ('last quarter', 'month to date') is resolved by that lookup, never by interpreting it yourself. Also returns the Context keys every pipeline can reference without declaring anything: the deployment's org_* values and the platform keys current_date, current_timestamp and execution_id. Read-only.", "inputSchema": { "type": "object", "properties": {}, "additionalProperties": false } } ``` **Permission:** `calculator.read` — and read in the strongest sense the surface has: the answer is a property of the BUILD, identical for every caller, every key and every workspace. No workspace scoping applies because there is no workspace data in it. **Response:** `kinds` (each with `kind`, `display_name`, `description`, `phrases`, `inputs`, `output`, `example`), `count`, `context_keys` (`org` names and `platform` name/type pairs), and `docs` pointing at the catalog page. A **single-output** kind carries `output` (the wire type, or `"ANY"`) and no `outputs` key at all; a **multi-output** kind (121) carries `"output": null` and `outputs` — the named set `[{name, type, description}]` a node maps through `context_keys` ([Pipeline Contract §4.10](/docs/pipeline-contract.md#410-json-structure-calculator-node)), every name mapped or the save is refused. `phrases` lists the everyday phrases the kind answers — match the question's words against them before picking a kind (the same lookup the skill's calculator rule teaches). An input carries `list: true` only when it takes a JSON array, and `default` only when it is optional — the absent keys carry the same information as `false`/`null` would, without spending an agent's context window on eighty of them. #### 6.2.24 `calculators_get` One kind's full definition — the same entry `calculators_list` returns, for a caller that already knows the name. ```json { "name": "calculators_get", "description": "One calculator kind's full definition: display name, description, typed inputs, output type (or the named `outputs` set of a multi-output kind), a worked example and `phrases` — the everyday phrases the kind answers, which you match the question's words against before picking a kind. Use it when you know the kind and need its exact input names and types. An unknown kind is refused with the catalogued names in the error detail. Read-only.", "inputSchema": { "type": "object", "required": ["kind"], "properties": { "kind": {"type": "string", "description": "The kind name, e.g. fiscal_quarter."} }, "additionalProperties": false } } ``` **Permission:** `calculator.read`. **Errors:** an unknown kind is `pipeline.validation.calculator_unknown` with `known_kinds` in the detail — deliberately the SAME code a rejected `pipelines_create` returns for a bad `kind`, so an agent sees one fact about the world rather than two unrelated failures. #### 6.2.29 `lake_tables_register` Register one table in a LAKE datasource's catalog — the dp-lake registry ([metadata-db §4.15](/docs/metadata-db.md#415-lake_tables), [Datasources §4.1](/docs/datasources.md)). A LAKE datasource's tables are exactly the registered rows: the engine cannot list a bucket, so this catalog is what introspection and query resolution read. Mirrors `POST /api/v1/datasources/{name}/tables` ([REST §9.8](/docs/rest-api.md#98-lake-tables-the-dp-lake-catalog)) — the SAME application service, so validation, the duplicate refusal and the pool invalidation are identical on both surfaces. ```json { "name": "lake_tables_register", "description": "Register one table in a LAKE datasource's catalog (the dp-lake registry). Mirrors POST /api/v1/datasources/{name}/tables: namespace (array of segments or the dotted 'acme.analytics' shorthand), table, format (parquet | iceberg) and location are required; partition_column is optional. The location is s3://bucket/prefix/ (parquet: a directory or glob; iceberg: the table's CURRENT metadata file, e.g. s3://bucket/table/metadata/00042-.metadata.json — DuckDB 1.5.5 cannot scan a pyiceberg table by its root, so register the file, and re-register it when the table commits) or a file:// path — no other scheme, and no quotes, backslashes, whitespace or control characters (it is interpolated into the engine's CREATE VIEW, so the refusal is total). Segments follow the pipeline/template segment grammar without dots. Registering an already-registered (namespace, table) is the 409 datasource.lake_table_duplicate; a non-LAKE datasource is refused. Mutating.", "inputSchema": { "type": "object", "required": [ "name", "namespace", "table", "format", "location" ], "additionalProperties": false, "properties": { "name": { "type": "string", "description": "Datasource name. A LAKE datasource visible in the key's pinned workspace." }, "namespace": { "description": "Namespace — a segments array or the dotted shorthand. 1-9 segments, the segment grammar without dots.", "anyOf": [ { "type": "array", "items": { "type": "string" } }, { "type": "string" } ] }, "table": { "type": "string", "description": "The table's name — one segment of the same grammar, e.g. events_by_day." }, "format": { "type": "string", "enum": [ "parquet", "iceberg" ] }, "location": { "type": "string", "description": "s3://bucket/prefix/ (parquet dir/glob; iceberg: the current metadata file, not the table root) or file:// path. Nothing else; no injection chars." }, "partition_column": { "type": "string", "description": "Optional. The hive-style partition column, e.g. event_date." } } } } ``` **Permission:** `lake_table.manage` — the datasource-mutation floor ([Auth §7.6](/docs/auth.md#76-operation-matrix--the-permission-catalog-authoritative)). Mutating a GLOBAL datasource's registry additionally requires admin, a workspaces D8 rule inside the shared service rather than a scope. **Mutating.** Declared `mutating` in the tool catalog: every call writes `mcp.tool.called` **and** `mcp.tool.write` at the dispatcher's single audit choke point (§6.3). **Errors:** `datasource.not_found` (unknown or invisible datasource), `datasource.validation.lake_dialect_required` (not a LAKE datasource), `lake_namespace_invalid` / `lake_name_invalid` / `lake_format_invalid` / `lake_location_invalid` (the grammar and the injection refusal), `datasource.lake_table_duplicate` (409 — the triple is taken). **Response:** the stored row — `namespace`, `name`, `qualified_name`, `format`, `location`, `partition_column`, `registered_at`. #### 6.2.30 `lake_tables_import` Bulk-register from a `manifest.json` `tables[]` block (the manifest shape documented in the description below), inline or by URL. Mirrors `POST /api/v1/datasources/{name}/tables/import`. A manifest URL is fetched **server-side, and only from the datasource's own bucket/endpoint** — derived from its declared `dialect.endpoint` / `catalog.ref`, or AWS S3 when neither is set; anything else is refused. There is no arbitrary URL fetch (SSRF). ```json { "name": "lake_tables_import", "description": "Bulk-register lake tables from a manifest's `tables[]` shape. Mirrors POST /api/v1/datasources/{name}/tables/import: pass EITHER tables (an array of {name, format, location|path, partition_column?, namespace?}, with publish_prefix for relative paths and an optional shared namespace) OR manifest_url. A manifest URL is fetched server-side ONLY from the datasource's own endpoint/bucket — derived from its declared dialect.endpoint / catalog.ref, or AWS S3 when neither is set; anything else is refused with datasource.validation.lake_manifest_url_forbidden (no arbitrary URL fetch — SSRF). Import is idempotent: already-registered tables are reported in already_registered, not errors. Mutating.", "inputSchema": { "type": "object", "required": [ "name" ], "additionalProperties": false, "properties": { "name": { "type": "string", "description": "Datasource name. A LAKE datasource visible in the key's pinned workspace." }, "tables": { "type": "array", "description": "Inline form: manifest entries {name, format, location|path, partition_column?, namespace?}.", "items": { "type": "object" } }, "namespace": { "description": "Shared namespace applied to entries that carry none — an array of segments or the dotted shorthand.", "anyOf": [ { "type": "array", "items": { "type": "string" } }, { "type": "string" } ] }, "publish_prefix": { "type": "string", "description": "Base URI resolving relative entry paths, e.g. s3://bucket/lake/v1." }, "manifest_url": { "type": "string", "description": "URL of a manifest.json. Fetched ONLY from the datasource's own endpoint/bucket or AWS S3; else refused." } } } } ``` **Permission:** `lake_table.manage`. **Mutating** — same audit pair as `lake_tables_register`. **Errors:** the register set, plus `datasource.validation.lake_manifest_url_forbidden` (a URL outside the datasource's own roots) and `pipeline.execution.datasource_unreachable` (502 — the manifest could not be fetched from the datasource's own storage). **Response:** `registered` (the stored rows), `registered_count`, `already_registered` (dotted qualified names skipped as already present — import is idempotent, so bootstrap can re-run it), `already_registered_count`. #### 6.2.31 `lake_tables_unregister` Unregister one table. The objects in the bucket are untouched — the table stops being served by the datasource. Mirrors `DELETE /api/v1/datasources/{name}/tables/{namespace}/{table}`. ```json { "name": "lake_tables_unregister", "description": "Unregister one table from a LAKE datasource's catalog. Mirrors DELETE /api/v1/datasources/{name}/tables/{namespace}/{table}: the objects in the bucket are untouched — the table stops being served by the datasource. Unregistering a table that is not registered is the 404 datasource.lake_table_not_found, never a silent no-op. Mutating.", "inputSchema": { "type": "object", "required": [ "name", "namespace", "table" ], "additionalProperties": false, "properties": { "name": { "type": "string", "description": "Datasource name. A LAKE datasource visible in the key's pinned workspace." }, "namespace": { "description": "The table's namespace, outermost first — an array of segments or the dotted shorthand ('acme.analytics').", "anyOf": [ { "type": "array", "items": { "type": "string" } }, { "type": "string" } ] }, "table": { "type": "string", "description": "The table to unregister." } } } } ``` **Permission:** `lake_table.manage`. **Mutating** — same audit pair as `lake_tables_register`. **Errors:** `datasource.not_found`, `datasource.validation.lake_dialect_required`, the grammar codes for a malformed `namespace`/`table`, and `datasource.lake_table_not_found` (404 — an absent triple, never a silent no-op). **Response:** `{datasource, table, deleted: true}` with `table` the dotted qualified name. #### 6.2.32 `templates_purge_draft` Hard-delete a template that has NEVER been released — the one lifecycle verb an agent gets (101's bounded D61/D62 self-service fraction; humans release, and humans discard releases — D4/D57 are untouched). ```json { "name": "templates_purge_draft", "description": "Hard-delete a template that has NEVER been released: the only version is a DRAFT, created by this key's user, and pinned by nothing — no pipeline version anywhere, draft or released, no parameter-set version either (#194), and no visualization version (#320) may reference any version of it (the refusal names the pinning pipelines, sets or visualizations your view admits; templates_used_by answers the working-version scan if you need to inspect them). The sole-draft purge takes the entity row with it. A template holding any RELEASED or discarded version, another user's draft, or a pinned draft is refused — humans release and humans discard releases; an agent's own draft that should not exist is what this verb removes. Mutating.", "inputSchema": { "type": "object", "required": ["id"], "additionalProperties": false, "properties": { "id": {"type": "string", "description": "Template id."} } } } ``` Returns: `{"id", "purged": true}`. Admission is exact: the ONLY version is a DRAFT, created by THIS key's user (templates record a creator user, never a key — "this key created it" degrades to "this key's user"), and NOTHING pins any version of it in ANY pipeline version ever — the delete guard's every-version-ever scan, not the working-version scan `templates_used_by` (§6.2.21) answers. A pinned draft is refused with `template.in_use` and `details.pinned_by` naming the pipelines to change first (`details.referencing_parameter_sets` for the parameter sets, #194 lane D; `details.referencing_visualizations` for the visualizations, #320 — the third arm, over every stored visualization version ever, discarded included); the scans read the WHOLE workspace whatever the caller's view (#300 — a hidden pin still refuses), and the refusal ECHOES only the pins the caller's view admits: when others exist outside the view it carries `details.pins_hidden: true` and says so in words — never a name, and never a count, of what the view hides; a template holding any RELEASED or discarded version is `template.version.last_release`; another user's draft is `auth.role_required` with `details.reason: "not_creator"` (the one authorization refusal since #215, owner ruling 2026-09-24); an unknown id is `template.not_found`. The sole-draft purge takes the entity row with it, and a concurrent edit between the guard reads and the delete is `template.version.conflict` — never a silent delete of someone else's save. **Permission:** `template.version.manage`. **Mutating.** Declared `mutating` in the tool catalog, so every call writes the §14 audit pair (`mcp.tool.called` and `mcp.tool.write`). #### 6.2.33 `datasources_get_table_stats` One table's catalog statistics — row estimate, indexes, per-column bounds — always from the engine's OWN catalog, never a scan ([Datasources §7C](/docs/datasources.md#7c-catalog-table-statistics)). The per-column min/max are the catalog's ESTIMATES (the planner's histogram ends from the last `ANALYZE`), not a scan — read them as bounds, and `sql_probe` for the exact bound. The fourth introspection tool, one step past the get_columns flow of §6.2.16–18. ```json { "name": "datasources_get_table_stats", "description": "One table's catalog statistics: a row estimate, the index list (a lake table's partition column reports as the pseudo-index it is), and per-column distinct / null-fraction / min-max bounds — the min/max are the catalog's estimates, not a scan — probe for the exact bound. For a LAKE table the payload also carries partition_column: the registered partition column's name, or null when none is registered. This metadata does not establish file count or physical layout; Parquet row-group skipping and column projection may still reduce reads. A pushed READ_PARQUET filter alone does not prove skipped files or bytes. Every number comes from the engine's own catalog (pg_class, information_schema, parquet footers) — never a scan of the table, so this is safe at any table size. When a dialect holds no catalog stats the stat fields are null and stats_source is \"none\" — probe an explicit count with sql_probe if you need one. An unknown table is refused as datasource.table_not_found, naming the nearest listed table when one is close; a LAKE datasource answers datasource.lake_table_not_found for a table its registry does not carry. Read this before writing a predicate, then probe the query's access path and timing; an absent index alone does not establish scan cost.", "inputSchema": { "type": "object", "required": ["name", "table"], "properties": { "name": {"type": "string", "description": "Datasource name."}, "table": {"type": "string", "description": "Table name exactly as datasources_get_tables returned it."}, "namespace": { "type": "array", "items": {"type": "string"}, "description": "The table's namespace, outermost first, as datasources_get_tables reported it. An unknown namespace matches nothing." } } } } ``` Returns: `{row_estimate?, stats_as_of?, stats_source, indexes: [{name, columns, unique, primary, kind}], columns: [{name, n_distinct?, distinct_is_ratio, null_fraction?, min?, max?}]}` — the stat fields are omitted-when-null per the envelope convention (a missing `row_estimate` means "the catalog does not hold this", not zero), and `stats_source` names the catalog the numbers came from (`pg_class`, `information_schema.tables`, `sys.partitions`, `parquet_metadata`, …) or `"none"` when the dialect holds no catalog stats for the table. A LAKE table's registered partition column reports as the `{kind: "partition"}` pseudo-index — this describes the registered partition key, not an ordinary table index. The payload also carries `partition_column`, explicitly null when no key is registered. Neither a missing key nor a missing pseudo-index rules out Parquet data skipping or column projection. An unknown TABLE is **`datasource.table_not_found`** (the §7A resolution, 123 §A — naming the nearest listed table when one is close), so empty stats now mean exactly one thing: the table EXISTS and the catalog holds nothing for it. An unknown datasource is `datasource.not_found`, and a connection failure is `pipeline.execution.datasource_unreachable` — the same translations as §6.2.16–18. **Permission:** `datasource.read` — the engine's own stored ESTIMATES about shape, never customer row data (the §6.2.21 reasoning), which is why it is `datasource.read`, not the introspection permission. #### 6.2.34 `sql_probe` Run ONE read-only SELECT/WITH against a datasource and get rows, the canonical schema, `wall_ms` of query time, and the EXPLAIN plan captured BEFORE the query ran — the bounded free-SQL probe ([Datasources §7D](/docs/datasources.md#7d-the-sql-probe)). A debug probe, not an export. ```json { "name": "sql_probe", "description": "Run ONE read-only SELECT or WITH statement against a datasource and return up to `limit` wire-encoded rows, the canonical column schema, wall_ms of query time, and the EXPLAIN plan captured BEFORE the query ran — a bounded debug probe, not an export. The statement is classified before any connection opens: anything but a single SELECT/WITH, or a denylisted verb (INSERT, DROP, ATTACH, EXPLAIN, INTO, ...) anywhere in it, is refused without touching the datasource. Parameters bind as named :name placeholders through the same binder pipeline SQL uses; every referenced name must be supplied in `parameters` with its canonical type. `tempdb` is a scratch check, not an execution: the statement is prepared against an EMPTY scratch H2 in the staging mode (no staged tables, no data). Read `validation_status`: `executed` (`parsed: true`, rows) means a self-contained statement ran; `incomplete` (`parsed: null`, `missing_table`) means H2 stopped at the first staged table it could not find and NOTHING after that point — syntax or names — was checked; an error is a real H2 error (a syntax slip, a `VALUES` column named column1 where H2 says C1) found in milliseconds instead of a full run. To finish an incomplete check, restate the suspect construct over typed, aliased `VALUES` inputs so it executes here, or run the node with its real staged inputs. On a timeout the error details carry wall_ms and the plan, so the plan that explains the timeout survives it. A statement the database refuses for lack of privilege on an existing table is datasource.table_forbidden. The sql text never reaches the audit log — only its SHA-256 and length are recorded.", "inputSchema": { "type": "object", "required": ["name", "sql"], "additionalProperties": false, "properties": { "name": {"type": "string", "description": "Datasource name, or the reserved name tempdb to prepare a staging (H2) statement against an empty scratch engine. validation_status executed means it ran; incomplete with missing_table means H2 stopped at a staged table that exists only inside a full execution, and the rest of the statement is unverified."}, "sql": {"type": "string", "description": "ONE SELECT or WITH statement. A second statement or a denylisted verb is refused before any connection opens."}, "parameters": { "type": "object", "description": "Bind values for the statement's :name placeholders, keyed by name. type is the canonical logical type; value is its wire string (BIGINTEGER/BIGDECIMAL as plain decimal text, temporal in exact ISO forms — TIMESTAMP with an explicit offset or Z —, BINARY as padded standard base64). The forms are judged strictly, exactly as a pipeline parameter is: nothing is trimmed (a padded value is refused), booleans are exactly true/false, and numeric text over 1024 digits is refused before parsing. A null value binds SQL NULL.", "additionalProperties": { "type": "object", "required": ["type"], "properties": { "type": {"type": "string", "enum": ["BOOLEAN","INTEGER","BIGINTEGER","DECIMAL","BIGDECIMAL","STRING","BINARY","DATE","TIME","TIMESTAMP"]}, "value": {"type": ["string", "null"]} } } }, "limit": {"type": "integer", "default": 50, "minimum": 1, "maximum": 500}, "timeout_seconds": {"type": "integer", "default": 10, "minimum": 1} } } } ``` Returns: `{columns: [{name, type, precision?, scale?, nullable?}], warnings, rows, row_count_returned, truncated, wall_ms, timeout_seconds, plan?}` — values wire-encoded per the result-cursor rules, exactly like `datasources_preview_rows` (§6.2.19). `plan` carries `{scan?, estimated_rows?, raw, partitions_scanned?, partitions_total?}`; on LAKE the partitions pair is DuckDB's `Scanning Files: x/y` marker, null when no static file filter pruned (never `y/y` — a filter selecting every file is optimized away entirely). `limit` defaults to 50 and clamps to 500; `timeout_seconds` defaults to 10 and is clamped to the instance's node query timeout for the datasource's dialect ([Configuration §3.2](/docs/configuration.md#32-executor) `node-query-timeout-seconds-by-dialect.` when set, LAKE shipping 180; else `node-query-timeout-seconds`, default 60 s; #167) — a probe may run as long as a node on that dialect, never longer than the dialect's default node budget, and a datasource's own `query_timeout_seconds` tightens that bound, never loosens it (#405 — a datasource set to 20 s probes for at most 20 s, the budget its nodes get unless a node or its pipeline sets its own; [Datasources §7D](/docs/datasources.md#7d-the-sql-probe)) — and the payload's `timeout_seconds` is the timeout the statement actually ran under. The schema carries no `maximum` for it because the ceiling is operator configuration, not a constant. Refusals split into two families. **Argument faults** (JSON-RPC `-32602`, nothing leased, nothing ran): the statement is not a single SELECT/WITH or carries a denylisted verb (the classifier is a conservative token scan — false positives err toward refusal, and a side-effecting function call it cannot see through is NOT caught; the datasource's own DB-user privileges remain the last line), or a `parameters` entry is missing/mistyped — since #265 including a value that is not its declared type's exact wire form: nothing is trimmed, booleans are exactly `true`/`false`, numeric text over 1024 digits is refused before parsing, and the static refusal names the parameter, never the value text (datasources §7D; the shared coercion every parameter surface judges by). **Run failures** (the catalogued `pipeline.node.query_execution_failed` `isError` envelope — a timeout under `pipeline.node.query_timeout`, Pipeline Contract §13.4): a timeout, whose `details` carry `reason: "timeout"`, `wall_ms`, `timeout_seconds` (the timeout it ran under, #167) and the pre-captured plan — the plan that explains the timeout survives it — or a driver refusal, carrying the bounded driver message. A driver refusal whose SQLSTATE is permission-denied (123 §A — and since 186, H2's `90040` "Admin rights are required", the shape a de-privileged in-process session answers with) is instead **`403 datasource.table_forbidden`** — a referenced table exists but this datasource's credentials cannot read it. `tempdb` uses the scratch check below; the real staged tables exist only inside a full execution. The `sql` argument is audited as `sql_sha256` + `sql_length`, never verbatim (§14). **The `tempdb` scratch check (2026-09-11; contract corrected 2026-09-16, #119; scratch de-privileged 2026-09-21, #186).** `name: "tempdb"` no longer refuses. The statement is PREPARED against a FRESH, EMPTY in-memory H2 opened in the staging engine's own `MODE` and lower-folding (the URL shape `StagingFactory` builds, minus the execution) — no registry, no lease, no visibility gate, nothing to read. The scratch session is a grantless NON-admin user (staging §9.5's two-phase open, shared as `H2RestrictedSession`): the classifier admits only SELECT/WITH, but a SELECT can still call host-reaching functions, and those (`FILE_READ`, `CSVREAD`, …) are refused by the engine with SQLState `90040` — mapped like any permission-denied refusal to `datasource.table_forbidden`, never a 500, and the file's content is never in the answer. The payload always carries `check: "syntax_and_names"` (the check attempted), `engine`, and **`validation_status`**, which says how far the engine got. **`"incomplete"`** — H2 reported a missing table (`42102`/`42103`/`42104`): `{"check", "engine", "validation_status": "incomplete", "parsed": null, "missing_table": "", "wall_ms", "note"}`. H2 stops preparing at the first table it cannot resolve, so nothing after it has been checked — measured on the pinned 2.3.232: `SELECT a.id FROM absent_a a FULL OUTER JOIN absent_b b ON a.id = b.id` reports `absent_a` on the empty scratch and is a `42000` syntax error at `OUTER` once both tables exist (`SqlProbeH2Test`); an acceptance run had matched the SQL hash of a "passing" probe to its subsequently failing execution. `parsed` is deliberately `null`, not omitted: the key keeps its place and the value withdraws the affirmative the pre-#119 payload made (`parsed: true`, "the SQL is sound"). The note names the two ways to finish the check — restate the suspect construct over typed, aliased `VALUES` inputs so it executes on the scratch (validates the construct, not the real column types or data), or use `pipelines_execute` to run the DAG with its real staged inputs, which validates against the real schema at the cost of a run (`pipelines_execute_node` cannot read tempdb or reuse staged tables from an earlier execution); `templates_render` checks what the template emits, not whether it runs. **`"executed"`** — the statement was self-contained (a `VALUES` spine, a constant): `parsed: true` plus the ordinary probe payload above and a note that this validates the statement as written, not other parameter values or the real staged inputs. **Anything else** → the catalogued `pipeline.node.query_execution_failed` with H2's message — never disguised as incomplete. The case that motivated the check: an agent's caller node died on `Column "column1" not found` (H2 names `VALUES` columns `C1…`) after five source nodes ran green — a full DAG run to learn one H2 spelling. Parameters bind exactly as in a real probe; the classifier still admits only one SELECT/WITH; the scratch never creates a missing table from a guessed shape and never reads another execution's tempdb. Compatibility: no tool name, argument or count changed; `parsed` became nullable on the incomplete branch and `validation_status` was added — no typed consumer of the payload exists in this repository (the field is read by agents, not code), and this paragraph is the contract. **Permission:** `datasource.sql_probe` — this returns arbitrary customer ROW DATA; the same 037 F reasoning as `datasources_preview_rows`. #### 6.2.35 `executions_cancel` Request cancellation of a RUNNING execution — the MCP twin of `DELETE /api/v1/executions/{id}` ([REST API §10.4](/docs/rest-api.md#104-cancel-execution)) with one rule REST does not have: the **same-credential rule**. ```json { "name": "executions_cancel", "description": "Request cancellation of a RUNNING execution. This key can cancel ONLY an execution its own MCP calls started: the execution must have been triggered via MCP by this key's user, and an audit row must pair this key with the execution's correlation id — an execution started over REST, the UI, a pipeline node, a published endpoint, or another key of the same user is refused, and the refusal names which rule fired. A non-RUNNING execution is refused with its current status. Cancellation is requested, not awaited: the flag reaches the executing instance within about one poll interval (immediately when same-instance); poll executions_get for the terminal ABORTED status. Mutating.", "inputSchema": { "type": "object", "required": ["execution_id"], "additionalProperties": false, "properties": { "execution_id": {"type": "string", "format": "uuid"} } } } ``` Returns: `{execution_id, status: "cancellation_requested"}`. Cancellation is REQUESTED, not awaited — the flag reaches the executing instance within about one poll interval (immediately when same-instance); poll `executions_get` (§6.2.14) for the terminal `ABORTED`. The same-credential rule, in order: an execution the caller may not see is the same not-found every read path answers (ownership before anything else); a non-RUNNING execution is `pipeline.execution.not_running` with its current status in `details`; an execution started outside MCP (REST, the UI, a PIPELINE node, a published endpoint) is `auth.role_required` with `details.reason: "started_outside_mcp"`; and one started by a DIFFERENT credential — another key of the same user included — is the same code with `details.reason: "different_credential"`. The join that proves "this key started it" is an audit row pairing this key id with the execution's correlation id, because `pipeline_executions` records the owner USER, never the key. `pipelines_execute` writes that row (`mcp.execution.launched`) BEFORE the blocking run begins — the dispatcher's end-of-call `mcp.tool.called` row cannot exist while the call is still blocking, and in-flight is exactly when a cancel matters. **Permission:** `execution.cancel` — the REST twin's permission (the same-credential rule is a handler gate, not expressible as a permission; its refusals are `auth.role_required` with `details.reason`). **Mutating.** Declared `mutating` in the tool catalog — cancellation IS a write (it ends a running execution), and the `mcp.tool.write` row is the trace of WHOSE key stopped it (§14). #### 6.2.36 `templates_update` Update an existing template by writing its DRAFT (versioning §3.2/§5.1/§5.2) — the MCP twin of REST `PUT /templates` (§8.4), and the template mirror of [`pipelines_update`](#625-pipelines_update). Before 117 the only MCP path to change a template draft was `templates_purge_draft` + `templates_create`, and purge is refused the moment any pipeline version pins the template — exactly the state an authoring agent is in mid-build. ```json { "name": "templates_update", "description": "Update an existing template by writing its DRAFT — the first update after a release creates the draft (copy-on-write); later updates overwrite that same draft in place. Requires expected_hash: the body_hash you read (templates_get, or a previous templates_create/templates_update result) for the version you based your edit on. The result carries status='DRAFT' — your work is NOT released; a human releases it from the UI. On template.version.conflict someone modified it after you loaded it: re-read with templates_get, rebase, retry; never retry blindly. The body takes the same fields as templates_create, and the template's type is fixed at creation — an update naming a different type is refused with template.validation.type_immutable. type and dialect are optional: omitted, the working version's are inherited; a different dialect is refused. templates_purge_draft is for a template that was a mistake, not for editing one.", "inputSchema": { "type": "object", "required": ["id", "expected_hash", "display_name", "description", "body"], "properties": { "id": {"type": "string", "pattern": "^[a-z0-9][a-z0-9_.-]{0,63}(/[a-z0-9][a-z0-9_.-]{0,63}){1,9}$", "description": "Template to update — the FOLDER PATH id it was created under (acme/finance/daily_orders.sql). Required here: §9.6, the name never travels in a path or anywhere else. There is no rename, so the id cannot change — an unknown id is the catalogued template.not_found."}, "expected_hash": {"type": "string", "description": "The body_hash of the version this edit is based on — templates_get, or a previous templates_create/templates_update result. A mismatch is a 409 template.version.conflict; re-read and rebase, never retry blindly."}, "engine": {"type": "string", "enum": ["freemarker", "none"], "default": "freemarker", "description": "Template engine, matched to the type: 'freemarker' for sql/html, 'none' for the script types ('jsonata'/'javascript' — the body is evaluated, never rendered). Any other pairing is refused with template.validation.engine_unsupported."}, "type": {"type": "string", "enum": ["sql", "html", "jsonata", "javascript"], "description": "Template kind — fixed at creation, so on an update it is OPTIONAL: omitted, the working version's is inherited; stated, it must equal it (template.validation.type_immutable otherwise)."}, "dialect": {"type": "string", "enum": ["POSTGRES", "ORACLE", "MSSQL", "MYSQL", "H2", "DUCKDB", "SQLITE", "LAKE"], "description": "Optional on update: omit it and the working version's dialect is inherited. When present it must be the dialect the template already has — a different one is refused with template.validation.dialect_invalid (a template pinned by pipeline nodes cannot change engine; create a new template instead). Never present for an html template."}, "display_name": {"type": "string"}, "description": {"type": "string", "description": "Free text. State the variables the body expects and their types — the template declares none."}, "imports": { "type": "array", "description": "Library templates whose macros this body calls. Aliases must be unique within the template; each referenced template must exist at that exact version and be is_library=true.", "items": { "type": "object", "required": ["id", "version", "alias"], "properties": { "id": {"type": "string"}, "version": {"type": "integer"}, "alias": {"type": "string", "description": "Namespace the macros are bound to, e.g. 'dates' → <@dates.date_range .../>."} }, "additionalProperties": false } }, "is_library": {"type": "boolean", "default": false, "description": "true if this template exists to be imported by others. A library body contains only <#macro>/<#function> definitions — no output outside macro definitions. body is still required."}, "body": {"type": "string", "description": "Template source. Must not contain <#import> or <#include>."}, "contract": {"type": "object", "description": "Script contract (script types only — refused on sql/html with template.blocks_not_allowed): { mode: 'row'|'table'|'value', inputs: { name: { kind: 'table', columns: [{name, type, precision?, scale?, nullable?}] } or { kind: 'value', type, precision?, scale? } }, output: { kind: 'table'|'value'|'object', ... }, rejects?: boolean }. Types are LogicalType wire names; a row-mode contract requires exactly one table input."}, "invariants": {"type": "array", "description": "Script invariants: [{ name, expr, message }] — JSONata over { rows, rejects, inputs }, must be true on every test case and every real execution. May be empty but is required on a script type."}, "tests": {"type": "array", "description": "Script test cases: [{ name, input: { rows?, inputs?, meta?, now? }, expect: { output } or { refusal } }] — non-empty, at least one case whose every table input and rows are empty, expect is exactly one of output/refusal. Save runs the suite; release re-runs it."}, "implements": {"type": "array", "items": {"type": "string", "format": "uuid"}, "description": "Script types only: the learned facts this version implements (WORKSPACE definition, exclusion or preference ids of this workspace). Omitted, the citations of the version you edit are kept (a new draft inherits the released version's); [] clears them. Not content — outside body_hash — so an update whose body equals the released version and changes only implements writes them ON the released version and opens no draft: that is how you clear needs_review (cite the superseding fact). An id this workspace cannot cite is template.implements_unresolved. At most 50."} }, "additionalProperties": false } } ``` The save-time validation is the SAME parse-only set `templates_create` runs ([Templates §7.1](/docs/templates.md#71-save-time-validation-is-parse-only)); the write is the SAME `TemplateDraftService.write` call REST §8.4 makes — one write path, two surfaces. First write after a release copies the released version to a draft (copy-on-write); later writes overwrite that one draft in place. Returns: the stored version's projection — `id`, `version`, `status`, `body_hash` (carry this into the next write), `dialect`, `type`, `body`, and the `draft` pointer (`version`, `body_hash`, `updated_by`, `updated_at`) when the write produced a draft. **The update does NOT release** (versioning D4): a human releases from the UI, and `templates_render` (§6.2.9) is the preview step before you hand the draft over. A write whose CONTENT equals the released content is the §5.1 no-op: `status: "RELEASED"`, no draft opened, no version number burned. An unknown id is `template.not_found`; a stale `expected_hash` is `template.version.conflict` with the current state in `details`; an update naming a different `type` than the template's established one is `template.validation.type_immutable` (046 §5.3). **`type` is optional on update too (136 §D, T289b):** omitted, the working version's type is inherited — it used to default to `sql`, so an html template updated without `type` was refused `type_immutable` for a field it never changed; stated, it must equal the established one (`template.validation.type_immutable`, the draft service's own refusal). **`dialect` is optional on update (135 §C, T276):** omitted, the working version's dialect is inherited before validation — the draft already carries it, and an agent resending the value it read is the round trip this saves; present, it must equal the established one, and a different dialect is `template.validation.dialect_invalid` with `details.dialect` / `details.established_dialect` / `details.template_id`, refused before validation or any write (a template's pipeline nodes pin it against a source of that dialect; another engine is a new template). The REST `PUT /templates` (§8.4) is unchanged — it still requires the field and does not compare it; the inheritance is the MCP surface's, resolved before the shared write. There is deliberately no `confirm_new_root` argument: the update names a template that already exists and cannot mint a folder. **`implements` on update (7e, [Templates §3.4](/docs/templates.md#34-implements-and-drift)).** The citations ride the same write and are NOT content — outside `body_hash`, so they never decide whether a draft opens. Stated, they replace the written version's citations (`[]` clears); **omitted, they are inherited** from the version the edit is based on (owner ruling 2026-09-25): an in-place draft write keeps the draft's, a write that opens a new draft copies the released version's, a §5.1 no-op keeps its own. A write whose content equals the released version and that states `implements` lands the citations **on the RELEASED version** — no draft, no version burned — which is how a `needs_review` version is cleared (cite the superseding fact). While a draft is open the existing precondition makes every write the draft's, so a released version's citations are edited when no draft is open. Refusals as on create: `template.implements_unresolved`, and `template.blocks_not_allowed` on `sql`/`html`. **Permission:** `template.update`. **Mutating.** #### 6.2.37 `semantics_record` Record ONE fact you learned about a datasource that introspection could not tell you ([learned-semantic-layer design](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-11-learned-semantic-layer-design.md) §7.1) — the second half of the skill's *learn before you assume* step: what a session learned should not die with it. ```json { "name": "semantics_record", "description": "Record ONE fact you learned about a datasource that introspection could not tell you — a unit, a time zone, a sample rate, a grain, what a coded value means, a join that holds, a trap — so the next session reads it beside the columns instead of probing again. Never record what introspection already returns (types, keys, comments). refs name the table(s) and column(s) the fact is about, structurally; every ref is checked against the live schema and an unknown one is refused. The fact's text must agree with its refs: a catalog table the text names but refs omit is ADDED to the stored refs for you (refs_added in the result names what was added); a near-miss of a listed table is refused as semantics.ref_mismatch (the refusal names the nearest listed table). A lake table's ref takes schema as ONE dotted namespace string ({\"schema\": \"lake.mart\", \"table\": \"events\"}), never a namespace array. Pass evidence_sql (the SELECT that showed the fact): it runs once, its first rows become evidence_summary, and the fact is stored as observed — without it the fact is only asserted. scope DATASOURCE is about the data over the table's WHOLE window and is shared with every workspace the datasource is granted to — an observation tied to one window belongs in the pipeline description, not in a fact; scope WORKSPACE (definition, exclusion, preference) is this organisation's meaning and stays here — and a WORKSPACE rule may carry NO refs: a rule that spans datasources ('busiest day = A + B combined') is recorded once, against the datasource the question is mostly about, and names no table; a DATASOURCE fact always names at least one (a unit without a column is meaningless — semantics.fact_invalid). To correct a stale or wrong fact, record the replacement with supersedes: the old one is retired as superseded. An identical live fact is refused as semantics.duplicate. Mutating.", "inputSchema": { "type": "object", "required": [ "scope", "datasource", "kind", "fact" ], "additionalProperties": false, "properties": { "scope": { "type": "string", "enum": [ "DATASOURCE", "WORKSPACE" ], "description": "DATASOURCE: a fact about the data, visible wherever the datasource is granted. WORKSPACE: this organisation's meaning, visible here only." }, "datasource": { "type": "string", "description": "Datasource name (must be granted to this workspace)." }, "kind": { "type": "string", "enum": [ "unit", "time_zone", "sampling", "grain", "window", "enum_meaning", "join", "caveat", "format", "definition", "exclusion", "preference" ], "description": "What the fact is about. unit, time_zone, sampling, grain, window, enum_meaning, join, caveat, format are DATASOURCE kinds; definition, exclusion, preference are WORKSPACE kinds." }, "fact": { "type": "string", "minLength": 8, "maxLength": 1000, "description": "The fact, in one or two sentences, specific enough to act on: 'value is already in the unit named by unit_col', 'pickup_ts is naive local time (America/New_York)'." }, "refs": { "type": "array", "items": { "type": "object", "required": [ "table" ], "additionalProperties": false, "properties": { "schema": { "type": "string", "description": "Namespace as datasources_get_tables reported it (a label, or the dotted catalog.schema form). Omit for the connection's current schema." }, "table": { "type": "string", "description": "Table name exactly as datasources_get_tables returned it." }, "column": { "type": "string", "description": "Column name exactly as datasources_get_columns returned it. Omit for a table-grain fact (grain, window, sampling, a table-level caveat)." } } }, "description": "The object(s) the fact is about. One ref for a column fact, two for a join, a column-less ref for a table-grain fact. Required (at least one) for a DATASOURCE kind; a WORKSPACE rule that spans datasources omits it." }, "evidence_sql": { "type": "string", "description": "ONE read-only SELECT/WITH that shows the fact (no :parameters). Runs once at record time under the sql_probe rules; a statement that fails refuses the record." }, "evidence_summary": { "type": "string", "maxLength": 300, "description": "What the evidence showed, in your words. Defaults to the probe's first rows." }, "source_pipeline_id": { "type": "string", "format": "uuid", "description": "The pipeline you learned this while building, if any. Shown only to readers who can read that pipeline." }, "source_version": { "type": "integer", "minimum": 1 }, "supersedes": { "type": "string", "format": "uuid", "description": "The id of the fact this one replaces (a stale or wrong one); it is retired with reason superseded." } } } } ``` Returns: the stored fact in the FULL shape — the §6.2.18a block plus `datasource`, `refs[]` (`{schema, table, column}`, normalised), `evidence_sql`, `recorded_by`, `source_version`, `supersedes`, `retired_at`/`retired_reason` (omitted-when-absent) — plus `refs_added` when the tool appended a ref for you (a catalog table the text named exactly and `refs` omitted; the stored `refs[]` already carries it, each entry the same `{schema, table, column}` shape). Validation, in order, each a catalogued refusal before anything is written ([Pipeline Contract §13.15](/docs/pipeline-contract.md#1315-learned-semantics)): `kind` in the closed list AND of the requested scope (`semantics.kind_invalid`); the fact window, `refs ≥ 1`, the summary cap (`semantics.fact_invalid`); every ref resolves against the LIVE schema — one column read per referenced table, which is also the table's fingerprint (`semantics.ref_unresolved`; the store never starts stale); no identical live fact (`semantics.duplicate`, `details.existing_id`); `evidence_sql` runs ONCE through the `sql_probe` path — a statement the classifier refuses or that names a `:parameter` is `semantics.evidence_refused`, one the database refuses or that times out is `semantics.evidence_failed`, and the fact is NOT recorded. Trust follows the evidence: `observed` with it, `asserted` without. `supersedes` retires the named fact with reason `superseded` in the same call; an id the workspace cannot see — or a `source_pipeline_id` it cannot read — is the not-found answer (D-R5). A DATASOURCE-scope record on a datasource not granted to this workspace is the §5.3 not-found BEFORE anything runs — that gate IS the grant requirement (D-S8). Audited as `semantics.recorded` (kind, scope, datasource, refs, trust, via — never the fact text or the SQL) beside the dispatcher's `mcp.tool.write`. **`refs` is optional for a WORKSPACE rule (136 §B / T278).** A `definition`, `exclusion` or `preference` that spans datasources ("busiest day = A + B combined") is recorded ONCE, against the datasource the question is mostly about, with `refs` omitted (or `[]`): it stays bound to that datasource — for visibility, and for the `definitions` block §6.2.10/§6.2.11 carry — but names no table, so nothing resolves it and nothing marks it stale. The text/refs check still runs against that datasource's catalog (an exact table name in the text is added as a ref, a near-miss is `semantics.ref_mismatch`); a table of ANOTHER datasource in the text is prose. A DATASOURCE kind keeps requiring at least one ref — a `unit` without a column is meaningless — refused as `semantics.fact_invalid` (`details.field: "refs"`). **Permission:** `semantic.record` — recording is an authoring act (D-S8), the same bar as writing a pipeline that reads the datasource. **Mutating.** #### 6.2.38 `semantics_list` The facts on a datasource this workspace can see, with trust, drift, refs, evidence and provenance. ```json { "name": "semantics_list", "description": "List the learned facts recorded on a datasource this workspace can see — every DATASOURCE fact (whoever recorded it) and this workspace's own WORKSPACE facts — with trust, drift, refs, the evidence SQL and who recorded it through what. The same facts also arrive inline on datasources_get / _get_tables / _get_columns, which is where to read them while authoring; use this to review, to find a fact's id to supersede or retire, or to answer 'what was recorded since