Queries Claude Code and Codex CLI history joined with Git commits using SQL to analyze productivity patterns, session insights, commit correlations, and tool usage.
From devsqlnpx claudepluginhub douglance/devsql --plugin devsqlThis skill uses the workspace's default tool permissions.
Executes pre-written implementation plans: critically reviews, follows bite-sized steps exactly, runs verifications, tracks progress with checkpoints, uses git worktrees, stops on blockers.
Guides idea refinement into designs: explores context, asks questions one-by-one, proposes approaches, presents sections for approval, writes/review specs before coding.
Dispatches parallel agents to independently tackle 2+ tasks like separate test failures or subsystems without shared state or dependencies.
Query Claude Code and Codex CLI history joined with Git commits to analyze productivity patterns.
Ensure devsql is installed:
brew install douglance/tap/devsql
| Table | Columns |
|---|---|
history | timestamp, display (prompt text), project, pastedContents |
jhistory | session_id, ts, text, display, timestamp |
codex_history | Alias of jhistory |
transcripts | Full conversation data including tool_use, tool_name |
todos | Todo items tracked in sessions |
| Table | Columns |
|---|---|
commits | id, message, summary, author_name, authored_at, short_id |
branches | name, is_head, commit_id |
diffs | Diff content per commit |
blame | Line-by-line attribution |
devsql "<query>"Note: history.timestamp is in milliseconds. Use datetime(timestamp/1000, 'unixepoch') to convert.
-- Recent prompts
SELECT display as prompt, 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;
-- Prompts this week
SELECT COUNT(*) as prompts
FROM history
WHERE datetime(timestamp/1000, 'unixepoch') > date('now', '-7 days');
-- Correlate 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;
-- Which prompts led to commits?
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
ORDER BY commits_after DESC
LIMIT 10;
-- Tool usage
SELECT tool_name, COUNT(*) as uses
FROM transcripts
WHERE type = 'tool_use'
GROUP BY tool_name
ORDER BY uses DESC;
devsql -f csv "<query>"devsql -f json "<query>"