ctx0-sessions.md

ctx0 Sessions

This document defines the session/conversation storage system for ctx0, including multi-source tracking, compaction handling, and the tracker-curator workflow.

Overview

ctx0 stores all agent conversations in a unified format, regardless of source. This enables:

  • Session continuity: Resume conversations from any point
  • Cross-platform history: bot0, Claude Code, Cursor, ChatGPT in one place
  • Compaction tracking: Know exactly where context was summarized
  • Tool call logging: Full audit trail of agent actions

Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                         SESSION SOURCES                                      │
│                                                                              │
│   bot0 Desktop/CLI                                                           │
│   └── Direct writes to ctx0_sessions + ctx0_session_messages                 │
│       └── Real-time as conversation happens                                  │
│                                                                              │
│   Claude Code                                                                │
│   └── Tracker script watches ~/.claude/projects/                             │
│       └── Parses .jsonl files → inserts into session tables                  │
│       └── Marks agent='claude_code', stores source_path                      │
│                                                                              │
│   Cursor / Windsurf                                                          │
│   └── Tracker script watches relevant directories                            │
│       └── Parses conversations → inserts into sessions                       │
│       └── Marks agent='cursor' or 'windsurf'                                 │
│                                                                              │
│   ChatGPT / Gemini / Other                                                   │
│   └── Manual export → import tool                                            │
│       └── Or browser extension for real-time tracking                        │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Database Tables

ctx0_sessions

High-level session/conversation metadata.

sql
CREATE TABLE ctx0_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,

  -- Identity
  title TEXT,

  -- Source information
  agent TEXT NOT NULL DEFAULT 'bot0',      -- 'bot0', 'claude_code', 'cursor', 'gemini', 'chatgpt'
  agent_version TEXT,                      -- Version of the agent/tool
  source_path TEXT,                        -- Original file path for tracking back

  -- Context
  working_directory TEXT,
  project_name TEXT,

  -- Status
  status TEXT DEFAULT 'active',            -- 'active', 'paused', 'completed', 'archived'

  -- Stats (denormalized)
  message_count INT DEFAULT 0,
  tool_call_count INT DEFAULT 0,
  total_input_tokens INT DEFAULT 0,
  total_output_tokens INT DEFAULT 0,
  compaction_count INT DEFAULT 0,

  -- Files touched
  files_modified TEXT[] DEFAULT '{}',
  files_created TEXT[] DEFAULT '{}',
  files_deleted TEXT[] DEFAULT '{}',

  -- Summary
  summary TEXT,

  -- Metadata
  metadata JSONB DEFAULT '{}',

  -- Timestamps
  started_at TIMESTAMPTZ DEFAULT NOW(),
  last_activity_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ctx0_session_messages

Individual messages and turns within a session.

sql
CREATE TABLE ctx0_session_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES ctx0_sessions(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,

  -- Sequencing
  message_sequence INT NOT NULL,           -- Order in conversation (1, 2, 3...)
  stream_step INT DEFAULT 0,               -- For streaming (multiple steps per message)

  -- Content
  role TEXT NOT NULL,                      -- 'user', 'assistant', 'system', 'tool_call', 'tool_result'
  content TEXT,

  -- Tool call details
  tool_name TEXT,
  tool_params JSONB,
  tool_result JSONB,
  tool_error TEXT,

  -- Subagent/skill info
  subagent_name TEXT,
  skill_name TEXT,

  -- Compaction markers
  is_compaction_point BOOLEAN DEFAULT false,
  compaction_summary TEXT,                 -- The summary used for compaction
  context_tokens_before INT,

  -- Token usage
  input_tokens INT DEFAULT 0,
  output_tokens INT DEFAULT 0,
  model_used TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(session_id, message_sequence, stream_step)
);

Message Roles

RoleDescriptionExample
userUser input"Fix the login bug"
assistantAgent response"I'll look at the auth code..."
systemSystem messageContext, instructions
tool_callAgent invoked a toolRead file, run bash
tool_resultTool returned resultFile contents, command output

