---
name: transactional-data-release-engineering
description: Build and release audited transactional data imports, canonical identity mappings, historical provider ingestion, and fail-closed CI-gated migrations.
version: 1.27.2
metadata:
  hermes:
    tags: [software-development, transactions, migrations, data-integrity, ci, audit]
---

# Transactional Data Release Engineering

For additive SQLite event-history migrations with immutable correction chains, schema-object-preserving table rebuilds, exact managed-trigger verification, HMAC-bound Preview → Confirm, effective plan-consumption semantics, and copy-first restore evidence, use `references/append-only-event-history-sqlite-migrations.md`.

For migrations-free, read-only grouped review contracts over legacy mapping/queue/rule/action tables, use `references/migrations-free-grouped-review-contracts.md`. It separates punctuation-preserving strict identity from legacy aggressive normalization, fails closed on collisions, binds target revisions/counts for Preview→Confirm, preserves concurrent writer compatibility, and verifies no writes or identifier/path disclosure.

For crypto ledgers that have a trusted historical anchor but incomplete current quantities and activities, use `references/crypto-observed-balance-reconciliation-cockpit.md`. It covers append-only observed snapshots, explicit wallet coverage, delta-without-invention semantics, current-balance selection, separate balance/price freshness, closed performance gates, provider-free rendering, and copy-first exact-SHA release evidence.

For canonical calendar-day valuation activation, source tracking modes, confirmed-only crypto reconstruction, resumable backfill audits, append-only snapshot supersession, fail-closed scheduler activation, progressive source-scoped metrics, and read-only production UAT, read `references/canonical-daily-valuation-activation-and-release.md`.

For a strict no-write activation inventory that reconciles physical versus canonical snapshots, probes the shipped TTWROR/XIRR reader, detects account-scope and activity-kind incompatibilities, derives exact user-file checklists, compares YTD versus earliest-common starts, and proves a before/after database sentinel, read `references/read-only-performance-activation-data-preview.md`.

When an owner says today's quantities equal an older snapshot but later discloses unrecorded transfers or adjustments, read `references/owner-attested-endpoint-vs-ledger-coverage.md`. It separates a provisional working quantity, a confirmed endpoint, and complete activity/cashflow coverage so unchanged endpoints never activate XIRR/TTWROR by implication.

For multi-account component valuations, coherent same-day source-set selection, non-destructive performance classification, economic cashflow deduplication across confirmation IDs, non-shrinking period coverage, visibly reviewable CSV/manual Preview UX, and post-review release closure, read `references/component-valuations-and-period-evidence-hardening.md`.

For brokerage portfolios composed of a securities depot plus settlement cash, read `references/brokerage-depot-settlement-anchor-activation.md`. It separates source-anchor confirmation from canonical component materialization, activity coverage, performance readiness, and job activation; counterprobes hidden cash double-counting and unsigned transfer direction; and defines exact stop-gate evidence requests plus no-write closure proof.

For managed portfolios with intermittent official anchors, bank-derived contributions, dual consolidated/performance transfer semantics, residual-cash modelling, provisional TTWROR, source-gated daily jobs, and adversarial post-fix release review, read `references/managed-portfolio-bank-flows-and-modelled-valuations.md`.

For an anchor-bound instrument activation that resolves ISIN+listing+currency safely, rehearses the real provider/model path on a production copy, selects one common actual market day, distinguishes writer idempotency from provider-row updates, and closes the provisional API/UI contract, read `references/anchor-bound-instrument-model-activation.md`.

For the first confirmed managed-portfolio performance activation, including day-end opening-anchor cashflow boundaries, production-clone rehearsal, separate history/rule/coverage capabilities, strict response-model validation, replay delta proof, and existing-UI reuse, read `references/managed-performance-first-activation.md`.

For exact-tree review freshness, stable filter identity, cross-scope reconciliation, complete read-model snapshots, race-safe dashboards, responsive acceptance, and temporary-UAT cleanup, read `references/review-freshness-and-cross-scope-reconciliation.md`.

For final verdict-only read-model reviews, use `references/exact-tree-read-model-counterexample-probes.md`: include untracked files, compare raw SQL with the migrated schema, activate realistic non-empty branches, separate historical period-end from request `as_of`, and probe unavailable-versus-zero plus freshness boundaries.

For provider/currency/FX status fields derived from rows bound to a completed run, use `references/run-bound-provenance-contract-review.md`: aggregate every bound row before provider filtering, account for SQL aggregate NULL behavior, and run missing/extra/NULL/mixed/alternate-provider counterexamples through strict API and UI contracts.

For read-only audits of PDFs that mix holdings, exact grouped totals, tax/reporting sections, cash, and multiple activities, use `references/grouped-statement-multi-event-parser-review.md`. It separates sections before regex extraction, prevents subtotal/component and statement/confirmation double counting, defines stable row-level events, and covers concurrent-worktree attribution.

Use this skill for systems that import externally sourced records into a canonical ledger or portfolio, especially when a preview/confirm contract, schema migration, provider history, audit lineage, and controlled deployment must succeed together.

## Core invariants

