Round ten: “prove the money is right”

-- level 1: every journal balances, per currency. runs every 30 seconds
-- over recent entries only, so it is cheap and continuous.
SELECT journal_id, currency, SUM(amount) AS drift
  FROM entries
 WHERE created_at > now() - interval '5 minutes'
 GROUP BY journal_id, currency
HAVING SUM(amount) <> 0;
-- any row is a P1 page. this cannot happen unless something is very wrong.

-- level 2: the WHOLE ledger balances, per currency, per region.
-- runs hourly against a replica. this is the real trial balance.
SELECT currency, SUM(amount) AS total
  FROM entries
 GROUP BY currency
HAVING SUM(amount) <> 0;

-- level 3: assets = liabilities + equity, the accounting identity.
SELECT a.gl_class, SUM(e.amount)
  FROM entries e JOIN accounts a ON a.id = e.account_id
 WHERE e.currency = $1
 GROUP BY a.gl_class;
-- assets + liabilities + equity + income + expenses must = 0
-- with our signed convention. finance reads this one directly.

-- the most valuable single query in the system: money that has been
-- taken from a customer and is sitting in limbo.
SELECT a.kind, e.journal_id, e.currency,
       SUM(e.amount) AS stuck,
       MIN(e.created_at) AS since,
       now() - MIN(e.created_at) AS age
  FROM entries e
  JOIN accounts a ON a.id = e.account_id
 WHERE a.kind IN ('in_transit', 'settle_suspense', 'dispute_suspense')
 GROUP BY a.kind, e.journal_id, e.currency
HAVING SUM(e.amount) <> 0
   AND MIN(e.created_at) < now() - interval '2 minutes'
 ORDER BY age DESC;

-- pass 1 as a single set-based statement. never loop in the application:
-- 400,000 statement lines against 30 million entries is a JOIN, not a for.
UPDATE statement_lines s
   SET matched_entry_id = e.id, match_pass = 1, match_confidence = 1.0
  FROM entries e
 WHERE s.recon_run_id = $run
   AND s.matched_entry_id IS NULL
   AND e.external_reference = s.reference
   AND e.amount = s.amount
   AND e.currency = s.currency;

CREATE TABLE recon_breaks (
  id              UUID PRIMARY KEY,
  recon_run_id    UUID NOT NULL,
  counterparty    TEXT NOT NULL,
  classification  TEXT NOT NULL,
  amount          BIGINT NOT NULL,
  currency        CHAR(3) NOT NULL,
  statement_line_id UUID,
  ledger_entry_id   BIGINT,
  -- the resolution, and critically WHO decided and WHY.
  state           TEXT NOT NULL,   -- open|investigating|resolved|written_off
  resolution_journal_id UUID,       -- the correcting entries, if any
  resolved_by     TEXT,
  resolution_note TEXT,
  -- ageing is the metric that matters operationally
  first_seen_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  resolved_at     TIMESTAMPTZ
);

CREATE INDEX breaks_open ON recon_breaks (first_seen_at)
  WHERE state <> 'resolved';

-- the journal carries its own provenance, so a correction is always
-- traceable to what it corrects and why.
ALTER TABLE journal
  ADD COLUMN reverses_journal_id UUID REFERENCES journal(id),
  ADD COLUMN adjusts_journal_id  UUID REFERENCES journal(id),
  ADD COLUMN break_id            UUID,   -- which break prompted this
  ADD COLUMN approved_by         TEXT;   -- four-eyes on corrections

-- and the net effect of an original plus its corrections is a query,
-- not a stored "current value" that could disagree with its history.
WITH RECURSIVE chain AS (
  SELECT id, reverses_journal_id, adjusts_journal_id FROM journal WHERE id = $1
  UNION ALL
  SELECT j.id, j.reverses_journal_id, j.adjusts_journal_id
    FROM journal j JOIN chain c
      ON j.reverses_journal_id = c.id OR j.adjusts_journal_id = c.id
)
SELECT SUM(e.amount) AS net_effect, e.currency
  FROM entries e WHERE e.journal_id IN (SELECT id FROM chain)
 GROUP BY e.currency;