Trust by the Numbers: Engineering a Rating & Review System for Vibe Projects

Three boundary lines first, so you don't finish reading and realize you're in the wrong theater: this guide does not cover nested threads, @mentions, or floor numbering (that's the companion piece in this batch, Comment Systems in Practice — comments handle "discussion", this guide handles "ratings and reputation", clean division of labor); it does not cover recommendation algorithms (see the Recommendation Cold-Start guide); it does not cover refund disputes (see the Refund & Dispute guide). This guide answers one question: in a vibe-coded project, what engineering decisions, pitfalls, and launch checklists stand between zero and a trustworthy rating & review system.

Why do ratings deserve their own guide? Because the little "★★★★★ 4.8" line is one of the highest-converting UI elements on the whole site — and the easiest feature to fake. In the vibe-coding workflow, ratings are usually among the first things AI generates: a rating column, an average, five stars — demo runs. Three months later, reality teaches: a newly listed product with one 5-star review averages 5.0 and outranks a veteran with 2,000 reviews at 4.7 — users call you a shill; a competitor scripts 500 one-star reviews and your 4.8 becomes 3.2 overnight; someone discovers reviews can be edited infinitely, flipping bad reviews to good ones as refund leverage; the boss asks "why doesn't our 4.9 show stars on Google" and you realize structured data was never added. The engineering of a rating system hides behind "the average": how the score is computed, how gaming is blocked, how edits are governed, how numbers are cached.

This guide targets solo teams and indie developers adding ratings/reviews to a vibe project, assuming Next.js / Node + PostgreSQL + Redis. The throughline is directly shippable: data model → aggregation algorithms → write path → anti-gaming → review moderation → multi-dimensional ratings → display UX → caching → launch SOP. Each section ends with a decision table or checkpoint; code samples are runnable TypeScript + Prisma.

1. Three Layers: Collection, Aggregation, Display

Split into three layers before coding. Most failures come from mixing them — e.g., computing AVG(rating) live in the list endpoint (20 products, 20 aggregations, database melts); or computing Bayesian averages client-side (every sort change refetches everything). The layers:

1. Collection: users submit a 1–5 star rating plus optional text and photos. Answers only "who can rate, how many times, how many edits".
2. Aggregation: N ratings become "4.7 (2,312 reviews)" plus a distribution histogram. Answers only "how the score is computed and when it's recomputed".
3. Display: stars, distribution bars, sorting, filters. Answers only "what the user sees".

Iron rule for solo teams: persist aggregation results (database or cache) — never compute live. A product list showing 20 products with live AVG() is 20 aggregation queries; add distribution histograms (5 bucket COUNTs) and it's 120. Aggregate at write time, serve at read time — the architectural cornerstone of this entire guide.

Second upfront decision: one table or two for ratings vs. text reviews? One table (reviews), with rating required and body optional. "Stars without words" is the dominant behavior (typically 70%+); two tables just add a JOIN to the write path for zero benefit. Textless reviews count toward rating stats but collapse by default in review lists — a product detail that costs exactly one table.

2. Data Model: A Reviews Table + an Aggregates Table — Two Is Enough

model Review {
  id          String   @id @default(cuid())
  subjectType String   // "product" | "course" | ...
  subjectId   String
  authorId    String
  rating      Int      // 1–5, database CHECK constraint
  body        String?  @db.Text
  bodyHtml    String?  @db.Text // server-rendered when text exists (reuse the comment guide's sanitizing path)
  verified    Boolean  @default(false) // verified purchase/experience
  status      ReviewStatus @default(VISIBLE)
  helpfulCount Int     @default(0) // "helpful" votes
  editedAt    DateTime?
  createdAt   DateTime @default(now())

  @@unique([subjectType, subjectId, authorId]) // one review per user per subject — the anti-gaming cornerstone
  @@index([subjectType, subjectId, status, createdAt])
}

model ReviewAggregate {
  subjectType String
  subjectId   String
  count       Int    @default(0)  // VISIBLE review count
  sum         Int    @default(0)  // rating total (store the sum, not the average — avoids float drift)
  avg         Float  @default(0)  // display average, sum/count persisted
  bayesian    Float  @default(0)  // Bayesian average (see §3)
  wilson      Float  @default(0)  // Wilson interval lower bound (see §3)
  dist1..dist5 Int   // per-star counts — histogram reads free
  updatedAt   DateTime @updatedAt

  @@id([subjectType, subjectId])
}

enum ReviewStatus { VISIBLE HIDDEN SPAM }

Four details decide success: first, @@unique([subjectType, subjectId, authorId]) is the first wall against gaming — one review per user per subject; changes go through the edit flow (§4), never resubmission. Without it, a script writes 10,000 reviews in 10 minutes. Second, the aggregate stores sum, and avg divides at display time: float accumulation error becomes visible after ten thousand updates (4.6999999); integer sums stay exact forever. Third, denormalize the five dist buckets — histogram display costs zero queries; on update, decrement the old bucket and increment the new one in the same transaction. Fourth, verified is independent of the rating: "verified purchase" is written back by the order system — never let users self-declare; self-declared "purchased" is the same as nothing.

Why PostgreSQL for aggregates instead of Redis? Because aggregates need durability + auditability: Redis can be rebuilt after a loss, but "all ratings show 0 site-wide during rebuild" is an incident; PostgreSQL is the source of truth, Redis is only a read cache (see §9). Never invert that order.

2b. Why Not Database Materialized Views for Aggregation?

PostgreSQL has MATERIALIZED VIEWs — SELECT subjectId, AVG(rating), COUNT(*) in one shot, periodic REFRESH. Looks like the "official" aggregate table. Why does this guide insist on a hand-written aggregate table? Three reasons:

• REFRESH granularity is uncontrollable: REFRESH MATERIALIZED VIEW recomputes everything by default (CONCURRENTLY only avoids locking — same compute cost). Past a million reviews, an hourly full scan costs far more than "update one aggregate row on change". Materialized views are for BI reports, not online serving.
• Bayesian and Wilson don't fit view definitions: the formulas need the global mean m, a daily-changing constant — hardcode m in the view and it's stale tomorrow; compute m via function and every REFRESH becomes a nested full-table scan. A hand-written table + cron decouples m updates from per-subject recomputation — much cleaner.
• State-transition semantics are lost: SPAM confirmations, moderation flips, verified write-backs are "business events" — in a materialized view they're just another REFRESH, and you lose "why did this change" audit info. In the hand-written update function, every update logs an aggregate_changelog row (subjectId, delta, reason, at) — 30-second root-causing. Views can't give you that.

One line: a materialized view is "a cache of a query"; an aggregate table is "the business's ledger". Ledgers are hand-written, every entry traceable.

3. Four Aggregation Algorithms: An Average Is Not Just an Average

The most consequential decision in this guide. How "4.7" is computed decides whether rankings are fair and whether new products ever surface. Four algorithms, no silver bullet:

Algorithm	Formula (sketch)	What it fixes	Weakness	Best for
Simple average	sum / count	Nothing — most intuitive	One 5-star beats two thousand 4.7-stars; gaming maxes it directly	Mature categories where everything has thousands of reviews (sample-size differences negligible)
Bayesian average	(C·m + sum) / (C + count) C=prior count, m=prior mean (site-wide average)	Shrinks small samples toward the global mean: one 5-star ≈ global mean, no chart-topping	C needs tuning; new products get "averaged down" during cold start — pair with a "new" badge	Solo-team default: e-commerce, courses, app stores — anywhere ranking must be fair
Wilson lower bound	Lower bound of the 95% confidence interval on "positive rate"	Mathematically rigorous small-sample penalty	Complex formula; 1–5 stars must collapse to binary positive/negative — information loss	Reddit-style "helpful" vote ranking; less intuitive than Bayes for pure stars
Time-decay weighting	Recent ratings weigh more, w = 0.5^(age/halfLife)	Old bad reviews stop haunting reformed sellers; gaming windows compress	Historical reputation dilutes; heritage brands lose out; another parameter to tune	Food delivery, ride-hailing — experience is strongly time-sensitive

The solo-team default: display the simple average (lowest comprehension cost), sort by the Bayesian average (fairest). The page says "4.7 (2,312 reviews)" — simple average, instantly understood; but "sort by rating" uses the bayesian column — new products can't chart-top on a single five-star. Two numbers, two jobs — don't store only one.

How to set Bayesian C and m? m = the average of all VISIBLE reviews site-wide (or per category), recomputed nightly; C = "minimum statistically meaningful sample", empirically 10–25. With C=20, m=4.2: one 5-star scores (20×4.2+5)/21 ≈ 4.24 — properly behind two thousand 4.7-stars; two thousand 4.7-stars score ≈ 4.695, barely moved by the prior. Bigger C buries new products; smaller C eases gaming. Compute m per category (electronics' m and fresh produce's m are different animals) — never one site-wide number.

Should you show "92% positive"? Yes, but state the definition: positive = 4–5 stars, positive rate = (dist4+dist5)/count. Don't count 3-star as positive like some platforms do — one discovered inflation and trust is gone permanently. Same for negative rate.

3b. Wilson Intervals: A Mathematical Story for "Helpful" Ranking

§3 listed Wilson as an alternate — here's the intuition, because "most helpful" sorting genuinely needs it. The question: review A has 10 helpful / 0 unhelpful (100% positive), review B has 900 helpful / 100 unhelpful (90%). Who ranks first? Intuition says B (bigger sample, more credible), but naive positive-rate ranking picks A. The Wilson lower bound asks: "with 95% confidence, what's the lowest this review's true positive rate could be?" — A's bound ≈ 72%, B's ≈ 88%. B wins; math did the sample-size penalty for you.

// Wilson score lower bound (binary: helpful=success, unhelpful=failure)
function wilsonLowerBound(up, down, z = 1.96) {
  const n = up + down;
  if (n === 0) return 0;
  const p = up / n;
  const denom = 1 + (z * z) / n;
  const center = p + (z * z) / (2 * n);
  const spread = z * Math.sqrt((p * (1 - p) + (z * z) / (4 * n)) / n);
  return (center - spread) / denom;
}

Three shipping rules: first, Wilson is for "helpful"-vote ranking only, not star ranking — collapsing 1–5 stars to binary loses too much; stars sort by Bayes. Second, persist wilson (denormalized on the review row) and recompute inline on each vote — voting is high-frequency, the formula is a pure function, recompute cost is negligible. Third, z=1.96 (95% confidence) is convention — don't tune it; nobody can explain why you tuned it, pure communication cost.

When does Wilson beat "sort by helpfulCount"? When reviews have both helpful AND unhelpful votes. "900 helpful / 100 unhelpful" vs. "900 helpful / 0 unhelpful" tie on helpfulCount; Wilson ranks the latter higher — controversy gets mathematically expressed. With only a "helpful" button (no "unhelpful"), Wilson degenerates — just sort by helpfulCount.

4. Write Path: Idempotency, Verified Purchase, Edit Windows

Review writes are more sensitive than comment writes — every review rewrites the product's "face" (the aggregate score), so the path is stricter. Five stages:

1. Identity & eligibility: logged-in users + the @@unique constraint (one review per user per subject). "Must purchase to review"? By vertical: e-commerce/food-delivery mandatory (unverified reviews get verified=false and ranking demotion); content/tools can open up ("tried it" is unverifiable) — but openness accepts shills. See §5.
2. Idempotency: clientRequestId unique index (same pattern as the comment guide). Matters more here: a double-tapped "submit" without idempotency is two reviews — the aggregate is instantly polluted.
3. Range validation: integer 1–5, database CHECK as backstop. Half stars (4.5)? Not in v1 — halves double the complexity of aggregation, histograms, and Bayes for marginal perceived gain. Add when users genuinely complain there's nothing between 4 and 5.
4. In-transaction aggregate update: INSERT/UPDATE review + UPDATE aggregate(count±, sum±, dist buckets) in one transaction. Note the edit case: 5-star edited to 2-star means sum = sum - 5 + 2, dist5-1/dist2+1, count unchanged. Edits get a 30-day window (createdAt + 30d), locked after — kills the "five stars for cashback, flip to one star for revenge six months later" play.
5. Cache invalidation + events: after commit, delete the Redis aggregate key (agg:product:{id}), emit review.created/updated. Bayesian recalculation is not realtime — an hourly cron batch-recomputes subjects changed in the last 7 days (below).

How does verified get written back? The order system emits an event on order completion (delivery confirmed / service rendered); the review service subscribes and flips that user's review on that subject to verified=true. The write-back is async: users may review before receiving; verified arriving days late is normal — display whatever the current value is. Watch the ordering trap: should refunds revoke verified? Yes — the refund-completion event flips verified back to false; otherwise "five stars then refund, score stays" is standard operating procedure for review farms (see the Refund & Dispute guide's companion section).

