---
title: Abacus overhaul — full feature-set spec
type: spec
status: draft
tags: [abacus, spec, budgeting, event-sourcing, sqlite, agentic-sage]
created: 2026-06-19
sources:
  - docs/budget-spreadsheet-map.md (spreadsheet element + formula map, openpyxl-verified)
  - e2e/MAPPING-RESEARCH.md (live code map + command-registry research)
  - e2e/api-scenario.mjs, e2e/ui-walkthrough.spec.ts (verified baseline of current app)
---

# Abacus overhaul — full feature-set spec

## 1. Goal

Make Abacus absorb the user's "Monthly Budget 2026" spreadsheet process and surpass it:
keep the spreadsheet's transparency, direct control, and single-screen omniscience while
adding intelligence, audit history, and an agentic Great Sage that can operate the app.
One consolidated SQLite unit per profile. Fully offline. No external database.

## 2. Non-goals

- No cloud database, no Supabase, no multi-device sync (explicit user decision: push SQLite
  as one consolidated unit).
- No replication of the spreadsheet's bugs (January column offset, stray ranges).
- AI is enrichment, never required. Every number is computed deterministically first.

## 3. Architecture: one consolidated SQLite unit

Verified on target (Node 26.2, SQLite 3.53.1 via `node:sqlite`): JSON1, window functions,
recursive CTEs, generated columns, triggers, FTS5, partial indexes, WAL all present. The
unit has four layers inside each `data/db/<slug>.db`:

1. **Event log** (exists) — append-only source of truth. All mutations are events.
2. **Projection tables** (new) — materialized read models rebuilt by replay. Fast joins for
   modules. Retires the "materialized views are a later lever" note in `docs/abacus.md`.
3. **History tables** (new) — `AFTER INSERT/UPDATE/DELETE` triggers write temporal rows so
   Sage Databases Insights queries relational change-history, not only the event stream.
4. **Search + flexible attributes** — FTS5 over transaction text; JSON1 columns for weather
   payloads, trend tags, calendar metadata, indexed via generated columns.

Schema-history is tracked in a `schema_version` table plus a `migrations` audit log, so Sage
can mine structural change over time.

Event sourcing already provides per-entity version history. History tables add the relational
shape. Together they satisfy "db versioning on all entities for Sage Databases Insights"
without a second backend.

## 4. Domain model additions (packages/domain)

Current aggregates: Account, Transaction, Budget, Goal. Additions below.

### 4.1 Budget tiers + payment timing

- Extend Budget line with `tier`: `needs | could_live_without | work_related |
  savings_retirement | bank_fees`, and `paymentTiming`: `1-3 | 27th | 30th | none`.
- New events: `BudgetLineTierSet`, `BudgetLinePaymentTimingSet`.
- Tier subtotals and timing buckets compute from line attributes (the spreadsheet did this
  by fixed row ranges; here it is explicit per line). Source formulas: map §1 Zone 3, Zone 4.

### 4.2 Account stream typing (dual ledger)

- Account already has `accountType` (cheque, credit_card, savings, investment, …). Add a
  derived `stream`: `cheque | credit` for the dual-ledger category rollups. Per-account,
  per-category spend with percent of stream total. Source: spreadsheet category-expenditure
  sheets, `SUMIFS(amount, category, label)` + `IFERROR(amount/total,0)`.

### 4.3 Asset taxonomy + liquidity

- New aggregate `Asset` (or extend Account) with `assetClass`:
  `house_investment | retirement_annuity | tax_free | money_market | notice_deposit |
  pure_save | cheque | credit | physical`, and `liquidity`: `liquid | locked`
  (the Touchie / No-Touchie split). Physical assets carry a free-text label (vehicle,
  bicycle, firearms). Net worth rolls up by class and by liquidity. Source: §1 Zone 6.
- New events: `AssetRecorded`, `AssetValued`, `AssetRemoved`.

### 4.4 Prepaid utilities

