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

PRIMARY KEY DEFAULT gen_random_uuid(), partner_id UUID NOT NULL REFERENCES partner_accounts(id), mapping_id UUID NOT NULL REFERENCES discount_mappings(id), stripe_session_ref TEXT UNIQUE NOT NULL, stripe_customer_ref TEXT NOT NULL, stripe_subscription_ref TEXT, gross_amount_cents INTEGER NOT NULL, discount_amount_cents INTEGER NOT NULL DEFAULT 0, commission_amount_cents INTEGER NOT NULL, ledger_status TEXT NOT NULL DEFAULT 'pending', recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), settled_at TIMESTAMPTZ );

CREATE INDEX idx_discount_mappings_partner ON discount_mappings(partner_id); CREATE INDEX idx_discount_mappings_stripe_coupon ON discount_mappings(stripe_coupon_ref); CREATE INDEX idx_revenue_ledger_partner ON revenue_ledger(partner_id); CREATE INDEX idx_revenue_ledger_status ON revenue_ledger(ledger_status);

### Step 2: Stripe Object Creation with Embedded Metadata

Stripe’s data model separates discount rules (`Coupon`) from user-facing strings (`PromotionCode`). A single coupon can power multiple promotion codes. To ensure attribution survives regardless of which object Stripe surfaces in the webhook, you must embed the partner identifier in both objects.

