data-migration-runbook-author

name

data-migration-runbook-author

description

Author the operator-executable runbook for a DATA move — backfill, re-shard, store cutover, tenant move, CDC initial load — that a stranger can run under pressure: preconditions gated on verified backups and dry-run evidence, batching with throttles and pause/resume, per-batch and per-stage verification queries (counts, checksums, sampled equality) with expected outputs, explicit abort criteria naming the safe halt state, rollback per stage composed from rollback-runbook-author conventions, and no-return points flagged for human approval. Authors the DOCUMENT only — never executes; destructive steps are human-run. Consumes an approved plan (schema-evolution-planner) and safety review (secure-migration-reviewer) rather than re-deriving them. Use when asked to write the runbook for a backfill/migration/cutover or to make a data move operator-safe. Do NOT use for the change SEQUENCE itself or general release rollbacks (rollback-runbook-author).

Data Migration Runbook Author

Purpose

Data moves fail operationally more often than logically: the batch size that melted the primary at hour two, the verification nobody ran because it wasn't written down, the abort decision improvised at 3 a.m. by whoever was awake. This skill authors the runbook — numbered steps with per-step verification and expected output, batching and throttle values with their rationale, abort criteria that name the safe halt state, and rollback per stage — so executing a backfill, re-shard, cutover, or tenant move is reading, not improvising. It writes the document; it runs nothing. The change's staged design comes in as an approved schema-evolution-planner plan (or equivalent), and its DDL safety verdict from secure-migration-reviewer — the runbook cites both and re-derives neither.

Use When

• Use when: a backfill, re-shard, store-to-store cutover, tenant data move, or CDC initial load needs an operator-executable procedure.
• Use when: an approved evolution plan's migrate stage says "backfill with parity gate" and someone must now write HOW, batch by batch.
• Use when: a previous data move was improvised (or went wrong) and the next one must be runbook-governed.
• Use when: a data move will be executed by someone other than its designer — the handoff artifact is this runbook.
• Do NOT use when: the change SEQUENCE (expand/migrate/contract stages, compatibility windows) is still being designed — that is schema-evolution-planner; the runbook implements an approved plan.
• Do NOT use when: reviewing migration DDL for safety (locks, destructive ops, RLS gaps) — that is secure-migration-reviewer, whose verdict is a runbook PREREQUISITE.
• Do NOT use when: authoring the rollback plan for a general RELEASE (artifact redeploys, flag kills, blue/green) — that is rollback-runbook-author; this skill composes its conventions for the data-move case specifically.
• Do NOT use when: the tenant-scoping strategy of the move (which isolation model, how tenant context flows) is undecided — that design is multi-tenant-data-architect.
• Do NOT use when: what's needed is a reusable operator PROMPT TEMPLATE for a recurring gated operation — gated-deployment-prompt-template owns that form; this skill writes the specific move's full runbook (the two compose: a recurring data move may get a template derived from this runbook).

Inputs to Inspect

1. The approved plan: stages, parity definition, read-switch condition, and abort philosophy from schema-evolution-planner (or the equivalent decision record for non-schema moves). No plan → stop.
2. The safety review: secure-migration-reviewer's verdict on the migration/DDL involved, including its rollback assessment and required negative tests.
3. The operational facts: table/store sizes, write rates, peak/quiet windows, replication topology and lag behavior, maintenance-window policy, and the throttling levers that exist (batch size, sleep, concurrency).
4. Backup and restore reality: what backup exists, when it last succeeded, how long a restore takes at this size — restore time bounds the abort options.
5. The executor's context: who runs this (role, access level), from where (bastion, migration job, console), and what approval the repo's conventions require per destructive step (human-approval-boundary / the approval register where present).
6. Verification access: where counts/checksums can be run without adding dangerous load (replica for reads where lag permits — stated).

Workflow

