38980-vm/app-9w9pd00g5j41/PURCHASE_LEAD_SECURITY_FIX.md

Purchase Lead Security Fix

Issue Summary

The purchase_lead function had a potential security vulnerability where the frontend was passing a client-controlled creditsCost parameter, which could theoretically allow providers to purchase high-value leads for arbitrary low prices.

Root Cause

• Migration 00010: Created purchase_lead(UUID, UUID, INTEGER) with client-controlled price parameter
• Migration 00012: Created safe 2-arg version purchase_lead(UUID, UUID) that gets price from server
• Problem: Frontend code still called the function with 3 arguments, passing p_credits_cost

Security Fix Applied

1. Database Migration (00058)

Created migration drop_insecure_purchase_lead_overload that:

• Explicitly drops any 3-argument version of purchase_lead if it exists
• Adds documentation comment to the safe 2-arg function
• Ensures only the secure version remains in the database

2. Frontend API Fix

File: src/db/api.ts

• Removed creditsCost parameter from leadPurchasesApi.purchase() function signature
• Removed p_credits_cost from the RPC call parameters
• Added comment documenting that price is determined server-side

Before:

async purchase(providerId: string, leadId: string, creditsCost: number = 10) {
  const { data, error } = await supabase.rpc('purchase_lead', {
    p_provider_id: providerId,
    p_lead_id: leadId,
    p_credits_cost: creditsCost, // ❌ Client-controlled
  });
}

After:

async purchase(providerId: string, leadId: string) {
  const { data, error } = await supabase.rpc('purchase_lead', {
    p_provider_id: providerId,
    p_lead_id: leadId, // ✅ Price from server
  });
}

3. Component Update

File: src/components/provider/LeadDetailModal.tsx

• Updated call to leadPurchasesApi.purchase() to only pass 2 arguments
• Added comment documenting server-side price determination

How the Secure Function Works

The safe purchase_lead(UUID, UUID) function:

1. Gets price from database:

   SELECT final_price INTO v_lead_price
   FROM leads
   WHERE id = p_lead_id;
   

2. Uses calculated/override price:

   • final_price = override_price (if set by admin) OR calculated_price
   • calculated_price is computed based on activities (balloon, ATV, tours, etc.)
   • Base price is 20 credits, with multipliers for premium activities

3. Atomic transaction:

   • Locks wallet row with FOR UPDATE
   • Verifies sufficient balance
   • Deducts correct amount
   • Records purchase with actual price paid
   • Creates transaction record

Verification

-- Only the safe 2-arg function exists
SELECT 
  proname, 
  pg_get_function_arguments(oid) as args
FROM pg_proc 
WHERE proname = 'purchase_lead';

-- Result:
-- purchase_lead | p_provider_id uuid, p_lead_id uuid

Impact

• ✅ Security: Clients can no longer specify arbitrary prices
• ✅ Integrity: All purchases use server-calculated pricing
• ✅ Audit: lead_purchases.price_paid accurately reflects actual cost
• ✅ Consistency: Frontend and backend are now aligned

Testing Recommendations

1. Verify lead purchase flow works correctly
2. Confirm correct price is deducted from wallet
3. Check that high-value leads (with balloon, ATV, etc.) charge appropriate amounts
4. Verify admin price overrides work correctly
5. Test insufficient balance scenarios

Related Files

• supabase/migrations/00010_create_purchase_lead_function.sql (original vulnerable version)
• supabase/migrations/00012_add_lead_pricing_system.sql (safe version created)
• supabase/migrations/00058_drop_insecure_purchase_lead_overload.sql (cleanup migration)
• src/db/api.ts (API layer)
• src/components/provider/LeadDetailModal.tsx (UI component)