Architect a Ratings and Comments Subsystem

Design a production-grade ratings subsystem with PostgreSQL and Redis caching.

Introduction

30 Second Summary

Every time you check a product's star rating or scroll through reviews before buying, you're interacting with a system that handles thousands of reads per second while keeping every average accurate and up to date. Designing that system from scratch is one of the most common system design challenges in software engineering.

In this project, you will architect a complete ratings and comments subsystem for a software marketplace. You'll produce an implementation-ready document with database schemas, caching strategies, and data flow diagrams that an AI agent can pick up and build directly.

What You'll Build

Hand your finished architecture document to an AI coding agent and watch it implement a production-grade ratings subsystem for your marketplace, complete with sub-5ms read latency and stampede-proof caching, without needing a single follow-up question.

By the end of this project, you'll have:

  • A capacity estimation worksheet showing exactly how 10K daily active users translates to peak read/write throughput, storage growth, and the concrete numbers that drive every downstream architecture decision.
  • A PostgreSQL schema with trigger-based aggregate computation and one-review-per-user enforcement across products, sellers, and auction completions, rendered as a Mermaid ER diagram.
  • A Redis cache-aside layer with Hash-based caching, sorted sets for rankings, and Lua-based stampede prevention, documented with exact key patterns, TTL decisions, and sequence diagrams showing the full review lifecycle.
  • Secret Mission: Design a post-auction bidirectional rating flow with time-bounded submission windows and escrow-linked verification.

Are there any prerequisites?

This project assumes familiarity with PostgreSQL, Redis concepts, and system design fundamentals. You don't need to be an expert, but you should understand what tables, indexes, and key-value caches do.

All tooling is free and browser-based. You'll use the Mermaid Live Editor at mermaid.live and any text editor (Cursor recommended).

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Project Structure and Mermaid Tooling

Your architecture document needs to live in a structured workspace. Without dedicated files for each concern (capacity, schema, data flow), your diagrams and notes become scattered and difficult for an AI agent to parse into implementation tasks.

In this step, you'll create a project folder with five architecture files, verify that Mermaid Live Editor renders diagrams in your browser, and configure your text editor for live markdown preview.

In this step, get ready to:
  • Create the marketplace-ratings-arch/ folder with five architecture files.
  • Verify Mermaid Live Editor renders an ER diagram in your browser.
  • Configure your text editor with Mermaid-compatible markdown preview.
Create the project folder and files

Each file in your project handles one architectural concern. Splitting them keeps each document focused and gives your AI agent clean boundaries when it reads the spec.

  • Open your terminal by pressing Cmd+Space (macOS) or the Windows key (Windows) and typing Terminal, then pressing Enter.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create the project folder and move into it by running these commands:
mkdir marketplace-ratings-arch
cd marketplace-ratings-arch
  • Create the five architecture files by running this command:

macOS

touch README.md capacity.md schema.sql architecture.md data-flow.md

Windows

New-Item -ItemType File -Path "README.md", "capacity.md", "schema.sql", "architecture.md", "data-flow.md"
  • Confirm the files exist by running:
ls

You should see all five files listed: README.md, capacity.md, schema.sql, architecture.md, and data-flow.md.

Not seeing all five files?

Check that you ran the touch or New-Item command from inside the marketplace-ratings-arch/ folder. Run pwd to confirm your current directory ends with marketplace-ratings-arch.

If you see an empty result, re-run the file creation command from the correct directory.

Help me figure out what went wrong with creating my project files

Open Mermaid Live Editor and verify rendering

Mermaid Live Editor is a free browser-based tool that renders diagrams from text. You'll use it throughout this project to preview ER diagrams, flowcharts, and sequence diagrams before embedding them in your markdown files.

  • Open your browser and navigate to mermaid.live.
  • Clear any default content in the editor pane on the left side of the screen.
  • Paste this minimal ER diagram into the editor:
erDiagram
    PRODUCT ||--|{ REVIEW : has

What does this diagram describe?

This defines a one-to-many relationship: one PRODUCT has many REVIEWs. The ||--|{ notation is Mermaid's crow's foot syntax where || means exactly one and |{ means one or more.

Before you look at the preview pane, what do you expect to see rendered on the right side of the screen?

The preview pane on the right should display two labeled boxes (PRODUCT and REVIEW) connected by a line with a crow's foot on the REVIEW end, indicating the one-to-many relationship.

Diagram not rendering?

Make sure erDiagram is on its own line with no leading spaces. The relationship definition must be indented on the following line.

If you see a syntax error, confirm there are no extra characters or blank lines before erDiagram.

Help me debug my Mermaid ER diagram

Configure your text editor for Mermaid preview

Your architecture files are markdown with embedded Mermaid code blocks. A text editor with Mermaid-aware markdown preview lets you write and verify diagrams without switching to the browser every time.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Cursor and press Enter to open it. If you use VS Code instead, type Visual Studio Code.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the marketplace-ratings-arch folder, then click Open.

You should see all five files listed in the Explorer sidebar on the left.

Do I need a Mermaid extension?

Cursor includes built-in Mermaid rendering in its markdown preview. If you are using VS Code, install the Markdown Preview Mermaid Support extension from the Extensions marketplace (click the square icon in the left sidebar and search for it).

To test it, open capacity.md and press Cmd+Shift+V (macOS) or Ctrl+Shift+V (Windows) to open the markdown preview pane.

Folder not showing in the Explorer sidebar?

Make sure you selected the marketplace-ratings-arch folder itself, not one of the files inside it. The folder name should appear at the top of the Explorer sidebar.

Help me open my project folder in my editor

Your workspace is ready. You have a clean project folder, a browser-based diagram renderer verified and working, and a text editor configured for Mermaid-aware markdown preview.

Next up, you'll use this workspace to calculate exactly how 10K DAU translates into read QPS, write TPS, and storage projections that drive every architecture decision.

Estimate Capacity — From 10K DAU to Concrete Numbers

Your project folder is ready. Now it's time to turn the vague requirement of "10K daily active users" into hard numbers that drive every architecture decision downstream.

Without concrete throughput numbers, your schema and caching decisions are guesswork. This step produces exact read QPS, write TPS, and storage projections. These numbers will justify whether you need Redis caching, whether a single PostgreSQL instance suffices, and how your schema should be indexed.

In this step, get ready to:
  • Calculate peak read QPS and write TPS from your DAU assumptions.
  • Project 12-month storage growth for reviews and aggregates.
  • Visualize the traffic funnel as a Mermaid flowchart with numbers at each stage.
Calculate throughput and storage estimates

Every capacity estimation starts with assumptions about user behavior. You need to know how many times each user triggers a rating read (every product page view) and how often users actually write reviews.

  • Open capacity.md in your text editor.
  • Add the file heading and traffic assumptions table by pasting the following:
# Capacity Estimation: Ratings Subsystem

## Traffic Assumptions

| Metric | Value | Rationale |
|--------|-------|-----------|
| Daily Active Users (DAU) | 10,000 | Given requirement |
| Avg sessions per user/day | 1.5 | Marketplace browsing pattern |
| Avg product pages per session | 8 | Each page loads rating aggregate |
| Peak multiplier | 3x average | Evening spike pattern |
| Review submission rate | 2% of DAU/day | Industry average for marketplaces |
| Avg review size | ~500 bytes | rating + title + body + metadata |

What do these assumptions mean?

Each marketplace user browses about 8 product pages per session, and each page triggers a rating aggregate read. Only 2% of users actually submit a review on any given day. The 3x peak multiplier accounts for evening traffic spikes when most users are active simultaneously.

  • Save capacity.md.
  • Check your markdown preview. You should see a clean table with 6 rows of metrics.

Now turn those assumptions into concrete throughput and storage numbers. These derived values tell you exactly how much load your system handles at peak.

  • Add the derived numbers table below the traffic assumptions by pasting this at the bottom of capacity.md:

## Derived Numbers

| Metric | Calculation | Result |
|--------|-------------|--------|
| Daily rating reads | 10,000 x 1.5 x 8 | 120,000 |
| Avg read QPS | 120,000 / 86,400 | ~1.4 QPS |
| Peak read QPS | 1.4 x 3 x (concentrated in 8hrs) | ~2,300 QPS |
| Daily new reviews | 10,000 x 0.02 | 200 |
| Peak write TPS | 200 / 28,800 x 3 | ~0.02 (bursty, up to ~7 concurrent) |
| Monthly reviews | 200 x 30 | 6,000 |
| 12-month storage (reviews) | 6,000 x 12 x 500B | ~36 MB |
| 12-month storage (stats) | ~10,000 entities x 200B | ~2 MB |

What do these numbers tell us?

The read/write ratio is roughly 330:1. Reads dominate at ~2,300 peak QPS while writes peak at only ~7 TPS. This extreme read-heavy pattern is what makes a cache-aside strategy effective.

Storage is tiny. 36MB of reviews after a full year means no partitioning, no sharding, and no special storage strategy. A single PostgreSQL instance handles this comfortably.

  • Save capacity.md and check the preview.
  • You should see both tables rendering with all 8 derived metrics visible.

Tables not rendering correctly?

Make sure there is a blank line before each ## heading and before each table. Markdown requires blank lines to separate block-level elements.

Help me fix my markdown tables in capacity.md

Create the traffic funnel flowchart

Numbers in a table are useful, but a flowchart makes the traffic funnel intuitive. It shows exactly how 10K DAU fans out into reads and writes at each stage.

  • Add the traffic funnel diagram to capacity.md by pasting this below the Derived Numbers table:

## Traffic Funnel Diagram

```mermaid
flowchart TD
    A[10K DAU] --> B[15K Sessions/day]
    B --> C[120K Product Page Views/day]
    C --> D[120K Rating Reads/day]
    D --> E[Peak: ~2,300 read QPS]
    A --> F[200 Review Submissions/day]
    F --> G[Peak: ~7 write TPS]
```

What does this flowchart show?

The flowchart TD declaration creates a top-down flow. Node A (10K DAU) branches into the read path (sessions to page views to read QPS) and the write path (review submissions to write TPS). Each node carries the concrete number from your derivation.

  • Save capacity.md.
  • Copy just the Mermaid code (from flowchart TD through the last line before the closing backticks) and paste it into the Mermaid Live Editor in your browser.
  • You should see a rendered diagram with 7 nodes and 6 connecting arrows, branching from the 10K DAU node into two paths.

Diagram not rendering in Mermaid Live Editor?

Make sure you only paste the Mermaid syntax itself, not the markdown fencing (the ```mermaid and closing ``` lines). The Live Editor expects raw Mermaid syntax.

Help me debug my Mermaid flowchart

Document your architectural conclusions

Your numbers now justify concrete architecture decisions. Document these conclusions so your AI agent (or future self) knows why you chose a single database instance and a Redis caching layer.

  • Add the conclusion section to the bottom of capacity.md:

## Conclusion

- Single PostgreSQL instance handles this comfortably
- Redis cache-aside justified: 2,300 peak QPS warrants caching to keep P95 under 5ms
- Storage is negligible; no partitioning needed for 12+ months

Why state these conclusions explicitly?

Each conclusion ties a number to a decision. The 2,300 peak read QPS justifies Redis. The ~36MB storage projection eliminates sharding. The ~7 write TPS means trigger-based aggregates won't cause contention. When someone asks "why Redis?" or "why not partition?", these three bullets are the answer.

  • Save capacity.md.

Before you check the final result, what do you expect to see when you scroll through your completed file? How many sections should be there, and which section contains a rendered diagram?

  • Scroll through the full capacity.md in your editor preview.
  • You should see four sections: Traffic Assumptions (table with 6 rows), Derived Numbers (table with 8 rows), Traffic Funnel Diagram (rendered flowchart with branching paths), and Conclusion (3 bullet points).

Verify your peak numbers

Confirm your derived table shows ~2,300 peak read QPS, ~7 peak write TPS, and ~36MB for 12-month review storage. These three numbers drive every decision in the next steps.

✔️ Awesome, I've got everything!

Great. Double check you have saved your file, then move on to the next step.

ⓧ I'd like to double check the full code

Your capacity.md should contain these four sections in order. Scroll up and confirm each one is present:

  • The # Capacity Estimation: Ratings Subsystem heading at the top.
  • The ## Traffic Assumptions table with 6 metric rows (DAU, sessions, pages, peak multiplier, submission rate, review size).
  • The ## Derived Numbers table with 8 rows covering daily reads, QPS, write TPS, monthly reviews, and 12-month storage.
  • The ## Traffic Funnel Diagram section containing a Mermaid flowchart TD with 7 nodes (A through G) and 6 arrows.
  • The ## Conclusion section with 3 bullet points covering PostgreSQL sufficiency, Redis justification, and storage assessment.

You now have hard numbers backing every architecture choice. Next up, you will design the PostgreSQL schema that handles those ~7 peak write TPS with trigger-based aggregates and a one-review-per-user constraint.

Design the Database Schema with Trigger Aggregates

Your capacity estimation proved that a single PostgreSQL instance handles the load comfortably. Now you need to decide how to structure the data inside that instance.

Your marketplace rates three different things: products, sellers, and auction completions. The naive approach of a separate reviews table per entity creates schema sprawl and duplicated logic. A polymorphic reviews table with a pre-computed stats table and a PostgreSQL trigger keeps it clean and fast.

In this step, get ready to:
  • Design a polymorphic reviews table with one-review-per-user enforcement and proper indexing.
  • Write a trigger function that recalculates aggregate stats automatically on every review change.
  • Create a Mermaid ER diagram showing all five entities with their relationships and key attributes.
Design the polymorphic reviews table

A polymorphic table uses an entity_type column to distinguish what kind of thing is being reviewed. Combined with entity_id, it lets one table serve product reviews, seller reviews, and auction reviews without duplication.

  • Open schema.sql in your text editor.
  • Add the reviews table and its indexes by pasting the following code:
-- Ratings and Comments Subsystem Schema
-- For: Software Marketplace (SaaS/BaaS products, bidding/auctions)

CREATE TABLE reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(20) NOT NULL CHECK (entity_type IN ('product', 'seller', 'auction_seller', 'auction_buyer')),
    entity_id UUID NOT NULL,
    user_id UUID NOT NULL,
    rating SMALLINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(200),
    body TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_one_review_per_user UNIQUE (entity_type, entity_id, user_id)
);

CREATE INDEX idx_reviews_entity_status ON reviews (entity_type, entity_id, status);
CREATE INDEX idx_reviews_entity_created ON reviews (entity_type, entity_id, created_at DESC);
CREATE INDEX idx_reviews_user ON reviews (user_id);

What does this schema do?

  • The CHECK constraint on entity_type restricts values to your four rateable entity kinds.
  • The UNIQUE constraint uq_one_review_per_user enforces one review per user per entity at the database level. No application logic needed.
  • The composite index idx_reviews_entity_status covers the most common query: fetching approved reviews for a specific product or seller.
  • The created_at DESC index supports sorting reviews newest-first without a full table scan.
  • Save schema.sql.
  • Review your file. You should see one CREATE TABLE statement and three CREATE INDEX statements.

Schema not looking right?

  • Make sure the CONSTRAINT line is inside the CREATE TABLE parentheses, before the closing );.
  • Check that each CHECK constraint uses parentheses around the condition.
  • help me check my reviews table structure

Now add the pre-computed stats table that caches aggregate values for each entity.

  • Below the indexes in schema.sql, add the review_stats table:
CREATE TABLE review_stats (
    entity_type VARCHAR(20) NOT NULL,
    entity_id UUID NOT NULL,
    avg_rating NUMERIC(3, 2) NOT NULL DEFAULT 0,
    review_count INTEGER NOT NULL DEFAULT 0,
    rating_distribution JSONB NOT NULL DEFAULT '[0,0,0,0,0]',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (entity_type, entity_id)
);

Why a separate stats table?

Computing AVG and COUNT across thousands of reviews on every page load is expensive. The review_stats table stores pre-computed results so reads are a single row lookup.

The rating_distribution JSONB array holds counts for each star level (1 through 5). This powers the histogram display on product pages without a GROUP BY query.

  • Save schema.sql.
  • You should now see two CREATE TABLE statements and three CREATE INDEX statements in the file.
Write the trigger function for aggregate updates

With ~7 peak write TPS from your capacity estimation, a PostgreSQL trigger is the right tool for keeping review_stats in sync. The trigger fires after every insert, update, or delete on reviews and recalculates the affected entity's aggregates.

  • Below the review_stats table in schema.sql, add the trigger function declaration and entity resolution logic:
CREATE OR REPLACE FUNCTION update_review_stats()
RETURNS TRIGGER AS $$
DECLARE
    v_entity_type VARCHAR(20);
    v_entity_id UUID;
    v_avg NUMERIC(3, 2);
    v_count INTEGER;
    v_dist JSONB;
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_entity_type := OLD.entity_type;
        v_entity_id := OLD.entity_id;
    ELSE
        v_entity_type := NEW.entity_type;
        v_entity_id := NEW.entity_id;
    END IF;

    SELECT
        COALESCE(AVG(rating), 0),
        COUNT(*)
    INTO v_avg, v_count
    FROM reviews
    WHERE entity_type = v_entity_type
      AND entity_id = v_entity_id
      AND status = 'approved';

What does this code do?

  • TG_OP is a special PostgreSQL variable that tells the trigger whether the operation was an INSERT, UPDATE, or DELETE.
  • For DELETE operations, the affected entity comes from OLD (the row being removed). For INSERT or UPDATE, it comes from NEW.
  • The SELECT only counts reviews where status = 'approved'. Pending or rejected reviews never affect the public aggregate.
  • Directly below (still inside the same function body), add the rating distribution calculation, the upsert into review_stats, and the trigger attachment:
    SELECT jsonb_build_array(
        COUNT(*) FILTER (WHERE rating = 1),
        COUNT(*) FILTER (WHERE rating = 2),
        COUNT(*) FILTER (WHERE rating = 3),
        COUNT(*) FILTER (WHERE rating = 4),
        COUNT(*) FILTER (WHERE rating = 5)
    )
    INTO v_dist
    FROM reviews
    WHERE entity_type = v_entity_type
      AND entity_id = v_entity_id
      AND status = 'approved';

    INSERT INTO review_stats (entity_type, entity_id, avg_rating, review_count, rating_distribution, updated_at)
    VALUES (v_entity_type, v_entity_id, v_avg, v_count, v_dist, NOW())
    ON CONFLICT (entity_type, entity_id)
    DO UPDATE SET
        avg_rating = EXCLUDED.avg_rating,
        review_count = EXCLUDED.review_count,
        rating_distribution = EXCLUDED.rating_distribution,
        updated_at = EXCLUDED.updated_at;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_review_stats
    AFTER INSERT OR UPDATE OR DELETE ON reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_review_stats();

What does this code do?

  • FILTER (WHERE rating = N) counts reviews per star level, building the histogram array [1-star, 2-star, 3-star, 4-star, 5-star].
  • ON CONFLICT ... DO UPDATE is an upsert. If the stats row already exists for this entity, it overwrites the values. If not, it creates one.
  • RETURN NULL is required for AFTER triggers. The trigger observes the change but does not modify the row itself.
  • The CREATE TRIGGER statement attaches the function to the reviews table. It fires once per row on any insert, update, or delete.
  • Save schema.sql.

Before you move on, trace through the trigger mentally. What happens when a new approved review for product #42 is inserted?

The trigger fires, reads NEW.entity_type ('product') and NEW.entity_id (42), recalculates the average and count from all approved reviews for that product, builds the star distribution array, and upserts the result into review_stats.

Trigger logic confusing?

  • If you are unsure about OLD vs NEW, remember: DELETE removes a row so there is no NEW record. INSERT creates a row so there is no OLD record. UPDATE has both.
  • The function handles all three cases because the trigger fires on INSERT OR UPDATE OR DELETE.
  • help me understand the trigger function flow
Create the ER diagram in Mermaid

An ER diagram gives your AI agent a visual map of which entities exist and how they relate. Mermaid's erDiagram syntax uses crow's foot notation for cardinality and supports PK and FK markers on attributes.

  • Switch to architecture.md in your text editor.
  • Add the section headings, open the mermaid fence, and define the relationships plus your two core entities by pasting the following:
# System Architecture: Ratings Subsystem

## Entity Relationship Diagram

```mermaid
erDiagram
    USERS ||--o{ REVIEWS : submits
    REVIEWS }o--|| REVIEW_STATS : aggregated-into
    PRODUCTS ||--o{ REVIEWS : rated-via
    SELLERS ||--o{ REVIEWS : rated-via
    REVIEWS {
        UUID id PK
        VARCHAR entity_type
        UUID entity_id FK
        UUID user_id FK
        SMALLINT rating
        VARCHAR title
        TEXT body
        VARCHAR status
        TIMESTAMPTZ created_at
    }
    REVIEW_STATS {
        VARCHAR entity_type PK
        UUID entity_id PK
        NUMERIC avg_rating
        INTEGER review_count
        JSONB rating_distribution
        TIMESTAMPTZ updated_at
    }

What do the cardinality markers mean?

  • ||--o{ means "exactly one to zero or more". A user can submit zero or many reviews, but each review belongs to exactly one user.
  • }o--|| means "zero or more to exactly one". Many reviews aggregate into one stats row per entity.
  • PK and FK after attribute names mark primary and foreign keys respectively.
  • Directly below (still inside the mermaid fence), add the three reference entities and the closing fence:
    USERS {
        UUID id PK
        VARCHAR name
    }
    PRODUCTS {
        UUID id PK
        VARCHAR name
    }
    SELLERS {
        UUID id PK
        VARCHAR name
    }
```
  • Save architecture.md.
  • Copy everything between the ```mermaid and closing ``` fences in your file.
  • Switch to the Mermaid Live Editor in your browser, clear the editor, and paste the copied diagram code.

What do you expect to see rendered? Predict before you look at the result.

You should see five entity boxes (USERS, REVIEWS, REVIEW_STATS, PRODUCTS, SELLERS) connected by relationship lines with crow's foot notation. Each box lists its attributes with PK/FK markers.

Diagram not rendering?

  • Check that each relationship line uses the correct markers. The outermost character represents the maximum cardinality and the innermost represents the minimum.
  • Ensure each entity's attribute block uses curly braces { } and that the format is type name on each line (type first, then name).
  • help me fix my Mermaid ER diagram rendering issue

✔️ Awesome, I've got everything!

Great. Double check you have saved both schema.sql and architecture.md.

ⓧ I'd like to double check the full code

Your schema.sql should contain these sections in order. Scroll up and confirm each is present:

  • Two comment lines at the top ("Ratings and Comments Subsystem Schema" and "For: Software Marketplace").
  • CREATE TABLE reviews with 10 columns, a CHECK on entity_type, a CHECK on rating, a CHECK on status, and the uq_one_review_per_user UNIQUE constraint.
  • Three CREATE INDEX statements: idx_reviews_entity_status, idx_reviews_entity_created, idx_reviews_user.
  • CREATE TABLE review_stats with a composite primary key on (entity_type, entity_id).
  • CREATE OR REPLACE FUNCTION update_review_stats() with the full trigger body (entity resolution, AVG/COUNT query, distribution query, upsert).
  • CREATE TRIGGER trg_update_review_stats attached to the reviews table firing AFTER INSERT OR UPDATE OR DELETE.

Your architecture.md should contain a level-1 heading "System Architecture: Ratings Subsystem", a level-2 heading "Entity Relationship Diagram", and a mermaid code fence with the full ER diagram showing all five entities with relationships and attributes.

Your schema is locked in. You have the data structures and the automation that keeps aggregates fresh on every write. Next up, you will design the Redis caching layer that sits in front of this database and serves those aggregates at sub-5ms latency.

Architect the Read/Write Paths with Redis Caching

Your schema and triggers handle the write side cleanly. But every time a user loads a product page, the API would hit PostgreSQL for the rating aggregate.

At 2,300 peak read QPS (from your capacity estimation), that direct database access pushes P95 latency well above acceptable levels. A Redis cache-aside layer keeps reads under 5ms while explicit invalidation on writes keeps the cache consistent with your database.

In this step, get ready to:
  • Design a Redis key schema with Hash, Sorted Set, and lock key patterns.
  • Document the read path with Lua-based stampede prevention.
  • Document the write path and create a Mermaid architecture diagram showing both paths.
Design the Redis key schema

Before documenting the read and write flows, you need to define what data lives in Redis and how it is keyed. Each key pattern maps to a specific role in the caching layer.

  • In your text editor, open architecture.md inside the marketplace-ratings-arch folder.
  • Below the ER diagram section, add the following Redis Key Schema section:
## Redis Key Schema

| Key Pattern | Type | Fields/Score | TTL |
|-------------|------|-------------|-----|
| `cache:rating:{entity_type}:{entity_id}` | Hash | avg_rating, review_count, rating_distribution | 300s (5 min) |
| `top_rated:{entity_type}` | Sorted Set | member=entity_id, score=avg_rating | None (updated on write) |
| `lock:rating:{entity_type}:{entity_id}` | String | unique_token | 3s (lock TTL) |

What do these three keys do?

  • cache:rating:{entity_type}:{entity_id} is a Redis Hash storing the pre-computed aggregate (average, count, distribution) for one entity. The 300-second TTL means stale data self-heals even if explicit invalidation fails.
  • top_rated:{entity_type} is a Sorted Set where each member is an entity ID scored by its average rating. This gives O(log N) top-N product queries without touching PostgreSQL.
  • lock:rating:{entity_type}:{entity_id} is a short-lived String lock that prevents cache stampedes. Only one caller loads from the database on a cache miss.
  • Save architecture.md.
  • Confirm the table renders in your markdown preview with three rows (Hash, Sorted Set, String).

Why 300 seconds for the cache TTL?

At 200 reviews/day across ~10,000 entities, most entities receive a new review less than once per day. A 5-minute TTL means at most 5 minutes of staleness during failure modes (like a Redis restart that skips explicit invalidation). Under normal operation, the write path DELetes the key immediately on approval.

Table not rendering correctly?

Make sure the pipe characters (|) align vertically and the separator row uses dashes between pipes. Some editors need a blank line between the heading and the table.

Help me fix my markdown table formatting in architecture.md

Document the read path with stampede prevention

The read path is where the caching layer earns its keep. When a user loads a product page, the API checks Redis first and only falls back to PostgreSQL on a cache miss.

  • Below the Redis Key Schema table in architecture.md, add the Read Path section:
## Read Path

1. Client requests product page with ratings
2. API calls Redis: `HGETALL cache:rating:product:{id}`
3. On HIT: return cached avg_rating, review_count, distribution
4. On MISS:
   a. Attempt Lua single-flight lock: `SET lock:rating:product:{id} {token} NX EX 3`
   b. If lock acquired: query `review_stats` table, `HSET` + `EXPIRE` result to cache, release lock
   c. If lock NOT acquired: wait 50ms, retry HGETALL (lock holder will have populated)
5. Return result to client

What does the single-flight lock solve?

When a popular product's cache key expires, dozens of concurrent requests all see a cache miss at once. Without the lock, all of them query PostgreSQL simultaneously. The SET NX EX pattern ensures only one winner loads from the database while others wait 50ms and read the freshly populated cache.

  • Now add the Lua script that implements the lock. Below the Read Path section, add the Stampede Prevention section:
## Stampede Prevention (Lua Script)

```
-- Single-flight lock: only one caller loads from DB
-- Key: lock:rating:{entity_type}:{entity_id}
-- Args: KEYS[1]=lock_key, ARGV[1]=token, ARGV[2]=ttl_seconds
if redis.call("SET", KEYS[1], ARGV[1], "NX", "EX", ARGV[2]) then
    return 1  -- lock acquired, caller should load from DB
else
    return 0  -- lock held by another, caller should wait and retry
end
```

What does this Lua script do?

  • redis.call("SET", KEYS[1], ARGV[1], "NX", "EX", ARGV[2]) atomically sets a lock key only if it does not exist (NX), with a 3-second expiry (EX) to prevent deadlocks.
  • Returning 1 tells the calling application it won the race and should query PostgreSQL. Returning 0 means another caller is already loading the data.
  • Save architecture.md.
  • Verify the Lua script appears inside a code fence in your markdown preview. You should see the redis.call line and the two return statements formatted as code.

Markdown code fence not rendering?

If the Lua script appears as plain text instead of inside a code block, make sure your opening and closing fences use three backticks on their own line. Nesting fences inside markdown can trip up some editors.

Help me fix my markdown code fences in architecture.md

Document the write path and create the architecture diagram

The write path is simpler than the read path because writes are infrequent (about 7 peak TPS). The key design decision is explicit cache invalidation after the trigger updates the stats.

  • In architecture.md, add the Write Path section between the Read Path section and the Stampede Prevention section:
## Write Path

1. User submits review via API
2. API validates (auth, rate limit, body min length)
3. INSERT into `reviews` with status='pending' (unique constraint prevents duplicates)
4. Return 201 to user
5. Moderator approves review (UPDATE status='approved')
6. PostgreSQL trigger fires: recalculates `review_stats` for entity
7. Application layer: `DEL cache:rating:{entity_type}:{entity_id}`
8. Application layer: `ZADD top_rated:{entity_type} {new_avg} {entity_id}`
9. Next read repopulates cache from fresh stats

Why DEL instead of updating the cache directly?

Using DEL on the cache key is the recommended cache-aside pattern for infrequent writes. It avoids the complexity of computing the new cached value in the application layer. The next read simply repopulates from the freshly updated review_stats row.

  • Save architecture.md and confirm the Write Path renders as a numbered list in your markdown preview.

Now you have both paths documented in prose. The final piece is a visual diagram that shows how requests flow across the system components.

  • Between the Write Path section and the Stampede Prevention section, add the Architecture Diagram heading and the first part of the Mermaid flowchart defining the system components:
## Architecture Diagram

```mermaid
flowchart LR
    subgraph Client
        U[User/Browser]
    end

    subgraph API Layer
        A[API Server]
    end

    subgraph Cache
        R[Redis]
    end

    subgraph Database
        P[(PostgreSQL)]
        T[Trigger: update_review_stats]
    end

    subgraph Moderation
        M[Moderation Queue]
    end

What are these subgraphs?

Each subgraph groups related nodes into a labeled box. The five boxes represent the major system boundaries: the browser client, your API layer, the Redis cache, the PostgreSQL database (with its trigger), and the moderation queue.

  • Directly below the last end statement (still inside the same mermaid code fence), add the edge connections that define how data flows between components:
    U -->|Read ratings| A
    A -->|HGETALL| R
    R -->|Miss| A
    A -->|SELECT review_stats| P
    A -->|HSET + EXPIRE| R

    U -->|Submit review| A
    A -->|INSERT review| P
    A -->|Queue for moderation| M
    M -->|UPDATE status=approved| P
    P --> T
    T -->|Recalc stats| P
    A -->|DEL cache key| R

What do these edges show?

  • The first five edges trace the read path: User to API, API to Redis (HGETALL), Redis miss back to API, API to PostgreSQL (SELECT), and API writing back to Redis (HSET + EXPIRE).
  • The remaining edges trace the write path: review submission through moderation, the trigger recalculating stats, and finally cache invalidation via DEL.
  • Each arrow label names the exact Redis or SQL command used at that hop, making the diagram directly implementable.
  • Close the mermaid code fence by adding three backticks (```) on their own line after the last edge.
  • Save architecture.md.
  • Copy the Mermaid flowchart content (everything between the ```mermaid and closing ``` fences) and paste it into Mermaid Live Editor.