- New aggregate `UtilityMeter` with `kind`: `electricity | water`. Dated `TopUp` events
  carry `units` (kWh, nullable for water), `amountCents`, `occurredAt`. Yields total spend,
  total units, per-kWh cost, and cost-creep trend. Source: §1 Zone 9.
- New events: `MeterRegistered`, `TopUpRecorded`.

### 4.5 Calendar backbone (keystone)

- New aggregate `LedgerEntry` unifying dated debit/credit entries that every module reads.
  Each entry: `date`, `accountId`, `stream`, `amountCents` (signed), `category`, `tier?`,
  `description`, `source` (`statement | manual | sage | import`), `metadata` (JSON:
  weather, tags). Transactions become the primary producers; manual and Sage-authored
  entries use the same shape.
- New events: `EntryRecorded`, `EntryEdited`, `EntryDeleted` (event-sourced, fully audited).
- A `calendar_day` projection table aggregates per-day totals and feeds Transactions,
  What's Coming Soon, and Sage insights from one substrate.

### 4.6 Persistence audit + reference data into the DB

Audit baseline (verified): seed/dummy data already persists. Archetype JSON in
`data/archetypes/*.json` is written to SQLite via `seedProfile()` in
`apps/api/src/seed/run-seed.ts` at seed time, then inert; the app reads only from SQLite.
All four current aggregates (Account, Transaction, Budget, Goal) are event-sourced. The
remaining unpersisted state:

- **Category taxonomy** — hardcoded const in
  `packages/domain/src/categorization/categories.ts`, served via `/api/categories`. Move
  into a DB reference table (`ref_category`, `ref_category_group`) seeded from the const on
  migration, so categories become user-editable and Sage-queryable as a closed set. This is
  the home for the spreadsheet's `Category DDL` and the source-category crosswalk. Merchant
  regex rules stay in code (logic, not data) but the matched merchant is already persisted.
- **UI settings + dashboard layout** — `localStorage` (`abacus-settings-v1`,
  `abacus-dash-layout-v1`). Keep per-browser as the default. Optionally mirror to a
  `user_pref` table for cross-restore, off by default; not audit-trail material.

Requirement carried forward: every system entity (the four existing aggregates plus the new
Asset, UtilityMeter, LedgerEntry, budget tiers, weather cache, command-event tags, and
history tables) maps to the consolidated `.db`. A migration adds the reference tables and
seeds them; the seed path is extended to write the new entities (assets, utilities, calendar
entries) the same way it already writes transactions.

## 5. Feature epics

Each epic lists what to build, the data it touches, and acceptance criteria. Ordering and
dependencies are in §10.

### Epic A — Budgeting model (tiers, timing, dual-ledger rollups)

- Budget module renders tier groupings with subtotals and a payment-timing view.
- Per-account (cheque vs credit) category rollup with percent of stream total.
- Accept: budget lines show their tier; tier subtotals equal the sum of their lines;
  category rollups match `SUMIFS` semantics from the source.

### Epic B — Net worth + SA asset taxonomy

- Net Worth module shows assets by class, by liquidity (liquid vs locked), liabilities as
  positive red magnitude (API stores liabilities signed-negative), physical-asset registry.
- Accept: `net = assets + liabilities` holds (signed convention); liquid and locked totals
  reconcile to the asset list.

### Epic C — Prepaid utilities module

- Electricity: dated top-up log, total kWh, total spend, per-kWh cost, cost-creep flag.
- Water: dated top-up log, total spend.
- Accept: totals equal the sum of top-ups; per-kWh cost = spend / units; creep flag fires
  when rolling per-kWh cost rises beyond a threshold.

### Epic D — Calendar backbone

- `LedgerEntry` aggregate + `calendar_day` projection. All modules read day-level data from
  it. Manual and Sage-authored entries supported.
- Accept: an entry created via API or Sage appears in Transactions, shifts net worth and
  cash flow, and lands in the correct calendar day. Round-trips are event-sourced and
  reversible.

