mdvault_core/index/
schema.rs

1//! SQLite schema definition and migrations.
2
3use rusqlite::Connection;
4use thiserror::Error;
5
6/// Current schema version.
7pub const SCHEMA_VERSION: i32 = 1;
8
9#[derive(Debug, Error)]
10pub enum SchemaError {
11    #[error("Database error: {0}")]
12    Database(#[from] rusqlite::Error),
13
14    #[error("Schema version {found} is newer than supported {supported}")]
15    VersionTooNew { found: i32, supported: i32 },
16
17    #[error("Migration failed: {0}")]
18    MigrationFailed(String),
19}
20
21/// Initialize or migrate the database schema.
22pub fn init_schema(conn: &Connection) -> Result<(), SchemaError> {
23    let version = get_schema_version(conn)?;
24
25    if version == 0 {
26        // Fresh database - create all tables
27        create_schema_v1(conn)?;
28        set_schema_version(conn, SCHEMA_VERSION)?;
29    } else if version < SCHEMA_VERSION {
30        // Run migrations
31        migrate(conn, version)?;
32    } else if version > SCHEMA_VERSION {
33        return Err(SchemaError::VersionTooNew {
34            found: version,
35            supported: SCHEMA_VERSION,
36        });
37    }
38
39    Ok(())
40}
41
42fn get_schema_version(conn: &Connection) -> Result<i32, SchemaError> {
43    // Check if schema_version table exists
44    let exists: bool = conn.query_row(
45        "SELECT COUNT(*) > 0 FROM sqlite_master WHERE type='table' AND name='schema_version'",
46        [],
47        |row| row.get(0),
48    )?;
49
50    if !exists {
51        return Ok(0);
52    }
53
54    let version: i32 =
55        conn.query_row("SELECT version FROM schema_version", [], |row| row.get(0))?;
56
57    Ok(version)
58}
59
60fn set_schema_version(conn: &Connection, version: i32) -> Result<(), SchemaError> {
61    conn.execute(
62        "INSERT OR REPLACE INTO schema_version (id, version) VALUES (1, ?1)",
63        [version],
64    )?;
65    Ok(())
66}
67
68fn create_schema_v1(conn: &Connection) -> Result<(), SchemaError> {
69    conn.execute_batch(
70        r#"
71        -- Schema version tracking
72        CREATE TABLE schema_version (
73            id INTEGER PRIMARY KEY CHECK (id = 1),
74            version INTEGER NOT NULL
75        );
76
77        -- Notes table: core metadata for each markdown file
78        CREATE TABLE notes (
79            id INTEGER PRIMARY KEY AUTOINCREMENT,
80            path TEXT NOT NULL UNIQUE,
81            note_type TEXT NOT NULL DEFAULT 'none',
82            title TEXT NOT NULL,
83            created_at TEXT,
84            modified_at TEXT NOT NULL,
85            frontmatter_json TEXT,
86            content_hash TEXT NOT NULL
87        );
88
89        -- Index for common queries
90        CREATE INDEX idx_notes_type ON notes(note_type);
91        CREATE INDEX idx_notes_modified ON notes(modified_at);
92        CREATE INDEX idx_notes_path ON notes(path);
93
94        -- Links table: relationships between notes
95        CREATE TABLE links (
96            id INTEGER PRIMARY KEY AUTOINCREMENT,
97            source_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
98            target_id INTEGER REFERENCES notes(id) ON DELETE SET NULL,
99            target_path TEXT NOT NULL,
100            link_text TEXT,
101            link_type TEXT NOT NULL,
102            context TEXT,
103            line_number INTEGER
104        );
105
106        -- Indexes for link queries
107        CREATE INDEX idx_links_source ON links(source_id);
108        CREATE INDEX idx_links_target ON links(target_id);
109        CREATE INDEX idx_links_target_path ON links(target_path);
110
111        -- Temporal activity: when notes are referenced in dailies
112        CREATE TABLE temporal_activity (
113            id INTEGER PRIMARY KEY AUTOINCREMENT,
114            note_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
115            daily_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
116            activity_date TEXT NOT NULL,
117            context TEXT
118        );
119
120        CREATE INDEX idx_temporal_note ON temporal_activity(note_id);
121        CREATE INDEX idx_temporal_daily ON temporal_activity(daily_id);
122        CREATE INDEX idx_temporal_date ON temporal_activity(activity_date);
123
124        -- Activity summary: cached aggregations (can be rebuilt)
125        CREATE TABLE activity_summary (
126            note_id INTEGER PRIMARY KEY REFERENCES notes(id) ON DELETE CASCADE,
127            last_seen TEXT,
128            access_count_30d INTEGER NOT NULL DEFAULT 0,
129            access_count_90d INTEGER NOT NULL DEFAULT 0,
130            staleness_score REAL NOT NULL DEFAULT 0.0
131        );
132
133        -- Note cooccurrence: notes appearing together in dailies
134        CREATE TABLE note_cooccurrence (
135            note_a_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
136            note_b_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
137            shared_daily_count INTEGER NOT NULL DEFAULT 0,
138            most_recent TEXT,
139            PRIMARY KEY (note_a_id, note_b_id)
140        );
141
142        CREATE INDEX idx_cooccurrence_a ON note_cooccurrence(note_a_id);
143        CREATE INDEX idx_cooccurrence_b ON note_cooccurrence(note_b_id);
144
145        -- Full-text search virtual table (optional, for content search)
146        CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
147            title,
148            content,
149            content_rowid='id'
150        );
151        "#,
152    )?;
153
154    Ok(())
155}
156
157fn migrate(_conn: &Connection, from_version: i32) -> Result<(), SchemaError> {
158    // Add migration steps here as schema evolves
159    // Example:
160    // match from_version {
161    //     1 => migrate_v1_to_v2(conn)?,
162    //     2 => migrate_v2_to_v3(conn)?,
163    //     _ => {}
164    // }
165
166    // For now, no migrations exist - we only have v1
167    Err(SchemaError::MigrationFailed(format!(
168        "No migration path from version {} to {}",
169        from_version, SCHEMA_VERSION
170    )))
171}
172
173#[cfg(test)]
174mod tests {
175    use super::*;
176    use rusqlite::Connection;
177
178    #[test]
179    fn test_init_fresh_database() {
180        let conn = Connection::open_in_memory().unwrap();
181        init_schema(&conn).unwrap();
182
183        // Verify schema version
184        let version: i32 = conn
185            .query_row("SELECT version FROM schema_version", [], |row| row.get(0))
186            .unwrap();
187        assert_eq!(version, SCHEMA_VERSION);
188
189        // Verify tables exist
190        let tables: Vec<String> = conn
191            .prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
192            .unwrap()
193            .query_map([], |row| row.get(0))
194            .unwrap()
195            .filter_map(|r| r.ok())
196            .collect();
197
198        assert!(tables.contains(&"notes".to_string()));
199        assert!(tables.contains(&"links".to_string()));
200        assert!(tables.contains(&"temporal_activity".to_string()));
201    }
202
203    #[test]
204    fn test_init_idempotent() {
205        let conn = Connection::open_in_memory().unwrap();
206        init_schema(&conn).unwrap();
207        init_schema(&conn).unwrap(); // Should not fail on second call
208    }
209}
mdvault_core/index/schema.rs

mdvault_core/index/
schema.rs
