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

- Tool: App Features
- Last updated: July 2026

## TL;DR

A reviews and ratings feature needs five pieces: an interactive star selector, a review submission form with one-review-per-user enforcement, a paginated review list with avatars, a Postgres trigger that keeps the aggregate rating updated, and RLS policies that let anyone read but only the author can edit. With Lovable or v0 you can ship this in 3-5 hours for $0/month on Supabase's free tier.

## What a Reviews and Ratings Feature Actually Is

A reviews and ratings system lets users leave a 1-5 star score and a written comment on a product, course, or service — and lets everyone else read those opinions before deciding whether to buy or enrol. The key product decisions are: how many reviews per user per item (always one), whether reviews appear immediately or after moderation, and how the aggregate score stays accurate as reviews are added or edited. The star selector and the review list are solved UI problems; the aggregate calculation and the one-per-user enforcement are where first builds reliably break.

## Anatomy of the Feature

Six components. Lovable and v0 handle the UI and RLS well on a first prompt. The Postgres trigger for aggregate recalculation and the moderation flow reliably need a second pass.

- **StarRating component** (ui): Interactive star selector built with SVG icons (Heroicons or custom) — renders filled, half-filled, and empty states. Keyboard-accessible via arrow keys and Enter for WCAG compliance. Can use the react-rating package or a 30-line custom SVG implementation — the package adds 6KB but saves an hour of accessibility wiring.
- **Review submission form** (ui): Textarea for the review body + star input + optional title field. Validated with react-hook-form + zod: body minimum 10 characters, rating must be 1-5. Submitted via Supabase upsert with onConflict on (product_id, user_id) to enforce one review per user per item — upsert instead of insert means editing the existing review rather than creating a second one.
- **Reviews list with pagination** (ui): Infinite scroll or page-based list using Supabase .range(from, to) pagination. Sorted by helpful_votes DESC then created_at DESC by default, with UI controls to switch to Newest or Highest Rating. Shows the author's avatar from Supabase Auth user_metadata.avatar_url with a fallback initials placeholder.
- **Aggregate rating calculation** (data): A Postgres trigger fires AFTER INSERT, UPDATE, or DELETE on the reviews table and recalculates AVG(rating) and COUNT(*) for that product, writing the result back to products.average_rating and products.review_count. This keeps the aggregate accurate in real time without a cron job or a slow GROUP BY on every page load.
- **Moderation flag endpoint** (backend): A Supabase Edge Function that inserts a row into review_reports (review_id, reporter_id, reason) and posts a Slack message via Slack Incoming Webhooks (free) so the admin sees the report immediately. The flagged review stays visible until an admin deletes it using the service_role key.
- **RLS policy layer** (backend): SELECT is open to all users (including anonymous visitors) so the review list loads publicly. INSERT, UPDATE, and DELETE are restricted to auth.uid() = user_id. Moderation deletes (admin removing a flagged review) use the service_role key inside the Edge Function — never expose the service_role key to the client.

## Data model

Four tables: products (with the aggregate columns), reviews, review_votes, and review_reports. The Postgres trigger keeps products.average_rating current automatically. Paste this into the Supabase SQL Editor and run it before prompting your AI tool:

```sql
create table public.products (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  average_rating numeric(3,1) default 0,
  review_count int default 0
);

create table public.reviews (
  id uuid primary key default gen_random_uuid(),
  product_id uuid references public.products(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  rating smallint not null check (rating between 1 and 5),
  title text,
  body text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (product_id, user_id)
);

create table public.review_votes (
  review_id uuid references public.reviews(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  is_helpful boolean not null,
  primary key (review_id, user_id)
);

create table public.review_reports (
  id uuid primary key default gen_random_uuid(),
  review_id uuid references public.reviews(id) on delete cascade not null,
  reporter_id uuid references auth.users(id) on delete cascade not null,
  reason text not null,
  created_at timestamptz not null default now()
);

-- Postgres trigger to keep aggregate rating current
create or replace function public.update_product_rating()
returns trigger language plpgsql as $$
begin
  update public.products
  set
    average_rating = (
      select round(avg(rating)::numeric, 1)
      from public.reviews
      where product_id = coalesce(new.product_id, old.product_id)
    ),
    review_count = (
      select count(*)
      from public.reviews
      where product_id = coalesce(new.product_id, old.product_id)
    )
  where id = coalesce(new.product_id, old.product_id);
  return coalesce(new, old);
end;
$$;

create trigger reviews_update_product_rating
  after insert or update or delete on public.reviews
  for each row execute function public.update_product_rating();

-- RLS
alter table public.reviews enable row level security;
alter table public.review_votes enable row level security;
alter table public.review_reports enable row level security;

create policy "Anyone can read reviews"
  on public.reviews for select
  using (true);

create policy "Auth users can insert own review"
  on public.reviews for insert
  with check (auth.uid() = user_id);

create policy "Users can update own review"
  on public.reviews for update
  using (auth.uid() = user_id);

create policy "Users can delete own review"
  on public.reviews for delete
  using (auth.uid() = user_id);

create policy "Auth users can vote"
  on public.review_votes for insert
  with check (auth.uid() = user_id);

create policy "Users can read own votes"
  on public.review_votes for select
  using (auth.uid() = user_id);

-- Indexes
create index reviews_product_idx on public.reviews (product_id, created_at desc);
create 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);
```

