# How to Build an Affiliate Tracking App with Lovable

- Tool: How to Build with Lovable
- Difficulty: Intermediate
- Compatibility: Lovable Pro or higher
- Last updated: April 2026

## TL;DR

Build an affiliate tracking system in Lovable where referral links append a ?ref= parameter, clicks are deduplicated by IP hash to prevent inflation, a 30-day cookie window attributes conversions, and an Edge Function calculates commissions atomically — with a real-time affiliate dashboard showing clicks, conversions, and earned commissions.

## Before you start

- Lovable Pro account for Edge Function generation
- Supabase project with SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY in Cloud tab → Secrets
- A working checkout or conversion event in your app that you can trigger the commission calculation from
- Decision on commission rate structure (flat fee vs percentage, per product vs site-wide)
- Optional: Stripe account for handling payouts via Stripe Connect

## Step-by-step guide

### 1. Create the affiliate tracking schema

Ask Lovable to set up all the tables for affiliates, clicks, conversions, and commissions. The schema is designed to answer every performance question with simple indexed queries.

```
Create an affiliate tracking schema in Supabase.

Tables:
- affiliates: id, user_id (references auth.users, unique), code (text unique, generated 8-char alphanumeric), name, email, website_url, commission_rate (decimal, e.g. 0.10 for 10%), status ('pending' | 'active' | 'suspended'), payout_method (text), payout_details (jsonb, encrypted), created_at
- affiliate_clicks: id, affiliate_id (references affiliates), ip_hash (text), user_agent_hash (text), landing_url (text), referrer_url (text), visitor_id (text, uuid stored in visitor's localStorage), created_at
- affiliate_conversions: id, affiliate_id (references affiliates), click_id (references affiliate_clicks, nullable), order_id (text unique), order_amount (decimal), commission_amount (decimal), status ('pending' | 'approved' | 'paid'), payout_id (text), created_at
- affiliate_payouts: id, affiliate_id (references affiliates), total_amount (decimal), commission_ids (uuid array), status ('processing' | 'completed' | 'failed'), payout_reference (text), processed_at, created_at

RLS:
- affiliates: users can SELECT and UPDATE their own row
- affiliate_clicks and affiliate_conversions: affiliates SELECT their own rows (affiliate_id = sub-select of their affiliate row)
- affiliate_payouts: affiliates SELECT their own
- All INSERT via service role (Edge Functions only)

Add indexes: affiliate_clicks(affiliate_id, created_at DESC), affiliate_clicks(ip_hash, affiliate_id, created_at), affiliate_conversions(affiliate_id, status), affiliate_conversions(order_id).
```

> Pro tip: Hash the visitor's IP and User-Agent server-side in the Edge Function, never client-side. Receiving the hash from the client would allow fraud — the attacker could just change the hash. The Edge Function reads the real IP from the CF-Connecting-IP header and hashes it before storing.

**Expected result:** All tables are created with correct RLS. Indexes are in place. The affiliates table has a unique code column. TypeScript types are generated.

### 2. Build the click tracking Edge Function

Every visit via a ?ref= link calls this Edge Function. It deduplicates by IP hash and stores the click for later attribution. This must be fast — it runs on page load.

```
// supabase/functions/track-click/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Content-Type': 'application/json',
}

async function hashString(input: string): Promise<string> {
  const data = new TextEncoder().encode(input)
  const hash = await crypto.subtle.digest('SHA-256', data)
  return Array.from(new Uint8Array(hash)).map(b => b.toString(16).padStart(2, '0')).join('').slice(0, 16)
}

serve(async (req: Request) => {
  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })

  try {
    const supabase = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
    )

    const { ref_code, landing_url, referrer_url, visitor_id } = await req.json()

    if (!ref_code) return new Response(JSON.stringify({ tracked: false }), { headers: corsHeaders })

    const { data: affiliate } = await supabase
      .from('affiliates')
      .select('id')
      .eq('code', ref_code)
      .eq('status', 'active')
      .single()

    if (!affiliate) return new Response(JSON.stringify({ tracked: false }), { headers: corsHeaders })

    const ip = req.headers.get('CF-Connecting-IP') ?? req.headers.get('X-Forwarded-For') ?? 'unknown'
    const ua = req.headers.get('User-Agent') ?? ''
    const ip_hash = await hashString(ip + affiliate.id)
    const user_agent_hash = await hashString(ua)

    // Dedup: check for existing click from same IP for this affiliate in last 24h
    const since = new Date(Date.now() - 86_400_000).toISOString()
    const { count } = await supabase
      .from('affiliate_clicks')
      .select('*', { count: 'exact', head: true })
      .eq('affiliate_id', affiliate.id)
      .eq('ip_hash', ip_hash)
      .gte('created_at', since)

    if ((count ?? 0) > 0) {
      return new Response(JSON.stringify({ tracked: false, reason: 'duplicate' }), { headers: corsHeaders })
    }

    const { data: click } = await supabase
      .from('affiliate_clicks')
      .insert({ affiliate_id: affiliate.id, ip_hash, user_agent_hash, landing_url, referrer_url, visitor_id })
      .select('id')
      .single()

    return new Response(JSON.stringify({ tracked: true, click_id: click?.id }), { headers: corsHeaders })
  } catch (err) {
    return new Response(JSON.stringify({ error: 'Internal error' }), { status: 500, headers: corsHeaders })
  }
})
```

