Build an Affiliate Commission Engine

Tables: affiliates (name, email, tracking_code, commission_rate, pending_balance, paid_balance, paystack_recipient_code), affiliate_clicks (affiliate_id, visitor_ip, visitor_session, clicked_at, converted), affiliate_conversions (affiliate_id, order_id, paystack_ref, order_amount, commission_amount, status, hold_until).

// When visitor clicks affiliate link: yoursite.com/ref?code=ABC123
async function trackClick(affiliateCode, visitorIp, visitorSession) {
  var affiliate = await db.affiliates.findByCode(affiliateCode);
  if (!affiliate) return;

  await db.affiliateClicks.create({
    affiliate_id: affiliate.id,
    visitor_ip: visitorIp,
    visitor_session: visitorSession,
    clicked_at: new Date(),
  });

  // Store attribution in session/cookie (30-day window)
  return affiliate.id; // save to cookie on the response
}

// When initializing Paystack checkout — read affiliate from session/cookie
async function initializeCheckoutWithAttribution(customerEmail, amount, orderId, affiliateId) {
  var reference = 'ORD-' + Date.now();
  var res = await fetch('https://api.paystack.co/transaction/initialize', {
    method: 'POST',
    headers: { Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({
      email: customerEmail, amount, currency: 'NGN', reference,
      metadata: {
        order_id: orderId,
        affiliate_id: affiliateId || null, // null if no affiliate
      },
    }),
  });
  return (await res.json()).data.authorization_url;
}

// Webhook: credit commission on successful payment
if (event.event === 'charge.success') {
  var meta = event.data.metadata;
  if (!meta.affiliate_id) return res.sendStatus(200);

  var affiliate = await db.affiliates.findById(meta.affiliate_id);
  var commissionAmount = Math.floor(event.data.amount * affiliate.commission_rate);

  var holdUntil = new Date();
  holdUntil.setDate(holdUntil.getDate() + 30); // 30-day hold for refund window

  await db.affiliateConversions.create({
    affiliate_id: affiliate.id,
    order_id: meta.order_id,
    paystack_ref: event.data.reference,
    order_amount: event.data.amount,
    commission_amount: commissionAmount,
    status: 'pending', // becomes 'available' after hold period
    hold_until: holdUntil,
  });

  // Mark the click as converted
  await db.affiliateClicks.markConverted(affiliate.id, meta.order_id);
}

// Cron: release commissions after hold period
async function releaseHeldCommissions() {
  var conversions = await db.affiliateConversions.findReadyToRelease(new Date());
  for (var conversion of conversions) {
    await db.affiliateConversions.update(conversion.id, { status: 'available' });
    await db.affiliates.increment(conversion.affiliate_id, 'pending_balance', conversion.commission_amount);
  }
}

// Affiliate requests withdrawal
async function withdrawCommission(affiliateId, amount) {
  var affiliate = await db.affiliates.findById(affiliateId);
  if (amount > affiliate.pending_balance) throw new Error('Insufficient available balance');
  await db.affiliates.decrement(affiliateId, 'pending_balance', amount);

  await fetch('https://api.paystack.co/transfer', {
    method: 'POST',
    headers: { Authorization: 'Bearer ' + process.env.PAYSTACK_SECRET_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({ source: 'balance', amount, recipient: affiliate.paystack_recipient_code, reason: 'Affiliate commission payout' }),
  });
}

See build a referral payout system for a simpler referral-only version of this commission architecture.

Sign up for the McTaba newsletter