Detecting Missing Paystack Transactions with a Sweeper Job

Paystack sends webhooks for every significant event: charge success, refund processed, dispute created. But webhooks are HTTP requests, and HTTP requests can fail. Here are the ways a webhook gets lost:

• Your server was down. A deployment, a crash, or an infrastructure outage took your webhook endpoint offline when Paystack tried to deliver.
• Your endpoint returned an error. A bug in your webhook handler caused a 500 response. Maybe a database connection failed, or a null pointer crashed the handler.
• Paystack gave up retrying. Paystack retries failed webhook deliveries several times over a period of hours. If every retry fails, the webhook is permanently lost.
• Network issues. A DNS resolution failure, a timeout, or a broken route between Paystack and your server.
• Payload too large. Some webhooks with extensive metadata can be large. If your server has a request body size limit, the webhook might be rejected.

None of these are Paystack bugs. They are normal operational failures that happen in any system that relies on HTTP callbacks. The sweeper job is your safety net for all of them.

For the full reconciliation pipeline, see the complete verification and reconciliation guide.

The sweeper does three things: fetch transactions from Paystack, check each against your ledger, and report what is missing.

// sweeper.js - Core sweeper logic
async function sweep(fromDate, toDate) {
  var page = 1;
  var hasMore = true;
  var missing = [];
  var mismatches = [];
  var checked = 0;

  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,
      },
    });

    if (!response.ok) {
      throw new Error('Paystack API returned ' + response.status);
    }

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

    for (var i = 0; i < transactions.length; i++) {
      var txn = transactions[i];
      checked++;

      // Check if this transaction exists in our ledger
      var ledgerResult = await db.query(
        'SELECT id, gross_amount FROM payment_ledger '
        + 'WHERE reference = $1 AND event_type = $2',
        [txn.reference, 'charge.success']
      );

      if (ledgerResult.rows.length === 0) {
        // Missing from our ledger
        missing.push({
          reference: txn.reference,
          amount: txn.amount,
          currency: txn.currency,
          channel: txn.channel,
          paid_at: txn.paid_at,
          customer_email: txn.customer ? txn.customer.email : null,
          paystack_id: txn.id,
        });
      } else if (ledgerResult.rows[0].gross_amount !== txn.amount) {
        // Amount mismatch
        mismatches.push({
          reference: txn.reference,
          ledger_amount: ledgerResult.rows[0].gross_amount,
          paystack_amount: txn.amount,
        });
      }
    }

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

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

  return {
    checked: checked,
    missing: missing,
    mismatches: mismatches,
    from: fromDate,
    to: toDate,
  };
}

The sweeper only checks successful transactions. Failed and abandoned transactions do not need reconciliation because no money moved. This reduces the number of API calls and database lookups significantly.

When the sweeper finds a missing transaction, you have two options: just alert your team, or also record the transaction in your ledger. Recording it is useful because it keeps your ledger complete, but granting value (giving the customer their product) should be a separate, human-reviewed step.

// Record a recovered transaction in the ledger
async function recoverTransaction(reference) {
  // Verify with Paystack first
  var response = await fetch(
    'https://api.paystack.co/transaction/verify/'
      + encodeURIComponent(reference),
    {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
      },
    }
  );

  var data = await response.json();

  if (!data.status || data.data.status !== 'success') {
    return { recovered: false, reason: 'Transaction not successful' };
  }

  var txn = data.data;

  // Record in ledger (idempotent)
  var result = await db.query(
    'INSERT INTO payment_ledger '
    + '(reference, event_type, direction, gross_amount, fee_amount, '
    + 'net_amount, currency, channel, paystack_id, customer_email, metadata) '
    + 'VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) '
    + 'ON CONFLICT (reference, event_type) DO NOTHING '
    + 'RETURNING id',
    [
      txn.reference,
      'charge.success',
      'credit',
      txn.amount,
      txn.fees,
      txn.amount - txn.fees,
      txn.currency,
      txn.channel,
      txn.id,
      txn.customer ? txn.customer.email : null,
      JSON.stringify(txn),
    ]
  );

  if (result.rows.length === 0) {
    // Already existed (race condition with another process)
    return { recovered: false, reason: 'Already in ledger' };
  }

  return { recovered: true, ledger_id: result.rows[0].id };
}

