How to Track Stripe Coupons for Affiliates: A Complete Technical Guide

Link-based affiliate tracking is straightforward: capture a ?ref= param, set a cookie, read it at checkout. The mechanics are well-documented and the failure modes are predictable.

Coupon-based affiliate tracking is a different problem entirely. There are no referral URLs. No cookies. No client-side JavaScript involved in the attribution. A user finds a promo code in a YouTube description, navigates directly to your pricing page, types it in at checkout, and completes the purchase. The entire attribution chain lives inside Stripe — and the only way to capture it is server-side, via webhooks, after the payment is already confirmed.

If you wire this up wrong, you lose conversions silently. No error. No log. Just a commission that never gets recorded.

This guide walks through the full implementation: schema design, Stripe object metadata, webhook parsing, and the edge cases that will burn you in production if you skip them.

---

Why Coupon Tracking Is Architecturally Different from Link Tracking

With link-based tracking, the attribution happens before the payment:

1. User clicks affiliate link → cookie set → checkout → webhook confirms
   With coupon-based tracking, the attribution happens during the payment, and you only learn about it after:

2. User navigates directly → types promo code at checkout → payment completes → webhook fires → you parse the coupon from the event
   This distinction matters because:

• There's no pre-checkout state to inspect. You can't read a cookie or a session variable. The coupon the user typed is an opaque string until Stripe tells you about it.
• The webhook is your only source of truth. If your webhook handler is wrong, buggy, or down during the event, the attribution is gone.
• The mapping from coupon code to affiliate must be server-side. You cannot do this lookup in the browser.

---

The Database & Metadata Architecture

You need two things to make coupon tracking work cleanly:

1. A local mapping table — links your affiliate IDs to their Stripe promotion code IDs
2. Metadata on the Stripe Coupon/Promotion Code object — so the affiliate ID travels with the Stripe object and is readable in webhook events without an extra DB round-trip ### Local Mapping Table

-- Affiliates table (you likely already have this)
CREATE TABLE affiliates (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  email           TEXT UNIQUE NOT NULL,
  commission_rate NUMERIC(5,4) NOT NULL DEFAULT 0.20,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- One row per promotion code assigned to an affiliate
-- An affiliate can have multiple codes (e.g., different campaigns)
CREATE TABLE affiliate_coupon_codes (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  affiliate_id          UUID NOT NULL REFERENCES affiliates(id) ON DELETE CASCADE,
  stripe_coupon_id      TEXT NOT NULL,           -- e.g., "SUMMER20"
  stripe_promotion_code_id TEXT,                 -- e.g., "promo_1ABC..."
  code                  TEXT NOT NULL,           -- Human-readable code, e.g., "ZAK20"
  discount_type         TEXT NOT NULL,           -- 'percentage' | 'fixed'
  discount_value        NUMERIC(10,2) NOT NULL,  -- e.g., 20.00 (%) or 10.00 ($)
  max_redemptions       INTEGER,                 -- NULL = unlimited
  times_redeemed        INTEGER NOT NULL DEFAULT 0,
  active                BOOLEAN NOT NULL DEFAULT true,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Conversion events attributed via coupon
CREATE TABLE affiliate_coupon_conversions (
  id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  affiliate_id             UUID NOT NULL REFERENCES affiliates(id),
  coupon_code_id           UUID NOT NULL REFERENCES affiliate_coupon_codes(id),
  stripe_session_id        TEXT UNIQUE NOT NULL,
  stripe_customer_id       TEXT NOT NULL,
  stripe_subscription_id   TEXT,                -- NULL for one-time payments
  amount_cents             INTEGER NOT NULL,
  discount_applied_cents   INTEGER NOT NULL DEFAULT 0,
  commission_cents         INTEGER NOT NULL,
  status                   TEXT NOT NULL DEFAULT 'pending',  -- pending | approved | paid | reversed
  converted_at             TIMESTAMPTZ NOT NULL,
  paid_at                  TIMESTAMPTZ
);

CREATE INDEX idx_coupon_codes_affiliate ON affiliate_coupon_codes(affiliate_id);
CREATE INDEX idx_coupon_codes_stripe_coupon ON affiliate_coupon_codes(stripe_coupon_id);
CREATE INDEX idx_coupon_codes_promo_id ON affiliate_coupon_codes(stripe_promotion_code_id);
CREATE INDEX idx_coupon_conversions_affiliate ON affiliate_coupon_conversions(affiliate_id);
CREATE INDEX idx_coupon_conversions_status ON affiliate_coupon_conversions(status);

Creating the Stripe Coupon with Affiliate Metadata

When you create a coupon for an affiliate, embed the affiliate ID directly into the Stripe object's metadata field. This is critical — it means your webhook handler can attribute the sale without a DB lookup on the hot path.

// lib/stripe/create-affiliate-coupon.ts
import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
  apiVersion: '2024-11-20',
});

