---
paths:
  - "**/*.sql"
  - "**/migrations/**"
  - "**/drizzle/**"
  - "**/drizzle.config.*"
  - "**/schema.ts"
  - "**/schema/**"
  - "**/db/**"
---
# Database

Naming and the fixed column vocabulary for relational schemas in **every** Nurix project — one concept, one name, everywhere. Written for SQLite / Cloudflare D1 + Drizzle; a Postgres project keeps the same column names and semantics with native types (`uuid`, `timestamptz`, `boolean`). **Design doctrine — what earns a table, state vs. logs, junctions, FK policy, uniqueness traps — is the `schema-design` skill; consult it before any schema change.**

## 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), 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. The single exemption is a log whose **composite key is its ordering** (`PRIMARY KEY (<entity>_id, sequence)`), declared at the table that uses it — everything else, logs included, takes a UUIDv7.
- **`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 between scopes is the `schema-design` skill. ❌ Never rename this role `handle`, `key`, `code`, or `name` in another project.
- **`label`** — the **human display name** — ❌ never `name`, `title`, or `display_name`.

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.

**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 the `schema-design` skill.
- **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`).
- **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.

## 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" })`).