Round seven: “multi-currency, multi-jurisdiction”

-- currency lives on the ACCOUNT. an entry cannot disagree with it.
CREATE TABLE accounts (
  id        UUID PRIMARY KEY,
  owner_id  UUID,
  kind      TEXT NOT NULL,
  currency  CHAR(3) NOT NULL REFERENCES currencies(code),
  -- one wallet per owner per currency. the pair IS the identity.
  UNIQUE (owner_id, kind, currency)
);

-- the entry still carries currency, and the database ENFORCES that it
-- matches the account. belt and braces, because this is the invariant
-- that protects every other one.
ALTER TABLE entries ADD CONSTRAINT entry_currency_matches_account
  CHECK (TRUE) -- enforced by trigger or by a composite FK:;

ALTER TABLE entries
  ADD FOREIGN KEY (account_id, currency)
  REFERENCES accounts (id, currency);   -- requires UNIQUE(id, currency)

CREATE TABLE currencies (
  code      CHAR(3) PRIMARY KEY,      -- ISO 4217
  exponent  SMALLINT NOT NULL,        -- 0, 2, 3, 4. NEVER assumed.
  name      TEXT NOT NULL,
  -- some currencies are quoted per 100 units by convention (JPY pairs).
  -- display concerns belong here too, not scattered in the frontend.
  symbol    TEXT,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

// formatting reads the exponent. it never divides by 100.
function format(minor: bigint, ccy: Currency): string {
  if (ccy.exponent === 0) return minor.toString();
  const divisor = 10n ** BigInt(ccy.exponent);
  const whole = minor / divisor;
  const frac  = (minor < 0n ? -minor : minor) % divisor;
  return `${whole}.${frac.toString().padStart(ccy.exponent, '0')}`;
}

// and parsing is the inverse, also driven by the exponent. a user typing
// "100" in JPY means 100 minor units; in KWD it means 100000.

-- the invariant, correctly stated. must return zero rows, forever.
SELECT journal_id, currency, SUM(amount) AS drift
  FROM entries
 GROUP BY journal_id, currency
HAVING SUM(amount) <> 0;

-- and the in-transaction assertion updated to match
DO $$ BEGIN
  IF EXISTS (
    SELECT 1 FROM entries
     WHERE journal_id = $jid
     GROUP BY currency
    HAVING SUM(amount) <> 0
  ) THEN RAISE EXCEPTION 'ledger imbalance per currency on %', $jid;
  END IF;
END $$;

CREATE TABLE fx_conversions (
  journal_id    UUID PRIMARY KEY REFERENCES journal(id),
  from_currency CHAR(3) NOT NULL,
  from_amount   BIGINT NOT NULL,
  to_currency   CHAR(3) NOT NULL,
  to_amount     BIGINT NOT NULL,
  -- the rate as an exact decimal, never a float. and BOTH rates:
  -- what we charged the customer, and what the market was.
  customer_rate NUMERIC(20,10) NOT NULL,
  market_rate   NUMERIC(20,10) NOT NULL,
  quote_id      UUID NOT NULL,   -- which quote authorised this
  quoted_at     TIMESTAMPTZ NOT NULL
);

CREATE TABLE fx_quotes (
  id            UUID PRIMARY KEY,
  pair          TEXT NOT NULL,       -- 'NGN/USD'
  customer_rate NUMERIC(20,10) NOT NULL,
  market_rate   NUMERIC(20,10) NOT NULL,
  max_amount    BIGINT NOT NULL,     -- a quote is size-bounded
  customer_id   UUID,                 -- quotes are not transferable
  -- short. 30 to 120 seconds. an expired quote CANNOT be executed.
  expires_at    TIMESTAMPTZ NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- execution atomically consumes the quote, so a quote cannot be used twice.
-- this is the Part 2 conditional write, applied to a quote instead of a balance.
UPDATE fx_quotes SET consumed_at = now()
 WHERE id = $quoteId
   AND consumed_at IS NULL
   AND expires_at > now()
   AND customer_id = $customer
   AND max_amount >= $amount;
-- 0 rows ⇒ expired, already used, wrong customer, or too large. reject.

// this is the shape that becomes unmaintainable by the third country,
// and untestable by the fourth.
if (region === 'ng') {
  if (amount > 5_000_000_00n) requireReport();
  if (tier === 1 && amount > 50_000_00n) reject();
} else if (region === 'eu') {
  if (amount > 10_000_00n) requireReport();
  ...
}

CREATE TABLE compliance_policies (
  id             UUID PRIMARY KEY,
  jurisdiction   CHAR(2) NOT NULL,
  rule_type      TEXT NOT NULL,   -- limit | report | screen | retain
  applies_to     JSONB NOT NULL,  -- {kind, currency, kyc_tier, corridor}
  parameters     JSONB NOT NULL,  -- {threshold, window, report_to}
  -- rules change by regulation, and past decisions must remain
  -- explicable under the rule that applied AT THE TIME.
  effective_from TIMESTAMPTZ NOT NULL,
  effective_to   TIMESTAMPTZ,
  source_ref     TEXT              -- the circular or directive it implements
);

// every compliance decision is RECORDED, with the rule version used.
// "why was this blocked?" must be answerable years later.
interface ComplianceDecision {
  journalId: string;
  jurisdiction: string;
  outcome: 'allow' | 'block' | 'allow_with_report' | 'review';
  rulesEvaluated: { policyId: string; version: string; matched: boolean }[];
  decidedAt: Date;
  // if a human overrode the engine, who, when, and on what authority.
  override?: { by: string; reason: string; approvedBy: string };
}