siddharthd
dbc6fd1352
ci / lint-test (push) Successful in 51s
The CSV importer has no idempotency and structurally cannot have any: batchInsertCSVTransactions assigns row_index = MAX(row_index) + 1, and row_index is the fifth column of uq_transaction_identity, so the constraint is guaranteed a fresh value on every run. The same file imported twice produces two full sets of rows and nothing objects. Tolerable for the hand-driven bank CSV this was built for. Not tolerable for any recurring feed, whose windows overlap by design — and not cleanable afterwards, since every child of transactions is ON DELETE CASCADE, so a duplicate must be superseded rather than deleted (0023). ~$42k of re-imported statement rows already show how that goes. source_ref carries the provider's own key verbatim, enforced by a partial unique index rather than an application-side ON CONFLICT that a refactor can quietly drop. Found while evaluating Frollo (DECISIONS.md ING-11 in the smarthome repo). That feed is not being ingested — 88% of it duplicates existing statement coverage — but this gap is real independently of it.
55 lines
3.0 KiB
SQL
55 lines
3.0 KiB
SQL
-- A feed that re-sends yesterday's rows needs an identity the importer can recognise.
|
|
--
|
|
-- The CSV import that exists today is built for a one-off: `batchInsertCSVTransactions`
|
|
-- assigns `row_index` as MAX(row_index) + 1 over the owner's manual rows, so the same
|
|
-- file imported twice produces two sets of rows with different indexes. That defeats
|
|
-- `uq_transaction_identity` (statement_id, transaction_date, description, amount,
|
|
-- row_index) by construction — the constraint cannot fire, because the fifth column is
|
|
-- guaranteed fresh on every run. Nothing else stops it either.
|
|
--
|
|
-- For a hand-driven bank CSV that is tolerable; the operator sees the file once. For a
|
|
-- recurring aggregator export it is not, because the windows overlap *by design*: a
|
|
-- 12-month export pulled weekly re-states ~51 weeks of rows it has already sent. The
|
|
-- failure would be silent and cumulative, and it is not one we can clean up afterwards —
|
|
-- every child of `transactions` is ON DELETE CASCADE, so a duplicate must be superseded
|
|
-- rather than deleted (migration 0023), and ~$42k of re-imported statement rows already
|
|
-- exist as evidence of how this goes.
|
|
--
|
|
-- So the provider's own key travels with the row. Frollo issues a stable per-transaction
|
|
-- `id`, which is exactly what the wallet-capture lane lacked and could never synthesise
|
|
-- from a notification. The same two columns serve any future feed that has one.
|
|
|
|
ALTER TABLE transactions
|
|
ADD COLUMN IF NOT EXISTS source TEXT;
|
|
|
|
COMMENT ON COLUMN transactions.source IS
|
|
'Feed this row was ingested from (e.g. ''frollo''). NULL = entered by hand, imported from a statement, or predates the column.';
|
|
|
|
ALTER TABLE transactions
|
|
ADD COLUMN IF NOT EXISTS source_ref TEXT;
|
|
|
|
COMMENT ON COLUMN transactions.source_ref IS
|
|
'The provider''s own identifier for this transaction, verbatim. Idempotency key for re-imports; never generated locally.';
|
|
|
|
-- Partial, so the millions of rows with no source do not have to be unique on (NULL,
|
|
-- NULL). Enforced in the database rather than in the importer: an ON CONFLICT DO NOTHING
|
|
-- that silently depends on application-side dedupe is one refactor away from not.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_transaction_source_ref
|
|
ON transactions (source, source_ref)
|
|
WHERE source IS NOT NULL AND source_ref IS NOT NULL;
|
|
|
|
-- A source row is only half-identified without knowing which account it came from --
|
|
-- two accounts at the same institution can legitimately carry the same provider id
|
|
-- namespace. Kept as free text rather than a foreign key: finance-app has no account
|
|
-- entity, and inventing one to hold a label from an external system would be the tail
|
|
-- wagging the dog.
|
|
ALTER TABLE transactions
|
|
ADD COLUMN IF NOT EXISTS source_account TEXT;
|
|
|
|
COMMENT ON COLUMN transactions.source_account IS
|
|
'Account label as the source system names it, for provenance and for scoping an import to particular accounts. Not an entity reference.';
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_source
|
|
ON transactions (source, transaction_date)
|
|
WHERE source IS NOT NULL;
|