| name | db-migration-validation |
| description | Validation & Testing phase of an AI-DLC database migration. Use after Construction is approved, or when the user asks to validate, test, reconcile, or verify a database migration — to prove the converted target behaves the same as the source. Loads test data (one-time load via dbmig for dev/test, or consumes data already loaded via AWS DMS), reconciles row counts, and runs LLM-generated equivalence tests for functions/procedures/packages — same input → same return value (functions) and same net effect (procedures) — executed inside a rolled-back transaction. You (Kiro) generate the test specs from sampled real data. Failures are logged for follow-up (silent mode) or prompted (interactive mode). Writes artifacts to migrations/<project>/03-validation/. |
Validation & Testing — Equivalence
This is the Validation phase. Prove that same input → same value (functions/queries)
and same input → same net effect (procedures/DML) between source and target. The
methodology is in engines/<pair>/checks/equivalence-spec.md (substitute the active
engine pair, e.g. oracle-to-postgresql or sqlserver-to-mysql, derived from the
connection engines).
Conversion-style hand-off: dbmig does the deterministic work (sample data, run tests,
compare, report); you (Kiro) generate the test cases from real data.
Run mode & follow-up (applies to this whole phase)
Per migration-config.yaml run.mode (or --mode):
- silent (default): any test/reconcile failure is recorded to
migrations/<project>/follow-up.yaml (+ follow-up.md) and the run continues — never
blocks. Summarize open follow-up items at the end for later human resolution.
- interactive: also prompt for input/correction; failures surface via a non-zero exit.
Step 1 — Get data into the target
Per migration-config.yaml testing.data_load:
framework (dev/test): python -m dbmig migrate-data --schema <S> --workers 8 --project <P>dms (prod/large): the user runs AWS DMS externally; confirm the target is populated.
Step 2 — Apply deferred foreign keys + triggers
python -m dbmig apply-schema --schema <S> --project <P> --post-data
Construction deferred FOREIGN KEY constraints and triggers; apply them now that the data
is loaded (enforced FKs would otherwise dictate load order, and row triggers would rewrite
loaded data). Writes 02-construction/apply_report_postdata.yaml; the same automated
error-retry loop applies. This is a gated target write — confirm before running.
Step 3 — Reconcile data (row counts)
python -m dbmig compare --schema <S> --project <P>
Writes 03-validation/reconcile_report.yaml. Mismatches are logged to the follow-up file
(silent) or surfaced (interactive).
Step 4 — Generate equivalence tests from REAL data (you do this)
python -m dbmig gen-tests --schema <S> --project <P>
gen-tests extracts callables (functions, procedures, packages), samples real rows from
the source, and writes a prompt bundle per object to 03-validation/test_prompts/ plus
test-manifest.yaml. Then, for each pending unit, you generate a test spec:
- Read the prompt bundle (it includes the source code + the sampled real data + the required
spec format).
- Write a
.test.yaml to the unit's spec_file (03-validation/tests/<S>/<obj>.test.yaml)
using only real values from the sample so tests hit rows that exist.
- For functions: cases with
source_sql/target_sql whose return value is compared.
- For procedures: choose probe queries that capture the procedure's effect; the runner
snapshots each probe before and after the call on both engines and compares the
delta (net effect). This is where you decide how to verify the net result.
- Set the unit's
status to generated in test-manifest.yaml.
Step 5 — Run the tests (txn + rollback)
python -m dbmig run-tests --schema <S> --project <P>
Each case runs on both engines inside a transaction that is rolled back afterward — real
data, real writes, but non-destructive. Functions compare the return value; procedures
compare before/after deltas per probe (tolerance/normalization from
testing.equivalence). Results go to 03-validation/equivalence-report.yaml (+ .md).
Failures are logged to the follow-up file (silent) or prompted (interactive).
Caveat: if a procedure issues its own COMMIT, rollback cannot undo it — flag such
procedures and test them only against a disposable target.
Step 6 — Triage
- silent: present the open follow-up items (conversion failures from Construction +
test/reconcile failures here) as the to-do list; the run did not block.
- For each failure, link to the converted object and the relevant playbook reference; the
fix loops back to
db-migration-construction (re-convert), then re-run the affected tests.
Gate
Summarize pass/fail counts and open follow-up items. Stop at the gate: "Approve the
equivalence test results?" Proceeding with open follow-up items is allowed (silent mode)
but must be explicitly acknowledged. On approval, hand off to db-migration-operations.
Safety
- Test data loads and procedure tests write to the target — gated actions. Prefer a
dedicated test schema/database; never run net-effect procedure tests against a populated
production target.
