ADR-I1: Isis Aggregate Ownership and the Prohibition on Shadow Canonical Tables

Status: Proposed Date: 2026-07-28 Authors: V1 Domain Workbenches persistence audit (Phase I, §I6.1) Reviewers: pending — Isis service owners (generation-api, output-registry, workflow-registry, ai-providers, operation-orchestrator) + data/platform architecture

This ADR records the owner per aggregate, the role split, the consistency decisions, and the prohibition that §I6.1 asks for. It is deliberately Proposed, not Accepted: ratification by the named service owners (I6.1.e) is a human governance step and remains open. Every quantitative claim below is drawn from deterministic evidence in evidence/v1-workbenches/, regenerated by pnpm inventory:v1-workbenches and drift-gated in CI.

Context#

Isis persists through a Postgres database reached by ISIS_DATABASE_URL ?? DATABASE_URL. An earlier inventory (I6.1.a) counted 24 Prisma aggregates from libs/isis/database/prisma/schema.prisma. That count is correct and the picture it gives is not.

schema.prisma is not the whole database. Two independent schema authorities are pointed at the same connection string:

1. Prisma — 24 models, DDL in prisma/generated/schema.sql plus the migrations under prisma/migrations/.
2. Service DDL — 23 tables created by CREATE TABLE IF NOT EXISTS executed by generation-api, output-registry, workflow-registry and ai-providers, plus the workflow-registry SQL migrations.

39 physical tables. Evidence: isis-aggregate-ownership.json.

The problem this ADR exists to stop#

Eight table names are declared by both authorities: lineage_edges, model_registry, provenance, retention_policies, workflows, workflow_versions, workflow_templates, workflow_stars.

CREATE TABLE IF NOT EXISTS is a silent no-op against whichever authority ran first, so one of the two writers is left querying columns that do not exist. The definitions do not agree:

Two further measurements shape everything else:

• 18 of 24 Prisma models have no call site anywhere in non-test Isis code. The only Prisma family with a live access path is pipeline_state_*, reached through one repository — and it is also the only family missing from the committed generated/schema.sql. @isis/database's exported PrismaClient interface has no model delegates at all.
• 10 independent connection pools target the one database (9 createPostgresClientFromUrl sites plus a Prisma client). A transaction belongs to a pool. Evidence: isis-consistency-seams.json.

Decision 1 — one system of record per record type#

Assigned by a recorded rule ladder, not by preference (I6.1.b). R1: a store nothing reads or writes owns nothing. R2: cascade-durability, as a filter, for records that must outlive their producer. R3: the live store that can hold most of the record's field vocabulary. R4: an owner in another domain's database. R5: unowned — a gap, not a choice.

The costs of these choices are recorded rather than hidden. service:jobs wins the run record while carrying no tenancy column at all — no user, owner, organization, tenant or project — losing userId, organizationId, projectId and eight other fields the declared-only generation_jobs already models. Adding them back is I6.2 work. Consent is referenced from two Isis tables by consent_id with no foreign key, because the owner is in a different database; the oshun copy is a projection, not a second owner (it carries sourceRecordId and payloadHash).

Decision 2 — derived roles are owned separately, or the fact that they are not is recorded#

Evidence: isis-derived-ownership.json (I6.1.c). Of the six roles the ledger names, three are owned by a separate store (audit, outbox, read model) and two are carried inside the canonical row: derived index/cache (stats_runs, stats_stars, total_versions, access_count, a searchVector) and analytics (durationMs). A rollup living in the row it summarizes cannot be rebuilt or invalidated without writing to the system of record.

Blob is scored pointer-against-payload: storage_key in a canonical row is correct and is not a defect. The one real case is pipeline_state_phase_artifacts.inlineData, which keeps payload in the row.

Coverage is the part that matters. Seven audit stores exist, yet four of seven owners have no audit trail reachable from their table. There is exactly one outbox, covering one owner, so six systems of record mutate with no transactional publication path. I6.4 builds that; it does not extend it.

Decision 3 — cross-authority invariants are eventually consistent, and say so#

A transaction belongs to a pool, so ten of the resolved owner pairs cannot be written atomically together however much they share a database. Where an invariant must hold across owners it is published through the outbox and reconciled — never asserted by a shared transaction.

Within a pool, the transaction handle stops being optional. recordAuditLog(entry, executor = this.db!) currently writes output_audit_logs through an executor that defaults to the non-transactional client, so a caller who forgets the handle gets an audit row that survives the rollback of what it was recording. The executor becomes a required parameter, so omitting it fails to compile rather than failing to roll back.

The remaining seam decisions — one identity per table, a version column on every owner, one tenancy column name present everywhere, deletion as a state rather than an absence, one declaring authority per table in a committed migration, an explicit terminal-or-retryable status, and recovery as a property of the record — are recorded per seam in isis-consistency-seams.json (I6.1.d) with the 13 unresolved conflicts they answer.

Decision 4 — the prohibition, and how it is enforced#

A physical table has exactly one schema authority and exactly one canonical writer. A second authority declaring the same table name is a shadow canonical table and is prohibited, whether or not the two definitions currently agree — CREATE TABLE IF NOT EXISTS makes disagreement silent. A table created by a running service rather than a committed migration is prohibited for the same reason: it cannot express a change and it races every other process on the database. A system of record with no live access path is prohibited because something else is already serving its reads.

A prohibition nobody can enforce is a sentence in a document. The enforcement is scripts/v1-workbenches/isis-shadow-table-gate.mjs, wired into verify:inventory:v1-workbenches and CI. It is a two-way ratchet over the committed inventory:

• a new dual-authority table fails the build;
• a new runtime-DDL table fails the build;
• a system of record that loses its live access path fails the build;
• paying down a baseline entry without tightening the list also fails, so the ceiling cannot quietly become permission.

The eight dual-authority tables and thirteen runtime-DDL tables are recorded as measured debt, not as a target. Every entry is expected to leave. The gate cannot delete them — that is I6.2's expand/backfill/verify/contract work — but it stops the number growing while that happens.

Considered options#

Consequences#

• I6.2's schema work has a concrete input: four unowned record types (brief, variant, gate measurement, rights), the eleven fields the run owner lacks, a version column per owner, and a single tenancy column name.
• I6.4's outbox work covers six owners, not one.
• New tables must arrive through a committed migration under a single authority, or CI fails.
• Cross-owner invariants are written as reconciliation, not as transactions.

Open (human) decisions#

• Ratification by the named service owners (I6.1.e). Until then this ADR is agent-authored and Proposed; the gate is nevertheless enforcing, because the prohibition it encodes is a restatement of what the evidence already shows breaking.
• Whether pipeline_state_* moves onto the service pool, or the services move onto Prisma, is a migration decision this ADR deliberately does not make — it records that the split exists and that no transaction spans it.

Evidence#