DEV Community: eProxim

Posting Stripe Payouts to a Ledger in TypeScript, to the Cent

Jayant Chaudhary — Tue, 27 Oct 2026 13:00:00 +0000

One of our clients, a direct-to-consumer apparel brand, dreaded the end of every month. Somewhere between 1% and 2% of its bank deposits matched nothing in the ledger. So, at the close of every month, there was a high-pressure stretch in which finance went through transactions and deposits by hand until the books agreed. This had been going on for over a year, and it was getting worse – for the best possible reason. The brand had found real success online through its BigCommerce store, and every month pushed more orders through the same broken process.

The store used Stripe as its payment gateway. When we looked at the integration, the cause turned out to be simple. Every time an order was paid for, the integration posted the successful charge to the ledger as cash received. To be honest, I understand why people build it that way. A charge succeeds, the order is marked paid, revenue is booked, and the accounting system is told the money has arrived. All of that is true about the customer. None of it is true about the cash, and the gap shows up weeks later, as deposits in the bank that nobody can explain.

Stripe actually keeps track of two different things. PaymentIntents, Charges and the webhooks that go with them tell you about the customer – who paid, who got a refund, who disputed a charge. Balance transactions and payouts tell you about the money – what moved through your Stripe balance, and what eventually landed in your bank account. A ledger needs both, tied together. In this article, I'll cover the money side, in TypeScript, with the official stripe Node package and PostgreSQL. I have written separately about webhooks, disputes and the business decisions that go into a production Stripe integration beyond checkout, so I will stay close to the code here.

Why a charge is not cash

Here is what actually happens to a $100.00 charge. Stripe takes its fee first. What's left sits in your Stripe balance as pending, until it becomes available. Then, at some later date, Stripe sweeps it into a payout, together with everything else that became available in the same window – minus any refunds, dispute debits and standalone fees that landed in that window too. (If the customer paid in euros and your account settles in dollars, there's a currency conversion in there as well.) So the deposit your bank sees is a net figure for dozens or hundreds of transactions, and it will almost never equal any single charge.

Every one of those steps creates a BalanceTransaction. Each one has a gross amount (in the smallest unit of your settlement currency – cents, if you settle in dollars), a fee, a net that equals amount - fee, a fee_details breakdown, a source that points to the charge, refund or dispute behind it, and a reporting_category. Use reporting_category to classify transactions. Stripe recommends it for accounting over the raw type, and once you've looked at the raw types, you'll see why. Card and non-card charges both become charge. The catch-all type=adjustment gets broken out into dispute, dispute_reversal and so on. And partial-capture reversals get a category of their own, so they don't get mistaken for refunds.

The payout is what your bank sees, so that's what the ledger should reconcile against. When the sale happens, you debit a Stripe clearing account and credit receivables. When the payout arrives, you clear that account and debit the bank. If the integration does both, the balance in the clearing account is, at any moment, exactly what Stripe owes you – a number finance can actually check.

Your own outbound calls need idempotency keys

Before any of this can work, the customer side has to be clean, and the most common way it gets messed up is a refund that goes out twice, because a request timed out and was retried automatically. Stripe accepts an Idempotency-Key header on every v1 POST, and the Node SDK exposes it as the idempotencyKey request option. The key has to identify the business operation – this refund, for this order – and not the individual HTTP attempt. A key generated fresh for each attempt protects you from nothing. So, write the refund request to your own database first, and build the key from that row.

import Stripe from "stripe";

export const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);

interface RefundRequest {
  id: string;              // primary key of our refund_requests row, created first
  orderId: string;
  paymentIntentId: string;
  amountMinor: number;
}

export async function issueRefund(req: RefundRequest): Promise<Stripe.Refund> {
  return stripe.refunds.create(
    {
      payment_intent: req.paymentIntentId,
      amount: req.amountMinor,
      metadata: { order_id: req.orderId, refund_request_id: req.id },
    },
    { idempotencyKey: `refund:${req.id}` },
  );
}

Stripe checks the parameters that come in with a reused key, so if a retry somehow carries a different amount, it fails with an error, which is exactly what you want. The catch is that Stripe can prune keys once they're 24 hours old, after which the same key is treated as a brand-new request. A retry a few minutes later is safe. A "retry failed refunds" job that someone runs on Monday for Friday's failures is not – unless it first checks your own row to see whether a Stripe refund ID is already there. I think of the key as protection against a flaky network, and of my own table as the real record of what was done.

The metadata pays off later. When the refund turns up in a payout, the expanded source carries the order_id, and matching it to an order becomes a simple lookup.

Listing the balance transactions in a payout

For automatic payouts, Stripe will tell you exactly which balance transactions went into a payout. You pass the payout ID as the payout filter when you list balance transactions. Manual payouts are another story. Stripe has no way of knowing which transactions you meant to include, so the filter doesn't work for them, and the payout's reconciliation_status reads not_applicable. For automatic payouts, the status moves from in_progress to completed, and Stripe sends a payout.reconciliation_completed event when it does. That event is the natural trigger for the code below.

export class NotReadyError extends Error {}
export class ReconciliationError extends Error {}

export interface PayoutBundle {
  payout: Stripe.Payout;
  txns: Stripe.BalanceTransaction[];
}

export async function loadPayout(payoutId: string): Promise<PayoutBundle> {
  const payout = await stripe.payouts.retrieve(payoutId);
  if (payout.reconciliation_status !== "completed") {
    throw new NotReadyError(`${payoutId}: reconciliation_status=${payout.reconciliation_status}`);
  }
  const ownTxnId =
    typeof payout.balance_transaction === "string"
      ? payout.balance_transaction
      : payout.balance_transaction?.id;

  const txns: Stripe.BalanceTransaction[] = [];
  const pages = stripe.balanceTransactions.list({
    payout: payoutId,
    limit: 100,
    expand: ["data.source"],
  });
  for await (const txn of pages) {
    if (txn.id === ownTxnId) continue; // the payout's own debit from the balance
    if (txn.currency !== payout.currency) {
      throw new ReconciliationError(`${payoutId}: ${txn.id} is in ${txn.currency}`);
    }
    txns.push(txn);
  }

  const net = txns.reduce((sum, t) => sum + t.net, 0);
  if (net !== payout.amount) {
    throw new ReconciliationError(`${payoutId}: transactions net ${net}, payout ${payout.amount}`);
  }
  return { payout, txns };
}

