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](references/postgres.md) (or [references/other-engines.md](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](references/schema-templates.md) and the layout in [references/output-format.md](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](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](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.