Skip to content

query.md

Latest commit

 

History

History
362 lines (286 loc) · 6.45 KB

query.md

File metadata and controls

362 lines (286 loc) · 6.45 KB

SQL Query Reference

orch supports querying issues and runs using SQL with an in-memory SQLite database. This enables powerful analysis and custom reporting.

Quick Start

# List open issues
orch query "SELECT * FROM issues WHERE status = 'open'"

# Alias: orch q
orch q "SELECT id, title FROM issues"

# Output formats
orch q "SELECT * FROM runs" --format json
orch q "SELECT * FROM runs" --format tsv

Database Schema

Tables

issues

Column Type Description
id TEXT Issue ID (primary key)
title TEXT Issue title
status TEXT Issue status (open, closed, etc.)
path TEXT File path
created TEXT Created timestamp
updated TEXT Updated timestamp

runs

Column Type Description
issue_id TEXT Parent issue ID
run_id TEXT Run ID (timestamp)
hex_id TEXT Short hex ID (6 chars)
agent TEXT Agent type
status TEXT Run status
worktree TEXT Worktree path
branch TEXT Git branch
session_name TEXT Multiplexer session name
pr_url TEXT Pull request URL
created TEXT Created timestamp
updated TEXT Updated timestamp

issue_tags

Column Type Description
issue_id TEXT Issue ID
tag TEXT Tag value

events (with --with-events)

Column Type Description
run_id TEXT Parent run ID
timestamp TEXT Event timestamp
type TEXT Event type
name TEXT Event name
data TEXT Additional data (JSON)

Views

issues_v

Extended issues view with computed columns:

Column Description
(all from issues) Base issue columns
run_count Number of runs for this issue
tags Comma-separated tags

runs_v

Extended runs view with issue info:

Column Description
(all from runs) Base run columns
issue_title Parent issue title
issue_status Parent issue status
event_count Number of events

Common Queries

Issue Queries

-- All open issues
SELECT id, title, status 
FROM issues 
WHERE status = 'open';

-- Issues with specific tag
SELECT i.* 
FROM issues i
JOIN issue_tags t ON i.id = t.issue_id
WHERE t.tag = 'bug';

-- Issues without runs
SELECT i.* 
FROM issues i
LEFT JOIN runs r ON i.id = r.issue_id
WHERE r.run_id IS NULL;

-- Issues with active runs
SELECT DISTINCT i.* 
FROM issues i
JOIN runs r ON i.id = r.issue_id
WHERE r.status IN ('running', 'waiting', 'booting');

-- Issue summary with run counts
SELECT 
  id,
  title,
  status,
  run_count
FROM issues_v
ORDER BY run_count DESC;

Run Queries

-- Currently running
SELECT issue_id, run_id, agent, status
FROM runs
WHERE status = 'running';

-- Waiting runs needing attention
SELECT 
  issue_id,
  run_id,
  hex_id,
  updated
FROM runs
WHERE status IN ('waiting', 'rate_limited')
ORDER BY updated DESC;

-- Runs with PRs
SELECT 
  issue_id,
  run_id,
  pr_url,
  status
FROM runs
WHERE pr_url IS NOT NULL;

-- Run history for an issue
SELECT 
  run_id,
  agent,
  status,
  created,
  updated
FROM runs
WHERE issue_id = 'my-issue'
ORDER BY created DESC;

-- Status distribution
SELECT 
  status,
  COUNT(*) as count
FROM runs
GROUP BY status
ORDER BY count DESC;

Analytics Queries

-- Agent usage
SELECT 
  agent,
  COUNT(*) as runs,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as successful
FROM runs
GROUP BY agent;

-- Daily run counts
SELECT 
  DATE(created) as day,
  COUNT(*) as runs,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as completed
FROM runs
GROUP BY DATE(created)
ORDER BY day DESC
LIMIT 14;

-- Average runs per issue
SELECT 
  AVG(run_count) as avg_runs
FROM (
  SELECT issue_id, COUNT(*) as run_count
  FROM runs
  GROUP BY issue_id
);

-- Issues with most runs
SELECT 
  issue_id,
  COUNT(*) as run_count,
  SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as done_count
FROM runs
GROUP BY issue_id
ORDER BY run_count DESC
LIMIT 10;

Event Queries

Events require the --with-events flag (slower query):

orch q "SELECT * FROM events" --with-events
-- Events for a specific run
SELECT *
FROM events
WHERE run_id = '20260120-163000'
ORDER BY timestamp;

-- Status transitions
SELECT 
  run_id,
  timestamp,
  name as status
FROM events
WHERE type = 'status'
ORDER BY run_id, timestamp;

-- Time in each phase
SELECT 
  run_id,
  name as phase,
  timestamp
FROM events
WHERE type = 'phase'
ORDER BY run_id, timestamp;

-- PRs created
SELECT 
  e.run_id,
  r.issue_id,
  e.timestamp,
  e.data
FROM events e
JOIN runs r ON e.run_id = r.run_id
WHERE e.type = 'artifact' AND e.name = 'pr';

Output Formats

Table (default)

orch q "SELECT id, status FROM issues"
ID          STATUS
my-issue    open
other-task  closed

JSON

orch q "SELECT id, status FROM issues" --format json
[
  {"id": "my-issue", "status": "open"},
  {"id": "other-task", "status": "closed"}
]

TSV (for scripting)

orch q "SELECT id, status FROM issues" --format tsv
my-issue    open
other-task  closed

Integration Examples

Shell scripts

# Get running run IDs
RUNNING=$(orch q "SELECT hex_id FROM runs WHERE status='running'" --format tsv)

# Iterate over waiting runs
orch q "SELECT hex_id FROM runs WHERE status='waiting'" --format tsv | while read id; do
  echo "Run $id is waiting"
done

jq integration

# Get PR URLs
orch q "SELECT pr_url FROM runs WHERE pr_url IS NOT NULL" --format json | \
  jq -r '.[].pr_url'

# Count by status
orch q "SELECT status, COUNT(*) as n FROM runs GROUP BY status" --format json | \
  jq -r '.[] | "\(.status): \(.n)"'

Export to CSV

# Using tsv + sed
orch q "SELECT * FROM issues" --format tsv | sed 's/\t/,/g' > issues.csv

Tips

Performance

  • Use --with-events only when needed (loads all events into memory)
  • Limit results for large datasets: LIMIT 100
  • Use specific columns instead of SELECT *

Debugging queries

# See schema
orch schema

# Test query structure
orch q "SELECT * FROM issues LIMIT 1" --format json

Common patterns

-- COALESCE for null handling
SELECT COALESCE(pr_url, 'no PR') FROM runs;

-- Date filtering
SELECT * FROM runs
WHERE created > datetime('now', '-7 days');

-- Pattern matching
SELECT * FROM issues
WHERE title LIKE '%bug%';