The payout itself appears in that list as a balance transaction of type payout, so the loop skips it by ID before adding anything up. The for await loop uses the SDK's auto-pagination, which matters on a busy account, where a single daily payout can hold thousands of transactions. Expanding data.source gives you each charge, refund or dispute with its metadata attached, so you don't have to make another call for every line.

The check at the bottom is the part I would defend hardest in a code review. If the nets don't add up to the payout amount, the function throws an error and stops. Sooner or later, someone will ask you to post the difference to a plug account so the job stops failing. Please don't. A plug account is where an unexplained difference goes to be forgotten.

Turning a payout into a journal entry

Once the list has been checked, building the journal entry should be boring – and I like it that way. Each balance transaction posts its gross amount to an account chosen by reporting_category. Each fee posts to an account chosen by its fee_details type. The bank line is the payout amount. Since net = amount - fee for every transaction, and the nets already add up to the payout, the entry has to balance. The code checks anyway. When the numbers come from someone else's API, I check even the arithmetic that can't possibly be wrong.

export interface JournalLine {
  account: string;
  amountMinor: number; // positive = debit, negative = credit
  sourceRef: string;   // txn_ or po_ id, for drill-down
}

const CATEGORY_ACCOUNT: Record<string, string> = {
  charge: "1210 Stripe clearing",
  refund: "1210 Stripe clearing",
  partial_capture_reversal: "1210 Stripe clearing",
  refund_failure: "1210 Stripe clearing",
  dispute: "1215 Funds held in dispute",
  dispute_reversal: "1215 Funds held in dispute",
  fee: "6150 Payment processing fees",
};
const FEE_ACCOUNT: Record<string, string> = {
  stripe_fee: "6150 Payment processing fees",
  tax: "1410 Recoverable tax on fees",
};
const SUSPENSE = "1299 Stripe suspense";
const BANK = "1010 Operating account";

export function buildJournal({ payout, txns }: PayoutBundle): JournalLine[] {
  const lines: JournalLine[] = [];
  for (const t of txns) {
    lines.push({ account: CATEGORY_ACCOUNT[t.reporting_category] ?? SUSPENSE, amountMinor: -t.amount, sourceRef: t.id });
    let feeBooked = 0;
    for (const fd of t.fee_details) {
      lines.push({ account: FEE_ACCOUNT[fd.type] ?? SUSPENSE, amountMinor: fd.amount, sourceRef: t.id });
      feeBooked += fd.amount;
    }
    if (feeBooked !== t.fee) {
      lines.push({ account: SUSPENSE, amountMinor: t.fee - feeBooked, sourceRef: t.id });
    }
  }
  lines.push({ account: BANK, amountMinor: payout.amount, sourceRef: payout.id });

  const balance = lines.reduce((s, l) => s + l.amountMinor, 0);
  if (balance !== 0) throw new ReconciliationError(`${payout.id}: entry out of balance by ${balance}`);
  return lines.filter((l) => l.amountMinor !== 0);
}

The account numbers are placeholders, so use your own chart of accounts. More importantly, the account map is an accounting decision, and it belongs to your controller. I write the code; finance decides where things land.

A few of the mappings need explaining. Charges credit the clearing account because the entry at the time of sale debited it, and refunds debit it for the same reason in reverse. Dispute debits go to a holding account, and not straight to expense, because a dispute can be won and reversed months later, and finance should record the outcome when it's known. Standalone fee transactions come in with a negative amount and a reporting_category of fee, so they reach fee expense through the first line and never touch the fee loop. The dispute fee comes through one of those two paths, and the code doesn't need to know which. Anything with a category the map doesn't recognize goes to suspense, and someone should be clearing suspense to zero every week. Stripe adds new categories from time to time. If your integration quietly defaults unknown categories to revenue, your ledger will drift, and it's quite possible nobody will notice until the auditors do.

Currency conversion

When a customer pays in a currency you don't settle in, the charge's balance transaction has already been converted into your settlement currency, with an exchange_rate that turns the presentment amount into the settled amount. So the payout entry never sees a single euro. That's correct, because the bank received dollars.

The currency question belongs to the entry made at the time of sale. If you book the receivable in euros at your own daily rate, and then clear it with dollars from Stripe, the difference is a realized FX gain or loss – and your accounting system should calculate it, not your integration. For many businesses, the simpler route is to book the clearing debit from the charge's own balance transaction once the charge succeeds. The clearing account then stays in your settlement currency, and the FX difference sits between receivables and clearing, which is where finance will look for it. One more thing about disputes: the disputed amount can differ from the original charge, because the exchange rate on the day of the dispute isn't the rate on the day of the purchase.

Posting it exactly once

The webhook that kicks all this off can arrive twice, and two workers can pick up the same payout at the same time. I want the database itself to make a second posting impossible. This version assumes PostgreSQL and the pg package.

