multica/server/migrations/234_agent_task_queue_retired_session_id.up.sql

-- retired_session_id records an agent session this task proved unresumable and
-- that must never be handed to a later run, on ANY path.
--
-- The daemon already knew this and had no way to say it. A terminal report
-- carries one session_id, and an empty one meant both "no session to report"
-- and "forget the old session" — so after a fresh-session retry recovered a
-- turn, the poisoned id it retried away from stayed selectable: an older
-- completed row on the issue still pointed at it, and the chat_session pointer
-- still held it. The next run resumed the same dead transcript (GH #6066).
--
-- Nullable and write-once per row: set only when a run abandons a session it
-- was told to resume. NULL — the overwhelming majority of rows — means nothing
-- was retired. No index was added in this migration: every reader at the time
-- scoped by (agent_id, issue_id) or chat_session_id, and the retired set within
-- a scope was tiny. Migrations 349/350 later added terminal-resume and retired-
-- session indexes after production history made that assumption too costly.
ALTER TABLE agent_task_queue ADD COLUMN IF NOT EXISTS retired_session_id TEXT;