Building a Daily Paystack Reconciliation Job

The daily reconciliation job answers one question: does your database agree with Paystack about what happened yesterday? It runs automatically, compares records, and alerts you when they diverge.

The job performs four checks:

1. Transaction comparison: Every successful transaction in Paystack should have a corresponding entry in your ledger. Every charge entry in your ledger should exist in Paystack.
2. Amount verification: For matched transactions, the amounts should agree. If your ledger says NGN 50,000 but Paystack says NGN 45,000, something is wrong.
3. Settlement check: Transactions from two or more days ago should have been settled. Flag any that have not been.
4. Summary statistics: Total transaction count, total volume, total fees. These numbers should be consistent between your system and Paystack.

The job should complete silently when everything matches. It should only send alerts when issues are found. Nobody wants a daily "everything is fine" email. They want silence when things are fine and a loud alert when they are not.

For the broader reconciliation pipeline that this job fits into, see the complete verification and reconciliation guide.

Here is the full job, from start to finish:

// jobs/reconcile.js
var db = require('../db');

async function fetchPaystackTransactions(fromDate, toDate) {
  var page = 1;
  var allTransactions = [];
  var hasMore = true;

  while (hasMore) {
    var url = 'https://api.paystack.co/transaction?from=' + fromDate
      + '&to=' + toDate + '&status=success&perPage=100&page=' + page;

    var response = await fetch(url, {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
      },
    });

    var data = await response.json();

    if (!data.status) {
      throw new Error('Paystack API error: ' + data.message);
    }

    allTransactions = allTransactions.concat(data.data);

    var meta = data.meta;
    hasMore = page * meta.perPage < meta.total;
    page++;

    // Respect rate limits
    await new Promise(function(resolve) { setTimeout(resolve, 200); });
  }

  return allTransactions;
}

async function reconcile(targetDate) {
  var fromDate = targetDate;
  var toDate = targetDate;

  console.log('Reconciliation starting for ' + targetDate);
  var startTime = Date.now();

  // 1. Fetch all successful transactions from Paystack
  var paystackTxns = await fetchPaystackTransactions(fromDate, toDate);

  // 2. Build a map of Paystack transactions by reference
  var paystackMap = {};
  for (var i = 0; i < paystackTxns.length; i++) {
    paystackMap[paystackTxns[i].reference] = paystackTxns[i];
  }

  // 3. Fetch our ledger entries for the same period
  var ledgerResult = await db.query(
    'SELECT reference, gross_amount, fee_amount, currency, created_at '
    + 'FROM payment_ledger '
    + 'WHERE event_type = $1 '
    + 'AND created_at >= $2::date '
    + 'AND created_at < $2::date + INTERVAL '1 day'',
    ['charge.success', targetDate]
  );

  var ledgerMap = {};
  for (var j = 0; j < ledgerResult.rows.length; j++) {
    var row = ledgerResult.rows[j];
    ledgerMap[row.reference] = row;
  }

  // 4. Compare
  var results = {
    date: targetDate,
    paystack_count: paystackTxns.length,
    ledger_count: ledgerResult.rows.length,
    matched: 0,
    missing_from_ledger: [],
    missing_from_paystack: [],
    amount_mismatches: [],
  };

  // Check every Paystack transaction against our ledger
  var paystackRefs = Object.keys(paystackMap);
  for (var k = 0; k < paystackRefs.length; k++) {
    var ref = paystackRefs[k];
    var psTxn = paystackMap[ref];
    var ledgerEntry = ledgerMap[ref];

    if (!ledgerEntry) {
      results.missing_from_ledger.push({
        reference: ref,
        amount: psTxn.amount,
        currency: psTxn.currency,
        customer: psTxn.customer ? psTxn.customer.email : 'unknown',
        paid_at: psTxn.paid_at,
      });
    } else if (ledgerEntry.gross_amount !== psTxn.amount) {
      results.amount_mismatches.push({
        reference: ref,
        paystack_amount: psTxn.amount,
        ledger_amount: ledgerEntry.gross_amount,
      });
    } else {
      results.matched++;
    }

    // Remove from ledger map so we can find orphans
    delete ledgerMap[ref];
  }

  // Remaining entries in ledgerMap exist in our ledger but not in Paystack
  var orphanRefs = Object.keys(ledgerMap);
  for (var m = 0; m < orphanRefs.length; m++) {
    var orphanRef = orphanRefs[m];
    results.missing_from_paystack.push({
      reference: orphanRef,
      amount: ledgerMap[orphanRef].gross_amount,
      currency: ledgerMap[orphanRef].currency,
    });
  }

  results.duration_ms = Date.now() - startTime;
  return results;
}

