SQLite Database Schema for Runs and Agent Invocations in no-mistakes

The no-mistakes CLI stores pipeline execution metadata in two core SQLite tables—runs and agent_invocations—defined in internal/db/schema.go, tracking everything from branch state and PR lifecycle to per-step LLM telemetry.

The no-mistakes CLI tool manages automated code review pipelines using a local SQLite database to persist execution state. Understanding the SQLite database schema is essential for debugging pipeline failures, analyzing agent performance, or extending the tool's functionality. The schema defines two primary tables that capture the complete lifecycle of a pipeline run and every LLM interaction within it.

Schema Overview

The database schema lives in internal/db/schema.go and is applied automatically at runtime by the initialization logic in internal/db/db.go. The design uses foreign key constraints with ON DELETE CASCADE to maintain referential integrity between repositories, runs, and agent invocations.

Table Relationships

• repos → runs: Each run belongs to a specific repository branch (foreign key repo_id)
• runs → agent_invocations: Each invocation record links to a specific run (foreign key run_id)

This hierarchy ensures that deleting a repository cascades to remove all associated runs and agent invocations automatically.

The runs Table Structure

The runs table represents a single pipeline execution for a repository branch. It tracks version control state, pull request status, CI readiness, and execution metadata.

Key Columns

• Identifiers: id (primary key), repo_id (foreign key to repos.id)
• Git State: branch, head_sha, base_sha, submitted_head_sha
• Execution Status: status (defaults to pending), error, created_at, updated_at
• PR Integration: pr_url, pr_state, pr_state_observed_at
• CI/CD Tracking: ci_ready_at, last_pushed_sha, last_pushed_at, push_ref
• Push Management: push_target_kind, push_target_fingerprint, push_generation, push_active
• Intent Handling: awaiting_agent_since, parked_ms

SQL Definition

According to the source code in internal/db/schema.go, the runs table is created with:

CREATE TABLE IF NOT EXISTS runs (
    id                   TEXT PRIMARY KEY,
    repo_id              TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    branch               TEXT NOT NULL,
    head_sha             TEXT NOT NULL,
    base_sha             TEXT NOT NULL,
    submitted_head_sha   TEXT,
    status               TEXT NOT NULL DEFAULT 'pending',
    pr_url               TEXT,
    pr_state             TEXT,
    pr_state_observed_at INTEGER,
    ci_ready_at          INTEGER,
    last_pushed_sha      TEXT,
    push_target_kind     TEXT,
    push_target_fingerprint TEXT,
    push_ref             TEXT,
    last_pushed_at       INTEGER,
    push_generation      INTEGER,
    push_active          INTEGER NOT NULL DEFAULT 0,
    error                TEXT,
    awaiting_agent_since INTEGER,
    parked_ms            INTEGER,
    created_at           INTEGER NOT NULL,
    updated_at           INTEGER NOT NULL
);

The push_active column uses an integer flag (0/1) to indicate whether a push operation is currently in progress, while parked_ms tracks time spent in a waiting state.

The agent_invocations Table Structure

The agent_invocations table records every LLM interaction that occurs during a run, organized by step and round. It provides granular telemetry for cost analysis and performance optimization.

Key Columns

• References: id (primary key), run_id (foreign key to runs.id)
• Execution Context: step_name, round, purpose, agent, model, model_provider
• Session Details: session_mode, session_key, fallback_reason
• Timing: started_at, completed_at, duration_ms, subprocess_wait_ms
• Outcome: exit_status, failure_category
• Token Metrics: input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, fresh_input_tokens, reasoning_tokens, plus delta variants for differential tracking
• Tool Usage: model_roundtrips, tool_calls, tool_wait_calls, tool_test_lint_calls, tool_edit_calls, tool_read_calls, tool_git_calls, tool_other_calls
• Workload: workload_files, workload_lines
• Results: finding_count

SQL Definition

The complete schema for agent invocations includes extensive telemetry fields:

