How to Add a Community Forum to Your App (Copy-Paste Prompts Included)

1create table public.forum_categories (
2  id uuid primary key default gen_random_uuid(),
3  name text not null,
4  slug text not null unique,
5  description text,
6  sort_order int not null default 0,
7  created_at timestamptz not null default now()
8);
9
10create table public.forum_posts (
11  id uuid primary key default gen_random_uuid(),
12  category_id uuid references public.forum_categories(id) on delete set null,
13  author_id uuid references auth.users(id) on delete set null not null,
14  parent_post_id uuid references public.forum_posts(id) on delete cascade,
15  title text,
16  body text not null,
17  is_pinned bool not null default false,
18  is_locked bool not null default false,
19  deleted_at timestamptz,
20  created_at timestamptz not null default now(),
21  updated_at timestamptz not null default now(),
22  -- title is required only for root posts (parent_post_id is null)
23  check (parent_post_id is not null or title is not null)
24);
25
26create table public.forum_votes (
27  post_id uuid references public.forum_posts(id) on delete cascade not null,
28  user_id uuid references auth.users(id) on delete cascade not null,
29  value smallint not null check (value in (1, -1)),
30  created_at timestamptz not null default now(),
31  primary key (post_id, user_id)
32);
33
34alter table public.forum_categories enable row level security;
35alter table public.forum_posts enable row level security;
36alter table public.forum_votes enable row level security;
37
38-- Categories are public
39create policy "categories are public"
40  on public.forum_categories for select
41  using (true);
42
43-- Posts are publicly readable (exclude deleted)
44create policy "posts are public"
45  on public.forum_posts for select
46  using (deleted_at is null);
47
48-- Authenticated users can create posts and replies
49create policy "authenticated can post"
50  on public.forum_posts for insert
51  to authenticated
52  with check (auth.uid() = author_id);
53
54-- Authors can edit their own posts
55create policy "author can update own"
56  on public.forum_posts for update
57  using (auth.uid() = author_id)
58  with check (auth.uid() = author_id);
59
60-- Moderators can pin, lock, or soft-delete any post
61create policy "moderator can update any"
62  on public.forum_posts for update
63  using (auth.jwt() ->> 'role' = 'moderator');
64
65create policy "moderator delete"
66  on public.forum_posts for delete
67  using (
68    auth.jwt() ->> 'role' = 'moderator'
69    or auth.uid() = author_id
70  );
71
72-- Votes are public
73create policy "votes are public"
74  on public.forum_votes for select
75  using (true);
76
77-- Authenticated users can vote
78create policy "authenticated can vote"
79  on public.forum_votes for insert
80  to authenticated
81  with check (auth.uid() = user_id);
82
83-- Users can change their vote
84create policy "authenticated can update vote"
85  on public.forum_votes for update
86  using (auth.uid() = user_id);
87
88create policy "authenticated can delete vote"
89  on public.forum_votes for delete
90  using (auth.uid() = user_id);
91
92-- Full-text search index (critical for search performance)
93create index forum_posts_fts
94  on public.forum_posts
95  using gin (to_tsvector('english', coalesce(title, '') || ' ' || body))
96  where deleted_at is null;
97
98-- Thread list performance indexes
99create index forum_posts_category_created_idx
100  on public.forum_posts (category_id, created_at desc)
101  where parent_post_id is null and deleted_at is null;
102
103create index forum_posts_parent_idx
104  on public.forum_posts (parent_post_id, created_at asc)
105  where deleted_at is null;
106
107create index forum_votes_post_idx
108  on public.forum_votes (post_id);