ORM Migration Diff Review Prompt

This prompt is designed for backend engineers and data platform teams who need to review auto-generated ORM migration diffs before applying them to a database. The core job is to catch implicit, dangerous, or incorrect schema changes that an ORM's migration generator can silently introduce—such as missing index operations, unexpected default value changes, or operations that the ORM generated incorrectly based on incomplete model metadata. The ideal user is an engineer who understands the application's data model and can interpret raw SQL, but who wants a structured, human-readable review that highlights risks the ORM's diff output obscures.

Use this prompt when you have a raw SQL diff produced by an ORM's migration tool (e.g., Alembic, Prisma Migrate, TypeORM, Entity Framework, or Django migrations exported as SQL) and you need a pre-flight review before executing the migration. The prompt requires the raw SQL diff as its primary input, along with optional context about the application's data model, known sensitive tables, and any business rules that should constrain schema changes. Do not use this prompt for reviewing hand-written migration scripts where the author's intent is explicit in comments, for performance tuning of query plans, or for post-migration data reconciliation—those are separate workflows with their own dedicated playbooks in this pillar.

This prompt is not a replacement for a full migration review by a senior engineer, nor does it execute the migration or validate it against a live database. It is a structured analysis layer that surfaces what the ORM's diff output implies but does not explicitly state. For high-risk migrations—those involving large tables, column drops, data type changes, or constraint additions—always pair this prompt's output with the sibling playbooks for rollback safety, transaction boundary review, and large-table ALTER assessment. The prompt's value is in making implicit changes explicit before they become expensive production incidents.

The following prompt template is designed to be copied directly into your AI harness, prompt library, or evaluation framework. It accepts a raw SQL diff as its primary input and instructs the model to act as a senior database engineer reviewing an auto-generated migration. The template uses square-bracket placeholders for all dynamic inputs, ensuring you can swap in different diffs, risk profiles, and output schemas without modifying the core instructions. Every placeholder is documented so you know what to provide and why it matters for the review quality.

textCopy
You are a senior database reliability engineer reviewing an auto-generated ORM migration diff before it is applied to production. Your job is to identify implicit changes, missing operations, default value surprises, and incorrect ORM-generated SQL that could cause data loss, downtime, or correctness bugs.

