BrightBot.co.uk
GitHubLinkedIn ProfileBuy Me a CoffeeRSS Feed
Personal finance aggregator: From script to service - Part 4

Personal finance aggregator: From script to service - Part 4

Building Finance System

~10 min read

The CLI prototype proved the OAuth flow worked. It also had a very short list of things it fundamentally couldn't do: run anywhere but my laptop, keep transaction history instead of overwriting it, or be looked at by anything other than cat transactions.json. This post covers the two commits that fixed all three: init: basic backend/frontend, which turned the script into a FastAPI package with SQLite storage and typed settings, and Add TrueLayer web linking, provider/storage packages, deploy configs, and docs, which moved linking into the API and added a real deployment target. Together they're a real storage layer, a real API, and somewhere to run it - plus a short tail of CI and deploy fixes that only showed up once it ran somewhere other than my laptop.

Splitting one script into two packages

The prototype was a single file that did OAuth, token storage, and the TrueLayer HTTP calls all in one place. The first move was mechanical but important: split it into a providers/ package (one module per data source - truelayer.py today, a Hargreaves Lansdown CSV importer as a stretch goal) and a storage/ package (currently just SQLite, but scoped so it doesn't have to stay that way). Neither package knows about the other's internals. providers/truelayer.py produces a flat list of transaction dicts; storage/sqlite.py doesn't care whether those dicts came from TrueLayer, a CSV file, or something else entirely.

This is the kind of restructuring that's easy to skip when you're the only user and the only one who'll ever read the code - but it paid for itself almost immediately, because it's exactly the boundary that later let me add providers list caching, connection metadata, and account balances as their own concerns without every change rippling through unrelated code.

A database, and the one line that makes re-running safe

The prototype's fetch overwrote transactions.json on every run. That's fine once; it's actively wrong for something meant to run nightly, because transactions inside the days=90 window would get re-fetched every time and you'd lose anything older than the window on each overwrite. SQLite fixed this (it arrived in the first backend commit as a flat storage.py, and only became the storage/ package in the later split), and the entire fix is one line in the schema and one clause in the insert:

CREATE TABLE IF NOT EXISTS transactions (
    bank TEXT NOT NULL,
    id TEXT NOT NULL,
    account TEXT,
    date TEXT NOT NULL,
    description TEXT,
    merchant TEXT,
    amount REAL NOT NULL,
    currency TEXT,
    bank_category TEXT,
    category TEXT,
    PRIMARY KEY (bank, id)
);

def save_transactions(conn, transactions):
    before = conn.total_changes
    conn.executemany(
        """
        INSERT OR IGNORE INTO transactions
            (bank, id, account, date, description, merchant, amount, currency, bank_category)
        VALUES
            (:bank, :id, :account, :date, :description, :merchant, :amount, :currency, :bank_category)
        """,
        transactions,
    )
    conn.commit()
    inserted = conn.total_changes - before
    return inserted, len(transactions) - inserted

PRIMARY KEY (bank, id) plus INSERT OR IGNORE means running the same 90-day fetch every night is completely safe: transactions already in the database are silently skipped, only genuinely new ones get inserted, and I get an (inserted, skipped) count back for free to log. No transaction gets duplicated, and none of the fetch logic needs to know or care that it's re-fetching data it already has. category sits in the schema unused at this point - a placeholder for rule-based categorisation I hadn't built yet, but wanted room for without a migration later.

(The key itself never changed, but what I put in it did: TrueLayer documents its transaction_id field as one that can change between requests - not the guarantee you want from a de-dup key - so a later commit switched the id column over to normalised_provider_transaction_id. That's covered in post 7.)

From a local callback server to real API endpoints

This was the change that actually mattered most for turning a script into a service. The prototype's wait_for_code() - a BaseHTTPRequestHandler that opens, catches one redirect, and closes - only works because a human is sitting at a browser on the same machine the script is running on. That assumption breaks the moment you want this running unattended on a server somewhere.

The fix was to stop treating "link a bank" as something the script does and start treating it as something the API does, with the browser redirecting to a real, always-listening route instead of a server the script spins up and tears down for one request:

router = APIRouter(prefix="/api/providers/truelayer")


@router.get("/link")
def link(label: str):
    return RedirectResponse(truelayer.build_auth_url(state=label))


@router.get("/callback")
def callback(code: str | None = None, state: str | None = None, error: str | None = None):
    if error:
        return HTMLResponse(f"Linking failed: {error}", status_code=400)

    tokens = truelayer.token_request({
        "grant_type": "authorization_code",
        "code": code,
        "redirect_uri": truelayer.REDIRECT_URI,
    })

    all_tokens = truelayer.load_tokens()
    all_tokens[state] = {"refresh_token": tokens["refresh_token"]}
    truelayer.save_tokens(all_tokens)

    return HTMLResponse(f"Linked '{state}'. You can close this tab.")


@router.get("/banks")
def list_banks():
    return {"banks": list(truelayer.load_tokens().keys())}

The label from the prototype's command-line argument became TrueLayer's OAuth state parameter instead - the one field in an OAuth redirect that's opaque to the provider and round-trips back to you unchanged, which makes it the natural place to carry "which of my accounts is this" through a redirect I don't otherwise control the shape of. /link redirects straight to TrueLayer's hosted consent page; /callback is where the browser lands after the user approves (or doesn't), and it's a real, permanent route - not a server that exists for one request and vanishes.

This meant linking a bank now worked from any browser, pointed at any deployment of the app - including a headless server with no display and no way to run webbrowser.open() at all. A tiny React component (LinkedBanks.tsx at this point, later renamed) replaced typing a bank name on a command line: pick a label, hit a button, get redirected to the bank, get redirected back.

Configuration that fails loudly

One thing that changed shape entirely in this move (the settings class itself landed in the first backend commit, and the linking commit extended it) was how the app got its settings. The prototype read straight from os.environ wherever it needed a value - os.environ["TL_CLIENT_ID"] sitting inline in whatever function happened to need it first. That's fine when there's one entrypoint and one developer who already knows what's required. It stops being fine the moment there's a FastAPI app, a CLI fetch command, and a systemd unit all potentially starting up with a slightly different environment underneath them - a typo in a unit file's Environment= line, or a .env that didn't get copied to the deploy target, should fail on startup with a clear message, not three requests into serving traffic.

The fix was centralising configuration into one pydantic-settings class, read once and cached:

class Settings(BaseSettings):
    model_config = SettingsConfigDict(env_file=".env", extra="ignore")

    tl_client_id: str
    tl_client_secret: str
    tl_is_sandbox: bool = True
    storage_dir: Path = Path("storage")
    public_base_url: str = "http://localhost:8000"

    @property
    def db_path(self) -> Path:
        return self.storage_dir / "db" / "finance.db"

@lru_cache
def get_settings() -> Settings:
    return Settings()

Every required field with no default - tl_client_id, tl_client_secret - raises a validation error the instant Settings() is constructed, which happens at import time, before the API has bound to a port or the fetch command has made a single HTTP request. Missing configuration now fails at the very first possible moment, with a message naming exactly which field is missing, instead of surfacing as a confusing downstream error the first time that particular code path happens to run.

Nightly, unattended, via systemd

With linking handled by the API and re-running fetch made safe by the de-dup key, the last piece was making fetch itself run without me remembering to. Two systemd units:

# finance-system-fetch.timer
[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

# finance-system-fetch.service
[Service]
Type=oneshot
User=finance-system
WorkingDirectory=/opt/finance-system
ExecStart=/opt/finance-system/.venv/bin/python -m finance_system.providers.truelayer fetch
ExecStart=/opt/finance-system/.venv/bin/python -m finance_system.storage.sqlite

(Two things in that unit didn't survive contact with the real server: a later commit (Fix remote users) switched User= to root on both units, and the second ExecStart later moved to python -m finance_system.storage - see post 17. The whole unit was eventually replaced by a script that loops over every user, which is post 10.)

Type=oneshot with two ExecStart lines runs them in sequence: pull fresh transactions to JSON, then load that JSON into SQLite - the prototype's fetch plus the new storage module, now on a timer instead of by hand. Persistent=true on the timer means if the machine was off at 3am, the missed run fires as soon as it's back up, rather than silently skipping a day. And the service unit's comment is worth keeping verbatim, because it states a constraint that's easy to forget once linking moved into the API: this unit only refreshes already-linked banks - linking a new one is still an interactive, browser-based step, and always will be, because OAuth consent fundamentally needs a human looking at a screen. Nightly fetch can never be the thing that links your first bank; it can only keep already-linked ones current.

Why systemd, not cron

Worth a short note since it's the kind of decision that's easy to make on autopilot: a nightly job is the textbook cron use case, and I considered it for about as long as it took to remember what debugging a failed cron job actually feels like. Systemd timers won for three concrete reasons, all of which mattered in practice, not just in theory. Persistent=true means a run missed while the machine was off or asleep fires as soon as it's back, which cron doesn't do at all - a laptop or a small home server isn't guaranteed to be up at 3am every single night, and I didn't want "missed the exact minute" to mean "missed the whole day." Logs go to the journal automatically (journalctl -u finance-system-fetch), searchable and timestamped, rather than needing me to redirect output to a log file by hand and remember to rotate it. And a .timer/.service pair is two declarative files I can read top to bottom and understand completely, rather than a crontab line whose syntax I have to look up fresh every time I need to change the schedule. None of that is exotic - it's just systemd being the better-fitted tool once you actually compare them on a target that already runs systemd anyway, which the deploy target here (a Proxmox LXC container) does.

Shipping it: Jenkins, rsync, and the first things a real server broke

A service nobody can deploy is still a script. The day after that second commit there was a run of small commits - feat: fixup jenkinsfile, Fixing pre-requs, ci: fixing tests in jenkins, ci: fix deploy script - that built the pipeline the rest of the project ships through. The Jenkinsfile is a multibranch pipeline with five ideas worth recording:

  • Tests run in parallel, and fail loudly on drift. The backend stage runs uv sync --locked (it errors if pyproject.toml and uv.lock disagree, rather than quietly re-resolving) then pytest with coverage; the frontend stage runs npm ci, npm test and npm run build. Both publish JUnit XML.
  • Only main deploys. The Deploy stage is guarded by when { branch 'main' }, so a feature branch gets tested but never touches the server.
  • The agent is pinned like local dev is. Node comes from nodenv and frontend/.node-version rather than a Jenkins tool installation, so CI and my laptop resolve the same version - which matters, because a Node that's too old breaks the Vite bundler with a native-binding error rather than a clear version message. uv is bootstrapped inline.
  • Fail-loud settings need fake values in CI. The Settings class above raises at import time if TL_CLIENT_ID/TL_CLIENT_SECRET are missing, and a clean checkout has no .env, so the pipeline sets obviously fake values in the backend stage's environment. The tests mock every real TrueLayer call, so nothing ever uses them - the cost of "fail on startup" is that CI has to say so explicitly.
  • Deploy is rsync, not a registry. deploy/deploy.sh rsyncs the checkout to /opt/finance-system on the LXC container, then runs deploy/remote-setup.sh there to install the systemd units and nginx config. .env and /storage/ are excluded from the --delete sweep so a deploy can't wipe credentials or data (a later fix added the .env exclusion and anchored the storage/ one to the repo root).

The first real server then did what real servers do. One fix (fix sqlite) added check_same_thread=False to the connection: FastAPI runs sync dependencies and route handlers in a worker thread pool, so the connection opened in a request's dependency isn't guaranteed to be used on the same thread, and SQLite's default refuses that. It's safe here because every request opens and closes its own connection, and the commit ships a regression test that uses a connection from another thread. Another commit moved /health to /api/health so nginx's single /api/ proxy rule covers it. A third commit cached TrueLayer's bank list at startup so the link-a-bank dropdown doesn't call out on every page load. None of these is interesting alone; together they're the reminder that "works locally" and "runs on a server" are different claims.

What "service" actually meant here

None of this was a big-system rewrite. It was: separate the code that talks to TrueLayer from the code that talks to SQLite; replace a script's temporary local server with the API's own permanent routes; make re-running the fetch idempotent with one primary key and one SQL clause; and hand the whole thing to systemd instead of my own memory of "did I run this recently." Small, individually boring changes. But they're the difference between "a script I run when I remember to" and "a thing that just has correct data in it every morning," which is the actual bar for a personal finance tool to be worth using.

What didn't exist yet, after this commit, was anywhere to look at that data beyond sqlite3 finance.db. That's next: turning the placeholder frontend into an actual multi-page app.

Previous: ← The first prototype · Next: Building the frontend →