---
name: schema-design
description: "Use when designing or altering a relational schema — a new table, column, index, junction, migration, or seed — or reviewing one. Owns the design doctrine: what earns a table, where a fact lives (state column vs. sequence log vs. audit log), scope columns over parallel tables, junction = M:N only, FK policy including append-only tables, uniqueness with nullable columns, vocabulary tables, copying rows between scopes. Also owns seeding doctrine: authored-source → compiled-artifact → load pipeline, choosing the authoring format by payload (CSV vs. JSON/YAML vs. markdown body), validation at the compile boundary, and the canonicalize-never-repair rule for ingesting scraped data. Naming and column types stay in the `database.md` rule. Triggers on 'new table', 'schema', 'migration', 'add a column', 'junction table', 'foreign key', 'audit log', 'versioning', 'seed', 'seeder', 'scraper ingestion'."
---

# Schema Design — the doctrine

You decide **what earns a table and where a fact lives** — for every Nurix relational schema, engine-agnostic (written for SQLite / Cloudflare D1 + Drizzle; a Postgres project keeps the same names and semantics with native types). Naming, the `id`/`slug`/`label` vocabulary, and column types are the `database.md` rule and are never restated here — this skill owns the design arguments consulted at schema-change time.

Apply the sections below to the change in hand; when reviewing, report violations with the section that names them.

## What earns a table

A table is the most expensive home for data — a migration to change its shape, a join to read, a mirror to keep in sync. Default to **not** a table — a concept earns one only with at least one property in the left column.

| A table is earned by…                                                      | Lacking all of these, the data belongs in…              |
| -------------------------------------------------------------------------- | ------------------------------------------------------- |
| identity minted at **runtime** — a session, an upload                      | —                                                       |
| variation **per tenant / user** — a workspace's theme                      | —                                                       |
| **independent addressing** — queried, indexed, or FK'd in isolation        | a **JSON column** on its parent row                     |
| **concurrency-safe** writes — racing writers must not clobber a shared blob | —                                                       |
| a **DB-enforced invariant** — immutability trigger, unique, atomic boundary | —                                                       |
| dev-authored, published on deploy, identical for every tenant — plan tiers | **config / a constants file**, validated at build       |
| exactly recomputable from other rows — a tenant's usage total              | a **cache** (KV / edge), recomputed on demand           |
| process state dead once the request completes — a retry count             | a **log or workflow engine**, never the domain DB       |
| owned by another system-of-record — the payment processor's invoice        | a **reference column** — store the id, not the data     |

Each right-column route is one test: process state — would a product feature break if this vanished the instant the request finished?; derived output — exactly recomputable from other rows + code?; un-addressed detail — is the row ever `SELECT`ed / `JOIN`ed / FK'd alone?; another system's record — does a specialized service (chat, telemetry, object store) already own it?

- **Static vocabulary or a fixed graph → config, not a table** — HTTP status labels, a fixed onboarding funnel. Surface it in code as a constant/type; reifying it into tables plus a seed makes the file and the tables a two-copy sync problem. The refined boundary is § Vocabulary / lookup tables.
- **History → one immutable log + a mutable head.** Never shatter versioning into per-entity `*_revisions` / `*_revision_sections` tables — snapshot the coherent set into one append-only versions table; current state is a projection of the newest entry. A table that is just "old rows of another table" collapses into the snapshot.
- **A surviving table names the invariant it buys** — content-addressed immutable rows buy reproducibility, a monotonic sequence buys ordering/sync, an atomic write boundary buys multi-row atomicity. Drop the requirement and the table drops with it.

Table count tracks **requirements, not domain richness** — a rich domain is not entitled to a table per noun.

## State, rules, and history

**One fact has one home, chosen by what kind of fact it is.** Confusing these produces the most common redundant table in a schema — a queue whose rows restate state the entity already carries.

| The fact | Home | Example |
| --- | --- | --- |
| What the entity **is now** | a **column on the entity's own row** | `documents.state = 'blocked'` |
| What may be **done** about that state | **code** — the transition map, the UI's affordances | which inputs unblock a document |
| What **was done**, and by whom | the **append-only audit log** | an `audit_log` row: `document.override_applied` |
| What a consumer must **replay in order** | the **sequence log** | an `app_events` row keyed `(app_id, sequence)` |

- **State becomes a column the moment anything selects on it.** `WHERE state = 'blocked'` takes an index; `WHERE json_extract(metadata, '$.state') = 'blocked'` scans the table and is invisible to every schema check. JSON keeps only what § What earns a table leaves it — detail nothing addresses alone.
- **A pending-work table mirroring row state is the anti-pattern** — `*_actions`, `*_inbox`, `*_flags`. The tell: resolving one of its rows also updates the entity, so two rows answer one question and are free to disagree. The *open question* is a column beside the state (at most one per row ⇒ a column, § A junction spells many-to-many); the *answer given* is an audit-log row.
- **Legal transitions live in code, never as rows** — a `status_transitions` table is a state machine the database cannot enforce and the code must re-implement anyway to use.

