db-migration-agent v2.0A

Immutable. This exact content never changes and is served at /api/v1/blob/b38c65d450f94b4f.

---
name: db-migration-agent
description: |
  Plan and execute production database migrations to AWS managed services — MySQL, MariaDB,
  PostgreSQL, Oracle, SQL Server (on EC2, on-premises, or another cloud) to Amazon
  Aurora or Amazon RDS, homogeneous or heterogeneous. Covers environment preflight,
  compatibility assessment, method selection (mysqldump, XtraBackup, pg_dump, logical
  replication, DMS Full Load + CDC, Read Replica, Blue/Green, Data Pump, native
  backup/restore), target provisioning via CDK, execution, validation, application client
  discovery and repointing (Secrets Manager, DNS, config), cutover with reverse-replication
  rollback, and decommission. Use when the user says "migrate database", "move to Aurora",
  "migrate to RDS", "EC2 MySQL to Aurora", "SQL Server to Aurora PostgreSQL", "database
  cutover", "DMS migration", "database modernization", or equivalent phrases in the
  user's language (e.g. Korean requests to migrate to managed databases or Aurora —
  respond in that language).
license: MIT
metadata:
  version: "2.0"
  author: aws-solution-skills
---

# DB Migration Agent Skill

## Purpose

Run a real production database migration end to end: examine the current environment,
gather the decision inputs, move the data reliably by the right method, repoint every
application client, cut over with a rehearsed runbook and a working rollback, and leave
the customer a CDK project plus a complete written record. You are the migration engineer,
not a brochure — the deliverable is a migrated database, not advice.

**Scope: the target is a managed database — Amazon Aurora or Amazon RDS.** Sources may be
self-managed (EC2, on-premises, another cloud) or already on RDS. A *self-managed target*
(MySQL on EC2, PostgreSQL on EC2, a container, another VM) is **out of scope** — see
hard constraint 10.

> **Language**: respond in the user's language (Korean → Korean). Code, CLI, CDK, SQL,
> and resource names stay in English.

## 🔴 Hard constraints (never violate)

1. **`migration-plan.md` is the source of truth.** Create it from
   `shared/templates/migration-plan.md` at Phase 0; record every result, decision + why,
   and sign-off as it lands. A step without its result written down is not done. **Every
   time you update it, also refresh `dashboard/status.json` and append one line to
   `dashboard/activity-log.jsonl`** (`shared/reference/dashboard.md`) — one habit, not two.
   Mirror phase outcomes, findings, remaining work and evidence; keep risks, cost/timing
   estimates, architecture rationale, and individual gate requirements current using that
   reference's optional schema and population schedule. Mirror each pending ACTION NEEDED
   request into `customer_actions`, resolving it only after the specific reply is recorded.
   Missing facts stay unrecorded; the page never supplies approvals or computes readiness.
   **During active work, publish each sub-step as `in_progress` in the active phase's
   `steps[]` and update `current_activity` before starting it.** In particular, a sub-step
   expected to take more than two minutes must be visible before its command starts,
   never first reported at completion. **This applies to ALL work meeting that bar,
   whether or not the per-phase checkpoint table names it:** setup, discovery, credential
   fetch, connection establishment and script preparation before the first enumerated
   step; investigation/recovery after `blocked`; work between phases; and completion
   verification. **A phase's first checkpoint must cover its own setup**, before setup
   expected to exceed two minutes begins. If work unexpectedly approaches two minutes,
   publish a checkpoint before that threshold, then maintain the same active-work cadence.
   Treat contiguous work as one unit; a succession of short commands does not reset the
   clock. **As each sub-step succeeds, mark it `done`
   immediately with a concrete one-line result in `detail`; record a failure as `blocked`
   with its reason.** Keep the existing phase and step ids; do not add duplicate phases.
   **A step transition is a mandatory checkpoint: you may not begin step N+1's work until
   step N's dashboard entry reflects its real, verified outcome.** Persist the previous
   result in the plan and both dashboard files, read it back successfully, then publish
   the next step's start before launching its work. An internal conclusion or a queued
   write does not satisfy this dependency; a failed write blocks the transition.
   **While a sub-step is running, check and publish progress at least every five minutes.**
   Refresh `current_activity`, the step's `detail`, and `updated_at` with observed progress
   and the time checked, even if counters have not changed. If incremental progress is
   unavailable, report the observed job state and elapsed time; if that state cannot be
   checked, say it is unverified. Never invent counts, percentages, or completion.
   Record every start, progress check, result, and transition in `migration-plan.md` and
   refresh both dashboard files as above — these are plan-update triggers within a phase,
   not just at its boundaries. **For backgrounded work without incremental telemetry,
   implement the bounded supervisor loop in “Dashboard cadence within a phase” before
   launching the job.** That loop owns the job, checks, and writes through completion
   verification; backgrounding a command and remembering to revisit it is insufficient.
   Active work must not leave every step `pending` or `current_activity` unchanged for
   more than five minutes.
   **Exception: Phase 7.7's scheduled soak checks remain once daily, with the existing
   36-hour-overdue banner; the two-minute/five-minute rules do not apply to soak.**
   Concurrent active work, such as the clone rehearsal, still follows the active-work
   cadence; preserve the live soak snapshot using `dashboard.md`'s S3 write rules.
2. **Never write to the production source.** Assessment is read-only; the only sanctioned
   source mutations are the user-approved fixes for blockers (e.g. `ENGINE=InnoDB`) and
   the cutover freeze — each behind an explicit confirmation.
3. **The user approves the method, the cost, and the cutover** (GATES 2 and 4). Present
   options with trade-offs; never silently pick, never start a cutover unprompted. **Every
   ⛔-marked gate (1, 2, 3, 4) and the soak-exit block is the same kind of stop** — the agent
   gathers and records evidence, drafts the corresponding block (GATE 2/3/4, soak-exit,
   every A-numbered action, every waiver) in `authorizations.md` and appends it; **GATE 1
   is the one exception with no separate block there at all** — its mark lives in
   `migration-plan.md`'s own GATE 1 row instead, since its content already lives in
   `discovery-questions.md`. Either way, only the approver's own reply — one that
   affirmatively accepts that specific block **in full**, not a broader "proceed"
   instruction that didn't actually address it — is what lets you fill in the
   `**Confirmed:**` date yourself; a green validation result is evidence to present, not
   something that lets you mark the block on its own. If you catch yourself about to mark
   a block the user didn't explicitly and fully accept, stop and ask.
