# Shared Persistence Conventions **Status**: Adopted for new shared workbench entities (agent-recorded) — data-platform and security ownership approval **pending** (§10) **Date**: 2026-08-02 **Ledger**: `V1_DOMAIN_WORKBENCHES_TODOS_2026-07-23.md` §S3.1 > Normative for every entity introduced by Phase S3 and after. Descriptive, not > retroactive, for what already exists — see §9, which says exactly how far the > tree is from this document rather than implying it conforms. > > Machine-checked companion: > `evidence/v1-workbenches/persistence-conformance.json`, produced by > `scripts/v1-workbenches/generate-persistence-conformance.mjs`. ## 0. What this document is for, and the state it was written in The workbench platform is about to acquire shared entities that several domains read and write: revisions, gate runs, waivers, signoffs, staleness marks, audit events. Those are the rows an incident review reads. A convention that arrives after them arrives after the decisions that matter. The tree it lands in is not uniform, and the document is worth nothing if it pretends otherwise. As of this writing: | Fact | Count | | --------------------------------------------- | ----- | | Prisma schemas | 16 | | Drizzle configs | 11 | | Models across the Prisma schemas | 439 | | Models carrying a tenant or workspace column | 129 | | Models carrying a creation instant | 324 | | Prisma schemas with a `migrations/` directory | 13/16 | | Domains carrying **both** schema languages | 3 | The last row is the one to read twice. A domain with two schema languages has two migration stories, two type generators, and two answers to every rule below. This document does not resolve that; it names it, because a convention that silently assumes one dialect is a convention that is wrong in three domains and does not know it. --- ## 1. Data model (§S3.1.a) **Naming.** Entities are singular `PascalCase` in the schema and map to `snake_case` plural tables. A table name is a fact in a query plan and a log line; it is not a place for a domain prefix, because the schema is already namespaced by database. **Identity.** Every row has one primary key, and it is opaque. The kit's `@oshun/workbench-kit/identity` types are the shared vocabulary (`TenantId`, `WorkbenchId`, `EntityId`, `RevisionId`, …) — namespaced strings with a strict parser, not bare UUIDs, so `idEquals(workbench, tenant)` is a compile error rather than a comparison that returns false. Sequential integer keys are prohibited on anything a client can see: a number that reveals how many rows exist is a fact about the business shipped in a URL. **Scope.** Every row carries the scope it is isolated by — `tenantId` always, plus `workspaceId` where the entity is workspace-owned. Both are columns, never derived at read time through a join, because an isolation rule enforced by a join is one a missing join disables. A **global reference table** may omit them, and must say so in a schema comment naming this section; an undeclared omission is indistinguishable from a forgotten one. **Actor and revision metadata.** Every mutable entity carries `createdAt`, `createdById`, `updatedAt`, `updatedById`. Append-only entities carry only the first two and are documented as append-only. `updatedById` is a column rather than a join to an audit table: an audit trail that has to join to answer "who" stops answering when the join breaks, and it breaks exactly when somebody is looking. **Tombstones.** Soft deletion is `deletedAt` **and** `deletedById` **and** `deletionReason`. A `deletedAt` alone is a row nobody can attribute and nobody can restore with confidence. §S3.3 governs the lifecycle; this section governs the columns. **Relationships.** Foreign keys are declared and enforced in the database. `ON DELETE CASCADE` is prohibited on anything carrying history: a cascade deletes an audit trail to keep a pointer tidy. Use `RESTRICT` and delete deliberately. **Extension fields.** A domain may add columns to a shared entity only through an owned side table keyed by the shared row's id. Adding a column to a shared table for one domain makes every other domain's migration wait on that domain. --- ## 2. Columns versus payloads (§S3.1.b) A field needs an **explicit typed column** when any of these is true: 1. a query filters, sorts, joins or aggregates on it; 2. a constraint depends on it (uniqueness, a check, a foreign key); 3. an authorization decision reads it; 4. it is part of a lifecycle state, a version, or an identity; 5. a retention, legal-hold or purge rule computes from it; 6. an operator would grep for it during an incident. JSON and blob payloads are permitted for exactly three things: - **opaque third-party responses**, stored verbatim for provenance, never read by application logic; - **schema-versioned document content** whose shape belongs to a domain plugin rather than to the platform, carrying its own `schemaVersion` column beside it; - **denormalised read-model projections**, which are rebuildable and therefore disposable (§S2.14 — a projection that cannot be thrown away is not a projection). Everything else in a payload is a field somebody will need to query within a year, and the migration that extracts it then is strictly more expensive than the column that would have been declared now. The scanner's `S3.1.b/json` rule catches only the crudest version of this failure — a model that is majority payload — and passes 439/439 today; whether a given payload is genuinely opaque is a review obligation (§11), not a machine decision. --- ## 3. Temporal semantics (§S3.1.c) **Instants** are `DateTime` columns stored as `timestamptz`, always UTC. They are never `String`: a timestamp in a text column sorts as text, and two spellings of the same instant both exist in the table. **Serialisation** at every boundary is ISO-8601 with a literal `Z`, second or millisecond precision — the kit's `Instant` shape. Offsets and local times are refused by the parser, so that **string order is time order**: every ordered log and every keyset pagination cursor depends on it. **Local dates and times** — a publication date in an editorial calendar, an embargo local to a region — are a **different type**, stored as a `date` or `time` column plus a separate IANA time-zone column. A local date coerced to an instant is a date that moves when the server does. **Durations** are integer columns with the unit in the name (`maxAgeSeconds`, `respondWithinHours`). A bare `duration` is a unit argument waiting to happen. **Clocks.** Every timestamp written by the platform comes from the database (`now()` in the transaction) or from a single injected clock, never from an application server's wall clock. §S2.14 makes the reason concrete: two writers a hundred milliseconds apart on skewed clocks produce an audit trail that reorders itself, and both orders read as plausible — which is why ordering there comes from a sequence and the timestamp is data. **Precision** is declared, not inherited. `timestamptz(3)` where milliseconds matter, `timestamptz(0)` where they do not; a column whose precision nobody chose is one whose round-trip equality nobody can predict. --- ## 4. Integrity and performance (§S3.1.d) **Constraints belong in the database.** Application-level checks run where somebody remembered to call them; a migration, a support script and a bulk importer all reach the table directly. The kit already proves this for its own invariants against a real engine (`pnpm nx history-constraints`, `pnpm nx outbox-atomicity`), and those harnesses are the template: express the constraint in DDL, then prove the engine refuses the broken row. **Uniqueness** that matters is a `UNIQUE` constraint, not a `SELECT` before an `INSERT`. A read-then-write uniqueness check is decided by concurrency, and the kit's revision race harness exists because that lesson cost a run. **Indexes** are declared with the query they serve named in a comment. An index with no named query is one nobody can remove. **Pagination** is keyset, ordered by `(sortColumn, id)` with the id as the tie break. Offset pagination over a table that receives writes returns duplicates and skips rows, silently, and only under load. **Query plans** are reviewed for any query on a table expected to exceed a million rows, and the plan is attached to the change. "It is fast on my machine" is a statement about the row count on that machine. **Row-level authorization** is enforced by the scope columns in §1 and by a query layer that cannot construct a query without them. Enforcement that depends on every call site remembering a `where` clause is enforcement that has already failed somewhere in the codebase. **Encryption and sensitive fields.** Secrets are never columns: they are references to a secret store. Personal data that must be stored is stored in named columns so that a retention rule and an erasure request can find it — a personal identifier inside a JSON payload is one no erasure request will reach. Free-text columns that cross a boundary are scanned with the kit's S1.10 detectors (`scanText`), which is what §S2.14 does for audit details. --- ## 5. Migrations (§S3.1.e) Every schema change is **expand → backfill → verify → contract**, as four separate deployable steps: 1. **Expand** — add the new column, table or index, nullable and unused. Old and new code both run against it. 2. **Backfill** — populate in bounded batches, resumable, with progress recorded. A backfill that cannot be resumed is one that cannot be run twice, and it will be. 3. **Verify** — a query that proves the backfill is complete and correct, run against production data, with its output attached to the change. 4. **Contract** — remove the old column or constraint, in a **later deploy** than the one that stopped writing to it. **Compatibility.** Every intermediate state must be one where the previously deployed application version still works, because that is the state a rollback lands in. A migration that is only correct after the deploy completes is a migration that cannot be rolled back. **Destructive steps** (`DROP COLUMN`, `DROP TABLE`, a narrowing type change) ship alone, in their own deploy, after the contract step's predecessor has been in production long enough to be confident. There is no compensating transaction for a dropped column; there is a restore from backup and an outage. **Rehearsal.** Any migration touching more than a million rows is rehearsed against a production-sized copy, and the wall-clock time is recorded in the change. Lock duration is the number that matters, not row count. **Evidence.** A migration is not complete until its verify query output, its rehearsal timing, and its rollback plan are attached. `13/16` Prisma schemas have a migrations directory today; the three without are the ones where none of this is currently possible, and they are the first to fix. --- ## 6. Generated clients (§S3.1.f) **The schema owns the types.** Generated clients and models are derived artifacts: `prisma generate` and `drizzle-kit generate` produce them, and the commands live in the project's targets so nobody has to remember the flags. **Generated output is checked in** — 460 generated files are tracked today, and that is the right call for a monorepo where a consumer must be able to typecheck without running a code generator first. It comes with one obligation, which is the whole of the rest of this section. **Hand-editing generated output is prohibited.** Not discouraged — prohibited. A hand edit survives exactly until the next regeneration, and it fails silently at that moment, in a diff nobody reads. Generated directories carry a header saying so. **Drift is checked, not assumed.** A CI step regenerates and fails on a diff. A checked-in artifact with no drift check is a cache with no invalidation. **Version skew.** The generator version is pinned in the pnpm catalog, and the client and CLI versions must match — a client generated by a newer CLI against an older runtime fails at the first query, in production, with an error message about a field. --- ## 7. Verification (§S3.1.g) The following are **required against a real PostgreSQL**, not a mock, not an in-memory substitute: | What | Why a fake cannot answer it | | ----------------------- | -------------------------------------------------------------------------------- | | unit-of-work boundaries | "these two writes commit together or neither commits" is a property of an engine | | concurrency | a unique constraint is only interesting under two real connections | | constraints | a `CHECK` a fake accepts is a `CHECK` that does not exist | | migrations | expand/backfill/contract has to be run forward and back on real data | | query plans | a plan is produced by a planner with statistics | | isolation levels | phantom reads and serialization failures are engine behaviour | | backup and restore | a restore nobody has performed is a hope | | compatibility | the previous application version against the new schema, both running | The kit already ships four such harnesses (`revision-race`, `history-constraints`, `restore-transaction`, `outbox-atomicity`), and they share a discipline worth restating as a rule: **a harness must contain a negative control that fails.** `outbox-atomicity` injects the same failure twice — once inside one transaction, where both tables must come back unchanged, and once across two, where the control **must** leave an orphaned row. A harness whose control does not break has proved that it ran, not that it can fail. They also **fail rather than skip** when no database is reachable. A skipped check that reports success is the evidence shape this ledger refuses. --- ## 8. Applicability to shared entities | Shared entity (phase) | Owner scope | Append-only | Soft delete | Notes | | ------------------------------- | ------------------ | ----------- | ------------------ | ----------------------------------------------- | | Revision history (S2.4) | tenant + workbench | yes | tombstone only | immutability enforced by trigger, not by code | | Gate definition (S2.9) | tenant | no | retire, not delete | retirement names a replacement | | Gate run result (S2.10) | tenant | yes | no | one result per gate per revision, enforced | | Waiver grant (S2.11) | tenant | yes | never | a revoked waiver stays readable | | Staleness mark (S2.12) | tenant | yes | no | cleared by a recompute, not by a delete | | Dependency change event (S2.12) | tenant | yes | never | per-node gapless sequence, `UNIQUE (node, seq)` | | Signoff record (S2.13) | tenant | yes | never | superseded, never updated | | Audit event (S2.14) | tenant | yes | never | per-object gapless sequence | | Audit projection (S2.14) | tenant | no | rebuildable | disposable by definition; may be truncated | Every row in that table except the last is append-only or effectively so, which is the single largest difference between these entities and most of the 439 already in the tree. --- ## 9. Where the tree stands against this document `evidence/v1-workbenches/persistence-conformance.json` is the measured baseline. The headline numbers, out of 439 models: | Rule | Conforming | | ----------------------------------------- | ---------- | | has a primary key | 399 | | carries a tenant or workspace scope | 129 | | records a creation instant | 324 | | records who last wrote it (if mutable) | 273 | | temporal columns are `DateTime`, not text | 435 | | is not majority JSON payload | 439 | | soft-delete records a requester | 400 | **This is not a to-do list and it is not an embarrassment.** These models were written before this document existed, mostly for entities that are not shared, and rewriting 310 of them to carry a tenant column would be a large change with no user visible in it. What the baseline is for is the **ratchet**: `--ratchet` fails when a schema's conforming count goes DOWN, which is the only enforcement that works on a tree this size. New shared entities conform; existing ones do not get worse. --- ## 10. Approval (§S3.1.h) This document is **agent-recorded** and is **not yet ratified**. Two named approvals are outstanding and neither can be given by an agent: - **Data platform ownership** — §1, §2, §5 and §6 commit the platform to an identity scheme, a payload policy, a four-step migration process and a checked-in generated-artifact policy with a drift gate. - **Security ownership** — §1's scope columns, §4's row-level authorization, encryption and sensitive-field rules, and §7's verification list are the controls a security review would sign. Until both are recorded with a name, a date and a review date, the corresponding ledger item (`S3.1.h`) stays unchecked. An agent may prepare the packet; it may not approve it. --- ## 11. What this document says and the scanner cannot check Written down so the gap is visible rather than assumed: - whether a JSON payload is **genuinely opaque** (§2) — a machine sees a `Json` column, not whether application logic reads inside it; - whether an index **matches the query that needs it** (§4); - whether a migration's **verify query actually proves** the backfill (§5); - whether a **rehearsal** happened and what it measured (§5); - whether the **negative control in a harness genuinely fails** (§7) — checked by breaking it by hand, once, per harness; - whether a **global reference table's** omission of scope columns was declared or forgotten (§1) — the declaration is a comment, and a comment is prose. Each is a review obligation. Listing them is not a substitute for automating them; it is a statement about which parts of this document are currently enforced by a person.