DevSQL Querying Skill
Query your Claude Code history joined with Git commits to analyze productivity patterns.
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 usage with Git history
Prerequisites
Ensure devsql is installed:
bash
brew install douglance/tap/devsql
Available Tables
Claude Code Tables
| Table | Columns |
|---|---|
history | timestamp, display (prompt text), project, pastedContents |
transcripts | Full conversation data including tool_use, tool_name |
todos | Todo items tracked in sessions |
Git Tables
| 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 |
Approach
- •Understand what the user wants to analyze
- •Compose a SQL query joining Claude and Git data as needed
- •Execute with:
devsql "<query>" - •Present results with insights
Note: history.timestamp is in milliseconds. Use datetime(timestamp/1000, 'unixepoch') to convert.
Example Queries
sql
-- Recent prompts
SELECT display as prompt, project
FROM history 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;
Output Formats
- •Default: formatted table
- •CSV:
devsql -f csv "<query>" - •JSON:
devsql -f json "<query>"