Bank Transaction Insights

[!NOTE] About this project. This is jax79sg's means of learning and understanding the challenges of building software with a coding agent. Much of this entire repository — the code, the tests and the documentation — was made with Claude Code, using Anthropic's Claude Opus and Sonnet models. The aidlc-docs/ folder keeps the trail: what was asked, what was decided and what was built, change by change.

How it was checked. Every change is built behind automated tests and security scans, and the findings are tracked: since the first push (3 Aug 2026), 65 vulnerabilities have been fixed and 25 findings are still open, all of medium severity (figures as of 9 Oct 2026; one more high-severity advisory, in a development-only build tool, has no patch yet; the breakdown, and what this is not, is in How this was reviewed and hardened).

A self-hosted, single-user web app that pulls your bank statement PDFs from a private Google Drive folder, extracts transactions with an LLM, auto-categorizes them (learning from your corrections over time), and gives you a filterable transaction table plus financial dashboards. Fully containerized — one docker-compose up runs the whole thing on your own machine.

📘 User Guide — a screenshot walkthrough of every page (login, top bar, dashboard, transactions, Ask AI, ingestion, review, settings). Every release keeps its own copy of the guide — see the list of releases; the version you are running is shown in the app's top bar and under Settings. Screenshots use AI-generated sample data, not real transactions. To cut a release, see RELEASING.md.

How this was reviewed and hardened

Written down so a reader can judge it, including the parts that are not flattering. Figures are a snapshot from GitHub's own security records and the repository on 9 October 2026; the live numbers are on the repository's Security tab.

Reviewed

• Approval gates, on the record. The project follows a written workflow (AI-DLC): requirements, designs and code-generation plans are written first and approved by jax79sg before code is generated. Every request, answer and approval is logged verbatim in aidlc-docs/audit.md (457 logged inputs). Some approvals were a standing instruction to carry on, and the log shows which.
• Pull requests with automated checks. From 21 Aug 2026 every change has been merged through a pull request (28 merged so far; none committed straight to main since, the last two direct commits having been made that morning, before the first pull request was opened). Each runs about 15 checks: lint and type checks, the test suites below, Docker builds, CodeQL, gitleaks, GitGuardian, and dependency scans. Automated checks and scanning were only switched on on 17 Aug, two weeks after the first push, so the first two weeks had none.
• Tests: about 1,800 (1,823: 257 database, 734 ingestion worker, 467 API, 327 frontend, 38 release tooling), run against a real PostgreSQL, with property-based tests on pure logic and four browser end-to-end specs run nightly.
• The tests were tested. For recent features, code was deliberately broken in a scratch copy to confirm a test notices (for example 72 such checks on the Costs page and 42 on release numbering); each time one went unnoticed, a test was added. The results are in aidlc-docs/.

Hardened (in the app)

• 57 of the API's 61 operations need a login. The four that don't are the health check, the release number, sign-in, and the address Google redirects back to during Drive connect (the API's own documentation pages are public too). Passwords are stored as bcrypt hashes and sessions expire.
• The API accepts browser requests only from the addresses listed in FRONTEND_ORIGIN.
• The Settings page can change only an allow-list of values, and never shows secrets.
• Destructive steps are guarded: removing a duplicate statement lists exactly what will be deleted and asks first; the one-time re-ingestion needs a typed confirmation and a backup; the newest database migrations refuse to downgrade over data they would destroy.
• Secrets live in a git-ignored .env; GitHub secret scanning and push protection are on, and CI runs gitleaks with a custom rule for password hashes.

What it took (Claude Code usage, as of 10 Oct 2026)

Building this with Claude Code from 15 Aug to 10 Oct 2026 used about $760 at Anthropic's API list prices: about 6,000 requests in 11 sessions; 71% of the cost is Claude re-reading the conversation so far from cache on every turn. Treat it as an estimate and a floor, not a bill: it cannot see calls Claude Code makes that are not in its transcripts (on the one session that could be checked, Claude Code's own figure was 2.5% to 9.0% higher), the advisor model's tokens, or anything before 15 Aug, twelve days after the first commit. The ledger, the method and every caveat are in aidlc-docs/claude-usage/; python scripts/claude_usage.py update refreshes them.