Anonymous reviews? Not supported. Anonymous ratings = free gaming. All reviews require login; display names masked ("J***") — privacy via masking, not anonymity, and never expose userIds.

4b. Pending-Review State Machine: From "Order Complete" to "Review Banked"

Reviews don't appear from thin air — the "pending review" intermediate state drives them. Order complete → user gets "rate this experience" → reviews (or expires in 30 days). A tiny state machine; skipping it costs you 80% of review volume:

pending_reviews(userId, subjectType, subjectId, orderId, expiresAt)
// order-completion event → insert one (dedupe: one pending per user per subject, keep latest)
// user submits review → delete the matching pending row
// expiresAt (30 days) → cron sweeps expired rows

Three product rules: first, nudge at most twice (once + one reminder after 7 days) — nudges go through the notification center (see the Notification Center guide); the review side only exposes the "pending reviews" list API. Second, the pending list is the highest-converting review entry point (fresh experience) — surface it in "my orders" and the profile hub, not buried three menus deep. Third, expiry means silence: auto-sweep at 30 days, no "last chance" nag — harassment costs uninstalls.

Should reviewing earn coupons? "Cashback for five stars" is textbook review manipulation and usually banned by platform policy; "points for any review" (regardless of stars) is compliant — the difference is incentives must never couple to the star value. Engineering-wise: grant points on the review.created event, independent of rating; a line like if (rating === 5) giveCoupon() gives legal a heart attack.

