bkb_core/
schema.rs

1/// SQL statements for creating the BKB database schema.
2pub const SCHEMA_SQL: &str = r#"
3CREATE TABLE IF NOT EXISTS documents (
4    id              TEXT PRIMARY KEY,
5    source_type     TEXT NOT NULL,
6    source_repo     TEXT,
7    source_id       TEXT NOT NULL,
8    title           TEXT,
9    body            TEXT,
10    author          TEXT,
11    author_id       TEXT,
12    created_at      TIMESTAMP NOT NULL,
13    updated_at      TIMESTAMP,
14    parent_id       TEXT,
15    metadata        TEXT,
16    seq             INTEGER,
17    UNIQUE(source_type, source_repo, source_id)
18);
19
20CREATE INDEX IF NOT EXISTS idx_documents_source ON documents(source_type, source_repo);
21CREATE INDEX IF NOT EXISTS idx_documents_parent ON documents(parent_id);
22CREATE INDEX IF NOT EXISTS idx_documents_author ON documents(author);
23CREATE INDEX IF NOT EXISTS idx_documents_created ON documents(created_at);
24CREATE INDEX IF NOT EXISTS idx_documents_seq ON documents(seq);
25
26CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts USING fts5(
27    title,
28    body,
29    content=documents,
30    content_rowid=rowid
31);
32
33CREATE TRIGGER IF NOT EXISTS documents_fts_insert AFTER INSERT ON documents BEGIN
34    INSERT INTO documents_fts(rowid, title, body)
35    VALUES (NEW.rowid, NEW.title, NEW.body);
36END;
37
38CREATE TRIGGER IF NOT EXISTS documents_fts_delete AFTER DELETE ON documents BEGIN
39    INSERT INTO documents_fts(documents_fts, rowid, title, body)
40    VALUES ('delete', OLD.rowid, OLD.title, OLD.body);
41END;
42
43CREATE TRIGGER IF NOT EXISTS documents_fts_update AFTER UPDATE ON documents BEGIN
44    INSERT INTO documents_fts(documents_fts, rowid, title, body)
45    VALUES ('delete', OLD.rowid, OLD.title, OLD.body);
46    INSERT INTO documents_fts(rowid, title, body)
47    VALUES (NEW.rowid, NEW.title, NEW.body);
48END;
49
50CREATE TABLE IF NOT EXISTS refs (
51    id          INTEGER PRIMARY KEY AUTOINCREMENT,
52    from_doc_id TEXT NOT NULL,
53    to_doc_id   TEXT,
54    ref_type    TEXT NOT NULL,
55    to_external TEXT,
56    context     TEXT,
57    FOREIGN KEY (from_doc_id) REFERENCES documents(id)
58);
59
60CREATE INDEX IF NOT EXISTS idx_refs_from ON refs(from_doc_id);
61CREATE INDEX IF NOT EXISTS idx_refs_to ON refs(to_doc_id);
62CREATE INDEX IF NOT EXISTS idx_refs_to_ext ON refs(to_external);
63CREATE INDEX IF NOT EXISTS idx_refs_type ON refs(ref_type);
64
65CREATE TABLE IF NOT EXISTS concepts (
66    slug        TEXT PRIMARY KEY,
67    name        TEXT NOT NULL,
68    category    TEXT,
69    aliases     TEXT
70);
71
72CREATE TABLE IF NOT EXISTS concept_mentions (
73    doc_id       TEXT NOT NULL,
74    concept_slug TEXT NOT NULL,
75    confidence   REAL DEFAULT 1.0,
76    PRIMARY KEY (doc_id, concept_slug),
77    FOREIGN KEY (doc_id) REFERENCES documents(id),
78    FOREIGN KEY (concept_slug) REFERENCES concepts(slug)
79);
80
81CREATE INDEX IF NOT EXISTS idx_concept_mentions_concept ON concept_mentions(concept_slug);
82
83CREATE TABLE IF NOT EXISTS change_log (
84    seq         INTEGER PRIMARY KEY AUTOINCREMENT,
85    doc_id      TEXT NOT NULL,
86    change_type TEXT NOT NULL,
87    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
88);
89
90CREATE TABLE IF NOT EXISTS sync_state (
91    source_id      TEXT PRIMARY KEY,
92    source_type    TEXT NOT NULL,
93    source_repo    TEXT,
94    last_cursor    TEXT,
95    last_synced_at TIMESTAMP,
96    next_run_at    TIMESTAMP,
97    status         TEXT DEFAULT 'pending',
98    error_message  TEXT,
99    retry_count    INTEGER DEFAULT 0,
100    items_found    INTEGER DEFAULT 0
101);
102"#;
bkb_core/schema.rs

bkb_core/
schema.rs
