task_graph_mcp/db/
search.rs

1//! Full-text search operations using FTS5.
2
3use super::Database;
4use anyhow::Result;
5use rusqlite::params;
6use serde::{Deserialize, Serialize};
7
8/// A search result from full-text search.
9#[derive(Debug, Clone, Serialize, Deserialize)]
10pub struct SearchResult {
11    /// Task ID
12    pub task_id: String,
13    /// Task title
14    pub title: String,
15    /// Task description
16    pub description: Option<String>,
17    /// Task status
18    pub status: String,
19    /// BM25 relevance score (lower is more relevant)
20    pub score: f64,
21    /// Highlighted snippet from title
22    pub title_snippet: String,
23    /// Highlighted snippet from description
24    pub description_snippet: Option<String>,
25    /// Attachment matches if include_attachments is true
26    #[serde(skip_serializing_if = "Vec::is_empty")]
27    pub attachment_matches: Vec<AttachmentMatch>,
28}
29
30/// A matching attachment from full-text search.
31#[derive(Debug, Clone, Serialize, Deserialize)]
32pub struct AttachmentMatch {
33    /// Attachment type/category
34    pub attachment_type: String,
35    /// Sequence within type
36    pub sequence: i32,
37    /// Attachment name/label
38    pub name: String,
39    /// Highlighted content snippet
40    pub content_snippet: String,
41}
42
43impl Database {
44    /// Search tasks using FTS5 full-text search.
45    ///
46    /// The query supports FTS5 MATCH syntax:
47    /// - Simple words: `error handling`
48    /// - Phrases: `"error handling"`
49    /// - Prefix: `error*`
50    /// - Boolean: `error AND NOT warning`
51    /// - Column-specific: `title:error` or `description:handling`
52    ///
53    /// Results are ranked by BM25 relevance score.
54    /// Search tasks with pagination support.
55    ///
56    /// Accepts limit and offset for pagination. The offset parameter skips
57    /// the first N results.
58    pub fn search_tasks(
59        &self,
60        query: &str,
61        limit: Option<i32>,
62        offset: i32,
63        include_attachments: bool,
64        status_filter: Option<&str>,
65    ) -> Result<Vec<SearchResult>> {
66        let limit = limit.unwrap_or(20).min(100);
67
68        self.with_conn(|conn| {
69            // First, search tasks_fts
70            let mut sql = String::from(
71                "SELECT
72                    fts.task_id,
73                    t.title,
74                    t.description,
75                    t.status,
76                    bm25(tasks_fts) as score,
77                    snippet(tasks_fts, 1, '<mark>', '</mark>', '...', 32) as title_snippet,
78                    snippet(tasks_fts, 2, '<mark>', '</mark>', '...', 64) as description_snippet
79                FROM tasks_fts fts
80                INNER JOIN tasks t ON fts.task_id = t.id
81                WHERE tasks_fts MATCH ?1",
82            );
83
84            let mut params_vec: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
85            params_vec.push(Box::new(query.to_string()));
86
87            if let Some(status) = status_filter {
88                sql.push_str(" AND t.status = ?2");
89                params_vec.push(Box::new(status.to_string()));
90            }
91
92            sql.push_str(" ORDER BY score LIMIT ?");
93            params_vec.push(Box::new(limit));
94
95            if offset > 0 {
96                sql.push_str(" OFFSET ?");
97                params_vec.push(Box::new(offset));
98            }
99
100            let params_refs: Vec<&dyn rusqlite::ToSql> =
101                params_vec.iter().map(|b| b.as_ref()).collect();
102
103            let mut stmt = conn.prepare(&sql)?;
104            let mut results: Vec<SearchResult> = stmt
105                .query_map(params_refs.as_slice(), |row| {
106                    Ok(SearchResult {
107                        task_id: row.get(0)?,
108                        title: row.get(1)?,
109                        description: row.get(2)?,
110                        status: row.get(3)?,
111                        score: row.get(4)?,
112                        title_snippet: row.get(5)?,
113                        description_snippet: row.get(6)?,
114                        attachment_matches: Vec::new(),
115                    })
116                })?
117                .filter_map(|r| r.ok())
118                .collect();
119
120            // If include_attachments, also search attachments_fts
121            if include_attachments {
122                // Search attachments
123                let attachment_sql = "SELECT
124                    afts.task_id,
125                    afts.attachment_type,
126                    afts.sequence,
127                    afts.name,
128                    snippet(attachments_fts, 4, '<mark>', '</mark>', '...', 64) as content_snippet
129                FROM attachments_fts afts
130                WHERE attachments_fts MATCH ?1
131                ORDER BY bm25(attachments_fts)
132                LIMIT ?2";
133
134                let mut att_stmt = conn.prepare(attachment_sql)?;
135                let att_matches: Vec<(String, String, i32, String, String)> = att_stmt
136                    .query_map(params![query, limit * 3], |row| {
137                        Ok((
138                            row.get::<_, String>(0)?,
139                            row.get::<_, String>(1)?,
140                            row.get::<_, i32>(2)?,
141                            row.get::<_, String>(3)?,
142                            row.get::<_, String>(4)?,
143                        ))
144                    })?
145                    .filter_map(|r| r.ok())
146                    .collect();
147
148                // Group attachment matches by task_id and merge with task results
149                for (task_id, attachment_type, sequence, name, content_snippet) in att_matches {
150                    // Check if task already in results
151                    if let Some(result) = results.iter_mut().find(|r| r.task_id == task_id) {
152                        result.attachment_matches.push(AttachmentMatch {
153                            attachment_type,
154                            sequence,
155                            name,
156                            content_snippet,
157                        });
158                    } else {
159                        // Add task to results if not already present (attachment-only match)
160                        // Apply status filter if needed
161                        let task_sql = if status_filter.is_some() {
162                            "SELECT id, title, description, status FROM tasks WHERE id = ?1 AND status = ?2"
163                        } else {
164                            "SELECT id, title, description, status FROM tasks WHERE id = ?1"
165                        };
166
167                        let task_result: Option<(String, String, Option<String>, String)> =
168                            if let Some(status) = status_filter {
169                                conn.query_row(task_sql, params![&task_id, status], |row| {
170                                    Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?))
171                                })
172                                .ok()
173                            } else {
174                                conn.query_row(task_sql, params![&task_id], |row| {
175                                    Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?))
176                                })
177                                .ok()
178                            };
179
180                        if let Some((id, title, description, status)) = task_result {
181                            results.push(SearchResult {
182                                task_id: id.clone(),
183                                title: title.clone(),
184                                description: description.clone(),
185                                status,
186                                score: 999.0, // Attachment-only matches get lower priority
187                                title_snippet: title,
188                                description_snippet: description,
189                                attachment_matches: vec![AttachmentMatch {
190                                    attachment_type,
191                                    sequence,
192                                    name,
193                                    content_snippet,
194                                }],
195                            });
196                        }
197                    }
198                }
199            }
200
201            // Sort by score and apply limit
202            results.sort_by(|a, b| a.score.partial_cmp(&b.score).unwrap());
203            results.truncate(limit as usize);
204
205            Ok(results)
206        })
207    }
208}
209
210#[cfg(test)]
211mod tests {
212    use super::*;
213    use crate::config::{IdsConfig, StatesConfig};
214
215    fn states() -> StatesConfig {
216        StatesConfig::default()
217    }
218
219    #[test]
220    fn test_search_empty_db() {
221        let db = Database::open_in_memory().unwrap();
222        let results = db.search_tasks("test", None, 0, false, None).unwrap();
223        assert!(results.is_empty());
224    }
225
226    #[test]
227    fn test_fts_insert_trigger_indexes_new_tasks() {
228        let db = Database::open_in_memory().unwrap();
229
230        // Create a task - trigger should automatically add to FTS
231        let task = db
232            .create_task(
233                None,
234                "Test FTS indexing with keywords".to_string(),
235                None,
236                None, // phase
237                None,
238                None,
239                None,
240                None,
241                None,
242                None,
243                &states(),
244                &IdsConfig::default(),
245            )
246            .unwrap();
247
248        // Search should find it immediately
249        let results = db.search_tasks("indexing", None, 0, false, None).unwrap();
250        assert_eq!(results.len(), 1);
251        assert_eq!(results[0].task_id, task.id);
252    }
253
254    #[test]
255    fn test_fts_update_trigger_reindexes_modified_tasks() {
256        let db = Database::open_in_memory().unwrap();
257
258        // Create a task with initial content
259        let task = db
260            .create_task(
261                None,
262                "Original title original".to_string(),
263                None,
264                None, // phase
265                None,
266                None,
267                None,
268                None,
269                None,
270                None,
271                &states(),
272                &IdsConfig::default(),
273            )
274            .unwrap();
275
276        // Verify initial content is indexed
277        let results = db.search_tasks("Original", None, 0, false, None).unwrap();
278        assert_eq!(results.len(), 1);
279
280        // Update the task - trigger should reindex
281        db.update_task(
282            &task.id,
283            Some("Updated title with newkeyword".to_string()),
284            Some(Some("Updated description".to_string())),
285            None,
286            None,
287            None,
288            None,
289            &states(),
290        )
291        .unwrap();
292
293        // Search should find new content
294        let results = db.search_tasks("newkeyword", None, 0, false, None).unwrap();
295        assert_eq!(results.len(), 1);
296        assert_eq!(results[0].task_id, task.id);
297
298        // Verify updated title is searchable
299        let results = db.search_tasks("Updated", None, 0, false, None).unwrap();
300        assert_eq!(results.len(), 1);
301    }
302
303    #[test]
304    fn test_fts_delete_trigger_removes_from_index() {
305        let db = Database::open_in_memory().unwrap();
306
307        // Create a task
308        let task = db
309            .create_task(
310                None,
311                "Deletable task content".to_string(),
312                None,
313                None, // phase
314                None,
315                None,
316                None,
317                None,
318                None,
319                None,
320                &states(),
321                &IdsConfig::default(),
322            )
323            .unwrap();
324
325        // Verify it's indexed
326        let results = db.search_tasks("Deletable", None, 0, false, None).unwrap();
327        assert_eq!(results.len(), 1);
328
329        // Delete the task
330        db.delete_task(&task.id, "test-worker", false, None, true, true)
331            .unwrap();
332
333        // Search should find nothing
334        let results = db.search_tasks("Deletable", None, 0, false, None).unwrap();
335        assert!(results.is_empty());
336    }
337
338    #[test]
339    fn test_fts_search_with_bm25_ranking() {
340        let db = Database::open_in_memory().unwrap();
341
342        // Create tasks with varying relevance
343        db.create_task(
344            None,
345            "Bug fix for minor bug".to_string(),
346            None,
347            None, // phase
348            None,
349            None,
350            None,
351            None,
352            None,
353            None,
354            &states(),
355            &IdsConfig::default(),
356        )
357        .unwrap();
358        db.create_task(
359            None,
360            "Bug bug bug multiple bugs".to_string(),
361            None,
362            None, // phase
363            None,
364            None,
365            None,
366            None,
367            None,
368            None,
369            &states(),
370            &IdsConfig::default(),
371        )
372        .unwrap();
373        db.create_task(
374            None,
375            "Feature implementation".to_string(),
376            None,
377            None, // phase
378            None,
379            None,
380            None,
381            None,
382            None,
383            None,
384            &states(),
385            &IdsConfig::default(),
386        )
387        .unwrap();
388
389        // Search for "bug" - higher frequency should rank better
390        let results = db.search_tasks("bug", None, 0, false, None).unwrap();
391        assert_eq!(results.len(), 2);
392        // The task with more "bug" occurrences should have a better (lower) score
393        assert!(results[0].score <= results[1].score);
394    }
395
396    #[test]
397    fn test_fts_attachment_trigger_indexes_text_content() {
398        let db = Database::open_in_memory().unwrap();
399
400        // Create a task
401        let task = db
402            .create_task(
403                None,
404                "Task with attachment".to_string(),
405                None,
406                None, // phase
407                None,
408                None,
409                None,
410                None,
411                None,
412                None,
413                &states(),
414                &IdsConfig::default(),
415            )
416            .unwrap();
417
418        // Add a text attachment
419        db.add_attachment(
420            &task.id,
421            "notes".to_string(),
422            String::new(),
423            "Important searchable content here".to_string(),
424            Some("text/plain".to_string()),
425            None,
426        )
427        .unwrap();
428
429        // Search with include_attachments should find it
430        let results = db.search_tasks("searchable", None, 0, true, None).unwrap();
431        assert_eq!(results.len(), 1);
432        assert_eq!(results[0].task_id, task.id);
433        assert_eq!(results[0].attachment_matches.len(), 1);
434        assert_eq!(results[0].attachment_matches[0].attachment_type, "notes");
435    }
436}
task_graph_mcp/db/search.rs

task_graph_mcp/db/
search.rs
