Payment reconciliation for SaaS and marketplaces

Why reconciliation is an engineering problem

Finance teams reconcile at month end with spreadsheets. That works for a handful of transactions. It doesn’t work for thousands of payments, partial refunds, multiple currencies and payouts to sellers. By the time a month-end spreadsheet shows a gap, the cause is weeks old and the logs have rotated.

Engineering-led reconciliation runs every day, against structured data, and produces a short list of exceptions with enough context to fix them. It depends on your system recording money properly in the first place, which is why it starts with the ledger.

The foundation: a double entry ledger

A double entry ledger records every movement of money as a transaction made of two or more entries: debits on some accounts, credits on others, summing to zero. Nothing is created or destroyed; it moves. That invariant is what makes reconciliation possible, because every unexplained number is, by construction, a missing or wrong entry.

For a SaaS product, a minimal chart of accounts might be:

• Processor receivable – money the processor holds on your behalf.
• Bank – money that has landed in your account.
• Revenue – what you earned.
• Processing fees – what the processor kept.
• Refunds and disputes – money returned or clawed back.
• For marketplaces, seller payable per seller – money you owe but haven’t paid out yet, and platform fees for your take.

A card payment of 100.00 with a 3.00 fee becomes: debit processor receivable 97.00, debit processing fees 3.00, credit revenue 100.00. When the processor pays out, you credit processor receivable and debit bank. The amounts are illustrative; your fees depend on your processor’s current pricing.

Ledger database design

You don’t need a specialist database. PostgreSQL does this well. A design I’d start from:

• An accounts table: id, name, type (asset, liability, revenue, expense), currency, and an optional owner such as a seller ID.
• A transactions table: id, a description, an effective date, a created timestamp, and a reference to the business event that caused it (payment, refund, payout, dispute) with that reference unique per event type, so the same event can’t be booked twice.
• An entries table: transaction id, account id, amount as a signed integer in minor units, and currency.
• A database constraint or a check in the single write path that every transaction’s entries sum to zero per currency.
• No UPDATE or DELETE on entries. Mistakes are fixed with reversing transactions.
• Balances computed by summing entries, with periodic snapshot rows if the sums get slow.

Keep the ledger write path narrow: one module, one function per business event type, one database transaction per ledger transaction. If you’re designing the wider system, my guide to payment system architecture shows where the ledger sits relative to payments and webhooks.

How payment reconciliation works, step by step

1. Pull processor data. For Stripe, that’s balance transactions: every charge, refund, dispute adjustment, fee and payout affecting your Stripe balance, each with gross amount, fee and net. Pull them by created date via the API, or use Stripe’s reconciliation reports.
2. Match to your ledger. Join on the processor’s object IDs stored on your payment and refund records. Every balance transaction should map to exactly one ledger transaction, and vice versa.
3. Compare amounts and currency, not just existence. Check gross, fee and net separately.
4. Reconcile payouts. Each payout is the net of a set of balance transactions. For automatic payouts, Stripe lets you list the balance transactions included in a payout, which makes this a sum-and-compare.
5. Match payouts to bank deposits by amount and expected arrival date, using bank feeds or statement exports.
6. Produce exceptions. Anything unmatched or mismatched becomes a row in an exceptions table with the evidence attached.
7. Resolve and record. Fix the cause, post correcting ledger entries, and close the exception with a reason.

The awkward cases: refunds, chargebacks and FX

Happy-path payments reconcile easily. These are the cases that cause most exceptions:

• Refunds issued outside your system. Someone refunds from the processor dashboard. If your system doesn’t listen for refund events and book them, the ledger overstates revenue. Reconciliation catches it; better still, handle the webhook.
• Partial refunds. A payment can have several refunds. Model refunds as their own records linked to the payment, never as a flag.
• Fees on refunds. Whether processing fees are returned on a refund depends on your processor’s terms. Don’t assume; book what the balance transaction says.
• Chargebacks. A dispute debits the disputed amount, often plus a dispute fee, when it opens, and may credit it back if you win. That’s two or more balance transactions over weeks. Book each when it happens, and link them to the original payment.
• Currency conversion. When a customer pays in one currency and you settle in another, the processor converts. The balance transaction records the settled amount and exchange rate. Book the presentment amount on the payment and the settled amount in the ledger, and post the difference to an FX account rather than letting it leak into revenue.
• Timing. A charge on the last day of the month may land in next month’s payout. Reconcile on effective dates, and treat in-transit balances as expected, not as exceptions.

Marketplaces add a second dimension

On a marketplace you’re reconciling not just your money but money you owe to sellers. Each seller payable account must equal what you’ve collected for them, minus your fee, minus refunds, minus what you’ve paid out. With Stripe Connect, transfers and payouts to connected accounts are additional records to match. My marketplace payments guide covers how charge types change this.

Automating reconciliation

• Run it daily, as a scheduled job, for the previous day plus a trailing window to catch late arrivals.
• Make it idempotent: rerunning a day should produce the same result, not duplicate exceptions.
• Store raw processor data in your own tables so you can rerun matching without calling the API.
• Alert on exception counts and amounts, with thresholds, rather than on every mismatch.
• Give finance a simple view of open exceptions and their resolution. They’ll trust the system faster if they can see it working.
• Track time-to-resolve. Exceptions that sit for weeks become write-offs.

Build or buy?

Reconciliation tools and ledger services exist, and for some companies they’re the right call – especially multi-processor, multi-bank setups with a finance team that wants a product. For a SaaS on one processor, a well-designed ledger in PostgreSQL plus a few hundred lines of matching code usually beats integrating another vendor. My build vs buy guide has the general framework.

For the wider picture of building payment systems that survive real customers, see Payments Playbook, the book I spent seven years writing after building payment systems at Yola and BLU Loyalty. If you’d like help putting this in place, it’s part of my payment integration services.

Related

• Payment system architecture: the parts that matter after launch
• Payment integration services that hold up under real customers
• Marketplace payments: how to design them on Stripe Connect
• Stripe webhook handling that survives production
• Fintech consulting for software that moves money
• Usage based billing and subscriptions, designed to stay correct