Notice that the recovery function does not grant value. It records the payment in the ledger so your books are complete, but it does not mark any order as paid or give the customer access. That decision requires context that only a human has: is the customer still waiting? Did they already get a refund through support? Did they buy the same product through a different payment?

The sweeper finds a transaction with reference "order_xyz_123". That reference probably matches an order in your orders table. Linking them requires a lookup:

// Try to link a recovered transaction to an existing order
async function linkToOrder(reference, txn) {
  var order = await db.query(
    'SELECT id, status, user_id, expected_amount, expected_currency '
    + 'FROM orders WHERE paystack_reference = $1',
    [reference]
  );

  if (order.rows.length === 0) {
    return {
      linked: false,
      reason: 'No matching order found',
      action: 'Manual investigation required',
    };
  }

  var row = order.rows[0];

  // Check if the order was already fulfilled through another path
  if (row.status === 'paid') {
    return {
      linked: true,
      already_paid: true,
      order_id: row.id,
      action: 'Order already paid. Ledger entry added for completeness.',
    };
  }

  // Verify amount and currency match
  if (txn.amount !== row.expected_amount || txn.currency !== row.expected_currency) {
    return {
      linked: true,
      amount_match: false,
      order_id: row.id,
      action: 'Amount or currency mismatch. Manual review required.',
    };
  }

  // Update the ledger entry with the order_id
  await db.query(
    'UPDATE payment_ledger SET order_id = $1 '
    + 'WHERE reference = $2 AND event_type = $3',
    [row.id, reference, 'charge.success']
  );

  return {
    linked: true,
    order_id: row.id,
    user_id: row.user_id,
    action: 'Linked to order. Ready for manual fulfillment.',
  };
}

Include the linking results in the alert sent to your team. If the order was already paid (maybe through a support ticket that manually marked it), the recovery is just a bookkeeping cleanup. If the order is still pending, someone needs to fulfill it.

// jobs/sweeper.js - Complete sweeper job
async function runSweeper() {
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  var fromDate = yesterday.toISOString().split('T')[0];
  var toDate = new Date().toISOString().split('T')[0];

  console.log('Sweeper starting for ' + fromDate + ' to ' + toDate);
  var startTime = Date.now();

  try {
    var results = await sweep(fromDate, toDate);

    console.log('Sweeper checked ' + results.checked + ' transactions. '
      + 'Missing: ' + results.missing.length
      + ', Mismatches: ' + results.mismatches.length);

    // Recover missing transactions
    var recoveryResults = [];
    for (var i = 0; i < results.missing.length; i++) {
      var missing = results.missing[i];

      try {
        var recovery = await recoverTransaction(missing.reference);
        var linkResult = null;

        if (recovery.recovered) {
          linkResult = await linkToOrder(missing.reference, missing);
        }

        recoveryResults.push({
          reference: missing.reference,
          amount: missing.amount,
          currency: missing.currency,
          customer: missing.customer_email,
          recovery: recovery,
          link: linkResult,
        });
      } catch (err) {
        console.error('Recovery failed for ' + missing.reference + ': ' + err.message);
        recoveryResults.push({
          reference: missing.reference,
          error: err.message,
        });
      }

      // Rate limit recovery calls
      await new Promise(function(resolve) { setTimeout(resolve, 300); });
    }

    // Send alert if issues found
    if (results.missing.length > 0 || results.mismatches.length > 0) {
      var alertMessage = 'Sweeper Report (' + fromDate + '):
'
        + 'Checked: ' + results.checked + '
'
        + 'Missing from ledger: ' + results.missing.length + '
'
        + 'Amount mismatches: ' + results.mismatches.length + '
'
        + 'Duration: ' + (Date.now() - startTime) + 'ms';

      if (recoveryResults.length > 0) {
        alertMessage += '

Recovery Results:';
        for (var j = 0; j < recoveryResults.length; j++) {
          var r = recoveryResults[j];
          alertMessage += '
  ' + r.reference + ': '
            + (r.recovery ? (r.recovery.recovered ? 'Recovered' : r.recovery.reason) : 'Error')
            + (r.link ? ' - ' + r.link.action : '');
        }
      }

      await sendAlert(alertMessage);
    }

  } catch (err) {
    console.error('Sweeper failed: ' + err.message);
    await sendAlert('Sweeper job FAILED: ' + err.message);
  }
}

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