> Pro tip: Include the affiliate_id in the IP hash (not just the raw IP). This means the same visitor can legitimately click links from two different affiliates within 24 hours — both clicks count, because the hashes differ.

**Expected result:** Calling the Edge Function with a valid ref_code creates a click record. Calling again from the same IP within 24 hours returns { tracked: false, reason: 'duplicate' }. The click record includes the correct affiliate_id.

### 3. Build the conversion and commission Edge Function

This fires when a purchase is completed. It looks up the attribution from the last 30 days, calculates commission, and creates a pending commission record.

```
Create a Supabase Edge Function at supabase/functions/track-conversion/index.ts.

The function accepts POST with body: { order_id: string, order_amount: number, visitor_id: string }.

Logic:
1. Check if a conversion for this order_id already exists in affiliate_conversions. If so, return { duplicate: true } to prevent double commission on retries.
2. Look up the most recent affiliate_clicks row WHERE visitor_id = p_visitor_id AND created_at > now() - interval '30 days', ordered by created_at DESC, LIMIT 1.
3. If no click found, return { attributed: false } — organic sale, no commission.
4. Fetch the affiliate's commission_rate from the affiliates table.
5. Calculate commission_amount = order_amount * commission_rate, rounded to 2 decimal places.
6. INSERT into affiliate_conversions: affiliate_id, click_id, order_id, order_amount, commission_amount, status='pending'.
7. Return { attributed: true, affiliate_id, commission_amount }.

This function is called from your existing checkout completion webhook or server action. It uses the visitor_id that the client stores in localStorage and sends at checkout.
```

**Expected result:** Completing a purchase with a visitor_id that has a click within 30 days creates a pending commission record. Duplicate order_ids are rejected. Purchases with no click history return attributed: false.

### 4. Build the affiliate dashboard

Affiliates need a clear view of their performance: their referral link, click stats, conversion rate, and earnings. Ask Lovable to build the dashboard page.

```
Build an affiliate dashboard at src/pages/AffiliateDashboard.tsx.

Requirements:
- Header Card: affiliate's unique referral link displayed with a Copy button. Show affiliate status Badge.
- Four stat Cards: Total Clicks (30 days), Conversions (30 days), Conversion Rate (conversions/clicks as percentage), Pending Earnings (sum of commission_amount WHERE status='pending')
- Recharts LineChart below stats: two lines, clicks and conversions per day for the last 30 days, with dual Y-axes. X-axis = date, formatted as 'Jan 15'.
- Conversions DataTable: columns Date, Order Amount (currency), Commission (currency), Status Badge (pending=yellow, approved=green, paid=blue). Paginated, 20 rows.
- A shareable link section: show the referral URL and allow the affiliate to add a custom UTM campaign parameter to it.

All data fetches are scoped to the currently authenticated affiliate's rows via RLS. Use Promise.all for parallel fetches of stats.
```

**Expected result:** The dashboard shows real-time affiliate performance data. The referral link copy button works. The chart shows 30 days of data. The conversions table shows all commissions with correct status badges.

### 5. Build the admin payout management page

Admins need to review pending commissions, approve them, and process payouts. Ask Lovable to build the admin area.

