Executed rollout and theoretical database review

Migration operational rehearsal

10/10 expected outcomes matched. This record separates real SQLite behavior from the modeled PostgreSQL rollout.

Acceptance record

Case	Requirement	Method	Expected	Observed	Check
backfill-after-commit	Resume actual recipe after process death	real SIGKILL at transaction boundary	750 committed rows retained; uncommitted batch absent; 4250 rows resume; integrity ok	{'persisted_rows': 750, 'persisted_checkpoint': 750, 'resume_rows': 4250, 'integrity': 'ok', 'kill_boundary': {'mode': 'backfill-after-commit', 'boundary': 'after-third-commit', 'checkpoint': 750}}	Matched
backfill-before-commit	Resume actual recipe after process death	real SIGKILL at transaction boundary	750 committed rows retained; uncommitted batch absent; 4250 rows resume; integrity ok	{'persisted_rows': 750, 'persisted_checkpoint': 750, 'resume_rows': 4250, 'integrity': 'ok', 'kill_boundary': {'mode': 'backfill-before-commit', 'boundary': 'transaction-written-before-commit', 'checkpoint': 1000}}	Matched
contract-before-commit	Resume actual recipe after process death	real SIGKILL at transaction boundary	pre-contract schema and triggers restored; clean retry contracts	{'recovered_stage': 'cutover', 'legacy_tag_present': True, 'integrity': 'ok', 'kill_boundary': {'mode': 'contract-before-commit', 'boundary': 'transaction-written-before-commit', 'checkpoint': 5000}}	Matched
overlap-traffic	Preserve old-application writes during migration	two SQLite connections with a writer thread	100 updates and 100 inserts retained; both contracts agree	{'writer_commits': 100, 'writer_errors': [], 'rows': 20100, 'normalized_updates': 100, 'missing_keys': 0, 'contracts_agree': True}	Matched
forward-recovery	Recover removed source tags without losing newer content	executed forward schema change using pre-contract archive	exact original tags and post-contract description retained	{'archived_rows': 20100, 'archive_sha256': 'e3cffd9a88fd5d81f2701d07b5a9c99aa7886f9d04c6019736a8f57cb8869e9c', 'restored_sha256': 'e3cffd9a88fd5d81f2701d07b5a9c99aa7886f9d04c6019736a8f57cb8869e9c', 'newer_content_preserved': True}	Matched
lock-abort-retry	Timeout leaves the schema unchanged and a later attempt succeeds	real SQLite held write transaction	locked error; legacy schema retained; retry expands	{'error': 'database is locked', 'observed_wait_seconds': 0.075993, 'timeout_seconds': 0.075, 'unchanged_on_timeout': True, 'retry_succeeded': True}	Matched
size-and-batch-sweep	Preserve contents across fixture size and batch sizes	five executed SQLite rollouts	all row counts, content digests and integrity checks match	{'cases': 5, 'largest_rows': 100000, 'all_preserved': True}	Matched
postgres-lock-model	Evaluate DDL/index contention under declared PostgreSQL lock modes	theoretical lock compatibility and timeline model	DDL times out; regular index waits; concurrent index admits writer; concurrent builders conflict	{'modeled_cases': 4, 'matched': 4}	Matched
postgres-invalid-index-model	Block cutover until a repaired concurrent index is valid	theoretical failed-build/catalog/retry state model	invalid named index blocks cutover; rebuild becomes valid	{'cutover_allowed_after_failure': False, 'cutover_allowed_after_validated_retry': True}	Matched
postgres-stage-boundary	Keep concurrent index creation outside a transaction block	theoretical executor schedule	concurrent index stage is standalone	[{'stage': 'expand', 'transaction': True}, {'stage': 'backfill_batch', 'transaction': True}, {'stage': 'create_index_concurrently', 'transaction': False}, {'stage': 'validate_index', 'transaction': True}, {'stage': 'contract', 'transaction': True}]	Matched

Recovery behavior

Backfill survives a process kill both between committed batches and inside an uncommitted batch. Killing contraction after its table rebuild but before commit restores the previous schema on reconnect. A separate forward-recovery exercise adds the original tag back from a retained archive while preserving a post-contract description update.

Recorded workload and model inputs

