forked from mtusi2026/mtusi2026lab5
20 lines
1.0 KiB
SQL
20 lines
1.0 KiB
SQL
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);
|