How to Build a Content Moderation Tool with Lovable

1-- Run in Supabase SQL Editor
2create table moderation_items (
3  id uuid primary key default gen_random_uuid(),
4  content_type text not null check (content_type in ('post','comment','image','video','profile')),
5  content_text text,
6  content_url text,
7  source_user_id text,
8  source_platform text,
9  ai_score numeric(3,2),
10  ai_flags jsonb default '[]',
11  status text default 'pending' check (status in ('pending','approved','rejected','escalated','skipped')),
12  reviewed_by uuid references auth.users(id),
13  reviewed_at timestamptz,
14  review_note text,
15  priority int default 0,
16  submitted_at timestamptz default now()
17);
18
19create table moderation_rules (
20  id uuid primary key default gen_random_uuid(),
21  rule_name text not null,
22  rule_type text check (rule_type in ('keyword','threshold','pattern')),
23  rule_value text not null,
24  action text default 'flag' check (action in ('flag','auto_reject','escalate')),
25  is_active boolean default true,
26  created_at timestamptz default now()
27);
28
29create table moderator_stats (
30  id uuid primary key default gen_random_uuid(),
31  moderator_id uuid references auth.users(id) unique,
32  total_reviewed int default 0,
33  total_approved int default 0,
34  total_rejected int default 0,
35  avg_decision_seconds numeric(6,1),
36  updated_at timestamptz default now()
37);
38
39create index idx_items_status_submitted on moderation_items(status, submitted_at);
40create index idx_items_priority on moderation_items(priority desc, submitted_at asc);
41
42alter table moderation_items enable row level security;
43alter table moderation_rules enable row level security;
44alter table moderator_stats enable row level security;
45
46create policy "Moderators read queue" on moderation_items for select using (auth.role() = 'authenticated');
47create policy "Moderators update queue" on moderation_items for update using (auth.role() = 'authenticated');
48create policy "Moderators read rules" on moderation_rules for select using (auth.role() = 'authenticated');
49create policy "Moderators read stats" on moderator_stats for select using (auth.role() = 'authenticated');

1Build the moderation queue at /queue.
2
3Fetch pending moderation_items ordered by priority DESC, submitted_at ASC (oldest high-priority first).
4
5Render a shadcn DataTable with columns:
6- submitted_at (relative time: '2 hours ago')
7- content_type Badge (post=blue, comment=gray, image=purple, video=orange, profile=teal)
8- content_text (truncated to 80 chars)
9- ai_score: colored badge (green < 0.3, yellow 0.3-0.7, red > 0.7) or 'Not scored' if null
10- priority Badge (high=red, normal=gray)
11- status Badge
12
13Add a selected row state. Clicking a row selects it and opens the ReviewDialog.
14
15Add a keyboard shortcut hook:
16- When a row is selected:
17  - Press 'A' → approve (update status to 'approved', reviewed_by to current user)
18  - Press 'R' → reject (update status to 'rejected')
19  - Press 'S' → skip (update status to 'skipped')
20  - Press arrow keys → move selection up/down
21- Show a keyboard shortcuts legend below the table
22
23Add filter tabs above the table: All | Pending | Escalated | High Priority
24Show item count per tab as a Badge.