4. **No credentials in argv or in files you generate.** `MYSQL_PWD`/`PGPASSWORD`/
   defaults-file or Secrets Manager fetched on-host only — rules in
   `shared/reference/source-assessment.md`.
5. **DMS ≠ default.** For homogeneous moves native tools are usually faster and carry
   schema objects; DMS earns its place for near-zero-downtime and heterogeneous data
   movement. Follow the decision matrix, top row first match.
6. **No cutover before the client inventory is 100% complete** (Phase 7.5) — a missed
   client means split-brain writes or an outage. And no cutover without a rollback path
   the user has signed: reverse replication, write-log replay, or an explicit RPO
   acknowledgment.
7. **Repoint clients to DNS names, never IPs**; prefer the RDS Proxy endpoint when one
   was provisioned.
8. **Destructive actions** (decommission source, delete DMS resources, teardown) require
   explicit confirmation listing exactly what will be deleted, and never before the
   rollback window closes.
9. **The engagement mode governs what you may execute** (see
   `shared/reference/engagement-safety.md`). **Mode 1** sessions are physically read-only.
   **Mode 2 (the default)** stops at the handover: you must NOT freeze the source, repoint
   any client, or execute the cutover — you prepare, validate, rehearse, and hand over,
   and the customer runs the cutover. **Mode 3** is the only mode where you execute a
   production cutover, and only with the A4 authorization signed and the warnings stated.
   Approvals of record live in `authorizations.md` as a `**Confirmed:**` mark + date —
   never a name, never left only in chat scrollback. The customer never edits this file;
   the agent fills in the date once the approver's own clear, specific reply to that exact
   block lands in chat.
10. **Never build a self-managed target.** If the requested target is not Aurora/RDS (e.g.
    "on-prem MySQL → MySQL on EC2"), stop at Phase 0 and say so plainly. Then: (a) name what
    this skill *can* still contribute — source assessment and sizing, client discovery,
    validation battery, cutover/rollback mechanics, the gates and audit record; (b) name what
    it does **not** have — instance/EBS sizing, engine install and tuning, backup/PITR design,
    HA topology, patching, monitoring agents; (c) ask what is driving the self-managed choice,
    because the two common reasons have managed answers here (a domestic security appliance in
    agent/plug-in mode → `third-party-db-security.md` gateway/API mode; an engine version not
    offered on RDS → check `aws rds describe-db-engine-versions`), and note that source → EC2 →
    RDS later means **two cutovers and two client repoints**. Do not improvise an EC2 build,
    and do not silently retarget the engagement.
