Design a ratings and reviews service

Instead, keep one aggregate row per item: a count for each of the 5 star values. The average is the sum of stars times counts, divided by the total, worked out on read. The row is about 100 bytes.

• Reviews table. One row per user and item, with a unique key on the pair. Shard by item ID, so an item's reviews and its aggregate live together, and the unique key is checked inside one shard.
• Versions. Every edit also appends a row to an append-only versions table. Moderators can see what a review said before it was changed.
• Counted stars. Each review row carries a field called counted_stars: the star value currently included in the histogram, or empty if none.

The trap is to update the histogram with plus one and minus one from the event. A retried event adds twice, and events that arrive out of order give wrong totals. Use the review row as the truth instead. On each event, the worker runs one transaction:

• Read the review row. The target is its live stars if it is published, or empty if it is pending, removed or deleted.
• If counted_stars already equals the target, stop. This makes duplicates harmless.
• Otherwise subtract one from the bucket of counted_stars, if any, and add one to the bucket of the target, if any. Then set counted_stars to the target.

The worker ignores the numbers in the event and reads the current row, so any order of events and any number of retries ends in the same state. Partition the queue by item ID so one worker handles an item at a time. A row lock on the aggregate covers the rest.

Repair. Even good code can drift: a bad deploy, a manual fix. Each night, recount a thirtieth of the items from their reviews and compare. In our estimate that is 667,000 items and 3.3 million review rows a night. Fix any difference and count it. A steady stream of drift means a bug, not routine noise.

Read your own write. The author sees their review at once because the API returns it in the response. The histogram follows within seconds.

A review with 5 helpful votes out of 5 has a 100% share, and one with 90 out of 100 has 90%. The first has far less evidence. Sorting by raw vote count has the opposite flaw: old reviews pile up votes, and new ones never get seen.

Use the lower bound of the Wilson score interval. It answers: given these votes, what is the lowest share of readers who plausibly find the review helpful, at 95% confidence? With n votes, share p and z = 1.96, the score is (p + z²/2n − z × √((p(1−p) + z²/4n)/n)) ÷ (1 + z²/n).

• 5 of 5 votes: p = 1, and the score works out to about 0.57.
• 90 of 100 votes: p = 0.9, and the score is about 0.83.
• So the review with more evidence ranks first, even though its share is lower. With few votes the bound is low, and it rises as evidence builds.

A review with no votes scores 0. Break ties by date, newest first, and offer a Newest sort so fresh reviews are still read.

Votes are counters on the review row, one vote per user per review, enforced by a unique key, and never on your own review. Recompute scores in batches every few minutes, not on each vote, and write the new score into the search index.

The search index holds one document per published review: item ID, stars, text, date, verified flag and helpful score. Route by item ID, so one shard answers a query. A page of reviews is then a filter on item, an optional filter on stars or verified, an optional text match for a keyword such as battery, and a sort on score or date. Page with a cursor that remembers the last sort value, not an offset, which slows with every page.

The database stays the source of truth. If the index is lost or wrong, rebuild it from the reviews table. The index trails by a few seconds, which readers do not notice.

Assume attackers will try, and build several layers.

• Pending first. A new review is stored as pending and is visible only to its author. The pipeline checks it and publishes it. Most pass within seconds. Some are held for a human.
• Content checks. Look for the same text pasted across many items, links and phone numbers, and a classifier score for abuse. Hash the text so exact copies are found fast.
• Behaviour checks. Many five-star reviews on one item within an hour from accounts less than a week old is a strong signal. So is a user who reviews 30 items in a day.
• Verified purchase. The pipeline asks order history whether the user bought the item, and stores the answer as a flag. Show a badge and offer a verified-only filter. The call is cached, since the answer rarely changes.
• Rate limits. Cap reviews per account per day, lower for new accounts, on top of the unique key that stops a second review of the same item.

Edits. An edit enters the same checks, but the live version stays visible until the new one passes. Otherwise a user's review vanishes while they fix a typo. The live stars are what the histogram counts, so the counted-stars rule still holds.

Removal. Removing a review, by a moderator, by its author, or because an account was closed, sets its status. The pipeline then does the same thing as for any change. It reads the row, sees a target of empty, takes the stars out of the histogram, and deletes the index document. One code path handles publishing, editing and removing, which is why it is easy to trust.

To clean up a spam wave, mark thousands of reviews removed in one batch and enqueue an event for each. Because the update converges, a replay is safe. Keep the removed rows and their versions for audit and for appeals, and never show them.