kasl/db/migrations.rs
1//! Database schema migration management and versioning system.
2//!
3//! Provides a comprehensive migration framework for evolving the database schema
4//! over time while maintaining data integrity and consistency.
5//!
6//! ## Usage
7//!
8//! ```rust,no_run
9//! # fn main() -> Result<(), Box<dyn std::error::Error>> {
10//! use kasl::db::migrations::{init_with_migrations, get_db_version};
11//! use rusqlite::Connection;
12//!
13//! let mut conn = Connection::open("kasl.db")?;
14//! init_with_migrations(&mut conn)?;
15//! let version = get_db_version(&conn)?;
16//! # Ok(())
17//! # }
18//! ```
19
20use crate::libs::messages::Message;
21use crate::{msg_debug, msg_error, msg_info, msg_success};
22use anyhow::Result;
23use rusqlite::{Connection, Transaction, params};
24
25/// SQL schema for the migrations tracking table.
26///
27/// This table maintains a complete record of all applied migrations,
28/// enabling version tracking and providing an audit trail of schema changes.
29/// Each migration is recorded with its version, name, and application timestamp.
30const MIGRATIONS_TABLE: &str = "
31CREATE TABLE IF NOT EXISTS migrations (
32 id INTEGER PRIMARY KEY,
33 version INTEGER NOT NULL UNIQUE,
34 name TEXT NOT NULL,
35 applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
36)";
37
38/// Represents a single database migration with execution logic.
39///
40/// Each migration contains the information needed to apply a specific
41/// schema change, including version tracking and the transformation function.
42/// Migrations are designed to be immutable and deterministic.
43#[derive(Debug, Clone)]
44struct Migration {
45 /// Unique version number for ordering and tracking
46 version: u32,
47 /// Human-readable name describing the migration's purpose
48 name: &'static str,
49 /// Function that applies the schema changes within a transaction
50 up: fn(&Transaction) -> Result<()>,
51}
52
53/// Central migration system manager that orchestrates schema evolution.
54///
55/// The `MigrationManager` maintains the complete registry of available migrations
56/// and provides the logic for applying them in the correct order. It ensures
57/// that migrations are applied atomically and tracks their completion status.
58pub struct MigrationManager {
59 /// Ordered list of all available migrations
60 ///
61 /// Migrations are stored in version order to ensure correct application
62 /// sequence. Each migration builds upon the schema state created by
63 /// its predecessors.
64 migrations: Vec<Migration>,
65}
66
67impl Default for MigrationManager {
68 fn default() -> Self {
69 Self::new()
70 }
71}
72
73impl MigrationManager {
74 /// Creates a new migration manager with all registered migrations.
75 ///
76 /// # Example
77 ///
78 /// ```rust
79 /// use kasl::db::migrations::MigrationManager;
80 ///
81 /// let manager = MigrationManager::new();
82 /// // Manager is ready to apply migrations
83 /// ```
84 pub fn new() -> Self {
85 let mut manager = Self { migrations: Vec::new() };
86
87 // Register all migrations in chronological order
88 // Each registration adds a migration to the internal registry
89 manager.register_migrations();
90 manager
91 }
92
93 /// Registers all database migrations in chronological order.
94 fn register_migrations(&mut self) {
95 // Initial schema - version 0 is implicit (empty database)
96 // Base tables are created by individual modules as needed
97
98 // Version 1: Base tables and performance indices
99 // Creates fundamental tables and adds indices for better performance
100 self.add_migration(1, "create_tables_and_indices", |tx| {
101 // First, create base tables that individual modules depend on
102 // This ensures tables exist before any indices are created
103
104 // Create tasks table
105 tx.execute(
106 "CREATE TABLE IF NOT EXISTS tasks (
107 id INTEGER NOT NULL PRIMARY KEY,
108 task_id INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 0,
109 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
110 name TEXT NOT NULL,
111 comment TEXT,
112 completeness INTEGER NOT NULL ON CONFLICT REPLACE DEFAULT 100,
113 excluded_from_search BOOLEAN NOT NULL ON CONFLICT REPLACE DEFAULT FALSE
114 )",
115 [],
116 )?;
117
118 // Create pauses table
119 tx.execute(
120 "CREATE TABLE IF NOT EXISTS pauses (
121 id INTEGER NOT NULL PRIMARY KEY,
122 start TIMESTAMP NOT NULL,
123 end TIMESTAMP,
124 duration INTEGER
125 )",
126 [],
127 )?;
128
129 // Create workdays table
130 tx.execute(
131 "CREATE TABLE IF NOT EXISTS workdays (
132 id INTEGER PRIMARY KEY,
133 date DATE NOT NULL UNIQUE,
134 start TIMESTAMP NOT NULL,
135 end TIMESTAMP
136 )",
137 [],
138 )?;
139
140 // Now create indices for the tables we just created
141
142 // Index tasks by timestamp for chronological queries
143 tx.execute("CREATE INDEX IF NOT EXISTS idx_tasks_timestamp ON tasks(timestamp)", [])?;
144 // Index tasks by parent task relationship
145 tx.execute("CREATE INDEX IF NOT EXISTS idx_tasks_task_id ON tasks(task_id)", [])?;
146 // Index pauses by start time for temporal queries
147 tx.execute("CREATE INDEX IF NOT EXISTS idx_pauses_start ON pauses(start)", [])?;
148 // Index workdays by date for daily/monthly reporting
149 tx.execute("CREATE INDEX IF NOT EXISTS idx_workdays_date ON workdays(date)", [])?;
150
151 Ok(())
152 });
153
154 // Version 2: Task templates system for reusable task patterns
155 // Introduces the ability to save and reuse common task configurations
156 self.add_migration(2, "add_task_templates", |tx| {
157 tx.execute(
158 "CREATE TABLE IF NOT EXISTS task_templates (
159 id INTEGER PRIMARY KEY,
160 name TEXT NOT NULL UNIQUE,
161 task_name TEXT NOT NULL,
162 comment TEXT,
163 completeness INTEGER DEFAULT 100,
164 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
165 )",
166 [],
167 )?;
168 Ok(())
169 });
170
171 // Version 3: Tags and categorization system for task organization
172 // Adds support for tagging tasks with customizable labels and colors
173 self.add_migration(3, "add_tags_system", |tx| {
174 // Main tags table for storing tag definitions
175 tx.execute(
176 "CREATE TABLE IF NOT EXISTS tags (
177 id INTEGER PRIMARY KEY,
178 name TEXT NOT NULL UNIQUE,
179 color TEXT,
180 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
181 )",
182 [],
183 )?;
184
185 // Junction table for many-to-many task-tag relationships
186 tx.execute(
187 "CREATE TABLE IF NOT EXISTS task_tags (
188 task_id INTEGER NOT NULL,
189 tag_id INTEGER NOT NULL,
190 PRIMARY KEY (task_id, tag_id),
191 FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
192 FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
193 )",
194 [],
195 )?;
196 Ok(())
197 });
198
199 // Version 4: Soft delete functionality for data preservation
200 // Enables logical deletion while maintaining data for auditing
201 self.add_migration(4, "add_soft_delete", |tx| {
202 tx.execute("ALTER TABLE tasks ADD COLUMN deleted_at TIMESTAMP", [])?;
203 tx.execute("CREATE INDEX idx_tasks_deleted_at ON tasks(deleted_at)", [])?;
204 Ok(())
205 });
206
207 // Version 5: Workday notes and annotations for context tracking
208 // Allows users to add contextual notes to their workdays
209 self.add_migration(5, "add_workday_notes", |tx| {
210 tx.execute("ALTER TABLE workdays ADD COLUMN notes TEXT", [])?;
211 Ok(())
212 });
213
214 // Version 6: Manual breaks table for productivity management
215 // Enables users to add manual break periods to improve productivity calculations
216 self.add_migration(6, "add_breaks_table", |tx| {
217 tx.execute(
218 "CREATE TABLE IF NOT EXISTS breaks (
219 id INTEGER PRIMARY KEY,
220 date DATE NOT NULL,
221 start_time DATETIME NOT NULL,
222 end_time DATETIME NOT NULL,
223 duration INTEGER NOT NULL,
224 reason TEXT,
225 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
226 )",
227 [],
228 )?;
229
230 // Create index for efficient daily break lookups
231 tx.execute("CREATE INDEX idx_breaks_date ON breaks(date)", [])?;
232 Ok(())
233 });
234
235 // Version 7: Jira inbox for assigned open issues and toast notifications
236 self.add_migration(7, "add_jira_inbox_table", |tx| {
237 tx.execute(
238 "CREATE TABLE IF NOT EXISTS jira_inbox (
239 issue_key TEXT PRIMARY KEY NOT NULL,
240 issue_id TEXT NOT NULL,
241 summary TEXT NOT NULL,
242 status TEXT NOT NULL,
243 priority TEXT,
244 priority_rank INTEGER NOT NULL DEFAULT 999,
245 url TEXT NOT NULL,
246 first_seen TIMESTAMP NOT NULL,
247 last_seen TIMESTAMP NOT NULL,
248 notified INTEGER NOT NULL DEFAULT 0,
249 pinned INTEGER NOT NULL DEFAULT 0,
250 dismissed INTEGER NOT NULL DEFAULT 0,
251 raw_updated TEXT
252 )",
253 [],
254 )?;
255 tx.execute(
256 "CREATE INDEX IF NOT EXISTS idx_jira_inbox_active
257 ON jira_inbox(dismissed, pinned DESC, priority_rank ASC, last_seen DESC)",
258 [],
259 )?;
260 Ok(())
261 });
262
263 // Version 8: status catalog + scoring/sort_value for inbox ranking
264 self.add_migration(8, "jira_inbox_status_id_and_sort_value", |tx| {
265 tx.execute(
266 "CREATE TABLE IF NOT EXISTS jira_statuses (
267 id TEXT PRIMARY KEY NOT NULL,
268 name TEXT NOT NULL
269 )",
270 [],
271 )?;
272 tx.execute("ALTER TABLE jira_inbox ADD COLUMN status_id TEXT", [])?;
273 tx.execute("ALTER TABLE jira_inbox ADD COLUMN sort_value REAL", [])?;
274 tx.execute(
275 "CREATE INDEX IF NOT EXISTS idx_jira_inbox_sort
276 ON jira_inbox(dismissed, pinned DESC, sort_value DESC, priority_rank ASC)",
277 [],
278 )?;
279 Ok(())
280 });
281
282 // Version 9: wipe legacy status name strings; use status_id + jira_statuses only
283 self.add_migration(9, "clear_jira_inbox_legacy_status_text", |tx| {
284 tx.execute("UPDATE jira_inbox SET status = ''", [])?;
285 Ok(())
286 });
287
288 // Version 10: drop unused legacy status text column (canonical: status_id)
289 self.add_migration(10, "drop_jira_inbox_legacy_status_column", |tx| {
290 tx.execute("ALTER TABLE jira_inbox DROP COLUMN status", [])?;
291 Ok(())
292 });
293
294 // Version 11: fold manual breaks into pauses as protected records.
295 //
296 // The separate `breaks` table held synthetic records whose times were
297 // invented by a placement heuristic; downstream code converted them to
298 // pauses anyway. Manual breaks now live in `pauses` with `protected = 1`,
299 // which exempts them from the duration threshold and from merging with
300 // adjacent pauses. Existing break rows are carried over so historical
301 // reports keep their numbers.
302 self.add_migration(11, "fold_breaks_into_protected_pauses", |tx| {
303 tx.execute("ALTER TABLE pauses ADD COLUMN protected INTEGER NOT NULL DEFAULT 0", [])?;
304 tx.execute("ALTER TABLE pauses ADD COLUMN reason TEXT", [])?;
305
306 // Carry over manual breaks; duration is stored in seconds in `pauses`
307 // but was stored in minutes in `breaks`.
308 tx.execute(
309 "INSERT INTO pauses (start, end, duration, protected, reason)
310 SELECT start_time, end_time, duration * 60, 1, reason FROM breaks",
311 [],
312 )?;
313
314 tx.execute("DROP INDEX IF EXISTS idx_breaks_date", [])?;
315 tx.execute("DROP TABLE IF EXISTS breaks", [])?;
316 Ok(())
317 });
318
319 // Version 12: inbox reconciliation and change tracking.
320 //
321 // `gone_at` marks issues that stopped appearing in the Jira poll
322 // (closed, reassigned) so the list stops showing them instead of
323 // freezing on the first sync. `last_change`/`changed_at` record the
324 // most recent visible change (status, priority, score) for badges
325 // and toasts.
326 self.add_migration(12, "jira_inbox_gone_and_change_tracking", |tx| {
327 tx.execute("ALTER TABLE jira_inbox ADD COLUMN gone_at TIMESTAMP", [])?;
328 tx.execute("ALTER TABLE jira_inbox ADD COLUMN last_change TEXT", [])?;
329 tx.execute("ALTER TABLE jira_inbox ADD COLUMN changed_at TIMESTAMP", [])?;
330 Ok(())
331 });
332 }
333
334 /// Registers a single migration in the migration system.
335 ///
336 /// This helper method adds a migration to the internal registry with
337 /// proper version ordering and validation. It ensures that migrations
338 /// are stored in a consistent format for later execution.
339 ///
340 /// # Panics
341 ///
342 /// Panics if a migration with the same version number is already registered.
343 fn add_migration(&mut self, version: u32, name: &'static str, up: fn(&Transaction) -> Result<()>) {
344 self.migrations.push(Migration { version, name, up });
345 }
346
347 /// Executes all pending migrations in the correct order.
348 ///
349 /// # Example
350 ///
351 /// ```rust
352 /// # fn main() -> Result<(), Box<dyn std::error::Error>> {
353 /// use kasl::db::migrations::MigrationManager;
354 /// use rusqlite::Connection;
355 ///
356 /// let manager = MigrationManager::new();
357 /// let mut conn = Connection::open(":memory:")?;
358 /// manager.run_migrations(&mut conn)?;
359 /// # Ok(())
360 /// # }
361 /// ```
362 pub fn run_migrations(&self, conn: &mut Connection) -> Result<()> {
363 // Initialize the migrations tracking table
364 conn.execute(MIGRATIONS_TABLE, [])?;
365
366 // Determine the current schema version
367 let current_version = self.get_current_version(conn)?;
368
369 // Find all migrations that haven't been applied yet
370 let pending: Vec<&Migration> = self.migrations.iter().filter(|m| m.version > current_version).collect();
371
372 // Exit early if no migrations are needed
373 if pending.is_empty() {
374 msg_debug!("Database is up to date");
375 return Ok(());
376 }
377
378 // Notify user about pending migrations
379 msg_info!(Message::MigrationsFound(pending.len()));
380
381 // Execute all pending migrations within a single transaction
382 let tx = conn.transaction()?;
383
384 for migration in pending {
385 msg_info!(Message::RunningMigration(migration.version, migration.name.to_string()));
386
387 match (migration.up)(&tx) {
388 Ok(()) => {
389 // Record successful migration in tracking table
390 tx.execute(
391 "INSERT INTO migrations (version, name) VALUES (?1, ?2)",
392 params![migration.version, migration.name],
393 )?;
394 msg_success!(Message::MigrationCompleted(migration.version));
395 }
396 Err(e) => {
397 // Log migration failure and propagate error
398 msg_error!(Message::MigrationFailed(migration.version, e.to_string()));
399 return Err(e);
400 }
401 }
402 }
403
404 // Commit all successful migrations
405 tx.commit()?;
406 msg_success!(Message::AllMigrationsCompleted);
407
408 Ok(())
409 }
410
411 /// Retrieves the current database schema version.
412 fn get_current_version(&self, conn: &Connection) -> Result<u32> {
413 let version: Option<u32> = conn.query_row("SELECT MAX(version) FROM migrations", [], |row| row.get(0)).unwrap_or(Some(0));
414
415 Ok(version.unwrap_or(0))
416 }
417
418 /// Checks if a specific migration version has been applied.
419 ///
420 /// This utility method allows callers to verify whether a particular
421 /// migration has been successfully applied to the database. Useful
422 /// for conditional logic based on schema capabilities.
423 ///
424 /// # Example
425 ///
426 /// ```rust
427 /// # fn main() -> Result<(), Box<dyn std::error::Error>> {
428 /// use kasl::db::migrations::MigrationManager;
429 /// use rusqlite::Connection;
430 ///
431 /// let manager = MigrationManager::new();
432 /// let mut conn = Connection::open(":memory:")?;
433 /// manager.run_migrations(&mut conn)?;
434 /// if manager.is_migration_applied(&conn, 3)? {
435 /// // Tags system is available
436 /// }
437 /// # Ok(())
438 /// # }
439 /// ```
440 pub fn is_migration_applied(&self, conn: &Connection, version: u32) -> Result<bool> {
441 let count: i32 = conn.query_row("SELECT COUNT(*) FROM migrations WHERE version = ?1", params![version], |row| row.get(0))?;
442
443 Ok(count > 0)
444 }
445
446 /// Retrieves the complete migration history with timestamps.
447 ///
448 /// # Example
449 ///
450 /// ```rust
451 /// # fn main() -> Result<(), Box<dyn std::error::Error>> {
452 /// use kasl::db::migrations::MigrationManager;
453 /// use rusqlite::Connection;
454 ///
455 /// let manager = MigrationManager::new();
456 /// let mut conn = Connection::open(":memory:")?;
457 /// manager.run_migrations(&mut conn)?;
458 /// let history = manager.get_migration_history(&conn)?;
459 /// for (version, name, applied_at) in history {
460 /// println!("v{}: {} ({})", version, name, applied_at);
461 /// }
462 /// # Ok(())
463 /// # }
464 /// ```
465 pub fn get_migration_history(&self, conn: &Connection) -> Result<Vec<(u32, String, String)>> {
466 let mut stmt = conn.prepare("SELECT version, name, applied_at FROM migrations ORDER BY version")?;
467
468 let history = stmt
469 .query_map([], |row| Ok((row.get::<_, u32>(0)?, row.get::<_, String>(1)?, row.get::<_, String>(2)?)))?
470 .collect::<Result<Vec<_>, _>>()?;
471
472 Ok(history)
473 }
474
475 /// Rolls back migrations to a specific target version (debug builds only).
476 ///
477 /// This development utility allows rolling back migrations to a previous
478 /// schema version by removing migration records from the tracking table.
479 ///
480 /// # Example
481 ///
482 /// ```rust
483 /// # fn main() -> Result<(), Box<dyn std::error::Error>> {
484 /// use kasl::db::migrations::MigrationManager;
485 /// use rusqlite::Connection;
486 ///
487 /// #[cfg(debug_assertions)]
488 /// {
489 /// let manager = MigrationManager::new();
490 /// let mut conn = Connection::open(":memory:")?;
491 /// manager.run_migrations(&mut conn)?;
492 /// manager.rollback_to(&mut conn, 2)?; // Roll back to version 2
493 /// }
494 /// # Ok(())
495 /// # }
496 /// ```
497 #[cfg(debug_assertions)]
498 pub fn rollback_to(&self, conn: &mut Connection, target_version: u32) -> Result<()> {
499 let current_version = self.get_current_version(conn)?;
500
501 if target_version >= current_version {
502 msg_info!(Message::NothingToRollback);
503 return Ok(());
504 }
505
506 msg_info!(Message::RollingBack(current_version, target_version));
507
508 // Remove migration records beyond the target version
509 // Note: This is a simplified rollback that doesn't actually reverse schema changes
510 conn.execute("DELETE FROM migrations WHERE version > ?1", params![target_version])?;
511
512 msg_success!(Message::RollbackCompleted(target_version));
513 Ok(())
514 }
515}
516
517/// Initializes a database connection with all pending migrations applied.
518///
519/// This convenience function creates a migration manager and applies all
520/// pending migrations to the provided connection. It's the recommended
521/// way to ensure a database is up to date with the latest schema.
522///
523/// # Example
524///
525/// ```rust,no_run
526/// # fn main() -> Result<(), Box<dyn std::error::Error>> {
527/// use kasl::db::migrations::init_with_migrations;
528/// use rusqlite::Connection;
529///
530/// let mut conn = Connection::open("kasl.db")?;
531/// init_with_migrations(&mut conn)?;
532/// # Ok(())
533/// # }
534/// ```
535pub fn init_with_migrations(conn: &mut Connection) -> Result<()> {
536 let manager = MigrationManager::new();
537 manager.run_migrations(conn)?;
538 Ok(())
539}
540
541/// Retrieves the current database schema version.
542///
543/// This utility function provides a simple way to check the current
544/// schema version without creating a full migration manager instance.
545///
546/// # Example
547///
548/// ```rust
549/// # fn main() -> Result<(), Box<dyn std::error::Error>> {
550/// use kasl::db::migrations::get_db_version;
551/// use rusqlite::Connection;
552///
553/// let conn = Connection::open(":memory:")?;
554/// let version = get_db_version(&conn)?;
555/// println!("Current schema version: {}", version);
556/// # Ok(())
557/// # }
558/// ```
559pub fn get_db_version(conn: &Connection) -> Result<u32> {
560 let manager = MigrationManager::new();
561 manager.get_current_version(conn)
562}
563
564/// Checks if the database requires migration to the latest schema version.
565///
566/// This utility function compares the current database version with the
567/// latest available migration version to determine if updates are needed.
568///
569/// # Example
570///
571/// ```rust
572/// # fn main() -> Result<(), Box<dyn std::error::Error>> {
573/// use kasl::db::migrations::needs_migration;
574/// use rusqlite::Connection;
575///
576/// let conn = Connection::open(":memory:")?;
577/// if needs_migration(&conn)? {
578/// println!("Database needs migration!");
579/// }
580/// # Ok(())
581/// # }
582/// ```
583pub fn needs_migration(conn: &Connection) -> Result<bool> {
584 let manager = MigrationManager::new();
585 let current = manager.get_current_version(conn)?;
586 let latest = manager.migrations.last().map(|m| m.version).unwrap_or(0);
587 Ok(current < latest)
588}