4c. Edit Audit Trail: Every Score Change on Record

The 30-day edit window (§4) raises a question: a user flips 5 stars to 1, the seller complains "but they gave five stars!" — how do you check? Audit-trail every review edit. Not optional:

model ReviewEditHistory {
  id        String   @id @default(cuid())
  reviewId  String
  oldRating Int
  newRating Int
  oldBody   String?  @db.Text
  newBody   String?  @db.Text
  editedAt  DateTime @default(now())
  ip        String?  // the last mile: invaluable when accounts get hijacked for mass edits

  @@index([reviewId, editedAt])
}

One create(reviewEditHistory) inside the write transaction — negligible cost, 30-second truth restoration on complaints. History never shows to ordinary users (ops back-office and seller appeals only) — showing "edited 3 times" escalates conflict; showing "currently 1 star (edited within 30 days)" is neutral fact. Also: hijacked accounts bulk-editing reviews is a real attack — the ip column plus a "one IP edited 50 reviews in an hour" alert catches it fast.

5. Anti-Gaming: Four Signal Layers + Human Review

Fake positives and malicious negatives are two faces of one coin; the defenses are shared. Four signal layers, weakest first:

1. Identity (≈free): one review per user per subject (unique constraint) + reviews from accounts under 7 days old don't count toward aggregates (status=VISIBLE but counted=false, labeled "new-user review"). Review farms hate this: 7 days of account aging before gaming — the business model breaks.
2. Behavior (≈free): one device fingerprint reviewing >5 distinct subjects in 24h → all to human review; star/text sentiment mismatch (5 stars + "scam garbage") → auto-FLAGGED (a simple sentiment lexicon suffices — no ML needed for a solo team); "extremist" users (only 1-star or only 5-star) get ranking demotion, not deletion — wrongly deleting a real bad review is a PR disaster.
3. Correlation (medium cost): a burst of same-score reviews on one subject → "review anomaly" alert (1-hour additions > 10× the 7-day mean); reviewer↔seller linkage (same device, same IP block, same payout account) → straight to SPAM. The former is the Observability guide's alerting applied to reviews; the latter needs order/account correlation data — solo teams can ship the former first.
4. Human review (most expensive): flagged reviews queue for eyeballs. The review UI must show "this user's historical rating distribution" (all-5-star or all-1-star obvious at a glance) and a simplified "device/IP association graph" (list sibling accounts on the same device). Design for 10 minutes a day of clearing; more means layers 1–3 are leaking — fix those first.