CREATE TABLE stripe_payout_journal (
  payout_id     text PRIMARY KEY,
  currency      text   NOT NULL,
  amount_minor  bigint NOT NULL,
  arrival_date  date   NOT NULL,
  posted_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE stripe_payout_journal_line (
  payout_id     text   NOT NULL REFERENCES stripe_payout_journal (payout_id),
  line_no       int    NOT NULL,
  account       text   NOT NULL,
  amount_minor  bigint NOT NULL,  -- positive = debit
  source_ref    text   NOT NULL,
  PRIMARY KEY (payout_id, line_no)
);

import { Pool } from "pg";

export async function postPayout(pool: Pool, b: PayoutBundle, lines: JournalLine[]) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const header = await client.query(
      `INSERT INTO stripe_payout_journal (payout_id, currency, amount_minor, arrival_date)
       VALUES ($1, $2, $3, (to_timestamp($4) AT TIME ZONE 'UTC')::date)
       ON CONFLICT (payout_id) DO NOTHING`,
      [b.payout.id, b.payout.currency, b.payout.amount, b.payout.arrival_date],
    );
    if (header.rowCount === 0) {
      await client.query("ROLLBACK");
      return "already_posted" as const;
    }
    for (const [i, l] of lines.entries()) {
      await client.query(
        `INSERT INTO stripe_payout_journal_line (payout_id, line_no, account, amount_minor, source_ref)
         VALUES ($1, $2, $3, $4, $5)`,
        [b.payout.id, i + 1, l.account, l.amountMinor, l.sourceRef],
      );
    }
    await client.query("COMMIT");
    return "posted" as const;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

The primary key on payout_id provides the whole guarantee. If two transactions race, the second insert waits for the first one to commit, and then does nothing. The header and its lines are written together, or not at all. Anything that exports to the ERP should read from these tables, never directly from Stripe, so the ERP gets exactly one entry per payout, no matter how many times the pipeline ran.

Orders that ship in pieces

Everything so far assumes that a charge is captured once. For our apparel client, that assumption fell apart on orders with several items that shipped at different times. Charging the full amount at checkout meant taking money for goods that might not ship for weeks, and charging at the first shipment left nothing clean to charge for the rest. The workflow I implemented uses Stripe's multicapture feature. The whole order is authorized at checkout, and each shipment's share is captured as it leaves the warehouse.

interface Order {
  id: string;
  totalMinor: number;
  currency: string;
}

export async function authorizeOrder(order: Order): Promise<Stripe.PaymentIntent> {
  return stripe.paymentIntents.create(
    {
      amount: order.totalMinor,
      currency: order.currency,
      capture_method: "manual",
      payment_method_options: { card: { request_multicapture: "if_available" } },
      metadata: { order_id: order.id },
    },
    { idempotencyKey: `authorize:${order.id}` },
  );
}

interface Shipment {
  id: string;              // primary key of our shipments row
  amountMinor: number;     // items in this box plus allocated tax, shipping and discounts
  isLast: boolean;
}

export async function captureShipment(paymentIntentId: string, s: Shipment): Promise<Stripe.PaymentIntent> {
  return stripe.paymentIntents.capture(
    paymentIntentId,
    { amount_to_capture: s.amountMinor, final_capture: s.isLast },
    { idempotencyKey: `capture:${s.id}` },
  );
}

The order is authorized at checkout with capture_method: "manual", and with multicapture requested. When a shipment is ready, the integration calculates what that box is worth – the items in it, plus their share of tax, shipping and discounts – and captures exactly that amount with final_capture: false, so the rest of the authorization stays in place. The last shipment captures with final_capture: true, and Stripe releases anything left over. The idempotency key comes from the shipment row, for the same reason the refund key came from the refund row.

Multicapture comes with conditions. First, it isn't available to everyone. Stripe offers it on IC+ pricing, only for online card payments, and only on certain card networks. The charge tells you whether a particular card supports it, so your integration needs a fallback. Second, authorizations expire. A card authorization is good only for a limited time, and if the remaining items won't ship before it runs out, you need a fresh payment authorized by the customer. Assuming the original hold is still there will fail you on exactly the day it matters. Third, the card networks have their own rules. Most limit multicapture to goods that ship separately, which is precisely this case, but check what applies to your business.

Every capture then needs to be reconciled three ways: against the order in BigCommerce, against Stripe's balance transactions, and, eventually, against the bank payout. Captures made on different days settle on different days, so one charge can end up spread across several payouts. The payout code above handles that without any change, because it works one balance transaction at a time and never assumes that one charge means one payout.

After go-live

Some payouts that fail show up as paid first, and change to failed later. When that happens, the payout's failure_balance_transaction returns the funds to your Stripe balance, and you need a reversing entry against the bank line, triggered by the payout.failed event. Manual payouts are the other trap. They can't be listed this way at all, so if someone on the team switches off automatic payouts, the pipeline should stop and say why. It should never guess.

Sooner or later, finance will do the obvious thing, and compare each posted payout's amount and arrival date with the bank statement. With the pipeline built this way, that comparison is a simple join between two tables, and the clearing account will tell them, at any moment, how much Stripe still owes.

And the apparel brand? Unmatched deposits did eventually go to zero – though not on the day the new pipeline went live. Real orders kept turning up situations the first version hadn't anticipated, and each one, the split shipments included, took its own piece of engineering before the books stayed clean. That, to be honest, is what this kind of work looks like. A sound design gets you most of the way on day one. The edge cases get you the rest of the way, one at a time, and the reconciliation check is what tells you which one to fix next.

I'm Jayant Chaudhary, founder of eProxim, where we build payment and ledger integrations for implementation partners and the firms they serve.

Writing a Reconciliation Job in SQL and Python That Proves Two Systems Agree

Jayant Chaudhary — Tue, 20 Oct 2026 13:00:00 +0000

On one engagement, operations and finance relied on a SQL Server data warehouse that brought manufacturing and inventory data across from SAP every night – production volumes, inventory movements and material consumption for several manufacturing plants. The loads ran in SQL Server Integration Services, and every morning the dashboard showed every job as completed successfully. Then a plant controller noticed something odd. According to the warehouse, raw material consumption had stayed almost perfectly flat for four days in a row, while production volumes had gone up substantially.

We picked one goods issue that had been posted in SAP the evening before, and traced it from SAP toward the warehouse. It was there in SAP's material documents. It wasn't in the warehouse. The extraction log said the job had completed successfully – and transferred zero records. As it turned out, the SAP team had recently moved the extraction to a new technical user as part of a security cleanup. That account could still log in and call the RFC function the integration used, but it was no longer authorized to read material documents for two of the plants. The custom RFC wrapper caught the authorization error, wrote it to an application log, and handed SSIS an empty result. As far as SSIS knew, it had called the function, received a valid answer and loaded zero rows. Monitoring only checked whether the package had run, so everything stayed green.

Monitoring on most integrations answers one question: did the job run? Finance wants the answer to a different question – do the two systems agree? – and those are not the same thing at all. A sync can finish successfully every night for a month while a validation rule in the target quietly rejects one record in five hundred, and nothing in the logs will look wrong. To be honest, the only way I know to be sure is to compare the systems directly, on a schedule, and to treat every difference as something somebody has to explain.

In this article, I'll walk through the reconciliation job I would build for a typical case: orders or payments that start life in a source system and get copied into a target, such as an ERP or a ledger. The code uses PostgreSQL 14 or later as the reconciliation store, and Python 3.11 with psycopg 3. Neither the source nor the target has to be Postgres. All they have to be is readable.

Snapshots first, comparison second

The first decision is to stop comparing live systems against each other. If you query both sides at the moment of comparison, the answer depends on whatever happened while the queries were running, and there's no way to go back and re-examine a run afterward. So each run takes a keyed snapshot of both sides – a copy of the records, with their keys, as of that moment – into staging tables, and every step after that works only on the snapshots.

CREATE TABLE recon_run (
  run_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  as_of         timestamptz NOT NULL,
  settle_window interval    NOT NULL DEFAULT '2 hours',
  finished_at   timestamptz
);

CREATE TABLE recon_snapshot (
  run_id     bigint      NOT NULL REFERENCES recon_run,
  side       text        NOT NULL CHECK (side IN ('source', 'target')),
  record_key text        NOT NULL,
  amount     numeric(18,4) NOT NULL,
  currency   char(3)     NOT NULL,
  status     text,
  event_at   timestamptz NOT NULL,   -- source event time, carried into the target
  updated_at timestamptz NOT NULL
);
CREATE INDEX ON recon_snapshot (run_id, side, record_key);

CREATE TABLE recon_tolerance (
  currency    char(3) PRIMARY KEY,
  minor_units int     NOT NULL,             -- 2 for USD, 0 for JPY
  amount_tol  numeric(18,4) NOT NULL DEFAULT 0
);

CREATE TABLE recon_result (
  run_id     bigint NOT NULL REFERENCES recon_run,
  record_key text   NOT NULL,
  outcome    text   NOT NULL,
  diff       jsonb,
  PRIMARY KEY (run_id, record_key, outcome)
);

You'll notice there's no unique constraint on (run_id, side, record_key). That's deliberate. If a key appears twice on one side, I want to know about it – it's a finding in its own right – and I want the job to report it and keep going, instead of crashing on a load error.

The whole design depends on record_key being an identifier both systems share – usually the source's ID, stored on the target record as an external ID. If the integration was built without one, adding it is the first piece of work. You can make fuzzy matching on dates and amounts work, after a fashion, but nobody ever fully trusts what it produces, and a reconciliation report that nobody trusts isn't worth running.

Extraction and normalization

Extraction streams rows into the snapshot with COPY, which is far faster than inserting rows one at a time once you're past a few thousand records. This is also the place for normalization. If the source says paid and the ERP says POSTED, map both to one vocabulary on the way in. The comparison SQL then compares like with like, and the mapping lives in one place, where someone can review it.

from collections.abc import Iterable, Iterator
from datetime import datetime
import psycopg

STATUS_MAP = {"paid": "posted", "refunded": "reversed", "pending": "open"}
COLUMNS = "run_id, side, record_key, amount, currency, status, event_at, updated_at"

def extract_source(src: psycopg.Connection, start: datetime, end: datetime) -> Iterator[tuple]:
    # Server-side cursor so a large window streams instead of loading into memory.
    with src.transaction(), src.cursor(name="recon_extract") as cur:
        cur.execute(
            """SELECT order_id::text, total, currency, status, created_at, updated_at
               FROM orders WHERE created_at >= %s AND created_at < %s""",
            (start, end),
        )
        for key, total, ccy, status, created, updated in cur:
            yield key, total, ccy, STATUS_MAP.get(status, status), created, updated

def load_snapshot(conn: psycopg.Connection, run_id: int, side: str,
                  rows: Iterable[tuple]) -> int:
    n = 0
    with conn.transaction(), conn.cursor() as cur:
        cur.execute("DELETE FROM recon_snapshot WHERE run_id = %s AND side = %s",
                    (run_id, side))
        with cur.copy(f"COPY recon_snapshot ({COLUMNS}) FROM STDIN") as copy:
            for row in rows:
                copy.write_row((run_id, side, *row))
                n += 1
    return n

The target extractor has the same shape, with one important difference: it filters on the source event time that the integration carried across, and not on the target's own creation timestamp. That sounds like a small detail. It isn't. If you cut the source by created_at and the target by the time the ERP inserted the row, every record near the edge of the window will show up as missing on one side or the other, and you'll spend your mornings explaining breaks that aren't really there. Both sides have to be cut by the same clock.

I use a rolling lookback window – typically seven days – instead of reconciling all of history every night. Older periods are still covered, because each night's run overlaps the previous six, and at month-end you can run a wider window with the same code.

The FULL OUTER JOIN that classifies everything

Duplicates get handled first. If a key has two rows on one side, there's no way to match it without guessing which copy is the real one, and I don't want the job guessing.

INSERT INTO recon_result (run_id, record_key, outcome, diff)
SELECT run_id, record_key, 'duplicate_in_' || side, jsonb_build_object('copies', count(*))
FROM recon_snapshot
WHERE run_id = %(run)s
GROUP BY run_id, side, record_key
HAVING count(*) > 1;

Everything else goes through a single statement. A FULL OUTER JOIN keeps rows from both sides whether or not they find a match, so one pass produces every outcome – missing in target, missing in source, mismatched, matched, and in flight. The field-level differences are built in a lateral subquery as a JSON object that holds only the fields that disagree, each as a [source, target] pair. If you haven't used FULL OUTER JOIN much, this is the job it was made for.

INSERT INTO recon_result (run_id, record_key, outcome, diff)
SELECT r.run_id,
       coalesce(s.record_key, t.record_key),
       CASE
         WHEN t.record_key IS NULL AND s.updated_at > r.as_of - r.settle_window THEN 'in_flight'
         WHEN t.record_key IS NULL THEN 'missing_in_target'
         WHEN s.record_key IS NULL THEN 'missing_in_source'
         WHEN d.diff = '{}'::jsonb THEN 'matched'
         WHEN s.updated_at > r.as_of - r.settle_window THEN 'in_flight'
         ELSE 'mismatched'
       END,
       CASE WHEN s.record_key IS NOT NULL AND t.record_key IS NOT NULL
                 AND d.diff <> '{}'::jsonb THEN d.diff END
FROM (SELECT * FROM recon_snapshot WHERE run_id = %(run)s AND side = 'source') s
FULL OUTER JOIN
     (SELECT * FROM recon_snapshot WHERE run_id = %(run)s AND side = 'target') t
  ON s.record_key = t.record_key
JOIN recon_run r ON r.run_id = %(run)s
LEFT JOIN recon_tolerance tol ON tol.currency = coalesce(s.currency, t.currency)
CROSS JOIN LATERAL (
  SELECT jsonb_strip_nulls(jsonb_build_object(
    'amount', CASE WHEN abs(round(s.amount, coalesce(tol.minor_units, 2))
                          - round(t.amount, coalesce(tol.minor_units, 2)))
                        > coalesce(tol.amount_tol, 0)
                   THEN jsonb_build_array(s.amount, t.amount) END,
    'currency', CASE WHEN s.currency IS DISTINCT FROM t.currency
                     THEN jsonb_build_array(s.currency, t.currency) END,
    'status', CASE WHEN s.status IS DISTINCT FROM t.status
                   THEN jsonb_build_array(s.status, t.status) END,
    'event_at', CASE WHEN abs(extract(epoch FROM s.event_at - t.event_at)) > 120
                     THEN jsonb_build_array(s.event_at, t.event_at) END
  )) AS diff
) d
WHERE NOT EXISTS (
  SELECT 1 FROM recon_result x
  WHERE x.run_id = %(run)s AND x.record_key = coalesce(s.record_key, t.record_key)
);

Most of the judgment in this job lives in the tolerances. Amounts are compared after both sides are rounded to the currency's minor units (cents, for dollars). Without that, a source that keeps four decimal places and an ERP that keeps two will disagree on almost every line. I leave amount_tol at zero by default, and raise it to one minor unit only for a target I know rounds differently – for example, one that uses banker's rounding, where Postgres's round() rounds half away from zero. A blanket tolerance of a few cents feels harmless, right up until someone notices it has been quietly absorbing a systematic fee error. Timestamps get two minutes of leeway, because targets often truncate to whole seconds, or store local time with a lossy conversion. Anything beyond two minutes is a real difference, and I want to see it.

The settle window takes care of records that simply haven't arrived yet. If a source record was created or updated in the last two hours, and it's missing or different in the target, it's classified as in_flight and isn't treated as a break. It stays visible in the results, but nobody gets paged. If it's still wrong on the next run, after the window has passed, it becomes a real break. Of everything in this design, I'd say this distinction does the most for the credibility of the report. A report that cries wolf on every record still in transit teaches people to stop reading it.

Control totals by day

Matching record by record does most of the work, but control totals are cheap, and they catch a different kind of failure – a whole batch loaded twice, say, or posted to the wrong period.

WITH daily AS (
  SELECT snap.side, (snap.event_at AT TIME ZONE 'America/New_York')::date AS day,
         snap.currency, count(*) AS n,
         sum(round(snap.amount, coalesce(tol.minor_units, 2))) AS total
  FROM recon_snapshot snap
  LEFT JOIN recon_tolerance tol ON tol.currency = snap.currency
  WHERE snap.run_id = %(run)s
  GROUP BY 1, 2, 3
)
SELECT coalesce(s.day, t.day) AS day, coalesce(s.currency, t.currency) AS currency,
       s.n AS source_count, t.n AS target_count,
       coalesce(s.total, 0) - coalesce(t.total, 0) AS difference
FROM (SELECT * FROM daily WHERE side = 'source') s
FULL OUTER JOIN (SELECT * FROM daily WHERE side = 'target') t
  ON s.day = t.day AND s.currency = t.currency
WHERE s.n IS DISTINCT FROM t.n OR s.total IS DISTINCT FROM t.total
ORDER BY 1, 2;

Amounts are rounded the same way here as in the record matching, so precision differences don't turn up as breaks in the totals. Group by the business day in the time zone where finance closes the books, and not by the UTC date. Otherwise, every evening's orders will straddle two days. And where the target can produce its own totals – a ledger balance by day, for instance – compare against those, and not against your own extract. A total calculated from your own snapshot can't catch a bug in your own extraction.

Breaks that persist until someone works them

A list of differences rebuilt from scratch every night has no memory. You can't tell whether a break appeared last night or three weeks ago, and there's nowhere to record who's responsible for it. So the results feed a recon_break table that persists from one run to the next.

CREATE TABLE recon_break (
  break_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  record_key  text NOT NULL,
  outcome     text NOT NULL,
  diff        jsonb,
  first_seen  timestamptz NOT NULL,
  last_run_id bigint NOT NULL,
  owner       text,
  status      text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'cleared', 'resolved')),
  resolution  text
);
CREATE UNIQUE INDEX recon_break_open ON recon_break (record_key, outcome) WHERE status = 'open';

