/query

description: Execute SQL queries against Claude Code, Codex CLI, macOS Unified Logs, Git, and source code data. Usage: /devsql:query <SQL>

DevSQL Query

Execute a direct SQL query against developer-local data, including macOS Unified Logs. This slash command is a convenience fallback; agents should prefer the DevSQL Code Mode server (`devsql --mcp`) when it is connected.

Prerequisites

Install devsql first:

brew install douglance/tap/devsql

Execution

Run the user's SQL query using devsql:

devsql "$ARGUMENTS"

For agent tool commands, use the subcommands directly:

devsql search "parse"            # Find symbols by name
devsql context src/engine.rs     # File metadata + symbols
devsql history src/engine.rs     # Git history for a file
devsql diff main~5 HEAD          # Compare refs (file + symbol level)
devsql impact src/lib.rs         # Exports and dependents

Available Tables

Claude + Codex Tables

• `history` — Claude prompt history (`~/.claude/history.jsonl`)
• `jhistory` — Codex prompt history (`~/.codex/history.jsonl`, or `$CODEX_HOME/history.jsonl`)
• `codex_history` — Alias of `jhistory`
• `transcripts` — Claude transcript logs from `~/.claude/projects/<slug>/**/*.jsonl` incl. subagents, plus legacy `~/.claude/transcripts/*.jsonl` (type, content, tool_name, session_id, `_project`, `_agent_id`, timestamp, model, `usage_input_tokens`, `usage_output_tokens`, `usage_cache_read_input_tokens`, `usage_cache_creation_input_tokens`, `usage_service_tier`, …)
• `sessions` — One row per Claude session (session_id, project, cwd, git_branch, version, title, first/last_timestamp, user/assistant_message_count, subagent_count, `total_*_tokens`, pr_url, pr_number)
• `todos` — Claude todo items (content, status)

Git Tables (from current repo)

• `commits` — Git commit history (id, message, summary, author_name, authored_at, short_id)
• `branches` — Branch information (name, is_head, commit_id)
• `diffs` — Commit-level diff stats (commit_id, files_changed, insertions, deletions)
• `diff_files` — Per-file diff stats (commit_id, path, status, insertions, deletions)
• Also: `commit_parents`, `tags`, `refs`, `stashes`, `reflog`, `blame`, `config`, `remotes`, `submodules`, `status`, `worktrees`, `hooks`, `notes`

Code Tables (source analysis of current repo)

• `source_files` — File inventory (path, name, extension, directory, size_bytes, line_count, modified_at, language)
• `source_lines` — Line-level content (file_path, line_number, content, is_blank)
• `symbols` — Functions, classes, structs, traits, etc. (file_path, name, kind, line_start, line_end, signature, visibility, parameters, return_type, language)
• `imports` — Import/use statements (file_path, line_number, module, name, alias, kind, is_default, is_wildcard). Requires the `tree-sitter-ast` feature.

Run `devsql schema <table>` for any table's exact columns.

macOS Unified Log

• `macos_logs` — Streamed events from the live log datastore or a `.logarchive`, with normalized process, subsystem, category, level, message, timestamp, provenance, and raw JSON fields. Bound scans with `--log-last`, paired `--log-start`/`--log-end`, `--log-predicate`, `--log-archive`, `--log-level`, `--log-max-rows`, and `--log-timeout`.

Example Queries

-- Recent errors; invoke with --log-last 10m --log-level info
SELECT timestamp, process, subsystem, category, message
FROM macos_logs
WHERE process = 'ExampleApp' AND message LIKE '%error%'
ORDER BY timestamp DESC
LIMIT 100;

-- Recent Claude prompts
SELECT display, project
FROM history
ORDER BY timestamp DESC
LIMIT 10;

-- Recent Codex prompts
SELECT datetime(timestamp/1000, 'unixepoch') AS time, display
FROM jhistory
ORDER BY timestamp DESC
LIMIT 10;

-- Commits correlated with Codex prompt activity
SELECT date(c.authored_at) AS day, COUNT(*) AS commits, COUNT(j.session_id) AS codex_prompts
FROM commits c
LEFT JOIN jhistory j ON date(c.authored_at) = date(datetime(j.timestamp/1000, 'unixepoch'))
GROUP BY day
ORDER BY day DESC;

-- Find all public Rust functions (Rust emits kind='fn'; JS/TS use 'function')
SELECT name, file_path, line_start, signature
FROM symbols
WHERE kind = 'fn' AND visibility = 'pub'
ORDER BY file_path, line_start;

-- Top sessions by cache-read tokens
SELECT title, project, total_cache_read_input_tokens
FROM sessions
ORDER BY total_cache_read_input_tokens DESC
LIMIT 10;

-- Files with the most symbols
SELECT s.file_path, f.language, COUNT(*) as symbol_count
FROM symbols s
JOIN source_files f ON s.file_path = f.path
GROUP BY s.file_path
ORDER BY symbol_count DESC
LIMIT 10;

-- Join prompts with code changes: which files were you working on?
SELECT df.path, COUNT(DISTINCT c.id) as commits, COUNT(DISTINCT h.timestamp) as prompts
FROM diff_files df
JOIN commits c ON df.commit_id = c.id
LEFT JOIN history h ON date(c.authored_at) = date(datetime(h.timestamp/1000, 'unixepoch'))
GROUP BY df.path
ORDER BY commits DESC
LIMIT 10;

Output

Display results in a formatted table. For large results, suggest the user pipe to csv:

devsql -f csv "SELECT ..." > output.csv