Real-Time Database

Overview

Designs database schemas and query patterns optimized for real-time applications — chat, activity feeds, notifications, collaborative editing. Focuses on efficient message storage, cursor-based pagination, unread tracking, and sync protocols that minimize data transfer on reconnection.

Instructions

1. Schema Design for Messaging

Core tables for a chat system:

-- Channels (direct messages + groups)
CREATE TABLE channels (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type VARCHAR(10) NOT NULL CHECK (type IN ('direct', 'group')),
  name VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Channel membership with read tracking
CREATE TABLE channel_members (
  channel_id UUID REFERENCES channels(id),
  user_id UUID NOT NULL,
  role VARCHAR(20) DEFAULT 'member',
  last_read_message_id BIGINT,
  joined_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (channel_id, user_id)
);

-- Messages with sequential IDs for ordering
CREATE TABLE messages (
  id BIGSERIAL PRIMARY KEY,
  channel_id UUID REFERENCES channels(id),
  sender_id UUID NOT NULL,
  content TEXT NOT NULL,
  reply_to_id BIGINT REFERENCES messages(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ,
  deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_messages_channel_cursor
  ON messages(channel_id, id DESC) WHERE deleted_at IS NULL;

Use BIGSERIAL for message IDs — sequential, sortable, perfect for cursor pagination.

2. Cursor-Based Pagination

Never use OFFSET for message history — it's O(n) and results shift as new messages arrive.

-- Load 50 messages before a cursor (scrolling up)
SELECT id, sender_id, content, created_at
FROM messages
WHERE channel_id = $1 AND id < $2 AND deleted_at IS NULL
ORDER BY id DESC
LIMIT 50;

-- Load messages after a cursor (sync on reconnect)
SELECT id, sender_id, content, created_at
FROM messages
WHERE channel_id = $1 AND id > $2 AND deleted_at IS NULL
ORDER BY id ASC;

Return cursor metadata: { messages: [...], nextCursor: 12345, hasMore: true }

3. Unread Count Tracking

Use the last_read_message_id approach — one integer per user per channel:

-- Get unread count for a user across all channels
SELECT cm.channel_id, COUNT(m.id) AS unread_count
FROM channel_members cm
JOIN messages m ON m.channel_id = cm.channel_id
  AND m.id > COALESCE(cm.last_read_message_id, 0)
  AND m.deleted_at IS NULL
  AND m.sender_id != $1
WHERE cm.user_id = $1
GROUP BY cm.channel_id
HAVING COUNT(m.id) > 0;

-- Mark channel as read
UPDATE channel_members
SET last_read_message_id = $2
WHERE channel_id = $1 AND user_id = $3;

4. Reconnection Sync

When a client reconnects, minimize data transfer:

1. Client sends: { lastMessageIds: { "ch_1": 500, "ch_2": 300 } }
2. Server queries: new messages per channel since those IDs
3. If gap > 200 messages: send summary + latest 50 (client should full-reload)
4. Return: { channels: { "ch_1": { messages: [...], hasMore: false } } }

5. Soft Deletes and Edits

Messages should use soft deletes to maintain thread integrity:

• deleted_at timestamp — filter in queries, show "message deleted" in UI
• updated_at timestamp — mark edited messages
• Keep reply_to_id references valid even after parent is soft-deleted

Examples

Example 1: Chat Schema for SaaS App

Prompt: "Design the database for chat in my project management tool. Direct messages and project channels."

Output: Complete migration with channels, members, messages tables; cursor pagination queries; unread count query; and index strategy. Estimated performance: sub-10ms for message history with 10M+ messages.

Example 2: Activity Feed Schema

Prompt: "I need an activity feed — user actions like 'Alex commented on Task-42'. Need fan-out for team feeds."

Output: Events table with actor/verb/object pattern, fan-out-on-write to per-user feed tables, cursor pagination, and a cleanup job for feeds older than 90 days.

Guidelines

• Use sequential IDs (BIGSERIAL) for cursor pagination — UUIDs can't be sorted by creation order
• Never use OFFSET — cursor pagination is O(1), OFFSET is O(n)
• Track reads per-channel, not per-message — one integer vs. millions of rows
• Index for your access patterns — (channel_id, id DESC) covers 90% of chat queries
• Soft delete messages — hard deletes break reply chains and confuse users
• Partition large tables by channel_id or time range if exceeding 100M rows
• Cache hot channels in Redis — recent messages and member lists