1. **One transaction owner:** the outer batch/confirm operation owns `BEGIN`, `COMMIT`, and `ROLLBACK`.
2. **No hidden commits:** nested mapping, metadata, quality-alert, audit, and reconciliation helpers accept and propagate `commit=False`.
3. **Canonical identity is database-enforced:** normalization rules in application code and unique indexes must agree.
4. **Provider metadata is evidenced:** never invent currency, venue, date, identifier, or rate when omitted.
5. **Temporal fields remain distinct:** requested `as_of`, actual provider `price_date`, and `fetched_at` are separate.
6. **Historical requests fail closed:** no current quote may satisfy a requested historical date; future observations are invalid.
7. **Preview binds confirmation:** hash mapping decisions, scope, source revision, and exceptional approvals into the preview/confirm contract.
8. **Source rows are retained:** corrections and supersession are explicit, auditable, and non-destructive.
9. **CI is a release gate:** inspect exact failed jobs and logs, correct the branch, and rerun the complete remote workflow before merge.
10. **Availability is not persistence:** track accepted inputs, valuation coverage, and newly written rows separately; cached observations must retain their exact source-row provenance.
11. **Historical activity cannot overlap an active synthetic baseline:** either prove complete-history equivalence and supersede the full baseline atomically, or append strictly after the baseline date.
12. **Classify before aggregating:** control totals, additive holdings, audited valuations, and historical fallbacks are distinct roles; select them by explicit role, provenance, `as_of`, and cutoff—not by account name or amount.
13. **Scope is not cashflow coverage:** an included account and zero recorded activities do not prove complete external-flow history; TTWROR/XIRR require explicit audited period coverage for every selected account.
14. **Mirrored flags cannot drift:** when a legacy account flag projects an authoritative audited classification, synchronize and guard both directions in the database, not only in application helpers.
15. **Deterministic identifiers are sensitive:** persist source/file/row/business identities as domain-separated keyed digests, keep the key in owner-only runtime configuration, and never expose stored-digest prefixes. Ordinary display-only rows may use per-preview ordinals; any row-level decision that must survive a re-preview uses a private HMAC-derived token bound to the exact source row/file identity and fingerprinted decision version.
16. **Near-match is not sameness:** exact source duplicates may be suppressed, but logical business matches without authoritative source identity must be reviewed; reconcile the new engine against both legacy staging candidates and canonical ledger rows.
17. **Enrichment follows lineage:** when a staging candidate has become a productive transaction, receipt/detail matching treats that candidate+transaction as one money movement and prefers the productive row.
18. **Enrichment is one-to-one:** a linked money candidate or productive transaction cannot be consumed by a second receipt; filter consumed rows in matching and enforce independent partial unique indexes for both lineage forms.
19. **Advertised review actions are executable:** list/read models and Preview/Confirm must call one shared capability function so no action can be shown as supported and then fail at preview.
20. **Local gates mirror remote CI:** enumerate every workflow command and schema constant; a green default test target does not cover custom migration/control scripts unless it actually invokes them.
21. **Provider parsing never optimizes for an expected count:** reconstruct rows from header-backed structure, retain uncertain near-matches for review, and open a stop gate when conservative source evidence contradicts an approved golden total.
22. **Explicit profiles still prove their headers:** a caller-supplied profile must match deterministic required-header detection, and a file with zero importable logical rows is non-confirmable; never persist a successful empty batch that can suppress a corrected retry.
23. **Source contracts stay private:** tracked evidence contains sanitized aggregate structure and role rules; provider balances, account suffixes, detailed counts, and chain-audit evidence live in an owner-only runtime manifest consumed without being printed.
24. **Mapping semantics are revalidated completely:** active state, currency, role, portfolio bucket, performance scope, reserve flags, and canonical linkage are checked both when configuring and whenever a stored mapping is resolved; drift fails closed.
25. **Backups are private from byte zero:** create a new SQLite backup target atomically with mode `0600` before opening it, then prove backup integrity, independent restore, logical digest equality, and pre-commit integrity/FK invariants.
26. **Deployment proves runtime dependency closure and import provenance:** build a fresh environment from tracked dependency metadata and import every newly introduced production module before restarting services; then print the imported module path plus a release-specific version marker. A green developer venv—or a successful editable-install message—is not evidence that production imports the intended tree, because an older `site-packages` copy may still win resolution.
27. **Read-only production previews prove non-persistence:** compare the productive database before/after, hash source files before/after, require zero new batches/candidates, store only an owner-only aggregate report, and stop before Confirm unless separately authorized.
28. **Technical confirmability is not business readiness:** expose both states; enforce the business-readiness gate again at the final server-side write boundary and keep Confirm blocked when classification coverage, critical semantics, or review-volume gates are unmet even if Preview reconstruction is valid.
29. **Coverage metrics stay honest:** publish ordinary monetary denominator, proposed-or-neutral coverage, singleton reviews, grouped ambiguity, and critical checks separately; grouped review is not categorization coverage and a fallback category cannot make the gate green.
30. **Cluster decisions are finite, inspectable capabilities:** bind normalized family, compatible semantics, exact member tokens, exclusions, selected category/neutral action, rule version, and learning baseline into Preview; every affected member must be inspectable/excludable before confirmation, and one cluster decision must never become an unlimited global rule.
31. **Receipt matching is bidirectionally unique:** a receipt must have exactly one exact money candidate and that money movement must be exact for exactly one receipt; evaluate every receipt regardless of amount unless an explicit business contract says otherwise, and never create a second expense for unmatched detail.
32. **Bound decisions still require semantic eligibility:** valid HMAC row tokens and fingerprints do not authorize a high-impact action; advertise and accept actions only for a narrow server-derived eligibility state, and reject ordinary categorized rows.
33. **Async previews are generation-bound:** input changes and discard invalidate in-flight responses; an older response must never restore stale private rows, fingerprints, mappings, or confirmability under a newer visible selection.
34. **Category existence is not semantic compatibility:** carry category type through canonical, learned, override, and cluster lookups; expense/income/neutral rows may only select compatible categories, enforced again at Confirm.
35. **A linked enrichment has one durable target:** pending, superseded, duplicate, neutral-transfer, and non-writable rows are not receipt money; `linked` must resolve under the write transaction to exactly one candidate or productive transaction, or the entire Confirm rolls back. Unmatched or ambiguous detail may remain in the preview/review candidate, but must not create a relationship-table row with both target columns null; expected-write counts include only durable links.
36. **New write representations invalidate old aggregate shortcuts:** trace them through all read models and aggregate economic volume by logical business event, not storage rows. A linked two-leg transfer contributes one logical volume globally, an unmatched transfer contributes once, an account-filtered view with one visible leg still contributes the full scoped volume, and missing canonical FX makes the logical unit unavailable rather than producing zero or a partial sum.
37. **Every categorization write boundary revalidates semantic type:** inline and cluster Preview guards are insufficient. Post-import inbox selection, batch Preview, final Confirm reconstruction, and any legacy review action must derive expense/income semantics from the immutable signed amount and require an active category of that exact type before writing.
38. **Readiness denominators exclude neutral movements:** safe transfer legs, credit-card settlements, and user-confirmed neutral transfers must not inflate ordinary monetary coverage. Prove this adversarially with many neutral pairs plus one unresolved ordinary expense; coverage must remain `0/1`, not become green through denominator dilution.
39. **Unmatched-transfer eligibility requires decisive server evidence:** generic phrases such as `transfer`, `payment`, `Zahlung an`, `E-Banking`, `wallet`, or `top up` are not enough. Accept only narrowly structured own-account markers or an independently derived transfer ambiguity; valid tokens and explicit user choice cannot neutralize an ordinary expense.
40. **Private business knowledge belongs in audited data, not source taxonomy:** review added merchant fragments, personal salary categories/families, addresses, and export-specific text cutoffs for real-data leakage. Keep only explicitly approved canonical category names needed by the contract; merchant-specific rules belong in active database rules with provenance and semantic checks.
41. **Replacement-file reads invalidate immediately:** do not wait for asynchronous `File.text()`/reader completion before clearing the old preview and Confirm capability. On change start, increment generations, clear fingerprints/decisions, block file and mapping controls while reading, and discard stale API or file-reader completions. Test with deferred promises at both layers.
42. **Transfer evidence excludes fee-like movements:** even a strong own-account phrase is not decisive when the same description denotes a fee, charge, commission, cost, or service entitlement. Apply explicit multilingual disqualifiers after normalization and prove that examples such as `internal transfer fee`, `wallet top up fee`, and `own account transfer fee` remain ordinary expenses and cannot be user-neutralized.
43. **Production integrity is a pre-merge release input:** run read-only `integrity_check` and `foreign_key_check` on the exact productive database—and on the intended online backup/restore—early enough that pre-existing drift cannot be discovered only after merge. Compare with the previous verified backup to establish provenance, but do not silently waive, null, relink, or repair unrelated legacy rows. If the required FK gate is nonzero and no deterministic reconstruction exists, stop before service changes and obtain an explicit choice between a separately audited repair, a documented release exception, or a fail-closed pause.
44. **A client timeout is not server cancellation:** never overlap expensive production Previews merely because the caller timed out. Establish completion/cancellation first, or use one authorized service restart to terminate orphaned computation; then run exactly one tracked retry with a realistic bound.
45. **Fallback source identity is fingerprinted mapping evidence:** when a provider file has authoritative row-level IDs plus permitted blank-ID rows, bind the approved file-level fallback reference and complete allowed mapping set into Preview. The fallback may fill blanks only; it never overrides a row-level identity or authorizes a similar account.
46. **Preview evidence has explicit attempt lineage:** every production Preview attempt writes immutable, owner-only evidence under a unique attempt/revision name. A mapping-invalid, timed-out, cancelled, or otherwise non-authoritative attempt is marked superseded with its concrete reason; never overwrite it or let a late background completion replace the accepted report. Final release evidence names exactly one authoritative attempt and reconciles delayed process notifications against that lineage.
47. **Public redaction never mutates Confirm input:** retain complete descriptions and private evidence in the internal reconstructed preview, redact only in the public serializer, and make Confirm consume the internal reconstruction. Prove both public non-disclosure and successful Preview → Confirm persistence in one regression test.
48. **Readiness counts only executable decision units:** a non-actionable cluster does not reduce individual-review counts. Cluster exclusions return to the individual inbox with their own compatible action; selected cluster members may remain represented by the group.
49. **Materializable proposals remain inspectable:** automatically classified rows may be separated from exceptions, collapsed, and paginated, but must not disappear before Confirm. Users need a bounded way to inspect and correct every proposal that can write.
50. **Income markers do not outrank structured transfer evidence:** salary/refund terms inside account-transfer wording remain review-only until transfer evidence is resolved. For example, `Übertrag von Lohnkonto` must not become a salary proposal merely because it contains `Lohn`.
51. **Performance telemetry is outside the confirm identity:** phase timings and total API duration are observational, non-deterministic fields added only after the canonical preview fingerprint is computed. Optimize request-local lookup reuse under a parity harness first; version semantic classifier changes separately, and never let a plausible merchant family override conflicting confirmed history.
52. **Applied preview state is an immutable confirm capability:** volatile drafts and the last successful applied request are separate objects. Failed Apply retains both the old preview and dirty draft; Confirm submits only a deep-cloned applied request with its matching fingerprints. Incomplete local cluster drafts may retain exclusions for editing but are omitted from serialization and cannot masquerade as applied state.
53. **Readiness limits have one source of truth:** the displayed limit, pass/fail predicate, Confirm reconstruction, boundary tests, and decision-package report use the same policy constant. A deliberate policy correction is fingerprint-significant and must be isolated from performance parity rather than mislabeled as classifier drift.
54. **Request caches preserve query tie-breaking:** bulk loading may remove repeated SQL but cannot change first/last selection, priority order, duplicate handling, or category semantics. Probe valid-schema duplicate names because `fetchone()` and dictionary materialization often select opposite rows.
55. **Merchant normalization is boundary-aware:** unrestricted compact substring matching is not a safe public taxonomy. Encode known glued/punctuation spellings explicitly and prove near-miss supersets remain unclassified.
56. **Applied-decision removal is explicit state:** clearing an applied category or transfer decision needs a visible tombstone/tri-state, participates in dirty comparison, serializes as removal, and never visually falls back to the old server value.
57. **Classifier fixes invalidate calibration artifacts:** after any semantic or safety correction, withdraw the old readiness report and decision package, regenerate the real read-only Preview and private sorted dossier, and ask the user only against the revised package.
58. **Approval closure attacks the complete capability:** a keyed token is insufficient unless minting and consumption bind the current complete member set, action, evidence, baseline, lifetime, and nonce. Probe public-field forgery, subsets, exclusions, omitted members, stale/expired use, immediate no-write replay, expired no-write replay, and committed audit evidence while keeping the tree unchanged.
59. **Logical digests are comparable only under one frozen canonical algorithm:** use the same table set, exclusions, column ordering, row ordering, type serialization, and null/blob encoding before and after backup, restore, Preview, and Confirm. Two individually deterministic digests built with different row-order or serialization rules are not drift evidence; label their methods or reject the comparison.
60. **Visibility UAT and maximum-limit load tests are different gates:** prove the shipped UI with its actual default query, pagination, viewport, and post-Confirm history/transaction cards first. Treat a manually forced maximum page size as a separate load probe; do not let a client timeout overlap another request. Establish completion/cancellation or perform one controlled service restart, then recheck database integrity and service health. A green Preview performance gate does not imply that a review/read model with per-row reclassification is performant.
61. **Canonical-currency totals never invent conversions:** when the canonical amount is null, use the original amount only if its currency is already canonical. A foreign-currency row with missing/unconfirmed FX contributes zero to canonical income, expense, category, budget, and net totals, remains visible in its original currency, and makes aggregate availability explicitly partial. Probe this through summary, category, overview, chart, export, and transaction detail contracts.
62. **Legacy metadata and typed lineage need one effective resolver:** when an older release stored a relationship only in valid JSON notes but the current schema has a typed foreign-key column, future writes populate the typed column while read models temporarily resolve `typed_column -> guarded json_valid/json_extract fallback`. Use that same effective relation for monetary effects, category attribution, filtering, and display; add separate future-write and legacy-read regressions before retiring the fallback.
63. **Review proposals count executable actions, not non-null suggestions:** `proposal_ready`, its filter predicate, item serializer, and final write capability must share one eligibility rule. A proposed category ID counts only when the category exists, is active, and matches the immutable income/expense semantics; otherwise the row is `decision_needed`. Counterprobe wrong-type, inactive, and missing category IDs and require `filtered_total` to match every returned item state.
64. **Pagination cursors are snapshot capabilities:** bind filter/order scope and server `data_version` into every cursor, verify the version again after counts/facets/page reads, and reject stale or mixed snapshots with conflict. The client discards accumulated rows and restarts from page one; it never appends across versions.
65. **Facets describe the authorized result set, not the visible page:** source/account/category/status options are separate server aggregates over the complete filtered scope. Page-local option derivation silently hides valid filters and makes empty-state recovery impossible.
66. **Every asynchronous read is generation-bound:** filterable lists and pagination need the same stale-response discipline as Preview. Fresh requests clear stale rows, append requests bind the expected cursor, and obsolete success/error/finally completions cannot overwrite newer state. Preserve stable global facets while replacing rows.
67. **Composed financial read models reuse one effects scan:** annual status, charts, forecasts, and category summaries aggregate one canonical row-level effects relation. Pass already-loaded year rows through composed functions, expose partial canonical-currency availability, and regression-test loader call count plus output parity.
68. **Snapshot versions are mutation-complete, not timestamp heuristics:** `COUNT(*)` plus `MAX(updated_at)` cannot bind a cursor snapshot; a non-max-row update, same-resolution timestamp update, or count-preserving replacement can evade it. Prefer a durable monotonic revision maintained at every write boundary. If a migration/revision counter is out of scope, use a deterministic streaming digest over every row and joined lookup value that can affect ordering, filtering, counts, or rendering, verify it before and after the page query, and record a production-scale latency gate.
69. **Every excluded confirmed record makes aggregates partial:** missing canonical FX is not the only completeness gap. Unlinked refunds, unresolved reversals, or any confirmed row intentionally assigned zero/unknown financial effect must contribute an explicit reason/count to canonical availability. Propagate that status through overview, monthly summary, category/status, chart, export, and detail contracts; never return `current` with a reassuring message while confirmed records are omitted.
70. **Refund lineage is eligible and cumulatively bounded:** a foreign key or JSON relation alone cannot reduce expenses. The origin must be a confirmed eligible expense/fee with a trusted canonical amount, cumulative accepted refunds must not exceed that amount, and deterministic ordering must decide which refunds remain effective. Preview and Confirm share the check; Confirm revalidates under a write lock, while invalid import-discovered links fail closed to review. Category attribution, filtering, detail, charts, and exports use the same effective relation.
71. **Neutral effect does not imply known canonical volume:** a transfer without trusted canonical FX still has zero income/expense/budget/net effect, but its canonical-currency volume is unavailable—not zero and not a complete partial subtotal. Return `null` or an explicit unavailable contract, increment missing/transfer counts, and propagate `partial` through every aggregate surface.
72. **Lifecycle status outranks mirrored review flags:** centralize one exact global open predicate. A stale legacy flag cannot close an authoritative open status or reopen a terminal/non-open status. Overview, navigation badge, cockpit KPI, summary segments, facets, pagination, and inbox all consume that same global contract; counterprobe both stale-flag directions.
73. **Review snapshot versions include classifier dependencies:** if rendering or confirmability performs live classification, cursor identity covers confirmed history, referenced staging rows, merchants/aliases, review and recurring rules, categories, mappings, and accounts—not only the visible candidates. Mutate each lookup family independently and require the old cursor to conflict.
74. **Inbox tabs are server-scoped capability views:** semantic unions are filtered before pagination and parenthesized under the authoritative open predicate. The client restarts from page one after a cursor conflict, honors server `can_confirm`, and offers only categories compatible with immutable financial type; special cases stay in dedicated server-revalidated actions.
75. **Relation joins cannot amplify economic rows:** never join a canonical effects row directly through an unconstrained many-membership relation or an `OR` over both relation legs. First normalize both legs into a one-row-per-transaction membership CTE (or enforce equivalent database uniqueness), detect more than one distinct logical membership, and fail that unit closed with explicit integrity metadata. A malformed relation may make volume unavailable, but it must not multiply transaction counts, income, expense, refunds, or categories.
76. **Financial quality reaches the visible decision surface:** reason-specific counts, `data_status`, and structured warnings propagate through every changed summary, month, chart, category, cockpit, and analysis contract and into the frontend. An empty category list or zero-valued KPI is not evidence of complete data; envelope empty collections so incompleteness metadata survives, and render a visible partial-data warning rather than trustworthy-looking zeroes.
77. **Unmount invalidates asynchronous readers:** generation guards cover component lifecycle as well as filter races. Increment or abort on unmount before any delayed success can update rows, errors, loading state, browser history, or the destination route. Regression-test a deferred request resolved after unmount and require zero URL/history mutation.
78. **Relation integrity is graph-scoped, not type-scoped:** a conflicting relation attached to an expense, fee, refund, or other non-transfer row can still poison transfer-unit identity. Any membership conflict in the scoped relation graph makes aggregate transfer volume unavailable, even when the conflicted row itself has zero transfer effect; preserve ordinary financial/category/count effects and report the integrity cause separately from missing FX.
79. **Forecasts consume canonical effects, never raw currency fallbacks:** planning/status overlays must reuse the same row-level `income_effect`/`expense_effect` and effective category as summaries. `COALESCE(amount_chf, amount_original)` is invalid when the original currency may be foreign. Pass already-loaded annual effect rows into forecast composition, propagate partial quality through status/detail UI, and display unconverted rows with their original currency label.
80. **Additive quality contracts are versioned:** do not silently replace a public top-level list with an envelope merely to carry `data_status`. Preserve the legacy response shape and add an explicitly versioned envelope endpoint (or negotiate a version), then regression-test both contracts. Internal services may use the richer object immediately, but external compatibility remains a release gate.
81. **Row-level corrections are immutable capabilities, not classification actions:** bind one opaque item/transaction token, dataset version, row version/baseline, exact action, and selected target into Preview. Confirm recomputes under the write lock, stale state conflicts, replay is idempotent, and every reachable UI entry point uses the same safe drawer/contract rather than a legacy direct update. Duplicate exclusion requires a concrete retained row plus deterministic merchant/source evidence; amount/date compatibility alone is not enough.
82. **Additive SQLite migration digests freeze the old projection:** record the pre-migration table set and columns, then compare those exact old columns after migration. Verify new defaults and new empty relation tables separately, exclude only predefined bookkeeping/compatibility scratch tables, apply twice, and run production-shaped no-write Previews on the restored copy with both connection-change and file-digest proof.
83. **Own-account corrections materialize balanced transfer units:** an item-specific transfer Preview binds one eligible opaque counterpart account token and signed direction. Confirm creates exactly two opposite-signed legs plus one durable relation in a single transaction, links the imported candidate to only its observed leg, uses a schema-permitted system source for the synthetic leg, leaves income/expense/net unchanged, and revalidates account activity, distinctness, currency, baseline, and replay identity under the write lock.
84. **Daily valuation activation reuses the canonical engine and scheduler:** extend the evidenced activity/price/FX/snapshot path; do not add a second performance engine or parallel timer. The checked-in job remains fail-closed, and its activation check occurs before database open, migrations, or provider calls.
85. **Partial backfill is attempted, not confirmed:** a failed or incomplete run retains its bound Preview identity and remaining-day count and can resume; only zero remaining days mint the final replay-idempotent confirmation audit.
86. **Valuation snapshots are calendar-day append-only:** identical retries are no-ops; changed results insert a new version with supersession lineage. Never use SQLite `INSERT OR REPLACE` for immutable valuation history, and preserve separate rows when one instrument exists in multiple accounts.
87. **Only confirmed activities can become confirmed valuations:** pending crypto or broker rows do not satisfy activation evidence, alter reconstruction fingerprints, or enter materialized totals.
88. **Run freshness has two clocks:** latest run status/time and latest successful valuation date are independent; a newer partial run must not erase the last confirmed date, and incomplete aggregate coverage must not leak lower-level numeric metrics or graphs into the main UI.
89. **Resumption permits bounded progress, not arbitrary fingerprint drift:** after an attempted backfill, bind the confirmation ID to source, date range, Preview ID, and original fingerprint, then classify drift. Allow only explicitly modeled prerequisite additions or rows written by that same attempt; changes to confirmed holdings, confirmed transactions, scope, or cashflow coverage invalidate the capability. Counterprobe by mutating a verified start holding after a partial attempt, adding the missing price, and requiring the original confirmation to fail stale.
90. **Multi-account valuation identity survives every reader:** writer row counts are insufficient. Any latest-version window for instrument snapshots partitions by account as well as scope kind, scope ID, and calendar day; otherwise the globally highest version silently drops the same instrument held in another account. Probe the production reader and downstream cost basis/unrealized-P&L/price-FX attribution with two accounts sharing one instrument.
91. **One transfer may have two scope-correct meanings:** bank-to-managed-portfolio funding is neutral in consolidated wealth but an external deposit in the managed-portfolio performance scope. Amount/cadence never establish recipient identity, and a name substring or superset is review-only without independent stable identity.
92. **Historical cashflow confirmation grants no broader capability:** confirming detected rows does not prove complete period coverage and does not activate future recipient automation. Coverage and rule activation remain separate fingerprint-bound Preview/Confirm/Audit gates.
93. **Modelled managed-portfolio cash is evidenced or derived, never invented:** explicit cash rows win; when none exist, residual cash is source total minus confirmed positions. Negative residuals, missing exact-day price/FX, and missing canonical amounts block the new day rather than becoming zero or stale-day output.
94. **Confirmed/modelled labels are reader invariants:** latest confirmed values filter confirmed points, chart data matches the displayed period, official anchors outrank same-day model points for confirmed endpoints, and model-dependent TTWROR is visibly provisional.
95. **Post-fix evidence replaces pre-fix evidence:** every review finding gets a counterexample regression and minimal fix, followed by full-suite, static, responsive-UAT, and independent frozen-tree reruns. Pre-fix green counts cannot authorize commit, merge, or deployment.
96. **Public financial contracts are recursively closed:** top-level `extra=forbid` is insufficient when nested fields are open dictionaries. Public serializers expose only rendered allowlisted fields; private reconstruction retains the evidence needed for Confirm. Idempotent replay must validate against the same closed response model, and official/modelled source labels never share a fallback value.
97. **Managed-portfolio release probes attack silent success paths:** unexpected deterministic-ID collisions raise and roll back instead of being ignored; stable recipient identity with unresolved account/CHF/FX quality blocks the whole batch; collision decisions cannot promote weak identity; canonical activation arithmetic includes withdrawals; model/source labels and redacted audits are consistent; Confirm responses are generation-bound; and any activated blocked/partial source makes the job exit nonzero.
98. **Persisted collision decisions remain fail-closed lineage:** an accepted `link_existing` mapping is fingerprinted and revalidated on every future Preview, even after the economic collision disappears. A missing, voided, non-unique, or economically changed linked cashflow makes the source row uncertain; it never reappears as a new writable cashflow.
99. **Daily canonicalization applies to official and modelled rows:** choose one deterministic latest official anchor and one latest model version per calendar day, then let the official anchor suppress the same-day model point. Deduplicating only model versions still permits duplicate confirmed endpoints.
100. **No-mutation sentinel equality has an explicit comparison domain:** compare database bytes/metadata, schema, row counts, integrity/FKs, activation state, timers, and gates exactly. Exclude only predeclared deployment metadata such as the intentionally changed Git SHA, and report that identity transition separately.
101. **Run-bound provenance is whole-set evidence:** select every row under the completed run’s exact binding key before validating provider/currency. Require declared count, actual count, and non-null/non-empty counts for every mandatory provenance field to agree; SQL `MIN`/`MAX` alone are unsafe because they ignore `NULL`. Provider predicates must not hide conflicting rows or turn consistency checks into tautologies, and direct-base-currency/no-FX claims are emitted only after the entire set passes.
102. **A day-end opening anchor excludes same-day flows from the following interval:** when source evidence states that the opening value already contains that calendar day's activity, return calculations use `opening_date < flow_date <= closing_date` for both effective and raw activity sets. Unknown intraday timing remains blocked rather than assumed.
103. **Direct service success does not prove the strict HTTP Preview contract:** validate the complete activation Preview payload against its declared response model and exercise the deployed endpoint before Confirm; an omitted public provenance field can otherwise turn a read-only Preview into HTTP 500.
104. **Replay idempotency is a database-delta claim:** APIs may repeat the original stored write count on idempotent replay. Require zero additional rows, stable source references, and unchanged confirmation/audit identity instead of interpreting a replay response field as the delta.
105. **A reconciled brokerage control total is not performance readiness:** separately prove depot and settlement-cash canonical projections, signed scope-boundary activity semantics, documentary post-anchor cashflow coverage, and source-specific job eligibility. A correct summary or source snapshot cannot waive hidden cash double-counting, unsigned transfer direction, or missing period evidence.
106. **SQLite rebuilds preserve unmanaged schema and replace managed contracts:** snapshot and recreate every non-obsolete explicit index/trigger attached to a rebuilt table, exclude automatic obsolete uniqueness only by exact indexed columns, and transactionally drop/recreate migration-owned indexes/triggers. Schema assertions compare normalized definitions or versioned hashes, never names alone.
107. **Append-only corrections require one latest-effective resolver:** plan consumption, action eligibility, reports, and summaries follow the linear correction chain. A plan corrected to administered/missed is consumed; a linked consumption corrected back to planned reopens it. Apply effective-state filtering before visible limits, and retain only explicit legacy-status compatibility mappings.

