Round five: “now add loans, overdraft, liens”

CREATE TABLE holds (
  id           UUID PRIMARY KEY,
  account_id   UUID NOT NULL,
  currency     CHAR(3) NOT NULL,
  amount       BIGINT NOT NULL CHECK (amount > 0),
  kind         TEXT NOT NULL,      -- card_auth | lien | pending_debit
  state        TEXT NOT NULL,      -- active | captured | released | expired
  -- card auths expire by network rule (7 days for most, 30 for hotels).
  -- a lien has no expiry: it is released only by instruction.
  expires_at   TIMESTAMPTZ,
  reference    TEXT,                -- network auth code, or court order number
  journal_id   UUID,                -- set when captured: links hold → movement
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- the index that makes the available-balance query cheap
CREATE INDEX holds_active ON holds (account_id, currency)
  WHERE state = 'active';

-- available balance, in one query. the partial index keeps it fast.
SELECT
  b.ledger
  - COALESCE(h.held, 0)
  + COALESCE(o.unused, 0) AS available
FROM (SELECT ledger_balance($1, $2) AS ledger) b
LEFT JOIN (
  SELECT SUM(amount) AS held FROM holds
   WHERE account_id = $1 AND currency = $2 AND state = 'active'
) h ON TRUE
LEFT JOIN (
  SELECT limit_amount - used AS unused FROM overdraft_facilities
   WHERE account_id = $1 AND state = 'active'
) o ON TRUE;

CREATE TABLE overdraft_facilities (
  account_id     UUID PRIMARY KEY,
  limit_amount   BIGINT NOT NULL CHECK (limit_amount >= 0),
  currency       CHAR(3) NOT NULL,
  state          TEXT NOT NULL,    -- active | suspended | closed
  interest_bps   INT NOT NULL,     -- basis points per annum on drawn amount
  -- which transaction kinds may draw on the facility. a customer may
  -- go overdrawn for a POS purchase but not to send money out.
  allowed_kinds  TEXT[] NOT NULL DEFAULT '{pos,card,direct_debit}',
  expires_at     TIMESTAMPTZ,
  granted_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

// the check, with the kind gate. note it stays a single condition
// evaluated inside the posting statement, exactly as in round one.
function floorFor(acct: Account, kind: TxKind, od: Facility | null): bigint {
  if (!od || od.state !== 'active') return 0n;
  if (od.expiresAt && od.expiresAt < now()) return 0n;
  // the gate: this transaction kind is not permitted to draw the facility
  if (!od.allowedKinds.includes(kind)) return 0n;
  return -od.limitAmount;
}

-- and in the posting transaction, the floor is a parameter
INSERT INTO entries (journal_id, account_id, amount, currency)
SELECT $jid, $from, -$amt, $ccy
 WHERE (SELECT COALESCE(SUM(amount),0) FROM entries
          WHERE account_id = $from AND currency = $ccy)
       - $amt >= $floor;   -- 0, or −limit for permitted kinds

// loans service → ledger service. one call, fully specified, idempotent.
await ledger.post({
  idempotencyKey: `loan-disb-${loanId}`,   // natural key: one per loan
  kind: 'loan.disbursement',
  entries: [
    { account: `loan_receivable:${loanId}`, amount: '-50000000' },
    { account: `wallet:${customerWallet}`,  amount:  '49500000' },
    { account: 'fee_income:origination',     amount:    '500000' }
  ],
  currency: 'NGN',
  metadata: { loan_id: loanId, product: 'salary_advance' }
});
// the ledger validates that entries sum to zero and REFUSES if they do not.
// a caller cannot break the invariant even with a bug, which is why the
// "one writer" rule from round one matters more with every new product.

// splitting 100 kobo three ways: 33, 33, 33 loses 1 kobo.
// the largest-remainder method assigns it deterministically, and the
// total is guaranteed to match, which keeps the invariant intact.
function splitExact(total: bigint, weights: bigint[]): bigint[] {
  const sum = weights.reduce((a, b) => a + b, 0n);
  const base = weights.map(w => (total * w) / sum);   // integer division
  let remainder = total - base.reduce((a, b) => a + b, 0n);
  // distribute the remainder to the largest fractional parts first
  const order = weights
    .map((w, i) => ({ i, frac: (total * w) % sum }))
    .sort((a, b) => (b.frac > a.frac ? 1 : -1));
  for (const { i } of order) {
    if (remainder === 0n) break;
    base[i] += 1n; remainder -= 1n;
  }
  return base;   // sums to total, exactly, always
}

// atomic check-and-increment in one Redis round trip. doing this as
// GET then INCR would be exactly the lost-update bug from Part 2.
const VELOCITY = `
  local current = tonumber(redis.call('GET', KEYS[1]) or '0')
  local amount  = tonumber(ARGV[1])
  local cap     = tonumber(ARGV[2])
  if current + amount > cap then
    return {0, current}                    -- denied, report the current value
  end
  redis.call('INCRBY', KEYS[1], amount)
  redis.call('EXPIRE', KEYS[1], ARGV[3], 'NX')   -- set TTL only on creation
  return {1, current + amount}             -- allowed
`;

ALTER TABLE accounts
  ADD COLUMN gl_code TEXT,          -- '2001' customer deposits
  ADD COLUMN gl_class TEXT;         -- asset|liability|equity|income|expense

-- the trial balance: the report finance asks for, straight from entries.
SELECT a.gl_class, a.gl_code, SUM(e.amount) AS balance
  FROM entries e
  JOIN accounts a ON a.id = e.account_id
 WHERE e.created_at < $as_of
 GROUP BY a.gl_class, a.gl_code
 ORDER BY a.gl_class, a.gl_code;
-- and the sum of ALL of it must still be zero. that is the trial balance
-- proving out, and it is the same invariant from chapter 16.

-- reversal: never UPDATE, never DELETE. always a new, linked journal.
INSERT INTO journal (id, kind, idempotency_key, reverses_journal_id)
VALUES ($newJid, 'transfer.reversal', 'rev-' || $origJid, $origJid);
-- then the opposite entries. history is append-only: both the original
-- and the correction are permanently visible, which is what an auditor
-- requires and what "REVOKE UPDATE, DELETE" from Part 2 enforces.