The trigger automatically fires on INSERT, UPDATE, and DELETE — you never need to recalculate the average manually. Test it by inserting a review in the Table Editor and checking that products.average_rating updates within a second.

## Build paths

### Lovable — fit 4/10, 3-5 hours

Best path for a complete reviews system: Lovable generates the Supabase schema, RLS, and React star UI together and handles the one-review-per-user upsert logic well when explicitly stated in the prompt.

1. Create a new Lovable project with Lovable Cloud enabled so Supabase schema and auth are provisioned automatically
2. Paste the prompt below and let Agent Mode build the star selector, review form, review list, and aggregate display
3. Open the Supabase SQL Editor and paste + run the trigger function from this page — verify products.average_rating updates after submitting a test review
4. Test the one-review-per-user enforcement: submit a review, then submit again and confirm it updates the existing row rather than creating a second one
5. Click Publish and verify the review list is visible to logged-out visitors (public SELECT policy)

Starter prompt:

```
Build a user reviews and ratings feature for a product page. Use Supabase. Tables: products (id uuid pk, title text, average_rating numeric(3,1) default 0, review_count int default 0), reviews (id uuid pk, product_id uuid fk products, user_id uuid fk auth.users, rating smallint check 1-5, title text, body text not null min 10 chars, created_at timestamptz, updated_at timestamptz, unique(product_id, user_id)), review_votes (review_id, user_id, is_helpful boolean, primary key review_id+user_id), review_reports (review_id, reporter_id, reason text). RLS: reviews SELECT open to all (using true); INSERT with check auth.uid()=user_id; UPDATE/DELETE using auth.uid()=user_id. UI on /products/[id]: show the aggregate rating as '4.3 ★ · 128 reviews' at the top. Below that, a star rating selector (1-5 full stars, SVG not emoji) + textarea (min 10 chars) + optional title. On submit: upsert into reviews with onConflict 'product_id,user_id' so editing replaces rather than duplicates the review. Validate with react-hook-form + zod. Below the form, show the review list paginated with .range(0,9) then load more; sort by helpful_votes DESC then created_at DESC. Each review shows the author avatar from user_metadata.avatar_url, star display (non-interactive), body, relative timestamp, and helpful/not-helpful buttons. Show edit and delete buttons only if the current user's id matches the review's user_id. Use UPDATE (not upsert) for edits: .update({ rating, body, updated_at: new Date() }).eq('id', reviewId).eq('user_id', userId) so created_at is preserved. Add a flag button that calls a Supabase Edge Function inserting into review_reports.
```

Limitations:

- Half-star precision requires a custom SVG component beyond what Lovable generates by default — specify 'full stars only' or expect a follow-up prompt
- The Postgres trigger for aggregate rating must be verified in the SQL Editor after generation — Lovable writes the SQL but cannot confirm it executed
- The moderation workflow (admin reviewing flags and deleting reviews) needs a separate admin route and a follow-up prompt

### V0 — fit 5/10, 3-5 hours

Best UI quality: v0 produces a polished Next.js review component with shadcn/ui Card and a custom star selector — ideal for embedding in an existing product page within a Next.js app.

1. Prompt v0 with the spec below to generate the ProductReviews component and the API routes
2. Add Supabase environment variables in the Vars panel: NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY
3. Run the SQL schema and trigger from this page in the Supabase SQL Editor
4. Test the star selector: confirm it does not render emoji characters by inspecting the DOM
5. Deploy to Vercel and confirm the review list loads for logged-out visitors by opening the page in an incognito window

Starter prompt:

