Revenue reconciliation engine
A daily pipeline that matches every Shopify order to its Stripe or PayPal charge and payout, and flags what doesn’t add up.
- D2C fragrance brand
- Automation, Dashboards
The problem
Orders live in Shopify, charges in Stripe and PayPal, and payouts arrive later in a different shape. Matching them by amount breaks the moment currency conversion, refunds or split payouts are involved.
What we built
An idempotent daily pipeline that pulls orders, balance transactions and settlements into a single Postgres ledger, then matches order to charge to payout by IDs and references, never by amount, with a one cent tolerance for FX rounding. The matching core is pure functions, so the same input always gives the same answer.
When it doesn’t match
Six classes of edge case are detected and coded: orphan charges, FX settlement drift, stale refund status, split payouts, missing settlement references and schema drift. They land in an exceptions queue with severity-tagged Slack digests, and a Next.js dashboard shows finance the ledger and its state.
Built to fail loudly
Three safety nets watch the pipeline: a workflow error trigger, an hourly heartbeat that fires if no run has succeeded in 26 hours, and an external dead-man switch.
How it fits together
Comes in
- Shopify
- Stripe
- PayPal
Runs through
- n8n
- PostgreSQL ledger
Goes out
- Slack digests
- Finance dashboard
- Healthchecks.io