Vulnerabilities since the first push (dependency and code scanning began on 17 Aug 2026, so nothing before that was scanned)

65 fixed, 25 open, none of them high or critical. Fourteen of the dependency fixes landed between 17 Aug and 2 Oct (thirteen of them within six days of the first alerts: the vitest and vite CVEs, the PyJWT and clearml set, nanoid). The other 49, including 4 critical and 22 high, were fixed on 9 Oct through four pull requests (#37, #38, #39, #40): PyJWT and urllib3 in the Python lock files, fsspec in model training, and vitest 4 for the frontend. Dependabot's automatic security updates are off and the dependency scan in CI is report-only, so these were fixed by hand, after most of those alerts had been open for two to seven days.

Two things worth knowing about those fixes. The container images install their packages when they are built, and the running API and worker containers were already on the patched PyJWT and urllib3, so nothing had to be redeployed; the lock files decide what CI and a developer's machine use. And one fix is a compromise: model training pins a newer fsspec than the datasets library declares it supports (mlx-tune, which pulls datasets in, has no release that allows a newer one). The unit tests and a smoke test pass, but a real fine-tuning run was not tried against it.

What this is not

• It is not clean. 16 medium dependency alerts and 9 medium CodeQL warnings (workflows with no explicit permissions block) are open. A high-severity advisory in braces, which the frontend's Tailwind 3 build tooling pulls in, has no patched release; GitHub auto-dismissed it, it is build tooling that does not ship in the app, and the real fix would be moving to Tailwind 4, which has not been done.
• It has had no independent security review or penetration test, and the workflow's security-baseline rules were not enabled: the hardening above comes from the scanners, the tests and jax79sg's approvals, not from a formal security design review.
• "Fixed" means GitHub's records or the commits say so. Open findings are still open.
• It is a single-user app designed to run on your own machine or network. Treat it as a learning project.

What it does

• Manually-triggered ingestion: you click "Run Ingestion" in the UI; nothing happens automatically or on a schedule
• Layout-adaptive extraction: reads PDF statements (including scanned/image-based ones) via Gemini's vision input — no per-bank parser code needed
• Learned categorization: matches new transactions against your past categorized ones first (fuzzy similarity, manual corrections weighted highest); falls back to an LLM classification constrained to your category whitelist; marks anything it's not confident about as UNSURE
• Duplicate-proof: re-running ingestion on the same statements never creates duplicate transactions
• Multi-currency: converts everything to SGD for dashboards, using the statement's own printed conversion when available, a public FX-rate API otherwise — original amounts always retained
• Dashboards: category spending trends, income vs. expenses, per-bank breakdowns
• Ask AI: ask a plain-language question about your own transaction history (e.g. "is this $33,000 outflow likely a transfer to my credit account?") and get an answer grounded in your real transaction data — scoped to a date range you pick, or your whole history. Each transaction also has a one-click "Ask AI" shortcut with a suggested question pre-filled.
• Costs: what the app has spent on Google's Gemini models (statement extraction, categorization, embeddings, Ask AI), in US dollars, by day, week or month, optionally grouped by purpose or model — priced from each call's token count and the prices in Settings

Architecture

Four containers, each independently deployable:

api-service and ingestion-worker never call each other directly — they coordinate only through shared database rows (a queued "ingestion run" or "recategorization job"), so either can be restarted or redeployed independently.

Full design rationale and decision history: aidlc-docs/ (see audit.md for the complete build log, including every real bug found by actually running the code).

Prerequisites

• Docker and Docker Compose (v2 — the docker compose command, not the standalone docker-compose binary)
• A Google Cloud project with an OAuth 2.0 Client ID (type "Web application")
• A Google AI Studio API key (Gemini)
• An OpenRouter API key (used with the free-tier openrouter/free router by default — no cost)

Quick Start

1. Configure environment

cp .env.example .env

Edit .env:

2. Build and start

docker compose build
docker compose up -d
docker compose ps    # wait until all 4 services show "healthy" (~30-60s)

3. Create your login

There's no self-registration (single-user app). Create your account with a single command, run entirely inside the api-service container (it already has the DB credentials and the hashing code — no need to export anything from .env into your own shell, and no bcrypt hash ever gets typed into a shell string, which avoids $-quoting issues):

docker exec transactagent-api python3 -c '
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from transactagent_db.migrate import build_database_url
from transactagent_db.models import User
from api_service.auth.security import hash_password

engine = create_engine(build_database_url())
with Session(engine) as session:
    session.add(User(username="your-username", password_hash=hash_password("choose-a-real-password")))
    session.commit()
print("User created.")
'

Edit your-username and choose-a-real-password before running. Keep the single quotes around the whole script (outer '...') — they're what stop your shell from trying to interpret anything inside it; use plain characters in the password (avoid embedding a literal ' in it).

