how do i connect stripe with supabase at a senior level design, using next js, first…
- 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...
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.
- Utilize Stripe Webhooks to listen for critical subscription events (e.g.,
- 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:
-
Database Setup (Supabase):
- Define
profilestable to storestripe_customer_idalongside Supabaseauth.usersID. - Define
products,prices, andsubscriptionstables to mirror essential Stripe data for application-level queries. - Set up Supabase Auth trigger to create a
profileentry for new users. - Implement Row Level Security (RLS) for subscription data.
- Define
-
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 forcheckout.session.completed,customer.subscription.created,customer.subscription.updated,customer.subscription.deleted,invoice.payment_succeeded. - Generate a Webhook Signing Secret.
-
Frontend (Next.js - Pricing Page):
- Fetch
productsandpricesfrom 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 chosenpriceId.
- Make a client-side API call to a Next.js API route (e.g.,
- Fetch
-
Backend (Next.js API Route -
/api/create-checkout-session):- Authenticate the user using Supabase server-side client to get
user_id. - Retrieve
stripe_customer_idfrom the Supabaseprofilestable for the authenticated user. If not found, create a new Stripe Customer and store itsidinprofiles. - Use the Stripe Node.js SDK to create a
checkout.Sessionwith thepriceId,stripe_customer_id, andsuccess_url/cancel_urlpointing back to your app. - Return the
sessionId(orurl) to the frontend.
- Authenticate the user using Supabase server-side client to get
-
Frontend (Next.js - Post-Checkout):
- Upon receiving the
sessionId, redirect the user to Stripe's hosted checkout page usingstripe.redirectToCheckout(). - Handle redirects from Stripe to your
success_urlandcancel_url(e.g.,/successor/cancelpages).
- Upon receiving the
-
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
switchstatement or similar logic to handle different event types:checkout.session.completed: If it's a subscription session, create/updatecustomer_idinprofilesandsubscriptioninsubscriptions.customer.subscription.created,updated,deleted: Update thesubscriptionstable.invoice.payment_succeeded: Ensuresubscriptionsstatus is active.
- Use the Supabase Admin client to perform database updates (bypassing RLS for server-side operations).
- Return a
200 OKresponse to Stripe.
-
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).
-
Backend (Next.js API Route -
/api/create-customer-portal-session):- Authenticate the user and retrieve their
stripe_customer_idfrom Supabase. - Use the Stripe Node.js SDK to create a
billingPortal.Sessionwith thecustomer_idandreturn_url. - Return the portal
urlto the frontend.
- Authenticate the user and retrieve their
-
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_urlwhen 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
profilesandsubscriptionstables) 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
profilestable is where we'll link our Supabase user IDs to their respective Stripe Customer IDs. Theproducts,prices, andsubscriptionstables 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_idinsubscriptionsdirectly links toauth.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
profileentry.-- 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
profileentry in ourprofilestable whenever a new user signs up via Supabase Auth. This is critical because theprofiletable will store thestripe_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 theSUPABASE_SERVICE_ROLE_KEYwhich 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 thestripe_customer_idfor 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_KEYis a sensitive environment variable.
5. Frontend: Pricing Page and Checkout Initiation
app/pricing/page.tsx(orpages/pricing.tsxfor 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:
- Data Fetching: The
useEffecthook fetchesproductsand their associatedpricesfrom your Supabase database. This data is what you synced from Stripe via webhooks, ensuring your application displays up-to-date pricing. - 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. - API Call to Backend: When "Subscribe" is clicked,
handleCheckoutmakes afetchrequest to your Next.js API route/api/create-checkout-session. This is a POST request sending thepriceId. - 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(orpages/api/create-checkout-session.tsfor 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:
- Authentication & User ID: It uses
createSupabaseServerClient()to get the authenticated user's session anduser.id. This ensures the checkout session is tied to a specific user. - Stripe Customer ID Management: It queries the Supabase
profilestable to see if the user already has astripe_customer_id. If not, it creates a new Stripe Customer and updates the Supabaseprofilestable with this new ID. This avoids creating duplicate Stripe customers for the same user. - Stripe Checkout Session Creation: It uses the
stripeobject (initialized inlib/stripe.ts) to callstripe.checkout.sessions.create(). It includes thecustomerId, thepriceIdfrom the frontend, andsuccess_url/cancel_urlpointing back to your application. Themetadatafield is crucial for passingsupabase_user_idto the webhook later. - Response to Frontend: It returns the
session.urlback 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(orpages/api/webhooks.tsfor 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:
- Webhook Verification: This route first validates the incoming request using
stripe.webhooks.constructEventandSTRIPE_WEBHOOK_SECRET. This is vital for security, ensuring the event genuinely came from Stripe and hasn't been tampered with. - Event Handling: It uses a
switchstatement to process different Stripe event types:product.created/updated,price.created/updated: These events directly update yourproductsandpricestables 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 thesupabase_user_idby matching thestripe_customer_idfrom the webhook with yourprofilestable. It thenupserts (updates or inserts) thesubscriptionstable in Supabase with the latest status, period dates, etc.checkout.session.completed: This event is primarily used to ensure thestripe_customer_idis linked to thesupabase_user_idin yourprofilestable if it wasn't already.
- Supabase Admin Client: It uses
createAdminClient()for all database modifications. This client has theSUPABASE_SERVICE_ROLE_KEYand bypasses RLS, ensuring that the webhook can update any user's data regardless of the current user's session. - Idempotency: The
upsertoperations 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. - Response to Stripe: A
200 OKresponse 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(orpages/api/create-customer-portal-session.tsfor 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:
- Authentication & Customer ID: Similar to checkout, it authenticates the user and fetches their
stripe_customer_idfrom the Supabaseprofilestable. - Stripe Billing Portal Session: It uses
stripe.billingPortal.sessions.create()with the retrievedcustomerID and areturn_urlfor when the user is done in the portal. - Response to Frontend: It returns the
session.urlto 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:
- API Call: The "Manage Subscription" button triggers a client-side call to
/api/create-customer-portal-session. - 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
upserthandles basic idempotency for webhooks, consider more complex scenarios if your application logic involves multiple steps or external services. Stripe provides anidempotency_keyfor 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.
- Environment Variables: Always use environment variables for sensitive keys (
- 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 (
Databasetype generated by Supabase CLIsupabase 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.