How to handle confirmed gaming? Flip to SPAM + roll back aggregates (count-1, sum-rating, bucket-1) in the same transaction. Hiding without rollback leaves the fake score in the aggregate — equivalent to doing nothing. When a seller is caught: 30-day demotion or delisting? That's an ops decision — keep a penaltyUntil field; during penalty, the subject's Bayesian score × 0.9 at sort time. Make penalties configurable — argue over config, not code.

Malicious negatives (competitors scripting 1-stars) have signatures: clustered in time, templated or empty text, disproportionate unverified share. Handle: FLAGGED first, SPAM + rollback on confirmation, and notify the seller — "detected an abnormal negative-review attack, intercepted N items." That notification is a seller-retention superpower — the platform is "protecting" them; renewal intent transforms.

6. Text-Review Moderation: Reuse the Comment Pipeline

A text review is essentially "a comment with stars" — moderation reuses the Comment Systems guide's §6 four-layer defense (rate-limit → keywords → cloud API → human review). Don't build a second one. Two differentiators:

• Textless pure-star ratings skip moderation (a number has no policy-violating surface); only non-empty body enters the pipeline. Saves ~70% of moderation cost.
• Failed text moderation hides the text, keeps the stars: split status into ratingStatus and bodyStatus. A user posts 5 stars + violating text — text HIDDEN, stars still aggregate. Blanket-burying the rating too triggers "but I gave five stars, why doesn't it count" complaints. The split costs one schema decision; halves complaint volume.

Review photos ride the Image Processing Pipeline guide's path: multi-size, EXIF auto-orient, watermark. One extra requirement for review photos: stolen-photo detection — the same image (phash similarity > 0.95) appearing in 3+ distinct users' reviews auto-FLAGGEDs. That's the fingerprint of review-farm studios ("one photo set for the whole network"). A phash library (dozens of lines) does it for a solo team — no vector database needed.

