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

task_graph_mcp/db/
search.rs
