Round sixteen: “who is this customer, and what may they do?”

-- the account type is a reference to POLICY, not a column of booleans.
-- adding a product later must not mean an ALTER TABLE.
CREATE TABLE account_types (
  code               TEXT PRIMARY KEY,   -- savings | current | wallet | fd …
  display_name       TEXT NOT NULL,
  -- capabilities, as data
  earns_interest     BOOLEAN NOT NULL,
  interest_bps       INT,
  allows_overdraft   BOOLEAN NOT NULL,
  free_withdrawals   INT,                -- null = unlimited
  maintenance_fee_minor BIGINT,
  requires_mandate   BOOLEAN NOT NULL DEFAULT FALSE,
  -- which KYC tiers may hold this type at all
  min_kyc_tier       INT NOT NULL DEFAULT 1,
  effective_from     TIMESTAMPTZ NOT NULL,
  effective_to       TIMESTAMPTZ
);

CREATE TABLE tier_type_policy (
  kyc_tier       INT NOT NULL,
  account_type   TEXT NOT NULL REFERENCES account_types(code),
  permitted      BOOLEAN NOT NULL,
  -- nulls mean "no limit from this dimension"
  max_single_minor     BIGINT,
  max_daily_minor      BIGINT,
  max_monthly_minor    BIGINT,
  max_balance_minor    BIGINT,
  -- which transaction kinds this combination may perform at all
  permitted_kinds TEXT[],
  -- the regulatory instrument this row implements, for audit
  source_ref     TEXT,
  effective_from TIMESTAMPTZ NOT NULL,
  effective_to   TIMESTAMPTZ,
  PRIMARY KEY (kyc_tier, account_type, effective_from)
);

// the rule: the MOST RESTRICTIVE wins, always, and we record WHICH.
// "declined" is unhelpful. "declined: your tier-2 daily limit" is actionable.
interface ResolvedLimit {
  effectiveMinor: bigint;
  bindingSource: 'kyc_tier' | 'account_type' | 'customer' | 'risk';
  allSources: { source: string; limitMinor: bigint | null }[];
}

function resolveLimit(ctx: Ctx, dim: LimitDimension): ResolvedLimit {
  const candidates = [
    { source: 'kyc_tier',     limitMinor: ctx.tierPolicy[dim] },
    { source: 'account_type', limitMinor: ctx.typePolicy[dim] },
    { source: 'customer',     limitMinor: ctx.customerPrefs[dim] },
    { source: 'risk',         limitMinor: ctx.riskRestriction?.[dim] }
  ].filter(c => c.limitMinor != null);

  if (!candidates.length) return { effectiveMinor: UNLIMITED, ... };

  // minimum, not the last one set, not a precedence order.
  const binding = candidates.reduce((a, b) =>
    b.limitMinor! < a.limitMinor! ? b : a);

  return { effectiveMinor: binding.limitMinor!,
           bindingSource: binding.source as any, allSources: candidates };
}

CREATE TABLE mandates (
  id            UUID PRIMARY KEY,
  account_id    UUID NOT NULL,
  -- "any one", "any two", "A and either B or C", or by amount band
  rule_type     TEXT NOT NULL,   -- any_one | any_n | specific_combination
  required_count INT,
  -- larger amounts need more signatures. the common real-world case.
  threshold_minor BIGINT,          -- this rule applies above this amount
  effective_from TIMESTAMPTZ NOT NULL,
  effective_to   TIMESTAMPTZ
);

CREATE TABLE signatories (
  mandate_id  UUID NOT NULL REFERENCES mandates(id),
  customer_id UUID NOT NULL,
  -- class A and class B signatories, for "one of each" rules
  signatory_class TEXT,
  can_initiate BOOLEAN NOT NULL DEFAULT TRUE,
  can_approve  BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (mandate_id, customer_id)
);

