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,
237                None, // phase
238                None,
239                None,
240                None,
241                None,
242                None,
243                None,
244                &states(),
245                &IdsConfig::default(),
246            )
247            .unwrap();
248
249        // Search should find it immediately
250        let results = db.search_tasks("indexing", None, 0, false, None).unwrap();
251        assert_eq!(results.len(), 1);
252        assert_eq!(results[0].task_id, task.id);
253    }
254
255    #[test]
256    fn test_fts_update_trigger_reindexes_modified_tasks() {
257        let db = Database::open_in_memory().unwrap();
258
259        // Create a task with initial content
260        let task = db
261            .create_task(
262                None,
263                "Original title original".to_string(),
264                None,
265                None,
266                None, // phase
267                None,
268                None,
269                None,
270                None,
271                None,
272                None,
273                &states(),
274                &IdsConfig::default(),
275            )
276            .unwrap();
277
278        // Verify initial content is indexed
279        let results = db.search_tasks("Original", None, 0, false, None).unwrap();
280        assert_eq!(results.len(), 1);
281
282        // Update the task - trigger should reindex
283        db.update_task(
284            &task.id,
285            Some("Updated title with newkeyword".to_string()),
286            Some(Some("Updated description".to_string())),
287            None,
288            None,
289            None,
290            None,
291            &states(),
292        )
293        .unwrap();
294
295        // Search should find new content
296        let results = db.search_tasks("newkeyword", None, 0, false, None).unwrap();
297        assert_eq!(results.len(), 1);
298        assert_eq!(results[0].task_id, task.id);
299
300        // Verify updated title is searchable
301        let results = db.search_tasks("Updated", None, 0, false, None).unwrap();
302        assert_eq!(results.len(), 1);
303    }
304
305    #[test]
306    fn test_fts_delete_trigger_removes_from_index() {
307        let db = Database::open_in_memory().unwrap();
308
309        // Create a task
310        let task = db
311            .create_task(
312                None,
313                "Deletable task content".to_string(),
314                None,
315                None,
316                None, // phase
317                None,
318                None,
319                None,
320                None,
321                None,
322                None,
323                &states(),
324                &IdsConfig::default(),
325            )
326            .unwrap();
327
328        // Verify it's indexed
329        let results = db.search_tasks("Deletable", None, 0, false, None).unwrap();
330        assert_eq!(results.len(), 1);
331
332        // Delete the task
333        db.delete_task(&task.id, "test-worker", false, None, true, true)
334            .unwrap();
335
336        // Search should find nothing
337        let results = db.search_tasks("Deletable", None, 0, false, None).unwrap();
338        assert!(results.is_empty());
339    }
340
341    #[test]
342    fn test_fts_search_with_bm25_ranking() {
343        let db = Database::open_in_memory().unwrap();
344
345        // Create tasks with varying relevance
346        db.create_task(
347            None,
348            "Bug fix for minor bug".to_string(),
349            None,
350            None,
351            None, // phase
352            None,
353            None,
354            None,
355            None,
356            None,
357            None,
358            &states(),
359            &IdsConfig::default(),
360        )
361        .unwrap();
362        db.create_task(
363            None,
364            "Bug bug bug multiple bugs".to_string(),
365            None,
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        db.create_task(
379            None,
380            "Feature implementation".to_string(),
381            None,
382            None,
383            None, // phase
384            None,
385            None,
386            None,
387            None,
388            None,
389            None,
390            &states(),
391            &IdsConfig::default(),
392        )
393        .unwrap();
394
395        // Search for "bug" - higher frequency should rank better
396        let results = db.search_tasks("bug", None, 0, false, None).unwrap();
397        assert_eq!(results.len(), 2);
398        // The task with more "bug" occurrences should have a better (lower) score
399        assert!(results[0].score <= results[1].score);
400    }
401
402    #[test]
403    fn test_fts_attachment_trigger_indexes_text_content() {
404        let db = Database::open_in_memory().unwrap();
405
406        // Create a task
407        let task = db
408            .create_task(
409                None,
410                "Task with attachment".to_string(),
411                None,
412                None,
413                None, // phase
414                None,
415                None,
416                None,
417                None,
418                None,
419                None,
420                &states(),
421                &IdsConfig::default(),
422            )
423            .unwrap();
424
425        // Add a text attachment
426        db.add_attachment(
427            &task.id,
428            "notes".to_string(),
429            String::new(),
430            "Important searchable content here".to_string(),
431            Some("text/plain".to_string()),
432            None,
433        )
434        .unwrap();
435
436        // Search with include_attachments should find it
437        let results = db.search_tasks("searchable", None, 0, true, None).unwrap();
438        assert_eq!(results.len(), 1);
439        assert_eq!(results[0].task_id, task.id);
440        assert_eq!(results[0].attachment_matches.len(), 1);
441        assert_eq!(results[0].attachment_matches[0].attachment_type, "notes");
442    }
443}
task_graph_mcp/db/search.rs

task_graph_mcp/db/
search.rs