INSERT INTO recon_break (record_key, outcome, diff, first_seen, last_run_id, owner)
SELECT res.record_key, res.outcome, res.diff, run.as_of, res.run_id, o.owner
FROM recon_result res
JOIN recon_run run ON run.run_id = res.run_id
LEFT JOIN recon_owner o ON o.outcome = res.outcome
WHERE res.run_id = %(run)s AND res.outcome NOT IN ('matched', 'in_flight')
ON CONFLICT (record_key, outcome) WHERE status = 'open'
DO UPDATE SET diff = EXCLUDED.diff, last_run_id = EXCLUDED.last_run_id;

UPDATE recon_break b SET status = 'cleared'
WHERE b.status = 'open' AND b.last_run_id < %(run)s
  AND EXISTS (SELECT 1 FROM recon_result x
              WHERE x.run_id = %(run)s AND x.record_key = b.record_key
                AND x.outcome <> 'in_flight');

recon_owner is a small lookup table that maps each outcome to a team – missing records go to operations, amount mismatches go to finance. The clearing rule is cautious on purpose. A break clears only when this run actually saw the key again, with a different outcome. A key that has aged out of the lookback window hasn't been fixed. It has just stopped being looked at, so it stays open. Better still, have the extractors also pull every key with an open break, however old it is.