1import { useState } from 'react'
2import { Dialog, DialogContent } from '@/components/ui/dialog'
3import { Badge } from '@/components/ui/badge'
4import { Button } from '@/components/ui/button'
5import { Textarea } from '@/components/ui/textarea'
6import { Alert, AlertDescription } from '@/components/ui/alert'
7import { supabase } from '@/lib/supabase'
8import { toast } from 'sonner'
9
10interface ModerationItem {
11  id: string
12  content_type: string
13  content_text: string | null
14  content_url: string | null
15  source_user_id: string | null
16  ai_score: number | null
17  ai_flags: string[]
18  priority: number
19}
20
21interface ReviewDialogProps {
22  item: ModerationItem | null
23  onDecision: (id: string, status: string) => void
24  onClose: () => void
25}
26
27export function ReviewDialog({ item, onDecision, onClose }: ReviewDialogProps) {
28  const [note, setNote] = useState('')
29  const [loading, setLoading] = useState(false)
30
31  const decide = async (status: 'approved' | 'rejected' | 'escalated') => {
32    if (!item) return
33    setLoading(true)
34    const { error } = await supabase.from('moderation_items').update({
35      status,
36      review_note: note || null,
37      reviewed_at: new Date().toISOString()
38    }).eq('id', item.id)
39    setLoading(false)
40    if (error) { toast.error('Failed to save decision'); return }
41    toast.success(`Item ${status}`)
42    onDecision(item.id, status)
43    setNote('')
44    onClose()
45  }
46
47  const scoreColor = (score: number | null) => {
48    if (score === null) return 'bg-gray-100 text-gray-600'
49    if (score < 0.3) return 'bg-green-100 text-green-700'
50    if (score < 0.7) return 'bg-yellow-100 text-yellow-700'
51    return 'bg-red-100 text-red-700'
52  }
53
54  return (
55    <Dialog open={!!item} onOpenChange={onClose}>
56      <DialogContent className="max-w-4xl">
57        <div className="grid grid-cols-2 gap-6 h-[500px]">
58          <div className="overflow-auto space-y-3">
59            <div className="flex gap-2 items-center">
60              <Badge variant="outline">{item?.content_type}</Badge>
61              {item?.priority === 1 && <Badge variant="destructive">High Priority</Badge>}
62            </div>
63            {item?.content_text && (
64              <div className="bg-muted rounded p-4 text-sm leading-relaxed">{item.content_text}</div>
65            )}
66            {item?.content_url && (
67              <img src={item.content_url} alt="Content" className="rounded max-h-64 object-contain" />
68            )}
69            <p className="text-xs text-muted-foreground">Source user: {item?.source_user_id ?? 'anonymous'}</p>
70          </div>
71          <div className="space-y-4">
72            <div>
73              <p className="text-sm font-medium mb-2">AI Pre-screen Score</p>
74              <span className={`px-3 py-1 rounded-full text-sm font-medium ${scoreColor(item?.ai_score ?? null)}`}>
75                {item?.ai_score !== null ? (item?.ai_score! * 100).toFixed(0) + '%' : 'Not scored'}
76              </span>
77            </div>
78            {item?.ai_flags && item.ai_flags.length > 0 && (
79              <div>
80                <p className="text-sm font-medium mb-2">Flagged Categories</p>
81                <div className="flex flex-wrap gap-2">
82                  {item.ai_flags.map(f => <Badge key={f} variant="destructive">{f}</Badge>)}
83                </div>
84              </div>
85            )}
86            <div>
87              <p className="text-sm font-medium mb-2">Moderator Note (optional)</p>
88              <Textarea value={note} onChange={e => setNote(e.target.value)}
89                placeholder="Add context for this decision..." rows={3} />
90            </div>
91            <div className="flex gap-2 pt-2">
92              <Button className="flex-1 bg-green-600 hover:bg-green-700" onClick={() => decide('approved')} disabled={loading}>Approve</Button>
93              <Button className="flex-1" variant="destructive" onClick={() => decide('rejected')} disabled={loading}>Reject</Button>
94              <Button className="flex-1" variant="outline" onClick={() => decide('escalated')} disabled={loading}>Escalate</Button>
95            </div>
96          </div>
97        </div>
98      </DialogContent>
99    </Dialog>
100  )
101}

1Build a /stats page with two sections:
2
31. Personal Stats (top)
4Fetch from moderator_stats where moderator_id = current user:
5- Four Cards: Total Reviewed, Approved Rate, Rejected Rate, Avg Decision Time
6- A sparkline chart (BarChart from recharts) showing the last 7 days of decisions
7
82. Team Trends (bottom)
9Fetch last 30 days of moderation_items grouped by date and status.
10Render two charts side by side using shadcn Chart (recharts):
11
12a) Line chart: volume per day (x=date, y=count, separate lines for approved/rejected/escalated)
13b) Bar chart: breakdown by content_type (x=type, y=count, stacked by status)
14
15Above the charts add a DateRangePicker (two Popover Calendars) to filter the date range.
16Fetching data should re-query Supabase when the date range changes.
17
18All charts must be responsive (100% width, use ResponsiveContainer).
19Use muted colors for approved, red for rejected, orange for escalated.

1// supabase/functions/prescreen-content/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 corsHeaders = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type'
8}
9
10serve(async (req) => {
11  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
12
13  const { item_id, text } = await req.json()
14  if (!item_id || !text) {
15    return new Response(JSON.stringify({ error: 'item_id and text required' }), { status: 400, headers: corsHeaders })
16  }
17
18  const openaiKey = Deno.env.get('OPENAI_API_KEY')
19  if (!openaiKey) return new Response(JSON.stringify({ error: 'Missing OPENAI_API_KEY' }), { status: 500, headers: corsHeaders })
20
21  const modRes = await fetch('https://api.openai.com/v1/moderations', {
22    method: 'POST',
23    headers: { 'Authorization': `Bearer ${openaiKey}`, 'Content-Type': 'application/json' },
24    body: JSON.stringify({ input: text })
25  })
26
27  const modData = await modRes.json()
28  const result = modData.results?.[0]
29  if (!result) return new Response(JSON.stringify({ error: 'No moderation result' }), { status: 500, headers: corsHeaders })
30
31  const flags = Object.entries(result.categories as Record<string, boolean>)
32    .filter(([, v]) => v)
33    .map(([k]) => k)
34
35  const supabase = createClient(
36    Deno.env.get('SUPABASE_URL')!,
37    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
38  )
39
40  await supabase.from('moderation_items').update({
41    ai_score: result.category_scores ? Math.max(...Object.values(result.category_scores as Record<string, number>)) : null,
42    ai_flags: flags,
43    priority: flags.length > 0 ? 1 : 0
44  }).eq('id', item_id)
45
46  return new Response(JSON.stringify({ flags, flagged: result.flagged }), { headers: { ...corsHeaders, 'Content-Type': 'application/json' } })
47})