# Database

The design decisions, naming and fixed column vocabulary that hold for **every** Nurix relational schema, whatever its traffic — one concept, one name, one home for each fact. Written for SQLite / Cloudflare D1 + Drizzle; a Postgres project keeps the same column names and semantics with native types (`uuid`, `timestamptz`, `boolean`), and a MySQL project takes the forms in § Column types (MySQL). Choices that depend on a product's traffic — what to cache, what to copy or store, which call an index serves, how a log is partitioned — are the `optimizer`'s, run on the user's ask; seed pipelines are `seeding.md`.

## Keys & the shared vocabulary

Three column roles are fixed across every table and every project:

- **`id`** — the primary key, **always a UUID** (`TEXT PRIMARY KEY` on D1, `uuid` on Postgres, `CHAR(36)` on MySQL), never a synthetic or natural key (❌ `type_page_button`, `human:default`). Runtime rows mint a UUIDv7; seed rows derive a deterministic UUIDv5 of the natural key so re-seeds stay stable. **Never** mix synthetic and UUID ids in one table. Two exemptions, each declared at the table that uses it — everything else, logs included, takes a UUIDv7 `id`: a log whose **composite key is its ordering** (`PRIMARY KEY (<entity>_id, sequence)`), and a **range-partitioned table**, whose key leads with its partition column and ends with the row's UUID, because MySQL and Postgres require the partition column in every unique key (`PRIMARY KEY (created_at, id)` on a day-partitioned `page_views`).
  - **Typed ids on the wire** — when an external contract wants prefixed ids (`cus_…`), the serializer adds the prefix over the stored UUID on the way out and strips it on the way in; storage never holds it, so no key, index or join carries a wire concern. `customers.id` holds the UUIDv7, and the API answers `cus_` followed by its 32 hex digits.
- **`slug`** — the **stable key rows in another scope reference**: `UNIQUE`, lowercase `snake_case`. A slug means the same thing in every scope, so a slug reference survives a copy with no remapping (§ Copying rows between scopes). ❌ Never rename this role `handle`, `key`, `code`, or `name` in another project.
- **`label`** — the **human display name** — ❌ never `name`, `title`, or `display_name`.
- **An id another system mints is not a row id.** An OAuth client id, a WebAuthn credential id or a hostname keeps its issuer's type and length (`VARCHAR(255)`), never the UUID column type, even when its name ends in `_id`; a blanket `*_id → CHAR(36)` rewrite truncates it, and fixtures with short values never notice.
- **Creation order is `(created_at, id)`, never `id` alone.** Seed rows carry a UUIDv5, which does not sort by time, and a UUIDv7 settles only ties inside one millisecond, so `ORDER BY id` is wrong the moment a seed row exists; seed rows whose order matters get distinct `created_at` values. No auto-increment column exists only to order inserts.

Reference columns name what they point at: `<entity>_id` for a UUID-PK reference (`user_id`, `app_id`), `<entity>_slug` for a slug reference (`source_slug`).

- **Enforced, same scope** (the referenced row must exist) → a real `FOREIGN KEY (<x>_id) REFERENCES <table>(id)`.
- **Cross-scope** (the reference must survive being copied) → `<x>_slug` as a plain column, **no** FK — an FK would bind the row to one scope's copy of the target, which is the remapping the slug exists to avoid.
- **Drift-tolerant** (a controlled-vocabulary value that may fall off-list) → a plain value column, **no** FK, paired with an `is_*` boolean recording whether the value is in the canonical set.

**A tenant pin leads with the owner's id.** A child pinned to its owner's tenant references `(owner_id, tenant_id) → owner(id, tenant_id)`, and the owner declares a unique `(id, tenant_id)` as the target: the index the foreign key needs then also serves every lookup by owner, where `(tenant_id, owner_id)` would need a second index for it.

**Every inbound FK to a hub table declares a deliberate `ON DELETE`.** A hub (`users`, `devices`, anything several modules point at) collects edges from teams that never meet, and a single `NO ACTION` among them turns deleting the parent into a constraint error naming neither table. Decide per edge: the child dies with the row (`CASCADE`) or survives it (`SET NULL`, which needs a nullable column) — `NO ACTION` on a hub is a deferred outage, not a default.

## Naming