The orchestration runs classification and break maintenance in one transaction, under an advisory lock, so two overlapping runs can't clear each other's breaks. Connections are opened with autocommit=True, so the explicit transaction blocks are the only transactions there are.

from datetime import datetime, timedelta

# The four SQL statements shown above, held as module-level strings.
RECON_LOCK = 724_001
STEPS = (DUPLICATES_SQL, CLASSIFY_SQL, UPSERT_BREAKS_SQL, CLEAR_BREAKS_SQL)

def run_recon(conn, src, tgt, as_of: datetime, lookback: timedelta) -> int:
    run_id = conn.execute(
        "INSERT INTO recon_run (as_of) VALUES (%s) RETURNING run_id", (as_of,)
    ).fetchone()[0]
    start = as_of - lookback
    n_src = load_snapshot(conn, run_id, "source", extract_source(src, start, as_of))
    n_tgt = load_snapshot(conn, run_id, "target", extract_target(tgt, start, as_of))
    if n_src == 0 or n_tgt == 0:
        raise RuntimeError(f"run {run_id}: empty extract (source={n_src}, target={n_tgt})")
    with conn.transaction():
        conn.execute("SELECT pg_advisory_xact_lock(%s)", (RECON_LOCK,))
        for sql in STEPS:
            conn.execute(sql, {"run": run_id})
        conn.execute("UPDATE recon_run SET finished_at = now() WHERE run_id = %s", (run_id,))
    return run_id