1. Confirm the prerequisites or stop. Approved plan, safety-review verdict, and a VERIFIED backup (not "backups are enabled" — evidence of a recent successful backup/restore test appropriate to the move's blast radius). Absent any of the three, the runbook is not writable yet; say which is missing.
2. Write the preconditions section. Concrete gates with commands' PURPOSE and expected output: backup evidence check, dry-run on a sampled/staging keyspace with its expected parity result, disk/ headroom checks, replication-lag baseline, and the announced window with its stakeholders. Each gate is pass/fail — an operator can answer "did it pass?" without judgment calls.
3. Design the batching. Batch key and size with the RATIONALE (rows × row-width vs lock/undo budget), throttle (sleep or rate) with the signal that tunes it (replication lag threshold, p99 on the primary), concurrency (usually 1; more only with partition-disjoint proof), pause/resume mechanics (progress marker stored WHERE, resume idempotency — re-running a completed batch must be harmless), and the quiet-window preference stated.
4. Write per-batch and per-stage verification. Per batch: the verification query's intent (count parity for the batch range, checksum over normalized columns) and its EXPECTED output shape. Per stage: the full-keyspace parity gate from the plan, plus a business-level sample (N known entities compared field-by-field). Every verification states what PASS looks like — a query without an expected result is not verification.
5. Define abort criteria and halt states per stage. Numeric triggers (parity mismatch > 0, replication lag > threshold for > duration, error rate on the serving path, batch duration trending 2× baseline) → the abort ACTION (stop the loop, leave dual-writes on, do NOT revert schema) → the named SAFE HALT STATE (system serving from old path, partial new data inert, resumable from marker). Improvised aborts are how partial moves become corruption; the halt state is designed, not discovered.
6. Write rollback per stage, composing rollback-runbook-author conventions (roll-back-vs-fix-forward criteria with a time-box, one observable verification per rollback step): pre-cutover stages roll back by stopping the loop and cleaning inert copies (or leaving them, stated); post-read-switch rollback switches reads back (condition: dual-writes still on); the no-return point — old path decommissioned/contracted — is FLAGGED as requiring explicit human sign-off, with the plan's zero-reader evidence cited.
7. Mark every human gate. Steps that are destructive, irreversible, or production-facing get an APPROVAL marker per the repo's conventions (human-approval-boundary; recorded in the approval register where the repo keeps one). The runbook never instructs an agent to proceed through these autonomously.

Runbook skeleton, batching-rationale worksheet, verification-query patterns, and abort/halt-state examples: references/runbook-skeleton.md.

Output Format

DATA MIGRATION RUNBOOK — <move> (vN, date)
Prerequisites: plan=<ref> safety-review=<ref/verdict> backup=<evidence + restore-time bound>
Preconditions: <numbered pass/fail gates with expected outputs>
Execution:
  Stage <n> — <name>
    Step n.m: <action (purpose-first; exact commands only where stable)>
              VERIFY: <check + EXPECTED output>
              ON FAIL: <abort ref | retry rule>
    Batching: key=<k> size=<n (rationale)> throttle=<signal-tuned> resume=<marker + idempotency>
    [APPROVAL REQUIRED] markers on destructive/irreversible steps
Abort criteria: <numeric triggers → action → SAFE HALT STATE, per stage>
Rollback:      <per stage, rollback-runbook-author conventions; no-return point flagged>
Execution log: <skeleton: timestamps, ranges, verification outputs, deviations>
Not covered:   <explicitly out of scope>
Execution posture: THIS DOCUMENT EXECUTES NOTHING — every step is operator-run;
                   destructive steps require the marked human approval.

Validation Checklist

• All three prerequisites cited with evidence (plan, safety review, verified backup) — none assumed.
• Every step has a verification with an EXPECTED output; no verify-free destructive steps exist.
• Batching states rationale and the tuning signal; resume is idempotent from the stored marker.
• Abort criteria are numeric, per stage, and land in a NAMED safe halt state — never "stop and assess".
• Rollback exists per stage; the no-return point is flagged with its required human sign-off and cited evidence.
• Commands are purpose-first and platform-stable; nothing assumes one product's exact CLI where the store's generic interface differs.
• The execution-log skeleton exists so the run produces evidence, not memories.
• The document claims no execution authority anywhere.

Gotchas

• Batch sizes calibrated at hour zero melt things at hour three: table hotspots, index bloat, and vacuum/GC debt accumulate. The throttle is signal-tuned (lag, p99), not a constant to set and forget.
• Resume-after-crash double-applies the last batch unless batches are idempotent (keyed upsert, range-replace) — the marker says where to resume; idempotency makes resuming SAFE.
• Verification on the replica lies during the move: the thing you are changing is what's replicating. State where each check runs and the lag it must tolerate.
• "Abort" that reverts schema mid-backfill turns a halt into an outage: the safe halt state usually keeps the expanded schema and inert partial data — removal is a separate reviewed step, not a reflex.
• The dry run that proved nothing: a staging dry-run on 1% synthetic data validates syntax, not behavior at volume. State what the dry run actually evidences and what it cannot.
• Progress markers stored in the table being migrated: the move's own writes then race the marker. Store progress OUTSIDE the moving data.
• Cutover on a Friday because the window was free: the runbook states window requirements including the days of hands-on-deck AFTER cutover, not just the hours during.
• A perfect runbook nobody rehearsed: for high-blast-radius moves, the rehearsal requirement (staging execution of THIS runbook) is a precondition gate, mirroring rollback-runbook-author's rehearsal discipline.

Stop Conditions

• No approved plan, no safety-review verdict, or no verified backup → stop; name the missing prerequisite. A runbook that papers over a missing prerequisite launders risk into procedure.
• Asked to EXECUTE the move (run the backfill, "just do the cutover") → refuse execution; this skill authors the document, and every destructive step is operator-run under the marked approvals.
• The plan's parity definition is missing or unverifiable (no way to state what PASS looks like) → stop and send the gap back to schema-evolution-planner; verification cannot be invented at the runbook layer.
• Restore time exceeds the tolerable outage for the worst-case abort (backup exists but is operationally useless at this size) → halt and surface: the move's risk envelope is a human decision that must be made with that fact on the table.
• Asked to fold the runbook's approval gates into "auto-proceed if checks pass" for an agent to run end-to-end → refuse; destructive data-move steps stay human-gated per human-approval-boundary and the repo's authorization conventions.

Supporting Files

• references/runbook-skeleton.md — full runbook skeleton, batching-rationale worksheet, verification-query patterns (counts/checksums/sampled equality), abort-trigger and safe-halt-state examples, execution-log format.
• evals/evals.json — behavior cases including the missing-backup gate and the just-run-it refusal.
• evals/trigger-evals.json — discrimination against schema-evolution-planner, secure-migration-reviewer, rollback-runbook-author, and gated-deployment-prompt-template.