How far back should the sweeper look? There are trade-offs:

• 24 hours (recommended for daily runs): Covers yesterday's transactions. Efficient, minimal API calls. Catches most lost webhooks since Paystack retries happen within hours.
• 48 hours: Catches webhooks that failed yesterday and were not retried in time. Good for businesses that do not run the sweeper on weekends.
• 7 days: Useful as a weekly deep sweep, but generates many API calls for high-volume businesses. Run this less frequently (weekly, not daily).

// Configurable sweep window
function getSweepDates(windowDays) {
  var now = new Date();
  var from = new Date();
  from.setDate(from.getDate() - windowDays);

  return {
    from: from.toISOString().split('T')[0],
    to: now.toISOString().split('T')[0],
  };
}

// Daily sweep: 1-day window
var dailyDates = getSweepDates(1);

// Weekly deep sweep: 7-day window
var weeklyDates = getSweepDates(7);

A good pattern is a daily 24-hour sweep plus a weekly 7-day deep sweep. The daily sweep catches problems quickly. The weekly sweep catches anything the daily sweep missed (for example, if the daily job itself failed one day).

Paystack has rate limits on API calls. A sweeper that makes hundreds of requests per second will get throttled. Handle this gracefully:

// Fetch with rate limiting and retry
async function fetchWithRateLimit(url, maxRetries) {
  var attempts = 0;

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

    if (response.status === 429) {
      // Rate limited. Wait and retry.
      var retryAfter = response.headers.get('Retry-After');
      var waitMs = retryAfter ? parseInt(retryAfter) * 1000 : 5000;
      console.log('Rate limited. Waiting ' + waitMs + 'ms');
      await new Promise(function(resolve) { setTimeout(resolve, waitMs); });
      attempts++;
      continue;
    }

    if (!response.ok) {
      throw new Error('API error: ' + response.status);
    }

    return response.json();
  }

  throw new Error('Max retries exceeded due to rate limiting');
}

For the database lookups, batch them when possible instead of querying one reference at a time:

// Batch lookup for better performance
async function batchCheckLedger(references) {
  if (references.length === 0) return {};

  var placeholders = references.map(function(_, i) {
    return '$' + (i + 1);
  }).join(', ');

  var result = await db.query(
    'SELECT reference, gross_amount FROM payment_ledger '
    + 'WHERE reference IN (' + placeholders + ') '
    + 'AND event_type = 'charge.success'',
    references
  );

  var map = {};
  for (var i = 0; i < result.rows.length; i++) {
    map[result.rows[i].reference] = result.rows[i];
  }
  return map;
}

Processing 100 transactions per page and batch-checking the references reduces the number of database queries from 100 individual queries to 1 batch query per page.

A sweeper that fails silently defeats its purpose. Monitor the sweeper job itself:

• Track run history: Log every sweeper run with its results. If the sweeper has not run in 48 hours, something is wrong with the scheduler.
• Alert on failure: If the sweeper throws an unhandled error, send an alert immediately.
• Track missing transaction trends: If the sweeper consistently finds 5+ missing transactions per day, your webhook endpoint has a reliability problem that needs fixing at the source.

-- Sweeper run history
CREATE TABLE sweeper_runs (
  id              BIGSERIAL PRIMARY KEY,
  from_date       DATE NOT NULL,
  to_date         DATE NOT NULL,
  checked_count   INT NOT NULL DEFAULT 0,
  missing_count   INT NOT NULL DEFAULT 0,
  mismatch_count  INT NOT NULL DEFAULT 0,
  recovered_count INT NOT NULL DEFAULT 0,
  duration_ms     INT NOT NULL DEFAULT 0,
  status          VARCHAR(20) NOT NULL DEFAULT 'completed',
  error_message   TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

If the sweeper finds zero missing transactions for 30 consecutive days, your webhook infrastructure is solid. If it finds missing transactions regularly, investigate the root cause instead of relying on the sweeper as a permanent fix. The sweeper is a safety net, not a replacement for a reliable webhook handler.

For a broader view of payment monitoring, see the admin payments dashboard guide.