-- Migration 0001: initial schema for lab2. -- Idempotent: safe to re-run on an existing database. CREATE TABLE IF NOT EXISTS users ( id uuid PRIMARY KEY, username text NOT NULL UNIQUE, password_hash text NOT NULL, display_name text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS chats ( id uuid PRIMARY KEY, title text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS chat_members ( chat_id uuid NOT NULL REFERENCES chats(id) ON DELETE CASCADE, user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, PRIMARY KEY (chat_id, user_id) ); CREATE TABLE IF NOT EXISTS messages ( id uuid PRIMARY KEY, chat_id uuid NOT NULL REFERENCES chats(id) ON DELETE CASCADE, sender_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); -- Keyset pagination index: history by (chat_id, created_at, id) avoids -- full table scan on every page read. CREATE INDEX IF NOT EXISTS idx_messages_chat_created_id ON messages (chat_id, created_at, id); CREATE INDEX IF NOT EXISTS idx_messages_chat_id ON messages (chat_id); CREATE TABLE IF NOT EXISTS sessions ( token_hash text PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at timestamptz NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id); CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at);