# Household cockpit backend/data architecture

Use this reference when an existing personal-finance codebase needs an implementation-ready cockpit plan grounded in current services, routes, schemas, and tests rather than a greenfield design.

## Exact-code audit sequence

1. Bind the review to the exact commit and verify the tree stays unchanged.
2. Trace the canonical transaction-effect stream first: confirmed status, CHF conversion, transfers/card settlements, linked refunds, category attribution, and quality flags.
3. Trace budget storage and the currently designated forecast formula separately; identify implicit machine-date or December defaults.
4. Trace category ancestry, account filtering, merchant identity, pagination/data-version inputs, and existing response types.
5. Inventory competing overview/analytics endpoints. Mark raw or legacy calculations as non-canonical instead of composing them into the cockpit.
6. Inspect chart dependencies and existing UI primitives before proposing new packages.

## Canonical read-model shape

Prefer one thin cockpit composer over a second financial engine. It should consume the canonical effect stream and extracted public budget helpers, then return:

- contract/semantics versions, `as_of`, data version, and server-resolved filters;
- reconciled income, expense, net, savings rate, and neutral-transfer volume;
- complete time buckets and complete category totals;
- one named budget/forecast model with an explicit cutoff;
- clearly labeled non-reconciling latest/largest/top-merchant subsets;
- explicit reconciliation deltas.

Use a separate category-detail read model only when needed for monthly actual/budget points and paginated transactions. The detail, chart, and list must receive the same exact category-descendant and account/date scope.

## Filter and scope contract

- Resolve preset/custom periods and comparisons on the server; return the resolved date bounds and bucket granularity. If a legacy frontend must resolve presets, persist the exact resolved bounds in the URL, have the server validate and echo them, and freeze the browser clock in tests—never send only a label such as `3m` and reconstruct it independently in multiple views.
- Use exact account/category IDs internally. When product policy forbids exposing IDs or keys in URLs/UI, issue stable non-secret opaque filter tokens with separate display labels; do not fall back to mutable account/category names as canonical identity. Keep fuzzy display-name parameters only as deprecated compatibility aliases.
- Multi-account drilldowns must carry every selected account, not only the first. Include normalized multi-account scope in cursor/data-version fingerprints.
- A custom `date_from`/`date_to` drilldown must clear any default month/period filter; otherwise the two scopes intersect and produce a deceptive empty list. Ensure reactive filter objects contain every URL-owned key before generic restoration iterates their keys.
- Bind pagination cursors to normalized filters and every table/relation that can alter rendered semantics.
- Execute multi-section reads in one consistent snapshot and expose a public data-version helper rather than importing a private implementation detail into the cockpit composer.
- If plans are household/category scoped but not account scoped, an account-filtered budget or forecast is unavailable. Do not compare account actuals with a household-wide plan. A clearly labeled all-account forecast may be shown separately, but it must not drive account-filtered budget-rest KPIs.

## Exact-money and semantic rules

- SQLite `REAL` aggregation is a release blocker when reconciliation must hold to CHF 0.01. Fold canonical effects with `Decimal` or integer minor units and quantize once under an explicit rounding policy.
- Missing CHF conversion is unknown, never a fallback to the original-currency number.
- Transfers and credit-card settlements are neutral; paired transfer volume is counted once.
- Valid linked refunds reduce expense in the origin category. Unlinked/excess refunds remain neutral and mark quality partial.
- Savings/comparison percentages are nullable for zero or unavailable denominators.
- Reconcile KPI income/expense/net to complete bucket and category totals. Top-N lists are subsets, not reconciliation surfaces.

## Budget and forecast pitfalls

- Extract the existing primary forecast formula into one public helper and reuse it; do not duplicate it in a cockpit composer.
- Reject implicit December and machine-date cutoffs in cockpit calls.
- Parent actuals, parent budgets, forecasts, charts, and transaction lists must use the same descendant closure.
- Broad exception handling that silently falls back to a different forecast is a correctness defect.
- If partial-period budget allocation is not established, return it unavailable or constrain scope to month boundaries; do not invent daily proration.

## Chart/data handoff

Make misleading bars impossible in the response contract:

- elapsed complete empty bucket: `0.00`;
- future or unavailable actual bucket: `null`;
- budget is a separate line dataset;
- all points share one currency/unit and ordered bucket domain.

Use the installed chart stack. Require one visible-zero linear axis and an accessible table equivalent; remove CSS minimum-height tricks that render zero/future bars as positive.

### Chart and drilldown test mechanics

- Extract or expose pure chart data/options so unit tests can assert `beginAtZero`, one shared dataset scale, true zero values, `null` future points, and budget-line omission when unbudgeted.
- Stub PrimeVue/Chart.js canvas components in JSDOM component tests and inspect the model plus accessible table. Real Chart.js observers can produce detached-window/canvas errors that obscure product assertions; browser UAT remains the rendering proof.
- Test chart and table interactions against the same month/category identifiers and require the detail transaction request to use the canonical paginated household endpoint.
- Keep the full filtered total distinct from the first page count, and provide a link that preserves all resolved date/account/category filters.

### Reconciliation behavior

Compute every reconciliation delta with `Decimal`, quantize only at the declared currency boundary, and fail closed when any complete-surface delta exceeds CHF `0.01` (for example HTTP 409). A visible `status: fail` on an otherwise successful cockpit payload is too easy for clients to ignore. Add boundary tests for `0.00`, `±0.01`, and `±0.02`, plus a data-version invalidation test after transaction/category changes.

## Risk classification

Typical **P0** blockers:

- floating-point money sums;
- raw analytics that classify refunds or missing FX differently from the canonical ledger;
- implicit forecast cutoff;
- account-scoped actuals compared with non-account-scoped plans;
- parent/descendant scope disagreement;
- fabricated positive zero/future chart bars.

Typical **P1** risks:

- fuzzy-name filters, incomplete data-version inputs, hard row caps reused for totals, inconsistent read snapshots, or silent semantic fallbacks.

Typical **P2** risks:

- untyped read contracts, duplicated chart options, ambiguous top-N labels, and speculative period-proration behavior.

## Verification plan

Add semantic tests before UI work:

1. adversarial cent, FX, refund, transfer, and settlement fixtures;
2. all KPI/series/category reconciliation deltas within CHF 0.01;
3. explicit cutoff and every period/comparison preset;
4. parent-descendant equality across chart, totals, and every transaction page;
5. cursor staleness after transaction, relation, category, merchant, or plan changes;
6. account-filtered budget-unavailable behavior;
7. future `null` versus elapsed zero buckets;
8. response-schema and synthetic chart-accessibility tests.

Do not propose DDL or indexes without query-plan evidence.