Files
mtusi2026lab5/db/migrations/00005_attachments.sql

55 lines
1.9 KiB
SQL

-- +goose Up
CREATE TABLE attachments
(
id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
chat_id UUID NOT NULL,
owner_id UUID NOT NULL,
filename text NOT NULL,
object_key text UNIQUE NOT NULL,
content_type VARCHAR(10) NOT NULL,
size_bytes bigint NOT NULL,
sha256 text NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT fk_attachments_chat_member FOREIGN KEY (chat_id, owner_id)
REFERENCES chat_members(chat_id, user_id),
CONSTRAINT attachments_sha256_format_check CHECK (sha256 COLLATE "C" ~ '^[0-9a-f]{64}$'),
CONSTRAINT attachments_content_type_check CHECK (content_type IN ('image/png', 'image/jpeg')),
CONSTRAINT attachments_filename_check CHECK (
char_length(filename) BETWEEN 1 AND 255
AND filename !~ '[/\\]'
),
CONSTRAINT attachments_size_bytes_check CHECK (size_bytes BETWEEN 1 AND 10485760),
CONSTRAINT attachments_id_chat_unique UNIQUE (id, chat_id),
CONSTRAINT attachments_object_key_length_check CHECK (char_length(object_key) > 0)
);
ALTER TABLE messages
ADD CONSTRAINT messages_id_chat_unique UNIQUE (id, chat_id);
CREATE TABLE messages_attachments
(
message_id UUID NOT NULL ,
attachment_id UUID NOT NULL ,
chat_id UUID NOT NULL,
position SMALLINT NOT NULL,
CONSTRAINT message_attachments_pk PRIMARY KEY (message_id, attachment_id),
CONSTRAINT message_attachments_position_unique UNIQUE (message_id, position),
CONSTRAINT message_attachments_position_check CHECK (position BETWEEN 0 AND 9),
CONSTRAINT message_attachments_message_chat_fk
FOREIGN KEY (message_id, chat_id) REFERENCES messages(id, chat_id),
CONSTRAINT message_attachments_attachment_chat_fk
FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments(id, chat_id)
);
-- +goose Down
DROP TABLE IF EXISTS messages_attachments;
DROP TABLE IF EXISTS attachments;
ALTER TABLE messages DROP CONSTRAINT IF EXISTS messages_id_chat_unique;