Reconciling Paystack Settlements Against Your Database

Throughout the day, customers pay you through Paystack. Each payment is a separate transaction. Paystack collects the money, deducts their processing fees, and groups the remaining transactions into a settlement batch.

The settlement is a single deposit to your bank account. If you had 50 successful transactions on Monday totalling NGN 5,000,000 in gross revenue, and Paystack fees were NGN 75,000, you would receive a settlement of approximately NGN 4,925,000. The exact amount depends on the fee structure for each transaction type and any refunds or chargebacks deducted from the batch.

Settlement timing varies by country and business type. Nigerian businesses typically receive next-business-day settlement. Kenyan and Ghanaian settlement windows may differ. Check the Paystack dashboard for your specific settlement schedule.

Your reconciliation job has one job: confirm that the transactions Paystack says it settled match the transactions your ledger says should be settled, and that the amounts agree.

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

Paystack provides two endpoints for settlement data. The first lists your settlements. The second shows the transactions within a specific settlement.

// Fetch settlements for a date range
async function fetchSettlements(fromDate, toDate) {
  var page = 1;
  var allSettlements = [];
  var hasMore = true;

  while (hasMore) {
    var url = 'https://api.paystack.co/settlement?from=' + fromDate
      + '&to=' + toDate + '&perPage=50&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('Settlement fetch failed: ' + data.message);
    }

    allSettlements = allSettlements.concat(data.data);

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

  return allSettlements;
}

// Fetch transactions within a settlement
async function fetchSettlementTransactions(settlementId) {
  var page = 1;
  var allTransactions = [];
  var hasMore = true;

  while (hasMore) {
    var url = 'https://api.paystack.co/settlement/' + settlementId
      + '/transactions?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('Settlement transactions fetch failed: ' + data.message);
    }

    allTransactions = allTransactions.concat(data.data);

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

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

  return allTransactions;
}

Each settlement object includes an ID, a total amount, a currency, a settlement date, and a status. The transaction list for each settlement includes the individual payment references, amounts, and fees.

For each settlement, fetch its transactions and compare each one against your ledger. There are three possible outcomes for each transaction:

// Reconcile a single settlement against the ledger
async function reconcileSettlement(settlementId, settlementDate) {
  var paystackTxns = await fetchSettlementTransactions(settlementId);

  var results = {
    matched: [],
    missing_from_ledger: [],
    amount_mismatch: [],
  };

  for (var i = 0; i < paystackTxns.length; i++) {
    var psTxn = paystackTxns[i];

    // Look up this transaction in our ledger
    var ledgerResult = await db.query(
      'SELECT id, gross_amount, fee_amount, net_amount, settled_at '
      + 'FROM payment_ledger '
      + 'WHERE reference = $1 AND event_type = $2',
      [psTxn.reference, 'charge.success']
    );

    if (ledgerResult.rows.length === 0) {
      // Transaction in Paystack but not in our ledger
      results.missing_from_ledger.push({
        reference: psTxn.reference,
        amount: psTxn.amount,
        fees: psTxn.fees,
        customer_email: psTxn.customer ? psTxn.customer.email : null,
      });
      continue;
    }

    var ledgerEntry = ledgerResult.rows[0];

    // Check amounts match
    if (ledgerEntry.gross_amount !== psTxn.amount) {
      results.amount_mismatch.push({
        reference: psTxn.reference,
        ledger_amount: ledgerEntry.gross_amount,
        paystack_amount: psTxn.amount,
      });
      continue;
    }

    // Match confirmed - update settled_at
    if (!ledgerEntry.settled_at) {
      await db.query(
        'UPDATE payment_ledger SET settled_at = $1, settlement_id = $2 '
        + 'WHERE id = $3',
        [settlementDate, settlementId, ledgerEntry.id]
      );
    }

    results.matched.push({
      reference: psTxn.reference,
      amount: psTxn.amount,
    });
  }

  return results;
}

After processing all Paystack transactions, also check for the reverse: transactions in your ledger that should have been settled but were not included in any settlement.

