## PR2 backend/data architecture plan **Audit basis:** exact commit `47d9ab1d2d66d939dc33ddb3df6e93ce5077669e`; worktree remained clean and `git diff --check` passed. ### 1. Canonical sources to reuse - **Monetary semantics:** `src/jarvis_finance/services/household_financials.py` - `_effects_cte()` lines 85–176 - `get_household_financial_summary()` lines 179–270 - `list_household_financial_effects()` lines 273–301 - Already enforces confirmed-only rows, neutral `transfer`, linked-refund attribution, excess/unlinked-refund neutrality, and missing-CHF quality metadata. - **Budget plans and canonical forecast:** `src/jarvis_finance/services/budget_planning.py` - `_plan_amounts()` lines 344–359 - `_actual_months()` lines 362–378 - `get_budget_planning_matrix()` lines 410–525 - Canonical forecast must be its documented primary formula: `actual YTD + remaining months × monthly budget`. - **Category hierarchy/roll-up:** `src/jarvis_finance/services/budget_overview.py` - `_category_meta()` lines 61–63 - `_ancestor_ids()` lines 66–76 - `get_budget_status_by_category()` lines 205–361 - **Paginated household transactions:** `src/jarvis_finance/services/household_import.py` - cursor helpers lines 2158–2172 - `_household_transaction_data_version()` lines 2175–2188 - `list_household_transactions()` lines 2220–2385 - Routed at `src/jarvis_finance/api/routers/budget.py:263–291`. - **Settlement neutrality:** `src/jarvis_finance/services/household_review_corrections.py:840–961` materializes card settlements as `transaction_type='transfer'`; detail semantics confirm neutrality at lines 1034–1059. - **Existing account/category lists:** `budget.py:762–791`, backed by `budget_accounts.list_budget_accounts()` and `budget_categories.list_categories()`. - **Chart stack already installed:** Chart.js 4.5.1 and PrimeVue Chart in `frontend/package.json:15–25`; no dependency needed. Do **not** use the following as cockpit calculations: - `budget_analytics.py:get_category_analysis()` / `get_monthly_comparison()` treat refunds as income and fall back from missing CHF to original currency. - `budget_data_explorer.py:_base_confirmed_rows()` has the same raw semantics and a hard 1,000-row cap. - `budget_transactions.py:list_budget_transactions()` is raw, capped at 200, and does not apply linked-refund effective categories. - `budget_analytics_v2.py` is closer to canonical but only month/year scoped and still composes legacy budget/status behavior. --- ## 2. Minimal backend shape ### New read-model composer Add: `src/jarvis_finance/services/household_cockpit.py` This is a **composer**, not a new calculation engine. It should call the canonical effect stream and extracted budget helpers. Recommended public functions: ```python resolve_household_scope(...) get_household_cockpit(conn, scope) get_household_category_detail(conn, category_id, scope) ``` ### Canonical routes Add in `src/jarvis_finance/api/routers/budget.py`: ```text GET /api/budget/household/cockpit GET /api/budget/household/categories/{category_id} ``` Keep the existing: ```text GET /api/budget/household/transactions ``` as the only transaction-list endpoint; extend its exact filters rather than creating another list implementation. ### URL/query contract Use one contract across overview, category detail, and transaction drill-down: ```text period=month|quarter|ytd|year|custom anchor=YYYY-MM-DD date_from=YYYY-MM-DD # required only for custom date_to=YYYY-MM-DD # required only for custom account_id= # repeatable or comma-normalized if multi-select is required comparison=none|previous_period|previous_year ``` Server resolves these into: ```json { "period": "ytd", "date_from": "2026-01-01", "date_to": "2026-07-30", "cutoff_date": "2026-07-30", "account_ids": [], "comparison": "previous_year", "comparison_date_from": "2025-01-01", "comparison_date_to": "2025-07-30", "bucket": "month" } ``` Return the resolved values; the browser must not independently derive comparison windows or forecast cutoffs. For migration compatibility, accept existing `period=YYYY-MM` and `account=` only as deprecated aliases, but emit exact canonical values. New links must use IDs. --- ## 3. Required response contract ### `GET /household/cockpit` ```json { "contract_version": "household_cockpit_v1", "semantics_version": "household_financial_semantics_v1", "as_of": "...", "data_version": "...", "resolved_filters": {}, "quality": { "data_status": "current|partial", "unavailable_chf_count": 0, "unlinked_refund_count": 0, "transfer_membership_conflict_count": 0, "warnings": [] }, "kpis": { "income_chf": "0.00", "expense_chf": "0.00", "net_chf": "0.00", "savings_rate_percent": null, "neutral_transfer_volume_chf": "0.00", "comparison": { "income_delta_chf": "0.00", "expense_delta_chf": "0.00", "net_delta_chf": "0.00" } }, "trend": [], "expense_categories": [], "budget": {}, "activity": { "latest": [], "largest": [], "top_merchants": [] }, "reconciliation": {} } ``` Rules: - `expense_categories` contains the complete ranked set; frontend may display top N but reconciliation uses all rows. - `latest`, `largest`, and `top_merchants` are explicitly labeled non-reconciling subsets. - Return transaction tokens, not internal transaction IDs. - Merchant grouping preference: `budget_merchants.display_name` via `merchant_id`, then `payee`, then `description`, then source label. - No unbounded raw transactions inside the cockpit response. ### `GET /household/categories/{category_id}` Return: - exact category metadata; - `scope_category_ids`, including all active descendants; - the same `resolved_filters`, quality and data version; - monthly points: ```json { "month": "2026-08", "actual_chf": null, "budget_chf": "250.00", "comparison_actual_chf": null, "is_future": true } ``` - `actual_chf="0.00"` only for elapsed, complete months with no effects; - `actual_chf=null` for future or unavailable months; - true monthly budget from `budget_plan_items`, not a CSS target; - transaction-list URL carrying the exact category, descendant policy, period and account scope. Add the response models to `src/jarvis_finance/api/schemas/household.py` instead of continuing untyped dictionaries for this new contract. --- ## 4. Formula and reconciliation contract Fold a single canonical effect stream with `Decimal`, quantized once to CHF 0.01: ```text income = Σ income_effect expense = Σ expense_effect net = income − expense bucket net = bucket income − bucket expense category total = Σ expense_effect by effective_category_id merchant total = Σ expense_effect by canonical merchant key ``` Current canonical behavior to preserve: - income → positive income effect; - expense/fee → positive expense effect; - valid linked refund → negative expense effect on the origin category; - unlinked/excess refund → zero financial effect plus partial-quality warning; - transfer and card settlement → zero income/expense/net; - missing CHF → unknown/excluded with partial quality, never original-currency fallback. Savings rate: ```text (income − expense) / income × 100 ``` Return `null` when income is zero or required CHF inputs are incomplete. Comparison: ```text absolute delta = current − comparison percentage delta = (current − comparison) / abs(comparison) × 100 ``` Percentage is `null` when comparison is zero or unavailable. Budget: ```text monthly budget = Σ canonical monthly plan amount for category scope YTD budget = Σ monthly budget for elapsed selected months variance = actual expense − applicable budget forecast = actual through explicit cutoff + remaining months × monthly budget ``` Use one forecast only: the current `budget_adjusted_forecast` from `budget_planning.py:464–465,482–484`. Extract that formula into a public helper and call it from both the planning matrix and cockpit; do not reimplement it. Required reconciliation payload: ```text KPI income == Σ trend income KPI expense == Σ trend expense KPI expense == Σ complete expense-category totals KPI net == income − expense Σ budget category actual == KPI expense for the same category universe each delta must be within CHF 0.01 ``` A failed invariant should fail the request/test, not be hidden by rounding. --- ## 5. Required changes to existing services ### `household_financials.py` - Replace `CAST(... AS REAL)` and SQL floating-point sums in `_effects_cte()` with exact minor-unit or Python `Decimal` aggregation. - Keep this module as the sole owner of refund/transfer semantics. - Extend filters to exact `account_id`; do not use name substring matching from `_filters():79–81`. - Expose enough transaction-level metadata for account IDs, transaction tokens, canonical merchant names and deterministic activity sorting. ### `budget_planning.py` - Extract public helpers for: - canonical monthly/annual plan amounts; - the existing budget-adjusted forecast formula; - explicit cutoff validation. - Remove the silent `current_month or f"{year}-12"` default at lines 417–418 for cockpit calls. - Preserve `get_budget_planning_matrix()` for existing consumers, but require the cockpit to pass an explicit cutoff and filtered canonical effects. ### `household_import.py:list_household_transactions()` Extend with: ```text account_id category_id include_descendants=true|false ``` - Filter by exact IDs. - Category filtering must use effective refund category and the same descendant set as category detail. - Include `effective_amount_chf` and `financial_semantics` in list rows. - Preserve cursor/data-version 409 behavior. - Include transfer/refund relation inputs in the data version, not just transactions/categories/accounts. ### Data version Cockpit/category-detail version must cover at least: - `budget_transactions` - `budget_transfers` - `household_credit_card_settlements` - `budget_categories` - `budget_accounts` - `budget_merchants` - `budget_plan_items` - `budget_category_baselines` No DDL or new index should be added in PR2 without query-plan evidence. --- ## 6. Category-detail/chart handoff Backend must make fake bars impossible: - future `actual_chf = null`; - elapsed zero month `actual_chf = "0.00"`; - monthly budget remains the line dataset; - all points share one unit and month sequence. Frontend should add one shared component, for example: `frontend/src/components/household/HouseholdBudgetActualChart.vue` Use it in household category detail and replace the CSS-bar implementation in `BudgetStatusPage.vue:25,42`. Chart.js options must include one linear Y scale with: ```ts scales: { y: { beginAtZero: true, min: 0 } } ``` The component must render an accessible table equivalent. Do not retain `barHeight()`’s six-pixel minimum, which currently creates positive bars for zero/future values. --- ## 7. Delivery sequence and tests 1. **Canonical monetary hardening** - Modify `household_financials.py`. - Extend `test_household_import_v1_golden.py`. - Add adversarial cent, refund, transfer, settlement and missing-FX reconciliation cases. 2. **Cockpit read model and API** - Add `household_cockpit.py`. - Extend `schemas/household.py` and `routers/budget.py`. - Add `tests/unit/test_household_cockpit_v1.py`. - Test all presets, comparisons, exact account IDs, zero denominators, explicit cutoff and snapshot consistency. 3. **Category detail and transaction scope** - Extend paginated household list. - Test parent/descendant equality across chart, category total and paginated transaction sum. - Test stale cursor after plan/category/transfer changes. 4. **Frontend contract** - Extend `frontend/src/api/household.ts`. - Use `useQueryFilters.ts`; add `period`, `anchor`, `comparison`. - Add shared Chart.js component and contract/accessibility tests. --- ## Risks ### P0 — release blockers - **Floating-point reconciliation:** `household_financials.py:87–99,130–132,200–210` uses SQLite `REAL`; CHF 0.01 reconciliation is not guaranteed. - **Legacy analytics are semantically incompatible:** `budget_analytics.py:18–136` treats refunds as income and may invent CHF from foreign original amounts. It must not feed PR2. - **Default December/machine-date forecast:** `budget_planning.py:417–418` defaults to December; `budget_overview.py:293–300` also derives scope from machine date. Both can silently turn YTD into full-year behavior. - **Account-scoped budgets do not exist:** `budget_plan_items` is category/year scoped, not account scoped. When `account_id` is selected, return budget and forecast as unavailable with reason `budget_not_account_scoped`; do not compare account actuals to household-wide budget. - **Parent-category mismatch:** `get_budget_status_by_category()` rolls actuals to ancestors, while planning rows and `get_category_month_detail()` are direct-category scoped. Category chart, budget, totals and transaction list will disagree unless one shared descendant set is used. - **Future/zero chart fabrication:** `BudgetStatusPage.vue:42` enforces a six-pixel minimum and current monthly arrays emit future zeroes. This violates the requested visual semantics. ### P1 — correctness/performance risks - Existing account/category household filters use fuzzy display-name matching (`household_import.py:2253–2255,2294–2296`), so duplicate names can mix scopes. - `_household_transaction_data_version()` omits transfer, settlement, merchant and plan relations. - Existing merchant/data-explorer paths are capped at 1,000 rows; raw budget list is capped at 200. - `get_budget_status_by_category():327–360` catches every exception and silently falls back to different forecast semantics. - Cockpit, comparison and detail queries must share one read snapshot; otherwise concurrent writes can produce internally inconsistent but individually valid sections. - Data-version hashing currently scans complete tables on every page; retain for correctness initially, then optimize only with measured query-plan/runtime evidence. ### P2 — maintainability/UX risks - New read contracts need Pydantic models; current dictionary-only responses allow frontend drift. - Existing Chart.js instances define options independently and do not consistently force a visible zero baseline. - Top-N sections can be mistaken for reconciling totals unless response metadata and labels identify them as subsets. - Partial-period budget proration is not established in existing code. PR2 should either remain month-boundary based or return partial-period budget as unavailable; do not invent daily proration in this PR. ## Outcome - Produced an implementation-ready, actual-code-based PR2 backend/data plan. - No files created or modified. - No blockers encountered during read-only inspection.