7. Multi-Dimensional Ratings: Only When One Score Isn't Enough

Food delivery wants "taste / speed / packaging"; hotels want "location / service / cleanliness"; courses want "content / instructor / assignments". When to add dimensions? When users complain "4.5 overall but I can't tell what's wrong." One total suffices for v1. Dimensions cost linearly: each adds a column to the aggregate table (sumTaste/count), an extra update in the write transaction, a histogram group.

Three rules once added: first, ≤4 dimensions — beyond that users rate randomly (choice overload in psychology, "data quality collapse" in engineering); second, total = plain mean of dimensions, no weights — weights are lose-lose-lose (product guesses, users don't understand, ops can't tune); express "delivery matters more than packaging" via display order (important first), not math; third, dimensions display only, never sort — sorting uses the total's Bayesian value; "sort by taste score" is a pseudo-requirement 99% of the time. Cut it.

Data model: Review.dimensions Json (e.g., {"taste":5,"speed":4}), aggregates keep dimSums Json. JSON for dimensions rather than columns because dimensions are product-configurable (ops adds a dimension without schema migration). Validate in app code: dimension keys must exist in the active dimension config, values integers 1–5. Config changes (retiring "packaging") don't touch history — historical JSON stays, display filters by current config.

7b. Multi-Dimension Aggregation Code: Updating JSON Columns Atomically

§7 stores dimension scores in JSON — here's the update logic pinned down, because AI-generated code fails most at JSON-column updates (read, modify, write back — three non-atomic steps). Do it atomically with database JSON functions:

-- PostgreSQL: atomic dimension accumulation (taste +5)
UPDATE review_aggregates
SET dim_sums = jsonb_set(
      COALESCE(dim_sums, '{}'),
      '{taste}',
      to_jsonb(COALESCE((dim_sums->>'taste')::int, 0) + 5)
    ),
    dim_counts = jsonb_set(
      COALESCE(dim_counts, '{}'),
      '{taste}',
      to_jsonb(COALESCE((dim_counts->>'taste')::int, 0) + 1)
    )
WHERE subject_type = 'product' AND subject_id = 'xxx';

Keep dim_sums and dim_counts separate: users may rate "taste" but skip "delivery" (dimensions optional), so each dimension averages independently — missing dimensions don't participate. Display "Taste 4.6 (231 ratings) / Delivery 4.2 (198 ratings)" — always show each dimension's sample size; a dimension score without sample size is dishonest. On dimension-config changes, history stays but display filters; the recompute script skips retired dimensions — tag each review with the config version (dimVersion) active at write time, and recompute only the current version.

8. Display UX: Stars, Histograms, Sorting & Filters

The display layer is the rating system's "face" — four components:

1. Star display: 4.7 shows 4.7 stars (full + partial via CSS gradient/clipping — never "round to whole stars": 4.7 as 5 stars feels fraudulent, as 4 feels stingy). Stars must sit next to "(2,312 reviews)" — a bare 4.7 has no credibility. Amber (amber-400) is the industry convention; don't innovate purple stars.
2. Distribution histogram: 5★ ████████ 68%, 4★ ███ 15%… — read straight from dist1..dist5. The histogram is a "gaming polygraph": healthy products show a J-shape (5-star most, 1-star second, middle thin); gamed ones are deformed (99% 5-star or 99% 1-star). Flag "abnormal distribution" in the ops back-office (>95% 5-star or >50% 1-star auto-red) — ten times more legible than numbers.
3. Review-list sorting: default "most helpful" (helpfulCount DESC, createdAt DESC), switchable to "newest". Never default to "highest rated" — that's gamer heaven. Verified reviews get a "verified purchase" badge and sort weighting (verified helpfulCount × 1.5) so real voices surface.
4. "Helpful" votes: one "helpful" button per review, review_helpful(reviewId, userId) unique constraint, vote-gaming protection (≤20 votes/min per user). Helpful counts fuel list sorting and double as gamer signals (gamed reviews usually sit at 0 helpful — nobody actually reads them).

Filters (by star, with-photos-only, verified-only) are standard for review lists — all WHERE combinations, covered by a (subjectType, subjectId, status, rating, createdAt) index. Note "show only bad reviews" (1–2 stars) is a high-frequency decision entry for shoppers; the seller back-office doubly needs a "bad-review clustering" view (keyword clusters like "too salty", "slow delivery") — that's a renewal argument: the platform doesn't just score, it tells sellers how to improve.

Mobile notes: on narrow screens put histogram percentages outside the bars, not squeezed inside; star touch targets ≥44px — five cramped stars and fat-fingered users can never reliably tap 5 stars. A real complaint category.

