Migration Risk Analyzer

Migration Risk Analyzer

Systematic risk assessment for database migrations: parse DDL/DML operations, classify lock types and durations, estimate downtime, design rollback strategies, identify irreversible changes, and produce deployment recommendations with pre/post validation queries.

Reference Files

| File | Contents | Load When | | ---------------------------------- | ------------------------------------------------------- | --------------------------- | | references/lock-matrix.md | Operation-to-lock-type mapping for PostgreSQL, MySQL | Always | | references/safe-patterns.md | Online DDL patterns, zero-downtime migration techniques | Risk mitigation needed | | references/rollback-templates.md | Rollback scripts for common DDL operations | Rollback strategy requested | | references/validation-queries.md | Pre/post migration validation SQL templates | Always |

Prerequisites

• The migration SQL or migration file (Alembic, Django, Flyway, etc.)
• Target database engine (PostgreSQL, MySQL)
• Approximate table sizes for affected tables (for duration estimation)

Workflow

Phase 1: Parse Migration

Extract all operations from the migration script:

1. DDL operations — CREATE TABLE, ALTER TABLE (ADD/DROP/MODIFY COLUMN, ADD/DROP INDEX),

DROP TABLE, RENAME TABLE

1. DML operations — UPDATE, INSERT, DELETE on existing data
2. Index operations — CREATE INDEX, DROP INDEX, REINDEX
3. Constraint operations — ADD/DROP FOREIGN KEY, ADD/DROP CHECK, ADD/DROP NOT NULL

Phase 2: Assess Lock Risk

For each operation, determine the lock type and impact:

| Lock Level | Impact | Examples | | -------------- | --------------------------- | ------------------------------------------------ | | No lock | Zero impact | CREATE TABLE, CREATE INDEX CONCURRENTLY (PG) | | Share lock | Blocks writes, allows reads | CREATE INDEX (non-concurrent) | | Exclusive lock | Blocks all access | ALTER TABLE ADD COLUMN (MySQL 10M) | | ------------------------- | ------------------- | ----------------- | --------------------------- | | ADD COLUMN (nullable) | < 1s | < 1s | < 1s (PG) / minutes (MySQL) | | ADD COLUMN (with default) | < 1s | seconds | minutes (table rewrite) | | CREATE INDEX | < 1s | seconds | minutes-hours | | ADD NOT NULL | seconds | minutes | hours (full scan) | | Backfill UPDATE | seconds | minutes | hours |

Phase 4: Design Rollback

For each operation, determine reversibility:

| Operation | Reversible | Rollback | | ------------- | ---------- | ---------------------------- | | ADD COLUMN | Yes | DROP COLUMN | | DROP COLUMN | No | Data is lost | | ADD INDEX | Yes | DROP INDEX | | DROP TABLE | No | Data is lost | | RENAME COLUMN | Yes | RENAME back | | ALTER TYPE | Sometimes | May lose precision | | UPDATE data | Sometimes | Only if old values preserved |

For irreversible operations, recommend backup strategies.

Phase 5: Generate Report

Produce a risk assessment with deployment recommendation.

Output Format

````

Migration Risk Analysis

Summary

• Operations: {N} DDL, {M} DML
• Tables affected: {list with row counts}
• Overall risk: {High | Medium | Low}
• Estimated duration: {range}
• Requires downtime: {Yes | No}

Operation Risk Table

| # | Operation | Risk | Lock Type | Est. Duration | Reversible | |---|-----------|------|-----------|---------------|------------| | 1 | {SQL operation} | {High/Med/Low} | {lock type} | {time} | {Yes/No} |

Lock Analysis

• Exclusive locks: {list of operations that block all access}
• Maximum lock duration: {estimated time}
• Affected queries: {types of queries that will be blocked}

Rollback Strategy

Reversible Operations

-- Rollback for operation 1: {description}
{rollback SQL}
````

#### Irreversible Operations

- **{operation}** — IRREVERSIBLE. Mitigation:
  ```sql
  -- Backup before migration
  {backup SQL}
  ```

### Pre-Migration Checklist

