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 Migration {
43 up_sql: include_str!("migrations/0004_task_logs.up.sql"),
44 down_sql: include_str!("migrations/0004_task_logs.down.sql"),
45 post_hook_up: None,
46 post_hook_down: None,
47 },
48];
49
50/// Read the current schema version from the database.
51fn get_version(conn: &Connection) -> Result<u32> {
52 let v: u32 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;
53 Ok(v)
54}
55
56/// Set the schema version inside an open transaction.
57fn set_version(tx: &rusqlite::Transaction, version: u32) -> Result<()> {
58 // PRAGMA cannot be parameterised, but the value is a u32 we control.
59 tx.pragma_update(None, "user_version", version)?;
60 Ok(())
61}
62
63/// Apply all pending up-migrations to bring the database to the latest version.
64pub fn migrate_up(conn: &mut Connection) -> Result<()> {
65 let current = get_version(conn)?;
66 let latest = MIGRATIONS.len() as u32;
67
68 if current > latest {
69 bail!(
70 "database is at version {current} but this binary only knows up to {latest}; \
71 upgrade td or use a matching version"
72 );
73 }
74
75 for (idx, m) in MIGRATIONS.iter().enumerate().skip(current as usize) {
76 let target_version = (idx + 1) as u32;
77
78 let tx = conn
79 .transaction()
80 .context("failed to begin migration transaction")?;
81
82 if !m.up_sql.is_empty() {
83 tx.execute_batch(m.up_sql)
84 .with_context(|| format!("migration {target_version} up SQL failed"))?;
85 }
86
87 if let Some(hook) = m.post_hook_up {
88 hook(&tx)
89 .with_context(|| format!("migration {target_version} post-hook (up) failed"))?;
90 }
91
92 set_version(&tx, target_version)?;
93
94 tx.commit()
95 .with_context(|| format!("failed to commit migration {target_version}"))?;
96 }
97
98 Ok(())
99}
100
101/// Roll back migrations down to `target_version` (inclusive — the database
102/// will be at `target_version` when this returns).
103pub fn migrate_down(conn: &mut Connection, target_version: u32) -> Result<()> {
104 let current = get_version(conn)?;
105
106 if target_version >= current {
107 bail!("target version {target_version} is not below current version {current}");
108 }
109
110 if target_version > MIGRATIONS.len() as u32 {
111 bail!("target version {target_version} exceeds known migrations");
112 }
113
114 // Walk backwards: if we're at version 3 and want version 1, we undo
115 // migration index 2 (v3→v2) then index 1 (v2→v1).
116 for (idx, m) in MIGRATIONS
117 .iter()
118 .enumerate()
119 .rev()
120 .filter(|(i, _)| *i >= target_version as usize && *i < current as usize)
121 {
122 let from_version = (idx + 1) as u32;
123
124 let tx = conn
125 .transaction()
126 .context("failed to begin down-migration transaction")?;
127
128 if let Some(hook) = m.post_hook_down {
129 hook(&tx)
130 .with_context(|| format!("migration {from_version} post-hook (down) failed"))?;
131 }
132
133 if !m.down_sql.is_empty() {
134 tx.execute_batch(m.down_sql)
135 .with_context(|| format!("migration {from_version} down SQL failed"))?;
136 }
137
138 set_version(&tx, idx as u32)?;
139
140 tx.commit()
141 .with_context(|| format!("failed to commit down-migration {from_version}"))?;
142 }
143
144 Ok(())
145}
146
147#[cfg(test)]
148mod tests {
149 use super::*;
150
151 #[test]
152 fn migrate_up_from_empty() {
153 let mut conn = Connection::open_in_memory().unwrap();
154 migrate_up(&mut conn).unwrap();
155
156 let version = get_version(&conn).unwrap();
157 assert_eq!(version, MIGRATIONS.len() as u32);
158
159 // Verify tables exist by querying them.
160 conn.execute_batch("SELECT id FROM tasks LIMIT 0").unwrap();
161 conn.execute_batch("SELECT task_id FROM labels LIMIT 0")
162 .unwrap();
163 conn.execute_batch("SELECT task_id FROM blockers LIMIT 0")
164 .unwrap();
165 conn.execute_batch("SELECT task_id FROM task_logs LIMIT 0")
166 .unwrap();
167 }
168
169 #[test]
170 fn migrate_up_is_idempotent() {
171 let mut conn = Connection::open_in_memory().unwrap();
172 migrate_up(&mut conn).unwrap();
173 // Running again should be a no-op, not an error.
174 migrate_up(&mut conn).unwrap();
175 assert_eq!(get_version(&conn).unwrap(), MIGRATIONS.len() as u32);
176 }
177
178 #[test]
179 fn migrate_down_to_zero() {
180 let mut conn = Connection::open_in_memory().unwrap();
181 migrate_up(&mut conn).unwrap();
182 migrate_down(&mut conn, 0).unwrap();
183 assert_eq!(get_version(&conn).unwrap(), 0);
184
185 // Tables should be gone.
186 let result = conn.execute_batch("SELECT id FROM tasks LIMIT 0");
187 assert!(result.is_err());
188 }
189
190 #[test]
191 fn rejects_future_version() {
192 let mut conn = Connection::open_in_memory().unwrap();
193 conn.pragma_update(None, "user_version", 999).unwrap();
194 let err = migrate_up(&mut conn).unwrap_err();
195 assert!(
196 err.to_string().contains("999"),
197 "error should mention the version: {err}"
198 );
199 }
200}