uniswap/ai-toolkit/analyze-migrations

Migration Safety Analyzer

Statically analyze database migration files to detect patterns that cause table locks, data loss, or production outages before they reach production.

Step 1: Detect migration framework and locate files

Scan the repository for migration file patterns:

If the user specifies a path or framework, use that. Otherwise auto-detect by searching for these patterns with Glob and Grep.

Report which framework was detected and how many migration files were found. If no migrations are found, report that clearly and stop.

Scope: analyze the 20 most-recent migrations by filename sort order (timestamp prefix is standard for all frameworks). If the user requests a specific migration or range, use that instead.

Step 2: Build migration inventory

For each migration file in scope:

• Record the filename, detected framework, and direction (up/forward/apply vs. down/rollback/revert)
• Note whether a corresponding rollback migration exists (critical gap if missing for destructive operations)
• Extract the raw SQL or ORM operation list

For ORM-based migrations (Rails, Django, Alembic, TypeORM), translate operations to their SQL equivalents before analysis:

• add_column → ALTER TABLE ... ADD COLUMN
• remove_column → ALTER TABLE ... DROP COLUMN
• change_column / alter_column → ALTER TABLE ... ALTER COLUMN
• add_index → CREATE INDEX
• create_table → CREATE TABLE

Step 3: Static safety analysis

Analyze each migration for the following patterns. Evaluate against both PostgreSQL and MySQL semantics unless the stack is known.

The three tables below are a starting checklist, not an allowlist. They cover the common cases; they do not enumerate every way a migration can lock a table or lose data. If a migration does something dangerous that no row describes — a stored-procedure change, a partition swap, a trigger that rewrites rows, a vendor-specific DDL — report it anyway at the severity you judge appropriate and say it was found outside the checklist. A finding you drop because it has no matching row is a production incident the report failed to prevent.

CRITICAL — causes table locks or data loss

WARNING — potential downtime or deployment risk

INFO — worth reviewing

Step 4: Generate safety report

Structure the output as a migration safety report grouped by severity.

Header: framework detected, files analyzed, analysis date.

CRITICAL findings (if any): formatted as:

[CRITICAL] <migration-filename>:<line>
Pattern: <what was found>
Risk: <what can go wrong in production>
Safe alternative:
  <concrete rewrite or multi-step approach>

WARNING findings: same format with severity label.

INFO findings: condensed list, one line per finding.

Summary table:

| Severity | Count | Migration Files Affected |
|----------|-------|--------------------------|
| CRITICAL |   N   | file1.sql, file2.rb      |
| WARNING  |   N   | ...                      |
| INFO     |   N   | ...                      |

Verdict:

• No CRITICAL and no WARNING → SAFE TO DEPLOY
• No CRITICAL but WARNING(s) present → DEPLOY WITH CAUTION — review WARNING findings
• Any CRITICAL unresolved → BLOCKED — resolve CRITICAL findings before deploying

For each CRITICAL and WARNING finding, provide a concrete safe alternative. Examples:

• ADD COLUMN NOT NULL without default → add nullable first, backfill data, then add NOT NULL constraint in a separate migration
• DROP COLUMN → use a multi-phase approach: deprecate in app code, verify no reads/writes, then drop in a later migration
• Missing CONCURRENTLY on index → use CREATE INDEX CONCURRENTLY with disable_ddl_transaction! (Rails) or op.execute("CREATE INDEX CONCURRENTLY ...") (Alembic)
• RENAME COLUMN → use expand/contract: add new column, dual-write in app, backfill, switch reads, remove old column

Success criteria

• Detected the correct migration framework
• Analyzed all in-scope migration files
• Identified all CRITICAL patterns with specific file and line references
• Provided concrete safe alternatives for every CRITICAL and WARNING finding
• Delivered a clear SAFE / BLOCKED / CAUTION verdict