How to Add User Reviews and Ratings to Your App (Copy-Paste Prompts Included)

1create table public.products (
2  id uuid primary key default gen_random_uuid(),
3  title text not null,
4  average_rating numeric(3,1) default 0,
5  review_count int default 0
6);
7
8create table public.reviews (
9  id uuid primary key default gen_random_uuid(),
10  product_id uuid references public.products(id) on delete cascade not null,
11  user_id uuid references auth.users(id) on delete cascade not null,
12  rating smallint not null check (rating between 1 and 5),
13  title text,
14  body text not null,
15  created_at timestamptz not null default now(),
16  updated_at timestamptz not null default now(),
17  unique (product_id, user_id)
18);
19
20create table public.review_votes (
21  review_id uuid references public.reviews(id) on delete cascade not null,
22  user_id uuid references auth.users(id) on delete cascade not null,
23  is_helpful boolean not null,
24  primary key (review_id, user_id)
25);
26
27create table public.review_reports (
28  id uuid primary key default gen_random_uuid(),
29  review_id uuid references public.reviews(id) on delete cascade not null,
30  reporter_id uuid references auth.users(id) on delete cascade not null,
31  reason text not null,
32  created_at timestamptz not null default now()
33);
34
35-- Postgres trigger to keep aggregate rating current
36create or replace function public.update_product_rating()
37returns trigger language plpgsql as $$
38begin
39  update public.products
40  set
41    average_rating = (
42      select round(avg(rating)::numeric, 1)
43      from public.reviews
44      where product_id = coalesce(new.product_id, old.product_id)
45    ),
46    review_count = (
47      select count(*)
48      from public.reviews
49      where product_id = coalesce(new.product_id, old.product_id)
50    )
51  where id = coalesce(new.product_id, old.product_id);
52  return coalesce(new, old);
53end;
54$$;
55
56create trigger reviews_update_product_rating
57  after insert or update or delete on public.reviews
58  for each row execute function public.update_product_rating();
59
60-- RLS
61alter table public.reviews enable row level security;
62alter table public.review_votes enable row level security;
63alter table public.review_reports enable row level security;
64
65create policy "Anyone can read reviews"
66  on public.reviews for select
67  using (true);
68
69create policy "Auth users can insert own review"
70  on public.reviews for insert
71  with check (auth.uid() = user_id);
72
73create policy "Users can update own review"
74  on public.reviews for update
75  using (auth.uid() = user_id);
76
77create policy "Users can delete own review"
78  on public.reviews for delete
79  using (auth.uid() = user_id);
80
81create policy "Auth users can vote"
82  on public.review_votes for insert
83  with check (auth.uid() = user_id);
84
85create policy "Users can read own votes"
86  on public.review_votes for select
87  using (auth.uid() = user_id);
88
89-- Indexes
90create index reviews_product_idx on public.reviews (product_id, created_at desc);
91create index reviews_helpful_idx on public.reviews (product_id, (select count(*) from public.review_votes v where v.review_id = id and v.is_helpful) desc);