DEV Community

Cover image for I Built a Multi-Processor Payment Reconciliation System (Stripe + PayPal + Square + ACH) in n8n + Postgres
Ugo Chukwu
Ugo Chukwu

Posted on

I Built a Multi-Processor Payment Reconciliation System (Stripe + PayPal + Square + ACH) in n8n + Postgres

#n8n #finops #automation #accounting

Companies using multiple payment processors (Stripe + PayPal + Square + bank ACH) often spend 10–20 hours/week manually matching transactions to accounting.

And the painful part is: even when the processor “charged correctly”, your internal logic can still be wrong — leading to:

  • missed transactions
  • duplicates / double-counting
  • fee mismatches
  • refunds not linking back to originals
  • delayed cash visibility

So I built an open-source system that automates the workflow:

✅ daily ingestion from processors → ✅ unified ledger → ✅ intelligent matching → ✅ exception queue → ✅ optional QuickBooks journal sync

This post is a full build breakdown + how you can run it yourself.

What I built

A Multi-Processor Payment Reconciliation pipeline using:

  • n8n for orchestration
  • PostgreSQL for storage (works on Supabase or any Postgres)
  • Python inside n8n Code nodes for transforms + matching

It delivers:

  • Daily auto-pull from Stripe, PayPal, Square, and ACH (CSV)
  • Unified transaction ledger (canonical schema)
  • Matching engine that handles refunds, split payments, timing differences, fee variance, and currency conversion
  • Discrepancy queue (missing txns, duplicates, fee mismatches, unmatched payouts)
  • (Optional) QuickBooks sync for journal entries

Value: save ~40+ hours/month and remove the majority of human error.

Repo

The 8 workflows (exactly what’s in the build)

These are the only workflows in the project (names are consistent across repo + screenshots):

  1. 01- Ingest Stripe
  2. 02 - Ingest PayPal
  3. 03 - Ingest Square
  4. 04 - Ingest ACH (CSV)
  5. 05 - Normalize & Enrich
  6. 06 - Reconcile & Match Engine
  7. 07 - QuickBooks Sync
  8. 08 - Exception Notifications

Screenshots for each workflow live in docs/screenshots/.

Screenshots

Architecture (high level)

The system follows a simple but reliable pipeline:

  1. Ingest from each processor (API pulls + CSV)
  2. Transform into a canonical ledger schema
  3. Persist in Postgres with idempotent upserts
  4. Normalize & enrich (standardize fields, compute derived values)
  5. Match transactions to payouts/settlements (multi-pass)
  6. Write outputs
  • matches
  • exceptions
    1. Notify: daily digest of exceptions
    2. Optional: build + sync QuickBooks journals

More detail in: docs/architecture.md

Why a unified ledger matters

Each processor has different terminology and event shapes:

  • Stripe: charges, payment intents, balance transactions, payouts
  • PayPal: transaction events, fees, settlement quirks
  • Square: payments, refunds, fees
  • ACH/bank: CSV exports with their own structure

If you try to reconcile directly from raw payloads, you end up rebuilding logic repeatedly.

So the core idea is:

Convert everything into one canonical structure first.

Once everything is in a unified ledger, reconciliation becomes a deterministic matching problem.

Database design (Postgres)

The schema is designed around three layers:

1) Raw ingestion (audit trail)

  • Store raw payloads for traceability and debugging.

2) Normalized transactions

  • Convert each processor’s format into consistent fields.

3) Reconciliation artifacts

  • Runs, candidates, matches, exceptions, and sync jobs.

I keep the database portable: it’s standard SQL in db/schema.sql.

Matching engine (how it works)

The matching strategy is intentionally rule-based and explainable.

Pass 1 — Exact linking

  • external_id matches
  • same processor account
  • same currency
  • within time window

Pass 2 — Fee-aware matching

Processors often represent amounts differently (gross vs net vs fee separately).

So we normalize:

  • gross_amount
  • fee_amount
  • net_amount