See `references/item-specific-corrections-and-additive-sqlite-migrations.md` for the correction contract, duplicate, own-account-transfer, and card-settlement safeguards, capability-safe UI boundary, frozen-column migration digest, and focused regression probes.

See `references/legacy-duplicate-semantic-identity-migrations.md` when a new idempotency identity must coexist with valid legacy duplicates. It covers nullable partial uniqueness, deterministic legacy replay fallback, savepoint-scoped SQLite DDL rollback counterprobes, replay audit deltas, privacy-safe evidence, and rerunning the blocker suite if `HEAD` advances during review.

See `references/review-snapshot-dependency-closure-and-capability-ui.md` for dependency-inventory probes, SQL-union precedence, server-scoped tab pagination, stale-cursor client recovery, capability-safe category controls, and nested-component request-mocking pitfalls.

See `references/logical-financial-aggregation-units.md` for logical-event volume aggregation, linked versus unmatched transfer handling, filtered-scope semantics, missing-FX unit availability, writer-to-reader regressions, and UI sign normalization.

See `references/refund-bounds-lifecycle-and-partial-volume.md` for cumulative refund-cap, locked writer revalidation, missing-FX transfer-volume, authoritative lifecycle, and review-cursor mutation probes.

See `references/snapshot-bound-read-models-and-race-safe-clients.md` for cursor-mutation conflicts, global-facet probes, deferred-response race tests, and one-scan financial aggregation gates.

