ALTER TABLE messages DROP CONSTRAINT IF EXISTS messages_text_bytes; ALTER TABLE messages ADD CONSTRAINT messages_text_bytes CHECK (octet_length(text) BETWEEN 0 AND 16000); CREATE TABLE IF NOT EXISTS attachments ( id UUID PRIMARY KEY, chat_id UUID NOT NULL REFERENCES chats(id), owner_id UUID NOT NULL REFERENCES users(id), object_key TEXT NOT NULL UNIQUE, backup_key TEXT NOT NULL UNIQUE, filename TEXT NOT NULL, content_type VARCHAR(32) NOT NULL CHECK(content_type IN ('image/png','image/jpeg')), size_bytes BIGINT NOT NULL CHECK(size_bytes BETWEEN 1 AND 10485760), sha256 CHAR(64) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), FOREIGN KEY (chat_id,owner_id) REFERENCES chat_members(chat_id,user_id) ); CREATE INDEX IF NOT EXISTS idx_attachments_chat ON attachments(chat_id); CREATE TABLE IF NOT EXISTS message_attachments ( message_id UUID NOT NULL REFERENCES messages(id), attachment_id UUID NOT NULL REFERENCES attachments(id), position INTEGER NOT NULL CHECK(position BETWEEN 0 AND 9), PRIMARY KEY (message_id, attachment_id), UNIQUE (message_id,position) ); CREATE INDEX IF NOT EXISTS idx_message_attachments_attachment ON message_attachments(attachment_id);