Metadata Database Schema Specification

Status: v1.17 (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-09-11

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 and Templates §12.2 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 — 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).
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); 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 (triggered_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)
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.

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)
    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
);

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) and 'local' (admin-created local account — Auth §5A.1). See Auth spec §5.1.
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 (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).
- NULL is meaningful: it means "no explicit choice — use the deployment default datapipelines.ui.theme". 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, 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.
password_hash (V5) holds the Argon2id encoded hash of a local account's password (Auth §5A, 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`

API keys issued per-user-per-agent. See Auth spec §7.

CREATE TABLE api_keys (
    id                    TEXT        PRIMARY KEY,          -- 'dpk_ABCDEFGHIJKL'
    user_id               UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    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
    scopes                TEXT[]      NOT NULL DEFAULT '{read}',
    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 'user',   -- 'user' | 'endpoint' (V11) | 'server' (V17)
    CONSTRAINT chk_api_keys_kind CHECK (kind IN ('user', 'endpoint', 'server'))
);

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;

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).
scopes is a TEXT[]; a key's scopes must be a subset of its creator's scopes at creation time (enforced by the application, not the schema — the creator's scopes are derived per D14, not stored).
is_revoked and expires_at are both re-checked on every request through the 60s cache in Auth §11.4 (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.
kind (V11) is the key's KIND (Auth §7.7): user is every key that existed before it — scopes, a workspace, the whole API surface its scopes allow — and endpoint is a credential for published endpoints only. An endpoint key's scopes are never consulted; its authority is its rows in endpoint_key_bindings, and one with no binding on any ancestor of the path it presents at authorises nothing. DEFAULT 'user' is the correct backfill for the whole pre-V11 table, so the migration needs no UPDATE.
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.

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).
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 for auth.* events, Datasources §7.4 for datasource.* events (both vocabularies registered in Enums §15); it is subject to the redaction rules in Observability §3 — no credentials, no jdbc_url.
Retention: datapipelines.audit.retention-days.

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.

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). See Pipeline Contract §17.3.

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
    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 — 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 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.
PRIMARY KEY (pipeline_id, version) is also the target of the composite foreign key on pipeline_executions (§4.6); 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.

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)
    triggered_by        UUID        NOT NULL REFERENCES users(id),
    triggered_via       TEXT        NOT NULL,        -- 'UI' | 'REST' | 'MCP' | 'PIPELINE'
    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
    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')),  -- 'PIPELINE' added by V3
    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(triggered_by, started_at DESC);
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; triggered_via values are ExecutionTrigger. Both CHECK constraints list only the shipped values — adding SCHEDULED/WEBHOOK later is a migration, deliberately (V3 added PIPELINE exactly that way).
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, DAG Executor §7.3). 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.
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).
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.

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). Both are written; only this one survives past the hour.

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).
event_type values are the SSE event names in Enums §11, 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 per datapipelines.executions.event-retention-days. The execution record itself (in pipeline_executions) is retained longer for history.

4.8 `templates`