See `references/private-preview-redaction-and-bounded-review.md` for the internal/public preview split, honest actionable-cluster accounting, excluded-member UX, inspectable proposal pagination, structured transfer precedence, and stale file-reader regression probes.

See `references/hmac-approval-boundary-closure-review.md` for dedicated-key HMAC tracing, complete-cluster attack probes, stale/expiry and idempotent-replay semantics, deterministic no-write evidence, audit validation, and severity-only closure reporting.

## Workflow

### 1. Establish release boundaries

- Record the source commit, schema version, production integrity result, source revision, and business date.
- Separate code work, migration rehearsal, deployment, data confirmation, market/provider jobs, and policy activation into distinct gates.
- Keep production unchanged until code review and remote CI pass.

### 2. Implement preview and atomic confirmation

- Preview must be side-effect free and expose all mappings, reconciliation differences, conflicts, and reason codes without raw source rows, filenames, full account references, or persisted-digest derivatives.
- Bind the exact selected stable mapping into the input fingerprint; name/hint matching is display-only and ambiguity is fail-closed.
- Include every relation that can change classification or writes in the baseline: mappings/accounts, legacy and new candidates, productive ledger rows, transfer rows/pairs, receipt links, batches, and applicable rules.
- The outer confirmation starts one transaction and calls every nested writer with `commit=False`.
- Recheck the baseline after obtaining the write lock to close mapping/ledger TOCTOU gaps.
- When a caller already owns a transaction, use a savepoint and roll back/release only that savepoint.
- An idempotent early return still validates the resent input identity and baseline.
- On any later-item failure, roll back the entire set.
- Verify rollback experimentally through the deepest helper; source inspection alone is insufficient.

