sc/storage/
migrations.rs

1//! Database migrations embedded at compile time.
2//!
3//! Migrations are sourced from `cli/migrations/` and embedded into the
4//! binary using `include_str!`. This ensures the package is self-contained
5//! for crates.io publishing.
6//!
7//! To sync migrations from repo root: `npm run sync:migrations`
8
9use rusqlite::{Connection, Result};
10use tracing::{info, warn};
11
12/// A single migration with version identifier and SQL content.
13struct Migration {
14    version: &'static str,
15    sql: &'static str,
16}
17
18/// All migrations in order, embedded at compile time.
19///
20/// Version names match the SQL filenames (without .sql extension).
21/// The `schema_migrations` table tracks which have been applied.
22const MIGRATIONS: &[Migration] = &[
23    Migration {
24        version: "001_add_session_lifecycle",
25        sql: include_str!("../../migrations/001_add_session_lifecycle.sql"),
26    },
27    Migration {
28        version: "002_add_multi_path_sessions",
29        sql: include_str!("../../migrations/002_add_multi_path_sessions.sql"),
30    },
31    Migration {
32        version: "003_add_agent_sessions",
33        sql: include_str!("../../migrations/003_add_agent_sessions.sql"),
34    },
35    Migration {
36        version: "004_add_memory_and_tasks",
37        sql: include_str!("../../migrations/004_add_memory_and_tasks.sql"),
38    },
39    Migration {
40        version: "005_add_checkpoint_grouping",
41        sql: include_str!("../../migrations/005_add_checkpoint_grouping.sql"),
42    },
43    Migration {
44        version: "006_rename_tasks_to_issues",
45        sql: include_str!("../../migrations/006_rename_tasks_to_issues.sql"),
46    },
47    Migration {
48        version: "007_embeddings_support",
49        sql: include_str!("../../migrations/007_embeddings_support.sql"),
50    },
51    Migration {
52        version: "008_dynamic_vec_dimensions",
53        sql: include_str!("../../migrations/008_dynamic_vec_dimensions.sql"),
54    },
55    Migration {
56        version: "009_rename_task_to_reminder",
57        sql: include_str!("../../migrations/009_rename_task_to_reminder.sql"),
58    },
59    Migration {
60        version: "010_issue_projects",
61        sql: include_str!("../../migrations/010_issue_projects.sql"),
62    },
63    Migration {
64        version: "011_blob_embeddings",
65        sql: include_str!("../../migrations/011_blob_embeddings.sql"),
66    },
67    Migration {
68        version: "012_tiered_embeddings",
69        sql: include_str!("../../migrations/012_tiered_embeddings.sql"),
70    },
71    Migration {
72        version: "013_plan_session_binding",
73        sql: include_str!("../../migrations/013_plan_session_binding.sql"),
74    },
75    Migration {
76        version: "014_close_reason",
77        sql: include_str!("../../migrations/014_close_reason.sql"),
78    },
79    Migration {
80        version: "015_add_time_entries",
81        sql: include_str!("../../migrations/015_add_time_entries.sql"),
82    },
83];
84
85/// Run all pending migrations on the database.
86///
87/// Migrations are applied in order. Already-applied migrations (tracked in
88/// the `schema_migrations` table) are skipped. This is idempotent and safe
89/// to call on every database open.
90///
91/// # Errors
92///
93/// Returns an error if a migration fails to apply. Note that ALTER TABLE
94/// errors for duplicate columns are handled gracefully (logged as warnings)
95/// since the schema may already have those columns from the base DDL.
96pub fn run_migrations(conn: &Connection) -> Result<()> {
97    // Ensure schema_migrations table exists
98    conn.execute(
99        "CREATE TABLE IF NOT EXISTS schema_migrations (
100            version TEXT PRIMARY KEY,
101            applied_at INTEGER NOT NULL
102        )",
103        [],
104    )?;
105
106    // Get already applied migrations
107    let applied: std::collections::HashSet<String> = conn
108        .prepare("SELECT version FROM schema_migrations")?
109        .query_map([], |row| row.get(0))?
110        .collect::<Result<_, _>>()?;
111
112    // Apply pending migrations in order
113    for migration in MIGRATIONS {
114        if applied.contains(migration.version) {
115            continue;
116        }
117
118        info!(version = migration.version, "Applying migration");
119
120        // Execute migration SQL
121        if let Err(e) = conn.execute_batch(migration.sql) {
122            let err_str = e.to_string();
123            // Handle expected failures gracefully:
124            // 1. ALTER TABLE with duplicate column (base schema already has columns)
125            // 2. vec0 module not found (sqlite-vec not available in Rust)
126            if err_str.contains("duplicate column name") {
127                warn!(
128                    version = migration.version,
129                    "Migration partially applied (columns exist), marking complete"
130                );
131            } else if err_str.contains("no such module: vec0") {
132                warn!(
133                    version = migration.version,
134                    "Skipping sqlite-vec virtual table (not available in Rust CLI)"
135                );
136            } else {
137                return Err(e);
138            }
139        }
140
141        // Record migration as applied
142        conn.execute(
143            "INSERT INTO schema_migrations (version, applied_at) VALUES (?1, ?2)",
144            rusqlite::params![migration.version, chrono::Utc::now().timestamp_millis()],
145        )?;
146
147        info!(version = migration.version, "Migration complete");
148    }
149
150    Ok(())
151}
152
153#[cfg(test)]
154mod tests {
155    use super::*;
156    use crate::storage::schema::SCHEMA_SQL;
157
158    /// Apply base schema before running migrations (mirrors production flow)
159    fn setup_db(conn: &Connection) {
160        conn.execute_batch(SCHEMA_SQL).expect("Base schema should apply");
161    }
162
163    #[test]
164    fn test_migrations_compile() {
165        // This test verifies that all include_str! paths are valid
166        // If any path is wrong, compilation will fail
167        assert!(!MIGRATIONS.is_empty());
168        assert_eq!(MIGRATIONS.len(), 15);
169    }
170
171    #[test]
172    fn test_run_migrations_fresh_db() {
173        let conn = Connection::open_in_memory().unwrap();
174        setup_db(&conn);
175        run_migrations(&conn).expect("Migrations should apply to fresh database");
176
177        // Verify all migrations are recorded
178        let count: i32 = conn
179            .query_row("SELECT COUNT(*) FROM schema_migrations", [], |row| {
180                row.get(0)
181            })
182            .unwrap();
183        assert_eq!(count, 15);
184    }
185
186    #[test]
187    fn test_run_migrations_idempotent() {
188        let conn = Connection::open_in_memory().unwrap();
189        setup_db(&conn);
190
191        // Run twice - should not fail
192        run_migrations(&conn).expect("First run should succeed");
193        run_migrations(&conn).expect("Second run should succeed (idempotent)");
194
195        // Still only 13 migrations recorded
196        let count: i32 = conn
197            .query_row("SELECT COUNT(*) FROM schema_migrations", [], |row| {
198                row.get(0)
199            })
200            .unwrap();
201        assert_eq!(count, 15);
202    }
203}
sc/storage/migrations.rs

sc/storage/
migrations.rs
