migration-safety-inspector

What this is for

Database migrations are the leading cause of production incidents. Missing CONCURRENTLY, ALTER COLUMN TYPE causing full-table rewrites, destructive DROP without rollback, unsafe constraint additions with no lock-timeout - every SQL migration file carries risks that schema review often misses.

This skill automates the safety review: a deterministic collector finds all SQL migration files, parses every DDL statement, and checks 20+ safety rules. The LLM then assesses the blast radius of each finding against the actual schema and usage patterns, and recommends safer alternatives.

PROTECTION RULE - never ~/.claude/

Read-only skill. Guard required if write mode added later.

What You Must Do When Invoked

During analysis, assign a confidence level to each finding: proven (confirmed by evidence), likely (strong signal, needs review), or suspected (weak signal).

Step 1 - -help/-h check

If -help or -h is passed, print the ## Usage block below and stop.

Step 2 - Confirm project directory

Confirm -ProjectDir is provided and the path exists.

Step 3 - Run collector

& "<SKILL_DIR>/scripts/migration-scan.ps1" -ProjectDir "<path>"

Step 4 - LLM reads findings

Read the JSON output. For each finding, note the severity, line, description, and DDL statement. The collector categorizes findings by safety rule:

• table-rewrite: ALTER COLUMN TYPE, ADD COLUMN NOT NULL without DEFAULT
• missing-concurrently: CREATE INDEX, REINDEX, VACUUM without CONCURRENTLY
• destructive: DROP TABLE, DROP COLUMN, TRUNCATE
• unsafe-constraint: ADD FOREIGN KEY without NOT VALID
• missing-if-exists: DDL referencing objects without IF EXISTS / IF NOT EXISTS
• dml-on-migration: INSERT/UPDATE/DELETE on existing tables in migration
• missing-transaction: DDL statements not wrapped in a transaction

Step 5 - Assess blast radius per finding

For each finding, the LLM should:

• Search the project for the affected table/column/index usage patterns
• Estimate: is this acceptable in a maintenance window?
• Consider: rows affected, locks held, replication lag, rollback cost
• Recommend a safer alternative (e.g. NOT VALID, CONCURRENTLY, multi-step)

Step 6 - Write report

Write migration-safety-report.md to the working directory with:

• Executive summary (files scanned, total findings, by severity)
• Critical findings (destructive, table rewrites on large tables)
• Medium findings (missing CONCURRENTLY, unsafe constraints)
• Low findings (missing IF EXISTS, missing transaction wrappers)
• Safer alternatives per finding
• Open questions (cannot determine table size or usage)

Usage

/migration-safety                           # interactive, prompts for directory
/migration-safety <dir>                     # scan SQL migration files in directory
/migration-safety -help                     # show usage

Report Format

migration-safety-report.md with:

• Executive summary (files scanned, statements, total findings by severity)
• Critical findings (destructive operations, table rewrites on referenced columns)
• Medium findings (missing CONCURRENTLY, unsafe constraints, DML on existing tables)
• Low findings (missing IF EXISTS, missing transaction wrappers)
• Per finding: file, line, DDL, blast radius estimate, safer alternative
• Open questions