siddharthdandClaude Opus 5.5
a3e4e1102a
ci / lint-test (push) Successful in 54s
Four defects found reviewing a month of overseas wallet rows: - Race: n8n posts a poll's items concurrently and both halves of a tap usually share a poll, so each ran findPairedRow before the other inserted. 11 taps became 22 rows (A$3,841.40), identical created_at on every pair. One advisory lock now covers re-check -> pair -> adopt -> insert, on the transaction's own connection (a second pooled one per request deadlocks a burst). Mutation-tested: without the lock the concurrent test fails 3/3. - Pair window: amount within +/-1 DAY folded the UNICEF $50 donation and an unrelated $50 Amex charge 17h later into one row and renamed it. A pair is now minutes apart AND from two different apps. Needs when/where a notification arrived: migration 0039 adds expense_metadata.notified_at / notif_app, backfilled from capture.wallet_events (144 rows). A merged half now records wallet_evt:<id>, so a replay cannot merge it twice. - Not purchases: a CommBank overdraft notice became a $21.21 purchase named "To avoid fees and interest," (the Westpac fixed-width parser ran on a CommBank body). Account notices and transit-card balance drawdowns are skipped; the descriptor parser only runs on Westpac. - FX: with no ledger history (Hong Kong) every tap stored A$0.00. The ECB rate via Frankfurter is the fallback, flagged rate_ecb; the ledger median stays first because it carries the card's FX fee. Also: dates in Australia/Melbourne like the statements (UTC slicing filed 28 of 134 rows a day early), and test:setup now actually rebuilds personal_test -- replaying the dump over it never applied a migration. Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
27 lines
1.2 KiB
SQL
27 lines
1.2 KiB
SQL
-- When, and from which app, a wallet notification arrived.
|
|
--
|
|
-- WHY: one tap emits two notifications 2-8 seconds apart -- the issuer app
|
|
-- (card tail) and Apple Wallet (merchant, suburb) -- and wallet-ingestion.ts
|
|
-- merges them. It used to recognise the pair by amount alone, within +/-1 DAY,
|
|
-- which is wide enough to fold two different purchases together: on 2026-10-01
|
|
-- an Amex "$50.00 at A MEDIUM" notification merged into the UNICEF $50 donation
|
|
-- from 17 hours earlier and renamed it. A real pair is seconds apart and comes
|
|
-- from two DIFFERENT apps; two notifications from one app are two charges.
|
|
-- Neither fact was stored anywhere on the ledger side, so the matcher could not
|
|
-- ask. These columns let it.
|
|
--
|
|
-- Backfilled from capture.wallet_events through source_message_id ('evt:<id>'),
|
|
-- which every wallet-written metadata row carries.
|
|
|
|
ALTER TABLE expense_metadata
|
|
ADD COLUMN IF NOT EXISTS notified_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS notif_app text;
|
|
|
|
UPDATE expense_metadata em
|
|
SET notified_at = e.received_at,
|
|
notif_app = e.notif_app
|
|
FROM capture.wallet_events e
|
|
WHERE em.source = 'wallet'
|
|
AND em.source_message_id = 'evt:' || e.id
|
|
AND em.notified_at IS NULL;
|