**The two logs are not substitutes**, and no one table serves both:

| | Sequence log | Audit log |
| --- | --- | --- |
| Answers | what happened here, **in order** | who did what, **and whether it succeeded** |
| Ordinal | a **gapless per-entity `sequence`**, allocated from a counter | none — nothing replays it |
| Scope | one entity, always | any — **including none**: a sign-up predates every tenant |
| Records | only what **happened** | **attempts too** — `denied`, `failure` |
| Read by | cursors replaying forward | investigations, at any age |

The bottom two rows are why neither can hold the other's rows: an event at a scope with no entity has no sequence to take, and a refused attempt in a replay stream would have consumers apply something that never happened.

### The audit log's own shape

**An audit log outlives every row it names, so it stores what it needs to stay readable without them.** Both consequences are missing from most first drafts:

- **Denormalize the display at write time** — `actor_label`, `target_label` beside `actor_id` / `target_id`. Ids alone leave the log intact but unreadable once the person or record is deleted, which is the one thing it exists to survive.
- **Record the attempt, not the success** — `outcome` (`success | failure | denied`) plus a `reason`. Refused sign-ins and permission denials are most of what anyone opens an audit log for; without the column they cannot be written at all.
- **Keep the columns product-neutral** — `actor_*` · `action` · `outcome` · `target_*` · `scope_path` · `channel` · `source_ip` · `correlation_id` · `changes_json` serve any domain. Per-level tenancy columns tie the log to today's model; a materialized `scope_path` (`workspace:abc/app:def`) makes "everything under this tenant" one prefix scan and survives a new level appearing — at the price of enforcing the hierarchy in code rather than a `CHECK`.

## Scope, not parallel tables

One entity that exists at several scopes is **one table with a nullable scope column** — never one table per scope, and never one table shared across entity families.

- **A table shared by several families carries the union of their columns** — one `settings` table for users, apps, and workspaces. Almost nothing can be `NOT NULL`, the payload lands in JSON beyond every constraint, index, and FK, and a new family widens a table other features already depend on. One table per family.
- **Two tables declared to have "the same columns" are two schemas maintained by hand** — the drift is silent, surfacing as an incomplete copy, never as an error. Collapse them and discriminate by column.
- **The scope column is the discriminator** (`app_id IS NULL` = parent scope, set = child scope). Copying a scope is then `INSERT … SELECT` over one table with one column rewritten, so source and target cannot disagree about shape.
- **A nullable owner column is how a row exists before its owner does** — a pending enrolment or unclaimed record is creatable before the owner is known, which is what lets a `pending_*` / `*_requests` table collapse into the real one.
- **A reserved row can own the shipped defaults.** A fixed-id "system" parent makes the defaults and a tenant's copy the same kind of row, through the same code. It must then be excluded from every tenant-scoped query — state that invariant where the id is defined.

## Copying rows between scopes

- **A copied junction row is already correct with no remapping** — its endpoints are `slug` references (§ Keys & the shared vocabulary), and a slug means the same thing in every scope. Rewriting ids through a source→copy map is the most error-prone step in a multi-level copy, and a partial failure leaves wiring pointed into another scope rather than raising.
- **Provenance is a soft reference — no FK.** `source_id` must outlive its referent; an FK would either block the source's deletion or erase the lineage.
- **Store the source's version at copy time** — `version` + `source_version` make "is an upgrade available" a comparison of two stored values, not a content diff.
- **A copy is a row, never a live read.** Nothing at the child scope reads the parent's row at runtime; propagation happens only through an explicit re-copy, so a local edit is never silently overwritten.

## A junction spells many-to-many — nothing else

**A `_map` table is the schema's notation for M:N. Using it for 1:M is a category error** — the junction's own uniqueness admits many parents per child, so the "one parent" rule stops being enforced the moment it is written this way. Cardinality decides between the only two shapes:

| Relationship | Shape |
| --- | --- |
| **1:M** — the child has exactly one parent | a **reference column on the child** (`agents.routing_slug`), plus an index |
| **M:N** — both sides have many | a **`<a>_<b>_map` junction** with a composite unique |