### 3. Enforce canonical identity consistently

- Normalize identity inputs at the API boundary.
- Before adding a unique index, detect legacy duplicates with the same normalization expression.
- Use expression indexes such as `UPPER(TRIM(isin))` or an equivalent `NOCASE` policy.
- Make conflict lookups and migration tests use the same semantics.

### 4. Resolve provider data without invention

- Provider adapters return only fields actually supplied by the provider.
- If a historical row omits currency or venue, return it as missing.
- The orchestrator may resolve that field from an already confirmed canonical mapping.
- If neither source establishes it, return a concrete fail-closed reason.
- Reject actual provider dates after the requested date and enforce any fallback window explicitly.

### 5. Consume and reverify the final review

- Read the complete review artifact even when the chat summary is truncated.
- Treat every High/Medium finding as a release gate.
- When findings are delayed until after merge, pin the exact full merge SHA and compare both merge parents before classifying them; do not review only the old feature-branch tip.
- Separate implementation status from regression-coverage status: a finding may be already fixed at the merge SHA while one independent branch remains untested.
- Add a focused regression probe for each finding, apply the minimal fix when still needed, then rerun the full bounded suite plus lint and diff checks.
- For global portfolio/overview semantics, probe at least two active accounts and verify latest-per-account selection followed by aggregation; a global `ORDER BY ... LIMIT 1` is a specific counterexample target.
- If the process specifies one final review, distinguish a completed independent verdict from direct fix verification: fix and regression-test that verdict's findings without starting an endless review loop. If the release contract explicitly requires an independent verdict on the post-fix frozen SHA, keep the review/release-report task open until that exact verdict arrives; never mark it complete merely because local regressions pass.

