Back to Blog
Node.js

How to Implement Payment Reconciliation for Payment Gateways

A practical Node.js guide to payment reconciliation—why ledgers drift from gateways, how to match settlements, and how to resolve mismatches with clear exception workflows.

How to implement payment reconciliation for payment gateways: matching gateway settlements to an internal ledger, then classifying matched, mismatched, and unmatched transactions.

I am Sajan Acharya, a Senior Software Engineer based in Kathmandu. If you have ever opened a payment gateway dashboard and found a successful charge that your app still shows as pending, you already know why payment reconciliation matters. Idempotency stops many double charges at the door, but it does not catch delayed webhooks, missed settlement rows, refunds without order IDs, or charges created by hand in the PSP console. Payment reconciliation is the habit of comparing your internal ledger to the gateway’s truth—every day—then fixing every gap with an owned workflow.

This guide shows how to implement payment reconciliation for payment gateways on a Node.js backend, with terminal-style examples so the “why” is obvious before the architecture. Pair it with how to implement idempotency keys in payment gateways. Prevention plus detection beats either alone. If you accept payments in Nepal, see how to integrate eSewa, Khalti, and Fonepay in Node.js for the gateway adapters this reconciliation job verifies against. For production workers and APIs, see Node.js developer services.

Why payment reconciliation exists (a real failure story)

Imagine checkout times out after the gateway already captured NPR 5,000. Your API marks the payment failed. The customer retries with a new key and gets charged again—or they do not retry, support gets a ticket, and finance asks why settlement includes money your ledger never booked. Webhooks can also arrive late or twice. Without reconciliation, those gaps hide until month-end CSV panic.

Here is what “drift” looks like when you dump both sides into a terminal:

# Your database (internal ledger)
$ node scripts/dump-payments.js --date 2026-08-20
order_id  provider_id     amount  status     created_at
ORD-1842  pay_8f7b2e...   500000  pending    2026-08-20T09:12:01Z
ORD-1843  pay_9c1a44...   120000  paid       2026-08-20T10:04:22Z
ORD-1844  null            89000   failed     2026-08-20T11:18:09Z

# Gateway settlement / list API (source of money truth)
$ node scripts/dump-gateway.js --date 2026-08-20
provider_id     merchant_ref  amount  status    fees
pay_8f7b2e...   ORD-1842      500000  captured  7500
pay_9c1a44...   ORD-1843      120000  captured  1800
pay_aa0191...   ORD-1844      89000   captured  1335
ch_manual_01    (none)        250000  captured  3750

# What reconciliation should scream about:
# 1) ORD-1842  -> CONFLICT: gateway captured, you still pending
# 2) ORD-1844  -> CONFLICT: gateway captured, you marked failed
# 3) ch_manual_01 -> GATEWAY_ONLY: money in PSP, no local payment
# 4) (none missing here) -> INTERNAL_ONLY would mean you "paid" with no PSP row

That output is the whole product problem. Payment gateway reconciliation is not accounting theatre. It is how you find money movement your app never learned about—or status your app got wrong—before customers and finance find it first.

Define the two ledgers you will match

Your internal ledger is whatever your product trusts for order status: a payments table with amount, currency, status, provider payment ID, idempotency key, and timestamps. The gateway ledger is their charge list, webhook history, or daily settlement file. Matching fails when either side lacks a stable join key. Always persist the provider payment ID as soon as you know it, and always send your order ID as the merchant reference on every gateway call.

Store amounts as integers in minor units (paisa / cents). Map gateway statuses onto your domain statuses in one place—never compare raw PSP strings ad hoc.

// src/payments/types.ts
export type PaymentStatus =
  | "pending"
  | "paid"
  | "failed"
  | "refunded"
  | "disputed";

export type GatewayStatus =
  | "authorized"
  | "captured"
  | "failed"
  | "refunded"
  | "disputed";

export function normalizeGatewayStatus(status: GatewayStatus): PaymentStatus {
  switch (status) {
    case "authorized":
      return "pending";
    case "captured":
      return "paid";
    case "failed":
      return "failed";
    case "refunded":
      return "refunded";
    case "disputed":
      return "disputed";
  }
}

// Bad:  amount === 5000.00 with floats
// Good: amountMinor === 500000  // NPR 5,000.00

Ingest gateway truth on a schedule

Run a worker that pulls transactions for a rolling window—yesterday plus a few days of lookback for late updates. Prefer settlement reports when the PSP provides them; otherwise page through list APIs with cursors and rate limits. Store raw payloads first, then project normalized rows. Raw storage saves you when a mapping bug appears weeks later.

$ # Run nightly (or hourly) outside the checkout request path
$ pnpm reconcile:import --from 2026-08-18 --to 2026-08-20

[recon] batch=bat_01J batch window=2026-08-18..2026-08-20
[recon] fetched gateway rows=412  upserted=412  raw_saved=412
[recon] alert=none
// src/reconciliation/importGateway.ts
async function importGatewayWindow(from: Date, to: Date, batchId: string) {
  let cursor: string | undefined;

  do {
    const page = await gateway.listTransactions({ from, to, cursor });

    for (const tx of page.data) {
      await db.gatewayImports.insert({
        batchId,
        providerId: tx.id,
        raw: tx, // immutable audit copy
      });

      await db.gatewayTransactions.upsert({
        where: { providerId: tx.id },
        create: normalizeGatewayRow(tx, batchId),
        update: normalizeGatewayRow(tx, batchId),
      });
    }

    cursor = page.nextCursor;
  } while (cursor);
}
  • Use a rolling lookback, not only “today,” so late settles still appear
  • Upsert by provider transaction ID so re-runs are safe
  • Normalize currency, amount, fees, and net separately when available
  • Record import batch IDs for audit and reprocessing
  • Alert when an import job fails or returns empty on a business day

