Reconciling Payouts Against Recipient Confirmations

There are two distinct events in a Paystack transfer:

1. transfer.success webhook — Paystack processed the transfer and it left your balance.
2. Bank credit — The receiving bank processes the deposit. This can take minutes to hours depending on the bank and transfer type.

Paystack sends one transfer.success when its processing is done. You do not receive a separate event when the recipient's bank credits the account. If the bank rejects it, Paystack fires transfer.reversed and refunds your balance.

Your reconciliation must account for both the initial success and any subsequent reversal.

-- Store every transfer event in this table
CREATE TABLE transfers (
  id SERIAL PRIMARY KEY,
  transfer_code TEXT UNIQUE NOT NULL,
  reference TEXT,
  recipient_code TEXT NOT NULL,
  recipient_name TEXT,
  bank_name TEXT,
  account_number TEXT,
  amount NUMERIC(12, 2) NOT NULL,
  currency TEXT NOT NULL DEFAULT 'NGN',
  status TEXT NOT NULL DEFAULT 'pending',
  -- 'pending' | 'success' | 'reversed' | 'failed'
  initiated_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  reversed_at TIMESTAMPTZ,
  paystack_created_at TIMESTAMPTZ,
  notes TEXT,
  raw_webhook JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

// Webhook handler for transfer events
if (event.event === 'transfer.success') {
  var t = event.data;
  await db.transfers.upsert({
    transfer_code: t.transfer_code,
    reference: t.reference,
    recipient_code: t.recipient.recipient_code,
    recipient_name: t.recipient.details?.account_name,
    bank_name: t.recipient.details?.bank_name,
    account_number: t.recipient.details?.account_number,
    amount: t.amount / 100,
    currency: t.currency,
    status: 'success',
    completed_at: t.updated_at,
    raw_webhook: t,
  }, { conflictField: 'transfer_code' });
}

if (event.event === 'transfer.reversed') {
  await db.transfers.update(
    { status: 'reversed', reversed_at: event.data.updated_at },
    { where: { transfer_code: event.data.transfer_code } }
  );
  // Alert: money returned to balance — needs manual review or re-payout
  console.error('Transfer reversed:', event.data.transfer_code, event.data.failure_reason);
}

if (event.event === 'transfer.failed') {
  await db.transfers.update(
    { status: 'failed' },
    { where: { transfer_code: event.data.transfer_code } }
  );
}

// reconcile-transfers.js — run daily
async function reconcileTransfers() {
  // 1. Fetch all transfers from Paystack for the last 7 days
  var response = await fetch(
    'https://api.paystack.co/transfer?perPage=100&page=1',
    { headers: { Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY } }
  );
  var { data } = await response.json();

  var mismatches = [];

  for (var pt of data) {
    var localTransfer = await db.transfers.findOne({
      where: { transfer_code: pt.transfer_code }
    });

    if (!localTransfer) {
      mismatches.push({ type: 'missing_local', transfer_code: pt.transfer_code });
      continue;
    }

    // Status mismatch
    if (localTransfer.status !== pt.status) {
      mismatches.push({
        type: 'status_mismatch',
        transfer_code: pt.transfer_code,
        local_status: localTransfer.status,
        paystack_status: pt.status
      });
      // Update local to match Paystack
      await db.transfers.update(
        { status: pt.status },
        { where: { transfer_code: pt.transfer_code } }
      );
    }

    // Amount mismatch
    var paystackAmount = pt.amount / 100;
    if (Math.abs(localTransfer.amount - paystackAmount) > 0.01) {
      mismatches.push({
        type: 'amount_mismatch',
        transfer_code: pt.transfer_code,
        local: localTransfer.amount,
        paystack: paystackAmount
      });
    }
  }

  if (mismatches.length > 0) {
    console.error('Transfer reconciliation mismatches:', mismatches);
    // Send alert to Slack/email
  }

  return { total: data.length, mismatches: mismatches.length };
}

This guide is part of the Paystack transfers and payouts series.

Sign up for the McTaba newsletter