1 files changed, 55 insertions, 0 deletions
diff --git a/makima/migrations/20250118000000_history_tables.sql b/makima/migrations/20250118000000_history_tables.sql
new file mode 100644
index 0000000..60e371c
--- /dev/null
+++ b/makima/migrations/20250118000000_history_tables.sql
@@ -0,0 +1,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';