interface CreateAffiliateCouponParams {
  affiliateId: string;
  affiliateSlug: string;
  code: string;                          // e.g., "ZAK20"
  percentOff?: number;                   // e.g., 20 for 20% off
  amountOff?: number;                    // in cents, e.g., 1000 for $10 off
  currency?: string;                     // required if amountOff is set
  maxRedemptions?: number;               // omit for unlimited
  durationInMonths?: number;             // for 'repeating' duration
}

export async function createAffiliateCoupon(
  params: CreateAffiliateCouponParams,
): Promise<{ couponId: string; promotionCodeId: string }> {
  const {
    affiliateId,
    affiliateSlug,
    code,
    percentOff,
    amountOff,
    currency = 'usd',
    maxRedemptions,
    durationInMonths,
  } = params;

  if (!percentOff && !amountOff) {
    throw new Error('Either percentOff or amountOff must be provided');
  }

  // Step 1: Create the underlying Stripe Coupon
  const coupon = await stripe.coupons.create({
    ...(percentOff ? { percent_off: percentOff } : {}),
    ...(amountOff ? { amount_off: amountOff, currency } : {}),
    duration: durationInMonths ? 'repeating' : 'once',
    ...(durationInMonths ? { duration_in_months: durationInMonths } : {}),
    metadata: {
      affiliate_id: affiliateId,
      affiliate_slug: affiliateSlug,
      source: 'refearnapp',
    },
  });

  // Step 2: Create a Promotion Code on top of the Coupon
  // Promotion Codes are what users actually type at checkout
  const promotionCode = await stripe.promotionCodes.create({
    coupon: coupon.id,
    code,                                // The human-readable code, e.g., "ZAK20"
    ...(maxRedemptions ? { max_redemptions: maxRedemptions } : {}),
    metadata: {
      affiliate_id: affiliateId,
      affiliate_slug: affiliateSlug,
      source: 'refearnapp',
    },
  });

  return {
    couponId: coupon.id,
    promotionCodeId: promotionCode.id,
  };
}

Why metadata on both the Coupon and the Promotion Code?

Stripe's data model has a subtle distinction: a Coupon is the discount rule (20% off), while a Promotion Code is the code users type (ZAK20). One Coupon can have multiple Promotion Codes. In the webhook, you'll receive the Promotion Code ID — but depending on how you query the event, you may only have the Coupon ID. Putting the affiliate metadata on both ensures you can recover the attribution either way.

---

The Webhook Implementation

This is the critical path. Every production edge case lives here.

// app/api/webhooks/stripe/route.ts
import { NextRequest, NextResponse } from 'next/server';
import Stripe from 'stripe';
import { db } from '@/lib/db';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
  apiVersion: '2024-11-20',
});

export async function POST(request: NextRequest): Promise<NextResponse> {
  const body = await request.text();
  const signature = request.headers.get('stripe-signature');

  if (!signature) {
    return NextResponse.json({ error: 'Missing stripe-signature header' }, { status: 400 });
  }

  let event: Stripe.Event;

  try {
    event = stripe.webhooks.constructEvent(
      body,
      signature,
      process.env.STRIPE_WEBHOOK_SECRET!,
    );
  } catch (err) {
    console.error('Webhook signature verification failed:', err);
    return NextResponse.json({ error: 'Invalid signature' }, { status: 400 });
  }

  try {
    switch (event.type) {
      case 'checkout.session.completed':
        await handleCheckoutCompleted(event.data.object as Stripe.Checkout.Session);
        break;

      case 'invoice.payment_succeeded':
        await handleInvoicePaymentSucceeded(event.data.object as Stripe.Invoice);
        break;

      default:
        // Acknowledge unhandled events — never return 4xx for unknown types
        break;
    }
  } catch (err) {
    // Return 500 so Stripe retries the webhook
    console.error(`Error processing webhook ${event.type}:`, err);
    return NextResponse.json({ error: 'Internal processing error' }, { status: 500 });
  }

  return NextResponse.json({ received: true });
}

