How to Build a Community Forum with V0

1-- Forum categories
2create table forum_categories (
3  id uuid primary key default gen_random_uuid(),
4  name text not null,
5  slug text unique not null,
6  description text,
7  icon text,
8  sort_order int default 0
9);
10
11-- Threads
12create table threads (
13  id uuid primary key default gen_random_uuid(),
14  category_id uuid references forum_categories not null,
15  author_id uuid references auth.users not null,
16  title text not null,
17  slug text unique not null,
18  content text not null,
19  is_pinned boolean default false,
20  is_locked boolean default false,
21  view_count int default 0,
22  reply_count int default 0,
23  last_reply_at timestamptz,
24  created_at timestamptz default now()
25);
26
27-- Replies with materialized path for nesting
28create table replies (
29  id uuid primary key default gen_random_uuid(),
30  thread_id uuid references threads on delete cascade not null,
31  author_id uuid references auth.users not null,
32  content text not null,
33  parent_reply_id uuid references replies,
34  path text not null,  -- e.g., '001', '001.002', '001.002.003'
35  vote_score int default 0,
36  created_at timestamptz default now(),
37  updated_at timestamptz default now()
38);
39
40-- Votes with composite PK to prevent duplicates
41create table votes (
42  user_id uuid references auth.users not null,
43  reply_id uuid references replies on delete cascade not null,
44  value int check (value in (-1, 1)) not null,
45  primary key (user_id, reply_id)
46);
47
48-- User reputation with auto-calculated level
49create table user_reputation (
50  user_id uuid primary key references auth.users,
51  points int default 0,
52  level text generated always as (
53    case
54      when points >= 1000 then 'expert'
55      when points >= 100 then 'contributor'
56      else 'newcomer'
57    end
58  ) stored
59);
60
61-- Trigger: update reply_count and last_reply_at on new reply
62create or replace function update_thread_on_reply()
63returns trigger as $$
64begin
65  update threads set
66    reply_count = reply_count + 1,
67    last_reply_at = now()
68  where id = NEW.thread_id;
69  return NEW;
70end;
71$$ language plpgsql;
72
73create trigger on_reply_insert
74  after insert on replies
75  for each row execute function update_thread_on_reply();
76
77-- Trigger: update vote_score on vote change
78create or replace function update_reply_vote_score()
79returns trigger as $$
80begin
81  update replies set vote_score = (
82    select coalesce(sum(value), 0) from votes where reply_id = coalesce(NEW.reply_id, OLD.reply_id)
83  ) where id = coalesce(NEW.reply_id, OLD.reply_id);
84  return coalesce(NEW, OLD);
85end;
86$$ language plpgsql;
87
88create trigger on_vote_change
89  after insert or update or delete on votes
90  for each row execute function update_reply_vote_score();
91
92-- Full-text search index on threads
93alter table threads add column fts tsvector
94  generated always as (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))) stored;
95create index threads_fts_idx on threads using gin(fts);
96
97-- RLS
98alter table forum_categories enable row level security;
99alter table threads enable row level security;
100alter table replies enable row level security;
101alter table votes enable row level security;
102alter table user_reputation enable row level security;
103
104create policy "Anyone can read categories" on forum_categories for select using (true);
105create policy "Anyone can read threads" on threads for select using (true);
106create policy "Authenticated users create threads" on threads for insert with check (auth.uid() = author_id);
107create policy "Authors edit own threads" on threads for update using (auth.uid() = author_id);
108create policy "Anyone can read replies" on replies for select using (true);
109create policy "Authenticated users create replies" on replies for insert with check (auth.uid() = author_id);
110create policy "Authors edit own replies" on replies for update using (auth.uid() = author_id);
111create policy "Authenticated users vote" on votes for all using (auth.uid() = user_id);
112create policy "Anyone can read reputation" on user_reputation for select using (true);

