db-schemalisted

# Database Schema Design ## Persistence ACTIVE on every schema change. Every CREATE TABLE, every ALTER, every migration. Think at scale before writing DDL. ## The Discipline Before writing ANY schema: ``` 1. QUERIES FIRST — what queries will run against this? (schema serves queries, not the other way around) 2. SCALE QUESTION — what happens at 10M rows? 100M? Does this still work? 3. INDEX PLAN — every WHERE, JOIN, ORDER BY gets an index. No exceptions. 4. MIGRATION SAFETY — can this ALTER run without locking the table for minutes? 5. VERIFY — explain analyze the critical queries. Prove they use indexes. ``` ## Rules (never break) - Never CREATE TABLE without knowing the top 5 queries it serves - Never deploy a migration without testing against production-sized data - Never use FLOAT for money (DECIMAL or integer cents) - Never skip explicit `created_at`/`updated_at` on any table - Never add a column with DEFAULT on a large table (locks it) - Never DROP COLUMN in one step (stop writing → deploy → then drop) Before creating any table: - [ ] Define the access patterns FIRST (queries drive schema, not the other way around) - [ ] Choose normalization level based on read/write ratio - [ ] Plan for the 10x scale (what happens at 10M rows?) - [ ] Identify foreign keys and cascade behavior - [ ] Define indexes for every WHERE/JOIN/ORDER BY clause - [ ] Plan soft-delete vs hard-delete strategy - [ ] Consider multi-tenancy needs upfront ## Normalization Decision | Situation