Compaction Tracking

When context is compacted (summarized to save tokens), we record this in the message history.

How Compaction Works

  1. Conversation reaches token limit
  2. System generates summary of conversation so far
  3. Summary replaces detailed history
  4. We mark this point in message history

Recording Compaction

sql
-- When compaction occurs, insert a marker message
INSERT INTO ctx0_session_messages (
  session_id,
  user_id,
  message_sequence,
  role,
  content,
  is_compaction_point,
  compaction_summary,
  context_tokens_before
) VALUES (
  'session-uuid',
  'user-uuid',
  42,                              -- Current sequence number
  'system',
  '[Context compacted]',
  true,
  'Summary: User was working on implementing OAuth login...',
  167189                           -- Tokens before compaction
);

Resuming from Compaction

To resume a conversation from a compaction point:

  1. Find the last compaction message
  2. Load the compaction_summary
  3. Load messages after the compaction point
  4. Inject summary + recent messages as context
sql
-- Find compaction points in a session
SELECT message_sequence, compaction_summary, context_tokens_before
FROM ctx0_session_messages
WHERE session_id = 'session-uuid'
  AND is_compaction_point = true
ORDER BY message_sequence DESC;

Tracker Script

The tracker is a script (not an agent) that monitors external AI tools and imports their conversations.

What Tracker Does

  1. Watch - Monitor directories for new/updated conversation files
  2. Parse - Extract messages, tool calls, metadata
  3. Insert - Write to ctx0_sessions and ctx0_session_messages
  4. Link - Store source_path for tracing back

Claude Code Tracking

Claude Code stores conversations in:

~/.claude/projects/{project-hash}/{session-id}.jsonl

Tracker reads:

  • Message content and role
  • Tool calls and results
  • Token counts
  • Model used
  • Compaction events

Tracker writes:

sql
-- Create session
INSERT INTO ctx0_sessions (
  user_id,
  title,
  agent,
  agent_version,
  source_path,
  working_directory,
  project_name
) VALUES (
  'user-uuid',
  'Fix login bug',
  'claude_code',
  '1.0.0',
  '~/.claude/projects/abc123/session-xyz.jsonl',
  '/Users/dev/myproject',
  'myproject'
);

-- Insert messages
INSERT INTO ctx0_session_messages (
  session_id,
  user_id,
  message_sequence,
  role,
  content,
  tool_name,
  tool_params,
  tool_result
) VALUES (...);

Tracker Configuration

json
{
  "trackers": {
    "claude_code": {
      "enabled": true,
      "watch_path": "~/.claude/projects/",
      "file_pattern": "*.jsonl",
      "poll_interval_ms": 5000
    },
    "cursor": {
      "enabled": false,
      "watch_path": "~/.cursor/conversations/",
      "file_pattern": "*.json"
    }
  }
}

Curator Agent Workflow

After tracker imports sessions, the curator agent processes them.

Curator Responsibilities

  1. Generate summaries - Create session summaries for completed conversations
  2. Extract insights - Find important decisions, learnings, contacts
  3. File to vault - Store extracted info in appropriate vault locations

Workflow

┌─────────────────────────────────────────────────────────────────────────────┐
│                         TRACKER → CURATOR WORKFLOW                           │
│                                                                              │
│   1. Tracker Script                                                          │
│      └── Watches ~/.claude/projects/                                         │
│      └── Detects new/updated conversation files                              │
│      └── Parses and inserts into ctx0_sessions + ctx0_session_messages       │
│                                                                              │
│   2. Curator Agent (triggered periodically or on session end)                │
│      └── Reads recently imported sessions                                    │
│      └── Generates summaries for completed sessions                          │
│      └── Extracts: decisions, contacts, learnings, action items              │
│      └── Files extracted content to vault (ctx0_remember)                    │
│                                                                              │
│   3. Result                                                                   │
│      └── Sessions searchable in ctx0                                         │
│      └── Key insights filed in vault                                         │
│      └── Cross-platform conversation history unified                         │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Curator Processing