9. Aggregate Caching & Invalidation: Redis Is Read Acceleration Only

§8 established the aggregate table as source of truth; Redis is the read cache. Three caching rules:

1. Key design: agg:{subjectType}:{subjectId}, value = the aggregate row as JSON (avg, bayesian, count, dist1–5), TTL 1 hour. A 20-product list page = one Redis MGET of 20 keys, P99 < 5ms. Don't split keys per field (avg:xxx, count:xxx) — invalidation then deletes N keys, and one missed key = dirty data.
2. Invalidation: delete-on-write, not write-through. After the review write/edit/status-flip transaction commits, DEL agg:product:123; the next read misses and reloads from the aggregate table (read-through). Write-through (updating avg inside Redis) reimplements aggregation in app code, non-atomically with the DB transaction — a crash leaves dirt. Deletion is idempotent; rebuild is single-path. Simple is reliable.
3. Bayesian recompute on cron, never realtime: hourly job scans subjects changed in the last hour (ReviewAggregate.updatedAt > now()-1h), recomputes bayesian (compute global m first, then per subject), writes back + deletes the Redis keys. Global m recomputes nightly at low traffic. Never recompute Bayes per review write — m is a site-wide constant; high-frequency recompute is wasteful and meaningless.

Cache penetration (nonexistent subjects) gets a bloom filter or null-caching (agg:xxx = NULL cached 5 min); stampedes (mass simultaneous expiry) get TTL jitter (1 hour ± 10 min). Standard Cache Strategy guide fare — copy it, not expanded here.

A cold fact: the aggregate table's updatedAt is one of the site's most useful "freshness signals". "Products with new reviews in the last 7 days" is one ORDER BY updatedAt DESC — a zero-cost "rising reputation this week" leaderboard for ops.

10. Pitfall Catalog: Twelve Real Crash Scenes

1. Live AVG(): per-product live aggregation on list pages — 20 products, 20 AVGs + 100 COUNTs. Persist aggregates; serve on read.
2. One 5-star chart-topping: naive-average sorting puts a single-five-star new product first. Sort by Bayesian average.
3. No one-review-per-user constraint: a script writes 10k reviews in 10 minutes. @@unique(subjectType, subjectId, authorId) is the first wall.
4. Float-stored averages: 4.6999999 after ten thousand updates. Store integer sum; divide at display.
5. Self-declared verified: users ticking "purchased". Verified must come from order-event write-back.
6. Refunds don't revoke verified: "five stars then refund, score stays" is farm SOP. Refund events flip it back to false.
7. Unbounded edit windows: "five stars for cashback → one star for revenge six months later". 30-day window, locked after.
8. Blanket-burying on moderation failure: violating text buries the 5 stars too — complaint explosion. Split ratingStatus/bodyStatus.
9. Write-through Redis: reimplementing aggregation in app code, non-atomic with the transaction — crash = dirt. Delete-on-write + read-through.
10. Half stars in v1: doubles aggregation, histogram, and Bayes complexity. Integer 1–5 first; add when complained about.
11. "No ratings yet" on zero-review products: tells shoppers nobody buys — −15% conversion. Hide the module at count=0, or show "new arrival".
12. Silent Bayesian-cron failure: dead cron, stale scores site-wide, no errors. Cron's last step writes a heartbeat; monitor heartbeat timeouts.

10b. Three Late Lessons

• Zero-review products shouldn't say "no ratings yet": it advertises "nobody buys" (−15% conversion). Hide the rating module, or show "new arrival, be the first to try". Zero-count UI copy is a product detail, but engineering must support conditional rendering on count=0 — the aggregate table's count is the signal.
• Aggregate-recompute cron failure is a silent incident: dead cron, stale scores everywhere, no errors, no alerts — until the boss notices "last week's hit still shows 0 reviews". A cron's last step must write a heartbeat (UPDATE cron_heartbeat SET last_ok=now() WHERE job='bayesian_recalc'); monitor heartbeat timeouts. Applies to all crons, not just ratings.
• Review-photo CDN bills: user-generated photo volume eventually shocks the CDN invoice. Route review photos through the Image Pipeline guide's three sizes; lists load thumb (256px) only, detail loads card; originals load on "view original" — 99% of users never tap, but 100% of the bill counts.

11. Reviews & SEO: Getting Stars into Search Results

The "★★★★★ 4.7 (2,312)" next to products on Google is a click-through amplifier. The mechanism is AggregateRating structured data:

{
  "@context": "https://schema.org",
  "@type": "Product",
  "name": "Example product",
  "aggregateRating": {
    "@type": "AggregateRating",
    "ratingValue": "4.7",      // simple average, one decimal
    "reviewCount": "2312",     // VISIBLE reviews only
    "bestRating": "5"
  }
}

