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

> Discover the SQLite schema for run/step state persistence in the no-mistakes daemon. Learn how five tables enable crash recovery, agent gating, and incremental auto-fix tracking.

- Repository: [Kun Chen/no-mistakes](https://github.com/kunchenguid/no-mistakes)
- Tags: internals
- Published: 2026-07-04

---

**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`](https://github.com/kunchenguid/no-mistakes/blob/main/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`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/schema.go) and is applied during database initialization:

```go
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`](https://github.com/kunchenguid/no-mistakes/blob/main/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:

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

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

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

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

```go
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`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/schema.go) | Schema DDL and migration statements |
| [`internal/db/db.go`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/db.go) | Database initialization and connection management |
| [`internal/db/run.go`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/run.go) | `Run` struct and CRUD operations for the `runs` table |
| [`internal/db/step.go`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/step.go) | `StepResult` struct and helpers for `step_results` |
| [`internal/db/round.go`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/round.go) | `StepRound` struct and operations for `step_rounds` |
| [`internal/daemon/manager.go`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/daemon/manager.go) | Orchestrates runs and persists state via the DB layer |
| [`internal/cli/axi_render.go`](https://github.com/kunchenguid/no-mistakes/blob/main/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`](https://github.com/kunchenguid/no-mistakes/blob/main/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`](https://github.com/kunchenguid/no-mistakes/blob/main/internal/db/db.go) handles creation and schema application at this location.