-- lab4: вложения. Байты лежат в S3, здесь только метаданные и связь с чатом/сообщением. -- Миграция только добавляет таблицы и ослабляет одно ограничение, API lab3 продолжает работать. -- Нужны для составных внешних ключей ниже: вложение и сообщение связываются только в пределах одного чата. ALTER TABLE messages ADD CONSTRAINT messages_id_chat UNIQUE (id, chat_id); CREATE TABLE attachments ( id uuid PRIMARY KEY, chat_id uuid NOT NULL, owner_id uuid NOT NULL, filename bytea NOT NULL CHECK (octet_length(filename) BETWEEN 1 AND 1020), content_type text NOT NULL CHECK (content_type IN ('image/png', 'image/jpeg')), size_bytes integer NOT NULL CHECK (size_bytes BETWEEN 1 AND 10485760), sha256 text NOT NULL CHECK (sha256 ~ '^[a-f0-9]{64}$'), width integer NOT NULL CHECK (width >= 1), height integer NOT NULL CHECK (height >= 1), -- Ключ объекта назначает сервер (originals/<случайный hex>), имя файла в него не входит. object_key text NOT NULL UNIQUE CHECK (object_key ~ '^originals/[a-f0-9]{32}$'), -- uploading: строка-намерение записана ДО PUT в S3, клиенту не видна; -- ready: объект в S3 сохранён, можно читать и прикреплять. state text NOT NULL CHECK (state IN ('uploading', 'ready')), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), CHECK (width::bigint * height <= 40000000), UNIQUE (id, chat_id), -- владелец на момент загрузки был участником именно этого чата FOREIGN KEY (chat_id, owner_id) REFERENCES chat_members (chat_id, user_id) ); -- janitor ищет старые незавершённые загрузки CREATE INDEX attachments_uploading ON attachments (created_at) WHERE state = 'uploading'; CREATE TABLE message_attachments ( message_id uuid NOT NULL, chat_id uuid NOT NULL, attachment_id uuid NOT NULL, position smallint NOT NULL CHECK (position BETWEEN 0 AND 9), PRIMARY KEY (message_id, position), UNIQUE (message_id, attachment_id), -- один chat_id в обоих ключах: вложение другого чата прикрепить нельзя даже в обход API FOREIGN KEY (message_id, chat_id) REFERENCES messages (id, chat_id), FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments (id, chat_id) ); CREATE INDEX message_attachments_by_attachment ON message_attachments (attachment_id); -- С lab4 текст может быть пустым, если есть вложение (проверяет API). ALTER TABLE messages DROP CONSTRAINT messages_text_check; ALTER TABLE messages ADD CONSTRAINT messages_text_check CHECK (octet_length(text) <= 16000);