Step 3: Database Schema & RLS — Build a Shared-Skills MCP Server

The model

auth.users  ─── workspace_members ─── workspaces
                                          │
                                          └── snippets

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>?
create or replace function public.is_workspace_member(ws uuid)
returns boolean language sql stable 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 as $$
  select exists (
    select 1 from public.workspace_members
    where workspace_id = ws and user_id = auth.uid() and role = 'owner'
  )
$$;

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: every member sees the roster; owners insert/delete; users can leave
create policy members_select on public.workspace_members
  for select using ( public.is_workspace_member(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) );

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
create policy snippets_update on public.snippets
  for update using (
    created_by = auth.uid()
    or public.is_workspace_owner(workspace_id)
  );
 
-- delete: same as update
create policy snippets_delete on public.snippets
  for delete using (
    created_by = auth.uid()
    or public.is_workspace_owner(workspace_id)
  );

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 a fake test user
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.

Now confirm RLS is doing its job. The SQL editor runs as the service role by default (bypasses RLS), but you can simulate Alice:

-- Pretend we're Alice for the rest of this transaction
set request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}';
 
-- This should return only Alice's workspace
select * from public.workspaces;
 
-- This should return zero rows (no shared workspace exists yet)
select * from public.snippets;
 
reset request.jwt.claims;

If Alice sees Bob's workspace, RLS is broken — go back and check the policies.

5. Clean up the test users

delete from auth.users where email like '%@test.local';

That cascades through workspace_members and workspaces.

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.