1// app/forum/page.tsx
2import { createClient } from '@/lib/supabase/server';
3import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
4import { Badge } from '@/components/ui/badge';
5import Link from 'next/link';
6
7export default async function ForumPage() {
8  const supabase = await createClient();
9
10  const { data: categories } = await supabase
11    .from('forum_categories')
12    .select('*, threads(count)')
13    .order('sort_order');
14
15  return (
16    <div className="max-w-4xl mx-auto p-6 space-y-6">
17      <h1 className="text-3xl font-bold">Community Forum</h1>
18      <div className="grid gap-4">
19        {categories?.map((category) => (
20          <Link key={category.id} href={`/forum/${category.slug}`}>
21            <Card className="hover:border-primary transition-colors">
22              <CardHeader className="flex flex-row items-center gap-4 pb-2">
23                <span className="text-2xl">{category.icon}</span>
24                <div className="flex-1">
25                  <CardTitle className="text-lg">{category.name}</CardTitle>
26                  <p className="text-sm text-muted-foreground">{category.description}</p>
27                </div>
28                <Badge variant="secondary">
29                  {category.threads[0]?.count ?? 0} threads
30                </Badge>
31              </CardHeader>
32            </Card>
33          </Link>
34        ))}
35      </div>
36    </div>
37  );
38}
39
40// app/forum/[category]/page.tsx
41import { createClient } from '@/lib/supabase/server';
42import { Card, CardContent } from '@/components/ui/card';
43import { Badge } from '@/components/ui/badge';
44import { Button } from '@/components/ui/button';
45import { Avatar, AvatarFallback } from '@/components/ui/avatar';
46import Link from 'next/link';
47import { Pin, Lock, MessageSquare } from 'lucide-react';
48
49export default async function CategoryPage({
50  params,
51}: {
52  params: Promise<{ category: string }>;
53}) {
54  const { category: categorySlug } = await params;
55  const supabase = await createClient();
56
57  const { data: category } = await supabase
58    .from('forum_categories')
59    .select('*')
60    .eq('slug', categorySlug)
61    .single();
62
63  const { data: threads } = await supabase
64    .from('threads')
65    .select('*')
66    .eq('category_id', category?.id)
67    .order('is_pinned', { ascending: false })
68    .order('last_reply_at', { ascending: false, nullsFirst: false })
69    .range(0, 24);
70
71  return (
72    <div className="max-w-4xl mx-auto p-6 space-y-6">
73      <div className="flex items-center justify-between">
74        <div>
75          <h1 className="text-2xl font-bold">{category?.name}</h1>
76          <p className="text-muted-foreground">{category?.description}</p>
77        </div>
78        <Button asChild>
79          <Link href="/forum/new">New Thread</Link>
80        </Button>
81      </div>
82
83      <div className="space-y-2">
84        {threads?.map((thread) => (
85          <Link key={thread.id} href={`/forum/${categorySlug}/${thread.slug}`}>
86            <Card className="hover:bg-accent/50 transition-colors">
87              <CardContent className="flex items-center gap-4 py-4">
88                <Avatar>
89                  <AvatarFallback>
90                    {thread.author_id.slice(0, 2).toUpperCase()}
91                  </AvatarFallback>
92                </Avatar>
93                <div className="flex-1 min-w-0">
94                  <div className="flex items-center gap-2">
95                    {thread.is_pinned && <Pin className="h-3 w-3 text-primary" />}
96                    {thread.is_locked && <Lock className="h-3 w-3 text-muted-foreground" />}
97                    <span className="font-medium truncate">{thread.title}</span>
98                  </div>
99                  <p className="text-sm text-muted-foreground">
100                    {new Date(thread.created_at).toLocaleDateString()}
101                  </p>
102                </div>
103                <div className="flex items-center gap-1 text-sm text-muted-foreground">
104                  <MessageSquare className="h-4 w-4" />
105                  {thread.reply_count}
106                </div>
107              </CardContent>
108            </Card>
109          </Link>
110        ))}
111      </div>
112    </div>
113  );
114}

