BrightBot.co.uk
GitHubLinkedIn ProfileBuy Me a CoffeeRSS Feed
Personal finance aggregator: Periods, charts, and a phone-sized UI - Part 13

Personal finance aggregator: Periods, charts, and a phone-sized UI - Part 13

Building Finance System

~9 min read

With real categories (post 12) the Home and Transactions pages finally had something worth slicing. This post covers the next batch of changes, mostly 30 September and 1 October: choosing a reporting window, charting balances over time, making the whole app usable on a phone, and a surprisingly instructive detour through deleting a transaction. The theme is that each change started as the obvious implementation and had to be corrected once it met real data.

Rolling windows instead of calendar months

Until now "this month" was literal: income and spending for the current calendar month against the previous one. The commit message for the user-configurable categories change (from post 12) records the problem - the chart "was only ever showing 1-2 categories because it was scoped to the current calendar month, and most already-categorised spending was dated in earlier months." Early in a month, a calendar view is nearly empty; late in one it's flattering. The month boundary, not my spending, was driving what I saw.

A follow-up replaced the framing with a "Last 30 / 60 / 90 days" selector on Home. The API change is the interesting part. Both GET /api/transactions/summary and GET /api/categories/spending now take a days parameter and compare the current window against the equal-length window immediately before it:

today = date.today()
end = today + timedelta(days=1)
start = today - timedelta(days=days - 1)
previous_end = start
previous_start = start - timedelta(days=days)

Two adjacent, equal-length windows is what makes the "vs previous N days" delta honest. Compare a 60-day window to a 30-day one and every delta would be a lie; derive both from the same days and the comparison is apples-to-apples whichever period is selected. The endpoint also rejects values outside an allowed set (ALLOWED_SUMMARY_WINDOWS) rather than accepting an arbitrary integer, which keeps the UI's three options and the API's validation from drifting apart. The same commit adds a matching date filter to the Transactions page (this month so far, last 60, last 90 days), and drops the separate always-90-days spending card that the categories change had added, since the selector now covers it.

Balance history: the chart you can only build going forward

The home page charts of account balances over time commit adds a line chart per account. There's a catch built into the idea: nothing records historic balances. TrueLayer reports the current balance, so a history only exists if I start saving snapshots. So the commit adds an account_balance_history table, keyed on (bank, account_id, date), written by save_accounts() on every sync and by the manual-account functions whenever a balance is set.

The write uses an upsert, so a second sync on the same day moves that day's point rather than piling up near-duplicates:

INSERT INTO account_balance_history (bank, account_id, date, balance)
VALUES (:bank, :account_id, :date, :balance)
ON CONFLICT (bank, account_id, date) DO UPDATE SET balance = excluded.balance

The endpoint's docstring owns the limitation: "Builds up over time; there's no backfill for balances from before this existed." The UI says the same thing to the viewer - an account with fewer than two points shows "Not enough history yet - check back after the next sync" rather than an empty axis. A chart that can't exist yet should say so.

Two design choices are worth recording. The line colour follows the same good/bad split as everywhere else on the page - green for accounts, red for debts - rather than a per-account hue, because each chart is a single series: "there's no identity to encode, only the same credit/debt status shown everywhere else." And this is where post 5's "no chart library" stance expired: for time-series with a tooltip and axes, hand-built <div> bars stopped being the cheaper option, so the commit adds recharts. The category bars stayed <div>s.

One unglamorous consequence: jsdom has no layout engine, so every element measures 0×0 and Recharts' ResponsiveContainer never reports a size - the charts would silently render nothing in every test. The commit adds a ResizeObserver stub to the test setup that fires once with a fixed size, and its comment explains exactly that, because the failure mode is a test that passes while asserting nothing.

A later change fixed how debt reads on these charts. A card's balance is stored negative (post 11), so its line fell as the debt grew - which looks like good news. Debt accounts now plot the amount owed, so the line rises as the debt does, and the tooltip says "Owed" instead of "Balance". The account list and the charts also share one ordering - cash and savings first, then cards and other debt - using a stable sort, so each group keeps the order the API returned.

Filters that survive a trip to Settings

The make transaction filters local storage commit came from a small, real annoyance: add a rule in Settings, come back to Transactions, and the filters I'd set were gone. The fix stores them in localStorage - as one object rather than five keys, so the whole filter set restores atomically.

There's a subtle bug the commit's own comment records, and it's a good one. Transactions already had an effect that clears the account filter if it doesn't belong to the chosen bank. But a filter restored from storage is set before the transactions have loaded, so the list of valid accounts is momentarily empty and the restored filter looks invalid for one render - and gets wiped before the data it belongs to has arrived. The fix is to gate that cleanup on loading. Persisted state plus a "clean up stale state" effect is a classic way to lose data on first render; the cleanup has to know whether it's judging against real data or not-yet-loaded data. (All access is wrapped in try/catch, because storage can be unavailable in private browsing; the filters then just don't persist.)

Deleting a transaction, twice

The add hide transactions commit added a way to remove a transaction - typically a duplicate, since the de-dup key from post 4 only catches exact (bank, id) repeats, and a bank that sends the same payment twice under different ids gets through. The first version simply deleted the row, and its docstring made a claim: "A later sync's INSERT OR IGNORE won't resurrect it unless the bank re-sends that same (bank, id) pair."

That claim has the bug in it. The nightly sync does re-fetch a window of recent history, and INSERT OR IGNORE only skips a row if it's already there. Delete the row and the next sync dutifully puts it back. The right fix, soft delete transactions, is to keep the row but mark it:

conn.execute(
    "UPDATE transactions SET is_deleted = 1, deleted_at = ? WHERE bank = ? AND id = ? AND is_deleted = 0",
    (datetime.now().isoformat(), bank, txn_id),
)

The row stays, so the primary key still exists, so INSERT OR IGNORE still skips it - the de-dup mechanism now protects the deletion instead of undoing it. list_transactions() filters WHERE t.is_deleted = 0, so categorisation, the summary and the frontend all treat it as gone. Two columns were added with the usual PRAGMA table_info guard, and pre-existing rows default to not-deleted. The earlier docstring was rewritten to describe what the code actually does.

A phone-sized UI

The same commit made the whole app usable on a phone: a global box-sizing reset, responsive nav, cards, filters and forms below 640px, and a horizontally scrollable wrapper around the Transactions table rather than squeezing every column into unreadable wrapped text. The most useful part was how it was found: by looking at real mobile screenshots, which turned up two bugs no desktop review would have - a negative total splitting its minus sign onto its own line, and Edit/Remove button pairs wrapping independently and stranding "Remove" alone on a row. Both are the kind of thing you only find by actually looking.

A second pass, after using it on a real phone, was CSS only, and mostly about fingers rather than layout:

  • Inputs and selects are 16px, because iOS Safari zooms the whole page in when a smaller field gets focus.
  • Buttons, nav links and inputs have a 44px minimum touch target, and checkboxes and colour swatches are bigger.
  • Inline edit forms put every control on its own full-width line, with the buttons sharing the last one.
  • A tall modal - the bank picker, or a form with the keyboard open - scrolls inside itself (max-height: calc(100dvh - 2rem)) instead of running off the bottom of the screen.

Previous: ← Rule-based categorisation · Next: Hargreaves Lansdown, with no API →