```
Build an admin page at src/pages/AffiliateAdmin.tsx. Protect with role='admin' check.

Two Tabs: 'Pending Approvals' and 'Affiliates'.

Pending Approvals tab:
- DataTable of all affiliate_conversions WHERE status='pending'
- Columns: Affiliate Name, Order ID, Order Amount, Commission Amount, Date, Approve Button, Reject Button
- Bulk select checkboxes. 'Approve Selected' Button updates status='approved' for all checked rows.
- Filter by affiliate Select and date range.

Affiliates tab:
- DataTable of all affiliates with columns: Name, Email, Code, Commission Rate, Status Badge, Total Earned (sum approved+paid commissions), Pending Balance (sum pending commissions), Actions
- 'Process Payout' Button per affiliate: opens a Dialog showing all approved commissions as line items, total payout amount, and a payout reference Input. Submitting inserts into affiliate_payouts and updates all included commission_ids status to 'paid'.
- 'Edit Commission Rate' inline — clicking the rate opens an editable Input that saves on blur.
- Add Affiliate Button opens a Sheet with registration form.
```

**Expected result:** Admins can approve pending commissions individually or in bulk. The payout dialog shows correct totals. Processing a payout updates all selected commission statuses to paid and creates an affiliate_payouts record.

## Complete code example

File: `src/hooks/useAffiliateTracking.ts`

```typescript
import { useEffect } from 'react'
import { supabase } from '@/integrations/supabase/client'

const VISITOR_ID_KEY = 'aff_visitor_id'
const REF_CODE_KEY = 'aff_ref_code'
const REF_EXPIRY_KEY = 'aff_ref_expiry'
const THIRTY_DAYS_MS = 30 * 24 * 60 * 60 * 1000

function getOrCreateVisitorId(): string {
  let id = localStorage.getItem(VISITOR_ID_KEY)
  if (!id) {
    id = crypto.randomUUID()
    localStorage.setItem(VISITOR_ID_KEY, id)
  }
  return id
}

export function useAffiliateTracking() {
  useEffect(() => {
    const params = new URLSearchParams(window.location.search)
    const refCode = params.get('ref')

    if (refCode) {
      // Store attribution with 30-day expiry
      localStorage.setItem(REF_CODE_KEY, refCode)
      localStorage.setItem(REF_EXPIRY_KEY, String(Date.now() + THIRTY_DAYS_MS))
    }

    const storedRef = localStorage.getItem(REF_CODE_KEY)
    const expiry = localStorage.getItem(REF_EXPIRY_KEY)
    const isExpired = expiry && Date.now() > parseInt(expiry)

    if (isExpired) {
      localStorage.removeItem(REF_CODE_KEY)
      localStorage.removeItem(REF_EXPIRY_KEY)
      return
    }

    const activeRef = refCode ?? storedRef
    if (!activeRef) return

    const visitorId = getOrCreateVisitorId()

    // Fire-and-forget click tracking
    supabase.functions.invoke('track-click', {
      body: {
        ref_code: activeRef,
        landing_url: window.location.href,
        referrer_url: document.referrer,
        visitor_id: visitorId,
      },
    }).catch(() => { /* non-critical, do not block the page */ })
  }, [])

  const getAttributionData = () => {
    const refCode = localStorage.getItem(REF_CODE_KEY)
    const expiry = localStorage.getItem(REF_EXPIRY_KEY)
    if (!refCode || (expiry && Date.now() > parseInt(expiry))) return null
    return {
      ref_code: refCode,
      visitor_id: getOrCreateVisitorId(),
    }
  }

  return { getAttributionData }
}
```

## Common mistakes

- **Tracking the raw IP address instead of a hash** — Storing raw IP addresses in your database may trigger GDPR compliance requirements for European users. IP addresses can be considered personal data. Fix: Hash the IP address (salted with the affiliate_id) using SHA-256 before storing. The hash is sufficient for deduplication without storing the raw personal data. This also means if the hash database is ever breached, IPs cannot be reconstructed.
- **Attributing conversions using only a query parameter, not a cookie window** — If the user visits via a ?ref= link but completes the purchase in a different browser tab or session, the query parameter is gone. You miss the conversion entirely. Fix: Store the ref code and expiry in localStorage when the visitor first lands (the useAffiliateTracking hook does this). Read it from localStorage at checkout. This persists the attribution for the full 30-day window across tabs and page refreshes.
- **Creating commission records directly from client-side code** — A malicious user could forge a conversion request from the browser, creating fraudulent commissions by calling the Supabase insert directly. Fix: All commission creation must happen in a server-side Edge Function called from your checkout completion webhook or server action. The Edge Function verifies the order_id against your orders table to confirm the purchase is real before creating the commission.
- **Not handling the case where a visitor clicks multiple affiliate links** — If a visitor clicks affiliate A's link on Monday and affiliate B's link on Friday, and converts on Saturday — who gets the commission? Without a clear policy, this creates disputes. Fix: The track-conversion function uses 'last click wins': it takes the most recent click within 30 days. Document this attribution model clearly in your affiliate terms of service so affiliates understand the rules.

