Database Schema Designer

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.