CREATE TABLE IF NOT EXISTS users ( id UUID PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, display_name VARCHAR(100) NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), CONSTRAINT username_format CHECK (username ~ '^[a-z0-9_]{3,32}$'), CONSTRAINT display_name_length CHECK (char_length(display_name) BETWEEN 1 AND 100), CONSTRAINT display_name_not_blank CHECK (length(btrim(display_name)) > 0) ); CREATE TABLE IF NOT EXISTS chats ( id UUID PRIMARY KEY, title VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), CONSTRAINT title_not_blank CHECK (length(btrim(title)) > 0) ); 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), position INTEGER NOT NULL, PRIMARY KEY (chat_id, user_id), UNIQUE (chat_id, position) ); 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 clock_timestamp(), CONSTRAINT message_length CHECK (char_length(text) BETWEEN 1 AND 4000), CONSTRAINT message_not_blank CHECK (length(btrim(text)) > 0) ); CREATE TABLE IF NOT EXISTS sessions ( token_hash CHAR(64) PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at TIMESTAMPTZ NOT NULL, revoked BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp() ); CREATE INDEX IF NOT EXISTS idx_chat_members_user ON chat_members(user_id, chat_id); CREATE INDEX IF NOT EXISTS idx_chats_order ON chats(created_at, id); CREATE INDEX IF NOT EXISTS idx_messages_history ON messages(chat_id, created_at, id); CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id); CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions(expires_at);