CREATE TABLE IF NOT EXISTS users ( id UUID PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, display_name BYTEA NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), CONSTRAINT users_display_bytes CHECK (octet_length(display_name) BETWEEN 1 AND 400) ); CREATE TABLE IF NOT EXISTS chats ( id UUID PRIMARY KEY, title BYTEA NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), CONSTRAINT chats_title_bytes CHECK (octet_length(title) BETWEEN 1 AND 400) ); CREATE TABLE IF NOT EXISTS chat_members ( chat_id UUID NOT NULL REFERENCES chats(id), user_id UUID NOT NULL REFERENCES users(id), position INTEGER NOT NULL, PRIMARY KEY (chat_id, user_id), UNIQUE (chat_id, position) ); CREATE INDEX IF NOT EXISTS idx_members_user ON chat_members (user_id, chat_id); CREATE TABLE IF NOT EXISTS messages ( id UUID PRIMARY KEY, chat_id UUID NOT NULL REFERENCES chats(id), sender_id UUID NOT NULL, text BYTEA NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), FOREIGN KEY (chat_id, sender_id) REFERENCES chat_members(chat_id, user_id), CONSTRAINT messages_text_bytes CHECK (octet_length(text) BETWEEN 1 AND 16000) ); CREATE INDEX IF NOT EXISTS idx_messages_chat_history ON messages (chat_id, created_at, id); CREATE INDEX IF NOT EXISTS idx_chats_history ON chats (created_at, id);