Match, classify, and queue exceptions

Matching should be automatic for the happy majority. Primary match: provider payment ID. Secondary match: merchant reference + amount + currency in a day window. Fuzzy heuristics belong in a human review queue—never silent auto-apply. Classify every row as matched, internal_only, gateway_only, or conflict.

// src/reconciliation/match.ts
type Outcome = "matched" | "internal_only" | "gateway_only" | "conflict";

function classify(local?: LocalPayment, remote?: GatewayTx): Outcome {
  if (local && remote) {
    const statusOk =
      local.status === normalizeGatewayStatus(remote.status);
    const amountOk = local.amountMinor === remote.amountMinor;
    return statusOk && amountOk ? "matched" : "conflict";
  }
  if (local && !remote) return "internal_only";
  return "gateway_only";
}

async function reconcileDay(day: string) {
  const locals = await db.payments.findByDay(day);
  const remotes = await db.gatewayTransactions.findByDay(day);
  const byProvider = new Map(remotes.map((r) => [r.providerId, r]));
  const byRef = new Map(remotes.map((r) => [r.merchantRef, r]));

  for (const local of locals) {
    const remote =
      (local.providerId && byProvider.get(local.providerId)) ||
      byRef.get(local.orderId);

    const outcome = classify(local, remote);
    if (outcome !== "matched") {
      await db.reconciliationExceptions.upsert({
        day,
        outcome,
        orderId: local.orderId,
        providerId: remote?.providerId ?? local.providerId,
        localSnapshot: local,
        remoteSnapshot: remote ?? null,
        status: "open",
      });
    }
  }

  // Catch gateway rows that never joined a local payment
  for (const remote of remotes) {
    const hasLocal = locals.some(
      (l) =>
        l.providerId === remote.providerId ||
        l.orderId === remote.merchantRef
    );
    if (!hasLocal) {
      await db.reconciliationExceptions.upsert({
        day,
        outcome: "gateway_only",
        providerId: remote.providerId,
        remoteSnapshot: remote,
        status: "open",
      });
    }
  }
}
$ pnpm reconcile:match --date 2026-08-20

[recon] matched=398
[recon] conflict=2        # status/amount disagree
[recon] internal_only=1   # you think paid, PSP has nothing
[recon] gateway_only=3    # PSP has money, you have no row
[recon] open_exceptions=6
[recon] amount_variance_minor=339000

Give each exception type a playbook. Internal_only may be a pending webhook or a failed write after a successful charge—check the idempotency record and PSP dashboard. Gateway_only may be a missed webhook or a manual dashboard charge—link carefully, never by guessing the order. Conflicts need a human before you overwrite financial state.

Near-real-time webhooks versus batch settlement

Webhooks update UX quickly. Daily (or hourly) reconciliation is the financial close. Deduplicate webhook events by event ID the same way you dedupe charges with idempotency keys. If a webhook says paid and settlement later says reversed, reconciliation must reopen the case. Refunds and chargebacks need their own matchers against the original payment ID.

// Webhook handler: fast path for UX, not the final books
async function onChargeSucceeded(event: GatewayEvent) {
  const inserted = await db.webhookEvents.tryInsert({
    eventId: event.id, // unique constraint = natural dedupe
    type: event.type,
  });
  if (!inserted) return; // duplicate delivery

  await db.payments.markPaid({
    providerId: event.data.paymentId,
    paidAt: event.createdAt,
  });
}

// Settlement job: final truth for finance
// If settlement disagrees with markPaid, open a conflict exception.

Scale importers and matchers like any other async pipeline: queues, retries, and visibility timeouts. Guidance in how to scale APIs with Node.js applies when settlement files grow or you add multiple PSP accounts. Keep reconciliation off the checkout request path so a slow import never blocks payments.

Controls, reports, and team habits

Ship a daily reconciliation report finance and engineering share: matched count, exceptions by type, amount variance, and aging open items. Page on-call when gateway_only cases sit open longer than your SLA. Write PSP-specific runbooks. Without runbooks, reconciliation becomes hero culture instead of a system.

  • Never delete financial events—void or reverse with new events
  • Require audit logs (or dual control) when manually linking payments to orders
  • Reconcile fees and taxes if books need net revenue, not only gross
  • Seed mismatches in staging before trusting production jobs
  • Version your status mapping when the gateway changes enums

Keep reconciliation modular so it can grow

Put importers, matchers, and exception use cases in their own module so checkout stays readable. That boundary matters when you add a second gateway or when finance tools start consuming reconciliation summaries through an internal API. Keep checkout focused on charging safely; keep reconciliation focused on proving the books still match.

From Kathmandu I help product teams turn gateway CSV panic into scheduled matching with clear exception queues. If your ledger and PSP reports already disagree, get in touch with your gateway, payment schema, and the worst mismatch examples you have. We can implement import, match, and resolution so payment reconciliation is a daily habit—not a month-end fire drill.