Round seventeen: “fees, the company books, and who pays for what”

CREATE TABLE fee_schedules (
  id             UUID PRIMARY KEY,
  fee_code       TEXT NOT NULL,      -- transfer.nip | card.maintenance
  -- what this schedule applies to. null = any.
  account_type   TEXT,
  kyc_tier       INT,
  channel        TEXT,                -- app | ussd | pos | api
  currency       CHAR(3) NOT NULL,
  -- the band this row prices. bands must tile the range with no gaps.
  min_amount_minor BIGINT NOT NULL DEFAULT 0,
  max_amount_minor BIGINT,
  -- flat + percentage + cap is the general form. most rows use one.
  flat_minor     BIGINT NOT NULL DEFAULT 0,
  rate_bps       INT NOT NULL DEFAULT 0,
  cap_minor      BIGINT,
  -- the regulatory instrument that constrains this price, if any
  regulatory_ref TEXT,
  effective_from TIMESTAMPTZ NOT NULL,
  effective_to   TIMESTAMPTZ
);

// fee reversal follows the transaction reversal, by policy, and it is
// ALWAYS a new journal linked to the original. never an edit.
async function reverseWithFee(original: Journal, reason: Reason) {
  const refundFee = policy.refundFeeOn(reason);
  // our fault → refund. customer changed their mind → usually not.

  await ledger.post({
    idempotencyKey: `rev-${original.id}`,
    kind: JournalKind.TRANSFER_REVERSAL,
    reversesJournalId: original.id,
    entries: [
      ...reverseEntriesOf(original, { includeFee: refundFee })
    ]
  });
}

-- the single most important query in the company books. it is the
-- Part 10 machinery pointed at solvency rather than at drift.
WITH owed AS (
  SELECT e.currency, SUM(e.amount) AS customer_funds
    FROM entries e JOIN accounts a ON a.id = e.account_id
   WHERE a.gl_code LIKE '21%'     -- customer deposits
      OR a.gl_code LIKE '29%'     -- suspense: somebody's money
      OR a.gl_code LIKE '22%'     -- pass-through payables
   GROUP BY e.currency
), held AS (
  SELECT e.currency, -SUM(e.amount) AS liquid_assets
    FROM entries e JOIN accounts a ON a.id = e.account_id
   WHERE a.gl_code LIKE '11%' OR a.gl_code LIKE '12%'
   GROUP BY e.currency
)
SELECT o.currency, h.liquid_assets, o.customer_funds,
       h.liquid_assets - o.customer_funds AS buffer
  FROM owed o JOIN held h USING (currency)
 WHERE h.liquid_assets < o.customer_funds;   -- must return ZERO rows

-- layer 5: sample and compare. cheap, and it catches cache and
-- projection bugs that every other reconciliation is blind to.
async function servingDriftCheck(sampleSize = 5000) {
  const accounts = await sampleRandomAccounts(sampleSize);
  const drifts = [];

  for (const a of accounts) {
    const served   = await bigtable.currentBalance(a.id);     // what they see
    const computed = await ledger.balanceFromEntries(a.id); // the truth
    if (served !== computed) drifts.push({ a, served, computed });
  }

  // any drift at all is a bug. it is not a tolerance.
  if (drifts.length) await alerts.page('serving.balance_drift', { drifts });
}

-- contribution per transaction: every income and expense entry that
-- shares a journal_id with the transaction, summed.
SELECT j.kind, j.metadata->>'channel' AS channel,
       COUNT(DISTINCT j.id)                          AS txn_count,
       SUM(CASE WHEN a.gl_code LIKE '4%' THEN e.amount ELSE 0 END) AS income,
       SUM(CASE WHEN a.gl_code LIKE '5%' THEN e.amount ELSE 0 END) AS expense,
       SUM(CASE WHEN a.gl_code LIKE '4%' OR a.gl_code LIKE '5%'
                THEN e.amount ELSE 0 END)             AS contribution
  FROM journal j
  JOIN entries e ON e.journal_id = j.id
  JOIN accounts a ON a.id = e.account_id
 WHERE j.created_at >= $from AND j.created_at < $to
 GROUP BY 1, 2
 ORDER BY contribution;   -- the loss-makers first. always look here.