### 6. Diagnose and repair CI

- Before opening the PR, read every workflow job and execute custom migration/control scripts locally in addition to the default `make verify` or test target.
- When schema N→N+1 changes, search all CI gates, fixture scripts, smoke checks, job labels, and expected-schema constants; update and run each executable contract, not only unit-test assertions.
- Inspect workflow run → failed job → failed step → exact log line before changing code.
- Respect repository safety policies for documentation formats and locations.
- After a PR is open, prefer an additive follow-up commit over rewriting published history.
- Re-run complete remote CI and confirm every required job succeeded before merge.

### 7. Deploy with independent evidence

- Disable schedulers before migration or confirmation.
- Capture a business digest and verified backup.
- Rehearse migration on an independent backup copy and run database integrity checks.
- Use the project's connection helper for migration rehearsals when migrations rely on named rows, pragmas, or connection configuration; a raw default `sqlite3.connect` can make the harness fail even when the migration is safe.
- Apply the migration twice on independent copies and compare pre-existing columns/material digests under exclusions defined before observing the result.
- Deploy the exact merged commit, verify schema and services, then execute mapping/baseline confirmation only if all conflict and reconciliation gates pass.
- If a destructive deployment command is blocked by an interactive safety guard, do not bypass or reformulate it. Restore any services stopped for the attempted cutover, verify the previous release is serving, and wait for fresh explicit consent before retrying the destructive step.
- Run scheduled/provider jobs twice when idempotency is required and compare digests.
- Re-enable schedulers only after data, GET-side-effect, and UAT checks pass.