Save every reconciliation run to a database table. This creates an audit trail and lets you track reconciliation health over time.

// Store reconciliation results
async function storeReconciliationResults(results) {
  var hasIssues = results.missing_from_ledger.length > 0
    || results.missing_from_paystack.length > 0
    || results.amount_mismatches.length > 0;

  await db.query(
    'INSERT INTO reconciliation_runs '
    + '(run_date, settlement_date, settlements_checked, matched_count, '
    + 'missing_count, mismatch_count, orphan_count, status, details) '
    + 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)',
    [
      new Date().toISOString().split('T')[0],
      results.date,
      results.paystack_count,
      results.matched,
      results.missing_from_ledger.length,
      results.amount_mismatches.length,
      results.missing_from_paystack.length,
      hasIssues ? 'issues_found' : 'clean',
      JSON.stringify(results),
    ]
  );

  return hasIssues;
}

Over time, you can query this table to see trends. If Monday reconciliations always have issues, maybe your webhook endpoint has problems on weekends. If issues cluster around specific payment channels, that channel might need attention.

-- Reconciliation health over the last 30 days
SELECT
  settlement_date,
  status,
  matched_count,
  missing_count,
  mismatch_count,
  orphan_count
FROM reconciliation_runs
WHERE run_date >= NOW() - INTERVAL '30 days'
ORDER BY settlement_date DESC;

When the reconciliation job finds issues, alert your team immediately. Here is a simple alerting setup that works with Slack webhooks and email:

// Send reconciliation alerts
async function sendReconciliationAlert(results) {
  var issues = [];

  if (results.missing_from_ledger.length > 0) {
    issues.push(results.missing_from_ledger.length
      + ' transactions in Paystack but NOT in our ledger');
  }

  if (results.missing_from_paystack.length > 0) {
    issues.push(results.missing_from_paystack.length
      + ' transactions in our ledger but NOT in Paystack');
  }

  if (results.amount_mismatches.length > 0) {
    issues.push(results.amount_mismatches.length + ' amount mismatches');
  }

  var message = 'Reconciliation for ' + results.date + ':
'
    + 'Checked: ' + results.paystack_count + ' Paystack transactions
'
    + 'Matched: ' + results.matched + '
'
    + 'Issues: ' + issues.join(', ') + '
'
    + 'Duration: ' + results.duration_ms + 'ms';

  // Slack webhook
  if (process.env.SLACK_WEBHOOK_URL) {
    await fetch(process.env.SLACK_WEBHOOK_URL, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ text: message }),
    });
  }

  // Email via your email service
  if (process.env.ALERT_EMAIL) {
    await sendEmail({
      to: process.env.ALERT_EMAIL,
      subject: 'Payment Reconciliation Issues - ' + results.date,
      body: message + '

Details:
' + JSON.stringify(results, null, 2),
    });
  }

  console.log('Alert sent: ' + message);
}

Do not send alerts when everything matches. Reserve alerts for actual issues. If your team gets a daily "all clear" email, they will start ignoring the emails, and when a real issue arrives, nobody will notice.

The job needs to run at a consistent time every day, after the previous day's settlements have been processed. Common scheduling options:

System cron:

# Run at 6:00 AM daily
0 6 * * * cd /app && node jobs/reconcile.js >> /var/log/reconcile.log 2>&1

Node.js with node-cron:

// scheduler.js
var cron = require('node-cron');

cron.schedule('0 6 * * *', async function() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var targetDate = yesterday.toISOString().split('T')[0];

  try {
    var results = await reconcile(targetDate);
    var hasIssues = await storeReconciliationResults(results);

    if (hasIssues) {
      await sendReconciliationAlert(results);
    }

    console.log('Reconciliation complete for ' + targetDate
      + ': ' + (hasIssues ? 'ISSUES FOUND' : 'clean'));
  } catch (err) {
    console.error('Reconciliation failed: ' + err.message);
    await sendReconciliationAlert({
      date: targetDate,
      error: err.message,
      matched: 0,
      missing_from_ledger: [],
      missing_from_paystack: [],
      amount_mismatches: [],
    });
  }
});

Managed services: Render Cron Jobs, Railway scheduled tasks, or Vercel Cron all support scheduled functions. These are easier to maintain than self-hosted cron because they handle retries, logging, and uptime monitoring.

Whichever method you choose, make sure the job has its own error handling and alerting. A reconciliation job that fails silently is worse than no reconciliation at all, because you think you are covered when you are not.