- **The test is one question about the child**: can this row ever belong to two parents at once? No ⇒ column. A junction "in case it becomes M:N later" ships the unenforced model today to buy a migration you may never run.
- **A junction for a 1:M also** makes the association a row that can go missing independently of the child — a child can exist correctly while its wiring does not — and forces a join onto the most common read in the system.
- **A junction carrying only its two endpoints and timestamps is the tell** — a real M:N junction usually earns its own columns (a role, an order, a status); one that never does is a 1:M waiting to be collapsed into a column.
- **Collapsing deletes a copy step** — the association travels inside the row it belongs to: no second pass to remap, no copy landing with its wiring half-applied.
- **A pair already recorded elsewhere needs no table at all.** If an append-only log stores `(parent, child)` on every write, the association table is `SELECT DISTINCT` over that log — a cache free to disagree with the log it derives from (§ What earns a table).

## Foreign keys into and out of append-only tables

**An append-only table can afford no `ON DELETE` action at all, so its references to mutable tables are soft — no FK.** Every option is closed to it:

| Action | Why it fails on an append-only table |
| --- | --- |
| `SET NULL` | It is an **UPDATE**. An append-only trigger aborts it, so the parent's deletion fails — with an error naming the trigger, not the constraint. |
| `CASCADE` | Deletes history to delete a person. The log exists precisely to outlive its subjects. |
| `NO ACTION` | Makes anyone who ever appeared in the log undeletable — the hub defect above. |

- **`SET NULL` is the trap** — it reads like a safe relaxation of `NO ACTION` and passes review as one.
- **Record the actor as a plain column** — the same soft-reference treatment provenance gets (`source_id`, `thread_id`). The log keeps naming who acted after the account is gone, which is what an audit trail is for.
- **`CASCADE` from the append-only side's own parent is still fine** — a cascade is a DELETE, so a `BEFORE UPDATE` trigger never sees it: an App's log dying with the App is legitimate, distinct from erasing a log to delete a person.
- **State the exemption where it exists.** A catalog whose rows must not disappear under a referencing row (a service definition, a vocabulary) keeps `NO ACTION` deliberately — that guarantee is worth one line saying so.

## Uniqueness with a nullable column

**`UNIQUE (a, b)` does not constrain rows where `b` is `NULL`** — SQL treats every `NULL` as distinct, so a composite unique over a nullable discriminator silently permits exactly the duplicates it was introduced to keep apart. Write one partial index per scope instead:

```sql
CREATE UNIQUE INDEX <t>_parent_slug_unique ON <t> (parent_id, slug) WHERE child_id IS NULL;
CREATE UNIQUE INDEX <t>_child_slug_unique  ON <t> (child_id, slug)  WHERE child_id IS NOT NULL;
```

**Prove a uniqueness claim by inserting the duplicate it forbids** — a schema review cannot tell a firing index from a silent one by reading.

## Vocabulary / lookup tables

A controlled vocabulary earns a **table** only when a user row FKs into it and reads its `label` / `description` at runtime — a service catalog rendered in a picker; static dev-authored config nobody references by FK stays **config** (§ What earns a table) — a `doc_type` value list. Enumerable is not tabular, and a `type`-style column validated against the constants file beats an FK to a seeded mirror. An earned table has a fixed shape: `id` (UUID) · `slug` (UNIQUE) · `label` · optional `description` · `status` · timestamps.

- **Seeds are generated, never hand-written** — the source of truth is an authored tree (§ Seeding) or a shared package; a generator emits the `*.seed.sql` with UUIDv5 ids and an idempotent `ON CONFLICT(slug)` upsert that preserves live counts / admin-tuned columns. ❌ Hand-editing seed SQL — regenerate it.

## Seeding — authored source, compiled artifact, loaded rows

Curated data that ships with the product — vocabularies, catalogs, reference profiles — is **authored in a tree, compiled to SQL, and loaded**. The compiled artifact is build output, not source.

```
seed/<data>/**        authored source, COMMITTED — the only thing a human edits
      ↓ compile       mechanical: parse, validate, emit. No judgment, no network
seed/sql/*.seed.sql   build output, GITIGNORED — rebuilt on every run
      ↓ load          the engine's own client (wrangler d1 execute, psql -f)
```

- **Never commit the compiled artifact.** Rebuilt seconds before it is applied, it cannot go stale — so nothing checks it for drift and no freshness test is owed. A committed `.sql` needs that check; a gitignored one needs nothing. ❌ Hand-editing compiled SQL — regenerate.
- **The load is idempotent**: UUIDv5 ids derived from the natural key plus `ON CONFLICT(slug) DO UPDATE`, preserving live counts and admin-tuned columns. Re-seeding is a no-op, never a duplicate.
- **A build step that reads the artifact runs the compiler first.** Bundlers resolve imports at *build* time, so a gitignored artifact breaks a fresh clone with a resolution error rather than a test failure — wire `pretest` / `prebuild`.

