devsql

DevSQL

Code Mode for observing AI coding history, shell history, macOS Unified Logs, Git repositories, and source code. Code Mode is the primary agent interface; the direct SQL CLI remains available for humans and scripts.

DevSQL loads data from Claude Code, Codex CLI, shell history, macOS Unified Logs, Git and your source tree into SQLite so you can join, filter, and aggregate across all of them with standard SQL. Most providers load into memory on demand. Unified Logs stream through a bounded virtual table rather than being materialized. Codex rollout journals use a rebuildable incremental cache so compressed conversation history does not need to be reparsed for every query.

Primary agent interface: Code Mode

Run devsql --mcp and connect it as an stdio MCP server. Agents see five stable lifecycle tools instead of a wide direct-tool surface:

Tool	Purpose
codemode_search	Discover typed devsql.* methods and saved snippets
codemode_execute	Start JavaScript that can compose multiple DevSQL calls
codemode_execution	Inspect a durable execution or fetch one artifact
codemode_decide	Approve or reject a pending write
codemode_cancel	Cancel a running or paused execution

For example, an agent can execute:

const recent = await devsql.query({
  query: "SELECT source, timestamp, command FROM shell_history ORDER BY timestamp DESC LIMIT 20"
});
recent

Read-only methods run without approval. Use direct devsql commands when working interactively in a terminal or writing a shell script.

Overview

~/.claude/       --+
~/.codex/        --+
shell histories  --+
macOS logs       --+--> SQLite --> SQL queries / JSON / CSV
.git/            --+
src/**/*         --+

Three standalone tools, one unified interface:

Tool	Data Source
ccql	Claude Code + Codex CLI data (~/.claude/, ~/.codex/)
vcsql	Git repositories (commits, branches, diffs)
devsql	All of the above, plus source code analysis

DevSQL auto-detects which tables your query references and only loads the data it needs.

Installation

Homebrew

brew install douglance/tap/devsql

Claude Code Plugin

/plugin marketplace add douglance/devsql
/plugin install devsql@devsql

The plugin auto-installs the binary on first session start.

Direct Download

Prebuilt binaries for macOS and Linux are available from GitHub Releases.

From Source

git clone https://github.com/douglance/devsql.git
cd devsql && cargo install --path crates/devsql

To enable tree-sitter-based AST analysis (richer symbol extraction and import parsing):

cargo install --path crates/devsql --features tree-sitter-ast

Usage

SQL Queries

devsql "<SQL>"                # Default table output
devsql --format json "<SQL>"  # JSON output
devsql --format csv "<SQL>"   # CSV output
devsql query "<SQL>"          # Named form for MCP and scripts

Commands

Structured commands that return JSON, designed for use by AI agents and scripts:

Command	Description
devsql search <query>	Find symbols by name across the codebase
devsql context <file>	File metadata and symbols for a given path
devsql history <file>	Git commit history for a specific file
devsql diff <base> <head>	Compare two Git refs with file and symbol-level stats
devsql impact <file>	Analyze exports and find potential dependents
devsql recall <terms>	Load prior work (Claude sessions, Codex threads, commits, prompts, shell commands, and agent-issued commands) ranked by term-match count then recency
devsql gather <terms>	Run prior_work, repo_state, code_search, symbols, excerpts, and activity concurrently and return one token-budgeted bundle

Common options: --repo / -r (default .), --data-dir / -d (default ~/.claude). gather also takes --budget (default 8000 tokens; lowest-ranked rows are dropped round-robin per section, never mid-row, until the bundle fits).

Code Mode server

Run devsql --mcp to start the primary agent interface described above. Direct commands remain the explicit CLI fallback.

Tables

AI History

