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, concurrency
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                        concurrency: row.get(8)?,
256                    })
257                })?
258                .filter_map(|r| r.ok())
259                .collect();
260
261            Ok(events)
262        })
263    }
264}
265
266#[cfg(test)]
267mod tests {
268    use super::*;
269    use crate::config::{DependenciesConfig, IdsConfig, StatesConfig};
270
271    fn default_states_config() -> StatesConfig {
272        StatesConfig::default()
273    }
274
275    fn default_deps_config() -> DependenciesConfig {
276        DependenciesConfig::default()
277    }
278
279    #[test]
280    fn test_export_empty_database() {
281        let db = Database::open_in_memory().unwrap();
282        let options = ExportOptions::default();
283        let export = db.export_tables(&options).unwrap();
284
285        assert!(export.tasks.as_ref().unwrap().is_empty());
286        assert!(export.dependencies.as_ref().unwrap().is_empty());
287        assert!(export.attachments.as_ref().unwrap().is_empty());
288        assert!(export.task_tags.as_ref().unwrap().is_empty());
289        assert!(export.task_needed_tags.as_ref().unwrap().is_empty());
290        assert!(export.task_wanted_tags.as_ref().unwrap().is_empty());
291        assert!(export.task_sequence.as_ref().unwrap().is_empty());
292    }
293
294    #[test]
295    fn test_export_selective_tables() {
296        let db = Database::open_in_memory().unwrap();
297        let options = ExportOptions {
298            exclude_deleted: false,
299            tables: Some(vec!["tasks".to_string(), "dependencies".to_string()]),
300        };
301        let export = db.export_tables(&options).unwrap();
302
303        // Selected tables should be Some
304        assert!(export.tasks.is_some());
305        assert!(export.dependencies.is_some());
306
307        // Non-selected tables should be None
308        assert!(export.attachments.is_none());
309        assert!(export.task_tags.is_none());
310        assert!(export.task_needed_tags.is_none());
311        assert!(export.task_wanted_tags.is_none());
312        assert!(export.task_sequence.is_none());
313    }
314
315    #[test]
316    fn test_export_tasks_ordered_by_id() {
317        let db = Database::open_in_memory().unwrap();
318        let states_config = default_states_config();
319
320        // Create tasks with IDs that would be out of order alphabetically if not sorted
321        db.create_task(
322            Some("z-task".to_string()),
323            "Z Task".to_string(),
324            None,
325            None,
326            None, // phase
327            None,
328            None,
329            None,
330            None,
331            None,
332            None,
333            &states_config,
334            &IdsConfig::default(),
335        )
336        .unwrap();
337        db.create_task(
338            Some("a-task".to_string()),
339            "A Task".to_string(),
340            None,
341            None,
342            None, // phase
343            None,
344            None,
345            None,
346            None,
347            None,
348            None,
349            &states_config,
350            &IdsConfig::default(),
351        )
352        .unwrap();
353        db.create_task(
354            Some("m-task".to_string()),
355            "M Task".to_string(),
356            None,
357            None,
358            None, // phase
359            None,
360            None,
361            None,
362            None,
363            None,
364            None,
365            &states_config,
366            &IdsConfig::default(),
367        )
368        .unwrap();
369
370        let options = ExportOptions::default();
371        let export = db.export_tables(&options).unwrap();
372        let tasks = export.tasks.unwrap();
373
374        assert_eq!(tasks.len(), 3);
375        assert_eq!(tasks[0].id, "a-task");
376        assert_eq!(tasks[1].id, "m-task");
377        assert_eq!(tasks[2].id, "z-task");
378    }
379
380    #[test]
381    fn test_export_excludes_deleted_tasks_when_requested() {
382        let db = Database::open_in_memory().unwrap();
383        let states_config = default_states_config();
384
385        // Create a normal task
386        db.create_task(
387            Some("task-1".to_string()),
388            "Task 1".to_string(),
389            None,
390            None,
391            None, // phase
392            None,
393            None,
394            None,
395            None,
396            None,
397            None,
398            &states_config,
399            &IdsConfig::default(),
400        )
401        .unwrap();
402
403        // Create and delete a task
404        db.create_task(
405            Some("task-2".to_string()),
406            "Task 2".to_string(),
407            None,
408            None,
409            None, // phase
410            None,
411            None,
412            None,
413            None,
414            None,
415            None,
416            &states_config,
417            &IdsConfig::default(),
418        )
419        .unwrap();
420        // Soft delete: task_id, worker_id, cascade, reason, obliterate, force
421        db.delete_task("task-2", "test-worker", false, None, false, true)
422            .unwrap();
423
424        // Export without excluding deleted
425        let options = ExportOptions {
426            exclude_deleted: false,
427            tables: None,
428        };
429        let export = db.export_tables(&options).unwrap();
430        assert_eq!(export.tasks.as_ref().unwrap().len(), 2);
431
432        // Export with excluding deleted
433        let options = ExportOptions {
434            exclude_deleted: true,
435            tables: None,
436        };
437        let export = db.export_tables(&options).unwrap();
438        assert_eq!(export.tasks.as_ref().unwrap().len(), 1);
439        assert_eq!(export.tasks.as_ref().unwrap()[0].id, "task-1");
440    }
441
442    #[test]
443    fn test_export_dependencies_ordered() {
444        let db = Database::open_in_memory().unwrap();
445        let states_config = default_states_config();
446        let deps_config = default_deps_config();
447
448        // Create tasks first
449        for id in ["a", "b", "c"] {
450            db.create_task(
451                Some(id.to_string()),
452                format!("Task {}", id),
453                None,
454                None,
455                None, // phase
456                None,
457                None,
458                None,
459                None,
460                None,
461                None,
462                &states_config,
463                &IdsConfig::default(),
464            )
465            .unwrap();
466        }
467
468        // Add dependencies in non-sorted order
469        db.add_dependency("c", "a", "blocks", &deps_config).unwrap();
470        db.add_dependency("a", "b", "follows", &deps_config)
471            .unwrap();
472        db.add_dependency("a", "b", "blocks", &deps_config).unwrap();
473
474        let options = ExportOptions::default();
475        let export = db.export_tables(&options).unwrap();
476        let deps = export.dependencies.unwrap();
477
478        assert_eq!(deps.len(), 3);
479        // Should be ordered by from_task_id, to_task_id, dep_type
480        assert_eq!(
481            (
482                deps[0].from_task_id.as_str(),
483                deps[0].to_task_id.as_str(),
484                deps[0].dep_type.as_str()
485            ),
486            ("a", "b", "blocks")
487        );
488        assert_eq!(
489            (
490                deps[1].from_task_id.as_str(),
491                deps[1].to_task_id.as_str(),
492                deps[1].dep_type.as_str()
493            ),
494            ("a", "b", "follows")
495        );
496        assert_eq!(
497            (
498                deps[2].from_task_id.as_str(),
499                deps[2].to_task_id.as_str(),
500                deps[2].dep_type.as_str()
501            ),
502            ("c", "a", "blocks")
503        );
504    }
505
506    #[test]
507    fn test_export_task_tags_ordered() {
508        let db = Database::open_in_memory().unwrap();
509        let states_config = default_states_config();
510
511        // Create tasks with tags in various orders
512        db.create_task(
513            Some("task-b".to_string()),
514            "Task B".to_string(),
515            None,
516            None,
517            None,
518            None,
519            None,
520            None,
521            None,
522            None,                                                 // wanted_tags
523            Some(vec!["zebra".to_string(), "apple".to_string()]), // tags
524            &states_config,
525            &IdsConfig::default(),
526        )
527        .unwrap();
528        db.create_task(
529            Some("task-a".to_string()),
530            "Task A".to_string(),
531            None,
532            None,
533            None,
534            None,
535            None,
536            None,
537            None,
538            None,                            // wanted_tags
539            Some(vec!["mango".to_string()]), // tags
540            &states_config,
541            &IdsConfig::default(),
542        )
543        .unwrap();
544
545        let options = ExportOptions::default();
546        let export = db.export_tables(&options).unwrap();
547        let tags = export.task_tags.unwrap();
548
549        assert_eq!(tags.len(), 3);
550        // Should be ordered by task_id, then tag
551        assert_eq!(
552            (tags[0].task_id.as_str(), tags[0].tag.as_str()),
553            ("task-a", "mango")
554        );
555        assert_eq!(
556            (tags[1].task_id.as_str(), tags[1].tag.as_str()),
557            ("task-b", "apple")
558        );
559        assert_eq!(
560            (tags[2].task_id.as_str(), tags[2].tag.as_str()),
561            ("task-b", "zebra")
562        );
563    }
564}
task_graph_mcp/db/export.rs

task_graph_mcp/db/
export.rs