Template metadata. See Templates spec.

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). 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).
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 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. 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).
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.
is_library lives on template_versions, not here (§4.9). Import validation resolves it at an exact {id, version} (Templates §6), 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 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).

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',
    dialect         TEXT        NOT NULL,            -- 'POSTGRES', 'ORACLE', etc.
    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`
    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 {engine,dialect,is_library,imports,body} object
    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 IN ('POSTGRES', 'ORACLE', 'MSSQL', 'MYSQL', 'H2', 'DUCKDB', 'SQLITE')),
    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) — because display_name/description live on the index row templates only and are not part of the versioned artifact (they keep updating at save time; versioning v1.3 records the asymmetry). Second, discard is always a hard delete — nothing references a template_versions row by FK (pipeline pins are numbers in JSON, not constraints), so versioning §3.4's executed-draft DISCARDED branch cannot fire here.
No params_schema column (D3) — deleted, not deprecated. Any migration from a pre-v1.1 draft drops it.
engine carries the TemplateEngine value. It is NOT NULL DEFAULT 'freemarker' and deliberately has no CHECK constraint: v1 accepts only freemarker (enforced by save-time validation, D2), and future engines must not require a migration to become storable. The default exists for the v1 write path, which never omits it.
is_library is version-scoped (§4.8). Templates §6 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. This table is where Datasources §7.2 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).

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). 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 says it is optional, and this column now says the same thing.
properties_json holds the Datasources §5 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; NULL means "use the global default" (Datasources §5.5).
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 dialect set.
credential_encrypted (V13; password_encrypted before it) is BYTEA ciphertext only (Datasources §7.1) — 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). 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) 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). 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 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).

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 capability (RBAC design D-R1/D-R2): a person's role is per workspace, carried as three additive flags. A row with all three false is a viewer. users.is_admin is the one global capability left and means super admin (§4.1).

CREATE TABLE workspace_members (
    workspace_id UUID        NOT NULL REFERENCES workspaces(id),
    user_id      UUID        NOT NULL REFERENCES users(id),
    author       BOOLEAN     NOT NULL DEFAULT FALSE,   -- create/edit/discard/restore/purge, publish endpoints, issue own keys (V23)
    promoter     BOOLEAN     NOT NULL DEFAULT FALSE,   -- release and promote (V23)
    admin        BOOLEAN     NOT NULL DEFAULT FALSE,   -- members, roles, workspace-bound datasources, the audit trail (V23)
    joined_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (workspace_id, user_id),
    CONSTRAINT chk_workspace_member_admin_authors CHECK (NOT admin OR author)
);

CREATE INDEX idx_workspace_members_admins ON workspace_members(workspace_id) WHERE admin;

Notes:

Flags, not a role column, because the roles are additive (D-R2). "An author who also releases" and "a DevOps person who ONLY releases" are both one row, and no single label can name both. The role TEXT column (owner | member) and its CHECK were dropped in V23.
V23's backfill: owner → admin + author, member → author. A member already had the whole authoring surface — session capability was JwtService.scopesFor, which gave every non-admin user author globally — so member → author preserves exactly what worked the day before rather than demoting anyone.
admin → author is a CHECK, not a convention. A workspace admin can author (RBAC design §1), and stating it once in the database is what lets every capability predicate read author off the row instead of re-spelling the implication at each site. The service normalises before writing so a caller who ticks only "admin" gets what they asked for rather than a constraint violation with no catalogued code.
The last-admin rule is NOT a constraint. "At least one 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 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_flags_changed records the before and after (auth.md §10.1).

4.13 `published_endpoints`

The registry of released pipelines served as GET endpoints under /api/x (V11, round 074). See REST API §19.

CREATE TABLE published_endpoints (
    id               UUID        PRIMARY KEY,
    workspace_id     UUID        NOT NULL REFERENCES workspaces(id),
    path_pattern     TEXT        NOT NULL,   -- '/lending/{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,
    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/x/lending/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.
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.

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 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).

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<String>: {"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: 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<String> (the NamespaceShape 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-<uuid>.metadata.json, not the table root, which DuckDB 1.5.5 cannot resolve for pyiceberg tables; the measured rule, datasources.md §8C.7) 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): 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.

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).

CREATE TABLE workspace_invitations (
    workspace_id UUID        NOT NULL REFERENCES workspaces(id),
    email        TEXT        NOT NULL,                       -- normalized lowercase, the §4.2 rule
    author       BOOLEAN     NOT NULL DEFAULT FALSE,
    promoter     BOOLEAN     NOT NULL DEFAULT FALSE,
    admin        BOOLEAN     NOT NULL DEFAULT FALSE,
    invited_by   UUID        NOT NULL REFERENCES users(id),
    invited_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (workspace_id, email),
    CONSTRAINT chk_workspace_invitation_admin_authors CHECK (NOT admin OR author),
    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 flags are the membership's flags, carried forward. The admin → author invariant is the same CHECK the members table states (§4.12), and the service normalises before writing for the same reason: a caller who ticks only "admin" gets the workspace admin they asked for.
One invitation per (workspace, email); a re-invite REPLACES the flags. 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 §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).

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 — 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). 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.

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). 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.

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": "<the single cell, wire-encoded>"} for value and range checks; {"rows": <count>} 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.

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 (<table>_pkey, <table>_<column>_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 user keys are the overwhelming majority (V11)
`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)
`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)
`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)
`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
`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)
`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)
`learned_facts`	`idx_learned_facts_workspace`	explicit (partial)	A workspace's own WORKSPACE facts; partial because DATASOURCE facts carry no workspace (§4.18)
`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)
`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)

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` row whose credential never leaves the deployment, and a `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` row and a not-yet-existing person, and materialises into `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` 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
`learned_facts`	environment-local	LearnedFact	—	—	A fact is about an environment-local `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` 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

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

