SkillSmith DB Schema Migration Guardian
Changing a production schema is the one engineering step where a single wrong ALTER locks the whole table or silently destroys data — and the people doing it usually have no DBA to call a halt.
What it does
Most tools either generate the SQL or run the migration. Neither tells you whether the change should be made, which lock it will take, whether it collides with old-and-new code during a rolling deploy, how long the backfill will run, or how to roll back when it goes wrong. They automate execution — not the decision and the review.
Give it your existing schema or ORM models (Prisma, Drizzle, TypeORM, Rails, Django, SQLAlchemy) plus the change you want, and it produces a reviewable, runnable plan: paired forward + rollback migrations, a zero-downtime expand-contract rollout, lock/long-transaction/large-table backfill warnings, and before/after data-integrity check scripts. Everything is plain text you run yourself — it never connects to your production database.
How it works
A 7-phase workflow, each with a hard quality gate before the next:
| Phase | Output |
|---|---|
| 0. Detect environment | Identify ORM + database (Postgres vs MySQL); ask, never guess, when ambiguous — lock behavior differs sharply |
| 1. Analyze change & risk | Per-change risk table: lock type x blocks reads/writes x large-table danger x safe alternative |
| 2. Forward + rollback | Paired migrations; explicit reverse SQL hand-written when the ORM won't auto-generate one |
| 3. Expand-contract plan | Zero-downtime rollout for destructive changes: add → dual-write → backfill → switch → remove, each step marked reversible-or-not |
| 4. Backfill scripts | Batched, re-runnable, throttleable backfills for large tables — never a single full-table UPDATE |
| 5. Integrity checks | pre / post / reconciliation scripts emitting PASS/FAIL, usable as a deployment gate |
| 6. Self-review | A consolidated list of human-review checkpoints and the exact "points of no return" |
⭐ Featured example — a 50M-row column split, zero downtime
Input: "Postgres + Prisma. users has a single name TEXT column, ~50M rows. Split it into first_name / last_name, backfill, no downtime."
Output: the change is graded destructive and routed to expand-contract, with the lossy parse ("Mary Jane Watson", CJK names with no space, double-spaced values) flagged up front. The risk table marks the exact lock each step takes, the 50M-row danger, and a safe alternative for every one:
![Stage 1 risk analysis: per-DDL lock, 50M-row danger, and safe alternative, with the change classified as destructive]
It doesn't stop at the diagnosis — it emits the actual zero-downtime plan (~3 deploys) plus the paired forward/rollback SQL, with DROP COLUMN name isolated as the irreversible point of no return:
![Stage 3 expand-contract plan and Stage 2 paired forward + rollback migrations]
The backfill never goes into a Prisma migration; it ships as a standalone batched keyset script. The DROP COLUMN is gated behind a backup and a "no remaining NULLs" integrity check.
Why it's different
- It sells decision and review, not SQL — and it does not replace free executors. pgroll, Atlas, gh-ost, and pt-online-schema-change are executors: once you already know a change is safe, they run it with zero downtime. They don't decide whether it should be done, how it could break, or how to retreat. This Skill is the layer in front of them: lock-behavior explanation, the expand-contract strategy, mandatory paired rollback, and integrity verification — risk moved forward into a step someone without a DBA can read line by line.
- Stacked, not either/or. Run the migration with pgroll or your ORM; decide whether and how to recover with this Skill.
- Multi-ORM, multi-DB: Prisma / Rails / Django as first-class support, covering Postgres vs MySQL lock differences (pgroll is Postgres-only).
- Built for people with no DBA who hold production data but have nobody to stop the change before it ships.
Disclaimer / Scope
All outputs are plain-text artifacts for you to review and execute yourself. This Skill does not connect to, or run anything against, your live database. It does not guarantee zero incidents — the guardrails (staging validation with production-scale data, human-review checkpoints, points of no return) must be carried out by you in your own environment. API and lock behavior evolve across DB/ORM versions; confirm against your target's current docs before going live.
