# IDEA.md – an expense tracker of your own for bank statements This is a self-contained brief. Give it in full to Claude Code (or a similar tool) to build an implementation of your own. It draws on what was learned building Expenser, a desktop program for Windows and macOS written in Rust with Claude Code. ## 1. What to build A desktop program that reads **the bank's CSV exports** into a **local database**, categorizes the transactions automatically and shows where the money goes. The problem: a spreadsheet per month gets too heavy to maintain, and what you want to know – for example how grocery costs change year by year – is hard to get at. Ready-made services exist, but they require handing over your transactions. Here everything stays on the computer: no server, no account, no bank connection. The most important part is **your own categories, set automatically** on recurring shops and payments. Everything else – charts, trends, budget, subscriptions – depends on the categories being right. The program must be able to: - **Import** one or more CSV files from a menu or by dragging them into the window. Recognize the bank's formats, repair broken characters, skip duplicates and a file that has already been imported. Show an import history; an import can be undone. - **Categorize** with rules: conditions on text or reference (*contains*, *equals*, *starts with*, *regex*), direction (in/out) and an amount range. Rules are tried in priority order. Rules can also rename the payee. When the user sets a category by hand, a rule is suggested for all transactions from the same payee. Categories set by hand are never overwritten by rules. - **Categories** with one level of subcategories, a color and a kind: *Expense*, *Income* or *Transfer*. Transfers never count in any statistics. Ship a starter set (Expenser has 17 categories and 260 rules for common Swedish shops). - **Transaction list**: a virtualized table with free-text search, filters (period, category, in/out, uncategorized), sorting, category set directly in the row, multi-select, notes, splitting a transaction across several categories, and export of what's shown to CSV. - **Overview** for a chosen period (month, quarter, year, 12 months, all, custom): spending, income, net and savings rate compared with the previous period; bars of income against spending per month; spending per category as a donut and a list; the largest payees; balance over time. Clicking a category, payee or month opens the transactions. - **Trends**: stacked spending per category and month, one line per chosen category, year over year, and a table with categories as rows and months as columns. - **Budget** per category and month, with a bar that turns red when over budget and the 3- and 12-month averages as guidance. - **Recurring payments**: found automatically, with cost per month and year, last and next charge, and whether they have stopped. The user can hide and merge them. - **Several accounts**: each transaction belongs to an account. Transfers between your own accounts are recognized and never counted. Overview, trends, budget and list show all accounts or one. - **Several profiles**: completely separate sets, one database per profile, which can be open in separate windows. - **Backup** of the open profile's database. **Not** included: a bank connection (banks offer no open API to private individuals without an intermediary), multiple currencies, envelope budgeting, sync between computers, a combined view across profiles, encryption. ## 2. Stack and architecture What **matters** is marked; the rest can be swapped. - **Rust** (edition 2024, ≥ 1.89 for `File::try_lock`), a Cargo workspace with a UI-free core and a window program. - **Important: a core with no interface** – CSV import, database, text normalization, rules, reports, recurring payments, transfers, profiles. It's tested without a window, and some tests run against the user's real bank files when they exist (and are skipped otherwise). The bank files are never checked in. - **Important: SQLite** (via `rusqlite`). One file in the user's data directory, no server, the same on both platforms, and the aggregations become SQL instead of loops. Compiled in on Windows; the system's on macOS. - **Important: amounts as integers in öre/cents (`i64`)**, never floating point except when drawing. - **eframe/egui** with **egui_plot** for bars and lines; the donut chart is drawn with egui's own painter. Can be swapped. - `chrono` for dates, `regex` for rule conditions, `unicode-normalization` to fold away diacritics in comparisons. - **The menu as data** (egui in the window on Windows, the system menu bar on macOS via `muda`), and keyboard shortcuts from a single source. Menu and keyboard produce the same `Action` through a single `apply`. - **Several windows are several processes**: *Open in new window* starts the program again with `--profile `. Simpler than several viewports, and one crash doesn't take the others with it. - Packaging: Windows cross-compiled from WSL with `cargo xwin` (SQLite is then built as C and needs `llvm-lib` in `PATH`), an NSIS installer and a portable zip; macOS `aarch64-apple-darwin`, `.app` and dmg. Data model (simplified): ``` accounts id, name, number imports id, file_name, sha256, format, imported_at, rows_total, rows_added, account_id transactions id, date, booked, amount, text, raw_text, reference, balance, payee, category_id, category_source, rule_id, note, import_id, dedupe_key, account_id, transfer_account_id, pair_id splits id, txn_id, category_id, amount, note categories id, name, parent_id, color, kind, budget, sort rules id, pattern, op, amount_dir, min_amount, max_amount, category_id, rename, priority, enabled ``` `category_source`: 0 none, 1 set by a rule, 2 set by hand. A view (`entries`) merges unsplit transactions and splits, so the reports only see one kind of row. Reports sum subcategories into their parent. ## 3. Decisions that turned out to matter **Study the bank's real files first.** Everything hard in this project came from the files, not the program. Read every format, count rows, look for broken characters, overlaps and oddities before building anything, and write it down. **Recognize the format by the number and position of columns, not their names.** In one of the bank's formats, å, ä and ö were replaced by U+FFFD in the file itself, header row included. Skip introductory lines (`* Transaktioner Period …`) before the header. Guess the delimiter from the header (`;` or `,`). Decode Windows-1252 when the file isn't valid UTF-8. Expect several formats from the same bank: date + text + amount with Swedish-style amounts (`"−4 000,00"`, U+2212 as minus, a non-breaking space as thousands separator); a format with row number, booking, transaction and value date, reference, description, amount and balance (dot as decimal separator, newest row first); and one with clearing and account number on every row. **Repair the text, but never let the repair affect duplicate detection.** Card receipts write `#` for Ä and `¦`/`@` for Ö, and the bank truncates the text after 16 characters. Repair against a built-in word list plus every full text already in the database: look up whole words, words inside words and the beginnings of known words. Keep both the repaired and the raw text. **Duplicate detection that counts occurrences.** The key is *(account, transaction date, amount, text without non-ASCII characters)*. For each key, only as many rows are added as the file has *beyond* those already present. Then the same purchase in two overlapping files becomes one purchase, while two identical purchases on the same day in the same file stay two. A file with the same SHA-256 as an earlier import is skipped entirely. **Rules match on folded text.** Lowercase, diacritics removed (`Hemköp` = `HEMKOP`), whitespace collapsed – for both pattern and text. Match against the text **or** the reference. The first rule that sets a category wins, and the first that sets a payee name wins. The payee key is the text without a trailing store number; it's used to suggest a rule when the user categorizes by hand. **The account number decides what's an internal transfer, not the text.** The reference on a transfer is the counterparty's account number (clearing and account run together). A transaction whose reference is one of your own accounts is internal and never counted, whatever its category – not even when a single account is shown. Accounts that aren't imported (a savings account) are added with just the number. Suggest numbers the user often transfers to, but not Swish phone numbers. **Learn the card account's number from the data.** The card account's own export has no account number. But the other account's transfers point at an unknown number, and when at least four out of five of them (and at least five) have a counterpart on the card account with the opposite sign within three days, that's the card account's number. Then pair the legs (same amount, opposite sign, ±3 days, closest first) and warn about a leg without a pair when the other account has data that day: an import is missing, or the money went to someone else. **Recurring payments** per payee (spending, not transfers): at least three charges; the median interval within one of the windows week (6–8 days), month (26–35), quarter (85–95) or year (350–380); at least 60 % of intervals within ±25 % of the median and at least 60 % of amounts within ±20 % of the median amount. Cost per month from the median amount and the cadence; the next charge on the same day next period; stopped after 1.5 missed periods. Group by the payee name after rules, not by the bank's text. **A profile is a database of its own.** Nothing is shared between profiles, so no profile column is needed in every table. The first profile stays where the database always was (no file moves); new ones in `profiles/.sqlite`. Names in `profiles.toml`, re-read before every change since several windows may write to it. A profile can only be open in one window: lock with `File::try_lock` on `.lock` – the operating system releases the lock when the process dies, so a stale lock can't happen. **Developer hooks from day one**, so an agent can review the interface with no one at the screen: - `_DATA_DIR=dir` – a separate data directory (must exist) - `_IMPORT=file;file` – import at start - `_TAB=overview|transactions|trends|budget|recurring|categories|rules|imports` - `_WINDOW=1240x820`, `_SCREENSHOT=file.ppm`, `_SCREENSHOT_DELAY=N` - `_DEBUG_STATE=…` – steps in order: `period=last12`, `trend=lines|yoy`, `search=…`, `select=…`, `set-category=…`, `create-rule`, `year=2026`, `profile=p2` … - `_DEBUG_MENU=File`, `_DEBUG_POINTER=600x300` (hold the pointer there so hover shows) ## 4. Pitfalls - **Don't turn "transfer via internet" into a transfer rule.** A transfer to someone else's account is spending. Let the account number decide (see section 3), and remove such text rules on upgrade. - **Don't forget the account in the duplicate key.** Otherwise the same purchase on the same day on two accounts is merged into one. - **Don't reject a bill because its amount varies.** An electricity bill is charged every month but varies a lot; with the ±20 % amount requirement the whole group was rejected, and a random subgroup was shown as stopped. Accept a group with a steady cadence (at least 6 charges and 4 of 5 intervals within ±25 %) even when the amount varies, mark it *varies* and let it cost the average of the last 12 charges. Exclude yearly payments – there, the amount is what tells a bill from a trip. Split by amount only when the cadence isn't steady. - **Expect subscriptions to change name** on the statement over the years. Let the user merge them; that should create one rename rule per text so all charges get the same payee. - **Don't use `INSERT OR REPLACE` on a row that other tables reference with `ON DELETE CASCADE`.** It deletes the row and inserts a new one, and the cascade deletes everything that belonged to it. Use `INSERT … ON CONFLICT DO UPDATE`, and test saving the parent after the children have been added. - **Two windows on the same database show stale data** if the cache hangs on a counter only the own connection increments. Lock the profile. - **Don't put the data directory on a network or 9p path in tests.** `File::try_lock` fails on `\\wsl.localhost\…` ("Incorrect function"); use a path on the Windows drive when running the Windows binary from WSL. - **Check the Windows build with `cargo xwin check`, not `cargo check --target`.** SQLite is compiled as C and otherwise stops at `failed to find tool "lib.exe"`. - **Don't put the system Cut/Copy/Paste in the macOS menu**; the native menu takes ⌘C/⌘V away from text fields. - **`cargo fmt` reformats the whole crate**, not just the files you touched. ## 5. Requirements and measurements Benchmarks from Expenser, on real bank files: - Importing just over 7,000 transactions from four files in two formats: **121 ms** in the Windows binary. Single files: 5,125 rows in 74 ms, 932 in 19 ms. An already imported file: 0 ms. - The overlap between two formats: exactly the overlapping rows (2) were removed; no broken å/ä/ö left after repair. - The default rules categorized **82.4 %** straight away. - With a second account: 0 new duplicates, import 176–182 ms regardless of order; all internal transfers paired. - Windows binary 14.6 MB, installer 6.7 MB. - Importing in a different order must give the same count and the same total. ## 6. How it's verified - **Unit tests in the core** with small synthetic files for every format: amounts (Swedish characters, minus, thousands separator), decoding, repair, duplicate detection (overlap versus identical purchases), rules (folding, priority, manual categories not overwritten), recurring payments (week, month, year, varying amount, stopped), transfers (learning the number, pairing, warnings), profiles (names, the lock) and database schema upgrades. - **Tests against the real files** when they exist: row counts, duplicates in the overlap, no broken characters left, the same result in both import orders. - **Lint and cross-check**: `cargo clippy --workspace --all-targets` with no warnings, `cargo test --workspace --release`, `cargo xwin check` for Windows. - **The interface is reviewed with screenshots** from the real Windows binary using the developer hooks. Use **made-up data** for anything shown to others – never the real statements. - What needs a human – the installer, the file dialog, drag and drop, macOS – becomes a separate verification task. ## 7. Way of working - **Plan first**: go through similar programs (Actual Budget, Firefly III, HomeBank, Monarch, Copilot, MoneyMoney) to see what's usually included, and the user's bank files to see what's actually needed. Write a plan with milestones and what's deliberately left out. - **Ask about what only the user knows**: which account numbers are their own, whether a recurring deposit is income or a transfer, what a payee is. List the questions together, with what the data already says. - **One task at a time**, with the measurement in the closing comment. - **The bank files are private**: they're never checked in, and never appear in screenshots or texts that are shared. ## 8. The prompt > Build a desktop program for [Windows and macOS] in [Rust with eframe/egui and SQLite] that reads my bank's CSV exports into a local database, categorizes the transactions automatically with rules that learn from what I categorize by hand, and shows where the money goes: overview, transaction list, year-over-year trends, budget and recurring payments. Nothing may leave the computer. > > My bank files are in [directory]. They are private: never check them in and never show them in screenshots. > > Read all of IDEA.md below first. It describes the features, the architecture, the decisions that turned out to matter and the pitfalls to avoid. > > Work like this: > 1. Study my bank files: formats, columns, character encoding, broken characters, overlaps. Go through similar programs. Write a plan with milestones. > 2. Build the core without an interface first, with tests against small synthetic files and against my real files when they exist. > 3. Build the developer hooks (data directory, import at start, tab, screenshot, debug steps) early, and review every interface change with a screenshot using made-up data. > 4. Ask me about what only I know, for example [which accounts are my own, what recurring deposits are]. > 5. Measure the import and report time, row count, duplicates and share categorized. > > My bank: [name, and which export formats it provides]. My categories: [list, or use a starter set for common shops in [country]]. > > [paste IDEA.md here]