database-schema-designer

Design schemas that survive production

A schema is the one place an invariant holds no matter which code path, process or concurrent request writes. Normalise first, let constraints enforce the rules, index for the real query patterns, and change it in steps that never block production. Sources: PostgreSQL docs (constraints, CREATE INDEX, ALTER TABLE, transaction isolation, row security); MySQL 8 reference (InnoDB locking, online DDL); Codd's normal forms as taught in standard database texts.

Steps

1. Read the existing schema and conventions. search_lore for the ORM, migration tool, naming, key strategy and tenancy model. Extend them; never introduce a second pattern.
2. Model entities and relationships. Nouns with attributes become tables. State cardinality before structure: 1:N → FK on the N side; M:N → junction table with a composite primary or unique key on the two FKs; 1:1 → FK with a UNIQUE constraint. Sketch the ERD (Mermaid is fine) in the plan or the evidence summary.
3. Normalise to 3NF for transactional data. 1NF: atomic values, no repeating groups. 2NF: no column depends on part of a composite key. 3NF: no column depends on another non-key column. Denormalise only for a measured read need, and document who keeps the copy consistent (trigger, same-transaction write, or rebuild job).
4. Write every invariant as a constraint — the application check alone is a race. Under Read Committed (Postgres default) and Repeatable Read (MySQL InnoDB default), two transactions can both "check, then insert" and both succeed:
   • uniqueness → UNIQUE; conditional ("one active subscription per user") → partial unique index WHERE status = 'active' (Postgres) or a generated column (MySQL);
   • no overlapping ranges (bookings) → Postgres EXCLUDE USING gist (room_id WITH =, during WITH &&) with btree_gist, or serialise via SELECT … FOR UPDATE on a parent;
   • value rules (amount >= 0, allowed states) → CHECK; references → FK with an explicit ON DELETE (RESTRICT unless cascade is truly intended);
   • counters and balances → a single UPDATE … SET n = n + :d WHERE … AND n + :d >= 0, never read-modify-write in application code; concurrent edits to one row → optimistic version column (UPDATE … WHERE id = ? AND version = ?, 0 rows → 409). Map constraint violations to a domain error (409/422), not a 500. If you use SERIALIZABLE, retry on SQLSTATE 40001.
5. Choose types that cannot drift. Timestamps timestamptz (MySQL: UTC DATETIME by convention); money as numeric/integer minor units, never float; text with a CHECK length over arbitrary varchar(n) in Postgres; enums via CHECK or a lookup table (easier to extend than a native enum). Keys: bigint identity for single-node, UUIDv7 (time-ordered, index-friendly; Postgres 18 has uuidv7()) when ids are generated outside the database. NOT NULL by default; nullable only with a meaning. Absent a repo convention: created_at/updated_at timestamptz NOT NULL DEFAULT now() on every table; soft delete as a nullable deleted_at with partial indexes and unique keys WHERE deleted_at IS NULL; and an append-only audit table (entity_type, entity_id, action, actor_id, changed_at, before, after) for user-facing data with compliance implications.
6. Tenancy. tenant_id NOT NULL on every tenant-scoped table, leading in composite unique keys and indexes; composite FKs (tenant_id, parent_id) make cross-tenant references impossible. With Postgres RLS: ENABLE and FORCE ROW LEVEL SECURITY (owners bypass otherwise) and connect as a non-owner role.
7. Index for the query patterns in the ticket. Postgres does not index FK columns automatically (InnoDB does): add them. Composite indexes: equality columns, then range, then sort. Partial indexes for hot subsets. Every index costs every write; add none without a query that needs it (the sql-query-performance skill).
8. Migrate without blocking (mechanics: the add-db-migration skill; review: the migration-review skill). Set lock_timeout for DDL. See the table below.
9. Generate types from the schema (Prisma/Drizzle/sqlc/SQLAlchemy/pydantic tooling the repo uses); never hand-write a parallel model.