# Migration rehearsal

A runnable SQLite rehearsal for replacing a legacy asset tag with a normalized, unique asset key. It builds a populated disposable database, expands the schema, backfills in committed batches, resumes after an injected interruption, checks old and new reads, cuts over, and removes the legacy column.

This package implements one specific migration recipe. It is not a general migration framework or a PostgreSQL locking test.

## Run

Python 3.10 or newer with SQLite enabled; no third-party packages. Verified on Python 3.12 and SQLite 3.53.1. Run from this directory:

```sh
python rehearse.py --rows 5000 --batch-size 250 --interrupt-after 3 --out results/manual
python -m unittest -v
python verify.py
```

`verify.py` runs the test suite plus a 5,000-row successful rehearsal and duplicate, null, and empty-key cases. It writes `verification.json`, per-case reports, `results/summary.csv`, and an offline `report.html`. Expected invalid fixtures count as successful detection in the verification summary; their individual `run.json` files correctly contain `passed: false`.

For a rejected input example:

```sh
python rehearse.py --invalid duplicate --out results/manual-duplicate
```

That command exits 1. Use `--interrupt-after 0` to skip interruption. The CLI accepts 2 to 1,000,000 synthetic rows and a positive batch size. It always creates its own temporary database; it does not accept a production database path.

## Recipe

1. **Legacy:** `assets(id, tag, description)` contains generated AHU tags, including mixed case and surrounding spaces.
2. **Expand:** add nullable `asset_key`; insert and tag-update triggers keep old writes compatible. New reads fall back to `lower(trim(tag))` until backfill completes.
3. **Backfill:** select rows whose key is missing, normalize tags, and commit the batch and checkpoint together. Resume uses actual missing rows, not just the highest recorded ID. Null or empty tags reject the batch. A unique index rejects normalized duplicates; the runner also checks the actual index definition.
4. **Cutover:** require a completed backfill and explicitly retire the old application's contract in rollout metadata.
5. **Contract:** rebuild the table with a required, unique, nonempty key and no `tag` column. Compare ID/description checksums across the change. The new query contract must still return every row.

Each phase uses an explicit transaction. Failed phase changes roll back; earlier committed backfill batches remain available for diagnosis and restart. The operational suite executes a forward recovery of original tag formatting from a retained pre-contract archive, preserving a newer description change. This restores source data; it does not automatically redeploy the old application.

## Interruption and locks

The default run interrupts after three committed 250-row batches, closes the connection, opens a new connection to the on-disk fixture, and fills the remaining 4,250 rows. This checks restart from committed state. The separate operational suite also tests real process kills before and after commit; neither test claims power-loss durability.

A second SQLite connection attempts a write while the first holds a write transaction. The report records the real busy timeout and observed lock error. SQLite has different locking, DDL, and index behavior from PostgreSQL. The results do not establish PostgreSQL lock duration, concurrent-index safety, or statement-timeout behavior.

## Scope and checks

The recipe is for the supplied ASCII asset-tag schema. It has no child tables. The operational suite interleaves 100 updates and 100 inserts with backfill batches and measures five size/batch combinations through 100,000 rows. Old application insert/update behavior is exercised in tests during the overlap phase. Contract retirement is an explicit fixture flag, not automatic service discovery. The new schema's required/unique/nonempty constraints are checked after contraction.

The suite also checks resumed and repeated backfills, rollback of a failed DDL phase, invalid input batches, duplicate keys, a same-named index with the wrong definition, contract ordering, retained row content, and cleanup after failure. Synthetic fixture timing is not an estimate for a production table.

## Files

- `rehearse.py`: fixture, recipe functions, contract checks, lock probe, CLI.
- `test_rehearsal.py`: phase and failure-path tests.
- `fixture-spec.json`: default input recipe and expected transformations.
- `verify.py`: reproducible verification and report generation.
- `results/`: successful and rejected runs, schemas, profiles, timings, CSV.
- `report.html`: offline run report.

## Operational and theoretical completion

`python verify.py` also runs `operations.py` and requires all 10 additional expected outcomes to match. On Linux, it executes real process-kill and recovery cases against disposable resources. The readable supplement is [operations-report.html](operations-report.html); [completion.json](completion.json) includes source hashes, expected outcomes, observed results and explicit evidence types. See [OPERATIONS.md](OPERATIONS.md) for the runbook and the completed theoretical PostgreSQL review.

For CI, run `python verify.py` from this package directory on a Linux worker, fail the job on its nonzero exit, and retain `verification.json`, `completion.json` and `results/`. The command needs only the Python standard library and SQLite. It accepts no production target or secret.