### Epic E — Transactions enrichment

- **Weather + day**: next to each transaction date show the weekday and historical weather
  for that date and the user's configured location: actual temp in °C and a feels-like
  describer from `apparent_temperature`. Source: Open-Meteo historical archive
  (`archive-api.open-meteo.com/v1/archive`, free, no key, data from 1940,
  10k calls/day non-commercial). Cache per `(date, location)` in a `weather_cache` table;
  one row reused across all transactions on that day. Location from a user setting; if
  unset, weather column hidden.
- **Timeline date picker + chainable sync-staleness filter**: the date picker doubles as a
  scrollable timeline. A new filter layers on top of existing filters (category, search,
  account), defaulting to the window since the last data sync/upload date, and shows a
  "N days out of sync to now" badge derived from the latest ingest event.
- Accept: weather renders for past-dated rows; cache prevents duplicate API calls; the
  staleness filter chains with category+search and the badge shows correct day delta.

### Epic F — What's Coming Soon module

- Future-facing module: current balance plus an accruing list along a forward timeline,
  cycling future months from detected trends. Recursive-CTE projection generates the months.
- Pattern fan-out (broad, diverse): recurring commitments, emerging trends, exiting trends,
  seasonal patterns, anomalies-likely-to-repeat, one-off vs habitual. Deterministic feature
  extraction first; Sage narrates and ranks.
- Accept: projected balance per future month is the running sum of detected recurring and
  trend-derived entries; each surfaced pattern names its type and the data range it came
  from; toggling a pattern off changes the projection.

### Epic G — XLSX importer (the user's workbook)

- Read the 12 month ledgers into LedgerEntries (account, category from column F/J, amount
  from C/G/K/N), Budget inputs into budget lines + tiers + payment timing, Current Assets
  into Asset records, the electricity/water logs into UtilityMeter top-ups. Normalize
  January's column offset on import. Map the 12 source categories onto Abacus category keys.
- Accept: importing the user's real file reproduces the spreadsheet's totals (cross-checked
  against its row-108 sums) with zero residue and the January offset corrected.

### Epic H — Command / Capability registry

- One typed registry, one entry per user verb (open module, set filter, add entry, edit
  budget tier, correlate, switch profile/month). Each command: `id`, `module`, `title`,
  `params` schema, `reads[]`, `writes[]`, `anchor` (stable `data-cmd` attribute on the
  control), `run(ctx, params)` calling the same API/state the click handler uses.
- Discovery: `listCommands()` + `GET /api/commands`. Categories materialized first-class.
- Self-heal: build-time check that every `anchor` resolves to exactly one `[data-cmd]` and
  every `[data-cmd]` has a registry entry; runtime resolve with fallbacks (re-query by
  `data-cmd`, then title match, then report unmapped and still run deterministically).
- Edits indexed + correlated: every `run()` emits an event tagged with `commandId` + actor
  (`user | sage`); `reads`/`writes` give the static correlation graph, the event log the
  dynamic one. Detail: `e2e/MAPPING-RESEARCH.md`.
- Accept: a command runs identically from a click and from Sage; layout change re-heals
  anchor resolution; the index check fails loudly on drift.

### Epic I — Agentic Great Sage + virtual cursor + tutorial

- Settings toggle: "Let Sage operate the app." When on, Sage maps a natural-language goal to
  a sequence of `commandId` + params, executes via the registry, and a virtual cursor tweens
  to each command's anchor, plays the action, then lets real state render.
- Tutorial engine: a tutorial is a saved list of `commandId` + params + caption, replayed on
  the same path. Reduced-motion fallback.
- Accept: with the toggle on, "show me my biggest category this month" navigates Spending,
  applies the filter, and narrates via the cursor; with it off, Sage answers in chat only.

### Epic J — Sage Databases Insights + forecasting + always-surface-patterns