```
Build a Next.js reviews and ratings feature. Use Supabase with tables: products (id uuid pk, title text, average_rating numeric(3,1), review_count int), reviews (id uuid pk, product_id uuid fk, user_id uuid fk auth.users, rating smallint check 1-5, title text, body text not null, created_at timestamptz, updated_at timestamptz, unique(product_id, user_id)), review_votes (review_id, user_id, is_helpful boolean, pk review_id+user_id). RLS: reviews SELECT using true (public read); INSERT with check auth.uid()=user_id; UPDATE/DELETE using auth.uid()=user_id. Component ProductReviews on /products/[id]: fetch product and reviews server-side via Supabase Server Client. Show aggregate as '4.3 ★ · 128 reviews' using filled SVG star icons — do NOT use emoji star characters. Star selector: interactive SVG stars where clicking a star sets the rating value 1-5; hovering previews; keyboard accessible (arrow keys + Enter). Review form: react-hook-form + zod (body min 10 chars, rating 1-5 required). Submit handler: upsert to reviews with onConflict 'product_id,user_id'. Edit handler: use .update({ rating, body, updated_at: new Date() }).eq('id', reviewId).eq('user_id', userId) — never upsert on edit. Review list: shadcn/ui Card per review showing avatar (user_metadata.avatar_url or initials fallback), star display (read-only), title, body, relative timestamp. Helpful/not-helpful buttons upsert into review_votes. Show edit/delete only when review.user_id === session.user.id. Sort: helpful_votes DESC then created_at DESC by default; add sort control for Newest and Highest Rating. Paginate with .range() and a Load More button.
```

Limitations:

- v0 does not auto-provision Supabase — environment variables must be set manually in the Vars panel and the SQL schema must be run in Supabase manually
- Supabase Realtime subscription for live aggregate updates requires a custom useEffect that v0 does not generate automatically
- The admin moderation flow (viewing flags, deleting reviews with service_role key) must be added as a separate prompt or built in Lovable as a companion admin panel

### Custom — fit 3/10, 1-2 weeks

Needed when reviews must pass automated toxicity scoring before appearing (Perspective API), when review data feeds an ML recommendation model, or when multi-tenant merchants each control their own moderation rules.

1. Integrate Google Perspective API inside a Supabase Edge Function that runs before every review INSERT — reject submissions with a toxicity score above 0.7
2. Build a review approval queue in an admin panel where reviewers see flagged content before it goes live
3. Implement a recommendation signal pipeline that exports review ratings and helpful-vote counts to a vector store for collaborative filtering
4. Add a per-merchant moderation configuration table so each merchant can set their own auto-approve threshold and blocked word list

Limitations:

- Perspective API free tier is limited to 1 QPS — at high submission volume you need a queue or the paid tier at approximately $1/1,000 requests
- Multi-tenant moderation rules require a separate tenant_id column on every table and RLS policies that scope reads and writes by tenant — adds significant schema complexity

## Gotchas

- **Duplicate reviews submitted on double-click** — The review form's submit handler fires twice before the async Supabase call resolves when a user double-clicks the submit button. Without a UNIQUE constraint, two rows are inserted for the same user and product — violating the one-review rule and confusing the aggregate calculation. Fix: Add UNIQUE (product_id, user_id) to the reviews table (included in the SQL above) and use upsert with onConflict: 'product_id,user_id'. Disable the submit button immediately on first click by setting a loading state, and re-enable it only if the upsert fails.
- **Average rating not updating after a new review** — The Postgres trigger that updates products.average_rating is generated by the AI as a SQL code block in the chat response but is never actually run — the AI cannot execute SQL on your behalf. So the trigger function exists in the chat transcript but not in the database, and products.average_rating stays at 0 forever. Fix: Open Supabase Dashboard → SQL Editor, paste the trigger definition from this page, and run it. Confirm by inserting a test review in the Table Editor and checking that products.average_rating updates within 1 second.
- **Stars render as text emoji '★★★☆☆' instead of interactive SVGs** — When the prompt does not specify the rendering method, v0 and Lovable sometimes generate plain text emoji stars. These cannot be clicked to set a rating and look inconsistent across operating systems — iOS renders ★ as a yellow emoji, Windows as a thin outline. Fix: Add this to your prompt: 'Use an interactive SVG star rating component where clicking a star sets the rating value 1-5. Do not use emoji characters. Render each star as an SVG path with filled/empty states controlled by a React state variable.'
- **Review list is empty for logged-out visitors** — The AI adds a SELECT policy with auth.uid() = user_id rather than a public read policy. Logged-out visitors see an empty review list, making the feature invisible to the majority of potential buyers who have not created an account yet. Fix: Open Supabase Dashboard → Authentication → Policies → reviews table. Change the SELECT policy condition to: using (true) — no auth check, public reads allowed. Keep INSERT, UPDATE, and DELETE restricted to auth.uid() = user_id.
- **Edited review loses its original created_at timestamp** — When the edit mutation uses upsert, it resets created_at to the current timestamp because upsert performs an INSERT if the conflict resolution path is not configured to preserve the original value. The review then sorts as if it was just posted, breaking the chronological feed. Fix: Use .update() (not upsert) for edits: supabase.from('reviews').update({ rating, body, updated_at: new Date().toISOString() }).eq('id', reviewId).eq('user_id', userId). The .eq('user_id', userId) guard means even if someone forges a reviewId, they can only update reviews they own.

