siddharthd
48ec151c15
Adds trips table usage across API and UI: trip CRUD, per-trip analytics (category/daily/merchant/tag/participant breakdowns), tag-to-trip conversion, trip assignment via transaction overrides, and trip filter in the transactions view. Recovered from working tree after local git corruption; feature was already live via host-context Docker builds.
5.2 KiB
5.2 KiB
Personal Finance Tracker — Project Context
What Is This?
A self-hosted personal finance tracker built from scratch. It automatically ingests bank statements, categorises transactions using AI, and provides a web UI for reviewing spending, managing shared expenses, and running analytics.
How It Works (High Level)
Automatic Statement Ingestion
Bank statements (PDFs) are uploaded to a document management system (Paperless-NGX). An automation workflow (N8N) polls for new documents every 5 minutes and:
- Sends the PDF to Google Gemini (AI) for structured data extraction
- Normalises the extracted data (merchant names, currencies, account numbers)
- Inserts the statement summary and individual transactions into a PostgreSQL database
- Creates a Google Calendar reminder for credit card payment due dates
- For new/unknown bank accounts, requires a human approval before inserting
Core Features
Transactions View
- Full paginated list of all transactions across all bank accounts
- Filters: date range, category, bank, tags, transaction type, amount range, split status, free-text search
- Sortable columns including transaction date, amount, and import date
- Inline editing of category, merchant name, and notes
- Tagging system with user-defined coloured labels
Statements View
- One row per billing period per account
- Filters by bank, statement type, owner, year
- Click a statement to see only its transactions
Analytics / Insights
- Monthly spend breakdown by category (stacked bar chart)
- Category trend lines over time
- Pareto chart (which categories drive the most spend)
- Cumulative spend curve
- Savings rate over time
- Recurring charge detection (subscriptions/recurring merchants)
- Fees and interest audit (tracks what's been paid in bank fees and interest charges)
- Committed vs discretionary spend split
Merchant Profiles
- Per-merchant transaction history and net spend
- Scatter plot of spend over time
- Accounts for refunds/credits
Shared Expenses
- Split transactions between multiple people (by percentage)
- Tracks who owes what with a running balance
- Record cash settlements between participants
- Tag filter on shared view to track specific projects/events
Rules Engine
- Create saved rules that auto-apply categories, merchant names, tags, or splits to matching transactions
- Conditions: merchant name, description, category, bank, amount, transaction type
- Operators: contains, equals, starts with, greater/less than, not equals
- Bulk-apply all rules at once with full revert support (snapshot stored before each run)
Manual Transactions + Reconciliation
- Enter transactions manually (cash, receipts not on a statement)
- CSV import for bulk entry
- Reconcile manual transactions against statement transactions when the statement arrives — merges tags, splits, and notes onto the statement version
Multi-Owner Support
- Multiple people's accounts can be tracked in the one app
- Each statement/transaction is scoped to an owner
- The logged-in user only sees their own data
Technology
- Frontend: Next.js (React), TypeScript, Tailwind CSS, Recharts for charts
- Backend: Next.js API routes with raw PostgreSQL queries (no ORM at query time)
- Database: PostgreSQL
- AI extraction: Google Gemini 2.5 Flash (PDF → structured JSON)
- Automation: N8N workflow orchestrates the ingestion pipeline
- Auth: Users are authenticated by the reverse proxy before reaching the app
- Hosting: Self-hosted Docker container on a home server
Data Structure (Summary)
| Concept | Description |
|---|---|
| Statement | One billing period for one bank account. Has summary totals (closing balance, fees, interest, credit limit, etc.) |
| Transaction | A line item. Has date, amount, merchant, category, transaction type (debit/credit/refund/fee/etc.) |
| Override | User correction to AI-extracted merchant name or category. Stored separately to preserve the original AI output |
| Split | A transaction shared with another person — records their percentage share and whether it's been settled |
| Tag | A free-form label applied to transactions (e.g. "Europe Trip 2026", "Home Reno") |
| Rule | A saved condition→action pair applied in bulk to auto-categorise/tag/split transactions |
| Participant | A person (account owner or expense-sharing partner) |
| Split Payment | A recorded cash settlement between two participants |
Category Taxonomy
Fixed set used by AI and overridable by the user:
groceries, dining, transport, fuel, shopping, utilities, entertainment, travel, health, insurance, subscriptions, cash_advance, government, education, rent, home_goods, home_maintenance, transfers, income, investment, personal_care, pets, gifts, charity, other
Known Limitations / Planned Work
- Payment provider conflation: Transactions processed through PayPal, Afterpay, Zip etc. sometimes show the payment provider as the merchant rather than the actual store. Plan is to extract
payment_provideras a separate field so the real merchant is preserved. - Budgets: The database has a budget table but it's not currently surfaced in the UI (the analytics/insights views replaced it for now).