Build a Membership Site in Lovable

Audit and harden the RLS policy on the content table.

First, open the SQL Editor and run: SELECT policyname, cmd, qual FROM pg_policies WHERE tablename = 'content';

Then do the following:
1. Drop any existing SELECT policy on content.
2. Create two separate SELECT policies:
   - Policy 'public_free_content' for the anon role: USING (min_tier = 'free' AND published_at <= now())
   - Policy 'tier_gated_content' for the authenticated role: USING (
       published_at <= now() AND (
         min_tier = 'free'
         OR (min_tier = 'premium' AND (auth.jwt() -> 'app_metadata' ->> 'tier') IN ('premium','founder'))
         OR (min_tier = 'founder' AND (auth.jwt() -> 'app_metadata' ->> 'tier') = 'founder')
       )
     )
3. After applying, test as anon: use the Supabase REST URL with the anon key and curl or Insomnia to SELECT from content. Confirm that zero premium rows are returned — the body_md field must NOT appear in the response for min_tier='premium' content when unauthenticated.
4. Add a comment in the migration: -- Content RLS: security enforced at DB layer. Paywall component is UX only.

Deliverable: A free user signed into the app sees the Paywall component on premium content, AND a curl request with the anon key returns zero premium rows from /rest/v1/content.

Implement the stripe-webhook Edge Function at supabase/functions/stripe-webhook/index.ts.

The function must:
1. Read the raw request body BEFORE any parsing: const body = await req.text();
2. Read the Stripe signature: const sig = req.headers.get('stripe-signature');
3. Verify the event: const event = await stripe.webhooks.constructEventAsync(body, sig, Deno.env.get('STRIPE_WEBHOOK_SECRET'));
   DO NOT use constructEvent (synchronous) — Deno's WebCrypto is async and will throw: 'SubtleCryptoProvider cannot be used in a synchronous context'
   DO NOT call req.json() before verification — JSON-parsing mutates the body and breaks signature verification.
4. Check idempotency: SELECT id FROM stripe_events WHERE stripe_event_id = event.id. If found, return new Response('Already processed', {status: 200}) immediately.
5. Insert into stripe_events: INSERT INTO stripe_events(stripe_event_id) VALUES(event.id).
6. Handle these event types:
   - checkout.session.completed: get userId from metadata.userId, plan from metadata.plan, write a subscriptions row with status='active', call supabaseAdmin.auth.admin.updateUserById(userId, {app_metadata: {tier: plan}}), update profiles.tier = plan WHERE id = userId.
   - customer.subscription.updated: if cancel_at_period_end=true, update subscriptions.cancel_at_period_end=true but do NOT change tier. If status='past_due', update subscriptions.status='past_due'.
   - customer.subscription.deleted: downgrade tier to 'free' — call supabaseAdmin.auth.admin.updateUserById(userId, {app_metadata: {tier: 'free'}}), update profiles.tier='free', subscriptions.status='canceled'.
7. Return new Response('OK', {status: 200}) after processing.

Also implement create-checkout-session: accept {plan} from the authenticated user, get or create the Stripe customer, call stripe.checkout.sessions.create with mode='subscription', the correct price ID from Deno.env.get('STRIPE_PRICE_ID_PREMIUM') or 'STRIPE_PRICE_ID_FOUNDER', success_url='/checkout/success', cancel_url='/pricing', and metadata: {userId: user.id, plan}.

Also implement create-portal-session: get stripe_customer_id from subscriptions WHERE user_id=auth.uid(), call stripe.billingPortal.sessions.create({customer, return_url: '/account'}), return {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.

Add two things:

1. /checkout/success page: When Stripe redirects here after a successful payment, call await supabase.auth.refreshSession() to force the JWT to refresh and pick up the new app_metadata.tier. Show a 'Welcome to [Plan]!' confirmation card with a 'Start reading premium content' button that links to /library. If the session refresh fails (network timeout), show: 'Your upgrade is active. If you still see the free tier after 60 seconds, sign out and sign back in to refresh your access.'

2. Update /account to wire up the Manage Subscription button: call the create-portal-session Edge Function and redirect to the returned URL. Show the current subscription status (tier badge, renewal date formatted as 'Renews June 25, 2027', or 'Cancels June 25, 2027' if cancel_at_period_end is true). Show a 'Downgrade to Free' note explaining it takes effect at period end.

Deliverable: A paid member can click Manage Subscription, land on the Stripe Customer Portal, cancel or change plan, and return to /account with accurate status shown.

Add transactional email notifications via Resend.

In the stripe-webhook Edge Function, after each subscription event, call a helper async function sendMembershipEmail(userId, type, metadata) that:
- Fetches the user's email from profiles WHERE id = userId
- Depending on type, sends via the Resend API (POST https://api.resend.com/emails with Authorization: Bearer RESEND_API_KEY):
  - 'welcome': Subject 'Welcome to [Site Name] Premium', body explaining what they can access, link to /library
  - 'renewed': Subject 'Your [Plan] membership has renewed', renewal date, link to /account
  - 'payment_failed': Subject 'Action required: update your payment method', link to Stripe portal, note that access continues for 7 days
  - 'canceled': Subject 'Your membership has ended', what they lose access to, link to /pricing to resubscribe
- Return early (don't throw) if RESEND_API_KEY is not set — email is optional, billing still works without it.

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

Deliverable: Test each email type by triggering the corresponding test webhook from Stripe Dashboard → Webhooks → Send test event.

Add a founder tier to the pricing page and checkout flow.

1. Update /pricing PricingTable: add a third column for 'Founder' with annual pricing (e.g., $199/yr). Highlight it with an amber-500 border and a 'Best value' badge. Feature list should include everything in Premium plus a Discord community access badge and early access to new content.

2. The 'Become a Founder' button calls create-checkout-session with plan='founder', using STRIPE_PRICE_ID_FOUNDER (a one-time payment or annual recurring from your Stripe Dashboard).

3. Update the stripe-webhook handler for checkout.session.completed: if metadata.plan='founder', set app_metadata.tier='founder' and profiles.tier='founder'. For a one-time payment (mode='payment'), also write a subscriptions row with status='active', plan='founder', current_period_end=null (lifetime).

4. Update all Paywall components and TierBadge to show the correct upgrade path: free users upgrading to premium see one CTA, premium users who could upgrade to founder see a different CTA ('Upgrade to Founder — save 40%').

5. Update the content RLS policy to confirm founder-only content (min_tier='founder') is accessible only to users with tier='founder' in app_metadata.

Deliverable: A complete 3-tier pricing page where free → premium → founder each have distinct pricing, feature lists, and upgrade paths.

Add a scheduled content drip system so premium articles publish automatically on a set schedule.

1. The content table already has published_at timestamptz. Set this to a future date for scheduled articles — the RLS policy already gates on published_at <= now().

2. In /admin/content, update the create/edit drawer: add a date-time picker for published_at. If published_at is in the future, show a 'Scheduled: [date]' badge on the row in the admin table. If null, treat as draft (never published).

3. In the /library page component, add a 'Next premium article' indicator: query the content table for the next scheduled published_at > now() WHERE min_tier != 'free', format as 'Next article: Tuesday, July 1 at 9am'. Show this above the library list for authenticated users.

4. Add a Postgres function next_scheduled_content() RETURNS TABLE(title text, published_at timestamptz, min_tier text) that returns the next 3 upcoming scheduled articles for the admin dashboard.

Deliverable: Admins can schedule articles in the future. The library page shows the next release date. RLS automatically opens access when published_at passes — no cron required.