Low Level Design: Reactions Service – Tech Interview Dot Org

Schema

Reaction (
  entity_type   VARCHAR(50),
  entity_id     BIGINT,
  user_id       BIGINT,
  reaction_type ENUM('like','love','haha','wow','sad','angry'),
  created_at    TIMESTAMP,
  PRIMARY KEY (entity_type, entity_id, user_id)
)

ReactionCount (
  entity_type   VARCHAR(50),
  entity_id     BIGINT,
  reaction_type VARCHAR(20),
  count         INT DEFAULT 0,
  PRIMARY KEY (entity_type, entity_id, reaction_type)
)

The composite PK on Reaction enforces one reaction per user per entity at the DB level.

One Reaction Per User

Enforced by the primary key. A user can change their reaction type but cannot hold multiple reactions on the same entity simultaneously.

Changing a Reaction

Upsert on the Reaction table:

INSERT INTO reaction (entity_type, entity_id, user_id, reaction_type, created_at)
VALUES (?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE reaction_type=VALUES(reaction_type);

Decrement old reaction_type count, increment new reaction_type count in ReactionCount — done atomically in a transaction.

Read Path

GET /reactions/{entity_type}/{entity_id}

Response:
{
  "counts": {"like": 142, "love": 38, "haha": 7},
  "viewer_reaction": "like"   // null if not reacted
}

Counts read from ReactionCount (fast). Viewer's own reaction fetched by PK lookup on Reaction.

Write Path

POST /reactions
{"entity_type": "post", "entity_id": 42, "reaction_type": "love"}

// Remove reaction:
DELETE /reactions?entity_type=post&entity_id=42

At Scale

Shard ReactionCount by entity_id to distribute write load.
Redis counters: use HINCRBY reactions:{entity_type}:{entity_id} {reaction_type} 1 for hot entities. Async job syncs Redis counts to DB periodically.
Read cache: cache count response in Redis with short TTL (e.g., 5s) for viral content.

Batch Fetch

POST /reactions/batch
{"entity_type": "post", "entity_ids": [1, 2, 3, 42]}

Response:
{
  "1":  {"counts": {...}, "viewer_reaction": null},
  "42": {"counts": {...}, "viewer_reaction": "like"}
}

Single query with WHERE entity_id IN (...) on ReactionCount.

Real-Time Broadcast

On reaction write, publish event to Redis channel reactions:{entity_type}:{entity_id}. WebSocket server fan-outs updated counts to all connected viewers of that entity. Clients update counts in place without a full page reload.

PUBLISH reactions:post:42 '{"reaction_type":"love","delta":1,"total_counts":{...}}'

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you enforce one reaction per user per entity?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Use a composite primary key (entity_type, entity_id, user_id) on the Reaction table. This enforces uniqueness at the database level. To change a reaction, upsert with ON DUPLICATE KEY UPDATE reaction_type — no application-level check needed.”
}
},
{
“@type”: “Question”,
“name”: “How do you maintain accurate reaction counts efficiently?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Keep a denormalized ReactionCount table keyed by (entity_type, entity_id, reaction_type) with a count column. Increment or decrement counts atomically in a transaction alongside the Reaction upsert. For hot entities, use Redis HINCRBY and sync to the database asynchronously.”
}
},
{
“@type”: “Question”,
“name”: “How do you design the reactions API for reads and writes?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “GET /reactions/{entity_type}/{entity_id} returns counts by reaction type plus the viewer's own reaction (null if none). POST /reactions upserts the user's reaction. DELETE /reactions removes it. For feed pages, POST /reactions/batch accepts a list of entity IDs and returns a map of counts in a single query.”
}
},
{
“@type”: “Question”,
“name”: “How do you scale a reactions service to millions of entities?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Shard the ReactionCount table by entity_id. Use Redis counters (HINCRBY per reaction type per entity) to absorb write bursts on viral content, with an async job syncing counts to the database. Cache the read response with a short TTL. Broadcast count changes via WebSocket to connected viewers.”
}
}
]
}