expense-tracker

Imported from Lalit-Patil-07/expense-tracker (AGENTS.md). Install upstream with npx skills add Lalit-Patil-07/expense-tracker. Copyright stays with the author.

AGENTS.md

Guidance for AI agents and human contributors working in this repository. Read this before making changes. It captures the architecture, the invariants you must not break, and the one hard runtime constraint that governs every decision here.

If anything below drifts from the code, the code wins — but please update this file in the same PR.

0. The one hard constraint: this runs on Cloudflare Workers

Everything in this project must run on the Cloudflare Workers runtime. This is not a preference; it is the platform. Before adding a dependency, an API call, or a new subsystem, ask: does this run inside a Workers isolate? If not, it does not belong here.

Concretely, that means:

• No Node.js runtime assumptions. There is no persistent filesystem, no process lifecycle, no long-running background threads, no listening sockets, no __dirname. nodejs_compat is enabled (see wrangler.jsonc), which polyfills a subset of Node built-ins — but that is a compatibility shim, not a Node server. Do not reach for fs, net, child_process, etc.
• Prefer Web Platform APIs. They are what the runtime natively provides. This codebase already relies on crypto.subtle, TextEncoder/TextDecoder, fetch, File/FormData, Request/ Response. Use these rather than Node equivalents. (Example: the dedup hash uses crypto.subtle.digest("SHA-256", …) in src/pipeline.ts, not Node's crypto.)
• Requests are short-lived and CPU-bounded. Work happens inside a single request. No cron-less background work, no in-memory state that must survive between requests (there is none — isolates come and go). All durable state lives in D1.
• Storage is D1 (SQLite at the edge), full stop. No local files, no external DB drivers, no connection pools. Data access is c.env.DB.prepare(...).bind(...).
• Dependencies must be Workers-compatible. Anything pulling in Node core modules, native addons, or a persistent server will not deploy. Keep the dependency set tiny (currently just Hono). The frontend uses a CDN <script> for Chart.js — there is no bundler/build step, so frontend deps are <script> tags, not npm packages.

When in doubt, check the Workers runtime API docs before adding anything.

1. What this is

A multi-account personal-finance dashboard. You upload bank/credit-card statement files; the Worker parses, cleans, deduplicates, categorizes, and stores each transaction in D1, plus a statement-level balance/EMI snapshot for credit cards. Read endpoints aggregate that data into JSON that a vanilla-JS dashboard renders with Chart.js.

Two questions drive the whole design: "where does my money go?" (spend analytics) and "where do I stand?" (balances + net worth across accounts). See README.md for the user-facing overview.

2. Repo map

src/
  index.ts     Hono app. All routes + shared query helpers. This is the API surface.
  parsers.ts   Format detection & statement parsing. Pure functions, no DB.
  pipeline.ts  normalize date → clean merchant → SHA-256 dedup hash → categorize. Pure, no DB.
public/
  index.html   dashboard markup (six views)
  app.js       fetches /api/*, renders Chart.js. Vanilla JS, no framework/build.
  style.css
migrations/    D1 schema, applied in filename order (0001 → 0004). Additive.
samples/       synthetic statements for local testing (see samples/README.md)
wrangler.jsonc Worker + D1 + static-assets config

The clean separation to preserve: parsers.ts and pipeline.ts are pure (string in, structured data out — no DB, no Env, no I/O). All persistence lives in src/index.ts. Keep it that way; it is what makes the parsers unit-testable and the pipeline reusable across formats.

3. How data flows

Ingestion — POST /api/upload (src/index.ts):

1. Read the file (multipart file field, or raw body + ?filename=). Enforce MAX_UPLOAD_BYTES (default 5 MB).
2. deriveAccountFromFilename(filename) → an optional account hint (for bank exports that carry no account number inside the file).
3. parseStatement(text, { accountHint }) → { format, rows, account, errors }. Format is auto-detected (see §5). If zero rows, log a failed import and return 422.
4. Load enabled category_rules, then cleanAndCategorize(rows, rules) → normalized, hashed, categorized CleanTxn[].
5. Insert the imports audit row first (so transactions can FK to it).
6. INSERT OR IGNORE transactions in batches of 50 via DB.batch(...). meta.changes > 0 ⇒ inserted; 0 ⇒ deduped.
7. Best-effort, in try/catch so they can never fail a committed import:
   • register/refresh the account in accounts (idempotent upsert, ON CONFLICT(account_key));
   • snapshot the statement balances + EMIs for credit cards via parseHdfcStatementMeta(text) (idempotent upsert on (card, statement_date); loans are replaced per statement).
8. Return counts (rowsParsed, inserted, duplicates) + any errors.

Read — GET /api/summary/*, /api/accounts/*, /api/balances/*, …: build a parameterized SQL query, aggregate in SQLite, return JSON. public/app.js fetches and charts it.

4. Data-model invariants — do not break these

These are the rules the analytics correctness depends on. Changing one changes reported numbers.

• Dedup is a DB-level guarantee. transactions.txn_hash is UNIQUE and inserts are INSERT OR IGNORE. The hash = SHA-256(account_key | when | description(trimmed, UPPER) | amount.toFixed(2) | direction), where when is the datetime if the source gives a time-of-day, else the date (computeHash in src/pipeline.ts). If you change the hash inputs, every existing row's identity changes and re-uploads will duplicate. Don't, unless you also plan a migration.
• account_key is the canonical account id (Card_6081, Savings_7464). Everything joins on it. account_type ∈ {credit_card (liability), savings (asset)}. In-file identity (a card's Card No:) always wins over the filename-derived hint.
• Spend = debits, with money-movement categories excluded by default. TRANSFER_CATEGORIES = ["Transfers", "Credit Card Payment", "ATM/Cash", "Investments"] (src/index.ts) are dropped from spend/category analytics unless includeTransfers=1. This prevents a ₹2L transfer from dwarfing real spend and stops card-bill payments being double-counted. Editing this list re-classifies what counts as spend — it is a policy knob, not a schema change.
• Balances are sourced, never summed from transactions. Credit-card current/previous balances come from the statement's authoritative Account Summary (statements table). Savings balances come from the per-row running balance_after; opening balance is derived as balance_after + signed_amount of the earliest row. Do not "simplify" these into SUM(amount).
• signed_amount = +debit / −credit. Used for the savings opening-balance derivation. Keep the sign convention.
• Uploads only ever add. The sole delete path is an explicit DELETE /api/imports/:id rollback (removes that import's transactions only). Never introduce an upload path that overwrites or bulk-deletes.

5. Statement formats & how detection works

parseStatement tries parsers in order and returns the first with rows (src/parsers.ts):

1. tryParseHdfcPipeStatement — HDFC credit-card export, ~|~-delimited, header line starts Transaction type~|~. Identity from the Card No: line.
2. tryParseHdfcSavingsCsv — comma CSV whose header has both narration and closing balance. Captures the running Closing Balance and tags rows savings. Identity from the filename hint. Ordered before the generic parser because it is a superset of it.
3. tryParseGenericCsv — any CSV with date + description + (amount | debit/credit) columns, matched via HEADER_ALIASES.

Order matters: more-specific parsers first. parseHdfcStatementMeta is a separate pass over the same text that extracts balances/limits/EMIs from the card statement's summary blocks; it is deliberately position-tolerant (locates values by column label, not fixed offset).

6. Coding conventions

• TypeScript, strict, ES modules. No any slipped in to dodge a type. tsc --noEmit (npm run typecheck) must pass — Wrangler bundles the Worker, so there is no separate build.
• SQL is always parameterized. Every value goes through .bind(...) with ? placeholders — never string-interpolate user input into SQL. Dynamic WHERE clauses are assembled from a fixed set of column names plus bound params (see accountFilter/transferFilter and the clauses[]/params[] pattern in src/index.ts). Follow that pattern for new filters. No ORM.
• Hono for routing. API routes live under /api/* (CORS enabled); the GET * fallback serves static assets from the ASSETS binding. Keep that fallback last.
• Parsers/pipeline stay pure. No DB or Env access in parsers.ts/pipeline.ts.
• Frontend is vanilla. Plain DOM + fetch + Chart.js from CDN in public/. No framework, no bundler, no npm frontend deps. Money is formatted with toLocaleString("en-IN") (Indian digit grouping, ₹). Match the existing style rather than introducing a build step.
• Comments explain why, not what. The existing code comments the reasoning behind non-obvious choices (dedup timestamp granularity, transfer exclusion, best-effort try/catch). Match that density and intent.
• Resilience over strictness on ingest. A malformed row is skipped and recorded in errors, never fatal to the whole upload. User-edited regex is wrapped in try/catch. Balance/EMI extraction is best-effort. Preserve this: a bad line in month 7 must not lose months 1–6.

7. How to make common changes

• Add a bank / statement format → write a pure tryParseYourBank(text, opts): ParseResult | null in src/parsers.ts that returns null when the text isn't its format, and register it in the attempts array in parseStatement in the right order (more specific before more generic). Return normalized RawTxn[] and, if the format identifies an account, an AccountDescriptor. The clean/dedupe/categorize pipeline and all endpoints are untouched.
• Add a category → POST /api/categories/rules {category, pattern, priority} at runtime, or seed category_rules in a new migration. Rules are regex, tested case-insensitively against the description, lowest priority first, first match wins, else Uncategorized. Takes effect on the next upload (categorization runs at ingest).
• Add an account type (e.g. wallet, loan, brokerage) → the model keys on account_key + account_type, so this is additive: a migration for any new columns/allowed values, a parser that tags rows with the new type, and (if it's an asset/liability) folding it into /api/accounts/overview's net-worth math. Not a rewrite.
• Add an endpoint → follow the clauses[]/params[] + .bind(...) pattern; reuse accountFilter/transferFilter/truthy; return JSON; keep the GET * asset fallback last.

8. Migrations (D1)

• Files in migrations/, numbered and applied in order by Wrangler.
• Never edit a migration that has been applied to any real database — add a new one. Migrations are append-only history.
• Prefer additive and idempotent (CREATE TABLE IF NOT EXISTS, INSERT OR IGNORE for seeds). New columns should be nullable or defaulted so existing rows survive.
• Apply locally first: npm run db:migrate:local. Only npm run db:migrate:remote once verified.
• The schema in migrations/ is authoritative for the data-model docs in README — keep them in sync.

9. Testing & verification

There is no automated test suite yet. Until one exists, verify like this:

1. npm run typecheck — must be clean (this is the closest thing to CI today; run it before every commit).
2. npm run dev, then upload each file in samples/ through the dashboard. Confirm the upload response (formatDetected, rowsParsed, inserted), that the views render, and that GET /api/accounts lists the sample accounts. Roll test data back out with DELETE /api/imports/:id (or just reset the disposable local D1 in .wrangler/).
3. Never test against production or real statements. Local D1 only; samples are synthetic.

Adding a test suite is welcome and low-friction because parsers.ts/pipeline.ts are pure: a runner like Vitest with @cloudflare/vitest-pool-workers runs tests inside the Workers runtime (respecting §0). Start with parser fixtures (a sample in → expected RawTxn[] out) and computeHash/normalizeDate/categorize unit tests.

10. Gotchas & footguns

• Upload cap: MAX_UPLOAD_BYTES (default 5 MB, in wrangler.jsonc vars). Larger files 413.
• 2-digit years: normalizeDate maps DD/MM/YY → 20YY. It tries 4-digit forms first so full years are never truncated. Bank exports use both; don't reorder those branches.
• Account identity precedence: in-file Card No: beats the filename hint. Bank CSVs with no in-file account number rely entirely on the filename (deriveAccountFromFilename) — so the filename matters for savings imports. Keep the bank's original filename.
• User-editable regex: category patterns are user data; a bad pattern must be skipped (try/catch in categorize), never crash an import.
• Best-effort blocks are intentional: the try/catch around account-registry and statement/EMI writes exists so balance-parsing quirks can't roll back a good transaction import. Don't "tidy" them into hard failures.
• byCard alias: /api/summary/overview still returns byCard as an alias of byAccount for older clients. Leave it unless you audit public/app.js.

11. Privacy — what must never be committed

• No real financial data. Real statements and the live local D1 live only in .wrangler/ (gitignored) and outside the repo. Only synthetic samples/ are committed.
• No real database_id. wrangler.jsonc ships a "<your-d1-database-id>" placeholder. If you fill in a real id to deploy, do not commit it.
• Do not advertise the maintainer's live deployment URL in docs — it serves real data. Use the https://<your-worker>.<subdomain>.workers.dev placeholder.
• .claude/settings.local.json and .dev.vars/.env* are gitignored — keep secrets there, never in tracked files.