1//! Versioned schema migrations for the task database.
2//!
3//! Each migration has up/down SQL and optional post-hook functions that run
4//! inside the same transaction. Schema version is tracked via SQLite's built-in
5//! `PRAGMA user_version`.
6
7use anyhow::{bail, Context, Result};
8use rusqlite::Connection;
9
10/// A single schema migration step.
11struct Migration {
12 up_sql: &'static str,
13 down_sql: &'static str,
14 post_hook_up: Option<fn(&rusqlite::Transaction) -> Result<()>>,
15 post_hook_down: Option<fn(&rusqlite::Transaction) -> Result<()>>,
16}
17
18/// All migrations in order. The array index is the version the database will
19/// be at *after* the migration runs (1-indexed: migration 0 brings the DB to
20/// version 1).
21static MIGRATIONS: &[Migration] = &[
22 // 0 → 1: initial schema
23 Migration {
24 up_sql: include_str!("migrations/0001_initial_schema.up.sql"),
25 down_sql: include_str!("migrations/0001_initial_schema.down.sql"),
26 post_hook_up: None,
27 post_hook_down: None,
28 },
29 // 1 → 2: add effort column (integer-backed, default medium)
30 Migration {
31 up_sql: include_str!("migrations/0002_add_effort.up.sql"),
32 down_sql: include_str!("migrations/0002_add_effort.down.sql"),
33 post_hook_up: None,
34 post_hook_down: None,
35 },
36 Migration {
37 up_sql: include_str!("migrations/0003_blocker_fk.up.sql"),
38 down_sql: include_str!("migrations/0003_blocker_fk.down.sql"),
39 post_hook_up: None,
40 post_hook_down: None,
41 },
42];
43
44/// Read the current schema version from the database.
45fn get_version(conn: &Connection) -> Result<u32> {
46 let v: u32 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;
47 Ok(v)
48}
49
50/// Set the schema version inside an open transaction.
51fn set_version(tx: &rusqlite::Transaction, version: u32) -> Result<()> {
52 // PRAGMA cannot be parameterised, but the value is a u32 we control.
53 tx.pragma_update(None, "user_version", version)?;
54 Ok(())
55}
56
57/// Apply all pending up-migrations to bring the database to the latest version.
58pub fn migrate_up(conn: &mut Connection) -> Result<()> {
59 let current = get_version(conn)?;
60 let latest = MIGRATIONS.len() as u32;
61
62 if current > latest {
63 bail!(
64 "database is at version {current} but this binary only knows up to {latest}; \
65 upgrade td or use a matching version"
66 );
67 }
68
69 for (idx, m) in MIGRATIONS.iter().enumerate().skip(current as usize) {
70 let target_version = (idx + 1) as u32;
71
72 let tx = conn
73 .transaction()
74 .context("failed to begin migration transaction")?;
75
76 if !m.up_sql.is_empty() {
77 tx.execute_batch(m.up_sql)
78 .with_context(|| format!("migration {target_version} up SQL failed"))?;
79 }
80
81 if let Some(hook) = m.post_hook_up {
82 hook(&tx)
83 .with_context(|| format!("migration {target_version} post-hook (up) failed"))?;
84 }
85
86 set_version(&tx, target_version)?;
87
88 tx.commit()
89 .with_context(|| format!("failed to commit migration {target_version}"))?;
90 }
91
92 Ok(())
93}
94
95/// Roll back migrations down to `target_version` (inclusive — the database
96/// will be at `target_version` when this returns).
97pub fn migrate_down(conn: &mut Connection, target_version: u32) -> Result<()> {
98 let current = get_version(conn)?;
99
100 if target_version >= current {
101 bail!("target version {target_version} is not below current version {current}");
102 }
103
104 if target_version > MIGRATIONS.len() as u32 {
105 bail!("target version {target_version} exceeds known migrations");
106 }
107
108 // Walk backwards: if we're at version 3 and want version 1, we undo
109 // migration index 2 (v3→v2) then index 1 (v2→v1).
110 for (idx, m) in MIGRATIONS
111 .iter()
112 .enumerate()
113 .rev()
114 .filter(|(i, _)| *i >= target_version as usize && *i < current as usize)
115 {
116 let from_version = (idx + 1) as u32;
117
118 let tx = conn
119 .transaction()
120 .context("failed to begin down-migration transaction")?;
121
122 if let Some(hook) = m.post_hook_down {
123 hook(&tx)
124 .with_context(|| format!("migration {from_version} post-hook (down) failed"))?;
125 }
126
127 if !m.down_sql.is_empty() {
128 tx.execute_batch(m.down_sql)
129 .with_context(|| format!("migration {from_version} down SQL failed"))?;
130 }
131
132 set_version(&tx, idx as u32)?;
133
134 tx.commit()
135 .with_context(|| format!("failed to commit down-migration {from_version}"))?;
136 }
137
138 Ok(())
139}
140
141#[cfg(test)]
142mod tests {
143 use super::*;
144
145 #[test]
146 fn migrate_up_from_empty() {
147 let mut conn = Connection::open_in_memory().unwrap();
148 migrate_up(&mut conn).unwrap();
149
150 let version = get_version(&conn).unwrap();
151 assert_eq!(version, MIGRATIONS.len() as u32);
152
153 // Verify tables exist by querying them.
154 conn.execute_batch("SELECT id FROM tasks LIMIT 0").unwrap();
155 conn.execute_batch("SELECT task_id FROM labels LIMIT 0")
156 .unwrap();
157 conn.execute_batch("SELECT task_id FROM blockers LIMIT 0")
158 .unwrap();
159 }
160
161 #[test]
162 fn migrate_up_is_idempotent() {
163 let mut conn = Connection::open_in_memory().unwrap();
164 migrate_up(&mut conn).unwrap();
165 // Running again should be a no-op, not an error.
166 migrate_up(&mut conn).unwrap();
167 assert_eq!(get_version(&conn).unwrap(), MIGRATIONS.len() as u32);
168 }
169
170 #[test]
171 fn migrate_down_to_zero() {
172 let mut conn = Connection::open_in_memory().unwrap();
173 migrate_up(&mut conn).unwrap();
174 migrate_down(&mut conn, 0).unwrap();
175 assert_eq!(get_version(&conn).unwrap(), 0);
176
177 // Tables should be gone.
178 let result = conn.execute_batch("SELECT id FROM tasks LIMIT 0");
179 assert!(result.is_err());
180 }
181
182 #[test]
183 fn rejects_future_version() {
184 let mut conn = Connection::open_in_memory().unwrap();
185 conn.pragma_update(None, "user_version", 999).unwrap();
186 let err = migrate_up(&mut conn).unwrap_err();
187 assert!(
188 err.to_string().contains("999"),
189 "error should mention the version: {err}"
190 );
191 }
192}