- [ ] Database backup completed
- [ ] Rollback scripts tested in staging
- [ ] Traffic reduction confirmed (if needed)
- [ ] Monitoring and alerting configured
- [ ] Stakeholders notified
- [ ] Connection pool sized for lock wait

### Post-Migration Validation

```sql
-- Verify structural changes
{validation queries}

-- Verify data integrity
{integrity checks}

Deployment Recommendation

Strategy: {Online | Low-Traffic Window | Maintenance Window} Estimated downtime: {time or "None with proper execution"} Rollback time: {time} Risk mitigation: {specific recommendations}

## Calibration Rules

1. **Assume large tables.** If table size is unknown, assume it's large enough for
   locks to matter. Overestimating risk is safer than underestimating.
2. **Engine-specific analysis.** PostgreSQL and MySQL handle DDL very differently.
   PostgreSQL can add nullable columns without table rewrite; MySQL often cannot.
   Always target the specific engine.
3. **Irreversible means irreversible.** DROP COLUMN destroys data. No amount of
   rollback scripting recovers it. Flag every irreversible operation prominently.
4. **Test the rollback.** Rollback scripts must be tested in staging before the
   migration runs in production. Untested rollback is no rollback.
5. **Sequence matters.** The order of operations affects lock duration. Adding a
   column then backfilling then adding NOT NULL is safer than adding a NOT NULL
   column with a default.

## Error Handling

| Problem | Resolution |
|---------|------------|
| Database engine not specified | Ask. Lock behavior differs significantly between engines. |
| Table sizes unknown | Analyze without duration estimates. Flag that estimates require row counts. |
| ORM migration format (not raw SQL) | Parse the ORM migration file. Translate operations to SQL equivalents for analysis. |
| Migration has data-dependent logic | Flag conditional operations. Risk depends on data state at migration time. |
| Multiple migrations in sequence | Analyze each independently and as a group. Cross-migration lock accumulation is a risk. |

## When NOT to Analyze

Push back if:
- The migration is for a development/staging database — risk analysis is for production
- The migration only creates new tables (no ALTER, no existing data) — low risk by definition
- The user wants migration execution, not analysis — this skill assesses risk, it doesn't run migrations

## Rationalizations

| Rationalization | Reality |
|---|---|
| "It's backwards compatible" | Backwards compatible at the schema level doesn't mean backwards compatible at the application level — query plans, ORM mappings, and application code all interact |
| "We can roll back" | Rollback is not free — data written after migration may not survive rollback; DROP COLUMN has no rollback without backup |
| "It's a small table" | Table size is one factor — lock duration, concurrent write rate, and replication lag matter more than row count |
| "We've run this migration type before" | Past success doesn't predict future success — different data distribution, different load, different constraints |
| "Downtime window is long enough" | Estimate based on dev data, not production — migration on 10k rows takes seconds; on 50M rows with indexes, it takes hours |
| "The ORM handles it" | ORMs generate SQL, they don't guarantee safety — `ALTER TABLE` locking behavior is engine-specific and ORM-opaque |

## Red Flags

- No estimate of migration duration based on production data volume
- No rollback plan or rollback plan that doesn't account for data written post-migration
- Analyzing migration SQL without checking the current table size and write rate
- No consideration of replication lag in multi-replica setups
- Assuming zero downtime without verifying lock behavior for the specific DDL operation
- Skipping index analysis — adding an index on a large table can lock writes for minutes to hours

## Verification

- [ ] Production table sizes and row counts documented for all affected tables
- [ ] Lock behavior identified for each DDL statement (exclusive lock, no lock, etc.)
- [ ] Migration duration estimated using production-scale data, not dev fixtures
- [ ] Rollback plan documented with specific steps and data preservation guarantees
- [ ] Concurrent write impact assessed — what happens to in-flight transactions during migration
- [ ] Replication lag impact assessed for multi-replica configurations

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [Mathews-Tom](https://github.com/Mathews-Tom)
- **Source:** [Mathews-Tom/armory](https://github.com/Mathews-Tom/armory)
- **License:** MIT
- **Homepage:** https://mathews-tom.github.io/armory/

Install and usage instructions live in the source repository linked above.