1// app/forum/[category]/[slug]/page.tsx
2import { createClient } from '@/lib/supabase/server';
3import { Avatar, AvatarFallback } from '@/components/ui/avatar';
4import { Badge } from '@/components/ui/badge';
5import { Button } from '@/components/ui/button';
6import { Card, CardContent } from '@/components/ui/card';
7import { ReplyForm } from './reply-form';
8import { VoteButtons } from './vote-buttons';
9import { ChevronUp, ChevronDown, MessageSquare } from 'lucide-react';
10
11type Reply = {
12  id: string;
13  content: string;
14  path: string;
15  author_id: string;
16  vote_score: number;
17  created_at: string;
18};
19
20export default async function ThreadPage({
21  params,
22}: {
23  params: Promise<{ category: string; slug: string }>;
24}) {
25  const { category, slug } = await params;
26  const supabase = await createClient();
27
28  const { data: thread } = await supabase
29    .from('threads')
30    .select('*')
31    .eq('slug', slug)
32    .single();
33
34  if (!thread) return <div>Thread not found</div>;
35
36  // Increment view count
37  await supabase
38    .from('threads')
39    .update({ view_count: thread.view_count + 1 })
40    .eq('id', thread.id);
41
42  // Fetch all replies sorted by materialized path
43  const { data: replies } = await supabase
44    .from('replies')
45    .select('*')
46    .eq('thread_id', thread.id)
47    .order('path');
48
49  const allReplies = replies ?? [];
50
51  function getDepth(path: string): number {
52    return path.split('.').length - 1;
53  }
54
55  return (
56    <div className="max-w-4xl mx-auto p-6 space-y-6">
57      <div>
58        <div className="flex items-center gap-2 mb-2">
59          {thread.is_pinned && <Badge>Pinned</Badge>}
60          {thread.is_locked && <Badge variant="secondary">Locked</Badge>}
61        </div>
62        <h1 className="text-2xl font-bold">{thread.title}</h1>
63        <p className="text-sm text-muted-foreground mt-1">
64          Posted {new Date(thread.created_at).toLocaleDateString()} · {thread.view_count} views · {thread.reply_count} replies
65        </p>
66      </div>
67
68      <Card>
69        <CardContent className="pt-6">
70          <p className="whitespace-pre-wrap">{thread.content}</p>
71        </CardContent>
72      </Card>
73
74      {!thread.is_locked && <ReplyForm threadId={thread.id} parentPath="" />}
75
76      <div className="space-y-2">
77        {allReplies.map((reply) => (
78          <div
79            key={reply.id}
80            style={{ marginLeft: `${getDepth(reply.path) * 32}px` }}
81          >
82            <Card>
83              <CardContent className="pt-4 pb-3">
84                <div className="flex items-start gap-3">
85                  <VoteButtons replyId={reply.id} score={reply.vote_score} />
86                  <div className="flex-1">
87                    <div className="flex items-center gap-2 mb-1">
88                      <Avatar className="h-6 w-6">
89                        <AvatarFallback className="text-xs">
90                          {reply.author_id.slice(0, 2).toUpperCase()}
91                        </AvatarFallback>
92                      </Avatar>
93                      <span className="text-sm text-muted-foreground">
94                        {new Date(reply.created_at).toLocaleDateString()}
95                      </span>
96                    </div>
97                    <p className="whitespace-pre-wrap text-sm">{reply.content}</p>
98                    {!thread.is_locked && (
99                      <ReplyForm threadId={thread.id} parentPath={reply.path} />
100                    )}
101                  </div>
102                </div>
103              </CardContent>
104            </Card>
105          </div>
106        ))}
107      </div>
108    </div>
109  );
110}