```ts
import Stripe from 'stripe';

const stripeClient = new Stripe(process.env.STRIPE_API_KEY!, {
  apiVersion: '2024-10-28.acacia',
  typescript: true,
});

interface ProvisionDiscountParams {
  partnerId: string;
  partnerHandle: string;
  publicCode: string;
  percentOff?: number;
  fixedOffCents?: number;
  currency?: string;
  maxRedemptions?: number;
  recurringMonths?: number;
}

export async function provisionPartnerDiscount(
  params: ProvisionDiscountParams
): Promise<{ couponRef: string; promoRef: string }> {
  const {
    partnerId,
    partnerHandle,
    publicCode,
    percentOff,
    fixedOffCents,
    currency = 'usd',
    maxRedemptions,
    recurringMonths,
  } = params;

  if (!percentOff && !fixedOffCents) {
    throw new Error('Discount configuration requires either percentOff or fixedOffCents');
  }

  const sharedMeta = {
    partner_ref: partnerId,
    partner_handle: partnerHandle,
    tracking_source: 'affiliate_pipeline',
  };

  const discountRule = await stripeClient.coupons.create({
    ...(percentOff ? { percent_off: percentOff } : {}),
    ...(fixedOffCents ? { amount_off: fixedOffCents, currency } : {}),
    duration: recurringMonths ? 'repeating' : 'once',
    ...(recurringMonths ? { duration_in_months: recurringMonths } : {}),
    metadata: sharedMeta,
  });

  const promoEntry = await stripeClient.promotionCodes.create({
    coupon: discountRule.id,
    code: publicCode,
    ...(maxRedemptions ? { max_redemptions: maxRedemptions } : {}),
    metadata: sharedMeta,
  });

  return {
    couponRef: discountRule.id,
    promoRef: promoEntry.id,
  };
}

Architecture Rationale: Placing metadata on both objects eliminates dependency on Stripe’s internal payload structure. Webhook events may surface the coupon ID in some contexts and the promotion_code ID in others. Dual embedding guarantees attribution recovery without hot-path database lookups.

Step 3: Webhook Event Routing & Parsing

The webhook handler must validate signatures, enforce idempotency, extract the discount object, calculate commissions deterministically, and write to the ledger.

import { IncomingHttpHeaders } from 'http';
import Stripe from 'stripe';
import { db } from './database';

const stripe = new Stripe(process.env.STRIPE_API_KEY!, {
  apiVersion: '2024-10-28.acacia',
});

type WebhookHandler = (payload: Stripe.Event) => Promise<void>;

export async function processStripeWebhook(
  rawBody: string,
  headers: IncomingHttpHeaders
): Promise<{ status: number; body: string }> {
  const sigHeader = headers['stripe-signature'];
  if (!sigHeader || typeof sigHeader !== 'string') {
    return { status: 400, body: 'Missing signature header' };
  }

  let event: Stripe.Event;
  try {
    event = stripe.webhooks.constructEvent(
      rawBody,
      sigHeader,
      process.env.STRIPE_WEBHOOK_SECRET!
    );
  } catch (validationError) {
    console.error('Webhook signature mismatch:', validationError);
    return { status: 400, body: 'Invalid signature' };
  }

  const handlers: Record<string, WebhookHandler> = {
    'checkout.session.completed': handleCheckoutSession,
    'invoice.payment_succeeded': handleInvoicePayment,
  };

  const handler = handlers[event.type];
  if (!handler) {
    return { status: 200, body: 'Event acknowledged' };
  }

  try {
    await handler(event);
    return { status: 200, body: 'Processed' };
  } catch (processingError) {
    console.error(`Handler failed for ${event.type}:`, processingError);
    return { status: 500, body: 'Processing error' };
  }
}

async function handleCheckoutSession(event: Stripe.Event): Promise<void> {
  const session = event.data.object as Stripe.Checkout.Session;
  if (session.payment_status !== 'paid') return;

  const existing = await db.revenueLedger.findUnique({
    where: { stripeSessionRef: session.id },
  });
  if (existing) return;

  const discountData = session.discounts?.[0];
  if (!discountData) return;

  const attribution = await resolveAttribution(discountData);
  if (!attribution) return;

  const { partnerId, mappingId, discountCents } = attribution;
  const partner = await db.partnerAccounts.findUnique({
    where: { id: partnerId },
  });
  if (!partner) return;

  const commissionCents = Math.floor(
    (session.amount_total ?? 0) * Number(partner.baseCommission)
  );

  await db.revenueLedger.create({
    data: {
      partnerId,
      mappingId,
      stripeSessionRef: session.id,
      stripeCustomerRef: session.customer as string,
      stripeSubscriptionRef: session.subscription as string | null,
      grossAmountCents: session.amount_total ?? 0,
      discountAmountCents: discountCents,
      commissionAmountCents: commissionCents,
      ledgerStatus: 'pending',
      recordedAt: new Date(),
    },
  });
}

async function handleInvoicePayment(event: Stripe.Event): Promise<void> {
  const invoice = event.data.object as Stripe.Invoice;
  if (invoice.status !== 'paid') return;

  const existing = await db.revenueLedger.findUnique({
    where: { stripeSessionRef: invoice.id },
  });
  if (existing) return;

  const discountData = invoice.discount?.coupon;
  if (!discountData) return;

  const attribution = await resolveAttribution({ coupon: discountData });
  if (!attribution) return;

  const { partnerId, mappingId, discountCents } = attribution;
  const partner = await db.partnerAccounts.findUnique({
    where: { id: partnerId },
  });
  if (!partner) return;

  const commissionCents = Math.floor(
    (invoice.amount_paid ?? 0) * Number(partner.baseCommission)
  );

  await db.revenueLedger.create({
    data: {
      partnerId,
      mappingId,
      stripeSessionRef: invoice.id,
      stripeCustomerRef: invoice.customer as string,
      stripeSubscriptionRef: invoice.subscription as string | null,
      grossAmountCents: invoice.amount_paid ?? 0,
      discountAmountCents: discountCents,
      commissionAmountCents: commissionCents,
      ledgerStatus: 'pending',
      recordedAt: new Date(),
    },
  });
}

async function resolveAttribution(
  discountPayload: Partial<Stripe.Discount>
): Promise<{ partnerId: string; mappingId: string; discountCents: number } | null> {
  const couponRef = discountPayload.coupon?.id;
  const promoRef = discountPayload.promotion_code?.id;

  if (!couponRef && !promoRef) return null;

  const mapping = await db.discountMappings.findFirst({
    where: {
      OR: [
        { stripeCouponRef: couponRef! },
        { stripePromoRef: promoRef! },
      ],
    },
  });

  if (!mapping) return null;

  const discountCents = discountPayload.coupon?.amount_off ?? 0;
  return {
    partnerId: mapping.partnerId,
    mappingId: mapping.id,
    discountCents,
  };
}

Architecture Rationale:

• Dual event handling covers both one-time Checkout Sessions and recurring Invoice payments.
• Idempotency is enforced via unique session/invoice references before any ledger write.
• Attribution resolution queries the mapping table using either coupon or promo references, matching the dual-metadata strategy.
• Commission calculation uses the post-discount gross amount, which aligns with standard affiliate compensation models.

Pitfall Guide

1. Ignoring invoice.payment_succeeded Events

Explanation: Checkout Sessions only fire for initial purchases. Subscription renewals, prorations, and manual invoice payments trigger invoice.payment_succeeded. Skipping this event misses recurring commissions. Fix: Register both event types in your webhook endpoint and normalize the payload structure before attribution resolution.

2. Missing Idempotency Guards

Explanation: Stripe retries failed webhooks up to 3 times with exponential backoff. Without a unique constraint on stripeSessionRef or explicit existence checks, you will double-record commissions. Fix: Always query the ledger for the session/invoice ID before inserting. Use database unique constraints as a secondary safeguard.

3. Calculating Commission on Pre-Discount Amounts

Explanation: Some teams calculate commissions on amount_total before subtracting the discount. This overpays partners and breaks financial reconciliation. Fix: Use the net amount after discount application. Stripe’s discounts array contains the exact discount amount; subtract it from the gross before applying the commission rate.

4. Assuming Metadata Exists Only on Promotion Codes

Explanation: Stripe’s webhook payloads sometimes surface the Coupon ID instead of the PromotionCode ID, depending on the event type and API version. Fix: Embed partner metadata on both objects during creation. Query the mapping table using OR conditions to recover attribution regardless of which reference Stripe provides.

5. Webhook Signature Verification Bypass in Development

Explanation: Disabling signature checks locally to speed up iteration creates security debt. When promoted to production, the bypass remains, exposing the endpoint to spoofed events. Fix: Use environment flags to toggle strict verification. Never commit code that skips stripe.webhooks.constructEvent.

6. Race Conditions Between Session Completion and Invoice Generation

Explanation: For subscriptions, checkout.session.completed fires before the first invoice is generated. If your system expects an invoice ID immediately, attribution fails. Fix: Store stripeSubscriptionRef from the session and rely on invoice.payment_succeeded for recurring commission recording. Treat the session event as a one-time attribution trigger.

7. Hardcoding Commission Rates in Webhook Handlers

Explanation: Embedding commission percentages in the webhook logic breaks when partners negotiate custom rates or when rates change dynamically. Fix: Fetch the partner record on the hot path. Cache partner profiles in memory or Redis if latency becomes a concern, but always validate against the source of truth before calculation.

Production Bundle

Action Checklist

• Register both checkout.session.completed and invoice.payment_succeeded in Stripe Dashboard
• Embed partner metadata on both Coupon and PromotionCode objects during creation
• Implement unique constraints on stripeSessionRef in the ledger table
• Add explicit idempotency checks before every ledger insert
• Validate webhook signatures using stripe.webhooks.constructEvent
• Extract discount amounts from the discounts array before commission calculation
• Route unhandled event types to a dead-letter queue for audit
• Monitor webhook retry rates and set up alerts for 5xx responses

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
One-time product sales	checkout.session.completed only	Simpler payload, immediate attribution	Low
Subscription renewals	invoice.payment_succeeded only	Captures recurring billing cycles	Medium
Mixed billing models	Dual event handling + normalized ledger	Covers all payment paths without duplication	Medium
High-volume partner program	Webhook queue + async ledger writes	Prevents request timeouts and retry storms	High (infrastructure)
Custom partner rates	Dynamic partner fetch on hot path	Ensures accurate commission calculation	Low

Configuration Template

// webhook-config.ts
import { WebhookEndpointConfig } from './types';

export const stripeWebhookConfig: WebhookEndpointConfig = {
  endpointPath: '/api/webhooks/stripe',
  allowedEvents: [
    'checkout.session.completed',
    'invoice.payment_succeeded',
    'invoice.payment_failed',
    'customer.subscription.deleted',
  ],
  signatureHeader: 'stripe-signature',
  retryPolicy: {
    maxAttempts: 3,
    backoffMultiplier: 2,
    initialDelayMs: 1000,
  },
  idempotency: {
    primaryKey: 'stripeSessionRef',
    table: 'revenue_ledger',
  },
  logging: {
    level: 'info',
    includePayload: false,
    redactFields: ['customer_email', 'payment_method_details'],
  },
};

Quick Start Guide

1. Provision the discount objects: Call the provisionPartnerDiscount function with partner metadata. Store the returned couponRef and promoRef in your mapping table.
2. Configure the webhook endpoint: Register your server URL in the Stripe Dashboard. Select checkout.session.completed and invoice.payment_succeeded. Copy the signing secret to your environment.
3. Deploy the handler: Mount the webhook processor at your configured path. Ensure the server can read raw request bodies without middleware interference (e.g., disable JSON parsing for this route).
4. Test with Stripe CLI: Run stripe listen --forward-to localhost:3000/api/webhooks/stripe and trigger test events using stripe trigger checkout.session.completed.
5. Verify ledger entries: Check your database for revenue_ledger records. Confirm commissionAmountCents matches expected calculations and ledgerStatus transitions correctly.