db-migrate

by codekungfu

Install this Skill

curl -fsSL -o db-migrate.zip https://skill.seembox.com/api/v1/skills/db-migrate/download && unzip db-migrate.zip

SKILL.md About

---
name: db-migrate
description: Deep database migration workflow—expand/contract, backward-compatible deploys, backfills, locking risks, and verification. Use when changing production schemas safely with zero or low downtime.
---

# DB Migrations

Production schema changes fail when old and new code disagree during rollout. Prefer **expand/contract**: add compatible changes first, remove old shapes later.

## When to Offer This Workflow

**Trigger conditions:**

- ALTER TABLE in production; large table rewrites
- Blue/green deploys coupled to schema state
- Need zero-downtime or low-downtime migrations

**Initial offer:**

Use **six stages**: (1) classify change, (2) expand phase, (3) backfill & dual-write, (4) flip reads/writes, (5) contract phase, (6) verify & rollback). Confirm database engine (PostgreSQL, MySQL, etc.).

---

## Stage 1: Classify Change

**Goal:** Additive vs destructive; lock risk (full table rewrite vs instant metadata change).

**Exit condition:** Migration labeled as expand or contract with risk notes.

---

## Stage 2: Expand Phase

**Goal:** Add nullable columns or new tables without breaking currently deployed code.

### Practices

- Avoid DEFAULT clauses that lock large tables badly on some engines (use phased backfill instead)

---

## Stage 3: Backfill & Dual-Write

**Goal:** Throttled batch backfill; dual-write old and new representations during transition when needed.

---

## Stage 4: Flip Reads/Writes

**Goal:** Deploy code that reads new columns only after backfill completes; use feature flags for staged rollout.

---

## Stage 5: Contract Phase

**Goal:** Drop old columns only after no code references them (search repo, logs, feature usage).

---

## Stage 6: Verify & Rollback

**Goal:** Monitor errors, slow queries, replication lag; rollback = redeploy previous app version + avoid destructive steps until stable.

---

## Final Review Checklist

- [ ] Change classified; expand/contract path clear
- [ ] Additive migrations before dependent code
- [ ] Backfill throttled and verified
- [ ] Read/write cutover sequenced with flags
- [ ] Contract only after references gone
- [ ] Monitoring and rollback tested

## Tips for Effective Guidance

- Long transactions on migrations can cause outages—chunk work.
- Use online schema tools (pt-online-schema-change, etc.) when appropriate.

## Handling Deviations

- SQLite/embedded engines have different locking—validate per engine.

_meta.json Metadata

{
  "ownerId": "kn79jhw85bss2z4dbpshhxg64x82zn3p",
  "publishedAt": 1774601027190,
  "slug": "db-migrate",
  "version": "1.0.0"
}