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];
80
81/// Run all pending migrations on the database.
82///
83/// Migrations are applied in order. Already-applied migrations (tracked in
84/// the `schema_migrations` table) are skipped. This is idempotent and safe
85/// to call on every database open.
86///
87/// # Errors
88///
89/// Returns an error if a migration fails to apply. Note that ALTER TABLE
90/// errors for duplicate columns are handled gracefully (logged as warnings)
91/// since the schema may already have those columns from the base DDL.
92pub fn run_migrations(conn: &Connection) -> Result<()> {
93    // Ensure schema_migrations table exists
94    conn.execute(
95        "CREATE TABLE IF NOT EXISTS schema_migrations (
96            version TEXT PRIMARY KEY,
97            applied_at INTEGER NOT NULL
98        )",
99        [],
100    )?;
101
102    // Get already applied migrations
103    let applied: std::collections::HashSet<String> = conn
104        .prepare("SELECT version FROM schema_migrations")?
105        .query_map([], |row| row.get(0))?
106        .collect::<Result<_, _>>()?;
107
108    // Apply pending migrations in order
109    for migration in MIGRATIONS {
110        if applied.contains(migration.version) {
111            continue;
112        }
113
114        info!(version = migration.version, "Applying migration");
115
116        // Execute migration SQL
117        if let Err(e) = conn.execute_batch(migration.sql) {
118            let err_str = e.to_string();
119            // Handle expected failures gracefully:
120            // 1. ALTER TABLE with duplicate column (base schema already has columns)
121            // 2. vec0 module not found (sqlite-vec not available in Rust)
122            if err_str.contains("duplicate column name") {
123                warn!(
124                    version = migration.version,
125                    "Migration partially applied (columns exist), marking complete"
126                );
127            } else if err_str.contains("no such module: vec0") {
128                warn!(
129                    version = migration.version,
130                    "Skipping sqlite-vec virtual table (not available in Rust CLI)"
131                );
132            } else {
133                return Err(e);
134            }
135        }
136
137        // Record migration as applied
138        conn.execute(
139            "INSERT INTO schema_migrations (version, applied_at) VALUES (?1, ?2)",
140            rusqlite::params![migration.version, chrono::Utc::now().timestamp_millis()],
141        )?;
142
143        info!(version = migration.version, "Migration complete");
144    }
145
146    Ok(())
147}
148
149#[cfg(test)]
150mod tests {
151    use super::*;
152    use crate::storage::schema::SCHEMA_SQL;
153
154    /// Apply base schema before running migrations (mirrors production flow)
155    fn setup_db(conn: &Connection) {
156        conn.execute_batch(SCHEMA_SQL).expect("Base schema should apply");
157    }
158
159    #[test]
160    fn test_migrations_compile() {
161        // This test verifies that all include_str! paths are valid
162        // If any path is wrong, compilation will fail
163        assert!(!MIGRATIONS.is_empty());
164        assert_eq!(MIGRATIONS.len(), 14);
165    }
166
167    #[test]
168    fn test_run_migrations_fresh_db() {
169        let conn = Connection::open_in_memory().unwrap();
170        setup_db(&conn);
171        run_migrations(&conn).expect("Migrations should apply to fresh database");
172
173        // Verify all migrations are recorded
174        let count: i32 = conn
175            .query_row("SELECT COUNT(*) FROM schema_migrations", [], |row| {
176                row.get(0)
177            })
178            .unwrap();
179        assert_eq!(count, 14);
180    }
181
182    #[test]
183    fn test_run_migrations_idempotent() {
184        let conn = Connection::open_in_memory().unwrap();
185        setup_db(&conn);
186
187        // Run twice - should not fail
188        run_migrations(&conn).expect("First run should succeed");
189        run_migrations(&conn).expect("Second run should succeed (idempotent)");
190
191        // Still only 13 migrations recorded
192        let count: i32 = conn
193            .query_row("SELECT COUNT(*) FROM schema_migrations", [], |row| {
194                row.get(0)
195            })
196            .unwrap();
197        assert_eq!(count, 14);
198    }
199}
sc/storage/migrations.rs

sc/storage/
migrations.rs
