How to Build a Reviews & Ratings with Lovable

1-- Run in Supabase SQL Editor
2create table public.reviewable_items (
3  id uuid primary key default gen_random_uuid(),
4  name text not null,
5  description text,
6  average_rating numeric(3,2) not null default 0,
7  review_count integer not null default 0,
8  created_at timestamptz not null default now()
9);
10
11create table public.reviews (
12  id uuid primary key default gen_random_uuid(),
13  item_id uuid not null references public.reviewable_items(id) on delete cascade,
14  user_id uuid not null references auth.users(id),
15  rating integer not null check (rating between 1 and 5),
16  title text,
17  body text,
18  status text not null default 'pending' check (status in ('pending','approved','rejected')),
19  helpful_count integer not null default 0,
20  created_at timestamptz not null default now(),
21  unique (item_id, user_id)
22);
23
24create table public.review_votes (
25  id uuid primary key default gen_random_uuid(),
26  review_id uuid not null references public.reviews(id) on delete cascade,
27  user_id uuid not null references auth.users(id),
28  vote text not null check (vote in ('helpful','unhelpful')),
29  unique (review_id, user_id)
30);
31
32-- Trigger to recalculate average_rating and review_count
33create or replace function public.recalculate_item_rating()
34returns trigger language plpgsql as $$
35declare
36  target_item_id uuid;
37begin
38  target_item_id := coalesce(new.item_id, old.item_id);
39  update public.reviewable_items
40  set
41    average_rating = coalesce((select avg(rating) from public.reviews where item_id = target_item_id and status = 'approved'), 0),
42    review_count = (select count(*) from public.reviews where item_id = target_item_id and status = 'approved')
43  where id = target_item_id;
44  return new;
45end;
46$$;
47
48create trigger reviews_rating_trigger
49after insert or update or delete on public.reviews
50for each row execute function public.recalculate_item_rating();
51
52alter table public.reviewable_items enable row level security;
53alter table public.reviews enable row level security;
54alter table public.review_votes enable row level security;
55
56-- Public can read items and approved reviews
57create policy "public_read_items" on public.reviewable_items for select to anon using (true);
58create policy "public_read_approved_reviews" on public.reviews for select to anon using (status = 'approved');
59
60-- Authenticated users can submit reviews and votes
61create policy "auth_insert_review" on public.reviews for insert to authenticated with check (user_id = auth.uid());
62create policy "auth_vote" on public.review_votes for insert to authenticated with check (user_id = auth.uid());
63create policy "auth_read_own_review" on public.reviews for select to authenticated using (user_id = auth.uid() or status = 'approved');
64
65-- Admins can update review status
66create policy "admin_update_reviews" on public.reviews for update to authenticated using (true);

1// Lovable prompt — paste into chat
2// Build a reviews and ratings system with Supabase.
3// Tables: reviewable_items (name, average_rating, review_count), reviews (rating 1-5, title, body, status, helpful_count), review_votes.
4// Pages:
5//   /items/[id] — public page: item name, average star display (filled/empty stars), review count,
6//     list of approved reviews (Card per review: stars, title, body, date, helpful count, Vote Helpful button),
7//     Review submission form at bottom (star selector, title Input, body Textarea).
8//     If user already reviewed this item show their existing review instead of the form.
9//   /admin/reviews — protected DataTable: reviewer, item, rating stars, title, status Badge, created_at, Actions.
10//     Actions DropdownMenu: Approve, Reject.
11//     Tabs: All | Pending | Approved | Rejected.
12// Use shadcn/ui throughout. Star rating uses 5 Button components with Star icon fill toggling.