{
  "size_sweep": [
    {
      "rows": 5000,
      "batch_rows": 100,
      "seconds": 0.059222,
      "rows_per_second_observed": 84428.8,
      "database_and_wal_bytes": 2013056,
      "content_preserved": true,
      "committed_batches": 50
    },
    {
      "rows": 5000,
      "batch_rows": 1000,
      "seconds": 0.02121,
      "rows_per_second_observed": 235742.0,
      "database_and_wal_bytes": 1172576,
      "content_preserved": true,
      "committed_batches": 5
    },
    {
      "rows": 50000,
      "batch_rows": 100,
      "seconds": 0.881005,
      "rows_per_second_observed": 56753.4,
      "database_and_wal_bytes": 12307128,
      "content_preserved": true,
      "committed_batches": 500
    },
    {
      "rows": 50000,
      "batch_rows": 1000,
      "seconds": 0.288573,
      "rows_per_second_observed": 173266.6,
      "database_and_wal_bytes": 12212368,
      "content_preserved": true,
      "committed_batches": 50
    },
    {
      "rows": 100000,
      "batch_rows": 1000,
      "seconds": 0.671821,
      "rows_per_second_observed": 148849.1,
      "database_and_wal_bytes": 24696312,
      "content_preserved": true,
      "committed_batches": 100
    }
  ],
  "postgresql_lock_model": [
    {
      "scenario": "ddl-reader",
      "held": "AccessShare",
      "requested": "AccessExclusive",
      "holder_release_s": 2.0,
      "lock_timeout_s": 0.25,
      "modeled_wait_s": 0.25,
      "outcome": "timeout",
      "expected": "timeout"
    },
    {
      "scenario": "index-writer",
      "held": "RowExclusive",
      "requested": "Share",
      "holder_release_s": 0.8,
      "lock_timeout_s": 2.0,
      "modeled_wait_s": 0.8,
      "outcome": "granted_after_wait",
      "expected": "granted_after_wait"
    },
    {
      "scenario": "concurrent-index-writer",
      "held": "RowExclusive",
      "requested": "ShareUpdateExclusive",
      "holder_release_s": 0.8,
      "lock_timeout_s": 0.25,
      "modeled_wait_s": 0,
      "outcome": "granted",
      "expected": "granted"
    },
    {
      "scenario": "two-index-builds",
      "held": "ShareUpdateExclusive",
      "requested": "ShareUpdateExclusive",
      "holder_release_s": 2.0,
      "lock_timeout_s": 0.25,
      "modeled_wait_s": 0.25,
      "outcome": "timeout",
      "expected": "timeout"
    }
  ],
  "postgresql_index_model": [
    {
      "action": "start_build",
      "exists": true,
      "valid": false,
      "release_allowed": false
    },
    {
      "action": "duplicate_detected",
      "exists": true,
      "valid": false,
      "release_allowed": false
    },
    {
      "action": "inspect_catalog",
      "exists": true,
      "valid": false,
      "release_allowed": false
    },
    {
      "action": "drop_invalid",
      "exists": false,
      "valid": false,
      "release_allowed": false
    },
    {
      "action": "repair_fixture",
      "exists": false,
      "valid": false,
      "release_allowed": false
    },
    {
      "action": "retry_build",
      "exists": true,
      "valid": true,
      "release_allowed": false
    },
    {
      "action": "validate_catalog",
      "exists": true,
      "valid": true,
      "release_allowed": true
    }
  ],
  "postgresql_stage_plan": [
    {
      "stage": "expand",
      "transaction": true
    },
    {
      "stage": "backfill_batch",
      "transaction": true
    },
    {
      "stage": "create_index_concurrently",
      "transaction": false
    },
    {
      "stage": "validate_index",
      "transaction": true
    },
    {
      "stage": "contract",
      "transaction": true
    }
  ]
}

Evidence boundaries

Crash tests kill the Python process on Linux while SQLite retains normal filesystem and kernel behavior. They do not simulate power loss, torn sectors or storage-controller failures.
PostgreSQL lock and failed-index cases are deterministic theoretical state models informed by PostgreSQL documentation, not measured PostgreSQL sessions.
The concurrent writer is a synthetic workload over one asset table. No child-table migration, service discovery or live customer traffic is claimed.
The original tag is recovered by an executed forward migration from an exact pre-contract tag archive. Without that archive, capitalization and whitespace cannot be reconstructed.
Local sizes and timings are observations of this environment, not production capacity or downtime guarantees.

Verification record · Acceptance CSV · Runbook and PostgreSQL review · Reproduction source