Payment Flow Debugger

# Payment Flow Debugger Skill

Debug payment issues, trace checkout flows, and analyze webhook failures in the id8composer billing system.

## When to Use This Skill

Invoke this skill when encountering:
- Checkout session failures
- Webhook processing errors
- Payment state inconsistencies
- Subscription sync issues
- User reports payment not working

## Debugging Workflow

### 1. Identify the Failure Point

**Check these logs in order**:
```bash
# Browser console (client-side errors)
# Network tab → Failed requests

# Server logs (API errors)
npm run dev | grep -i "error\|fail"

# Stripe dashboard
# → Developers → Webhooks → Recent events
# → Look for failed webhook deliveries

# Database (data inconsistencies)
# Check subscriptions table
```

### 2. Common Issues & Solutions

**Issue: Checkout Session Creation Fails**
- **Symptom**: User clicks "Upgrade", nothing happens
- **Check**: Browser console for 400/500 errors
- **Common Causes**:
  - Invalid priceId (not in env vars)
  - Invalid returnUrl (validation failing)
  - Missing Stripe keys
  - Rate limiting (429 response)
- **Fix**: Check `/api/billing/checkout` logs, verify env vars

**Issue: Webhook Not Processing**
- **Symptom**: Payment succeeds in Stripe, but user still shows FREE tier
- **Check**: Stripe dashboard → Webhooks → View event
- **Common Causes**:
  - Webhook signature verification failing
  - Database connection error
  - User lookup failing (missing metadata)
  - RLS policy blocking update
- **Fix**: Check `/api/webhooks/stripe` logs, verify signature

**Issue: Subscription Shows Wrong Tier**
- **Symptom**: User paid but still sees FREE limits
- **Check**: Database `subscriptions` table
- **Common Causes**:
  - Webhook processed but tier not updated
  - Cache not invalidated
  - Multiple subscriptions for same user
- **Fix**: Manually update tier, clear cache, investigate duplicate subs

### 3. Debugging Tools

**Test Stripe Webhooks Locally**:
```bash
# Install Stripe CLI
brew install stripe/stripe-cli/stripe

# Forward webhooks to local server
stripe listen --forward-to localhost:3000/api/webhooks/stripe

# Trigger test events
stripe trigger invoice.payment_failed
stripe trigger customer.subscription.updated
```

**Query Subscription State**:
```sql
-- Find user's subscription
SELECT * FROM public.subscriptions
WHERE user_id = 'uuid-here';

-- Find subscriptions by status
SELECT user_id, tier, status, current_period_end
FROM public.subscriptions
WHERE status = 'past_due';

-- Find duplicate subscriptions
SELECT user_id, COUNT(*)
FROM public.subscriptions
WHERE status = 'active'
GROUP BY user_id
HAVING COUNT(*) > 1;
```

**Test Checkout Flow**:
```typescript
// Test script: test-checkout.ts
import { createCheckoutSession } from '@/lib/billing/stripe';

const testUserId = 'test-user-123';
const testPriceId = process.env.STRIPE_PRICE_ID_PRO_MONTHLY!;
const returnUrl = 'http://localhost:3000/settings/billing?success=true';

const session = await createCheckoutSession(testUserId, testPriceId, returnUrl);
console.log('Checkout URL:', session.url);
```

### 4. Trace Complete Payment Flow

```
User clicks "Upgrade"
  ↓
  → Check: Did button click fire? (console.log in onClick)
  ↓
POST /api/billing/checkout
  ↓
  → Check: Did request reach server? (see logs)
  → Check: What status code returned? (200, 400, 500?)
  → Check: Validation passed? (returnUrl, priceId valid?)
  ↓
Stripe API called
  ↓
  → Check: Did Stripe respond? (check response.url exists)
  → Check: Any Stripe errors? (insufficient balance, invalid price, etc.)
  ↓
Redirect to Stripe Checkout
  ↓
  → Check: Did redirect happen? (user sees Stripe page?)
  ↓
User enters payment
  ↓
Stripe processes payment
  ↓
Webhook: checkout.session.completed
  ↓
  → Check: Did webhook reach /api/webhooks/stripe? (see logs)
  → Check: Signature verified? (should see "Webhook verified" log)
  → Check: Event type matched? (case statement hit?)
  ↓
syncSubscriptionToDatabase()
  ↓
  → Check: Database query succeeded? (no SQL errors?)
  → Check: Subscription record created/updated?
  ↓
User tier updated to PRO
  ↓
  → Check: Database shows tier=PRO?
  → Check: UI refreshed with new tier?
  → Check: Limits now enforced correctly?
```

### 5. Emergency Recovery Procedures

**User Paid But Still Shows FREE**:
```sql
-- Manually update subscription (last resort!)
UPDATE public.subscriptions
SET
  tier = 'PRO',
  status = 'active',
  updated_at = NOW()
WHERE user_id = 'affected-user-id'
  AND stripe_subscription_id = 'sub_from_stripe_dashboard';
```

**Webhook Missed, Need to Resync**:
1. Get subscription ID from Stripe dashboard
2. Manually trigger webhook again (Stripe dashboard → Webhook → Resend)
3. Or manually sync using Stripe API:
```typescript
const sub = await stripe.subscriptions.retrieve('sub_xxx');
await syncSubscriptionToDatabase(sub);
```

## Conclusion

Follow the trace methodically from user action → API → Stripe → webhook → database. Most issues are caught in logs. When in doubt, check Stripe dashboard for ground truth.