cqs/store/
notes.rs

1//! Note CRUD operations
2
3use std::path::Path;
4
5use sqlx::Row;
6
7use super::helpers::{NoteStats, NoteSummary, StoreError};
8use super::Store;
9use crate::nl::normalize_for_fts;
10use crate::note::Note;
11use crate::note::{SENTIMENT_NEGATIVE_THRESHOLD, SENTIMENT_POSITIVE_THRESHOLD};
12
13/// Insert a single note + FTS entry within an existing transaction.
14async fn insert_note_with_fts(
15    tx: &mut sqlx::Transaction<'_, sqlx::Sqlite>,
16    note: &Note,
17    source_str: &str,
18    file_mtime: i64,
19    now: &str,
20) -> Result<(), StoreError> {
21    let mentions_json = serde_json::to_string(&note.mentions)?;
22
23    // Write empty blob for embedding column (SQ-9: note embeddings removed).
24    // Column retained for SQLite compatibility (no DROP COLUMN in older versions).
25    let empty_blob: &[u8] = &[];
26    sqlx::query(
27        "INSERT OR REPLACE INTO notes (id, text, sentiment, mentions, embedding, source_file, file_mtime, created_at, updated_at)
28         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
29    )
30    .bind(&note.id)
31    .bind(&note.text)
32    .bind(note.sentiment)
33    .bind(&mentions_json)
34    .bind(empty_blob)
35    .bind(source_str)
36    .bind(file_mtime)
37    .bind(now)
38    .bind(now)
39    .execute(&mut **tx)
40    .await?;
41
42    // Delete from FTS before insert - error must fail transaction to prevent desync
43    sqlx::query("DELETE FROM notes_fts WHERE id = ?1")
44        .bind(&note.id)
45        .execute(&mut **tx)
46        .await?;
47
48    sqlx::query("INSERT INTO notes_fts (id, text) VALUES (?1, ?2)")
49        .bind(&note.id)
50        .bind(normalize_for_fts(&note.text))
51        .execute(&mut **tx)
52        .await?;
53
54    Ok(())
55}
56
57impl Store {
58    /// Insert or update notes in batch
59    pub fn upsert_notes_batch(
60        &self,
61        notes: &[Note],
62        source_file: &Path,
63        file_mtime: i64,
64    ) -> Result<usize, StoreError> {
65        let _span = tracing::info_span!("upsert_notes_batch", count = notes.len()).entered();
66        let source_str = crate::normalize_path(source_file);
67        tracing::debug!(
68            source = %source_str,
69            count = notes.len(),
70            "upserting notes batch"
71        );
72
73        self.rt.block_on(async {
74            let mut tx = self.pool.begin().await?;
75
76            let now = chrono::Utc::now().to_rfc3339();
77            for note in notes {
78                insert_note_with_fts(&mut tx, note, &source_str, file_mtime, &now).await?;
79            }
80
81            tx.commit().await?;
82            self.invalidate_notes_cache();
83            Ok(notes.len())
84        })
85    }
86
87    /// Replace all notes for a source file in a single transaction.
88    ///
89    /// Atomically deletes existing notes and inserts new ones, preventing
90    /// data loss if the process crashes mid-operation.
91    pub fn replace_notes_for_file(
92        &self,
93        notes: &[Note],
94        source_file: &Path,
95        file_mtime: i64,
96    ) -> Result<usize, StoreError> {
97        let _span =
98            tracing::info_span!("replace_notes_for_file", path = %source_file.display()).entered();
99        let source_str = crate::normalize_path(source_file);
100        tracing::debug!(
101            source = %source_str,
102            count = notes.len(),
103            "replacing notes for file"
104        );
105
106        self.rt.block_on(async {
107            let mut tx = self.pool.begin().await?;
108
109            // Step 1: Delete existing notes + FTS for this file
110            sqlx::query(
111                "DELETE FROM notes_fts WHERE id IN (SELECT id FROM notes WHERE source_file = ?1)",
112            )
113            .bind(&source_str)
114            .execute(&mut *tx)
115            .await?;
116
117            sqlx::query("DELETE FROM notes WHERE source_file = ?1")
118                .bind(&source_str)
119                .execute(&mut *tx)
120                .await?;
121
122            // Step 2: Insert new notes + FTS
123            let now = chrono::Utc::now().to_rfc3339();
124            for note in notes {
125                insert_note_with_fts(&mut tx, note, &source_str, file_mtime, &now).await?;
126            }
127
128            tx.commit().await?;
129            self.invalidate_notes_cache();
130            tracing::info!(source = %source_str, count = notes.len(), "Notes replaced successfully");
131            Ok(notes.len())
132        })
133    }
134
135    /// Check if notes file needs reindexing based on mtime.
136    ///
137    /// Returns `Ok(Some(mtime))` if reindex needed (with the file's current mtime),
138    /// or `Ok(None)` if no reindex needed. This avoids reading file metadata twice.
139    pub fn notes_need_reindex(&self, source_file: &Path) -> Result<Option<i64>, StoreError> {
140        let _span =
141            tracing::debug_span!("notes_need_reindex", path = %source_file.display()).entered();
142        let current_mtime = source_file
143            .metadata()?
144            .modified()?
145            .duration_since(std::time::UNIX_EPOCH)
146            .map_err(|_| StoreError::SystemTime)?
147            .as_millis() as i64;
148
149        self.rt.block_on(async {
150            let row: Option<(i64,)> =
151                sqlx::query_as("SELECT file_mtime FROM notes WHERE source_file = ?1 LIMIT 1")
152                    .bind(crate::normalize_path(source_file))
153                    .fetch_optional(&self.pool)
154                    .await?;
155
156            match row {
157                Some((mtime,)) if mtime >= current_mtime => Ok(None),
158                _ => Ok(Some(current_mtime)),
159            }
160        })
161    }
162
163    /// Retrieves the total count of notes stored in the database.
164    ///
165    /// This method executes a SQL COUNT query against the notes table and returns the total number of notes. If no notes exist, it returns 0.
166    ///
167    /// # Returns
168    ///
169    /// Returns a `Result` containing the count of notes as a `u64`, or a `StoreError` if the database query fails.
170    ///
171    /// # Errors
172    ///
173    /// Returns `StoreError` if the database query encounters an error or the connection fails.
174    pub fn note_count(&self) -> Result<u64, StoreError> {
175        let _span = tracing::debug_span!("note_count").entered();
176        self.rt.block_on(async {
177            let row: Option<(i64,)> = sqlx::query_as("SELECT COUNT(*) FROM notes")
178                .fetch_optional(&self.pool)
179                .await?;
180            Ok(row.map(|(c,)| c as u64).unwrap_or(0))
181        })
182    }
183
184    /// Get note statistics (total, warnings, patterns).
185    ///
186    /// Uses `SENTIMENT_NEGATIVE_THRESHOLD` (-0.3) and `SENTIMENT_POSITIVE_THRESHOLD` (0.3)
187    /// to classify notes. These thresholds work with discrete sentiment values
188    /// (-1, -0.5, 0, 0.5, 1) -- negative values (-1, -0.5) count as warnings,
189    /// positive values (0.5, 1) count as patterns.
190    pub fn note_stats(&self) -> Result<NoteStats, StoreError> {
191        let _span = tracing::debug_span!("note_stats").entered();
192        self.rt.block_on(async {
193            let (total, warnings, patterns): (i64, i64, i64) = sqlx::query_as(
194                "SELECT COUNT(*),
195                        SUM(CASE WHEN sentiment < ?1 THEN 1 ELSE 0 END),
196                        SUM(CASE WHEN sentiment > ?2 THEN 1 ELSE 0 END)
197                 FROM notes",
198            )
199            .bind(SENTIMENT_NEGATIVE_THRESHOLD)
200            .bind(SENTIMENT_POSITIVE_THRESHOLD)
201            .fetch_one(&self.pool)
202            .await?;
203
204            Ok(NoteStats {
205                total: total as u64,
206                warnings: warnings as u64,
207                patterns: patterns as u64,
208            })
209        })
210    }
211
212    /// List all notes with metadata (no embeddings).
213    ///
214    /// Returns `NoteSummary` for each note, useful for mention-based filtering
215    /// without the cost of loading embeddings.
216    pub fn list_notes_summaries(&self) -> Result<Vec<NoteSummary>, StoreError> {
217        let _span = tracing::debug_span!("list_notes_summaries").entered();
218        self.rt.block_on(async {
219            let rows: Vec<_> =
220                sqlx::query("SELECT id, text, sentiment, mentions FROM notes ORDER BY created_at")
221                    .fetch_all(&self.pool)
222                    .await?;
223
224            Ok(rows
225                .into_iter()
226                .map(|row| {
227                    let id: String = row.get(0);
228                    let text: String = row.get(1);
229                    let sentiment: f64 = row.get(2);
230                    let mentions_json: String = row.get(3);
231                    let mentions: Vec<String> =
232                        serde_json::from_str(&mentions_json).unwrap_or_else(|e| {
233                            tracing::warn!(note_id = %id, error = %e, "Failed to deserialize note mentions");
234                            Vec::new()
235                        });
236                    NoteSummary {
237                        id,
238                        text,
239                        sentiment: sentiment as f32,
240                        mentions,
241                    }
242                })
243                .collect())
244        })
245    }
246}
247
248#[cfg(test)]
249mod tests {
250    use crate::note::{Note, SENTIMENT_NEGATIVE_THRESHOLD, SENTIMENT_POSITIVE_THRESHOLD};
251    use crate::test_helpers::setup_store;
252    use std::path::Path;
253
254    /// Creates a new Note with the specified id, text, and sentiment.
255    ///
256    /// # Arguments
257    ///
258    /// * `id` - A string slice representing the unique identifier for the note
259    /// * `text` - A string slice containing the note's content
260    /// * `sentiment` - A floating-point value representing the sentiment score of the note
261    ///
262    /// # Returns
263    ///
264    /// A new `Note` struct initialized with the provided id, text, and sentiment, and an empty mentions vector.
265    fn make_note(id: &str, text: &str, sentiment: f32) -> Note {
266        Note {
267            id: id.to_string(),
268            text: text.to_string(),
269            sentiment,
270            mentions: vec![],
271        }
272    }
273    /// Verifies that sentiment thresholds are positioned correctly between discrete sentiment values to ensure proper classification boundaries.
274    ///
275    /// This test function asserts that the negative sentiment threshold falls strictly between -0.5 and 0, and the positive sentiment threshold falls strictly between 0 and 0.5. This positioning ensures that discrete sentiment values are classified into their intended categories (negative, neutral, or positive) without ambiguity.
276    ///
277    /// # Panics
278    ///
279    /// Panics if either threshold assertion fails, indicating that sentiment thresholds are not properly configured for the discrete sentiment value system.
280
281    #[test]
282    fn sentiment_thresholds_match_discrete_values() {
283        // Discrete sentiment values: -1, -0.5, 0, 0.5, 1
284        // Negative threshold must sit between -0.5 and 0 so that
285        // -0.5 counts as a warning but 0 does not.
286        assert!(SENTIMENT_NEGATIVE_THRESHOLD > -0.5);
287        assert!(SENTIMENT_NEGATIVE_THRESHOLD < 0.0);
288        // Positive threshold must sit between 0 and 0.5 so that
289        // 0.5 counts as a pattern but 0 does not.
290        assert!(SENTIMENT_POSITIVE_THRESHOLD > 0.0);
291        assert!(SENTIMENT_POSITIVE_THRESHOLD < 0.5);
292    }
293
294    #[test]
295    fn test_replace_notes_replaces_not_appends() {
296        let (store, _dir) = setup_store();
297        let source = Path::new("/tmp/notes.toml");
298
299        // Insert 2 notes
300        let notes = vec![
301            make_note("n1", "first", 0.0),
302            make_note("n2", "second", 0.0),
303        ];
304        store.upsert_notes_batch(&notes, source, 100).unwrap();
305        assert_eq!(store.note_count().unwrap(), 2);
306
307        // Replace with 1 note
308        let replacement = vec![make_note("n3", "replacement", 0.0)];
309        store
310            .replace_notes_for_file(&replacement, source, 200)
311            .unwrap();
312        assert_eq!(store.note_count().unwrap(), 1);
313    }
314
315    #[test]
316    fn test_replace_notes_with_empty_deletes() {
317        let (store, _dir) = setup_store();
318        let source = Path::new("/tmp/notes.toml");
319
320        let notes = vec![
321            make_note("n1", "first", 0.0),
322            make_note("n2", "second", 0.5),
323        ];
324        store.upsert_notes_batch(&notes, source, 100).unwrap();
325        assert_eq!(store.note_count().unwrap(), 2);
326
327        // Replace with empty
328        store.replace_notes_for_file(&[], source, 200).unwrap();
329        assert_eq!(store.note_count().unwrap(), 0);
330    }
331
332    #[test]
333    fn test_notes_need_reindex_stale() {
334        let (store, dir) = setup_store();
335        // Create a real temp file so metadata() works
336        let notes_file = dir.path().join("notes.toml");
337        std::fs::write(&notes_file, "# empty").unwrap();
338
339        // Insert a note with an old mtime (0) so it's stale
340        let notes = vec![make_note("n1", "old note", 0.0)];
341        store.upsert_notes_batch(&notes, &notes_file, 0).unwrap();
342
343        // Should return Some(current_mtime) because stored mtime (0) < file mtime
344        let result = store.notes_need_reindex(&notes_file).unwrap();
345        assert!(
346            result.is_some(),
347            "Should need reindex when stored mtime is old"
348        );
349    }
350
351    #[test]
352    fn test_notes_need_reindex_current() {
353        let (store, dir) = setup_store();
354        let notes_file = dir.path().join("notes.toml");
355        std::fs::write(&notes_file, "# empty").unwrap();
356
357        // Get the file's actual mtime
358        let current_mtime = notes_file
359            .metadata()
360            .unwrap()
361            .modified()
362            .unwrap()
363            .duration_since(std::time::UNIX_EPOCH)
364            .unwrap()
365            .as_millis() as i64;
366
367        // Insert with the current mtime
368        let notes = vec![make_note("n1", "current note", 0.0)];
369        store
370            .upsert_notes_batch(&notes, &notes_file, current_mtime)
371            .unwrap();
372
373        // Should return None — no reindex needed
374        let result = store.notes_need_reindex(&notes_file).unwrap();
375        assert!(
376            result.is_none(),
377            "Should not need reindex when mtime matches"
378        );
379    }
380
381    #[test]
382    fn test_note_count() {
383        let (store, _dir) = setup_store();
384        let source = Path::new("/tmp/notes.toml");
385
386        assert_eq!(store.note_count().unwrap(), 0);
387
388        let notes = vec![
389            make_note("n1", "first", 0.0),
390            make_note("n2", "second", -0.5),
391            make_note("n3", "third", 1.0),
392        ];
393        store.upsert_notes_batch(&notes, source, 100).unwrap();
394        assert_eq!(store.note_count().unwrap(), 3);
395    }
396
397    #[test]
398    fn test_note_stats_sentiment() {
399        let (store, _dir) = setup_store();
400        let source = Path::new("/tmp/notes.toml");
401
402        // -1 = warning, 0 = neutral, 0.5 = pattern
403        let notes = vec![
404            make_note("n1", "pain point", -1.0),
405            make_note("n2", "neutral obs", 0.0),
406            make_note("n3", "good pattern", 0.5),
407        ];
408        store.upsert_notes_batch(&notes, source, 100).unwrap();
409
410        let stats = store.note_stats().unwrap();
411        assert_eq!(stats.total, 3);
412        assert_eq!(stats.warnings, 1, "Only -1 should count as warning");
413        assert_eq!(stats.patterns, 1, "Only 0.5 should count as pattern");
414    }
415}
cqs/store/notes.rs

cqs/store/
notes.rs
