Audience: Product owners, QA, frontend and backend developers Scope: Review lifecycle, the rating aggregate, moderation, abuse reports, and the product-page integration

Reviews Module - Overview

1. What the module is

The reviews module owns what customers say about products they have actually received — the star rating, the written review, its moderation state, and the aggregate that a product page renders above it.

Catalog describes what the business says about a product. Reviews describe what buyers say. The module is the single source of truth for both the individual opinions and the derived numbers: average rating, published review count, and the five-star distribution.

A review cannot exist without a delivered purchase. There is no "verified purchase" flag to set or forget — the row carries the order_id and order_item_id that earned it, and a row without them is not representable. That is the whole of the badge.

2. The routes — 20

The two public routes are genuinely unauthenticated: reviews are what a shopper reads before they have an account, and an authentication wall on a product page is a conversion bug.

3. The three policies that shape everything else

The specification left these open and demanded they be decided explicitly rather than assumed. They were, and each is enforced by the database rather than by convention.

One live review per customer per product. Buying the same phone twice does not buy a second vote; the repeat purchase edits the existing review. Enforced by uq_product_review_customer_product, partial on deleted_at IS NULL — so withdrawing a review frees the slot rather than banning that customer from the product forever.

Reviews publish on submission; moderation is reactive. A new review is visible and counted immediately. Moderators take content down after the fact, with a recorded reason. A storefront with nobody on moderation duty still shows reviews.

An edit of a published review returns it to pending. It leaves the aggregate until a moderator approves it again.

The last two look contradictory and the asymmetry is the point. The attack auto-publication opens is not "somebody writes something rude" — a report and a takedown handle that. It is bait-and-switch: publish a genuine five-star review, accumulate visibility, then rewrite the text into an advertisement or an attack that no one ever read. Gating edits closes that without gating the honest first submission, which is the one that would otherwise never appear.

4. The rule that makes the rating trustworthy

A customer must never see an updated average beside a stale review count. In this module that is not prevented by discipline — it is unrepresentable.

product_rating_summary has exactly five writable columns: one counter per star. review_count, rating_sum and average_rating are GENERATED ALWAYS, computed by PostgreSQL from those five. Publishing a five-star review is one statement:

UPDATE product_rating_summary SET rating_count_5 = rating_count_5 + 1 WHERE product_id = $1;

There is no arrangement of that write that moves the average without moving the count, because they are the same write. The database refuses (428C9) any attempt by an application to write the derived columns at all.

The ±1 runs inside the same transaction as the status change that caused it, so the review and the aggregate commit together or not at all. Decrementing a bucket that is already zero aborts the transaction (23514) rather than producing a negative average — which is what makes a retried un-publish loud instead of silently corrupting a public number.

5. What a report can and cannot do

A customer can flag a published review. That raises report_count, which orders the moderator's abuse queue, and that is the entire mechanism. Nothing in this module hides a review because enough people reported it.

Auto-hiding on a threshold hands any group of customers a button that removes a competitor's honest five-star review, and nobody notices because the queue empties itself. Taking a review down is always a deliberate admin action carrying a recorded reason — and it resolves that review's open reports in the same transaction, so it leaves the queue.

One report per customer per review, enforced by a unique constraint, which is also what makes the endpoint idempotent: tapping the flag twice succeeds and changes nothing.

6. What the module deliberately does not do

• No admin create and no admin edit of review content. Businesses moderate; they never rewrite what a customer wrote. Removing content changes its visibility — the words survive every moderation action, and the history records each one.
• No hard delete. A customer's withdrawal sets deleted_at; the row and its full timeline stay as the audit record.
• No rating calculation in the Product module. Products reads product_rating_summary and projects it onto the detail response. It writes nothing.
• No merchant replies, review media, helpful votes or automatic moderation. All are future work in the specification. The lifecycle is designed so each extends it rather than replacing it, and none is stubbed — an empty abstraction for an unspecified feature is worse than its absence.

7. Where to go next