Multi-tenancy: tenant id on every row, tenant list in the JWT

The answer. Shared database, shared code, and a tenant column on every row that belongs to a tenant. The pieces: never add columns to auth.users (the auth schema is Supabase's, and upgrades assume it); keep your own profiles table joined by a not null unique foreign key with on delete cascade; model membership as a join table between profiles and tenants so a person can belong to several; and stamp the tenants a user belongs to into raw_app_meta_data (surfaced as app_metadata in the JWT), which only the admin API or a hook can write, unlike user_metadata, which the user edits freely. Then every tenant table gets the same policy: does the row's tenant appear in the caller's claim. No joins, one index, readable at a glance. Our product uses the same skeleton with an active_org_id claim chosen per session instead of a list.

The pattern.

create table public.profiles (
  id bigint generated always as identity primary key,
  user_id uuid not null unique references auth.users (id) on update cascade on delete cascade,
  full_name text
);
create table public.memberships (
  profile_id bigint not null references public.profiles (id) on delete cascade,
  tenant text not null references public.tenants (id) on delete cascade,
  role text not null default 'member', primary key (profile_id, tenant)
);
create policy "tenant members read" on public.tickets for select to authenticated
  using (coalesce(((select auth.jwt())->'app_metadata'->'tenants') ? tickets.tenant, false));
create index on public.tickets (tenant, created_at desc);

auth.users.raw_app_meta_data = {"tenants": ["packt", "oddmonkey"]}   -- admin.updateUserById or the token hook

Watch out.

Claims are stale until refresh, so removal from a tenant needs the membership row deleted too, and sensitive mutations re-check that row.
Child tables (comments on a ticket) should carry the tenant column, set by a trigger from the parent, so their policies stay one line.
Human-readable tenant ids (packt) make URLs, logs and policies legible; keep them immutable, since they are baked into claims.