Automating Payment Reconciliation for SaaS Platforms

Why Reconciliation Matters

Payment reconciliation is the process of matching your internal payment records against your PSP's settlement reports and your bank statements. It answers a simple but critical question: does the money in your bank account match what your system says should be there?

Without reconciliation, discrepancies accumulate silently. A missed webhook here, a double refund there, a currency conversion fee you did not account for. Over months, these add up to real financial discrepancies that are painful to untangle retroactively.

The Three-Way Match

Robust reconciliation involves three data sources:

1. Your application database - what your system recorded (payments, refunds, fees)
2. PSP settlement reports - what your PSP says happened (transactions, fees, net settlements)
3. Bank statements - what actually arrived in your bank account

The reconciliation process matches records across these three sources and flags discrepancies.

Building the Reconciliation Pipeline

Step 1: Normalize Data

Each source uses different formats, field names, and identifiers. Normalize everything into a common format before comparing:

class NormalizedTransaction
{
    public function __construct(
        public string $externalId,      // PSP's transaction ID
        public string $type,            // payment, refund, chargeback, fee
        public int $grossAmountCents,   // Gross amount in cents
        public int $feeAmountCents,     // PSP fee in cents
        public int $netAmountCents,     // Net amount in cents
        public string $currency,
        public Carbon $transactionDate,
        public Carbon $settlementDate,
        public ?string $internalId,     // Your order/payment ID
    ) {}
}

Step 2: Import Settlement Data

Most PSPs provide settlement data through:

API endpoints: Fetch settlements and their transactions programmatically:

class MollieSettlementImporter
{
    public function import(Carbon $date): Collection
    {
        $settlements = $this->mollie->settlements->page();

        return collect($settlements)
            ->filter(fn ($s) => Carbon::parse($s->createdAt)->isSameDay($date))
            ->flatMap(function ($settlement) {
                $payments = $this->mollie->settlementPayments
                    ->pageForId($settlement->id);

                return collect($payments)->map(
                    fn ($p) => $this->normalize($p, $settlement)
                );
            });
    }
}

CSV/Excel exports: Available in PSP dashboards. Useful for manual reconciliation but not scalable. Automate if possible.

SFTP file delivery: Some PSPs push settlement files to an SFTP server daily. Build a file watcher to import these automatically.

Step 3: Match Records

The matching logic compares your internal records against PSP records:

class ReconciliationMatcher
{
    public function match(Collection $internal, Collection $psp): ReconciliationResult
    {
        $matched = collect();
        $unmatchedInternal = collect();
        $unmatchedPsp = collect();

        foreach ($internal as $record) {
            $pspRecord = $psp->first(
                fn ($p) => $p->externalId === $record->externalId
            );

            if ($pspRecord) {
                $matched->push(new MatchedPair(
                    internal: $record,
                    psp: $pspRecord,
                    amountMatch: $record->grossAmountCents === $pspRecord->grossAmountCents,
                    statusMatch: $record->type === $pspRecord->type,
                ));
                $psp = $psp->reject(fn ($p) => $p->externalId === $pspRecord->externalId);
            } else {
                $unmatchedInternal->push($record);
            }
        }

        $unmatchedPsp = $psp; // Remaining PSP records with no internal match

        return new ReconciliationResult($matched, $unmatchedInternal, $unmatchedPsp);
    }
}

Step 4: Identify Discrepancies

Discrepancies fall into categories:

Missing internally: PSP has a transaction that your system does not. Common causes: failed webhook processing, manual transactions in PSP dashboard, or chargebacks you did not receive notification for.

Missing at PSP: Your system has a transaction that the PSP settlement does not include. Common causes: payment still pending settlement, payment in a future settlement batch, or a status mismatch (you recorded it as paid but the PSP shows it as failed).

Amount mismatches: Both records exist but amounts differ. Common causes: currency conversion rounding, partial refunds not captured correctly, or fee calculations differing from your estimate.

Step 5: Resolve and Report

Generate a daily reconciliation report:

class DailyReconciliationReport
{
    public function generate(Carbon $date): void
    {
        $result = $this->reconcile($date);

        ReconciliationRun::create([
            'date' => $date,
            'total_matched' => $result->matched->count(),
            'total_unmatched_internal' => $result->unmatchedInternal->count(),
            'total_unmatched_psp' => $result->unmatchedPsp->count(),
            'amount_discrepancy_cents' => $result->totalDiscrepancy(),
            'status' => $result->hasDiscrepancies() ? 'needs_review' : 'clean',
        ]);

        if ($result->hasDiscrepancies()) {
            // Alert finance team
            Notification::route('email', config('finance.reconciliation_email'))
                ->notify(new ReconciliationDiscrepancyFound($date, $result));
        }
    }
}

Bank Statement Reconciliation

The third leg matches PSP settlements to actual bank movements:

PSP settlement amounts should match bank credits on the settlement date. Differences typically arise from:

• Settlement timing (PSP processes on day X, bank credits on day X+1)
• Currency conversion if your bank account currency differs from the settlement currency
• Bank fees deducted from incoming transfers
• Multiple PSP settlements batched into a single bank credit

For bank statement import, use:

• MT940 format (traditional SWIFT format, widely supported)
• CAMT.053 format (ISO 20022, increasingly standard in Europe)
• Bank API (if your bank provides one through open banking)

Handling Common Discrepancy Types

Webhooks That Never Arrived

Your system shows a payment as pending, but the PSP settlement shows it as paid. Root cause: the webhook was lost or your handler returned an error.

Fix: Update your local record based on the PSP settlement data. Investigate why the webhook failed and fix the underlying issue.

Duplicate Payments

Customer was charged twice for the same order. Your system may show one or both payments.

Fix: Refund the duplicate through the PSP. Update your reconciliation to flag orders with multiple payments.

Chargeback Not Recorded

PSP deducted a chargeback from your settlement, but your system has no record of it.

Fix: Create the chargeback record retroactively. Review why the chargeback notification was missed.

Reconciliation Schedule

• Daily: Match PSP transactions to internal records (automated)
• Weekly: Match PSP settlements to bank statements (semi-automated)
• Monthly: Full three-way reconciliation with financial close (manual review of automated results)

Metrics to Track

• Match rate: Percentage of transactions matched automatically (target: above 99%)
• Time to resolve: Average days to close a discrepancy
• Discrepancy trend: Is the number of discrepancies increasing or decreasing?
• Net discrepancy amount: The total unresolved financial difference

Reconciliation is the financial immune system of your payment platform. Automate the matching, investigate every discrepancy, and track your match rate. A clean reconciliation gives you confidence that your revenue numbers are real. Neglect it, and you are flying blind.