siddharthd
5076f27f68
ci / lint-test (push) Successful in 56s
Answers "what annual payments are coming?" without touching the email lane --
a bank announces an annual fee on the statement and nowhere else.
WHAT DOES NOT WORK, measured before writing it: "two charges ~365 days apart"
ranks a BAKERY first on this ledger, with 202 such pairs, then a donation,
Patreon, Coles. Frequency is the confound. Tightening to "every gap is annual"
still admits a Target visited twice ($30, then $198).
transaction_type='fee' is the universe, not a description match. The extractor
already classifies fees structurally (131 rows) and every card and loan annual
fee here is typed that way -- the first cut of this filtered
transaction_type='debit' and returned NONE of them. The fees are not debits.
Three confidence tiers, kept apart so a guess never renders like a bank fact:
confirmed typed a fee AND repeated on an annual cadence
likely typed a fee, seen once, and NAMED annual/card/package -- one charge
cannot show a cadence but the kind is not in doubt
possible no fee wording; merchant whose every gap is annual AND whose amount
is stable (<=20%, absorbing VicRoads' ~3%/yr drift). Catches Amazon
Prime and rego; also where the false positives live, hence the label.
expected_not_seen is the more interesting status: the estimate passed and
nothing landed. account_days_stale is the discriminator -- Westpac at 0 days is
trustworthy, ANZ at 392 is simply missing data. Reporting those as upcoming
would be a lie.
account_links (migration 0038) fixes account continuity. Citi Prestige
5258...0253 became NAB MyCard 1015...6227 -- same card, same 14th-to-13th cycle,
no gap -- so the $700 fee charged 2024-12-13 and 2025-11-13 read as two dead
one-offs instead of one due again ~Nov 2026. Hand-asserted with source_documents
ids as evidence rather than inferred: a contiguous-cycle heuristic would join two
genuinely different accounts as readily as one renamed one. Display resolves to
the CURRENT bank; naming the retired brand sends you to the wrong bank.
The GET takes its own DASH_READ_TOKEN with no fallback to WALLET_INGEST_TOKEN --
that one unlocks wallet ingest and the estate-wide reconcile sweep, and
home-dashboard's compose states the rule: it never holds a write credential.
Known limit: the merchant signal fragments on label drift (VicRoads paid as
VICROADS then VICROADS ONLINE PAYM). Not fuzzy-matched, per the rejected pg_trgm
merchant grouping.
339 integration + 196 unit green, tsc clean. Board #265.
77 lines
4.0 KiB
SQL
77 lines
4.0 KiB
SQL
-- Account continuity across a rebrand, reissue or issuer change.
|
|
--
|
|
-- WHY THIS EXISTS: one card can appear in `statements` under two completely
|
|
-- different identities. The live case is the Citi Prestige card, which moved to
|
|
-- NAB and was rebranded MyCard:
|
|
--
|
|
-- Citibank 5258073330340253 statements ... 2025-11-13
|
|
-- NAB 1015001000116227 statements 2025-11-14 ...
|
|
--
|
|
-- Same 14th-to-13th billing cycle, no gap, one continuous product. Nothing in
|
|
-- the schema linked them, so anything grouping by (bank_name, account) sees a
|
|
-- dead account and a new one with no history. For the annual-fee detector that
|
|
-- is not cosmetic: the $700 fee charged 2024-12-13 and 2025-11-13 reads as
|
|
-- "stopped" when it is due again around November 2026 under the new name.
|
|
--
|
|
-- WHY A TABLE AND NOT AN INFERENCE: contiguous-cycle heuristics would join two
|
|
-- genuinely different accounts as readily as one renamed one, and a wrong join
|
|
-- merges two real fee series. There are only a handful of these events in a
|
|
-- lifetime, each announced by mail, so each link is asserted once with the
|
|
-- document that proves it. `evidence` is not decoration -- it is how a future
|
|
-- reader checks the claim instead of trusting it.
|
|
--
|
|
-- NOT `account_owner_mappings`: that answers "whose account is this" and keys on
|
|
-- the raw (bank_name, account_number). This answers "is this the same account as
|
|
-- that one", and keys on the normalised form.
|
|
|
|
CREATE TABLE IF NOT EXISTS account_links (
|
|
id bigserial PRIMARY KEY,
|
|
-- Normalised the same way statements.account_number_key is generated
|
|
-- (non-alphanumerics stripped), so these join without per-call cleanup.
|
|
-- Amex-style masks keep their X's: they record which digits were redacted.
|
|
from_bank_name text NOT NULL,
|
|
from_account_key text NOT NULL,
|
|
to_bank_name text NOT NULL,
|
|
to_account_key text NOT NULL,
|
|
-- When the successor took over. The predecessor's last statement and the
|
|
-- successor's first are the observable boundary; this is the announced date
|
|
-- where mail gives one.
|
|
effective_date date,
|
|
reason text NOT NULL
|
|
CHECK (reason IN ('rebrand', 'reissue', 'issuer_change', 'migration')),
|
|
-- Provenance for the claim, e.g. 'source_documents:32919'. Free text because
|
|
-- the evidence is not always a document -- sometimes it is a statement pair.
|
|
evidence text,
|
|
notes text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
|
|
-- One successor per predecessor: a chain, never a fork. A fork would make
|
|
-- "the current identity of this account" ambiguous, and the resolver walks
|
|
-- this forward assuming it is not.
|
|
CONSTRAINT uq_account_links_from UNIQUE (from_bank_name, from_account_key),
|
|
-- A link to itself would make that walk non-terminating.
|
|
CONSTRAINT account_links_no_self CHECK (
|
|
from_bank_name IS DISTINCT FROM to_bank_name
|
|
OR from_account_key IS DISTINCT FROM to_account_key
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE account_links IS
|
|
'Predecessor -> successor account identity across rebrands/reissues. Hand-asserted, each row carrying the evidence for its claim.';
|
|
|
|
CREATE INDEX IF NOT EXISTS account_links_to
|
|
ON account_links (to_bank_name, to_account_key);
|
|
|
|
-- The one link the mail record proves today.
|
|
-- source_documents 32919 (2025-10-01) "your Citi Prestige Card account is moving to MyCard"
|
|
-- source_documents 32618 (2025-11-10) "MyCard is coming soon"
|
|
-- source_documents 32539 (2025-11-24) "The new MyCard App and MyCard Online are now live"
|
|
-- Statement boundary: Citibank ends 2025-11-13, NAB begins 2025-11-14.
|
|
INSERT INTO account_links
|
|
(from_bank_name, from_account_key, to_bank_name, to_account_key, effective_date, reason, evidence, notes)
|
|
VALUES
|
|
('Citibank', '5258073330340253', 'NAB', '1015001000116227', '2025-11-14', 'issuer_change',
|
|
'source_documents:32919,32618,32539',
|
|
'Citi Prestige -> MyCard Prestige. Billing cycle (14th-13th) continues unbroken across the boundary.')
|
|
ON CONFLICT (from_bank_name, from_account_key) DO NOTHING;
|