# Review-driven financial read-model hotfix release

Use this pattern for stateful financial applications when imported source data is correct but one or more read models aggregate it incorrectly.

## 1. Freeze the data boundary

- Prove the defect is in projection/aggregation before proposing rollback or data repair.
- Keep official imports, provenance, archives, and unrelated portfolio providers immutable during development.
- Select official components by durable roles and provenance, never account names or amount heuristics.
- Treat provider account totals as reconciliation controls unless the contract explicitly declares them additive components.
- Evaluate the candidate code read-only against a production backup or a `mode=ro` connection before merge. Emit aggregate values only.

## 2. Preserve temporal semantics

A snapshot-backed read model needs two independent boundaries:

- economic `as_of`: select by the source-local calendar date;
- processing cutoff: select by the import/creation timestamp.

For offset timestamps stored as text, compare the leading ISO date (`substr(timestamp,1,10)`) when the contract is source-local calendar day. SQLite `date()` normalizes offsets to UTC and can move shortly-after-midnight snapshots into the previous day.

After choosing a snapshot baseline, replay supported confirmed movements that occurred after it. Holdings and cash must advance together. Include authoritative cash-leg postings written to a cash-balance table by workflows whose transaction remains on the securities account. Avoid double counting workflows that materialize both a transaction and a cash-balance row on the same account.

## 3. Preserve generic cash behavior

When introducing an official-provider projection path, regression-test non-provider accounts separately:

- all ledger currencies remain visible, even when they differ from the account default;
- materialized cash-only currencies remain visible;
- manual/account-total controls emit one account-level row rather than one duplicate per currency;
- nullable CHF conversions do not crash aggregation and remain visibly unreconciled;
- sale aliases accepted by the transaction layer have matching signed-cash semantics.

For signed reconciliation components, proportional scaling is valid when raw and target totals have the same non-zero sign. For zero/opposite-sign edge cases, preserve rounded signed components and apply the residual to the largest absolute component; always assert the allocated sum equals the authoritative target.

## 4. Consume independent review as a gate

`codex review` or another review command returning exit code 0 proves only that the reviewer completed. It does **not** prove that there were no findings.

- Redirect the complete review output to an owner-safe temporary file when normal tool output may truncate.
- Read the terminal verdict and every full review comment.
- Disposition every finding with code plus a focused regression, or record a justified rejection.
- Re-run the independent review after each review-driven code/test change.
- Freeze the candidate only when the final review explicitly reports no introduced issue.

Keep focused tests fast during the review loop. Run full backend/frontend/static gates only after the candidate stabilizes, then run the aggregate release command if required.

## 5. GitHub API fallback without weakening gates

When the preferred GitHub CLI is unavailable, use the REST API with a token read at runtime from its owner-only file; never put the token in arguments or output.

- Create PR: `POST /repos/{owner}/{repo}/pulls`.
- Poll the workflow run from `GET /repos/{owner}/{repo}/actions/runs?head_sha={sha}` and then `GET /repos/{owner}/{repo}/actions/runs/{run_id}`.
- A token may be able to read workflow runs while `check-runs` or combined-status endpoints return 403. Fall back to the workflow-runs endpoint and report that exact evidence; do not call CI green from PR mergeability alone.
- Merge normally: `PUT /repos/{owner}/{repo}/pulls/{number}/merge` with the reviewed head SHA and configured merge method.
- Verify the PR is merged and `refs/heads/main` equals the returned merge SHA before deployment.

## 6. Coherent SQLite backup and restore evidence

For a live SQLite database, prefer `sqlite3.Connection.backup()` over a raw filesystem copy so WAL activity cannot produce an incoherent backup.

Before deployment:

1. create the online backup outside Git;
2. set owner-only permissions;
3. compute SHA-256 and write a checksum file;
4. restore/copy it to a separate restore-test path;
5. verify restored SHA-256, `PRAGMA integrity_check`, schema version, protected table counts, and aggregate business digests;
6. store only aggregate evidence in an owner-only runtime artifact.

A failed evidence script does not invalidate a successfully created backup. Reuse and verify the same immutable backup rather than silently producing a second baseline, then fix only the evidence query.

## 7. Exact deployment and UAT

- Stop/restart stateful services in explicit calls, not hidden inside a long compound command.
- Fetch remote main, check out the exact merge SHA in the deployment worktree, build runtime assets, then start services.
- Verify service state, process working directories, deployment `HEAD`, and `origin/main` independently.
- Discover actual readiness and business endpoints from service configuration/OpenAPI/router definitions; do not assume a generic `/health` route exists.
- Complete API, browser/responsive, read-only database digests, and import-idempotency UAT before declaring the sprint closed.
- If an execution layer explicitly blocks a probe pending consent, stop rather than routing around it; report deployment as complete and UAT as pending.

## 8. Prove production import idempotency without inventing a second baseline

When the release contract requires a post-deploy idempotency UAT, use the already archived, hash-addressed source bundle and overview document through the local API only. Do not reconstruct or edit the source files.

1. Open the production database read-only and resolve the archive references plus durable account-role mapping.
2. Before the API call, capture counts and deterministic row digests for every economic/provenance table the import may affect (batches, snapshots, snapshot components, documents, ledger events/components, canonical transactions, account values, cash balances, and position snapshots). Keep raw rows out of output.
3. Run Preview with the archived bytes. Require `duplicate=true`, `conflict=false`, the expected component counts, and matched reconciliation.
4. Confirm that exact preview once. Require the response to identify the operation as idempotent and return the existing confirmed batch.
5. Recompute the same table counts/digests and `PRAGMA integrity_check`. Economic/provenance tables must be byte-semantically unchanged. Exclude the generic audit-log table from the strict no-change set when the API contract legitimately records an idempotency attempt, but report that distinction explicitly.
6. Re-query the public aggregate endpoints after the confirm so a no-op import cannot mask a read-model regression.

This is stronger than checking counts alone: duplicate rows can replace or alter values without changing cardinality. It is also safer than comparing the SQLite file hash, which can change because of page layout, WAL/checkpoint, or allowed audit metadata even when economic rows are unchanged.