forked from mtusi2026/mtusi2026lab4
- S3: SeaweedFS 4.51 (MinIO-образы недоступны), приватный bucket, отдельные ограниченные ключи API, служебные порты закрыты; идемпотентный s3-init с проверкой приватности и прогревом записи - вложения: потоковая загрузка PNG/JPEG, лимиты 10 MiB / 40 Мпикс, тип по содержимому, проверка filename, скачивание только участникам - согласованность PG/S3: pending -> PUT -> ready, компенсация, sweeper с grace и dry-run; attachment_ids в сообщениях, FK по чату - back-pressure на S3 PUT после найденного OOM SeaweedFS - тесты student-tests, evidence, REPORT.md, DEMO.md
50 lines
2.7 KiB
SQL
50 lines
2.7 KiB
SQL
-- lab4: message text may be empty when the message carries attachments (checked in the API).
|
|
ALTER TABLE messages DROP CONSTRAINT messages_text_len;
|
|
ALTER TABLE messages ADD CONSTRAINT messages_text_len CHECK (char_length(text) <= 4000);
|
|
ALTER TABLE messages ADD CONSTRAINT messages_id_chat_key UNIQUE (id, chat_id);
|
|
|
|
-- Metadata only; bytes live in the private S3 bucket under object_key (assigned by the server).
|
|
-- 'pending' = intent recorded before S3 PUT, invisible to the API; the sweeper removes stale ones.
|
|
CREATE TABLE attachments (
|
|
id uuid PRIMARY KEY,
|
|
chat_id uuid NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
filename text NOT NULL,
|
|
content_type text NOT NULL,
|
|
size_bytes bigint NOT NULL,
|
|
sha256 text NOT NULL,
|
|
width integer NOT NULL,
|
|
height integer NOT NULL,
|
|
object_key text NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending',
|
|
created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
|
|
CONSTRAINT attachments_object_key_key UNIQUE (object_key),
|
|
CONSTRAINT attachments_id_chat_key UNIQUE (id, chat_id),
|
|
CONSTRAINT attachments_owner_member_fk FOREIGN KEY (chat_id, owner_id)
|
|
REFERENCES chat_members (chat_id, user_id),
|
|
CONSTRAINT attachments_status CHECK (status IN ('pending', 'ready')),
|
|
CONSTRAINT attachments_filename_len CHECK (char_length(filename) BETWEEN 1 AND 255),
|
|
CONSTRAINT attachments_content_type CHECK (content_type IN ('image/png', 'image/jpeg')),
|
|
CONSTRAINT attachments_size CHECK (size_bytes BETWEEN 1 AND 10485760),
|
|
CONSTRAINT attachments_sha256 CHECK (sha256 ~ '^[a-f0-9]{64}$'),
|
|
CONSTRAINT attachments_pixels CHECK (width > 0 AND height > 0 AND width::bigint * height <= 40000000)
|
|
);
|
|
|
|
CREATE INDEX attachments_pending_idx ON attachments (created_at) WHERE status = 'pending';
|
|
|
|
-- chat_id is repeated so that both composite FKs guarantee: an attachment is linked only within its own chat.
|
|
CREATE TABLE message_attachments (
|
|
message_id uuid NOT NULL,
|
|
attachment_id uuid NOT NULL,
|
|
chat_id uuid NOT NULL,
|
|
position smallint NOT NULL,
|
|
PRIMARY KEY (message_id, position),
|
|
CONSTRAINT message_attachments_unique UNIQUE (message_id, attachment_id),
|
|
CONSTRAINT message_attachments_message_fk FOREIGN KEY (message_id, chat_id) REFERENCES messages (id, chat_id),
|
|
CONSTRAINT message_attachments_attachment_fk FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments (id, chat_id),
|
|
CONSTRAINT message_attachments_position CHECK (position BETWEEN 0 AND 9)
|
|
);
|
|
|
|
CREATE INDEX message_attachments_attachment_idx ON message_attachments (attachment_id);
|