### 8. Treat cross-account supersession as an exceptional decision

- Bind the exact transaction, source account, target account, canonical identity, quantity, closed reason code, owner timestamp, and owner note into the preview/confirm hash.
- Query all active history for that instrument before classifying a candidate. Never prefilter to placeholder-looking rows, because doing so can hide evidenced transactions or additional history.
- Require exactly one total active cross-account transaction and then reapply the full unlineaged-placeholder gate, including `source_id`, external ID, row hash, price, gross amount, type, and quantity.
- Preserve the original account ID and source row; void/supersede it atomically and audit the owner decision. Do not create fake trades or balancing entries.
- Replay the identical confirmation immediately and require the same batch/audit identity with no extra records.

### 9. Audit historical cache reuse as an input, not a write

- Fall back only to an exact-cutoff, positive, error-free `fresh` row matching canonical instrument, provider, symbol, and currency.
- Preserve its stable row ID and original run provenance; never repoint it to the current run.
- Persist the source-row reference in the analysis/audit payload. A generic cache reason code alone is insufficient.
- Version the analysis source key whenever cache eligibility, lineage, quality, or metric semantics change.
- Report accepted inputs, coverage, and newly written rows as separate metrics. A cache hit may raise coverage while writing zero rows.
- After deployment, run once and replay once, compare protected digests and exact market/FX payloads, then restore the scheduler and record its next local-time run.

## Required probes

