28 lines
1.4 KiB
SQL
28 lines
1.4 KiB
SQL
-- Emoji reactions on chat messages.
|
|
--
|
|
-- One reaction per person per message: reacting again REPLACES rather than
|
|
-- accumulates, which is what the primary key enforces. That matches how the
|
|
-- UI reads — a row of distinct emoji with counts — and stops someone stacking
|
|
-- twenty reactions on one bubble.
|
|
--
|
|
-- The emoji is stored as text rather than a code point or shortname: what the
|
|
-- sender picked is what everyone should see, and a mapping table would need
|
|
-- updating every time the emoji set grows.
|
|
CREATE TABLE IF NOT EXISTS message_reactions (
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
-- Denormalized so deleting a conversation takes its reactions with it
|
|
-- without relying on the messages cascade alone.
|
|
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
emoji VARCHAR(32) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (message_id, user_id),
|
|
-- A stray empty string would render as an invisible reaction chip.
|
|
CONSTRAINT message_reactions_emoji_not_blank CHECK (btrim(emoji) <> '')
|
|
);
|
|
|
|
-- Hydrating a page of messages: one index range scan per batch.
|
|
CREATE INDEX IF NOT EXISTS message_reactions_message_idx
|
|
ON message_reactions (message_id);
|