Table	Source	Description
history	~/.claude/history.jsonl	Claude Code prompts (timestamp, display, project)
transcripts	~/.claude/projects/<slug>/**/*.jsonl (+ legacy ~/.claude/transcripts/*.jsonl)	Full conversations incl. subagents (type, content, tool_name, session_id, _project, _agent_id, timestamp, model, usage_* token columns)
sessions	Same files as transcripts	One row per session: title, cwd, git_branch, first/last_timestamp, message counts, subagent_count, total_*_tokens, pr_url, pr_number
todos	~/.claude/todos/*.json	Task items (content, status)
jhistory	~/.codex/history.jsonl	Codex CLI prompts (session_id, text, display, timestamp)
codex_history	-	Alias for jhistory
codex_threads	$CODEX_HOME/{sessions,archived_sessions}/**/rollout-*.jsonl[.zst]	One row per Codex thread: lineage, agent identity, originator, cwd, Git branch, state, compression, journal path, timestamps, first user text, and aggregate counts
codex_events	Same rollout journals	One row per newline-terminated journal record: thread ID, record index, timestamp, record type, payload type, role, call ID, and source path
codex_messages	Same rollout journals	Normalized user and assistant content, including canonical-message status and source provenance
codex_tool_executions	Same rollout journals	Tool calls paired with outputs by thread and call ID
codex_tool_calls	Same rollout journals	Backward-compatible tool-call view with source, session, agent, cwd, and timestamp provenance
codex_compactions	Same rollout journals	Compaction summaries and window metadata
codex_ingest_errors	DevSQL Codex index	Nonfatal journal read and JSON parsing errors
tool_calls	~/.claude/projects/<slug>/**/*.jsonl (+ legacy ~/.claude/transcripts/*.jsonl)	Claude assistant tool calls with source, session, subagent, cwd, and timestamp provenance
grok_bots	~/Library/Application Support/Grok Bot/sand-client-persistence/*.blob	One row per Grok Bot: name, roster presence, remote store path, entry counts, first/last entry, local replica and gateway backfill state
grok_entries	Replicas, in-box store.db, and optional gateway backfill	Every transcript entry: kind, role, direction, text, agents, request/batch IDs, raw_json, and provenance (local_replica, store_db, gateway, or both)
grok_messages	View over grok_entries	Entries that carry real text, joined to the bot name
grok_ingest_errors	DevSQL Grok index	Nonfatal blob read, parse, and gateway errors, deduplicated with an occurrence count

Grok Bots

Grok Bots are cloud agents on a Grok Bot Sand gateway. Unlike every other source, they have no working directory and no repo — their only path is a remote /home/box/sand-data/agents/<uuid>/store.db. They are therefore global, and are deliberately excluded from recall, gather, and command_events: reach them explicitly through devsql grok or by querying the grok_* tables.

devsql reads three sources into one index, deduplicated on (bot_id, entry_id); a row seen from more than one is marked provenance = 'both':

Source	Where	Notes
Desktop replicas	~/Library/Application Support/Grok Bot/sand-client-persistence/*.blob	Offline and zero-config, but truncated client-side windows
Per-bot stores	/home/box/sand-data/agents/<uuid>/store.db	The server of record. Only present inside a Grok Bot sandbox, where it is the complete, offline source
Gateway	grokctl bot transcript-tail	Full history from anywhere, but needs a reachable gateway

Because replicas are truncated, the index is append-only and never prunes: an entry the app evicts, or a bot deleted from the roster, stays queryable. Set DEVSQL_GROK_DIR to override the data directory (point it at the app-support root, not sand-client-persistence).

Running inside a Grok Bot sandbox, devsql finds /home/box/sand-data automatically and reads every bot's store.db directly -- no configuration and no network. That path also carries entry kinds the desktop replica never shows, such as tool-call. Bot names come from each store's kv table when no roster blob exists.

devsql grok status                      # coverage, sync state, ingest errors
devsql grok search "standup card"       # global search across all bots
devsql grok search release --bot Terri  # one bot
devsql grok sync --offline true         # refresh local replicas, no network
devsql grok sync --bot Terri --full     # backfill full history via grokctl

grok sync optionally backfills complete history from the gateway by shelling out to grokctl (--grokctl <path> or DEVSQL_GROKCTL_BIN). Only read-classified grokctl commands are used. If grokctl is missing or the gateway is unreachable, the command still succeeds: local sources are committed and the result reports gateway.reachable = false.

Shell History

Table	Source	Description
shell_history	Atuin, zsh, and bash	Normalized commands with source, source identity/order, timestamp, execution metadata, cwd, session, hostname, and history path
command_events	shell_history, Claude Bash calls, and Codex exec/shell calls	Commands with channel, actor, provenance quality/reason, source identity, session/agent metadata, execution metadata, and source path

DevSQL reads shell history without modifying it. It excludes Atuin rows marked deleted, keeps duplicate commands across sources, and treats missing or unreadable sources as empty. Commands are returned exactly as stored, including credential-like text.

command_events is a source-native union. It does not infer who typed an Atuin, zsh, or bash command and does not correlate or deduplicate rows across sources. Those rows use channel = 'shell', actor = 'unknown', provenance_quality = 'unattributed', and provenance_reason = 'unattributed_shell_history'. Claude Bash calls and Codex exec_command/shell calls use channel = 'agent_tool', actor = 'agent', and provenance_quality = 'exact'.

The stable event identity is (source, session_id, source_id). Shell rows use their native history source and command identity; Claude and Codex rows use the session/thread ID plus the tool call ID. For Codex commands, that pair maps back to codex_tool_executions(thread_id, call_id).

The stable command_events columns are source, channel, actor, provenance_quality, provenance_reason, source_id, source_order, session_id, parent_session_id, agent_id, agent_role, originator, tool_name, timestamp, duration_ms, exit_code, command, cwd, hostname, and source_path. Values are read without redaction.

exit_code is nullable. Shell history reports source-native exit status when available, Claude Bash rows leave it NULL, and Codex command rows report it only when DevSQL recognizes Codex's host wrapper line before the Final output: delimiter.

Source paths are discovered from Atuin's db_path setting and the standard Atuin, zsh, and bash locations. Set DEVSQL_ATUIN_DB, DEVSQL_ZSH_HISTORY, or DEVSQL_BASH_HISTORY to override a source path.

macOS Unified Logs

Table	Source	Description