WebuildonSupabasebecausethesecuritymodelisreal

Most "multi-tenant SaaS on Supabase" tutorials show you how to add a tenant_id column and pretend that solves authorisation. It does not. The pattern below is the one we ship to production: a members join table that owns the tenant–user relationship, plus row-level security policies that delegate every authorisation check to it.

1. The shape of the tables

A user belongs to many tenants through a members row. Each members row carries the role. Every other table that needs tenant scoping has a tenant_id and trusts the policy to enforce it.

-- supabase/migrations/0001_tenants.sql
create table tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  stripe_customer_id text unique,
  created_at timestamptz not null default now()
);

create type member_role as enum ('owner', 'admin', 'member', 'billing');

create table members (
  tenant_id uuid not null references tenants(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role member_role not null,
  joined_at timestamptz not null default now(),
  primary key (tenant_id, user_id)
);

create index members_user_idx on members(user_id);

create table tickets (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id) on delete cascade,
  title text not null,
  body text,
  status text not null default 'open',
  assignee_id uuid references auth.users(id),
  created_at timestamptz not null default now()
);

create index tickets_tenant_idx on tickets(tenant_id, status, created_at desc);

2. The row-level security policies

Policies enable RLS on the table and define what is readable, writable and updatable. Every policy delegates to a subquery against members so a single source of truth controls every check.

-- supabase/migrations/0002_tickets_rls.sql
alter table tickets enable row level security;

create policy "tickets: tenant members can read"
on tickets for select
using (
  tenant_id in (select tenant_id from members where user_id = auth.uid())
);

create policy "tickets: tenant members can insert"
on tickets for insert
with check (
  tenant_id in (select tenant_id from members where user_id = auth.uid())
);

create policy "tickets: admins can update"
on tickets for update
using (
  tenant_id in (
    select tenant_id from members
    where user_id = auth.uid() and role in ('owner', 'admin')
  )
);

create policy "tickets: only owners can delete"
on tickets for delete
using (
  tenant_id in (
    select tenant_id from members
    where user_id = auth.uid() and role = 'owner'
  )
);

3. The Next.js Server Component reading the data

The application code stops carrying authorisation. The client (here a Next.js Server Component) reads tickets as if the user could see everything; the database tells the truth.

// app/[lang]/(app)/tickets/page.tsx
import { createServerClient } from '@/lib/supabase/server'

export default async function TicketsPage() {
  const supabase = await createServerClient()
  const { data: tickets, error } = await supabase
    .from('tickets')
    .select('id, title, status, created_at')
    .eq('status', 'open')
    .order('created_at', { ascending: false })

  if (error) throw error
  if (!tickets) return null

  return (
    <ul>
      {tickets.map((t) => (
        <li key={t.id}>
          <a href={`/tickets/${t.id}`}>{t.title}</a>
        </li>
      ))}
    </ul>
  )
}

4. The generated types

We run supabase gen types typescript --linked > src/types/db.ts after every migration. The generated file is committed; the CI fails if it drifts from the deployed schema. The tickets row becomes a Tables<'tickets'> type the application uses everywhere.

// src/lib/supabase/server.ts
import { cookies } from 'next/headers'
import { createServerClient as createSupabaseServerClient } from '@supabase/ssr'
import type { Database } from '@/types/db'

export async function createServerClient() {
  const cookieStore = await cookies()
  return createSupabaseServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll: () => cookieStore.getAll(),
        setAll: (toSet) => {
          for (const { name, value, options } of toSet) {
            cookieStore.set(name, value, options)
          }
        },
      },
    },
  )
}

5. What this buys you

A new column in tickets, a new tenant role, a new policy: each one is a single SQL migration that goes through review. The authorisation document the next enterprise buyer asks for is a screenshot of the policies file. The next team that opens the codebase reads the same SQL we read, not a custom abstraction layer.

Multi-tenant security stops being a feature you ship; it becomes a property of the database.