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');