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."

• Entities: orgs, users, org_members (N:M between users and orgs with a role), projects (N:1 org), tasks (N:1 project, optional assignee).
• Key query: open tasks for one project ordered by due date, so a partial index ON tasks (project_id, due_at) WHERE status <> 'done'.
• Rules as constraints: UNIQUE (org_id, user_id) on org_members; CHECK (role IN ('owner','admin','member')); status limited by CHECK; unique project name per org with UNIQUE (org_id, name).
• Deletes: removing an org cascades to projects and tasks; deleting a user sets tasks.assignee_id to null instead of deleting tasks.
• Tenancy: tasks carries org_id so row-level security can check membership without a join through projects, and a composite foreign key (project_id, org_id) -> projects (id, org_id) keeps the two consistent.

The full DDL for this example is in references/schema-templates.md.

Failure modes

• Designing tables before knowing the queries, then indexing every column "just in case". Each index slows every write.
• Postgres ENUM types for values that will change. Adding a value is easy, but removing or renaming one is painful; a CHECK constraint or lookup table is easier to evolve.
• Soft deletes (deleted_at) added everywhere by default. They leak into every query and unique constraint. Use them where there is a real restore or audit need, and pair them with partial unique indexes.
• Polymorphic foreign keys (commentable_type + commentable_id) that the database cannot enforce. Prefer separate nullable FKs with a CHECK that exactly one is set, or separate tables.
• Storing structured, queried fields inside jsonb. JSON is for sparse or truly variable attributes; anything you filter, join, or constrain on deserves a column.