# Investment performance scope, returns, attribution, and SQLite migration gates

Use this reference when adding or repairing portfolio TTWROR/XIRR, performance attribution, account-scope metadata, or a single canonical performance UI.

## 1. Freeze an explicit performance scope

Treat `performance_included` as a financial classification, not a convenience flag.

- Include accounts only through explicit role/configuration evidence.
- Keep settlement cash inside the investment scope when it is operationally part of the depot.
- Keep ordinary bank, credit-card, and household cash accounts in net-worth/cash views while excluding them from investment returns.
- Preserve empty and duplicate historical accounts; exclude them from performance without deleting balances, transactions, snapshots, or provenance.
- Canonical total-value platforms must have exactly one included account. Do not aggregate duplicate manual and official totals.
- Non-account domains such as crypto must be named as expected scope components even before historical valuation coverage exists. A global return must not silently omit them.

Before mutation, create an owner-only preview listing platform/account label, role evidence, old flag, new flag, and reason. Keep IDs and detailed financial data out of tracked evidence.

## 2. SQLite default and trigger trap

Changing a fresh-schema default from `1` to `0` does not change the default of an already-created production column. Conversely, an `AFTER INSERT` trigger that rewrites every `performance_included=1` row cannot distinguish an omitted legacy default from an explicit `1`; it silently defeats legitimate role-based inserts and synthetic fixtures.

Choose one verified strategy:

1. Safely rebuild the production table so its column default is really `0`, preserving foreign keys, indexes, triggers, rows, and dependent contracts; or
2. Keep the existing production column intact but make every application account writer explicitly insert `performance_included=0`, then apply an explicit audited classification service for approved investment roles.

Do not use a blanket rewrite trigger as a shortcut. Guarding only the transition to `performance_included=1` is also insufficient: a direct transition back to `0` can leave an included classification beside an excluded account flag, while a direct classification update can leave the opposite mismatch. Treat one representation as authoritative and make the other a trigger-maintained projection, or require every flag transition in both directions to match the current audited classification.

Add a deterministic drift probe: classify an account as included, attach two valuations so coverage invokes the canonical calculator, directly flip only the account flag, and call the coverage endpoint. The database must reject/synchronize the drift; the endpoint must never turn it into an uncaught `ValueError`/500. Repeat for a classification-only change.

The classification operation should:

- validate the account and allowlisted role;
- compare the current `(included, role, decision_version)` and return a no-op for an equal repeat;
- write a deterministic or otherwise idempotent audit record;
- upsert the classification;
- update only the performance flag;
- leave all financial rows untouched.

Wire role-producing import confirmations to this service. For example, a confirmed PostFinance depot/cash mapping can include those accounts, E-Finance control remains excluded, and a confirmed official TrueWealth total identifies the canonical account. Generic bank/household importers must explicitly create accounts excluded.

## 3. Copy-first migration verification

Before production deployment:

1. Open the source database read-only and copy it with SQLite backup semantics.
2. Hash a privacy-safe cash-summary projection and protected source/ledger/snapshot tables.
3. Apply the migration to the copy.
4. Verify schema version, exact included/excluded labels and roles, audit count, `PRAGMA integrity_check`, and foreign keys.
5. Reapply and compare the scope/audit/account-flag state itself. Do not use total SQLite change count as the idempotency oracle because unrelated compatibility migrations may perform benign repeated writes.
6. Recompute the cash summary and protected-table digests; they must be byte-equivalent.
7. Keep the detailed evidence owner-only (`0600`), with only aggregate contracts in Git.

## 4. Coverage before returns

A platform becomes performance-capable only after a supported opening valuation and a traceable continuation exist. Inventory:

`Platform | reliable start | valuations | external cashflows | position history | TTWROR | XIRR | attribution | gap`

Rules:

- Never infer the start from an account name or the oldest arbitrary record.
- Never show missing performance as `0`, `0.00%`, or CHF `0`.
- Use explicit states such as unavailable, partial, or available from a date, with reason codes.
- If a required global component lacks history, global performance is unavailable; do not calculate over the remaining components and label it global.
- Undifferentiated `external_cashflow` rows must fail closed until direction and scope classification are known.
- **No cashflow rows is not evidence of zero cashflows.** Require an explicit, source- and period-bounded positive assertion that external cashflow history is complete. Without it, TTWROR and XIRR are unavailable with a stable reason such as `external_cashflow_history_missing`; do not infer completeness from two terminal valuations.
- Distinguish a positively covered period that happens to contain zero flows from a source such as a total-value import that provides no cashflow history.
- A total-value series can support value change, but it supports TTWROR/XIRR only when the cashflow-completeness gate is satisfied and does not automatically support price/FX attribution.

## 5. TTWROR contract

Version the formula and timing convention. A robust daily/subperiod convention is:

`r = (closing_value + withdrawals_at_period_end) / (opening_value + deposits_at_period_start) - 1`

Link subperiods geometrically. Require a valuation boundary on every external cashflow date. Reject:

- unsupported cashflow kinds;
- inconsistent signs;
- missing cashflow-day valuations;
- zero/negative denominator;
- deposits at the final boundary or withdrawals at the initial boundary that cannot belong to a subperiod.