@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<Pipeline> {
        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 INSERTs 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):

@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 — 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:

Instance A acquires the lock, runs the migration, releases.
Instance B waits for the lock, finds the migration already applied, skips.
No corruption, no double-execution.

8. Operational Jobs

Three scheduled jobs act on this schema. 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 (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

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.

This purges the durable 7-day record only. The 1-hour Redis event log (§9) expires on its own TTL and is not this job's concern.

8.2 Audit log retention

DELETE FROM audit_log
 WHERE timestamp < NOW() - make_interval(days => :auditRetentionDays);

:auditRetentionDays ← datapipelines.audit.retention-days.

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):

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. :instanceLostError is the standard error envelope with code pipeline.execution.instance_lost.

The heartbeat (108 §D, V21)

heartbeat_at is stamped by the instance that OWNS the row, every datapipelines.executor.heartbeat-seconds (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. A node that has started and not finished appears with status: "RUNNING" (Enums §9) 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.

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
Caller results (materialized rows + schema, served by the result cursor)	Redis	`DP-Result-TTL-Seconds`, clamped; fixed expiry	REST API §7
Post-completion replayable event log	Redis	1 hour, not configurable	REST API §10.3
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). 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-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): 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): 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. 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. 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, Enums §8A) — 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) — a released pipeline served as `GET /api/x/…`, 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) 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).
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). 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.

This is the documentation packaged with the running version. The same files live in the repository on GitHub.

Metadata Database Schema Specification

1. Purpose

2. Conventions

3. Entity Relationship Diagram

4. Table Definitions

4.1 users

4.2 api_keys

4.3 audit_log

4.4 pipelines

4.5 pipeline_versions

4.6 pipeline_executions

4.7 execution_events

4.8 templates

4.9 template_versions

4.10 datasources

4.11 workspaces

4.12 workspace_members

4.13 published_endpoints

4.14 endpoint_key_bindings

4.15 lake_tables

4.16 datasource_workspaces

4.17 workspace_invitations

4.18 learned_facts

4.19 mail_sends

4.20 pipeline_check_runs

5. Index Strategy Summary

5A. Promotion Classification

6. Data Access Pattern (NamedParameterJdbcTemplate)

6.1 Example: Pipeline repository

6.2 RowMapper convention

6.3 Transaction management

7. Flyway Migration

7.1 File structure

7.2 V1 migration

7.3 Migration safety in multi-instance deployment

8. Operational Jobs

8.1 Execution event cleanup

8.2 Audit log retention

8.3 Stale execution sweep

The heartbeat (108 §D, V21)

Live progress on the same row (108 §D)

9. What Is NOT In This Database

Appendix A: Change Log

4.1 `users`

4.2 `api_keys`

4.3 `audit_log`

4.4 `pipelines`

4.5 `pipeline_versions`

4.6 `pipeline_executions`

4.7 `execution_events`

4.8 `templates`

4.9 `template_versions`

4.10 `datasources`

4.11 `workspaces`

4.12 `workspace_members`

4.13 `published_endpoints`

4.14 `endpoint_key_bindings`

4.15 `lake_tables`

4.16 `datasource_workspaces`

4.17 `workspace_invitations`

4.18 `learned_facts`

4.19 `mail_sends`

4.20 `pipeline_check_runs`