DevSQL

Code Mode for AI coding history, shell history, macOS Unified Logs, Git repositories, source code, and durable worklogs. 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, your source tree, and its durable worklog 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:

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. Worklog writes pause for an explicit decision. Use direct devsql commands when working interactively in a terminal or writing a shell script.

Overview

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

Three standalone tools, one unified interface:

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:

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).

Workday memory

Agents write short work events so you can answer “what did I do today?” across every project:

devsql work start "Fix auth token refresh" --project velo --agent codex --body "Investigating 401s"
devsql work done <task-id> --body "Shipped fix; tests green"
devsql today
devsql day yesterday

Events live in ~/.devsql/worklog.sqlite (override with DEVSQL_HOME) and are also queryable as SQL tables work_tasks and work_events.

Code Mode server

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

Tables

AI History

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':

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

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

Use it through Code Mode:

await devsql.query({
  query: "SELECT timestamp, process, subsystem, category, level, message FROM macos_logs WHERE subsystem = 'com.apple.runningboard' ORDER BY timestamp DESC LIMIT 100",
  log_last: "10m",
  log_level: "info"
})

The direct CLI accepts --log-last, paired --log-start/--log-end, --log-predicate, --log-archive, --log-level, --log-max-rows, and --log-timeout. Defaults are the last 15 minutes, standard-level events, 50,000 rows, and 30 seconds. last accepts boot or a positive value ending in s, m, h, or d.

DevSQL safely pushes timestamp, process, PID, subsystem, category, level, event type, exact message, and simple %literal% message filters into /usr/bin/log. The child process writes through a bounded channel; DevSQL drains stderr and kills and reaps the process on timeout, SQL LIMIT, row cap, query error, or cursor teardown. A timeout or configured row cap returns the rows already read with a partial-scan warning. SQL LIMIT is treated as an intentional bound.

The stable columns are timestamp, event_type, subsystem, category, process, process_id, thread_id, sender, message, level, activity_id, trace_id, boot_uuid, raw_json, provenance, source, archive_path, source_order, timestamp_ms, format_string, process_image_path, sender_image_path, parent_activity_id, signpost fields, user_id, and mach_timestamp. The hidden columns start, end, predicate, archive, level_filter, max_rows, and timeout can override the matching option inside SQL. This provider is read-only, macOS-only, and does not cache or persist logs. Messages and raw_json are returned without redaction.

Git

Source Code

* Requires the tree-sitter-ast feature for full extraction. Without it, symbols falls back to regex-based extraction (Rust, TypeScript, JavaScript, Python, Go) and imports/ast_nodes are empty.

Examples

Join prompts with commits

SELECT
  date(c.authored_at) as day,
  COUNT(DISTINCT h.timestamp) as prompts,
  COUNT(DISTINCT c.id) as commits
FROM commits c
LEFT JOIN history h
  ON date(c.authored_at) = date(datetime(h.timestamp/1000, 'unixepoch'))
GROUP BY day
ORDER BY day DESC
LIMIT 14;

Find productive prompts

SELECT h.display as prompt, COUNT(c.id) as commits_after
FROM history h
JOIN commits c ON date(datetime(h.timestamp/1000, 'unixepoch')) = date(c.authored_at)
GROUP BY h.display
HAVING commits_after > 0
ORDER BY commits_after DESC
LIMIT 20;

Codebase overview by language

SELECT language, COUNT(*) as files, SUM(line_count) as total_lines
FROM source_files
GROUP BY language
ORDER BY total_lines DESC;

Hottest files (most commits + most symbols)

SELECT df.path,
  COUNT(DISTINCT df.commit_id) as commits,
  SUM(df.insertions) as lines_added,
  (SELECT COUNT(*) FROM symbols s WHERE s.file_path = df.path) as symbols
FROM diff_files df
GROUP BY df.path
ORDER BY commits DESC
LIMIT 10;

Search symbols

devsql search "parse"
devsql search "Error" --kind struct

Semantic diff between refs

devsql diff main~5 HEAD

Recall prior work

devsql recall "vision simulator mute"
devsql recall "auth token refresh" -r /path/to/repo

Query Codex conversation history

SELECT
  thread.cwd,
  message.role,
  message.text,
  message.timestamp
FROM codex_messages AS message
JOIN codex_threads AS thread
  ON thread.thread_id = message.thread_id
WHERE message.is_canonical = 1
  AND message.text LIKE '%auth callback%'
ORDER BY message.timestamp DESC;

Gather a context bundle

devsql gather "auth token refresh"
devsql gather "auth token refresh" -r /path/to/repo --budget 4000

gather returns six independently computed sections: prior work, repository state, code search, symbols, excerpts, and recent activity. If one section fails, the remaining sections are still returned and the failed section includes a note.

File context and impact

devsql context src/engine.rs
devsql impact src/lib.rs
devsql history src/engine.rs

Notes

• history.timestamp is in epoch milliseconds. Use datetime(timestamp/1000, 'unixepoch') to convert.
• A custom DATE() function normalizes epoch ms, epoch seconds, and ISO strings.
• Tables are loaded lazily; only those referenced in your query are populated.
• The symbols table extracts functions, structs, enums, traits, types, classes, interfaces, and more depending on language.

Codex journal indexing and privacy

• DevSQL reads canonical Codex journals from $CODEX_HOME, falling back to ~/.codex. It reads active and archived .jsonl and .jsonl.zst journals; it does not query Codex's catalog, history, goals, memories, logs, credentials, attachments, generated images, or shell snapshots.
• The versioned Codex index lives under the platform cache directory at devsql/codex-index/<CODEX_HOME-hash>.sqlite. Its first build parses the complete journal corpus and the derived cache can be as large as, or larger than, the source files. Later loads use file metadata to skip unchanged journals and read only appended records from growing plain journals.
• A journal's own payload.id is its thread ID. For subagent journals, payload.session_id and source.subagent.thread_spawn.parent_thread_id identify the parent and are stored as lineage instead of collapsing the child into the parent.
• The index is rebuildable and removes derived rows after source journals disappear. Schema changes automatically replace the disposable cache.
• DevSQL creates the cache directory with mode 0700 and cache files with mode 0600 on Unix. The cache still contains conversation text, tool arguments, commands, and tool output, so protect it like the source journals.
• Explicit SQL returns indexed content as stored. recall and gather redact common authorization values, API keys, access and refresh tokens, passwords, cookies, known token prefixes, and sensitive URL values before rendering automatic context.
• DevSQL records encrypted reasoning only as event metadata. It does not decrypt or index reasoning content.

License

MIT