…and match on net/gross using tolerances.

Pass 3 — Refund linking

  • link refunds back to original transaction
  • support partial refunds

Pass 4 — Split / partial payments

When multiple transactions sum to one expected settlement:

  • group candidates within a window
  • pick combinations that sum to target within tolerance

Pass 5 — Score + threshold

Each candidate gets a score.

  • above threshold → match
  • below threshold → exception

This keeps the system deterministic and auditable.

Walkthrough of the workflows

01- Ingest Stripe

Cron → HTTP Request: Stripe → Transform to Ledger → Upsert row(s)

What matters:

  • pagination
  • incremental cursor sync (so you don’t re-fetch everything)
  • idempotent upsert keys

02 - Ingest PayPal

Same structure:

Cron → HTTP Request: PayPal → Transform to Ledger → Upsert row(s)

03 - Ingest Square

Same structure:

Cron → HTTP Request: Square → Transform to Ledger → Upsert row(s)

04 - Ingest ACH (CSV)

This is the “bank feed” option:

Cron → Read CSV → Parse CSV → Upsert row(s)

05 - Normalize & Enrich

This phase standardizes and enriches:

Cron → Query Ledger → Enrich Transactions → Upsert row(s)

Examples of enrichment:

  • compute net = gross − fee (when missing)
  • standardize currency codes
  • compute base currency conversion (if you store FX)
  • normalize descriptions

06 - Reconcile & Match Engine

This is the core:

Cron → Create Recon Run → Load Transactions + Load Payouts → Match & Create Exceptions → Insert Matches + Insert Exceptions

It produces two outputs:

  • matches: automatically reconciled items
  • exceptions: what a human needs to review

07 - QuickBooks Sync

This builds journal entries (and optionally posts):

Cron → Load Payouts → Build Journal Entries → Insert QB Sync Job

A typical clearing account journal model:

  • Debit Bank (net payout)
  • Debit Merchant Fees (fees)
  • Credit Processor Clearing (gross)

08 - Exception Notifications

Daily digest of issues:

Cron → Load Exceptions → Format Digest → Send a message

I kept this intentionally simple (Gmail), but you can swap to Slack/SMS.

How to run it (quick start)

1) Create database tables

Run:

psql "$DATABASE_URL" -f db/schema.sql
Enter fullscreen mode Exit fullscreen mode

2) Load seed data (optional)

Seed CSVs exist under db/seed/ (or data/ depending on your repo layout).

3) Import n8n workflows

Import JSONs under:

n8n/workflows/
Enter fullscreen mode Exit fullscreen mode

4) Configure credentials

Set:

  • database connection
  • Stripe / PayPal / Square API keys
  • QuickBooks credentials (optional)

5) Trigger the run

Execute in order:

  • 01 → 04 (ingestion)
  • 05 (normalize/enrich)
  • 06 (match engine)
  • 07 (optional)
  • 08 (digest)

What I learned building this

1) Reconciliation is mostly “data normalization”

Matching becomes easy when your ledger is consistent.

2) Idempotency is non-negotiable

Every ingestion + write must be safe to rerun.

3) Exceptions are the product

The automation is nice — but the real value is a clean, reliable exception queue.

4) Rule-based works surprisingly well

Most businesses can get 80–95% match rates without ML.

Extensions / next improvements

  • Add FX rate ingestion + robust multi-currency settlement handling
  • Add Slack notifications + severity thresholds
  • Add dashboards (Metabase / Superset / simple UI)
  • Add a small “review UI” for exceptions
  • Add support for more processors (Adyen, Braintree, etc.)

If you want to use this

Feel free to:

  • fork the repo
  • adapt the schema
  • plug in your own chart of accounts / journal logic

If you’re building finance ops automation at scale, I’m also happy to share patterns and templates.

Repo: https://github.com/Etherlabs-dev/multi-processor-reconciliation

If you found this useful, drop a comment — I’m publishing more systems like this as part of my 30-day build challenge.

Top comments (0)