egutierrez
43 lines
1.7 KiB
SQL
43 lines
1.7 KiB
SQL
-- Per-user notifications + persisted @mentions.
|
|
-- Created by card chat messages (card_messages).
|
|
--
|
|
-- Kinds:
|
|
-- mention — user mentioned via @username in body
|
|
-- assigned_chat — user is the card's assignee and someone else commented
|
|
-- reply — user previously commented on this card (or is requester)
|
|
-- A row is created per (recipient_user, message). The kind chosen is the
|
|
-- highest priority among those that apply: mention > assigned_chat > reply.
|
|
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
card_id TEXT NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
actor_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
read_at TEXT,
|
|
FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (message_id) REFERENCES card_messages(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_unread
|
|
ON notifications(user_id, read_at, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_user_created
|
|
ON notifications(user_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS card_mentions (
|
|
id TEXT PRIMARY KEY,
|
|
card_id TEXT NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
FOREIGN KEY (card_id) REFERENCES cards(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (message_id) REFERENCES card_messages(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_card_mentions_user ON card_mentions(user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_card_mentions_card ON card_mentions(card_id);
|
|
CREATE INDEX IF NOT EXISTS idx_card_mentions_message ON card_mentions(message_id);
|