Database Schema Design Using Cursor

The product team just finalized the spec for a multi-tenant SaaS billing system. You need user accounts, organizations, subscription plans, usage tracking, invoices, and an audit trail. The relationships are not obvious — does a user belong to an organization, or can they belong to several? Are invoices tied to subscriptions or to usage records? And the performance requirements are non-trivial: the billing dashboard needs to load in under 200ms even for organizations with 50,000 monthly usage events.

Getting the schema right on the first pass saves weeks of painful migrations later. Getting it wrong means either running ALTER TABLE on a production database under load or maintaining backward-compatible migration scripts that make your head spin. Cursor’s Ask mode is surprisingly good at spotting normalization issues, missing indexes, and relationship mistakes before you write your first migration.

What You’ll Walk Away With

• A prompt workflow for turning business requirements into a normalized database schema with Cursor’s Ask mode
• A migration generation prompt that produces Drizzle/Prisma/Knex migration files with rollback support
• A technique for using Agent mode to generate seed data that exercises your schema’s edge cases
• An index optimization prompt that catches N+1 queries and missing indexes before they hit production
• A data modeling checklist that prevents the most common schema design mistakes

The Workflow

Step 1: Extract entities and relationships with Ask mode

Start in Ask mode. Do not jump to creating tables — first get the conceptual model right. The AI is excellent at asking clarifying questions that surface ambiguities in your requirements.

Tip

Copy-paste prompt — Entity extraction:

I am designing a database for a multi-tenant SaaS billing system with these requirements:

• Users sign up and create or join organizations
• Each organization subscribes to a plan (free, pro, enterprise)
• Usage is tracked per-organization per-month (API calls, storage, compute minutes)
• Invoices are generated monthly based on plan + overage usage
• All changes to subscriptions and billing must be auditable
• Users can have different roles in different organizations (owner, admin, member)

Before creating any tables, identify:

1. All entities and their core attributes
2. The relationships between entities (one-to-one, one-to-many, many-to-many)
3. Where a junction table is needed vs. a foreign key
4. Ambiguities in the requirements I should resolve before designing the schema
5. Denormalization opportunities for query performance

Do not write SQL yet. Just give me the conceptual model.

This prompt deliberately avoids asking for SQL. The conceptual model step catches mistakes like “should a user belong to one organization or many?” that are expensive to fix after tables exist. The AI will typically surface 3-5 ambiguities that you need to resolve with the product team before writing code.

Step 2: Generate the schema with proper constraints

Once the conceptual model is clear, switch to Agent mode and generate the actual schema. Be specific about which ORM and database you are targeting — the constraints and syntax vary significantly.

Tip

Copy-paste prompt — Schema generation (Drizzle + PostgreSQL):

Based on the entity model we discussed, generate the complete database schema using Drizzle ORM for PostgreSQL:

1. Create src/db/schema.ts with all tables, columns, and relationships
2. Use proper PostgreSQL types (uuid for IDs, timestamptz for timestamps, jsonb for metadata)
3. Add all foreign keys with ON DELETE behavior (CASCADE for owned data, RESTRICT for referenced data, SET NULL for optional references)
4. Add unique constraints where business rules require them (e.g., one active subscription per organization)
5. Add CHECK constraints for data integrity (e.g., usage counts cannot be negative)
6. Create indexes for every foreign key column and every column used in WHERE clauses
7. Add composite indexes for the queries we know we will run frequently:
   • Usage by organization + month
   • Invoices by organization + status
   • Audit log by entity type + entity id + timestamp
8. Add created_at and updated_at to every table with default values
9. Use an enum for subscription status: active, canceled, past_due, trialing

Generate the initial migration file as well.

Step 3: Validate the schema against real queries

A schema that looks clean on paper might perform terribly under real query patterns. Use Ask mode to check your schema against the actual queries your application will run.

@src/db/schema.ts

Here are the 10 most common queries this application will execute:

1. Get all organizations a user belongs to, with their role in each
2. Get the current subscription and plan details for an organization
3. Get this month's usage breakdown for an organization
4. Generate an invoice: sum usage by type for a given org and month
5. Get the last 50 audit log entries for an organization
6. Find all organizations with past_due subscriptions
7. Get the monthly usage trend for an organization over the last 12 months
8. Search users by email across all organizations
9. Get all members of an organization with their roles
10. Count total API calls across all organizations for a given day

