The model

A user (managed by Supabase Auth) can belong to many workspaces through the workspace_members join table. Each membership has a role (owner or member). A snippet belongs to exactly one workspace and has a visibility of private, workspace, or public.

Visibility rules:

  • private — only the snippet's created_by user can see it (the workspace is just a home for it).
  • workspace — every member of that workspace can read; writers are owners or the snippet's creator.
  • public — anyone with the server URL who is signed in can read; writers same as workspace.

1. Create the migration

supabase migration new init_snippets_schema

That creates supabase/migrations/<timestamp>_init_snippets_schema.sql. Open it and paste this whole thing:

-- =========================================================================
-- Workspaces
-- =========================================================================
create table public.workspaces (
  id          uuid primary key default gen_random_uuid(),
  name        text not null check (char_length(name) between 1 and 80),
  created_at  timestamptz not null default now(),
  created_by  uuid not null references auth.users(id) on delete restrict
);
 
create index workspaces_created_by_idx on public.workspaces(created_by);
 
-- =========================================================================
-- Workspace members
-- =========================================================================
create type workspace_role as enum ('owner', 'member');
 
create table public.workspace_members (
  workspace_id  uuid not null references public.workspaces(id) on delete cascade,
  user_id       uuid not null references auth.users(id)        on delete cascade,
  role          workspace_role not null default 'member',
  joined_at     timestamptz not null default now(),
  primary key (workspace_id, user_id)
);
 
create index workspace_members_user_id_idx on public.workspace_members(user_id);
 
-- =========================================================================
-- Snippets
-- =========================================================================
create type snippet_visibility as enum ('private', 'workspace', 'public');
 
create table public.snippets (
  id            uuid primary key default gen_random_uuid(),
  workspace_id  uuid not null references public.workspaces(id) on delete cascade,
  created_by    uuid not null references auth.users(id)        on delete restrict,
  title         text not null check (char_length(title) between 1 and 120),
  body          text not null check (char_length(body)  between 1 and 60000),
  tags          text[] not null default array[]::text[],
  visibility    snippet_visibility not null default 'workspace',
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);
 
create index snippets_workspace_id_idx on public.snippets(workspace_id);
create index snippets_created_by_idx   on public.snippets(created_by);
create index snippets_tags_idx         on public.snippets using gin (tags);
 
-- Trigger to keep updated_at fresh on any UPDATE
create or replace function public.touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at := now();
  return new;
end $$;
 
create trigger snippets_touch_updated_at
  before update on public.snippets
  for each row execute function public.touch_updated_at();
 
-- =========================================================================
-- Auto-create a personal workspace + owner membership on signup
-- =========================================================================
create or replace function public.bootstrap_personal_workspace()
returns trigger language plpgsql security definer as $$
declare
  new_ws_id uuid;
begin
  insert into public.workspaces (name, created_by)
  values (coalesce(new.raw_user_meta_data->>'name', new.email, 'Personal'),
          new.id)
  returning id into new_ws_id;
 
  insert into public.workspace_members (workspace_id, user_id, role)
  values (new_ws_id, new.id, 'owner');
 
  return new;
end $$;
 
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.bootstrap_personal_workspace();

A few things to notice:

  • gen_random_uuid() for primary keys — Supabase enables the pgcrypto extension by default.
  • array[]::text[] for tags — Postgres arrays are perfectly fine for a few-tags-per-snippet workload. The GIN index makes tags && array['rag'] queries fast.
  • The signup trigger gives every new user their own personal workspace so they don't land in an empty void on first login.

2. Enable Row-Level Security

The whole point. Append to the same migration:

-- =========================================================================
-- Row-Level Security
-- =========================================================================
alter table public.workspaces        enable row level security;
alter table public.workspace_members enable row level security;
alter table public.snippets          enable row level security;
 
-- Helper: is the calling user a member of <ws>?
-- SECURITY DEFINER so the helper reads workspace_members WITHOUT firing
-- that table's own RLS policies — see the recursion note below.
create or replace function public.is_workspace_member(ws uuid)
returns boolean
language sql stable
security definer set search_path = public
as $$
  select exists (
    select 1 from public.workspace_members
    where workspace_id = ws and user_id = auth.uid()
  )
$$;
 
-- Helper: is the calling user an OWNER of <ws>?
create or replace function public.is_workspace_owner(ws uuid)
returns boolean
language sql stable
security definer set search_path = public
as $$
  select exists (
    select 1 from public.workspace_members
    where workspace_id = ws and user_id = auth.uid() and role = 'owner'
  )
$$;

Why security definer on two read-only helpers? Because of a trap that catches nearly everyone who writes membership-based RLS: these helpers query workspace_members, and workspace_members has RLS policies of its own — which call these helpers. Without security definer, checking a policy on workspace_members invokes the helper, which queries workspace_members, which checks the policy, which invokes the helper… Postgres detects the loop and aborts with infinite recursion detected in policy. security definer makes the helper run as the function's owner, bypassing RLS inside the helper only, which breaks the cycle. The set search_path = public pin is standard hygiene for definer functions — it stops a malicious schema from shadowing the tables the function touches.

Now the policies. Workspaces:

-- workspaces: members can read their workspaces; anyone signed in can create
create policy workspaces_select on public.workspaces
  for select using ( public.is_workspace_member(id) );
 
create policy workspaces_insert on public.workspaces
  for insert with check ( auth.uid() = created_by );
 
create policy workspaces_update on public.workspaces
  for update using ( public.is_workspace_owner(id) );

Workspace members:

