siddharthd
c82a22767f
Ingestion now runs on the rebuilt parser. Three substantive changes. Deferred card reconciliation. A 'MasterCard 8032 and/or credits' receipt never states the split, but the card leg lands on the statement — Subway's $29.08 order shows $13.06 on 8032, so $16.02 was credits. For a live order that statement is weeks away, so the split cannot be settled at ingest time. Such orders are now parked with provenance and no transaction, and reconcilePendingOrders() resolves them once the statement arrives. Backfill takes the same path and resolves immediately. Migration 0019 adds the columns that make an order resumable; applied to personal_test only, prod untouched. Payment detection bug, found by the new tests: the old regex delimited the 'Paid with' line on a double space, which whitespace collapsing removes. Every card and mixed receipt fell through to the credits branch — the Woolworths receipt booked $60.93 of credits spend that never happened. Category resolution reversed deliberately. Correction 1 said never default to dining; the implementation of that sent everything unrecognised to 'other', and knowing six merchants meant Carl's Jr, Taco Bell, Chilli India, Oporto, Schnitz and Souvlaki GR all landed there. Grocers are an enumerable set and restaurants are not, so match groceries explicitly and let the residual be dining. Tests rebuilt on real captured receipts; the synthetic fixtures are deleted. 60 unit + 41 integration green on three consecutive runs.
18 lines
954 B
SQL
18 lines
954 B
SQL
-- Deferred card reconciliation.
|
|
--
|
|
-- An order paid "MasterCard Ending in 8032 and/or credits" does not state the
|
|
-- split. The split is recoverable from the card statement -- but for a live
|
|
-- order that statement is weeks away, so the split cannot be resolved at ingest
|
|
-- time. These columns let an order be parked and revisited.
|
|
|
|
ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS card_last4 TEXT;
|
|
ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS currency TEXT;
|
|
ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS flags JSONB NOT NULL DEFAULT '[]';
|
|
ALTER TABLE expense_metadata ADD COLUMN IF NOT EXISTS reconciled_at TIMESTAMPTZ;
|
|
|
|
-- The pending set: provenance recorded, no transaction yet, still waiting on a
|
|
-- statement line. Partial so it stays small regardless of table growth.
|
|
CREATE INDEX IF NOT EXISTS idx_expense_metadata_pending
|
|
ON expense_metadata (transaction_date)
|
|
WHERE transaction_id IS NULL AND reconciled_at IS NULL;
|