task_graph_mcp/db/
export.rs

1//! Export functionality for the task-graph database.
2//!
3//! Provides methods to serialize database tables for structured export.
4//! Each table is queried with deterministic ordering to produce
5//! stable, diffable output.
6
7/// Tables excluded from export (ephemeral/runtime state).
8///
9/// These tables contain runtime state that should not be version-controlled:
10/// - `workers`: Session-based worker registrations
11/// - `file_locks`: Active file marks (advisory locks)
12/// - `claim_sequence`: File lock audit log (runtime coordination)
13/// - `tasks_fts`: Full-text search virtual table (rebuilt on import)
14/// - `attachments_fts`: Full-text search virtual table (rebuilt on import)
15pub const EPHEMERAL_TABLES: &[&str] = &[
16    "workers",
17    "file_locks",
18    "claim_sequence",
19    "tasks_fts",
20    "attachments_fts",
21];
22
23/// Tables included in export (project data).
24///
25/// These tables contain project data that should be version-controlled:
26pub const PROJECT_TABLES: &[&str] = &[
27    "tasks",
28    "dependencies",
29    "attachments",
30    "task_tags",
31    "task_needed_tags",
32    "task_wanted_tags",
33    "task_sequence",
34];
35
36use crate::types::{
37    Attachment, Dependency, ExportTables, TaskNeededTagRow, TaskSequenceEvent, TaskTagRow,
38    TaskWantedTagRow,
39};
40use anyhow::Result;
41
42use super::Database;
43use super::tasks::parse_task_row;
44
45/// Options for controlling export behavior.
46#[derive(Debug, Clone, Default)]
47pub struct ExportOptions {
48    /// If true, exclude soft-deleted tasks (where deleted_at is set).
49    pub exclude_deleted: bool,
50    /// Optional list of specific tables to export. If None, export all tables.
51    pub tables: Option<Vec<String>>,
52}
53
54impl Database {
55    /// Export all project data tables to an ExportTables struct.
56    ///
57    /// Tables are queried with deterministic ordering per the export spec:
58    /// - tasks: ORDER BY id
59    /// - dependencies: ORDER BY from_task_id, to_task_id, dep_type
60    /// - attachments: ORDER BY task_id, attachment_type, sequence
61    /// - task_tags: ORDER BY task_id, tag
62    /// - task_needed_tags: ORDER BY task_id, tag
63    /// - task_wanted_tags: ORDER BY task_id, tag
64    /// - task_sequence: ORDER BY task_id, id
65    pub fn export_tables(&self, options: &ExportOptions) -> Result<ExportTables> {
66        let tables_to_export = options.tables.as_ref();
67
68        let should_export =
69            |table: &str| -> bool { tables_to_export.is_none_or(|t| t.iter().any(|s| s == table)) };
70
71        let mut export = ExportTables::default();
72
73        if should_export("tasks") {
74            export.tasks = Some(self.export_tasks(options.exclude_deleted)?);
75        }
76
77        if should_export("dependencies") {
78            export.dependencies = Some(self.export_dependencies()?);
79        }
80
81        if should_export("attachments") {
82            export.attachments = Some(self.export_attachments()?);
83        }
84
85        if should_export("task_tags") {
86            export.task_tags = Some(self.export_task_tags()?);
87        }
88
89        if should_export("task_needed_tags") {
90            export.task_needed_tags = Some(self.export_task_needed_tags()?);
91        }
92
93        if should_export("task_wanted_tags") {
94            export.task_wanted_tags = Some(self.export_task_wanted_tags()?);
95        }
96
97        if should_export("task_sequence") {
98            export.task_sequence = Some(self.export_task_sequence()?);
99        }
100
101        Ok(export)
102    }
103
104    /// Export all tasks ordered by id.
105    fn export_tasks(&self, exclude_deleted: bool) -> Result<Vec<crate::types::Task>> {
106        self.with_conn(|conn| {
107            let sql = if exclude_deleted {
108                "SELECT * FROM tasks WHERE deleted_at IS NULL ORDER BY id"
109            } else {
110                "SELECT * FROM tasks ORDER BY id"
111            };
112
113            let mut stmt = conn.prepare(sql)?;
114            let tasks = stmt
115                .query_map([], parse_task_row)?
116                .filter_map(|r| r.ok())
117                .collect();
118            Ok(tasks)
119        })
120    }
121
122    /// Export all dependencies ordered by from_task_id, to_task_id, dep_type.
123    fn export_dependencies(&self) -> Result<Vec<Dependency>> {
124        self.with_conn(|conn| {
125            let mut stmt = conn.prepare(
126                "SELECT from_task_id, to_task_id, dep_type 
127                 FROM dependencies 
128                 ORDER BY from_task_id, to_task_id, dep_type",
129            )?;
130
131            let deps = stmt
132                .query_map([], |row| {
133                    Ok(Dependency {
134                        from_task_id: row.get(0)?,
135                        to_task_id: row.get(1)?,
136                        dep_type: row.get(2)?,
137                    })
138                })?
139                .filter_map(|r| r.ok())
140                .collect();
141
142            Ok(deps)
143        })
144    }
145
146    /// Export all attachments ordered by task_id, attachment_type, sequence.
147    fn export_attachments(&self) -> Result<Vec<Attachment>> {
148        self.with_conn(|conn| {
149            let mut stmt = conn.prepare(
150                "SELECT task_id, attachment_type, sequence, name, mime_type, content, file_path, created_at
151                 FROM attachments
152                 ORDER BY task_id, attachment_type, sequence",
153            )?;
154
155            let attachments = stmt
156                .query_map([], |row| {
157                    Ok(Attachment {
158                        task_id: row.get(0)?,
159                        attachment_type: row.get(1)?,
160                        sequence: row.get(2)?,
161                        name: row.get(3)?,
162                        mime_type: row.get(4)?,
163                        content: row.get(5)?,
164                        file_path: row.get(6)?,
165                        created_at: row.get(7)?,
166                    })
167                })?
168                .filter_map(|r| r.ok())
169                .collect();
170
171            Ok(attachments)
172        })
173    }
174
175    /// Export all task tags ordered by task_id, tag.
176    fn export_task_tags(&self) -> Result<Vec<TaskTagRow>> {
177        self.with_conn(|conn| {
178            let mut stmt =
179                conn.prepare("SELECT task_id, tag FROM task_tags ORDER BY task_id, tag")?;
180
181            let tags = stmt
182                .query_map([], |row| {
183                    Ok(TaskTagRow {
184                        task_id: row.get(0)?,
185                        tag: row.get(1)?,
186                    })
187                })?
188                .filter_map(|r| r.ok())
189                .collect();
190
191            Ok(tags)
192        })
193    }
194
195    /// Export all task needed tags ordered by task_id, tag.
196    fn export_task_needed_tags(&self) -> Result<Vec<TaskNeededTagRow>> {
197        self.with_conn(|conn| {
198            let mut stmt =
199                conn.prepare("SELECT task_id, tag FROM task_needed_tags ORDER BY task_id, tag")?;
200
201            let tags = stmt
202                .query_map([], |row| {
203                    Ok(TaskNeededTagRow {
204                        task_id: row.get(0)?,
205                        tag: row.get(1)?,
206                    })
207                })?
208                .filter_map(|r| r.ok())
209                .collect();
210
211            Ok(tags)
212        })
213    }
214
215    /// Export all task wanted tags ordered by task_id, tag.
216    fn export_task_wanted_tags(&self) -> Result<Vec<TaskWantedTagRow>> {
217        self.with_conn(|conn| {
218            let mut stmt =
219                conn.prepare("SELECT task_id, tag FROM task_wanted_tags ORDER BY task_id, tag")?;
220
221            let tags = stmt
222                .query_map([], |row| {
223                    Ok(TaskWantedTagRow {
224                        task_id: row.get(0)?,
225                        tag: row.get(1)?,
226                    })
227                })?
228                .filter_map(|r| r.ok())
229                .collect();
230
231            Ok(tags)
232        })
233    }
234
235    /// Export all task sequence events ordered by task_id, id.
236    fn export_task_sequence(&self) -> Result<Vec<TaskSequenceEvent>> {
237        self.with_conn(|conn| {
238            let mut stmt = conn.prepare(
239                "SELECT id, task_id, worker_id, status, phase, reason, timestamp, end_timestamp
240                 FROM task_sequence
241                 ORDER BY task_id, id",
242            )?;
243
244            let events = stmt
245                .query_map([], |row| {
246                    Ok(TaskSequenceEvent {
247                        id: row.get(0)?,
248                        task_id: row.get(1)?,
249                        worker_id: row.get(2)?,
250                        status: row.get(3)?,
251                        phase: row.get(4)?,
252                        reason: row.get(5)?,
253                        timestamp: row.get(6)?,
254                        end_timestamp: row.get(7)?,
255                    })
256                })?
257                .filter_map(|r| r.ok())
258                .collect();
259
260            Ok(events)
261        })
262    }
263}
264
265#[cfg(test)]
266mod tests {
267    use super::*;
268    use crate::config::{DependenciesConfig, IdsConfig, StatesConfig};
269
270    fn default_states_config() -> StatesConfig {
271        StatesConfig::default()
272    }
273
274    fn default_deps_config() -> DependenciesConfig {
275        DependenciesConfig::default()
276    }
277
278    #[test]
279    fn test_export_empty_database() {
280        let db = Database::open_in_memory().unwrap();
281        let options = ExportOptions::default();
282        let export = db.export_tables(&options).unwrap();
283
284        assert!(export.tasks.as_ref().unwrap().is_empty());
285        assert!(export.dependencies.as_ref().unwrap().is_empty());
286        assert!(export.attachments.as_ref().unwrap().is_empty());
287        assert!(export.task_tags.as_ref().unwrap().is_empty());
288        assert!(export.task_needed_tags.as_ref().unwrap().is_empty());
289        assert!(export.task_wanted_tags.as_ref().unwrap().is_empty());
290        assert!(export.task_sequence.as_ref().unwrap().is_empty());
291    }
292
293    #[test]
294    fn test_export_selective_tables() {
295        let db = Database::open_in_memory().unwrap();
296        let options = ExportOptions {
297            exclude_deleted: false,
298            tables: Some(vec!["tasks".to_string(), "dependencies".to_string()]),
299        };
300        let export = db.export_tables(&options).unwrap();
301
302        // Selected tables should be Some
303        assert!(export.tasks.is_some());
304        assert!(export.dependencies.is_some());
305
306        // Non-selected tables should be None
307        assert!(export.attachments.is_none());
308        assert!(export.task_tags.is_none());
309        assert!(export.task_needed_tags.is_none());
310        assert!(export.task_wanted_tags.is_none());
311        assert!(export.task_sequence.is_none());
312    }
313
314    #[test]
315    fn test_export_tasks_ordered_by_id() {
316        let db = Database::open_in_memory().unwrap();
317        let states_config = default_states_config();
318
319        // Create tasks with IDs that would be out of order alphabetically if not sorted
320        db.create_task(
321            Some("z-task".to_string()),
322            "Z Task".to_string(),
323            None,
324            None, // phase
325            None,
326            None,
327            None,
328            None,
329            None,
330            None,
331            &states_config,
332            &IdsConfig::default(),
333        )
334        .unwrap();
335        db.create_task(
336            Some("a-task".to_string()),
337            "A Task".to_string(),
338            None,
339            None, // phase
340            None,
341            None,
342            None,
343            None,
344            None,
345            None,
346            &states_config,
347            &IdsConfig::default(),
348        )
349        .unwrap();
350        db.create_task(
351            Some("m-task".to_string()),
352            "M Task".to_string(),
353            None,
354            None, // phase
355            None,
356            None,
357            None,
358            None,
359            None,
360            None,
361            &states_config,
362            &IdsConfig::default(),
363        )
364        .unwrap();
365
366        let options = ExportOptions::default();
367        let export = db.export_tables(&options).unwrap();
368        let tasks = export.tasks.unwrap();
369
370        assert_eq!(tasks.len(), 3);
371        assert_eq!(tasks[0].id, "a-task");
372        assert_eq!(tasks[1].id, "m-task");
373        assert_eq!(tasks[2].id, "z-task");
374    }
375
376    #[test]
377    fn test_export_excludes_deleted_tasks_when_requested() {
378        let db = Database::open_in_memory().unwrap();
379        let states_config = default_states_config();
380
381        // Create a normal task
382        db.create_task(
383            Some("task-1".to_string()),
384            "Task 1".to_string(),
385            None,
386            None, // phase
387            None,
388            None,
389            None,
390            None,
391            None,
392            None,
393            &states_config,
394            &IdsConfig::default(),
395        )
396        .unwrap();
397
398        // Create and delete a task
399        db.create_task(
400            Some("task-2".to_string()),
401            "Task 2".to_string(),
402            None,
403            None, // phase
404            None,
405            None,
406            None,
407            None,
408            None,
409            None,
410            &states_config,
411            &IdsConfig::default(),
412        )
413        .unwrap();
414        // Soft delete: task_id, worker_id, cascade, reason, obliterate, force
415        db.delete_task("task-2", "test-worker", false, None, false, true)
416            .unwrap();
417
418        // Export without excluding deleted
419        let options = ExportOptions {
420            exclude_deleted: false,
421            tables: None,
422        };
423        let export = db.export_tables(&options).unwrap();
424        assert_eq!(export.tasks.as_ref().unwrap().len(), 2);
425
426        // Export with excluding deleted
427        let options = ExportOptions {
428            exclude_deleted: true,
429            tables: None,
430        };
431        let export = db.export_tables(&options).unwrap();
432        assert_eq!(export.tasks.as_ref().unwrap().len(), 1);
433        assert_eq!(export.tasks.as_ref().unwrap()[0].id, "task-1");
434    }
435
436    #[test]
437    fn test_export_dependencies_ordered() {
438        let db = Database::open_in_memory().unwrap();
439        let states_config = default_states_config();
440        let deps_config = default_deps_config();
441
442        // Create tasks first
443        for id in ["a", "b", "c"] {
444            db.create_task(
445                Some(id.to_string()),
446                format!("Task {}", id),
447                None,
448                None, // phase
449                None,
450                None,
451                None,
452                None,
453                None,
454                None,
455                &states_config,
456                &IdsConfig::default(),
457            )
458            .unwrap();
459        }
460
461        // Add dependencies in non-sorted order
462        db.add_dependency("c", "a", "blocks", &deps_config).unwrap();
463        db.add_dependency("a", "b", "follows", &deps_config)
464            .unwrap();
465        db.add_dependency("a", "b", "blocks", &deps_config).unwrap();
466
467        let options = ExportOptions::default();
468        let export = db.export_tables(&options).unwrap();
469        let deps = export.dependencies.unwrap();
470
471        assert_eq!(deps.len(), 3);
472        // Should be ordered by from_task_id, to_task_id, dep_type
473        assert_eq!(
474            (
475                deps[0].from_task_id.as_str(),
476                deps[0].to_task_id.as_str(),
477                deps[0].dep_type.as_str()
478            ),
479            ("a", "b", "blocks")
480        );
481        assert_eq!(
482            (
483                deps[1].from_task_id.as_str(),
484                deps[1].to_task_id.as_str(),
485                deps[1].dep_type.as_str()
486            ),
487            ("a", "b", "follows")
488        );
489        assert_eq!(
490            (
491                deps[2].from_task_id.as_str(),
492                deps[2].to_task_id.as_str(),
493                deps[2].dep_type.as_str()
494            ),
495            ("c", "a", "blocks")
496        );
497    }
498
499    #[test]
500    fn test_export_task_tags_ordered() {
501        let db = Database::open_in_memory().unwrap();
502        let states_config = default_states_config();
503
504        // Create tasks with tags in various orders
505        db.create_task(
506            Some("task-b".to_string()),
507            "Task B".to_string(),
508            None,
509            None,
510            None,
511            None,
512            None,
513            None,
514            None,                                                 // wanted_tags
515            Some(vec!["zebra".to_string(), "apple".to_string()]), // tags
516            &states_config,
517            &IdsConfig::default(),
518        )
519        .unwrap();
520        db.create_task(
521            Some("task-a".to_string()),
522            "Task A".to_string(),
523            None,
524            None,
525            None,
526            None,
527            None,
528            None,
529            None,                            // wanted_tags
530            Some(vec!["mango".to_string()]), // tags
531            &states_config,
532            &IdsConfig::default(),
533        )
534        .unwrap();
535
536        let options = ExportOptions::default();
537        let export = db.export_tables(&options).unwrap();
538        let tags = export.task_tags.unwrap();
539
540        assert_eq!(tags.len(), 3);
541        // Should be ordered by task_id, then tag
542        assert_eq!(
543            (tags[0].task_id.as_str(), tags[0].tag.as_str()),
544            ("task-a", "mango")
545        );
546        assert_eq!(
547            (tags[1].task_id.as_str(), tags[1].tag.as_str()),
548            ("task-b", "apple")
549        );
550        assert_eq!(
551            (tags[2].task_id.as_str(), tags[2].tag.as_str()),
552            ("task-b", "zebra")
553        );
554    }
555}
task_graph_mcp/db/export.rs

task_graph_mcp/db/
export.rs
