milestone 3/4 Revenue & payment reconciliation

Reconcile revenue with payments and identify differences.

AI Platform Economics · Calculate success rates and usage costs, prevent join fan-out, and reconcile split payments with refunds.

approach

Reconciliation compares independent totals. Sum payments and refunds by invoice before joining. Net collected is payments minus refunds; gap is billed minus net collected. A refund creates a gap under this definition, even when the original bill was paid.

WITH paid AS(
  SELECT invoice_id,SUM(amount) AS paid_amount
  FROM payments
  GROUP BY invoice_id
),returned AS(
  SELECT invoice_id,SUM(amount) AS refund_amount
  FROM refunds
  GROUP BY invoice_id
)
SELECT
  i.id,
  i.billed_amount,
  COALESCE(p.paid_amount,0)-COALESCE(r.refund_amount,0) AS net_collected,
  i.billed_amount-COALESCE(p.paid_amount,0)+COALESCE(r.refund_amount,0) AS gap
FROM invoices i
LEFT JOIN paid p ON p.invoice_id=i.id
LEFT JOIN returned r ON r.invoice_id=i.id;

data for this milestone