Payment Reconciliation Engine

A reconciliation system that matches incoming Stripe webhook events against internal payment records, closing the gap where webhooks arrive out of order, get retried, or go missing entirely.

Payment webhooks are not guaranteed to arrive exactly once or in order. Without reconciliation, that leads to duplicate transaction records, settlements that silently never get matched to an order, and no visibility into which payments are actually in a healthy state.

Every incoming webhook event is matched to an internal record using an idempotency key before being written anywhere. Unmatched settlements are flagged automatically and trigger an email alert via Resend instead of failing silently, so a missed reconciliation is caught within minutes, not discovered during a monthly audit.

• →Immutable, append-only PostgreSQL ledger managed through Prisma — transactions are never updated in place, only new rows are appended
• →Three explicit payment states tracked per transaction: pending, matched, failed
• →Idempotency keys enforced at the write layer to guarantee exactly-once processing regardless of webhook retries
• →Discrepancy reports generated across all three states for manual review of edge cases

The hardest part wasn't matching the happy path — it was designing for Stripe retrying the same webhook multiple times under network failure, and making sure that never resulted in a duplicate ledger entry.

Append-only writes make the ledger easy to audit and impossible to silently corrupt, at the cost of needing derived views (rather than simple updates) to answer 'what is the current state of this transaction.'

Duplicate transaction errors reduced to zero. Unmatched settlements are now flagged automatically instead of requiring manual reconciliation.

Add a real-time dashboard over the discrepancy reports instead of relying on email alerts alone, and extend reconciliation to support multiple payment providers beyond Stripe.