Database Schema Designer

Design a schema that enforces the data's rules in the database and makes the app's real queries fast.

Workflow

1. Collect requirements. Get from the conversation or codebase, and ask only for what blocks the design:

   • Engine and version (default: PostgreSQL 18; check docker-compose.yml, ORM config, or Supabase project if present)
   • Entities and how they relate, in plain language
   • The 5-10 most frequent or most important queries, including filters and sort order
   • Volume and growth (rows per table per year), read/write mix
   • Tenancy (single app, multi-tenant by organization), retention, audit, and compliance needs (PII, deletion requests)
   • ORM or migration tool in use (Prisma, Drizzle, Rails, Django, Alembic, Supabase migrations, plain SQL)

2. Model entities and relationships. List entities, attributes, and cardinality (1:1, 1:N, N:M). Normalize to 3NF by default; denormalize only for a named query that needs it, and say which.

3. Choose keys and types using references/postgres.md (or references/other-engines.md for MySQL, SQLite, MongoDB). Defaults for Postgres: bigint generated always as identity or uuid DEFAULT uuidv7() primary keys, text over varchar(n), timestamptz for every timestamp, numeric for money.

4. Encode the rules as constraints. NOT NULL wherever a value is required, UNIQUE for natural keys, CHECK for ranges and allowed values, foreign keys with a deliberate ON DELETE choice for each. Data rules that live only in app code get broken by the next script or admin fix.