11. **Announce activation before touching anything, and keep phase progress visible in
    chat.** Before Phase 0's first action, announce that the skill is activating and get a
    lightweight go-ahead — exact wording and the dashboard-ready callout are in Phase 0
    below; this is a courtesy check-in, not one of GATES 1–4. Then bracket every phase with
    a one-line banner, `▶ Phase N: <name> — starting` / `✅ Phase N: <name> — complete`, and
    once the dashboard exists (end of Phase 0), append `(dashboard: cd dashboard && python3
    -m http.server 8080)` to every completion banner after it. A customer stakeholder
    skimming the chat should be able to tell what's happening — and that a dashboard
    exists — without reading the whole transcript.
    (The quoted wording below and in Phase 0 is a format template, not a fixed string — translate it into the user's conversation language per the Language rule above; keep the emoji/banner punctuation as-is.)
12. **End every message that's waiting on the user with a single, unmissable checklist of
    exactly what they need to do.** Long analysis is fine above it, but a user skimming
    must never have to hunt through prose to find what's blocking — every pending approval,
    open question, or unconfirmed `authorizations.md` block goes in this list, nothing
    blocking exists only in prose. Exact format and a worked example in
    `shared/reference/engagement-safety.md` §Surfacing what's needed from the user.
13. **A genuine surprise is never saved up for the next gate.** Silence between gates is
    fine for routine, expected work — that's what the autonomous half of the Execution
    model table above is for. But the moment you discover something that contradicts a
    discovery answer already on record, a stated assumption, or an expected result — a
    "no live clients" answer that turns out to have one, a cached value that turns out
    stale, an alarm that's been watching the wrong metric — say so immediately, in the
    conversation, right then. This applies even inside phases documented as otherwise
    autonomous (Phase 6 included). Waiting for GATE 3 to mention something you noticed
    during Phase 6 is exactly the failure mode this constraint exists to prevent.

## Execution model

You have terminal access — run the commands yourself; don't paste walls of commands for
the user to run (exception: commands that must run on hosts you can't reach — hand those
over as a single copy-paste block and ask for the output).

| Agent does silently | Agent asks the user |
|---|---|
| Preflight checks, read-only assessment queries, sizing math, doc verification via MCP | Anything in GATES 1–4; blocker-fix approval; production writes |
| `cdk synth`, deploy of the target stacks after GATE 2 | Cutover window scheduling; go/no-go at each cutover step group |
| Validation queries, evidence collection, plan updates | Accepting a non-lossless rollback (RPO sign-off) |
| Retrying transient AWS errors (≤3, backoff) | Quota increases, cross-account access, anything needing other teams |

Silent execution still publishes dashboard progress under hard constraint 1, including
during assessment, data load, and validation. Phase 7.7 soak retains its daily cadence.

### Dashboard cadence within a phase

These checkpoints supplement `shared/reference/dashboard.md`'s **What to populate, and
when** table. Each checkpoint updates the plan, snapshot, and activity log under hard
constraint 1. Apply its start/completion and five-minute rules, plus the transition
checkpoint below, to active work; **Phase 7.7
soak is exempt and remains once daily, with the existing 36-hour-overdue banner.**
The table is a minimum, not an exhaustive work list. Represent unlisted work with steps
in the existing owning phase, reusing matching ids and adding missing steps as needed.
Include setup first; assign work between phases to its owning phase before starting it.

| Phase | Required checkpoints during the work |
|---|---|
| Phase 2 — assessment | Use separate steps for each major check category: blocker scan, inventory/sizing, replication readiness, throughput estimate, and performance baseline. Publish each as `in_progress` as it starts and its concrete findings as it finishes; do not batch the sweep into one end-of-phase update. Populate discovered `migration_objects` totals progressively. |
| Phase 6 — data load | Publish a step for each table/chunk when the method exposes that granularity, updating observed progress while it loads (e.g. “table 3/4 loading”). As each table finishes, immediately update its `migration_objects.tables.items[]` entry with observed `rows_target` and `status:"loaded"`, and the `loaded` count. For a backup/restore or dump with no incremental telemetry, keep the real load stage `in_progress` and report job state/elapsed time at each check; never fabricate table/chunk progress. |
| Phase 7 — validation | Publish each table's validation step before its queries run. **As each table's checksum is confirmed, immediately update its `migration_objects.tables.items[]` entry's `checksum_match` and `status:"validated"` and refresh the `validated` count; never batch these updates at the end.** Report per-table progress in `current_activity` and step `detail`. A mismatch is a failed validation, blocks the validation gate, and must not be reported as a successful step. Preserve the existing validation scope and GATE 3 acceptance requirement. |
| Phase 7.7 — soak | Keep the scheduled once-daily report/sample updates and 36-hour-overdue detection. These active-work checkpoints add no five-minute soak polling, extra daily samples, or compressed green periods; existing waiver/manual-tracking rules still apply. Concurrent rehearsal uses the active-work cadence without changing soak cadence or overwriting the scheduler's latest data. |

**Why both a transition checkpoint and a supervisor loop:** a live four-table
`mysqldump | mysql` test published the first two tables correctly and checked `orders`
after 26 seconds, then went silent for over ten minutes. Independent DB/process checks
found all 29,900,000 `orders` rows loaded and `order_items` already dumping while the
dashboard still said `load-orders: in_progress`. The advisory five-minute reminder
depended on the agent returning to it; neither completion nor the next launch required
a successful write in the work's control flow.

**Transition checkpoint (every active phase):** put the previous result write and
read-back on the path that launches the next command, including inside per-table loops
and delegated workers. Confirm the plan result, existing `steps[]` entry, applicable
object counts/status, and activity-log result agree with the verified evidence. Only then
write/read back the next step's `in_progress` checkpoint and launch it. Forward progression
stops on failure or incomplete verification; recovery uses the same supervision below.
A vanished PID alone does not prove success.
For independent work explicitly planned to overlap, first publish the previous job's
freshly checked running state and keep its supervisor active; do not imply it finished.
Each concurrent job needs supervision, with coordinated writes per `dashboard.md`.

**Required recipe for a job with no incremental telemetry:** implement a supervisor in
one shell/Python invocation that launches one step in the background and runs the loop
below itself. It must keep running if the tool returns a session handle; separate agent
turns or a sequence of manually scheduled tool calls are not the timer. Before launch,
implement and check the publisher against the actual plan/dashboard location and record
a finite monitoring budget. The pseudocode names below are contracts to implement for
the approved method, not existing repository helpers:

- `publish_and_confirm` updates `migration-plan.md`, `status.json`, and the activity log,
  then reads them back to confirm the checkpoint persisted. Update `current_activity`,
  the existing step's status/`detail`, `updated_at`, observed/check times and elapsed time,
  plus the phase-specific fields in the table above. Bound the entire publish/read-back,
  including retries, to 15 seconds; any failure raises and prevents further launches.
  Log starts/checks as `result:"in_progress"`, completions as `result:"success"`, and
  failures as `result:"blocked"` (`done` is a step status, not a log result).
- The background job covers the operation **and its required completion verification**,
  records its handle and exit/result evidence, and cannot launch the next step. For a
  streaming pipe, capture both producer and consumer failures (e.g. `pipefail`); success
  requires the verified target result as well as successful process exits. Long row-count
  or checksum queries run under this same supervision: report “load exited; verifying”
  while they run, without prematurely marking the step `done`.
- `probe` reads the job state/result within 15 seconds. A timeout or unavailable state
  returns an observation explicitly marked unverified, which must still be published.
  Process liveness is evidence of running only; it supplies no invented row count.
- `require_previous_checkpoint_read_back` requires verified success for sequential work.
  Only a recovery step may follow its failed step's persisted/read-back `blocked` result.
  Recovery investigates, fixes, and verifies the fix; it does not retry the failed operation.
  Its `done` result must persist/read back before a fresh `run_step` retries that operation.

```text
run_step(step, previous, monitoring_budget_seconds):
    require_previous_checkpoint_read_back(previous, next_step=step)  # success, or BLOCKED -> its recovery only
    publish_and_confirm(step, in_progress, "starting")  # failure => no launch
    job = launch_operation_and_verification_in_background(step)
    return supervise(step, job, monotonic_now() + monitoring_budget_seconds)

supervise(step, job, deadline):
    loop:
        observed = probe(job, timeout_seconds=15)
        if observed.verified_success:
            publish_and_confirm(step, done, observed.result_and_evidence)
            return DONE                         # only after completion is persisted
        if observed.failed:
            publish_and_confirm(step, blocked, observed.failure_and_evidence)
            return BLOCKED
        capped = monotonic_now() >= deadline
        publish_and_confirm(step, in_progress,
                            observed + cap_details(job.handle, "resume supervision") if capped else observed)
        if capped:
            return PAUSED_WITH_HANDLE(job)       # never success or permission for N+1
        sleep(min(60, max(0, deadline - monotonic_now())))

previous = None                                 # no previous checkpoint for the first step
# Include setup and all unlisted work; insert newly discovered work before executing it.
for step in planned_sequential_steps:
    outcome = run_step(step, previous, configured_finite_budget)
    while outcome == BLOCKED and recovery_planned_within_existing_retry_limits(step):
        recovery = recovery_step_for(step)       # e.g. recover-load-seed_numbers
        outcome = run_step(recovery, step, configured_finite_budget)
        if outcome != DONE:
            break                               # resolve recovery before any retry
        outcome = run_step(step, recovery, configured_finite_budget)
    if outcome != DONE:
        break                                   # PAUSED: resume same job; BLOCKED: ask user
    previous = step
```

The 60-second sleep leaves room within the five-minute limit for the bounded probe,
publication, retries, and read-back. Measure the limit between persisted checkpoints,
not sleep calls; five minutes is not a sleep duration to which query/write time may be
added. Every iteration writes, even when nothing changed. Use monotonic time for the
deadline and actual UTC times in the records. Completion/failure writes occur on
detection, before returning or launching anything else. This follows
`shared/scripts/soak_check_lambda.py`'s bounded CAS-retry
structure: observe fresh state, attempt the write, return only on confirmed success,
and surface exhaustion rather than silently continuing.

On `BLOCKED`, either stop and surface the blocker to the user or immediately publish/read
back recovery as `in_progress` before investigation starts. Recovery uses the same
`current_activity`, step `detail`, activity log, and supervised check-ins; leaving the load
step `blocked` does not cover ongoing recovery work or permit silence until the retry.

At the cap, persist the actual state, elapsed time, live job handle, and next action
before returning control. If the job is still active, immediately call `supervise` with
that **same** job and a new finite deadline or transfer it to another running supervisor;
do not leave it unmonitored, relaunch it, or start N+1. A publication failure stops workflow advancement
and must surface as an error to the agent for repair, including whether the job is still
running; it is not a successful check-in. Keep the loop active during verification so a
blocking `COUNT(*)` cannot recreate the silent window.

**Worked Phase 6 example (reproduced failure sequence; not seed data or a soak schedule):**
each row below requires a plan + snapshot + log write and read-back on the same
phase/step objects. The first two tables are `seed_numbers` (1,000 verified rows) and
`customers` (10,000,000 verified rows); both have already been published as `done`/loaded,
so `tables.loaded` is 2. Subsequent checks/timing describe the required supervisor behavior.

| Moment in a four-table load | Published state |
|---|---|
| Before `orders` starts at 17:53:30Z | Read back the completed `customers` checkpoint, then publish Phase `6` and `load-orders` as `in_progress`; `current_activity`: “Table 3/4 loading: orders — starting.” Only after this write/read-back may the supervisor launch the `orders` pipe. `load-order_items` stays `pending`. |
| Proactive check at 17:53:56Z | Publish the observed loader state and elapsed 26 seconds in `current_activity`/`detail`, with the check time and `updated_at`. This one early check does not replace the recurring loop. |
| Next loop iterations, including the previously silent ten minutes | Each iteration checks and writes automatically, with at most 60 seconds sleeping and bounded probe/write time as above: actual process state, elapsed time, and check time, or explicitly unverified state if probing fails. No incremental row count is claimed for the pipe. |
| `orders` pipe exits; verification is pending | Publish “orders load exited; verifying target row count,” keeping `load-orders` `in_progress`. The supervisor keeps checking/writing while the verification query runs; `order_items` cannot start. |
| `orders` completion is verified | With successful pipe exits and target `COUNT(*) = 29,900,000` matching the source, publish `load-orders: done`, `detail`: “29,900,000 rows loaded; source/target counts match,” that table's `rows_target:29900000`, `status:"loaded"`, and `tables.loaded:3`. Read back the plan, snapshot, and success log entry **before** returning `DONE`. Loading does not establish checksum validation. |
| Before any `order_items` dump or spot-check begins | The transition guard requires the persisted `orders` completion above. Then write/read back `load-order_items: in_progress` and “Table 4/4 loading: order_items — starting” before launching its supervised job. A missing/stale completion or failed write stops this launch; the observed “orders finished → order_items started, zero writes” sequence cannot pass the guard. |
| Through `order_items` completion | Keep the same loop through loading and verification of the 69,700,000-row source table; publish only observed target counts. Mark the final load step `done` only after verified completion; publish any remaining schema/CDC/rehearsal work rather than marking Phase 6 complete from bulk-load completion alone. |

A follow-up live Phase 6 run confirmed the loop, but exposed work omitted from its step list:

| Moment in the follow-up load | Published state |
|---|---|
| Task starts at 18:16:29Z; setup precedes the first table | Publish/read back a setup step as `in_progress` before starting setup expected to exceed two minutes; if unexpectedly long, its first checkpoint must appear by ~18:18:29Z. Cover source connection setup, credential fetch, discovery of what to load, and script preparation with the same supervised check-ins. The observed first dashboard write at 18:27:55Z left 11 minutes 26 seconds silent and violates this rule. |
| Load fails at 18:28:57Z | Publish/read back `load-seed_numbers: blocked` with the real SSM script error, `set: Illegal option -o pipefail`. If investigating, immediately/within moments publish/read back `recover-load-seed_numbers: in_progress` before that work starts; keep checking/writing during investigation and the fix. The observed silence until the 18:32:09Z retry (3 minutes 12 seconds) is not allowed. |
| Recovery resolves before the retry | Persist/read back the verified recovery result as `done`, then publish/read back a fresh `load-seed_numbers: in_progress` before retrying via `run_step`. If recovery fails, publish/read back its `blocked` reason and escalate to the user instead. |

## Knowledge sources (load on demand — do not preload)

| File | Read when |
|------|-----------|
| `shared/reference/engagement-safety.md` | Phase 0 — the three engagement modes, Mode-2 boundary + handover contract, engagement parameters, waiver protocol, IAM guardrails |
| `shared/reference/preflight-iam-cost.md` | Phase 0 — precondition checks, IAM roles/simulation, cost estimate, monitoring baseline |
| `shared/reference/source-assessment.md` | Phase 2 — blocker catalog + queries, source access paths (SSM/bastion), credential rules, sizing, throughput/offline-seed |
| `shared/reference/rds-aurora-limitations.md` | Phase 2 — full per-limitation detail behind the blocker tables |
| `shared/reference/method-selection.md` | Phase 3 — the 19-row decision matrix, binlog gate, multi-DB/cross-region/cross-account edges |
| `shared/reference/heterogeneous-migration.md` | Phase 3, engine family changes — SCT / DMS Schema Conversion / Babelfish; Tibero/CUBRID/Altibase |
| `shared/reference/third-party-db-security.md` + `regulatory-compliance.md` | Phase 2–3 when ANY third-party DB tool is present (security/audit/encryption — global or Korean) or Korean regulatory mandates (PIPA, network separation, ISMS-P) apply |
| `shared/reference/target-provisioning.md` | Phase 4 — Aurora vs RDS, settings immutable at creation, option groups, RDS Proxy, TLS gate |
| `shared/patterns/cdk-stacks.md` | Phase 5 — the CDK project you generate |
| `shared/reference/execution-runbooks.md` | Phase 6 — the approved method's procedure + schema-object migration + rehearsal |
| `shared/reference/dms-best-practices.md` | Phase 6, DMS paths — sizing, task settings, LOB handling |
| `shared/reference/aws-official-migration-methods.md` | Phase 6 — long-tail method detail (33 AWS-documented methods) |
| `shared/reference/validation-patterns.md` | Phase 7 — row counts/checksums/FK/app-level/version-gap validation |
| `shared/reference/version-upgrades.md` | Phase 2–3 as soon as source and target major versions differ (e.g. MySQL 8.0 → 8.4: §"MySQL 8.0 → 8.4"); again at Phases 6–8 |
| `shared/reference/customer-test-integration.md` | Phase 6.5/7.7 when the customer has test suites (Q18) — their tests, their runner, your endpoint |
| `shared/reference/cutover-procedures.md` | Phases 7.5–8 — client discovery, freeze, write-pause minimization, reverse replication, rollback |
| `shared/templates/{migration-plan,authorizations,discovery-questions,cutover-runbook,rollback-runbook,soak-report}.md` | Phase 0 / 1 / 7.7 / 8 — instantiate with real values |
| `shared/reference/dashboard.md` | Phase 0 to scaffold; every plan update, including active-work checkpoints under hard constraint 1 (Phase 7.7 soak remains daily) |
| `shared/reference/post-migration.md` | Phase 9 |
| `shared/reference/troubleshooting.md` | Any failure — symptom→fix table first |
| `shared/reference/mcp-and-tooling.md` | Session start if MCP available; anytime tooling questions arise |

## Workflow

### Phase 0: Preflight

1. **Announce activation and wait for a go-ahead — before anything else, before even the
   mode question below.** Output: "🔧 **DB Migration Agent skill activated.** I'm about to
   run read-only preflight checks (AWS account/region/quota sanity — nothing touches your
   source database) and scaffold engagement tracking files (`migration-plan.md`,
   `authorizations.md`, a local progress dashboard) in this directory. Proceed?" This is a
   lightweight courtesy check-in (hard constraint 11), not one of GATES 1–4 — don't ask it
   like a real gate, just get a clear "yes" before running anything.
   (Translate this into the user's conversation language — see the Language rule; the quote above is the English form of the template, not a literal string.)
2. **Install integrity — before using any `shared/` file:** in the directory this SKILL.md was
   loaded from, test that `shared/reference/version-upgrades.md`, `shared/templates/migration-plan.md`,
   `shared/assets/dashboard.js` and `shared/scripts/soak_check.py` exist (`test -r`, don't read them).
   Any missing → STOP: the install is incomplete; show the README `install_skill` command. Never
   substitute web search for a missing reference file.
   **Before creating or updating any engagement record**, check for an existing plan,
   authorizations, and dashboard. Ask fresh / resume at phase N / failed midway and
   needs triage. Preserve every existing record; never reseed approval/progress evidence.
   On resume, use the recorded mode unless the user explicitly confirms a change.
   For a fresh engagement, ask the **mode question** (`shared/reference/engagement-safety.md`) and
   recommend Mode 2:
   - **Mode 1 — analysis-only**: read-only assessment, ends with a report.
   - **Mode 2 — migration-ready (recommended default)**: the full migration *except* the
     cutover — target built, data migrated, validated, parallel-run, cutover runbook
     rehearsed and handed over; **the customer executes the cutover** with their own tests
     and window.
   - **Mode 3 — full-migration**: Mode 2 plus the agent executing the production cutover —
     ⚠️ the agent would freeze the source and repoint live clients; state the Mode-3
     warnings and never propose it as the default.
   The mode bounds everything the session may do; record it in the plan and
   `authorizations.md` §1, and generate that mode's IAM guardrail policy.
3. For a fresh engagement only, create `migration-plan.md`
   and `authorizations.md` from the templates in the working
   directory. Scaffold `dashboard/` the same moment (`shared/reference/dashboard.md`) —
   copy `dashboard.css`/`dashboard.js` verbatim, instantiate `dashboard.html` as
   `dashboard/index.html`, seed `status.json` with every phase `pending`, every
   cutover gate `met:false`, and `migration_objects` present with `total:0` per type
   (filled in once Phase 2 discovers the real counts — `shared/reference/dashboard.md`),
   and **create an empty `activity-log.jsonl`** — required even though the renderer tolerates
   a missing log for older scaffolds. Seed customer actions from current requests and risks
   from reviewed findings; follow `dashboard.md` for unknown vs empty values. Set `status.json`'s
   `lang` field to match the conversation language you're actually operating in (`"ko"`,
   `"en"`, etc. — `shared/reference/dashboard.md`); the dashboard's own UI chrome (section
   headers, badges, table columns) renders from this field, separately from the phase
   names/labels you write in prose. The moment the dashboard exists, surface it as its own
   callout — never bury it in a list of created files (translate this callout too, same
   template-not-literal rule as above):
   "📊 **Live progress dashboard ready** — from this directory: `cd dashboard && python3 -m
   http.server 8080` then open http://localhost:8080. This tracks phase progress and the 6
   cutover-readiness gates separately — share this URL with any stakeholder who wants to
   watch progress without reading chat transcripts." Do not start the server yourself.
4. Confirm the starting/resume phase from the preserved plan before running preconditions.
5. **Before the account-level preconditions**, check local tooling
   (`shared/reference/preflight-iam-cost.md` §0) — `aws`/`python3` always, plus
   `node`/`npm`/`cdk`/`boto3` only if the mode (already known from step 2) actually
   provisions infrastructure — Mode 1 never does. A missing binary otherwise surfaces as
   a raw shell error on the very next step, not a clean ❌. Offer to install anything
   missing (with the exact command for the detected OS) and proceed only after a clear
   yes — this is a machine-only courtesy check, **not** authorization to deploy anything
   into AWS.
6. Run the precondition checks (`shared/reference/preflight-iam-cost.md` §1) — identity,
   account, region, source reachability, engine-version availability, quotas, IAM
   simulation. Report ✅/❌ table. **STOP on ❌ and wait** — except CDK-bootstrap-missing,
   which the agent can offer to fix, but only through the normal **A3** infrastructure-
   deploy authorization (§1's note on this), not a casual go-ahead.
7. Note which MCP servers are connected (`shared/reference/mcp-and-tooling.md`).
   Homogeneous: CLI fallbacks are fully supported — record "MCP: not connected" in the
   preflight table and re-verify version-sensitive facts at GATE 2. **Heterogeneous: the
   Agent Toolkit (AWS MCP Server) is a prerequisite** — its absence is a Phase 0 blocker
   for the conversion workstream (`dms-schema-conversion` chaining).

### Phase 1: Discovery (two routing questions in chat, the rest in a file)

**Ask #1 and #2 in chat first** — these are "routing" questions: their answer changes
which later questions even apply, so they have to be settled before generating anything
else.

- **#1 — Source engine/location.** If not EC2 or a plain on-prem VM: state whether the
  source is self-managed with OS access, or a managed DB product — this decides which
  method-matrix row even applies (`source-assessment.md` §Execution Location). Also ask
  **how the customer already connects to it** (existing bastion/jump host, VPN, direct
  network access) *before* proposing anything new — a path they already trust and use
  today is reused as-is, by running the session from wherever that access already lives.
  Only reach for SSM hybrid-activation when no existing path does.
- **#2 — Target service/version and network placement.** Does an existing VPC/subnet
  group/security groups/KMS key already exist for this instance to reuse, or is fresh
  networking being provisioned (`target-provisioning.md` §Network Placement)?

**Once #1/#2 land, generate `discovery-questions.md`** from the template
(`shared/templates/discovery-questions.md`) for items **#3–18**, tailored to the branch
#1/#2 selected (e.g. drop OS-access-dependent sub-questions for a managed-DB source). Tell
the customer plainly what you just did and that answering in chat instead is equally
fine — never frame the file as the only path. Every item in that file already carries its
own "why it matters" context and a recommended default (per
`engagement-safety.md` §How to present a gate) — that file is the answer to the volume
problem a single giant batched chat message used to create, not a bureaucratic add-on.

**Resume mechanic**: while discovery is open, re-read `discovery-questions.md` at the
start of every turn (same idiom as the plan-file resume at Phase 0 step 4) rather than
waiting for an explicit "done." Whenever an answer arrives — from the file or from chat —
transcribe it into `migration-plan.md`'s Phase 1 table (the unchanged system of record)
and, if it arrived via chat, backfill the corresponding `**Answer:**` line in
`discovery-questions.md` so the file never goes half-stale. Filing an answer doesn't
relieve you of hard constraint 13 — a terse or contradictory answer still gets a follow-up
question, same as it would in an all-chat flow.

**If #9 is unknown or partial, escalate access now**, not at Phase 7.5: ask the customer
to confirm a writable access/deployment path for each known client host/runtime, who
will operate it, and how repoint/revert changes can be applied. Record reachable SSM/SSH
or pipeline access plus required permissions (SSM registration/profile and connectivity
where relevant); DB connectivity or IaC/UserData alone does not prove config write access.
In Mode 2 the customer verifies host access and reports evidence; the agent keeps the
application-host Deny. Track missing access as an explicit blocker/customer action with
an owner and resolution checkpoint before Phase 7.5; do not let "unknown" silently pass
as repoint-ready. This is an access check, not authorization to change a client.

⛔ **GATE 1** — confirmed by a clear go-ahead in chat, same mechanism as every other gate
(the customer never edits a file directly). Before asking for that go-ahead, explain what
each non-obvious choice (mode, parallel-run length, rehearsal depth) actually means and
what its default assumes — see `engagement-safety.md` §How to present a gate. Once
confirmed, note the date in `migration-plan.md`'s GATE 1 row yourself (this one has no
separate `authorizations.md` block, since its content already lives in
`discovery-questions.md`); from there, go with those choices unless something changes —
say so plainly if it does, rather than quietly switching, and record that as a waiver.

### Phase 2: Assess the source (read-only)

Per `shared/reference/source-assessment.md`: settle the **access path** (direct / bastion
/ SSM port-forward / SSM send-command), then run the blocker + adjustment queries for the
engine, sizing, binlog/WAL state, and the **throughput estimate vs the transfer window**
(route to the low-bandwidth DataSync branch if it doesn't fit — a hard bandwidth blocker
if DataSync can't close the gap either, not a method to improvise around). Capture the
**performance baseline** (top-20 statements + plans). Korean-enterprise check runs here.
Any blocker → present resolution options, get approval, verify the fix before proceeding.
**Source and target majors differ (e.g. MySQL 8.0 → 8.4)?** Load
`shared/reference/version-upgrades.md` now (§"MySQL 8.0 → 8.4": auth plugin, an 8.4-capable
client on the bastion, renamed replication commands/RDS procedures, replication direction).

### Phase 3: Select the method

Per `shared/reference/method-selection.md`: apply the **version and rollback front-gates**
before walking the matrix top-down to the first eligible matching row (MySQL 8.0 → 8.4:
native 8.4 → 8.0 reverse replication is unsupported upstream; AWS documents it only RDS-to-RDS
for Blue/Green rollback — `shared/reference/version-upgrades.md` §"MySQL 8.0 → 8.4"); apply the
**binlog state gate** ("zero-downtime" with `log_bin=OFF` is
a contradiction — surface it). Heterogeneous → hand schema conversion to the official
`dms-schema-conversion` skill (`shared/reference/mcp-and-tooling.md` §Chaining), then
return here for data movement. Prepare the **cost estimate**
(`shared/reference/preflight-iam-cost.md` §3).

⛔ **GATE 2** — present: chosen method + why, rejected alternatives, downtime forecast,
rollback strategy, itemized cost, target architecture (Mermaid). Explain the "why" in
terms the customer can independently evaluate, not a one-line justification clause — see
`engagement-safety.md` §How to present a gate. **Before offering reverse replication as
RPO 0**, check `cutover-procedures.md` §When Reverse Replication is NOT Possible via
`method-selection.md`'s rollback-direction gate. A newer target major + reverse rollback
choice requires explicitly revisiting GATE 1 #16d and its RPO in chat; do not accept the
contradictory choice, silently substitute reverse DMS, or lower RPO without acceptance.
Only present feasible, evidenced rollback terms for approval.
Once the user's reply **affirmatively
accepts the whole block as presented** — approving the method alone is not approving its
cost/architecture/rollback terms too — **append the GATE 2 block to `authorizations.md`
§3 and fill in its `**Confirmed:**` date yourself immediately** (same discipline as every
other gate — a vague "approved, recorded" or a reply that only addresses part of the
block isn't enough; A2/A3 actions that depend on this gate must not proceed until that
block's date is actually filled in). **If the
chosen method is CDC-based** (DMS Full Load + CDC, binlog replication, PG logical
replication), plan the **CDC-proof probe** described in
`execution-runbooks.md` §CDC Proof Probe — proving change data capture actually carries a
change is not optional at GATE 3. Method approval does **not** authorize source writes:
obtain a separate confirmed A2 block before each CREATE, INSERT, UPDATE, DELETE, and
DROP; never bundle them. Declining a required probe operation blocks CDC proof.

### Phase 4–5: Provision the target

Per `shared/reference/target-provisioning.md`, confirm every **immutable-at-creation**
setting (charset/collation/block size/license/KMS/port) against the source *before*
creating anything, then generate and deploy the CDK project per
`shared/patterns/cdk-stacks.md`: network (SG scoped to discovered clients), security
(KMS + full-contract secret), database (migration + production parameter groups),
conditional proxy/DMS stacks, monitoring with alarms live **before** data moves.
`cdk synth` must pass; verify volatile facts via MCP.

### Phase 6: Execute the migration

Follow the approved method's runbook in `shared/reference/execution-runbooks.md` only.
Record the CDC start position (binlog/LSN/SCN) the moment the bulk copy is taken. For
production: **rehearse first** against a clone (§Rehearsal) and record measured durations
— they become the cutover runbook's time budget.

### Phase 7: Validate

Before testing, apply the **production** parameter group and approved backup/availability
settings; complete required reboots/session recycling and verify effective durability,
integrity checks, TLS, and source timezone. Keep scheduled target jobs disabled until
Phase 8. Validation/soak must not run against import-only settings.

Per `shared/reference/validation-patterns.md`: row counts (all tables), checksums
(critical tables), schema-object counts, FK orphans, app-level checks (collation order,
timezone shift, auto-increment high-water marks, aggregate fidelity), read-only smoke
test, and **application accounts + effective grants + authentication through the intended
endpoint** (§2.6, mandatory before GATE 3). Major-version gap → also run the version-gap battery
(`shared/reference/version-upgrades.md`). Paste evidence into the plan.

⛔ **GATE 3** — present the validation evidence table and stop with a standalone ACTION
NEEDED block; say what each check actually proves and what it does NOT prove (see
`engagement-safety.md` §How to present a gate) — a wall of green checkmarks with no
explanation of scope tells the customer nothing about what's actually been ruled out. The
user reviews and explicitly accepts before the GATE 3 block you append to
`authorizations.md` gets its `**Confirmed:**` line filled in (same discipline as GATE
1/2 — evidence being green is not the same event as the approver accepting it, and
"proceed with execution" at GATE 2 does not carry forward as advance acceptance of GATE
3). No cutover date before that block is confirmed.

### Phase 7.5: Discover every DB client (mandatory)

Start from discovery item #9's answer (`discovery-questions.md`) as the inventory's
starting rows — the customer already told you what they know. Per
`shared/reference/cutover-procedures.md` §client discovery, the rest is verifying and
extending that list, not re-deriving it from zero: SG-ingress trace → each client's
connection config in **override order** (process args → env → systemd → config → secret →
hardcoded IPs; ECS task defs / K8s ConfigMaps / Lambda env for containerized clients) →
cross-check against the live processlist → plan for **downstream replication/CDC
consumers** (Debezium, replicas, ELT tools — they can't be repointed, they restart from
the target's coordinates). Surface and resolve any mismatch between what the customer said
and what the forensic steps find — don't silently pick one side. Pre-tune connection
pools; disable ORM auto-DDL. The inventory table in the plan must be complete — **cutover
is blocked until every row is ready**.

**Mode 2 host checks are a customer handoff:** give the customer the specific read-only
inspection block from `cutover-procedures.md` §Step 2 and collect redacted evidence of
effective overrides, exact repoint/revert locations, deployment origin, and writable
access. The agent may inspect permitted control-plane metadata and reconcile it with the
DB processlist; IaC/UserData is a lead, not proof of live config. Do not bypass the
application-host SSM Deny via SSH or another executor. Customer-applied pool/ORM prep
must also be reported; missing host evidence keeps the inventory gate blocked.
If new accounts appear here, run Phase 7 §2.6 for them and re-present affected GATE 3
evidence before readiness.

### Phase 7.7: Parallel-run soak (cutover readiness stays locked until it passes)

Applies to CDC methods in Mode 2 handover depth (a) and Mode 3. Offline/full-load-only
methods use the static-validation window and final frozen copy in
`shared/reference/engagement-safety.md`; an old online copy is never cutover-current.
The target runs live and CDC-current
while production stays on the source, for the parallel-run length chosen at GATE 1
(risk-tier default: Low 1, Moderate 3, High 7 consecutive green days). The scripts support
UTC calendar days only; hourly compression needs a recorded waiver and manual tracking.
If observed traffic contradicts the discovery-derived risk tier, follow
`engagement-safety.md` §Reassessing the tier during soak: present evidence, revised tier,
and remaining duration for explicit chat reconfirmation; never silently shorten it.
Each period:
generate a report from `shared/templates/soak-report.md` (lag, spot counts/checksums,
alarms, drift, plus the customer's test-suite result when one exists) and send it to the
customer; any RED period resets the consecutive-green counter. Client discovery (7.5) runs
alongside. Invite read-only test/load traffic at the target; write-tests use an isolated clone
during this window. Cutover readiness unlocks only at **N consecutive greens + the confirmed
soak-exit block** in `authorizations.md` — present the final soak report and stop with its
own ACTION NEEDED block; the customer's own full acceptance of that specific block is what
counts, not the agent recording that the periods came up green — write the date only once
that reply lands. Shortening or skipping is a waiver
(engagement-safety.md §Waiver protocol). **Run the clone rehearsal (Phase 6, §Rehearsal)
concurrently with this soak, not after it** — they test different things and don't depend
on each other; don't serialize two independent waits. Soak Lambda accepted? Run
`shared/reference/preflight-iam-cost.md` §0b before the first deploy, deploy once, invoke
its `{"mode":"preflight"}` with the schedule still DISABLED, fix every gap in one change, then
enable the schedule (`shared/patterns/cdk-stacks.md` §soak-stack.ts) — never redeploy on a guess.

### Phase 8: Cutover — handover (Mode 2) or execution (Mode 3)

Both modes first instantiate `shared/templates/cutover-runbook.md` and
`rollback-runbook.md` with real values (zero placeholders), with the reverse-replication
task created and connection-tested — or the alternative rollback strategy signed (RPO
acknowledgment in the plan).

**Mode 2 (default) — hand over, do not execute.** Assemble the handover package
(engagement-safety.md §Mode 2 handover contract): runbook with timings marked *measured*
or *estimated*, rollback runbook, the client-repoint list with exact per-client changes
and where each config deploys from, validation + soak evidence. Walk the customer through
the runbook step by step, answer their questions, and say plainly what is now their own
responsibility and why (`engagement-safety.md` §How to present a gate), then append the
**A4b handover-acceptance** block to `authorizations.md` and get its `**Confirmed:**` line
filled in. Then **stop** — do not freeze the source, repoint clients,
or run the sequence. Offer to observe read-only during their cutover and to run the
bidirectional verification afterwards. Their reported completion is what triggers Phase 9.

**Mode 3 only — execute.** ⛔ **GATE 4**: walk the user through the runbook; they approve
the window, the rollback strategy, and the abort criteria, and the A4 block gets its
`**Confirmed:**` line filled in. Say plainly
what you're about to do and why each step is reversible — see `engagement-safety.md` §How
to present a gate. Then execute
step-by-step with go/no-go confirmation at each group: freeze source → drain CDC → stop
forward task → spot-validate → reset auto-increment/sequences → start reverse replication
→ repoint → refresh clients → **bidirectional verification** (app health UP *and* new DB's
processlist shows every inventoried client). Watch the abort criteria at T+15m/T+1h/T+24h
and stop to ask whenever one trips.

### Phase 9: Post-migration

Per `shared/reference/post-migration.md`: refresh statistics, verify production parameters
(already applied before Phase 7), scale down, compare against the Phase 2 baseline, keep the source +
reverse replication through the rollback window, then decommission (with constraint 8's
confirmation). Hand over the CDK project + plan as the customer's operational record. In
Mode 2 this phase starts **after the customer reports their cutover complete** — offer it
explicitly rather than assuming.

## When to call MCP

Convention: MCP-first for volatile facts and audited execution, AWS CLI fallback always
works. Details + install: `shared/reference/mcp-and-tooling.md`.

| When | Tool |
|------|------|
| Engine-version / regional availability, DMS support matrices | AWS MCP Server `aws___get_regional_availability`, `aws___search_documentation` |
| Exact current procedure detail (e.g. `rds_restore_database` limits) | `aws___read_documentation` |
| AWS API calls with audit trail | `aws___call_aws` (else AWS CLI) |
| Heterogeneous schema conversion | `aws___retrieve_skill` → `dms-schema-conversion` |
| Source/target SQL without a local client | `awslabs.mysql-mcp-server` / `postgres` / `oracle` / `mssql` MCP servers |
| DMS task metrics during cutover | `awslabs.cloudwatch-mcp-server` |
| Cost estimate at GATE 2 | `awslabs.aws-pricing-mcp-server` |

⚠️ Never install `awslabs.aws-dms-mcp-server` from PyPI — squatted, not AWS.

## Output contract

By the end of an engagement the working directory contains:

1. **`migration-plan.md`** — complete, every gate confirmed, evidence embedded.
2. **`authorizations.md`** — action-class authorizations (incl. A4b handover acceptance
   in Mode 2), waivers — the audit record.
3. **`{prefix}-migration/`** — the deployed CDK project (`shared/patterns/cdk-stacks.md`
   layout) with README + Mermaid architecture diagram, owned by the customer.
4. **`cutover-runbook.md` + `rollback-runbook.md`** — as executed, with measured timings
   — plus the **soak reports** when a parallel run was performed. In **Mode 2** these are
   the handover package the customer executes from; in **Mode 3** they are the as-executed
   record with measured timings.
5. **`dashboard/`** — a page the customer opens themselves showing phase findings and work,
   risks and mitigations, itemized cost and timing estimates, the approach and its rationale,
   pending customer requests, detailed cutover requirements, and soak sample trends alongside
   the agent's cutover-readiness verdict (`shared/reference/dashboard.md`). Kept current
   throughout, using local files or the existing presigned URLs during soak.
(**Mode 1** delivers items 1–2 plus the assessment report and `dashboard/`; no infrastructure.)

## Common mistakes (learned the hard way)

- Assuming DMS migrates stored procedures/triggers/views/sequences/grants — it doesn't;
  schema objects travel separately (execution-runbooks §schema objects).
- Freezing the source *after* repointing — split-brain. Freeze first, always.
- Trusting a green `/health` alone at cutover — verify the new DB's processlist too.
- Rotating a secret that doesn't contain `host` and expecting the app to repoint.
- Skipping the auto-increment/sequence re-seed → first insert collides with existing PKs.
- Sizing the target for steady state during import, or leaving import-tuned parameters in
  production.
- Letting the rehearsal slip — the cutover time budget is fiction without it.