Build a Subscription System in Lovable

Implement the stripe-webhook Edge Function at supabase/functions/stripe-webhook/index.ts. This is the most critical part of the subscription system — do it exactly as described.

The function must:
1. Read raw body BEFORE any parsing: const body = await req.text();
2. Read signature: const sig = req.headers.get('stripe-signature');
3. Verify: const event = await stripe.webhooks.constructEventAsync(body, sig, Deno.env.get('STRIPE_WEBHOOK_SECRET'));
   Use constructEventAsync, NOT constructEvent. Deno's WebCrypto is async — the synchronous variant throws: 'SubtleCryptoProvider cannot be used in a synchronous context.'
   DO NOT call req.json() before verification — JSON-parsing mutates the bytes and breaks signature verification.
4. Check idempotency: const {data: existing} = await supabaseAdmin.from('stripe_events').select('id').eq('stripe_event_id', event.id).maybeSingle(); if (existing) return new Response('Already processed', {status: 200});
5. Insert into stripe_events: await supabaseAdmin.from('stripe_events').insert({stripe_event_id: event.id, event_type: event.type});
6. Handle these event types:

   checkout.session.completed:
   - Extract userId from event.data.object.metadata.userId and planId from metadata.planId
   - Extract stripeCustomerId, subscriptionId from event.data.object
   - Call apply_subscription_event() with all fields and status='active'
   - Call await supabaseAdmin.auth.admin.updateUserById(userId, {app_metadata: {tier: planId}})

   customer.subscription.updated:
   - If cancel_at_period_end=true: update subscriptions.cancel_at_period_end=true only, do NOT change tier
   - If status='past_due': call apply_subscription_event with status='past_due'
   - If status='active' (reactivated after past_due): call apply_subscription_event with status='active'

   customer.subscription.deleted:
   - Call apply_subscription_event with plan_id='free', status='canceled', canceled_at=now()
   - Call await supabaseAdmin.auth.admin.updateUserById(userId, {app_metadata: {tier: 'free'}})

   invoice.payment_failed:
   - Update subscriptions.status='past_due'
   - Send Resend email 'payment_failed' if RESEND_API_KEY is set

7. Return new Response('OK', {status: 200}) after processing.

Also implement create-checkout-session:
- Require authentication (read JWT from Authorization header)
- Accept {planId} from request body
- Get or create Stripe customer: check subscriptions for existing stripe_customer_id, otherwise call stripe.customers.create({email: user.email, metadata: {userId: user.id}})
- Create Stripe Checkout session: mode='subscription', line_items with quantity 1 of the plan's stripe_price_id (from plans table), success_url='/checkout/success', cancel_url='/pricing', metadata: {userId, planId}
- Return {url: session.url}

Also implement create-portal-session:
- Require authentication
- Fetch stripe_customer_id from subscriptions WHERE user_id = auth.uid()
- Call stripe.billingPortal.sessions.create({customer: stripe_customer_id, return_url: '/account/billing'})
- Return {url: session.url}

Deliverable: All three Edge Functions deployed. Test with Stripe test card 4242 4242 4242 4242 on the DEPLOYED lovable.app URL — not in preview. Check Stripe Dashboard → Webhooks → your endpoint to confirm events are being received and returning 200.

Add two features:

1. Verify the create-portal-session Edge Function is deployed and working. On /account/billing, wire the 'Manage subscription' button: call create-portal-session, await the response URL, then window.location.href = url. Configure the Customer Portal in Stripe Dashboard → Billing → Customer Portal: enable 'Update subscription' (allow plan changes), 'Cancel subscription', and 'View invoice history'.

2. Add trial period support to create-checkout-session: add an optional trial_days parameter (default 0). If trial_days > 0, include subscription_data: {trial_period_days: trial_days} in the Checkout session. On the /pricing page, add an optional 'Start 14-day free trial' CTA for the Pro plan that passes trial_days=14.

3. Handle trial webhook events in stripe-webhook:
   - customer.subscription.trial_will_end (fires 3 days before trial ends): send a Resend email 'trial_ending' warning the user their trial ends in 3 days and their card will be charged
   - checkout.session.completed when a trial session completes: set status='trialing', tier to the trial plan

4. Update CurrentPlanCard on /account/billing to show trial status: 'Trial ends [date] — after that, $20/mo' instead of a renewal date.

Deliverable: Users can start a 14-day free trial via the Pro plan CTA. They see trial status on /account/billing. They receive a warning email 3 days before trial ends. After trial ends, Stripe auto-charges and the webhook sets their status to 'active'.

Add billing transactional emails via Resend. RESEND_API_KEY must already be in Cloud Secrets.

Create a helper function sendBillingEmail(userId, type, metadata) in the stripe-webhook Edge Function:
- Fetch user email from profiles WHERE id = userId
- POST to https://api.resend.com/emails with Authorization: Bearer RESEND_API_KEY
- Templates (use plain text for reliability, no HTML required):
  'welcome': Subject '[App Name] — Welcome to [plan]!', body: what they can now do, link to /dashboard
  'renewed': Subject 'Your [plan] subscription has renewed', body: renewed until [date], link to /account/billing
  'payment_failed': Subject 'Action required: update your payment method', body: 7-day grace period, link to Stripe portal CTA
  'canceled': Subject 'Your [plan] subscription has ended', body: what they lose access to, link to /pricing to resubscribe
  'trial_ending': Subject 'Your trial ends in 3 days', body: will be charged $X on [date], how to cancel if not interested

