heezy-finance Data Standards

Canonical reference for date fields, numeric types, category values, identifiers, and source tracking across the heezy finance database. Read this before adding a table or a parser.

Last audited: 2026-08-11

---

Date & Timestamp Rules

Rule	Detail
Never store dates as text in new tables	Use date (no time needed) or timestamptz (if time matters)
All timestamps stored in UTC	Display converts to America/New_York via NY_TZ in app.py
date type for statement and transaction dates	e.g. bank_transactions.date DATE
timestamptz type for ingest and processing times	e.g. inserted_at TIMESTAMPTZ DEFAULT now()

Current State

Table	Column	Type	Status
bank_transactions	date	date	Clean
bank_statements	period_end_date	date	Clean
bank_statements	statement_period_start	text	Tech debt. Raw text from the PDF, not normalized
bank_statements	statement_period_end	text	Tech debt. Raw text from the PDF, not normalized
orders	order_date	text	RFC 2822 raw text from the email
orders	order_date_ts	timestamptz	Normalized form. Use this. 0 NULL rows
receipts	date	text	Historical text, multiple formats
receipts	date_ts	timestamptz	Normalized form. Use this. 12 of 155 rows are NULL
receipts	upload_date	text	Upload timestamp stored as text
account_balances	as_of_date	date	Clean
mortgage_statements	statement_date, next_payment_due	date	Clean
statement_files_processed	as_of_date	date	Clean
*	inserted_at	timestamptz	Present on every ingest table

receipts.date_ts is no longer fully backfilled

The June 2026 backfill covered all 51 rows that existed then. Receipt volume has since tripled to 155 and 12 rows have a NULL date_ts. Anything filtering on date_ts silently drops them. Backfill from the text column where it parses: UPDATE receipts SET date_ts = date::date::timestamptz WHERE date_ts IS NULL AND date ~ '^\d{4}-\d{2}-\d{2}'

Always use the _ts column

In app.py, filter and display on order_date_ts and date_ts, never the raw text columns. The text columns exist for audit trail only.

Rule for New Parsers

Every new parser must:

1. Parse the date into a Python datetime
2. Store it as date (statement and transaction dates) or timestamptz (timestamps)
3. Never write a raw date string into a text column

---

Numeric / Amount Rules

Rule: numeric(12,2) for all money columns. Never real or float.

The real tech debt is resolved. Every money column across receipts, receipt_items, orders, order_items, bank_statements, bank_transactions, account_balances, mortgage_statements, spend_budgets, and annual_budgets is now numeric. receipt_items.quantity is numeric, order_items.quantity is integer.

Nothing further is planned here. The old "Phase 3 migration target" list is retired.

---

Category Standards

Categories are the single source of truth for spending classification across bank_transactions, receipt_items, and order_items.

The canonical list lives in categories.py, not app.py. app.py re-exports CATEGORY_ICONS, CATEGORIES, ANNUAL_ONLY_CATS, and FIXED_COST_CATS from it for templates and other modules; item_classifier.py imports ITEM_CATEGORIES and normalize_category from the same file. Keeping one list in one module is deliberate: the classifier's private copy drifted from app.py's for three months.

See Categories for the full 27-entry table, the four subsets, and the alias map.

No FK enforcement

There is no categories lookup table and no FK constraint. Category values are free text. Validation happens in Python: normalize_category() on ingest, and a membership check against CATEGORIES on the category-update endpoints. Use exact strings.

---

Identifier Standards

Table	ID Column	Type	Notes
bank_transactions	id	bigint (bigserial)
bank_statements	statement_id	text	{bank_slug}{type_slug}{last4}_{YYYY-MM-DD}
orders	order_id	text	Vendor order number, normalized by _normalize_order_id()
order_items	id	integer (serial)
receipts	id	text	UUID v4
receipt_items	id	text	UUID v4
accounts	id	integer (serial)
account_balances	id	bigint (bigserial)
spend_budgets, annual_budgets	id	bigint (bigserial)	Correct pattern for new tables
mortgage_statements	id	bigint (bigserial)
item_category_cache	cache_key	text	ASIN when known, else sha1 of the normalized title

Rule for new tables: bigserial (preferred) or uuid. Existing inconsistencies are grandfathered.

orders.order_id has no ON UPDATE

order_items.order_id references orders.order_id with ON DELETE CASCADE and no ON UPDATE, so Postgres rejects a plain UPDATE of the key. Rewriting an order ID means copy the row under the new key, repoint the children, drop the old row, in that order. repair_order_data.py --normalize-ids does exactly this.

---

Source File Tracking

Every ingest pipeline stores the source filename (not the full path) for audit trail.

Table	Column
bank_statements	source_file
bank_transactions	source_file
account_balances	source_file
mortgage_statements	source_file
receipts	filename
statement_files_processed	original_filename plus file_hash for dedupe

---

Account References

accounts is the reference table. Its real columns are id, name, nickname, institution, account_type, account_last4, display_group, is_active, statement_frequency, created_at.

There is still no FK from bank_statements or bank_transactions to accounts. Statements match accounts by institution + account_last4 + account_type string comparison at ingest time. account_balances.account_id is the one real FK.

Rule for new tables: reference accounts.id. No more bare account_last4 strings.

---

Quick Reference: What to Use When

Scenario	Use
New table with a date field	date if no time; timestamptz if time matters
New table with a money field	numeric(12,2)
New table primary key	bigserial
Storing ingest source	source_file text (filename only)
Category value	Exact string from CATEGORY_ICONS in categories.py
Account reference	accounts.id FK
Timestamps for created/updated	timestamptz DEFAULT now()