1// src/components/StarRating.tsx
2import { useState } from 'react'
3import { Star } from 'lucide-react'
4import { cn } from '@/lib/utils'
5
6type Props = {
7  value: number
8  onChange: (value: number) => void
9  readonly?: boolean
10  size?: 'sm' | 'md' | 'lg'
11}
12
13const sizes = { sm: 'h-4 w-4', md: 'h-6 w-6', lg: 'h-8 w-8' }
14
15export function StarRating({ value, onChange, readonly = false, size = 'md' }: Props) {
16  const [hovered, setHovered] = useState(0)
17
18  return (
19    <div className="flex gap-1">
20      {[1, 2, 3, 4, 5].map(star => (
21        <button
22          key={star}
23          type="button"
24          disabled={readonly}
25          onClick={() => !readonly && onChange(star)}
26          onMouseEnter={() => !readonly && setHovered(star)}
27          onMouseLeave={() => !readonly && setHovered(0)}
28          className={cn('focus:outline-none', !readonly && 'cursor-pointer hover:scale-110 transition-transform')}
29          aria-label={`${star} star${star !== 1 ? 's' : ''}`}
30        >
31          <Star
32            className={cn(
33              sizes[size],
34              (hovered || value) >= star ? 'fill-yellow-400 text-yellow-400' : 'text-muted-foreground'
35            )}
36          />
37        </button>
38      ))}
39    </div>
40  )
41}

1// src/components/ReviewForm.tsx
2import { useEffect, useState } from 'react'
3import { useForm, Controller } from 'react-hook-form'
4import { zodResolver } from '@hookform/resolvers/zod'
5import { z } from 'zod'
6import { supabase } from '@/lib/supabase'
7import { StarRating } from './StarRating'
8import { Button } from '@/components/ui/button'
9import { Input } from '@/components/ui/input'
10import { Textarea } from '@/components/ui/textarea'
11import { Form, FormField, FormItem, FormLabel, FormControl, FormMessage } from '@/components/ui/form'
12import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card'
13import { Badge } from '@/components/ui/badge'
14import { toast } from 'sonner'
15
16const schema = z.object({
17  rating: z.number().min(1, 'Please select a star rating').max(5),
18  title: z.string().max(120).optional(),
19  body: z.string().max(1000).optional()
20})
21type FormValues = z.infer<typeof schema>
22
23type Props = { itemId: string }
24
25export function ReviewForm({ itemId }: Props) {
26  const [existing, setExisting] = useState<any>(null)
27  const [loading, setLoading] = useState(true)
28
29  const form = useForm<FormValues>({
30    resolver: zodResolver(schema),
31    defaultValues: { rating: 0 }
32  })
33
34  useEffect(() => {
35    supabase.auth.getUser().then(({ data: { user } }) => {
36      if (!user) { setLoading(false); return }
37      supabase.from('reviews').select('*').eq('item_id', itemId).eq('user_id', user.id).maybeSingle()
38        .then(({ data }) => { setExisting(data); setLoading(false) })
39    })
40  }, [itemId])
41
42  async function onSubmit(values: FormValues) {
43    const { data: { user } } = await supabase.auth.getUser()
44    if (!user) { toast.error('Sign in to leave a review'); return }
45    const { error } = await supabase.from('reviews').insert({
46      item_id: itemId, user_id: user.id, ...values
47    })
48    if (error?.code === '23505') {
49      toast.error('You already reviewed this item')
50      return
51    }
52    if (error) { toast.error('Failed to submit review'); return }
53    toast.success('Review submitted — it will appear after moderation')
54    form.reset()
55  }
56
57  if (loading) return null
58
59  if (existing) return (
60    <Card><CardHeader><CardTitle>Your Review</CardTitle></CardHeader>
61      <CardContent className="space-y-2">
62        <StarRating value={existing.rating} onChange={() => {}} readonly />
63        {existing.title && <p className="font-medium">{existing.title}</p>}
64        {existing.body && <p className="text-sm text-muted-foreground">{existing.body}</p>}
65        <Badge variant="outline">{existing.status}</Badge>
66      </CardContent>
67    </Card>
68  )
69
70  return (
71    <Card><CardHeader><CardTitle>Write a Review</CardTitle></CardHeader>
72      <CardContent>
73        <Form {...form}>
74          <form onSubmit={form.handleSubmit(onSubmit)} className="space-y-4">
75            <Controller control={form.control} name="rating" render={({ field }) => (
76              <FormItem><FormLabel>Rating</FormLabel>
77                <StarRating value={field.value} onChange={field.onChange} size="lg" />
78                {form.formState.errors.rating && <p className="text-sm text-destructive">{form.formState.errors.rating.message}</p>}
79              </FormItem>
80            )} />
81            <FormField control={form.control} name="title" render={({ field }) => (
82              <FormItem><FormLabel>Title (optional)</FormLabel><FormControl><Input placeholder="Summary" {...field} /></FormControl><FormMessage /></FormItem>
83            )} />
84            <FormField control={form.control} name="body" render={({ field }) => (
85              <FormItem><FormLabel>Review (optional)</FormLabel><FormControl><Textarea placeholder="Share your experience" {...field} /></FormControl><FormMessage /></FormItem>
86            )} />
87            <Button type="submit" disabled={form.formState.isSubmitting}>Submit Review</Button>
88          </form>
89        </Form>
90      </CardContent>
91    </Card>
92  )
93}

