How to Build a Community Forum with Lovable

1Create a community forum with Supabase. Set up these tables:
2
3- user_roles: user_id (uuid references auth.users pk), role (text check in ('member','moderator','admin'), default 'member'), created_at
4- categories: id (uuid pk), name (text unique not null), slug (text unique), description (text), sort_order (int), post_count (int default 0), last_post_at (timestamptz), created_at
5- posts: id (uuid pk), author_id (uuid references auth.users), category_id (uuid references categories), title (text not null), body (text, markdown), vote_score (int default 0), comment_count (int default 0), is_pinned (bool default false), is_locked (bool default false), deleted_at (timestamptz, null = not deleted), created_at, updated_at
6- comments: id (uuid pk), post_id (uuid references posts on delete cascade), author_id (uuid references auth.users), parent_id (uuid references comments null, null = top-level), body (text, markdown), vote_score (int default 0), deleted_at (timestamptz), created_at, updated_at
7- votes: id (uuid pk), user_id (uuid references auth.users), target_type (text check in ('post','comment')), target_id (uuid), vote_type (int check in (1,-1)), UNIQUE(user_id, target_type, target_id)
8
9RLS:
10- posts: authenticated SELECT where deleted_at IS NULL, authenticated INSERT, author/moderator/admin UPDATE, moderator/admin soft-delete
11- comments: same pattern as posts
12- votes: authenticated SELECT, authenticated INSERT/UPDATE on their own votes (use upsert), authenticated DELETE on their own votes
13- user_roles: users SELECT their own role, admin manages all
14- categories: anon/authenticated SELECT, admin INSERT/UPDATE/DELETE
15
16Triggers:
17- On votes INSERT/UPDATE/DELETE: recalculate vote_score on the target post or comment
18- On comments INSERT: increment posts.comment_count
19- On comments DELETE (or deleted_at set): decrement posts.comment_count
20- On posts INSERT: increment categories.post_count, update categories.last_post_at

1// supabase/functions/get-comments/index.ts
2import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
4
5const cors = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
8  'Content-Type': 'application/json',
9}
10
11interface FlatComment {
12  id: string
13  parent_id: string | null
14  author_id: string
15  author_name: string
16  body: string
17  vote_score: number
18  deleted_at: string | null
19  created_at: string
20  depth: number
21  replies?: FlatComment[]
22}
23
24function buildTree(flat: FlatComment[]): FlatComment[] {
25  const map = new Map<string, FlatComment>()
26  flat.forEach((c) => { map.set(c.id, { ...c, replies: [] }) })
27  const roots: FlatComment[] = []
28  flat.forEach((c) => {
29    const node = map.get(c.id)!
30    if (c.parent_id && map.has(c.parent_id)) {
31      map.get(c.parent_id)!.replies!.push(node)
32    } else {
33      roots.push(node)
34    }
35  })
36  return roots
37}
38
39serve(async (req: Request) => {
40  if (req.method === 'OPTIONS') return new Response('ok', { headers: cors })
41
42  const url = new URL(req.url)
43  const postId = url.searchParams.get('postId')
44  if (!postId) return new Response(JSON.stringify({ error: 'postId required' }), { status: 400, headers: cors })
45
46  const supabase = createClient(
47    Deno.env.get('SUPABASE_URL') ?? '',
48    Deno.env.get('SUPABASE_ANON_KEY') ?? ''
49  )
50
51  const { data, error } = await supabase.rpc('get_comment_tree', { p_post_id: postId })
52  if (error) return new Response(JSON.stringify({ error: error.message }), { status: 500, headers: cors })
53
54  const tree = buildTree(data as FlatComment[])
55  return new Response(JSON.stringify({ data: tree }), { headers: cors })
56})

1Build a category page at src/pages/Category.tsx. Route: /c/:slug.
2
3Fetch posts for this category: supabase.from('posts').select('*, user_profiles(display_name, avatar_url)').eq('category_id', categoryId).is('deleted_at', null).order('is_pinned', { ascending: false }).order('created_at', { ascending: false })
4
5Post list items:
6- Pinned posts appear first with a 'Pinned' Badge
7- Each item shows: title (link to /post/:id), author avatar + name, created_at relative, vote_score (with up/down arrow Buttons), comment_count, category Badge
8- The vote Buttons call handleVote(postId, +1 or -1)
9
10voteScore local state:
11- Store vote scores as a Map<postId, number> in component state
12- Initialize from fetched post data
13- On vote button click: optimistic update (increment/decrement local state immediately), then upsert to votes table
14
15Realtime vote updates:
16- Subscribe to supabase.channel('forum-votes').on('postgres_changes', { event: '*', schema: 'public', table: 'votes' }, (payload) => { refetchPostScore(payload.new.target_id) })
17- On each vote event, fetch the updated vote_score for the affected post and update local state
18- Unsubscribe on unmount
19
20Moderation controls (only visible to moderators/admins):
21- DropdownMenu per post: Pin/Unpin, Lock/Unlock, Delete (sets deleted_at = now())

1Build a post detail page at src/pages/PostDetail.tsx. Route: /post/:postId.
2
3Post body rendering:
41. Install marked (for markdown to HTML conversion, importable via esm.sh or npm)
52. Install DOMPurify for XSS sanitization
63. To render the post body: const html = DOMPurify.sanitize(marked.parse(post.body))
74. Render: <div className="prose" dangerouslySetInnerHTML={{ __html: html }} />
8
9Comment section:
10- Call the get-comments Edge Function with postId
11- Render the tree recursively with a CommentNode component
12- CommentNode shows: avatar, author name, vote buttons, relative time, body (DOMPurify.sanitize(marked.parse(comment.body))), 'Reply' Button
13- Clicking 'Reply' on a CommentNode shows an inline Textarea below it. On submit, insert to comments with parent_id = comment.id.
14- Soft-deleted comments show: '[This comment was deleted]' instead of body, no author info
15- Indent nested comments with left border + padding (depth * 16px margin-left, capped at 5 levels)
16
17Reply form (new top-level comment at the bottom):
18- Textarea with markdown support note
19- Submit Button calling supabase.from('comments').insert({ post_id, author_id, body })
20
21Locked post: if post.is_locked, show a 'Locked — this thread is closed' Alert and hide all reply forms.