SaaS that bills, scales and stays up, built by one senior developer.

migrations/002_projects.sqlsql

create table projects (
  id         uuid primary key default gen_random_uuid(),
  tenant_id  uuid not null references tenants (id),
  name       text not null,
  created_at timestamptz not null default now()
);
create index on projects (tenant_id, created_at desc);

alter table projects enable row level security;
alter table projects force row level security; -- the table owner too

-- current_setting() errors when unset, so a missing tenant fails closed.
create policy tenant_isolation on projects
  using (tenant_id = current_setting('app.tenant_id')::uuid)
  with check (tenant_id = current_setting('app.tenant_id')::uuid);

Why:Even if a query in the app forgets its WHERE clause, PostgreSQL won't return another tenant's rows. If no tenant is set, the query fails instead of returning everything.

src/db/tenant.tsts

import type { PoolClient } from 'pg';
import { pool } from './pool';

// Every query for a request runs in one transaction scoped to its tenant.
export async function withTenant<T>(tenantId: string, work: (db: PoolClient) => Promise<T>) {
  const db = await pool.connect();
  try {
    await db.query('begin');
    await db.query("select set_config('app.tenant_id', $1, true)", [tenantId]); // true = this transaction only
    const result = await work(db);
    await db.query('commit');
    return result;
  } catch (err) {
    await db.query('rollback');
    throw err;
  } finally {
    db.release();
  }
}

// In a route: no tenant filter to forget.
app.get('/projects', requireAuth, async (req, res) => {
  const { rows } = await withTenant(req.user.tenantId, (db) =>
    db.query('select id, name from projects order by created_at desc'),
  );
  res.json(rows);
});

Why:The tenant comes from the verified session, never from the request body. The setting lasts only for the transaction, so a pooled connection can't carry it into the next request.

src/billing/webhook.tsts

import express from 'express';
import Stripe from 'stripe';
import { pool } from '../db/pool';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!);
export const webhook = express.Router();

// Raw body: the signature covers the exact bytes Stripe sent.
webhook.post('/stripe', express.raw({ type: 'application/json' }), async (req, res) => {
  let event: Stripe.Event;
  try {
    event = stripe.webhooks.constructEvent(req.body, req.headers['stripe-signature']!, process.env.STRIPE_WEBHOOK_SECRET!);
  } catch {
    return res.status(400).send('Bad signature');
  }

  const db = await pool.connect();
  try {
    await db.query('begin');
    // A retried event inserts nothing, so it changes nothing.
    const { rowCount } = await db.query('insert into stripe_events (id) values ($1) on conflict do nothing', [event.id]);
    if (rowCount && event.type.startsWith('customer.subscription.')) {
      const sub = event.data.object as Stripe.Subscription;
      await db.query('update tenants set plan_status = $1 where stripe_customer_id = $2', [sub.status, sub.customer]);
    }
    await db.query('commit');
  } catch {
    await db.query('rollback');
    return res.sendStatus(500); // Stripe retries later
  } finally {
    db.release();
  }
  res.sendStatus(200);
});

Why:Stripe signs the raw request body and retries failed deliveries, so the handler checks the signature first and records each event id once, in the same transaction as the change it makes.

test/tenant-isolation.test.tsts

import { test } from 'node:test';
import assert from 'node:assert/strict';
import { withTenant } from '../src/db/tenant';
import { seedTenant } from './helpers';

test("a tenant never sees or writes another tenant's rows", async () => {
  const a = await seedTenant({ projects: ['Alpha'] });
  const b = await seedTenant({ projects: ['Bravo'] });

  const { rows } = await withTenant(a.id, (db) => db.query('select name from projects'));
  assert.deepEqual(rows.map((r) => r.name), ['Alpha']);

  await assert.rejects(
    withTenant(a.id, (db) =>
      db.query('insert into projects (tenant_id, name) values ($1, $2)', [b.id, 'Sneaky']),
    ),
    /row-level security/,
  );
});

Why:The test runs as the same non-superuser role the app uses, because superusers skip row-level security. If anyone weakens the policy, this fails before it ships.