Files

T

nngo ceb1d1395d Aug 17 - Update with utilities management

2026-08-17 12:53:24 -04:00

54 KiB

Raw Permalink Blame History

Personal Finance Management System (PFM)

Stack: Python Flask · MySQL · Ubuntu Server · Nginx · Gunicorn · Groq API (free AI) App URL: https://pfm.ngodanguyen.tech Code: /home/pfm/web · User: pfm · Gitea: gitea.ngodanguyen.tech

1. Project Overview

Self-hosted personal finance web app. Tracks income, expenses, investments. AI assistant powered by Groq API (free tier, fast inference, no local hardware). Receipt OCR via Groq vision model. Bank account sync via Teller API (mTLS), Schwab Developer API (OAuth 2.0), and Plaid API. Bank statement import (CSV, OFX/QFX, PDF). Everything runs on Ubuntu server behind Nginx + Certbot SSL.

Status: All 7 phases complete + all post-MVP features implemented.

2. Core Features (Implemented)

2.1 Dashboard

Net worth snapshot (assets − liabilities)
Monthly cash flow bar chart (6 months)
Budget utilization per category
Recent transactions feed (last 8)
AI daily insight card (Groq-generated, stored in DB)
Savings Rate stat card — net cash flow ÷ income for the selected period; green ≥ 20%, blue > 0%, red negative
Schwab expiry warning — banner shown when Schwab refresh token expires within 2 days
USD → VND exchange rate widget — reference only, independent of app currency
- Click to expand 30-day history chart
- ↻ refresh button (force-fetches fresh rate without page reload)
- Source label shown (yfinance / exchangerate-api)
- Stale indicator if rate > 1 day old
Period selector: This Month / Last Month / Custom date range
Credit card accounts display "owed" balance (positive Amount Owed) not raw negative
Checking & Savings card — sum of balances for checking, savings, cash account types
Investments card — sum of balances for investment, crypto account types
Reconcile button — AJAX GET /api/reconcile; excludes transactions in any category whose name contains "transfer" (case-insensitive); updates Income / Expenses / Net Cash Flow / Savings Rate cards in-place; toggles back to original; shows notice with excluded amounts and category names

2.2 Transactions

Income + Expense entry with Income/Expense tabs
Transfer between accounts
Filter: search, category, account, date range (safe int parsing — no crash on bad params)
Quick date filters — "This Month" and "Last Month" buttons above the filter bar; active button highlighted; ✕ clear button shown when a quick filter is active
Pagination (30/page)
Receipt upload (PNG/JPG/WEBP/GIF/PDF, max 10MB)
AI Receipt OCR — drag-drop receipt image → Groq vision extracts amount/date/merchant/category → auto-fills form
Re-extract from already-uploaded receipt (edit mode)
Inline category change — Category column is a <select> dropdown; change fires AJAX POST /transactions/<id>/set-category with no page reload
Bulk actions — checkbox per row + select-all header checkbox; sticky dark toolbar appears when rows are checked; supports:
- Bulk delete — confirmation dialog; rows removed from DOM after AJAX delete
- Bulk set category — dropdown + Apply; inline category selects updated in DOM without reload
- AJAX endpoint: POST /transactions/bulk-action with {action, ids, category_id}
Export to CSV / Excel
Edit form: receipt sub-forms are outside #txnForm to prevent nested-form bug

2.3 Accounts

Types: checking, savings, cash, credit_card, crypto, investment, other
Balance source of truth:
- Teller-linked accounts: balance comes from Teller API (live refresh or after sync); calc_balance is NOT called on page load for these
- Schwab-linked accounts: balance comes from Schwab snapshot sync; calc_balance is NOT called on page load for these
- Plaid-linked accounts: balance comes from Plaid API after sync or Refresh button; calc_balance is NOT called on page load for these
- Unlinked accounts: balance auto-calculated from all transactions via calc_balance
Teller badge (blue) shown on account cards linked to Teller
Schwab badge (green) shown on account cards linked to Schwab
Plaid badge (purple) shown on account cards linked to Plaid; credit card billing card shown (due date, days left, min payment, statement balance)
Per-account action buttons for provider-linked accounts:
- Teller: Refresh (live balance AJAX), Sync (transaction preview), Reset (90-day resync)
- Schwab: Balance & Positions (snapshot sync POST), Transactions (preview link)
- Plaid: Refresh (live balance AJAX), Sync (→ sync preview), Billing (POST liabilities refresh)
Color + icon picker; soft delete
Credit cards show "Amount Owed" (positive) and "This Month" charges
Opening balance field on account creation (negative for credit cards = starting debt)

2.4 Categories

Expense + Income categories with color/icon
System categories (protected from delete)
Custom categories (user-created)
14 expense + 7 income defaults seeded on init

2.5 Budget Planner

Monthly limits per expense category
Progress bars: green → amber (80%) → red (100%+)
Rollover unused budget to next month (toggle)
Copy previous month's budgets in one click
Unbudgeted spending shown with "Set Budget" prompt

2.6 Goals & Savings

Goals with target amount, target date, color, icon
Contribution tracking + history
Progress bar + projected completion date (based on avg monthly contrib)
Auto-complete on 100%
Emergency fund tracker (liquid assets vs 3-month / 6-month expense targets)

2.7 Investments

Asset types: stock, ETF, crypto, real_estate, bond, cash, other
Buy/sell/dividend/split transaction log
FIFO cost basis auto-recalculated from transaction log
yfinance price auto-fetch (daily 4PM weekdays via cron)
Manual price refresh button (portfolio page)
Live ticker check on add form
Doughnut allocation chart + per-asset-type breakdown
P&L per holding + portfolio total
"Sync Schwab" button (topbar POST) — runs snapshot sync for all mapped Schwab accounts, redirects back to investments page
Per-account sections — when investments span multiple accounts (e.g. Individual + Roth IRA), holdings are grouped into one card per account, each showing account name, total value, and holdings table. Allocation sidebar also shows "By Account" breakdown
investments.account_id FK — each Schwab-synced holding is stamped with its source account; same ticker in different accounts (AAPL in Individual vs Roth IRA) stays as separate rows
Holdings table uses a Jinja2 {% macro %} (reused across single and multi-account views)
Price history chart on investment detail page — 1W / 1M / 3M / 6M / 1Y timeframe buttons; fetches from /investments/api/price-history/<ticker>

