How to build automated payment reconciliation systems that match PSP settlements to your internal records and catch discrepancies.
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.
Robust reconciliation involves three data sources:
The reconciliation process matches records across these three sources and flags discrepancies.
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
) {}
}
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.
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);
}
}
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.
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));
}
}
}
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:
For bank statement import, use:
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.
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.
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 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.
Whether you're modernizing your infrastructure, navigating compliance, or building new software - we can help.
Book a 30-min Call