spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Patterns — Designing Database Schemas78## Contents9- Canonical table template10- One-to-many and many-to-many11- Partial unique index with soft delete12- Audit table (hard-delete alternative)13- Enum strategies14- updated_at trigger15- Dialect deviations (MySQL, SQLite)16- Gotchas1718## Canonical table template1920```sql21CREATE TABLE app_user (22    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,23    email       text NOT NULL UNIQUE,24    display_name text NOT NULL,25    status      text NOT NULL DEFAULT 'active'26                CHECK (status IN ('active', 'suspended', 'closed')),27    created_at  timestamptz NOT NULL DEFAULT now(),28    updated_at  timestamptz NOT NULL DEFAULT now()29);30```3132## One-to-many and many-to-many3334```sql35CREATE TABLE user_order (36    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,37    user_id     bigint NOT NULL REFERENCES app_user (id) ON DELETE RESTRICT,38    total_cents integer NOT NULL CHECK (total_cents >= 0),39    created_at  timestamptz NOT NULL DEFAULT now()40);41-- Rule 6: index the FK immediately.42CREATE INDEX user_order_user_id_idx ON user_order (user_id);4344-- N-N: junction table, composite PK, both FKs indexed (PK covers the first).45CREATE TABLE order_tag (46    order_id bigint NOT NULL REFERENCES user_order (id) ON DELETE CASCADE,47    tag_id   bigint NOT NULL REFERENCES tag (id) ON DELETE CASCADE,48    PRIMARY KEY (order_id, tag_id)49);50CREATE INDEX order_tag_tag_id_idx ON order_tag (tag_id);51```5253`ON DELETE` decision: `RESTRICT` (default choice — force explicit cleanup),54`CASCADE` only for true child rows (junction rows, line items), `SET NULL`55when the relationship is optional history.5657## Partial unique index with soft delete5859```sql60ALTER TABLE app_user ADD COLUMN deleted_at timestamptz NULL;6162-- Plain UNIQUE(email) would block re-registering a deleted email:63DROP INDEX IF EXISTS app_user_email_key;64CREATE UNIQUE INDEX app_user_email_live_key65    ON app_user (email) WHERE deleted_at IS NULL;66```6768Every live-row query now needs `WHERE deleted_at IS NULL` — encode it in a view69if the application layer can't be trusted to remember.7071## Audit table (hard-delete alternative)7273```sql74CREATE TABLE app_user_audit (75    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,76    user_id    bigint NOT NULL,          -- no FK: row may be gone77    action     text NOT NULL CHECK (action IN ('insert','update','delete')),78    old_row    jsonb,79    changed_at timestamptz NOT NULL DEFAULT now()80);81```8283## Enum strategies8485Default — `text` + `CHECK` (cheap to extend: one `ALTER TABLE … DROP/ADD CONSTRAINT`):8687```sql88status text NOT NULL CHECK (status IN ('draft','published','archived'))89```9091Escape hatch — native `CREATE TYPE … AS ENUM` when many tables share the set;92note that removing enum values is painful.93Lookup table when values carry attributes (label, sort order) or are user-editable.9495## updated_at trigger9697```sql98CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$99BEGIN100    NEW.updated_at = now();101    RETURN NEW;102END $$ LANGUAGE plpgsql;103104CREATE TRIGGER app_user_touch BEFORE UPDATE ON app_user105FOR EACH ROW EXECUTE FUNCTION set_updated_at();106```107108## Dialect deviations (MySQL, SQLite)109110| PostgreSQL | MySQL 8 | SQLite |111|---|---|---|112| `bigint GENERATED ALWAYS AS IDENTITY` | `BIGINT AUTO_INCREMENT` | `INTEGER PRIMARY KEY` (rowid) |113| `timestamptz` | `TIMESTAMP` (stored UTC) — store app-side UTC | `TEXT` ISO-8601 UTC |114| `text` freely | prefer `VARCHAR(n)`; `TEXT` can't be fully indexed | `TEXT` |115| partial indexes | none — emulate with generated column | supported |116| `CHECK` enforced | enforced 8.0.16+ | enforced, but FKs need `PRAGMA foreign_keys = ON` |117118## Gotchas119120- PostgreSQL folds unquoted identifiers to lowercase — never create quoted CamelCase names.121- `UNIQUE` allows multiple NULLs (PostgreSQL < 15 semantics); use `UNIQUE NULLS NOT DISTINCT` (15+) or `NOT NULL` if that's wrong for the domain.122- FK constraints don't create indexes (rule 6) — the referenced side's PK is indexed, the referencing column is not.123- `serial` is legacy; `GENERATED ALWAYS AS IDENTITY` is the standard-conforming replacement.124- Random UUIDv4 PKs fragment B-tree indexes at scale; prefer UUIDv7 (time-ordered) when using uuid.125- `varchar(255)` has no performance benefit over `text` in PostgreSQL — the limit is only a constraint.126- Money as `float` fails equality checks and loses cents in aggregation — `numeric` or integer cents, always.127