2.8 AI Financial Assistant

Chat UI with SSE streaming (Groq API, word-by-word response)
Context: last 90 days transactions + budget status + goals + investments
8 suggested question buttons
Daily auto-insight generated at midnight (stored in ai_insights table)
Manual "Generate Now" button
Chat history page
Model: llama-3.3-70b-versatile (default) or llama-3.1-8b-instant (fast)
Fallback messages for rate limit / invalid key / unavailable

2.9 Receipt OCR (Groq Vision)

Model: meta-llama/llama-4-scout-17b-16e-instruct
Drag-drop or click-to-upload on new expense form
Extracts: amount, date, merchant name, category suggestion, notes
Maps category suggestion → system category ID
Auto-fills form fields with green flash animation
Re-extract button on existing receipt (edit mode)
Auto-triggers OCR when image file selected in edit mode
Handles fenced markdown JSON output from LLM
Sanity check: rejects amounts < 1000 (catches garbage values)
Full error handling: 400/401/429/timeout/bad JSON

2.10 Reports & Export

Monthly / Quarterly / Yearly summary reports
Tax year summary (all income by source, all expenses by category)
Net worth history line chart (from monthly snapshots)
Category spending trends (top 6 categories, 6-month line chart)
"Snapshot Now" manual button
Export: CSV, Excel (color-coded, formatted), PDF (WeasyPrint)

2.11 Settings

Profile: name, email, timezone, currency, Groq model
8 currency options (USD/VND/EUR/GBP/JPY/AUD/CAD/SGD) — auto-updates symbol
Password change (requires current password)
Two-Factor Authentication (TOTP) — enable/disable TOTP 2FA; setup shows QR code + manual key entry; disable requires password confirmation
Audit Log (/settings/audit) — paginated log of login, 2FA, password, and bank-connection events with IP address; filterable by event type; Purge dropdown (7 / 30 / 90 days) via POST /settings/audit/purge
Recurring rules: CRUD, pause/enable, frequency (daily/weekly/biweekly/monthly/quarterly/yearly)
"Run Now" button to process due rules immediately
CSV import: upload → preview with ⚠ warnings → confirm
Upcoming recurring transactions (30-day view)
All Settings sub-pages have a ← Settings back button in the topbar (Teller, Schwab, Plaid, Audit Log, Profile, Password, Recurring, Import, System Logs, Recurring Form)

2.12 USD → VND Exchange Rate

Reference widget only — not used in transaction calculations
Primary source: yfinance USDVND=X (Yahoo Finance forex)
Fallback: open.er-api.com (free, no key)
Sanity check: rate must be > 1000 (rejects garbage values)
force_refresh() — always fetches fresh on cron, bypasses cache
DB caches one record per day (fx_rates table)
Dashboard: shows rate, date, source, stale warning, ↻ refresh button
30-day history chart (click widget to expand)

2.13 Teller Bank Sync

Connects US bank accounts via Teller API (mTLS + HTTP Basic Auth)
Bank Connections page (/teller/) — accessible via Settings; connect/disconnect only; no sync buttons here
- Shows: institution name, connected date, last synced, account list with linked PFM account names
- "Map Account" button for unmapped accounts
Accounts page — all Teller action buttons live here per account card (see §2.3)
Transaction sync: preview → confirm → import; after import, live balance fetched from Teller API (not recalculated from transactions)
Duplicate detection using Teller transaction ID (Teller:<id> in notes)
Balance refresh: uses ledger field for credit cards (amount owed), available for bank accounts
- Credit card ledger forced negative (our debt convention: -abs(ledger))
Auto-categorize: keyword match on description (auto_categorize from bank_import_service) first; Teller category field as fallback
Income/expense type: positive Teller amount = income (credit), negative = expense (debit)
Webhook: transactions.processed event with HMAC-SHA256 signature + 5-min replay protection
Config: TELLER_APP_ID, TELLER_ENV, TELLER_CERT_PATH, TELLER_KEY_PATH, TELLER_WEBHOOK_SECRET
Models: teller_enrollments, teller_accounts (2 tables)
Not in sidebar — accessed via Settings page only

2.14 Schwab Bank Sync

Connects Schwab brokerage/IRA accounts via Schwab Developer API (OAuth 2.0)
Accessible via Settings page only — removed from sidebar
OAuth flow: GET /schwab/connect → redirect to Schwab with PKCE state → GET /schwab/callback → exchange code → store tokens
- State parameter included in auth URL (fix for "OAuth state mismatch" error)
- SCHWAB_REDIRECT_URI must match exactly what's registered in Schwab developer portal
Account hashes: Schwab requires hashValue (encrypted account number) in all API paths
- On connect: calls GET /trader/v1/accounts/accountNumbers to get {accountNumber → hashValue} map
- account_hash stored in DB is always the hashValue, never the raw account number
- On reconnect: existing SchwabAccount records updated in-place (by hash, raw number, or masked display) to preserve pfm_account_id mapping
Account mapping: each Schwab account → PFM account (or create new); stored in schwab_accounts
Transaction sync: preview → confirm → import; uses same duplicate-skipping as other providers
- After import: live balance fetched from Schwab API (not recalculated from transactions)
Balance + position snapshot (POST /schwab/snapshot/<id> or "Balance & Positions" button):
- Fetches GET /trader/v1/accounts/{hash}?fields=positions
- Updates linked PFM account balance from currentBalances.liquidationValue
- Upserts Investment records for each long position, matched on (ticker, account_id)
- Asset type mapping: EQUITY→stock, ETF→etf, MUTUAL_FUND→etf, FIXED_INCOME→bond, CASH_EQUIVALENT→cash, unknown→other
- Position zero-quantity and empty-symbol positions skipped; null positions array guarded
Investments sync: topbar "Sync Schwab" button on investments page → POST /investments/sync-schwab → runs snapshot for all mapped accounts (ignores stale connection_id — always uses active connection)
Token auto-refresh: access token expires 30 min; refreshed automatically before API calls
Refresh token expiry: refresh_token_expires_at tracked in DB; reset on every successful token exchange; dashboard warns when ≤ 2 days remain
Account type map: CASH→checking, MARGIN/IRA/ROTH_IRA/ROLLOVER_IRA/TRADITIONAL_IRA/401K/ROTH_401K/BROKERAGE→investment; unknown types fall back to 'other'
Config: SCHWAB_CLIENT_ID, SCHWAB_CLIENT_SECRET, SCHWAB_REDIRECT_URI
Models: schwab_connections, schwab_accounts (2 tables)

