Database changes / SQLite
Migration rehearsal
A legacy asset-tag column was replaced with a normalized, required, unique asset key in a 5,000-row disposable database. Old and new reads agreed during the overlap phase.
14 automated tests passed. The successful run resumed after 750 committed rows using a fresh database connection, completed all 5,000 rows, and retained the ID/description checksum.
Executed cases
| Fixture | Outcome | Final stage | Finding |
|---|---|---|---|
| good | Pass | contracted | 5,000 normalized keys; ID/description checksum preserved |
| duplicate | Rejected as expected | expanded | IntegrityError: UNIQUE constraint failed: assets.asset_key |
| null | Rejected as expected | expanded | MigrationError: Null or empty legacy tag at IDs 2 |
| empty | Rejected as expected | expanded | MigrationError: Null or empty legacy tag at IDs 2 |
Successful run timings
| Recorded stage | Seconds |
|---|---|
| fixture | 0.010633 |
| expand | 0.001784 |
| overlap contracts | 0.008497 |
| resume backfill | 0.015048 |
| backfilled contracts | 0.006898 |
| lock probe | 0.076125 |
| cutover | 0.000575 |
| contract | 0.012104 |
| new contract | 0.002358 |
The intentional interruption's first three batches are included in total run time, not the resumed-backfill stage. Synthetic timings are not a production rollout estimate. The lock probe uses two real SQLite connections and a 75 ms busy timeout; it says nothing about PostgreSQL locking or concurrent indexes.
Checks exercised
- complete rollout preserves data
- contract has actual unique not null constraints
- duplicate constraint failure
- empty source rejected
- failed ddl rolls back
- legacy write during overlap updates new key
- lock timeout and recovery
- null source fails without partial batch
- old app blocks contraction
- old query retired after contract
- repeated backfill is noop
- resume committed batches from new connection
- runner reports invalid fixture and cleans temp
- same named wrong index rejected
Run it
python rehearse.py --rows 5000 --batch-size 250 --interrupt-after 3 --out results/manual python verify.py
Operational verification
The additional process-failure, concurrency, capacity and theoretical deployment checks are recorded in the operations report. The overall verification includes those outcomes.
Scope
This is one executable SQLite recipe with generated ASCII asset tags. It has no dependent tables. The operational supplement executes a controlled writer during backfill and sizes up to 100,000 rows. The old application is retired through explicit fixture metadata. Failed phases roll back transactionally; completed backfill batches remain committed. Removing the original tag is not automatically reversible. The basic run restarts a connection; the operational supplement also kills real processes at transaction boundaries. Neither simulates power failure.
Runtime: Python 3.12.14; SQLite 3.53.1. Generated 2026-10-27T23:43:09.398964+00:00.
Verification JSON · Case CSV · Schema and run details · Test log · Run instructions