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.

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.

5. Derive indexes from the queries in step 1, not from a checklist. For each query, name the index that serves it. Index every foreign key column you join or cascade on (Postgres does not do this automatically). Do not add an index a UNIQUE or primary key already provides.

6. Write the deliverable: DDL, ERD, index rationale, and migrations with rollback, using references/schema-templates.md and the layout in references/output-format.md. Match the user's migration tool if they have one.

7. Verify. If a database is available (local Postgres, Docker postgres:18, a Supabase branch), run the DDL, insert a few rows, and EXPLAIN the key queries to confirm the planned indexes are used. Otherwise, walk each query from step 1 against the schema by hand. Then check the list below.

Checks before delivering

• Every query from step 1 is served by a stated index or is explained as a scan that is fine at the expected size.
• Every foreign key has an explicit ON DELETE behavior and an index on the referencing column.
• No timestamp without time zone; no floating point for money; no varchar(255) by habit.
• updated_at actually updates: a DEFAULT now() only sets it on insert, so add a trigger or set it in the ORM.
• Uniqueness that should ignore case (emails, usernames) is enforced case-insensitively.
• Multi-tenant tables carry tenant_id (or org_id), include it in unique constraints and leading index columns, and have row-level security if the database is reachable from clients (always on Supabase).
• Migrations that touch large existing tables avoid long locks (see migration safety in references/postgres.md).
• Rollback scripts are real inverses, and destructive rollbacks (dropping a column with data) are called out.

Worked example

Request: "Multi-tenant project tracker: orgs, users who can belong to several orgs, projects per org, tasks per project. The main screen lists a project's open tasks by due date."