/devsql-querying

name: devsql-querying
description: Query and analyze developer-local history, macOS Unified Logs, Git, and source code through DevSQL Code Mode. Use when the user asks about prior conversations, shell commands, system logs, 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, macOS Unified Logs, Git commits, and source code.

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. Inspect query executions with `codemode_execution`. Devsql observes existing history; it does not create tasks or require progress reports.

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
• User wants to diagnose recent macOS application or subsystem behavior
• User asks what a Grok Bot said or decided (use `devsql grok search`, not `recall`/`gather`)

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. | | `devsql grok search "<terms>"` | Searching Grok Bot conversations. Supports `--bot` and `--limit`. | | `devsql grok status` | Checking Grok Bot index coverage and ingest errors. | | `devsql grok sync` | Refreshing local replicas, and optionally backfilling full history from the gateway. |

All commands accept `--repo` / `-r` and `--data-dir` / `-d` options.

Available Tables

The lists below are a guide. For the exact columns, run `devsql schema` (every table) or `devsql schema <table>`; a query naming a column that does not exist fails with the table's real columns and the closest match.

Claude/Codex Tables

| Table | Columns | |-------|---------| | `history` | rowid, display (prompt text), timestamp (epoch ms), project (working directory) | | `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 | | `codex_threads` | one row per Codex conversation: thread_id, cwd, started_at, last_event_at, first_user_text, … | | `codex_messages`, `codex_events`, `codex_tool_executions` | Codex messages, raw events, and commands with output |

Shell Tables

| Table | Columns | |-------|---------| | `shell_history` | Atuin, zsh, and bash history: source, timestamp, command, cwd, exit_code, … | | `command_events` | shell and agent commands in one stream (large; filter early) |

`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 |

Also loaded when a query reads them: `commit_parents`, `tags`, `refs`, `stashes`, `reflog`, `blame`, `config`, `remotes`, `submodules`, `status`, `worktrees`, `hooks`, `notes`. A commit's hash is `commits.id` (`short_id` for 7 characters); line counts live in `diffs` and `diff_files`, not `commits`.

macOS Unified Log

| Table | Columns | |-------|---------| | `macos_logs` | 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, image/signpost fields |

Pass `log_last`, `log_start`/`log_end`, `log_predicate`, `log_archive`, `log_level`, `log_max_rows`, and `log_timeout` to `devsql.query`. Prefer a narrow time window plus process or subsystem filters. Defaults are 15 minutes, standard level, 50,000 rows, and 30 seconds. The provider streams with bounded memory, pushes safe filters into macOS `log show`, and returns partial rows with a