The empty-extract guard looks almost too simple to bother with. To be honest, it's the check I'd least want to lose. Two empty snapshots reconcile perfectly! An expired credential, a renamed table or a service account that has quietly lost an authorization can produce a clean, green report for a system that hasn't been read at all.

That last one is exactly what happened in the SAP case at the top of this article. Restoring the authorization, re-extracting the missing movements and reconciling the warehouse against SAP by plant and posting date fixed the data. It didn't fix the reason nobody had noticed. So we changed the integration contract: an authorization failure could never again come back as an empty, successful extract – the wrapper had to return a structured error, or fail the call. We also split monitoring into three separate questions. Did the job run? Is the data fresh, meaning have we received transactions with recent posting dates? And is it complete, meaning do counts and quantities agree with SAP by plant and posting date? Finally, we added an alert for abnormal zero-record extracts. One plant can have a quiet day. Several active plants with no material movements at all is something a person should look at.

Reporting so breaks get worked

The alert should be about what changed and what's getting old, and not about how many breaks there are. A message listing four hundred open breaks every morning will be ignored by the second week. Compare that with a message saying three new amount mismatches appeared overnight, and two missing-in-target breaks owned by operations are now past their 24-hour target. That one gives a specific person a specific job.

import httpx

def alert(conn, run_id: int, webhook_url: str, sla_hours: int = 24) -> None:
    rows = conn.execute(
        """WITH run AS (SELECT as_of FROM recon_run WHERE run_id = %(run)s),
           summary AS (
             SELECT b.outcome, coalesce(b.owner, 'unassigned') AS owner,
                    count(*) FILTER (WHERE b.first_seen = run.as_of) AS new,
                    count(*) FILTER (WHERE b.first_seen
                                     < run.as_of - %(sla)s * interval '1 hour') AS aged
             FROM recon_break b CROSS JOIN run
             WHERE b.status = 'open'
             GROUP BY 1, 2)
           SELECT outcome, owner, new, aged FROM summary
           WHERE new > 0 OR aged > 0 ORDER BY aged DESC""",
        {"run": run_id, "sla": sla_hours},
    ).fetchall()
    if not rows:
        return
    lines = [f"{o} ({owner}): {new} new, {aged} past {sla_hours}h" for o, owner, new, aged in rows]
    httpx.post(webhook_url, json={"text": f"Reconciliation run {run_id}\n" + "\n".join(lines)},
               timeout=10).raise_for_status()

The payload is the plain text form that Slack incoming webhooks accept, and any chat or ticketing endpoint works the same way. When someone resolves a break, they set status = 'resolved' and write what they did in resolution. There's one rule I hold teams to: a break gets fixed where it started, by correcting the source or the integration – never by editing the target until the numbers line up. Hand-editing the target turns tonight's report green, and it destroys the evidence of why it was red in the first place.

Over time, the resolution column turns into the most useful data in the whole system. If the same cause shows up every week, that's a defect in the integration, and the reconciliation job has just told you exactly where to look. The job proves agreement record by record. The process around it – who signs off, what goes in the period report, how timing items are presented – is a separate design problem, and I've written about it in more depth in a piece on designing reconciliation reports finance will sign off against.

I'm Jayant Chaudhary, founder of eProxim, where we build integrations that carry money and inventory between systems, and reconciliation is part of every one.

Building an Idempotent Webhook Receiver with PostgreSQL and Python

Jayant Chaudhary — Mon, 12 Oct 2026 01:02:28 +0000

Most webhook receivers I've reviewed were written on a quiet assumption: each event shows up exactly once, and in the order things happened. Neither is true, and the code usually works fine until the day it doesn't. Providers deliver at least once. They retry whenever your endpoint is slow or returns an error, and they don't promise anything about ordering. Stripe says this outright in its docs, and the Standard Webhooks spec builds its whole retry model around the receiver deduplicating by message ID. So the receiver's job isn't just to accept events. It has to soak up the duplicates, the retries and the shuffling and still leave your database right.

What follows is the receiver I'd actually put into production, built up one piece at a time: Python 3.11, FastAPI, psycopg 3, and PostgreSQL doing the work that really matters. I'm assuming PostgreSQL 13 or later and a provider that signs with the Standard Webhooks scheme, but the shape carries over to any provider that gives you signed payloads and stable event IDs.