// Find unsettled transactions that are old enough to be concerning
async function findOrphanedCharges(olderThanDays) {
  var result = await db.query(
    'SELECT reference, gross_amount, net_amount, currency, created_at '
    + 'FROM payment_ledger '
    + 'WHERE event_type = $1 '
    + 'AND settled_at IS NULL '
    + 'AND created_at < NOW() - INTERVAL '' + olderThanDays + ' days' '
    + 'ORDER BY created_at',
    ['charge.success']
  );

  return result.rows;
}

Transaction in Paystack but not in your ledger. This means a payment succeeded on Paystack but your system never recorded it. The most common cause is a lost webhook. Your webhook handler was down, returned an error, or Paystack gave up retrying. Action: verify the transaction using the reference, record it in your ledger, and check whether the customer received their product. If they paid but did not receive value, you have a support issue to resolve.

Amount mismatch. The transaction exists in both systems but the amounts differ. This is rare and usually indicates a bug in your recording logic. Maybe you stored the amount in naira instead of kobo, or you recorded the net amount where you should have recorded the gross. Action: check the JSONB metadata in your ledger entry (which should contain the original Paystack response) and compare it to what you stored in the amount columns.

Transaction in your ledger but not in any settlement. Several explanations are possible:

• The transaction is recent and has not been settled yet. Check whether it falls within the normal settlement window.
• The transaction was refunded and the refund was deducted from the settlement. Check for a corresponding refund entry.
• There was a chargeback. Check the Paystack dashboard for disputes.
• The transaction was reversed by Paystack. Check the transaction status on the API.

// Investigate a missing settlement
async function investigateMissingSettlement(reference) {
  // Check current status with Paystack
  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) {
    return { status: 'api_error', message: data.message };
  }

  var txnStatus = data.data.status;

  if (txnStatus === 'reversed') {
    return { status: 'reversed', action: 'Record reversal in ledger' };
  }

  if (txnStatus === 'success') {
    return { status: 'pending_settlement', action: 'Wait for next settlement cycle' };
  }

  return { status: txnStatus, action: 'Investigate manually' };
}

Beyond matching individual transactions, verify that the settlement total matches the sum of its component transactions:

// Verify settlement total matches individual transactions
async function verifySettlementTotal(settlementId) {
  var settlements = await fetch(
    'https://api.paystack.co/settlement/' + settlementId,
    {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
      },
    }
  );

  var settlementData = await settlements.json();
  var reportedTotal = settlementData.data.total_amount;

  // Fetch all transactions in this settlement
  var transactions = await fetchSettlementTransactions(settlementId);

  // Sum net amounts (amount - fees) for all transactions
  var calculatedTotal = 0;
  for (var i = 0; i < transactions.length; i++) {
    calculatedTotal += (transactions[i].amount - transactions[i].fees);
  }

  // Account for refunds deducted from this settlement
  // (Paystack may deduct refunds from settlement totals)

  if (reportedTotal !== calculatedTotal) {
    console.error('Settlement total mismatch for #' + settlementId
      + ': reported=' + reportedTotal + ' calculated=' + calculatedTotal
      + ' diff=' + (reportedTotal - calculatedTotal));

    return {
      match: false,
      reported: reportedTotal,
      calculated: calculatedTotal,
      difference: reportedTotal - calculatedTotal,
    };
  }

  return { match: true, total: reportedTotal };
}

If the totals do not match, the difference is often caused by refund deductions. Paystack may deduct refunds from a settlement batch rather than processing them as separate transactions. Check whether the difference corresponds to any refunds processed on that day. See the gross vs net recording guide for more on how fees and deductions affect settlement amounts.

Run settlement reconciliation daily as a scheduled job. The job should process the previous day's settlements, flag any issues, and send a summary report to your team.

