Recreating WeChat’s Unread Red Dot with Spring Boot: One lastReadSeq Handles 10,000 Messages

Why the naïve per‑message read flag fails

Storing a boolean read column for every message works for tiny conversations, but in a group of 500 users with 10,000 messages the chat_message table would need 5 million rows just to track read status, quickly becoming a performance disaster.

Redefining the problem

Instead of asking "Has each message been read?", the design asks "Up to which message has the user read?". This shift reduces the state to a single number per user per conversation.

Adding a per‑conversation sequence

CREATE TABLE chat_message (
    id BIGINT PRIMARY KEY,
    conversation_id BIGINT NOT NULL,
    seq BIGINT NOT NULL,
    sender_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uk_conversation_seq (conversation_id, seq)
);

The seq column is a monotonically increasing number that represents the message’s position inside its conversation, independent of the global messageId.

Storing the read cursor

CREATE TABLE conversation_member (
    conversation_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    last_read_seq BIGINT NOT NULL DEFAULT 0,
    unread_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (conversation_id, user_id)
);

When a user opens a chat, the client sends the highest sequence it has displayed:

POST /api/conversations/80001/read
{
  "readSeq": 1038
}

The backend updates the cursor with a safe GREATEST operation to avoid regressions caused by concurrent devices:

UPDATE conversation_member
SET last_read_seq = GREATEST(last_read_seq, :readSeq),
    unread_count = 0
WHERE conversation_id = :conversationId
  AND user_id = :userId;

Fast unread‑count display

Counting unread messages with SELECT COUNT(*) … WHERE seq > last_read_seq is cheap for a single conversation but would require dozens of queries on the chat list page. Therefore the system maintains an unreadCount cache (e.g., in Redis) that is incremented when a new message arrives and cleared when the cursor advances.

chat:unread:10086 → {80001=18, 80002=3, 80003=99}

The chat list reads this hash once and shows the red dot instantly.

Real‑time read receipt

After updating last_read_seq, the server pushes a read‑receipt event via Spring’s STOMP/WebSocket support:

messagingTemplate.convertAndSendToUser(
    targetUserId.toString(),
    "/queue/read-receipts",
    event);

The client subscribes to /user/queue/read-receipts and receives a payload such as:

{
  "type": "READ_RECEIPT",
  "conversationId": 80001,
  "userId": 10087,
  "readSeq": 1038
}

This single message tells the sender that all messages with seq ≤ 1038 are now read, eliminating the need for per‑message status pushes.

Multi‑instance synchronization

In production, multiple Spring Boot instances sit behind a gateway. To propagate read‑progress events across instances, a lightweight Redis Pub/Sub channel (e.g., READ_PROGRESS_CHANGED) is used. The persistent state (the last_read_seq column) remains in the database, while Redis and WebSocket provide fast, volatile updates.

Key takeaways

lastReadSeq guarantees correctness; unreadCount guarantees speed.

The cursor‑based approach scales from a handful of messages to tens of thousands without exploding the database, works seamlessly across multiple devices, and can be generalized to other domains such as MQ consumption offsets or log replication.