Three red lines (Google penalizes all three): first, ratingValue must be the true aggregate — writing 5.0 for clicks gets rich-result eligibility revoked on report; second, reviewCount counts VISIBLE only — including SPAM is data fabrication; third, the reviews must genuinely display on the page — structured data without visible reviews reads as manipulation. SSR the JSON-LD (server reads the aggregate table directly, zero extra queries); it coexists fine with the comment guide's DiscussionForumPosting on the same page.

Per-review detail pages? Not in v1. They're a thin-content disaster zone (a 20-word review holding up a whole page) — SEO negative. Reviews live on the subject page, anchored (#review-xxx). Promote a "legendary review" (helpfulCount > 1000) to its own page only as an ops action — not an engineering default.

12. Launch SOP: Check Every Box Before Shipping

1. Data model: @@unique(subjectType, subjectId, authorId) proven (concurrent double-submit → exactly 1 review), rating CHECK 1–5, aggregate columns sum/count/dist1–5 complete, indexes (subjectType, subjectId, status, createdAt) + (subjectType, subjectId, status, rating, createdAt) built.
2. Aggregation: simple average for display, Bayesian for sorting, m (category mean) cron'd nightly, C (10–25) configured per category, bayesian/wilson columns non-NULL (spot-check 100 subjects).
3. Write path: idempotency dedup (double-click → 1 review), 30-day edit window (day-31 edits rejected), verified via async order events (mock a refund, verify revocation), in-transaction aggregate updates (mock a rollback, verify aggregates untouched), Redis key deleted post-commit.
4. Anti-gaming: under-7-day users excluded from aggregates, device fingerprint >5 subjects/24h → review queue, anomaly alerts (1h additions > 10× 7-day mean) configured, SPAM confirmation rolls back aggregates in the same transaction, seller penalty penaltyUntil configurable.
5. Moderation: text-bearing reviews only enter the pipeline, ratingStatus/bodyStatus split (HIDDEN text keeps counting stars), review-photo phash dedup (similarity > 0.95 → review queue).
6. Display: fractional star rendering (4.7 shows 4.7 stars), histogram from dist buckets, default "most helpful" sort, verified badges, helpful-vote unique constraint, ≥44px mobile touch targets.
7. Caching: single-key whole-row JSON, delete-on-write + read-through, hourly Bayesian cron, TTL jitter against stampedes, null-caching against penetration.
8. SEO: AggregateRating JSON-LD SSR'd, ratingValue = true value, reviewCount VISIBLE-only, reviews genuinely displayed on-page.
9. Observability: review-write P99, aggregate-recompute duration, SPAM confirmation rate, anomaly-alert firings on the dashboard; Bayesian-cron failure alerts (failure = site-wide ranking frozen on stale values — more insidious than an error).

13. B2B Ratings: When It's "Client Rates Vendor"

Everything so far assumed C2C (users rate products). B2B (clients rate vendors, employers rate freelancers) differs in three ways solo SaaS builders meet sooner or later:

• Two-way ratings: A rates B and B rates A ("does this client pay promptly?"). Same table, plus direction (CLIENT_TO_VENDOR / VENDOR_TO_CLIENT), aggregates computed per direction. Display both — "4.8 (as vendor) / 4.9 (as client)" — symmetry is fairness.
• Simultaneous reveal: two-way ratings must go public together (both submitted, or auto-reveal after 14 days). Seeing the other's bad review first invites retaliatory 1-stars — the classic two-way game. Implementation: status=SEALED (submitted, unrevealed); flip batch to VISIBLE + recompute when both land or expiry hits. One more state in the machine — reserve it in the schema now.
• Ratings as contract exhibits: B2B bad reviews can enter legal proceedings, so ReviewEditHistory becomes WORM (write-once) — database triggers forbidding UPDATE/DELETE. C2C can purge after 180 days; B2B audit trails live as long as the contract does.

One more B2B subtlety: bad-review rates run structurally lower than C2C (parties keep doing business; bridge-burning is expensive), so the Bayesian prior m must be computed per scenario — B2B's m might be 4.6 vs. C2C's 4.2; mixing them makes a B2B 4.5 look like a bad review. Per-scenario m in config, never one global number.

14. Cross-Border Reviews: Language, Timezone, Culture

Three engineering points for reviews going global (general i18n mechanics live in the i18n guide — only review-specifics here):

