17 lines
912 B
SQL
17 lines
912 B
SQL
-- Event-driven unread tracking for chat sessions.
|
|
--
|
|
-- Semantics: unread_since is the timestamp of the first unread assistant
|
|
-- message. It stays NULL while the session has no unread. It's SET when
|
|
-- an assistant reply lands and the column was NULL. It's RESET to NULL
|
|
-- when the user marks the session as read. Existing rows start as NULL,
|
|
-- meaning "no unread to track" — historic chats are not mass-flagged.
|
|
ALTER TABLE chat_session ADD COLUMN unread_since TIMESTAMPTZ;
|
|
|
|
-- GetPendingChatTask runs on every session open / switch and filters by
|
|
-- chat_session_id + in-flight status + orders by created_at. A partial
|
|
-- index on the in-flight subset keeps that query cheap as the queue grows.
|
|
CREATE INDEX IF NOT EXISTS idx_agent_task_queue_chat_pending
|
|
ON agent_task_queue (chat_session_id, created_at DESC)
|
|
WHERE chat_session_id IS NOT NULL
|
|
AND status IN ('queued', 'dispatched', 'running');
|