Reconciliation jobs catch silent failures

Money mismatches hide until someone checks. You ship a payment feature, tests pass, orders flow in, but somewhere between your database and the payment gateway's records, numbers quietly drift apart. A week later, finance reports we've collected $47,000 but only processed $46,500. Nobody noticed because nobody was looking. That's where reconciliation jobs step in—they're the automated guardians that catch these silent failures before they become compliance nightmares.

I've built payment systems for e-commerce and travel platforms where a single reconciliation bug cost us weeks of manual investigation. The reconciliation job wasn't just a nice-to-have; it was the difference between trusting our numbers and having finance rebuild reports manually. In this article, I'll walk you through building reconciliation that actually catches problems, keeps your ledger honest, and gives you confidence in every transaction.

Compare gateway reports with your orders daily

The foundation of reconciliation is simple: pull what the gateway says happened and compare it against what you recorded. This sounds straightforward until you realize gateways report in their own timezone, with their own terminology, and on their own schedule. A Stripe charge might settle differently than a PayPal transaction. Refunds report separately from chargebacks. Without daily comparison, discrepancies pile up.

Start by querying your payment gateway's reports—Stripe Settlement Reports, PayPal Transaction Logs, whatever your provider offers. Then fetch your own order records for the same period. The key is consistency: always reconcile the same window (usually yesterday's transactions) at the same time (usually 2-3 AM, after overnight processing settles).

typescript


// Reconciliation job runner
async function runDailyReconciliation() {
  const yesterday = subDays(startOfDay(new Date()), 1);
  const reconciliationDate = format(yesterday, 'yyyy-MM-dd');
  
  console.log(`Starting reconciliation for ${reconciliationDate}`);
  
  try {
    // Fetch gateway transactions
    const gatewayTransactions = await stripe.reportRuns.create({
      report_type: 'balance.transaction_interval_report',
      parameters: {
        interval_start: getUnixTime(startOfDay(yesterday)),
        interval_end: getUnixTime(endOfDay(yesterday)),
      },
    });
    
    // Parse gateway report
    const gatewayData = await parseStripeReport(gatewayTransactions);
    
    // Fetch our recorded orders
    const ourOrders = await db.orders.findMany({
      where: {
        createdAt: {
          gte: startOfDay(yesterday),
          lt: startOfDay(new Date()),
        },
      },
      include: { payments: true },
    });
    
    // Compare and flag mismatches
    const discrepancies = compareTransactions(gatewayData, ourOrders);
    
    // Store results
    await db.reconciliationRun.create({
      data: {
        date: yesterday,
        gatewayTransactionCount: gatewayData.length,
        ourTransactionCount: ourOrders.length,
        discrepancies,
        status: discrepancies.length > 0 ? 'FLAGGED' : 'CLEAN',
      },
    });
    
    if (discrepancies.length > 0) {
      await alertFinance(reconciliationDate, discrepancies);
    }
    
  } catch (error) {
    console.error('Reconciliation failed:', error);
    await db.reconciliationRun.create({
      data: {
        date: yesterday,
        status: 'FAILED',
        error: error.message,
      },
    });
    await alertOps('Reconciliation job failed', error);
  }
}

Run this as a scheduled job (I use AWS EventBridge + Lambda, or a simple node-cron in development). The magic isn't the code—it's the discipline of running it every single day without exception. Reconciliation isn't useful if it only happens when you remember.

Flag missing, duplicate and mismatched payments

Three types of problems surface in reconciliation, and each needs different handling. Missing payments are orders we recorded but the gateway never confirms. Duplicates are transactions that appear twice in our system. Mismatches are orders where the amount or status doesn't align. Each one needs to be detected, classified, and flagged clearly.

Missing payments often happen when a webhook fails silently or a customer closes their browser mid-flow. The gateway has the charge, but your system never got the confirmation. Duplicates occur when webhooks retry (intentionally) and your idempotency logic fails. Mismatches happen when currency conversion rounds differently than expected, or a partial refund wasn't fully recorded.

typescript


interface TransactionComparison {
  type: 'MISSING' | 'DUPLICATE' | 'MISMATCH' | 'CLEAN';
  orderId?: string;
  gatewayId: string;
  ourAmount?: number;
  gatewayAmount: number;
  reason: string;
}

