SQLite Run/Step State Persistence Schema in the no-mistakes Daemon

The no-mistakes daemon persists execution state across five SQLite tables—repos, runs, step_results, step_rounds, and intent_cache—enabling crash recovery, agent gating, and incremental auto-fix tracking.

The no-mistakes daemon (kunchenguid/no-mistakes) relies on a SQLite backend to store the complete lifecycle of pipeline executions. The SQLite run/step state persistence schema defined in internal/db/schema.go captures everything from repository metadata to individual step rounds, ensuring that run state survives daemon restarts and supports complex retry logic.

Schema Overview

The database centers on four core entities that track execution from repository configuration down to individual fix attempts. All tables use TEXT primary keys (UUIDs) and foreign key constraints with ON DELETE CASCADE to maintain referential integrity.

repos Table

The repos table stores repository configuration. Each row represents a codebase the daemon manages.

• id: Primary key (TEXT)
• working_path: Local filesystem path (UNIQUE, NOT NULL)
• upstream_url: Source repository URL
• fork_url: Optional fork URL for PR operations
• default_branch: Typically "main" or "master"

runs Table

The runs table captures a single pipeline execution. This is the central entity for run state persistence.

• id: Primary key (TEXT)
• repo_id: Foreign key to repos.id
• branch, head_sha, base_sha: Git context
• status: Execution state (pending, running, success, failed)
• awaiting_agent_since: UNIX timestamp indicating when the run entered a gated state waiting for human or agent approval
• pr_url, error: Outcome metadata
• intent, intent_source, intent_session_id, intent_score: LLM-derived intent caching

step_results Table

The step_results table stores outcomes for individual pipeline steps (e.g., rebase, lint, test).

• id: Primary key (TEXT)
• run_id: Foreign key to runs.id
• step_name, step_order: Identification and sequencing
• status, exit_code, duration_ms: Execution metrics
• log_path: Filesystem location for step logs
• findings_json: JSON-encoded lint/security findings
• started_at, completed_at: Timing tracking

step_rounds Table

The step_rounds table implements step-level state persistence for retries. Each row represents one attempt (round) of a step, supporting auto-fix iterations.

• id: Primary key (TEXT)
• step_result_id: Foreign key to step_results.id
• round: Integer sequence number (1, 2, 3...)
• trigger_type: Mechanism that triggered the round (e.g., "auto-fix", "manual")
• findings_json, user_findings_json: Automated and human-reported findings
• selected_finding_ids, selection_source, fix_summary: Fix application metadata
• duration_ms, created_at: Performance tracking

intent_cache Table

The intent_cache table caches LLM intentions to avoid redundant model queries.

• cache_key: Primary key (TEXT)
• summary, agent_name, session_id: Intent metadata
• created_at: Cache timestamp

Complete Schema DDL

The exact DDL resides in internal/db/schema.go and is applied during database initialization:

const schemaSQL = `
CREATE TABLE IF NOT EXISTS repos (
    id             TEXT PRIMARY KEY,
    working_path   TEXT NOT NULL UNIQUE,
    upstream_url   TEXT NOT NULL,
    fork_url       TEXT,
    default_branch TEXT NOT NULL DEFAULT 'main',
    created_at     INTEGER NOT NULL
);

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,
    status               TEXT NOT NULL DEFAULT 'pending',
    pr_url               TEXT,
    error                TEXT,
    awaiting_agent_since INTEGER,
    created_at           INTEGER NOT NULL,
    updated_at           INTEGER NOT NULL,
    intent               TEXT,
    intent_source        TEXT,
    intent_session_id    TEXT,
    intent_score         REAL
);

CREATE TABLE IF NOT EXISTS step_results (
    id            TEXT PRIMARY KEY,
    run_id        TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    step_name     TEXT NOT NULL,
    step_order    INTEGER NOT NULL,
    status        TEXT NOT NULL DEFAULT 'pending',
    exit_code     INTEGER,
    duration_ms   INTEGER,
    log_path      TEXT,
    findings_json TEXT,
    error         TEXT,
    started_at    INTEGER,
    completed_at  INTEGER
);

CREATE TABLE IF NOT EXISTS step_rounds (
    id                   TEXT PRIMARY KEY,
    step_result_id       TEXT NOT NULL REFERENCES step_results(id) ON DELETE CASCADE,
    round                INTEGER NOT NULL,
    trigger_type         TEXT NOT NULL,
    findings_json        TEXT,
    user_findings_json   TEXT,
    selected_finding_ids TEXT,
    selection_source     TEXT,
    fix_summary          TEXT,
    duration_ms          INTEGER NOT NULL,
    created_at           INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS intent_cache (
    cache_key   TEXT PRIMARY KEY,
    summary     TEXT NOT NULL,
    agent_name  TEXT NOT NULL,
    session_id  TEXT NOT NULL,
    created_at  INTEGER NOT NULL
);
`

