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:

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:

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#

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:

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.