## Best practices

- Use 'last click wins' attribution as the default and document it in your affiliate agreement. It is the simplest model to explain and implement, and it is the industry standard.
- Never store raw IP addresses. Hash them server-side in the Edge Function. Include the affiliate_id as a salt so the same visitor's hash differs per affiliate.
- Process commissions through a pending → approved → paid workflow so you have time to reverse fraudulent conversions before they are paid out.
- Add a minimum payout threshold (e.g. $50) to reduce payment processing overhead. Store this in the affiliates table as min_payout_amount.
- Log every call to track-conversion regardless of attribution outcome. Log attributed=false cases too. This audit trail is invaluable for dispute resolution when affiliates claim they should have received credit for a sale.
- Rate-limit the track-click Edge Function to prevent bots from generating artificial clicks. Return 429 if the same IP sends more than 10 click tracking requests per minute.
- Send a welcome email to new affiliates with their referral link, commission rate, and a link to the affiliate portal. Use an Edge Function triggered by a Postgres trigger on the affiliates table INSERT to call Resend.

## Frequently asked questions

### What happens if a user clears their localStorage before converting?

The attribution is lost. This is a known limitation of localStorage-based tracking. For higher attribution accuracy, you can supplement with a server-side cookie (set via your domain's own server) that persists the ref code. However, this requires a backend that can set HTTP cookies, which goes beyond Lovable's Edge Functions alone. For most use cases, localStorage with a 30-day window has acceptable attribution rates.

### Can an affiliate inflate their own click count by visiting their link repeatedly?

No. The 24-hour IP hash deduplication in the track-click Edge Function prevents this. Repeated visits from the same IP hash within 24 hours return tracked: false and no new click record is inserted. The hash includes the affiliate_id, so visiting your own link and other pages do not interfere with each other.

### How do I integrate the conversion tracking with Stripe payments?

In your Stripe checkout.session.completed webhook handler, read the client_reference_id (set this to the visitor_id when creating the Checkout Session) and the amount_total. Call the track-conversion Edge Function with these values. The visitor_id links the purchase to the affiliate click record, completing the attribution chain.

### What commission rate structure should I use?

Percentage commissions (e.g. 20% of sale value) are common for SaaS products and scale naturally with order value. Flat-fee commissions (e.g. $25 per signup) work better for free-trial-to-paid funnels where order amounts vary. You can support both by adding a commission_type ('percentage' | 'flat') column to the affiliates table and adjusting the calculation in the Edge Function accordingly.

### How do I prevent the same user from being counted as both a click and a conversion from two different affiliate links?

The track-conversion function uses 'last click wins' — it attributes the sale to the most recent click from the visitor_id within 30 days. If the user clicked affiliate A three weeks ago and affiliate B yesterday, affiliate B gets the credit. Only one commission record is created per order_id (enforced by the unique constraint).

### Can I track affiliate performance for non-purchase conversions like signups or free trials?

Yes. The track-conversion Edge Function does not care what type of conversion it is — it just records an order_id, amount, and visitor_id. For a free trial signup, pass order_amount=0 and use a flat commission. The affiliate still gets credit for the signup. You can add a conversion_type column to distinguish purchase vs signup conversions in your reports.

### How do I handle chargebacks or refunds affecting affiliate commissions?

Add a webhook for charge.dispute.created or refund Stripe events. When received, find the affiliate_conversion with the matching order_id and update its status to 'reversed'. If the commission was already paid, insert a negative adjustment record in a new affiliate_adjustments table that reduces the next payout. Always communicate refund policies clearly in your affiliate agreement.

### Is there help available to build a more complex affiliate program?

RapidDev builds Lovable apps with multi-tier commissions, Stripe Connect payouts, and custom attribution models. Reach out if your affiliate program requires features beyond this guide.

---

Source: https://www.rapidevelopers.com/how-to-build-lovable/affiliate-tracking-app
© RapidDev — https://www.rapidevelopers.com/how-to-build-lovable/affiliate-tracking-app