CREATE TABLE message_idempotency ( user_id UUID NOT NULL, chat_id UUID NOT NULL, key TEXT NOT NULL, request_hash BYTEA NOT NULL, message_id UUID NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), expires_at TIMESTAMPTZ NOT NULL, CONSTRAINT message_idempotency_pk PRIMARY KEY (user_id, chat_id, key), CONSTRAINT message_idempotency_user_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT message_idempotency_chat_fk FOREIGN KEY (chat_id) REFERENCES chats (id) ON DELETE CASCADE, CONSTRAINT message_idempotency_message_fk FOREIGN KEY (message_id) REFERENCES messages (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT message_idempotency_key_format CHECK (key ~ '^[A-Za-z0-9._:-]{1,128}$'), CONSTRAINT message_idempotency_request_hash_length CHECK (octet_length(request_hash) = 32), CONSTRAINT message_idempotency_expiry CHECK (expires_at > created_at) ); CREATE INDEX message_idempotency_expires_at_idx ON message_idempotency (expires_at);