A schema mistake is the most expensive kind of bug. It lives for years, every backfill makes it worse, and the migration that would fix it is the one nobody wants to schedule. These prompts force an AI model to walk the schema from the perspective of a future engineer who has to query it under load, migrate it under live writes, or scale it 10x in volume. They target the failures that survive code review: TEXT-for-everything types, JSON columns with an implicit undocumented shape, cascade rules that do not match application intent, and missing composite indexes for the queries the product actually runs. Pair them with the API contract review prompts so the schema and the API do not drift apart.
TL;DR
- Paste your DDL or ORM schema, pick the relevant prompt, and ask for a per-item verdict, not prose.
- Run prompts 1, 2, and 4 before launch; run 7 (migration safety) before every production
ALTER TABLE. - Use a reasoning model: Claude Opus 4.7 or Sonnet 4.6 are strongest at relational reasoning across joins; both ship a 1M-token context window (June 2026), so a 200-table schema fits in one paste. GPT-5.5 and Gemini 3.1 Pro are solid alternatives.
- The AI flags risk; it does not run your migration. Validate every “safe phasing” suggestion against the Postgres lock rules in the table below.
Best for
- Schema design before launch
- Migration prep on a live table
- Performance audits
- Production DB reviews
- ORM (Prisma / Drizzle) to SQL audits
Which model to use
Schema review is reasoning-heavy: the model has to hold table relationships, query shapes, and lock semantics in its head at once. As of June 2026:
| Model | Context | Best for | Notes |
|---|---|---|---|
| Claude Opus 4.7 | 1M tokens | Deepest reasoning on FK graphs, cascades, multi-table joins | Top SWE-bench Verified (87.6%); runs in Claude Code |
| Claude Sonnet 4.6 | 1M tokens | The daily workhorse for schema review | Cheaper API ($3/$15 per 1M tok); bundled in Claude Pro |
| GPT-5.5 | ~320 pages in-app on Plus (1M on $200 Pro) | Strong on writing the corrected SQL | Picker: use Thinking/Pro for migration safety |
| Gemini 3.1 Pro | 1M tokens | Pasting a very large warehouse schema (200+ tables) | $2/$12 per 1M tok API |
A 1M-token window matters here: paste the entire schema plus your 10 hottest queries in one shot rather than feeding tables one at a time, where the model loses the cross-table picture.
1. Schema-design smell finder
Below is my schema (DDL or Prisma / Drizzle). List the top 5 design smells: (a) under-normalization, (b) over-normalization, (c) wrong types (TEXT for everything), (d) missing constraints, (e) implicit assumptions. Each with a 1-line fix.
[paste schema]2. Index review
Below: schema + 10 most common queries. For each query: (a) which indexes hit, (b) which scan tables, (c) suggested index (single / composite), (d) trade-off vs write cost. Note any index that should be built with CREATE INDEX CONCURRENTLY.
[paste]3. Foreign-key & cascade audit
Below: schema with FKs. Evaluate each: (a) is the cascade rule (CASCADE / SET NULL / RESTRICT) correct, (b) does it match the application's intent, (c) what breaks if the parent is deleted in production. Flag issues.
[paste]4. Nullability audit
Below schema. For each nullable column, ask: (a) is null meaningful or a placeholder, (b) does it cause query branching, (c) should it have a default. Flag fields that should be NOT NULL with a default.
[paste]5. Soft-delete strategy
Below schema. Evaluate soft-delete strategy: (a) is it consistent across tables, (b) deleted_at index, (c) cascading soft-delete via FK, (d) view to hide deleted rows. Propose a unified approach.
[paste]6. Audit-column completeness
Below schema. For each table, check: (a) created_at, (b) updated_at, (c) created_by / updated_by, (d) version. Flag tables missing audit columns and propose defaults.
[paste]7. Migration safety
Below: a migration SQL file targeting Postgres 16+. Evaluate safety: (a) does any statement take an ACCESS EXCLUSIVE lock and block reads/writes, (b) is the order safe under concurrent writes, (c) is it reversible, (d) does it backfill in batches rather than one statement. Rewrite it as a lock-safe phased migration and add SET lock_timeout to every step.
[paste migration]This is the prompt to run before every production ALTER TABLE. See the lock-safety table below to sanity-check whatever the model proposes.
8. Many-to-many table review
Below is a join table for many-to-many. Review: (a) composite PK vs surrogate, (b) ordering / uniqueness rules, (c) timestamps on the join row, (d) cascade rules, (e) indexes for both directions.
[paste]9. Polymorphic-relation review
Below schema uses a polymorphic relation (for example, a commentable_type + commentable_id pair). Evaluate: (a) trade-offs vs separate tables, (b) FK enforcement gaps, (c) query patterns, (d) when to migrate to per-type tables.
[paste]10. JSON / JSONB column audit
Below schema has N JSON / JSONB columns. For each: (a) is the shape implicit and undocumented, (b) is it queried by path, (c) does it have a GIN index, (d) is a relational refactor warranted. Output a verdict per column.
[paste]11. Scaling-readiness review
Below schema. Evaluate readiness to scale 10x in volume: (a) tables likely to bloat, (b) partition candidates, (c) hot-spot indexes, (d) write amplification from triggers / FK. Output a 5-row report.
[paste]12. ORM-to-SQL diff
Below: my Prisma / Drizzle schema and the generated SQL. Find: (a) ORM fields without matching SQL constraints, (b) SQL features the ORM lost, (c) implicit assumptions the ORM hides. Propose hand-rolled SQL additions.
[paste]Lock-safe migration primitives (Postgres)
AI is good at spotting that a migration is dangerous and weak at remembering the exact lock each statement takes. Keep this table next to prompt 7 and check every “safe” rewrite against it. Lock behavior verified against the PostgreSQL ALTER TABLE docs (June 2026).
| Operation | Default behavior | Lock-safe approach |
|---|---|---|
Add column with constant DEFAULT | Fast, no table rewrite since Postgres 11 | Safe as-is; avoid volatile defaults like now(), which still rewrite the whole table |
Add NOT NULL to an existing column | Full-table scan under ACCESS EXCLUSIVE | Add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL |
| Create an index | SHARE lock blocks writes for the build | CREATE INDEX CONCURRENTLY (runs outside a transaction; can leave an INVALID index on failure, so re-check) |
| Add a foreign key | Scans both tables under ACCESS EXCLUSIVE | ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT (validation uses SHARE UPDATE EXCLUSIVE, does not block writes) |
| Backfill a new column | One UPDATE locks every row and bloats WAL | Batch 1,000 to 10,000 rows with a short sleep between batches |
| Drop a column | Fast metadata-only change | Safe, but drop the app’s references first or reads break |
Set SET lock_timeout = '5s'; at the top of every migration so a slow statement fails fast instead of queueing behind every other query. For large tables, follow the expand-contract (parallel-change) pattern: add the new shape, dual-write, backfill in batches, then drop the old shape in a separate deploy.
Tools that catch what the model misses
- Squawk is a static linter for Postgres migration SQL. Run it in CI to block statements that take an
ACCESS EXCLUSIVElock or break existing clients (latest release 2.54.0, May 2026). Use it as a deterministic backstop to the AI review, not a replacement. - pgroll automates the expand-contract pattern and serves two schema versions at once so a rollout never locks the table.
- For ORM-first teams, run prompt 12, then verify the generated SQL with
prisma migrate diffor your Drizzle migration output before applying.
FAQ
Which AI model is best for reviewing a database schema? For relational reasoning across joins and cascade rules, Claude Opus 4.7 and Sonnet 4.6 lead as of June 2026, and their 1M-token context lets you paste a full schema at once. GPT-5.5 and Gemini 3.1 Pro are strong alternatives, and Gemini’s 1M window is handy for very large warehouse schemas. For the migration-safety prompt, switch GPT-5.5 to a Thinking/Pro mode rather than the Instant default.
Can I trust the AI to write the migration for me?
Treat it as a senior reviewer, not an operator. It reliably flags ACCESS EXCLUSIVE locks, missing NOT VALID phasing, and one-shot backfills. It still hallucinates lock details, so check every rewrite against the table above and lint the SQL with Squawk before running it on production.
Will adding a NOT NULL column lock my table?
Adding a column with a constant DEFAULT does not rewrite the table on Postgres 11 and later, so it is fast. Adding NOT NULL to an existing column does a full scan under an ACCESS EXCLUSIVE lock; the safe path is a CHECK ... NOT VALID constraint, then VALIDATE, then SET NOT NULL.
Should I paste my whole schema or one table at a time? Paste the whole schema plus your 10 hottest queries in one prompt. Schema problems are cross-table (a missing composite index, a wrong cascade, a denormalized field that should be a join), and a model only sees them with the full graph in context. The 1M-token windows on current models make this practical.
How do I review a Prisma or Drizzle schema?
Run prompt 1 on the ORM schema for design smells, then prompt 12 to diff the ORM definition against the generated SQL. ORMs silently drop partial indexes, check constraints, and ON DELETE rules, so the diff is where most surprises live.
Common mistakes
- Using
TEXTfor everything instead of typed, length-bounded columns - Skipping
NOT NULLconstraints, so nulls leak into every query branch - Missing composite indexes for the filter combos the product actually runs
- Inconsistent soft-delete across tables
- Migrations that take an
ACCESS EXCLUSIVElock in production - JSON columns with implicit shapes that no one documents
Related
- API contract review prompts
- Security audit prompts
- Performance optimization prompts
- Code review prompts
- Deployment check prompts
Tags: #Prompt #AI coding #AI coding