• Original + machine translation, side by side: a Chinese shopper reading English reviews gets a "translate" toggle; cache translations (review_translations(reviewId, locale, text) — translation APIs bill per call; caching is money). Translate for display, never for storage: the original is forever the source of truth; a mistranslation is recoverable when the original sits beside it; storing translations as originals hides errors permanently.
• Regional rating cultures: the same 4.0 reads as "meh" in generous-rating markets and "good" in strict ones. Don't normalize displayed scores (4.0 is 4.0) — but compute the Bayesian prior m per region for sorting. Local fairness, same logic as B2B's per-scenario m.
• Timezone and date display: store UTC, display per user timezone (the comment guide's "store UTC, show local"). One more cross-border wrinkle: holiday periods skew distributions (Ramadan, Lunar New Year) — set anomaly-alert baselines per region, not one global threshold. Post-Eid review volume tripling in MENA is normal, not an attack.

15. North-Star Metrics: Knowing Your Ratings Are Trustworthy

After launch, four metrics weekly — ten minutes:

• Review penetration = orders with reviews / total orders. Healthy band 5–15% (varies wildly by vertical); watch the trend: after shipping pending-review nudges it should 2–3×; flat means the entry is buried.
• Verified share = verified reviews / total. Below 50% means the "must purchase" chain is leaking (order events unwired) — or unverified reviews were deliberately opened. The former is a bug; the latter a product decision. Know which.
• Distribution health = site-wide dist shape. Healthy is J-shaped (5-star 60–75%, 1-star 8–15%); >90% 5-star means farming or "cashback for five stars" is circulating; >30% 1-star means a category-level quality incident. Distribution is the site's bloodwork — more honest than the average.
• Review→conversion attribution: order conversion of review-viewers vs. non-viewers. In commerce this gap usually runs 20–50% — the rating system's armor in planning meetings. It echoes the comment guide's metric: comments own "discussion heat", reviews own "trust conversion" — two metrics, two jobs.

Dashboard the metrics, alert the thresholds. The rating system is now closed-loop: model, algorithms, write path, anti-gaming, moderation, display, caching, SEO, measurement — nine pieces assembled. What remains is ten minutes a day glancing at the distribution chart — the day 5-star share hits 99%, you'll thank this guide.

16. From Ratings to Reputation Ops: Three Things Beyond Engineering

Shipping the system is the start; reputation is operated. Three engineering-ops collaborations:

• Bad-review ticket flow: 1–2 star reviews auto-create seller todos ("3 new bad reviews to handle"); sellers' replies notify the reviewer (via notification-center events). Platforms where "sellers reply to bad reviews within 24h" see 15–30% bad→good flip rates. Engineering-wise it's a review.created(rating<=2) event → seller-todo table — one line of code, enormous ops value.
• Review text feeding search: natural language in reviews ("good for oily skin", "two-day battery") is long-tail search gold. Index de-identified review text in site search (see the Search Experience guide's UGC-indexing chapter) — "oily-skin foundation" appears in no product title but in hundreds of reviews. Findable = convertible.
• Annual reputation reports: year-end "how your 4.7 happened" per seller (distribution drift, bad-review keywords, reply rate) — PDF export via the PDF Export guide's pipeline. The best material on the renewal-negotiation table: the platform doesn't just take commission, it coaches growth.

Appendix: A Prompt Template — Generating Review Modules to This Spec

A vibe-coding exclusive to close: a directly usable prompt template. Compress this guide's decisions into AI-executable constraints — paste it next time AI generates your review module and skip half the pitfalls:

Generate a review module for my Next.js + Prisma + PostgreSQL project.
Hard constraints:
1. Tables: reviews (subjectType/subjectId/authorId/rating 1-5/
   verified/body/status) + review_aggregates (count/sum/
   avg/bayesian/dist1-5), @@unique([subjectType, subjectId, authorId])
2. No live AVG/COUNT on read paths — aggregates must persist;
   display avg, sort by bayesian (C=20, m refreshed daily per category)
3. Write path: clientRequestId idempotency → validation →
   transaction (INSERT/UPDATE + aggregate update) → post-commit
   delete Redis key agg:{type}:{id} → emit events
4. verified only via async order-event write-back, never self-declared;
   refund events must flip verified back to false
5. 30-day edit window; every edit logged to review_edit_history
6. Comment concepts (path/floors) are forbidden in this table
   (that's the comments module's business)
Output the Prisma schema and write-transaction code first,
then the aggregate-recompute cron.

The template's essence is "constraints first": AI doesn't lack implementation skill — it lacks decisions. Feed it this guide's decisions and its first draft lands near production-grade. Happy vibing — and may your ratings be real.

A final word: the hardest part of a rating system was never the code — it's "daring to let bad reviews live". Deleting bad reviews feels good for a day; broken trust never comes back. Treat every bad review as free user research — each problem it names is an opportunity your competitors haven't found. May your stars be real, every one of them standing up to scrutiny — that is the entire point of reputation engineering, and it's worth doing right.