siddharthdandClaude Opus 5.5
c25c82932d
ci / lint-test (push) Successful in 54s
A card notification is sent for an authorisation; a released hold never reaches a statement. Txn 7951 (a $1,040.41 Airbnb hold, booking cancelled the next day) sat in the reconcile queue forever and counted as spend. Neither reconcile (needs a statement twin) nor supersede (needs a survivor) can express it. - transactions.voided_at + void_reason; CHECKs: reason mandatory, statementless only - EXCLUDE_RECONCILED_SOURCE excludes voided rows; reconcile queue, findAutoMatches, both adoption helpers, annual charges and the manual reconcile POST filter it; merge refuses a voided row on either side - POST/DELETE /api/transactions/<id>/void; refuses reconciled, superseded and settled-split rows - "Never posted" button on /reconcile - wallet-ingestion deliberately still sees voided rows, so a late twin or a prompt_version re-post cannot rebook the hold Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
44 lines
2.2 KiB
SQL
44 lines
2.2 KiB
SQL
-- A statementless row for a charge that never posted.
|
|
--
|
|
-- WHY: the wallet lane books every card notification as a purchase, and a card
|
|
-- notification is sent for an AUTHORISATION, not a settlement. When the
|
|
-- merchant releases the hold the bank never posts the charge, so no statement
|
|
-- line ever arrives: the row sits in the reconcile queue forever and counts as
|
|
-- spend in every figure. Txn 7951: Airbnb HMXAHAW49Y, $1,040.41 held on Amex
|
|
-- 1000 on 2026-09-24, booking cancelled 25 Sep inside the free window, hold
|
|
-- released ("We removed the $1,040.41 (AUD) payment hold"). Statement 219
|
|
-- (11 Sep - 10 Oct) has no such line and reconciles to its header without it.
|
|
--
|
|
-- Neither existing exclusion fits. reconciled_with_id needs a statement twin
|
|
-- and superseded_by_id needs a surviving row (and may not point at itself,
|
|
-- transactions_no_self_supersede). There is no survivor: nothing happened.
|
|
--
|
|
-- Not a DELETE: every child of transactions cascades, and the row is also the
|
|
-- sink the wallet lane checks before re-posting an event. Deleting it would let
|
|
-- the next prompt_version bump re-create it.
|
|
|
|
ALTER TABLE transactions
|
|
ADD COLUMN IF NOT EXISTS voided_at timestamptz,
|
|
ADD COLUMN IF NOT EXISTS void_reason text;
|
|
|
|
COMMENT ON COLUMN transactions.voided_at IS
|
|
'Set when this statementless row records a charge that never posted (e.g. a released authorisation hold). Excluded from every figure and from the reconcile queue, kept for its children and audit trail. NULL = live.';
|
|
|
|
-- A reason is mandatory: a void with no explanation is indistinguishable from
|
|
-- a mistake six months later.
|
|
ALTER TABLE transactions
|
|
ADD CONSTRAINT transactions_void_has_reason
|
|
CHECK ((voided_at IS NULL AND void_reason IS NULL)
|
|
OR (voided_at IS NOT NULL AND btrim(coalesce(void_reason, '')) <> ''));
|
|
|
|
-- Statement rows cannot be voided. A statement line is the bank's own record
|
|
-- of a posted charge, and the statement's rows must keep summing to its
|
|
-- header; a wrong statement row is a supersede, not a void.
|
|
ALTER TABLE transactions
|
|
ADD CONSTRAINT transactions_void_statementless
|
|
CHECK (voided_at IS NULL OR statement_id IS NULL);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_transactions_voided
|
|
ON transactions (voided_at)
|
|
WHERE voided_at IS NOT NULL;
|