Build a Community Forum in Lovable

Build a community forum. Stack: React + Vite + TypeScript + Tailwind + shadcn/ui (already scaffolded), React Router v6 for routes, Supabase JS client against Lovable Cloud.

## Database schema (create one migration)

Create seven tables in the public schema:

1. `profiles` — id uuid PK references auth.users.id, username text unique not null check (length(username) BETWEEN 3 AND 30), display_name text, avatar_url text, bio text, karma int default 0, role text default 'member' check in ('member','moderator','admin'), created_at timestamptz default now(). RLS: public-read; own write; role enforced via JWT app_metadata, not this column.

2. `categories` — id uuid PK, slug text unique not null, name text not null, description text, position int default 0, created_at timestamptz default now(). RLS: public-read; admin-write via has_role().

3. `posts` — id uuid PK default gen_random_uuid(), author_id uuid not null references auth.users.id, category_id uuid not null references categories(id), title text not null check (length(title) BETWEEN 5 AND 200), body text not null check (length(body) BETWEEN 10 AND 10000), is_deleted bool default false, is_locked bool default false, is_pinned bool default false, vote_score int default 0, comment_count int default 0, created_at timestamptz default now(). RLS: public-read WHERE NOT is_deleted; INSERT authenticated WITH CHECK calling posts_rate_limit(); UPDATE/DELETE by author OR moderator/admin.

4. `comments` — id uuid PK, post_id uuid not null references posts(id) on delete cascade, author_id uuid not null references auth.users.id, parent_comment_id uuid references comments(id), body text not null check (length(body) BETWEEN 1 AND 5000), is_deleted bool default false, vote_score int default 0, created_at timestamptz default now(). RLS: public-read WHERE NOT is_deleted AND post not deleted; INSERT authenticated rate-limited.

5. `votes` — id uuid PK, voter_id uuid not null references auth.users.id, target_type text check in ('post','comment'), target_id uuid not null, value smallint check in (-1, 1), created_at timestamptz default now(). UNIQUE(voter_id, target_type, target_id). RLS: voter writes own; public aggregates via denormalized vote_score on post/comment.

6. `reports` — id uuid PK, reporter_id uuid references auth.users.id, target_type text check in ('post','comment','user'), target_id uuid not null, reason text not null, status text default 'open' check in ('open','dismissed','actioned'), reviewed_by uuid references auth.users.id, reviewed_at timestamptz, created_at timestamptz default now(). RLS: reporter writes; moderator reads/updates.

7. `bans` — id uuid PK, user_id uuid references auth.users.id, banned_by uuid references auth.users.id, reason text, expires_at timestamptz, created_at timestamptz default now(). RLS: moderator-only read/write.

## Required triggers and functions

Create these as part of the migration:

A. `has_role(check_role text) RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER STABLE` — reads `(auth.jwt() -> 'app_metadata' ->> 'role') = check_role`.

B. `posts_rate_limit() RETURNS trigger LANGUAGE plpgsql` — BEFORE INSERT on posts. If (SELECT COUNT(*) FROM posts WHERE author_id = NEW.author_id AND created_at > now() - interval '10 minutes') >= 5, RAISE EXCEPTION 'P0001: Rate limit exceeded: max 5 posts per 10 minutes'. Return NEW otherwise.

C. `update_target_vote_score() RETURNS trigger LANGUAGE plpgsql` — AFTER INSERT OR DELETE on votes. Updates posts.vote_score or comments.vote_score with SUM(value) for the target. Use NEW.target_id on INSERT, OLD.target_id on DELETE.

Attach triggers:
- `posts_rate_limit_trigger` BEFORE INSERT ON posts EXECUTE FUNCTION posts_rate_limit()
- `votes_score_trigger` AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE FUNCTION update_target_vote_score()

Also run: `ALTER PUBLICATION supabase_realtime ADD TABLE posts; ALTER PUBLICATION supabase_realtime ADD TABLE comments;`

Seed: 4 categories (General / Q&A / Showcase / Off-topic).

## Layout

Create `src/layouts/PublicLayout.tsx` — top nav with logo, category pill nav (from categories table), search box, sign in button / user avatar dropdown with username + karma + sign out. Mobile: hamburger menu collapses category nav.

## Pages

Create these routes in src/App.tsx:
- `/` (Home.tsx) — sorted feed of all posts (most recent), each row is a PostCard with vote arrows
- `/c/:categorySlug` (Category.tsx) — feed filtered to one category
- `/post/:postId` (PostDetail.tsx) — full post body + CommentThread (nested up to 3 levels)
- `/post/new` (NewPost.tsx) — form with category dropdown + title + body (Markdown textarea); requires auth
- `/u/:username` (UserProfile.tsx) — user profile with their posts and karma score
- `/search` (Search.tsx) — full-text search results via Supabase full-text search on posts.title + body
- `/mod` (ModerationDashboard.tsx) — reports queue (open reports with actions: Dismiss / Delete / Ban Author)
- `/login` and `/signup`

## Components

Build these reusable components:
- `AuthGuard` — wraps /post/new and vote interactions; redirects to /login if no session
- `ModeratorGate` — wraps /mod routes; checks JWT app_metadata.role IN ('moderator','admin')
- `PostCard` — compact row: vote arrows (up/orange, down/blue, unvote on re-click) + title + author username + category badge + comment count + age ('2h ago')
- `CommentThread` — recursive rendering of comments with left-border indentation per nesting level; collapse/expand; Reply button requires auth
- `VoteArrows` — handles optimistic upvote/downvote/unvote; INSERT into votes table; shows user's current vote state
- `ReportDialog` — reason dropdown + submit; creates reports row
- `NewPostForm` — title input + category combobox + body textarea + submit; catches P0001 rate-limit exception and shows friendly toast

## Edge Functions

Scaffold `supabase/functions/moderate-post/index.ts` — accepts {text} (post title + body concatenated), calls OpenAI Moderation endpoint at https://api.openai.com/v1/moderations with OPENAI_API_KEY from secrets, returns {flagged: bool, categories: object}. Client calls this before INSERT; if flagged, still INSERTs but with is_deleted=true and creates a reports row with reason='auto_moderation_flagged'.

Scaffold `supabase/functions/notify-mention/index.ts` — accepts {post_id, comment_id, body, author_username}. Parses body for @username patterns, looks up matching profiles, sends Resend email to each mentioned user with subject '@{author} mentioned you in {post_title}' and a link to the post.

## Styling

Light + dark mode via shadcn ThemeProvider. Primary color orange-500 (community energy). PostCard: no card background — list-style with bottom border, vote arrows left, title right. VoteArrows: orange on upvote, blue/indigo on downvote, gray neutral. Threaded comment indentation: 20px left border per level, max 3 levels deep. Category pills: colored by position (use ring-orange-500, ring-blue-500 etc). Moderator UI: banner at top of /mod with 'X open reports' badge.

Generate migration first, then PublicLayout, then pages in order, then Edge Functions.