async function handleCheckoutCompleted(
  session: Stripe.Checkout.Session,
): Promise<void> {
  // Guard: only process paid sessions
  if (session.payment_status !== 'paid') return;

  // Guard: idempotency — don't double-record the same session
  const existing = await db.affiliateCouponConversion.findUnique({
    where: { stripeSessionId: session.id },
  });
  if (existing) return;

  // Extract the applied promotion code from the session
  const affiliateAttribution = await resolveAffiliateFromSession(session);
  if (!affiliateAttribution) return; // No coupon applied, or not an affiliate code

  const { affiliateId, couponCodeRecord, discountAppliedCents } = affiliateAttribution;

  // Fetch the affiliate to get their commission rate
  const affiliate = await db.affiliate.findUnique({
    where: { id: affiliateId },
  });
  if (!affiliate) return;

  const commissionCents = Math.floor(
    (session.amount_total ?? 0) * Number(affiliate.commissionRate),
  );

  await db.affiliateCouponConversion.create({
    data: {
      affiliateId,
      couponCodeId: couponCodeRecord.id,
      stripeSessionId: session.id,
      stripeCustomerId: session.customer as string,
      stripeSubscriptionId: session.subscription as string | null,
      amountCents: session.amount_total ?? 0,
      discountAppliedCents,
      commissionCents,
      status: 'pending',
      convertedAt: new Date(),
    },
  });

  // Increment the redemption counter on the local record
  await db.affiliateCouponCode.update({
    where: { id: couponCodeRecord.id },
    data: { timesRedeemed: { increment: 1 } },
  });
}

async function handleInvoicePaymentSucceeded(
  invoice: Stripe.Invoice,
): Promise<void> {
  // Only handle recurring subscription invoices, not the initial one
  // (the initial invoice is already captured via checkout.session.completed)
  if (invoice.billing_reason !== 'subscription_cycle') return;
  if (!invoice.subscription) return;

  // Look up the original conversion to find the affiliate
  const originalConversion = await db.affiliateCouponConversion.findFirst({
    where: { stripeSubscriptionId: invoice.subscription as string },
    orderBy: { convertedAt: 'asc' },
    include: { affiliate: true, couponCode: true },
  });

  if (!originalConversion) return;

  // Check if the coupon still applies to this invoice
  // Stripe stops applying coupons after their duration expires
  const discountApplied = (invoice.discount?.coupon?.id)
    ? await getCouponDiscountAmount(invoice)
    : 0;

  const commissionCents = Math.floor(
    invoice.amount_paid * Number(originalConversion.affiliate.commissionRate),
  );

  await db.affiliateCouponConversion.create({
    data: {
      affiliateId: originalConversion.affiliateId,
      couponCodeId: originalConversion.couponCodeId,
      stripeSessionId: `invoice_${invoice.id}`, // Synthetic ID for recurring events
      stripeCustomerId: invoice.customer as string,
      stripeSubscriptionId: invoice.subscription as string,
      amountCents: invoice.amount_paid,
      discountAppliedCents: discountApplied,
      commissionCents,
      status: 'pending',
      convertedAt: new Date(),
    },
  });
}

/**
 * Resolves affiliate attribution from a completed checkout session.
 * Checks both total_details (for promotion codes applied at checkout)
 * and session metadata (for coupons applied programmatically).
 */