1// src/pages/AdminReviews.tsx (key section)
2import { useEffect, useState } from 'react'
3import { supabase } from '@/lib/supabase'
4import { Tabs, TabsContent, TabsList, TabsTrigger } from '@/components/ui/tabs'
5import { Badge } from '@/components/ui/badge'
6import { Button } from '@/components/ui/button'
7import { DataTable } from '@/components/ui/data-table'
8import { DropdownMenu, DropdownMenuContent, DropdownMenuItem, DropdownMenuTrigger } from '@/components/ui/dropdown-menu'
9import { MoreHorizontal } from 'lucide-react'
10import { toast } from 'sonner'
11import type { ColumnDef } from '@tanstack/react-table'
12
13type Review = { id: string; rating: number; title: string; status: string; created_at: string; reviewable_items: { name: string } }
14
15const statusVariants: Record<string, 'default' | 'secondary' | 'destructive' | 'outline'> = {
16  pending: 'secondary', approved: 'default', rejected: 'destructive'
17}
18
19export function AdminReviews() {
20  const [reviews, setReviews] = useState<Review[]>([])
21  const [tab, setTab] = useState('all')
22
23  useEffect(() => {
24    supabase.from('reviews').select('*, reviewable_items(name)').order('created_at', { ascending: false })
25      .then(({ data }) => setReviews(data ?? []))
26  }, [])
27
28  async function updateStatus(id: string, status: string) {
29    const { error } = await supabase.from('reviews').update({ status }).eq('id', id)
30    if (error) { toast.error('Update failed'); return }
31    setReviews(prev => prev.map(r => r.id === id ? { ...r, status } : r))
32    toast.success(`Review ${status}`)
33  }
34
35  const filtered = tab === 'all' ? reviews : reviews.filter(r => r.status === tab)
36
37  const columns: ColumnDef<Review>[] = [
38    { accessorKey: 'reviewable_items.name', header: 'Item' },
39    { accessorKey: 'rating', header: 'Rating', cell: ({ row }) => `${'★'.repeat(row.original.rating)}${'☆'.repeat(5 - row.original.rating)}` },
40    { accessorKey: 'title', header: 'Title' },
41    { accessorKey: 'status', header: 'Status', cell: ({ row }) => <Badge variant={statusVariants[row.original.status]}>{row.original.status}</Badge> },
42    { id: 'actions', cell: ({ row }) => (
43      <DropdownMenu>
44        <DropdownMenuTrigger asChild><Button variant="ghost" size="icon"><MoreHorizontal className="h-4 w-4" /></Button></DropdownMenuTrigger>
45        <DropdownMenuContent>
46          <DropdownMenuItem onClick={() => updateStatus(row.original.id, 'approved')}>Approve</DropdownMenuItem>
47          <DropdownMenuItem onClick={() => updateStatus(row.original.id, 'rejected')}>Reject</DropdownMenuItem>
48        </DropdownMenuContent>
49      </DropdownMenu>
50    )}
51  ]
52
53  return (
54    <div className="p-6">
55      <Tabs value={tab} onValueChange={setTab}>
56        <TabsList><TabsTrigger value="all">All</TabsTrigger><TabsTrigger value="pending">Pending</TabsTrigger><TabsTrigger value="approved">Approved</TabsTrigger><TabsTrigger value="rejected">Rejected</TabsTrigger></TabsList>
57        <TabsContent value={tab}><DataTable columns={columns} data={filtered} /></TabsContent>
58      </Tabs>
59    </div>
60  )
61}