1// app/actions/forum.ts
2'use server';
3
4import { createClient } from '@/lib/supabase/server';
5import { revalidatePath } from 'next/cache';
6
7export async function voteOnReply(replyId: string, value: 1 | -1) {
8  const supabase = await createClient();
9  const { data: { user } } = await supabase.auth.getUser();
10  if (!user) throw new Error('Not authenticated');
11
12  // Check if user already voted
13  const { data: existing } = await supabase
14    .from('votes')
15    .select('value')
16    .eq('user_id', user.id)
17    .eq('reply_id', replyId)
18    .single();
19
20  if (existing?.value === value) {
21    // Remove vote if clicking same button
22    await supabase.from('votes')
23      .delete()
24      .eq('user_id', user.id)
25      .eq('reply_id', replyId);
26  } else {
27    // Upsert vote
28    await supabase.from('votes').upsert({
29      user_id: user.id,
30      reply_id: replyId,
31      value,
32    });
33  }
34
35  // Update reply author reputation
36  const { data: reply } = await supabase
37    .from('replies')
38    .select('author_id, vote_score')
39    .eq('id', replyId)
40    .single();
41
42  if (reply) {
43    await supabase.from('user_reputation').upsert({
44      user_id: reply.author_id,
45      points: Math.max(0, reply.vote_score),
46    });
47  }
48}
49
50export async function createReply(
51  threadId: string,
52  parentPath: string,
53  content: string
54) {
55  const supabase = await createClient();
56  const { data: { user } } = await supabase.auth.getUser();
57  if (!user) throw new Error('Not authenticated');
58
59  // Generate next path segment
60  const pathPrefix = parentPath ? `${parentPath}.` : '';
61  const { data: siblings } = await supabase
62    .from('replies')
63    .select('path')
64    .eq('thread_id', threadId)
65    .like('path', `${pathPrefix}___`)
66    .order('path', { ascending: false })
67    .limit(1);
68
69  let nextIndex = 1;
70  if (siblings && siblings.length > 0) {
71    const lastSegment = siblings[0].path.split('.').pop()!;
72    nextIndex = parseInt(lastSegment, 10) + 1;
73  }
74
75  const newPath = `${pathPrefix}${String(nextIndex).padStart(3, '0')}`;
76
77  await supabase.from('replies').insert({
78    thread_id: threadId,
79    author_id: user.id,
80    content,
81    parent_reply_id: parentPath
82      ? (await supabase.from('replies').select('id').eq('thread_id', threadId).eq('path', parentPath).single()).data?.id
83      : null,
84    path: newPath,
85  });
86
87  // Initialize reputation if needed
88  await supabase.from('user_reputation').upsert({
89    user_id: user.id,
90    points: 0,
91  });
92}
93
94// components/vote-buttons.tsx
95'use client';
96
97import { useState } from 'react';
98import { Button } from '@/components/ui/button';
99import { ChevronUp, ChevronDown } from 'lucide-react';
100import { voteOnReply } from '@/app/actions/forum';
101
102export function VoteButtons({ replyId, score }: { replyId: string; score: number }) {
103  const [currentScore, setCurrentScore] = useState(score);
104  const [voting, setVoting] = useState(false);
105
106  async function handleVote(value: 1 | -1) {
107    setVoting(true);
108    await voteOnReply(replyId, value);
109    setCurrentScore(prev => prev + value);
110    setVoting(false);
111  }
112
113  return (
114    <div className="flex flex-col items-center gap-0.5">
115      <Button variant="ghost" size="icon" className="h-6 w-6" disabled={voting}
116        onClick={() => handleVote(1)}>
117        <ChevronUp className="h-4 w-4" />
118      </Button>
119      <span className="text-sm font-medium">{currentScore}</span>
120      <Button variant="ghost" size="icon" className="h-6 w-6" disabled={voting}
121        onClick={() => handleVote(-1)}>
122        <ChevronDown className="h-4 w-4" />
123      </Button>
124    </div>
125  );
126}

1// app/forum/search/page.tsx
2import { createClient } from '@/lib/supabase/server';
3import { Card, CardContent } from '@/components/ui/card';
4import { Input } from '@/components/ui/input';
5import { Badge } from '@/components/ui/badge';
6import Link from 'next/link';
7import { Search } from 'lucide-react';
8
9export default async function SearchPage({
10  searchParams,
11}: {
12  searchParams: Promise<{ q?: string }>;
13}) {
14  const { q } = await searchParams;
15  const supabase = await createClient();
16
17  let threads: any[] = [];
18  if (q && q.trim()) {
19    // Convert query to tsquery format
20    const tsquery = q.trim().split(/\s+/).join(' & ');
21    const { data } = await supabase
22      .from('threads')
23      .select('*, forum_categories(name, slug)')
24      .textSearch('fts', tsquery)
25      .order('created_at', { ascending: false })
26      .limit(20);
27    threads = data ?? [];
28  }
29
30  return (
31    <div className="max-w-4xl mx-auto p-6 space-y-6">
32      <h1 className="text-2xl font-bold">Search Forum</h1>
33
34      <form className="relative">
35        <Search className="absolute left-3 top-1/2 -translate-y-1/2 h-4 w-4 text-muted-foreground" />
36        <Input
37          name="q"
38          defaultValue={q}
39          placeholder="Search threads..."
40          className="pl-10"
41        />
42      </form>
43
44      {q && (
45        <p className="text-sm text-muted-foreground">
46          {threads.length} result{threads.length !== 1 ? 's' : ''} for "{q}"
47        </p>
48      )}
49
50      <div className="space-y-2">
51        {threads.map((thread) => (
52          <Link
53            key={thread.id}
54            href={`/forum/${thread.forum_categories.slug}/${thread.slug}`}
55          >
56            <Card className="hover:bg-accent/50 transition-colors">
57              <CardContent className="py-4">
58                <div className="flex items-center gap-2 mb-1">
59                  <Badge variant="outline">{thread.forum_categories.name}</Badge>
60                  <span className="font-medium">{thread.title}</span>
61                </div>
62                <p className="text-sm text-muted-foreground line-clamp-2">
63                  {thread.content}
64                </p>
65              </CardContent>
66            </Card>
67          </Link>
68        ))}
69      </div>
70    </div>
71  );
72}