# Transactional preview, idempotency, and immutable-snapshot probes

Use during independent release review when a change adds Preview → Confirm workflows, deterministic run IDs, daily valuation snapshots, or retryable batch jobs.

## 1. Prove immutable history under partial retries

A schema-level `BEFORE UPDATE`/`BEFORE DELETE` trigger is not sufficient if writers use SQLite `INSERT OR REPLACE`. `REPLACE` can remove and recreate a conflicting row; delete-trigger behavior depends on recursive-trigger settings, so an apparent no-delete contract may still be bypassed.

Construct a synthetic partial run that writes only some child snapshots, then retry with exactly the same business inputs. Compare snapshot IDs/counts, versions/timestamps, supersession lineage, run status, and audits. An identical retry must be a true no-op or append a separately identified version with explicit supersession. Prefer plain `INSERT`, append-only version IDs, and an explicit retry state machine.

## 2. Bind fingerprints to consumed valuation inputs

A fingerprint calculated before price/FX/mapping resolution often binds only positions and policy, not the valuation produced. Complete a synthetic run, change one quote, FX rate, or mapping while keeping positions/cash constant, and rerun. If it returns idempotently without comparing the corrected input, the fingerprint is incomplete.

Include accepted cutoff-safe inputs and provenance: instrument/account identity, quantity/cash lineage, mapping version, provider/symbol, price/source timestamp, currency, FX rate/date/provider, policy/benchmark version, and effective business date. If external inputs cannot be frozen at Preview time, do not claim the Preview fingerprint binds them.

## 3. Prove Preview → Confirm stale detection

Generate Preview, mutate one required synthetic input, regenerate Preview, and compare fingerprints. Probe exact-date prices, FX/mappings, confirmed transactions/coverage, and missing-day sets. Confirm must consume the previewed input set or reject stale state. A hash of account IDs, date ranges, and missing-day counts is a plan fingerprint, not an input fingerprint.

For resumable partial jobs, test drift twice: once before the first Confirm and once after a partial attempt but before retry. The post-attempt revision must cover every input consumed by the resumed writer—not only obvious transaction, price, FX, and output tables. Include instrument master data, provider/symbol/market/currency mappings, active policy/benchmark configuration, scope/coverage, cash and position snapshots, and any other lookup that can change valuation semantics. Mutate one consumed mapping field independently (for example `provider_symbol`) as a narrow counterprobe; acceptance of the original preview/confirmation is a stale-input failure.

When account roles are resolved dynamically from account metadata, bind the **resolved mapping** itself—canonical sorted `(role, account_id)` pairs—into the revision. Hashing only the unordered/set-like account IDs, an optional persisted role table, or a list of role names is insufficient: two cash accounts can exchange semantic roles while every queried row/count remains unchanged. Also bind the account metadata used by resolution (for example `account_name`, type, platform, currency, and active state). Add a synthetic swap probe that exchanges two role-bearing account names or passes a swapped explicit mapping and proves `db_revision`, `preview_id`, and `confirmation_id` all change; the old confirmation must be rejected.

Do not assume a local database revision binds provider responses fetched only after the retry's stale check. Either freeze/persist provider results as previewed inputs, include their immutable provenance in the confirmation contract, or require a new Preview → new confirmation before consuming changed external data. Also verify that a fresh preview actually changes its fingerprint for each supported drift dimension; allowing a new confirmation ID with an unchanged, under-specified preview is not meaningful stale-state recovery.

## 4. Bind confirmation IDs to payload hashes

Replay the same `confirmation_id` with a different source, date range, preview ID, or fingerprint. Correct behavior rejects the mismatch. Persist a unique confirmation ID plus canonical payload hash and compare both transactionally before returning idempotent success. Add a concurrent duplicate-key probe where applicable.

## 5. Do not finalize incomplete child runs

Force one child complete and another partial/failed. The parent must not record successful `*_confirmed` audit state or permanently return idempotent success while work remains. Persist explicit parent states and child outcomes; make incomplete operations resumable. Final success requires all mandatory children complete.

## 6. Validate deterministic stable IDs against semantic drift

Pre-create each predictable ID with one wrong dimension: type, currency, linkage/ownership, active status, portfolio bucket, performance inclusion, or special-purpose flags. Preview and Confirm must fail closed rather than equating existence with compatibility. Pair negative cases with a valid pre-existing object and prove valid retries are no-ops.

## 7. Verify tests target real production identities

A rollback or read-only security test can pass vacuously when it queries a stale `source`, action name, or endpoint path. Before trusting the assertion, trace the writer constants and router/OpenAPI contract, then assert against those exact identities. For write rollback, use at least two child rows and force the later write to fail so the test proves an earlier successful insert is rolled back—not merely that the first insert failed before any partial state existed. For write-disabled HTTP tests, first assert the canonical route exists in OpenAPI; global middleware may return the expected `403` for an unknown POST path.

## 8. Keep idempotency and version allocation inside the write lock

For immutable daily snapshots, perform or repeat the existing-run check, predecessor lookup, and next-version allocation after acquiring the write lock. A pre-lock idempotency check followed only by a fingerprint recheck can turn concurrent identical runs into duplicate-key failures and can compute stale supersession/version metadata. Probe two concurrent identical runs: one may write, but the other must return a truthful idempotent result rather than `failed`; then probe concurrent different-input runs for explicit append-only version lineage.

## Verdict guidance

Treat silent immutable-row replacement, unbound financial-input confirmation, incompatible account-role acceptance, or final success over partial children as P1 release blockers (P0 only for demonstrated irreversible loss or security impact).