1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
-- Create supervisor_heartbeats table for tracking supervisor state over time.
-- This enables detection of dead/stale supervisors and provides audit trail.
CREATE TABLE IF NOT EXISTS supervisor_heartbeats (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
supervisor_task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
state VARCHAR(50) NOT NULL,
phase VARCHAR(50) NOT NULL,
current_activity TEXT,
progress INTEGER DEFAULT 0 CHECK (progress >= 0 AND progress <= 100),
pending_task_ids UUID[] DEFAULT ARRAY[]::UUID[],
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Index for finding heartbeats by supervisor task
CREATE INDEX idx_heartbeats_supervisor ON supervisor_heartbeats(supervisor_task_id);
-- Index for finding heartbeats by timestamp (for cleanup and monitoring)
CREATE INDEX idx_heartbeats_timestamp ON supervisor_heartbeats(timestamp);
-- Index for finding heartbeats by contract
CREATE INDEX idx_heartbeats_contract ON supervisor_heartbeats(contract_id);
-- Composite index for finding latest heartbeat per supervisor
CREATE INDEX idx_heartbeats_supervisor_timestamp ON supervisor_heartbeats(supervisor_task_id, timestamp DESC);
COMMENT ON TABLE supervisor_heartbeats IS 'Historical record of supervisor heartbeats for monitoring and dead supervisor detection';
COMMENT ON COLUMN supervisor_heartbeats.state IS 'Supervisor state: initializing, idle, working, waiting_for_user, waiting_for_tasks, blocked, completed, failed, interrupted';
COMMENT ON COLUMN supervisor_heartbeats.phase IS 'Current contract phase when heartbeat was sent';
COMMENT ON COLUMN supervisor_heartbeats.current_activity IS 'Human-readable description of what the supervisor is doing';
COMMENT ON COLUMN supervisor_heartbeats.progress IS 'Progress percentage (0-100)';
COMMENT ON COLUMN supervisor_heartbeats.pending_task_ids IS 'Array of task IDs the supervisor is waiting on';
-- Note: Cleanup of old heartbeats (24 hour TTL) should be done by a scheduled job
-- or application-level cleanup, not a CHECK constraint (which can't reference NOW())
|