Before you check the rendered result, what do you expect to see? Think about how many subgraph boxes should appear and how many labeled arrows connect them.

You should see five subgraph boxes (Client, API Layer, Cache, Database, Moderation) with labeled arrows showing both the read path (HGETALL, Miss, SELECT, HSET + EXPIRE) and the write path (Submit review, INSERT, Queue for moderation, UPDATE, Recalc stats, DEL cache key).

Diagram not rendering in Mermaid Live Editor?

Check that you pasted only the content between the mermaid fences, not the ``` markers themselves. The editor expects raw Mermaid syntax starting with flowchart LR.

Also verify the pipe characters in arrow labels (like -->|HGETALL|) are not accidentally escaped or missing.

Help me debug my Mermaid flowchart for the architecture diagram

✔️ Awesome, I've got everything!

Double check you have saved architecture.md with all sections in place.

ⓧ I'd like to double check the full code

Your architecture.md should now contain these sections in order. Scroll up and confirm each is present:

  • # System Architecture: Ratings Subsystem (top-level heading, unchanged from the previous step).
  • ## Entity Relationship Diagram with the Mermaid ER diagram from the previous step.
  • ## Redis Key Schema with the 3-row table (Hash, Sorted Set, String).
  • ## Read Path with the 5-step numbered list including the lock sub-steps.
  • ## Write Path with the 9-step numbered list ending at cache repopulation.
  • ## Architecture Diagram with the Mermaid flowchart showing 5 subgraphs and labeled arrows.
  • ## Stampede Prevention (Lua Script) with the 7-line Lua script using redis.call("SET", ...).

Your architecture document now has the full caching strategy: key schema, both data paths, a visual diagram, and the stampede-prevention script. Next up, you will trace the complete lifecycle of a review submission through a sequence diagram that makes timing dependencies and race conditions explicit.

Document the Review Lifecycle with Sequence Diagrams

Your architecture document now has a complete read/write path with Redis caching and PostgreSQL triggers. The system architecture flowchart shows the components, but it does not make the event ordering between them explicit.

The review lifecycle spans multiple services with timing dependencies. A submission passes through validation, database insertion, moderation, trigger execution, and cache invalidation. A sequence diagram makes this ordering visual so your AI agent implements the correct event sequence without race conditions.

In this step, get ready to:
  • Create a Mermaid sequence diagram showing the full review submission lifecycle through moderation and cache invalidation.
  • Add a second sequence diagram demonstrating cache stampede prevention with Lua-based single-flight locks.
  • Document edge cases and failure modes for the entire data flow.
Create the review submission lifecycle diagram

A review travels through six participants before it affects what users see on a product page. The sequence diagram captures this exact ordering as an implementation contract.

  • Open data-flow.md in your text editor.
  • Add the file heading, section heading, and the review submission sequence diagram by pasting this content:
# Data Flow: Review Lifecycle

## Review Submission Sequence

```mermaid
sequenceDiagram
    participant User
    participant API
    participant DB
    participant Trigger
    participant Moderator
    participant Redis

    User->>API: POST /reviews (rating, title, body)
    API->>DB: INSERT review (status=pending)
    Note over DB: Unique constraint check passes
    DB-->>API: 201 Created
    API-->>User: Review submitted (pending approval)

    Moderator->>DB: UPDATE status=approved WHERE id=review_id
    DB->>Trigger: AFTER UPDATE fires
    Trigger->>DB: Recalculate review_stats for entity
    DB-->>Trigger: Stats updated
    API->>Redis: DEL cache:rating:{type}:{id}
    API->>Redis: ZADD top_rated:{type} {avg} {id}
    Note over Redis: Next read repopulates cache
