Reconciling payouts with your database

Your bank shows a deposit of $11.68. Your orders table says you sold $12.34. Reconciliation is the work of proving those two numbers describe the same money — and of noticing, automatically, on the day they stop doing so.

Every figure in this article comes from a real payout in the Paydude sandbox, pulled through the API on 28 September 2026. The traps it describes are ones that data actually contains, not ones inferred from the reference.

The four objects, and how they link

The last link is the one you own. If you did not put a reference in the payment intent's metadata when you created it, there is nothing exact to join on — which is the same reason idempotency keys explained recommends it for safe retries.

A real payout, taken apart

Payout po_nU7M0EaxRxGJzCR217mDD paid out $11.68 from a single $12.34 card payment. Asking for its balance transactions returns four rows:

The percentage fee is 2.9% of 1234 cents, which is exactly 35.786 — the API records it unrounded. The 30-cent fixed fee is its own row. The rounding adjustment of −0.214 brings the total to a whole 1168 cents, and a matching +0.214 adjustment appeared on the next payout. The fraction is carried forward, not lost, which means a payout can only be reconciled with its neighbours in view.

Four things that break a naive reconciliation

1. The fee is not on the charge

The charge's balance transaction reports fee: 0 and net: 1234. The API's own field description says fees "are now portrayed as individual balance transactions of type = fee" and that "net will no longer differ from amount". Any report that computes revenue from net is reporting gross revenue and calling it net.

2. Amounts are not integers

"Smallest currency unit" suggests whole cents, and every example in the reference is an integer. The data is not: −35.786, −0.214, and in another payout a pair of ±0.786 rounding entries. An integer column will reject or truncate these, and summing them as JavaScript numbers drifts. Store them as NUMERIC and add them up in scaled integers.

3. The payout's own transaction is not part of the payout

Each payout also has a balance transaction of type: "payout" for the full amount, negative (−1168 here), with payout_id: null. It records money leaving the balance. It is not one of the lines the payout is made of, and including it zeroes every total.

4. The transaction count is not a checksum

The payout object accepts include=transaction_count. On the payout above it returned 2, against four balance transactions carrying that payout's id; on another payout it returned 1 against four. Whatever it counts, it is not the number of rows you need to have fetched, so do not use it to decide your fetch was complete — use has_more.

The reconciliation job

Run it when a payout is paid. payout.paid is a webhook event you can subscribe to; verify the signature first, as covered in webhook signature verification.

// Called from your webhook handler for payout.paid, after verification.
async function reconcilePayout(payoutId) {
  // Read the object rather than trusting the event name — see below.
  const payout = await api.get(`/v1/payouts/${payoutId}`);
  if (payout.status !== "paid") return;

  // Every balance transaction in this payout. Offset-paginated; has_more,
  // not transaction_count, tells you when you have them all.
  const txns = [];
  for (let offset = 0; ; offset += 100) {
    const page = await api.get(
      `/v1/balance-transactions?payout_id=${payoutId}&limit=100&offset=${offset}`
    );
    txns.push(...page.items);
    if (!page.has_more) break;
  }

  // Amounts carry fractions of a cent. Sum as scaled integers, never floats.
  const micro = (n) => Math.round(n * 1_000_000);
  const total = txns.reduce((sum, t) => sum + micro(t.amount), 0);
  const balanced = total === micro(payout.amount);

  // One row per transaction, keyed by its own id: a re-delivered webhook
  // overwrites rather than duplicates.
  for (const t of txns) {
    await db.ledger.upsert({ id: t.id }, {
      payout_id: payoutId,
      type: t.type,
      fee_type: t.fee_type,
      amount: String(t.amount),          // NUMERIC column keeps the fraction
      source_type: t.source_type,
      source_id: t.source_id,
    });
  }

  // Tie each charge — and the fees that share its source_id — to your order.
  const chargeIds = new Set(
    txns.filter((t) => t.source_type === "charge").map((t) => t.source_id)
  );
  for (const id of chargeIds) {
    const charge = await api.get(`/v1/charges/${id}`);
    const intent = await api.get(`/v1/payment-intents/${charge.payment_intent_id}`);
    await db.ledger.update(
      { source_id: id },
      { order_ref: intent.metadata?.reference ?? null }
    );
  }

  await db.payouts.upsert({ id: payoutId }, { amount: String(payout.amount), balanced });
  if (!balanced) await alertFinance({ payoutId, expected: payout.amount, found: total / 1e6 });
}

create table ledger_entries (
  id           text primary key,         -- txn_…, so re-running is an upsert
  payout_id    text not null,
  type         text not null,            -- charge, fee, adjustment, refund, …
  fee_type     text,                     -- discount, transaction, …
  amount       numeric(18, 6) not null,  -- minor units, and fractional
  source_type  text not null,
  source_id    text not null,            -- ch_… for a charge and its fees
  order_ref    text                      -- your reference, via the payment intent
);

create index on ledger_entries (payout_id);
create index on ledger_entries (source_id);

What "reconciled" should mean

1. The payout balances. The sum of its balance transactions equals its amount, exactly, in scaled integers.
2. Every charge maps to an order. A charge with no order_ref is money you received and cannot explain — usually a payment intent created without metadata.
3. Every paid order appears in some payout. Run this one from your side: orders marked paid with no ledger row after a few days are the gap the first two checks cannot see.
4. Anything unmatched goes to a person. The job's value is the exception list, not the green tick.

The balance-transaction type list also includes refund, dispute, dispute_won and several failure types. They were not present in the payouts examined here, so this article does not describe their shape — but they sit in the same list, carry a source_id, and belong in the same sum. Treat any type you have not seen before as an exception until you have.

Daily totals and the timezone default

GET /v1/balance-transactions/summary groups totals by day or month, which is useful for a finance dashboard. Its day buckets began at 05:00 UTC when no time_zone was passed — midnight in US Central daylight time. If your books close on UTC days, or on your own local day, pass time_zone explicitly or the summary and your ledger will disagree at the edges of every day.