24 lines
1007 B
MySQL
24 lines
1007 B
MySQL
|
-- Per-user delivery state for direct and broadcast bottom-bar messages.
|
||
|
|
CREATE TABLE IF NOT EXISTS bottom_bar_message_receipts (
|
||
|
|
message_id INTEGER NOT NULL REFERENCES bottom_bar_messages(id) ON DELETE CASCADE,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
|
read_at TIMESTAMP NULL,
|
||
|
|
acknowledged_at TIMESTAMP NULL,
|
||
|
|
PRIMARY KEY (message_id, user_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_bottom_bar_message_receipts_user_unread
|
||
|
|
ON bottom_bar_message_receipts (user_id, read_at, message_id);
|
||
|
|
|
||
|
|
-- Preserve the state of existing direct messages. Broadcast read_at cannot be
|
||
|
|
-- migrated safely because the old schema stored one shared value for everyone.
|
||
|
|
INSERT INTO bottom_bar_message_receipts (message_id, user_id, read_at, acknowledged_at)
|
||
|
|
SELECT id,
|
||
|
|
recipient_user_id,
|
||
|
|
read_at,
|
||
|
|
CASE WHEN requires_manual_ack THEN read_at ELSE NULL END
|
||
|
|
FROM bottom_bar_messages
|
||
|
|
WHERE recipient_user_id IS NOT NULL
|
||
|
|
ON CONFLICT (message_id, user_id) DO NOTHING;
|
||
|
|
|