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

| Table                | Disagreement                                                                                                                               |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `provenance`         | Prisma `outputId`/`jobId`/`generatedAt` vs service `output_id`/`job_id`/`generated_at`; foreign key to `generated_outputs` vs to `outputs` |
| `retention_policies` | primary key `id` vs primary key `name`                                                                                                     |
| `workflow_stars`     | zero column names in common across 4 Prisma columns and 6 service columns; surrogate `id` vs composite `(workflow_id, user_id)`            |
| `model_registry`     | 19 columns matching in concept under two different physical names                                                                          |

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.

| Record type      | System of record                              | Rule                |
| ---------------- | --------------------------------------------- | ------------------- |
| plan             | `service:workflows`                           | R3 field coverage   |
| run              | `service:jobs`                                | R3 field coverage   |
| stage            | `prisma:pipeline_state_phases`                | R3 field coverage   |
| output           | `service:outputs`                             | R3 field coverage   |
| gate verdict     | `prisma:pipeline_state_checkpoint_decisions`  | R1 live access path |
| provenance       | `service:provenance`                          | R3 field coverage   |
| release          | `service:workflow_promotion_states`           | R1 live access path |
| consent          | `iris:consent_records` (**another database**) | R4 external         |
| brief            | — **unowned**                                 | R5 gap              |
| variant          | — **unowned**                                 | R5 gap              |
| gate measurement | — **unowned**                                 | R5 gap              |
| rights           | — **unowned**, three declared-only claimants  | R1 no survivor      |

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

| Option                                                             | Why not chosen                                                                                                                                                                                            |
| ------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Declare Prisma the single authority and delete the service DDL** | 18 of 24 Prisma models have no call site; the service tables are the ones actually serving traffic. This would delete the working half.                                                                   |
| **Declare the services the single authority and delete Prisma**    | `pipeline_state_*` is genuinely Prisma-owned with a live repository, and the Prisma models carry fields the service tables lack (job tenancy, cost, queue state) that I6.2 needs to migrate, not discard. |
| **Leave both and document the overlap**                            | The overlap is not stable: `CREATE TABLE IF NOT EXISTS` means a fresh environment and a migrated one diverge silently, so the same code passes in one and fails in the other.                             |
| **Per-table authority + a ratchet (chosen)**                       | Keeps both halves where each is real, names one owner per record type, and makes the debt visible and non-growing while I6.2 unwinds it.                                                                  |

## 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

| Artifact                                                  | Covers                                |
| --------------------------------------------------------- | ------------------------------------- |
| `evidence/v1-workbenches/isis-persistence-inventory.json` | I6.1.a current state                  |
| `evidence/v1-workbenches/isis-aggregate-ownership.json`   | I6.1.b owner per record type          |
| `evidence/v1-workbenches/isis-derived-ownership.json`     | I6.1.c role split + data-flow diagram |
| `evidence/v1-workbenches/isis-consistency-seams.json`     | I6.1.d seven seams                    |
| `evidence/v1-workbenches/isis-shadow-table-gate.json`     | I6.1.e schema inventory check         |