- Sage reads the history tables and event log to mine patterns only visible from granular
  audited history (e.g., a budget cap repeatedly raised, a category drifting across months,
  a schema change correlating with a data shift).
- Forecasting cites the dataset ranges and combinations used ("based on Mar–Jun cheque
  outflows and your RA debit order").
- Conversational behavior: Sage always attempts to surface a hidden pattern while answering,
  flagged distinctly from the direct answer.
- Accept: a Sage answer includes the direct deterministic result plus at least one cited,
  data-ranged pattern when one exists; forecasts name their input ranges.

### Epic K — Budget Command Center

- One consolidated module composing the spreadsheet's zones: income, tiered expenses, net
  worth, running balances, YTD averages, utilities, payment timing. Inline-editable figures
  that write events. "Explain this number" expands any figure into its derivation.
- Accept: the center shows all zones on one focused surface; editing a figure inline writes
  an event and updates dependents; "explain" traces a number to its source rows.

## 6. SQLite schema sketch (per-profile `.db`)

```sql
-- Layer 1: event log (exists)
-- events(seq, aggregate_id, type, data JSON, actor, command_id, occurred_at)

-- Layer 2: projections (rebuilt by replay)
CREATE TABLE proj_ledger_entry (
  id TEXT PRIMARY KEY, date TEXT, account_id TEXT, stream TEXT,
  amount_cents INTEGER, category TEXT, tier TEXT, description TEXT,
  source TEXT, metadata TEXT,                          -- JSON: {weather:{...}, tags:[...]}
  weather_feels TEXT GENERATED ALWAYS AS (json_extract(metadata,'$.weather.feels')) VIRTUAL
);
CREATE INDEX idx_entry_date ON proj_ledger_entry(date);
CREATE INDEX idx_entry_cat ON proj_ledger_entry(category);

CREATE TABLE proj_calendar_day (
  date TEXT PRIMARY KEY, cheque_in INTEGER, cheque_out INTEGER,
  credit_in INTEGER, credit_out INTEGER, net INTEGER
);

CREATE TABLE proj_budget_line (
  month TEXT, category TEXT, cap_cents INTEGER, tier TEXT, payment_timing TEXT,
  PRIMARY KEY(month, category)
);

CREATE TABLE proj_asset (
  id TEXT PRIMARY KEY, label TEXT, asset_class TEXT, liquidity TEXT, value_cents INTEGER
);

CREATE TABLE proj_meter_topup (
  id TEXT PRIMARY KEY, kind TEXT, occurred_at TEXT, units REAL, amount_cents INTEGER
);

-- Layer 3: history (trigger-fed temporal rows for Sage Databases Insights)
CREATE TABLE hist_budget_line (
  month TEXT, category TEXT, cap_cents INTEGER, tier TEXT,
  changed_at TEXT, op TEXT                            -- insert|update|delete
);
CREATE TRIGGER tg_budget_hist AFTER UPDATE ON proj_budget_line BEGIN
  INSERT INTO hist_budget_line VALUES
    (OLD.month, OLD.category, OLD.cap_cents, OLD.tier, datetime('now'), 'update');
END;

-- Layer 4: search + meta
CREATE VIRTUAL TABLE fts_entry USING fts5(id UNINDEXED, description, merchant, category);
CREATE TABLE weather_cache (date TEXT, location TEXT, temp_c REAL, feels TEXT,
  PRIMARY KEY(date, location));
CREATE TABLE schema_version (version INTEGER, applied_at TEXT, note TEXT);
```

(Timestamps come from event `occurred_at`, not `datetime('now')`, in production replay; the
trigger example uses `now` only for live edits.)

## 7. API additions (apps/api, Hono)

- `GET /api/budget` — extend with tier rollups + payment-timing buckets.
- `GET /api/networth/assets` — taxonomy, liquid/locked totals, physical assets.
- `GET /api/utilities` — electricity/water logs, totals, kWh, per-kWh cost trend.
- `GET /api/calendar?from&to` — day-level aggregates.
- `GET /api/coming-soon` — projected future months + ranked patterns with cited ranges.
- `GET /api/transactions` — add weather (cached) + weekday; add `since-sync` filter +
  staleness delta.
- `GET /api/commands` — registry discovery.
- `POST /api/sage/act` — agentic command sequence (gated by the settings toggle).
- `GET /api/sage/insights` — history/forecast patterns with cited dataset ranges.
- `POST /api/import/xlsx` — workbook importer.
- `GET /api/weather?date&lat&lon` — server-side Open-Meteo proxy + cache (keeps the browser
  off the third-party host and centralizes the 10k/day budget).

## 8. Web modules (apps/web)

Add to the registry (currently 10 modules): `utilities`, `coming-soon`, `command-center`,
`calendar`. Extend: `transactions` (weather, weekday, timeline picker, staleness filter),
`budget` (tiers + timing), `networth` (taxonomy, liquid/locked), `sage` (agentic toggle,
cursor, insights with cited ranges). Inject `data-cmd` anchors on all controls.

## 9. External integration: Open-Meteo

- Historical archive only (transactions are past-dated). `apparent_temperature` gives
  feels-like; `temperature_2m` gives actual °C. No API key. Server-side proxy + `weather_cache`
  keyed by `(date, location)`, so one call serves all transactions on a day and re-runs are
  free. Location is a user setting (city or lat/lon). Degrade gracefully: if location unset
  or API unreachable, hide the weather column. Source: open-meteo.com historical docs.

## 10. Implementation phasing (dependency order)

1. **Backbone**: consolidated SQLite (projections + history triggers + FTS5 + schema_version),
   `LedgerEntry`/calendar aggregate (Epic D), persistence audit + reference tables (§4.6:
   `ref_category`/`ref_category_group` seeded from the const; seed path extended to new
   entities). Everything else reads this.
2. **Budgeting model** (Epic A), **assets** (Epic B), **utilities** (Epic C) — domain + API + modules.
3. **XLSX importer** (Epic G) — needs the model from 1–2 to import into.
4. **Transactions enrichment** (Epic E) + **weather proxy** (§9).
5. **What's Coming Soon** (Epic F) — needs calendar + trend extraction.
6. **Command registry** (Epic H) — instrument all modules from 1–5 with `data-cmd`.
7. **Budget Command Center** (Epic K) — composes the now-complete zones, inline edit via registry.
8. **Agentic Sage + cursor + tutorial** (Epic I) — rides the registry.
9. **Sage Databases Insights + forecasting** (Epic J) — mines history tables once they have depth.

Phases 1–7 are the budgeting overhaul (was "Spec 1"). Phases 8–9 are the agentic layer
(was "Spec 2"). They share the command registry as the seam.

## 11. Testing

Extend the verified baseline (`e2e/api-scenario.mjs`, `e2e/ui-walkthrough.spec.ts`):
- Per new endpoint, add data-correctness assertions to the API scenario.
- Per new module, add a dock-tour leg + screenshot to the UI walkthrough.
- Importer: golden test importing the user's workbook, asserting reproduced row-108 totals.
- Command registry: the build-time anchor/registry drift check is itself a test.
- Weather: assert cache hit on second call for the same `(date, location)`.
- Keep "zero console errors" and the net-worth/cash-flow identity checks.

## 12. Risks + open questions

- Weather location granularity: single home location vs per-transaction geo (not tracked).
  Default to one configured location; revisit only if merchant geo becomes available.
- Trend taxonomy scope (Epic F): start with recurring, emerging, exiting, seasonal; expand
  the fan-out iteratively rather than enumerate exhaustively up front.
- Agentic write-scope (Epic I): which commands Sage may run unattended vs needing a confirm.
  Default: reads and filters free; writes (entries, budget edits) ask once per session.
- Inline-edit conflict with event replay: inline edits must emit events, never mutate
  projections directly, so replay stays authoritative.