makima/migrations/20250119000000_add_task_retry_tracking.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Add retry tracking columns to tasks table for daemon failover

-- Number of times this task has been retried after daemon failure
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS retry_count INTEGER NOT NULL DEFAULT 0;

-- Maximum retry attempts before marking as permanently failed
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS max_retries INTEGER NOT NULL DEFAULT 3;

-- Array of daemon IDs that have failed this task (excluded from retry selection)
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS failed_daemon_ids UUID[] DEFAULT '{}';

-- When the task was last interrupted due to daemon disconnect
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS interrupted_at TIMESTAMPTZ;

-- Index for efficient pending task queries with retry consideration
CREATE INDEX IF NOT EXISTS idx_tasks_status_retry ON tasks(status, retry_count)
  WHERE status = 'pending';

COMMENT ON COLUMN tasks.retry_count IS 'Number of times this task has been retried after daemon failure';
COMMENT ON COLUMN tasks.max_retries IS 'Maximum retry attempts before marking as permanently failed';
COMMENT ON COLUMN tasks.failed_daemon_ids IS 'Array of daemon IDs that have failed this task (excluded from retry)';
COMMENT ON COLUMN tasks.interrupted_at IS 'When the task was last interrupted due to daemon disconnect';