Delayed Settlement Patterns for Marketplaces

CREATE TABLE payout_holds (
  id SERIAL PRIMARY KEY,
  vendor_id INTEGER NOT NULL,
  reference TEXT NOT NULL,
  amount NUMERIC(12, 2) NOT NULL,
  currency TEXT DEFAULT 'NGN',
  hold_reason TEXT,         -- 'delivery_pending' | 'dispute_window' | 'event_date'
  hold_until TIMESTAMPTZ NOT NULL,
  released_at TIMESTAMPTZ,
  status TEXT DEFAULT 'held', -- 'held' | 'released' | 'disputed' | 'refunded'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_holds_release ON payout_holds(status, hold_until);

// On charge.success, create a hold record
if (event.event === 'charge.success') {
  var holdUntil = new Date();
  holdUntil.setDate(holdUntil.getDate() + 7); // 7-day dispute window

  await db.payoutHolds.create({
    vendor_id: order.vendor_id,
    reference: event.data.reference,
    amount: event.data.amount / 100 * 0.9, // Vendor share (90%)
    hold_reason: 'dispute_window',
    hold_until: holdUntil,
    status: 'held',
  });
}

// Daily cron: release matured holds
async function releaseMatureHolds() {
  var now = new Date();
  var holds = await db.payoutHolds.findAll({
    where: { status: 'held', hold_until: { lte: now } }
  });

  for (var hold of holds) {
    var vendor = await db.vendors.findById(hold.vendor_id);
    await initiateTransfer(vendor.recipient_code, hold.amount, hold.currency);
    await db.payoutHolds.update(
      { status: 'released', released_at: now },
      { where: { id: hold.id } }
    );
  }
}

This guide is part of the Paystack split payments guide.

Sign up for the McTaba newsletter