-- PND is a restriction with a DIRECTION and a SCOPE, extending the
-- Part 9 restriction model rather than replacing it.
ALTER TABLE account_restrictions
  ADD COLUMN direction TEXT NOT NULL DEFAULT 'debit',  -- debit|credit|both
  ADD COLUMN amount_minor BIGINT,        -- null = the whole balance
  ADD COLUMN scoped_kinds TEXT[],        -- null = all kinds
  ADD COLUMN mode TEXT NOT NULL DEFAULT 'block';   -- block | review

-- and the check on the posting path is direction-aware:
--   a CREDIT entry is unaffected by a debit-direction restriction.
--   a DEBIT entry is refused, or queued, per mode.

// the check, direction-aware, on the posting path. note it inspects
// the SIGN of the entry rather than the operation's name, so a fee
// leg and a transfer leg are treated identically and correctly.
function checkRestrictions(e: EntrySpec, r: Restriction[]): Decision {
  const isDebit = e.amountMinor < 0n;

  for (const x of r) {
    if (x.direction === 'credit' && isDebit) continue;   // not our concern
    if (x.direction === 'debit' && !isDebit) continue;   // credits pass a PND
    if (x.scopedKinds && !x.scopedKinds.includes(e.kind)) continue;

    return x.mode === 'review'
      ? { action: 'queue_for_review', restrictionId: x.id }
      : { action: 'refuse', reasonCode: x.reasonCode, restrictionId: x.id };
  }
  return { action: 'allow' };
}

CREATE TABLE credit_claims (
  id            UUID PRIMARY KEY,
  account_id    UUID NOT NULL,
  claimant      TEXT NOT NULL,      -- which service registered it
  amount_minor  BIGINT NOT NULL,    -- what is owed
  collected_minor BIGINT NOT NULL DEFAULT 0,
  -- lower number = collected first. statutory claims outrank commercial ones.
  priority      INT NOT NULL,
  -- never take the customer below this. the line between recovery and harm.
  protected_floor_minor BIGINT NOT NULL DEFAULT 0,
  -- partial collection allowed, or all-or-nothing?
  allow_partial BOOLEAN NOT NULL DEFAULT TRUE,
  -- the customer agreed to this, and when
  consent_ref   TEXT NOT NULL,
  expires_at    TIMESTAMPTZ,
  state         TEXT NOT NULL       -- active | satisfied | cancelled | expired
);

CREATE INDEX claims_active ON credit_claims (account_id, priority)
  WHERE state = 'active';

// allocation must be ATOMIC across all claims, or two consumers
// processing the same credit event could each allocate the full amount.
await db.transaction(async tx => {
  // one idempotency key per credit event, so a redelivery is a no-op
  const key = `claims-${creditEntryId}`;

  const claims = await tx.query(`
    SELECT * FROM credit_claims
     WHERE account_id = $1 AND state = 'active'
     ORDER BY priority, id
     FOR UPDATE                      -- lock them all, in a stable order
  `, [accountId]);

  let available = creditAmount - protectedFloor;
  const entries: EntrySpec[] = [];

  for (const c of claims) {
    if (available <= 0n) break;
    const outstanding = c.amount_minor - c.collected_minor;
    const take = c.allow_partial ? min(available, outstanding)
                                : (available >= outstanding ? outstanding : 0n);
    if (take === 0n) continue;
    entries.push(...claimEntries(c, take));
    available -= take;
  }

  if (entries.length) await ledger.post({ idempotencyKey: key, entries });
});

// the resolution rule: ANY blocking restriction blocks. precedence
// decides who may LIFT it, not whether it applies.
function effectiveRestriction(all: Restriction[]): Effective {
  const active = all.filter(isActive);
  if (!active.length) return { blocked: false };

  // the MOST restrictive applies; the LOWEST level number decides
  // who is allowed to remove it.
  const governing = active.reduce((a, b) => b.level < a.level ? b : a);

  return {
    blocked: true,
    reasonCode: governing.reasonCode,
    liftableBy: governing.liftableBy,
    // ALL of them, because lifting one does not unblock the account
    // and support needs to see the full picture.
    allActive: active
  };
}