Reconcile invoice, payment and refund records

Invoices describe amounts billed under an invoice contract. Payments and refunds describe money movement or attempts to move money. Credit notes adjust invoice value. These records are related, but they are not interchangeable, and subtracting the same economic adjustment in several places can create a false shortfall.

Build separate invoice and cash bridges, then explain the difference between them with allocation and timing evidence.

Inspect a deliberately imperfect teaching ledger

The synthetic billing invoices total 150,000 paise. A posted credit of 20,000 reduces the adjusted invoice amount to 130,000.

The payments include 150,000 of settled invoice-linked payments, one failed attempt of 10,000 and an unallocated settled payment of 7,000. The refund ledger includes a settled refund of 20,000 and a pending refund request of 5,000.

All records are synthetic. Status names are local to the fixture, and the example omits tax, fees, currency conversion and disputes. Its purpose is analytical reconciliation rather than a prescribed bookkeeping treatment.

Reconcile only the selected status and cutoff

SELECT
 (SELECT SUM(amount_paise) FROM billing_invoices) AS invoiced_paise,
 (SELECT SUM(amount_paise) FROM billing_credits
  WHERE posted_at<'2026-02-01') AS credited_paise,
 (SELECT SUM(amount_paise) FROM billing_payments
  WHERE status='settled' AND occurred_at<'2026-02-01') AS settled_payments_paise,
 (SELECT SUM(amount_paise) FROM billing_refunds
  WHERE status='settled' AND occurred_at<'2026-02-01') AS settled_refunds_paise,
 (SELECT SUM(amount_paise) FROM billing_payments
  WHERE status='settled' AND invoice_id IS NULL
    AND occurred_at<'2026-02-01') AS unallocated_payments_paise;

The invoice bridge is 150,000 − 20,000 = 130,000. The cash bridge is 157,000 − 20,000 = 137,000. The 7,000 difference is unallocated settled cash, not unexplained invoice revenue.

from build_and_verify import database

db = database()
invoiced = db.execute('SELECT SUM(amount_paise) FROM billing_invoices').fetchone()[0]
credits = db.execute("SELECT SUM(amount_paise) FROM billing_credits WHERE posted_at<'2026-02-01'").fetchone()[0]
payments = db.execute("SELECT * FROM billing_payments WHERE occurred_at<'2026-02-01'").fetchall()
refunds = db.execute("SELECT * FROM billing_refunds WHERE occurred_at<'2026-02-01'").fetchall()
settled = sum(r[2] for r in payments if r[3]=='settled')
refunded = sum(r[2] for r in refunds if r[3]=='settled')
unallocated = sum(r[2] for r in payments if r[3]=='settled' and r[1] is None)
pending_refunds = sum(r[2] for r in refunds if r[3]=='pending')
db.close()
assert (invoiced,credits,settled,refunded,unallocated,pending_refunds)==(
    150000,20000,157000,20000,7000,5000)
assert settled-refunded-unallocated == invoiced-credits == 130000
print({'adjusted_invoices':invoiced-credits,'net_settled_cash':settled-refunded,
       'unallocated_cash':unallocated,'pending_refund_requests':pending_refunds})

The pending 5,000 refund remains a separate operational item. It has not reduced settled cash under this fixture's status contract. Stripe's refund API documentation distinguishes refund statuses; a production integration must map its provider's actual lifecycle rather than treating every refund record as completed cash movement.

Avoid subtracting the credit and refund twice

For B1, a 20,000 credit reduces the billed amount from 100,000 to 80,000. Its settled refund reduces net paid cash from 100,000 to 80,000. Those two ledgers now agree. Subtracting both the credit and the refund from invoice value would incorrectly reduce that invoice-side measure to 60,000.

The appropriate journal entries depend on the accounting system and transaction circumstances. The analytical requirement is to preserve each ledger's meaning and reconcile their relationships explicitly.

Check identities before accepting the bridge

Payment IDs and refund IDs should identify economic events, not webhook deliveries. A repeated notification must not create another payment. Conflicting payloads for the same identifier require investigation rather than arbitrary deduplication.

Refunds must link to the correct payment, and invoice allocation must not multiply money through a many-to-many join. For partial allocations, use a separate allocation table with amounts and verify that allocated totals do not exceed the applicable payment under the chosen rules.

An aggregate equality is necessary but not sufficient. Two offsetting errors can cancel. Reconcile per invoice or account, retain unmatched records and compare source-control totals as well as the final grand total.

Exercise: change the pending refund to settled after February 1. Show that the January snapshot remains unchanged while the later cash bridge requires a corresponding invoice adjustment or an explicitly explained timing difference.

NeuraPath's Data Analytics with Generative AI course connects SQL data modeling with financial operations. A good reconciliation explains status, timing and allocation before presenting a zero difference as success.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Accounts receivable ageing with partial payments.
• Continue with Analyze operating expenses without changing the cost taxonomy.