The reconciliation job should be safe to run multiple times for the same date. This matters because cron jobs can be triggered manually for investigation, or you might need to re-run after fixing a bug.

// Check if reconciliation already ran for this date
async function shouldRun(targetDate) {
  var existing = await db.query(
    'SELECT id, status FROM reconciliation_runs WHERE settlement_date = $1',
    [targetDate]
  );

  if (existing.rows.length > 0) {
    var lastRun = existing.rows[0];
    console.log('Reconciliation already ran for ' + targetDate
      + ' with status: ' + lastRun.status);

    // Allow re-run if previous run found issues (for re-checking after fixes)
    if (lastRun.status === 'clean') {
      return false;
    }
  }

  return true;
}

// Main entry point
async function main() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var targetDate = yesterday.toISOString().split('T')[0];

  // Allow override via command line
  if (process.argv[2]) {
    targetDate = process.argv[2];
  }

  if (!(await shouldRun(targetDate))) {
    console.log('Skipping reconciliation for ' + targetDate + ' (already clean)');
    return;
  }

  var results = await reconcile(targetDate);
  var hasIssues = await storeReconciliationResults(results);

  if (hasIssues) {
    await sendReconciliationAlert(results);
  }
}

main().catch(function(err) {
  console.error('Fatal error: ' + err.message);
  process.exit(1);
});

The command-line override (process.argv[2]) lets you run the job for any specific date. This is useful for backfilling reconciliation after the job was down, or for investigating a specific day's transactions.

If your business processes thousands of transactions per day, the reconciliation job needs to handle pagination, rate limits, and memory carefully.

// Stream-based reconciliation for high volume
async function reconcileHighVolume(targetDate) {
  var page = 1;
  var hasMore = true;
  var results = {
    date: targetDate,
    paystack_count: 0,
    matched: 0,
    missing_from_ledger: [],
    amount_mismatches: [],
  };

  while (hasMore) {
    var url = 'https://api.paystack.co/transaction?from=' + targetDate
      + '&to=' + targetDate + '&status=success&perPage=100&page=' + page;

    var response = await fetch(url, {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
      },
    });

    var data = await response.json();
    var transactions = data.data;

    // Process this page immediately instead of loading all into memory
    for (var i = 0; i < transactions.length; i++) {
      var txn = transactions[i];
      results.paystack_count++;

      var ledgerEntry = await db.query(
        'SELECT gross_amount FROM payment_ledger '
        + 'WHERE reference = $1 AND event_type = $2',
        [txn.reference, 'charge.success']
      );

      if (ledgerEntry.rows.length === 0) {
        results.missing_from_ledger.push({
          reference: txn.reference,
          amount: txn.amount,
        });
      } else if (ledgerEntry.rows[0].gross_amount !== txn.amount) {
        results.amount_mismatches.push({
          reference: txn.reference,
          paystack_amount: txn.amount,
          ledger_amount: ledgerEntry.rows[0].gross_amount,
        });
      } else {
        results.matched++;
      }
    }

    var meta = data.meta;
    hasMore = page * meta.perPage < meta.total;
    page++;

    // Rate limiting: 200ms between pages
    await new Promise(function(resolve) { setTimeout(resolve, 200); });

    // Log progress
    if (page % 10 === 0) {
      console.log('Processed ' + results.paystack_count + ' transactions so far');
    }
  }

  return results;
}

This version processes transactions page by page instead of loading all of them into memory at once. For a business with 10,000 daily transactions, loading all at once would use significant memory. The page-by-page approach keeps memory usage constant.

The reconciliation job finds issues. Now what?

Missing from ledger (most common): A webhook was lost. Verify the transaction with the Paystack API. If it is genuine, record it in your ledger and check whether the customer received their product. Your sweeper job should catch these too, but the reconciliation job provides a second safety net.

Missing from Paystack (rare): Your ledger has a transaction that Paystack does not know about. This usually means a bug in your recording logic that created a phantom ledger entry. Verify by calling the Paystack API with the reference. If Paystack says the transaction does not exist, remove the phantom entry from your ledger (with an audit note explaining why).

Amount mismatch (very rare): The transaction exists in both systems but with different amounts. This is almost always a recording bug. Check the JSONB metadata in your ledger entry against the amount columns. The metadata should contain the original Paystack response, which is the source of truth.

Build a simple dashboard or admin page where your team can view reconciliation results and mark issues as resolved. This keeps the process organized and prevents issues from falling through the cracks.