# SQLite FK-safe compatibility migrations and release closure

Use this reference when an additive SQLite upgrade succeeds on fresh/local probes but fails on a populated production-equivalent database with `FOREIGN KEY constraint failed`.

## Reproduce once, with the real execution contract

1. Create a working copy of an existing online backup; never debug against the production database.
2. Open the copy through the application's normal connection factory and explicitly assert `PRAGMA foreign_keys = 1`.
3. Before migration, record schema version, `PRAGMA integrity_check`, `PRAGMA foreign_key_check`, and relevant table counts. If `foreign_key_check` is non-empty, stop: that is a pre-existing data-integrity issue, not permission to rewrite records.
4. Run the migration once through the same CLI, connection, and transaction path as production. A probe using raw `sqlite3.connect()` is insufficient because SQLite defaults foreign-key enforcement to off per connection.
5. Determine the first failing operation from migration order plus `sqlite_schema` and `PRAGMA foreign_key_list` inspection. Identify the exact parent table, referencing child table, and whether the failure is create/copy/drop/rename/insert ordering.

## Minimal compatibility-repair pattern

Historical compatibility functions must detect current structure, not assume every start still needs every repair.

- Inspect the canonical table's `sqlite_schema.sql` for the obsolete renamed parent that the repair was designed to remove.
- Rebuild a child table only when that obsolete reference is actually present.
- Do not infer repair necessity merely from the presence of an old shadow/temporary table; a stale shadow can coexist with an already-correct canonical table.
- Never unconditionally drop and recreate an already-correct parent table. A later child may legitimately reference it and make `DROP TABLE` fail under active FK enforcement.
- If a rebuild is genuinely required, order dependency removal/recreation child-first and preserve all canonical rows. Stop for review if this requires semantic data rewriting or broad historical-migration reconstruction.
- Keep restart behavior safe: no `foreign_keys=OFF`, constraint ignore, data deletion, `IntegrityError` catch-and-continue, or manual production SQL.

A common exact failure shape is: a compatibility routine always rebuilds a parent-like table, while a newer table references it. The canonical table is already fixed, so the correct repair is to skip the obsolete rebuild based on schema introspection—not to weaken the newer FK.

## Focused regression fixture

Build a synthetic fixture, never a copied production database in Git:

1. Start with the current migrated schema and `foreign_keys=ON`.
2. Insert minimal parent data plus a child row whose FK points at the canonical table.
3. Add the historical shadow-table shape that caused the compatibility routine to think work remained.
4. Invoke the compatibility function again.
5. Assert parent/child row counts unchanged, FK enforcement still on, empty `foreign_key_check`, `integrity_check=ok`, and unchanged final schema version.

This fixture proves the precise idempotency failure without embedding financial data.

## One final verification cycle

On the final code candidate, run:

- the focused FK regression;
- a fresh database through all migrations;
- a new working copy of the real pre-upgrade backup through the production CLI/connection path;
- post-upgrade schema version, FK enforcement, empty `foreign_key_check`, `integrity_check=ok`, unchanged counts for all common persistent tables, and expected new tables;
- a second normal migration/application start to prove restart safety;
- lint/compile only for changed backend files, `git diff --check`, and repository safety.

Do not repeat unrelated backend, frontend, typecheck, build, or browser suites when the patch touches only migration code, focused tests, and documentation and their prior evidence remains valid. If final documentation alone changes after the cycle, rerun only gates invalidated by documentation (normally diff/repository safety), not the whole migration cycle.

## Release and rollback gates

- Merge only with final CI green, production-equivalent migration green, empty FK check, and P0/P1/P2 all zero.
- Immediately before production migration, create exactly one fresh online backup and record schema, `foreign_keys=ON`, empty FK check, integrity, byte size, and SHA-256.
- Deploy the exact merge SHA from the designated deployment worktree and migrate only through the tested CLI/connection path.
- On any migration error: stop services, do no ad-hoc repair, restore the backup, reactivate the prior known-good SHA, and report the first failure.
- After success, perform only the scoped read-only smoke test when no frontend code changed.

Static runbook/process changes belong in the hotfix commit. Dynamic evidence that cannot exist before merge—final merge SHA, backup hash, deployment SHA, and production smoke result—belongs in the PR/release audit after deployment. Phrase pre-deployment status entries as checkpoints so they do not falsely claim a still-pending state is current after release.