← All case studies · McKeen Asma · Portfolio

Case study · Full-stack SaaS · Next.js + Postgres RLS + Stripe

SaaS billing starter: tenants, roles and Stripe billing enforced in the database

A tested starting point for a multi-tenant SaaS on Next.js + Postgres: orgs and member roles enforced by row-level security, plus a signed, idempotent, order-safe Stripe subscription webhook.

The 63-second demo: the README, the architecture, the RLS policies in db/schema.sql, the signed + idempotent webhook and out-of-order guard in lib/stripe-webhook.ts, and the 12 tests passing. No audio; captions on screen.
12 / 12offline tests pass: tenant isolation, roles, billing, webhooks
3 rolesowner, admin, member, enforced by Postgres RLS
1×each Stripe event applied once, in one transaction with its record

The problem it solves

Every SaaS needs the same two hard parts, and both fail quietly: one customer seeing another customer's data, and a billing table that drifts from Stripe because a webhook arrived twice, out of order, or failed half-way.

This repo puts the rules where they cannot be skipped. Row-level security decides who sees and changes what, so the rules hold for every client, not just the code path someone remembered to check. The webhook verifies the signature, records each event id once, applies the record and the change in one transaction, and ignores older events that arrive late. A cancelled org keeps read access but cannot create new projects.

How it fits together

Flowchart: a signed-in user reaches Postgres through RLS policies (org_role, org_has_plan) that guard orgs, members and projects; Stripe Checkout carries the org id to Stripe; Stripe sends a signed webhook to /api/stripe/webhook, which verifies the signature, inserts the event id with on-conflict-skip into stripe_events, and upserts subscriptions only if newer; RLS reads subscriptions for the billing gate
The architecture diagram from the README (Mermaid, rendered to SVG). Tap to open it full size.
  • Tenants: orgs own everything; users join through members as owner, admin or member.
  • Roles in the database: owners and admins invite, only the owner changes roles, nobody can mint a second owner or delete the owner, and one org never sees another's rows.
  • Billing: POST /api/stripe/webhook keeps one subscriptions row per org in sync with Stripe.
  • Billing gate: RLS only lets an org create projects while its subscription is trialing or active.

The key code

Two excerpts. First the RLS policies in db/schema.sql, then the webhook core in lib/stripe-webhook.ts: the event id is inserted with on conflict do nothing inside the same transaction that applies it.

create policy members_read on members for select using (org_role(org_id) is not null);
-- owners/admins invite; nobody can hand out 'owner' through an insert
create policy members_add on members for insert
  with check (org_role(org_id) in ('owner', 'admin') and role <> 'owner');
-- only the owner changes roles
create policy members_change_role on members for update
  using (org_role(org_id) = 'owner') with check (org_role(org_id) = 'owner');
-- owners/admins remove people, but the owner row cannot be removed
create policy members_remove on members for delete
  using (org_role(org_id) in ('owner', 'admin') and role <> 'owner');

create policy subscriptions_read on subscriptions for select using (org_role(org_id) is not null);

create policy projects_read on projects for select using (org_role(org_id) is not null);
-- billing gate in the database: creating a project needs a trialing/active subscription
create policy projects_create on projects for insert
  with check (org_role(org_id) is not null and org_has_plan(org_id));

// lib/stripe-webhook.ts
  let event: Stripe.Event;
  try {
    event = deps.stripe.webhooks.constructEvent(rawBody, signature ?? "", deps.secret);
  } catch {
    return { status: 400, body: "invalid signature" };
  }

  try {
    return await deps.db.tx(async (q) => {
      const fresh = await q("insert into stripe_events (id, type) values ($1, $2) on conflict (id) do nothing returning id", [event.id, event.type]);
      if (fresh.rows.length === 0) return { status: 200, body: "duplicate" };
      await apply(event, q);
      return { status: 200, body: "ok" };
    });
  } catch (e) {
    console.error("stripe webhook failed", event.id, e);
    return { status: 500, body: "error" };
  }
}
db/schema.sql lines 71–87 and lib/stripe-webhook.ts lines 20–38. The upsert that drops stale events (where subscriptions.stripe_event_created <= excluded.stripe_event_created) is a few lines further down.

Proof: the real test run

Run on 16 Oct 2026 on the published commit with npm test. Fully offline: Postgres is PGlite in memory, and Stripe webhooks are signed locally with Stripe's own test helper.

✔ tenants are isolated: Carol (Globex) sees nothing of Acme
✔ a plain member can read the org but cannot invite people or change roles
✔ owner invites an admin; an admin can invite but cannot create a second owner or change roles
✔ billing gate: projects can only be created while the subscription is active
✔ signed-in users cannot touch billing tables directly
✔ rejects a bad signature and writes nothing
✔ checkout.session.completed links the Stripe customer to the org
✔ subscription events upsert the org's subscription, even before checkout completes
✔ idempotent: the same event delivered twice is applied once
✔ out of order: an older event cannot overwrite a newer one
✔ a failure rolls back the event record so Stripe's retry is processed
✔ checkout params carry the org id to both the session and the subscription
ℹ tests 12
ℹ pass 12
ℹ fail 0
npm test: 12 tests, 12 pass, 0 fail. The first five are RLS tests, the last seven are webhook tests.

Choices and limits

  • RLS, not app-code checks, so the rules hold for every client. The helpers are security definer so policies on members can look up members without recursing.
  • The out-of-order guard assumes one subscription per org; several would key on stripe_subscription_id instead.
  • No UI for sign-in, invites or the billing page: this repo is the data model and the webhook. Supabase Auth would supply auth.uid() in production.
  • Seat limits, proration and invoices are not modelled.
  • An owner can still demote themselves and leave an org without an owner; a real product should block that with a trigger.