Build an Affiliate Tracking System

// src/affiliate/tracking.ts — Affiliate tracking with attribution, commissions, and fraud detection
import { pool } from "../db";
import { Redis } from "ioredis";
import { createHash } from "node:crypto";

const redis = new Redis(process.env.REDIS_URL!);

interface Affiliate {
  id: string;
  name: string;
  email: string;
  tier: "bronze" | "silver" | "gold" | "platinum";
  commissionRate: number;      // percentage (0-50)
  recurringCommission: boolean;
  recurringMonths: number;     // how many months of recurring
  cookieDays: number;          // attribution window
  referralCode: string;
  customDomain: string | null;
  status: "active" | "pending" | "suspended";
  payoutMethod: "paypal" | "stripe" | "wire";
  payoutEmail: string;
  minimumPayout: number;       // cents
  totalEarned: number;
  totalPaid: number;
  pendingBalance: number;
}

interface Click {
  affiliateId: string;
  ip: string;
  userAgent: string;
  referrer: string;
  landingPage: string;
  subId: string;               // affiliate's sub-tracking ID
  timestamp: number;
}

interface Conversion {
  id: string;
  affiliateId: string;
  customerId: string;
  orderId: string;
  amount: number;
  commission: number;
  type: "first_sale" | "recurring" | "upsell";
  status: "pending" | "approved" | "rejected" | "paid";
  attributionType: "first_click" | "last_click" | "cookie";
  clickId: string;
  createdAt: string;
}

// Track affiliate click
export async function trackClick(
  referralCode: string,
  context: { ip: string; userAgent: string; referrer: string; landingPage: string; subId?: string }
): Promise<{ cookieValue: string; affiliateId: string } | null> {
  // Look up affiliate
  const { rows: [affiliate] } = await pool.query(
    "SELECT id, cookie_days, status FROM affiliates WHERE referral_code = $1 AND status = 'active'",
    [referralCode]
  );
  if (!affiliate) return null;

  // Deduplicate clicks from same IP within 1 hour
  const dedupeKey = `aff:click:${affiliate.id}:${createHash("md5").update(context.ip).digest("hex").slice(0, 8)}`;
  const exists = await redis.get(dedupeKey);
  if (exists) return { cookieValue: exists, affiliateId: affiliate.id };

  const clickId = `clk-${Date.now()}-${Math.random().toString(36).slice(2, 6)}`;

  // Store click
  await pool.query(
    `INSERT INTO affiliate_clicks (id, affiliate_id, ip_hash, user_agent, referrer, landing_page, sub_id, created_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())`,
    [clickId, affiliate.id, createHash("md5").update(context.ip).digest("hex").slice(0, 12),
     context.userAgent.slice(0, 200), context.referrer, context.landingPage, context.subId || null]
  );

  // Increment click counter
  const day = new Date().toISOString().slice(0, 10);
  await redis.hincrby(`aff:clicks:${affiliate.id}`, day, 1);
  await redis.incr(`aff:clicks:total:${affiliate.id}`);

  // Cookie value for attribution
  const cookieValue = `${affiliate.id}:${clickId}:${Date.now()}`;
  await redis.setex(dedupeKey, 3600, cookieValue);

  return { cookieValue, affiliateId: affiliate.id };
}

