Multi-Tenant SaaS Foundation. Postgres + RLS. Done properly.

The schema shape

Every domain table includes a `tenant_id` column with a foreign key to `tenants(id)`. Every domain table has RLS enabled with a policy: rows are visible if `tenant_id` equals the JWT’s active tenant.

CREATE TABLE tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  plan text NOT NULL DEFAULT 'free',
  stripe_customer_id text UNIQUE,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE memberships (
  user_id uuid REFERENCES auth.users(id),
  tenant_id uuid REFERENCES tenants(id),
  role text NOT NULL CHECK (role IN ('owner','admin','member')),
  PRIMARY KEY (user_id, tenant_id)
);

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 DEFAULT now()
);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

The active-tenant claim

Users may belong to multiple tenants. The active tenant lives in the JWT as a custom claim, set when the user picks a tenant in the UI. RLS policies read this claim.

-- Helper: read active tenant from JWT
CREATE FUNCTION app.active_tenant() RETURNS uuid AS $$
  SELECT (auth.jwt() -> 'app_metadata' ->> 'active_tenant_id')::uuid
$$ LANGUAGE sql STABLE;

-- Tenant isolation policy
CREATE POLICY "tenant_isolation"
  ON projects FOR SELECT
  USING (tenant_id = app.active_tenant());

CREATE POLICY "members_only_writes"
  ON projects FOR INSERT WITH CHECK (
    tenant_id = app.active_tenant() AND
    EXISTS (
      SELECT 1 FROM memberships m
      WHERE m.user_id = auth.uid()
        AND m.tenant_id = app.active_tenant()
    )
  );

Billing integration

Each tenant has exactly one Stripe customer. Subscriptions drive the `plan` column on the tenant row via webhook. Feature gates read the plan and enforce limits.

Always read the live `plan` from Postgres, not from a cached value. Stripe’s webhook is the source of truth for subscription state, but Postgres is the source of truth for what your application sees.

// app/api/stripe/webhook/route.ts (excerpt)
case 'customer.subscription.updated':
case 'customer.subscription.deleted': {
  const sub = event.data.object
  const customerId = sub.customer as string
  const plan = mapPriceToPlan(sub.items.data[0].price.id, sub.status)
  await db.update(tenants).set({ plan }).where(eq(tenants.stripeCustomerId, customerId))
  break
}

Feature gates

Plan-aware checks live in a single `canUse(feature, tenant)` function. Every feature gate in the UI and the API calls this. The function reads the tenant’s plan and returns boolean + reason.

Critical limits (number of projects, storage usage) should be enforced in RLS as well, so a buggy frontend cannot bypass them. Cosmetic gates (showing or hiding a UI element) are fine in app layer alone.

The admin surface

Internal staff need to be able to see across tenants — for support, debugging, refunds. The clean way is a separate sub-app routed under `/admin/*`, authenticated by a separate role and using the Supabase service role key (which bypasses RLS).

Treat the admin surface as a different application. Different auth path, different middleware, different audit log. Every admin action gets logged with the operator’s identity. Never let regular sessions grant admin access.

When to migrate off this

You will know. A single tenant’s queries start dominating the database. A regulated customer demands physical isolation. Cross-tenant analytics become so expensive they need a separate warehouse. None of these have hit me on this pattern below the “low thousands of tenants” mark — and by then you have the revenue to do the migration well.