Verify the signature against the raw body

Signature checking comes first, and it has to run against the exact bytes that came over the wire. This one bites people constantly: the framework parses the JSON, someone re-serializes it to verify, the key order or whitespace shifts, and the HMAC never matches again. Standard Webhooks sends three headers: webhook-id, webhook-timestamp and webhook-signature. The signed content is {id}.{timestamp}.{body}, the signature is a base64 HMAC-SHA256, and the header can carry several space-separated v1,<sig> entries so the sender can rotate secrets without downtime.

import base64
import hashlib
import hmac
import time


class SignatureError(Exception):
    pass


def verify_signature(secret: str, msg_id: str, timestamp: str,
                     body: bytes, header: str, tolerance: int = 300) -> None:
    try:
        ts = int(timestamp)
    except ValueError:
        raise SignatureError("invalid timestamp") from None
    if abs(time.time() - ts) > tolerance:
        raise SignatureError("timestamp outside tolerance")

    key = base64.b64decode(secret.removeprefix("whsec_"))
    signed = f"{msg_id}.{timestamp}.".encode() + body
    expected = base64.b64encode(hmac.new(key, signed, hashlib.sha256).digest())

    for entry in header.split():
        version, _, sig = entry.partition(",")
        if version == "v1" and hmac.compare_digest(sig.encode(), expected):
            return
    raise SignatureError("no matching signature")

Two small things in there matter more than they look. The comparison has to be constant-time, which is what hmac.compare_digest is for, and I compare bytes rather than strings because a header from an attacker can contain non-ASCII characters that make the string version throw. And since the timestamp is part of what's signed, rejecting old ones is what stops someone from replaying a request they captured.

The smallest correct version

Before adding queues and workers, it's worth seeing the core idea on its own, because everything later is built on it. You keep a table of the event IDs you've processed, and you insert the ID in the same transaction as whatever the event changes.

CREATE TABLE processed_events (
    provider   text        NOT NULL,
    event_id   text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (provider, event_id)
);

def handle_event(conn, provider: str, event_id: str, event: dict) -> None:
    with conn.transaction():
        cur = conn.execute(
            "INSERT INTO processed_events (provider, event_id) VALUES (%s, %s) "
            "ON CONFLICT (provider, event_id) DO NOTHING",
            (provider, event_id),
        )
        if cur.rowcount == 0:
            return  # already processed; acknowledge and stop
        apply_event(conn, event["type"], event)  # same database

It's worth slowing down on why this holds up under concurrency, because this is exactly the part people rebuild badly with a SELECT to check and then an INSERT. That version has a gap between the check and the write, and two copies of the same event will both slip through it. Here, when two copies arrive on two servers at the same moment, both transactions try to insert the same key, and PostgreSQL makes the second one wait on the unique index until the first commits or rolls back. If the first commits, the second hits the conflict, inserts nothing and gets a row count of zero. If the first rolls back because apply_event blew up, the second carries on as though it had been first all along. The whole guarantee comes from the dedup record and the business write committing, or disappearing, together.

The trouble with this version is that it does all the work inside the HTTP request. If apply_event is slow, the provider times out and retries, and those retries stack up during precisely the incidents when you can least afford extra load.

Acknowledge fast with an inbox table

So split receiving from processing. The HTTP handler checks the signature, drops the raw event into an inbox table and returns, and a worker picks it up afterward. The inbox carries the same unique key, so the insert is still where deduplication happens; it has just moved to the front door.

CREATE TABLE webhook_inbox (
    provider        text        NOT NULL,
    event_id        text        NOT NULL,
    event_type      text        NOT NULL,
    payload         jsonb       NOT NULL,
    status          text        NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'processed', 'parked', 'dead')),
    attempts        int         NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    last_error      text,
    received_at     timestamptz NOT NULL DEFAULT now(),
    processed_at    timestamptz,
    PRIMARY KEY (provider, event_id)
);

CREATE INDEX webhook_inbox_ready
    ON webhook_inbox (next_attempt_at) WHERE status = 'pending';

import json
import os
from contextlib import asynccontextmanager

from fastapi import FastAPI, Request, Response
from psycopg.types.json import Jsonb
from psycopg_pool import AsyncConnectionPool

SECRET = os.environ["WEBHOOK_SECRET"]
pool = AsyncConnectionPool(os.environ["DATABASE_URL"], open=False)


@asynccontextmanager
async def lifespan(app: FastAPI):
    await pool.open()
    yield
    await pool.close()


app = FastAPI(lifespan=lifespan)


@app.post("/webhooks/billing")
async def billing_webhook(request: Request) -> Response:
    body = await request.body()
    h = request.headers
    msg_id = h.get("webhook-id", "")
    try:
        if not msg_id:
            raise SignatureError("missing webhook-id")
        verify_signature(SECRET, msg_id, h.get("webhook-timestamp", ""),
                         body, h.get("webhook-signature", ""))
        event = json.loads(body)
        if not isinstance(event, dict):
            raise ValueError("payload is not a JSON object")
    except (SignatureError, ValueError):
        return Response(status_code=400)

    async with pool.connection() as conn:  # commits on clean exit
        await conn.execute(
            "INSERT INTO webhook_inbox (provider, event_id, event_type, payload) "
            "VALUES ('billing', %s, %s, %s) "
            "ON CONFLICT (provider, event_id) DO NOTHING",
            (msg_id, event.get("type", "unknown"), Jsonb(event)),
        )
    return Response(status_code=204)

Notice that a duplicate gets the same 204 as a brand-new event. That's on purpose. As far as the provider is concerned the event was delivered, and answering with anything other than success only buys you more retries.

The same thinking applies on the way out. If handling an event means telling some other system about it, have the worker write that message to an outbox table in the same transaction as its business writes, and let a separate relay deliver it. The inbox protects you from things that arrive twice; the outbox stops you from sending something you never committed, or committing something you never sent.

The worker: claim, apply, mark, all in one transaction

