ccql

Claude Code Query Language - SQL query engine for Claude Code and Codex CLI data.

Installation

Homebrew (macOS/Linux)

brew install douglance/tap/ccql

Shell script (macOS/Linux)

curl --proto '=https' --tlsv1.2 -LsSf https://github.com/douglance/ccql/releases/latest/download/ccql-installer.sh | sh

PowerShell (Windows)

powershell -ExecutionPolicy ByPass -c "irm https://github.com/douglance/ccql/releases/latest/download/ccql-installer.ps1 | iex"

npm

npm install -g ccql

Cargo

cargo install ccql

From source

git clone https://github.com/douglance/ccql
cd ccql
cargo install --path .

Quick Start

# SQL is the default command - just pass a query
ccql "SELECT display FROM history ORDER BY timestamp DESC LIMIT 5"
ccql "SELECT session_id, text FROM jhistory ORDER BY ts DESC LIMIT 5"
ccql "SELECT tool_name, COUNT(*) as n FROM transcripts WHERE type='tool_use' GROUP BY tool_name"
ccql "SELECT content FROM todos WHERE status='pending'"

# Get help
ccql -h              # Quick reference
ccql --help          # Full documentation
ccql tables          # Show table schemas
ccql examples        # Show query examples

Tables

Table	Source	Description
`history`	`history.jsonl`	User prompts
`jhistory`	`~/.codex/history.jsonl`	Codex CLI prompt history (virtual)
`codex_history`	Alias of `jhistory`	Codex CLI prompt history (virtual)
`transcripts`	`transcripts/*.jsonl`	Conversation logs (virtual)
`todos`	`todos/*.json`	Task items (virtual)

history

Column	Type	Description
`display`	TEXT	The prompt text
`timestamp`	INTEGER	Unix timestamp (ms)
`project`	TEXT	Project directory
`pastedContents`	OBJECT	Pasted content (JSON)

jhistory

Column	Type	Description
`session_id`	TEXT	Codex session ID
`ts`	INTEGER	Raw Unix timestamp (seconds)
`text`	TEXT	Raw prompt text
`display`	TEXT	Prompt text (normalized alias for `text`)
`timestamp`	INTEGER	Unix timestamp (milliseconds)

codex_history exposes the exact same columns as jhistory.

transcripts

Column	Type	Description
`_source_file`	TEXT	Source file (ses_xxx.jsonl)
`_session_id`	TEXT	Session ID
`type`	TEXT	`user` \| `tool_use` \| `tool_result`
`timestamp`	TEXT	ISO 8601 timestamp
`content`	TEXT	Message text (type='user')
`tool_name`	TEXT	Tool name (type='tool_*')
`tool_input`	OBJECT	Tool parameters
`tool_output`	OBJECT	Tool response (type='tool_result')

todos

Column	Type	Description
`_source_file`	TEXT	Source filename
`_workspace_id`	TEXT	Workspace ID
`_agent_id`	TEXT	Agent ID
`content`	TEXT	Todo description
`status`	TEXT	`pending` \| `in_progress` \| `completed`
`activeForm`	TEXT	Display text when active

Examples

Filter by Current Project

Use the project column to limit queries to the folder you're working in:

# Only prompts from current project
ccql "SELECT display FROM history WHERE project = '$(pwd)' ORDER BY timestamp DESC LIMIT 10"

# Transcripts from current project (via session join)
ccql "SELECT t.tool_name, COUNT(*) as n FROM transcripts t
      JOIN history h ON t._session_id = h.session_id
      WHERE h.project = '$(pwd)' AND t.type='tool_use'
      GROUP BY t.tool_name ORDER BY n DESC"

History Queries

# Recent prompts
ccql "SELECT display FROM history ORDER BY timestamp DESC LIMIT 10"

# Search prompts
ccql "SELECT display FROM history WHERE display LIKE '%error%'"

# Prompts by project
ccql "SELECT project, COUNT(*) as n FROM history GROUP BY project ORDER BY n DESC"

# Long prompts (likely pasted code)
ccql "SELECT LENGTH(display) as len, SUBSTR(display, 1, 60) as preview
      FROM history ORDER BY len DESC LIMIT 10"

# Recent Codex prompts
ccql "SELECT datetime(timestamp/1000, 'unixepoch') as time, display
      FROM jhistory ORDER BY timestamp DESC LIMIT 10"

Transcript Queries

# Tool usage stats
ccql "SELECT tool_name, COUNT(*) as n FROM transcripts
      WHERE type='tool_use' GROUP BY tool_name ORDER BY n DESC"

# Most active sessions
ccql "SELECT _session_id, COUNT(*) as n FROM transcripts
      GROUP BY _session_id ORDER BY n DESC LIMIT 10"

# Find sessions mentioning a topic
ccql "SELECT DISTINCT _session_id FROM transcripts
      WHERE content LIKE '%authentication%'"

Todo Queries

# Pending todos
ccql "SELECT content FROM todos WHERE status='pending'"

# Todo counts by status
ccql "SELECT status, COUNT(*) as n FROM todos GROUP BY status"

Output Formats

ccql -f table "SELECT ..."    # Pretty table (default)
ccql -f json "SELECT ..."     # JSON array
ccql -f jsonl "SELECT ..."    # JSON lines
ccql -f raw "SELECT ..."      # Raw output

Write Operations

Write operations require explicit flags for safety:

# Preview changes (dry run)
ccql --dry-run "DELETE FROM history WHERE timestamp < 1700000000000"

# Execute with backup
ccql --write "DELETE FROM history WHERE timestamp < 1700000000000"

Other Commands

ccql prompts                  # Extract prompts with filtering
ccql sessions                 # List sessions
ccql search "term"            # Full-text search
ccql todos --status pending   # List todos
ccql stats                    # Usage statistics
ccql duplicates               # Find repeated prompts
ccql query '.[]' history      # jq-style queries
ccql query '.[]' jhistory     # jq-style queries for Codex prompts
ccql query '.[]' codex_history

Configuration

# Set data directory
export CLAUDE_DATA_DIR=~/.claude
export CODEX_HOME=~/.codex

# Or via flag
ccql --data-dir ~/.claude "SELECT ..."

License

MIT

ccql 0.2.2