Database schema design with Cursor

Database schema design with Cursor works best as a staged loop: a data model agreed in Plan Mode, a Drizzle schema generated by the agent, constraint tests that insert every forbidden row, query plans checked on seeded data, and expand-and-contract migrations. Each stage leaves a checkable artifact, so correctness rests on tests and plans, not on reading generated SQL.

This page is for developers who design the tables for a new feature, and for tech leads who approve those designs. The product team has just finalised a multi-tenant SaaS billing spec: users, organisations, plans, usage tracking, invoices, and an audit trail. Nobody has decided whether a user can belong to several organisations, whether invoices hang off subscriptions or usage records, or how the dashboard stays under 200 ms for an organisation with 50,000 usage events a month. A wrong answer now means an ALTER TABLE on a hot production table later.

The examples use PostgreSQL and Drizzle ORM. The loop is the same for Prisma or plain SQL migrations; change the file names in the prompts. For the architecture decisions that come before the data model, start with architecture planning in Cursor. For the same workflow in Claude Code and Codex, see database development with agents.

What agent-assisted schema design gives you

• A data-model document (docs/data-model.md) that lists entities, business rules and the queries the schema must serve, written before any table exists
• Four copy-paste prompts: model extraction, schema generation, constraint tests, and query-plan review
• A constraint test suite that proves each business rule is enforced by the database, not only by application code
• A read-only Postgres MCP setup so Cursor reads real EXPLAIN output instead of guessing
• An expand-and-contract migration pattern with a hand-written rollback, because Drizzle Kit generates no down migrations
• A sign-off checklist a tech lead can approve without reading every generated line

How the schema-design loop fits together

Every stage produces a file, and every file has a gate. The agent writes most of the files; you own the decisions and the gates.

Stage	Cursor surface	Artifact	Gate that proves it
1. Model	Plan Mode (no code)	docs/data-model.md	You and the product owner resolve every open question in it
2. Schema	Agent	src/db/schema.ts, generated SQL migration	npx drizzle-kit generate produces a clean diff; npx drizzle-kit check passes; type check passes
3. Rules	Agent	tests/db/constraints.test.ts	Every business rule has a test that inserts a forbidden row and expects the database to reject it
4. Queries	Agent + Postgres MCP (read-only)	tests/db/query-plans.test.ts or a plan report	No sequential scan on a large table for any query in the model document
5. Change	Agent	Expand, backfill and contract migrations plus rollback SQL	Migrations apply to a copy of production data; rollback applies cleanly

Plan Mode is the Cursor mode that, in Cursor’s words, “creates detailed implementation plans before writing any code”. It keeps stage 1 about decisions instead of files. See Cursor’s agent modes for how the modes differ.

Design the schema step by step

1. Write the data model before any table exists. Put the raw requirements in docs/data-model.md and open a new chat in Plan Mode. The prompt below asks for entities, cardinalities and the list of questions the spec leaves open, as a section you save into the document yourself (or switch to Agent and ask it to write the file). It forbids SQL on purpose: “Can a user belong to several organisations?” costs one message now and a migration later.

   Copy-paste prompt: extract the data model (Plan Mode)

   @docs/data-model.md contains the requirements for a multi-tenant SaaS billing system.
   Do not write SQL or TypeScript. Produce a conceptual data model as a Markdown section headed "## Model", for me to save into that file:
   
   1. Entities with their core attributes and the natural key of each (the business identifier, not the surrogate id).
   2. Every relationship with its cardinality (1:1, 1:N, M:N) and the reason, quoting the requirement it comes from.
   3. Business rules the database must enforce, one per line, numbered R1, R2, ... Examples of the kind I mean: "at most one active subscription per organisation", "usage quantities are never negative", "an audit row is never updated or deleted".
   4. The queries the application will run, numbered Q1, Q2, ..., with expected row counts at 10x today's largest customer.
   5. Open questions: every place where the requirements allow two different schemas. For each, state both options and what each would cost to change later.
   
   Stop after the open questions. I will answer them before we generate anything.

   Answer each open question in the document itself, with the date and who decided. The document is now the spec the schema is checked against, and a reviewer reads it in minutes.