function compareTransactions(
  gateway: GatewayTransaction[],
  orders: OrderWithPayments[]
): TransactionComparison[] {
  const discrepancies: TransactionComparison[] = [];
  const processedGatewayIds = new Set();
  
  // Check for missing payments (in gateway, not in our system)
  for (const gw of gateway) {
    const match = orders.find(
      o => o.payments.some(
        p => p.gatewayId === gw.id && p.amount === gw.amount
      )
    );
    
    if (!match) {
      discrepancies.push({
        type: 'MISSING',
        gatewayId: gw.id,
        gatewayAmount: gw.amount,
        reason: `Gateway charge ${gw.id} (${gw.amount}) not found in orders`,
      });
    } else {
      processedGatewayIds.add(gw.id);
    }
  }
  
  // Check for duplicates (one order with multiple matching payments)
  for (const order of orders) {
    const matchingPayments = order.payments.filter(
      p => gateway.some(g => g.id === p.gatewayId)
    );
    
    if (matchingPayments.length > 1) {
      discrepancies.push({
        type: 'DUPLICATE',
        orderId: order.id,
        gatewayId: matchingPayments.map(p => p.gatewayId).join(','),
        ourAmount: matchingPayments.reduce((sum, p) => sum + p.amount, 0),
        reason: `Order ${order.id} has ${matchingPayments.length} payments in gateway`,
      });
    }
  }
  
  // Check for mismatches (different amounts)
  for (const order of orders) {
    for (const payment of order.payments) {
      const gwTx = gateway.find(g => g.id === payment.gatewayId);
      if (gwTx && gwTx.amount !== payment.amount) {
        discrepancies.push({
          type: 'MISMATCH',
          orderId: order.id,
          gatewayId: payment.gatewayId,
          ourAmount: payment.amount,
          gatewayAmount: gwTx.amount,
          reason: `Amount mismatch: we have ${payment.amount}, gateway says ${gwTx.amount}`,
        });
      }
    }
  }
  
  return discrepancies;
}

Each discrepancy type needs investigation. Missing payments might mean a webhook really did fail—you need to manually create the payment record. Duplicates need deduplication (usually keeping the earliest record). Mismatches might be rounding errors that need manual approval from finance before you adjust.

Alert finance on discrepancies

Finding problems is worthless if nobody acts on them. Set up immediate alerts to your finance team when reconciliation flags anything. This isn't a daily digest—it's real-time notification because reconciliation only works if discrepancies get human eyes quickly.

I've seen teams that run reconciliation but never act on it. Months later, their discrepancy count was in the thousands and nobody could trace what went wrong. Finance teams need clear, actionable alerts: which order is affected, what's the exact mismatch, and what action is needed.

typescript


async function alertFinance(
  date: string,
  discrepancies: TransactionComparison[]
): Promise {
  
  // Group by type for clarity
  const byType = groupBy(discrepancies, 'type');
  
  const alert = {
    title: `Reconciliation Alert for ${date}`,
    date,
    summary: `Found ${discrepancies.length} discrepancies`,
    breakdown: {
      missing: (byType.MISSING || []).length,
      duplicate: (byType.DUPLICATE || []).length,
      mismatch: (byType.MISMATCH || []).length,
    },
    details: discrepancies.map(d => ({
      type: d.type,
      orderId: d.orderId,
      gatewayId: d.gatewayId,
      amount: d.ourAmount ? `${d.ourAmount} vs ${d.gatewayAmount}` : d.gatewayAmount,
      reason: d.reason,
      link: `${DASHBOARD_URL}/orders/${d.orderId}?reconciliation=true`,
    })),
  };
  
  // Send to Slack
  if (byType.MISSING?.length || byType.DUPLICATE?.length) {
    // Critical alerts go to #finance-critical
    await slack.post('#finance-critical', {
      text: '⚠️ ' + alert.title,
      blocks: [
        {
          type: 'section',
          text: {
            type: 'mrkdwn',
            text: `*${alert.title}*\n${alert.breakdown.missing} missing, ${alert.breakdown.duplicate} duplicate, ${alert.breakdown.mismatch} mismatch`,
          },
        },
        ...alert.details.slice(0, 5).map(d => ({
          type: 'section',
          text: {
            type: 'mrkdwn',
            text: `*${d.type}*: Order ${d.orderId || d.gatewayId}\n${d.reason}\n<${d.link}|View in dashboard>`,
          },
        })),
      ],
    });
  } else if (byType.MISMATCH?.length) {
    // Minor alerts go to #finance
    await slack.post('#finance', {
      text: 'ℹ️ ' + alert.title,
      blocks: [{
        type: 'section',
        text: {
          type: 'mrkdwn',
          text: `Found ${alert.breakdown.mismatch} minor amount mismatches. Review in reconciliation dashboard.`,
        },
      }],
    });
  }
  
  // Store alert history
  await db.reconciliationAlert.create({
    data: {
      date: new Date(date),
      discrepancyCount: discrepancies.length,
      alert,
      status: 'OPEN',
    },
  });
}

Make alerts actionable: include a direct link to the order in your dashboard, show the exact discrepancy amount, and state what manual action is needed. Finance needs to see at a glance whether this is "payment failed to record" (they create it manually) or "we double-charged" (they initiate a refund).

Keep a full audit trail

Reconciliation only has value if you can trace what happened and when. Store every reconciliation run—even the clean ones—with full details of what was compared, what was found, and what action was taken. This audit trail is your defense if something goes wrong and someone asks "why didn't you catch this earlier?"

