research_agent/store/
schema.rs

1/// Additive migrations for pre-existing databases. Each entry is `(sql, column)`
2/// where `column` is checked via `PRAGMA table_info` before running, so each
3/// migration is idempotent — it runs only when the column is actually missing.
4/// This never crashes on "duplicate column" even if `_meta.schema_version` lags
5/// behind reality (e.g. a DB upgraded by an earlier build that added the column
6/// without bumping the version).
7pub const MIGRATION_SQL: &[(&str, &str)] = &[
8    ("ALTER TABLE papers ADD COLUMN rating INTEGER", "rating"),
9    (
10        "ALTER TABLE papers ADD COLUMN openalex_id TEXT",
11        "openalex_id",
12    ),
13    (
14        "ALTER TABLE papers ADD COLUMN keywords TEXT NOT NULL DEFAULT ''",
15        "keywords",
16    ),
17];
18
19/// Rebuilds `papers_fts` so it carries the `keywords` column, plus the three
20/// triggers that feed it. Unlike `MIGRATION_SQL` this cannot be guarded by a
21/// column check: `papers_fts` is a virtual table created with
22/// `IF NOT EXISTS`, so an existing database keeps its old 4-column definition
23/// forever unless the table is dropped and recreated. Runs only when the
24/// stored schema version lags `TARGET_SCHEMA_VERSION` — the trailing `rebuild`
25/// re-indexes the whole corpus, which is O(corpus), not something to repeat on
26/// every open. Must run AFTER the `keywords` column exists: the recreated
27/// triggers reference `new.keywords`.
28pub const FTS_V3_SQL: &str = r#"
29DROP TRIGGER IF EXISTS papers_ai;
30DROP TRIGGER IF EXISTS papers_ad;
31DROP TRIGGER IF EXISTS papers_au;
32DROP TABLE IF EXISTS papers_fts;
33
34CREATE VIRTUAL TABLE papers_fts USING fts5(
35    title, abstract_text, notes, tags, keywords,
36    content=papers, content_rowid=rowid, tokenize='trigram'
37);
38
39CREATE TRIGGER papers_ai AFTER INSERT ON papers BEGIN
40    INSERT INTO papers_fts(rowid, title, abstract_text, notes, tags, keywords)
41    VALUES (new.rowid, new.title, new.abstract_text, new.notes, new.tags, new.keywords);
42END;
43
44CREATE TRIGGER papers_ad AFTER DELETE ON papers BEGIN
45    INSERT INTO papers_fts(papers_fts, rowid, title, abstract_text, notes, tags, keywords)
46    VALUES('delete', old.rowid, old.title, old.abstract_text, old.notes, old.tags, old.keywords);
47END;
48
49CREATE TRIGGER papers_au AFTER UPDATE ON papers BEGIN
50    INSERT INTO papers_fts(papers_fts, rowid, title, abstract_text, notes, tags, keywords)
51    VALUES('delete', old.rowid, old.title, old.abstract_text, old.notes, old.tags, old.keywords);
52    INSERT INTO papers_fts(rowid, title, abstract_text, notes, tags, keywords)
53    VALUES (new.rowid, new.title, new.abstract_text, new.notes, new.tags, new.keywords);
54END;
55
56INSERT INTO papers_fts(papers_fts) VALUES('rebuild');
57"#;
58
59/// `_meta.schema_version` written once `MIGRATION_SQL` is fully applied.
60pub const TARGET_SCHEMA_VERSION: i64 = 3;
61
62pub const SCHEMA_SQL: &str = r#"
63CREATE TABLE IF NOT EXISTS _meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
64INSERT OR IGNORE INTO _meta (key, value) VALUES ('schema_version', '1');
65
66CREATE TABLE IF NOT EXISTS papers (
67    id TEXT PRIMARY KEY,
68    title TEXT NOT NULL,
69    authors TEXT NOT NULL DEFAULT '[]',
70    abstract_text TEXT NOT NULL DEFAULT '',
71    year INTEGER,
72    venue TEXT,
73    doi TEXT,
74    arxiv_id TEXT,
75    s2_id TEXT,
76    openalex_id TEXT,
77    url TEXT,
78    pdf_path TEXT,
79    status TEXT NOT NULL DEFAULT 'discovered',
80    reading_status TEXT NOT NULL DEFAULT 'unread',
81    notes TEXT NOT NULL DEFAULT '',
82    tags TEXT NOT NULL DEFAULT '[]',
83    relevance_score REAL NOT NULL DEFAULT 0.5,
84    rating INTEGER,
85    keywords TEXT NOT NULL DEFAULT '',
86    created_at TEXT NOT NULL,
87    updated_at TEXT NOT NULL
88);
89
90CREATE VIRTUAL TABLE IF NOT EXISTS papers_fts USING fts5(
91    title, abstract_text, notes, tags, keywords,
92    content=papers, content_rowid=rowid, tokenize='trigram'
93);
94
95CREATE TABLE IF NOT EXISTS research_topics (
96    id TEXT PRIMARY KEY,
97    name TEXT NOT NULL,
98    description TEXT NOT NULL DEFAULT '',
99    parent_topic_id TEXT REFERENCES research_topics(id),
100    depth INTEGER NOT NULL DEFAULT 0,
101    priority REAL NOT NULL DEFAULT 0.5,
102    created_at TEXT NOT NULL
103);
104
105CREATE TABLE IF NOT EXISTS topic_papers (
106    topic_id TEXT NOT NULL REFERENCES research_topics(id),
107    paper_id TEXT NOT NULL REFERENCES papers(id),
108    relevance REAL NOT NULL DEFAULT 0.5,
109    PRIMARY KEY (topic_id, paper_id)
110);
111
112CREATE TABLE IF NOT EXISTS knowledge_gaps (
113    id TEXT PRIMARY KEY,
114    description TEXT NOT NULL,
115    topic_id TEXT NOT NULL REFERENCES research_topics(id),
116    gap_type TEXT NOT NULL DEFAULT 'missing_literature',
117    priority REAL NOT NULL DEFAULT 0.5,
118    discovered_at TEXT NOT NULL
119);
120
121CREATE TABLE IF NOT EXISTS research_state (
122    topic_id TEXT PRIMARY KEY REFERENCES research_topics(id),
123    papers_read INTEGER NOT NULL DEFAULT 0,
124    papers_queued INTEGER NOT NULL DEFAULT 0,
125    gaps_identified INTEGER NOT NULL DEFAULT 0,
126    coverage_score REAL NOT NULL DEFAULT 0.0,
127    last_updated TEXT NOT NULL
128);
129
130CREATE TABLE IF NOT EXISTS research_reports (
131    id TEXT PRIMARY KEY,
132    title TEXT NOT NULL,
133    topic_ids TEXT NOT NULL DEFAULT '[]',
134    content TEXT NOT NULL DEFAULT '',
135    format TEXT NOT NULL DEFAULT 'markdown',
136    output_path TEXT,
137    generated_at TEXT NOT NULL
138);
139
140CREATE TRIGGER IF NOT EXISTS papers_ai AFTER INSERT ON papers BEGIN
141    INSERT INTO papers_fts(rowid, title, abstract_text, notes, tags, keywords)
142    VALUES (new.rowid, new.title, new.abstract_text, new.notes, new.tags, new.keywords);
143END;
144
145CREATE TRIGGER IF NOT EXISTS papers_ad AFTER DELETE ON papers BEGIN
146    INSERT INTO papers_fts(papers_fts, rowid, title, abstract_text, notes, tags, keywords)
147    VALUES('delete', old.rowid, old.title, old.abstract_text, old.notes, old.tags, old.keywords);
148END;
149
150CREATE TRIGGER IF NOT EXISTS papers_au AFTER UPDATE ON papers BEGIN
151    INSERT INTO papers_fts(papers_fts, rowid, title, abstract_text, notes, tags, keywords)
152    VALUES('delete', old.rowid, old.title, old.abstract_text, old.notes, old.tags, old.keywords);
153    INSERT INTO papers_fts(rowid, title, abstract_text, notes, tags, keywords)
154    VALUES (new.rowid, new.title, new.abstract_text, new.notes, new.tags, new.keywords);
155END;
156
157-- Full extracted body text lives outside `papers` so the Paper domain type and
158-- every tool response stay small; FTS5 searches it through its own index.
159-- Paper-to-paper reference edges (citation graph). Logical keys only: a
160-- referenced paper may be ingested in the same call as the citing one, so
161-- foreign keys would force an ordering the fetch cannot guarantee.
162CREATE TABLE IF NOT EXISTS citations (
163    citing_paper_id TEXT NOT NULL,
164    cited_paper_id TEXT NOT NULL,
165    context TEXT NOT NULL DEFAULT '',
166    PRIMARY KEY (citing_paper_id, cited_paper_id)
167);
168
169CREATE TABLE IF NOT EXISTS paper_bodies (
170    paper_id TEXT PRIMARY KEY REFERENCES papers(id) ON DELETE CASCADE,
171    body TEXT NOT NULL DEFAULT ''
172);
173
174CREATE VIRTUAL TABLE IF NOT EXISTS bodies_fts USING fts5(
175    body, content=paper_bodies, content_rowid=rowid, tokenize='trigram'
176);
177
178CREATE TRIGGER IF NOT EXISTS bodies_ai AFTER INSERT ON paper_bodies BEGIN
179    INSERT INTO bodies_fts(rowid, body) VALUES (new.rowid, new.body);
180END;
181
182CREATE TRIGGER IF NOT EXISTS bodies_ad AFTER DELETE ON paper_bodies BEGIN
183    INSERT INTO bodies_fts(bodies_fts, rowid, body)
184    VALUES('delete', old.rowid, old.body);
185END;
186
187CREATE TRIGGER IF NOT EXISTS bodies_au AFTER UPDATE ON paper_bodies BEGIN
188    INSERT INTO bodies_fts(bodies_fts, rowid, body)
189    VALUES('delete', old.rowid, old.body);
190    INSERT INTO bodies_fts(rowid, body) VALUES (new.rowid, new.body);
191END;
192"#;
research_agent/store/schema.rs

research_agent/store/
schema.rs
