# Migration operations

Run all regression, fixture and operational checks on Linux with Python 3.10 or newer:

```sh
python verify.py
```

Run only the additional operations and theoretical models:

```sh
python operations.py
```

Every database is generated inside a new temporary directory. The process-kill cases stop only child processes created by this command. There is no option to point the rehearsal at a production database. The output is `completion.json`, `results/operations/acceptance.csv`, `results/operations/results.json` and `operations-report.html`.

## Executed rollout evidence

| Drill | Observed behavior |
| --- | --- |
| Kill after third backfill commit | 750 rows remain committed; a new process fills the other 4,250 |
| Kill inside the fourth transaction | That transaction exposes a temporary checkpoint of 1,000 inside the child; reopening observes 750, so both uncommitted rows and checkpoint roll back together |
| Kill contraction before commit | The previous table, legacy tag column and compatibility triggers return; retry finishes contraction |
| Old-application traffic | 100 tag changes and 100 inserts commit between backfill batches; all 20,100 rows agree under both read contracts |
| Held write lock | A real contender reaches the 75 ms busy timeout without changing the schema; retry succeeds after the holder releases |
| Forward recovery | Original tags return from an archived pre-contract mapping while a post-contract description correction remains intact |
| Size and batch sweep | Five actual rollouts cover 5,000, 50,000 and 100,000 rows with 100- or 1,000-row batches; every content digest and integrity check matches |

The traffic case interleaves a real writer connection with committed backfill batches. It is a controlled workload, not a claim about arbitrary application concurrency. SIGKILL verifies process-crash recovery through the filesystem used by this run. It does not test storage hardware or power failure.

## Cutover and recovery procedure

1. Record schema, row count, invalid normalized keys and content digest. Do not expand a database with unknown dependent tables using this one-table recipe.
2. Expand, then require both old and new query contracts. Keep the compatibility triggers until the old application has been retired.
3. Backfill bounded batches. Data changes and progress commit together. Resume from missing keys, not a guessed highest ID.
4. On a lock timeout, leave the database in its previous stage. Release the conflicting fixture transaction and retry; the executed run verifies this sequence.
5. Before contraction, archive the exact `id` and original `tag` pairs. Retain their digest with the rollout record.
6. Retire the old contract and contract in one transaction. If the process dies before commit, reopen and inspect the actual stage before deciding whether to retry.
7. If source tag formatting is needed after contraction, run a forward schema change that adds a tag column and restores the archived pairs. Refuse a mapping that omits a current row. Preserve newer non-tag changes; replacing the whole database from an earlier snapshot would discard them.

The forward recovery restores source data and read access. It does not silently restart the old application or undo subsequent business writes. A new row absent from the tag archive requires an explicit mapping before that recovery can commit.

## PostgreSQL: completed theoretical run

No PostgreSQL server or container runtime was available. The package therefore executes a documented lock/timeline model and a concurrent-index recovery state model for the original PostgreSQL design. The engine behavior below is modeled; the real database runs above use SQLite.

The lock model includes the table-lock modes needed for this recipe. It is deliberately smaller than PostgreSQL's full lock manager and omits queue fairness, deadlock detection, row locks, snapshots and catalog locks.

| Modeled case | Inputs | Computed outcome |
| --- | --- | --- |
| DDL behind a reader | AccessShare holder lasts 2 seconds; AccessExclusive request times out after 0.25 seconds | Timeout at 0.25 seconds; no DDL applied |
| Regular index behind a writer | RowExclusive holder lasts 0.8 seconds; Share request allows 2 seconds | Granted after 0.8 seconds |
| Concurrent index with a writer | RowExclusive and ShareUpdateExclusive requests | Compatible table-lock modes; no wait in this simplified case |
| Two concurrent index builders | ShareUpdateExclusive holder lasts 2 seconds; same-mode request allows 0.25 seconds | Timeout at 0.25 seconds |

These compatibility rules come from PostgreSQL's [table-lock documentation](https://www.postgresql.org/docs/18/explicit-locking.html). The model computes `wait = min(holder release time, lock timeout)` when the requested and held modes conflict. Real concurrent index builds still wait at additional snapshot and transaction boundaries; zero table-lock wait is not a zero-duration index build.

A failed concurrent unique-index build can leave an invalid index. The modeled run creates it, injects a duplicate failure, rejects cutover, inspects validity, drops the invalid index, repairs the fixture, rebuilds, and allows cutover only after validity is checked. A separate schedule keeps concurrent index creation outside a transaction block. PostgreSQL documents both restrictions in [CREATE INDEX](https://www.postgresql.org/docs/18/sql-createindex.html).

## Deployment interpretation

The SQLite program is runnable as shipped. The PostgreSQL models cover the original brief's lock-timeout, nontransactional-index and invalid-index decisions; they do not execute PostgreSQL SQL. Their output can justify a rollout design review, but cannot establish a production lock-duration guarantee.

A production rollout must bind these decisions to its actual schema, application versions, workload and database version. This package keeps the measured SQLite sizes and times separate from the theoretical PostgreSQL timeline. No synthetic timing is labeled as customer traffic or production downtime.

## References

- [SQLite WAL](https://www.sqlite.org/wal.html): commit records, connection concurrency and filesystem scope.
- [PostgreSQL 18 explicit locking](https://www.postgresql.org/docs/18/explicit-locking.html): compatibility of the table-lock modes used by the model.
- [PostgreSQL 18 CREATE INDEX](https://www.postgresql.org/docs/18/sql-createindex.html): concurrent-build failure handling and transaction boundaries.
- [PostgreSQL 18 ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html): operation-specific locking and constraints.

References were checked on 2026-10-27. All model assumptions and outputs are embedded in the source and JSON evidence.