Split Payment Reconciliation and Subaccount Reporting

-- Vendor earnings statement for a specific month
SELECT
  v.id as vendor_id,
  v.business_name,
  v.subaccount_code,
  COUNT(o.id) as order_count,
  SUM(o.total_amount) as gross_sales,
  SUM(o.platform_commission) as platform_commission,
  SUM(o.total_amount - o.platform_commission) as net_earnings,
  SUM(o.paystack_fee_absorbed) as fees_absorbed,
  SUM(o.total_amount - o.platform_commission - o.paystack_fee_absorbed) as settled_amount
FROM vendors v
JOIN orders o ON o.vendor_id = v.id
WHERE o.status = 'paid'
  AND o.paid_at BETWEEN '2026-07-01' AND '2026-07-31'
GROUP BY v.id, v.business_name, v.subaccount_code
ORDER BY net_earnings DESC;

// Pull Paystack transactions per subaccount and compare
async function reconcileSubaccount(subaccountCode) {
  var response = await fetch(
    'https://api.paystack.co/transaction?subaccount=' + subaccountCode + '&status=success&perPage=100',
    { headers: { Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY } }
  );
  var { data } = await response.json();

  var discrepancies = [];

  for (var tx of data) {
    var localOrder = await db.orders.findOne({
      where: { reference: tx.reference, subaccount_code: subaccountCode }
    });

    if (!localOrder) {
      discrepancies.push({ type: 'missing_local', reference: tx.reference });
      continue;
    }

    var expectedVendorShare = localOrder.total_amount - localOrder.platform_commission;
    var actualVendorShare = tx.subaccount?.amount / 100;

    if (Math.abs(expectedVendorShare - actualVendorShare) > 1) {
      discrepancies.push({
        type: 'amount_mismatch',
        reference: tx.reference,
        expected: expectedVendorShare,
        actual: actualVendorShare,
      });
    }
  }

  return discrepancies;
}

This guide is part of the Paystack split payments guide.

Sign up for the McTaba newsletter