The append-only audit ledger

CREATE OR REPLACE FUNCTION audit_immutable_trigger()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'audit tables are append-only; % is not allowed', TG_OP;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_audit_mutation
BEFORE UPDATE OR DELETE ON audit.channel_audit
FOR EACH ROW EXECUTE FUNCTION audit_immutable_trigger();

CREATE TRIGGER prevent_audit_mutation
BEFORE UPDATE OR DELETE ON audit.payments_audit
FOR EACH ROW EXECUTE FUNCTION audit_immutable_trigger();

-- Initial event: escrow held, awaiting confirmation
INSERT INTO audit.payments_audit (
  id, user_id, motion_kind, amount_kobo, external_ref, verified_at, occurred_at
) VALUES (
  'pay_01HMFB...',
  'usr_01HMFA...',
  'ESCROW_HOLD',
  6000000,
  'sqref_xyz',
  NULL,  -- not yet verified
  now()
);

-- Later: OPay confirmation arrives, reconcile guard passes
INSERT INTO audit.payments_audit (
  id, user_id, motion_kind, amount_kobo, external_ref, verified_at, occurred_at,
  references_id
) VALUES (
  'pay_01HMFC...',
  'usr_01HMFA...',
  'ESCROW_FUNDED',
  6000000,
  'sqref_xyz',
  now(),
  now(),
  'pay_01HMFB...'  -- points back at the original event
);

// Pseudocode for a wallet credit
await db.transaction(async (tx) => {
  // Update derived state
  await tx.wallets.update({
    where: { userId: 'usr_01HMFA...' },
    data: { balanceKobo: { increment: amountKobo } }
  });

  // Append audit row (immutable)
  await tx.paymentsAudit.create({
    data: {
      userId: 'usr_01HMFA...',
      motionKind: 'WALLET_CREDIT',
      amountKobo,
      externalRef: webhook.reference,
      verifiedAt: new Date()
    }
  });
});