Event mapping:
- checkout.session.completed → 'welcome'
- customer.subscription.updated (status='active', not first) → 'renewed'
- invoice.payment_failed → 'payment_failed'
- customer.subscription.deleted → 'canceled'
- customer.subscription.trial_will_end → 'trial_ending'

Return early without throwing if RESEND_API_KEY is not set — email is optional, billing still works without it.

Deliverable: Each billing lifecycle event triggers the appropriate transactional email. Test by sending a test webhook from Stripe Dashboard → Webhooks → Send test event for each event type.

Expand the /admin/customers page with a full subscriber view and MRR analytics.

1. Subscriber list: paginated table (25 rows per page) with columns: email, plan (badge), status (badge: active=green, past_due=yellow, canceled=red), current_period_end or canceled_at, link to Stripe Dashboard customer (https://dashboard.stripe.com/customers/{stripe_customer_id}). Add filters: status dropdown, plan dropdown, date range for created_at.

2. MRR stat card: display as a large number at the top. Compute via Postgres RPC: CREATE OR REPLACE FUNCTION admin_subscription_stats() RETURNS TABLE(mrr_cents bigint, active_count int, past_due_count int, churned_last_30_days int) LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN RETURN QUERY SELECT SUM(p.price_cents)::bigint AS mrr_cents, COUNT(*) FILTER (WHERE s.status = 'active')::int AS active_count, COUNT(*) FILTER (WHERE s.status = 'past_due')::int AS past_due_count, COUNT(*) FILTER (WHERE s.canceled_at > now() - interval '30 days')::int AS churned_last_30_days FROM subscriptions s JOIN plans p ON p.id = s.plan_id WHERE s.status IN ('active', 'past_due'); END; $$; Call this RPC on page mount and refresh every 60 seconds.

3. MRR display: format as '$X,XXX' (e.g., $1,240). Show month-over-month change if you have historical data (compare to same RPC last 30 days — skip if no prior data).

4. Protect /admin/customers: wrap with an AdminGuard component that checks app_metadata.role='admin' from the JWT. Redirect to /dashboard if role is missing.

Deliverable: Admin users can see a real-time MRR stat and a paginated, filterable list of all paying subscribers with links to their Stripe profiles.

Add the ability for users to change between paid plans (e.g., Pro to Team) with Stripe proration.

1. On /account/billing, add a 'Change plan' section visible to active subscribers. Show available upgrade/downgrade options (exclude current plan and free tier). Each option shows new price and 'Prorated credit' note.

2. Create Edge Function supabase/functions/update-subscription/index.ts:
   - Require authentication
   - Accept {newPlanId} from request body
   - Fetch the user's current stripe_subscription_id from subscriptions
   - Fetch the new plan's stripe_price_id from plans WHERE id = newPlanId
   - Call stripe.subscriptions.update(subscriptionId, {items: [{id: currentItemId, price: newPriceId}], proration_behavior: 'create_prorations'})
   - Return {success: true}
   - The stripe-webhook handler's customer.subscription.updated event will pick up the new plan_id and call apply_subscription_event automatically

3. Add the 'Change plan' button to /account/billing that calls update-subscription and shows a loading spinner while waiting. After success, call supabase.auth.refreshSession() and reload CurrentPlanCard to reflect the new plan.

4. Update the PricingTable on /pricing: for signed-in paid users, replace 'Go Pro' / 'Go Team' buttons with 'Upgrade' or 'Downgrade' buttons that call update-subscription instead of create-checkout-session.

Deliverable: A Pro subscriber can upgrade to Team with prorated billing. A Team subscriber can downgrade to Pro. The TierGate component updates within 2 seconds of the plan change.

Add usage tracking and metered billing for a variable feature like API calls or AI credits.

1. Create table usage_events:
   - id uuid PRIMARY KEY DEFAULT gen_random_uuid()
   - user_id uuid NOT NULL REFERENCES auth.users(id)
   - kind text NOT NULL (e.g., 'api_call', 'ai_request', 'record_created')
   - quantity int DEFAULT 1
   - occurred_at timestamptz DEFAULT now()
   - RLS: own row INSERT and SELECT; admin SELECT all.

2. Create a Postgres function record_usage(p_user_id uuid, p_kind text, p_quantity int) SECURITY DEFINER that inserts into usage_events and returns the user's total usage for the current billing period from subscriptions.current_period_start to now().

3. Update TierGate: add a UsageGate variant that checks current usage against the plan's feature limit. Example: {const {data} = await supabase.rpc('get_usage_this_period', {kind: 'ai_request'}); if (data.count >= plan.features.ai_credits) return <UpgradeCTA/>; } Show remaining usage in the gate fallback: 'You have used X of Y AI credits this period.'

4. In Stripe: create a metered price for the feature (e.g., $0.01 per API call above the plan limit). Create Edge Function supabase/functions/report-metered-usage/index.ts that runs nightly (via a scheduled Edge Function or pg_cron): SELECT user_id, COUNT(*) as quantity FROM usage_events WHERE kind = 'api_call' AND occurred_at >= current_period_start GROUP BY user_id; then call stripe.subscriptionItems.createUsageRecord(itemId, {quantity, timestamp, action: 'set'}) for each user.

Deliverable: Usage events are tracked per user. TierGate shows remaining credits. Users who exceed their plan's limit see a clear upgrade CTA with remaining count.