- **Tables** — `snake_case`, plural noun for what they hold (`users`, `apps`, `devices`).
- **Junction (M:N) tables** — `<entityA>_<entityB>_map`, **always**, even when the join carries its own columns; singular stems, owning/anchor entity first (`app_user_map`, `app_device_map`). ❌ Never a "nice" noun (`members`, `installs`, `memberships`, `enrolments`) — the name must read as a join, and a reviewer should know both sides from it. The `_map` suffix is reserved for genuine M:N — the cardinality test that decides junction vs. column is § A junction spells many-to-many.
- **Columns** — `snake_case`; booleans `is_*` / `has_*`; timestamps `created_at` / `updated_at`; foreign keys `<entity>_id`.
- **Indexes** — `<table>_<col(s)>_idx`; unique `<table>_<col(s)>_unique`; a junction's composite unique reads `<table>_<colA>_<colB>_unique` (`app_user_map_app_user_unique`). On MySQL an index name is scoped to its table, so the prefix is dropped: `<col(s)>_idx`, `<col(s)>_unique` (`slug_unique` on `stores`). SQLite and Postgres scope index names to the schema, so they keep it. In every name, a reference column is written as its entity (`organization_id` → `organization`), other columns in full, and a trailing `id` that only breaks ties is omitted. A key over an expression adds the state it selects (`organization_active_unique` for one active key per organization).
- **Constraints** — foreign keys and checks are named on every engine, because their names are schema-wide even on MySQL: `<table>_<col(s)>_fk`, `<table>_<rule>_check`. An unnamed key gets the engine's generated name (`orders_ibfk_2`), and every error and migration then cites a name nobody chose.
- **Names are proven from the catalog** — an inline `UNIQUE` or an unnamed foreign key gets an engine-generated name that reading the DDL does not reveal; a test reads `information_schema` (or the engine's catalog) and asserts every constraint and index name.
- **Drizzle mirror** — the exported object is the camelCase table name (`app_user_map` → `appUserMap`); column props are the camelCase of the column (`created_at` → `createdAt`). The `sqliteTable("…")` string and every index name must match the DDL (`init.sql`) exactly — the schema-parity boundary.

## Indexes and columns

- **No secondary index is a left prefix of another index or unique key.** The longer key already serves every query the shorter one would; the primary key is the one exception, because a composite foreign-key target extends it.
- **Every index names the read it serves or the invariant a writer relies on.** A change adding one cites the statement (`file:line`) and how often it runs per month; an index citing neither is not built, and one whose read has not been written yet waits for it. Whether the frequency earns it is the `optimizer` skill's `indexes.md`: the full review runs on the user's ask, and its decision steps apply by hand to each index a change adds.
- **No column is written without a reader.** A column nothing reads is dropped, not kept for later; `created_at` and `updated_at` stay on every table regardless.
- **A value derivable from a sibling column is generated, never written.** A flag that is true exactly when another column is set (`is_verified` beside `verified_via`) is a stored generated column of that predicate, so the two cannot disagree; a value identical for every row under one rule is computed in code, not stored.

## Column types (SQLite / D1)

- **Timestamps** — `created_at` + `updated_at`, both `TEXT NOT NULL DEFAULT (datetime('now'))`, on **every** table — except append-only logs, which carry only `created_at`.
- **Booleans** — `INTEGER` (Drizzle `integer(…, { mode: "boolean" })`); the `is_*` / `has_*` names are § Naming.
- **JSON** — `TEXT` (Drizzle `text(…, { mode: "json" })`).

## Column types (MySQL)

- **`id`** — `CHAR(36)`, the UUID in its canonical hyphenated text; reference columns match it (`user_id CHAR(36)`).
- **Timestamps** — `DATETIME(3)`, millisecond precision, always written in UTC: `created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)`, and `updated_at` the same plus `ON UPDATE CURRENT_TIMESTAMP(3)`, with the session's `time_zone` set to `'+00:00'`. An epoch-millisecond wire format is the serializer's, never a `BIGINT` column. Append-only logs carry only `created_at`, as on D1.
- **Booleans** — `TINYINT(1)` (`is_active TINYINT(1) NOT NULL DEFAULT 0`); the `is_*` / `has_*` names are § Naming.
- **JSON** — the native `JSON` type, which validates on write (`stores.opening_hours JSON`); writer and reader share one schema for each JSON column's shape (§ What earns a table).
- **A JSON value carries no version field of its own** — no `{"v": 1, …}` wrapper; a shape change is a migration of the column like any other. A shape change also reaches SQL strings (`$.key` paths, `LIKE '%"key"%'`), which keep compiling and match nothing, so the change greps them as well as the code readers.

## What earns a table

A table costs a migration to reshape, 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 or 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.
- **A one-to-one extension earns no table unless a different authority writes it** — write cadence is not the test. Racing writers serialize by locking the owner row (`SELECT … FOR UPDATE`), so the concurrency-safe row above means writers that cannot share that lock; a secret column is kept out of the read model, not out of the row. A `user_preferences` row per user folds into `users.preferences`.
- **A small, bounded set the owner always reads is JSON on the owner — even when one value is looked up from outside.** Promote only that value to an indexed unique column, because it is the key a request arrives carrying; a uniqueness rule spanning rows moves to the one write path that owns the set. Writer and reader import one shared schema per JSON shape. `stores.opening_hours` is a JSON list on `stores`; `stores.public_slug` is a unique column because links arrive carrying it. The set earns a table again when it grows without bound or an outsider can write it.
- **Model the variation the product uses, not the variation the protocol or the domain allows** — a per-row override nobody fills is a table, a join and a merge rule bought for nothing. One shipping policy per store, not a per-product override table; a change to the policy applies to every product at once.
- **A cache may hold a security counter only when a durable source recomputes it on a miss** — a brake held only in a cache that fails open fails open with it. A coupon's redemption counter lives in the cache and is recomputed from its `redemptions` rows when the cache is cold.

## State, rules, and history

**One fact has one home, chosen by what kind of fact it is.**

| 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`; it is the most common redundant table in a schema. 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.
- **A state machine is one row moving through states, never a table per state — and a single-use step is an update conditioned on the state it leaves.** Exactly one affected row is the proof the step ran once; zero means another request won. `orders.status` moves `pending → paid → shipped`, and payment is captured by `UPDATE orders SET status = 'paid' WHERE id = ? AND status = 'pending'`.
- **When the product reads only the newest instance of a one-to-many, keep that one on the owner and log the rest.** Older instances nobody reads are not history, they are rows nobody deletes. A `carts` row holds its one live checkout as JSON; each finished checkout is written to an append-only `order_events`.
- **A copy inside the database is accepted when its source guards it** — written in the same transaction as the source, the source keeping every row and the unique key; an unpinned copy never decides uniqueness or security. `comments.post_author_id` is copied beside the post's own `author_id`, written in the same transaction as the post.

**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 Scope and Records 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.**

- **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.
- **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.
- **A scheduled run writes one summary row, and none when it changed nothing** — housekeeping is one daily maintenance run of many steps, never a cron per kind of upkeep, and each step answers what it changed. After the steps the run writes a single row: each step with a non-zero count, each failed step with its error class, outcome `failure` when any failed. A run that changed nothing writes no row, because zero entries bury the ones that matter.
- **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 lets a row exist before its owner does** — a pending enrolment or unclaimed record is creatable before the owner is known, which collapses a `pending_*` / `*_requests` table 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. Where the special row is also an ordinary member of its table — one tenant among tenants, the default plan among plans — mark it with a flag guarded by a unique key (§ Uniqueness with a nullable column) instead: it runs through the same code, and no reserved id exists for every query to exclude. `plans.is_default` marks the one default plan.
- **The exception to one table per family: families that share an identity and a lookup key share a table**, told apart by a discriminator column with per-kind `CHECK` constraints tying each kind's columns together. Split, every lookup by that key becomes a search across tables. `payment_instruments` holds cards and bank accounts, both looked up by one `token`.
- **A key every query filters by is copied onto every row it scopes, and the database pins the copy.** Reached through a chain of parents, the tenant filter costs a join per hop, and one forgotten hop is a cross-tenant leak; as a column it is one indexed predicate, and indexes lead with it. A composite foreign key makes the copy unable to disagree with its owner: `tasks` carries `project_id` beside `board_id`, pinned by `(project_id, board_id) REFERENCES boards (project_id, id)`, which needs a unique key on `boards (project_id, id)`.
- **A setting that governs a shared object lives on the owner of the share, never on each consumer of it** — two consumers cannot both govern one thing they share, and whichever is read last silently wins. A team's shared calendar time zone lives on `teams`, not on each member's view of the calendar.

## 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 is unenforced from the moment it is written. 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 1:M junction also** makes the association a row that can go missing independently of the child — the child exists 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 relation that is always total needs no junction** — when every A relates to every B, the map holds every pair and records nothing; the day a product needs a subset is the day it earns one. No `store_currency_map` when every store accepts every currency.
- **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 to outlive its subjects. |
| `NO ACTION` | Makes anyone who ever appeared in the log undeletable — the hub-table defect of § Keys & the shared vocabulary. |

- **`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`), so the log keeps naming who acted after the account is gone.
- **`CASCADE` from the append-only side's own parent is 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 — 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;
```

**On an engine without partial indexes (MySQL 8.0.13+), guard "at most one row in this state" with a functional unique key over an expression that is `NULL` outside the state** — every `NULL` is distinct, so only rows in the state collide, and no extra column exists. A query that relies on the key repeats the expression byte for byte, because the engine uses a functional key only for the identical expression.

```sql
CREATE TABLE plans (
  …,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY plans_default_unique ((IF(is_default, 1, NULL)))
);
```

**Where functional keys are unavailable (older MySQL, MariaDB), the same expression moves into a generated column** under a plain unique key. A generated column over a JSON key unquotes only strings — `CASE WHEN JSON_TYPE(j->'$.k') = 'STRING' THEN JSON_UNQUOTE(j->'$.k') END` — because `JSON_UNQUOTE` of a JSON `null` is the string `'null'`, which collides on the key.

**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: 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.

- **When a row cannot exist without code behind it, the registry is code and the database holds only activation** — a table of handlers is a second copy of the code's own list. Payment-method handlers are code modules; a tenant's `enabled_payment_methods` JSON names which are on.
- **Seeds are generated, never hand-written** — the source of truth is an authored tree or a shared package, compiled and loaded through the pipeline in `seeding.md`.