remem/retrieval/memory_search/
fts.rs

1use anyhow::Result;
2use rusqlite::Connection;
3
4use crate::db;
5use crate::memory::{map_memory_row_pub, Memory};
6use crate::retrieval::memory_search::filters::{push_branch_filter, push_project_filter};
7
8#[derive(Debug, Clone)]
9pub struct FtsMemoryHit {
10    pub memory: Memory,
11    pub score: f64,
12}
13
14/// FTS5 trigram search on memories.
15pub fn search_memories_fts(
16    conn: &Connection,
17    query: &str,
18    project: Option<&str>,
19    memory_type: Option<&str>,
20    limit: i64,
21    offset: i64,
22) -> Result<Vec<Memory>> {
23    search_memories_fts_filtered(
24        conn,
25        query,
26        project,
27        memory_type,
28        limit,
29        offset,
30        false,
31        None,
32    )
33}
34
35pub fn search_memories_fts_filtered(
36    conn: &Connection,
37    query: &str,
38    project: Option<&str>,
39    memory_type: Option<&str>,
40    limit: i64,
41    offset: i64,
42    include_inactive: bool,
43    branch: Option<&str>,
44) -> Result<Vec<Memory>> {
45    Ok(search_memories_fts_hits_filtered(
46        conn,
47        query,
48        project,
49        memory_type,
50        limit,
51        offset,
52        include_inactive,
53        branch,
54    )?
55    .into_iter()
56    .map(|hit| hit.memory)
57    .collect())
58}
59
60pub fn search_memories_fts_hits_filtered(
61    conn: &Connection,
62    query: &str,
63    project: Option<&str>,
64    memory_type: Option<&str>,
65    limit: i64,
66    offset: i64,
67    include_inactive: bool,
68    branch: Option<&str>,
69) -> Result<Vec<FtsMemoryHit>> {
70    crate::memory::retrieval_enrichment::ensure_retrieval_open(conn)?;
71    let mut conditions = vec!["memories_fts MATCH ?1".to_string()];
72    let mut param_values: Vec<Box<dyn rusqlite::types::ToSql>> = vec![Box::new(query.to_string())];
73
74    let mut idx = 2;
75    conditions.push(crate::memory::memory_current_filter_sql(
76        "m.status",
77        "m.expires_at_epoch",
78        include_inactive,
79    ));
80
81    idx = push_project_filter(
82        conn,
83        "m.project",
84        project,
85        idx,
86        &mut conditions,
87        &mut param_values,
88    )?;
89    idx = push_branch_filter("m.branch", branch, idx, &mut conditions, &mut param_values);
90    if let Some(memory_type) = memory_type {
91        conditions.push(format!("m.memory_type = ?{idx}"));
92        param_values.push(Box::new(memory_type.to_string()));
93        idx += 1;
94    }
95
96    param_values.push(Box::new(limit));
97    param_values.push(Box::new(offset));
98
99    let sql = format!(
100        "WITH ranked AS (
101             SELECT m.id, m.session_id, m.project, m.topic_key, m.title, m.content,
102                    m.memory_type, m.files, m.created_at_epoch, m.updated_at_epoch,
103                    m.status, m.branch, m.scope,
104                    (bm25(memories_fts, 10.0, 1.0, 3.0) * CASE WHEN m.memory_type IN ('decision','bugfix') THEN 1.5 ELSE 1.0 END) AS rank_score
105             FROM memories m
106             JOIN memories_fts ON memories_fts.rowid = m.id
107             WHERE {}
108         )
109         SELECT id, session_id, project, topic_key, title, content,
110                memory_type, files, created_at_epoch, updated_at_epoch,
111                status, branch, scope, rank_score
112         FROM ranked
113         ORDER BY rank_score ASC, id ASC
114         LIMIT ?{} OFFSET ?{}",
115        conditions.join(" AND "),
116        idx,
117        idx + 1
118    );
119
120    let mut stmt = conn.prepare(&sql)?;
121    let refs = db::to_sql_refs(&param_values);
122    let rows = stmt.query_map(refs.as_slice(), |row| {
123        Ok(FtsMemoryHit {
124            memory: map_memory_row_pub(row)?,
125            score: row.get(13)?,
126        })
127    })?;
128    crate::db::query::collect_rows(rows)
129}
130
131#[cfg(test)]
132mod tests {
133    use super::*;
134
135    fn setup_conn() -> Connection {
136        let conn = Connection::open_in_memory().unwrap();
137        crate::migrate::run_migrations(&conn).unwrap();
138        conn
139    }
140
141    fn insert_memory(conn: &Connection, id: i64, body: &str, status: &str) {
142        conn.execute(
143            "INSERT INTO memories(id, project, title, content, memory_type, created_at_epoch,
144                updated_at_epoch, status)
145             VALUES (?1, 'proj', 'title', ?2, 'decision', 100, 100, ?3)",
146            rusqlite::params![id, body, status],
147        )
148        .unwrap();
149    }
150
151    /// Reproduction for #236: before v019 a stale row never entered memories_fts,
152    /// so the JOIN dropped it and include_inactive bm25 search returned empty.
153    #[test]
154    fn include_inactive_finds_stale_rows() {
155        let conn = setup_conn();
156        insert_memory(&conn, 1, "deprecated zookeeper approach", "stale");
157
158        // active-only search must hide the stale row
159        let active_only = search_memories_fts_filtered(
160            &conn,
161            "zookeeper",
162            Some("proj"),
163            None,
164            10,
165            0,
166            false,
167            None,
168        )
169        .unwrap();
170        assert!(
171            active_only.is_empty(),
172            "active-only search must hide stale rows: {active_only:?}"
173        );
174
175        // include_inactive must surface the stale row via the bm25 path
176        let with_inactive =
177            search_memories_fts_filtered(&conn, "zookeeper", Some("proj"), None, 10, 0, true, None)
178                .unwrap();
179        assert_eq!(
180            with_inactive.len(),
181            1,
182            "include_inactive must retrieve stale"
183        );
184        assert_eq!(with_inactive[0].status, "stale");
185    }
186
187    /// Active rows must remain retrievable on the default (active-only) path.
188    #[test]
189    fn active_path_still_finds_active_rows() {
190        let conn = setup_conn();
191        insert_memory(&conn, 1, "current kafka pipeline", "active");
192
193        let hits =
194            search_memories_fts_filtered(&conn, "kafka", Some("proj"), None, 10, 0, false, None)
195                .unwrap();
196        assert_eq!(hits.len(), 1);
197        assert_eq!(hits[0].status, "active");
198    }
199
200    #[test]
201    fn equal_rank_scores_use_memory_id_as_a_stable_tiebreaker() -> anyhow::Result<()> {
202        let conn = setup_conn();
203        insert_memory(&conn, 2, "stable rankbridge result", "active");
204        insert_memory(&conn, 1, "stable rankbridge result", "active");
205
206        let hits = search_memories_fts_filtered(
207            &conn,
208            "rankbridge",
209            Some("proj"),
210            None,
211            10,
212            0,
213            false,
214            None,
215        )?;
216
217        assert_eq!(
218            hits.iter().map(|hit| hit.id).collect::<Vec<_>>(),
219            vec![1, 2]
220        );
221        Ok(())
222    }
223}
remem/retrieval/memory_search/fts.rs

remem/retrieval/memory_search/
fts.rs
