Ecommerce Payment Integration: Reconcile Stripe Payouts in Node.js

Ecommerce payment integration isn't finished when checkout works. It's finished when you can trace every Stripe payout back to the orders in your database, and anything that doesn't match gets sent to a person. If your finance team exports CSVs every month and matches rows by hand in a spreadsheet, this tutorial replaces that with a nightly Node.js job.

The job pulls the balance transactions behind each payout and matches them to your orders table. It then writes refunds, disputes, amount mismatches and missing captures to a review queue.

Why don't Stripe payouts match your orders?

A payout is a net figure. It bundles many charges, takes off Stripe fees, refunds and dispute withdrawals, and adds back any dispute reversals. It all settles on Stripe's schedule, not on the day the order was placed. So an order from Friday night can land in Tuesday's payout, next to a refund for an order from last month.

Your orders table tracks orders, and Stripe tracks balance transactions. Every movement of money in your Stripe balance is a balance transaction. Each one has a reporting_category such as charge, refund, dispute or fee, and points to a source object. The key that links the two is the PaymentIntent ID, which you should store on the order at checkout.

If you don't store it, fix that first. Nothing below works without a shared key.

What goes wrong if nobody reconciles?

Problems stay hidden until they're hard to unwind. A dispute pulls the money back, but the order still says paid, so support ships a replacement. A manual-capture authorization expires after seven days without being captured, and the customer keeps the goods for free. Someone issues a partial refund from the Stripe dashboard, it never reaches your database, and your revenue reports drift for months.

Each of these is small on its own. Together they slow down month-end close, make your numbers hard to defend to an accountant or investor, and leave finance cleaning up after engineering.

Do you actually need a custom job?

Not always. If you run on a hosted platform that handles payments for you, or you process a handful of orders a day, Stripe's built-in payout reconciliation report and a monthly check are enough. Skip the code.

Build the job if you own the checkout, use manual capture or partial refunds, or the spreadsheet now takes more than an afternoon.

The schema

Your orders table needs stripe_payment_intent_id, total_cents, currency, status, created_at and a nullable reconciled_at. Add one table for findings:

CREATE TABLE reconciliation_issues (
  id SERIAL PRIMARY KEY,
  payout_id TEXT NOT NULL,
  ref_id TEXT NOT NULL,
  order_id INTEGER,
  kind TEXT NOT NULL,
  detail JSONB,
  resolved_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (payout_id, ref_id, kind)
);

The unique constraint makes reruns safe. If the job crashes halfway, run it again and nothing gets flagged twice.

How do you pull the transactions behind each payout?

First list the payouts that arrived in your time window. Then list the balance transactions for each payout, filtered by payout ID. Expanding source saves you an API call per charge.

const Stripe = require('stripe')
const knex = require('knex')

const stripe = Stripe(process.env.STRIPE_SECRET_KEY)
const db = knex({ client: 'pg', connection: process.env.DATABASE_URL })

async function collect(iterable) {
  const out = []
  for await (const item of iterable) out.push(item)
  return out
}

const payoutsBetween = (start, end) =>
  collect(stripe.payouts.list({ arrival_date: { gte: start, lt: end }, status: 'paid', limit: 100 }))

const txnsForPayout = (payoutId) =>
  collect(stripe.balanceTransactions.list({ payout: payoutId, expand: ['data.source'], limit: 100 }))

The payout filter only works for automatic payouts. If you trigger payouts manually, filter balance transactions by created date and group them yourself.

How do you match transactions to orders?

Charges, refunds and disputes all carry a payment_intent field on their source object. Use that field to look up the order, then apply a rule for each category.

const piOf = (txn) => (txn.source && typeof txn.source === 'object' ? txn.source.payment_intent : null)