### Choosing the authoring format

**Choose by what the payload is, not by where it lands.** That the destination is a relational table argues nothing about the source: rows routinely carry document-valued columns, and the compiler exists precisely to bridge the two shapes. The litmus is one question per field — **is this a scalar a human scans in a table, or prose a human reads in paragraphs?**

| Payload | Format | Why |
| --- | --- | --- |
| Flat, uniform records — one scalar or short list per cell | **CSV** | Reviews as a table; the diff is one line per record |
| Nested structures — list-of-objects, maps of maps | **JSON / YAML block** | The only formats that express nesting at all |
| Authored prose that is itself read or served | **Markdown body** + frontmatter, with a fenced block for the structured half | Renders, diffs by line, survives review |

- **Mixed is normal — never force one format on a heterogeneous corpus.** A flat `index.csv` of per-entry metadata beside one `.md` per entry carrying the body is the *correct* shape when the corpus has both halves, not a failure to standardize.
- **One row per file** when bodies are large and reviewed as prose; **one file of many rows** when records are flat and reviewed as a table.
- **CSV cannot hold multi-line text.** Quoting a paragraph into a cell yields an undiffable blob and breaks most naive parsers, which split on newlines *before* parsing quotes. Prose in a CSV cell is a defect, not a tradeoff.
- **Check the delimiter against the data before choosing CSV.** A corpus whose values contain commas (`oklch(0.8 0.16 84 / 0.3)`) pays quoting tax on every row.

### Rigidity comes from validators, never from containers

**No file format enforces a shape.** CSV checks no types, no required fields, no nesting — not even column count. YAML and JSON check only that the syntax parses. Every format is a container; the shape is enforced by a schema or not at all, so a format migration undertaken to gain rigidity buys none.

- **Validate at the compile boundary and fail the build**, naming the file and the reason. A compiler that checks only *parseability* ships a typo'd key or a missing map straight to production, where it is served with full authority.
- **Declare the shape as a schema and share it** between the compiler that writes the column and the code that reads it — one definition, so compile-time and read-time cannot disagree. A `values_json`-style column is opaque to the database; that schema is the only thing standing between it and `unknown` at the read site.
- **Types are code, data is seed.** The shape lives in source and is reviewed as code; the content lives in the authored tree and is reviewed as data. Moving content into source (a 1,700-line constant) puts data behind a deploy; moving shape into the tree puts the contract beyond the typechecker's reach.
- **Distrust forgiving parsers.** Padding a short row with empty strings, or defaulting a missing field, converts a malformed record into a plausible wrong one — the exact failure a validator exists to prevent.

### Canonicalize, never repair

Two operations get called "cleaning the data" and only one is safe:

| Operation | Verdict | Why |
| --- | --- | --- |
| **Canonicalization** — deterministic and information-preserving: slug folding, diacritic normalization, key and unit ordering | ✅ belongs in the compiler | The same input always yields the same output, and nothing is invented |
| **Repair** — filling, clamping, defaulting, or dropping content that failed validation | ❌ reject loudly instead | Deciding what the author meant *is* judgment; a repaired row is then served indistinguishably from a curated one |

**Correction cannot be both unintelligent and correct.** A pipeline that guesses at malformed input has swapped a loud rejection for a quiet wrong answer — and for curated data, whose whole value is that a human judged it, that is corruption wearing the name of automation. Reject-with-report is what scales: the producer retries, or a human triages.

### Ingesting scraped or generated data

Automated sources produce **candidate raw material, not rows.** Publish the compile-boundary schema as the ingestion contract and run scraped output through it unchanged — validate, reject loudly on mismatch, queue the survivors for curation. A curator promotes a candidate by adding the judgment the source could not supply: the rationale, the caveat, the editorial line. A pipeline that lets unreviewed extracts land directly as catalog rows has quietly swapped a curated corpus for a scraped one.

## Run

1. **Route every new fact through § What earns a table** — default to *not* a table; name the left-column property that earns one, or the right-column home it takes instead.
2. **Classify every fact by § State, rules, and history** — now-state → entity column; rules → code; what-was-done → audit log; replayable → sequence log.
3. **Check cardinality before any junction** (§ A junction spells many-to-many) and every FK's `ON DELETE` (§ Keys, § Append-only).
4. **Prove uniqueness claims by inserting the forbidden duplicate** — a schema review cannot tell a firing index from a silent one by reading.
5. **For a seed pipeline, walk § Seeding** — format chosen by payload rather than by destination, shape enforced by a schema at the compile boundary, artifact gitignored, correction limited to canonicalization.
6. **Report as a findings list** — one line per violation: the section, the table/column, the fix.