AI AI Tools Guidebook
Prompt Library

Database Schema Review Prompts for Safe Migrations

12 prompts that walk a schema like a future migrator — normalization, indexes, FK cascades, nullability, soft-delete, JSON columns, migration safety, ORM diff.

Published: Author: AI Productivity Guide Team 🌐 查看中文版本

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 AI 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 10× in volume. They cover the failures that survive code review: TEXT-for-everything types, JSON columns with implicit undocumented shape, cascade rules that don’t match application intent, missing composite indexes for the queries the product actually runs. Pair with the API contract review prompts so the schema and the API don’t drift apart.

Best for

  • Schema design before launch
  • Migration prep
  • Performance audits
  • Production DB reviews
  • ORM (Prisma / Drizzle) → SQL audits

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.

{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. Evaluate safety: (a) does it lock the table, (b) is the order safe under concurrent writes, (c) is it reversible, (d) does it backfill efficiently. Propose a safer phasing.

{paste migration}

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 (e.g., {commentable_type, commentable_id}). 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 schema 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}

Common mistakes

  • Using TEXT for everything
  • No NOT NULL constraints
  • Missing composite indexes for common filter combos
  • Inconsistent soft-delete across tables
  • Migrations that lock tables in production
  • JSON columns with implicit schemas no one documents

Tags: #Prompt #AI coding #AI coding

Related Articles