| name | data-modeling |
| description | Use when designing schemas, adding indexes, writing migrations, or scoping multi-tenant data — constraint-first modeling and expand/contract migration rules. |
Data Modeling & Migrations
The schema outlives every service that reads it. Rules for making data changes boring.
Constraints
- Declare at creation time:
NOT NULL, foreign keys, UNIQUE, CHECK. Adding a constraint later is a locking migration + backfill; removing one is instant. Asymmetry says: start strict.
- Application-level validation duplicates, never replaces, database constraints — the DB is the last line against every code path you forgot.
- Enums: a reference table or DB enum with explicit values. "Magic string column validated in code" drifts within a quarter.
- Money is integer minor units or
NUMERIC. Floats for money is a BLOCK.
Keys & types
- Surrogate PKs; natural keys get
UNIQUE constraints instead (they change; PKs must not).
- UUIDv7 (time-ordered) when inserts are hot or ordering matters — random UUIDv4 PKs fragment B-tree indexes.
- Timestamps:
timestamptz, always UTC. created_at/updated_at on every table; updated_at maintained by trigger or ORM hook, not discipline.
Indexes
- Index from query shapes, not intuition: write the query, run
EXPLAIN (ANALYZE, BUFFERS), add the index, prove the plan changed.
- Composite index column order = equality columns first, then range. An index on
(a, b) serves WHERE a=? but not WHERE b=?.
- Soft-delete × UNIQUE:
UNIQUE(email) blocks re-registration after delete — use a partial index WHERE NOT is_deleted.
- Every index taxes every write. Quarterly: drop indexes with zero scans (
pg_stat_user_indexes).
Migrations (expand → migrate → contract)
- Expand: additive change (new nullable column/table/index
CONCURRENTLY). Old and new code both work.
- Migrate: deploy code writing both / reading new-with-fallback; backfill in batches (bounded, resumable, off-peak).
- Contract: remove old column/path — ships alone, after verification, one deploy behind.
- Destructive steps (DROP, type narrowing, NOT NULL on existing data) never share a deploy with the code that stops needing them. Rollback of a combined deploy is impossible.
- Every migration states its lock behavior.
ALTER TABLE that takes ACCESS EXCLUSIVE on a hot table is an outage, not a migration.
- Down-migrations that can't restore data (dropped column) are documented as irreversible — say so, don't fake it.
Multi-tenancy
- Tenancy scoping lives in exactly one layer: RLS, a session variable, or the repository base query — never per-query
WHERE tenant_id=? discipline. One forgotten WHERE is a data breach.
- Cross-tenant queries (admin/analytics) go through a named, audited path — not by omitting the filter.
When NOT to apply
Prototypes and single-user tools: skip the ceremony, keep the constraints (they're free at creation). Analytics/warehouse schemas follow their own denormalization rules — this file governs OLTP.