async function resolveAffiliateFromSession(session: Stripe.Checkout.Session): Promise<{
  affiliateId: string;
  couponCodeRecord: { id: string };
  discountAppliedCents: number;
} | null> {
  // Path 1: Promotion code applied by user at checkout
  // Stripe exposes this in session.total_details.breakdown.discounts
  const expandedSession = await stripe.checkout.sessions.retrieve(session.id, {
    expand: ['total_details.breakdown.discounts'],
  });

  const discounts = expandedSession.total_details?.breakdown?.discounts ?? [];

  for (const discount of discounts) {
    const promoCodeId = (discount.discount as Stripe.Discount).promotion_code;
    if (!promoCodeId) continue;

    // Look up the promotion code in our local DB
    const couponCodeRecord = await db.affiliateCouponCode.findUnique({
      where: {
        stripePromotionCodeId: typeof promoCodeId === 'string'
          ? promoCodeId
          : promoCodeId.id,
      },
    });

    if (couponCodeRecord) {
      return {
        affiliateId: couponCodeRecord.affiliateId,
        couponCodeRecord: { id: couponCodeRecord.id },
        discountAppliedCents: discount.amount ?? 0,
      };
    }
  }

  // Path 2: Coupon applied programmatically (not via promotion code)
  // Fall back to checking the coupon metadata directly
  if (session.total_details?.breakdown?.discounts) {
    for (const discount of session.total_details.breakdown.discounts) {
      const couponId = (discount.discount as Stripe.Discount).coupon?.id;
      if (!couponId) continue;

      const couponCodeRecord = await db.affiliateCouponCode.findFirst({
        where: { stripeCouponId: couponId },
      });

      if (couponCodeRecord) {
        return {
          affiliateId: couponCodeRecord.affiliateId,
          couponCodeRecord: { id: couponCodeRecord.id },
          discountAppliedCents: discount.amount ?? 0,
        };
      }
    }
  }

  return null;
}

async function getCouponDiscountAmount(invoice: Stripe.Invoice): Promise<number> {
  return Math.abs(
    invoice.lines.data.reduce((total, line) => {
      const discountAmounts = line.discount_amounts ?? [];
      return total + discountAmounts.reduce((sum, d) => sum + d.amount, 0);
    }, 0),
  );
}

---

Edge Cases to Handle in Production

1. Idempotency: Stripe Retries Webhooks

Stripe will retry a webhook up to 3 days if it doesn't receive a 2xx response. This means your handler will be called multiple times for the same event during outages or deploys. The idempotency guard at the top of handleCheckoutCompleted (checking for an existing record by stripeSessionId) prevents double-recording. Never skip this.

// Always check before writing
const existing = await db.affiliateCouponConversion.findUnique({
  where: { stripeSessionId: session.id },
});
if (existing) return; // Idempotent: already processed

2. Multi-Use vs. Single-Use Promotion Codes

Multi-use codes (shared publicly): one code used by many customers. Commission goes to the affiliate who owns the code. Standard case, handled by the implementation above.

Single-use codes (generated per-customer): one code per user, often used for personalized discounts or referral bonuses. If you generate these at scale, make sure your affiliate_coupon_codes table can handle the volume and your lookup index on stripe_promotion_code_id stays performant.

For single-use codes, enforce max_redemptions: 1 when creating the Promotion Code in Stripe:

const promotionCode = await stripe.promotionCodes.create({
  coupon: coupon.id,
  code: `${affiliateSlug.toUpperCase()}-${crypto.randomUUID().slice(0, 8).toUpperCase()}`,
  max_redemptions: 1,   // Stripe enforces this server-side
  metadata: { affiliate_id: affiliateId, affiliate_slug: affiliateSlug },
});

3. Subscription Renewals vs. One-Time Payments

The checkout.session.completed event fires once per initial purchase. For subscriptions, subsequent renewals fire invoice.payment_succeeded with billing_reason: 'subscription_cycle'.

Key distinction: the first invoice for a subscription fires both checkout.session.completed AND invoice.payment_succeeded (with billing_reason: 'subscription_create'). If you handle both naively, you'll double-record the initial conversion. The guard billing_reason !== 'subscription_cycle' in handleInvoicePaymentSucceeded prevents this — only recurring renewals trigger that handler.

4. Coupon Duration vs. Commission Duration

