Appendix C · TaskHarbor reference policy set

The complete, runnable definition of the book's example project — schema, seed, grants, helpers, and policies in one file. Disclosed as fabricated; fixed UUIDs so results are reproducible. Run top to bottom on a fresh project as postgres. Chapter 9's denormalized fast form is an alternative shape for tasks, not a replacement set.

Schema

create schema if not exists private;

create table public.workspaces (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_at timestamptz not null default now()
);

create table public.members (
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  user_id      uuid not null,
  role         text not null default 'member' check (role in ('owner', 'member')),
  primary key (workspace_id, user_id)
);

create table public.projects (
  id           uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  name         text not null
);

create table public.tasks (
  id         uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects(id) on delete cascade,
  title      text not null,
  done       boolean not null default false
);

alter table public.workspaces enable row level security;
alter table public.members    enable row level security;
alter table public.projects   enable row level security;
alter table public.tasks      enable row level security;

Grants

revoke all on public.workspaces from anon, authenticated;
grant select, update, delete on public.workspaces to authenticated;

revoke all on public.members from anon, authenticated;
grant select, insert, delete on public.members to authenticated;

revoke all on public.projects from anon, authenticated;
grant select, insert, update, delete on public.projects to authenticated;

revoke all on public.tasks from anon, authenticated;
grant select, insert, update, delete on public.tasks to authenticated;

Helper functions

create or replace function private.is_member(ws uuid)
returns boolean language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.workspace_id = ws and m.user_id = (select auth.uid())
  )
$$;

create or replace function private.is_owner(ws uuid)
returns boolean language sql stable security definer
set search_path = ''
as $$
  select exists (
    select 1 from public.members m
    where m.workspace_id = ws and m.user_id = (select auth.uid()) and m.role = 'owner'
  )
$$;

revoke all on function private.is_member(uuid), private.is_owner(uuid) from public;
grant execute on function private.is_member(uuid), private.is_owner(uuid) to authenticated;

create or replace function private.create_workspace(new_name text)
returns uuid language plpgsql security definer set search_path = ''
as $$
declare new_id uuid;
begin
  insert into public.workspaces (name) values (new_name) returning id into new_id;
  insert into public.members (workspace_id, user_id, role)
    values (new_id, (select auth.uid()), 'owner');
  return new_id;
end
$$;

revoke all on function private.create_workspace(text) from public;
grant execute on function private.create_workspace(text) to authenticated;

Policies

-- workspaces: members read; owners rename or delete; creation via function only.
create policy "workspaces: members read"  on public.workspaces for select to authenticated using (private.is_member(id));
create policy "workspaces: owners update" on public.workspaces for update to authenticated
  using (private.is_owner(id)) with check (private.is_owner(id));
create policy "workspaces: owners delete" on public.workspaces for delete to authenticated using (private.is_owner(id));

-- members: visible to the workspace; owners add or remove; nobody removes themselves.
create policy "members: workspace reads"  on public.members for select to authenticated using (private.is_member(workspace_id));
create policy "members: owners add"       on public.members for insert to authenticated
  with check (private.is_owner(workspace_id));
create policy "members: owners remove"    on public.members for delete to authenticated
  using (private.is_owner(workspace_id) and user_id <> (select auth.uid()));

-- projects: members manage; owners delete.
create policy "projects: members read"    on public.projects for select to authenticated using (private.is_member(workspace_id));
create policy "projects: members create"  on public.projects for insert to authenticated
  with check (private.is_member(workspace_id));
create policy "projects: members update"  on public.projects for update to authenticated
  using (private.is_member(workspace_id)) with check (private.is_member(workspace_id));
create policy "projects: owners delete"   on public.projects for delete to authenticated
  using (private.is_owner(workspace_id));

-- tasks: reachable only through a project in your workspace.
create policy "tasks: members read"       on public.tasks for select to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members create"     on public.tasks for insert to authenticated
  with check (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members update"     on public.tasks for update to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)))
  with check (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));
create policy "tasks: members delete"     on public.tasks for delete to authenticated
  using (exists (select 1 from public.projects p
                 where p.id = project_id and private.is_member(p.workspace_id)));

Seed data

Run as the table owner (postgres), which bypasses RLS by ownership:

insert into public.workspaces (id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Aster Labs'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Borealis Design');

insert into public.members (workspace_id, user_id, role) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'c3c3c3c3-c3c3-c3c3-c3c3-c3c3c3c3c3c3', 'owner'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'd4d4d4d4-d4d4-d4d4-d4d4-d4d4d4d4d4d4', 'owner'),
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'e5e5e5e5-e5e5-e5e5-e5e5-e5e5e5e5e5e5', 'member');

insert into public.projects (id, workspace_id, name) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1', 'Brand refresh'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b2', 'Studio handbook');

insert into public.tasks (project_id, title) values
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Draft moodboard'),
  ('a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a2', 'Review typography'),
  ('b2b2b2b2-b2b2-b2b2-b2b2-b2b2b2b2b2b3', 'Outline chapter 1');

Verify

Appendix B's five proofs run green against exactly this state. In production, replace the plain user_id uuid with references auth.users(id) once you control seeding, and keep every statement above inside versioned migrations rather than ad-hoc editor sessions.