CREATE TABLE IF NOT EXISTS schema_migrations ( version text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS users ( id uuid PRIMARY KEY, username text NOT NULL UNIQUE, display_name text NOT NULL, password_hash text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE IF NOT EXISTS sessions ( token_hash text PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL ); CREATE INDEX IF NOT EXISTS sessions_user_id_idx ON sessions(user_id); CREATE INDEX IF NOT EXISTS sessions_expires_at_idx ON sessions(expires_at); 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 INDEX IF NOT EXISTS chat_members_user_id_idx ON chat_members(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), text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS messages_chat_order_idx ON messages(chat_id, created_at, id); CREATE TABLE IF NOT EXISTS idempotency_keys ( user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE, method text NOT NULL, path text NOT NULL, key text NOT NULL, request_hash text NOT NULL, status_code integer, response_body jsonb, created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz NOT NULL, PRIMARY KEY (user_id, method, path, key) ); CREATE INDEX IF NOT EXISTS idempotency_keys_expires_at_idx ON idempotency_keys(expires_at);