```

What does this diagram show?

The diagram splits into two phases. The first phase is the user-facing submission: the API inserts with status=pending, the unique constraint prevents duplicates, and the user gets a 201 immediately.

The second phase is the backend approval flow. Only after moderation approves does the trigger fire to recalculate review_stats. Then the cache is explicitly invalidated with DEL and the sorted set updated with ZADD. This ordering guarantees stale data never persists past the next read.

  • Save data-flow.md.
  • Copy just the Mermaid block (from sequenceDiagram through the closing backticks) and paste it into the Mermaid Live Editor.
  • You should see a rendered diagram with 6 participant boxes across the top: User, API, DB, Trigger, Moderator, and Redis.

Diagram not rendering?

Make sure you pasted only the content between the ```mermaid and closing ``` fences, not the fences themselves. The Mermaid Live Editor expects raw diagram syntax.

Check that every arrow uses the correct syntax: ->> for solid arrows and -->> for dashed return arrows.

help me debug why my Mermaid sequence diagram is not rendering

Add the cache stampede prevention diagram

The read path documentation in architecture.md describes the Lua single-flight lock in prose. A sequence diagram makes the concurrency interleaving visible, showing exactly when User2's request waits and when it retries.

  • In data-flow.md, add the following below the first diagram's closing backticks:
## Cache Stampede Prevention

```mermaid
sequenceDiagram
    participant User1
    participant User2
    participant API
    participant Redis
    participant DB

    User1->>API: GET /products/42/rating
    User2->>API: GET /products/42/rating
    API->>Redis: HGETALL cache:rating:product:42
    Redis-->>API: MISS (key expired)
    API->>Redis: SET lock:rating:product:42 token1 NX EX 3
    Redis-->>API: OK (lock acquired by User1 request)

    API->>Redis: SET lock:rating:product:42 token2 NX EX 3
    Redis-->>API: FAIL (lock held)
    Note over API: User2 request waits 50ms

    API->>DB: SELECT FROM review_stats WHERE entity_id=42
    DB-->>API: avg=4.3, count=128, dist=[2,5,12,45,64]
    API->>Redis: HSET cache:rating:product:42 + EXPIRE 300
    API-->>User1: Return rating data

    API->>Redis: HGETALL cache:rating:product:42
    Redis-->>API: HIT (freshly populated)
    API-->>User2: Return rating data
