# Metadata Database Schema Specification **Status:** v1.49 (frozen — Flyway V1 migration source of truth; **sole DDL authority**, D4) **Owner:** datapipelines.co core **Depends on:** all specs (this is the physical schema for every logical model) **Last updated:** 2026-10-25 --- ## 1. Purpose This spec defines the **complete physical schema** for the app's own metadata Postgres database. Every table, column, type, constraint, index, and foreign key is specified here. The Flyway V1 migration is generated from this document; the two must never diverge. **This is the only doc that writes DDL.** [Datasources §7.2](/docs/datasources.md#72-schema) and [Templates §12.2](/docs/templates.md#122-persistence) state the *semantics* their specs depend on and point here for the `CREATE TABLE` blocks. Where a logical model and this schema appear to disagree, this document's DDL is what ships. **This is NOT a user datasource.** Pipelines query user-configured datasources (PG, Oracle, MSSQL, etc.) via the Datasource Registry. This metadata DB is the app's own internal storage — it lives in Postgres and is managed by Spring Boot via `NamedParameterJdbcTemplate`. **This is not the only store.** Redis holds the transient state described in [§9](#9-what-is-not-in-this-database) — it has no tables here and never will. --- ## 2. Conventions - **Postgres 16+** required (uses `gen_random_uuid()`, JSONB, `TEXT[]`, partial indexes). - **All timestamps** are `TIMESTAMPTZ` (timestamp with time zone). Stored in UTC. Never use `TIMESTAMP` (without TZ). - **All IDs** are `UUID` (generated by Postgres via `gen_random_uuid()`). Not sequential — safe to expose in URLs without enumeration risk. - **JSON columns** are `JSONB` (binary, queryable, compressed). Never `TEXT` or `JSON`. Every JSONB column carries the **`_json` suffix** in its name — `definition_json`, `parameters_json`, `properties_json`, `node_stats_json`, `details_json`, `payload_json`, `error_json`, `body_json`, `imports_json`. The suffix is a naming rule, not a hint: a column without it is not JSONB. No exceptions in §4. - **String columns** are `TEXT` (Postgres `TEXT` has no length limit; `VARCHAR(n)` is only for enforcing max length, which we don't need for most columns). - **Boolean columns** default to `FALSE` unless noted. - **Soft deletes** use `is_deleted BOOLEAN DEFAULT FALSE`. Queries filter `WHERE is_deleted = FALSE`. - **Encryption** uses `BYTEA` for ciphertext (AES-256-GCM, see [Datasources spec](/docs/datasources.md#7-credential-storage)). - **Naming**: `snake_case` for all identifiers. Table names are plural (`users`, `pipelines`). Column names are singular (`email`, `created_at`). - **Foreign keys** use `ON DELETE CASCADE` for child tables that don't make sense without their parent (e.g., `pipeline_versions` without `pipelines`). Use `ON DELETE RESTRICT` (default) for reference columns (e.g., `owner_id` in `pipelines` — don't delete a user who owns pipelines). - **`updated_at` maintenance:** every table that has an `updated_at` column gets it set **by the application, in the `SET` clause of every `UPDATE` statement** (`updated_at = NOW()`). There are **no triggers** in this schema — no `BEFORE UPDATE` trigger, no `moddatetime` extension. Rationale: a trigger hides a write from the statement that caused it, and this schema is small enough that the discipline is a code-review item, not an operational risk. The `DEFAULT NOW()` on the column covers INSERT only. An UPDATE that forgets `updated_at` is a bug in the repository method. - **Immutable tables carry no `updated_at`** (`execution_events`, `audit_log`) — see the per-table notes. Since V6 the version tables carry one **narrowly**: `pipeline_versions` / `template_versions` record the last DRAFT write in `updated_at`/`updated_by` (the 409 conflict details of [versioning §4.2](/docs/versioning.md)); a release or discard does not restamp it, and RELEASED/DISCARDED rows are never UPDATEd otherwise (versioning §3.1's amended immutability discipline). --- ## 3. Entity Relationship Diagram ``` users ──1:N── api_keys users ──1:N── pipelines (owner_id) users ──1:N── templates (created_by) users ──1:N── datasources (created_by) users ──1:N── pipeline_executions (executed_by) users ──1:N── audit_log users ──1:N── pipeline_versions (created_by) users ──1:N── template_versions (created_by) users ──0:N── workspaces (created_by; NULL = system-provisioned) workspaces ──1:N── workspace_members users ──1:N── workspace_members workspaces ──1:N── pipelines (workspace_id) workspaces ──1:N── templates (workspace_id) workspaces ──0:N── datasources (workspace_id; NULL = global) workspaces ──1:N── api_keys (workspace_id) pipelines ──1:N── pipeline_versions pipelines ──1:N── pipeline_executions pipeline_versions ──1:N── pipeline_executions (composite FK on (pipeline_id, version) — §4.6) pipeline_executions ──1:N── execution_events templates ──1:N── template_versions datasources ──1:N── lake_tables (datasource_id references the datasources NAME primary key — §4.15) datasources ──1:N── learned_facts (datasource_name references the NAME primary key, ON DELETE CASCADE — §4.18) workspaces ──1:N── learned_facts (workspace_id: the WORKSPACE-scope binding; recorded_in: provenance — §4.18) learned_facts ──1:N── learned_facts (supersedes — the drift history, §4.18) template_versions ──1:N── template_implements (composite (template_id, version), ON DELETE CASCADE — §4.21) learned_facts ──1:N── template_implements (fact_id, NO cascade — a cited fact is retired, never deleted, §4.21) users ──1:N── mail_sends (the claim row behind every notice sent about or to the user, ON DELETE CASCADE — §4.19) pipelines ──1:N── pipeline_check_runs (the server-run release-check history of the pipeline's versions — §4.20) ``` Not shown, because they are not foreign keys: `audit_log.key_id` names an `api_keys` row without referencing it (the audit trail outlives the key), and `template_versions.imports_json` references other template versions inside a JSONB array (validated at save time, D2 — a JSONB array cannot carry an FK). The `{id, version}` entries in that array — and the `template` refs inside `pipeline_versions.body_json` — name a template by its human id (`templates.name` since V4), never by the surrogate `templates.id`. --- ## 4. Table Definitions ### 4.1 `users` OIDC-authenticated users, plus optional local password accounts (auth.md §5A). OIDC users are provisioned automatically on first login; local accounts are created by an admin or seeded as the bootstrap admin. See [Auth spec §4](/docs/auth.md#4-user-identity). ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL, profile_picture_url TEXT, provider TEXT NOT NULL, -- OIDC registration name (free text: 'google', 'okta', 'company-sso', etc.), -- or a placeholder with meaning: 'bootstrap' (pre-provisioned, auth.md §4.4), 'local' (admin-created, §5A), -- 'system' (the System actor, §4.5), 'key' (a key's own identity, §4.7) provider_subject TEXT NOT NULL, -- OIDC 'sub' claim is_active BOOLEAN NOT NULL DEFAULT TRUE, is_admin BOOLEAN NOT NULL DEFAULT FALSE, theme_preference TEXT, -- NULL = use the deployment default password_hash TEXT, -- NULL = OIDC-only account; Argon2id when set (V5, auth.md §5A) password_changed_at TIMESTAMPTZ, -- when the current hash was set (V5) must_change_password BOOLEAN NOT NULL DEFAULT FALSE, -- forced-change gate (V5, auth.md §5A.4) failed_login_count INTEGER NOT NULL DEFAULT 0, -- consecutive local-login failures (V5) locked_until TIMESTAMPTZ, -- per-account lockout horizon (V5, auth.md §5A.3) created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), last_login_at TIMESTAMPTZ, kind TEXT NOT NULL DEFAULT 'human', -- V34 (#215): 'human' | 'service' (a key's own identity) | 'system' CONSTRAINT chk_users_kind CHECK (kind IN ('human', 'service', 'system')) ); CREATE UNIQUE INDEX uq_users_provider_subject ON users(provider, provider_subject); ``` **Notes:** - `email` is UNIQUE — one account per email, regardless of provider. If a user logs in via Google first, then Okta with the same email, the record is updated (provider switches). The UNIQUE constraint's implicit index (`users_email_key`) is the login lookup path; no separate index is created. - `(provider, provider_subject)` is also UNIQUE — one record per OIDC identity. - `provider` is free text (no CHECK constraint) — stores whatever OIDC registration name the deployment configured, or one of two placeholders with meaning: `'bootstrap'` (pre-provisioned, never logged in — [Auth §4.4](/docs/auth.md#44-bootstrap-admin)) and `'local'` (admin-created local account — [Auth §5A.1](/docs/auth.md#5a1-accounts)). See [Auth spec §5.1](/docs/auth.md#51-provider-configuration-generic). - `is_active` is **read on every authenticated request**, not only at login: both the JWT filter and the API-key filter re-check it through the 60-second lookup cache described in [Auth §11.4](/docs/auth.md#114-api-key-validation-cache) (D13). Deactivating a user therefore takes effect within ~1 minute without any token revocation step. Practical consequence for this table: reads of `users` by `id` are hot and cached — do not add columns whose staleness for 60s would be unsafe. - `theme_preference` is a **stored user preference, not session state** — it survives logout and follows the user across browsers, which session storage would not. Written by the profile screen (`PATCH /partials/profile/theme` → `UPDATE users`, [UI Screens §4.11](/docs/ui-screens.md#411-user-settings)). - **`NULL` is meaningful:** it means "no explicit choice — use the deployment default [`datapipelines.ui.theme`](/docs/configuration.md#310-ui)". It is not the same as storing the default's current value: a NULL row *follows* the deployment default when an operator changes it, whereas a materialized copy would silently pin the user to yesterday's default. This is why the column is nullable with no DEFAULT. - **No CHECK constraint.** Valid values are the theme list in [Configuration §3.10](/docs/configuration.md#310-ui), validated by the application on write. A CHECK here would mean a migration every time a theme is added, and would reject rows that were valid when written if a theme were ever retired — the wrong failure mode for a cosmetic preference. - `updated_at` is set by the application in every UPDATE (§2) — including deactivation, login (`last_login_at`), and the theme PATCH. - `kind` (V34, #215, [Auth §4.7](/docs/auth.md#47-key-identities), [Enums §8](/docs/enums.md#8-userkind--what-a-users-row-is)): `human` for a person, `service` for an `endpoint` or `server` key's own identity (provider `key`, `provider_subject` = the key id, email `@keys.invalid`, no password, `is_admin` FALSE), `system` for the System actor. V34 set `system` on the System row, `human` on every other, and created one `service` row per existing endpoint/server key (inactive for a revoked key). ONE predicate, `kind = 'human'`, guards every login, linking and user-administration path; the admin users page, members lists and invitations read people only. - `password_hash` (V5) holds the Argon2id encoded hash of a local account's password ([Auth §5A](/docs/auth.md#5a-local-password-accounts-optional), same `SecretHasher` as API keys, §7.2). **`NULL` means OIDC-only** — such an account can never authenticate locally, and every pre-V5 row backfilled NULL. The plaintext password is never stored, never logged, and (except the one-time bootstrap seed, auth.md §5A.2) never in config. - `must_change_password` (V5) is set TRUE by every seed/admin-reset and cleared only by the self-service change; while TRUE the forced-change gate redirects every authenticated route to the change-password screen (auth.md §5A.4). - `failed_login_count` / `locked_until` (V5) back the per-account lockout (auth.md §5A.3): consecutive failures increment the count, the count reaching the configured maximum sets `locked_until`, and a successful login or an admin reset clears both. No CHECK constraint — the bounds are applied by the repository's atomic UPDATE, the same application-maintained discipline as `updated_at` (§2). ### 4.2 `api_keys` Keys (keys v2, V37): every key is a robot member of one workspace — the role is chosen at creation from the creator's own permissions, and kind is the transport. See [Auth spec §7](/docs/auth.md#7-api-keys). ```sql CREATE TABLE api_keys ( id TEXT PRIMARY KEY, -- 'dpk_ABCDEFGHIJKL' user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- who the key ACTS AS: its own `service` identity on EVERY kind (V34 endpoint/server; V37 mcp too) created_by UUID NOT NULL REFERENCES users(id), -- V34 (#215 B4): who created the key workspace_id UUID NOT NULL REFERENCES workspaces(id), -- pinned at issuance (V4, D3) name TEXT NOT NULL, -- 'Claude Desktop key' key_hash TEXT NOT NULL, -- Argon2id hash of full key is_revoked BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), last_used_at TIMESTAMPTZ, expires_at TIMESTAMPTZ, last_used_ip INET, last_used_user_agent TEXT, kind TEXT NOT NULL DEFAULT 'mcp', -- 'mcp' (V37, renamed from 'user') | 'endpoint' (V11) | 'server' (V17) | 'dashboard' (V45) secret_sealed BYTEA, -- V31: the full key, sealed (D16); NULL pre-R3, and NULL again from the first Copy on (#213 show-once; V32 cleared every pre-amendment copy) role TEXT, -- V37 (keys v2 A13/A14): the CHOSEN role — author|promoter|workspace_admin on mcp, api_caller on endpoint, promotion_receiver on server, dashboard_viewer on dashboard (V45); NULL only on a revoked pre-v2 row CONSTRAINT chk_api_keys_kind CHECK (kind IN ('mcp', 'endpoint', 'server', 'dashboard')), CONSTRAINT chk_api_keys_role CHECK ( (kind = 'mcp' AND role IS NOT NULL AND role IN ('author', 'promoter', 'workspace_admin')) OR (kind = 'mcp' AND role IS NULL AND is_revoked) OR (kind = 'endpoint' AND role IS NOT NULL AND role = 'api_caller') OR (kind = 'server' AND role IS NOT NULL AND role = 'promotion_receiver') OR (kind = 'dashboard' AND role IS NOT NULL AND role = 'dashboard_viewer')) ); CREATE INDEX idx_api_keys_user ON api_keys(user_id) WHERE is_revoked = FALSE; CREATE INDEX idx_api_keys_expires ON api_keys(expires_at) WHERE expires_at IS NOT NULL AND is_revoked = FALSE; CREATE INDEX idx_api_keys_endpoint_kind ON api_keys(workspace_id) WHERE kind = 'endpoint' AND is_revoked = FALSE; -- V37 (keys v2 A18): a key's NAME is unique within its workspace among live keys. CREATE UNIQUE INDEX uq_api_keys_live_workspace_name ON api_keys(workspace_id, name) WHERE is_revoked = FALSE; CREATE INDEX idx_api_keys_created_by ON api_keys(created_by); -- V34: the creator's own-keys list ``` **Notes:** - `id` is the public key prefix (`dpk_...`), not a UUID — it is the lookup handle presented in the `DP-API-Key` header (D10). `key_hash` is the Argon2id hash of the *full* key; the secret itself is returned exactly once at creation and never stored. - `workspace_id` (V4) pins the key to exactly one workspace at issuance (workspaces design D3): an agent key is a workspace-scoped credential, and the pinned workspace — not a request header — is the key's context. V4 backfills existing keys to `default`; slice 1 issues every new key into `default` from repository code (no column DEFAULT — slice 2 must find every pin by grepping the constant, [§4.11](#411-workspaces)). - `user_id` / `created_by` / `role` (V34 #215 + V37 keys v2 — [Auth §4.7](/docs/auth.md#47-key-identities), [§7.5](/docs/auth.md#75-key-roles)): `user_id` is who the key ACTS AS — its OWN `service` identity on every kind (endpoint/server since V34, `mcp` since V37 A13; created with the key in one transaction; revoking the key deactivates it). `created_by` is the person who created the key (the Keys page's "Created by"; own-key listing and revocation are creator-scoped; removing the member revokes the keys they created, A17/B6; since A20 the creator's `users.is_active` gates an `mcp` key's liveness too — a key converted by V37 whose owner row is inactive stays converted but is refused at request time until the owner is reactivated, which is why the conversion CTE reads the membership and never `users` itself). `role` is CHOSEN at creation under the subset rule (A14) — `author|promoter|workspace_admin` on an `mcp` key, the transport role on the others. The CHECK spells every role-bearing arm with `role IS NOT NULL`, since a bare comparison is UNKNOWN for a NULL role and a CHECK passes on UNKNOWN — V37's first draft omitted it on the `mcp` arm and ADMITTED a live NULL-role row (measured 2026-09-25; the arm now spells it). `scopes` was dropped (PK8); V34 refuses to run unless identities created = keys repointed = endpoint + server keys. - `is_revoked` and `expires_at` are both re-checked on every request through the 60s cache in [Auth §11.4](/docs/auth.md#114-api-key-validation-cache) (D13), so revocation takes effect within ~1 minute. - Revocation is a soft flag, not a DELETE: `audit_log.key_id` must keep resolving to something meaningful. The retention sweep's purge hard-deletes a revoked key and its identity only once NOTHING references them (A17/B5). - `secret_sealed` (V31, [Auth §7.4](/docs/auth.md#74-issuance), D16): a created key's plaintext is sealed with the deployment's credential-encryption key (AAD = the key id) so the Keys page's show-once can open it — ONCE (#213, D16 amended 2026-09-23): the open and the clear are one owner-scoped statement, the key is hash-only from its first read on, and V32 nulled every pre-amendment copy fleet-wide. V37 (A15) dropped `minted_at_login` and its per-(user, workspace) unique index with the login mint: the Keys page is the one creation path. - `kind` (V11, renamed V37 A19) is the key's KIND ([Auth §7.7](/docs/auth.md#77-key-kinds-and-published-endpoint-bindings)): `mcp` (renamed from `user` — kind is the transport and the word says which) reaches `/mcp` and only `/mcp`; `endpoint` is a credential for published endpoints only; `dashboard` (V45) serves the dashboard runtime under its `dashboard_key_bindings` folders ([§4.36](#436-dashboard_key_bindings)). An `endpoint` key serves exactly the paths its rows in [`endpoint_key_bindings`](#414-endpoint_key_bindings) cover, and one with no binding on any ancestor of the path it presents at authorises **nothing** — the `dashboard` kind's binding rule is the same sentence over the name space. `DEFAULT 'mcp'` follows the rename. - No `updated_at` — the only mutations are `last_used_*` (written on use), `is_revoked` (written once) and `kind` (written once, at issuance), and all are self-timestamping or immutable. ### 4.3 `audit_log` Append-only audit trail for auth and admin events. See [Auth spec §10](/docs/auth.md#10-audit-log). ```sql CREATE TABLE audit_log ( id BIGSERIAL PRIMARY KEY, timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(), event TEXT NOT NULL, -- 'auth.login.success' etc. user_id UUID REFERENCES users(id), key_id TEXT, -- API key id, if auth via API key source_ip INET, user_agent TEXT, details_json JSONB NOT NULL DEFAULT '{}' ); CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC); CREATE INDEX idx_audit_user ON audit_log(user_id, timestamp DESC) WHERE user_id IS NOT NULL; CREATE INDEX idx_audit_event ON audit_log(event, timestamp DESC); ``` **Notes:** - Append-only: rows are INSERTed and never UPDATEd, so there is no `updated_at`. The only DELETE is the retention job ([§8.2](#82-audit-log-retention)). - `key_id` is deliberately **not** a foreign key to `api_keys(id)` — the audit trail must survive deletion of the key it names. - `details_json` holds the per-event payload defined by the emitting spec — [Auth §10.1](/docs/auth.md#101-events) for `auth.*` events, [Datasources §7.4](/docs/datasources.md#74-decryption-points-and-audit-log) for `datasource.*` events (both vocabularies registered in [Enums §15](/docs/enums.md#15-authauditevent--auth-audit-log-events)); it is subject to the redaction rules in [Observability §3](/docs/observability.md#3-logging) — no credentials, no `jdbc_url`. - Retention: [`datapipelines.audit.retention-days`](/docs/configuration.md#312-audit). **Partitioning candidate:** For high-volume deployments, partition by month (`PARTITION BY RANGE (timestamp)`). v1 ships as a single partition. ### 4.4 `pipelines` Pipeline metadata. One row per pipeline (not per version). See [Pipeline Contract spec](/docs/pipeline-contract.md). ```sql CREATE TABLE pipelines ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL REFERENCES workspaces(id), -- owning workspace (V4) name TEXT NOT NULL, -- machine name AND folder path; pipeline-contract §3.2 display_name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', owner_id UUID NOT NULL REFERENCES users(id), current_version INTEGER NULL, -- V18: nullable, no default — the sticky pointer (D60, 101) created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_pipelines_workspace_name UNIQUE (workspace_id, name) ); CREATE INDEX idx_pipelines_owner ON pipelines(owner_id); -- V19: plain (the is_deleted partial is gone) ``` **Notes:** - `name` is unique **per workspace** (V4, workspaces design D2 — pre-launch break, owner-ratified 2026-08-16); the constraint's implicit index (`uq_pipelines_workspace_name`) serves name lookup within a workspace — no separate index is created. A plain UNIQUE constraint, deliberately not a partial index: since 101/D59 names are unique FOREVER — a discarded pipeline keeps its name (restore must always work) and no second entity may take it. - **`current_version` is the sticky pointer** (V18 made it nullable; 101/D60 made it event-driven): the version every pointer-following dependent runs. NULL until the first release (D55) and after a discard of the version it named with no eligible survivor; it moves only on release / discard-of-current / restore-above-current / manual switch / purge-of-current-draft, and an import moves it only when the entity has no current at all. It is NOT "the latest released" as a derived fact. - **There is no entity status column** (V19 retired `is_deleted`): a pipeline is ACTIVE while any version is DRAFT or RELEASED and DISCARDED when every version is — a derivation over `pipeline_versions`, read as an `EXISTS` probe; no reader may resurrect a stored flag. - `updated_at` is set by the application in every UPDATE (§2). ### 4.5 `pipeline_versions` Per-version pipeline bodies with their lifecycle state (V6, [versioning §3/§4](/docs/versioning.md)). See [Pipeline Contract §17.3](/docs/pipeline-contract.md#173-persistence). ```sql CREATE TABLE pipeline_versions ( pipeline_id UUID NOT NULL REFERENCES pipelines(id) ON DELETE CASCADE, version INTEGER NOT NULL, body_json JSONB NOT NULL, -- full pipeline JSON (per Pipeline Contract §3) status TEXT NOT NULL DEFAULT 'RELEASED' CONSTRAINT chk_pipeline_versions_status CHECK (status IN ('DRAFT', 'RELEASED', 'DISCARDED')), body_hash TEXT NOT NULL, -- SHA-256 (hex) of body_json's canonical projection, DB-computed released_at TIMESTAMPTZ NULL, -- DB-generated (NOW()) at release — never application-supplied; UNTOUCHED by discard/restore (V19) released_by UUID REFERENCES users(id), discarded_at TIMESTAMPTZ NULL, -- V19 (101): set at discard, cleared at restore; CHECK requires it when status = 'DISCARDED' discarded_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id),-- last DRAFT writer — powers the 409 conflict details updated_at TIMESTAMPTZ NULL, -- DRAFT writes only; not restamped at release/discard/restore created_via TEXT NOT NULL DEFAULT 'session', -- V20 (102): which surface wrote the row — the *_by fields name the person, always updated_via TEXT NOT NULL DEFAULT 'session', -- V20: moves with each draft write; release stamps nothing new caller_output_json JSONB NULL, -- V47 (#328): the release's record of its caller node's result columns — written ONLY by the release flip (D1); NULL on drafts, never-run releases and no-caller pipelines created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), PRIMARY KEY (pipeline_id, version), CONSTRAINT chk_pipeline_versions_discard_stamps CHECK ( (status = 'DISCARDED' AND discarded_at IS NOT NULL) OR (status <> 'DISCARDED' AND discarded_at IS NULL AND discarded_by IS NULL) ), CONSTRAINT chk_pipeline_versions_via CHECK ( created_via IN ('session', 'api_key', 'mcp') AND updated_via IN ('session', 'api_key', 'mcp') ) ); CREATE UNIQUE INDEX uq_pipeline_versions_one_draft ON pipeline_versions (pipeline_id) WHERE status = 'DRAFT'; ``` **Purge and executions (101):** a purged DRAFT's executions are deleted by the service in the SAME transaction (the `fk_executions_pipeline_version` constraint stays `NO ACTION` — the delete is explicit, auditable and tested: "a purge leaves zero orphan execution rows"). Redis result keys are not deleted; they expire on their own TTL, and an expired key is not a reference. The pre-101 executed-draft tombstone flip is withdrawn — drafts never reach DISCARDED. **Notes:** - `body_json` is the complete pipeline JSON as defined by [Pipeline Contract §3](/docs/pipeline-contract.md#3-top-level-pipeline-schema) — `schema_version`, `name`, `display_name`, `description`, `parameters`, `settings`, `nodes`. It already carries the `_json` suffix, so it is **not** renamed by the D4 sweep. - **The version lifecycle (V6, versioning §3.1).** RELEASED and DISCARDED rows are never UPDATEd; DRAFT rows may be — that is the one bounded exception to append-only, and only the DB predicate `status = 'DRAFT'` permits mutation. A write to a RELEASED version first copies it to a DRAFT (copy-on-write); a write to a DRAFT overwrites it in place. `pipelines.current_version` names the latest RELEASED version and does not move while a draft exists (§4.4), so every reader of the pointer keeps its semantics. Creation still lands v1 RELEASED — creation is not modification. - **`uq_pipeline_versions_one_draft` is the concurrency rule made physical.** At most one DRAFT per pipeline; two simultaneous first-writers both insert and the loser violates this index, surfacing as `pipeline.version.conflict` carrying the winner's hash (versioning §3.3). - **`body_hash` is computed by the database** — `encode(sha256(convert_to(body_json::text, 'UTF8')), 'hex')` — in V6's backfill and in every repository write, one expression everywhere: a JSONB column does not preserve the writer's key order, so the application's serialized string would be a drifting canonical anchor. The hash is the precondition token of every mutation (versioning §4.2) and the cross-server content identity (§11.2). - **`released_at` is database-generated** (`NOW()` in the release statement). [Versioning §8](/docs/versioning.md#8-executing-drafts) derives the draft-run label by comparing it against the application-supplied `pipeline_executions.started_at`; requiring both clocks to be right would double the failure window, so at most one side spans a clock. - `updated_at` belongs to the DRAFT write path; a release or discard does not restamp it (versioning §11) — the row's modification clock is the last draft write, and §2's every-UPDATE rule is a `pipelines`-table rule. - **The `*_via` columns name the SURFACE, never the credential (V20, owner ruling 2026-09-09).** `created_via` / `updated_via` are stamped at the entry point — the REST controllers map the principal's auth method, the MCP tools pass `'mcp'` (MCP is API-key-authenticated, so only the tool knows), and import/seed paths keep the `'session'` default. A key's writes remain its OWNER's in `created_by`/`updated_by`: no key id is stored on any row, and the CHECK's value set is what keeps a future key-id column from drifting back in as a value. - There is **no terminal-node column and no derived result-node column**. Under D1 the result node is the node whose `output` resolves to `caller` (at most one per pipeline, zero legal); it is a property of the stored JSON, read at execution time. Nothing about it is denormalized here — a denormalized copy would be a second source of truth for a value validation already guarantees. (`caller_output_json`, V47, is NOT such a copy: it is the RELEASE's observed record of the caller's columns — pipeline-contract §3.3.1 — not the body's declaration, and it is written only by the flip.) - **`caller_output_json` (V47, #328)** — the flip copies the version's latest qualifying execution's `result_schema_json` (D1: SUCCESS, root, non-null schema, started after the last draft write) in the release statement itself. Outside `body_hash`; a release with nothing to observe records NULL and still succeeds (`not_observed`). The D2 shape, the bounds and the reader's precedence are pipeline-contract §3.3.1. - `PRIMARY KEY (pipeline_id, version)` is also the target of the composite foreign key on `pipeline_executions` ([§4.6](#46-pipeline_executions)); it must not be reordered or dropped. The same FK is why an **executed** draft cannot be hard-deleted — discard flips it to DISCARDED and the number stays consumed (versioning §3.4). ### 4.6 `pipeline_executions` One row per pipeline execution. High-volume table — index carefully. ```sql CREATE TABLE pipeline_executions ( execution_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), pipeline_id UUID NOT NULL REFERENCES pipelines(id), pipeline_version INTEGER NOT NULL, -- snapshot of version at execution time status TEXT NOT NULL, -- 'RUNNING' | 'SUCCESS' | 'FAILED' | 'ABORTED' parameters_json JSONB NOT NULL DEFAULT '{}', -- the FULLY RESOLVED Context (see below) executed_by UUID NOT NULL REFERENCES users(id), -- the run's user: the session's, or who the key ACTS AS — the member for an MCP key, the key's own identity for an endpoint/server key since V34 (was triggered_by; renamed by V30, D11) executed_by_key_kind TEXT, -- 'user' | 'mcp' (V37) | 'endpoint' | 'server' | 'dashboard' (V45) when a key started it; NULL = a signed-in session (V30) triggered_via TEXT NOT NULL, -- 'UI' | 'REST' | 'MCP' | 'PIPELINE' | 'ENDPOINT' | 'SCHEDULE' | 'DASHBOARD' correlation_id UUID, started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), completed_at TIMESTAMPTZ, duration_ms BIGINT, failed_node_id TEXT, error_json JSONB, -- error envelope if FAILED node_stats_json JSONB, -- array of node stats result_row_count BIGINT, -- rows in the caller result; NULL if the pipeline has no caller node result_size_bytes BIGINT, -- materialized result size in Redis; NULL if no caller node result_schema_json JSONB, -- V47 (#328): the caller result's columns as {name, type, nullable} — history, durable past the Redis TTL; NULL if no caller node, no record or past the record bounds parent_execution_id UUID REFERENCES pipeline_executions(execution_id), -- spawning execution; NULL for roots (V3) parent_node_id TEXT, -- PIPELINE node id in the parent that spawned this execution; NULL for roots (V3) root_execution_id UUID NOT NULL, -- top ancestor; equals execution_id for roots — backfilled = own id (V3) heartbeat_at TIMESTAMPTZ, -- the owning instance's liveness stamp while RUNNING; NULL on a pre-V21 row (V21, §8.3) CONSTRAINT chk_status CHECK (status IN ('RUNNING', 'SUCCESS', 'FAILED', 'ABORTED')), CONSTRAINT chk_triggered_via CHECK (triggered_via IN ('UI', 'REST', 'MCP', 'PIPELINE', 'ENDPOINT', 'SCHEDULE', 'DASHBOARD')), -- 'PIPELINE' added by V3, 'ENDPOINT' by V11, 'SCHEDULE' by V38, 'DASHBOARD' by V43 CONSTRAINT chk_executions_executed_by_key_kind CHECK (executed_by_key_kind IS NULL OR executed_by_key_kind IN ('user', 'mcp', 'endpoint', 'server', 'dashboard')), -- V30; 'mcp' added by V37, 'dashboard' by V45 CONSTRAINT fk_executions_pipeline_version FOREIGN KEY (pipeline_id, pipeline_version) REFERENCES pipeline_versions (pipeline_id, version) ); CREATE INDEX idx_executions_pipeline ON pipeline_executions(pipeline_id, started_at DESC); CREATE INDEX idx_executions_status_running ON pipeline_executions(started_at) WHERE status = 'RUNNING'; CREATE INDEX idx_executions_user ON pipeline_executions(executed_by, started_at DESC); -- the column was renamed under it (V30); the index kept its name CREATE INDEX idx_executions_correlation ON pipeline_executions(correlation_id) WHERE correlation_id IS NOT NULL; CREATE INDEX idx_executions_root ON pipeline_executions(root_execution_id); -- family lookup / cancellation (V3) CREATE INDEX idx_executions_heartbeat ON pipeline_executions(heartbeat_at) -- the crash sweep's other access path (V21, §8.3) WHERE status = 'RUNNING'; ``` **Notes:** - **`fk_executions_pipeline_version` is the point of this table's integrity.** `pipeline_version` is a snapshot of the version executed, and without the composite FK it was a free-floating integer — nothing stopped an execution from recording a version that was never stored, and the "which JSON actually ran?" question had no reliable answer. The target is `pipeline_versions`' primary key `(pipeline_id, version)`. - The single-column `pipeline_id REFERENCES pipelines(id)` is retained alongside it. It is *implied* by the composite FK (every `pipeline_versions` row has a valid `pipeline_id`), and is kept for ERD clarity and because it is the constraint that survives if `pipeline_versions` is ever partitioned. - **Delete interaction:** `pipelines` uses soft delete, so this does not arise in normal operation — but a *hard* `DELETE FROM pipelines` now fails while any execution references one of its versions. The `ON DELETE CASCADE` from `pipelines` to `pipeline_versions` cannot fire, because the composite FK is `ON DELETE RESTRICT` (default). This is the correct outcome: execution history must not be silently erasable. - `status` values are [`ExecutionStatus`](/docs/enums.md#10-executionstatus--whole-pipeline-execution-outcome); `triggered_via` values are [`ExecutionTrigger`](/docs/enums.md#18-executiontrigger--how-execution-was-initiated). Both CHECK constraints list only the shipped values — adding a trigger (`WEBHOOK`) later is a migration, deliberately (V3 added `PIPELINE`, V11 `ENDPOINT` and V38 `SCHEDULE` exactly that way). A `SCHEDULE` execution's `executed_by` is the system identity and its attribution — the schedule, and a Run now's person — is the scheduler's own [`schedule_runs`](#423-schedule_runs) row, joined by `execution_id` ([scheduler design revision](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-22-scheduler-design-revision.md) §4). - **`parameters_json` holds the FULLY RESOLVED Context, and its name is historical (072).** The row is inserted with the request's `parameters` object as sent, and the terminal UPDATE replaces it with what the nodes actually saw: org configuration (`org_*`), the platform keys (`current_date`, `current_timestamp`, `execution_id`), the declared parameters after defaulting, the execute-time inputs and every `CALCULATOR` node's output ([Calculators design §0.5](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-04-calculators-design.md), [DAG Executor §7.3](/docs/dag-executor.md#73-the-context-snapshot--pipeline_executionsparameters_json)). The name predates calculators and org config, when parameters WERE the whole Context; it is not renamed because a rename costs a migration and a re-read of every consumer, and this note costs a line. Without the snapshot a completed execution cannot answer "which fiscal quarter did this run report on?" — the caller's parameters do not contain it, and the configuration that produced it may since have changed. - **`result_schema_json` (V47, #328)** rides the same `recordResult` write as `result_row_count` / `result_size_bytes` — the stored result's columns, from the driver's `ResultSetMetaData` through the result store. **History, not availability**, exactly like its siblings: durable after the Redis key expires, and what the release flip (§4.5 `caller_output_json`) copies from. The D2 array shape, the 256-column / 128-character bounds (past them: record NULL + `warn`, never fail) and the JSON-null-`nullable` rule are pipeline-contract §3.3.1. Column NAMES and types only — never row values. - **Lineage columns (V3).** A PIPELINE node's child execution records `parent_execution_id` + `parent_node_id` (NULL for roots); `root_execution_id` is the top ancestor. The migration backfills `root_execution_id = execution_id` for pre-existing rows and sets it NOT NULL from then on — one indexed query (`idx_executions_root`) returns the whole family and cancellation keys off it, with no NULL special-case anywhere. The repository binds `root_execution_id` as the record's own `execution_id` when the caller leaves it null, so roots never have to repeat their id. - **No `result_delivery` column.** Under D9 there is exactly one delivery path (every caller result is materialized in Redis and read through the cursor), so the old `'inline' | 'claim_check'` discriminator describes a fork that no longer exists. `result_row_count` and `result_size_bytes` remain as history/observability facts — they describe a result that is very likely already expired, and they are **not** a claim that the result is still retrievable. Retrievability is a Redis TTL question ([§9](#9-what-is-not-in-this-database)). - Both result columns are NULL for pipelines with zero caller nodes (legal under D1 — pure write-back pipelines). - `updated_at` is absent by design: the row's lifecycle is INSERT-on-start then one terminal UPDATE, and `completed_at` already carries that timestamp. **Partitioning candidate:** Partition by month on `started_at` for large deployments. Note that the composite FK constrains this — a partitioned `pipeline_executions` still references an unpartitioned `pipeline_versions`, which Postgres supports; partitioning `pipeline_versions` would not. ### 4.7 `execution_events` The **durable** SSE event log per execution. Highest-volume table. Backs event replay via [`GET /api/v1/executions/{id}/events`](/docs/rest-api.md#103-replay-sse-stream). This is the 7-day record. It is *not* the store a live or just-finished consumer reads from — that is the 1-hour Redis event log ([§9](#9-what-is-not-in-this-database)). Both are written; only this one survives past the hour. ```sql CREATE TABLE execution_events ( id BIGSERIAL PRIMARY KEY, execution_id UUID NOT NULL REFERENCES pipeline_executions(execution_id) ON DELETE CASCADE, event_id INTEGER NOT NULL, -- monotonic per execution (1, 2, 3...) event_type TEXT NOT NULL, -- 'execution_started', 'node_started', etc. timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(), payload_json JSONB NOT NULL, CONSTRAINT uq_events_execution_event UNIQUE (execution_id, event_id) ); ``` **Notes:** - **No `idx_events_execution`.** The `UNIQUE (execution_id, event_id)` constraint creates a btree index on exactly `(execution_id, event_id)` — the replay query's access path. A second index on the same column list in the same order was pure write amplification on the highest-volume table in the schema, and it has been removed. Naming the constraint (`uq_events_execution_event`) rather than leaving it anonymous gives the index a stable, predictable name. - Append-only and immutable: no `updated_at`. The only DELETE is the retention job ([§8.1](#81-execution-event-cleanup)). - `event_type` values are the SSE event names in [Enums §11](/docs/enums.md#11-sseeventtype--pipeline-execution-event-types), including `execution_aborted` (D7). No CHECK constraint — the event vocabulary grows more often than the schema should. **Partitioning candidate:** Partition by month. Or partition by `execution_id` hash if executions are very numerous. **Retention:** Events are purged by [§8.1](#81-execution-event-cleanup) per [`datapipelines.executions.event-retention-days`](/docs/configuration.md#311-execution-history). The execution record itself (in `pipeline_executions`) is retained longer for history. ### 4.8 `templates` Template metadata. See [Templates spec](/docs/templates.md). ```sql CREATE TABLE templates ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- surrogate (V4) workspace_id UUID NOT NULL REFERENCES workspaces(id), -- owning workspace (V4) name TEXT NOT NULL, -- the human id: 'acme/finance/fetch_orders.sql' display_name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', current_version INTEGER NULL, -- V18: nullable, no default — the sticky pointer (D60, 101) created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), CONSTRAINT uq_templates_workspace_name UNIQUE (workspace_id, name) ); CREATE INDEX idx_templates_active ON templates(name); -- V19: plain (the is_deleted partial is gone) ``` **Notes:** - **Surrogate key (V4).** `id` was the TEXT human id (`'fetch_orders.sql'`) and the primary key until V4; per-workspace name uniqueness (workspaces design D2) required a surrogate, so `id` is now a generated UUID PK and the human id lives in `name`. Everything that referenced the TEXT id keeps working on `name`: pipeline-JSON `template: {id, version}` refs, `imports_json` `{id, version, alias}` entries, REST path params, and MCP tool arguments all mean `name`, resolved within the active workspace. Stored payloads are immutable and were **not** rewritten — nothing anywhere stores the surrogate except `template_versions.template_id` and the `templates` PK itself. - `name` is unique per workspace via `uq_templates_workspace_name` — a plain UNIQUE constraint like the pipelines rule ([§4.4](#44-pipelines)). Since 101/D59 names are unique FOREVER: a discarded template keeps its name (restore must always work), and saved references to its versions keep resolving ([Templates §5](/docs/templates.md#5-template-versioning)). - `idx_templates_active` indexed `templates(id)` before V4; the column rename carried it onto `name`, which is exactly the listing path it exists for. V19 rebuilt it plain — the `WHERE is_deleted = FALSE` partial went with the retired column, and the derived ACTIVE probe answers through the `(template_id) …` PK. - **`name` carries a FOLDER, and `V12__folder_required.sql` is the gate (077).** The [§4.1 grammar](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md#41-grammar) requires 2–10 `/`-separated segments; the column stays TEXT and the UNIQUE constraint is unchanged, because a folder is a name prefix and never a schema dimension. V12 carries **no DDL** — only a `DO`-block pre-check that ABORTS the migration and names every offending `templates.name`, active and soft-deleted alike (`lookupVersion` resolves soft-deleted rows for pinned refs, so their names are in scope). It has to be a deploy-time abort rather than a save-time refusal because a template name is re-validated at RENDER (`RegistryTemplateLoader.parseKey`, and the import-prologue synthesis), so a stored flat name would break execution of already-released pipelines with no in-place repair — [Template Hierarchy §4.6](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md#46-legacy-names-the-grammar-is-not-purely-a-widening-normative). This re-issues V7's gate rather than editing it: an applied migration's text is frozen by its Flyway checksum. - **The gate does NOT look at `pipelines`, deliberately.** `pipelines.name` takes the same grammar, but it is validated at SAVE only — nothing on the execute path re-checks it and pipelines are UUID-addressed — so a pre-077 flat pipeline keeps listing, opening and executing, and its next save is refused with `pipeline.validation.name_invalid`. Aborting a deployment over a row that still works would be a false alarm ([Template Hierarchy §14.2](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)). - **No `params_schema` column** (D3). Templates declare no parameters; the render context is the calling pipeline's `parameters` map with defaults applied — see [Templates §3](/docs/templates.md#3-template-entity). - **`is_library` lives on `template_versions`, not here** (§4.9). Import validation resolves it at an exact `{id, version}` ([Templates §6](/docs/templates.md#6-library-templates)), so a table-level copy would be a second source of truth that a new version could silently contradict. Listing "all libraries" joins to the current version. - `description` is `NOT NULL DEFAULT ''` here (unlike `datasources.description`, §4.10) because [Templates §3](/docs/templates.md#3-template-entity) makes it the discoverability field agents search on. The asymmetry with datasources is deliberate, not drift. - **`current_version` is the sticky pointer** (V18 nullable; 101/D60 event-driven — the twin of §4.4's rule: NULL until the first release, moved only by the events versioning §3.4 lists). **There is no entity status column** (V19 retired `is_deleted`): the ACTIVE/DISCARDED derivation reads `template_versions`. - `updated_at` is set by the application in every UPDATE (§2) — chiefly a pointer move or an index-metadata write. ### 4.9 `template_versions` Per-version template bodies with their lifecycle state (V6, [versioning §6](/docs/versioning.md)). ```sql CREATE TABLE template_versions ( template_id UUID NOT NULL REFERENCES templates(id) ON DELETE CASCADE, -- surrogate (V4) version INTEGER NOT NULL, engine TEXT NOT NULL DEFAULT 'freemarker', type TEXT NOT NULL DEFAULT 'sql', -- V8 (046); four values since V33 (7b) dialect TEXT NULL, -- V8: required iff type='sql' (chk_type_dialect) is_library BOOLEAN NOT NULL DEFAULT FALSE, imports_json JSONB NOT NULL DEFAULT '[]', -- array of {id, version, alias} body TEXT NOT NULL, -- template source; syntax per `engine` contract_json JSONB NULL, -- V33 (7b): the script contract (script types only) invariants_json JSONB NULL, -- V33: the invariants (script types only) tests_json JSONB NULL, -- V33: the test suite (script types only) status TEXT NOT NULL DEFAULT 'RELEASED' CONSTRAINT chk_template_versions_status CHECK (status IN ('DRAFT', 'RELEASED', 'DISCARDED')), body_hash TEXT NOT NULL, -- SHA-256 (hex) of the canonical version-owned object (see notes) released_at TIMESTAMPTZ NULL, -- DB-generated at release; UNTOUCHED by discard/restore (V19) released_by UUID REFERENCES users(id), discarded_at TIMESTAMPTZ NULL, -- V19 (101): set at discard, cleared at restore discarded_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id), updated_at TIMESTAMPTZ NULL, -- DRAFT writes only created_via TEXT NOT NULL DEFAULT 'session', -- V20 (102): which surface wrote the row updated_via TEXT NOT NULL DEFAULT 'session', -- V20: moves with each draft write created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), PRIMARY KEY (template_id, version), CONSTRAINT chk_dialect CHECK (dialect IS NULL OR dialect IN ('POSTGRES', 'ORACLE', 'MSSQL', 'MYSQL', 'H2', 'DUCKDB', 'SQLITE')), CONSTRAINT chk_template_type CHECK (type IN ('sql','html','jsonata','javascript')), -- V33 replaced V8's two-value form CONSTRAINT chk_type_dialect CHECK ( -- V33 replaced V8's "required unless html" form (type = 'sql' AND dialect IS NOT NULL) OR (type IN ('html','jsonata','javascript') AND dialect IS NULL) ), CONSTRAINT chk_script_blocks CHECK ( -- V33 (7b) as chk_transform_blocks; renamed by V50 (the TRANSFORM→SCRIPT node-type rename) — the blocks exist exactly on the script types (type IN ('jsonata','javascript') AND contract_json IS NOT NULL AND invariants_json IS NOT NULL AND tests_json IS NOT NULL) OR (type IN ('sql','html') AND contract_json IS NULL AND invariants_json IS NULL AND tests_json IS NULL) ), CONSTRAINT chk_template_versions_discard_stamps CHECK ( (status = 'DISCARDED' AND discarded_at IS NOT NULL) OR (status <> 'DISCARDED' AND discarded_at IS NULL AND discarded_by IS NULL) ), CONSTRAINT chk_template_versions_via CHECK ( created_via IN ('session', 'api_key', 'mcp') AND updated_via IN ('session', 'api_key', 'mcp') ) ); CREATE INDEX idx_template_versions_dialect ON template_versions(dialect); CREATE UNIQUE INDEX uq_template_versions_one_draft ON template_versions (template_id) WHERE status = 'DRAFT'; ``` **Notes:** - `template_id` re-keyed from the TEXT human id to the surrogate `templates.id` in V4 (§4.8) — same FK name (`template_versions_template_id_fkey`), same `ON DELETE CASCADE`, new type. Lookups by human id join `templates` on the surrogate and filter `name` within the workspace; the join back is how a stored version "resolves to its named template". - **The version lifecycle mirrors `pipeline_versions` (V6, versioning §6)**: same statuses, same one-draft partial index, same four write paths, same DB-computed `body_hash` discipline. Two template-specific differences. First, the canonical body is the **version-owned field object** — `jsonb_build_object('engine', …, 'dialect', …, 'is_library', …, 'imports', imports_json, 'body', body)`, extended since V33 (7b) with the CASE arm `|| CASE WHEN contract_json IS NOT NULL THEN jsonb_build_object('contract', contract_json, 'invariants', invariants_json, 'tests', tests_json) ELSE '{}'::jsonb END` — present exactly on the script types, so an `sql`/`html` row's hash is byte-identical before and after V33 (the migration test recomputes every pre-V33 row and finds zero changes; `type` is deliberately NOT in the hash — V8 explains why). Second, discard is always a hard delete — no EXECUTION references a `template_versions` row (pipeline pins are numbers in JSON, not constraints), so versioning §3.4's executed-draft DISCARDED branch cannot fire here. The one FK that does reference a version is [`template_implements`](#421-template_implements)' (V36, 7e), and it cascades: a purged draft takes its citations with it. - **No `params_schema` column** (D3) — deleted, not deprecated. Any migration from a pre-v1.1 draft drops it. - `engine` carries the [`TemplateEngine`](/docs/enums.md#6-templateengine--template-language) value. It is `NOT NULL DEFAULT 'freemarker'` and deliberately has **no CHECK constraint**: the accepted value is type-conditional — `freemarker` iff `sql`/`html`, `none` iff a script type (7b; enforced by save-time validation, D2), and future engines must not require a migration to become storable. The default exists for the sql/html write path, which never omits it. - `type` (V8, 046) is the template's kind, fixed at create and identical on every version (`template.validation.type_immutable` — the application twin of `chk_type_dialect`); V33 (7b) admits the two script types in `chk_template_type`. JavaScript rows save when the isolate is installed and the pooled test suite passes; without the isolate, save refuses with `script.js.unavailable`. The existing CHECK already admits the value; enabling JavaScript requires no schema change. - `contract_json` / `invariants_json` / `tests_json` (V33, 7b, D-T7) are the script blocks as **bound** JSON (templates.md §3.3). They are version content: the hash's CASE arm above folds them in exactly when present. `implements` (7e) is deliberately NOT among them and NOT in the hash — a citation is a claim about meaning, not a change of behaviour (R9). - `is_library` is version-scoped (§4.8). [Templates §6](/docs/templates.md#6-library-templates) resolves `template.validation.import_not_library` against the *exact* `{id, version}` an `imports` entry names, so this is the column that validation reads. A library body holds only macro/function definitions — `body` is still `NOT NULL`. - `imports_json` is a JSONB **array** of `{id, version, alias}` objects, `[]` when the template imports nothing. The body never contains `<#import>` (D12) — the engine synthesizes the prologue from this array. Nothing here enforces the referenced versions exist: that is save-time validation's job (D2), and a DB-level FK is impossible against a JSONB array. - `body` is `TEXT`, not JSONB — it is template source, so no `_json` suffix applies. ### 4.10 `datasources` Environment-specific database connections. See [Datasources spec](/docs/datasources.md). This table is where [Datasources §7.2](/docs/datasources.md#72-schema) points; every semantic that section lists is satisfied below. **Visibility is NOT here.** V23 replaced `workspace_id` (whose NULL meant "global") with two separate things: `owner_workspace_id`, which records the workspace that REGISTERED the datasource — NULL for an instance datasource a super admin registered — and `datasource_workspaces` (§4.16), the grant table that decides who can SEE it. Ownership and visibility were one column and are now two concepts, because a datasource shared with five teams has one owner and five grants (D-R7). ```sql CREATE TABLE datasources ( name TEXT PRIMARY KEY, -- 'pg-prod' display_name TEXT NOT NULL, description TEXT, -- OPTIONAL (nullable) — datasources.md §3.3 dialect TEXT NOT NULL, -- 'POSTGRES', 'ORACLE', etc. jdbc_url TEXT NOT NULL, username TEXT, -- NULL for the kinds that have none (V13) credential_kind TEXT NOT NULL DEFAULT 'password', -- §3.4 — WHAT the credential is (V13) credential_encrypted BYTEA, -- AES-256-GCM: version ‖ nonce ‖ ciphertext ‖ tag; NULL iff kind = 'none' (V13) properties_json JSONB NOT NULL DEFAULT '{}', -- {"hikari": {...}, "jdbc": {...}} query_timeout_seconds INTEGER, -- NULL = fall back to the global executor default introspection_include_schemas_json JSONB NOT NULL DEFAULT '[]', -- §7A allowlist: schemas exempt from the system-schema exclusion (V2) owner_workspace_id UUID REFERENCES workspaces(id), -- the OWNING workspace; NULL = an instance datasource (V23) is_readonly BOOLEAN NOT NULL DEFAULT FALSE, -- write-shaped uses forbidden (V4) last_test_at TIMESTAMPTZ, -- last connection test: when (V9) last_test_ok BOOLEAN, -- last connection test: did it authenticate and answer (V9) last_test_message TEXT, -- last connection test: driver message on failure, server version on success (V9) is_deleted BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), CONSTRAINT chk_datasource_name CHECK ( char_length(name) BETWEEN 1 AND 63 AND name ~ '^[a-z0-9_-]+$' ), CONSTRAINT chk_datasource_dialect CHECK ( dialect IN ('POSTGRES', 'ORACLE', 'MSSQL', 'MYSQL', 'H2', 'DUCKDB', 'SQLITE') ), CONSTRAINT chk_datasource_query_timeout CHECK ( query_timeout_seconds IS NULL OR query_timeout_seconds >= 1 ), CONSTRAINT chk_datasource_credential_kind CHECK ( -- V13 credential_kind IN ('password', 'token', 'private_key', 'service_account_json', 'none') ), CONSTRAINT chk_datasource_credential_present CHECK ( -- V13 (credential_kind = 'none') = (credential_encrypted IS NULL) ), CONSTRAINT chk_datasource_credential_username CHECK ( -- V13 CASE credential_kind WHEN 'password' THEN username IS NOT NULL WHEN 'token' THEN TRUE ELSE username IS NULL END ) ); CREATE INDEX idx_datasources_active ON datasources(name) WHERE is_deleted = FALSE; ``` **Notes:** - `name` is the primary key and the immutability anchor — pipelines reference datasources by this value, so rename is delete+create ([Datasources §11.1](/docs/datasources.md#111-rename-semantics--name-is-immutable)). `chk_datasource_name` mirrors the `datasource.validation.name_invalid` rule (`[a-z0-9_-]+`, length 1–63) at the database level. The 63-character bound is not cosmetic: the name appears in generated identifiers, and 63 is Postgres's identifier limit. - `description` is **nullable with no default** (D4/2.9.4). The previous `NOT NULL DEFAULT ''` made "absent" and "deliberately empty" indistinguishable while pretending the field was required; [Datasources §3.3](/docs/datasources.md#33-field-reference) says it is optional, and this column now says the same thing. - `properties_json` holds the [Datasources §5](/docs/datasources.md#5-connection-pool-configuration) object verbatim: `{"hikari": {...}, "jdbc": {...}}`, both namespaces optional, `{}` when neither is set. Keys are **not** validated by this schema or by a key allow-list — they are validated at save time by building a test pool (D7/D2), which is the only check that stays true as HikariCP and the drivers evolve. - `query_timeout_seconds` is a **first-class column, not a `properties_json` key** — it is a datasource-level execution policy the executor reads per node, not a pool or driver property, and it needs to be queryable. When set it overrides [`datapipelines.executor.node-query-timeout-seconds`](/docs/configuration.md#32-executor); NULL means "use the global default" ([Datasources §5.5](/docs/datasources.md#55-query-timeout-precedence)). - `dialect`'s CHECK duplicates the application-level validation on purpose — a bad dialect reaching this table would break every pipeline referencing the datasource, and the DB is the last place to catch it. Values are the [Type System §5](/docs/type-system.md#5-source-to-canonical-mapping-tables) dialect set. - `credential_encrypted` (V13; `password_encrypted` before it) is `BYTEA` ciphertext only ([Datasources §7.1](/docs/datasources.md#71-encryption-at-rest)) — never plaintext, never returned by any endpoint, and never logged. The master key is required and fail-fast (D8). The blob is **kind-agnostic**: a password, a bearer token, a PEM private key or a service-account JSON document are all sealed identically under the V10 versioned envelope with the datasource NAME as AAD, so a new credential kind needs no crypto change and rotation (§7.3) is unaffected. - The three V13 credential columns are one contract ([Datasources §3.4](/docs/datasources.md#34-credential-kinds)). `credential_kind` gains `DEFAULT 'password'`, which is what makes the backfill TRUE rather than a guess: every pre-087 row went through a save path that required a username and a password, so every one of them IS a password. `chk_datasource_credential_present` pins `kind = 'none'` ⟺ no ciphertext — that equivalence is why the §3.2 response can DERIVE `password_set` from the kind instead of carrying a read-side flag every constructor would have to get right. `chk_datasource_credential_username` restates §3.4's field rules at the column, as the backstop for a row written by restore or by hand; the application validator reports the same rules as field errors on a 400. `username` became nullable in the same migration: a private key, a service-account blob and "no credential" have no username, and the dummy value a NOT NULL column forced (`username: "sqlite"`, [Datasources §8A.1](/docs/datasources.md#8a1-file-shape)) is the lie V13 removes. - `created_by` is a real foreign key to `users(id)` (`ON DELETE RESTRICT`, the §2 default) — a user who owns datasources cannot be hard-deleted. - `workspace_id` (V4) binds the datasource to a workspace as **visibility/ownership only** — `name` stays the globally-unique PK and namespace (workspaces design D2/D3: it is the PK, the GCM AAD anchor, and the cross-env contract). **`NULL = global`**: existing rows backfilled NULL, preserving the pre-workspaces shared behavior (D9). `is_readonly` (V4) forbids the three write-shaped uses of the datasource in the pipeline contract — enforced since the readonly slice (save-time validation + executor backstop + the pool flag), and WRITABLE since the surfaces slice through the D8-gated registry save path (pool rebuilt on every flag write; `readonly` on a global datasource is admin-only). Since the surfaces slice both columns live on the entity and the repository's SQL: INSERT/UPDATE write `workspace_id`/`is_readonly` explicitly, and every read joins `workspaces` for the additive `workspace` name; visibility (`bound-to-active OR global`) is the repository's `findAllVisible`/`findVisibleByName` predicates — one authority for REST, MCP and the UI. - `idx_datasources_active` gives soft-delete parity with `pipelines` and `templates`: the registry's hot path is "list/lookup non-deleted datasources", and without it that scan had no supporting index. Note the PK index cannot serve it — `is_deleted` is not in the PK, so the filter would be a heap recheck on every row. - The three `last_test_*` columns (V9) record the outcome of the LAST connection test ([Datasources §8.1B](/docs/datasources.md#81b-the-last-tests-outcome-is-stored-and-listed)). All three NULL = **never tested**, which is the truthful state of every pre-V9 row — there is no outcome to backfill and no default that would not be an invention. They are written by the probe (`POST .../test`, the UI's Test button, and the §8A.3 rule-3 bootstrap check) and by nothing else; `last_test_message` is redaction-scrubbed at the probe, so no credential can reach the column. They exist because LISTING a datasource does not connect to it: on 2026-09-02 the screen showed a datasource as fine while every execution failed at CONNECT. - All timestamps are `TIMESTAMPTZ`; `updated_at` is set by the application in every UPDATE (§2), including credential rotation and pool-affecting property changes — **with one documented exception**: the `last_test_*` write does NOT move it. A test outcome is an observation about the datasource, not a change to it, and [Datasources §8A.3](/docs/datasources.md#8a3-semantics-create-if-absent-never-update) rule 1 promises an operator's row survives a boot byte-untouched, a promise that stays mechanically checkable only while a probe leaves the definition columns (`updated_at` among them) alone. ### 4.11 `workspaces` The unit of team isolation (workspaces design 2026-08-16, D1). Pipelines and templates belong to exactly one workspace; datasources bind by visibility only (§4.10); API keys pin one at issuance (§4.2). ```sql CREATE TABLE workspaces ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL UNIQUE, -- [a-z0-9_-]+, 1–63, immutable (referenced in config/UX) display_name TEXT NOT NULL, is_personal BOOLEAN NOT NULL DEFAULT FALSE, -- historical: the retired auto-per-user mode set it created_by UUID REFERENCES users(id), -- NULL = system-provisioned (R1) is_deleted BOOLEAN NOT NULL DEFAULT FALSE, deactivated_at TIMESTAMPTZ, -- D-R10: deactivate, never delete (V23) deactivated_by UUID REFERENCES users(id), -- who deactivated it (V23) created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_workspaces_active ON workspaces(name) WHERE is_deleted = FALSE AND deactivated_at IS NULL; ``` **Notes:** - **`created_by` is nullable (re-base resolution R1).** The design spec's `NOT NULL REFERENCES users(id)` gave V4's `default`-workspace seed no user to reference on a fresh install. NULL means **system-provisioned**; workspaces provisioned for a user later (auto-per-user mode) carry the real user. - **The `default` workspace carries the well-known constant UUID `defa0000-0000-0000-0000-000000000001` (re-base resolution R2).** V4 seeds it (`created_by NULL`, `is_personal = FALSE`) and backfills every pre-existing pipeline/template/api-key row onto it (D9: the pre-workspaces world was one shared space). Slice-1 repositories pin this constant in code — deliberately **not** a column DEFAULT and **not** a boot-time lookup or config key: the constant is deterministic across deployments and greppable, and slice 2 replaces the pins with real workspace resolution by finding every occurrence of the value. A boot-time DB lookup or a config key was considered and rejected for exactly those reasons. - `name` is globally UNIQUE (the namespace is flat — workspaces themselves are not scoped) and immutable, like datasource names. No CHECK constraint here: the `[a-z0-9_-]+`, 1–63 rule is validated by the application on write, matching how `templates.name` is handled (contrast `datasources`, whose CHECK exists because a bad name breaks every referencing pipeline). - `is_deleted` is the house soft-delete flag; the uniqueness of `name` includes soft-deleted rows (house rule — the name is not reusable until the row is hard-deleted). - **`deactivated_at` is the D-R10 state, and it is not a delete.** A deactivated workspace cannot be selected, its endpoints answer 404, keys pinned to it are refused, its schedules do not fire, and a super admin's listing shows it greyed with the date. **Nothing it owns is purged — ever**, which is the whole reason the state exists: deactivation is reversible and deletion is not. To a member a deactivated workspace is indistinguishable from one that never existed (`workspace.not_found`), so deactivation cannot be read as a signal; an API key pinned to it gets `auth.key_workspace_inactive` at the same 404 (owner ruling 2026-09-14, 131 §B: not-found on every surface, keys included; the code stays distinct only because the holder already knows the workspace exists and an operator greps for it). - **The `demo` workspace carries the well-known constant UUID `de000000-0000-0000-0000-000000000001`**, the same convention `default` uses — but it is created by `DemoWorkspaceSeeder` **at first boot**, not by V23. The migration deliberately does not seed it: the seeder imports the example content in the same act, and SQL cannot (it goes through the pipeline and template import services), so a migration-time insert would have made the seeder dead code and handed every deployment an empty `demo` with nothing saying why. It is the one workspace the product ships (D-R11) and the one a user with no membership joins as a **viewer** on first login. The boot-time seeder is idempotent and **never recreates a DEACTIVATED `demo`** (O-3): a seeder that re-creates what somebody deliberately turned off is a seeder that cannot be turned off. - `is_personal` is historical. The `auto-per-user` provisioning mode that set it was removed in RBAC round 1 (D-R11); existing personal workspaces stay as ordinary workspaces with their sole member as admin (D-R14). ### 4.12 `workspace_members` **Membership IS the role** (RBAC design D-R1; roles design D1, 2026-09-20): a person's role is per workspace, carried as ONE value. `users.is_admin` is the one global authority left and means **super admin** (§4.1). ```sql CREATE TABLE workspace_members ( workspace_id UUID NOT NULL REFERENCES workspaces(id), user_id UUID NOT NULL REFERENCES users(id), role TEXT NOT NULL DEFAULT 'viewer', -- 'viewer' | 'author' | 'promoter' | 'workspace_admin' (V29; enums §8C) joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (workspace_id, user_id), CONSTRAINT chk_workspace_member_role CHECK (role IN ('viewer', 'author', 'promoter', 'workspace_admin')) ); CREATE INDEX idx_workspace_members_admins ON workspace_members(workspace_id) WHERE role = 'workspace_admin'; ``` **Notes:** - **One role, back again (V29).** V1 had `role TEXT` (`owner` | `member`); V23 replaced it with three additive booleans (`author`, `promoter`, `admin`) so that "an author who also releases" could be one row; the 2026-09-20 rulings took release away from the promoter and made it an ops role that authors nothing, so the combination the booleans existed for no longer exists and V29 folded them back into one value. V29's precedence for the rows it found: `admin → workspace_admin, else promoter → promoter, else author → author, else viewer` — an author+promoter row became a PROMOTER. The down path is documented in the migration and proven by `WorkspaceRolesMigrationTest`. - **The value set is a CHECK, not a convention.** Four values; a fifth cannot be stored whatever a caller sends. The `admin → author` CHECK of V23 left with the flags it constrained. - **The last-admin rule is NOT a constraint.** "At least one workspace admin per workspace" is a cross-row invariant no CHECK can express, and a trigger's refusal would carry no catalogued error code — so it is enforced in `WorkspaceService` and answered as `workspace.last_admin` (409). The partial index above (same name as before V29, new predicate) is what makes the count cheap. - No `updated_at`: membership rows are inserted, updated and deleted, and `joined_at` carries the only timestamp the model needs. **The audit trail is where role changes live** — `workspace.member_role_changed` records the before and after (auth.md §10.1). ### 4.13 `published_endpoints` The registry of released pipelines served as `GET` endpoints at `/api///` (V11, round 074; re-rooted by 172's R-EP5). See [REST API §19](/docs/rest-api.md#19-published-endpoints). ```sql CREATE TABLE published_endpoints ( id UUID PRIMARY KEY, workspace_id UUID NOT NULL REFERENCES workspaces(id), path_pattern TEXT NOT NULL, -- '/lending/v1/{borough}/home' pipeline_id UUID NOT NULL REFERENCES pipelines(id), timeout_seconds INTEGER NOT NULL, -- clamped by config at write time description TEXT NOT NULL DEFAULT '', is_enabled BOOLEAN NOT NULL DEFAULT TRUE, retired_reason TEXT NULL, -- NULL = a normal row; 'pre-R-EP5 path' = retired (#274) created_by UUID NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (path_pattern) ); CREATE INDEX idx_published_endpoints_workspace ON published_endpoints(workspace_id); CREATE INDEX idx_published_endpoints_pipeline ON published_endpoints(pipeline_id); ``` **Notes:** - **`UNIQUE (path_pattern)` is deployment-wide, not per-workspace, on purpose.** A URL is global: `GET /api/lending/v1/home` has exactly one meaning on a deployment, so two workspaces cannot both own it. `workspace_id` says who may manage the row and whose datasources the pipeline runs against — it does not namespace the path. A per-workspace constraint would let two rows claim one URL and make request-time resolution ambiguous. - **`retired_reason` (V41, #274) — a stored row that fails a later grammar is retired, never fatal.** R-EP5 (2026-09-19) made three segments the floor, and rows written before that day under the then-legal two-segment grammar parse against nothing: the row mapper threw inside the demo seeder's conflict check and refused the whole boot. V41 disables every row whose path has fewer than three segments and records why (`retired_reason = 'pre-R-EP5 path'`; NULL is a normal row). Rows are never deleted — the listing shows them flagged with the reason, and unpublishing one is the operator's deliberate act. Independently of this column, the repository maps a row whose path no longer parses as a legacy value and keeps it out of the serve registry and the conflict check, so a database V41 has not reached yet still boots; the column is what makes the row read back as an ordinary disabled row everywhere else. The migration's down path (documented in the file's header) re-enables every row the reason names — V41 cannot distinguish "disabled by V41" from "disabled by an operator before V41" — and drops the column. The column is an audit marker, not the source of the listing's flag: `legacy` and `reason` come from parsing the stored path on every read (the mapper), and V41's predicate is the two-segment shape only — a row malformed another way (an illegal segment, an empty path) stays `is_enabled = TRUE` in the table, is mapped legacy on every read and is never served. **What sees a legacy row (#286).** Only the legacy read (`findLegacy`, workspace-scoped — there is no deployment-wide read) and the row-level read unpublish needs (`findRowByPath`); every valid-endpoint read skips it — the serve registry, the conflict check, the workspace listing, `EndpointKeyService.requireInsideWorkspace` (a node only a legacy path lies under is not inside the workspace's tree, so no key binds there) and the pipeline explorer's Usage tab (`findByPipeline`: a pipeline served only by a legacy row reads as unpublished, which it is). A promotion batch omits it and the sender logs the count. `path_pattern` is `TEXT` with no CHECK, so the surfaces that echo a legacy path (REST, `endpoints_list`, the console) cut it at the grammar's 200 characters (`EndpointPath.echoBounded`); the column keeps the stored value, because unpublish is by path. The once-per-JVM `event=endpoint.legacy_rows` WARN names the FIRST query's count as `at_least` — after V41 the registry warm-up reads enabled rows only, so it is a floor, not the deployment's total. - The constraint is not the whole uniqueness rule. `/a/{x}` and `/a/b` are different strings that match the same URL; that overlap is refused at publish time in the application (`endpoint.path_conflict`), under a transaction-scoped advisory lock so two concurrent publishes cannot both pass the check. The constraint is the second line, catching exact duplication. - The row pins a **pipeline, not a version**: the latest RELEASED version is resolved at request time, which is why the read-only rule is re-checked on every serve and not only at publish. - `timeout_seconds` is clamped to the `datapipelines.endpoints.timeout-min-seconds` / `datapipelines.endpoints.timeout-max-seconds` bounds when written, deliberately **not** by a CHECK constraint — the bounds are configuration an operator may retune, and a row written under the old bounds must keep serving rather than make the table unreadable. - Matching reads the enabled rows whole and matches them in memory (the set is small and cached per instance, invalidated over the same Redis channel datasource pools use), so no query plan depends on the shape of `path_pattern`. ### 4.14 `endpoint_key_bindings` Which API keys authorise which part of the endpoint tree (V11). See [Auth §7.7](/docs/auth.md#77-key-kinds-and-published-endpoint-bindings). ```sql CREATE TABLE endpoint_key_bindings ( path_prefix TEXT NOT NULL, -- a tree NODE: '/lending' binds '/lending/**' api_key_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE, workspace_id UUID NOT NULL REFERENCES workspaces(id), created_by UUID NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (path_prefix, api_key_id) ); CREATE INDEX idx_endpoint_key_bindings_key ON endpoint_key_bindings(api_key_id); ``` **Notes:** - `path_prefix` is a **node** of the endpoint tree, not a pattern: `/lending` authorises every endpoint beneath it. Resolution walks the request path's ancestors from the most specific, and the FIRST node carrying any binding decides — so a deeper binding **replaces** an inherited one for its subtree rather than adding to it. Bind both keys at the deeper node when both should keep working. - `api_key_id` is `TEXT` because [`api_keys.id`](#42-api_keys) is the `dpk_…` id itself, not a UUID. - `ON DELETE CASCADE`: a key that no longer exists cannot authorise anything, and an orphaned binding would show on the endpoints screen as a binding to nothing. Note that ordinary revocation is a soft flag and leaves the row — the cascade is for a genuine row delete. - The primary key `(path_prefix, api_key_id)` says one key binds a node once and several keys may bind the same node. - No `updated_at`: a binding is inserted and deleted, never edited. ### 4.15 `lake_tables` The dp-lake catalog: which tables a LAKE-dialect datasource serves (V15, round 089 §A; the 2026-09-07 lake-datasource design record §2). A LAKE datasource reads object storage in place and the engine cannot LIST a bucket — this registry is the catalog introspection and (in a later phase) per-table view creation read. Rows are written by `LakeTableRegistryService` alone (REST `POST /api/v1/datasources/{name}/tables`, the `lake_tables_*` MCP tools). ```sql CREATE TABLE lake_tables ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), datasource_id TEXT NOT NULL REFERENCES datasources(name), namespace TEXT[] NOT NULL, -- 087's List: {"nyc","mobility"} name TEXT NOT NULL, format TEXT NOT NULL, -- 'parquet' | 'iceberg' (CHECK) location TEXT NOT NULL, -- s3://bucket/prefix[/glob] or file:// path partition_column TEXT, -- NULL = unpartitioned registered_by UUID NOT NULL REFERENCES users(id), registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), last_error TEXT, -- V22: NULL = the view last built cleanly last_error_at TIMESTAMPTZ, -- V22: "broken since" (transition-only recording) CONSTRAINT chk_lake_table_format CHECK (format IN ('parquet', 'iceberg')), CONSTRAINT uq_lake_tables_datasource_namespace_name UNIQUE (datasource_id, namespace, name) ); ``` **Notes:** - **`datasource_id` is TEXT referencing `datasources(name)`** — the datasource table's primary key IS its name ([§4.10](#410-datasources): PK, GCM AAD anchor, cross-env contract); there is no surrogate id to point at. The column keeps the design record's name; what it holds is the datasource NAME. No `ON DELETE` clause: datasources are soft-deleted, the row stays, and its tables stay registered with it — a datasource name is never reused, so the reference can never silently repoint. - `namespace` is 087's `List` (the [NamespaceShape](/docs/datasources.md) world) as a Postgres `TEXT[]`: `{"nyc","mobility"}` is the table a template reads as `nyc.mobility.hvfhv_zone_day`. NOT NULL with one to nine segments. The segment grammar — the pipeline/template §4.1 production, minus `.` inside a segment (the dotted wire form `nyc.mobility` must round-trip) — is enforced by the application validator, not a CHECK: a per-element array CHECK is expressible but unreadable, and this table's only writer is that validating service. - `format`'s CHECK duplicates the application enum on purpose, the `chk_datasource_dialect` precedent (§4.10): a third value here generates bad SQL later — the view-creation phase maps `parquet` to `read_parquet(...)` and `iceberg` to `iceberg_scan(...)`, and the database is the last place to catch it. - `location` is the object-storage address: `s3://bucket/prefix/` (Parquet: a directory or glob; Iceberg: the table's **current metadata FILE** — `…/metadata/00042-.metadata.json`, not the table root, which DuckDB 1.5.5 cannot resolve for pyiceberg tables; the measured rule, [datasources.md §8C.7](/docs/datasources.md#8c7-the-iceberg-location-rule--register-the-metadata-file)) or a `file://` path for an on-prem volume. The scheme allowlist and the injection refusal — no quotes, no backslash, no control characters, no whitespace, because the value is later interpolated into `CREATE VIEW` statements — are the application validator's, and they are TOTAL: there is no escaping rule, because a value that would need one is refused at registration instead. - `partition_column` is nullable with no default: an unpartitioned table has none, and NULL is the truthful spelling of that (the `description` precedent, D4/2.9.4). - `registered_by` / `registered_at` follow the house `created_by`/`created_at` shape — a real FK to `users(id)` (`ON DELETE RESTRICT`, the §2 default) and a DB-defaulted TIMESTAMPTZ. There is no `updated_at`: a row is inserted and deleted, never edited — re-registration is delete + insert, so the registration audit columns always name the actor of the row that exists. - `last_error` / `last_error_at` (V22, 109 §A) record the connect-time view creation's failure ([datasources.md §8C.2](/docs/datasources.md#8c2-a-view-per-registered-table-built-at-connect--and-isolated-per-table)): a failing view is skipped and its bounded engine error stored here instead of failing the pool build; a later success clears both in the same UPDATE. NULL is the healthy spelling, so previously-healthy rows needed no backfill. Recording is transition-only (an unchanged outcome writes nothing), so `last_error_at` reads as "broken since", never "last observed". Written by `LakeTableRepository.recordViewOutcome` from the pool's view application. - `uq_lake_tables_datasource_namespace_name` is the one rule beyond the CHECK: a table is its (datasource, namespace, name) triple, registered once. It is NAMED so the service maps its violation to the catalogued `datasource.lake_table_duplicate` (the `uq_pipelines_workspace_name` precedent, §4.4), and its index doubles as the access path for the registry's hot read — "list the tables of one datasource" — which is why §5 creates no separate index on `datasource_id`. --- ### 4.16 `datasource_workspaces` **Visibility is a grant** (RBAC design §4, D-R7). A datasource is registered once — credentials are instance secrets — and granted to N workspaces. There is no "global" datasource any more: nothing is visible by default. ```sql CREATE TABLE datasource_workspaces ( datasource_name TEXT NOT NULL REFERENCES datasources(name) ON DELETE CASCADE, workspace_id UUID NOT NULL REFERENCES workspaces(id), granted_by UUID NOT NULL REFERENCES users(id), granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (datasource_name, workspace_id) ); CREATE INDEX idx_datasource_workspaces_workspace ON datasource_workspaces(workspace_id); ``` **Notes:** - **Keyed by `datasource_name`, not by an id.** `datasources` is keyed by `name TEXT` (§4.10) and always has been; the RBAC design record's `datasource_id UUID` names a column that does not exist. - **V23's backfill preserves yesterday's visibility exactly.** A workspace-bound datasource is granted to its own workspace and `owner_workspace_id` is set to it; a former `global` datasource (`workspace_id IS NULL`) is granted to EVERY existing workspace and owns none. `granted_by` is the datasource's own `created_by` — the honest actor, and it keeps the migration from having to mint a users row. - **`ON DELETE CASCADE` on the datasource, never on the workspace.** Removing a datasource removes its grants; a workspace is DEACTIVATED, never deleted (D-R10), so its grants must survive to be there when it is reactivated. - **Every grant is audited** (`datasource.granted` / `datasource.revoked`, auth.md §10.1). The row records who and when; the audit trail records the decision. - Revoking a grant does not touch the datasource. A workspace that loses one loses only its ability to SEE it — pipelines there that referenced it then fail with the ordinary not-found, which is the honest answer: it no longer exists for them. ### 4.17 `workspace_invitations` **An invitation is a membership waiting for its user** (113, auth.md §4.6). A workspace admin can add `bob@company.com` before Bob has ever signed in: the row is keyed by EMAIL, and the login path materialises it into a `workspace_members` row the moment the `users` row comes into existence (§4.2 first provisioning, or the admin's local-account creation — the two creation paths). ```sql CREATE TABLE workspace_invitations ( workspace_id UUID NOT NULL REFERENCES workspaces(id), email TEXT NOT NULL, -- normalized lowercase, the §4.2 rule role TEXT NOT NULL DEFAULT 'viewer', -- the ONE role the membership will carry (V29, D20) invited_by UUID NOT NULL REFERENCES users(id), invited_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (workspace_id, email), CONSTRAINT chk_workspace_invitation_role CHECK (role IN ('viewer', 'author', 'promoter', 'workspace_admin')), CONSTRAINT chk_workspace_invitation_email_lower CHECK (email = lower(email)) ); CREATE INDEX idx_workspace_invitations_email ON workspace_invitations(email); ``` **Notes:** - **Separate from `workspace_members` on purpose.** The members table's `user_id` is `NOT NULL REFERENCES users(id)` and stays that way — nothing pretends a person exists before they do. An invitation references an email, never a user id. - **The role is the membership's role, carried forward** (D20). The value set is the same CHECK the members table states (§4.12), mirrored by V29 exactly as V24 mirrored the flags. - **One invitation per (workspace, email); a re-invite REPLACES the role.** The upsert is the latest admin decision winning, and it is audited every time (`workspace.member_invited`), so the row holds no history by design — the audit trail does (the §4.12 rule again). - **No expiry column in v1** (owner ruling): an invitation is revocable like any other membership, and it is a membership waiting for its user, not a message that can go stale. Invitations for a user who ALREADY exists are never created — the invite becomes the membership at once (auth.md §4.6 rule 1). - **Materialisation preserves `invited_at` as `joined_at`.** A user invited into two workspaces materialises both in one statement, and the membership ordering (`joined_at`, §4.12) then stamps `active_workspace` to the EARLIER invitation — the admin's first decision, not the alphabetical accident of two same-transaction timestamps. - **Pending invitations into a DEACTIVATED workspace do not materialise** (113 §B.5): the materialise predicate requires the workspace to be active, so the rows wait and reactivation makes them live again. The index on `email` is the materialise lookup's access path — the PK prefix serves only the per-workspace listing. ### 4.18 `learned_facts` **What an agent learned about a datasource that introspection could not tell it** (V25, round 118; the [learned semantic layer design record](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-11-learned-semantic-layer-design.md) §3–§6). A unit, a time zone, a sample rate, a grain, what a coded value means, a join that holds — structured rows keyed by the object they describe, served INLINE by the three introspection surfaces (`datasources_get` / `_get_tables` / `_get_columns` and their REST twins) so the next session reads them where it is already looking (D-S7). Rows are written by `SemanticsService` alone (`semantics_record` / `semantics_retire`; the §6 drift check's trust mark on the read path). ```sql CREATE TABLE learned_facts ( id UUID PRIMARY KEY, scope TEXT NOT NULL, workspace_id UUID REFERENCES workspaces(id), -- NULL iff scope = DATASOURCE datasource_name TEXT NOT NULL REFERENCES datasources(name) ON DELETE CASCADE, kind TEXT NOT NULL, -- closed list, enums.md §19 fact TEXT NOT NULL, refs_json JSONB NOT NULL, -- [{schema?, table, column?}], ≥ 1 for a DATASOURCE fact; may be [] for a WORKSPACE rule (V26) evidence_sql TEXT, -- the probe that showed it evidence_summary TEXT, -- what the probe returned, ≤ 300 chars trust TEXT NOT NULL, schema_fingerprint TEXT NOT NULL, -- per referenced table, at record time recorded_by UUID NOT NULL REFERENCES users(id), recorded_via TEXT NOT NULL, -- WriteSurface: mcp | session | api_key recorded_in UUID NOT NULL REFERENCES workspaces(id), -- the ACTIVE workspace at record time (provenance) source_pipeline_id UUID REFERENCES pipelines(id) ON DELETE SET NULL, source_version INT, recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), verified_by UUID REFERENCES users(id), verified_at TIMESTAMPTZ, supersedes UUID REFERENCES learned_facts(id), retired_at TIMESTAMPTZ, retired_reason TEXT, CONSTRAINT chk_learned_facts_scope CHECK (scope IN ('DATASOURCE', 'WORKSPACE')), CONSTRAINT chk_learned_facts_kind CHECK (kind IN ( 'unit', 'time_zone', 'sampling', 'grain', 'window', 'enum_meaning', 'join', 'caveat', 'format', 'definition', 'exclusion', 'preference' )), CONSTRAINT chk_learned_facts_kind_scope CHECK ( (scope = 'WORKSPACE') = (kind IN ('definition', 'exclusion', 'preference')) ), CONSTRAINT chk_learned_facts_fact_length CHECK (length(fact) BETWEEN 8 AND 1000), CONSTRAINT chk_learned_facts_summary_length CHECK (evidence_summary IS NULL OR length(evidence_summary) <= 300), CONSTRAINT chk_learned_facts_refs CHECK (jsonb_typeof(refs_json) = 'array' AND (scope = 'WORKSPACE' OR jsonb_array_length(refs_json) >= 1)), -- V26: a WORKSPACE rule may name no table CONSTRAINT chk_learned_facts_trust CHECK (trust IN ('asserted', 'observed', 'verified', 'needs_review', 'stale', 'retired')), CONSTRAINT chk_learned_facts_via CHECK (recorded_via IN ('session', 'api_key', 'mcp')), CONSTRAINT chk_learned_facts_scope_workspace CHECK ((scope = 'WORKSPACE') = (workspace_id IS NOT NULL)), CONSTRAINT chk_learned_facts_retired CHECK ((trust = 'retired') = (retired_at IS NOT NULL)) ); CREATE INDEX idx_learned_facts_datasource ON learned_facts (datasource_name); CREATE INDEX idx_learned_facts_workspace ON learned_facts (workspace_id) WHERE workspace_id IS NOT NULL; ``` **Notes:** - **Two scopes, one table (D-S1).** A `DATASOURCE` fact describes the data and is visible wherever the datasource is granted (§4.16) — `workspace_id` is NULL; a `WORKSPACE` fact is one organisation's meaning and is visible to that workspace only. The visibility predicate is ONE line in `LearnedFactRepository` (`scope = 'DATASOURCE' OR workspace_id = :reader`) and the 112 sweep walks the three tools and the REST block that use it. `recorded_in` is provenance (which workspace was active when the fact was recorded), never visibility. - **Nothing JDBC metadata provides is stored (D-S2).** The `kind` CHECK is the closed list of [Enums §19](/docs/enums.md#19-learnedfactkind--what-a-learned-fact-is-about) — no `type`, `nullable`, `key`, `comment`, `partition` or `row_count` kind exists. `chk_learned_facts_kind_scope` states the per-kind scope once in the database: the three business kinds are WORKSPACE facts, the nine data kinds DATASOURCE facts. - **Refs are structural (D-S3)** and stored NORMALISED — sorted by `schema.table.column` — so the O-4 duplicate refusal (`semantics.duplicate`) is a `jsonb` equality over `(scope, workspace_id, datasource_name, kind, refs_json, fact)` among live rows. A ref is validated against live introspection at record time; a ref that does not resolve is refused, so the store never starts stale. - **`schema_fingerprint` is per referenced table, addressable.** Sorted `tableKey=sha256` entries joined by `;` — each entry the SHA-256 of that table's sorted `(column, canonical type)` list. The read-time drift check (design §6) recomputes only the table whose columns it just read, so a two-table join fact must keep each table's digest addressable; hashing the concatenation once more would make such a fact un-checkable from either listing alone. - **Trust is demoted, never promoted, by machine (D-S6).** `markTrust` refuses a retired row and the drift check only ever asks for `needs_review` or `stale`; a read path writing a mark is deliberate (idempotent, and the alternative is serving a mark the server computed and then forgot). Promotion to `verified` is a human act (UI round 2 / REST). - **Never hard-deleted by users (D-S11).** `retired` is a state and `chk_learned_facts_retired` ties it to its stamp; `supersedes` links the history. The one cascade is the datasource's own delete (the object is gone). A purged source pipeline only detaches — `ON DELETE SET NULL` — because the fact outlives the pipeline that learned it. - **No expression index over `refs_json`.** The design sketched `(datasource_name, (refs_json->0->>'table'))`; it would index only the FIRST ref of a multi-ref fact and serve none of the reads that ship (every read is per datasource, then narrowed to a table in the reader over a set that is hundreds of rows at most). The honest index is the datasource one. ### 4.19 `mail_sends` **The claim row behind every notice the product sends** (V27, round 137; [Auth §5A.8](/docs/auth.md#5a8-mail-the-welcome-mail-and-the-new-user-notice)). One row per MESSAGE IDENTITY `(user, kind, act)`, inserted BEFORE the transport is touched with `ON CONFLICT DO NOTHING`: whoever inserts the row sends; a retry, a double-submit or a second instance finds it and does not. The welcome mail carries a one-time password and must never go twice — this table is what makes that a database fact rather than a hope. ```sql CREATE TABLE mail_sends ( id UUID PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, kind TEXT NOT NULL, -- welcome | password_reset | new_user act_id UUID NOT NULL, -- the user's own id for the once-per-user kinds; a fresh id per reset recipient TEXT NOT NULL, -- the To list as sent (a comma list for the ops sink) claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), sent_at TIMESTAMPTZ, -- the transport accepted it message_id TEXT, -- the Message-ID it went out under error TEXT, -- the exception's class + message; never a body CONSTRAINT uq_mail_sends_message UNIQUE (user_id, kind, act_id), CONSTRAINT chk_mail_sends_kind CHECK (kind IN ('welcome', 'password_reset', 'new_user')) ); ``` **Notes:** - **A row proves an ATTEMPT, not a delivery.** `claimed_at` is the claim; `sent_at`/`message_id` land when the transport accepted the message, `error` when it did not. The status is derived: neither stamp = `PENDING` (the send has not returned — or the process died between commit and send, which the admin screen then shows), `sent_at` = `SENT`, `error` = `FAILED`. Rows are never cleaned up: they are what the admin screen reads back and what an operator greps. - **`act_id` is the message's act.** The user's own id for `welcome` and `new_user` (once per user, ever), a fresh id per `password_reset` — two resets mint two credentials and two mails, and the screen reads the LATEST reset by `claimed_at`. - **The claim rides the caller's transaction.** `MailSendRepository.tryClaim` runs on the request thread inside whatever metadata transaction is open, so a rolled-back creation claims nothing; the send itself is an after-commit task on a bounded pool. - **Nothing here is a credential.** The body is never stored; the one-time password exists in exactly one place, the message handed to the transport (`MailNotifierIntegrationTest` greps `row_to_json(mail_sends)` for it). ### 4.20 `pipeline_check_runs` **The server-side record of every release check run** (V28, round 140; [Pipeline Contract §3.3/§12.12](/docs/pipeline-contract.md)). A pipeline body optionally declares `checks[]` — a read-only statement against a datasource plus the value the author expects it to produce — and the SERVER runs them: on demand from a surface (`mcp` / `rest` / `ui`) and inside the release gate (`release`). One row per check per run, written by the run itself before the outcome is returned. There is deliberately no field on the `checks[]` declaration that could carry an observed value in from a caller: the only `observed` that exists anywhere is the one this table stores, and only a run produces it. ```sql CREATE TABLE pipeline_check_runs ( id UUID PRIMARY KEY, pipeline_id UUID NOT NULL REFERENCES pipelines(id), version INT NOT NULL, -- the version NUMBER whose body declared the check check_id TEXT NOT NULL, -- the check's §15.1 identifier within the body ran_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), ran_by UUID, -- the actor; NULL-able, no FK — the run outlives the user via TEXT NOT NULL, -- mcp | rest | ui | release parameters_json JSONB NOT NULL DEFAULT '{}', -- the BOUND parameters (defaults applied), wire-encoded observed_json JSONB, -- {"value": "74.62"} or {"rows": 6}; NULL on error verdict TEXT NOT NULL, -- pass | fail | error message TEXT, -- the reason for a fail / error, bounded correlation_id TEXT, duration_ms BIGINT, CONSTRAINT chk_pipeline_check_runs_via CHECK (via IN ('mcp', 'rest', 'ui', 'release')), CONSTRAINT chk_pipeline_check_runs_verdict CHECK (verdict IN ('pass', 'fail', 'error')) ); CREATE INDEX idx_pipeline_check_runs_latest ON pipeline_check_runs (pipeline_id, version, check_id, ran_at DESC); ``` **Notes:** - **Append-only.** Rows are never updated and never cleaned up — a check run is an observation, and the history of observations is what a release refusal cites, what the UI's latest-run list reads, and what an operator greps. The latest run per check is a read (`DISTINCT ON (check_id)` over `idx_pipeline_check_runs_latest`), never a stored row that could lie about being current. - **`verdict` is three-valued on purpose.** `pass` / `fail` are clean comparisons — the run produced a value and it did or did not satisfy the expectation. `error` is "no verdict could be formed": the datasource was unresolvable or unreachable, the statement was refused or timed out, the result had a shape the expectation cannot compare (two columns for a `value` check), or the parameters did not bind. Recording `error` instead of folding it into `fail` is what keeps "the check said no" distinct from "the check could not run" — a release gate refuses on both, but for different reasons, and the operator's next action differs. - **The FK is to `pipelines(id)`, not the composite `(pipeline_id, version)`.** A check run is a fact about a version NUMBER, and a DRAFT version row is deleted by a purge — the composite FK `pipeline_executions` carries would make purging a checked draft refuse for history the table should keep. This is the same separation §4.5's purge-deletes-executions choice already documents from the other side. - **`observed_json` is one small object, two shapes.** `{"value": ""}` for `value` and `range` checks; `{"rows": }` for a `rows` check. NULL whenever the verdict is `error` reached before a value could be read (unresolvable datasource, refusal, timeout, bind failure) — there is no observed value to record, and storing a placeholder would invent one. - **`parameters_json` stores the BOUND context, not the request.** Defaults are applied, undeclared supplied keys are absent, and every value is wire-encoded per its declared type — the row records what the statement actually ran with, which is the only parameters value an audit needs. A run whose bind failed stores the default `{}`: there is no bound context to record. ### 4.21 `template_implements` **The learned facts a script version cites as implementing** (V36, lane 7e, #7; the [transform-nodes design record](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-09-transform-nodes-design.md) §2.3, §8.2, §8.3). A `jsonata`/`javascript` template version may say which WORKSPACE rules it implements — "this script IS the workspace's definition of a rainy day" — so an agent that finds the definition finds the script (`implemented_by`, the `templates_list implements=` filter), and a version whose cited rule is retired is served marked `needs_review`. ```sql CREATE TABLE template_implements ( template_id UUID NOT NULL, -- the templates SURROGATE, as template_versions keys it (V4) version INTEGER NOT NULL, fact_id UUID NOT NULL, PRIMARY KEY (template_id, version, fact_id), CONSTRAINT fk_template_implements_version FOREIGN KEY (template_id, version) REFERENCES template_versions (template_id, version) ON DELETE CASCADE, CONSTRAINT fk_template_implements_fact FOREIGN KEY (fact_id) REFERENCES learned_facts (id) -- NO cascade: facts are retired, never deleted (D-S11) ); CREATE INDEX idx_template_implements_fact ON template_implements (fact_id); CREATE INDEX idx_learned_facts_supersedes ON learned_facts (supersedes) WHERE supersedes IS NOT NULL; ``` **Notes:** - **Not content, not in the hash.** A citation is a claim about meaning, never a change of behaviour (R9): no `body_hash` expression reads this table, a write of `implements` alone never opens a draft or bumps a version, and a RELEASED version accepts it after release — the one post-release write [Templates §5.1](/docs/templates.md#51-rules) allows. The templates module's hash-equality test pins that the hash is byte-identical before and after a citation write. - **Which facts may be cited is the application's rule** (`template.implements_unresolved`, 400, at write): a fact visible from the writing workspace (the §4.18 predicate) that is a `WORKSPACE` fact of kind `definition`, `exclusion` or `preference`. A DATASOURCE fact is refused by rule — a script never sees a datasource, so it cannot implement a fact about one. A CHECK cannot read another table, so there is no database twin. - **`needs_review` is computed on read, never stored.** Every template read (`TemplateRepository`'s projection) joins this table to `learned_facts` and marks the version when a cited fact is `retired`; the detail names each retired fact, its `retired_reason`, and its successor — the visible row whose `supersedes` is the retired id (a superseded fact is a retired one with reason `superseded`; there is no `superseded_by` column). `idx_learned_facts_supersedes` is what keeps that successor lookup an index read. Nothing re-maps a citation: clearing the mark is a deliberate `implements` write (cite the successor, or drop the citation). - **Deletes.** `(template_id, version)` cascades — a purged draft (versioning §5.4's hard delete) and an entity purge take their citations; `fact_id` does not — the product never deletes a fact (datasource delete is soft, §4.10), so a manual hard delete of a cited fact is refused rather than silently orphaning a script's claim. - **Export, import, promotion** carry the ids on the version's payload; an import stores only the ids that resolve in the IMPORTING workspace and drops the rest without refusing (owner ruling 2026-09-25): `learned_facts` is environment-local (§5A), so a cross-deployment promotion lands with no citations, and a same-workspace round trip is lossless. ### 4.22 `schedules` **A schedule** (V38, #9; the [scheduler design revision](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-22-scheduler-design-revision.md) §7, [Scheduler](/docs/scheduler.md)). Workspace-scoped, named by the pipeline path grammar, pipeline-AGNOSTIC: the job is an executor id plus opaque JSON the executor validated at save (record §5) — nothing pipeline-shaped is a column. ```sql CREATE TABLE schedules ( id UUID PRIMARY KEY, workspace_id UUID NOT NULL REFERENCES workspaces(id), name TEXT NOT NULL, -- the pipeline path grammar (checked in code) revision INT NOT NULL DEFAULT 1, -- the ETag / If-Match value; not a releasable version executor_id TEXT NOT NULL, -- 'pipeline' in slice 1 — an allowlisted registry id payload_schema_version INT NOT NULL, payload_json JSONB NOT NULL, -- opaque; the executor validates it parameters_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- literal inputs (R7), opaque here target_ref TEXT NOT NULL, -- the executor's generic reference (B15): 'pipeline:' cron TEXT NOT NULL, -- five-field Unix timezone TEXT NOT NULL, -- an IANA region id missed_run_policy TEXT NOT NULL DEFAULT 'skip', notification_recipients TEXT[] NOT NULL DEFAULT '{}', -- V49, #442a; empty = off notification_events TEXT[] NOT NULL DEFAULT '{failure,unknown,blocked}', enabled BOOLEAN NOT NULL DEFAULT TRUE, blocked_reason TEXT, blocked_at TIMESTAMPTZ, blocked_run_id UUID, -- the run whose outcome blocked it next_due_at TIMESTAMPTZ, created_by UUID NOT NULL REFERENCES users(id), updated_by UUID NOT NULL REFERENCES users(id), idempotency_key TEXT, -- a create's Idempotency-Key (L1) … idempotency_hash TEXT, -- … and the hash of the request it named created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), deleted_at TIMESTAMPTZ, deleted_by UUID REFERENCES users(id), CONSTRAINT chk_schedules_missed_run_policy CHECK (missed_run_policy IN ('skip', 'latest')), CONSTRAINT chk_schedules_blocked CHECK ((blocked_reason IS NULL) = (blocked_at IS NULL)), CONSTRAINT chk_schedules_idempotency CHECK ((idempotency_key IS NULL) = (idempotency_hash IS NULL)), CONSTRAINT chk_schedules_notification_events CHECK (notification_events <@ ARRAY['start','success','failure','unknown','blocked']::TEXT[]), CONSTRAINT chk_schedules_revision CHECK (revision >= 1) ); CREATE UNIQUE INDEX uq_schedules_workspace_name_live ON schedules (workspace_id, name) WHERE deleted_at IS NULL; CREATE UNIQUE INDEX uq_schedules_create_idempotency ON schedules (workspace_id, created_by, idempotency_key) WHERE idempotency_key IS NOT NULL; CREATE INDEX idx_schedules_due ON schedules (next_due_at) WHERE enabled AND blocked_at IS NULL AND deleted_at IS NULL; CREATE INDEX idx_schedules_target ON schedules (workspace_id, target_ref) WHERE deleted_at IS NULL; ``` **Notes:** - **Soft delete** (`deleted_at`, D-9.6/B17), not §2's `is_deleted` flag: the name is unique among LIVE schedules only (the partial index), so a deleted schedule keeps its runs and their trail and its name is free again. A deleted schedule is absent from every read. - **`enabled` and `blocked_*` are independent operational controls** (record §1.1): pause flips `enabled`; an `unknown` run or a blocking refusal sets the block; resume never clears a block (unblock does). - **`next_due_at` is written only by the ONE occurrence function** (record §3.2) — at create, at the dispatcher's advance, and when resume, unblock or a cron/timezone edit recompute it from now. - **The dispatcher's scan** is `idx_schedules_due` joined to the workspace row (a deleted or DEACTIVATED workspace is not dispatched), locked `FOR UPDATE SKIP LOCKED`. ### 4.23 `schedule_runs` **One run** — an occurrence (`cron`, `catch_up`) or a manual request (`manual`) — with its frozen context and its state (V38; record §7, §7.1). ```sql CREATE TABLE schedule_runs ( id UUID PRIMARY KEY, schedule_id UUID REFERENCES schedules(id), -- NULL only for slice 6's ad-hoc runs workspace_id UUID NOT NULL REFERENCES workspaces(id), origin TEXT NOT NULL, -- 'cron' | 'catch_up' | 'manual' scheduled_at TIMESTAMPTZ, -- the UTC occurrence; NULL for a manual run reference_at TIMESTAMPTZ NOT NULL, -- the frozen time context (record §5.1) reference_timezone TEXT NOT NULL, target_ref TEXT, -- safe target summary frozen at run creation (V51); NULL for older/ad-hoc runs admit_by TIMESTAMPTZ NOT NULL, -- the lateness window's end schedule_revision INT, executor_id TEXT NOT NULL, payload_schema_version INT NOT NULL, payload_json JSONB NOT NULL, -- frozen copies of the schedule's parameters_json JSONB NOT NULL DEFAULT '{}'::jsonb, prepared_json JSONB, -- the executor-owned snapshot (R5), set IN the claim actor_user_id UUID NOT NULL REFERENCES users(id), -- the system identity (R2) requested_by UUID REFERENCES users(id), -- a manual run's person execution_id UUID, -- opaque execution reference, minted at the claim; no FK state TEXT NOT NULL, -- R6's table (record §7.1) reason TEXT, worker TEXT, attempts INT NOT NULL DEFAULT 0, -- capacity retries (R4) idempotency_key TEXT, -- a Run now's Idempotency-Key (L1) … idempotency_hash TEXT, trail_seq INT NOT NULL DEFAULT 0, -- the last seq of this run's trail created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), claimed_at TIMESTAMPTZ, started_at TIMESTAMPTZ, finished_at TIMESTAMPTZ, updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), execution_started_at TIMESTAMPTZ, -- the EXECUTION's own timing (#258), set by the reconciler's terminal execution_completed_at TIMESTAMPTZ, CONSTRAINT chk_schedule_runs_origin CHECK (origin IN ('cron', 'catch_up', 'manual')), CONSTRAINT chk_schedule_runs_state CHECK (state IN ('queued', 'starting', 'running', 'succeeded', 'failed', 'cancelled', 'aborted', 'unknown', 'not_started', 'skipped')), CONSTRAINT chk_schedule_runs_occurrence CHECK ((origin = 'manual') = (scheduled_at IS NULL)), CONSTRAINT chk_schedule_runs_claimed CHECK (state NOT IN ('starting', 'running') OR execution_id IS NOT NULL), CONSTRAINT chk_schedule_runs_idempotency CHECK ((idempotency_key IS NULL) = (idempotency_hash IS NULL)), CONSTRAINT uq_schedule_runs_occurrence UNIQUE (schedule_id, scheduled_at) ); CREATE UNIQUE INDEX uq_schedule_runs_manual_idempotency ON schedule_runs (schedule_id, requested_by, idempotency_key) WHERE idempotency_key IS NOT NULL; CREATE UNIQUE INDEX uq_schedule_runs_one_active ON schedule_runs (schedule_id) WHERE state IN ('queued', 'starting', 'running'); CREATE INDEX idx_schedule_runs_schedule ON schedule_runs (schedule_id, created_at DESC); CREATE INDEX idx_schedule_runs_open ON schedule_runs (updated_at) WHERE state IN ('starting', 'running', 'unknown'); CREATE INDEX idx_schedule_runs_execution ON schedule_runs (execution_id) WHERE execution_id IS NOT NULL; ``` **Notes:** - **One occurrence, one row** — `uq_schedule_runs_occurrence` is the dispatcher's `ON CONFLICT ON CONSTRAINT … DO NOTHING` arbiter; manual rows (`scheduled_at` NULL) never conflict with it. - **At most one active run per schedule** — `uq_schedule_runs_one_active` is the database's second line under the overlap guard every run-inserting path decides under the schedule row's lock (record §3). - **The start claim** is the one conditional UPDATE `queued → starting` that also records `execution_id` and `prepared_json` — `chk_schedule_runs_claimed` makes "a claimed run names its execution" a database fact. - **`execution_id` has no FK** on purpose: it is minted BEFORE the execution exists (the fail-closed rule, record §2.1), and it is an opaque reference to whichever executor ran it. - **`prepared_json`** is the executor's snapshot — for the pipeline executor `{pipeline_id, pipeline, version, version_status, body_sha256}` (R5: a DRAFT is mutable under its number, so the body's hash names what ran). The scheduler stores it and never reads it. ### 4.24 `schedule_run_events` **A run's trail** (V38; R10, record §5.4) — one row per transition, append-only. ```sql CREATE TABLE schedule_run_events ( run_id UUID NOT NULL REFERENCES schedule_runs(id), seq INT NOT NULL, -- 1, 2, 3 … per run, from schedule_runs.trail_seq kind TEXT NOT NULL, reason TEXT, at TIMESTAMPTZ NOT NULL, worker TEXT, details_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- the execution reference, a retry number, a summary's counts PRIMARY KEY (run_id, seq), CONSTRAINT chk_schedule_run_events_kind CHECK (kind IN ('recorded', 'capacity_retry', 'claimed', 'execution_started', 'not_started', 'skipped', 'finished', 'unknown', 'updated_after_unknown', 'unblocked', 'notification_enqueued', 'notification_sent', 'notification_failed', 'notification_uncertain')), -- V51 widened it CONSTRAINT chk_schedule_run_events_seq CHECK (seq >= 1) ); ``` **Notes:** - **Append-only by construction**, not by a trigger (§2: this schema has none): the one INSERT in `ScheduleRunRepository.appendTrail` draws `seq` from `schedule_runs.trail_seq` in the same statement, and no code path updates or deletes a row — `ScheduleTrailAppendOnlyTest` scans the sources. Like §2's other immutable tables it carries no `updated_at`. - **No copy of the pipeline's events.** The execution's own events stay in [`execution_events`](#47-execution_events); `schedule_runs.execution_id` is the join a merged Messages read uses (record §5.4). - **Retention** follows the run's history: slice 1 deletes neither (a retention policy for runs and their trail is a later slice's; the notification outbox, [§4.39](#439-schedule_notification_events)–[§4.40](#440-schedule_notification_deliveries), is kept with them). - **The notification kinds** (V51, #442b): `notification_enqueued` is written in the transition's own transaction when the outbox queued a notice (details: the notice id, its event and outcome, the queued and refused counts — never an address); `notification_sent` / `notification_failed` / `notification_uncertain` when the sender settles a delivery, or recovers a claim a stopped instance left (details: the delivery id, the notice id, the attempt count and the code — no part of the address, not even its domain: the trail is read by every `schedule.read` holder). A retry that returns a delivery to `pending` writes no row. ### 4.25 `scheduled_tasks` **db-scheduler's own queue** (V38) — the library's EXACT PostgreSQL DDL at 16.12.0 (`db-scheduler/src/test/resources/postgresql_tables.sql`, tag `v16.12.0`), copied verbatim with its three indexes. The library owns the shape and every write; this schema only creates it. Its `task_data` is JSON (never Java serialization, record §2.2), and the scheduler writes at most a run id there. Rows: one per recurring task (`schedule-dispatcher`, `schedule-reconciler`) and one per enqueued run (`schedule-run`, instance id = the run id), removed when that run's task completes. ### 4.26 `parameter_sets` **A parameter set** (V39, #194; the [parameter-engine design record](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-21-parameter-engine-design.md) §8.1) — the INDEX over a set's versions, the [`templates`](#48-templates) shape addressed like a pipeline: by a UUID. ```sql CREATE TABLE parameter_sets ( id UUID PRIMARY KEY, -- the stable id every surface addresses (record P24) workspace_id UUID NOT NULL REFERENCES workspaces(id), name TEXT NOT NULL, -- the pipelines/templates folder grammar display_name TEXT NOT NULL, -- indexes the CURRENT version's body description TEXT NOT NULL DEFAULT '', current_version INTEGER NULL, -- the sticky pointer (D60); NULL until the first release created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), CONSTRAINT uq_parameter_sets_workspace_name UNIQUE (workspace_id, name), CONSTRAINT chk_parameter_sets_current_version CHECK (current_version IS NULL OR current_version >= 1) ); ``` **Notes:** - **`id` has no database default.** The application generates it at create, and an IMPORT keeps the exported one (record P24, the `PipelineImportService` rule), so a set's id is stable across environments — the export key of §5A. Names travel in bodies and `?prefix=` only; a route carries the id. - `name` is unique per workspace FOREVER (versioning §3.2, D59): a discarded set keeps its name. It follows the §4.1 folder grammar, enforced in code (`PipelineNameGrammar`) — no DDL gate, because nothing re-validates a set name at run time. - **`display_name` / `description` index the CURRENT body** — they are content of `body_json` (the pipeline rule of versioning §3.7, not the templates asymmetry): a release sets them from the released body, and any pointer move (discard's fallback, restore, switch, a first import) re-indexes them from the version it now names. A never-released set shows its create-time values. - No entity status column: ACTIVE while a version is DRAFT or RELEASED, DISCARDED when every version is (versioning §3.2). - Every statement against this table carries `workspace_id = :workspaceId` (`ParameterSetRepository`): a set of another workspace is absent, never hidden. ### 4.27 `parameter_set_versions` **One version of a set** (V39) — the [`template_versions`](#49-template_versions) shape minus the template-only columns. ```sql CREATE TABLE parameter_set_versions ( parameter_set_id UUID NOT NULL REFERENCES parameter_sets(id) ON DELETE CASCADE, version INTEGER NOT NULL, body_json JSONB NOT NULL, -- the record's §3 document, `name` omitted status TEXT NOT NULL DEFAULT 'DRAFT' CONSTRAINT chk_parameter_set_versions_status CHECK (status IN ('DRAFT', 'RELEASED', 'DISCARDED')), body_hash TEXT NOT NULL, -- SHA-256 (hex) of body_json's JSONB text, computed by the database released_at TIMESTAMPTZ NULL, released_by UUID REFERENCES users(id), discarded_at TIMESTAMPTZ NULL, discarded_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id), updated_at TIMESTAMPTZ NULL, -- DRAFT writes only created_via TEXT NOT NULL DEFAULT 'session', updated_via TEXT NOT NULL DEFAULT 'session', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), PRIMARY KEY (parameter_set_id, version), CONSTRAINT chk_parameter_set_versions_version CHECK (version >= 1), CONSTRAINT chk_parameter_set_versions_body CHECK (jsonb_typeof(body_json) = 'object' AND NOT (body_json ? 'name')), CONSTRAINT chk_parameter_set_versions_release_stamps CHECK ( (status = 'DRAFT' AND released_at IS NULL AND released_by IS NULL) OR (status <> 'DRAFT' AND released_at IS NOT NULL) ), CONSTRAINT chk_parameter_set_versions_discard_stamps CHECK ( (status = 'DISCARDED' AND discarded_at IS NOT NULL) OR (status <> 'DISCARDED' AND discarded_at IS NULL AND discarded_by IS NULL) ), CONSTRAINT chk_parameter_set_versions_via CHECK ( created_via IN ('session', 'api_key', 'mcp') AND updated_via IN ('session', 'api_key', 'mcp') ) ); CREATE UNIQUE INDEX uq_parameter_set_versions_one_draft ON parameter_set_versions (parameter_set_id) WHERE status = 'DRAFT'; ``` **Notes:** - **The lifecycle is the templates' (versioning §3.5)**: the same statuses, the same one-draft partial index, the same four write paths, the same database-computed hash. The body is the validator's canonical form (expressions reprinted) and the hash is `encode(sha256(convert_to(body_json::text, 'UTF8')), 'hex')` over the JSONB projection — versioning §4.1's PIPELINE rule, one expression in every write, so key order and whitespace never move a hash. - **Two constraints can refuse the loser of a first-writer race**, and it is usually the PRIMARY KEY: both writers allocate `max(version) + 1` from the same committed rows, and Postgres checks unique indexes in creation order. `ParameterSetRepository` maps either to `parameter.version.conflict` carrying the winner's state (the pipeline repository's rule; proven by a forced race in the module's container suite). - `chk_parameter_set_versions_release_stamps` holds a RELEASED or DISCARDED row to its `released_at` (a release stamps it, an import copies the source's, discard and restore never touch it). The discard-stamps and via CHECKs are the template twins. - A version is referenced by nothing else: no execution row (a set is evaluated, never executed), and the templates reverse arrow (record §8.4, lane D) scans `body_json` for pins — no foreign key. So a purge is always a hard delete, and the entity purge cascades. ### 4.28 `visualizations` **A visualization** (V42, #10 L1a; the [dashboard implementation spec](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-28-dashboard-implementation-spec.md) §2.1, [Dashboards §2](/docs/dashboards.md)) — the INDEX over a visualization's versions: [§4.26 `parameter_sets`](#426-parameter_sets) line for line with the names changed. ```sql CREATE TABLE visualizations ( id UUID PRIMARY KEY, workspace_id UUID NOT NULL REFERENCES workspaces(id), name TEXT NOT NULL, display_name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', current_version INTEGER NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), CONSTRAINT uq_visualizations_workspace_name UNIQUE (workspace_id, name), CONSTRAINT chk_visualizations_current_version CHECK (current_version IS NULL OR current_version >= 1) ); ``` **Notes:** every note of §4.26 holds with "visualization" for "set": the id has no database default (the application generates it and an import KEEPS it — a taken id is refused `visualization.import.id_taken`, never re-issued); `name` is unique per workspace forever and follows the folder grammar in code; `display_name` / `description` index the CURRENT body; no entity status column; every statement filters by `workspace_id` (`VisualizationRepository`). ### 4.29 `visualization_versions` **One version of a visualization** (V42) — [§4.27 `parameter_set_versions`](#427-parameter_set_versions) line for line; `body_json` holds [Dashboards §2.1](/docs/dashboards.md)'s document with `name` omitted. ```sql CREATE TABLE visualization_versions ( visualization_id UUID NOT NULL REFERENCES visualizations(id) ON DELETE CASCADE, version INTEGER NOT NULL, body_json JSONB NOT NULL, status TEXT NOT NULL DEFAULT 'DRAFT' CONSTRAINT chk_visualization_versions_status CHECK (status IN ('DRAFT', 'RELEASED', 'DISCARDED')), body_hash TEXT NOT NULL, released_at TIMESTAMPTZ NULL, released_by UUID REFERENCES users(id), discarded_at TIMESTAMPTZ NULL, discarded_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id), updated_at TIMESTAMPTZ NULL, created_via TEXT NOT NULL DEFAULT 'session', updated_via TEXT NOT NULL DEFAULT 'session', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), PRIMARY KEY (visualization_id, version), CONSTRAINT chk_visualization_versions_version CHECK (version >= 1), -- The body is an object whose versioned artifact never carries the visualization's name (spec §3). CONSTRAINT chk_visualization_versions_body CHECK (jsonb_typeof(body_json) = 'object' AND NOT (body_json ? 'name')), -- A DRAFT has never been released; every other row has (released_at survives discard/restore). CONSTRAINT chk_visualization_versions_release_stamps CHECK ( (status = 'DRAFT' AND released_at IS NULL AND released_by IS NULL) OR (status <> 'DRAFT' AND released_at IS NOT NULL) ), CONSTRAINT chk_visualization_versions_discard_stamps CHECK ( (status = 'DISCARDED' AND discarded_at IS NOT NULL) OR (status <> 'DISCARDED' AND discarded_at IS NULL AND discarded_by IS NULL) ), CONSTRAINT chk_visualization_versions_via CHECK ( created_via IN ('session', 'api_key', 'mcp') AND updated_via IN ('session', 'api_key', 'mcp') ) ); CREATE UNIQUE INDEX uq_visualization_versions_one_draft ON visualization_versions (visualization_id) WHERE status = 'DRAFT'; ``` **Notes:** the lifecycle is §4.27's (versioning §3.5): the same statuses, the one-draft partial index, the database-computed hash (`HASH_EXPR` over the bound body, versioning §4.1's pipeline rule), the release/discard/via stamps. `ArtifactRepository` maps the first-writer race (on the version PRIMARY KEY or the one-draft index) to `visualization.version.conflict` carrying the winner's state. The one row that references a version is its test evidence ([§4.32](#432-visualization_test_runs)), which cascades: purging a draft takes its runs. ### 4.30 `dashboards` **A dashboard** (V42) — §4.28 again for the second family; addressed by its UUID, named by the folder grammar, unique per workspace forever. ```sql CREATE TABLE dashboards ( id UUID PRIMARY KEY, workspace_id UUID NOT NULL REFERENCES workspaces(id), name TEXT NOT NULL, display_name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', current_version INTEGER NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), CONSTRAINT uq_dashboards_workspace_name UNIQUE (workspace_id, name), CONSTRAINT chk_dashboards_current_version CHECK (current_version IS NULL OR current_version >= 1) ); ``` ### 4.31 `dashboard_versions` **One version of a dashboard** (V42) — §4.29 again; `body_json` holds [Dashboards §2.2](/docs/dashboards.md)'s document with `name` omitted. Nothing references a dashboard version in this migration; the refresh record that will (`dashboard_refreshes`, spec §2.2) is V43's (L2). ```sql CREATE TABLE dashboard_versions ( dashboard_id UUID NOT NULL REFERENCES dashboards(id) ON DELETE CASCADE, version INTEGER NOT NULL, body_json JSONB NOT NULL, status TEXT NOT NULL DEFAULT 'DRAFT' CONSTRAINT chk_dashboard_versions_status CHECK (status IN ('DRAFT', 'RELEASED', 'DISCARDED')), body_hash TEXT NOT NULL, released_at TIMESTAMPTZ NULL, released_by UUID REFERENCES users(id), discarded_at TIMESTAMPTZ NULL, discarded_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id), updated_at TIMESTAMPTZ NULL, created_via TEXT NOT NULL DEFAULT 'session', updated_via TEXT NOT NULL DEFAULT 'session', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), created_by UUID NOT NULL REFERENCES users(id), PRIMARY KEY (dashboard_id, version), CONSTRAINT chk_dashboard_versions_version CHECK (version >= 1), -- The body is an object whose versioned artifact never carries the dashboard's name (spec §3). CONSTRAINT chk_dashboard_versions_body CHECK (jsonb_typeof(body_json) = 'object' AND NOT (body_json ? 'name')), CONSTRAINT chk_dashboard_versions_release_stamps CHECK ( (status = 'DRAFT' AND released_at IS NULL AND released_by IS NULL) OR (status <> 'DRAFT' AND released_at IS NOT NULL) ), CONSTRAINT chk_dashboard_versions_discard_stamps CHECK ( (status = 'DISCARDED' AND discarded_at IS NOT NULL) OR (status <> 'DISCARDED' AND discarded_at IS NULL AND discarded_by IS NULL) ), CONSTRAINT chk_dashboard_versions_via CHECK ( created_via IN ('session', 'api_key', 'mcp') AND updated_via IN ('session', 'api_key', 'mcp') ) ); CREATE UNIQUE INDEX uq_dashboard_versions_one_draft ON dashboard_versions (dashboard_id) WHERE status = 'DRAFT'; -- #331 (V46): the pins path, indexed for the containment probe — `livePinsOf`'s -- `body_json -> 'visualizations' @> :probe` (the pin guards and `visualizations_get` -- pay it once per probe). Measured on demo-shaped content: a bitmap index scan, -- 5.3 ms → 0.9 ms at 4 000 dashboards. The batched page answer reads the pins -- whole and does not use the index; it is not written for it. CREATE INDEX idx_dashboard_versions_pins ON dashboard_versions USING GIN ((body_json -> 'visualizations')); ``` ### 4.32 `visualization_test_runs` **One agent test session over one visualization version** (V42 + V44; spec §11.2, D56 (a)) — written by the tests lane (L4a, #352). The candidate content is `body_hash`: a run whose hash no longer matches its version's is void (EXPIRED on read) and can never qualify a release. ```sql CREATE TABLE visualization_test_runs ( id UUID PRIMARY KEY, visualization_id UUID NOT NULL, version INTEGER NOT NULL, body_hash TEXT NOT NULL, session_id UUID NOT NULL, preview_token_hash TEXT NULL, expires_at TIMESTAMPTZ NOT NULL, started_by UUID NOT NULL REFERENCES users(id), started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), completed_at TIMESTAMPTZ NULL, status TEXT NOT NULL DEFAULT 'RUNNING', cases_json JSONB NULL, environment_json JSONB NULL, mechanical_json JSONB NULL, -- The run belongs to one VERSION: purging a draft takes its evidence with it. CONSTRAINT fk_visualization_test_runs_version FOREIGN KEY (visualization_id, version) REFERENCES visualization_versions (visualization_id, version) ON DELETE CASCADE, CONSTRAINT uq_visualization_test_runs_session UNIQUE (visualization_id, version, session_id), CONSTRAINT chk_visualization_test_runs_status CHECK (status IN ('RUNNING', 'GREEN', 'RED', 'INCOMPLETE', 'EXPIRED')), -- A verdict is a completed run; a running one has none. EXPIRED is either (a session that timed -- out unsubmitted, or a completed run whose version's hash moved on). CONSTRAINT chk_visualization_test_runs_completed CHECK ( (status = 'RUNNING' AND completed_at IS NULL) OR (status IN ('GREEN', 'RED', 'INCOMPLETE') AND completed_at IS NOT NULL) OR status = 'EXPIRED' ), CONSTRAINT chk_visualization_test_runs_json CHECK ( (cases_json IS NULL OR jsonb_typeof(cases_json) = 'array') AND (environment_json IS NULL OR jsonb_typeof(environment_json) = 'object') AND (mechanical_json IS NULL OR jsonb_typeof(mechanical_json) = 'object') ) ); -- V44 (#352): the screenshot upload capability — hash-only at rest, expiring no later than the -- session's own deadline, consumed atomically with the stored image. ALTER TABLE visualization_test_runs ADD COLUMN upload_token_hash TEXT NULL, ADD COLUMN upload_expires_at TIMESTAMPTZ NULL, ADD COLUMN upload_consumed_at TIMESTAMPTZ NULL; ALTER TABLE visualization_test_runs ADD CONSTRAINT chk_visualization_test_runs_upload CHECK ( ( upload_token_hash IS NULL AND upload_expires_at IS NULL AND upload_consumed_at IS NULL ) OR ( upload_token_hash IS NOT NULL AND upload_token_hash ~ '^[0-9a-f]{64}$' AND upload_expires_at IS NOT NULL AND (upload_consumed_at IS NULL OR upload_consumed_at < upload_expires_at) ) ); ``` **Notes:** - **The run hangs off the VERSION** (`fk_visualization_test_runs_version`, `ON DELETE CASCADE`), not only the visualization: purging a draft takes its evidence, and a release keeps the run that qualified it for the version's life (the retention rule of spec §2.1 is the SERVICE's — at most one completed run per draft keeps its screenshot). - **The preview token is stored hashed** (`preview_token_hash`, spec §11.2): the 32-byte capability the agent's browser presents is never in the database; `expires_at` is its TTL (`datapipelines.visualization.session-ttl-minutes`, 60 by default) and the hash is cleared on submit or expiry. - `cases_json` (per case: name, verdict, notes ≤ 2,000 characters), `environment_json` (agent-reported — theme, viewport, browser, locale, renderer version) and `mechanical_json` (the server's §11.3 outcome) are bounded by the service; the schema holds only their JSON kind. - **The upload capability (V44) is the second, purpose-bound credential**: minted at successful submission, hash-only (`upload_token_hash`), expiring no later than the session's `expires_at` (`upload_expires_at`), consumed once (`upload_consumed_at`, strictly before the deadline). The three columns are one capability — nothing without the hash, nothing beside a minted one — and the single-use consumption is one guarded UPDATE, so the replay fence survives a process restart. The hash's `IS NOT NULL` is load-bearing in the CHECK: `NULL ~ regex` is NULL, and a CHECK passes on NULL. ### 4.33 `visualization_test_screenshots` **The one representative image of a run** (V42; D35) — a separate table so a run listing never loads bytes. Written by L4a (#352), which also validates the bytes before the table ever sees them (the detected media type, independently read positive dimensions, the computed digest). ```sql CREATE TABLE visualization_test_screenshots ( run_id UUID PRIMARY KEY REFERENCES visualization_test_runs(id) ON DELETE CASCADE, media_type TEXT NOT NULL, bytes BYTEA NOT NULL, sha256 TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL, depicted_case TEXT NULL, uploaded_by UUID NOT NULL REFERENCES users(id), uploaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT chk_visualization_test_screenshots_media_type CHECK (media_type IN ('image/png', 'image/webp')), -- The spec §17 cap: 4 MiB, independent of the platform's 2 MiB request-body cap. CONSTRAINT chk_visualization_test_screenshots_size CHECK (octet_length(bytes) BETWEEN 1 AND 4194304), CONSTRAINT chk_visualization_test_screenshots_sha256 CHECK (sha256 ~ '^[0-9a-f]{64}$'), CONSTRAINT chk_visualization_test_screenshots_dimensions CHECK (width >= 1 AND height >= 1) ); ``` **Notes:** the 4 MiB cap is the schema's as well as the route's (spec §17 — its own cap, independent of the platform's 2 MiB request-body cap); `sha256` is the server's digest of the stored bytes (integrity, not authorship — D56's trust boundary); `ON DELETE CASCADE` from the run. ### 4.34 `dashboard_refreshes` **One row per refresh of a released dashboard** (V43; #10 L2, the implementation spec's §8.3, §9 and §18). `id` is the refresh id the CLIENT mints (a UUID v4, validated in code — a reused id is refused before any insert). The row is inserted `RUNNING` only AFTER admission holds the slots (§18 premise 3): a refused refresh (`429 dashboard.refresh.saturated`) leaves NO row, and the status list has no value for a refusal. ```sql CREATE TABLE dashboard_refreshes ( id UUID PRIMARY KEY, -- the client-minted refresh id (UUID v4) dashboard_id UUID NOT NULL REFERENCES dashboards(id) ON DELETE CASCADE, dashboard_version INTEGER NOT NULL, -- the RELEASED version this refresh served workspace_id UUID NOT NULL REFERENCES workspaces(id), instance_id UUID NOT NULL, -- the client's page instance: ownership of ITS refreshes, nothing else principal_user_id UUID NULL REFERENCES users(id), principal_key_id TEXT NULL REFERENCES api_keys(id), -- L5's column, present so V44 needs no rewrite scope TEXT NOT NULL, -- 'ALL' | 'TARGETS' targets_json JSONB NOT NULL DEFAULT '[]'::jsonb, parameter_revision INTEGER NOT NULL, selections_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- <= 64 KiB stored (CHECK) status TEXT NOT NULL, -- RefreshStatus (enums.md §38) started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), finished_at TIMESTAMPTZ NULL, summary_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- per target: outcome, stage, reason; per source: rows, bytes CONSTRAINT chk_dashboard_refreshes_principal CHECK ((principal_user_id IS NOT NULL AND principal_key_id IS NULL) OR (principal_user_id IS NULL AND principal_key_id IS NOT NULL)), CONSTRAINT chk_dashboard_refreshes_scope CHECK (scope IN ('ALL', 'TARGETS')), CONSTRAINT chk_dashboard_refreshes_status CHECK (status IN ('RUNNING', 'COMPLETED', 'PARTIAL', 'FAILED', 'ABORTED', 'TIMED_OUT')), CONSTRAINT chk_dashboard_refreshes_selections_size CHECK (pg_column_size(selections_json) <= 65536), CONSTRAINT chk_dashboard_refreshes_finished CHECK ((status = 'RUNNING' AND finished_at IS NULL) OR (status <> 'RUNNING' AND finished_at IS NOT NULL)) ); CREATE INDEX idx_dashboard_refreshes_dashboard_started ON dashboard_refreshes (workspace_id, dashboard_id, started_at DESC); CREATE INDEX idx_dashboard_refreshes_running ON dashboard_refreshes (started_at) WHERE status = 'RUNNING'; CREATE INDEX idx_dashboard_refreshes_finished ON dashboard_refreshes (finished_at) WHERE finished_at IS NOT NULL; ``` **Notes:** a refresh is the CALLER's — the list and read routes return the caller's own (`dashboard.execute`), all of the dashboard's with `execution.read_all`. `dashboard_id` cascades: purging a draft-only dashboard drops the preview refreshes it accumulated (§18 premise 7); a released dashboard is never purged (versioning §3.2). Retention is the execution events' ([§8.1](#81-execution-event-cleanup)); a crash that leaves a row `RUNNING` is closed by [§8.4](#84-stale-dashboard-refresh-sweep). The terminal write (status, `finished_at`, `summary_json`) and its audit row run under a non-cancellable context, so a client that disconnects mid-stream still ends the row. ### 4.35 `dashboard_refresh_executions` **The link D52 requires** — a refresh to the executions it started, one per source. The events pane joins here and never copies. Written by the launcher's `onRecorded` hook, which fires right after the execution's `RUNNING` insert and before its first event, so "linked before its first event" holds by construction. `shared` marks an invocation that served more than one target within the refresh (sharing is within ONE refresh, never across refreshes). ```sql CREATE TABLE dashboard_refresh_executions ( refresh_id UUID NOT NULL REFERENCES dashboard_refreshes(id) ON DELETE CASCADE, source_name TEXT NOT NULL, execution_id UUID NOT NULL REFERENCES pipeline_executions(execution_id), shared BOOLEAN NOT NULL DEFAULT FALSE, CONSTRAINT pk_dashboard_refresh_executions PRIMARY KEY (refresh_id, source_name) ); CREATE INDEX idx_dashboard_refresh_executions_execution ON dashboard_refresh_executions (execution_id); ``` **Notes:** the execution reference has NO cascade — the execution rows are the durable history and outlive their refresh's link (§8.1). A dashboard run writes no stored result (`directSink`): the execution has no `result_row_count` and `GET /api/v1/executions/{id}/result` answers the family's `not_found`; the per-source byte and row counts are in the refresh's `summary_json`. ### 4.36 `dashboard_key_bindings` Which `dashboard` keys serve which part of the dashboard NAME space (V45, L5 #367) — [`endpoint_key_bindings`](#414-endpoint_key_bindings)' twin for the `dashboard` key kind. See [Auth §7.7](/docs/auth.md#77-key-kinds-and-published-endpoint-bindings) and [Dashboards §6.5](/docs/dashboards.md#65-credentials-session-and-proxy). ```sql CREATE TABLE dashboard_key_bindings ( name_prefix TEXT NOT NULL, -- a FOLDER of the name space: 'finance/dashboards' binds every dashboard beneath it; '/' binds the tree api_key_id TEXT NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE, workspace_id UUID NOT NULL REFERENCES workspaces(id), created_by UUID NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (name_prefix, api_key_id) ); CREATE INDEX idx_dashboard_key_bindings_key ON dashboard_key_bindings(api_key_id); ``` **Notes:** - `name_prefix` is a **folder** of the dashboard NAME space, not a pattern: `finance/dashboards` serves every dashboard whose name begins `finance/dashboards/`, and the root `/` serves the workspace's whole dashboard tree. Resolution walks the dashboard name's ancestors from the most specific, and the FIRST folder carrying any binding decides — a deeper binding **replaces** an inherited one (R-EP2 verbatim), and a dashboard with no binding on any ancestor is served to NO key. - `api_key_id` is `TEXT` because [`api_keys.id`](#42-api_keys) is the `dpk_…` id itself, not a UUID. The key retention purge refuses to delete a key this table names, exactly as it does for [`endpoint_key_bindings`](#414-endpoint_key_bindings). - `ON DELETE CASCADE`: a key that no longer exists cannot serve anything, and an orphaned binding would show on the Keys page as a binding to nothing. - The primary key `(name_prefix, api_key_id)` says one key binds a folder once and several keys may bind the same folder. - No `updated_at`: a binding is inserted and deleted, never edited. ### 4.37 `parameter_evaluations` **One row per parameter-set evaluation ATTEMPT, whoever ran it** (V48; #376, the [parameter-set workspace spec](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-10-02-parameter-set-workspace-spec.md) §2.1, R2) — the Parameter Sets page's observed evaluation, a dashboard's parameter evaluate or refresh, the REST evaluate, the MCP tool. `id` is the observed route's client-minted `evaluation_id` (the owner's §11.3 ruling), server-minted for every other caller — never re-issued. The row is inserted `RUNNING` when the evaluator ADMITS the attempt: a request refused before admission (an unknown selection key, `parameter.evaluate.unknown_parameter`) writes no row. It is finished exactly once, at the one point that owns the evaluation's end, non-cancellably (`ParameterEvaluator`'s `finally`; the dashboard refreshes' rule), whatever the ending. ```sql CREATE TABLE parameter_evaluations ( id UUID PRIMARY KEY, -- the evaluation id (client-minted on the observed route) workspace_id UUID NOT NULL REFERENCES workspaces(id), parameter_set_id UUID NOT NULL REFERENCES parameter_sets(id) ON DELETE CASCADE, parameter_set_version INTEGER NOT NULL, -- the version actually evaluated; NO FK (a purged version keeps its record) caller TEXT NOT NULL, -- EvaluationCaller (enums.md §39) principal_user_id UUID NULL REFERENCES users(id), principal_key_id TEXT NULL, -- the key's dpk_ id as text; NO FK (see the notes) correlation_id TEXT NULL, -- the dashboard runtime's refresh id; NULL for page, REST, MCP status TEXT NOT NULL, -- ParameterEvaluationStatus (enums.md §40) outcome_code TEXT NULL, -- the catalogued whole-request code; never a message valid BOOLEAN NULL, -- the response's valid flag — set exactly when COMPLETED outcomes_json JSONB NULL, -- per parameter {name, outcome, error_code?, detail?}; <= 8 KiB stored started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), finished_at TIMESTAMPTZ NULL, CONSTRAINT chk_parameter_evaluations_version CHECK (parameter_set_version >= 1), CONSTRAINT chk_parameter_evaluations_caller CHECK (caller IN ('PAGE', 'DASHBOARD', 'PIPELINE', 'REST', 'MCP')), CONSTRAINT chk_parameter_evaluations_principal CHECK ((principal_user_id IS NOT NULL AND principal_key_id IS NULL) OR (principal_user_id IS NULL AND principal_key_id IS NOT NULL)), CONSTRAINT chk_parameter_evaluations_status CHECK (status IN ('RUNNING', 'COMPLETED', 'ABORTED', 'TIMEOUT', 'FAILED', 'INCOMPLETE')), CONSTRAINT chk_parameter_evaluations_finished CHECK ((status = 'RUNNING' AND finished_at IS NULL) OR (status <> 'RUNNING' AND finished_at IS NOT NULL)), CONSTRAINT chk_parameter_evaluations_valid CHECK ((status = 'COMPLETED') = (valid IS NOT NULL)), CONSTRAINT chk_parameter_evaluations_outcomes_size CHECK (pg_column_size(outcomes_json) <= 8192) ); CREATE INDEX idx_parameter_evaluations_set_started ON parameter_evaluations (workspace_id, parameter_set_id, started_at DESC); CREATE INDEX idx_parameter_evaluations_running ON parameter_evaluations (started_at) WHERE status = 'RUNNING'; CREATE INDEX idx_parameter_evaluations_finished ON parameter_evaluations (started_at) WHERE finished_at IS NOT NULL; ``` **Notes:** - **The status words** (enums.md §40): `COMPLETED` — a response was produced (`valid` its flag; an invalid form is a COMPLETED `valid = false` evaluation, never a failure); `TIMEOUT` — the whole-request `parameter.evaluate.timeout`; `FAILED` — a whole-request refusal after work began (`outcome_code` names it, e.g. `parameter.evaluate.response_too_large`) or an uncatalogued defect (`outcome_code` NULL; the exception class is logged, never stored); `ABORTED` — the caller stopped it (the observed route's disconnect grace, the owner's §11.7 ruling, or a shutdown); `INCOMPLETE` — no terminal write ever landed, written ONLY by the stale sweep ([§8.5](#85-stale-parameter-evaluation-sweep)) and never claiming a timeout that may not have happened. - **`outcomes_json` never holds a value.** One entry per parameter that finished, in declaration order: `outcome` is `resolved`, `reset` or `error`; `error_code` the first error's catalogued code; `detail` that error's own `details.reason` word (≤ 200 characters) — never its message, which may quote a driver. `required_missing`, `constraint_violation`, `selector_rows_invalid` and every per-parameter error land here, so a parameter that ran no statement is never given a fabricated query row. The writer bounds the encoding (5,120 text bytes — measured ≤ 6,005 bytes of binary JSONB at the worst shape), degrading to the error entries first; the 8 KiB CHECK is the backstop. - **The principal is a person XOR a key** (the [§4.34](#434-dashboard_refreshes) CHECK shape): a session records its user, an API key its key id. `principal_key_id` has no foreign key on purpose: the keys purge ([Auth §11C](/docs/auth.md#11c-the-keys-purge-keys-v2-a17b5)) deletes a revoked key once nothing references it, and a diagnostic record must neither hold a key alive nor block that purge; the id stays as the text it was. - **`PIPELINE` is dormant** (the owner's §11.10 ruling): declared so no migration widens the CHECK later, refused by the recorder until a pipeline binds a parameter set (the engine record's §13). - **Cascade and retention.** Purging the SET takes its history (a history of a purged artifact answers absent); purging a VERSION never rewrites history. Finished rows go with the execution events ([§8.1](#81-execution-event-cleanup)); a row a crash left `RUNNING` is closed by [§8.5](#85-stale-parameter-evaluation-sweep). - **Persistence never fails an evaluation**: a failed START is one structured ERROR line and the evaluation proceeds unrecorded; a failed terminal write leaves the row `RUNNING` for the sweep. ### 4.38 `parameter_evaluation_queries` **One row per actual statement attempt of an evaluation** (V48; #376, the spec's §2.2) — a template-backed `SELECT`'s selector, or a database-fed `INPUT`'s source. Inserted when the statement asks the `SelectorPool` bulkhead for admission (`queued_at`), finished once when it ends; concurrency is preserved IN the evidence — each row carries its own three stamps (taken on the selector's worker thread for `started_at`/`ended_at`) and nothing serialises them, so two statements that ran together show overlapping ranges. ```sql CREATE TABLE parameter_evaluation_queries ( id UUID PRIMARY KEY, evaluation_id UUID NOT NULL REFERENCES parameter_evaluations(id) ON DELETE CASCADE, parameter TEXT NOT NULL, datasource TEXT NOT NULL, -- re-resolved per evaluate (C25) template_id TEXT NOT NULL, -- the pin that rendered the statement (P22) template_version INTEGER NOT NULL, queued_at TIMESTAMPTZ NULL, -- asked the bulkhead for admission started_at TIMESTAMPTZ NULL, -- the worker began it; NULL when it never started ended_at TIMESTAMPTZ NULL, -- the worker returned it; NULL on TIMEOUT / ABORTED outcome TEXT NULL, -- QueryAttemptOutcome (enums.md §41); NULL while in flight refusal_code TEXT NULL, -- the catalogued code, REFUSED only error_code TEXT NULL, -- the owning subsystem's code, FAILED only row_count INTEGER NULL, -- rows read, EXECUTED only CONSTRAINT chk_parameter_evaluation_queries_outcome CHECK (outcome IN ('EXECUTED', 'REFUSED', 'FAILED', 'TIMEOUT', 'ABORTED')), CONSTRAINT chk_parameter_evaluation_queries_codes CHECK ((refusal_code IS NULL OR outcome = 'REFUSED') AND (error_code IS NULL OR outcome = 'FAILED') AND (row_count IS NULL OR (outcome = 'EXECUTED' AND row_count >= 0))) ); CREATE INDEX idx_parameter_evaluation_queries_evaluation ON parameter_evaluation_queries (evaluation_id); ``` **Notes:** - **`REFUSED` is a value, never an absence** (R2): the statement never ran — a render failure (`pipeline.node.template_render_failed` / `…template_not_found`), a datasource the workspace cannot see (`datasource.not_found`) or reach (`pipeline.execution.datasource_unreachable`), a bind the context lacks (`pipeline.node.sql_parameter_missing`), `parameter.evaluate.too_many_binds`, the read-only gate or a placeholder text the binder refused (`pipeline.node.query_execution_failed` with that `details.reason`), a full bulkhead (`parameter.evaluate.selectors_saturated` — `started_at` NULL). `FAILED` ran and failed (a driver error, the statement's own timeout `pipeline.node.query_timeout`, `datasource.table_forbidden`; a runtime defect has no code). `TIMEOUT` — the evaluate deadline abandoned it (`Statement.cancel()` + `ConnectionPool.discard`); `ABORTED` — its evaluation's caller stopped it: both mean only that the evaluation stopped waiting, so `ended_at` stays NULL — the worker may still be running and the row never claims it stopped. - **Never stored, by name:** SQL text (rendered or pinned), a bind value, a selection or resolved value, a result row, a driver message — no column exists that could carry one. - **Constants and free inputs are resolution steps, not query rows**: a `constants` selector and a plain-default `INPUT` produce NO row (their outcomes ride `outcomes_json`); the SAVE-time probe is authoring validation and records nothing here. - An evaluation holds at most `max-parameters-per-set` rows. `outcome` stays NULL on the rows of an evaluation the sweep closed `INCOMPLETE` — nobody saw them end. ### 4.39 `schedule_notification_events` **One row per run transition — or schedule block — that the schedule's event set selected at that moment** (V51; #442b, the [scheduler design revision](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-22-scheduler-design-revision.md) §6.1). Written by `RunLedger` through `NotificationOutbox` in the SAME transaction as the transition and its trail row: both commit or neither, so there is never a notice without its transition nor a transition whose selected notice was lost. ```sql CREATE TABLE schedule_notification_events ( id UUID PRIMARY KEY, schedule_id UUID NOT NULL REFERENCES schedules(id), run_id UUID NOT NULL REFERENCES schedule_runs(id), -- a block is always caused by a run; the notice attaches to it event TEXT NOT NULL, -- the checkbox (NotificationEvent, enums.md §42) outcome TEXT NOT NULL, -- what happened (NotificationOutcome, enums.md §43) run_state TEXT NOT NULL, -- the run's state after the transition reason TEXT, -- the run's (or the block's) reason word occurred_at TIMESTAMPTZ NOT NULL, sequence INT NOT NULL, -- the trail seq of the transition that produced it created_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT uq_schedule_notification_events_run_outcome UNIQUE (run_id, outcome), CONSTRAINT chk_schedule_notification_events_event CHECK (event IN ('start', 'success', 'failure', 'unknown', 'blocked')), CONSTRAINT chk_schedule_notification_events_outcome CHECK ( (event = 'start' AND outcome = 'started') OR (event = 'success' AND outcome = 'completed') OR (event = 'failure' AND outcome IN ('failed', 'could_not_start', 'cancelled', 'aborted')) OR (event = 'unknown' AND outcome IN ('unknown', 'unknown_update')) OR (event = 'blocked' AND outcome = 'blocked')), CONSTRAINT chk_schedule_notification_events_sequence CHECK (sequence >= 1) ); CREATE INDEX idx_schedule_notification_events_schedule ON schedule_notification_events (schedule_id); ``` **Notes:** - **Nothing is written when delivery is off** (`notifications.enabled=false`, mail unconfigured, or a development posture with no allowlist), when the event set does not select the outcome, when the schedule has no recipients, or when the schedule is deleted — no phantom rows a later enable would send. - **A run reaches each outcome once**, so the UNIQUE makes a replayed transition write nothing (`ON CONFLICT … DO NOTHING`). `blocked` is written only on the ACTUAL transition to blocked (`ScheduleRepository.block` returned true), never for a block that was already in place. A skipped run (its schedule blocked or deleted) writes nothing. ### 4.40 `schedule_notification_deliveries` **One row per (event, recipient)** (V51; #442b, record §6.1) — one message per recipient, never a shared address list. The address is snapshotted at enqueue; the Message-ID (``) is fixed at enqueue, so a resend carries the same one. ```sql CREATE TABLE schedule_notification_deliveries ( id UUID PRIMARY KEY, event_id UUID NOT NULL REFERENCES schedule_notification_events(id), recipient TEXT NOT NULL, -- personal data: never logged; shown only to schedule.update holders state TEXT NOT NULL, -- DeliveryState (enums.md §44) attempts INT NOT NULL DEFAULT 0, next_attempt_at TIMESTAMPTZ, -- set exactly while pending sending_since TIMESTAMPTZ, -- a claimed send in flight last_error_code TEXT, -- a sanitized code, never transport text message_id TEXT NOT NULL, sent_at TIMESTAMPTZ, -- the relay accepted it resend_count INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT uq_schedule_notification_deliveries_event_recipient UNIQUE (event_id, recipient), CONSTRAINT uq_schedule_notification_deliveries_message_id UNIQUE (message_id), CONSTRAINT chk_schedule_notification_deliveries_state CHECK (state IN ('pending', 'accepted', 'failed', 'uncertain')), CONSTRAINT chk_schedule_notification_deliveries_due CHECK ((state = 'pending') = (next_attempt_at IS NOT NULL)), CONSTRAINT chk_schedule_notification_deliveries_sending CHECK (sending_since IS NULL OR state = 'pending'), CONSTRAINT chk_schedule_notification_deliveries_accepted CHECK (state <> 'accepted' OR sent_at IS NOT NULL), CONSTRAINT chk_schedule_notification_deliveries_failure CHECK (state NOT IN ('failed', 'uncertain') OR last_error_code IS NOT NULL), CONSTRAINT chk_schedule_notification_deliveries_counts CHECK (attempts >= 0 AND resend_count >= 0) ); CREATE INDEX idx_schedule_notification_deliveries_due ON schedule_notification_deliveries (state, next_attempt_at); CREATE INDEX idx_schedule_notification_deliveries_event ON schedule_notification_deliveries (event_id); ``` **Notes:** - **Decided per recipient at enqueue, never dropped silently:** a recipient outside the allowlist is written `failed` / `domain_not_allowed`; over `notifications.backlog-max` pending deliveries a new one is written `failed` / `backlog_full` (and one WARN line). The codes are listed in enums.md §44. - **`accepted` means the SMTP relay took it** — not that it reached an inbox; no state says "delivered". `uncertain` is a send that may have reached the relay (the connection dropped after the data was sent, or a claim whose `sending_since` went stale because the instance stopped mid-send); it is never retried automatically. A person may resend a `failed` or `uncertain` delivery (`resend_count` counts it), with a may-duplicate warning for the uncertain one. **Strict deployment backlog admission (#442 round two).** Enqueue and explicit resend take transaction-scoped `pg_advisory_xact_lock(442004001)` (`NotificationRepository.ADMISSION_LOCK_KEY`) before counting pending rows, in the same READ COMMITTED transaction as insert/requeue. Lock order: enqueue takes the run row, then the advisory lock; resend takes the delivery row, then the advisory lock. The sender claims delivery rows with `FOR UPDATE SKIP LOCKED` and never takes the advisory lock, so there is no cycle. The advisory statement has a five-second query timeout on a dedicated template. This serializes admission across schedules and instances; at the hard cap, new deliveries become `failed/backlog_full` and resend is refused (`409 schedule.notification.backlog_full`, `details.pending`, `details.backlog_max`) without changing the row. Lowering the configured cap does not discard existing pending mail. ## 5. Index Strategy Summary **This table is generated from §4 and must contain nothing §4 does not create.** Two kinds of entry appear: - **Explicit** — a `CREATE INDEX` / `CREATE UNIQUE INDEX` statement in §4. The name is ours. - **Via constraint** — the index Postgres creates automatically for a `PRIMARY KEY` or `UNIQUE` constraint. There is no `CREATE INDEX` for these; the name is Postgres's (`_pkey`, `
__key`) unless the constraint is explicitly named. **Do not write a `CREATE INDEX` for a column list a constraint already covers** — it costs a second btree on every write and buys nothing. | Table | Index | Kind | Purpose | |---|---|---|---| | `users` | `users_pkey` | via PK | Lookup by id (hot — per-request `is_active` check, D13) | | `users` | `users_email_key` | via UNIQUE | Login lookup by email; enforces one account per email | | `users` | `uq_users_provider_subject` | explicit (unique) | OIDC identity lookup `(provider, provider_subject)` | | `api_keys` | `api_keys_pkey` | via PK | Key lookup by `dpk_` id on every API-key request | | `api_keys` | `idx_api_keys_user` | explicit (partial) | List a user's active keys | | `api_keys` | `idx_api_keys_expires` | explicit (partial) | Find expiring/expired keys for cleanup | | `api_keys` | `idx_api_keys_endpoint_kind` | explicit (partial) | The endpoint keys of a workspace — the endpoints screen and binding resolution; partial because mcp keys are the overwhelming majority (V11) | | `api_keys` | `uq_api_keys_live_workspace_name` | explicit (partial, unique) | V37 (keys v2 A18): a key NAME is unique within its workspace among LIVE keys — the duplicate-name arbiter behind the creation path's clean 409 | | `api_keys` | `idx_api_keys_created_by` | explicit | V34 (#215): the keys a person CREATED — own-key listing and creator-scoped revocation, now that `user_id` names an endpoint/server key's own identity | | `audit_log` | `audit_log_pkey` | via PK | Surrogate `BIGSERIAL` id | | `audit_log` | `idx_audit_timestamp` | explicit | Recent events (DESC) | | `audit_log` | `idx_audit_user` | explicit (partial) | Per-user audit trail | | `audit_log` | `idx_audit_event` | explicit | Filter by event type | | `pipelines` | `pipelines_pkey` | via PK | Lookup by id | | `pipelines` | `uq_pipelines_workspace_name` | via UNIQUE | Name lookup within a workspace; prevents duplicate names per workspace (incl. soft-deleted) | | `pipelines` | `idx_pipelines_owner` | explicit (partial) | List non-deleted pipelines by owner | | `pipeline_versions` | `pipeline_versions_pkey` | via PK | `(pipeline_id, version)` fetch; also the target of `fk_executions_pipeline_version` | | `pipeline_versions` | `uq_pipeline_versions_one_draft` | explicit (partial, unique) | The one-DRAFT-per-pipeline rule of versioning §3.3 — the physical concurrency guard behind copy-on-write | | `pipeline_executions` | `pipeline_executions_pkey` | via PK | Lookup by `execution_id` | | `pipeline_executions` | `idx_executions_pipeline` | explicit | List executions for a pipeline, newest first | | `pipeline_executions` | `idx_executions_status_running` | explicit (partial) | Find in-flight executions by AGE — the stale sweep's backstop path for rows with no heartbeat ([§8.3](#83-stale-execution-sweep)) | | `pipeline_executions` | `idx_executions_heartbeat` | explicit (partial) | Find in-flight executions by HEARTBEAT — the stale sweep's primary path since V21, and why a 15-second tick stays cheap ([§8.3](#83-stale-execution-sweep)) | | `pipeline_executions` | `idx_executions_user` | explicit | List executions by user | | `pipeline_executions` | `idx_executions_correlation` | explicit (partial) | Trace lookup by correlation id | | `pipeline_executions` | `idx_executions_root` | explicit | The whole execution family (root + descendants) in one lookup — composition lineage and cancellation key off `root_execution_id` (V3) | | `execution_events` | `execution_events_pkey` | via PK | Surrogate `BIGSERIAL` id | | `execution_events` | `uq_events_execution_event` | via UNIQUE | Event replay ordered by `event_id`; enforces no duplicate sequence numbers | | `templates` | `templates_pkey` | via PK | Lookup by surrogate id (V4) | | `templates` | `uq_templates_workspace_name` | via UNIQUE | Name lookup within a workspace; prevents duplicate names per workspace (incl. soft-deleted) | | `templates` | `idx_templates_active` | explicit (partial) | List non-deleted templates (indexed on `name` since V4) | | `template_versions` | `template_versions_pkey` | via PK | `(template_id, version)` fetch — the render path's lookup (surrogate `template_id` since V4) | | `template_versions` | `uq_template_versions_one_draft` | explicit (partial, unique) | The one-DRAFT-per-template rule of versioning §3.3/§6 | | `template_versions` | `idx_template_versions_dialect` | explicit | Filter template versions by dialect | | `datasources` | `datasources_pkey` | via PK | Lookup by name — the registry's hot path | | `datasources` | `idx_datasources_active` | explicit (partial) | List non-deleted datasources | | `workspaces` | `workspaces_pkey` | via PK | Lookup by id | | `workspaces` | `workspaces_name_key` | via UNIQUE | Workspace lookup by name (config/UX references) | | `workspace_members` | `workspace_members_pkey` | via PK | Membership check `(workspace_id, user_id)` | | `workspace_members` | `idx_workspace_members_admins` | explicit, partial (`WHERE admin`) | The last-admin count (§4.12) — enforced in the service, so the count runs on every membership change | | `workspace_invitations` | `workspace_invitations_pkey` | via PK | One invitation per (workspace, email); the per-workspace listing walks its prefix (§4.17) | | `workspace_invitations` | `idx_workspace_invitations_email` | explicit | The login-path materialise, which looks up by EMAIL — the one access path the PK prefix cannot serve (§4.17) | | `workspaces` | `idx_workspaces_active` | explicit, partial | Selectable workspaces (`is_deleted = FALSE AND deactivated_at IS NULL`) — every workspace selection reads it | | `datasource_workspaces` | `datasource_workspaces_pkey` | via PK | The visibility check `(datasource_name, workspace_id)` — the hot per-read predicate (§4.16) | | `datasource_workspaces` | `idx_datasource_workspaces_workspace` | explicit | "Everything this workspace can see", the listing's access path | | `published_endpoints` | `published_endpoints_pkey` | via PK | Lookup by id | | `published_endpoints` | `published_endpoints_path_pattern_key` | via UNIQUE | One meaning per URL, deployment-wide ([§4.13](#413-published_endpoints)) | | `published_endpoints` | `idx_published_endpoints_workspace` | explicit | A workspace's endpoints — the management listing | | `published_endpoints` | `idx_published_endpoints_pipeline` | explicit | "Does any endpoint publish this pipeline?" — what a pipeline delete and the read-only re-check ask | | `endpoint_key_bindings` | `endpoint_key_bindings_pkey` | via PK | `(path_prefix, api_key_id)` — one key binds a node once | | `endpoint_key_bindings` | `idx_endpoint_key_bindings_key` | explicit | Which nodes a key binds — the key detail view and a revoke's blast radius | | `dashboard_key_bindings` | `dashboard_key_bindings_pkey` | via PK | `(name_prefix, api_key_id)` — one key binds a folder once (§4.36) | | `dashboard_key_bindings` | `idx_dashboard_key_bindings_key` | explicit | Which folders a key binds — the Keys page's editor and a revoke's blast radius | | `lake_tables` | `lake_tables_pkey` | via PK | Lookup by surrogate id | | `lake_tables` | `uq_lake_tables_datasource_namespace_name` | via UNIQUE | One registration per (datasource, namespace, name); doubles as the list-by-datasource access path ([§4.15](#415-lake_tables)) | | `learned_facts` | `learned_facts_pkey` | via PK | Lookup by id (`semantics_retire`, `supersedes`) | | `learned_facts` | `idx_learned_facts_datasource` | explicit | Every read is per datasource: the introspection enrichment, the listing, the drift check ([§4.18](#418-learned_facts)) | | `learned_facts` | `idx_learned_facts_workspace` | explicit (partial) | A workspace's own WORKSPACE facts; partial because DATASOURCE facts carry no workspace ([§4.18](#418-learned_facts)) | | `learned_facts` | `idx_learned_facts_supersedes` | explicit (partial) | V36 (7e): a retired fact's successor — the row whose `supersedes` names it — for the `needs_review` detail; partial because almost no fact supersedes another ([§4.21](#421-template_implements)) | | `mail_sends` | `mail_sends_pkey` | via PK | The claim's id — what `markSent` / `markFailed` update | | `mail_sends` | `uq_mail_sends_message` | via UNIQUE | One claim per message identity `(user_id, kind, act_id)` — the "never twice" rule; doubles as the per-user read the admin screen makes ([§4.19](#419-mail_sends)) | | `pipeline_check_runs` | `pipeline_check_runs_pkey` | via PK | The run row's id | | `pipeline_check_runs` | `idx_pipeline_check_runs_latest` | explicit | The latest run per `(pipeline_id, version, check_id)` — the release gate's and the UI's only read ([§4.20](#420-pipeline_check_runs)) | | `template_implements` | `template_implements_pkey` | via PK | `(template_id, version, fact_id)` — one version's citations (the `needs_review` join on read) | | `template_implements` | `idx_template_implements_fact` | explicit | V36 (7e): the reverse arrow — every version citing one fact (`implemented_by`, the `implements=` filter) ([§4.21](#421-template_implements)) | | `schedules` | `schedules_pkey` | via PK | Lookup by id | | `schedules` | `uq_schedules_workspace_name_live` | explicit (partial, unique) | V38 (#9): a LIVE schedule's name is unique per workspace; a soft-deleted one frees it ([§4.22](#422-schedules)) | | `schedules` | `uq_schedules_create_idempotency` | explicit (partial, unique) | V38: one schedule per (workspace, creator, Idempotency-Key) — the durable create replay (L1) | | `schedules` | `idx_schedules_due` | explicit (partial) | V38: the dispatcher's due scan — only enabled, unblocked, live rows, so a paused fleet costs nothing | | `schedules` | `idx_schedules_target` | explicit (partial) | V38: "which schedules run this target?" (B15) without parsing a payload | | `schedule_runs` | `schedule_runs_pkey` | via PK | Lookup by id — the run task's data is the id | | `schedule_runs` | `uq_schedule_runs_occurrence` | via UNIQUE | V38: one row per (schedule, occurrence) — the dispatcher's `ON CONFLICT` arbiter ([§4.23](#423-schedule_runs)) | | `schedule_runs` | `uq_schedule_runs_manual_idempotency` | explicit (partial, unique) | V38: one Run now per (schedule, requester, Idempotency-Key) (L1) | | `schedule_runs` | `uq_schedule_runs_one_active` | explicit (partial, unique) | V38: at most one queued/starting/running run per schedule — the durable overlap guard | | `schedule_runs` | `idx_schedule_runs_schedule` | explicit | V38: a schedule's history, newest first | | `schedule_runs` | `idx_schedule_runs_open` | explicit (partial) | V38: the reconciler's worklist (starting, running, unknown) | | `schedule_runs` | `idx_schedule_runs_execution` | explicit (partial) | V38: the run of an execution — the Messages join | | `schedule_run_events` | `schedule_run_events_pkey` | via PK | `(run_id, seq)` — a run's trail in order, its only access path | | `scheduled_tasks` | `scheduled_tasks_pkey` | via PK | db-scheduler's `(task_name, task_instance)` ([§4.25](#425-scheduled_tasks)) | | `scheduled_tasks` | `execution_time_idx` | explicit | db-scheduler's own: due tasks by time (its DDL, verbatim) | | `scheduled_tasks` | `last_heartbeat_idx` | explicit | db-scheduler's own: dead-execution detection | | `scheduled_tasks` | `priority_execution_time_idx` | explicit | db-scheduler's own: priority polling (priority is off; the index is the library's) | | `parameter_sets` | `parameter_sets_pkey` | via PK | Lookup by id — every route and tool addresses a set by it (record P24) | | `parameter_sets` | `uq_parameter_sets_workspace_name` | via UNIQUE | V39 (#194): one name per workspace forever; leads the tree browse's bounded range scan ([§4.26](#426-parameter_sets)) | | `parameter_set_versions` | `parameter_set_versions_pkey` | via PK | `(parameter_set_id, version)` — every version read | | `parameter_set_versions` | `uq_parameter_set_versions_one_draft` | explicit (partial, unique) | V39: the one-DRAFT-per-set rule of versioning §3.3 ([§4.27](#427-parameter_set_versions)) | | `visualizations` | `visualizations_pkey` | via PK | Lookup by id — every route and tool addresses a visualization by it (spec §4) | | `visualizations` | `uq_visualizations_workspace_name` | via UNIQUE | V42 (#10): one name per workspace forever; leads the tree browse's bounded range scan ([§4.28](#428-visualizations)) | | `visualization_versions` | `visualization_versions_pkey` | via PK | `(visualization_id, version)` — every version read | | `visualization_versions` | `uq_visualization_versions_one_draft` | explicit (partial, unique) | V42: the one-DRAFT rule of versioning §3.3 ([§4.29](#429-visualization_versions)) | | `dashboards` | `dashboards_pkey` | via PK | Lookup by id | | `dashboards` | `uq_dashboards_workspace_name` | via UNIQUE | V42: one name per workspace forever; the tree browse ([§4.30](#430-dashboards)) | | `dashboard_versions` | `dashboard_versions_pkey` | via PK | `(dashboard_id, version)` — every version read | | `dashboard_versions` | `uq_dashboard_versions_one_draft` | explicit (partial, unique) | V42: the one-DRAFT rule ([§4.31](#431-dashboard_versions)) | | `dashboard_versions` | `idx_dashboard_versions_pins` | explicit (GIN, expression) | V46 (#331): the pins path the containment probe reads — `body_json -> 'visualizations' @> :probe` — as a bitmap index scan (the visualization purge/discard/restore guards and `visualizations_get`'s `used_by`); the batched page answer reads the pins whole and does not use it | | `visualization_test_runs` | `visualization_test_runs_pkey` | via PK | Lookup by run id | | `visualization_test_runs` | `uq_visualization_test_runs_session` | via UNIQUE | V42: one run per (visualization, version, session); its leading `(visualization_id, version)` is the per-version run lookup and the cascade's index ([§4.32](#432-visualization_test_runs)) | | `visualization_test_screenshots` | `visualization_test_screenshots_pkey` | via PK | One image per run, fetched by the run ([§4.33](#433-visualization_test_screenshots)) | | `dashboard_refreshes` | `dashboard_refreshes_pkey` | via PK | Lookup by the client-minted refresh id — the abort, the read and the stream's own row ([§4.34](#434-dashboard_refreshes)) | | `dashboard_refreshes` | `idx_dashboard_refreshes_dashboard_started` | explicit | V43: a dashboard's refreshes, newest first — the list route and `dashboards_get`'s `last_refresh` | | `dashboard_refreshes` | `idx_dashboard_refreshes_running` | explicit (partial) | V43: the stale-refresh sweeper's worklist (`RUNNING` only, so the scan stays tiny) | | `dashboard_refreshes` | `idx_dashboard_refreshes_finished` | explicit (partial) | V43: the retention step's cutoff scan | | `dashboard_refresh_executions` | `pk_dashboard_refresh_executions` | explicit (PK) | V43: one link per (refresh, source) ([§4.35](#435-dashboard_refresh_executions)) | | `dashboard_refresh_executions` | `idx_dashboard_refresh_executions_execution` | explicit | V43: the refresh of an execution — the events pane's reverse lookup | | `parameter_evaluations` | `parameter_evaluations_pkey` | via PK | Lookup by the evaluation id — the record detail and every guarded write ([§4.37](#437-parameter_evaluations)) | | `parameter_evaluations` | `idx_parameter_evaluations_set_started` | explicit | V48: one set's records, newest first — the History tab | | `parameter_evaluations` | `idx_parameter_evaluations_running` | explicit (partial) | V48: the stale sweep's worklist (`RUNNING` only, so the scan stays tiny) | | `parameter_evaluations` | `idx_parameter_evaluations_finished` | explicit (partial) | V48: the retention step's cutoff scan on `started_at` over finished rows | | `parameter_evaluation_queries` | `parameter_evaluation_queries_pkey` | via PK | Each attempt's guarded terminal write ([§4.38](#438-parameter_evaluation_queries)) | | `parameter_evaluation_queries` | `idx_parameter_evaluation_queries_evaluation` | explicit | V48: a record's attempts (the detail) and the cascade from a deleted record (retention) | | `schedule_notification_events` | `schedule_notification_events_pkey` | via PK | Lookup by the notice id ([§4.39](#439-schedule_notification_events)) | | `schedule_notification_events` | `uq_schedule_notification_events_run_outcome` | via UNIQUE | V51 (#442b): a run reaches each outcome once — the outbox's `ON CONFLICT` arbiter, and a run's notices | | `schedule_notification_events` | `idx_schedule_notification_events_schedule` | explicit | V51: a schedule's notices (the FK to `schedules`) | | `schedule_notification_deliveries` | `schedule_notification_deliveries_pkey` | via PK | The sender's settle and the resend, by delivery id ([§4.40](#440-schedule_notification_deliveries)) | | `schedule_notification_deliveries` | `uq_schedule_notification_deliveries_event_recipient` | via UNIQUE | V51: one message per recipient per notice | | `schedule_notification_deliveries` | `uq_schedule_notification_deliveries_message_id` | via UNIQUE | V51: a Message-ID is never reused across deliveries | | `schedule_notification_deliveries` | `idx_schedule_notification_deliveries_due` | explicit | V51: the sender's claim — pending rows by `next_attempt_at` — and the backlog count | | `schedule_notification_deliveries` | `idx_schedule_notification_deliveries_event` | explicit | V51: a notice's deliveries — the run detail | **Deliberately absent:** - `uq_users_email` — this name never existed. The uniqueness rule is a `UNIQUE` *constraint* declared inline in §4, so Postgres names its index `users_email_key`. The old entry would have sent a migration author looking for a `CREATE UNIQUE INDEX` statement that was not there. (`uq_pipelines_name`/`pipelines_name_key` are gone too — V4 replaced the global rule with the explicitly named `uq_pipelines_workspace_name`.) - `idx_events_execution` — dropped as an exact duplicate of the `uq_events_execution_event` constraint index on the schema's highest-volume table. - Indexes on FK columns that are only ever joined *from* the parent (`pipeline_versions.created_by`, `template_versions.created_by`, `templates.created_by`, `datasources.created_by`). v1 has no "list everything user X created" screen. Add them when a query needs one — an unused index on a write-heavy table is a cost with no return. - Indexes on the V4 `workspace_id` FK columns (`pipelines`, `templates`, `api_keys`, `datasources`) and on `workspace_members.user_id`. Slice-1 read paths pin one constant workspace, so these indexes would filter nothing; slice 2's real workspace resolution adds them with the queries that need them. --- ## 5A. Promotion Classification One row per table in §4 — the registry `FlywayMigrationIntegrationTest` parses and compares against the live schema **in both directions** (every live table is classified; every classified table exists). A new table fails that test until it is BOTH expected in the schema assertions AND classified here, so the classification cannot rot behind the schema and the schema cannot grow past the classification. When the guard fires, update THIS table and the test's expected-table list in the same commit. - **promotable** — authored content that promotion transfers between environments at an exact version number (identity rule D5: numbers are global identities; imports never renumber). - **environment-local** — state that belongs to exactly one deployment and is never transferred; promotion references these only by name. - **derived** — data this deployment's own activity produced (executions, events, audit); per-environment by construction, never authored, never promoted. | table | verdict | resource | version series | export key | why | |---|---|---|---|---|---| | `pipelines` | promotable | Pipeline | `current_version` (latest RELEASED; versioning §3.4) | `id` (UUID, portable) | The index row over the current released body; metadata rides the release (versioning §3.5) | | `pipeline_versions` | promotable | Pipeline | per-pipeline `version` — global identity, preserved on import (D5) | `(pipeline_id, version)` | The artifacts themselves; promotion pushes the latest RELEASED body (versioning D6/§9.2) | | `templates` | promotable | Template | `current_version` | `name` | The human id is the cross-env identity pipeline pins reference by number | | `template_versions` | promotable | Template | per-template `version` — same preservation rule | `(name, version)` | Content of the artifact rows promotion transfers (versioning §6/§9.2) | | `datasources` | environment-local | Datasource | — | `name` | Connection secrets never leave the environment; the NAME is the cross-env contract (portability §11.1) | | `users` | environment-local | User | — | `email` | Identities are per-deployment; imported rows' `created_by` names the importing actor (versioning §10.6's service principal, when promotion ships) | | `api_keys` | environment-local | ApiKey | — | — | Credentials are per-deployment by definition | | `workspaces` | environment-local | Workspace | — | `name` | Isolation topology is per-deployment | | `workspace_members` | environment-local | Membership | — | — | Follows `users` and `workspaces`, both local. It is also where CAPABILITY lives since V23 (§4.12), which makes it doubly local: a role granted in one environment must not travel to another — that is the whole point of having a promoter who can release on staging and not in production | | `datasource_workspaces` | environment-local | DatasourceGrant | — | — | A grant joins two environment-local rows — a [`datasources`](#410-datasources) row whose credential never leaves the deployment, and a [`workspaces`](#411-workspaces) row whose isolation topology is per-deployment. The receiving environment's super admin grants there, as part of the same setup that registers the datasource (RBAC design D-R7, O-4) | | `workspace_invitations` | environment-local | Membership | — | — | A membership waiting for its user (§4.17): it joins a [`workspaces`](#411-workspaces) row and a not-yet-existing person, and materialises into [`workspace_members`](#412-workspace_members) at the first login on THIS deployment. An invitation is an admin's decision about this environment's staffing, never content | | `pipeline_executions` | derived | Execution | — | `execution_id` | Produced by running; each environment's history is its own (versioning §9.3: "its own history references its own numbers") | | `execution_events` | derived | Event | — | `(execution_id, event_id)` | The durable SSE trail of local executions | | `audit_log` | derived | AuditEvent | — | — | Records local activity; not authored, not transferable | | `published_endpoints` | promotable | PublishedEndpoint | — (follows the pipeline it publishes) | `path_pattern` | The URL contract is authored, and an endpoint that exists in dev and not in prod is the whole point of promoting it. The row references its pipeline by NAME in the batch, like everything promoted; `workspace_id`, `created_by` and the timestamps are resolved locally on the target | | `endpoint_key_bindings` | promotable | EndpointKeyBinding | — | `(path_prefix, api key name)` | Which node a key authorises is authored topology, not local state, so it travels. It is carried by key NAME because [`api_keys`](#42-api_keys) itself is environment-local — a target missing that key name refuses the batch with `endpoint.promotion.key_missing` before anything is pushed, rather than importing a binding to nothing | | `dashboard_key_bindings` | promotable | DashboardKeyBinding | — | — | The `endpoint_key_bindings` twin's OWN class, by the same reasoning (L5 #367's lane decision, recorded in the handback): a folder binding is authored topology. TODAY it has no promotion carrier (no batch row, no entity registered with the transfer) — the class states what it WOULD travel as; when a batch carries it, it travels by key NAME exactly like its twin | | `learned_facts` | environment-local | LearnedFact | — | — | A fact is about an environment-local [`datasources`](#410-datasources) row and was validated against THAT database's live schema (§4.18); the target environment's data may have a different shape, and a fact that has not been checked against it is exactly what the store refuses to start with. Round 2 may export facts as OSI; nothing promotes them | | `mail_sends` | derived | MailSend | — | — | The claim rows behind the notices THIS deployment sent about ITS users (§4.19) — a record of local sends, not authored, and meaningless beside another environment's `users` | | `pipeline_check_runs` | derived | CheckRun | — | `(pipeline, version, check_id)` | Produced by THIS deployment's server running the checks a pipeline body declares (§4.20): the observed value is only truthful against this environment's datasource data, which is the whole point of a check. The runs of a promoted pipeline are re-produced by the target's own runs, never transferred | | `lake_tables` | environment-local | LakeTable | — | — | Rows point at an environment-local [`datasources`](#410-datasources) row and at bucket locations whose credentials never leave the deployment; the registry is rebuilt on the target from its own manifest import, exactly as the datasource itself is re-registered there | | `schedules` | environment-local | Schedule | — | — | Schedules are NEVER promoted (the owner's decision, scheduler design revision §1.1): operational configuration of THIS deployment, created directly, with no artifact lifecycle | | `schedule_runs` | derived | ScheduleRun | — | — | The runs THIS deployment's scheduler recorded; their executions are this environment's own | | `schedule_run_events` | derived | ScheduleRunEvent | — | — | Each run's local trail — produced, never authored | | `scheduled_tasks` | derived | ScheduledTask | — | — | db-scheduler's queue of this deployment's pending tasks; library-owned, never transferred | | `parameter_sets` | promotable | ParameterSet | `current_version` (sticky; versioning §3.4) | `id` (UUID, portable — an import keeps it, record P24) | The index over the current body; promotion is by name and carries the id (record §8.3) | | `parameter_set_versions` | promotable | ParameterSet | per-set `version` — global identity, preserved on import (D5) | `(id, version)` | The artifacts; an import validates against the TARGET's templates and datasources and lands RELEASED (record §8.3) | | `visualizations` | promotable | Visualization | `current_version` (sticky; versioning §3.4) | `id` (UUID, portable — an import keeps it; C29 refuses a taken one) | The index over the current body; promotion order templates → parameter sets → pipelines → visualizations → dashboards (D61) | | `visualization_versions` | promotable | Visualization | per-visualization `version` — global identity, preserved on import (D5) | `(id, version)` | The artifacts; an import validates against the TARGET's templates (spec §12) and lands RELEASED | | `dashboards` | promotable | Dashboard | `current_version` (sticky) | `id` (UUID, portable) | The index over the current body; promoted last (D61) | | `dashboard_versions` | promotable | Dashboard | per-dashboard `version` — global identity, preserved on import (D5) | `(id, version)` | The artifacts; an export carries its pinned visualizations and REFERENCES its pipelines and set (spec §12) | | `visualization_test_runs` | environment-local | Visualization | — | — | Test evidence qualifies a release in the environment that ran it; the released version travels, its evidence does not | | `visualization_test_screenshots` | environment-local | Visualization | — | — | A run's image — the run's classification | | `dashboard_refreshes` | derived | DashboardRefresh | — | — | What THIS deployment's viewers refreshed; per-environment by construction, never authored, never promoted | | `dashboard_refresh_executions` | derived | DashboardRefresh | — | — | The refresh's link to its executions — the refresh's classification | | `parameter_evaluations` | derived | ParameterEvaluation | — | — | What THIS deployment's callers evaluated (#376); per-environment diagnostics by construction, never authored, never promoted | | `parameter_evaluation_queries` | derived | ParameterEvaluation | — | — | The evaluation's statement attempts — the record's classification | | `schedule_notification_events` | derived | ScheduleNotification | — | — | Notices THIS deployment's scheduler queued for its own runs (#442b); produced, never authored | | `schedule_notification_deliveries` | derived | ScheduleNotification | — | — | The per-recipient sends of those notices — the event's classification; addresses never leave the environment | | `template_implements` | promotable | Template | — (follows `template_versions`) | `(name, version)` + the fact ids | The citations ride the template version's payload (export, import, the promotion batch) outside its `body_hash` (R9). Their targets are [`learned_facts`](#418-learned_facts) rows, which are environment-local: the importing workspace stores the ids that resolve there and drops the rest without refusing (owner ruling 2026-09-25), so a cross-deployment promotion lands with none ([§4.21](#421-template_implements)) | --- ## 6. Data Access Pattern (NamedParameterJdbcTemplate) All metadata DB access uses Spring's `NamedParameterJdbcTemplate` with `RowMapper` classes. No JPA, no Hibernate, no repository magic. ### 6.1 Example: Pipeline repository ```kotlin @Repository class PipelineRepository( private val jdbc: NamedParameterJdbcTemplate ) { fun findById(id: UUID): Pipeline? { val sql = """ SELECT id, name, display_name, description, owner_id, current_version, is_deleted, created_at, updated_at FROM pipelines WHERE id = :id AND is_deleted = FALSE """.trimIndent() return jdbc.query(sql, mapOf("id" to id), PipelineRowMapper).singleOrNull() } fun findVersionBody(pipelineId: UUID, version: Int): String? { val sql = """ SELECT body_json::TEXT FROM pipeline_versions WHERE pipeline_id = :pipelineId AND version = :version """.trimIndent() return jdbc.query(sql, mapOf( "pipelineId" to pipelineId, "version" to version )) { rs, _ -> rs.getString("body_json") }.singleOrNull() } fun create(pipeline: Pipeline, bodyJson: String, createdBy: UUID): Pipeline { val sql = """ WITH new_pipeline AS ( INSERT INTO pipelines (id, name, display_name, description, owner_id, workspace_id, current_version) VALUES (:id, :name, :displayName, :description, :ownerId, :workspaceId, 1) RETURNING id, name, display_name, description, owner_id, current_version, is_deleted, created_at, updated_at ), new_version AS ( INSERT INTO pipeline_versions (pipeline_id, version, body_json, created_by) SELECT id, 1, CAST(:bodyJson AS jsonb), :createdBy FROM new_pipeline RETURNING pipeline_id ) SELECT p.id, p.name, p.display_name, p.description, p.owner_id, p.current_version, p.is_deleted, p.created_at, p.updated_at FROM new_pipeline p JOIN new_version v ON v.pipeline_id = p.id """.trimIndent() return jdbc.queryForObject( sql, mapOf( "id" to pipeline.id, "name" to pipeline.name, "displayName" to pipeline.displayName, "description" to pipeline.description, "ownerId" to pipeline.ownerId, "workspaceId" to pipeline.workspaceId, "bodyJson" to bodyJson, "createdBy" to createdBy ), PipelineRowMapper )!! } private object PipelineRowMapper : RowMapper { override fun mapRow(rs: ResultSet, rowNum: Int) = Pipeline( id = rs.getObject("id", UUID::class.java), name = rs.getString("name"), displayName = rs.getString("display_name"), description = rs.getString("description"), ownerId = rs.getObject("owner_id", UUID::class.java), currentVersion = rs.getInt("current_version"), isDeleted = rs.getBoolean("is_deleted"), createdAt = rs.getObject("created_at", OffsetDateTime::class.java).toInstant(), updatedAt = rs.getObject("updated_at", OffsetDateTime::class.java).toInstant() ) } } ``` **Why this shape.** The previous version of this example did not work and would not have compiled: - It chained two `INSERT`s so that the second read `new_pipeline`, but returned from the *second* one (`RETURNING pipeline_id, version`) while the mapper tried to build a `Pipeline` — the CTE returned the wrong row shape entirely. - Its `RowMapper` lambda had an empty body (`// version created`), so `queryForObject` was typed `Unit`. - It then discarded the query result and returned `pipeline.copy(currentVersion = 1)` — a hand-built object asserting what the database *should* have stored, with `created_at`/`updated_at` never read back. Any default, trigger, or CHECK the DB applied would have been invisible to the caller. The single-statement CTE above fixes all three: both inserts happen in one statement (so they are atomic even without an enclosing transaction — though the service layer's `@Transactional` still applies), and the final `SELECT` returns exactly the column list `PipelineRowMapper` consumes, with the server-generated timestamps included. The `JOIN` on `new_version` is what forces the version insert to be part of the same plan. **The two-statement alternative is equally valid** and is preferable when the create path needs to do more between the two writes (e.g. emit an audit event using the new id): ```kotlin @Transactional("metadataTransactionManager") fun create(pipeline: Pipeline, bodyJson: String, createdBy: UUID): Pipeline { val created = jdbc.queryForObject(INSERT_PIPELINE_SQL, params, PipelineRowMapper)!! jdbc.update(INSERT_VERSION_SQL, mapOf( "pipelineId" to created.id, "bodyJson" to bodyJson, "createdBy" to createdBy )) return created } ``` What is **not** acceptable is either statement running outside a transaction: a `pipelines` row with `current_version = 1` and no matching `pipeline_versions` row is a pipeline that cannot be executed or read, and nothing in the schema forbids it (the FK points the other way). **Timestamp reads.** Use `rs.getObject(col, OffsetDateTime::class.java)`, not `rs.getTimestamp(col).toInstant()`. `getTimestamp` without a `Calendar` interprets the value in the JVM default zone; `TIMESTAMPTZ` → `OffsetDateTime` is exact regardless of it. (The JVM is required to run in UTC — [Type System §8.4](/docs/type-system.md#84-timestamp-timezone-normalization) — but code that is correct only because of a deployment flag is code waiting to break.) ### 6.2 RowMapper convention - One `RowMapper` object per table, defined as a private object on the repository (as `PipelineRowMapper` above) or a top-level object — never an inline lambda duplicated across query methods. Every query that returns that entity uses the same mapper, so its column list is the one thing each `SELECT` must satisfy. - Mappers handle `NULL` explicitly. `rs.getObject()` returns null for SQL NULL on object types; `rs.getInt()`/`getLong()`/`getBoolean()` return `0`/`false` for NULL — use `rs.wasNull()` immediately after, or read the boxed type. This matters for the genuinely nullable numerics in §4: `duration_ms`, `result_row_count`, `result_size_bytes`, `datasources.query_timeout_seconds`. - Timestamps read as `rs.getObject(col, OffsetDateTime::class.java)` (see §6.1) — never `getTimestamp` without a `Calendar`. - **JSONB columns** (every column with the `_json` suffix, §2): bind as `String` and cast in SQL with `CAST(:param AS jsonb)`; read with `rs.getString("col")` and deserialize via Jackson. Do not bind a Jackson `JsonNode` or a `PGobject` directly — the string+cast form is driver-independent and keeps the serialization in one place. - Nullable JSONB (`error_json`, `node_stats_json`) reads as `null` from `getString`, not as the string `"null"` — check before deserializing. ### 6.3 Transaction management - `@Transactional` on service-layer methods only (per CLAUDE.md rules). - Named transaction manager: `@Transactional("metadataTransactionManager")` if multiple datasources are configured. - Read-only queries marked `@Transactional(readOnly = true)` for hint to Postgres. --- ## 7. Flyway Migration ### 7.1 File structure ``` modules/app/src/main/resources/db/migration/ ├── V1__initial_schema.sql ← all 10 tables + indexes (this spec) ├── V2__datasource_introspection_include_schemas.sql ├── V3__execution_lineage.sql ├── V4__workspaces_rekey.sql ← workspaces + workspace_members; workspace_id on pipelines/templates/api_keys/datasources; templates surrogate re-key ├── V5__local_password_auth.sql ← users gains password_hash / password_changed_at / must_change_password / failed_login_count / locked_until (auth.md §5A) ├── V6__version_lifecycle.sql ← pipeline_versions/template_versions gain status/body_hash/released_*/updated_* + the one-draft partial indexes (versioning.md §11) └── V7__... ← future migrations ``` ### 7.2 V1 migration The V1 migration is generated directly from §4 of this spec. It creates all 10 tables, their constraints, and every **explicit** index from §5 in one transaction. Flyway applies it on app startup. Three things the generator must respect: - **Only explicit indexes get a `CREATE INDEX`.** Constraint-backed indexes (§5, "via PK"/"via UNIQUE") are created by Postgres from the constraint. Emitting a `CREATE INDEX` for one of those is a duplicate index, not a safety net. - **Table order follows the FK graph:** `users` → `api_keys`/`audit_log` → `pipelines` → `pipeline_versions` → `pipeline_executions` → `execution_events`, and `templates` → `template_versions`; `datasources` depends only on `users`. `pipeline_executions` cannot be created before `pipeline_versions` — its composite FK targets that table's primary key. - **No triggers are emitted.** `updated_at` is application-maintained (§2). A generator that "helpfully" adds a `BEFORE UPDATE` trigger contradicts this spec. The §8 jobs are **not** migrations — they are scheduled statements against the live schema and never appear in a versioned migration file. ### 7.3 Migration safety in multi-instance deployment Flyway uses Postgres advisory locks (`pg_advisory_lock`). Multiple instances starting simultaneously: 1. Instance A acquires the lock, runs the migration, releases. 2. Instance B waits for the lock, finds the migration already applied, skips. 3. No corruption, no double-execution. --- ## 8. Operational Jobs Four scheduled jobs act on this schema (the hourly retention tick carries several steps — §8.1 and [§8.5](#85-stale-parameter-evaluation-sweep)). **None of them hard-codes an interval literal.** Every retention and timeout bound is a bind parameter fed from a config key owned by [Configuration §3](/docs/configuration.md#3-optional-configuration-with-defaults) (D8) — the SQL below shows the parameterized form, because an `INTERVAL '7 days'` written into a query is a config key that silently stopped working. The `make_interval()` form is used rather than string concatenation: it takes an integer bind parameter, so there is no interval literal to build and nothing to inject. ### 8.1 Execution event cleanup ```sql DELETE FROM execution_events WHERE execution_id IN ( SELECT execution_id FROM pipeline_executions WHERE completed_at IS NOT NULL AND completed_at < NOW() - make_interval(days => :eventRetentionDays) ); ``` `:eventRetentionDays` ← [`datapipelines.executions.event-retention-days`](/docs/configuration.md#311-execution-history). **Dashboard refreshes ride the same tick (#10 L2).** After the events, the step deletes `dashboard_refreshes` rows that FINISHED before the same cutoff — `DELETE FROM dashboard_refreshes WHERE finished_at IS NOT NULL AND finished_at < NOW() - make_interval(days => :eventRetentionDays)` — and the cascade takes their `dashboard_refresh_executions` links (never the executions, which are never deleted). Before this rule the spec said "the executions' own policy"; `pipeline_executions` has none, so refreshes are retained like the events that describe them (§18 premise 7). A `RUNNING` row is never deleted by retention. The DELETE is bounded at 25 s like the events' DELETE (#636, below). **Parameter evaluation records ride the same tick (#376; the workspace spec §2.3, the owner's §11.5 ruling).** After the dashboard refreshes, the step deletes finished `parameter_evaluations` rows that STARTED before the same cutoff, ONE bounded batch per tick — `DELETE FROM parameter_evaluations WHERE id IN (SELECT id FROM parameter_evaluations WHERE finished_at IS NOT NULL AND started_at < NOW() - make_interval(days => :eventRetentionDays) ORDER BY started_at LIMIT :batchSize)` over `idx_parameter_evaluations_finished`, `:batchSize` = 5,000 (a code constant, `ParameterEvaluationRetention`); the cascade takes their `parameter_evaluation_queries`. A full batch is a backlog, said once at WARN (`parameter.evaluation_retention_incomplete`); a purge that deleted rows logs `parameter.evaluations_purged`, a failed DELETE `parameter.evaluation_retention_failed` ([Observability §3.4M](/docs/observability.md#34m-the-parameter-evaluation-events-376)), and the next hour takes the next batch. No key of its own: a record is a bounded row with no payload, so the event retention is the policy (a retention key of its own would be an additive change). A `RUNNING` row is never deleted. The `execution_events` DELETE is bounded (#326): a JDBC `queryTimeout` of `RETENTION_STATEMENT_TIMEOUT_SECONDS` (25 s, a code constant beside the hourly cadence, not a key), so a DELETE queued behind a lock is cancelled with SQLState `57014` — one statement, so it deletes nothing — and the next tick retries. Since #636 the dashboard refreshes' DELETE and the parameter evaluations' batch DELETE above (and §8.5's sweep UPDATE, a step of the same tick) carry the same 25 s bound, each a single statement that a cancel rolls back whole; the repositories are built for the tick over a template of their own, so the runtime's writes to the same tables stay unbounded. This purges the **durable** 7-day record only. The 1-hour Redis event log ([§9](#9-what-is-not-in-this-database)) expires on its own TTL and is not this job's concern. ### 8.2 Audit log retention ```sql -- once per tick: the cutoff, from the database's clock SELECT NOW() - make_interval(days => :auditRetentionDays); -- then, until a batch comes back short (or the tick's bound is reached): DELETE FROM audit_log WHERE id IN ( SELECT id FROM audit_log WHERE "timestamp" < :cutoff ORDER BY "timestamp" LIMIT :batchSize ); ``` `:auditRetentionDays` ← [`datapipelines.audit.retention-days`](/docs/configuration.md#312-audit) (30–3650, refused at startup outside them). Implemented by `AuditLogRetention` in `auth` (#310), the retention sweep's last step, hourly. The rows it deletes are exactly the ones the one-statement form `DELETE FROM audit_log WHERE timestamp < NOW() - make_interval(days => :auditRetentionDays)` would — one cutoff, every event — but in bounded batches, so a year's backlog never holds a lock or a transaction for minutes: each `DELETE` is its own auto-committed statement of at most `:batchSize` (5,000) rows, chosen oldest-first through `idx_audit_timestamp`, and a tick runs at most 50 of them or 2 seconds. The cutoff is read ONCE per tick and bound into every batch — `timestamp` is the database's `NOW()` at write, so the database's clock decides, never an instance's; a row inserted during the tick is younger than the cutoff by construction. Rows are only ever INSERTed, so a batch contends with nothing but another replica's batch, which then deletes zero. The bounds are code constants, not keys ([Auth §10.3](/docs/auth.md#103-retention) states what the job never deletes and what the audit read-backs lose). ### 8.3 Stale execution sweep If an instance crashes mid-execution, its execution record would stay `RUNNING` forever — no instance is left to write the terminal state. A periodic sweep marks over-age `RUNNING` executions as `ABORTED` (one of the four `ABORTED` production paths in [Enums §10](/docs/enums.md#10-executionstatus--whole-pipeline-execution-outcome)): ```sql UPDATE pipeline_executions SET status = 'ABORTED', completed_at = NOW(), duration_ms = EXTRACT(EPOCH FROM (NOW() - started_at)) * 1000, error_json = CAST(:instanceLostError AS jsonb) WHERE status = 'RUNNING' AND (heartbeat_at < :heartbeatCutoff OR (heartbeat_at IS NULL AND started_at < NOW() - make_interval(mins => :staleTimeoutMinutes))); ``` `:staleTimeoutMinutes` ← [`datapipelines.executions.stale-timeout-minutes`](/docs/configuration.md#311-execution-history). `:instanceLostError` is the standard error envelope with code `pipeline.execution.instance_lost`. The UPDATE is bounded (#326): a JDBC `queryTimeout` of `SWEEP_STATEMENT_TIMEOUT_SECONDS` (10 s, a code constant beside the 15 s cadence, not a key), so an UPDATE queued behind a lock is cancelled with SQLState `57014`, marks nothing, and the next tick retries. #### The heartbeat (108 §D, V21) `heartbeat_at` is stamped by the instance that OWNS the row, every [`datapipelines.executor.heartbeat-seconds`](/docs/configuration.md#32-executor) (15) while the execution runs, and again by every live-progress write. `:heartbeatCutoff` is `NOW() - 3 × heartbeat-seconds` — one missed beat is a slow tick, two a loaded box, three an instance that is gone. With the sweep ticking every 15 s, a crashed instance's rows are `ABORTED` in **under a minute**. Before V21 the age condition was the only one, so those rows stayed `RUNNING` for `stale-timeout-minutes` — sixty of them. That is not a tidiness problem: an agent in T199 waited the full hour before learning its run had died, because nothing in the system said otherwise. **The two conditions are OR'd and neither replaces the other.** The age condition survives, restricted to rows with NO stamp. A pre-V21 instance never writes one, and reaping its live executions after 45 seconds on the strength of a column it does not know about would abort healthy runs during a rolling upgrade. So: stamped rows are judged by the stamp, unstamped rows by their age, and the sweep is safe across the version boundary in both directions. #### Live progress on the same row (108 §D) `node_stats_json` is also written **while** the execution runs — the same column, the same shape as the terminal write, so every reader that renders node stats renders progress for free. It is updated at each node boundary (unthrottled: that is the event a watcher is waiting for) and from the staging drain at most once per [`datapipelines.executor.progress-write-interval-seconds`](/docs/configuration.md#32-executor). A node that has started and not finished appears with `status: "RUNNING"` ([Enums §9](/docs/enums.md#9-nodestatus--per-node-execution-outcome)) and its rows staged so far; a node that has not started is **absent** rather than given a status. Both writes carry `AND status = 'RUNNING'`, and that guard is the whole safety of the feature: a throttled write can be in flight when the execution finishes, and without it a late progress write would overwrite the FINAL stats with a snapshot that still says a node is running — a corrupted terminal record produced by an observability feature. Two things this job must get right: - **The timeout must exceed the longest legitimate execution.** `stale-timeout-minutes` is not independent of `datapipelines.executor.execution-timeout-seconds` — if it is set below it, the sweep aborts executions that are still running normally, and the row says `ABORTED` while the work continues on the instance. Keep it comfortably above the execution timeout. - **It is a crash sweep, not a cancellation path.** Live cancellation (client disconnect beyond grace, explicit `DELETE /api/v1/executions/{id}`, shutdown drain) travels through the Redis cancel flag (D7) and is handled by the executing instance. This job only cleans up after instances that are gone. `idx_executions_status_running` (on `started_at`) and `idx_executions_heartbeat` (on `heartbeat_at`, V21) are the partial indexes this `WHERE` clause rides — both restricted to `status = 'RUNNING'`, which is why a tick every 15 seconds stays cheap on a large table. Scheduling for all three (Spring `@Scheduled`, or external cron in multi-instance deployments where a single runner is preferred) is an implementation choice, not a schema concern. Each statement is idempotent and safe to run concurrently from more than one instance. ### 8.4 Stale dashboard refresh sweep An instance that crashes mid-refresh leaves its `dashboard_refreshes` row `RUNNING` forever — the coroutine that would write the terminal state died with it (the [§8.3](#83-stale-execution-sweep) shape, one level up). `DashboardRefreshSweeper` (#10 L2; on the `dp-scheduled` thread like `StaleExecutionSweeper`, registered in `ArchitectureGuardTest.APPROVED_SCHEDULING_FILES`) closes them: ```sql UPDATE dashboard_refreshes SET status = 'TIMED_OUT', finished_at = NOW(), summary_json = summary_json || jsonb_build_object('reason', CASE WHEN EXISTS ( SELECT 1 FROM dashboard_refresh_executions l JOIN pipeline_executions e ON e.execution_id = l.execution_id WHERE l.refresh_id = dashboard_refreshes.id AND e.status = 'ABORTED' AND e.error_json ->> 'code' = 'pipeline.execution.instance_lost') THEN 'instance_lost' ELSE 'deadline_passed' END) WHERE status = 'RUNNING' AND started_at < NOW() - make_interval(secs => :staleAfterSeconds); ``` `:staleAfterSeconds` = [`datapipelines.dashboards.timeouts.max-refresh-seconds`](/docs/configuration.md#334-the-dashboard-runtime-10-l2) plus one [`cancel-poll-interval-seconds`](/docs/configuration.md#32-executor) — no live refresh outlives its own deadline (§9.6 caps it), so a row past both is not running anywhere. The `AND status = 'RUNNING'` guard is the whole safety: a refresh that finishes as the sweep runs keeps its own terminal state. Every replica may run it (one idempotent `UPDATE`, like §8.1); a tick that fails is logged (`event=dashboard.refresh_sweep_failed`, the class and SQLState only) and retried. The `UPDATE` is bounded (#636): a JDBC `queryTimeout` of `REFRESH_SWEEP_STATEMENT_TIMEOUT_SECONDS` (10 s — the stale-execution sweep's value, a sixth of the 60 s cadence; a code constant, not a key) on the sweeper's own template, so an `UPDATE` queued behind a lock is cancelled with SQLState `57014` — one statement, so it closes nothing — and the next tick retries; the runtime's own writes to `dashboard_refreshes` carry no bound. ### 8.5 Stale parameter evaluation sweep An instance that dies mid-evaluation — or a terminal write the database refused — leaves a `parameter_evaluations` row `RUNNING` forever (§8.4's shape). `ParameterEvaluationSweeper` (#376) closes them as a step of the HOURLY retention tick ([§8.1](#81-execution-event-cleanup); no `@Scheduled` of its own), before that tick's evaluation retention, so a record it closes ages out with the rest: ```sql UPDATE parameter_evaluations SET status = 'INCOMPLETE', finished_at = NOW() WHERE status = 'RUNNING' AND started_at < NOW() - make_interval(secs => :staleAfterSeconds) RETURNING id; ``` `:staleAfterSeconds` = [`datapipelines.parameters.evaluate-timeout-seconds`](/docs/configuration.md#330-parameter-engine-194) plus a 60-second margin (`ParameterEvaluationSweeper.MARGIN_SECONDS`, a code constant: the instance's clock stamps `started_at`, the database's `NOW()` judges it). Every evaluate is bounded by that deadline, so a row past it is not running anywhere. It writes `INCOMPLETE`, never `TIMEOUT` — nobody knows how the lost attempt ended — and leaves the record's query rows as they were. The `status = 'RUNNING'` guard keeps a terminal write that lands as the sweep runs; the scan reads the partial `idx_parameter_evaluations_running`. A step that fails is logged by class and SQLState only (`parameter.evaluation_sweep_failed`; each record it closes is one `parameter.evaluation_swept` line — [Observability §3.4M](/docs/observability.md#34m-the-parameter-evaluation-events-376)) and retried the next hour. The `UPDATE` carries the retention tick's bound (#636): a JDBC `queryTimeout` of `RETENTION_STATEMENT_TIMEOUT_SECONDS` (25 s, §8.1), so an `UPDATE` queued behind a lock is cancelled with SQLState `57014` — one statement, so it closes nothing — and the next hourly tick retries. --- ## 9. What Is NOT In This Database Three pieces of execution state that a reader might reasonably expect to find here live **in Redis only, with no Postgres table and no planned one**. This section exists so that "there's no table for it" reads as a decision rather than an omission — the absence has been checked. | State | Store | Lifetime | Spec | |---|---|---|---| | Idempotency keys (`Idempotency-Key` → execution id) | Redis | `datapipelines.idempotency.ttl-seconds` (24h) | [REST API §3.5](/docs/rest-api.md#35-idempotency) | | Caller results (materialized rows + schema, served by the result cursor) | Redis | `DP-Result-TTL-Seconds`, clamped; **fixed** expiry | [REST API §7](/docs/rest-api.md#7-result-delivery) | | Post-completion replayable event log | Redis | 1 hour, not configurable | [REST API §10.3](/docs/rest-api.md#103-replay-sse-stream) | | Cross-instance cancellation flags (`dp:cancel:{execution_id}`) | Redis | until the execution ends | D7 | **Why not Postgres.** All four are short-lived, high-churn, and read almost exclusively by the instance that wrote them (or by any instance for at most an hour). Result payloads in particular are bounded by `datapipelines.result.max-size-bytes` (100MB default) — writing those into the metadata DB would make the app's own database grow with query *output* volume, and the durable path for large data is already `output.target: datasource` (D9's explicit NOT-goal). **What this means in practice:** - `execution_events` (§4.7) is the **durable** 7-day event record. The 1-hour Redis log is the live/just-finished replay surface. Both are written; they are not the same store and they do not expire together. - `pipeline_executions.result_size_bytes` / `result_row_count` (§4.6) are **history**, not availability. A row saying `result_size_bytes = 4200` says nothing about whether that result is still fetchable — that is a TTL question, answered by Redis, and past TTL the cursor returns `result.expired`. - **Redis must be configured `maxmemory-policy noeviction`** (D9, [Deployment](/docs/deployment.md)). Under an LRU policy Redis would silently evict idempotency keys and results under pressure: a replayed request would execute twice, and a completed execution's result would vanish inside its TTL. Neither failure produces an error anywhere — which is exactly why the policy is a deployment requirement and not a tuning suggestion. - Redis being unavailable at result-write time fails the execution with `result.storage_unavailable`. There is deliberately **no fallback to a metadata-DB table** — a fallback would reintroduce the dual delivery path D9 removed. --- ## Appendix A: Change Log | Date | Version | Author | Change | |---|---|---|---| | 2026-10-25 | v1.49 | 442c (#442) | §4.23: nullable target_ref frozen at run creation (V51). §4.40: strict pending cap, shared advisory key, bounded acquisition, lock order and resend refusal. | | 2026-10-24 | v1.48 | 442b (#442) | **V51 — the schedule notification outbox.** New **§4.39 `schedule_notification_events`** (one row per selected transition or block, `run_id` NOT NULL, UNIQUE `(run_id, outcome)` so a replayed transition writes none) and **§4.40 `schedule_notification_deliveries`** (one row per notice and recipient, the address snapshotted, a fixed `message_id`, `pending` / `accepted` / `failed` / `uncertain` with the due/claim CHECKs, `resend_count`); §4.24's trail CHECK gains the four `notification_*` kinds (details carry ids, counts and codes — no address, no domain; retries write no trail row); §5 lists the five new indexes. Down path documented in the migration header, not shipped. | | 2026-10-22 | v1.47 | 8b (#8) | The existing V33 CHECK admits JavaScript rows; the pooled save gate and availability control writes without a schema change. | | 2026-10-21 | v1.46 | 636 (#636) the housekeeping statements' bounds | **§8.1** — the dashboard refreshes' DELETE and the parameter evaluations' batch DELETE are bounded at 25 s like the `execution_events` DELETE (the "not bounded" clause removed); **§8.4** — the stale refresh sweep's `UPDATE` at 10 s (`REFRESH_SWEEP_STATEMENT_TIMEOUT_SECONDS`, on the sweeper's own template); **§8.5** — the evaluation sweep's `UPDATE` at the tick's 25 s. Each names the SQLState `57014` cancel, which rolls the one statement back. No DDL, no migration. | | 2026-10-20 | v1.45 | V50 (#640, script-node-rename lane, owner ruling 2026-10-20) | The **TRANSFORM→SCRIPT node-type rename**, no alias and no deprecation window. V50 rewrites every stored `pipeline_versions.body_json` node `type` value `TRANSFORM` → `SCRIPT` (RELEASED and DRAFT alike), rebuilding the `nodes` array order-preservingly and re-stamping `body_hash` over the NEW body text in the same statement (all rows carry `body_hash` = sha256 over `body_json::text` again; the untouched control rows are byte-identical). Prose that merely mentions the word — node ids, descriptions — is deliberately NOT rewritten. `template_versions`' blocks constraint is RENAMED `chk_transform_blocks` → `chk_script_blocks` (metadata-only; the V33 file itself stays byte-identical — shipped migrations are immutable). The row-level proof is `ScriptNodeMigrationTest` (V1–V49 on a scratch database, seeded pre-V50 rows, then V50): typed field moved, prose and the no-transform control intact, hash re-stamped, node order preserved, the constraint answering to the new name. Renumbered at merge after #326's v1.44. | | 2026-10-20 | v1.44 | 326 (#326) the scheduled jobs' statement bounds | **§8.1** and **§8.3** name the `execution_events` DELETE's 25 s and the stale sweep's 10 s JDBC bound — code constants beside the cadences, not keys — and the cancel's SQLState `57014`. No schema change. | | 2026-10-03 | v1.43 | 442a (#442) | Saved schedule notification settings: recipients and five event choices, validated and shown by role; omitted PUT settings preserved; mail remains off pending part b. | | 2026-10-03 | v1.42 | 418 (#418) the evaluation jobs' log events cited | **§8.1** and **§8.5** name the log events the parameter-evaluation retention and sweep write (`parameter.evaluations_purged`, `parameter.evaluation_retention_failed`, `parameter.evaluation_retention_incomplete`, `parameter.evaluation_swept`, `parameter.evaluation_sweep_failed`) and cite [Observability §3.4M](/docs/observability.md#34m-the-parameter-evaluation-events-376), where the rows are defined. No schema change. | | 2026-10-02 | v1.41 | V48 (#376, parameter-set workspace S3) the evaluation history | **§4.37 `parameter_evaluations` and §4.38 `parameter_evaluation_queries`** — the spec's §2.1/§2.2 verbatim with the owner's rulings: the status CHECK carries `ABORTED` beside the spec's five (§11.7), the query outcome CHECK carries `ABORTED` too, the caller CHECK all five values with `PIPELINE` dormant (§11.10); plus three CHECKs that pin the state machine (`finished_at` iff not `RUNNING`, `valid` iff `COMPLETED`, each code only on its own outcome) and the `parameter_set_version >= 1` floor. `principal_key_id` takes NO foreign key (the keys purge must not be blocked by a diagnostic row), and `outcome` is NULL while a statement is in flight. §5 gains six indexes (the spec's two, the §2.3 cutoff index, the queries' FK index the cascade and the detail need, two PKs); §5A classifies both tables **derived**. **§8.1** gains the evaluation retention (the event cutoff on `started_at`, one bounded batch of 5,000 per tick); **new §8.5** the stale sweep (`INCOMPLETE` past `evaluate-timeout-seconds` + 60 s, on the same hourly tick). Up-and-down rehearsal on a copy of the demo database is the lane's evidence. | | 2026-10-02 | v1.40 | V47 (#328) the release records its caller node's result columns | **Two nullable JSONB columns, no backfill:** §4.6 `pipeline_executions.result_schema_json` — every execution's caller-output schema, an array of `{name, type, nullable}`, written by `recordResult` beside `result_row_count` (history, durable past the Redis TTL; NULL for no caller node, no record, or past the 256-column / 128-character record bounds — record nothing + `warn`, never fail); §4.5 `pipeline_versions.caller_output_json` — the release's copy, written ONLY by the release flip's D1 subselect (the latest SUCCESS root execution started after the last draft write; a never-run release records NULL and still succeeds). Both outside `body_hash`. Pipeline-contract §3.3.1 is the shape and precedence source of truth. Up-and-down rehearsal on the shared test container: ALTER, ALTER, DROP, DROP, ALTER, ALTER. | | 2026-10-01 | v1.39 | V45 (#367, L5 — the key kind; the number 352's `V44__visualization_test_capabilities` holds) | **§4.2 `api_keys`: `chk_api_keys_kind` gains `'dashboard'`, `chk_api_keys_role` gains the arm `(kind = 'dashboard' AND role IS NOT NULL AND role = 'dashboard_viewer')`** — the `IS NOT NULL` is the V37 lesson spelled again; **§4.6 `pipeline_executions.executed_by_key_kind`'s CHECK gains `'dashboard'`** (the column comment also gains the `'mcp'` V37 had added and this table omitted). **§4.36 `dashboard_key_bindings`** — the `endpoint_key_bindings` twin: a folder `name_prefix` of the dashboard name space (the root `/` allowed), the key by TEXT id, cascading on delete; §5 gains its two indexes; §5A classifies it **promotable** — the twin's own class and reason, the lane's recorded decision (no promotion carrier today; a batch would carry it by key NAME exactly like its twin). | | 2026-10-01 | v1.38 | L4b (#353) a key name | **§4.32, no DDL change:** the preview token's TTL names the shipped key `datapipelines.visualization.session-ttl-minutes` (the spec's `tests.session-ttl-minutes` has no YAML form — configuration.md §3.33). | | 2026-10-01 | v1.37 | 332 (#332, #330, #331) | **V46 `idx_dashboard_versions_pins`**: the GIN index on `dashboard_versions (body_json -> 'visualizations')` — the exact expression `livePinsOf`'s containment probe reads (the visualization purge/discard/restore guards and `visualizations_get`'s `used_by`), a bitmap index scan where a whole-table scan ran (measured 5.3 ms → 0.9 ms at 4 000 dashboards; the up-and-down rehearsal on a copy of the demo-shaped database is the lane's evidence). §4.31 carries the index row. No other DDL: #330's projection and #331's batched page answer are statements over the existing tables. | | 2026-10-01 | v1.36 | V44 (#10 L4a, the test capabilities) | **§4.32 `visualization_test_runs` gains the screenshot upload capability** (`upload_token_hash`, `upload_expires_at`, `upload_consumed_at` + `chk_visualization_test_runs_upload`): the three columns are one capability — hash-only at rest, the consumption stamp strictly before the deadline — and the hash's `IS NOT NULL` is load-bearing (`NULL ~ regex` is NULL, and a CHECK passes on NULL; caught by the behavioral probe). The notes carry the single-use consumption's guarded-UPDATE fence. | | 2026-09-29 | v1.35 | V43 (#10 L2, dashboards round one) | New **§4.34 `dashboard_refreshes`** and **§4.35 `dashboard_refresh_executions`** (the refresh per run, the link D52 requires; a refused refresh writes no row, the terminal write is non-cancellable), `pipeline_executions.triggered_via` gains **`DASHBOARD`** (§4.6's CHECK text and column comment), the §5 index rows and the §5A rows (both `derived`), **§8.1** gains the refresh retention (the events' cutoff — the spec's "the executions' own policy" was none) and a new **§8.4 Stale dashboard refresh sweep**. V43 is L2's only (the `api_keys` kind/role CHECKs, `executed_by_key_kind = 'dashboard'` and `dashboard_key_bindings` are L5's V44). | | 2026-09-29 | v1.34 | V42 (#10 L1a, dashboards round one) | New **§4.28 `visualizations`**, **§4.29 `visualization_versions`**, **§4.30 `dashboards`**, **§4.31 `dashboard_versions`** (V39's two tables twice, the names changed: the kept UUID, the per-workspace name unique forever, the index row over the current body, the one-draft partial index, the database-computed hash, the release/discard/via stamps) and the test evidence L4 writes — **§4.32 `visualization_test_runs`** (a run per version and session, hanging off the version by a composite foreign key that cascades, the preview token stored hashed with its expiry, the closed status list) and **§4.33 `visualization_test_screenshots`** (PNG/WebP, ≤ 4 MiB in the schema, the server's SHA-256). §5 gains their eleven indexes; §5A classifies the four artifact tables promotable and the evidence environment-local. The spec's `byte_length(bytes)` is Postgres's `octet_length`. Down path documented in the migration's header and proven on a copy of the demo database. | | 2026-09-29 | v1.33 | 310 (#310) | §8.2 is IMPLEMENTED (it was documented and never run): the batched form of the same statement — the cutoff read once per tick from the database's clock, then `DELETE … WHERE id IN (SELECT id … WHERE "timestamp" < :cutoff ORDER BY "timestamp" LIMIT :batchSize)` until a batch comes back short, at most 50 batches or 2 s a tick. The rows deleted are exactly the one-statement form's; no schema change | | 2026-09-28 | v1.32 | 286 (#286) | §4.13's `retired_reason` bullet says what sees a legacy row: the workspace-scoped legacy read (the unscoped `null` branch, which had no caller, is removed), the unpublish row read, and nothing valid-only — the registry, the conflict check, key binding (`requireInsideWorkspace`), the Usage tab (`findByPipeline`), promotion (omitted, the count logged). The echo of a stored path is cut at the grammar's 200 characters; the column is unchanged (no CHECK added — unpublish is by path). The boot WARN reads `at_least`. No DDL change. | | 2026-09-27 | v1.31 | V41 (#274) | §4.13 `published_endpoints` gains `retired_reason TEXT NULL` (NULL = a normal row), and V41 retires every row whose stored path has fewer than three segments — the R-EP5 rows saved before 2026-09-19 made the row mapper throw at boot (#274). `is_enabled` goes FALSE with the reason `'pre-R-EP5 path'`; rows are never deleted (the listing flags them; unpublishing is the fix). The repository's legacy mapping is the fail-closed second half — a row whose path fails today's grammar never reaches the serve registry or the conflict check even without this migration. Down path is in the migration header (re-enables every retired row; the column cannot distinguish its own disables from an operator's). | | 2026-09-26 | v1.30 | V39 (#194 lane B, the parameter engine) | New **§4.26 `parameter_sets`** (the index row: a UUID id kept by import, the per-workspace name unique forever, display metadata indexing the CURRENT body, the sticky pointer) and **§4.27 `parameter_set_versions`** (the template_versions shape minus the template-only columns: `body_json` without the name, the database-computed hash, the release/discard/via stamps with CHECKs, the one-draft partial index). §5 gains their four indexes; §5A classifies both promotable. The record's §8.1 said "§4.21/§4.22" — stale since V36/V38 took them. Down path documented in the migration's header and proven on a copy of the demo database. | | 2026-09-27 | v1.29 | V40 (scheduler follow-ups, #258) | **§4.23 `schedule_runs` gains the execution's own timing**: `execution_started_at TIMESTAMPTZ NULL` / `execution_completed_at TIMESTAMPTZ NULL` — copied by the reconciler onto the run in the SAME transition that records the terminal state, so rest-api §20.10–§20.12 answer "how long did the execution take" from the run row alone (the run's own `finished_at` is the reconciler's stamp, up to a tick late). Null until such a terminal: runs that never launched and rows finished before V40. Additive; down path in the migration header. | | 2026-09-25 | v1.28 | V38 (scheduler lane 1, #9) | New **§4.22 `schedules`**, **§4.23 `schedule_runs`**, **§4.24 `schedule_run_events`** and **§4.25 `scheduled_tasks`** (the [scheduler design revision](https://github.com/msabiransari/datapipelines/blob/main/docs/superpowers/specs/2026-09-22-scheduler-design-revision.md) §7): the schedules (soft delete with a live-name partial unique index, independent `enabled`/`blocked_*`, the create Idempotency-Key and its hash — L1), their runs (R6's ten states, the frozen context, the executor-owned `prepared_json`, one row per occurrence, one Run now per key, AT MOST ONE ACTIVE RUN per schedule as a partial unique index), the runs' append-only trail (by construction — no trigger, §2), and db-scheduler 16.12.0's own table verbatim. `pipeline_executions.chk_triggered_via` gains `SCHEDULE`. §5 gains the eighteen indexes; §5A classifies `schedules` environment-local (never promoted) and the other three derived. Twenty-five tables. | | 2026-09-25 | v1.27 | V37 (keys v2, #233) | **Every key is a robot member of one workspace** (record §10 A13–A19). §4.2 `api_keys`: the `user` kind is renamed `mcp` (A19 — kind is the transport) and `DEFAULT 'mcp'` follows; `role` is the role CHOSEN at creation — `author\|promoter\|workspace_admin` on `mcp` (A13/A14), the transport roles unchanged; `chk_api_keys_kind` is replaced for the rename, `chk_api_keys_role` is replaced — every role-bearing arm spells `role IS NOT NULL` because `role = …` is UNKNOWN for a NULL role and a CHECK passes on UNKNOWN, and the first draft's missing `IS NOT NULL` on the `mcp` arm ADMITTED a live NULL-role row (measured; fixed before merge); `minted_at_login` and its per-`(user, workspace)` unique index are DROPPED (A15 — the login mint is gone), and V37 CONVERTS every live `user`-kind row — the login-minted and the pre-R3 on-demand ones (V31 left the latter `minted_at_login = FALSE`; a row the final CHECK would refuse must not survive): an owner's membership `author\|promoter\|workspace_admin` → identity-backed key with that role, a viewer or membership-less owner → REVOKED, never guessed (B4, NOTICE counts); `created_by` keeps the human creator, whose removal revokes the key (A17/B6). New `uq_api_keys_live_workspace_name` — a live key's NAME is unique per workspace (A18; duplicates disambiguated by the migration, all but the newest gaining the key id's tail). `pipeline_executions.executed_by_key_kind`'s CHECK widens to admit `'mcp'` beside `'user'` for history. §5 index inventory swaps `api_keys_one_live_user_key` for `uq_api_keys_live_workspace_name`. A20 (the 233c correction): the conversion reads the owner's MEMBERSHIP, never `users.is_active` — an inactive owner's key converts and is then refused at request time until the owner is reactivated. | | 2026-09-25 | v1.26 | V36 (7e, #7) | New **§4.21 `template_implements`** — the WORKSPACE facts a transform version cites as implementing (transform-nodes design §2.3): PK `(template_id, version, fact_id)`, the composite FK to `template_versions` ON DELETE CASCADE (a purged draft takes its citations), the FK to `learned_facts` with NO cascade (facts are retired, never deleted), `idx_template_implements_fact` for the reverse arrow. Outside `body_hash`; `needs_review` computed on read, never stored. `learned_facts` gains the partial `idx_learned_facts_supersedes` (a retired fact's successor). §4.9's "nothing references a `template_versions` row by FK" note corrected; §5 gains the three indexes; §5A classifies the table promotable (carried on the version, resolvable ids kept on import — owner ruling 2026-09-25); the ERD gains both edges. Twenty-one tables. V37 is left unused — lane 233b's keys-v2 migration renumbered to V37 at the 233c base refresh (Flyway refuses a lower number after V36 has run). | | 2026-09-24 | v1.25 | V34 (215b, #215) | Key identities and key roles ([Auth §4.7](/docs/auth.md#47-key-identities), [§7.5](/docs/auth.md#75-key-roles)). §4.1 `users` gains `kind` (`human` \| `service` \| `system`, CHECK'd; the System row is set to `system`). §4.2 `api_keys` gains `created_by` (backfilled from `user_id`, NOT NULL, FK to `users`) and `role` with `chk_api_keys_role` — `api_caller` on every endpoint key, `promotion_receiver` on every server key, NULL on the MCP key; each non-null arm is spelled `role IS NOT NULL AND role = …`, because the record's `role = …` alone is UNKNOWN for a NULL role and a CHECK passes on UNKNOWN. V34 creates one `service` identity per existing endpoint/server key (inactive for a revoked key), repoints the key's `user_id` to it, and refuses to run unless the three counts agree; `scopes` is DROPPED (PK8). New `idx_api_keys_created_by`. `pipeline_executions.executed_by`'s comment names the identity for endpoint/server-key runs; past rows are not rewritten. Down path is in the migration header (lossy by design). | | 2026-09-23 | v1.24 | V32 (213, #213) | Data-only, no DDL: `UPDATE api_keys SET secret_sealed = NULL WHERE secret_sealed IS NOT NULL` — show-once (D16 amended 2026-09-23) makes the fleet hash-only immediately; the column stays for keys minted since, cleared on their first read. §4.2's column note updated. No down path (a cleared copy cannot be restored — that is the point). | | 2026-09-21 | v1.23 | V31 (179, roles R3) | §4.2 `api_keys` gains `secret_sealed BYTEA` (the login-minted key's plaintext sealed under the credential-encryption key, AAD = the key id) and `minted_at_login BOOLEAN`, plus the partial UNIQUE `api_keys_one_live_user_key` (`(user_id, workspace_id)` where `kind = 'user' AND NOT is_revoked`) — the migration revokes all but the newest live user key per pair first, with the count in a NOTICE. §5's table gains the index. | | 2026-09-20 | v1.22 | V29 + V30 (177, roles R1) | **§4.12 `workspace_members` and §4.17 `workspace_invitations` carry ONE `role`** (`viewer` \| `author` \| `promoter` \| `workspace_admin`, CHECK'd) — V23's three booleans folded back by V29 with the precedence admin → workspace_admin, else promoter, else author, else viewer; `idx_workspace_members_admins` keeps its name over the new predicate; the down path is in the migration and proven up-and-down by `WorkspaceRolesMigrationTest`. **§4.6 `pipeline_executions.triggered_by` → `executed_by`** (V30, D11 — the same NOT NULL FK, every actor kept) plus **`executed_by_key_kind`** (`user` \| `endpoint` \| `server` \| NULL, CHECK'd; backfilled from `triggered_via`: ENDPOINT → endpoint, MCP → user); the ERD edge follows. §4.6 also records `ENDPOINT` in `chk_triggered_via`, which V11 had widened without this document noticing. | | 2026-09-19 | v1.21 | 172 (#172) | No schema change. §4.13 reworded for R-EP5: the stored `path_pattern` is the part after `/api` in the `/api///` shape — the column, the constraint and every index are exactly as V11 wrote them. | | 2026-09-14 | v1.20 | V28 migration (140 release checks) | New **§4.20 `pipeline_check_runs`** — the server-side record of every release check run ([Pipeline Contract §3.3/§12.12](/docs/pipeline-contract.md)): one append-only row per check per run, `via` CHECK'd over `mcp` \| `rest` \| `ui` \| `release`, `verdict` CHECK'd over `pass` \| `fail` \| `error` (error = no verdict could be formed, recorded — never silently a fail), `parameters_json` the BOUND context, `observed_json` one small object (`{"value": …}` or `{"rows": …}`, NULL when the run errored before a value was read). FK to `pipelines(id)` only, deliberately not the composite version FK — a purged draft's runs are history to keep. §5 gains `idx_pipeline_check_runs_latest`; §5A classifies it derived; the ERD gains `pipelines ──1:N── pipeline_check_runs`. Nineteen tables. | | 2026-09-14 | v1.19 | V27 migration (137 mail notices) | New **§4.19 `mail_sends`** — the claim row behind every notice ([Auth §5A.8](/docs/auth.md#5a8-mail-the-welcome-mail-and-the-new-user-notice)): one row per message identity `(user_id, kind, act_id)` (`uq_mail_sends_message`), `chk_mail_sends_kind` over the closed list, `claimed_at` / `sent_at` / `message_id` / `error`; a row proves an attempt, never a delivery, and is never cleaned up. §5 gains its two constraint-backed indexes; the ERD gains `users ──1:N── mail_sends`. Eighteen tables. | | 2026-08-05 | v1.0 | initial draft | Complete metadata DB schema: 10 tables (users, api_keys, audit_log, pipelines, pipeline_versions, pipeline_executions, execution_events, templates, template_versions, datasources), indexes, ERD, NamedParameterJdbcTemplate data access pattern, Flyway strategy, maintenance jobs | | 2026-08-07 | v1.1 | consistency campaign | Applied [SPEC-REVIEW-2026-08 §2.10](https://github.com/msabiransari/datapipelines/blob/main/docs/SPEC-REVIEW-2026-08.md#210-metadata-dbmd). Established as sole DDL authority (D4): `_json` suffix rule stated in §2 and applied (`audit_log.details` → `details_json`); `TIMESTAMPTZ` confirmed everywhere. `datasources` (§4.10) absorbed the datasources.md reconciliation — `description` nullable, `name` CHECK (63 chars + `^[a-z0-9_-]+$`), new `query_timeout_seconds` column with CHECK, soft-delete partial index, `properties_json` documented as the hikari/jdbc passthrough (D7). `params_schema` deleted from `template_versions` (D3); `is_library` moved from `templates` to `template_versions` (version-scoped per D12); `idx_templates_active` added. `users` gained `updated_at` + `theme_preference TEXT NULL` (NULL = follow the deployment default `datapipelines.ui.theme`; stored preference, not session state — ui-screens §2.12.4), with the per-request `is_active` cache note (D13); `templates` gained `updated_at`; `updated_at` maintenance rule stated (app-set, no triggers). `pipeline_executions` gained the composite FK to `pipeline_versions(pipeline_id, version)`, dropped `result_delivery` (D9), gained `result_row_count`. `execution_events`: redundant `idx_events_execution` dropped, UNIQUE constraint named. §5 regenerated from §4 with constraint-backed indexes under their real names (phantom `uq_users_email` / `uq_pipelines_name` removed) plus a deliberately-absent list. §6.1 `create()` rewritten as working single-CTE SQL + a real `RowMapper` whose result is returned; §6.2 conventions extended. §8 renamed "Operational Jobs" and fully parameterized from config keys (D8) — no interval literals. New §9 stating idempotency keys, results, the 1-hour event log, and cancel flags are Redis-only (D9/D7). Broken link `pipeline-contract §15.3` → §17.3 fixed; terminal-node language replaced by the caller-node model (D1) | | 2026-08-15 | v1.2 | V2 migration | §4.10 `datasources` gains `introspection_include_schemas_json JSONB NOT NULL DEFAULT '[]'` (migration V2) — the §7A introspection allowlist of datasources.md §3.3; `[]` and absent are the same behavior. | | 2026-08-16 | v1.3 | V3 migration | §4.6 `pipeline_executions` gains the composition-lineage columns `parent_execution_id` (self-FK), `parent_node_id`, `root_execution_id` (migration V3, design doc 2026-08-13-pipeline-node-type §5) — `root_execution_id` backfilled to `execution_id` and NOT NULL going forward, so family queries and cancellation never special-case NULL; new explicit index `idx_executions_root`; `chk_triggered_via` widened with `'PIPELINE'`. | | 2026-08-26 | v1.4 | V4 migration | Workspaces, slice 1 (design doc 2026-08-16-workspaces-design §3/§4, D2/D9; re-base resolutions R1/R2 recorded here, amending that spec's PROPOSED DDL). New tables §4.11 `workspaces` and §4.12 `workspace_members`; the `default` workspace is seeded with the well-known constant UUID `defa0000-0000-0000-0000-000000000001` (R2 — deterministic across deployments and greppable; a boot-time DB lookup or a config key was considered and rejected) and `created_by` NULL (R1 — NULL = system-provisioned; the spec's `NOT NULL` gave the seed no user to reference on a fresh install). §4.4 `pipelines` gains `workspace_id NOT NULL` backfilled to `default`; name uniqueness moves from global (`pipelines_name_key`) to per-workspace via the explicitly named `uq_pipelines_workspace_name` — same mechanism (plain UNIQUE constraint, soft-deleted rows included). §4.8 `templates` re-keys onto a surrogate `id UUID` PK; the TEXT id becomes `name`, unique per workspace (`uq_templates_workspace_name`); `idx_templates_active` follows the rename onto `name`. §4.9 `template_versions.template_id` re-keys onto the surrogate (same FK name and `ON DELETE CASCADE`); pipeline-JSON and `imports_json` `{id, version}` refs keep meaning the human id (`name`) — stored payloads not rewritten. §4.10 `datasources` gains `workspace_id UUID NULL` (NULL = global; existing rows backfill NULL, D9) and `is_readonly NOT NULL DEFAULT FALSE` — columns only, no datasources-module change in this slice. §4.2 `api_keys` gains `workspace_id NOT NULL` backfilled to `default` (D3 pinning). §3 ERD, §5 index table, §6.1 example, and §7.1 file list updated (the §7.1 list also stops showing the stale `V2__seed_admin_user.sql` placeholder — V2/V3 are the introspection and lineage migrations). | | 2026-08-28 | §4.10 prose refresh | The "columns land here, module unchanged / enforcement arrives with the readonly slice" note is history: the surfaces slice put `workspace_id` and `is_readonly` on the entity, in the repository's INSERT/UPDATE/read-join SQL, and made visibility (`bound-to-active OR global`) a repository-level predicate. No DDL change in this note. | | 2026-08-29 | v1.5 | V5 migration | §4.1 `users` gains the local password auth columns (migration V5, auth.md §5A): `password_hash TEXT NULL` (NULL = OIDC-only account; Argon2id via the same `SecretHasher` as API keys), `password_changed_at TIMESTAMPTZ NULL`, `must_change_password BOOLEAN NOT NULL DEFAULT FALSE` (the forced-change gate), and the per-account lockout pair `failed_login_count INTEGER NOT NULL DEFAULT 0` / `locked_until TIMESTAMPTZ NULL`. Additive only — existing rows backfill NULL/defaults and behave exactly as before; no new indexes (the login lookup keys off the existing UNIQUE `email`); the "No password column" note is replaced by the password/lockout notes. | | 2026-09-01 | v1.6 | V6 migration (035) | §4.5 `pipeline_versions` and §4.9 `template_versions` gain the version-lifecycle columns (migration V6, versioning.md §11): `status` (DRAFT/RELEASED/DISCARDED, CHECK-constrained, existing rows backfill RELEASED), `body_hash` (SHA-256 hex, DB-computed over the canonical body projection — the same expression in the backfill and every write, so writer and reader can never disagree; NOT NULL after backfill), `released_at` (DB-generated at release — versioning §8's cross-clock precondition), `released_by`, and the draft-write pair `updated_by`/`updated_at` (the 409 conflict details; not restamped at release/discard). Each table gains `uq_*_versions_one_draft`, the partial unique index that makes copy-on-write race-safe (versioning §3.3). §2's immutable-tables rule amended accordingly; §5 index table updated; §7.1 file list gains V6. New **§5A Promotion Classification** — one row per table (promotable / environment-local / derived) parsed by `FlywayMigrationIntegrationTest` in both directions, so a new table fails that test until it is both expected and classified (the D17 registry). | | 2026-09-03 | v1.7 | V9 migration (061/T84) | §4.10 `datasources` gains `last_test_at TIMESTAMPTZ`, `last_test_ok BOOLEAN` and `last_test_message TEXT` (migration V9) — the last connection test's outcome, [Datasources §8.1B](/docs/datasources.md#81b-the-last-tests-outcome-is-stored-and-listed). All three nullable with no default: all-NULL is "never tested", the truthful state of every existing row. The §2 `updated_at` rule gains its one exception — the outcome write does not move it, because an observation is not an edit and §8A.3 rule 1's byte-untouched guarantee is checked against exactly those columns. No new table, so §5A's promotion classification is unchanged: `datasources` stays environment-local, and an environment-local table's connectivity record is environment-local by construction. | | 2026-09-08 | v1.9 | V17 migration (091) | §4.2 `chk_api_keys_kind` widens to admit `'server'` ([Auth §7.7](/docs/auth.md#77-key-kinds-and-published-endpoint-bindings), [Enums §8A](/docs/enums.md#8a-apikeykind--what-an-api-key-is)) — the promotion peer's credential, until now a pre-shared config value with no row anywhere, is now an ordinary `api_keys` row (hash, prefix, owner, expiry, revocation, last-used) whose authority is a route family. V11 named the constraint "so a later kind widens it"; this is that kind. Additive: no existing row can be `'server'`, so nothing is backfilled and no index changes — `idx_api_keys_endpoint_kind` stays `endpoint`-only, and a deployment holds at most a handful of server keys. | | 2026-09-05 | v1.8 | V11 migration (074) | New **§4.13 `published_endpoints`** and **§4.14 `endpoint_key_bindings`** (migration V11, [REST API §19](/docs/rest-api.md#19-published-endpoints)) — a released pipeline served as a `GET` endpoint, and which API keys authorise which node of that tree. §4.2 `api_keys` gains `kind` (`user` \| `endpoint`, CHECK-constrained, `DEFAULT 'user'` so the whole pre-V11 table backfills correctly) plus the partial index `idx_api_keys_endpoint_kind`. `pipeline_executions.chk_triggered_via` widens to admit `'ENDPOINT'` — a closed set since V1, so without this the first serve would fail on the constraint rather than on anything the design describes. §5 index table and §5A's classification updated: both new tables are **promotable** (a URL contract is authored, and bindings travel by key NAME because `api_keys` itself is environment-local — a target missing that name refuses the batch with `endpoint.promotion.key_missing`). | | 2026-09-05 | v1.9 | V12 migration (077) | §4.8 `templates`: `name` requires a **folder** ([Template Hierarchy §4.1](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md#41-grammar)) and migration **V12** carries the deploy gate for stored names — a `DO`-block pre-check that aborts naming every flat offender, active and soft-deleted, and **no DDL at all**. No table, column, index, constraint or classification changes, which is why every other section of this document is untouched. The gate deliberately ignores `pipelines`: that name is validated at save only, so a legacy flat pipeline still runs and an abort over it would be a false alarm ([Template Hierarchy §14.2](https://github.com/msabiransari/datapipelines/blob/main/docs/template-hierarchy-design.md)). | | 2026-09-07 | v1.10 | V13 + V14 migrations (087) | §4.10 `datasources`: `password_encrypted` → **`credential_encrypted`, now NULLABLE**, plus **`credential_kind TEXT NOT NULL DEFAULT 'password'`** and a nullable `username` (V13, [Datasources §3.4](/docs/datasources.md#34-credential-kinds)). Three CHECKs: the kind is one of the enums.md §5A set, `kind = 'none'` ⟺ no ciphertext (which is what makes `password_set` derivable rather than a stored flag), and `username` is present exactly when the kind allows it. The backfill is TRUE rather than a guess — every pre-087 row went through a save path that required a username and a password. The credential blob stays kind-agnostic under the V10 versioned envelope, so rotation is untouched. **V14** widens `chk_datasource_dialect` to admit `'LAKE'` (dropped and recreated — Postgres has no ALTER for a CHECK expression); no data changes, since no existing row can hold a value that did not exist. | | 2026-09-10 | v1.12 | V23 migration (112, RBAC round 1) | **§4.12 `workspace_members` is now the capability record** (RBAC design D-R1/D-R2): `role TEXT` and its CHECK are gone, replaced by three additive flags — `author`, `promoter`, `admin` — with `chk_workspace_member_admin_authors` stating "admin implies author" once, in the database, so every predicate can read `author` off the row. A row with all three false is a viewer. V23's backfill is `owner → admin+author`, `member → author`, which preserves exactly what worked the day before: session capability WAS `JwtService.scopesFor`, giving every non-admin user `author` globally. The last-admin rule is deliberately NOT a constraint (a cross-row invariant no CHECK can state, and a trigger's refusal carries no catalogued code) — `WorkspaceService` enforces it and answers `workspace.last_admin`; `idx_workspace_members_admins` makes its count cheap. **§4.11 `workspaces` gains `deactivated_at` / `deactivated_by`** (D-R10: deactivate, never delete — nothing is purged, ever) (D-R11's `demo` workspace is created by `DemoWorkspaceSeeder` at boot, NOT by this migration — the seeder imports the example content in the same act and SQL cannot, so seeding the row here would have made the seeder dead code). **§4.10 `datasources` loses `workspace_id`** and gains `owner_workspace_id`: ownership and visibility were one column and are now two concepts. **New §4.16 `datasource_workspaces`** is the visibility half (D-R7) — "global" is gone, and V23's backfill grants every former global datasource to every existing workspace so nothing visible yesterday stopped being visible. It is keyed by `datasource_name`, because `datasources` is keyed by its name and there is no id to point at (the design record's `datasource_id UUID` names a column that has never existed). **`users.scopes` was NOT dropped: it never existed** — the only `scopes TEXT[]` in the schema is `api_keys.scopes`, which stays, and what carried global session capability was Kotlin, not a column. | | 2026-09-11 | v1.17 | V25 migration (118, learned semantic layer round 1) | **New §4.18 `learned_facts`** — the facts an agent learned about a datasource that introspection could not tell it (the 2026-09-11 learned-semantic-layer design record): two scopes in one table (`DATASOURCE` visible wherever the datasource is granted, `WORKSPACE` bound to one workspace — `chk_learned_facts_scope_workspace`), a closed `kind` CHECK with no JDBC-provided kind (D-S2) and a `kind ↔ scope` CHECK stating each kind's scope once, structural refs stored normalised as JSONB (the O-4 duplicate key), a per-table addressable `schema_fingerprint`, the six-state `trust` with `retired` tied to its stamp, `recorded_via` under the V20 write-surface set, and `supersedes` for history. Cascades: the datasource delete only; a purged source pipeline detaches (`SET NULL`). Two indexes — per datasource, and a partial one on `workspace_id`; the design's expression index over `refs_json->0` was not created (it indexes only the first ref and serves no shipped read). §5 and §5A (environment-local) updated. | | 2026-09-14 | v1.18 | V26 migration (136 §B, a WORKSPACE rule may carry no refs) | **§4.18 `learned_facts`**: `chk_learned_facts_refs` is re-created scope-aware — the array half is unchanged, the `≥ 1` half applies to `scope = 'DATASOURCE'` only, so a WORKSPACE-scope `definition`/`exclusion`/`preference` may store `refs_json = []` (a rule that spans datasources, bound to one datasource for visibility, naming no table). Same constraint name; no data change (no existing row violates the wider CHECK). | | 2026-09-10 | v1.13 | V24 migration (113, workspace invitations) | **New §4.17 `workspace_invitations`** — the bridge that lets a workspace admin add `bob@company.com` BEFORE Bob has ever signed in. Keyed by `(workspace_id, email)` with the email stored lowercase (a CHECK enforces the §4.2 canonical form); the member flags are carried on the row under the same `admin → author` CHECK the members table states, and a re-invite REPLACES them (the latest admin decision wins, audited). Deliberately separate from `workspace_members`: that table's `user_id` stays `NOT NULL` and nothing pretends a person exists before they do. The login path materialises the row into a membership in one statement, preserving `invited_at` as `joined_at` so a multi-workspace invitee's `active_workspace` stamps the EARLIER invitation; pending invitations into a deactivated workspace wait for reactivation. `idx_workspace_invitations_email` is the materialise lookup's access path. No expiry column in v1 — an invitation is revocable like any other membership (owner ruling). | | 2026-09-07 | v1.11 | V15 migration (089 §A) | New **§4.15 `lake_tables`** — the dp-lake catalog: which Parquet/Iceberg tables a LAKE-dialect datasource serves (the 2026-09-07 lake-datasource design record §2). `datasource_id` is TEXT referencing `datasources(name)` — the datasource PK IS its name; there is no surrogate id to point at. `namespace` is 087's segment list as a Postgres `TEXT[]`; `format` is CHECKed (`parquet` \| `iceberg`) because a third value would generate bad view SQL later; the named `uq_lake_tables_datasource_namespace_name` lets the service map a re-registration to the catalogued `datasource.lake_table_duplicate`, and its index doubles as the list-by-datasource access path, so §5 gains no separate FK index. §3 ERD, §5 index table and §5A's classification updated: the table is **environment-local** — it points at an environment-local datasource row and at bucket locations whose credentials never leave the deployment. | | 2026-09-10 | v1.16 | V22 migration (109 §A) | `lake_tables` gains `last_error TEXT` / `last_error_at TIMESTAMPTZ` — the per-table connect-time view-creation outcome (datasources.md §8C.2): a failing view is recorded and skipped rather than failing the pool build. Both NULL = healthy; transition-only writes; cleared on the next successful view creation. §4.15 sketch and notes amended. | | 2026-09-08 | v1.13 | V18 migration (099, backfilled entry) | `pipelines.current_version` / `templates.current_version` drop `DEFAULT 0` and `NOT NULL` and become nullable, and any `0` sentinel rows are nulled — creation lands version 1 as a DRAFT (D55), so a fresh entity has a version and no pointer at all. §4.4/§4.8 sketches and notes updated (this entry was missing from the Appendix when 099 landed — the sketches still said `NOT NULL DEFAULT 0`; caught while amending them for V19). | | 2026-09-08 | v1.14 | V19 migration (101) | **Discard stamps** on both version tables: `discarded_at TIMESTAMPTZ NULL` / `discarded_by UUID NULL REFERENCES users(id)` plus `chk_*_discard_stamps` — both NULL unless the row is DISCARDED, and a DISCARDED row must carry `discarded_at` (pre-101 executed-draft tombstones backfill `COALESCE(updated_at, NOW())`; `discarded_by` stays NULL — the actor is unknown history). **`is_deleted` retired** from `pipelines` and `templates`: soft-deleted rows migrate to "every version DISCARDED, pointer NULL" (counted by a `RAISE NOTICE` — expected zero outside tests), `idx_pipelines_owner` / `idx_templates_active` are rebuilt plain, and the columns drop — entity status is the §3.2 derivation (`EXISTS` a live version), never stored. §4.4/§4.5/§4.8/§4.9 sketches and notes amended in place; the purge-deletes-executions choice is documented at §4.5. | | 2026-09-08 | v1.12 | V16 migration (089 §F) | **V16 widens `template_versions.chk_dialect` to admit `'LAKE'`** — dropped and recreated, the V14 shape, since Postgres has no ALTER for a CHECK expression; additive in effect, no data changes (no existing row can hold a value the CHECK has refused since V1). The dialect itself joined the datasource CHECK in V14; this is its template twin, found by the MinIO suite going red on the first `dialect: LAKE` template insert — the 088 showcase content (`nyc/lake/rideshare_zone_day.sql`) declares exactly one. The §4.9 sketch keeps the V1 constraint, the same convention §4.10 follows for V14 — this row is the record of the widening. | | 2026-09-09 | v1.15 | V20 migration (102) | **Write-surface stamps** on both version tables: `created_via` / `updated_via TEXT NOT NULL DEFAULT 'session'` with `chk_*_via` admitting `'session' \| 'api_key' \| 'mcp'` — the surface a write arrived on, stamped at the entry point (REST maps the auth method, MCP tools pass `'mcp'`, imports keep the default), while `*_by` keep naming the person; a release stamps nothing new (D4). §4.5/§4.9 sketches and notes amended in place. |