explore-issues

Exploring GitHub Issues

Mirror a repo's issues into a local SQLite database, then answer questions about them with SQL and by reading issue bodies. Working locally avoids API rate limits and lets you query and re-read issues cheaply.

Step 1: Download the issues

Find the repo as owner/name. If the user didn't name one, use the current directory's repo: gh repo view --json nameWithOwner --jq .nameWithOwner.

Store the database outside the repo so it can't be committed, then download:

scripts/github-issues-to-sqlite.py <owner>/<name> -c -d /tmp/<owner>-<name>-issues.db

Options worth knowing:

• -c also fetches comment bodies. Include it; comments often hold the answer.
• -s all (or closed) if the user's question covers closed issues; the default is open only.
• --include-prs if the user wants pull requests too.
• Re-running the same command refreshes the database (it upserts). If the database already exists from earlier in the session, refresh instead of re-exploring stale data.
• --schema prints the schema.

Step 2: Get oriented

Tables: issues (number, title, state, state_reason, body, author, milestone, comments, is_pull, locked, html_url, created_at, updated_at, closed_at), labels and assignees (issue_number, name/login), comments (issue_number, author, body, created_at).

Start with a shape check before reading anything:

sqlite3 /tmp/<name>-issues.db "SELECT count(*) FROM issues;
  SELECT name, count(*) FROM labels GROUP BY name ORDER BY 2 DESC;"

If sqlite3 is missing, run queries through python3 -c with the sqlite3 module instead.

Step 3: Explore

Use SQL to narrow, then read the survivors in full. Useful patterns:

-- Unlabeled issues (often the triage backlog)
SELECT number, title FROM issues WHERE state='open' AND is_pull=0
  AND number NOT IN (SELECT issue_number FROM labels);

-- Keyword search across bodies and comments
SELECT DISTINCT i.number, i.title FROM issues i
  LEFT JOIN comments c ON c.issue_number = i.number
  WHERE i.body LIKE '%segfault%' OR c.body LIKE '%segfault%';

-- Oldest open, most-discussed, etc.
SELECT number, title, comments FROM issues WHERE state='open'
  ORDER BY comments DESC LIMIT 20;