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

task_graph_mcp/db/
search.rs
