heezy-finance - Data Sources

All data lives in the heezy Postgres database on big-boi (192.168.1.21:5432).

Three Data Sources

heezy-finance ingests from three independent sources: email orders, physical receipts, and bank transactions.

Orders (Gmail + Ollama)

• Source: Email order confirmations (Amazon, Steam, Woot, etc.)
• Ingested by: amazon_orders.py in heezy-finance container
• Tables: orders, order_items
• Current count: 284 orders, 367 line items, 25 vendors (as of 2026-08-11)
• Schedule: heezy-finance-sync CronJob, hourly at :00, --hours 2
• Categorization: rules, then cache, then Ollama llama3.2:3b on big-boi. See below

Item classification: rules, then cache, then LLM

item_classifier.py runs three stages in order, and the LLM is the last resort rather than the only path. Asking a 3B model about a product title is slow, costs a round trip per item, and is not stable: the same product got different answers on different days. "Native Body Wash" was filed under Food & Grocery on one order and Household on another; "Cellucor Creatine Powder" landed in Electronics.

Stage	Mechanism	Confidence
rule	Regex over the title. Free, instant, same answer forever	0.9
cache	item_category_cache, keyed on ASIN when known, else sha1 of the normalized title truncated to 160 chars. 135 entries	inherited
llm	Only for titles neither stage recognizes. The answer is normalized and then cached, so an unfamiliar product costs exactly one call, once	0.5

A manual override wins over all three (confidence 1.0) and the LLM may not overwrite it. Every path funnels through normalize_category(), so a model that invents its own taxonomy cannot write a category the rest of the app does not understand. The classifier may only emit ITEM_CATEGORIES, 18 of the 27 canonical categories. See Categories.

Titles are normalized before hashing because Amazon carries trademark glyphs and inconsistent punctuation on the same product, and appends pack size and variant text that differs between the confirmation email and the CSV export for what is unambiguously the same item.

Passing a live conn to classify_item() is what enables the cache. Without it every unrecognized title still costs an LLM call.

How emails are found

The Gmail search is the union of two clauses, and it needs both:

Clause	Purpose
KNOWN_SENDER_QUERY	Seven vendors whose subject lines we cannot predict. Amazon's is Ordered: "<item>", which matches no generic phrase.
ORDER_SUBJECT_QUERY	Fourteen generic phrases ("order confirmation", "receipt for", …) that catch vendors not on the list at all.

Neither clause is redundant. Dropping the senders is exactly what caused the June 2026 ten-week gap — the subject phrases have never matched Amazon.

A bare subject:"Ordered:" looks like a simpler fix and is a trap: it matches ~380 messages where the sender clause matches ~124, because the heezy-mailbot-amazon-forward job produces a Fwd: Ordered: copy of every Amazon confirmation. Those arrive from a personal address, so is_amazon is false and each would be handed to the LLM parser as a new unknown vendor.

Credentials

Environment only. GMAIL_CLIENT_ID, GMAIL_CLIENT_SECRET and GMAIL_REFRESH_TOKEN, projected by the heezy-finance-gmail ExternalSecret from OpenBao, which is the one copy the token refresher keeps current. A missing or partial env raises GmailCredentialsError naming the absent keys.

There is deliberately no file fallback. /credentials/gmail-oauth.json was a hand-maintained second copy that caused two outages and prevented none; it is gone, and a test asserts it stays gone.

What ingestion does to the mailbox

Amazon order emails are labeled Finance/Amazon and removed from the inbox. Non-Amazon emails are left in place. Amazon return emails go to Finance/Amazon Returns. This is the only irreversible part of a sync, which is why --dry-run exists — always use it before a wide backfill.

python3 amazon_orders.py --hours 1750 --dry-run   # parse and report, write nothing

--dry-run keeps inserts in the open transaction so in-run dedupe still works, rolls them back at the end, and skips every Gmail mutation rather than faking it.

Order ID normalization

order_id is the primary key of orders and dedupe is exact string equality, so two spellings of one order become two rows. _normalize_order_id() strips surrounding whitespace, drops a leading # and any space behind it, and collapses internal whitespace runs. Case is preserved on purpose — some vendors issue mixed-case IDs and folding it risks colliding real orders.

This matters because the LLM parser is inconsistent about the # vendors print, and the forward mailbot means many vendor emails arrive twice. One Woot order was seen as both #212850333 and #212850333 in the same run.

Known Quirks

Symptom	Cause	Status
Ten weeks of zero orders, Jun–Aug 2026	28a7171 replaced the sender allowlist with a subject-only search; Amazon subjects match no phrase in it	Fixed — clauses are now a union. 123 orders recovered 2026-08-11
Sync crash-loops on invalid_grant	CronJob lost its envFrom and silently fell back to the stale gmail-oauth.json	Fixed — env-only, file fallback removed
grand_total is NULL or 0	Current Amazon HTML does not expose a parseable total on roughly a third of confirmations	Open, 7 orders. Line items are priced, so the total is usually recoverable: repair_order_data.py --backfill-totals. The remainder have an unpriced item
Same order appears twice	Unnormalized legacy order IDs	Fixed — repair_order_data.py --normalize-ids, run 2026-08-11
Amazon Shipped:/Delivered: mail in trash	Intentional, keeps the inbox clear	Working as designed, not a bug

Repairing historical rows

repair_order_data.py holds one-off repairs. Both operations are opt-in by name, idempotent, and support --dry-run:

python3 repair_order_data.py --normalize-ids --backfill-totals --dry-run