The worker grabs one pending row with FOR UPDATE SKIP LOCKED, which lets you run as many workers as you like without them tripping over the same event. It applies the event and marks it processed inside the same transaction that holds the lock, so the inbox can never claim something happened that the business tables don't reflect.

import psycopg


class EntityNotYetKnown(Exception):
    pass


CLAIM = """
    SELECT provider, event_id, event_type, payload, attempts
    FROM webhook_inbox
    WHERE status = 'pending' AND next_attempt_at <= now()
    ORDER BY next_attempt_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
"""


def process_one(conn: psycopg.Connection) -> bool:
    with conn.transaction():
        row = conn.execute(CLAIM).fetchone()
        if row is None:
            return False
        provider, event_id, event_type, payload, attempts = row
        try:
            with conn.transaction():  # savepoint around the business writes
                apply_event(conn, event_type, payload)
        except EntityNotYetKnown as exc:
            reschedule(conn, provider, event_id, str(exc), max_attempts=8, final="parked")
        except Exception as exc:
            reschedule(conn, provider, event_id, repr(exc), max_attempts=12, final="dead")
        else:
            conn.execute(
                "UPDATE webhook_inbox SET status = 'processed', processed_at = now() "
                "WHERE provider = %s AND event_id = %s",
                (provider, event_id),
            )
    return True


def reschedule(conn, provider, event_id, error, *, max_attempts, final):
    conn.execute(
        """
        UPDATE webhook_inbox
        SET attempts = attempts + 1,
            last_error = %s,
            next_attempt_at = now() + least(power(2, attempts), 3600) * interval '1 second',
            status = CASE WHEN attempts + 1 >= %s THEN %s ELSE 'pending' END
        WHERE provider = %s AND event_id = %s
        """,
        (error, max_attempts, final, provider, event_id),
    )

One setup detail: open the connection with autocommit=True, so the outer conn.transaction() is a real BEGIN/COMMIT and the inner one becomes a savepoint. That way a failing apply_event rolls back its own half-finished writes while the retry bookkeeping still gets committed.

I should be upfront about the limit of all this. The transaction only covers writes to this database. If apply_event calls some downstream API, you can't roll that call back, and a crash after the call but before the commit means it will run again. For those calls, either pass the event ID along as the downstream system's idempotency key, or put the call behind the outbox so the transaction only records the intention to make it.

Out-of-order events need a per-entity version guard

Deduplication doesn't fix ordering, and this is where a lot of otherwise careful receivers fall down. An updated event can turn up after a later updated event, and a plain upsert will cheerfully overwrite the new state with the old one. The defense is to keep, for each entity, the version of the source state you last applied, and to ignore anything older. PostgreSQL lets you put that rule right into the upsert.

INSERT INTO subscriptions (external_id, status, plan, source_version)
VALUES (%(id)s, %(status)s, %(plan)s, %(version)s)
ON CONFLICT (external_id) DO UPDATE
SET status         = EXCLUDED.status,
    plan           = EXCLUDED.plan,
    source_version = EXCLUDED.source_version,
    updated_at     = now()
WHERE subscriptions.source_version < EXCLUDED.source_version;

If two workers apply events for the same subscription at the same time, the row lock taken by ON CONFLICT DO UPDATE lines them up, and the WHERE clause is checked against the latest committed row. Whichever one wins the race, the older event ends up doing nothing.

The SQL is the easy part. The real question is what you put in source_version. It has to be something the provider assigns and moves forward, like a revision number or a fine-grained modification timestamp on the object itself. Don't use your own receive time, and be careful with coarse event-creation timestamps: Stripe specifically warns that its created field is in seconds, so different events can share a value, and that you shouldn't use it to order events. When the provider gives you nothing you can trust, my preference is to treat the webhook as a nudge, fetch the object's current state from the API inside apply_event, and use the version or timestamp from that response. It costs you an API call, and in exchange ordering stops mattering at all.

Events for entities you have not seen yet

The last case is the one that produces the weirdest bugs, the kind that take a day to track down. Say an invoice.paid event arrives for a subscription your system has never heard of, because the subscription.created event is still stuck in a retry loop somewhere. Inserting the invoice will either break a foreign key or leave you with an orphan.

There are three sensible ways to handle it, and I'd reach for them in this order. If the provider's API lets you fetch the parent, do that inside apply_event and create it from the real, current state. If you can't fetch it, raise EntityNotYetKnown and let the worker retry with backoff, which is what the code above does; the missing event usually shows up shortly. And if the retries run out, park the event instead of dropping it or calling it dead. "Parked" means a valid event that's missing context, and it deserves its own alert and its own runbook, separate from code failures. There's a fourth option people often grab, which is creating a placeholder parent row full of nulls. I'd steer clear of it. Placeholders have a way of leaking into reports and downstream syncs long after the real record has arrived.

The inbox needs a retention rule, because deduplication only works while the event ID is still in the table. A processed row has to outlive every window in which the same event might arrive again. That means the provider's automatic retries (Stripe, for example, keeps trying for up to three days) and the manual resends that someone on either side can trigger from a dashboard. Thirty days is a sensible default that covers both with room to spare. A scheduled job can then delete processed rows in small batches, so the cleanup never holds long locks on a busy table:

DELETE FROM webhook_inbox
WHERE (provider, event_id) IN (
    SELECT provider, event_id FROM webhook_inbox
    WHERE status = 'processed'
      AND processed_at < now() - interval '30 days'
    LIMIT 5000
);

Parked and dead rows stay until someone has dealt with them, because they are the record of what still needs fixing.

A nice side effect of parked and dead rows is that replay comes for free. Once you've fixed the bug or backfilled the missing parent, set the affected rows back to pending with next_attempt_at = now(), and the worker will reprocess them safely, because every step above is idempotent. Even with all of this in place, I'd still run a periodic reconciliation against the provider's API. Webhooks tell you what changed when delivery works; reconciliation tells you whether it actually did. I wrote about where this pattern fits in a broader integration, including what to demand when you are the one sending webhooks, in this guide to handling duplicate and out-of-order webhook events.

I'm Jayant Chaudhary, founder of eProxim, where we build event-driven integrations for implementation partners and the firms they serve.