Nine concrete steps from Stripe products to a webhook-driven, RLS-protected subscription state in your Supabase Postgres — with TypeScript, SQL, and Stripe CLI examples.
Methodology. Code follows stripe.com/docs/billing for subscription patterns and the Supabase docs for database setup. The schema is similar to the Supabase Stripe sync extension but written by hand to keep the contract explicit. For a side-by-side of payment processors see Lemon Squeezy vs Stripe.
Stripe Billing is the de facto subscription engine for SaaS. Wiring it correctly to a Supabase Postgres database is the first “real” backend integration most solo founders ship, and it has more pitfalls than the docs let on. The pattern below is what works: Stripe owns the source of truth for billing, your database mirrors it via webhooks, and your app reads from the database (never from Stripe at request time).
In the Stripe dashboard (test mode for now), go to Product catalog → Add product. Create a product per plan tier (e.g. “Starter,” “Pro,” “Business”). For each, add at least one recurring price.
Save the price IDs — they look like price_1Q4xY8... and are what your client passes to checkout. Most teams put them in env vars so test and live can be swapped:
NEXT_PUBLIC_STRIPE_PRICE_STARTER=price_1Q4xY8... NEXT_PUBLIC_STRIPE_PRICE_PRO=price_1Q4xZ2...
Install the SDK and create a typed singleton. Avoid creating a new Stripe instance per request — the constructor is non-trivial.
npm install stripe @stripe/stripe-js
// src/lib/stripe.ts
import Stripe from 'stripe';
if (!process.env.STRIPE_SECRET_KEY) {
throw new Error('STRIPE_SECRET_KEY missing');
}
export const stripe = new Stripe(process.env.STRIPE_SECRET_KEY, {
apiVersion: '2024-12-18.acacia',
typescript: true,
appInfo: { name: 'YourSaaS', version: '1.0.0' }
});
The apiVersion string pins the API behavior — never let it default. Pinning means a Stripe API change won’t silently change your app.
You need three tables: a customers mapping (Supabase user id → Stripe customer id), a subscriptions mirror, and a prices reference. The schema below is a hand-written variant of the official Stripe sync schema.
create table public.customers (
id uuid primary key references auth.users(id) on delete cascade,
stripe_customer_id text unique not null,
created_at timestamptz default now()
);
create table public.prices (
id text primary key, -- Stripe price id
product_id text not null,
active boolean default true,
unit_amount integer not null,
currency text not null,
interval text not null check (interval in ('day','week','month','year')),
interval_count integer not null default 1,
metadata jsonb
);
create table public.subscriptions (
id text primary key, -- Stripe subscription id
user_id uuid not null references auth.users(id) on delete cascade,
status text not null, -- active, trialing, past_due, canceled, etc
price_id text references public.prices(id),
quantity integer default 1,
cancel_at_period_end boolean default false,
current_period_start timestamptz,
current_period_end timestamptz,
cancel_at timestamptz,
canceled_at timestamptz,
trial_start timestamptz,
trial_end timestamptz,
metadata jsonb,
created_at timestamptz default now()
);
create index on public.subscriptions (user_id);
create index on public.subscriptions (status);
The frontend hits a server route that creates a Stripe Checkout Session and returns its URL; the browser redirects to that URL. Never call checkout.sessions.create from the client — that requires the secret key. For a primer on this flow see what is Stripe Checkout.
// src/app/api/stripe/checkout/route.ts
import { NextResponse } from 'next/server';
import { stripe } from '@/lib/stripe';
import { createClient } from '@/lib/supabase/server';
export async function POST(req: Request) {
const supabase = createClient();
const { data: { user } } = await supabase.auth.getUser();
if (!user) return NextResponse.json({ error: 'unauthorized' }, { status: 401 });
const { priceId } = await req.json();
// find or create the Stripe customer
let { data: customer } = await supabase
.from('customers')
.select('stripe_customer_id')
.eq('id', user.id)
.maybeSingle();
if (!customer) {
const created = await stripe.customers.create({
email: user.email!,
metadata: { supabase_user_id: user.id }
});
await supabase.from('customers').insert({
id: user.id,
stripe_customer_id: created.id
});
customer = { stripe_customer_id: created.id };
}
const session = await stripe.checkout.sessions.create({
mode: 'subscription',
customer: customer.stripe_customer_id,
line_items: [{ price: priceId, quantity: 1 }],
success_url: `${process.env.NEXT_PUBLIC_SITE_URL}/dashboard?checkout=success`,
cancel_url: `${process.env.NEXT_PUBLIC_SITE_URL}/pricing?checkout=cancelled`,
allow_promotion_codes: true,
billing_address_collection: 'auto'
});
return NextResponse.json({ url: session.url });
}
Stripe’s billing portal lets users manage their subscription, payment method, and invoices — without you building any of those screens. Configure it once in Settings → Billing → Customer portal, then create a session per user.
// src/app/api/stripe/portal/route.ts
import { NextResponse } from 'next/server';
import { stripe } from '@/lib/stripe';
import { createClient } from '@/lib/supabase/server';
export async function POST() {
const supabase = createClient();
const { data: { user } } = await supabase.auth.getUser();
if (!user) return NextResponse.json({ error: 'unauthorized' }, { status: 401 });
const { data: customer } = await supabase
.from('customers')
.select('stripe_customer_id')
.eq('id', user.id)
.single();
const portal = await stripe.billingPortal.sessions.create({
customer: customer.stripe_customer_id,
return_url: `${process.env.NEXT_PUBLIC_SITE_URL}/dashboard`
});
return NextResponse.json({ url: portal.url });
}
The webhook is where Stripe tells your app what happened. Two non-negotiables: read the raw body (not parsed JSON) before signature verification, and never trust the payload before verifying. For deeper background on why, read what is a webhook.
// src/app/api/stripe/webhook/route.ts
import { NextResponse } from 'next/server';
import { headers } from 'next/headers';
import { stripe } from '@/lib/stripe';
import { handleStripeEvent } from '@/lib/stripe-events';
export const runtime = 'nodejs'; // signature lib needs Node, not Edge
export async function POST(req: Request) {
const sig = headers().get('stripe-signature');
const body = await req.text(); // RAW body, not json
let event;
try {
event = stripe.webhooks.constructEvent(
body,
sig!,
process.env.STRIPE_WEBHOOK_SECRET!
);
} catch (err: any) {
console.error('Webhook signature failed:', err.message);
return NextResponse.json({ error: 'invalid signature' }, { status: 400 });
}
try {
await handleStripeEvent(event);
return NextResponse.json({ received: true });
} catch (err) {
console.error('Webhook handler error', err);
return NextResponse.json({ error: 'handler error' }, { status: 500 });
}
}
Five events cover the entire subscription lifecycle. Handle them, ignore the rest. Use the Supabase service role client — webhook handlers act as “the system” and need to bypass RLS.
// src/lib/stripe-events.ts
import type Stripe from 'stripe';
import { createClient } from '@supabase/supabase-js';
const admin = createClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.SUPABASE_SERVICE_ROLE_KEY!,
{ auth: { persistSession: false } }
);
export async function handleStripeEvent(event: Stripe.Event) {
switch (event.type) {
case 'customer.created': {
const c = event.data.object as Stripe.Customer;
const supabaseId = c.metadata?.supabase_user_id;
if (!supabaseId) return;
await admin.from('customers').upsert({
id: supabaseId, stripe_customer_id: c.id
});
break;
}
case 'customer.subscription.created':
case 'customer.subscription.updated':
case 'customer.subscription.deleted': {
const s = event.data.object as Stripe.Subscription;
const { data: customer } = await admin
.from('customers')
.select('id')
.eq('stripe_customer_id', s.customer as string)
.single();
if (!customer) return;
await admin.from('subscriptions').upsert({
id: s.id,
user_id: customer.id,
status: s.status,
price_id: s.items.data[0]?.price.id,
quantity: s.items.data[0]?.quantity ?? 1,
cancel_at_period_end: s.cancel_at_period_end,
current_period_start: new Date(s.current_period_start * 1000).toISOString(),
current_period_end: new Date(s.current_period_end * 1000).toISOString(),
cancel_at: s.cancel_at ? new Date(s.cancel_at * 1000).toISOString() : null,
canceled_at: s.canceled_at ? new Date(s.canceled_at * 1000).toISOString() : null,
trial_start: s.trial_start ? new Date(s.trial_start * 1000).toISOString() : null,
trial_end: s.trial_end ? new Date(s.trial_end * 1000).toISOString() : null,
metadata: s.metadata
});
break;
}
case 'invoice.paid': {
// optionally log invoice for receipts/audit
break;
}
default:
// ignore
}
}
Use upsert, not insert. Webhooks can arrive out of order or be retried — your handler must be idempotent. The Stripe subscription id is the natural primary key.
Lock down both customers and subscriptions so users can only read their own state. Webhook writes go through the service role, which bypasses RLS — so SELECT-only policies for authenticated users are enough.
alter table public.customers enable row level security; alter table public.subscriptions enable row level security; alter table public.prices enable row level security; create policy "select_own_customer" on public.customers for select to authenticated using (id = auth.uid()); create policy "select_own_subscription" on public.subscriptions for select to authenticated using (user_id = auth.uid()); -- prices are public reference data create policy "select_prices_public" on public.prices for select to anon, authenticated using (active = true);
Note there are no INSERT/UPDATE/DELETE policies for these tables — clients should never modify billing state directly. For the deeper RLS pattern, see how to set up Supabase RLS.
The Stripe CLI forwards real webhook events from your test-mode account to your local dev server. This is how you debug the webhook handler before deploying.
# install once brew install stripe/stripe-cli/stripe # log in stripe login # forward webhooks to your local dev server stripe listen --forward-to localhost:3000/api/stripe/webhook
The first run prints a webhook signing secret like whsec_abc.... Set that as STRIPE_WEBHOOK_SECRET in your .env.local. Then trigger events from another terminal:
stripe trigger customer.subscription.created stripe trigger customer.subscription.updated stripe trigger invoice.paid
Check that each event lands in the subscriptions table with the right shape. Then sign up as a real user, click your “Subscribe” button, and walk through the test card 4242 4242 4242 4242. The flow you should observe: redirect to Stripe Checkout → pay → redirect back to dashboard → webhook arrives → row appears in subscriptions with status = 'active'.
For the production cutover, swap to the live secret key, rotate the webhook secret in production env vars, and add the production webhook endpoint URL to Stripe → Developers → Webhooks. For pricing comparisons before committing to Stripe, see Lemon Squeezy vs Stripe. For an invoicing-specific build, see how to build an invoicing SaaS with Claude.
Stripe + Supabase is straightforward once you accept one rule: Stripe is the source of truth for billing, your database mirrors it via webhooks, and your app reads only from the database. Get those three roles right and the rest follows.
The stack, prompts, pricing, and mistakes to avoid — for solo founders building with AI.