---
name: migration-plan-writer
description: "Draft an incremental database migration plan with rollback gates from a before/after schema diff — no single-shot migrations that can't be reversed."
title: Migration Plan Writer
category: code-development
difficulty: intermediate
author: admin
icon: 🚚
input: structured-data
output: code
phase: pre
domain: ops
tags: database-migration,schema-evolution,rollback-safety,sql,orm-diff,incremental-deployment,zero-downtime,data-migration,reversible-changes,production-safety,verification-query,infrastructure
best_for:
  - PostgreSQL/MySQL schema upgrades in live systems
  - Zero-downtime deployments with data model changes
  - Risk mitigation for large-table migrations
  - Multi-phase infrastructure changes requiring rollback gates
---

## Description

Input: two SQL schemas (or an ORM diff), plus an estimated row count for each affected table. Output: a numbered migration plan split into phases, each phase reversible independently, with a rollback script and a verification query for every phase.

## Why it works

The biggest outages from schema changes come from one-shot migrations that can't be rolled back once traffic hits the new code. Forcing the plan to decompose into independently-reversible phases eliminates the whole class of 'we have to forward-fix in prod' incidents.

## How it works

1. Parse both schemas into a normalized form (tables, columns, constraints, indices). 2. Classify each diff entry into one of: additive (always safe), destructive (needs a data migration), or transform (backfill). 3. Order phases so every destructive step is preceded by a deploy-new-code step that tolerates both old and new shapes. 4. For each phase, generate forward SQL + rollback SQL + a post-phase verification query that must return the expected row counts. 5. Annotate each phase with estimated lock time based on row count and table size.