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()