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,
220                None,
221                None,
222                None,
223                None,
224                None,
225                None,
226                &states(),
227            )
228            .unwrap();
229
230        // Search should find it immediately
231        let results = db.search_tasks("indexing", None, false, None).unwrap();
232        assert_eq!(results.len(), 1);
233        assert_eq!(results[0].task_id, task.id);
234    }
235
236    #[test]
237    fn test_fts_update_trigger_reindexes_modified_tasks() {
238        let db = Database::open_in_memory().unwrap();
239
240        // Create a task with initial content
241        let task = db
242            .create_task(
243                None,
244                "Original title original".to_string(),
245                None,
246                None,
247                None,
248                None,
249                None,
250                None,
251                None,
252                &states(),
253            )
254            .unwrap();
255
256        // Verify initial content is indexed
257        let results = db.search_tasks("Original", None, false, None).unwrap();
258        assert_eq!(results.len(), 1);
259
260        // Update the task - trigger should reindex
261        db.update_task(
262            &task.id,
263            Some("Updated title with newkeyword".to_string()),
264            Some(Some("Updated description".to_string())),
265            None,
266            None,
267            None,
268            None,
269            &states(),
270        )
271        .unwrap();
272
273        // Search should find new content
274        let results = db.search_tasks("newkeyword", None, false, None).unwrap();
275        assert_eq!(results.len(), 1);
276        assert_eq!(results[0].task_id, task.id);
277
278        // Verify updated title is searchable
279        let results = db.search_tasks("Updated", None, false, None).unwrap();
280        assert_eq!(results.len(), 1);
281    }
282
283    #[test]
284    fn test_fts_delete_trigger_removes_from_index() {
285        let db = Database::open_in_memory().unwrap();
286
287        // Create a task
288        let task = db
289            .create_task(
290                None,
291                "Deletable task content".to_string(),
292                None,
293                None,
294                None,
295                None,
296                None,
297                None,
298                None,
299                &states(),
300            )
301            .unwrap();
302
303        // Verify it's indexed
304        let results = db.search_tasks("Deletable", None, false, None).unwrap();
305        assert_eq!(results.len(), 1);
306
307        // Delete the task
308        db.delete_task(&task.id, "test-worker", false, None, true, true)
309            .unwrap();
310
311        // Search should find nothing
312        let results = db.search_tasks("Deletable", None, false, None).unwrap();
313        assert!(results.is_empty());
314    }
315
316    #[test]
317    fn test_fts_search_with_bm25_ranking() {
318        let db = Database::open_in_memory().unwrap();
319
320        // Create tasks with varying relevance
321        db.create_task(
322            None,
323            "Bug fix for minor bug".to_string(),
324            None,
325            None,
326            None,
327            None,
328            None,
329            None,
330            None,
331            &states(),
332        )
333        .unwrap();
334        db.create_task(
335            None,
336            "Bug bug bug multiple bugs".to_string(),
337            None,
338            None,
339            None,
340            None,
341            None,
342            None,
343            None,
344            &states(),
345        )
346        .unwrap();
347        db.create_task(
348            None,
349            "Feature implementation".to_string(),
350            None,
351            None,
352            None,
353            None,
354            None,
355            None,
356            None,
357            &states(),
358        )
359        .unwrap();
360
361        // Search for "bug" - higher frequency should rank better
362        let results = db.search_tasks("bug", None, false, None).unwrap();
363        assert_eq!(results.len(), 2);
364        // The task with more "bug" occurrences should have a better (lower) score
365        assert!(results[0].score <= results[1].score);
366    }
367
368    #[test]
369    fn test_fts_attachment_trigger_indexes_text_content() {
370        let db = Database::open_in_memory().unwrap();
371
372        // Create a task
373        let task = db
374            .create_task(
375                None,
376                "Task with attachment".to_string(),
377                None,
378                None,
379                None,
380                None,
381                None,
382                None,
383                None,
384                &states(),
385            )
386            .unwrap();
387
388        // Add a text attachment
389        db.add_attachment(
390            &task.id,
391            "notes".to_string(),
392            "Important searchable content here".to_string(),
393            Some("text/plain".to_string()),
394            None,
395        )
396        .unwrap();
397
398        // Search with include_attachments should find it
399        let results = db.search_tasks("searchable", None, true, None).unwrap();
400        assert_eq!(results.len(), 1);
401        assert_eq!(results[0].task_id, task.id);
402        assert_eq!(results[0].attachment_matches.len(), 1);
403        assert_eq!(results[0].attachment_matches[0].name, "notes");
404    }
405}
task_graph_mcp/db/search.rs

task_graph_mcp/db/
search.rs
