# Portfolio ingestion source audit and projection matrix

Use this before implementing a portfolio ingestion/reconciliation sprint in a mature finance repository. The goal is to inventory existing writers and semantics without touching productive data or accidentally treating mutable projections as canonical history.

## Audit sequence

1. **Freeze the review baseline.** Record commit/branch and initial worktree status. If files change during the audit, classify the late diff as concurrent and separate baseline findings from current-tree additions; never claim the audit created it.
2. **Inventory every source and writer.** Trace CSV/broker parsers, manual APIs, ledger writers, crypto writers, price/FX persistence, snapshot writers, correction/void paths, and account creation. Inspect schema, migrations, services, and tests—not table names alone.
3. **Build a source matrix.** For each source, report: exact files/tables, supported records, trust/readiness gates, current lineage, idempotency key, mutability, and safest canonical projection.
4. **Separate four data roles:**
   - canonical activities/transactions;
   - immutable valuation inputs;
   - mutable derived projections such as current positions;
   - import/review/batch metadata.
5. **Trace vocabularies end to end.** Compare importer transaction types with ledger calculators and performance activity maps. Flag aliases accepted in one subsystem but ignored in another (`sell` versus `partial_sell`, snapshots/corrections marked unsupported, etc.).
6. **Audit account semantics.** Enumerate values actually produced and consumed by account import, manual creation, UI classification, reconciliation, and performance inclusion. A free-form column is not a controlled account-type contract.
7. **Audit idempotency at three levels:**
   - request/confirmation replay;
   - file/batch replay;
   - logical source-record replay.
   A UUID session, an audit row, or an intra-file duplicate check does not provide cross-run idempotency.
8. **Audit lineage to the target.** Prove whether a target row can be traced to batch, source record, source revision, file hash, external reference, and canonical input fingerprint. Import-session totals without a target FK are operational logs, not row lineage.
9. **Audit price/FX immutability.** Upserts can rewrite history. Canonical valuations should freeze exact price/FX values, direction, provider/source, timestamps, quality, and source reference.
10. **Verify unchanged scope.** Recheck worktree status and diff whitespace. In a strict read-only audit, avoid test/build commands that create caches unless explicitly permitted.

## Projection rules

- Prefer an existing canonical transaction ledger for recognized activities; enrich explicit activity/date/source-reference fields rather than creating a second ledger.
- Project confirmed account totals and balance snapshots to immutable account valuations.
- Project derived account/instrument positions only when the exact local price and FX inputs can be frozen with quality reasons.
- Never use a mutable `INSERT OR REPLACE` position table as canonical history.
- Do not auto-project blocked, aggregate-only, name-only, missing-identifier, or missing-numeric broker rows.
- Missing foreign FX yields `unavailable`/`partial`; never silently substitute 1 or 0.
- Initial holdings/snapshots are not ordinary buys unless the policy explicitly defines that mapping; preserve snapshot-only and uncertain-cost-basis quality.
- Crypto wallet holdings require an explicit wallet-to-canonical-account bridge before account-scoped performance projection.

## Common idempotency traps

- Hashing a filename string instead of file bytes.
- Global external IDs that are not namespaced by source/provider/account.
- Deterministic IDs based on account/date/currency that omit amount and source revision.
- Row hashes checked only within one input file but never persisted on the created target.
- Time-based IDs or timestamps in logical identity, making retries create duplicates.
- Upserting holdings, prices, FX, or snapshots in place and calling them append-only.

## Final read-only Sprint review probes

When reviewing the completed implementation rather than only the source inventory:

1. **Include untracked files explicitly.** `git diff <base>` omits them; enumerate and read every untracked sprint file before calling the review complete.
2. **Use disposable in-memory counterexamples.** They preserve read-only repository/runtime scope while exercising migrations and finance logic. Recheck Git status afterward and state that the tree remained unchanged.
3. **Probe multiple source rows for one logical valuation key.** Two distinct normalized snapshots for the same `(account, valuation_date)` may both receive version `1` or `N+1` when preview calculates versions only from persisted canonical rows. Confirm must produce a deterministic version chain/winner/blocking decision instead of colliding with the canonical unique key and rolling back the whole batch.
4. **Treat preview time as a security boundary.** A client-returned `preview_created_at` is forgeable unless persisted server-side or included in a server-authenticated token. Verify that changing only the timestamp cannot revive an expired preview, reject future timestamps, and look up an already-confirmed idempotency key before expiry checks so legitimate retries remain idempotent after TTL. Compare every advertised identity field, including the supplied input fingerprint.
5. **Probe stale-but-mutually-consistent reconciliation.** Request an `as_of` later than all inputs while position, price, FX, cash, and total share one old date. `reason_codes=['stale_snapshot']` is insufficient if quantity, valuation, account, and overall status still become `matched`; cutoff/freshness must affect comparability and coverage.
6. **Keep active-ledger predicates aligned.** Preview, lineage, quantity reconstruction, cost-basis coverage, cashflow, and transfer-group lookup should consistently exclude voided and explicitly unconfirmed transactions. Test an unconfirmed buy and a voided transfer counterpart.
7. **Verify currency and quality for total values.** Never compare canonical `value_original` directly with a CHF legacy total unless currency/base-currency/FX prove that it is CHF. Partial or unavailable canonical valuations must not participate as complete values.
8. **Check pagination honesty.** If frontend clients hardcode `limit=N, offset=0`, the UI must expose total/truncation or navigation; a complete-history/reconciliation label must not silently omit later rows.

Classify only concrete sprint-introduced correctness, security, data-integrity, and contract violations. Ignore disclosed project-wide lint debt outside the diff, and avoid generic style suggestions in the finding list.

## Reporting shape

Return:

1. concise outcome;
2. source matrix;
3. exact gaps/risks with file and line anchors;
4. ordered safest projection plan;
5. explicit statement of files created/modified and whether late concurrent changes appeared.

Do not overclaim test coverage in a source-only audit. Distinguish inspected contracts, synthetic tests found in the repository, and commands actually executed.