--normalize-ids cannot simply UPDATE the key: order_items.order_id references orders.order_id with ON DELETE CASCADE and no ON UPDATE, so Postgres checks the children immediately and rejects it. The repair copies the row under the new key, repoints the children, then drops the old row — safe in that order and only that order.

Receipts (OCR + Categorization)

• Source: Physical receipts photographed via receipts.heezy.info
• Ingested by: the receipts service. AWS Textract AnalyzeExpense by default, with DetectDocumentText, a local Ollama vision model, and Tesseract as alternate backends
• Tables: receipts, receipt_items
• Current count: 155 receipts, 821 line items. 12 rows have a NULL date_ts and are dropped by every date-filtered query
• Date column: date_ts (timestamptz) - canonical date for filtering and display
• Merchant column: merchant_normalized - standardized lowercase with aliases
• Payment method: payment_type - set manually per receipt (e.g., cash, CC, debit)

See Receipts Scanner for the OCR backends and their tradeoffs.

Bank Transactions (Statement Parser)

• Source: Bank of America, Capital One, Chase, and Edward Jones statements. PDF for all, plus CSV and OFX/QFX for Chase
• Ingested by: parse.py in the heezy-finance container
• Two ingest paths:
  • heezy-statement-scanner CronJob, hourly at :30, runs scan_statements.py over /ingest/new on the nfs-heezy-ingest PVC. Files land there from statements.heezy.info or an scp to the NFS path. Processed files move to processed/, failures to error/
  • POST /api/statements/upload (field name: files) parses synchronously in-process, from the Statements page
• Tables: bank_statements, bank_transactions. Fifth Third mortgage statements go to mortgage_statements instead
• Current count: 80 statements, 1,582 transactions, 9 mortgage statements
• Supported parsers:
• parse_boa() — BoA checking
• parse_boa_cc() — BoA credit cards
• parse_cap1() — Capital One checking and savings, including multi-account PDFs
• parse_chase_amazon() — Chase Amazon Visa PDF
• parse_chase_csv() / parse_chase_ofx() — Chase CSV and OFX/QFX exports
• parse_edward_jones() — Edward Jones brokerage, 529, IRA, Roth IRA, money market
• parse_fifth_third_mortgage() — Fifth Third mortgage statements
• Auto-populated fields:
• category - rule-based keyword classifier in scan_statements.py
• merchant_normalized - standardized merchant names

detect_bank() order

Order matters, because several issuers print each other's names inside transaction descriptions. The current sequence for PDFs:

1. Year-end summary — BoA year-end PDFs contain both "bank of america" and "credit card", so they would otherwise parse as a CC statement. Detected first and moved straight to processed/
2. Capital One — Cap1 PDFs contain both "Bank of America" (transfers) and "EDWARD JONES" (investment withdrawals) in descriptions
3. Bank of America — then split by positive checking markers before CC markers, so BankAmeriDeals footer text saying "credit card" cannot misclassify a checking statement
4. Edward Jones
5. Chase Amazon
6. Fifth Third mortgage

CSV files route through _is_chase_csv(), OFX/QFX through _is_chase_ofx(). Anything unrecognized returns None and the file moves to error/.

Known Parser Quirks

Bank	Quirk	Fix Applied
Capital One	Descriptions contain "EDWARD JONES" and "Bank of America"	Cap1 is detected before both
Bank of America	Year-end summary PDFs look like CC statements	YEAR_END_DETECT runs first, file skipped to processed/
Bank of America	BankAmeriDeals footer says "credit card" on checking statements	Positive checking markers take priority over CC markers
Bank of America CC	BOA_CC_ACCT_RE can miss last4, producing a boacrdNone_* ghost statement	Guard skips insert_bank_statement when last4 is None on a credit card. 12 ghosts deleted, now 0
Chase Amazon	Header reads Opening/Closing Date MM/DD/YY - MM/DD/YY	Regex captures the second (closing) date
Capital One multi-account	Transactions were all reassigned to the last-seen account	_acct_last4 stored per transaction, resolved to stmt_id after every account flush completes
Capital One multi-account	FK violation, transactions inserted before their statement row	All statement rows insert first, then transactions

Statement ID Format

{bank_slug}{type_slug}{last4}_{YYYY-MM-DD} — e.g. chasecrd2609_2026-06-19

Adding a New Account

New accounts must be inserted directly into the accounts table before statements will parse successfully. Columns:

Column	Notes
institution	Must match what the parser writes (e.g. Bank of America, Capital One, Chase, Edward Jones)
account_last4	Last 4 digits as text
account_type	checking, savings, credit_card, mortgage, money_market, brokerage, 529, ira, roth_ira
name	Human-readable label. Not display_name — that column does not exist
nickname	Optional override, editable from the UI via PATCH /api/accounts/<id>/nickname
display_group	cash, debt, investments, or retirement. Drives Net Worth grouping and Targets membership
is_active	false hides the account from Net Worth, Targets, and the balance form
statement_frequency	Default monthly

If upsert_account_balance() can't find a matching account row, it logs a WARN and silently skips the balance update — no error is surfaced to the upload response.

Dashboard Integration

All three sources are unified in get_all_purchases() in app.py:

Source	Date field used	Filter
orders	order_date (RFC 2822 text, parsed per row in Python)	all
receipts	date_ts (timestamptz)	date_ts IS NOT NULL
bank_transactions	date (DATE)	type=debit, category NOT IN (Transfer, Income)

Two things to know about that table:

• get_all_purchases() still sorts and parses orders.order_date, the raw text column, even though order_date_ts is fully populated and indexed. Data Standards says to use the _ts column. This one query predates that rule.
• The receipts filter is why the 12 rows with a NULL date_ts are invisible everywhere, not just on date-filtered views.