# Audited neutralization of legacy orphan foreign-key references

Use this recipe only after production integrity checks reveal a fixed, pre-existing set of orphan references and the owner explicitly authorizes neutralization rather than relinking or a release exception.

## Fail-closed preconditions

Prove on a fresh SQLite online-backup copy, before touching production:

1. `PRAGMA integrity_check` is `ok`.
2. `PRAGMA foreign_key_check` returns exactly the owner-authorized count and only the expected child table, column, and parent table.
3. Every referenced parent identity is absent.
4. Exact source ID, external ID, row hash, and any other deterministic identity produce zero canonical candidates. Never use fuzzy or near-match evidence.
5. The child FK column is nullable.
6. Every affected workflow row is already terminal/non-replayable (for example `execution_status='imported'`). If not, stop: nulling the FK could make the work executable again.

Persist raw child/parent identities only in an owner-only (`0600`) private manifest. Freeze that manifest as the expected identity set for the live operation; do not rediscover a different set and silently repair it.

## Live repair pattern

- Quiesce relevant writers and schedulers first.
- Create a new mode-`0600` online backup immediately before repair.
- Open the productive connection with `PRAGMA foreign_keys=ON` and verify it reads back as `1`.
- Start one `BEGIN IMMEDIATE` transaction.
- For each frozen pair, issue an identity-bound update such as:

```sql
UPDATE child_table
SET orphan_fk = NULL
WHERE child_id = ?
  AND orphan_fk = ?
  AND execution_status = 'imported';
```

- Require the aggregate update row count to equal the exact authorized count; otherwise roll back.
- Insert one immutable audit event per row in the same transaction. Record the old reference, new null state, child identity, UTC timestamp, reason, actor, and a stable action code such as `legacy_orphan_transaction_reference_neutralized`.
- Do not alter workflow status, execution timestamp, payload, source hash, plan content, or unrelated notes.
- Before commit, require zero FK findings and `integrity_check=ok`; any mismatch rolls back the entire transaction.

## Verification matrix

Capture table-level logical digests before and after. Expected differences are only:

- the authorized FK column on the exact child rows; and
- the exact number of new audit rows.

Require:

- financial transaction count and content digest unchanged;
- balances, portfolio, household bookings, import candidates, and every other table unchanged;
- child row count and every child column except the repaired FK unchanged;
- exactly the authorized number of audit rows added;
- zero FK findings and `integrity_check=ok` after commit;
- a second execution is a clean no-op with no duplicate audit rows;
- a new post-repair backup restores independently with matching logical digest, zero FK findings, and `integrity_check=ok`.

Keep detailed evidence owner-only and report only masked counts, changed-table names, gate results, and artifact modes.

## Deployment boundary

A successful legacy repair is not a release exception and does not authorize transactional Confirm. Complete exact-commit deployment and read-only Preview as separate gates. If the execution layer blocks a healthcheck or service probe after an approved service action, do not reroute around it; preserve the accurately known service state and obtain fresh direct consent before further side effects.