> ## Documentation Index
> Fetch the complete documentation index at: https://piloop.autoresearch.inc/llms.txt
> Use this file to discover all available pages before exploring further.

# Data model

> The tables of the piloop database and how they relate.

The database is the contract between every actor: you, the orchestrator, the daemon and the agents. They all read and
write the same tables, with `piloop sql` or through the CLI. The rules live in the schema, not in an API: CHECK
constraints fix the allowed values, foreign keys and unique keys fix the relations, and triggers record who changed a
row and log each change in `events`.

* A **project** is one repo. It owns its ideas, its candidates and its evaluators. `piloop init` adds an evaluator
  row when the evaluate command, the metrics or `link` change.
* An **idea** is something to try. `idea_links` relates two ideas of one project: one runs `after` the other, or
  `supersedes` it. A track is not a table. `ideas.track` is the name of a track in `piloop.toml`.
* A **run** is one attempt at an idea. A **worker** row is one agent process. A run's worker row has its `run_id`.
* A **candidate** is a distinct state of the code. Its git tree identifies it, and `parent_id` is its lineage. A
  **measurement** is one run of an evaluator on a candidate.
* A **comment** or an **event** points at any row through `(target_kind, target_id)`, so it has no foreign key.
* A **technique** is an outside technique that a scout sweep found.

Three columns hold a candidate id or an evaluator id without a foreign key: `projects.evaluator_id`,
`ideas.base_candidate_id` and `runs.base_candidate_id`. The foreign keys would make a cycle, and SQLite cannot add a
foreign key to a cycle afterwards.

The diagram is generated from the models in `piloop/db/models.py`. Each line is a foreign key, labeled with its column.
The [database schema](/reference/schema) has the full DDL with the indexes.

```mermaid theme={null}
erDiagram
    projects {
        integer id PK
        varchar name UK
        varchar path
        json config
        integer evaluator_id
        ProjectStatus status
        varchar stopped_reason
        double spend_usd
        varchar spend_error
        datetime spend_at
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    evaluators {
        integer id PK
        integer project_id FK
        varchar key
        json definition
        datetime created_at
    }
    ideas {
        integer id PK
        integer project_id FK
        varchar title
        varchar hypothesis
        varchar track
        varchar plan
        IdeaStatus status
        integer priority
        varchar harness
        integer base_candidate_id
        integer max_runs
        varchar created_by
        varchar provenance
        varchar family
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    idea_links {
        integer idea_id PK,FK
        integer target_id PK,FK
        LinkKind kind PK
    }
    runs {
        integer id PK
        integer idea_id FK
        integer attempt
        integer base_candidate_id
        boolean branch
        varchar harness
        varchar model
        RunStatus status
        varchar brief
        datetime started_at
        datetime finished_at
        varchar error
        varchar log_dir
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    candidates {
        integer id PK
        integer project_id FK
        integer run_id FK
        integer parent_id FK
        varchar tree_sha
        varchar commit_sha
        varchar summary
        Verdict verdict
        varchar verdict_by
        varchar verdict_reason
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    measurements {
        integer id PK
        integer candidate_id FK
        integer evaluator_id FK
        json metrics
        varchar error
        double seconds
        datetime created_at
    }
    comments {
        integer id PK
        TargetKind target_kind
        integer target_id
        varchar author
        CommentKind kind
        varchar body
        integer priority
        datetime acked_at
        varchar acked_by
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    techniques {
        integer id PK
        integer project_id FK
        varchar title
        varchar mechanism
        varchar url
        varchar created_by
        datetime created_at
    }
    workers {
        integer id PK
        WorkerKind kind
        integer project_id FK
        integer run_id FK
        varchar harness
        varchar model
        varchar runtime
        json handle
        WorkerState state
        varchar reason
        varchar log_dir
        datetime started_at
        datetime ended_at
        varchar error
        varchar session_id
        varchar checkpoint_id
        json usage
        datetime updated_at
        varchar updated_by
    }
    events {
        integer id PK
        datetime at
        varchar kind
        varchar actor
        TargetKind target_kind
        integer target_id
        json payload
    }
    projects ||--o{ evaluators : "project_id"
    projects ||--o{ ideas : "project_id"
    ideas ||--o{ idea_links : "idea_id"
    ideas ||--o{ idea_links : "target_id"
    ideas ||--o{ runs : "idea_id"
    projects ||--o{ candidates : "project_id"
    runs |o--o{ candidates : "run_id"
    candidates |o--o{ candidates : "parent_id"
    candidates ||--o{ measurements : "candidate_id"
    evaluators ||--o{ measurements : "evaluator_id"
    projects ||--o{ techniques : "project_id"
    projects |o--o{ workers : "project_id"
    runs |o--o{ workers : "run_id"
```

The full-text indexes are not in the diagram: `ideas_fts` (`title`, `hypothesis`, `plan`), `comments_fts` (`body`).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.