How to Add User Groups to Your App (Copy-Paste Prompts Included)

1create table public.groups (
2  id uuid primary key default gen_random_uuid(),
3  name text not null check (char_length(name) between 3 and 50),
4  slug text not null unique,
5  avatar_url text,
6  created_by uuid references auth.users(id) on delete set null,
7  created_at timestamptz not null default now()
8);
9
10create type public.group_role as enum ('owner', 'admin', 'member');
11
12create table public.group_members (
13  group_id uuid references public.groups(id) on delete cascade not null,
14  user_id uuid references auth.users(id) on delete cascade not null,
15  role public.group_role not null default 'member',
16  joined_at timestamptz not null default now(),
17  primary key (group_id, user_id)
18);
19
20create table public.group_invites (
21  id uuid primary key default gen_random_uuid(),
22  group_id uuid references public.groups(id) on delete cascade not null,
23  email text not null,
24  token uuid not null unique default gen_random_uuid(),
25  expires_at timestamptz not null default now() + interval '24 hours',
26  accepted_at timestamptz
27);
28
29-- Indexes
30create index group_members_user_idx on public.group_members (user_id);
31create index group_invites_token_idx on public.group_invites (token);
32create index groups_slug_idx on public.groups (slug);
33
34-- RLS
35alter table public.groups enable row level security;
36alter table public.group_members enable row level security;
37alter table public.group_invites enable row level security;
38
39-- Groups: members can read their groups
40create policy "Members can view their groups"
41  on public.groups for select
42  using (
43    exists (
44      select 1 from public.group_members
45      where group_id = groups.id
46        and user_id = auth.uid()
47    )
48  );
49
50-- Groups: authenticated users can create groups
51create policy "Authenticated users can create groups"
52  on public.groups for insert
53  with check (auth.uid() is not null);
54
55-- Groups: owners and admins can update group details
56create policy "Owners and admins can update groups"
57  on public.groups for update
58  using (
59    exists (
60      select 1 from public.group_members
61      where group_id = groups.id
62        and user_id = auth.uid()
63        and role in ('owner', 'admin')
64    )
65  );
66
67-- Group members: members can view the member list
68create policy "Members can view group members"
69  on public.group_members for select
70  using (
71    exists (
72      select 1 from public.group_members gm2
73      where gm2.group_id = group_members.group_id
74        and gm2.user_id = auth.uid()
75    )
76  );
77
78-- Group invites: owners and admins can insert invites
79create policy "Owners and admins can create invites"
80  on public.group_invites for insert
81  with check (
82    exists (
83      select 1 from public.group_members
84      where group_id = group_invites.group_id
85        and user_id = auth.uid()
86        and role in ('owner', 'admin')
87    )
88  );