// Attribute a conversion to an affiliate
export async function recordConversion(
  customerId: string,
  orderId: string,
  amount: number,
  cookieValue: string | null,
  type: Conversion["type"] = "first_sale"
): Promise<Conversion | null> {
  let affiliateId: string | null = null;
  let clickId: string | null = null;
  let attributionType: Conversion["attributionType"] = "cookie";

  // Try cookie attribution
  if (cookieValue) {
    const parts = cookieValue.split(":");
    if (parts.length >= 2) {
      affiliateId = parts[0];
      clickId = parts[1];
    }
  }

  // Fallback: check if customer was referred (stored at signup)
  if (!affiliateId) {
    const { rows: [ref] } = await pool.query(
      "SELECT affiliate_id, click_id FROM customer_referrals WHERE customer_id = $1",
      [customerId]
    );
    if (ref) {
      affiliateId = ref.affiliate_id;
      clickId = ref.click_id;
      attributionType = "first_click";
    }
  }

  if (!affiliateId) return null;

  // Get affiliate commission rate
  const { rows: [affiliate] } = await pool.query(
    "SELECT commission_rate, recurring_commission, recurring_months, status FROM affiliates WHERE id = $1",
    [affiliateId]
  );
  if (!affiliate || affiliate.status !== "active") return null;

  // Skip recurring if not enabled or past limit
  if (type === "recurring" && !affiliate.recurring_commission) return null;
  if (type === "recurring") {
    const { rows: [{ count }] } = await pool.query(
      "SELECT COUNT(*) as count FROM affiliate_conversions WHERE affiliate_id = $1 AND customer_id = $2 AND type = 'recurring'",
      [affiliateId, customerId]
    );
    if (parseInt(count) >= affiliate.recurring_months) return null;
  }

  const commission = Math.round(amount * (affiliate.commission_rate / 100));
  const conversionId = `conv-${Date.now()}-${Math.random().toString(36).slice(2, 6)}`;

  // Fraud check
  const fraudScore = await checkFraud(affiliateId, customerId, amount);
  const status = fraudScore > 70 ? "rejected" : "pending";

  const conversion: Conversion = {
    id: conversionId, affiliateId, customerId, orderId,
    amount, commission, type, status, attributionType,
    clickId: clickId || "", createdAt: new Date().toISOString(),
  };

  await pool.query(
    `INSERT INTO affiliate_conversions (id, affiliate_id, customer_id, order_id, amount, commission, type, status, attribution_type, click_id, created_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW())`,
    [conversionId, affiliateId, customerId, orderId, amount, commission, type, status, attributionType, clickId]
  );

  // Update pending balance
  if (status === "pending") {
    await pool.query("UPDATE affiliates SET pending_balance = pending_balance + $2 WHERE id = $1", [affiliateId, commission]);
  }

  return conversion;
}

// Fraud detection
async function checkFraud(affiliateId: string, customerId: string, amount: number): Promise<number> {
  let score = 0;

  // Self-referral check
  const { rows: [affiliate] } = await pool.query("SELECT email FROM affiliates WHERE id = $1", [affiliateId]);
  const { rows: [customer] } = await pool.query("SELECT email FROM customers WHERE id = $1", [customerId]);
  if (affiliate?.email === customer?.email) score += 90;

  // High conversion rate (>50% is suspicious)
  const clicks = parseInt(await redis.get(`aff:clicks:total:${affiliateId}`) || "0");
  const { rows: [{ count: conversions }] } = await pool.query(
    "SELECT COUNT(*) as count FROM affiliate_conversions WHERE affiliate_id = $1",
    [affiliateId]
  );
  if (clicks > 10 && parseInt(conversions) / clicks > 0.5) score += 30;

  // Multiple conversions from same IP in short time
  const recentConversions = await redis.incr(`aff:conv:recent:${affiliateId}`);
  await redis.expire(`aff:conv:recent:${affiliateId}`, 3600);
  if (recentConversions > 10) score += 40;

  return Math.min(score, 100);
}

// Process payouts
export async function processPayouts(): Promise<Array<{ affiliateId: string; amount: number; status: string }>> {
  // Approve pending conversions older than 30 days (past refund window)
  await pool.query(
    `UPDATE affiliate_conversions SET status = 'approved'
     WHERE status = 'pending' AND created_at < NOW() - INTERVAL '30 days'`
  );

  // Get affiliates with approved balance above minimum
  const { rows: affiliates } = await pool.query(
    `SELECT a.id, a.payout_email, a.payout_method, a.minimum_payout,
            COALESCE(SUM(c.commission), 0) as approved_balance
     FROM affiliates a
     LEFT JOIN affiliate_conversions c ON a.id = c.affiliate_id AND c.status = 'approved'
     GROUP BY a.id
     HAVING COALESCE(SUM(c.commission), 0) >= a.minimum_payout`
  );

  const results = [];
  for (const aff of affiliates) {
    const amount = parseInt(aff.approved_balance);

    // Mark conversions as paid
    await pool.query(
      `UPDATE affiliate_conversions SET status = 'paid' WHERE affiliate_id = $1 AND status = 'approved'`,
      [aff.id]
    );

    await pool.query(
      `UPDATE affiliates SET total_paid = total_paid + $2, pending_balance = 0 WHERE id = $1`,
      [aff.id, amount]
    );

    await pool.query(
      `INSERT INTO affiliate_payouts (affiliate_id, amount, method, status, created_at) VALUES ($1, $2, $3, 'completed', NOW())`,
      [aff.id, amount, aff.payout_method]
    );

    results.push({ affiliateId: aff.id, amount, status: "completed" });
  }

  return results;
}