Design a Food Rating System

Design a rating and review system for a food delivery marketplace (e.g. DoorDash). Users of the system should be able to: - Rate a food item on a fixed scale and optionally attach a text review. - View the **average rating** for each food item. - View the list of reviews for a food item, **sorted by recency, helpfulness, or score**. - **Upvote or downvote** another user's review. The system must also support an **automatic reward flow**: when a review accumulates enough positive engagement, its author may receive a one-time monetary reward (e.g. account credit). Your design should cover the **data model (database tables), API surface, the read and write flows, consistency choices, and how the system handles client retries and duplicate requests** end-to-end. ```hint Where to start Notice the requirements have two different consistency demands living side by side: the average rating and vote counts are explicitly allowed to lag, but an acknowledged review/vote/reward must never be lost. That tension usually pushes toward separating *what is canonical* from *what is merely displayed*. Decide early which tables you'd be willing to rebuild from scratch and which ones you'd never recompute — that line shapes the rest of the design. ``` ```hint The two "exactly-once" spots Two requirements are really uniqueness rules in disguise: *one vote per user per review* and *one reward per qualifying review/rule*. Before reaching for application-level "check then write" logic (which races under retries and concurrency), ask what the **database itself** can guarantee for free, and what the natural key for each of those two facts would be. ``` ```hint Updating the average without losing writes If you update the running average inline on every write, hot items contend on one row and a failed mid-write leaves the average and the source data disagreeing. Think about how to make the canonical write and the "tell the aggregator something changed" signal succeed or fail *together*, and what you'd store per food item so the average can be recomputed exactly (hint: a single float field is hard to correct after an edit). ``` ```hint Surviving retries on the payout The reward touches an external payment service, so two failures matter: the worker could try to reward the same review twice, and a single payout call could time out and get retried. These are *different* problems at *different* layers — one is about not creating a second reward record, the other is about the money side of a retried call. Also consider: should you trust the vote count from when eligibility was first noticed, or re-check it at payout time? ``` ### Constraints & Assumptions State your assumptions explicitly; reasonable starting numbers: - **Scale:** millions of food items, tens of millions of reviews, read-heavy — assume on the order of 100:1 reads to writes. Reads of rating summaries and top reviews dominate. - **Rating scale:** integer 1–5; one review per (user, food item), editable. - **Latency:** rating-summary and review-list reads should be served in tens of milliseconds; write acknowledgements should be fast and durable. - **Consistency:** average rating and vote counts may be eventually consistent (a few seconds of lag is acceptable). Reviews, votes, and reward *eligibility decisions* must be durable — a write that the user sees acknowledged must never be silently lost. - **Reward:** a single reward rule (e.g. "review reaches score ≥ N with a healthy upvote ratio") pays a fixed credit once per review. Treat the rule set as configurable. ### Clarifying Questions to Ask - What is the rating scale, and may a user submit more than one review per food item (or only edit a single one)? - Are vote counts and average rating allowed to be stale, and if so, what lag is tolerable? - What exactly defines reward eligibility — raw upvotes, net score, an upvote ratio, a time window — and is the reward a fixed amount or variable? - Can a user vote on their own review, and do we need to defend against coordinated/fraudulent voting before paying out? - What are the read/write volumes and the hot-item distribution (are a few items or reviews disproportionately popular)? - Is moderation (spam, abusive content, takedowns) in scope, and does a removed review's votes need to be reversed? ### What a Strong Answer Covers - **Requirements split** into functional vs. non-functional, with a clear back-of-envelope estimate of read/write volume that justifies the architecture. - A **normalized relational data model** with the right keys and unique constraints, plus separate **aggregate tables** for the read path; an explicit choice of what is canonical vs. derived. - A clean **API surface** with idempotent write semantics (idempotency keys / natural-key UPSERTs). - The **write path** for ratings/reviews and for votes, including how aggregates get updated (synchronous vs. asynchronous tradeoff) and how durability is guaranteed. - The **read path**: how rating summaries and sorted/paginated review lists are served quickly (caching, materialized aggregates, sort indexes), including the cache-miss fallback. - A correct **eventual-consistency mechanism** (atomic write+event emission, idempotent consumers, and the ability to rebuild aggregates from source of truth). - A **reward flow** that is exactly-once at both the DB layer and the external payment layer, with protection against paying out on stale or fraudulent counts. - **Retry / duplicate-request handling** end-to-end, not just hand-waved. - **Scaling** (sharding/partitioning keys, hot-key handling, caching) and **observability** (aggregate lag, consumer failures, reconciliation). ### Follow-up Questions - A handful of reviews go viral and receive votes far faster than a single row can be updated. How do you keep the vote counter from becoming a write hotspot while still enforcing one vote per user? - A bug double-counted votes for an hour. How do you detect the drift and repair the aggregates without taking the system offline? - The payment service times out after you've inserted the `rewards` row — you don't know if the credit was issued. How do you guarantee the author is paid exactly once? - Product wants reviews sortable by a "helpfulness" score that blends votes, recency, and reviewer reputation. How does that change your read model and indexes?