2. Generate the schema in Agent mode. Switch to Agent, point it at the agreed model, and name the database, the ORM and the rules for deletes and indexes. Stating conventions explicitly removes guesswork the agent would otherwise fill with its own defaults.

   Copy-paste prompt: generate the Drizzle schema

   @docs/data-model.md is the agreed data model. Implement it with Drizzle ORM for PostgreSQL in src/db/schema.ts.
   
   Conventions:
   - uuid primary keys with defaultRandom(); timestamptz for every timestamp; created_at and updated_at on every table.
   - Money as integer minor units (cents) plus a currency column, never float or real.
   - Foreign keys: ON DELETE RESTRICT by default. CASCADE only where the model says the child is owned by the parent, and add a comment naming the rule that justifies it.
   - Postgres does not index foreign key columns automatically: add an index for every foreign key that a query in the model filters or joins on.
   - Enforce every business rule R1..Rn in the database where Postgres can express it: CHECK constraints, unique indexes, partial unique indexes (for example one active subscription per organisation). For each rule, add a comment "// Rn" on the line that enforces it. List the rules you could not enforce in the database, with the reason.
   - Composite indexes only for queries Q1..Qn that need them, each with a comment "// Qn".
   
   Then run `npx drizzle-kit generate --name initial_billing_schema` and show me the generated SQL file. Do not apply it to any database.

   The // Rn and // Qn comments are what make the output checkable. A reviewer greps for R7 and finds either the constraint that enforces it or its entry on the “not enforceable” list.

3. Prove every business rule with a constraint test. A constraint the tests never exercise is a constraint nobody knows works. Ask the agent to write one test per rule that inserts the forbidden row into a real Postgres and expects a rejection.

   drizzle-kit migrate reads the target database from drizzle.config.ts. If the project has none yet, create it first so the command uses DATABASE_URL and not some other database. drizzle-kit also needs a Postgres driver in the project: npm i pg (or postgres).

   drizzle.config.ts

   import { defineConfig } from 'drizzle-kit';
   
   export default defineConfig({
     dialect: 'postgresql',
     schema: './src/db/schema.ts',
     out: './drizzle',
     dbCredentials: { url: process.env.DATABASE_URL! },
   });

   Then start a disposable database and apply the migrations. It needs no password because it listens only on your machine and is deleted when it stops. With trust auth, any password is ignored locally; set a real one when you point this setup at a shared test database or replica.

   # Terminal: throwaway Postgres for the test suite, removed on stop
   docker run --rm -d --name schema-test -p 127.0.0.1:55432:5432 \
     -e POSTGRES_HOST_AUTH_METHOD=trust postgres:17
   export DATABASE_URL=postgres://postgres@localhost:55432/postgres
   npx drizzle-kit migrate

   Copy-paste prompt: constraint tests from the business rules

   @docs/data-model.md @src/db/schema.ts
   Write tests/db/constraints.test.ts (Vitest) against the Postgres at DATABASE_URL, with the migrations already applied.
   
   For every business rule R1..Rn that the schema claims to enforce:
   - one test named "Rn: <rule>" that inserts a valid row and expects success, then inserts the forbidden row and expects the database to reject it with the specific constraint name;
   - wrap each test in a transaction that is rolled back, so tests do not depend on each other.
   
   Add these edge-case tests as well: a user in three organisations with a different role in each; usage rows on 31 January 23:59:59 UTC and 1 February 00:00:00 UTC landing in different billing months; an organisation with zero usage producing a zero-amount invoice; deleting an organisation that still has an unpaid invoice (must fail).
   
   Do not modify src/db/schema.ts. If a test shows a rule is not enforced, stop and list the failing rules.

   Run npx vitest run tests/db. A red test is the useful outcome here: it is a schema bug found before production. Fix the schema in a separate prompt, regenerate the migration, and rerun. The instruction not to touch the schema stops the agent from weakening a rule to make its own test pass.