-- members: you always see your own memberships; owners see the whole
-- roster; owners insert/delete; users can remove themselves (leave)
create policy members_select on public.workspace_members
  for select using (
    user_id = auth.uid()
    or public.is_workspace_owner(workspace_id)
  );
 
create policy members_insert on public.workspace_members
  for insert with check ( public.is_workspace_owner(workspace_id) );
 
create policy members_delete_self on public.workspace_members
  for delete using ( user_id = auth.uid() );
 
create policy members_delete_owner on public.workspace_members
  for delete using ( public.is_workspace_owner(workspace_id) );

Note the shape of members_select: user_id = auth.uid() handles the common case (your own rows) without touching the helpers at all, and the owner branch uses the now-recursion-safe is_workspace_owner. Keeping the policy body this simple is deliberate — policies run on every row of every query against the table.

Snippets — the meaty one:

-- read: private to owner, workspace to members, public to anyone signed in
create policy snippets_select on public.snippets
  for select using (
    (visibility = 'private'   and created_by = auth.uid())
    or
    (visibility = 'workspace' and public.is_workspace_member(workspace_id))
    or
    visibility = 'public'
  );
 
-- insert: must be a member of the target workspace, and own the row
create policy snippets_insert on public.snippets
  for insert with check (
    created_by = auth.uid()
    and public.is_workspace_member(workspace_id)
  );
 
-- update: creator OR workspace owner may edit; the WITH CHECK stops anyone
-- from moving a snippet into a workspace they don't belong to
create policy snippets_update on public.snippets
  for update using (
    created_by = auth.uid()
    or public.is_workspace_owner(workspace_id)
  )
  with check (
    public.is_workspace_member(workspace_id)
  );
 
-- delete: creator OR workspace owner
create policy snippets_delete on public.snippets
  for delete using (
    created_by = auth.uid()
    or public.is_workspace_owner(workspace_id)
  );

One more function while we're in here. In step 9 we'll add a create_workspace MCP tool, and it can't be a plain client-side insert: the moment you create a workspace you aren't a member of it yet, so workspaces_select hides the row you just inserted, and members_insert won't let you add yourself as owner because you aren't an owner yet. Chicken, meet egg. The fix is one atomic security definer RPC. Append it to the migration:

-- =========================================================================
-- create_workspace: workspace + owner membership, atomically.
-- A client-side two-step insert can't work under RLS (you can't add
-- yourself as owner until you're an owner), so this runs as definer.
-- =========================================================================
create or replace function public.create_workspace(name text)
returns public.workspaces
language plpgsql
security definer set search_path = public
as $$
declare
  ws public.workspaces;
begin
  if auth.uid() is null then
    raise exception 'not authenticated';
  end if;
 
  insert into public.workspaces (name, created_by)
  values (create_workspace.name, auth.uid())
  returning * into ws;
 
  insert into public.workspace_members (workspace_id, user_id, role)
  values (ws.id, auth.uid(), 'owner');
 
  return ws;
end $$;
 
revoke execute on function public.create_workspace(text) from public, anon;
grant  execute on function public.create_workspace(text) to authenticated;

It still requires a signed-in caller (auth.uid() is null check) and only ever inserts rows attributed to that caller, so being a definer function doesn't widen anyone's privileges — it just performs the two inserts the RLS policies can't sequence.

3. Apply to the cloud

supabase db push

Verify in the Supabase dashboard → Table Editor: you should see workspaces, workspace_members, snippets. Click any of them → "RLS enabled" should be green.

4. Manual smoke test

In the SQL Editor (Database → SQL Editor), simulate two users:

-- Create fake test users
insert into auth.users (id, email, raw_user_meta_data)
values
  ('00000000-0000-0000-0000-000000000001', 'alice@test.local', '{"name":"Alice"}'),
  ('00000000-0000-0000-0000-000000000002', 'bob@test.local',   '{"name":"Bob"}');
 
-- Verify the signup trigger created their personal workspaces
select w.name, w.id, m.role
  from public.workspaces w
  join public.workspace_members m on m.workspace_id = w.id
  where m.user_id = '00000000-0000-0000-0000-000000000001';

You should see one row: a workspace named "Alice" with role owner. (Inserting directly into auth.users is a shortcut that works for this trigger test but is brittle — the table has many columns Supabase normally fills in. If it complains, create the two users via Dashboard → Authentication → Add user instead; the trigger fires either way.)

Now confirm RLS is doing its job. This part trips people up: the SQL Editor runs as postgres, which bypasses RLS entirely — a bare select * from workspaces will always return everything, proving nothing. To actually engage the policies you have to drop to the authenticated role and supply JWT claims, inside a transaction:

begin;
 
-- Run as the role our MCP users will have, with Alice's claims
set local role authenticated;
set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';
 
-- Should return exactly one row: Alice's personal workspace
select * from public.workspaces;
 
-- Should return only Alice's own membership row
select * from public.workspace_members;
 
-- Should return zero rows (no snippets exist yet)
select * from public.snippets;
 
rollback;  -- discards the role/claims changes with the transaction

If that first select shows Bob's workspace too, RLS is broken — go back and check the policies. The workspace_members select returning just Alice's own row (not every member of every workspace) is the members_select policy from section 2 doing what it says.

5. Clean up the test users

Order matters here: workspaces.created_by and snippets.created_by are on delete restrict, so deleting the users first would fail with a foreign-key error. Delete their workspaces first (snippets and memberships cascade from those), then the users:

delete from public.workspaces
 where created_by in (select id from auth.users where email like '%@test.local');
 
delete from auth.users where email like '%@test.local';

The schema is done. Everything from here on can lean on RLS. Step 4 turns on Supabase's OAuth 2.1 server — the auth side of the MCP handshake.