// Daily settlement reconciliation job
async function dailySettlementReconciliation() {
  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('Starting settlement reconciliation for ' + fromDate);

  var settlements = await fetchSettlements(fromDate, toDate);

  var summary = {
    settlements_processed: settlements.length,
    total_matched: 0,
    total_missing_from_ledger: 0,
    total_amount_mismatch: 0,
    details: [],
  };

  for (var i = 0; i < settlements.length; i++) {
    var settlement = settlements[i];
    var results = await reconcileSettlement(
      settlement.id,
      settlement.settled_date || settlement.created_at
    );

    summary.total_matched += results.matched.length;
    summary.total_missing_from_ledger += results.missing_from_ledger.length;
    summary.total_amount_mismatch += results.amount_mismatch.length;

    if (results.missing_from_ledger.length > 0 || results.amount_mismatch.length > 0) {
      summary.details.push({
        settlement_id: settlement.id,
        missing: results.missing_from_ledger,
        mismatches: results.amount_mismatch,
      });
    }

    // Rate limiting between settlements
    await new Promise(function(resolve) { setTimeout(resolve, 500); });
  }

  // Also check for orphaned charges
  var orphans = await findOrphanedCharges(5);
  summary.orphaned_charges = orphans.length;

  // Report
  if (summary.total_missing_from_ledger > 0
      || summary.total_amount_mismatch > 0
      || orphans.length > 0) {
    await sendAlert('Settlement reconciliation issues found', summary);
  }

  console.log('Reconciliation complete: ' + JSON.stringify(summary));
  return summary;
}

Schedule this job to run after the settlement window has passed. If Paystack settles the previous day's transactions by 9 AM, run the job at 10 AM to give some buffer. For the complete job architecture, see the daily reconciliation job guide.

When you issue a refund through Paystack, the refund amount is typically deducted from a future settlement. This means a settlement might be smaller than the sum of its charge transactions because Paystack subtracted a refund for a completely different transaction.

Your reconciliation must account for this. When the settlement total does not match the sum of charges, check for refund deductions:

// Check if settlement difference is explained by refunds
async function checkRefundDeductions(settlementId, difference) {
  // Fetch refunds processed around the settlement date
  var settlement = await fetch(
    'https://api.paystack.co/settlement/' + settlementId,
    {
      headers: {
        Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY,
      },
    }
  );

  var settlementData = await settlement.json();
  var settledDate = settlementData.data.settled_date;

  // Check our ledger for refunds around that date
  var refunds = await db.query(
    'SELECT reference, gross_amount FROM payment_ledger '
    + 'WHERE event_type = $1 '
    + 'AND created_at >= $2::date - INTERVAL '2 days' '
    + 'AND created_at <= $2::date + INTERVAL '1 day'',
    ['refund.processed', settledDate]
  );

  var totalRefunds = 0;
  for (var i = 0; i < refunds.rows.length; i++) {
    totalRefunds += refunds.rows[i].gross_amount;
  }

  if (Math.abs(difference) === totalRefunds) {
    return { explained: true, refund_total: totalRefunds };
  }

  return { explained: false, refund_total: totalRefunds, remaining_diff: difference + totalRefunds };
}

If the difference is exactly equal to the refund total, the mismatch is explained. If not, there is still an unexplained difference that needs manual investigation.

Keep a record of every reconciliation run. This creates an audit trail that shows when reconciliation was performed, what was found, and how it was resolved.

-- Reconciliation runs table
CREATE TABLE reconciliation_runs (
  id                    BIGSERIAL PRIMARY KEY,
  run_date              DATE NOT NULL,
  settlement_date       DATE NOT NULL,
  settlements_checked   INT NOT NULL DEFAULT 0,
  matched_count         INT NOT NULL DEFAULT 0,
  missing_count         INT NOT NULL DEFAULT 0,
  mismatch_count        INT NOT NULL DEFAULT 0,
  orphan_count          INT NOT NULL DEFAULT 0,
  status                VARCHAR(20) NOT NULL DEFAULT 'completed',
  details               JSONB DEFAULT '{}',
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Reconciliation issues table
CREATE TABLE reconciliation_issues (
  id              BIGSERIAL PRIMARY KEY,
  run_id          BIGINT REFERENCES reconciliation_runs(id),
  issue_type      VARCHAR(30) NOT NULL,
  -- 'missing_from_ledger', 'amount_mismatch', 'orphaned_charge'
  reference       VARCHAR(100) NOT NULL,
  details         JSONB DEFAULT '{}',
  resolved        BOOLEAN NOT NULL DEFAULT FALSE,
  resolved_at     TIMESTAMPTZ,
  resolved_by     VARCHAR(100),
  resolution_note TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

When your team investigates and resolves an issue, they update the reconciliation_issues row with the resolution. Over time, this history shows you which types of issues recur and whether your integration is getting more reliable or less.