sql-architectlisted

# SQL Architecture & Database Standards Targets **PostgreSQL 18** as the primary engine; MySQL 9 and SQLite 3.53 noted where they differ. See [STACK.md](STACK.md) for pinned tool versions. ## 1. Schema design - **Primary key:** `id UUID PRIMARY KEY DEFAULT uuidv7()` on every table. UUID v7 is sortable, distributed-friendly, and doesn't leak counts via URLs. PG 18 has native `uuidv7()`; on earlier engines use an extension or app-generated UUID v7. - **Natural identifiers stay UNIQUE:** the surrogate `id` is for joins; domain meaning lives in `UNIQUE` constraints (`email`, `slug`, `iso_code`). - **Foreign keys:** always declared at the DB level (`REFERENCES other(id) ON DELETE RESTRICT` by default). Never enforce relationships in app code alone. - **NOT NULL by default.** Make NULL an explicit, justified choice — every nullable column should have a documented reason. - **CHECK constraints:** push invariants into the DB (`CHECK (price >= 0)`, `CHECK (status IN (...))`). They survive bad code paths. - **Audit columns (opt-in):** `created_at` + `updated_at` (`timestamptz NOT NULL DEFAULT now()`) on tables whose rows change after creation. Skip on pure lookup tables (`countries`, `currencies`) and event/log tables (where `event_at` alone is enough). - **Soft delete (default):** `deleted_at timestamptz NULL`. Filter via partial indexes (`CREATE INDEX ... WHERE deleted_at IS NULL`) so queries stay fast. Use **hard delete** only when GDPR/compliance requires it, or for append-only