2.15 Bank Statement Import

Sidebar link: "Import Statement" under Money section
Supported formats: PDF, OFX/QFX, Chase/BofA/Citi/Capital One/Discover/Amex/USAA/Wells Fargo CSV, Generic CSV, Custom column mapping
Auto-categorizes using 200+ keyword rules across 14 categories
Preview table: per-row checkboxes, editable category dropdowns, bulk type toggle + bulk category apply
Duplicate detection: OFX FITID (import:<id> in notes) or date+amount+description+account
AJAX-based: no page reloads

2.16 System Logs Viewer

Log file: logs/app.log (rotating, 10 MB, 5 backups) — kept for download/backup
DB-backed viewer — log entries also written to app_logs table via DBLogHandler; viewer queries DB (not file)
Format: YYYY-MM-DD HH:MM:SS|LEVEL|module.name|message
Viewer at /logs/: colour-coded pills, free-text search, module filter, auto-refresh, download (file), clear all (DB + file)
Purge dropdown (7 / 30 / 90 days) via AJAX POST /logs/purge — deletes app_logs rows older than N days
DB entry count shown in header chip

2.17 Financial Health Score

Dashboard widget: 0–100 score with letter grade (A/B/C/D/F) + color
Computed by app/services/health_score_service.py::compute_health_score(), served via GET /api/health-score (AJAX, dashboard.py)
4 components, 25 points each:
- Savings Rate — 3-month avg; ≥20% full marks, 10–20% → 18pts, 1–10% → 10pts, ≤0% → 0
- Budget Adherence — fraction of budgeted categories currently under limit (no budgets set = not penalised)
- Goal Progress — avg completion % across active (non-completed) goals
- Emergency Fund — liquid assets vs 3-month expense target, scales linearly
Each component returns a tip string when below target; surfaced in UI

2.18 Budget Alerts (Email)

Threshold alerts at 80% and 100% of a category's monthly budget (limit + rollover)
Triggered by app/services/alert_service.py::check_and_flash_budget_alerts(), called after committing an expense transaction
Dedup: budgets.alert_sent_80 / alert_sent_100 boolean flags prevent repeat sends within the same month
In-app: Flask flash() message (warning/danger) always shown when a threshold is newly crossed
Email: HTML email sent only if budget_alerts_enabled is on (Settings → Profile) AND SMTP is configured (SMTP_HOST, SMTP_USER, SMTP_PASSWORD, ALERT_EMAIL all set)
Settings → Profile: "Email me when a budget category reaches 80% or 100%" checkbox (users.budget_alerts_enabled) + "Send test email" button (POST /settings/test-email) — shows SMTP-configured status inline
Uses stdlib smtplib + ssl (STARTTLS), no third-party mail service

2.19 Plaid Bank Sync

