Design optimized database schemas for SQL and NoSQL databases including tables, relationships, indexes, and constraints. Creates ERD diagrams, migration scripts, and data modeling best practices. Use when users need database design, schema optimization, or data architecture planning.

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.

database-schema-designer