Migration Strategy

The schema employs additive migrations to support zero-downtime updates. When db.Open() detects an older database version, it executes ALTER TABLE statements (see lines 68-78 of internal/db/schema.go) to add columns like fork_url or awaiting_agent_since without data loss. This approach ensures backward compatibility while allowing the schema to evolve with new features like agent gating and intent scoring.

Practical Usage Examples

Opening the Database

Initialize the connection and apply the schema:

import (
    "github.com/kunchenguid/no-mistakes/internal/db"
)

// dbPath is typically $NM_HOME/no-mistakes.db
database, err := db.Open(dbPath)
if err != nil {
    log.Fatalf("open DB: %v", err)
}
defer database.Close()

Inserting a New Run

Create a run record with pending status:

run := db.Run{
    ID:        generateID(),
    RepoID:    repoID,
    Branch:    "feature/foo",
    HeadSHA:   head,
    BaseSHA:   base,
    Status:    "pending",
    CreatedAt: time.Now().Unix(),
    UpdatedAt: time.Now().Unix(),
}
if err := db.InsertRun(ctx, &run); err != nil {
    // handle error
}

Recording Step Results

Capture outcome after executing a pipeline step:

step := db.StepResult{
    ID:         generateID(),
    RunID:      run.ID,
    StepName:   "lint",
    StepOrder:  2,
    Status:     "success",
    ExitCode:   0,
    DurationMs: 123,
    LogPath:    "/path/to/log.txt",
    StartedAt:  time.Now().Unix(),
}
_ = db.InsertStepResult(ctx, &step)

Tracking Retry Rounds

Record an auto-fix attempt as a new round:

round := db.StepRound{
    ID:                generateID(),
    StepResultID:      step.ID,
    Round:             2,
    TriggerType:       "auto-fix",
    FindingsJSON:      jsonFindings,
    DurationMs:        45,
    CreatedAt:         time.Now().Unix(),
}
_ = db.InsertStepRound(ctx, &round)

Querying for Agent-Gated Runs

Identify runs awaiting human or agent intervention:

awaiting, err := db.QueryRunsByAwaitingAgent(
    ctx, 
    time.Now().Add(-5*time.Minute).Unix(),
)

Key Source Files

The persistence layer spans these implementation files:

File	Purpose
internal/db/schema.go	Schema DDL and migration statements
internal/db/db.go	Database initialization and connection management
internal/db/run.go	Run struct and CRUD operations for the runs table
internal/db/step.go	StepResult struct and helpers for step_results
internal/db/round.go	StepRound struct and operations for step_rounds
internal/daemon/manager.go	Orchestrates runs and persists state via the DB layer
internal/cli/axi_render.go	Renders UI badges based on awaiting_agent_since timestamps

Summary

• The no-mistakes daemon uses a five-table SQLite schema to achieve durable state persistence across daemon restarts.
• Cascading deletes ensure that deleting a repository removes all associated runs, steps, and rounds automatically.
• The awaiting_agent_since column in runs enables agent-gating workflows where execution pauses for external approval.
• Round tracking in step_rounds supports iterative auto-fixes by preserving history across multiple execution attempts.
• Additive migrations (lines 68-78 of schema.go) allow schema evolution without breaking existing databases.

Frequently Asked Questions

How does the schema handle step retries and auto-fixes?

The step_rounds table records each execution attempt as a distinct round. When a step fails and an auto-fix is applied, the daemon inserts a new row with an incremented round number, preserving the history of findings_json and fix_summary. This design supports iterative improvement while maintaining audit trails in the step state persistence layer.

What is the purpose of the awaiting_agent_since column?

This column in the runs table implements the agent-gating mechanism. When set to a UNIX timestamp, it signals that the pipeline has paused execution and is waiting for either a human operator or an automated agent to review findings and approve continuation. The UI queries this column to display "awaiting agent" badges.

How are findings stored in the database?

Findings are stored as JSON strings in the findings_json column of step_results and step_rounds. The user_findings_json column in step_rounds specifically captures human-provided feedback. The selected_finding_ids field tracks which specific findings were chosen for fixing during a round.

Where is the SQLite database file located?

By default, the daemon stores the database at $NM_HOME/no-mistakes.db, where NM_HOME is typically ~/.local/share/no-mistakes or a path specified via environment configuration. The db.Open() function in internal/db/db.go handles creation and schema application at this location.

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