Connects 12,000+ US financial institutions via Plaid API
Plaid page (/plaid/) — accessible via Settings; connect/disconnect/sync/billing
Link flow: AJAX POST /plaid/create-link-token → open Plaid Link widget (CDN JS) → onSuccess(public_token) → AJAX POST /plaid/exchange-token → redirect to account mapping
Environments: sandbox and production only — development was sunset by Plaid; old configs that set development fall back to production
Credit card liabilities: POST /plaid/liabilities/<item_db_id> fetches due date, minimum payment, last statement balance, is_overdue via /liabilities/get; shown on both Plaid page and Accounts page
Transaction sync: cursor-based (/transactions/sync); preview → confirm → import; cursor stored at item level in plaid_items.cursor; pending transactions skipped
Reset sync (POST /plaid/resync/<item_db_id>) — clears cursor and last_sync_date so next sync re-fetches full available history; duplicates skipped automatically via Plaid:<id> in notes
Balance refresh (AJAX POST /plaid/balance/<pa_db_id>) — live balance from /accounts/balance/get; credit cards stored as negative (debt convention)
Duplicate detection: Plaid:<transaction_id> in notes
Sign convention: positive Plaid amount = expense (outflow), negative = income (inflow) — same for ALL account types
Auto-categorize: keyword match on description first; Plaid top-level category as fallback
Config: PLAID_CLIENT_ID, PLAID_SECRET, PLAID_ENV (sandbox / production), PLAID_WEBHOOK_URL
Models: plaid_items, plaid_accounts, plaid_sync_previews (3 tables)
Webhook (POST /plaid/webhook) — CSRF-exempt; verified via Plaid JWT (ES256, rotating JWK from /webhook_verification_key/get); handles TRANSACTIONS/* events by auto-importing without preview; handles ITEM/ERROR with logging; requires PyJWT package
Auto-sync — plaid_service.auto_sync_item(item) runs cursor sync + silent import; also deletes transactions Plaid marks removed; used by webhook handler
Update webhook for existing items (POST /plaid/update-webhook) — calls Plaid /item/webhook/update for all active items; "Apply to Existing Items" button shown on Plaid page when URL is configured
Not in sidebar — accessed via Settings page only

2.20 Utilities (Electricity / Water / Gas / Internet)

Sidebar link: "Utilities" under Money section; amber badge counts bills due within 7 days
Providers (/utilities/providers) — one record per utility company
- Types: electricity, water, gas, internet, phone, trash, other (each with a default icon/color/usage unit)
- Fields: name, type, account number, usage unit, pay-from account, expense category, billing day, color, icon, notes
- usage_unit blank = no consumption tracking for that provider (the bill form dims those fields)
- Changing the type on a new provider auto-fills unit/icon/color; editing an existing one never overwrites choices
- Archive (hides from dashboard, keeps history) or delete (cascades to bills; payment transactions are left in place)
Bills (/utilities/bills) — one record per billing period
- Fields: period start/end, amount, due date, usage, meter start/end, notes
- Usage entered directly or derived from meter readings — readings win (sync_usage_from_meter)
- Live unit-rate hint on the form; usage_unit snapshotted from the provider at entry time
- UNIQUE (provider_id, period_start) — duplicate periods rejected with a flash, not a 500
- Validation: period end ≥ period start; meter end ≥ meter start
- Filters: provider, type, status (unpaid / overdue / paid), year; pagination 30/page
Status is derived, not stored: paid → overdue (past due) → due_soon (≤ 7 days) → unpaid
Payment — two paths (both set utility_bills.transaction_id):
- Mark Paid (/utilities/bills/<id>/pay) — creates an expense transaction (notes Utility:<bill_id>), defaults account/category from the provider, then runs calc_balance + the budget alert check
- Link Existing (/utilities/bills/<id>/link) — attaches an already-imported Plaid/Teller/Schwab transaction; candidates are unlinked expenses within 45 days of the due date, closest amount first; linking one transaction to two bills is refused
- Reopen (unpay) — deletes the transaction only if PFM generated it (Utility:<id> marker); externally linked ones are left alone. Same rule when deleting a bill.
Dashboard (/utilities/) — this month / 12-month average / YTD / unpaid+overdue stat cards, bills-due table, 12-month stacked bar chart by type, spend-by-type breakdown, per-provider cards with period-over-period Δ
Provider detail — latest/average/12-month/usage stats plus a chart toggling Amount / Usage / Unit Rate over 24 months
Service: app/services/utility_service.py — dashboard_summary, monthly_series, type_totals, provider_summary, usage_series, candidate_transactions, build_payment_transaction, is_generated_payment

3. Database Schema (MySQL)

All 25 Tables

users                   — single user, hashed password, currency/timezone prefs, totp_secret, totp_enabled
accounts                — bank/wallet accounts (balance managed per provider rules)
categories              — expense/income categories with color/icon
transactions            — income/expense/transfer, receipt_id, recurring_rule_id
receipts                — receipt file metadata (filename, size, mime_type)
recurring_rules         — templates: frequency, next_run, start/end date
budgets                 — monthly limits per category, rollover support
goals                   — savings goals with target amount/date
goal_contributions      — individual deposits toward each goal
investments             — holdings: ticker, shares, avg_cost_basis, current_price, account_id
investment_transactions — buy/sell/dividend/split log
net_worth_snapshots     — monthly snapshots: assets, liabilities, net_worth (JSON)
ai_insights             — stored AI responses: daily_summary / chat_response
fx_rates                — daily USD/VND rate cache (date UNIQUE, source)
audit_logs              — security event log: action, description, ip_address, timestamp
app_logs                — application log mirror: timestamp, level, module, message (TEXT)
teller_enrollments      — Teller enrollment: enrollment_id, access_token (TEXT, encrypted), institution_name
teller_accounts         — Teller account ↔ PFM account mapping, last_sync_date
schwab_connections      — Schwab OAuth tokens (TEXT, encrypted), token_expires_at, refresh_token_expires_at
schwab_accounts         — Schwab account ↔ PFM account mapping, account_hash (hashValue)
plaid_items             — Plaid item: item_id, access_token (EncryptedText), institution_name, cursor, last_synced_at
plaid_accounts          — Plaid account ↔ PFM account mapping; cc_due_date, cc_minimum_payment, cc_last_statement_balance, cc_is_overdue
plaid_sync_previews     — temporary preview data: item_id (UNIQUE), data_json (TEXT), next_cursor
utility_providers       — utility company: name, utility_type, usage_unit, default_account_id, category_id, billing_day
utility_bills           — one billing period: amount, period, due_date, is_paid, transaction_id, usage_amount, meter readings

Key Column Notes

accounts.balance — set by calc_balance() for unlinked accounts; set directly by Teller/Schwab/Plaid sync for provider-linked accounts; never overwritten on page load for provider accounts
investments.account_id — nullable FK to accounts.id; NULL for manually-added holdings, set to PFM account ID for Schwab-synced holdings; enables per-account grouping on portfolio page
investments.shares / avg_cost_basis — recalculated from investment_transactions (FIFO) for manual holdings; overwritten directly by Schwab snapshot for synced holdings
transactions.notes — used to store import source IDs: Teller:<id>, Schwab:<activityId>, Plaid:<transaction_id>, or import:<fitid>
schwab_accounts.account_hash — Schwab hashValue (encrypted account number), required in all API paths
teller_enrollments.access_token — stored as TEXT (widened from VARCHAR(128)); encrypted at rest via EncryptedText TypeDecorator
schwab_connections.access_token / refresh_token — TEXT, encrypted at rest; refresh_token_expires_at reset on every token exchange
plaid_items.access_token — EncryptedText (Fernet); cursor is VARCHAR(500), NULL = full history on next sync
plaid_accounts.cc_* — credit card billing fields updated by POST /plaid/liabilities/<item_db_id>
app_logs.message — raw record.getMessage() + exception traceback (if any); no pipe-delimited prefix
users.totp_secret — base32 TOTP secret (VARCHAR 64); NULL when 2FA disabled
users.budget_alerts_enabled — boolean; toggles budget threshold emails (Settings → Profile)
budgets.alert_sent_80 / alert_sent_100 — dedup flags so threshold emails/flashes fire once per month per category
utility_bills.usage_amount — mapped to the Python attribute UtilityBill.usage; the column is NOT named usage because that is a reserved word in MySQL
utility_bills.transaction_id — nullable FK to transactions.id; set by both mark-paid and link-existing. A transaction whose notes start with Utility:<bill_id> was generated by PFM and is deleted on reopen/bill-delete; anything else is left alone
utility_providers.usage_unit — blank/NULL means the provider has no consumption tracking

Migration Scripts

scripts/add_investment_account.py   — adds investments.account_id column (run once)
scripts/add_security_columns.py     — adds totp columns, audit_logs table, widens token columns to TEXT (run once)
scripts/add_plaid_tables.py         — creates plaid_items, plaid_accounts, plaid_sync_previews tables (run once)
scripts/add_log_tables.py           — creates audit_logs and app_logs tables (run once; safe to re-run)
scripts/add_utility_tables.py       — creates utility_providers and utility_bills tables (run once; safe to re-run)

Prefer flask db migrate + flask db upgrade where the Alembic chain is healthy — these scripts are the fallback for schema managed outside the chain. Always read a generated migration before running it: autogenerate cannot see models missing from app/models/__init__.py and will propose dropping their tables.

4. Project File Structure (Actual)

pfm/                             # /home/pfm/web on server
├── wsgi.py
├── requirements.txt
├── .env                         # Not committed
├── .env.example
├── .gitignore
├── logs/
│   └── app.log
│
├── app/
│   ├── __init__.py              # session idle timeout hook; Sentry init; limiter init; DBLogHandler registered
│   ├── config.py                # SENTRY_DSN, SESSION_IDLE_MINUTES, RATELIMIT_STORAGE_URI, PLAID_* added
│   ├── extensions.py            # + limiter (flask-limiter, storage via RATELIMIT_STORAGE_URI)
│   │
│   ├── models/
│   │   ├── user.py              # + totp_secret, totp_enabled columns
│   │   ├── account.py
│   │   ├── category.py
│   │   ├── transaction.py
│   │   ├── receipt.py
│   │   ├── recurring_rule.py
│   │   ├── budget.py
│   │   ├── goal.py
│   │   ├── investment.py        # + account_id FK, account relationship
│   │   ├── net_worth_snapshot.py
│   │   ├── ai_insight.py
│   │   ├── fx_rate.py
│   │   ├── utility.py           # UtilityProvider, UtilityBill (+ UTILITY_TYPE_META)
│   │   ├── audit_log.py         # AuditLog model (action, description, ip_address, timestamp)
│   │   ├── app_log.py           # AppLog model (timestamp, level, module, message TEXT)
│   │   ├── teller_enrollment.py # access_token now EncryptedText (TEXT column)
│   │   ├── schwab_connection.py # tokens now EncryptedText; + refresh_token_expires_at
│   │   └── plaid_item.py        # PlaidItem, PlaidAccount, PlaidSyncPreview models
│   │
│   ├── routes/
│   │   ├── auth.py              # + TOTP verify/setup/disable routes; rate limits; audit calls
│   │   ├── dashboard.py         # + savings_rate; Schwab expiry warning; reconcile API; checking/savings/investment totals; health-score API
│   │   ├── accounts.py          # teller_map + schwab_map + plaid_map; skip calc_balance for providers
│   │   ├── categories.py
│   │   ├── transactions.py      # + set-category AJAX; bulk-action AJAX; quick date filter vars; fixed income-form-submits-as-expense bug
│   │   ├── budgets.py
│   │   ├── goals.py
│   │   ├── investments.py       # + sync-schwab route; price-history API
│   │   ├── reports.py
│   │   ├── ai.py
│   │   ├── settings.py          # + audit_log route; audit_purge route; audit calls on password change; MIME magic-byte check on receipt upload; test-email route
│   │   ├── teller.py            # balance uses ledger/available correctly
│   │   ├── schwab.py            # OAuth, mapping, sync, snapshot; audit calls; fallback type 'other'
│   │   ├── plaid.py             # Link flow, exchange, map, sync preview/confirm, balance, liabilities, resync, disconnect
│   │   ├── bank_import.py
│   │   ├── logs.py              # DB-backed API; purge endpoint; clear truncates DB + file
│   │   └── utilities.py         # providers + bills CRUD, mark-paid, link payment, usage API
│   │
│   ├── services/
│   │   ├── account_service.py
│   │   ├── ai_service.py
│   │   ├── budget_service.py
│   │   ├── export_service.py      # CSV/Excel exports stream via generator + yield_per(500)
│   │   ├── fx_service.py
│   │   ├── goal_service.py
│   │   ├── import_service.py
│   │   ├── investment_service.py  # get_portfolio_summary returns account_groups
│   │   ├── health_score_service.py # compute_health_score() — savings/budgets/goals/emergency fund → 0-100 score
│   │   ├── alert_service.py       # budget threshold alerts (flash + SMTP email), dedup via alert_sent_80/100
│   │   ├── ocr_service.py
│   │   ├── recurring_service.py
│   │   ├── report_service.py
│   │   ├── teller_service.py      # auto_categorize; correct sign convention; live balance after sync
│   │   ├── schwab_service.py      # + expanded ACCOUNT_TYPE_MAP; refresh_token_expires_at always reset
│   │   ├── plaid_service.py       # Link token, exchange, accounts, balances, liabilities, cursor sync, parse, import
│   │   ├── utility_service.py     # bill roll-ups, usage/rate trends, payment matching
│   │   └── bank_import_service.py
│   │
│   ├── templates/
│   │   ├── base.html              # mobile responsive tweaks; Teller/Schwab removed from sidebar
│   │   ├── auth/totp_setup.html   # QR code + manual key entry for 2FA setup
│   │   ├── auth/totp_verify.html  # 6-digit code entry on login
│   │   ├── dashboard/index.html   # + savings_rate card; Schwab warning; Reconcile btn; Checking/Savings + Investments cards
│   │   ├── accounts/index.html    # Teller/Schwab/Plaid badges + action buttons; Plaid CC billing card
│   │   ├── transactions/index.html # inline category <select>; bulk actions toolbar; quick date filters
│   │   ├── investments/index.html  # per-account sections; Sync Schwab btn
│   │   ├── investments/detail.html # + price history chart (1W/1M/3M/6M/1Y)
│   │   ├── settings/audit.html    # audit log viewer with event filter + Purge dropdown; ← Settings back btn
│   │   ├── settings/index.html    # + 2FA section; audit log nav card; Plaid Sync nav card
│   │   ├── settings/profile.html  # + ← Settings back btn
│   │   ├── settings/password.html # + ← Settings back btn
│   │   ├── settings/recurring.html # + ← Settings back btn
│   │   ├── settings/recurring_form.html # + ← Recurring back btn
│   │   ├── settings/import.html   # + ← Settings back btn
│   │   ├── teller/index.html      # connect/disconnect only; + ← Settings back btn
│   │   ├── schwab/                # index.html (+ ← Settings back btn), map_accounts.html, preview.html
│   │   ├── plaid/                 # index.html (+ ← Settings back btn), map_accounts.html, preview.html
│   │   ├── logs/index.html        # DB-backed viewer; Purge dropdown; ← Settings back btn moved to topbar
│   │   ├── utilities/             # index.html, providers.html, provider_form.html, detail.html,
│   │   │                          # bills.html, bill_form.html, pay.html, link.html
│   │   └── ... (other templates unchanged)
│   │
│   └── utils/
│       ├── formatters.py
│       ├── decorators.py
│       ├── audit.py               # audit() helper — writes AuditLog rows; swallows DB errors
│       ├── crypto.py              # EncryptedText SQLAlchemy TypeDecorator (Fernet, key=SHA256(SECRET_KEY))
│       └── db_log_handler.py      # DBLogHandler — writes app.* log records to app_logs table; reentrancy guard; swallows errors
│
├── scripts/
│   ├── init_db.py
│   ├── process_recurring.py
│   ├── fetch_fx_rate.py
│   ├── fetch_prices.py
│   ├── daily_snapshot.py
│   ├── daily_ai_insight.py
│   ├── add_investment_account.py  # adds investments.account_id column
│   ├── add_security_columns.py    # adds TOTP cols, audit_logs table, widens token cols to TEXT
│   ├── add_plaid_tables.py        # creates plaid_items, plaid_accounts, plaid_sync_previews
│   ├── add_log_tables.py          # creates audit_logs + app_logs tables (safe to re-run)
│   ├── add_utility_tables.py      # creates utility_providers + utility_bills (safe to re-run)
│   └── sync_schwab.py             # daily Schwab auto-sync (balance + positions + transactions)
│
└── tests/

5. Python Dependencies (`requirements.txt`)

flask==3.1.0
flask-sqlalchemy==3.1.1
flask-login==0.6.3
flask-migrate==4.1.0
flask-wtf==1.2.2
pymysql==1.1.1
python-dotenv==1.0.1
gunicorn==23.0.0
groq==0.13.1
weasyprint==63.1
openpyxl==3.1.5
Pillow==11.1.0
apscheduler==3.10.4
requests==2.32.3
cryptography==44.0.2
python-dateutil==2.9.0
pdfplumber==0.11.4
# Security
flask-limiter==3.5.0
pyotp==2.9.0
qrcode==7.4.2
# Monitoring
sentry-sdk[flask]==2.7.0

6. AI Integration — Groq API

Chat + Daily Insights

Model: llama-3.3-70b-versatile (default) / llama-3.1-8b-instant (fast)
Context: last 90 days transactions, budget status, goals, investments (anonymised)
SSE streaming: stream_chat() yields data: <chunk>\n\n
Daily insight: non-streaming, stored in ai_insights table, max 400 tokens

Receipt OCR (Vision)

Model: meta-llama/llama-4-scout-17b-16e-instruct
Input: base64-encoded image (JPEG/PNG/GIF/WEBP)
Prompt: structured JSON extraction (amount, date, merchant, category, notes)
Temperature: 0.1

Bank Statement PDF Parsing

Text extracted by pdfplumber then sent to llama-3.3-70b-versatile
Max 30 K chars sent per request
Scanned PDFs rejected with clear error message

Free Tier Limits

Metric	Limit
Requests/day	14,400
Tokens/minute	500,000
Cost	Free

7. Teller Bank Sync

mTLS: client cert + key from TELLER_CERT_PATH / TELLER_KEY_PATH
HTTP Basic Auth: access_token as username, empty password
Endpoints: GET /accounts, GET /accounts/:id/balances, GET /accounts/:id/transactions
All API errors logged with status code + full response body
Webhook: HMAC-SHA256 Teller-Signature header; 5-minute replay window
Balance convention: credit cards use ledger (amount owed, stored as negative); bank accounts use available
After transaction sync: live balance re-fetched from Teller API instead of computing from transactions

8. Schwab Developer API

OAuth 2.0: SCHWAB_AUTH_URL + SCHWAB_TOKEN_URL
State parameter sent in auth URL (CSRF protection)
Account identification: hashValue from /trader/v1/accounts/accountNumbers (NOT raw account number)
Token refresh: access tokens expire 30 min; auto-refreshed via _ensure_fresh(connection)
Endpoints:
- GET /trader/v1/accounts/accountNumbers → {accountNumber: hashValue} map
- GET /trader/v1/accounts?fields=positions → accounts list with balances + positions
- GET /trader/v1/accounts/{hash}?fields=positions → single account
- GET /trader/v1/accounts/{hash}/transactions?startDate&endDate → transactions

9. Bank Statement Import

Route: /bank-import/ (blueprint bank_import_bp)
Parse: POST /bank-import/parse (AJAX, multipart with X-CSRFToken header)
Import: POST /bank-import/import (AJAX, JSON with X-CSRFToken header)
Duplicate detection: OFX import:<FITID> in notes; CSV/PDF: date+amount+type+description scoped to account_id

10. Account Balance Rules

Account type	Balance source	When updated
Unlinked (no provider)	`calc_balance()` from transactions	After every txn add/edit/delete; on accounts page load
Teller-linked	Teller API `available` (bank) or `ledger` (credit card)	After Teller sync; when Refresh button clicked
Schwab-linked	Schwab API `liquidationValue`	After Schwab sync; when Balance & Positions clicked
Plaid-linked	Plaid API `available` (bank) or `current` (credit card, stored negative)	After Plaid sync; when Refresh button clicked

Key rule: accounts page load calls calc_balance ONLY for accounts NOT in teller_map, schwab_map, or plaid_map. Dashboard does NOT call calc_balance (reads stored values).

11. Logging System

Config key: LOG_FILE_PATH (default: <project-root>/logs/app.log)
File handler: RotatingFileHandler — 10 MB per file, 5 backups; kept for download/external tools
DB handler: DBLogHandler (app/utils/db_log_handler.py) — mirrors every app.* log record into app_logs table; has reentrancy guard (skips sqlalchemy.* / werkzeug to prevent recursion); swallows all errors so a DB issue never crashes the app
Format: YYYY-MM-DD HH:MM:SS|LEVEL|module.name|message (file); fields stored separately in DB
Namespace: logging.getLogger('app') at INFO; propagate=False
Viewer queries app_logs DB table (not file); file used only for download
Purge via POST /logs/purge with days=7|30|90; audit log purge via POST /settings/audit/purge

12. UI/UX

Sidebar: collapsible (desktop state saved in localStorage), mobile overlay; Teller Sync and Schwab Sync removed — accessible via Settings only; Utilities sits under Money with an amber badge counting bills due within 7 days (utility_due_count, set in the inject_globals context processor)
Mobile responsive: sidebar goes off-canvas with a dimmed overlay under 769px (topbar toggle button opens/closes it); topbar and main content collapse to full width; tables scroll horizontally via .table-wrap / .pcard.p-0 wrapper classes + .pfm-table min-widths, with .d-mob-none hiding low-priority columns first; under 576px, button labels hide to icon-only (.btn-label) and chart/chat heights are capped (base.html)
Dark mode: toggle button in topbar (moon/sun icon); persisted via localStorage['pfm_dark']; applied pre-paint via a data-pfm-dark attribute to avoid flash-of-light-mode; CSS variable overrides plus targeted [style*="..."] overrides for hardcoded inline colors in templates (base.html)
Charts: Chart.js 4.x (CDN)
Forms: WTForms + Bootstrap 5.3
Icons: Bootstrap Icons 1.11
Fonts: DM Sans + DM Mono (Google Fonts CDN)
Color scheme: #0f172a sidebar, #f1f5f9 body, #10b981 income, #ef4444 expense, #3b82f6 invest, #7c3aed plaid/purple
CSS: All inline in templates (no build step)
CSRF meta tag: <meta name="csrf-token"> in base.html for JS fetch calls
Back buttons: all Settings sub-pages have ← Settings (or ← Recurring for the recurring form) in {% block topbar_actions %}
Keyboard shortcuts: n/i new expense/income, / focus search, g h/g t/g a navigation chords, ? shows cheatsheet modal (base.html)

13. Authentication & Security

Single-user, Flask-Login, session-based
Hashed password (Werkzeug generate_password_hash)
SESSION_COOKIE_SECURE=True, SESSION_COOKIE_HTTPONLY=True, SESSION_COOKIE_SAMESITE='Lax'
Session idle timeout — configurable via SESSION_IDLE_MINUTES (default 60); enforced in before_request hook
TOTP 2FA — optional TOTP second factor (pyotp); setup via QR code; verify endpoint rate-limited 10/min; 30/hr; 5 failed attempts clears pending session and forces re-login
Rate limiting — flask-limiter on login (10/min; 30/hr), TOTP verify (10/min; 30/hr), TOTP setup (10/min); storage backend set via RATELIMIT_STORAGE_URI (use Redis in production to share limits across Gunicorn workers; defaults to memory:// per-process if unset)
At-rest encryption — Teller, Schwab, and Plaid OAuth tokens encrypted in DB via EncryptedText SQLAlchemy TypeDecorator (Fernet symmetric, key = SHA-256(SECRET_KEY)); columns are TEXT not VARCHAR
Audit log — security events written to audit_logs table via app/utils/audit.py; events: login_success, login_success_2fa, login_failed, login_failed_2fa, totp_enabled, totp_disabled, password_changed, schwab_connected, schwab_disconnected
CSRF protection on all forms (Flask-WTF); meta tag in base.html for AJAX
SQLAlchemy ORM (no raw SQL)
Schwab OAuth state parameter validated on callback (CSRF protection)
next redirect params validated to start with / (no open redirect)
Sentry (optional) — error monitoring; enable via SENTRY_DSN env var; send_default_pii=False

14. Scheduled Jobs

Job	Schedule	Script	Notes
Process recurring transactions	Daily 6AM	`process_recurring.py`	90-day catchup cap
Fetch USD/VND rate	Daily 8AM	`fetch_fx_rate.py`	force_refresh(), yfinance primary
Fetch investment prices	Mon-Fri 4PM	`fetch_prices.py`	yfinance, all tickers
Net worth snapshot	1st of month 00:05	`daily_snapshot.py`	Saves to net_worth_snapshots
AI daily insight	Daily 00:01	`daily_ai_insight.py`	Skips if already done today
Schwab auto-sync	Daily 7AM	`sync_schwab.py`	Balance + positions + transactions; warns if refresh token expires soon
DB backup	Daily 2AM	pfm-backup (systemd)	mysqldump → gzip, keep 30 days

15. Environment Variables (`.env`)

SECRET_KEY=your-secret-key
DATABASE_URL=mysql+pymysql://pfm_user:password@localhost/pfm_db
GROQ_API_KEY=your-groq-api-key-here
GROQ_MODEL=llama-3.3-70b-versatile
UPLOAD_FOLDER=/home/pfm/web/uploads
MAX_CONTENT_LENGTH=10485760
FLASK_ENV=production
FLASK_APP=wsgi:app
APP_CURRENCY=USD
APP_CURRENCY_SYMBOL=$
APP_TIMEZONE=Asia/Ho_Chi_Minh
LOG_FILE_PATH=/home/pfm/web/logs/app.log
# Teller
TELLER_APP_ID=your-teller-app-id
TELLER_ENV=development
TELLER_CERT_PATH=/home/pfm/teller/certificate.pem
TELLER_KEY_PATH=/home/pfm/teller/private_key.pem
TELLER_WEBHOOK_SECRET=your-webhook-secret
# Schwab
SCHWAB_CLIENT_ID=your-schwab-client-id
SCHWAB_CLIENT_SECRET=your-schwab-client-secret
SCHWAB_REDIRECT_URI=https://pfm.ngodanguyen.tech/schwab/callback
# Plaid (sandbox / production — 'development' is retired by Plaid)
PLAID_CLIENT_ID=your-plaid-client-id
PLAID_SECRET=your-plaid-secret
PLAID_ENV=sandbox
# Security (optional)
SENTRY_DSN=                          # leave blank to disable Sentry
SESSION_IDLE_MINUTES=60              # session idle timeout in minutes
RATELIMIT_STORAGE_URI=redis://localhost:6379  # use Redis to share rate limits across Gunicorn workers
# Budget alert emails (optional — leave blank to disable)
SMTP_HOST=
SMTP_PORT=587
SMTP_USER=
SMTP_PASSWORD=
ALERT_EMAIL=                         # recipient address for budget alert emails
APP_URL=https://pfm.ngodanguyen.tech # used to build links in alert emails

16. Blueprints Registered (18 total)

Blueprint	Prefix	Key routes
health	(none)	/health (public, no auth)
auth	/auth	login, logout, totp/verify, totp/setup, totp/disable
dashboard	/	index, api/fx-history, api/fx-refresh, api/reconcile, api/health-score
accounts	/accounts	CRUD, adjust
categories	/categories	CRUD
transactions	/transactions	index, new, edit, delete, transfer, ocr, ocr-file, `<id>/set-category`, bulk-action
budgets	/budgets	index, new, edit, delete, copy
goals	/goals	index, new, edit, delete, contribute, contributions
investments	/investments	index, new, detail, edit, delete, add_transaction, refresh-prices, sync-schwab, api/price, api/daychange, api/price-history
reports	/reports	monthly, quarterly, yearly, tax, export/csv\|excel\|pdf, snapshot
ai	/ai	index, stream (SSE), history, generate-insight
settings	/settings	index, profile, password, test-email, audit, audit/purge, recurring, import, recalc-balances, upload_receipt, delete_receipt, view_receipt
teller	/teller	callback, map, index, sync, sync/confirm, sync/all, balance, balance/all, resync, disconnect, webhook
schwab	/schwab	connect, callback, index, map, sync/`<id>`, sync/confirm, resync, snapshot/`<id>`, disconnect
plaid	/plaid	index, create-link-token, exchange-token, map/`<id>`, sync/`<id>`, sync/confirm, balance/`<pa_id>`, liabilities/`<id>`, resync/`<id>`, disconnect/`<id>`, webhook, update-webhook
bank_import	/bank-import	index, parse (AJAX), import (AJAX)
logs	/logs	index, api (AJAX), clear (AJAX), download, purge (AJAX)
utilities	/utilities	index, providers, providers/new, providers/`<id>`, providers/`<id>`/edit, providers/`<id>`/toggle, providers/`<id>`/delete, bills, bills/new, bills/`<id>`/edit, bills/`<id>`/delete, bills/`<id>`/pay, bills/`<id>`/link, bills/`<id>`/unpay, api/usage/`<id>`

17. Known Issues / Notes

wsgi.py has sys.path.insert(0, ...) — required for Gunicorn at /home/pfm/web/
FX rate widget: open.er-api.com may return stale values; yfinance is the reliable primary
WeasyPrint PDF: requires libpango* system libs on server
Bank statement PDF import: scanned/image PDFs have no text layer; must use digital download
Schwab: run scripts/add_investment_account.py then scripts/add_security_columns.py then scripts/add_log_tables.py once after fresh deploy
Schwab: after first connect, run "Balance & Positions" to populate investments; then re-sync if holdings were already added manually
Schwab refresh token: Schwab tokens last ~7 days; refresh_token_expires_at is reset on every token exchange (including access-only refreshes); dashboard warns at ≤ 2 days
Teller: access_token column is TEXT (widened from VARCHAR(128) to fit Fernet-encrypted values); run scripts/add_security_columns.py to apply
Teller: development environment only; requires cert/key from Teller Dashboard
Plaid: run scripts/add_plaid_tables.py once after fresh deploy; development environment retired — use sandbox or production
Plaid: resync clears cursor so full history is re-fetched on next sync; duplicates are skipped automatically via Plaid:<id> in notes
App logs: run scripts/add_log_tables.py to create audit_logs and app_logs tables; DBLogHandler is registered in create_app() after db.init_app(); fails silently if table doesn't exist yet
Rate limiter: defaults to memory:// per-process if RATELIMIT_STORAGE_URI is not set — effective limit is stated_limit × num_workers; set RATELIMIT_STORAGE_URI=redis://localhost:6379 in production
EncryptedText TypeDecorator: key = SHA-256(SECRET_KEY); changing SECRET_KEY invalidates all stored tokens (requires reconnect for Teller, Schwab, and Plaid)
MySQL does not support NULLS LAST; use func.isnull(column) for null-last ordering
Reconcile button: matches categories by name ILIKE %transfer%; if no such categories exist, shows "No internal transfers found" rather than silently changing nothing

18. Security Fixes Applied (session log)

Date	Fix	File
2026-06	Path traversal in `view_receipt` — added `os.path.basename()`	settings.py
2026-06	`int()` crash on bad filter params in transactions index	transactions.py
2026-06	Teller account mapping validates ID exists in DB	teller.py
2026-06	Recurring catchup capped at 90 days (prevents runaway loops)	recurring_service.py
2026-06	CSV/bank import duplicate check scoped by `account_id`	import_service.py
2026-06	CSRF token added to bank import AJAX parse request	bank_import/index.html
2026-06	Receipt sub-forms moved outside `#txnForm` (nested-form bug)	transactions/form.html
2026-06	Drop zone file input moved outside overlay (blocked account select)	bank_import/index.html
2026-06	Teller balance refresh uses `ledger` for credit cards (not `available`)	teller.py
2026-06	Schwab OAuth `state` param added to auth URL (state mismatch fix)	schwab_service.py
2026-06	Schwab uses `hashValue` (not raw account number) in API paths	schwab.py, schwab_service.py
2026-06	`next` redirect params validated to start with `/` (no open redirect)	teller.py, schwab.py
2026-06	Teller income/expense type corrected (positive = income)	teller_service.py
2026-06	`EncryptedText.process_bind_param` removed silent plaintext fallback — raises on encrypt failure	crypto.py
2026-06	Rate limiter storage moved to `RATELIMIT_STORAGE_URI` config (was hardcoded `memory://` per-worker)	extensions.py, config.py
2026-06	TOTP verify: added hourly rate limit (`30/hr`) + per-session attempt counter (locks out after 5 failures)	auth.py
2026-06	TOTP setup endpoint: added `@limiter.limit('10 per minute')`	auth.py
2026-06	`refresh_token_expires_at` now reset on every token exchange, not only when Schwab rotates the token	schwab_service.py
2026-06	Schwab unknown account type fallback changed back to `'other'` (was incorrectly changed to `'investment'`)	schwab.py
2026-06	`teller_enrollments.access_token` widened VARCHAR(128) → TEXT to fit Fernet-encrypted values	add_security_columns.py
2026-06	New income transaction submitted as expense — `form.transaction_type.data` not set on GET	transactions.py

19. To-Do / Roadmap

High priority

(none currently open)

Medium priority