| name | devsql-querying |
| description | Query and analyze developer-local history, Git, and source code through DevSQL Code Mode. Use when the user asks about prior conversations, shell commands, productivity, commits, sessions, symbols, file context, or impact analysis. |
DevSQL Querying Skill
Use DevSQL Code Mode to query Claude Code and Codex CLI history, shell history, Git commits, source code, and durable worklogs.
Primary interface
Prefer the connected DevSQL Code Mode server. Use codemode_search to discover typed devsql.* methods, then codemode_execute to compose them in JavaScript. Poll durable work with codemode_execution; approve writes with codemode_decide only when the user has authorized them.
Use the direct commands below only when Code Mode is unavailable or when writing a shell script.
When to Use
- User asks "How many Claude sessions did I have this week?"
- User wants to "Find my longest debugging sessions"
- User asks "Which prompts led to the most commits?"
- User wants productivity analytics or session insights
- User asks about correlating Claude/Codex usage with Git history
- User wants to search for symbols, functions, or classes in the codebase
- User asks "What's in this file?" or needs file context
- User wants to understand what changed between commits
- User asks about imports, dependencies, or impact of a file
Prerequisites
Ensure devsql is installed:
brew install douglance/tap/devsql
Agent Tool Commands
For structured queries, prefer these subcommands (they return JSON):
| Command | Use When |
|---|
devsql search "<query>" | Finding symbols by name (functions, classes, structs). Supports --kind filter and --limit. |
devsql context <file> | Getting file metadata + all symbols defined in a file. |
devsql history <file> | Showing Git commit history for a specific file with diff stats. |
devsql diff <base> <head> | Comparing two Git refs with file-level and symbol-level change analysis. |
devsql impact <file> | Analyzing a file's exports and finding potential dependents via imports. |
All commands accept --repo / -r and --data-dir / -d options.
Available Tables
Claude/Codex Tables
| Table | Columns |
|---|
history | timestamp, display (prompt text), project, pastedContents |
jhistory | session_id, ts, text, display, timestamp |
codex_history | Alias of jhistory |
transcripts | type, content, tool_name, session_id, _source_file, _session_id, _project, _agent_id, timestamp, model, usage_input_tokens, usage_output_tokens, usage_cache_read_input_tokens, usage_cache_creation_input_tokens, usage_ephemeral_5m_input_tokens, usage_ephemeral_1h_input_tokens, usage_service_tier |
sessions | session_id, project, cwd, git_branch, version, title, first_timestamp, last_timestamp, user_message_count, assistant_message_count, subagent_count, total_input_tokens, total_output_tokens, total_cache_read_input_tokens, total_cache_creation_input_tokens, pr_url, pr_number |
todos | content, status |
transcripts covers ~/.claude/projects/<slug>/**/*.jsonl (top-level sessions
plus subagent transcripts) and the legacy ~/.claude/transcripts/*.jsonl.
sessions has one aggregated row per session file; _project / project is
the project slug directory (e.g. -Users-you-Developer-app), NULL for legacy
files. _agent_id is set only on subagent rows.
Git Tables
| Table | Columns |
|---|
commits | id, message, summary, author_name, authored_at, short_id |
branches | name, is_head, commit_id |
diffs | commit_id, files_changed, insertions, deletions |
diff_files | commit_id, path, status (A/D/M/R/C), insertions, deletions |
Code Tables (Source Analysis)
| Table | Columns |
|---|
source_files | path, name, extension, directory, size_bytes, line_count, modified_at, language |
source_lines | file_path, line_number, content, is_blank |
symbols | file_path, name, kind, line_start, line_end, signature, visibility, parameters, return_type, language |
imports* | file_path, line_number, module, name, alias, kind, is_default, is_wildcard |
ast_nodes* | (requires tree-sitter-ast feature) |
* Full extraction requires the tree-sitter-ast build feature. Without it, symbols uses regex-based extraction and imports/ast_nodes are empty.
Supported languages for symbol extraction: Rust, TypeScript, JavaScript, Python, Go.
Symbol kinds: fn (Rust), function (TypeScript/JavaScript/Python), struct, enum, trait, type, const, static, mod, macro, class, interface (varies by language).
Approach
- Understand what the user wants to analyze
- Choose the right tool:
- For structured queries about symbols/files/history → use a subcommand
- For cross-table analytics or custom joins → compose a SQL query
- Execute with:
devsql "<query>" or devsql <subcommand> <args>
- Present results with insights
Note: history.timestamp is in milliseconds. Use datetime(timestamp/1000, 'unixepoch') to convert.
Example Queries
SELECT display as prompt, project
FROM history ORDER BY timestamp DESC LIMIT 10;
SELECT COUNT(*) as prompts
FROM history
WHERE datetime(timestamp/1000, 'unixepoch') > date('now', '-7 days');
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;
SELECT h.display prompt, (c.id) commits_after
history h
commits c (datetime(h.timestamp, )) (c.authored_at)
h.display
commits_after
LIMIT ;
tool_name, () uses
transcripts
type
tool_name
uses ;
name, file_path, line_start, signature
symbols
kind visibility
file_path, line_start;
title, project, total_cache_read_input_tokens, last_timestamp
sessions
total_cache_read_input_tokens
LIMIT ;
() , model, (usage_output_tokens) output_tokens
transcripts
type usage_output_tokens
, model
;
, () files, (line_count) total_lines
source_files
total_lines ;
s.file_path, f.language, () symbol_count
symbols s
source_files f s.file_path f.path
s.file_path
symbol_count
LIMIT ;
df.path, ( df.commit_id) commits,
(df.insertions) total_adds,
( () symbols s s.file_path df.path) symbols
diff_files df
df.path
commits
LIMIT ;
Output Formats
- Default: formatted table
- CSV:
devsql -f csv "<query>"
- JSON:
devsql -f json "<query>"