Scope-internal transfers are never TTWROR cashflows. Buys, sells, dividends, interest, fees, and taxes are investment activity, not external funding.

## 6. XIRR contract

Label XIRR as **annualized personal return**. Use actual dates and a deterministic solver with bounded rate domain, bracket discovery, and stable tolerance. Opening value is a negative terminal cashflow, closing value positive, deposits negative from the investor perspective, and withdrawals positive.

Return unavailable for missing terminal values, missing usable cashflow history, no sign change, or ambiguous/multiple-root patterns. Cashflow completeness must be positively evidenced; an empty activity query must not silently reduce XIRR to opening/closing values and report `complete`. Validate against a published spreadsheet reference plus irregular-date and edge-case fixtures, including two complete valuations from a source with no cashflow-history capability.

## 7. CHF attribution bridge

Reconcile:

`investment_result = closing - opening - net_external_cashflows`

Bridge components are signed CHF amounts:

- market-price effect;
- FX effect;
- dividends and interest;
- fees (negative);
- taxes (negative);
- other documented effects;
- explicit residual.

If position quantities/activity are not sufficient, keep market and FX `null` and expose the unexplained residual. Never invent a split. When local values and FX at both endpoints are valid and holdings are unchanged, a deterministic split can allocate:

- price effect: `(local_close - local_open) * fx_open`;
- FX effect including interaction: `local_close * (fx_close - fx_open)`.

A nontrivial residual makes attribution partial and must remain visible.

## 8. One canonical UI source

Do not leave a legacy proxy-performance/benchmark panel beside the canonical performance engine. Remove or convert it to non-performance risk content. Use the canonical response for the main curve, TTWROR, XIRR, attribution, coverage, and benchmark comparison.

If the old analytics projection calls the new global engine, ensure it does not collapse a valid historical proxy series to one point merely because the new global scope is unavailable. Prefer deleting the duplicate proxy performance rather than adapting two competing sources. Test route-addressable Overview, Performance, Data/Imports, and Strategy views independently at desktop, tablet, and mobile sizes.

## 9. Independent review and adversarial contract probes

For an independent review against `origin/main`, inventory the exact baseline, tracked diff, and untracked files before reading code. Recheck `git status`, diff stat, and every cited line immediately before reporting because an implementation worktree may change concurrently. Do not modify source. If a test runner creates an ignored environment, lockfile, report, or cache that did not exist initially, remove it and verify the final status matches the initial inventory.

Trace the contract end to end: UI selector → frontend request → HTTP schema → service scope selection → valuation/cashflow loading → pure formula → migration/trigger/audit invariant → tests. Matching labels are not evidence that a scope selector reached the backend.

Use deterministic adversarial probes in addition to green unit tests:

- Select a provider-specific scope and assert both the request scope and the absence of stale global TTWROR/XIRR, investment-result, chart, and attribution values. A status-only assertion can miss cross-scope financial disclosure.
- Pass an intraday cashflow timestamp against a date-only valuation on the same calendar day. A `daily` TTWROR contract should normalize to an explicit calendar-day boundary convention unless storage guarantees identical instants.
- Place an unsupported activity before the requested period and a clean activity inside it. Historical reason aggregation must not poison the requested period unless it affects opening state or reverses a period event.
- Attempt direct SQL insertion of an unapproved role, wrong decision version, and unrelated audit row before setting `performance_included=1`. A Python allowlist is not a database invariant. DB constraints/triggers must bind approved role, version, account/audit identity, and target value; every transition must retain immutable audit history.
- Populate formerly missing scope history and verify the coverage endpoint can transition from unavailable to complete. Hardcoded coverage rows drift from the canonical engine.
- Verify chart X coordinates use one shared date scale. Independently indexing valuation and cashflow arrays visually misplaces events.

Review changed tests for semantic weakening. Selector tests must check request parameters and stale-value absence; missing-value tests must assert canonical fields are `None`; chart tests must verify date alignment, not merely SVG existence. A `KeyError` caused by stale contract field names is a release-blocking test failure and can mask the intended fail-closed assertion.

Report findings by severity with current file/line range, trigger, financial impact, smallest safe fix, and probe evidence. Cross-scope value display and bypassable audited classification are normally release blockers; conservative unavailability from overly broad reason propagation is usually lower severity unless it blocks the sprint acceptance criterion.

## 10. Test and release discipline

- Add synthetic golden tests for deposits, withdrawals, linked subperiods, irregular XIRR, internal transfers, fees/taxes/income, FX/price effects, residuals, missing data, determinism, and read-only GET behavior.
- When the schema version changes, update exact-version assertions deliberately.
- A new default can invalidate many fixtures that relied on implicit inclusion. Update shared fixture constructors to perform explicit audited classification; do not weaken production guards or blindly change expectations.
- Run the focused migration/performance tests first, then the complete backend suite, frontend suite/typecheck/build, lint, compile, diff checks, migration-copy tests, review, and responsive UAT.
- If the full suite exposes an old proxy-performance conflict, fix source ownership instead of weakening a test or claiming the suite is green.