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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
-- History tables for Resume and History System
-- Enables conversation rewind, snapshots, and unified event timeline
-- 1. Conversation Snapshots table
-- Stores conversation state at specific points for rewind capability
CREATE TABLE IF NOT EXISTS conversation_snapshots (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE SET NULL,
snapshot_type VARCHAR(50) NOT NULL, -- 'auto', 'manual', 'checkpoint'
message_count INTEGER NOT NULL,
conversation_state JSONB NOT NULL, -- Full conversation at this point
metadata JSONB, -- Additional context (token count, cost, etc.)
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_conversation_snapshots_task ON conversation_snapshots(task_id);
CREATE INDEX idx_conversation_snapshots_checkpoint ON conversation_snapshots(checkpoint_id);
CREATE INDEX idx_conversation_snapshots_created ON conversation_snapshots(created_at DESC);
-- 2. History Events table
-- Unified event stream for timeline views
CREATE TABLE IF NOT EXISTS history_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
contract_id UUID REFERENCES contracts(id) ON DELETE CASCADE,
task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
event_type VARCHAR(50) NOT NULL, -- 'task', 'chat', 'checkpoint', 'phase', 'file'
event_subtype VARCHAR(50), -- Specific event: 'created', 'completed', 'message', etc.
phase VARCHAR(50), -- Contract phase when event occurred
event_data JSONB NOT NULL, -- Event-specific data
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_history_events_contract ON history_events(contract_id, created_at DESC);
CREATE INDEX idx_history_events_task ON history_events(task_id, created_at DESC);
CREATE INDEX idx_history_events_owner ON history_events(owner_id, created_at DESC);
CREATE INDEX idx_history_events_type ON history_events(event_type, created_at DESC);
-- 3. Alter task_checkpoints - add conversation snapshot reference
ALTER TABLE task_checkpoints
ADD COLUMN IF NOT EXISTS conversation_snapshot_id UUID REFERENCES conversation_snapshots(id) ON DELETE SET NULL;
-- 4. Alter tasks - add forking fields
ALTER TABLE tasks
ADD COLUMN IF NOT EXISTS forked_from_task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
ADD COLUMN IF NOT EXISTS forked_at_checkpoint_id UUID REFERENCES task_checkpoints(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_tasks_forked_from ON tasks(forked_from_task_id) WHERE forked_from_task_id IS NOT NULL;
-- Comments for documentation
COMMENT ON TABLE conversation_snapshots IS 'Stores conversation state at specific points for rewind/resume capability';
COMMENT ON TABLE history_events IS 'Unified event stream for timeline views across contracts and tasks';
COMMENT ON COLUMN conversation_snapshots.snapshot_type IS 'Type: auto (periodic), manual (user-triggered), checkpoint (at git checkpoint)';
COMMENT ON COLUMN history_events.event_type IS 'Category: task, chat, checkpoint, phase, file';
|