## Best practices

- Always use upsert with onConflict: 'product_id,user_id' for the initial review submission — it handles both first submission and re-editing without requiring separate insert and update code paths
- Disable the submit button on first click to prevent double submissions — restore it only if the Supabase call returns an error
- Store the aggregate rating in the products table via a Postgres trigger rather than calculating it with GROUP BY on every page load — at 10,000 users the GROUP BY query can take 200-400ms
- Make SELECT public (using true) by default — most review systems are intended for anonymous visitors deciding whether to buy, not just for logged-in users
- Show a rating breakdown histogram (5★ 60%, 4★ 25%, 3★ 10%, 2★ 3%, 1★ 2%) — it gives users more signal than the average number alone and is a 30-minute add-on once the data is in the database
- Validate review body minimum length (10 characters) client-side with zod AND enforce it in the Supabase check constraint — AI-generated forms sometimes omit the database-level constraint
- Cache the aggregate rating (average_rating, review_count) in the products table rather than recalculating on every request — the Postgres trigger keeps it current at zero cost

## Frequently asked questions

### Can I show reviews from one product on multiple pages without duplicating data?

Yes — store reviews once in the reviews table keyed by product_id and query them wherever the product appears. Use Supabase Server Components to fetch and render the same review list on a product detail page, a category comparison page, and a homepage featured section — all from the same database rows.

### How do I prevent fake or duplicate reviews?

Add a UNIQUE constraint on (product_id, user_id) in the reviews table — included in the SQL schema above. This makes it impossible for the same user to submit two reviews for the same product at the database level. For fake account fraud, require email verification before reviewing and rate-limit new accounts.

### Can users attach photos to their reviews?

Yes — add an attachment_urls TEXT[] column to reviews and upload images to a Supabase Storage bucket (e.g. 'review-photos'). Add a Supabase Storage INSERT policy: auth.uid()::text = (storage.foldername(name))[1] to scope uploads to the user's own folder. Display uploaded images as a horizontal scroll row below the review body.

### How do I sort reviews by most helpful by default?

Count helpful votes with a correlated subquery or a materialized column, then order by that count: .order('helpful_votes', { ascending: false }).order('created_at', { ascending: false }). The second order clause breaks ties by newest first. Add a sort control so users can switch to Newest or Highest Rating.

### Will my aggregate rating update instantly after a new review?

Yes — the Postgres trigger fires AFTER INSERT, UPDATE, or DELETE on the reviews table and writes the new average and count back to products immediately. The next page load or Supabase query will return the updated values. There is no delay or cron job required.

### Can I moderate reviews before they go live?

Add a status TEXT column to reviews with values 'pending', 'approved', 'rejected' and default 'pending'. Change the public SELECT policy to: using (status = 'approved'). Build an admin route that lists pending reviews and lets an admin set status to 'approved' or 'rejected' using the Supabase service_role key. New reviews are invisible to the public until approved.

### How do I display a rating breakdown (5★ 60%, 4★ 25%...)?

Run a Supabase query grouped by rating: .select('rating, count').from('reviews').eq('product_id', productId).groupBy('rating'). Divide each count by the total review_count from the products table to get percentages. Render as a bar chart with five rows, one per star level — a 20-line React component.

### How do I export reviews to a CSV for analysis?

Fetch all reviews for your product with the Supabase service_role client (bypassing RLS) and pipe the result array through papaparse.unparse(data) on the client side. Add a Download CSV button to the admin route that triggers this client-side export — no server-side file generation needed.

---

Source: https://www.rapidevelopers.com/app-features/user-reviews-and-ratings
© RapidDev — https://www.rapidevelopers.com/app-features/user-reviews-and-ratings