4. Seed the category whitelist

docker exec transactagent-api python3 -m transactagent_db.seed_categories

Safe to re-run — only inserts categories that don't already exist. Edit the whitelist afterward from Settings in the app, or by editing database/src/transactagent_db/seed_categories.py before re-running.

5. Open the app

http://localhost:8787 — log in with the username/password from step 3.

6. Connect Google Drive and run your first ingestion

Settings → Connect Google Drive → complete the Google consent screen → Ingestion → Run Ingestion. Watch live progress; check Transactions afterward for the extracted rows, and Dashboard for the charts.

Stopping / Resetting

docker compose down        # stop containers, keep your data
docker compose down -v     # also wipe the database volume — full reset

Data persists in ./data/postgres (bind-mounted, gitignored) between docker compose up/down cycles.

Configuration Reference

See .env.example for the full list with inline explanations. A few worth knowing about:

• FRONTEND_ORIGIN and API_BASE_URL must stay in sync with the frontend's/API's actual host ports — CORS is locked to FRONTEND_ORIGIN exactly.
• GEMINI_MODEL must support image input (statement pages are always sent as images) — the default gemini-3.5-flash-lite does.
• OPENROUTER_MODEL should stay a free-tier model/router if you want to avoid charges — the default openrouter/free auto-routes across free-tier models.
• OPENROUTER_BASE_URL points the categorization LLM client at any OpenAI-compatible endpoint, not just OpenRouter — e.g. a local model server. If that server runs on your Mac (not in a container), ingestion-worker must reach it via host.docker.internal, not 127.0.0.1/localhost, since the worker itself runs inside a container.
• Ask AI (api-service) reuses GEMINI_API_KEY/GEMINI_MODEL from the ingestion-worker settings above — one key, shared by both. AI_ASSISTANT_MAX_TRANSACTIONS (default 3000) caps how many transactions are sent as context per question.
• Ports default to 7878 (API) and 8787 (frontend) — chosen to avoid common local-dev port collisions; change them in docker-compose.yml if needed (and keep FRONTEND_ORIGIN/API_BASE_URL/GOOGLE_OAUTH_REDIRECT_URI in sync).

Development

Each unit can be built and tested independently, without the full Docker stack:

# Backend units (each needs Docker running, for testcontainers' disposable Postgres)
cd database && pip install -e ".[test]" && pytest
cd api-service && pip install -e "../database" -e ".[test]" && pytest
cd ingestion-worker && pip install -e "../database" -e ".[test]" && pytest

# Frontend
cd frontend && npm install && npm test && npm run build

Full instructions, including integration-test scenarios and the complete list of bugs found and fixed by actually running this project end-to-end: aidlc-docs/construction/build-and-test/.

Troubleshooting

api-service or ingestion-worker unhealthy / restarting — check docker compose logs api-service (or ingestion-worker); usually a missing/wrong .env value, or the container started before database was ready (it retries automatically).

Frontend loads but every action fails — check that FRONTEND_ORIGIN (in api-service's env) exactly matches the URL you're loading the app from; a mismatch is silently rejected by CORS.

"Ingestion run already in progress" but nothing seems to be running — check Ingestion page run history; if a run is genuinely stuck, check docker compose logs ingestion-worker for errors.

More detail: aidlc-docs/construction/build-and-test/build-instructions.md.

Project Structure

database/            Unit 1 — schema, migrations (SQLAlchemy + Alembic), no runtime service
api-service/          Unit 2 — FastAPI REST API
ingestion-worker/       Unit 3 — background worker (Drive, extraction, categorization, FX)
frontend/                Unit 4 — React SPA
docker-compose.yml         Orchestrates all 4 services
.env.example                Full configuration reference
aidlc-docs/                   Design docs, requirements, decision history, audit trail