Your audit trail should answer: What date was reconciled? How many transactions on each side? Which discrepancies were found? What alert was sent? What action did finance take? This isn't just compliance theater—it's your insurance policy against silent cascading failures.

typescript


interface ReconciliationRecord {
  id: string;
  date: Date;
  status: 'CLEAN' | 'FLAGGED' | 'FAILED';
  gatewayTransactionCount: number;
  ourTransactionCount: number;
  discrepancies: TransactionComparison[];
  alertSent: boolean;
  alertId?: string;
  resolvedBy?: string;
  resolvedAt?: Date;
  resolutionNotes?: string;
  createdAt: Date;
}

// Store comprehensively
async function storeReconciliationRun(
  date: Date,
  gateway: GatewayTransaction[],
  orders: OrderWithPayments[],
  discrepancies: TransactionComparison[]
): Promise {
  
  const record = await db.reconciliationRun.create({
    data: {
      date: startOfDay(date),
      status: discrepancies.length > 0 ? 'FLAGGED' : 'CLEAN',
      gatewayTransactionCount: gateway.length,
      ourTransactionCount: orders.length,
      discrepancies: JSON.stringify(discrepancies),
      
      // Store raw reports for audit trail
      gatewayReport: JSON.stringify(gateway.map(g => ({
        id: g.id,
        amount: g.amount,
        currency: g.currency,
        timestamp: g.timestamp,
        status: g.status,
      }))),
      
      ourReport: JSON.stringify(orders.map(o => ({
        id: o.id,
        totalAmount: o.totalAmount,
        payments: o.payments.map(p => ({
          id: p.id,
          gatewayId: p.gatewayId,
          amount: p.amount,
          status: p.status,
        })),
      }))),
      
      alertSent: discrepancies.length > 0,
      createdAt: new Date(),
    },
  });
  
  // Log in an immutable event store
  await db.reconciliationEvent.create({
    data: {
      reconciliationRunId: record.id,
      type: 'RUN_COMPLETED',
      timestamp: new Date(),
      data: {
        discrepancyCount: discrepancies.length,
        status: record.status,
      },
    },
  });
  
  return record;
}

// Query the audit trail
async function getReconciliationHistory(
  startDate: Date,
  endDate: Date
) {
  const runs = await db.reconciliationRun.findMany({
    where: {
      date: { gte: startOfDay(startDate), lte: endOfDay(endDate) },
    },
    include: {
      alert: true,
      events: { orderBy: { timestamp: 'asc' } },
    },
    orderBy: { date: 'desc' },
  });
  
  return runs;
}

Store everything—gateway reports, your own records, discrepancies found, alerts sent, resolutions applied. Later, when finance asks "did we catch that duplicate charge from March 15th?", you have proof. When audit shows up, you have a complete trail. When you're debugging a payment bug, you can trace when it first appeared in reconciliation.

Common mistakes

Running reconciliation too infrequently. Daily is the minimum. Weekly reconciliation means five days of undetected discrepancies. I've seen teams reconcile monthly and discover hundreds of mismatched orders that became impossible to resolve. Daily runs catch problems early when they're fixable.

Ignoring "small" mismatches. A $0.47 rounding error today becomes a pattern of $0.47 errors tomorrow, and suddenly you're $230 short by month's end. Every discrepancy matters. Small mismatches are often early warning signs of a systematic problem.

No escalation path. Finding discrepancies and not alerting anyone means nothing got fixed. Create clear ownership: if reconciliation flags something, who investigates? How quickly? If nobody owns it, it gets ignored. Assign explicitly.

Not testing the job itself. I've had reconciliation jobs silently fail because a timeout happened mid-run, or the gateway API changed format, and nobody noticed because we never tested failure scenarios. Test what happens when the gateway is unavailable, when reports are malformed, when network fails. Your reconciliation job is critical—treat it like production code.

Comparing raw amounts without accounting for refunds. A $100 charge minus a $40 refund should show as $60 settled. If you only compare the charge side, you'll flag a mismatch. Always reconcile the net amount after all adjustments.

Key takeaways

Run reconciliation daily at a fixed time. Make it automated, consistent, and impossible to skip. No manual triggers—let the scheduler own it.
Compare three things: transactions from the gateway, your recorded orders, and the delta between them. This three-way comparison catches missing payments, duplicates, and amount mismatches.
Alert finance immediately on any discrepancy. Include the order ID, exact mismatch amount, and a direct link to investigate. Don't make them hunt for context.
Store every reconciliation run with full details. Your audit trail is proof that you ran it, what you found, and what you did. This matters for compliance, debugging, and trust.
Treat reconciliation failures as critical. If the reconciliation job itself fails, alert ops. A broken reconciliation is worse than no reconciliation because you think you're safe when you're blind.
Own the resolution process. Reconciliation is only useful if someone actually fixes the flagged problems. Define who investigates, who approves the fix, and when it's marked resolved.

Reconciliation jobs catch silent failures for production