<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Claude: scripts/model-profiles.json -->

<critical_constraints> FORBIDDEN: Truncating code or writing placeholders such as "// ... existing code ..." or "# rest unchanged". Every edit is complete and applies as written. FORBIDDEN: Reporting a check as passed without showing the command and its output. REQUIRED: Reason through the rules below before the first edit; when two rules conflict, the one stated first wins.

• Never use an email address, username, or phone number as a primary key. They change, and the change cascades through every foreign key in the system.
• Never use uuid v4 as a clustered/primary key on a high-insert table without measuring — random insertion order fragments the index and inflates write amplification. uuid v7 gives you opacity and locality together.
• Never store a comma-separated list in a column. It cannot be indexed, joined, or constrained. Use a join table. </critical_constraints>

---

#Purpose

Rules for designing tables. The schema is the longest-lived artefact in the system — application code is rewritten, the data outlives it. Every invalid state the schema permits will eventually exist in production.

Design principle: make illegal states unrepresentable at the database level. Application validation is a convenience for the user; the constraint is the guarantee.

---

#Keys

Every table gets a primary key. No exceptions — a table without one cannot be replicated logically, deduplicated, or safely updated.

sql
CREATE TABLE orders (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenants(id),
  status       order_status NOT NULL DEFAULT 'pending',
  total_cents  bigint NOT NULL CHECK (total_cents >= 0),
  currency     char(3) NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

Never use an email address, username, or phone number as a primary key. They change, and the change cascades through every foreign key in the system.

Never use uuid v4 as a clustered/primary key on a high-insert table without measuring — random insertion order fragments the index and inflates write amplification. uuid v7 gives you opacity and locality together.

---

#Types

0.1 + 0.2 != 0.3 in binary floating point. Money in float will produce a reconciliation failure — the only question is when. Store minor units as an integer, and always store the currency beside the amount; an amount without a currency is not a price.

timestamp without time zone records a wall-clock reading with no way to know which clock. Use timestamptz everywhere and convert at the presentation layer.

---

#Nullability

NOT NULL is the default position. Add nullability only when "unknown" or "not applicable" is a genuine, distinct domain state.

NULL is not zero, not empty string, and not false. It propagates: NULL = NULL is NULL, and count(col) skips it. A nullable column that is never legitimately null is a permanent source of defensive branching in every query that touches it.

Where a column is only meaningful for some rows, prefer a partial constraint over a nullable free-for-all:

sql
-- cancelled_at is null unless status = 'cancelled', and required when it is
ALTER TABLE orders ADD CONSTRAINT cancelled_consistency CHECK (
  (status = 'cancelled') = (cancelled_at IS NOT NULL)
);

---

#Normalise first, denormalise on evidence

Reach 3NF by default: every non-key column depends on the key, the whole key, and nothing but the key. Duplicated data is duplicated truth, and the copies diverge.

Denormalise only with a measured read problem, and only when you can state how the copy is kept correct — a trigger, a materialised view, or an explicit recompute job. A denormalised column with no stated maintenance mechanism is a bug with a delay fuse.

Never store a comma-separated list in a column. It cannot be indexed, joined, or constrained. Use a join table.

sql
CREATE TABLE order_tags (
  order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  tag_id   uuid NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (order_id, tag_id)
);

---

#Constraints belong in the database

Always state ON DELETE explicitly — RESTRICT, CASCADE, or SET NULL. The default varies and silent RESTRICT failures surface as opaque 500s.

Application-level checks race. Two concurrent requests both read "no existing row", both insert, and only a UNIQUE constraint stops the duplicate.

---

#Soft deletes and history

If rows must be recoverable, use deleted_at timestamptz, and then remember that every query and every unique constraint must account for it:

sql
CREATE UNIQUE INDEX users_email_active
  ON users (lower(email)) WHERE deleted_at IS NULL;

Without the partial index, a deleted user permanently blocks their own email from being reused. This is the single most common soft-delete bug.

For audit history, append to a separate table rather than overwriting. → Security/audit-log

---

#Anti-patterns

---

#Checklist

• Every table declares a primary key
• Keys are surrogate and opaque where rows are externally visible
• Money stored as integer minor units with an explicit currency column
• All timestamps are timestamptz
• Columns are NOT NULL unless "unknown" is a real domain state
• Enumerations constrained by CHECK or a native enum, never free text
• No multi-valued columns; many-to-many uses a join table
• Every foreign key declares ON DELETE explicitly
• Uniqueness enforced by the database, not by an application read-then-write
• Soft-deleted tables use partial unique indexes
• Denormalised columns have a stated maintenance mechanism