4. Check the query plans against seeded data. A schema can satisfy every rule and still scan a whole table for the dashboard query. Seed the test database at realistic volume (the Q-list row counts), then have Cursor read the query plans. Connect Postgres MCP Pro in restricted mode, which runs read-only transactions with execution-time limits. Add this to ~/.cursor/mcp.json (global, so the credential stays out of the repo):

   {
     "mcpServers": {
       "postgres": {
         "command": "uvx",
         "args": ["postgres-mcp==0.3.0", "--access-mode=restricted"],
         "env": { "DATABASE_URI": "postgresql://agent_ro:CHANGE_ME@localhost:55432/postgres" }
       }
     }
   }

   Postgres MCP Pro is the PyPI package postgres-mcp (0.3.0, checked 2026-09-26), so it runs with uvx; the npm package of the same name is an unrelated project. Its last PyPI release was 2025-05-16 (about 3.3k GitHub stars, checked 2026-09-26), so pin the version (the postgres-mcp==0.3.0 above) and treat the read-only role as the real guard. The older @modelcontextprotocol/server-postgres is deprecated and archived. See database MCP servers for Supabase, Neon and multi-engine alternatives, and MCP setup in Cursor for where the file lives.

   Create the agent_ro role with SELECT only before you start Cursor. Under the trust auth from step 3 its password is not checked, so the read-only guarantee comes from the role the MCP server logs in as, not from the password:

   psql "$DATABASE_URL" -c "CREATE ROLE agent_ro LOGIN PASSWORD 'CHANGE_ME'; GRANT USAGE ON SCHEMA public TO agent_ro; GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO agent_ro;"

   Restricted mode rejects EXPLAIN ANALYZE (postgres-mcp 0.3.0, checked 2026-09-26), so the agent reads estimated plans: cost and row estimates, not measured time. That is enough to spot a Seq Scan. When you need real execution time, run it yourself outside MCP, for example psql "$DATABASE_URL" -c 'EXPLAIN (ANALYZE, BUFFERS) SELECT ...', or measure it in query-plans.test.ts.

   Copy-paste prompt: query plans for every query in the model

   @docs/data-model.md @src/db/schema.ts
   Use the postgres MCP server (read-only). The database is seeded at the row counts listed next to Q1..Qn.
   
   For each query Qn:
   1. Write it with the Drizzle query builder and show the SQL it produces.
   2. Run EXPLAIN (FORMAT JSON) on that SQL through the MCP server, or call explain_query without analyze. Do not use EXPLAIN ANALYZE: restricted mode rejects it.
   3. Report: the most expensive node with its estimated cost and rows, and whether any table over 10,000 rows is read with a Seq Scan.
   4. If a Seq Scan or a sort over many rows can be avoided with an index, propose the smallest index that fixes it. If the hypopg extension is installed, check it with explain_query using a hypothetical index; otherwise say the estimate is unverified.
   
   Output one table: Qn | estimated total cost | estimated rows | worst node | proposed index | expected plan after. Do not create or drop anything.

   Add each index you accept to schema.ts with its // Qn comment and regenerate the migration. To keep the result from regressing, ask the agent to turn the table into tests/db/query-plans.test.ts: run EXPLAIN (FORMAT JSON) for each query and fail if the plan contains a Seq Scan on a table listed as large. Plan tests are coarse, but they catch the dropped index that a code review misses.