sql
-- Find sessions needing processing
SELECT s.*
FROM ctx0_sessions s
WHERE s.status = 'completed'
  AND s.summary IS NULL
  AND s.agent IN ('claude_code', 'cursor', 'gemini')
ORDER BY s.ended_at DESC;

Curator then:

  1. Reads full message history
  2. Generates summary
  3. Updates session with summary
  4. Extracts insights to working_memory
  5. Files insights to vault

Session Lifecycle

┌─────────────────────────────────────────────────────────────────────────────┐
│                         SESSION LIFECYCLE                                    │
│                                                                              │
│   active                                                                     │
│   └── Conversation in progress                                               │
│   └── Messages being added                                                   │
│   └── May have compaction points                                             │
│                                                                              │
│       ↓ (user pauses or switches tasks)                                      │
│                                                                              │
│   paused                                                                     │
│   └── Temporarily inactive                                                   │
│   └── Can be resumed                                                         │
│                                                                              │
│       ↓ (user ends conversation or timeout)                                  │
│                                                                              │
│   completed                                                                  │
│   └── Conversation ended                                                     │
│   └── Ready for curator processing                                           │
│                                                                              │
│       ↓ (curator processes and summarizes)                                   │
│                                                                              │
│   archived                                                                   │
│   └── Fully processed                                                        │
│   └── Summary generated                                                      │
│   └── Insights extracted                                                     │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Querying Sessions

Find Recent Sessions

sql
SELECT id, title, agent, status, message_count, started_at
FROM ctx0_sessions
WHERE user_id = 'user-uuid'
ORDER BY last_activity_at DESC
LIMIT 20;

Find Sessions by Agent

sql
SELECT *
FROM ctx0_sessions
WHERE user_id = 'user-uuid'
  AND agent = 'claude_code'
  AND status = 'completed'
ORDER BY ended_at DESC;

Get Session Messages

sql
SELECT
  message_sequence,
  role,
  content,
  tool_name,
  is_compaction_point,
  compaction_summary
FROM ctx0_session_messages
WHERE session_id = 'session-uuid'
ORDER BY message_sequence, stream_step;

Find Tool Usage

sql
SELECT
  tool_name,
  COUNT(*) as use_count,
  AVG(output_tokens) as avg_output_tokens
FROM ctx0_session_messages
WHERE user_id = 'user-uuid'
  AND tool_name IS NOT NULL
GROUP BY tool_name
ORDER BY use_count DESC;

Find Compaction Points

sql
SELECT
  s.title,
  m.message_sequence,
  m.compaction_summary,
  m.context_tokens_before
FROM ctx0_sessions s
JOIN ctx0_session_messages m ON m.session_id = s.id
WHERE s.user_id = 'user-uuid'
  AND m.is_compaction_point = true
ORDER BY s.started_at DESC, m.message_sequence;

Resuming Conversations

To resume a conversation in bot0:

From Last State

sql
-- Get session metadata
SELECT * FROM ctx0_sessions WHERE id = 'session-uuid';

-- Get messages after last compaction (or all if no compaction)
WITH last_compact AS (
  SELECT COALESCE(MAX(message_sequence), 0) as seq
  FROM ctx0_session_messages
  WHERE session_id = 'session-uuid'
    AND is_compaction_point = true
)
SELECT m.*
FROM ctx0_session_messages m, last_compact lc
WHERE m.session_id = 'session-uuid'
  AND m.message_sequence >= lc.seq
ORDER BY m.message_sequence, m.stream_step;

Build Resume Context

  1. Load session metadata (working_directory, project_name)
  2. Load compaction summary (if exists)
  3. Load messages after compaction
  4. Inject as context for new conversation

Storage Bucket Structure

Session archives in ctx0-vault bucket:

ctx0-vault/
└── {userId}/
    └── sessions/
        └── {sessionId}/
            ├── messages.json      # Full message export
            └── summary.md         # Generated summary