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

# Database schema

> The tables that piloop sql reads and writes.

Every client reads and writes one SQLite database, `$PILOOP_HOME/piloop.db`. Run one statement with `piloop sql`.
`piloop schema` prints the same tables with the conventions that agents follow. The agents' procedures are in
[sql\_procedures.md](https://github.com/hiloopai/piloop/blob/main/piloop/agents/prompts/sql_procedures.md).

The indexes are part of the contract: they show which filters are cheap.

```sql theme={null}
CREATE TABLE comments (
	id INTEGER NOT NULL,
	target_kind VARCHAR(16) NOT NULL,
	target_id INTEGER NOT NULL,
	author VARCHAR NOT NULL,
	kind VARCHAR(16) DEFAULT 'note' NOT NULL,
	body VARCHAR NOT NULL,
	priority INTEGER,
	acked_at DATETIME,
	acked_by VARCHAR,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	CONSTRAINT escalation_needs_priority CHECK (kind != 'escalation' OR priority IS NOT NULL),
	CONSTRAINT "comments.author must be a text value" CHECK (typeof(author) IN ('text')),
	CONSTRAINT "comments.body must be a text value" CHECK (typeof(body) IN ('text')),
	CONSTRAINT targetkind CHECK (target_kind IN ('project', 'idea', 'run', 'candidate', 'measurement', 'worker', 'comment')),
	CONSTRAINT commentkind CHECK (kind IN ('note', 'finding', 'decision', 'escalation'))
);
CREATE INDEX ix_comments_inbox ON comments (priority, id) WHERE priority IS NOT NULL AND acked_at IS NULL;
CREATE INDEX ix_comments_target ON comments (target_kind, target_id);
CREATE TABLE events (
	id INTEGER NOT NULL,
	at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	kind VARCHAR NOT NULL,
	actor VARCHAR NOT NULL,
	target_kind VARCHAR(16) NOT NULL,
	target_id INTEGER NOT NULL,
	payload JSON DEFAULT '{}' NOT NULL,
	PRIMARY KEY (id),
	CONSTRAINT targetkind CHECK (target_kind IN ('project', 'idea', 'run', 'candidate', 'measurement', 'worker', 'comment'))
);
CREATE INDEX ix_events_target ON events (target_kind, target_id, id);
CREATE TABLE projects (
	id INTEGER NOT NULL,
	name VARCHAR NOT NULL,
	path VARCHAR NOT NULL,
	config JSON NOT NULL,
	evaluator_id INTEGER,
	status VARCHAR(16) DEFAULT 'active' NOT NULL,
	stopped_reason VARCHAR,
	spend_usd DOUBLE,
	spend_error VARCHAR,
	spend_at DATETIME,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	UNIQUE (name),
	CONSTRAINT projectstatus CHECK (status IN ('active', 'stopped'))
);
CREATE TABLE evaluators (
	id INTEGER NOT NULL,
	project_id INTEGER NOT NULL,
	"key" VARCHAR NOT NULL,
	definition JSON NOT NULL,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (project_id, "key"),
	FOREIGN KEY(project_id) REFERENCES projects (id) ON DELETE CASCADE
);
CREATE TABLE ideas (
	id INTEGER NOT NULL,
	project_id INTEGER NOT NULL,
	title VARCHAR NOT NULL,
	hypothesis VARCHAR NOT NULL,
	track VARCHAR NOT NULL,
	"plan" VARCHAR,
	status VARCHAR(16) DEFAULT 'ready' NOT NULL,
	priority INTEGER DEFAULT 2 NOT NULL,
	harness VARCHAR,
	base_candidate_id INTEGER,
	max_runs INTEGER DEFAULT 2 NOT NULL,
	created_by VARCHAR NOT NULL,
	provenance VARCHAR,
	family VARCHAR,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	UNIQUE (project_id, title),
	CONSTRAINT "ideas.title must be a text value" CHECK (typeof(title) IN ('text')),
	CONSTRAINT "ideas.hypothesis must be a text value" CHECK (typeof(hypothesis) IN ('text')),
	CONSTRAINT "ideas.created_by must be a text value" CHECK (typeof(created_by) IN ('text')),
	CONSTRAINT "ideas.track must be a text value" CHECK (typeof(track) IN ('text')),
	CONSTRAINT "ideas.plan must be a text value" CHECK (typeof(plan) IN ('text', 'null')),
	CONSTRAINT "ideas.harness must be a text value" CHECK (typeof(harness) IN ('text', 'null')),
	CONSTRAINT "ideas.provenance must be a text value" CHECK (typeof(provenance) IN ('text', 'null')),
	CONSTRAINT "ideas.family must be a text value" CHECK (typeof(family) IN ('text', 'null')),
	FOREIGN KEY(project_id) REFERENCES projects (id) ON DELETE CASCADE,
	CONSTRAINT ideastatus CHECK (status IN ('seed', 'proposed', 'ready', 'scheduled', 'done', 'parked', 'rejected'))
);
CREATE INDEX ix_ideas_project_status_priority ON ideas (project_id, status, priority);
CREATE TABLE techniques (
	id INTEGER NOT NULL,
	project_id INTEGER NOT NULL,
	title VARCHAR NOT NULL,
	mechanism VARCHAR NOT NULL,
	url VARCHAR NOT NULL,
	created_by VARCHAR NOT NULL,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (project_id, url),
	CONSTRAINT "techniques.title must be a text value" CHECK (typeof(title) IN ('text')),
	CONSTRAINT "techniques.mechanism must be a text value" CHECK (typeof(mechanism) IN ('text')),
	CONSTRAINT "techniques.url must be a text value" CHECK (typeof(url) IN ('text')),
	CONSTRAINT "techniques.created_by must be a text value" CHECK (typeof(created_by) IN ('text')),
	FOREIGN KEY(project_id) REFERENCES projects (id) ON DELETE CASCADE
);
CREATE TABLE idea_links (
	idea_id INTEGER NOT NULL,
	target_id INTEGER NOT NULL,
	kind VARCHAR(16) NOT NULL,
	PRIMARY KEY (idea_id, target_id, kind),
	CONSTRAINT idea_links_not_self CHECK (idea_id != target_id),
	FOREIGN KEY(idea_id) REFERENCES ideas (id) ON DELETE CASCADE,
	FOREIGN KEY(target_id) REFERENCES ideas (id) ON DELETE CASCADE,
	CONSTRAINT linkkind CHECK (kind IN ('after', 'supersedes'))
);
CREATE INDEX ix_idea_links_target ON idea_links (target_id, kind);
CREATE TABLE runs (
	id INTEGER NOT NULL,
	idea_id INTEGER NOT NULL,
	attempt INTEGER NOT NULL,
	base_candidate_id INTEGER NOT NULL,
	branch BOOLEAN DEFAULT 0 NOT NULL,
	harness VARCHAR NOT NULL,
	model VARCHAR NOT NULL,
	status VARCHAR(16) DEFAULT 'queued' NOT NULL,
	brief VARCHAR,
	started_at DATETIME,
	finished_at DATETIME,
	error VARCHAR,
	log_dir VARCHAR,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	UNIQUE (idea_id, attempt),
	FOREIGN KEY(idea_id) REFERENCES ideas (id) ON DELETE CASCADE,
	CONSTRAINT runstatus CHECK (status IN ('queued', 'running', 'succeeded', 'failed', 'timed_out'))
);
CREATE INDEX ix_runs_status ON runs (status);
CREATE TABLE candidates (
	id INTEGER NOT NULL,
	project_id INTEGER NOT NULL,
	run_id INTEGER,
	parent_id INTEGER,
	tree_sha VARCHAR NOT NULL,
	commit_sha VARCHAR NOT NULL,
	summary VARCHAR,
	verdict VARCHAR(16) DEFAULT 'pending' NOT NULL,
	verdict_by VARCHAR,
	verdict_reason VARCHAR,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	UNIQUE (project_id, tree_sha),
	CONSTRAINT "candidates.summary must be a text value" CHECK (typeof(summary) IN ('text', 'null')),
	CONSTRAINT "candidates.verdict_by must be a text value" CHECK (typeof(verdict_by) IN ('text', 'null')),
	CONSTRAINT "candidates.verdict_reason must be a text value" CHECK (typeof(verdict_reason) IN ('text', 'null')),
	FOREIGN KEY(project_id) REFERENCES projects (id) ON DELETE CASCADE,
	FOREIGN KEY(run_id) REFERENCES runs (id) ON DELETE SET NULL,
	FOREIGN KEY(parent_id) REFERENCES candidates (id),
	CONSTRAINT verdict CHECK (verdict IN ('pending', 'keep', 'interesting', 'discard', 'crash', 'reference'))
);
CREATE INDEX ix_candidates_parent ON candidates (parent_id);
CREATE INDEX ix_candidates_project_verdict ON candidates (project_id, verdict);
CREATE INDEX ix_candidates_run ON candidates (run_id);
CREATE TABLE workers (
	id INTEGER NOT NULL,
	kind VARCHAR(16) NOT NULL,
	project_id INTEGER,
	run_id INTEGER,
	harness VARCHAR,
	model VARCHAR,
	runtime VARCHAR,
	handle JSON DEFAULT '{}' NOT NULL,
	state VARCHAR(16) DEFAULT 'requested' NOT NULL,
	reason VARCHAR,
	log_dir VARCHAR,
	started_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ended_at DATETIME,
	error VARCHAR,
	session_id VARCHAR,
	checkpoint_id VARCHAR,
	usage JSON,
	updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	updated_by VARCHAR,
	PRIMARY KEY (id),
	CONSTRAINT "workers.reason must be a text value" CHECK (typeof(reason) IN ('text', 'null')),
	CONSTRAINT "workers.harness must be a text value" CHECK (typeof(harness) IN ('text', 'null')),
	CONSTRAINT "workers.model must be a text value" CHECK (typeof(model) IN ('text', 'null')),
	CONSTRAINT workerkind CHECK (kind IN ('worker', 'watchdog', 'research')),
	FOREIGN KEY(project_id) REFERENCES projects (id) ON DELETE CASCADE,
	FOREIGN KEY(run_id) REFERENCES runs (id) ON DELETE SET NULL,
	CONSTRAINT workerstate CHECK (state IN ('requested', 'starting', 'running', 'exited', 'killed'))
);
CREATE INDEX ix_workers_project_kind_state ON workers (project_id, kind, state);
CREATE INDEX ix_workers_run ON workers (run_id);
CREATE TABLE measurements (
	id INTEGER NOT NULL,
	candidate_id INTEGER NOT NULL,
	evaluator_id INTEGER NOT NULL,
	metrics JSON DEFAULT '{}' NOT NULL,
	error VARCHAR,
	seconds DOUBLE,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY(candidate_id) REFERENCES candidates (id) ON DELETE CASCADE,
	FOREIGN KEY(evaluator_id) REFERENCES evaluators (id)
);
CREATE INDEX ix_measurements_candidate_evaluator ON measurements (candidate_id, evaluator_id);
CREATE VIRTUAL TABLE ideas_fts USING fts5(title, hypothesis, plan, content='ideas', content_rowid='id', tokenize='porter unicode61 remove_diacritics 2');
CREATE VIRTUAL TABLE comments_fts USING fts5(body, content='comments', content_rowid='id', tokenize='porter unicode61 remove_diacritics 2');
```


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