> ## 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. 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`.

Core's tables hold what every campaign has:

* A **campaign** is one repo.
* An **agent session** is one agent process, in any role.
* A **comment** or an **event** points at any row through `(target_kind, target_id)`, so it has no foreign key. A
  program reads the events after a cursor with `piloop events`: see the [event feed](https://piloop.autoresearch.inc/reference/event-feed.md).

The `optimization` campaign type's tables hold the research:

* An **idea** is something to try. `idea_links` relates two ideas of one campaign: one runs `after` the other, or
  `supersedes` it. `ideas.track` is the name of a track in `piloop.toml`.
* A **run** is one attempt at an idea. Its agent session has its `run_id`.
* A **candidate** is a distinct state of the code. Its git tree identifies it, and `parent_id` is its lineage.
* An **evaluator** is one version of the evaluate command and the metrics. `piloop init` adds one when they change.
  A **measurement** is one run of an evaluator on a candidate.
* A **technique** is an outside technique that a [scout](https://piloop.autoresearch.inc/concepts/roles.md#the-built-in-roles) sweep found.

The memory's tables (`lessons`, `documents`, `journal`) and the archive's (`archives`) are their providers'.
Three columns hold a candidate id or an evaluator id without a foreign key: `campaigns.evaluator_id`,
`ideas.base_candidate_id` and `runs.base_candidate_id`. The foreign keys would make a cycle.

Each line of the diagram is a foreign key, labeled with its column. The [database schema](https://piloop.autoresearch.inc/reference/schema.md) has the
full DDL with the indexes, by the plugin that owns each table.

```mermaid theme={null}
erDiagram
    campaigns {
        integer id PK
        varchar name
        varchar path
        json config
        integer evaluator_id
        CampaignStatus status
        varchar stopped_reason
        Pause paused
        varchar paused_reason
        varchar repo
        varchar type
        integer parent_id FK
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    comments {
        integer id PK
        varchar target_kind
        integer target_id
        integer in_reply_to FK
        varchar author
        CommentKind kind
        varchar body
        integer priority
        datetime acked_at
        varchar acked_by
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    events {
        integer id PK
        datetime at
        varchar kind
        varchar actor
        TargetKind target_kind
        integer target_id
        json payload
    }
    lease_workers {
        varchar id PK
        varchar pool
        varchar version
        json runners
        json sample
        datetime seen_at
        NoLease no_lease
        integer cursor
        datetime drained_at
    }
    limits {
        varchar key PK
        json value
        varchar reason
        varchar actor
        datetime at
    }
    migrations {
        varchar name PK
        varchar package
        integer fingerprint
    }
    tokens {
        integer id PK
        varchar name UK
        Access access
        varchar sha256 UK
        integer agent_session_id UK
        datetime created_at
    }
    worker_instances {
        integer id PK
        varchar name UK
        varchar pool
        WorkerInstanceState state
        json handle
        datetime requested_at
        datetime registered_at
        datetime ended_at
        InstanceEnd end
    }
    archives {
        integer id PK
        integer campaign_id FK
        TargetKind subject_kind
        integer subject_id
        varchar manifest
        integer files
        integer bytes
        datetime created_at
        datetime pruned_at
        boolean complete
    }
    documents {
        integer id PK
        integer campaign_id FK
        varchar path
        varchar hash
        varchar title
        varchar body
        varchar created_by
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    evaluators {
        integer id PK
        integer campaign_id FK
        varchar key
        json definition
        varchar runner
        datetime created_at
    }
    first_transfers {
        integer campaign_id PK,FK
        varchar revision
        varchar blob
    }
    ideas {
        integer id PK
        integer campaign_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
    }
    journal {
        integer id PK
        integer campaign_id FK
        varchar author
        varchar line
        datetime created_at
    }
    lessons {
        integer id PK
        integer campaign_id FK
        varchar family
        LessonStatus status
        double effect
        varchar metric
        varchar body
        varchar created_by
        datetime created_at
    }
    secrets {
        integer id PK
        integer campaign_id FK
        varchar name
        varchar value
    }
    techniques {
        integer id PK
        integer campaign_id FK
        varchar title
        varchar mechanism
        varchar url
        varchar created_by
        datetime created_at
    }
    idea_links {
        integer idea_id PK,FK
        integer target_id PK,FK
        LinkKind kind PK
    }
    runs {
        integer id PK
        integer idea_id FK
        varchar role
        integer attempt
        integer base_candidate_id
        boolean branch
        varchar harness
        varchar model
        RunStatus status
        boolean scoring
        varchar brief_cid
        datetime started_at
        datetime finished_at
        RunEnd end
        varchar error
        varchar log_dir
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    agent_sessions {
        integer id PK
        varchar kind
        integer campaign_id FK
        integer run_id FK
        varchar harness
        varchar model
        varchar runtime
        json handle
        AgentSessionState state
        varchar reason
        varchar log_dir
        datetime started_at
        datetime ended_at
        varchar error
        varchar session_id
        varchar machine
        varchar session_file
        json usage
        datetime updated_at
        varchar updated_by
    }
    candidates {
        integer id PK
        integer campaign_id FK
        integer run_id FK
        integer parent_id FK
        varchar tree_sha
        varchar revision
        varchar summary
        Origin origin
        Outcome verdict
        Cause cause
        varchar verdict_by
        varchar verdict_reason
        datetime created_at
        datetime updated_at
        varchar updated_by
    }
    evaluations {
        integer id PK
        integer candidate_id FK
        integer evaluator_id FK
        datetime created_at
        datetime started_at
        varchar cancelled_by
    }
    leases {
        integer id PK
        LeaseKind kind
        integer agent_session_id FK
        integer evaluation_id
        LeaseState state
        varchar worker
        varchar job_cid
        json handle
        datetime created_at
        datetime acquired_at
        datetime started_at
        datetime renewed_at
        datetime expires_at
        datetime revoked_at
        datetime ended_at
        varchar acquire_request
        varchar report_request
        json receipt
    }
    measurements {
        integer id PK
        integer candidate_id FK
        integer evaluator_id FK
        json metrics
        varchar error
        double seconds
        integer pair_id FK
        varchar rung
        varchar stopped
        datetime created_at
    }
    campaigns |o--o{ campaigns : "parent_id"
    comments |o--o{ comments : "in_reply_to"
    campaigns ||--o{ archives : "campaign_id"
    campaigns ||--o{ documents : "campaign_id"
    campaigns ||--o{ evaluators : "campaign_id"
    campaigns ||--o{ first_transfers : "campaign_id"
    campaigns ||--o{ ideas : "campaign_id"
    campaigns |o--o{ journal : "campaign_id"
    campaigns ||--o{ lessons : "campaign_id"
    campaigns |o--o{ secrets : "campaign_id"
    campaigns ||--o{ techniques : "campaign_id"
    ideas ||--o{ idea_links : "idea_id"
    ideas ||--o{ idea_links : "target_id"
    ideas ||--o{ runs : "idea_id"
    campaigns |o--o{ agent_sessions : "campaign_id"
    runs |o--o{ agent_sessions : "run_id"
    campaigns ||--o{ candidates : "campaign_id"
    runs |o--o{ candidates : "run_id"
    candidates |o--o{ candidates : "parent_id"
    candidates ||--o{ evaluations : "candidate_id"
    evaluators ||--o{ evaluations : "evaluator_id"
    agent_sessions |o--o{ leases : "agent_session_id"
    candidates ||--o{ measurements : "candidate_id"
    evaluators ||--o{ measurements : "evaluator_id"
    measurements |o--o{ measurements : "pair_id"
```

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

## Statuses

A CHECK constraint fixes the values of each status column. The daemon and the CLI move a row from one value to the
next, as [ideas and runs](https://piloop.autoresearch.inc/concepts/ideas-and-runs.md), [the verdict](https://piloop.autoresearch.inc/concepts/verdict.md) and
[agents](https://piloop.autoresearch.inc/concepts/roles.md#watch-and-steer-an-agent) show. Anyone with `piloop sql` can set any value, for example to park an
idea.

| Enum | Column | Values |
| - | - | - |
| `IdeaStatus` | `ideas.status` | `seed`, `proposed`, `ready`, `scheduled`, `done`, `parked`, `rejected` |
| `RunStatus` | `runs.status` | `queued`, `running`, `succeeded`, `failed`, `timed_out` |
| `RunEnd` | `runs.end` | `finished`, `failed`, `timed_out`, `awaiting_answer`, `cancelled`, `campaign_stopped`, `daemon_stopped`, `stale`, `role_removed`, `not_started`, `worker_lost` |
| `AgentSessionState` | `agent_sessions.state` | `requested`, `starting`, `running`, `ending`, `exited`, `killed` |
| `Outcome` | `candidates.verdict` | `keep`, `interesting`, `discard`, `crash` |
| `Origin` | `candidates.origin` | `baseline`, `run`, `reference`, `recovered`, `skipped` |
| `Cause` | `candidates.cause` | `note`, `moved` |


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