```

What does the stampede diagram prove?

Without the lock, both User1 and User2 would hit PostgreSQL simultaneously on a cache miss. Multiply that by hundreds of concurrent requests to a hot product page and you get a thundering herd.

The diagram shows User1 acquiring the lock with SET NX EX 3, User2 failing to acquire it and waiting 50ms, then User2 reading the freshly cached value. Only one request ever touches the database.

  • Save data-flow.md.
  • Copy the second Mermaid block into the Mermaid Live Editor.
  • You should see 5 participant boxes (User1, User2, API, Redis, DB) with the interleaved request flow showing User2's wait period.

Arrows pointing the wrong way?

In Mermaid sequence diagrams, ->> means a request (solid arrow pointing right), and -->> means a response (dashed arrow pointing left). If your arrows look reversed, check that response lines use the double-dash prefix.

help me fix arrow directions in my Mermaid sequence diagram

Document edge cases and failure modes

Sequence diagrams show the happy path. Your AI agent also needs to know what happens when things break. A failure modes table gives it explicit fallback behavior to implement.

  • In data-flow.md, add the following below the second diagram's closing backticks:
## Edge Cases and Failure Modes

| Scenario | Behavior | Recovery |
|----------|----------|----------|
| Redis down | API falls back to direct DB query on review_stats | Automatic on Redis recovery |
| Trigger fails | Review saved, stats stale until next write | Manual recompute or next successful trigger |
| Duplicate review attempt | Unique constraint returns 409 Conflict | Client shows "already reviewed" |
| Moderation backlog | Reviews pending, not in aggregates | User sees confirmation, public after approval |
| Cache TTL expires during low traffic | Single DB read, no stampede (low concurrency) | Normal cache-aside repopulation |

Why document failure modes explicitly?

Each row tells your AI agent exactly what to do when a dependency fails. "Redis down" means the API must have a fallback code path to query review_stats directly. "Trigger fails" means the review is still saved and the system self-heals on the next successful write.

Without this table, an implementer might add retry logic where graceful degradation is the correct response, or crash the request where a fallback exists.

  • Save data-flow.md.

Before you verify the final file, what do you expect to see when you paste each sequence diagram into the Mermaid Live Editor? How many participant boxes should appear in each?

  • Paste the first diagram into the Mermaid Live Editor. You should see 6 participants (User, API, DB, Trigger, Moderator, Redis) with arrows flowing from submission through moderation, trigger execution, and cache invalidation.
  • Paste the second diagram. You should see 5 participants (User1, User2, API, Redis, DB) with User2's request visibly pausing while User1's populates the cache.

Missing participants in the rendered diagram?

If a participant does not appear, check that its name in the participant declaration matches exactly how it appears in the arrow lines. Mermaid is case-sensitive.

If the entire diagram fails to render, look for a missing colon after an arrow or a line that starts with incorrect indentation.

help me debug participant rendering issues in my Mermaid sequence diagram

✔️ Awesome, I've got everything!

Great. Confirm you have saved data-flow.md with all three sections: the review submission sequence, the cache stampede prevention sequence, and the edge cases table.

ⓧ I'd like to double check the full code

Scroll up through your work and confirm these three chunks are present in order in data-flow.md:

  • The # Data Flow: Review Lifecycle heading followed by the ## Review Submission Sequence section with its 6-participant Mermaid diagram.
  • The ## Cache Stampede Prevention section with its 5-participant Mermaid diagram showing User1 and User2.
  • The ## Edge Cases and Failure Modes section with a 5-row markdown table covering Redis down, trigger failure, duplicate review, moderation backlog, and TTL expiry.

Secret mission

Post-Auction Rating Flow with Time-Bounded Verification

Your product and seller rating flows assume ratings can be submitted any time. Auction ratings are different. They require proof the user won, a time-bounded submission window, and two-way ratings where both parties rate each other. Can you extend your schema and data flow to handle bidirectional, time-bounded, escrow-verified ratings?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally with markdown files and the free Mermaid Live Editor. There are no ongoing costs or cloud resources to worry about.

Resources you used:

  • Local project folder marketplace-ratings-arch/ with files: README.md, capacity.md, schema.sql, architecture.md, data-flow.md.

Keep everything running

No action needed. Choose this if you plan to continue refining your architecture document or want to reference it while building the implementation.

  • Your marketplace-ratings-arch/ folder stays on your Desktop with all diagrams and schema files intact.
  • Mermaid Live Editor is browser-based and requires no account. Close or keep the tab as you like.

Pause - I'll come back to this later

Since this project is just local files with no running processes, there is nothing to pause. Your files remain on disk until you decide to delete them.

  • Close any open editor tabs for the project files.
  • Close the Mermaid Live Editor browser tab.
  • Your work is saved in the marketplace-ratings-arch/ folder whenever you want to return.

Delete - I don't want to use this again

Remove all project files from your machine. This is permanent.

  • Close any open editor tabs for the project.
  • Delete the project folder by running the command for your operating system below.

On macOS or Linux:

cd ~/Desktop
rm -rf marketplace-ratings-arch

On Windows:

cd ~\Desktop
rmdir /s /q marketplace-ratings-arch
  • Close the Mermaid Live Editor browser tab. No account or data is stored there.

Nice Work!

Nice Work!

You just architected a production-grade ratings and comments subsystem from the ground up. Your marketplace now has an implementation-ready specification that an AI agent can pick up and build directly.

You've learned how to:

  • Produce a capacity estimation translating 10K DAU into concrete read QPS, write TPS, and storage projections that justify every downstream architecture decision.
  • Design a polymorphic PostgreSQL schema with trigger-based aggregate computation and one-review-per-user enforcement across products, sellers, and auctions.
  • Architect a Redis cache-aside layer with Hash-based aggregate caching, sorted sets for rankings, Lua-based stampede prevention, and explicit invalidation on writes.
  • (Secret Mission) Design a post-auction bidirectional rating flow with time-bounded submission windows and escrow-linked verification.

Ready to quiz yourself?