For each query:
- Write the SQL (or Drizzle query builder code) that answers it
- Identify if any query will be slow without an index we haven't created
- Flag any query that requires a full table scan
- Suggest schema changes if a query is unreasonably complex

This step almost always catches missing indexes. The usage trend query (query 7) typically needs a composite index on (organization_id, month) that was not obvious from the schema alone.

Step 4: Generate seed data that tests edge cases

Good seed data is not random data — it is data designed to exercise your schema’s constraints and expose edge cases.

Tip

Copy-paste prompt — Seed data generation:

@src/db/schema.ts

Create a seed script at src/db/seed.ts that populates the database with realistic test data covering these scenarios:

1. An organization on each plan type (free, pro, enterprise) with appropriate usage levels
2. A user who belongs to 3 organizations with different roles in each
3. An organization with usage that exceeds their plan limits (for testing overage billing)
4. An organization with a canceled subscription and outstanding invoice
5. A month boundary scenario: usage that spans the last day of January into February
6. An organization with exactly 0 usage (edge case for invoice generation)
7. 10,000 audit log entries for one organization (for testing pagination performance)
8. A user with the same email address attempting to join an organization twice (uniqueness constraint test)

Use Drizzle’s insert().values() syntax. Add a clear comment above each section explaining what scenario it tests.

Run the seed script. If any insert fails due to a constraint violation you did not expect, that is a schema design issue to fix now rather than in production.

Step 5: Write migrations with rollback support

Every migration should be reversible. Agent mode can generate both the “up” and “down” migration in one pass.

@src/db/schema.ts @src/db/migrations

Generate a migration to add a "billing_email" field to the organizations table:

1. The field is optional (nullable) initially
2. Add a migration that sets billing_email = owner's email for all existing organizations
3. Then make the field NOT NULL with a default
4. Generate both the forward migration and the rollback migration
5. The rollback should reverse each step in opposite order
6. Add a comment in the migration explaining why this is done in 3 steps instead of 1
   (hint: you cannot add a NOT NULL column to a table with existing rows without a default or data backfill)

Use Drizzle Kit migration format.

Step 6: Optimize with EXPLAIN ANALYZE

After your schema is populated with seed data, use Cursor to analyze query performance and suggest optimizations.

@src/db/schema.ts @src/db/seed.ts

For the 3 most expensive queries in our application (invoice generation, usage trend,
and audit log retrieval), do the following:

1. Write the query using Drizzle's query builder
2. Show me the raw SQL that Drizzle generates
3. Suggest what EXPLAIN ANALYZE output I should look for to confirm good performance
4. If a query does a sequential scan where an index scan would be better, suggest the index
5. If a query joins more than 3 tables, suggest whether a materialized view or denormalized
   column would be worth the trade-off

Keep recommendations practical -- do not over-index.

When This Breaks

The AI suggests denormalization too aggressively. Models trained on tutorial content tend to recommend storing computed values (like “total_amount” on invoices) to avoid JOINs. For a billing system, this creates data integrity risks — if a line item changes but the total is not recalculated, your invoices are wrong. Denormalize only when you have measured a performance problem and the integrity risk is acceptable.

Generated migrations are not idempotent. If a migration fails halfway through, re-running it should not crash. Add IF NOT EXISTS checks to CREATE TABLE and IF EXISTS checks to DROP. Ask Agent to make migrations idempotent explicitly.

Foreign key cascades delete too much data. ON DELETE CASCADE on the wrong relationship can wipe entire tables. For a billing system, almost nothing should cascade-delete. Use RESTRICT by default and CASCADE only for true ownership relationships (like deleting an organization deletes its usage records). Always code-review the ON DELETE behavior.

Schema changes break existing queries. When you rename a column or change a type, existing application code breaks. Use a two-phase migration: first add the new column, deploy code that writes to both old and new columns, then drop the old column in a subsequent migration. Ask Agent to generate the full two-phase plan.

The AI generates PostgreSQL-specific syntax for SQLite. If your development environment uses SQLite but production uses PostgreSQL, you will hit syntax differences. Be explicit about the target database in your prompt. Better yet, use Drizzle’s dialect abstraction and test against the same database engine you deploy to.

What’s Next

Frontend UI from Designs Build the billing dashboard UI that queries the schema you just designed.

Performance Optimization Profile your database queries under load and optimize the slow ones.

Security Audit Review your schema for security issues: SQL injection vectors, sensitive data exposure, access control gaps.