Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

how do i connect stripe with supabase at a senior level design, using next js, first…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Qwenqwen3-235b-a22b-no-thinking’s answer.
  1. User

    how do i connect stripe with supabase at a senior level design, using next js, first only write PRDs and baby step implementations, then write a paragraph how its done, then finally write each code section needed and also explain how that connects to the next part of code...

  2. Gemini Assistant

    Connecting Stripe with Supabase and Next.js at a senior-level design involves careful consideration of security, scalability, data synchronization, and user experience. We'll leverage Next.js API routes for server-side operations, Supabase for authentication and database management, and Stripe for payments and subscription management.


    Phase 1: Product Requirements Documents (PRDs)

    PRD: Subscription Management with Stripe & Supabase

    1. Title: Secure & Scalable Subscription Management with Stripe, Supabase, and Next.js

    2. Objective: To enable users to securely subscribe to premium content/features via Stripe, manage their subscriptions, and have their subscription status reflected accurately and in real-time within our Supabase-backed Next.js application.

    3. Key Features:

    • User Authentication: Leverage Supabase Auth for user registration and login.
    • Pricing Display: Dynamically display available subscription plans (products & prices) from Stripe, cached or synced to Supabase.
    • Checkout Flow:
      • Initiate Stripe Checkout sessions securely from the Next.js backend.
      • Redirect users to Stripe's hosted checkout page.
      • Handle successful and cancelled payment redirects back to the application.
    • Subscription Synchronization:
      • Utilize Stripe Webhooks to listen for critical subscription events (e.g., checkout.session.completed, customer.subscription.created, customer.subscription.updated, customer.subscription.deleted, invoice.payment_succeeded).
      • Update Supabase database records (profiles, subscriptions) based on these webhook events to maintain an accurate source of truth.
    • Subscription Management (Customer Portal):
      • Provide users with a link to Stripe's Customer Portal for self-service subscription management (e.g., updating payment methods, changing plans, canceling subscriptions).
      • Securely generate Customer Portal sessions via the Next.js backend.
    • Access Control: Implement Row Level Security (RLS) in Supabase to restrict access to premium content based on the user's subscription status.
    • Error Handling & Logging: Robust error handling for API calls, webhooks, and database operations.

    4. User Stories:

    • As a new user, I want to sign up/log in using my email/password (or social auth) so I can access the platform.
    • As a user, I want to see different subscription plans and their features so I can choose the one that suits me.
    • As a user, I want to securely pay for a subscription using Stripe's hosted checkout page so my payment information is handled safely.
    • As a user, after successful payment, I want my subscription status to be immediately updated in the application so I can access premium content.
    • As a user, I want to be able to manage my subscription (update payment, change plan, cancel) through a self-service portal so I don't need to contact support.
    • As a user, if my subscription expires or is cancelled, I want my access to premium content to be revoked automatically.
    • As an administrator, I want to ensure that user subscription data in Supabase is always in sync with Stripe to maintain data consistency.

    5. Technical Requirements:

    • Frontend: Next.js (React) for dynamic UI.
    • Backend: Next.js API Routes for server-side logic (Stripe API calls, webhook handling).
    • Database & Auth: Supabase (PostgreSQL, Supabase Auth).
    • Payment Gateway: Stripe (Checkout, Billing, Webhooks, Customer Portal).
    • Security:
      • Environment variables for API keys.
      • Stripe Webhook signature verification.
      • Supabase RLS for data protection.
      • HTTPS for all communication.
    • Deployment: Vercel (or similar) for Next.js, Supabase Cloud.

    6. Non-Functional Requirements:

    • Scalability: The architecture should support a growing number of users and subscriptions without significant re-architecture.
    • Security: All payment and user data must be handled securely, adhering to PCI compliance best practices (by offloading to Stripe).
    • Reliability: Webhooks must be idempotent to handle retries without data corruption.
    • Performance: Fast page loads and minimal latency for payment initiation and subscription updates.
    • Maintainability: Clear code structure, good comments, and proper documentation.

    7. Success Metrics:

    • Conversion Rate: Percentage of users who complete a subscription.
    • Webhook Success Rate: Percentage of webhook events successfully processed and updated in Supabase.
    • Data Consistency: Minimal discrepancies between Stripe and Supabase subscription data.
    • Customer Portal Usage: Number of users utilizing the Customer Portal for self-service.

    8. Assumptions & Constraints:

    • Stripe account is set up and configured (products, prices).
    • Supabase project is initialized.
    • Basic Next.js project is set up.
    • Users are already authenticated with Supabase before attempting to subscribe.

    Phase 2: Baby Step Implementations (High-Level Flow)

    Here's a step-by-step implementation flow at a high level:

    1. Database Setup (Supabase):

      • Define profiles table to store stripe_customer_id alongside Supabase auth.users ID.
      • Define products, prices, and subscriptions tables to mirror essential Stripe data for application-level queries.
      • Set up Supabase Auth trigger to create a profile entry for new users.
      • Implement Row Level Security (RLS) for subscription data.
    2. Stripe Configuration:

      • Create Products and Prices in your Stripe Dashboard.
      • Configure a Webhook Endpoint that points to your Next.js API route (/api/webhooks). Ensure it listens for checkout.session.completed, customer.subscription.created, customer.subscription.updated, customer.subscription.deleted, invoice.payment_succeeded.
      • Generate a Webhook Signing Secret.
    3. Frontend (Next.js - Pricing Page):

      • Fetch products and prices from Supabase (which will be synced from Stripe).
      • Display pricing plans.
      • When a user clicks "Subscribe":
        • Make a client-side API call to a Next.js API route (e.g., /api/create-checkout-session), passing the chosen priceId.
    4. Backend (Next.js API Route - /api/create-checkout-session):

      • Authenticate the user using Supabase server-side client to get user_id.
      • Retrieve stripe_customer_id from the Supabase profiles table for the authenticated user. If not found, create a new Stripe Customer and store its id in profiles.
      • Use the Stripe Node.js SDK to create a checkout.Session with the priceId, stripe_customer_id, and success_url/cancel_url pointing back to your app.
      • Return the sessionId (or url) to the frontend.
    5. Frontend (Next.js - Post-Checkout):

      • Upon receiving the sessionId, redirect the user to Stripe's hosted checkout page using stripe.redirectToCheckout().
      • Handle redirects from Stripe to your success_url and cancel_url (e.g., /success or /cancel pages).
    6. Backend (Next.js API Route - /api/webhooks):

      • Receive Stripe webhook events (POST requests).
      • Crucial: Verify the Stripe signature in the request header to ensure authenticity.
      • Parse the event.
      • Use a switch statement or similar logic to handle different event types:
        • checkout.session.completed: If it's a subscription session, create/update customer_id in profiles and subscription in subscriptions.
        • customer.subscription.created, updated, deleted: Update the subscriptions table.
        • invoice.payment_succeeded: Ensure subscriptions status is active.
      • Use the Supabase Admin client to perform database updates (bypassing RLS for server-side operations).
      • Return a 200 OK response to Stripe.
    7. Frontend (Next.js - Subscription Management):

      • Provide a button/link to "Manage Subscription."
      • This button triggers a client-side call to another Next.js API route (e.g., /api/create-customer-portal-session).
    8. Backend (Next.js API Route - /api/create-customer-portal-session):

      • Authenticate the user and retrieve their stripe_customer_id from Supabase.
      • Use the Stripe Node.js SDK to create a billingPortal.Session with the customer_id and return_url.
      • Return the portal url to the frontend.
    9. Frontend (Next.js - Post-Customer Portal):

      • Redirect the user to the Stripe Customer Portal URL.
      • Stripe handles all subscription management UI and logic.
      • Stripe redirects back to your return_url when the user is done.

    Phase 3: How it's Done (Paragraph Summary)

    The integration of Stripe, Supabase, and Next.js is primarily orchestrated through server-side API routes in Next.js, acting as the secure intermediary between the client, Stripe, and Supabase. When a user wishes to subscribe, the Next.js frontend initiates a request to a serverless API route, which then securely communicates with Stripe's API to create a Checkout Session. The user is redirected to Stripe's hosted page for payment, ensuring sensitive card data never touches our servers. Upon successful payment, Stripe notifies our Next.js backend via a webhook, which is a dedicated API route designed to receive and verify Stripe events. This webhook handler then updates the Supabase database (user profiles and subscriptions tables) with the latest subscription status, effectively synchronizing the data. For self-service subscription management, another Next.js API route securely generates a Stripe Customer Portal session, redirecting the user to Stripe's portal. This architecture centralizes payment logic securely on the server, offloads PCI compliance to Stripe, and uses webhooks for real-time, robust data synchronization with Supabase, making the Supabase database the authoritative source for the application's access control and UI.


    Phase 4: Code Sections & Connections

    Let's break down the essential code sections.

    1. Supabase Database Schema (SQL)

    This defines the tables needed to store Stripe-related information within Supabase.

    -- profiles table to store user metadata including Stripe customer ID
    -- Extends auth.users to link our custom data to authenticated users
    CREATE TABLE public.profiles (
      id uuid REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
      full_name text,
      avatar_url text,
      billing_address jsonb,
      payment_method jsonb,
      stripe_customer_id text UNIQUE -- Crucial: Stores Stripe Customer ID
    );
    
    ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
    
    CREATE POLICY "Public profiles are viewable by everyone."
      ON public.profiles FOR SELECT
      USING (true);
    
    CREATE POLICY "Users can insert their own profile."
      ON public.profiles FOR INSERT
      WITH CHECK (auth.uid() = id);
    
    CREATE POLICY "Users can update their own profile."
      ON public.profiles FOR UPDATE
      USING (auth.uid() = id);
    
    
    -- products table (to mirror Stripe Products for UI display)
    CREATE TABLE public.products (
      id text PRIMARY KEY, -- Stripe Product ID
      active boolean,
      name text,
      description text,
      image text,
      metadata jsonb
    );
    
    ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
    CREATE POLICY "Public products are viewable by everyone." ON public.products FOR SELECT USING (true);
    
    
    -- prices table (to mirror Stripe Prices and link to products)
    CREATE TABLE public.prices (
      id text PRIMARY KEY, -- Stripe Price ID
      product_id text REFERENCES public.products, -- Links to product
      active boolean,
      unit_amount bigint,
      currency text,
      type text, -- one_time or recurring
      interval text, -- day, week, month, year
      interval_count integer,
      trial_period_days integer,
      metadata jsonb
    );
    
    ALTER TABLE public.prices ENABLE ROW LEVEL SECURITY;
    CREATE POLICY "Public prices are viewable by everyone." ON public.prices FOR SELECT USING (true);
    
    
    -- subscriptions table (to store active user subscriptions)
    CREATE TABLE public.subscriptions (
      id text PRIMARY KEY, -- Stripe Subscription ID
      user_id uuid REFERENCES auth.users ON DELETE CASCADE, -- Links to Supabase Auth user
      status text, -- active, canceled, unpaid, past_due, etc.
      metadata jsonb,
      price_id text REFERENCES public.prices, -- Current price subscribed to
      quantity integer,
      cancel_at_period_end boolean,
      created timestamp with time zone DEFAULT timezone('utc'::text, now()),
      current_period_start timestamp with time zone DEFAULT timezone('utc'::text, now()),
      current_period_end timestamp with time zone DEFAULT timezone('utc'::text, now()),
      ended_at timestamp with time zone,
      cancel_at timestamp with time zone,
      canceled_at timestamp with time zone,
      trial_start timestamp with time zone,
      trial_end timestamp with time zone
    );
    
    ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
    
    CREATE POLICY "Public subscriptions are viewable by auth user."
      ON public.subscriptions FOR SELECT
      USING (auth.uid() = user_id);
    
    CREATE POLICY "Users can update their own subscription (e.g., cancel)."
      ON public.subscriptions FOR UPDATE
      USING (auth.uid() = user_id);
    

    How it connects: This SQL defines the foundational data model in Supabase. The profiles table is where we'll link our Supabase user IDs to their respective Stripe Customer IDs. The products, prices, and subscriptions tables will store the synchronized data from Stripe, enabling our application to query subscription status and display product information directly from Supabase, without constantly hitting Stripe's API. user_id in subscriptions directly links to auth.users, allowing us to leverage Supabase's built-in authentication for access control.


    2. Supabase Auth Trigger (SQL)

    This ensures that every new Supabase user automatically gets a profile entry.

    -- Function to create a new profile for a new user
    CREATE OR REPLACE FUNCTION public.handle_new_user()
    RETURNS TRIGGER AS $$
    BEGIN
      INSERT INTO public.profiles (id, full_name, avatar_url)
      VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'avatar_url');
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;
    
    -- Trigger to call the function when a new user is created in auth.users
    CREATE TRIGGER on_auth_user_created
      AFTER INSERT ON auth.users
      FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();
    

    How it connects: This trigger automates the creation of a profile entry in our profiles table whenever a new user signs up via Supabase Auth. This is critical because the profile table will store the stripe_customer_id, which is essential for initiating Stripe checkouts and managing subscriptions for that user.


    3. Supabase Client Initialization (Frontend & Backend)

    You'll need separate Supabase clients for the frontend and backend to handle authentication and admin-level operations.

    lib/supabase/client.ts (For client-side interactions):

    import { createClient } from '@supabase/supabase-js';
    
    const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
    const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;
    
    export const supabase = createClient(supabaseUrl, supabaseAnonKey);
    

    How it connects: This client is used in your Next.js React components (frontend) to interact with Supabase, primarily for user authentication state and fetching user-specific data (e.g., current subscription status, if RLS is set up correctly for the client).

    lib/supabase/server.ts (For Next.js API Routes / Server Components):

    import { createClient } from '@supabase/supabase-js';
    import { Database } from '../database.types'; // Optional: for typed client
    
    // This client should be used for operations that require service_role access
    // or when you need to interact with Supabase in a server context (API routes, server components)
    // WITHOUT the current user's session.
    export const createAdminClient = () => createClient<Database>(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.SUPABASE_SERVICE_ROLE_KEY! // This key has elevated privileges!
    );
    
    // This client should be used when you need the authenticated user's session
    // in a server context (e.g., for RLS-protected queries in API routes).
    import { cookies } from 'next/headers';
    import { createServerClient } from '@supabase/ssr';
    
    export const createSupabaseServerClient = () => {
      const cookieStore = cookies();
    
      return createServerClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL!,
        process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
        {
          cookies: {
            get: (name: string) => cookieStore.get(name)?.value,
            set: (name: string, value: string, options: any) => cookieStore.set(name, value, options),
            remove: (name: string, options: any) => cookieStore.set(name, '', options),
          },
        }
      );
    };
    

    How it connects:

    • createAdminClient: Used in API routes (like the webhook handler) where you need to perform database operations that might bypass RLS or don't pertain to the current authenticated user (e.g., Stripe webhooks updating any user's subscription). This client uses the SUPABASE_SERVICE_ROLE_KEY which must be kept secret.
    • createSupabaseServerClient: Used in API routes or Server Components where you need to interact with Supabase on behalf of the currently authenticated user, leveraging their session and respecting RLS. This is vital for operations like fetching the stripe_customer_id for the logged-in user.

    4. Stripe Initialization (Backend)

    lib/stripe.ts:

    import Stripe from 'stripe';
    
    export const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, {
      apiVersion: '2024-04-10', // Use your desired API version
      typescript: true,
    });
    

    How it connects: This file initializes the Stripe SDK for Node.js. It's imported into your Next.js API routes that interact with the Stripe API (e.g., creating checkout sessions, customer portal sessions, verifying webhooks). The STRIPE_SECRET_KEY is a sensitive environment variable.


    5. Frontend: Pricing Page and Checkout Initiation

    app/pricing/page.tsx (or pages/pricing.tsx for Pages Router):

    'use client'; // For App Router
    
    import { useState, useEffect } from 'react';
    import { useRouter } from 'next/navigation'; // or 'next/router' for Pages Router
    import { supabase } from '@/lib/supabase/client';
    import { Product, Price } from '@/lib/database.types'; // Assuming you generated types
    
    export default function PricingPage() {
      const [products, setProducts] = useState<(Product & { prices: Price[] })[]>([]);
      const [loading, setLoading] = useState(true);
      const router = useRouter();
    
      useEffect(() => {
        async function fetchProducts() {
          setLoading(true);
          const { data, error } = await supabase
            .from('products')
            .select('*, prices(*)')
            .eq('active', true)
            .eq('prices.active', true)
            .order('unit_amount', { foreignTable: 'prices', ascending: true }); // Order prices by amount
    
          if (error) {
            console.error('Error fetching products:', error);
          } else {
            setProducts(data || []);
          }
          setLoading(false);
        }
        fetchProducts();
      }, []);
    
      const handleCheckout = async (priceId: string) => {
        const { data: user, error: userError } = await supabase.auth.getUser();
    
        if (userError || !user.user) {
          alert('You need to be logged in to subscribe.');
          router.push('/login'); // Redirect to login page
          return;
        }
    
        try {
          setLoading(true);
          const response = await fetch('/api/create-checkout-session', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify({ priceId }),
          });
    
          const { url, error } = await response.json();
    
          if (error) {
            alert(`Error: ${error}`);
            console.error(error);
          } else if (url) {
            router.push(url); // Redirect to Stripe Checkout
          }
        } catch (error) {
          console.error('Failed to create checkout session:', error);
          alert('Failed to create checkout session. Please try again.');
        } finally {
          setLoading(false);
        }
      };
    
      if (loading) return <div>Loading plans...</div>;
      if (products.length === 0) return <div>No plans available.</div>;
    
      return (
        <div className="container mx-auto p-4">
          <h1 className="text-3xl font-bold mb-6">Choose Your Plan</h1>
          <div className="grid grid-cols-1 md:grid-cols-3 gap-6">
            {products.map((product) => (
              <div key={product.id} className="border p-6 rounded-lg shadow-lg">
                <h2 className="text-2xl font-semibold mb-2">{product.name}</h2>
                <p className="text-gray-600 mb-4">{product.description}</p>
                {product.prices.length > 0 ? (
                  product.prices.map((price) => (
                    <div key={price.id} className="mb-4">
                      <p className="text-xl font-bold">
                        {(price.unit_amount / 100).toFixed(2)} {price.currency.toUpperCase()}{' '}
                        {price.type === 'recurring' ? `/${price.interval}` : ''}
                      </p>
                      <button
                        onClick={() => handleCheckout(price.id)}
                        className="mt-4 px-6 py-2 bg-blue-600 text-white rounded-md hover:bg-blue-700"
                        disabled={loading}
                      >
                        Subscribe
                      </button>
                    </div>
                  ))
                ) : (
                  <p>No prices available for this product.</p>
                )}
              </div>
            ))}
          </div>
        </div>
      );
    }
    

    How it connects:

    1. Data Fetching: The useEffect hook fetches products and their associated prices from your Supabase database. This data is what you synced from Stripe via webhooks, ensuring your application displays up-to-date pricing.
    2. User Authentication Check: Before initiating checkout, it verifies if the user is logged in using supabase.auth.getUser(). If not, it redirects them to the login page.
    3. API Call to Backend: When "Subscribe" is clicked, handleCheckout makes a fetch request to your Next.js API route /api/create-checkout-session. This is a POST request sending the priceId.
    4. Redirection to Stripe: The API route responds with a Stripe Checkout Session URL. The frontend then uses router.push(url) to redirect the user directly to Stripe's secure hosted checkout page.

    6. Backend: Create Stripe Checkout Session

    app/api/create-checkout-session/route.ts (or pages/api/create-checkout-session.ts for Pages Router):

    import { NextRequest, NextResponse } from 'next/server';
    import { stripe } from '@/lib/stripe';
    import { createSupabaseServerClient } from '@/lib/supabase/server'; // For auth'd user access
    
    export async function POST(req: NextRequest) {
      if (req.method !== 'POST') {
        return new NextResponse('Method Not Allowed', { status: 405 });
      }
    
      const { priceId } = await req.json();
    
      try {
        const supabase = createSupabaseServerClient();
        const { data: { user }, error: userError } = await supabase.auth.getUser();
    
        if (userError || !user) {
          return NextResponse.json({ error: 'User not authenticated' }, { status: 401 });
        }
    
        // Get or create Stripe Customer ID for the user
        let { data: profile, error: profileError } = await supabase
          .from('profiles')
          .select('stripe_customer_id')
          .eq('id', user.id)
          .single();
    
        let customerId = profile?.stripe_customer_id;
    
        if (profileError || !customerId) {
          // Create a new Stripe Customer if one doesn't exist for this user
          const customer = await stripe.customers.create({
            email: user.email,
            metadata: { supabase_user_id: user.id },
          });
          customerId = customer.id;
    
          // Update Supabase profile with the new Stripe Customer ID
          const { error: updateError } = await supabase
            .from('profiles')
            .update({ stripe_customer_id: customerId })
            .eq('id', user.id);
    
          if (updateError) {
            console.error('Error updating profile with Stripe Customer ID:', updateError);
            // Log error but proceed, as customer is created in Stripe
          }
        }
    
        const session = await stripe.checkout.sessions.create({
          payment_method_types: ['card'],
          billing_address_collection: 'auto',
          customer: customerId, // Link session to user's Stripe customer
          line_items: [{
            price: priceId,
            quantity: 1,
          }],
          mode: 'subscription',
          success_url: `${req.nextUrl.origin}/success?session_id={CHECKOUT_SESSION_ID}`,
          cancel_url: `${req.nextUrl.origin}/cancel`,
          metadata: {
            supabase_user_id: user.id, // Pass Supabase user ID for webhook handling
          },
        });
    
        return NextResponse.json({ url: session.url });
      } catch (err: any) {
        console.error('Error creating checkout session:', err);
        return NextResponse.json({ error: err.message }, { status: 500 });
      }
    }
    

    How it connects:

    1. Authentication & User ID: It uses createSupabaseServerClient() to get the authenticated user's session and user.id. This ensures the checkout session is tied to a specific user.
    2. Stripe Customer ID Management: It queries the Supabase profiles table to see if the user already has a stripe_customer_id. If not, it creates a new Stripe Customer and updates the Supabase profiles table with this new ID. This avoids creating duplicate Stripe customers for the same user.
    3. Stripe Checkout Session Creation: It uses the stripe object (initialized in lib/stripe.ts) to call stripe.checkout.sessions.create(). It includes the customerId, the priceId from the frontend, and success_url/cancel_url pointing back to your application. The metadata field is crucial for passing supabase_user_id to the webhook later.
    4. Response to Frontend: It returns the session.url back to the frontend, which then uses it to redirect the user to Stripe.

    7. Backend: Stripe Webhook Handler

    This is the most critical part for data synchronization. You'll need to configure your Stripe Webhook endpoint to point to this API route.

    app/api/webhooks/route.ts (or pages/api/webhooks.ts for Pages Router):

    import { NextRequest, NextResponse } from 'next/server';
    import Stripe from 'stripe';
    import { stripe } from '@/lib/stripe';
    import { createAdminClient } from '@/lib/supabase/server'; // For admin-level DB operations
    
    const relevantEvents = new Set([
      'product.created',
      'product.updated',
      'price.created',
      'price.updated',
      'checkout.session.completed',
      'customer.subscription.created',
      'customer.subscription.updated',
      'customer.subscription.deleted',
      'invoice.payment_succeeded',
    ]);
    
    export async function POST(req: NextRequest) {
      const buf = await req.text();
      const sig = req.headers.get('stripe-signature') as string;
      const webhookSecret = process.env.STRIPE_WEBHOOK_SECRET!;
    
      let event: Stripe.Event;
    
      try {
        event = stripe.webhooks.constructEvent(buf, sig, webhookSecret);
      } catch (err: any) {
        console.error(`Webhook Error: ${err.message}`);
        return new NextResponse(`Webhook Error: ${err.message}`, { status: 400 });
      }
    
      if (!relevantEvents.has(event.type)) {
        return new NextResponse(`Unhandled event type ${event.type}`, { status: 200 });
      }
    
      const supabaseAdmin = createAdminClient(); // Use admin client for database updates
    
      try {
        switch (event.type) {
          case 'product.created':
          case 'product.updated':
            const product = event.data.object as Stripe.Product;
            await supabaseAdmin.from('products').upsert({
              id: product.id,
              active: product.active,
              name: product.name,
              description: product.description,
              image: product.images?.[0] || null,
              metadata: product.metadata,
            }, { onConflict: 'id' });
            break;
    
          case 'price.created':
          case 'price.updated':
            const price = event.data.object as Stripe.Price;
            await supabaseAdmin.from('prices').upsert({
              id: price.id,
              product_id: typeof price.product === 'string' ? price.product : null, // Ensure product is string ID
              active: price.active,
              currency: price.currency,
              unit_amount: price.unit_amount,
              type: price.type,
              interval: price.recurring?.interval || null,
              interval_count: price.recurring?.interval_count || null,
              trial_period_days: price.recurring?.trial_period_days || null,
              metadata: price.metadata,
            }, { onConflict: 'id' });
            break;
    
          case 'customer.subscription.created':
          case 'customer.subscription.updated':
          case 'customer.subscription.deleted':
            const subscription = event.data.object as Stripe.Subscription;
            // Fetch the associated user_id from profiles table using stripe_customer_id
            const { data: profile, error: profileError } = await supabaseAdmin
              .from('profiles')
              .select('id')
              .eq('stripe_customer_id', subscription.customer)
              .single();
    
            if (profileError || !profile) {
              console.error(`Subscription webhook: User profile not found for customer ${subscription.customer}`);
              // Consider creating a new user or logging for manual review
              return new NextResponse(`User profile not found`, { status: 404 });
            }
    
            await supabaseAdmin.from('subscriptions').upsert({
              id: subscription.id,
              user_id: profile.id, // Link to Supabase user ID
              status: subscription.status,
              metadata: subscription.metadata,
              price_id: typeof subscription.items.data[0].price.id === 'string'
                ? subscription.items.data[0].price.id
                : null,
              quantity: subscription.items.data[0].quantity,
              cancel_at_period_end: subscription.cancel_at_period_end,
              created: new Date(subscription.created * 1000).toISOString(),
              current_period_start: new Date(subscription.current_period_start * 1000).toISOString(),
              current_period_end: new Date(subscription.current_period_end * 1000).toISOString(),
              ended_at: subscription.ended_at ? new Date(subscription.ended_at * 1000).toISOString() : null,
              cancel_at: subscription.cancel_at ? new Date(subscription.cancel_at * 1000).toISOString() : null,
              canceled_at: subscription.canceled_at ? new Date(subscription.canceled_at * 1000).toISOString() : null,
              trial_start: subscription.trial_start ? new Date(subscription.trial_start * 1000).toISOString() : null,
              trial_end: subscription.trial_end ? new Date(subscription.trial_end * 1000).toISOString() : null,
            }, { onConflict: 'id' });
            break;
    
          case 'checkout.session.completed':
            const checkoutSession = event.data.object as Stripe.CheckoutSession;
            if (checkoutSession.mode === 'subscription' && checkoutSession.customer && checkoutSession.subscription) {
              // The subscription.created event will handle the DB update for the subscription itself.
              // This case is mainly to handle one-time payments or ensure customer_id is linked.
              const supabase_user_id = checkoutSession.metadata?.supabase_user_id;
    
              if (supabase_user_id) {
                await supabaseAdmin
                  .from('profiles')
                  .update({ stripe_customer_id: checkoutSession.customer as string })
                  .eq('id', supabase_user_id);
              }
            }
            break;
    
          case 'invoice.payment_succeeded':
            // This event can also be used to confirm subscription status,
            // but 'customer.subscription.updated' usually covers most status changes.
            // Useful for logging or triggering specific actions post-payment.
            const invoice = event.data.object as Stripe.Invoice;
            if (invoice.subscription) {
                // Logic similar to subscription updates if needed, but often redundant if `subscription.updated` handles it.
                console.log(`Invoice paid for subscription: ${invoice.subscription}`);
            }
            break;
    
          default:
            console.warn(`Unhandled event type: ${event.type}`);
        }
      } catch (error) {
        console.error('Error handling webhook event:', error);
        return new NextResponse('Webhook handler failed', { status: 500 });
      }
    
      return new NextResponse(JSON.stringify({ received: true }), { status: 200 });
    }
    

    How it connects:

    1. Webhook Verification: This route first validates the incoming request using stripe.webhooks.constructEvent and STRIPE_WEBHOOK_SECRET. This is vital for security, ensuring the event genuinely came from Stripe and hasn't been tampered with.
    2. Event Handling: It uses a switch statement to process different Stripe event types:
      • product.created/updated, price.created/updated: These events directly update your products and prices tables in Supabase. This keeps your application's displayed pricing in sync with Stripe.
      • customer.subscription.created/updated/deleted: These are the core events for managing user subscriptions. The handler fetches the supabase_user_id by matching the stripe_customer_id from the webhook with your profiles table. It then upserts (updates or inserts) the subscriptions table in Supabase with the latest status, period dates, etc.
      • checkout.session.completed: This event is primarily used to ensure the stripe_customer_id is linked to the supabase_user_id in your profiles table if it wasn't already.
    3. Supabase Admin Client: It uses createAdminClient() for all database modifications. This client has the SUPABASE_SERVICE_ROLE_KEY and bypasses RLS, ensuring that the webhook can update any user's data regardless of the current user's session.
    4. Idempotency: The upsert operations in Supabase make the webhook handler idempotent. If Stripe retries sending an event (which it often does), processing the same event multiple times won't cause data corruption.
    5. Response to Stripe: A 200 OK response is crucial to acknowledge receipt of the webhook event, preventing Stripe from continually retrying.

    8. Backend: Stripe Customer Portal Session

    app/api/create-customer-portal-session/route.ts (or pages/api/create-customer-portal-session.ts for Pages Router):

    import { NextRequest, NextResponse } from 'next/server';
    import { stripe } from '@/lib/stripe';
    import { createSupabaseServerClient } from '@/lib/supabase/server';
    
    export async function POST(req: NextRequest) {
      if (req.method !== 'POST') {
        return new NextResponse('Method Not Allowed', { status: 405 });
      }
    
      try {
        const supabase = createSupabaseServerClient();
        const { data: { user }, error: userError } = await supabase.auth.getUser();
    
        if (userError || !user) {
          return NextResponse.json({ error: 'User not authenticated' }, { status: 401 });
        }
    
        const { data: profile, error: profileError } = await supabase
          .from('profiles')
          .select('stripe_customer_id')
          .eq('id', user.id)
          .single();
    
        if (profileError || !profile?.stripe_customer_id) {
          return NextResponse.json({ error: 'Stripe customer ID not found for user' }, { status: 404 });
        }
    
        const session = await stripe.billingPortal.sessions.create({
          customer: profile.stripe_customer_id,
          return_url: `${req.nextUrl.origin}/account`, // Where to redirect after portal
        });
    
        return NextResponse.json({ url: session.url });
      } catch (err: any) {
        console.error('Error creating customer portal session:', err);
        return NextResponse.json({ error: err.message }, { status: 500 });
      }
    }
    

    How it connects:

    1. Authentication & Customer ID: Similar to checkout, it authenticates the user and fetches their stripe_customer_id from the Supabase profiles table.
    2. Stripe Billing Portal Session: It uses stripe.billingPortal.sessions.create() with the retrieved customer ID and a return_url for when the user is done in the portal.
    3. Response to Frontend: It returns the session.url to the frontend.

    Frontend app/account/page.tsx (Example component to link to portal):

    'use client';
    
    import { useRouter } from 'next/navigation';
    import { useState } from 'react';
    
    export default function AccountPage() {
      const router = useRouter();
      const [loading, setLoading] = useState(false);
    
      const handleManageSubscription = async () => {
        setLoading(true);
        try {
          const response = await fetch('/api/create-customer-portal-session', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
          });
          const { url, error } = await response.json();
    
          if (error) {
            alert(`Error: ${error}`);
            console.error(error);
          } else if (url) {
            router.push(url); // Redirect to Stripe Customer Portal
          }
        } catch (error) {
          console.error('Failed to create customer portal session:', error);
          alert('Failed to manage subscription. Please try again.');
        } finally {
          setLoading(false);
        }
      };
    
      return (
        <div className="container mx-auto p-4">
          <h1 className="text-3xl font-bold mb-6">Account Settings</h1>
          <button
            onClick={handleManageSubscription}
            className="px-6 py-2 bg-green-600 text-white rounded-md hover:bg-green-700"
            disabled={loading}
          >
            Manage Subscription
          </button>
          {/* Other account settings */}
        </div>
      );
    }
    

    How it connects:

    1. API Call: The "Manage Subscription" button triggers a client-side call to /api/create-customer-portal-session.
    2. Redirection to Stripe: The API route responds with the Stripe Customer Portal URL. The frontend then redirects the user to this URL, allowing them to manage their subscription directly with Stripe. This offloads significant UI and logic complexity from your application.

    Important Considerations for Senior-Level Design:

    • Error Logging & Monitoring: Implement robust logging for all API routes, especially webhooks, to external services like Datadog, Sentry, or plain files. Monitor webhook delivery failures in Stripe Dashboard.
    • Idempotency: While the Supabase upsert handles basic idempotency for webhooks, consider more complex scenarios if your application logic involves multiple steps or external services. Stripe provides an idempotency_key for API requests.
    • Security:
      • Environment Variables: Always use environment variables for sensitive keys (STRIPE_SECRET_KEY, SUPABASE_SERVICE_ROLE_KEY, STRIPE_WEBHOOK_SECRET). Never hardcode them.
      • Webhook Signing: Never skip Stripe webhook signature verification.
      • Rate Limiting: Implement rate limiting on your API routes if exposed to prevent abuse.
      • Input Validation: Always validate input coming from the client.
      • Supabase RLS: Crucial for protecting user data in Supabase at the database level.
    • Testing:
      • Unit Tests: For your API routes and utility functions.
      • Integration Tests: Simulate the full flow from frontend to Stripe to webhook to Supabase.
      • Stripe CLI: Use the Stripe CLI (stripe listen --forward-to localhost:3000/api/webhooks) for local webhook testing.
    • Scalability: Next.js API routes are serverless functions, which scale automatically. Supabase scales well with PostgreSQL. Stripe handles its own scaling.
    • Typescript: Using a typed Supabase client (Database type generated by Supabase CLI supabase gen types typescript --schema public > lib/database.types.ts) significantly improves code quality and reduces runtime errors.
    • User Experience: Clear success/error messages, proper loading states, and intuitive redirects are key.
    • Stripe Syncing: Consider a mechanism to periodically sync all products, prices, and subscriptions from Stripe to Supabase, especially if you modify them manually in the Stripe Dashboard, to ensure your Supabase data is always accurate. While webhooks handle real-time updates, a periodic full sync can catch anything missed.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.