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,
325            None, // phase
326            None,
327            None,
328            None,
329            None,
330            None,
331            None,
332            &states_config,
333            &IdsConfig::default(),
334        )
335        .unwrap();
336        db.create_task(
337            Some("a-task".to_string()),
338            "A Task".to_string(),
339            None,
340            None,
341            None, // phase
342            None,
343            None,
344            None,
345            None,
346            None,
347            None,
348            &states_config,
349            &IdsConfig::default(),
350        )
351        .unwrap();
352        db.create_task(
353            Some("m-task".to_string()),
354            "M Task".to_string(),
355            None,
356            None,
357            None, // phase
358            None,
359            None,
360            None,
361            None,
362            None,
363            None,
364            &states_config,
365            &IdsConfig::default(),
366        )
367        .unwrap();
368
369        let options = ExportOptions::default();
370        let export = db.export_tables(&options).unwrap();
371        let tasks = export.tasks.unwrap();
372
373        assert_eq!(tasks.len(), 3);
374        assert_eq!(tasks[0].id, "a-task");
375        assert_eq!(tasks[1].id, "m-task");
376        assert_eq!(tasks[2].id, "z-task");
377    }
378
379    #[test]
380    fn test_export_excludes_deleted_tasks_when_requested() {
381        let db = Database::open_in_memory().unwrap();
382        let states_config = default_states_config();
383
384        // Create a normal task
385        db.create_task(
386            Some("task-1".to_string()),
387            "Task 1".to_string(),
388            None,
389            None,
390            None, // phase
391            None,
392            None,
393            None,
394            None,
395            None,
396            None,
397            &states_config,
398            &IdsConfig::default(),
399        )
400        .unwrap();
401
402        // Create and delete a task
403        db.create_task(
404            Some("task-2".to_string()),
405            "Task 2".to_string(),
406            None,
407            None,
408            None, // phase
409            None,
410            None,
411            None,
412            None,
413            None,
414            None,
415            &states_config,
416            &IdsConfig::default(),
417        )
418        .unwrap();
419        // Soft delete: task_id, worker_id, cascade, reason, obliterate, force
420        db.delete_task("task-2", "test-worker", false, None, false, true)
421            .unwrap();
422
423        // Export without excluding deleted
424        let options = ExportOptions {
425            exclude_deleted: false,
426            tables: None,
427        };
428        let export = db.export_tables(&options).unwrap();
429        assert_eq!(export.tasks.as_ref().unwrap().len(), 2);
430
431        // Export with excluding deleted
432        let options = ExportOptions {
433            exclude_deleted: true,
434            tables: None,
435        };
436        let export = db.export_tables(&options).unwrap();
437        assert_eq!(export.tasks.as_ref().unwrap().len(), 1);
438        assert_eq!(export.tasks.as_ref().unwrap()[0].id, "task-1");
439    }
440
441    #[test]
442    fn test_export_dependencies_ordered() {
443        let db = Database::open_in_memory().unwrap();
444        let states_config = default_states_config();
445        let deps_config = default_deps_config();
446
447        // Create tasks first
448        for id in ["a", "b", "c"] {
449            db.create_task(
450                Some(id.to_string()),
451                format!("Task {}", id),
452                None,
453                None,
454                None, // phase
455                None,
456                None,
457                None,
458                None,
459                None,
460                None,
461                &states_config,
462                &IdsConfig::default(),
463            )
464            .unwrap();
465        }
466
467        // Add dependencies in non-sorted order
468        db.add_dependency("c", "a", "blocks", &deps_config).unwrap();
469        db.add_dependency("a", "b", "follows", &deps_config)
470            .unwrap();
471        db.add_dependency("a", "b", "blocks", &deps_config).unwrap();
472
473        let options = ExportOptions::default();
474        let export = db.export_tables(&options).unwrap();
475        let deps = export.dependencies.unwrap();
476
477        assert_eq!(deps.len(), 3);
478        // Should be ordered by from_task_id, to_task_id, dep_type
479        assert_eq!(
480            (
481                deps[0].from_task_id.as_str(),
482                deps[0].to_task_id.as_str(),
483                deps[0].dep_type.as_str()
484            ),
485            ("a", "b", "blocks")
486        );
487        assert_eq!(
488            (
489                deps[1].from_task_id.as_str(),
490                deps[1].to_task_id.as_str(),
491                deps[1].dep_type.as_str()
492            ),
493            ("a", "b", "follows")
494        );
495        assert_eq!(
496            (
497                deps[2].from_task_id.as_str(),
498                deps[2].to_task_id.as_str(),
499                deps[2].dep_type.as_str()
500            ),
501            ("c", "a", "blocks")
502        );
503    }
504
505    #[test]
506    fn test_export_task_tags_ordered() {
507        let db = Database::open_in_memory().unwrap();
508        let states_config = default_states_config();
509
510        // Create tasks with tags in various orders
511        db.create_task(
512            Some("task-b".to_string()),
513            "Task B".to_string(),
514            None,
515            None,
516            None,
517            None,
518            None,
519            None,
520            None,
521            None,                                                 // wanted_tags
522            Some(vec!["zebra".to_string(), "apple".to_string()]), // tags
523            &states_config,
524            &IdsConfig::default(),
525        )
526        .unwrap();
527        db.create_task(
528            Some("task-a".to_string()),
529            "Task A".to_string(),
530            None,
531            None,
532            None,
533            None,
534            None,
535            None,
536            None,
537            None,                            // wanted_tags
538            Some(vec!["mango".to_string()]), // tags
539            &states_config,
540            &IdsConfig::default(),
541        )
542        .unwrap();
543
544        let options = ExportOptions::default();
545        let export = db.export_tables(&options).unwrap();
546        let tags = export.task_tags.unwrap();
547
548        assert_eq!(tags.len(), 3);
549        // Should be ordered by task_id, then tag
550        assert_eq!(
551            (tags[0].task_id.as_str(), tags[0].tag.as_str()),
552            ("task-a", "mango")
553        );
554        assert_eq!(
555            (tags[1].task_id.as_str(), tags[1].tag.as_str()),
556            ("task-b", "apple")
557        );
558        assert_eq!(
559            (tags[2].task_id.as_str(), tags[2].tag.as_str()),
560            ("task-b", "zebra")
561        );
562    }
563}
task_graph_mcp/db/export.rs

task_graph_mcp/db/
export.rs