## INPUT
Review the following raw SQL migration diff:
```sql
[RAW_SQL_DIFF]

CONTEXT

• ORM Framework: [ORM_NAME_AND_VERSION]
• Source ORM Models: [ORM_MODEL_DEFINITIONS]
• Target Database: [DATABASE_ENGINE_AND_VERSION]
• Table Sizes (approximate rows): [TABLE_SIZE_ESTIMATES]
• Deployment Strategy: [DEPLOYMENT_STRATEGY]
• Known Application Constraints: [APPLICATION_CONSTRAINTS]

OUTPUT SCHEMA

Return a JSON object with the following structure: { "summary": "string (one-paragraph executive summary of risks found)", "findings": [ { "severity": "critical | high | medium | low", "category": "data_loss | downtime | correctness | performance | missing_operation | implicit_change | default_surprise", "location": "string (line reference or operation description)", "description": "string (what the ORM generated and why it's problematic)", "impact": "string (what happens if this migrates as-is)", "suggested_fix": "string (specific SQL or ORM config change to resolve)", "rollback_safe": true | false } ], "missing_operations": [ { "operation": "string (e.g., CREATE INDEX, ANALYZE, UPDATE)", "reason": "string (why this operation is needed based on the schema change)", "suggested_sql": "string (the missing SQL statement)" } ], "rollback_plan": "string (step-by-step rollback instructions or null if fully reversible)", "overall_risk": "safe_to_apply | needs_review | do_not_apply" }

CONSTRAINTS

• Flag any DROP COLUMN, DROP TABLE, or TRUNCATE as critical severity.
• Flag any ALTER COLUMN with type changes that could truncate data or lose precision.
• Flag any new NOT NULL constraint without a DEFAULT value on a table with existing rows.
• Flag any missing index on new foreign key columns.
• Flag any operation that acquires an ACCESS EXCLUSIVE lock on large tables.
• If the ORM generated a table rename as DROP + CREATE, flag it as data loss.
• If the deployment strategy is blue-green or zero-downtime, flag any operation incompatible with that strategy.
• Do not invent findings. Only report issues present in the provided diff.

EXAMPLES

Example finding for a missing index: { "severity": "high", "category": "missing_operation", "location": "new column 'user_id' on table 'orders'", "description": "ORM added foreign key column 'user_id' but did not generate a corresponding index.", "impact": "Joins and lookups on user_id will perform sequential scans on the orders table, causing performance degradation under load.", "suggested_fix": "Add 'CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);' as a separate migration step.", "rollback_safe": true }

TOOLS

You have access to the following tool:

• validate_sql_syntax(sql: string): Returns syntax errors or confirms validity for the target database engine. Use this tool to verify any suggested fix SQL before including it in your output.

RISK_LEVEL

[RISK_LEVEL] If RISK_LEVEL is "high", require explicit human approval before any destructive operation is suggested. If RISK_LEVEL is "medium", flag but do not block. If RISK_LEVEL is "low", focus on correctness and performance only.

To adapt this template for your stack, replace each placeholder with concrete values before sending it to the model. The [RAW_SQL_DIFF] should be the exact output of your ORM's migration generation command (e.g., alembic upgrade --sql, prisma migrate diff, or django makemigrations --dry-run). The [ORM_MODEL_DEFINITIONS] should include the before and after model definitions so the model can cross-reference intent against the generated SQL. For [TABLE_SIZE_ESTIMATES], provide approximate row counts so the model can assess lock duration and index creation time. The [DEPLOYMENT_STRATEGY] field is critical: if you use blue-green or rolling deployments, the model will flag operations that break backward compatibility between old and new application versions. The [RISK_LEVEL] parameter controls the review's conservatism—set it to "high" for financial or healthcare data, and "low" for internal development databases. After copying the template, run it through your evaluation suite with known-good and known-bad migration diffs to calibrate the model's sensitivity before trusting it on production migrations.

The ORM Migration Diff Review Prompt is designed to sit inside a pre-apply review gate, not as a standalone chat interaction. The typical integration point is a CI pipeline step that triggers when a new migration file is added to a pull request. The harness extracts the raw SQL diff from the ORM's migration output (e.g., alembic upgrade --sql, prisma migrate diff, or django-admin sqlmigrate), feeds it into the prompt as [RAW_SQL_DIFF], and posts the structured review as a comment on the PR. This ensures every migration gets a consistent, automated first-pass review before a human ever looks at it.

The harness must validate the model's output against the expected [OUTPUT_SCHEMA] before posting results. At minimum, validate that the response is valid JSON, that the findings array contains objects with the required severity, category, location, and explanation fields, and that severity values are constrained to the allowed enum (CRITICAL, HIGH, MEDIUM, LOW, INFO). If validation fails, retry once with the same input and an explicit instruction appended: 'Your previous response failed schema validation. Return ONLY valid JSON matching the output schema exactly.' If the second attempt also fails, post a failure notification to the PR and escalate to the on-call channel. Do not silently swallow malformed reviews.

For model selection, prefer a model with strong SQL reasoning and structured output discipline. GPT-4o, Claude 3.5 Sonnet, and Gemini 1.5 Pro all perform well on this task when given the raw SQL diff and a clear output schema. Avoid smaller or older models that may hallucinate index operations or misidentify generated SQL patterns. Set temperature to 0 or a very low value (0.1 maximum) to reduce variability in severity classification. If your ORM generates exceptionally large diffs (over 8,000 tokens of SQL), split the diff by table or operation type and run the prompt in parallel batches, then merge the findings arrays. Always log the full prompt, raw SQL input, model response, and validation result to your prompt observability platform for audit and regression testing.

The human approval gate is critical. The prompt's output should be treated as a review assistant, not an auto-approver. Configure your CI pipeline to block merge if any CRITICAL finding is detected, and to require a human reviewer to explicitly acknowledge HIGH severity findings before the migration can be applied. For MEDIUM and below, the findings should be visible in the PR but not blocking. This tiered gating prevents the most dangerous migration mistakes—implicit schema changes, missing index operations, and default value surprises—from reaching production while keeping the review workflow fast for low-risk changes. Pair this prompt with the Migration Rollback Safety Review Prompt for a complete pre-deployment safety net.