A Stripe coupon with duration: 'once' only applies the discount to the first invoice. Your affiliate still deserves commissions on subsequent renewals even after the discount expires — unless your program explicitly limits commission windows. Handle this by looking up the original conversion via stripeSubscriptionId on every renewal, regardless of whether the coupon is still active on that invoice.

5. Refunds and Reversals

When a customer is refunded, listen to the charge.refunded event and mark the corresponding conversion as reversed:

case 'charge.refunded': {
  const charge = event.data.object as Stripe.Charge;
  if (charge.payment_intent) {
    // Find the session associated with this payment intent
    const sessions = await stripe.checkout.sessions.list({
      payment_intent: charge.payment_intent as string,
    });
    if (sessions.data[0]) {
      await db.affiliateCouponConversion.updateMany({
        where: { stripeSessionId: sessions.data[0].id },
        data: { status: 'reversed' },
      });
    }
  }
  break;
}

Never pay out commissions with status: 'pending' or status: 'reversed'. Only pay status: 'approved'.

---

The Build-vs-Deploy Calculation for Coupon Tracking

Component	DIY Effort
Stripe coupon + promotion code creation	~2 hours
Local DB schema + migrations	~1 hour
Webhook handler with idempotency	~3 hours
Recurring commission attribution	~2 hours
Refund/reversal handling	~1 hour
Affiliate-facing coupon dashboard	40–80 hours
Admin UI to create and assign codes	20–40 hours
Commission reporting per code	20–40 hours

The webhook logic above is a weekend of work. The surrounding infrastructure — the UI to create and assign coupon codes to affiliates, the dashboard where affiliates see their code performance, the commission reporting broken down by code — is where real time goes.

---

Where RefearnApp Handles This Out of the Box

Building and maintaining this webhook pipeline is the kind of infrastructure work that looks simple until you're debugging a missed attribution at 2am because Stripe changed how it surfaces promotion code IDs in a new API version.

RefearnApp is an open-source, self-hosted affiliate tracking platform that ships coupon code tracking as a built-in feature alongside standard link-based referral tracking.

What's already built:

• Coupon code tracking — The Stripe webhook handler, promotion code resolution, and affiliate attribution pipeline described in this post are implemented and maintained in the RefearnApp codebase. You configure it, not build it.
• Affiliate portal — Affiliates log into their own dashboard and see performance broken down by their assigned coupon code: redemptions, revenue attributed, commission earned.
• Admin coupon management — Create and assign promotion codes to affiliates from the admin UI. No Stripe dashboard hunting required.
• Payout tracking — Approve or decline commissions at the affiliate level. Mark payouts as paid. Full commission history per affiliate.
• Zero revenue cut — Every dollar of commission goes to your affiliates. RefearnApp takes nothing.
• Your database, your schema — Data lives in your own Postgres instance. Direct SQL access. No CSV exports, no vendor lock-in. If you're running both link-based and coupon-based affiliate programs simultaneously (common for SaaS — links for content creators, coupons for podcast sponsors), RefearnApp tracks both attribution paths in a unified dashboard.

For more on how referral link tracking works alongside coupons, see our guides on building an affiliate program in Next.js and self-hosted affiliate tracking software for SaaS.

---

Summary

Coupon-based affiliate tracking is a pure server-side problem. The key points:

• No cookies, no URL params. Attribution lives entirely in the Stripe webhook event.
• Metadata on both Coupon and Promotion Code objects ensures you can recover the affiliate ID regardless of which Stripe object surfaces in the event.
• Idempotency is non-negotiable. Stripe retries webhooks. Your handler will be called multiple times.
• Subscription renewals need separate handling. checkout.session.completed handles the initial conversion; invoice.payment_succeeded with billing_reason: 'subscription_cycle' handles recurring commissions.

- Refunds must reverse commissions. Never pay out on a conversion that's been refunded.

Conclusion & Next Steps

Take Control of Your Affiliate Data

RefearnApp is fully open-source, self-hostable, and AGPL-3.0 licensed. You can deploy it completely free on your own infrastructure using Coolify or check out our direct paths:

• Developer Choice: Read Deployment Docs or ⭐ Star the Repo to run it on your own VPS via Coolify with 100% data ownership.
• Production Ready: Try Managed Cloud to get up and running instantly with a fully turnkey, zero-config deployment.