Files
mtusi2026lab4-implementation/internal/repository/postgres/migrations/000003_attachments.up.sql

25 lines
924 B
SQL

CREATE TABLE attachments (
id uuid PRIMARY KEY,
chat_id uuid NOT NULL REFERENCES chats (id) ON DELETE CASCADE,
owner_id uuid NOT NULL REFERENCES users (id),
filename text NOT NULL,
content_type text NOT NULL,
size_bytes bigint NOT NULL,
sha256 text NOT NULL,
object_key text NOT NULL UNIQUE,
status text NOT NULL,
error_code text,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL
);
CREATE INDEX attachments_chat_created_id_idx ON attachments (chat_id, created_at, id);
CREATE TABLE message_attachments (
message_id uuid NOT NULL REFERENCES messages (id) ON DELETE CASCADE,
attachment_id uuid NOT NULL REFERENCES attachments (id),
position int NOT NULL,
PRIMARY KEY (message_id, attachment_id)
);
CREATE INDEX message_attachments_attachment_id_idx ON message_attachments (attachment_id);