CREATE TABLE 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, filename TEXT NOT NULL, content_type TEXT 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, state TEXT NOT NULL CHECK(state IN ('pending','ready','failed')), error_code TEXT, created_at TIMESTAMPTZ NOT NULL, updated_at TIMESTAMPTZ NOT NULL ); CREATE INDEX attachments_state_age ON attachments(state, updated_at); CREATE TABLE message_attachments ( message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE, 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 message_attachments_attachment ON message_attachments(attachment_id); -- Preserve replay of lab3 text requests if an existing lab3 DB is upgraded. ALTER TABLE message_requests ALTER COLUMN body TYPE JSONB USING jsonb_build_object('text', body, 'attachment_ids', '[]'::jsonb); UPDATE message_requests SET response = response || '{"attachment_ids":[]}'::jsonb;