payment-reconciliation

name	payment-reconciliation
description	Step-by-step playbook for reconciling your internal double-entry ledger against the PSP's payout and transaction reports — identifying discrepancies, classifying them, and closing the books with confidence.

Payment Reconciliation

When to invoke

Use when building the reconciliation job for a new payments integration, investigating a balance discrepancy, or auditing an existing reconciliation process for gaps.

Concepts

Term	Meaning
PSP report	CSV/API export of all transactions, fees, refunds, and disputes settled in a period
Internal ledger	Your double-entry record of every money movement (credit/debit entries)
Settlement	When the PSP actually transfers funds to your bank account
Payout	A single bank transfer from PSP; may aggregate many transactions
Disputed amount	Chargebacks currently open; may not yet be settled/reversed

The goal: every PSP transaction maps 1:1 to a ledger entry, every payout maps to a bank credit, and the sum of fees matches the PSP's fee report.

Step 1 — Data collection

Pull two sources daily (or per-payout cycle):

1. PSP transaction report — via API (preferred) or SFTP CSV export:

   • Stripe: GET /v1/balance/history?limit=100&created[gte]=...
   • Each record: {id, type, amount, fee, net, currency, created, available_on, description}

2. Internal ledger entries for the same period:

   SELECT
     ledger_entry_id,
     external_transaction_id,   -- PSP charge/refund ID
     amount_minor_units,
     currency,
     entry_type,                -- charge | refund | fee | payout
     created_at
   FROM ledger_entries
   WHERE created_at BETWEEN :start AND :end
   ORDER BY created_at;
   

Step 2 — Matching algorithm

Match on external_transaction_id (PSP id = your ledger's external_transaction_id):

for each psp_record in psp_report:
    ledger_match = find ledger_entry WHERE external_transaction_id = psp_record.id

    if no match:
        flag UNMATCHED_PSP — money in PSP not in ledger
    elif ledger_match.amount != psp_record.net:
        flag AMOUNT_MISMATCH (delta = psp_record.net - ledger_match.amount)
    else:
        mark MATCHED

for each ledger_entry in ledger WHERE type in (charge, refund):
    if not matched:
        flag UNMATCHED_LEDGER — money in ledger not in PSP

Step 3 — Discrepancy classification

Code	Description	Typical cause	Resolution
UNMATCHED_PSP	PSP record has no ledger entry	Missed webhook; duplicate prevention over-fired	Replay the webhook or create compensating entry
UNMATCHED_LEDGER	Ledger entry has no PSP record	Phantom charge; ledger bug	Investigate and void/reverse the ledger entry
AMOUNT_MISMATCH	Amounts differ	Fee deducted at wrong point; currency conversion	Correct fee ledger entries
TIMING_GAP	Present in one source, absent in other at T	Timing window — PSP and webhook not yet synced	Re-run reconciliation after settlement lag
DISPUTE_OPEN	Chargeback open; PSP holds funds	Normal during dispute window	Track in a dispute register; reconcile on resolution

Step 4 — Fee reconciliation

PSP fees are deducted per transaction. Verify separately:

expected_fees = Σ (psp_report.fee for all records in period)
ledger_fees   = Σ (ledger_entries WHERE type = 'fee' AND period)

if abs(expected_fees - ledger_fees) > tolerance:
    flag FEE_RECONCILIATION_FAILURE

Tolerance: zero for integer minor-unit systems. Any delta is a real discrepancy.

Step 5 — Payout reconciliation

for each bank_credit (from bank statement):
    psp_payout = find psp_payout WHERE payout_id = bank_credit.reference

    if no match: flag PAYOUT_UNMATCHED
    elif psp_payout.amount != bank_credit.amount: flag PAYOUT_AMOUNT_MISMATCH

Payouts aggregate transactions — verify the payout total equals the sum of its constituent transactions minus fees:

payout_total = Σ (psp_record.net WHERE payout_id = X)

Step 6 — Reporting and alerting

Run reconciliation on a scheduled job (daily, after settlement window closes):

• Emit metrics: matched_count, unmatched_psp_count, unmatched_ledger_count, discrepancy_amount_minor_units.
• Alert on-call if discrepancy_amount > 0 or unmatched_count > 0.
• Write a reconciliation report to an audit table — never overwrite, always append-only.
• Route FEE_RECONCILIATION_FAILURE and PAYOUT_UNMATCHED to finance for the accounting close.

Pitfalls

• Reconciling on created-at instead of settled/available-at — transactions created near midnight may settle the next day; use available_on from the PSP for period boundaries.
• Not handling refunds as negative charges — a refund reverses a charge; both create ledger entries; matching only on charge IDs misses refund records.
• Treating TIMING_GAP as an error — PSP webhooks can arrive hours after the transaction; rerun after the settlement lag before flagging.
• Discarding the discrepancy record after resolution — always preserve the original mismatch and the resolution action for the audit trail.
• Floating-point comparison of amounts — store and compare in integer minor units; a float equality check on currency amounts fails due to rounding.