5. Change the schema with expand, backfill and contract. The first schema is never the last. Drizzle Kit generates forward migrations only: drizzle-kit 0.31.11 (checked 2026-09-26) has no down or rollback command, and drop only deletes a migration file. You write the rollback yourself, and splitting a risky change into small steps keeps each rollback trivial.

   Copy-paste prompt: add a required column without downtime

   @src/db/schema.ts @drizzle
   Add a required billing_email column to organizations, which already holds production rows. Use three migrations:
   
   1. Expand: add billing_email as nullable. Generate it with `npx drizzle-kit generate --name add_billing_email`.
   2. Backfill: `npx drizzle-kit generate --custom --name backfill_billing_email`, then write SQL that sets billing_email to the owner's email in batches of 1,000 rows by primary key, so no single statement locks the whole table.
   3. Contract: set NOT NULL, generated as `set_billing_email_not_null`, shipped in a later deploy once application code writes the column on every insert.
   
   For each migration, write the rollback SQL in drizzle/rollback/<same name>.sql and explain in one line when it is safe to run.
   Add a test to tests/db/constraints.test.ts that inserts an organization without billing_email and expects rejection after step 3.

   Apply the migrations to a restored copy of production data before merging, and run the rollback files against that copy too. A rollback nobody has run is a guess. For renames, type changes and backfills on large tables, the framework and database migration workflow goes further.

What a reviewer checks before approving the schema

The tech lead approves the evidence, not every generated line. The pull request description carries these items, and each can be checked in a few minutes:

Evidence	Where it lives	What “approved” means
Data model with resolved questions	docs/data-model.md	Every open question has an answer, a date and an owner
Rule coverage	// Rn comments and constraints.test.ts	Every R number appears in both, or on the “not enforceable in the database” list with the application-level check named
Query coverage	query-plans.test.ts or the plan table	Every Q number has a plan; no Seq Scan on a large table
Delete behaviour	grep -n "onDelete" src/db/schema.ts	Every cascade has a comment naming the ownership rule
Migration safety	drizzle/ and drizzle/rollback/	npx drizzle-kit check passes; each migration has rollback SQL that was run on a copy of production data
CI gate	the pipeline log	Type check, vitest run tests/db and migrations against a fresh database all green

Put the conventions from step 2 in a project rule so every future schema prompt starts from them, and run the constraint and plan tests in CI as described in testing patterns in Cursor.

When agent-generated schemas go wrong

The agent denormalises too early. Models often store computed values such as invoices.total_amount to avoid joins. In billing, a stored total that disagrees with its line items is an incorrect invoice. Recovery: remove the column, compute the total in a view or query, and add a stored copy back only when a Q-number plan proves the join is too slow. If you keep it, add a constraint test that fails when the total and the line-item sum differ.

A cascade deletes more than intended. ON DELETE CASCADE on the wrong foreign key turns one organisation delete into an empty invoices table. Recovery: grep for every cascade, demand the ownership comment, and switch the rest to restrict. The “delete an organisation with an unpaid invoice” test from step 3 turns red when this regresses.

The agent edits the schema to make its own tests pass. Asked to “make the tests green”, an agent may drop a CHECK constraint instead of fixing the data. Recovery: keep schema changes and test changes in separate prompts, and reject any diff that touches both schema.ts and constraints.test.ts without a matching change in docs/data-model.md.

A migration fails halfway through. A backfill times out, or CREATE INDEX CONCURRENTLY is refused because Postgres does not allow it inside a transaction block and your migration runner wraps files in one. Recovery: restore the copy, split the failing statement into its own migration, and check how your runner handles transactions before creating concurrent indexes on a production table.

Development and production run different engines. A schema tested on SQLite and deployed on Postgres differs in types, partial indexes and constraint behaviour. Recovery: run the constraint and plan tests against the same engine and major version you deploy, which is why step 3 starts a Postgres container.

The MCP server can write. Several database MCP servers default to full access, and a generated “cleanup” query can mutate data. Recovery: use --access-mode=restricted (or the vendor’s read-only switch), connect as a SELECT-only role, and point it at a test database or a read replica, never at the primary.

Where to go next after designing your schema

Architecture planning in Cursor Settle service boundaries and data ownership before you model the tables.

Database MCP servers Install Postgres MCP Pro, DBHub, Supabase or Neon read-only in Cursor, Claude Code and Codex.

Framework and database migrations Plan and verify larger schema and framework changes with Cursor's agent.

Performance work in Cursor Profile queries under load once the schema is in production.