async function reconcilePayout(payout) {
  const txns = await txnsForPayout(payout.id)
  const orders = await db('orders').whereIn('stripe_payment_intent_id', txns.map(piOf).filter(Boolean))
  const byPi = new Map(orders.map((o) => [o.stripe_payment_intent_id, o]))
  const issues = []
  const clean = []

  const net = txns.filter((t) => t.reporting_category !== 'payout').reduce((sum, t) => sum + t.net, 0)
  if (net !== payout.amount) {
    issues.push({ payout_id: payout.id, ref_id: payout.id, kind: 'payout_total_mismatch', detail: { net, payout: payout.amount } })
  }

  for (const txn of txns) {
    const cat = txn.reporting_category
    if (cat === 'fee' || cat === 'payout') continue

    const order = byPi.get(piOf(txn))
    const base = { payout_id: payout.id, ref_id: txn.id, order_id: order ? order.id : null }

    if (!order) {
      issues.push({ ...base, kind: 'no_matching_order', detail: { cat, amount: txn.amount } })
    } else if (cat === 'charge') {
      const src = txn.source
      if (src.amount !== order.total_cents || src.currency !== order.currency) {
        issues.push({ ...base, kind: 'amount_mismatch', detail: { stripe: src.amount, order: order.total_cents } })
      } else {
        clean.push(order.id)
      }
    } else if (cat === 'refund' && !['refunded', 'partially_refunded'].includes(order.status)) {
      issues.push({ ...base, kind: 'refund_not_recorded', detail: { amount: txn.amount } })
    } else if (cat === 'dispute' || cat === 'dispute_reversal') {
      issues.push({ ...base, kind: cat, detail: { due_by: txn.source.evidence_details?.due_by } })
    }
  }

  if (clean.length) await db('orders').whereIn('id', clean).update({ reconciled_at: new Date() })
  return issues
}

The charge check compares txn.source.amount instead of txn.amount. The balance transaction amount is in your settlement currency, but the charge amount is in the currency the customer paid in. If you sell in more than one currency, comparing the wrong one floods the queue with false mismatches.

The payout total check is the one most tutorials leave out. If the net of all transactions doesn't equal the payout amount, your job missed something, usually a pagination bug or a category you didn't handle. Fix the code before you trust any other finding.

How do you catch missing captures?

Missing captures never show up in a payout, which is why reconciling payouts alone misses them. Check them separately by looking for orders marked paid that no payout has confirmed yet.

async function findMissingCaptures() {
  const cutoff = new Date(Date.now() - 5 * 24 * 3600 * 1000)
  const orders = await db('orders').where('status', 'paid').whereNull('reconciled_at').where('created_at', '<', cutoff)
  const issues = []
  for (const order of orders) {
    const pi = await stripe.paymentIntents.retrieve(order.stripe_payment_intent_id)
    if (pi.status !== 'succeeded') {
      issues.push({ payout_id: 'none', ref_id: pi.id, order_id: order.id, kind: 'missing_capture', detail: { stripe_status: pi.status } })
    }
  }
  return issues
}

A five day cutoff still leaves time to capture before the authorization expires. Tune it to your payout schedule.

Running it every night

async function main() {
  const end = Math.floor(Date.now() / 1000)
  const start = end - 3 * 24 * 3600
  const issues = []
  for (const payout of await payoutsBetween(start, end)) issues.push(...(await reconcilePayout(payout)))
  issues.push(...(await findMissingCaptures()))
  if (issues.length) {
    await db('reconciliation_issues').insert(issues).onConflict(['payout_id', 'ref_id', 'kind']).ignore()
  }
  console.log('reconciliation done, issues: ' + issues.length)
  await db.destroy()
}

main().catch((err) => {
  console.error(err)
  process.exit(1)
})

The window covers three days on purpose. Overlapping runs catch payouts that arrive late, and the unique constraint stops duplicates. You can schedule the job with cron, a GitHub Actions schedule, or your platform's job runner.

Who handles each flag?

Kind	Owner	First action
dispute	Support and finance	Pause fulfilment, submit evidence before due_by
refund_not_recorded	Finance	Find who refunded from the dashboard, update the order
amount_mismatch	Engineering	Check for discounts or tax applied after the PaymentIntent was created
no_matching_order	Finance	Usually manual invoices or tests, label them
missing_capture	Operations	Capture now, or cancel and contact the customer
payout_total_mismatch	Engineering	Stop and fix the job first

Give each flag an owner. An unowned review queue ends up as another spreadsheet nobody opens.

For the rest of the store build, see the ecommerce development guide. Or send Geminate Solutions your current checkout and payout setup for a free review, and you'll get a reply within 24 hours.