CREATE TABLE IF NOT EXISTS agent_invocations (
    id                    TEXT PRIMARY KEY,
    run_id                TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    step_name             TEXT NOT NULL,
    round                 INTEGER NOT NULL,
    purpose               TEXT NOT NULL,
    agent                 TEXT NOT NULL,
    model                 TEXT,
    model_provider        TEXT,
    session_mode          TEXT NOT NULL,
    session_key           TEXT,
    fallback_reason       TEXT,
    started_at            INTEGER NOT NULL,
    completed_at          INTEGER NOT NULL,
    duration_ms           INTEGER NOT NULL,
    subprocess_wait_ms    INTEGER,
    exit_status           TEXT NOT NULL,
    failure_category      TEXT,
    input_tokens          INTEGER,
    output_tokens         INTEGER,
    cache_read_tokens     INTEGER,
    cache_creation_tokens INTEGER,
    fresh_input_tokens    INTEGER,
    reasoning_tokens      INTEGER,
    delta_input_tokens    INTEGER,
    delta_output_tokens   INTEGER,
    delta_cache_read_tokens INTEGER,
    model_roundtrips      INTEGER,
    tool_calls            INTEGER,
    tool_wait_calls       INTEGER,
    tool_test_lint_calls  INTEGER,
    tool_edit_calls       INTEGER,
    tool_read_calls       INTEGER,
    tool_git_calls        INTEGER,
    tool_other_calls      INTEGER,
    workload_files        INTEGER,
    workload_lines        INTEGER,
    finding_count         INTEGER
);

Working with the Schema in Go

The internal/db package provides Go structs that mirror these tables, enabling type-safe database operations.

Querying Runs

The Run struct defined in internal/db/run.go maps directly to the runs table columns. To query pending runs:

// Open the DB (using the helper in db.go) and query runs
db, _ := db.Open(path)
var runs []db.Run
err := db.Select(&runs, `SELECT id, branch, status, created_at FROM runs WHERE status = ?`, "pending")
if err != nil {
    log.Fatal(err)
}
for _, r := range runs {
    fmt.Printf("Run %s on %s – %s\n", r.ID, r.Branch, r.Status)
}

Recording Agent Invocations

The AgentInvocation struct in internal/db/agent_invocation.go facilitates inserting telemetry data. Here is a simplified example of recording a successful agent call:

// Insert a new agent invocation (simplified)
inv := db.AgentInvocation{
    ID:        uuid.NewString(),
    RunID:     runID,
    StepName:  "review",
    Round:     1,
    Purpose:   "generate_review",
    Agent:     "codex",
    Model:     "gpt-4",
    SessionMode: "persistent",
    StartedAt: time.Now().Unix(),
    CompletedAt: time.Now().Add(2*time.Second).Unix(),
    DurationMs: 2000,
    ExitStatus: "success",
}
_, err = db.Exec(`INSERT INTO agent_invocations (
    id, run_id, step_name, round, purpose, agent, model,
    session_mode, started_at, completed_at, duration_ms, exit_status
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    inv.ID, inv.RunID, inv.StepName, inv.Round, inv.Purpose,
    inv.Agent, inv.Model, inv.SessionMode, inv.StartedAt,
    inv.CompletedAt, inv.DurationMs, inv.ExitStatus)
if err != nil {
    log.Fatal(err)
}

Key Source Files

• internal/db/schema.go: Contains the raw SQL schema strings
• internal/db/run.go: Defines the Run struct and query helpers
• internal/db/agent_invocation.go: Defines the AgentInvocation struct and CRUD operations
• internal/db/db.go: Handles connection pooling and schema migrations

Summary

• The no-mistakes CLI uses SQLite to persist pipeline state across two primary tables defined in internal/db/schema.go
• The runs table tracks repository branch state, PR lifecycle, CI readiness, and execution status with cascading deletion from the parent repos table
• The agent_invocations table captures granular LLM telemetry including token usage, tool call counts, timing metrics, and workload statistics per step/round
• Both tables use TEXT primary keys and INTEGER timestamps (Unix epoch), with foreign key constraints ensuring data integrity
• The Go implementation in internal/db/ provides struct mappings and helper methods for type-safe database access

Frequently Asked Questions

What is the relationship between the runs and agent_invocations tables?

The agent_invocations table has a foreign key run_id that references runs.id with ON DELETE CASCADE. This means every agent invocation record belongs to a specific run, and deleting a run automatically removes all its associated invocation records. The runs table itself references repos.id with the same cascade behavior.

How does no-mistakes track token usage for cost analysis?

The agent_invocations table includes eleven token-related columns: input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, fresh_input_tokens, reasoning_tokens, and their delta variants (delta_input_tokens, etc.). These fields capture detailed usage metrics from the LLM provider APIs, enabling precise cost tracking and cache efficiency analysis.

What does the parked_ms column in the runs table indicate?

The parked_ms column tracks the cumulative time (in milliseconds) that a run has spent in a "parked" state, indicated by awaiting_agent_since. This occurs when the pipeline is waiting for agent availability or specific conditions before proceeding, helping identify bottlenecks in the execution flow.

Where is the database schema initialized in the codebase?

The schema is defined as SQL strings in internal/db/schema.go and applied during database initialization in internal/db/db.go. The Open() function in db.go handles connection establishment and executes the CREATE TABLE IF NOT EXISTS statements, ensuring the schema is current whenever the CLI starts.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}