- Deep-helper write with `commit=False` keeps `conn.in_transaction` true; rollback leaves zero related rows.
- Multi-item batch where a later item fails leaves no earlier mappings, instruments, alerts, or audit rows.
- Case-variant canonical identities cannot coexist after migration.
- Historical exact-date, allowed prior-date, future-date rejection, no-history, and idempotent rerun tests.
- Non-default-currency historical row without a currency field persists only with the confirmed mapping currency.
- Production-copy preview demonstrates conflicts without writing.
- Provider parser preserves two near-identical same-day/same-amount rows when running balances or source evidence differ, and reconstructs unquoted description delimiters from a validated header without inferring columns from row length.
- When parser output contradicts a fixed acceptance count, aggregate balance-transition evidence is reported and release remains stopped until the count is explicitly resolved.
- Remote CI job conclusions are checked directly, not inferred from local tests.
- Cache fallback rejects stale/error/future rows and records the exact reused row ID and original run ID.
- Accepted-input, coverage, and rows-written metrics diverge correctly; one instrument held in two accounts writes one price row but produces two valuations.
- Included account plus two valuations plus no activities remains return-unavailable until audited cashflow coverage spans the full requested period.
- Direct account-flag drift fails, while an audited reclassification synchronizes classification and projection atomically.
- Daily TTWROR matches timestamped cashflows by the documented calendar valuation day and rejects ambiguous duplicate daily valuations.
- Sparse cashflow and valuation chart series use one shared temporal x-domain rather than independent index scaling.

## Pitfalls

- A case-sensitive database index does not enforce a case-insensitive application identity.
- `commit=False` on the top helper is meaningless if a nested quality or audit helper commits.
- Defaulting omitted historical currency to USD fabricates data and can both accept bad USD rows and reject valid non-USD mappings.
- Aggregate PR state such as `unstable` is not enough to diagnose CI; inspect job steps and logs.
- Repository scanners may prohibit JSON/CSV documentation outside examples, synthetic data, tests, or fixtures; use an allowed format/location rather than bypassing the scanner.
- Hiding tests only to preserve a fixed count is invalid. If a repository intentionally freezes collection counts, execute the contract from a collected integration gate and prove that execution.
- Treating `price_stored` as coverage overclaims persistence when cached or already-identical rows were reused; label writes honestly and carry coverage separately.
- A cache reason code without the exact source-row reference is not sufficient audit lineage.

See `references/review-and-ci-probes.md` for concise reproduction recipes and release evidence.

See `references/owner-confirmed-supersession-and-live-release.md` for the exact exceptional-resolution contract, all-history candidate gate, audit/replay rules, copy-first rehearsal, and rounding reconciliation evidence.

See `references/market-cache-provenance-and-release-closeout.md` for exact-date cache eligibility, immutable lineage, metric semantics, replay probes, and scheduler closeout.

See `references/snapshot-baseline-to-ledger-history.md` for the two-mode contract that replaces a synthetic position baseline with complete historical activity—or appends strictly after it—without duplicating accounts, instruments, snapshots, holdings, or FIFO lots.

See `references/exact-merge-sha-delayed-finding-reverification.md` for exact-tree post-merge review classification, independent collision/immutability/safe-summary probes, and multi-account aggregation counterexamples.

See `references/role-provenance-aggregate-read-models.md` for classifying control totals versus additive holdings, shared temporal resolvers, snapshot-baseline plus later-trade replay, multi-currency component identity, and the corresponding release probes.

See `references/investment-performance-scope-and-coverage.md` for explicit performance roles, audited cashflow-history coverage, drift-proof projected flags, calendar-day TTWROR, canonical coverage/status derivation, shared chart time axes, and copy-first migration evidence.

See `references/version-bound-backend-schema-contract-inventory.md` for read-only, exact-commit schema/service/API inventories; migration-history probes; account-writer discovery; Preview/Confirm/Audit/Idempotency boundary classification; and Green/Yellow/Red sprint reuse reporting.

See `references/household-import-dedup-reconciliation-and-privacy.md` for keyed fingerprint privacy, ordinal preview tokens, layered and legacy deduplication, pending/final and reversal handling, candidate-versus-ledger semantics, transfer neutrality, cross-batch receipt enrichment, TOCTOU-safe confirmation, and copy-first/read-only release probes.

See `references/financial-source-onboarding-and-zero-row-guards.md` for explicit-profile header proof, zero-logical-row fail-closed behavior, balance-chain source-truth corrections, private manifest boundaries, complete account-role drift checks, owner-only SQLite backups from byte zero, idempotent onboarding, and exact-scope confirmation UI.

See `references/classification-coverage-and-business-readiness.md` for private root-cause reports, canonical category resolution, evidence-ordered classification, HMAC-bound row decisions, finite merchant-cluster decisions, bidirectionally unique amount-agnostic receipt matching, and honest technical-versus-business readiness gates.

See `references/audited-legacy-orphan-fk-neutralization.md` for copy-first preconditions, frozen expected identities, identity-bound nullable-FK repair, same-transaction audit events, table-digest invariants, no-op replay, and post-repair backup/restore proof.

See `references/production-preview-uat-and-long-running-requests.md` for owner-only real-file payload binding, fallback source-reference semantics, read-only digest proof, long-running HTTP cancellation discipline, exact-commit old/new comparisons, masked review accounting, responsive deployed-route UAT, and scheduler closeout.

See `references/high-volume-preview-performance-and-batched-decisions.md` for request-local N+1 elimination, phase timings outside fingerprints, performance-only parity harnesses, volatile multi-decision drafts, stale-input invalidation, and versioned fail-closed calibration.

See `references/python-sqlite-exact-sha-release.md` for Python runtime import-provenance checks after installation, exact-merge deployment, SQLite backup/restore evidence, task-scoped responsive UAT metrics, candidate/deployed read-only preview parity, final-review completion discipline, and aggregate CI-state fallback when check-run API scope is unavailable.

See `references/atomic-sqlite-candidate-confirm-and-promotion.md` when existing Confirm helpers commit internally: run full Confirm plus replay on a private same-filesystem clone, close production drift with a frozen logical digest, reject WAL/SHM sidecars, atomically promote the verified candidate with a pre-Confirm rollback rename, and prove responsive GET-only UAT under a no-write sentinel before re-enabling schedulers.