Skip to main content

fetchium_core/index/
store.rs

1//! SQLite metadata store for the local index (Phase 5, PRD §28).
2//!
3//! Stores document metadata alongside the HNSW vector index.
4//! The SQLite DB is the source of truth for document content;
5//! the HNSW index is a search accelerator built on top.
6
7use crate::error::FetchiumError;
8use crate::index::document::{IndexStats, IndexedDocument};
9use chrono::Utc;
10use rusqlite::{Connection, OptionalExtension};
11use sha2::{Digest, Sha256};
12use std::path::Path;
13use std::sync::Mutex;
14use tracing::debug;
15
16/// SQLite-backed document metadata store.
17pub struct DocumentStore {
18    conn: Mutex<Connection>,
19}
20
21impl DocumentStore {
22    /// Open or create the store at `db_path`.
23    pub fn new(db_path: &Path) -> Result<Self, FetchiumError> {
24        if let Some(p) = db_path.parent() {
25            std::fs::create_dir_all(p)?;
26        }
27        let conn = Connection::open(db_path)?;
28        conn.execute_batch(
29            "PRAGMA journal_mode=WAL;
30             CREATE TABLE IF NOT EXISTS documents (
31                 id           INTEGER PRIMARY KEY AUTOINCREMENT,
32                 url          TEXT    NOT NULL UNIQUE,
33                 title        TEXT    NOT NULL DEFAULT '',
34                 content      TEXT    NOT NULL DEFAULT '',
35                 domain       TEXT    NOT NULL DEFAULT '',
36                 fetched_at   TEXT    NOT NULL,
37                 content_hash TEXT    NOT NULL,
38                 has_embedding INTEGER NOT NULL DEFAULT 0
39             );
40             CREATE INDEX IF NOT EXISTS idx_docs_domain
41                 ON documents(domain);
42             CREATE INDEX IF NOT EXISTS idx_docs_hash
43                 ON documents(content_hash);",
44        )?;
45        Ok(Self {
46            conn: Mutex::new(conn),
47        })
48    }
49
50    /// Insert or update a document. Returns `(id, is_new_or_changed)`.
51    pub fn upsert(&self, doc: &IndexedDocument) -> Result<(u64, bool), FetchiumError> {
52        let hash = &doc.content_hash;
53        let conn = self.conn.lock().unwrap();
54
55        // Check existing content hash
56        let existing_hash: Option<String> = conn
57            .query_row(
58                "SELECT content_hash FROM documents WHERE url = ?1",
59                [&doc.url],
60                |r| r.get(0),
61            )
62            .optional()?;
63
64        let changed = existing_hash.as_deref() != Some(hash.as_str());
65
66        conn.execute(
67            "INSERT INTO documents (url, title, content, domain, fetched_at, content_hash, has_embedding)
68             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
69             ON CONFLICT(url) DO UPDATE SET
70                 title        = excluded.title,
71                 content      = excluded.content,
72                 domain       = excluded.domain,
73                 fetched_at   = excluded.fetched_at,
74                 content_hash = excluded.content_hash,
75                 has_embedding = CASE
76                     WHEN excluded.content_hash != documents.content_hash THEN 0
77                     ELSE documents.has_embedding
78                 END",
79            rusqlite::params![
80                doc.url,
81                doc.title,
82                doc.content,
83                doc.domain,
84                doc.fetched_at.to_rfc3339(),
85                hash,
86                doc.embedding.is_some() as i64,
87            ],
88        )?;
89
90        let id: i64 =
91            conn.query_row("SELECT id FROM documents WHERE url = ?1", [&doc.url], |r| {
92                r.get(0)
93            })?;
94
95        debug!(
96            "Document upserted: id={id}, url={}, changed={changed}",
97            doc.url
98        );
99        Ok((id as u64, changed))
100    }
101
102    /// Load a document by ID.
103    pub fn get_by_id(&self, id: u64) -> Result<Option<IndexedDocument>, FetchiumError> {
104        let conn = self.conn.lock().unwrap();
105        let result = conn
106            .query_row(
107                "SELECT id, url, title, content, domain, fetched_at, content_hash
108                 FROM documents WHERE id = ?1",
109                [id],
110                |r| {
111                    Ok((
112                        r.get::<_, i64>(0)?,
113                        r.get::<_, String>(1)?,
114                        r.get::<_, String>(2)?,
115                        r.get::<_, String>(3)?,
116                        r.get::<_, String>(4)?,
117                        r.get::<_, String>(5)?,
118                        r.get::<_, String>(6)?,
119                    ))
120                },
121            )
122            .optional()?;
123
124        Ok(result.map(
125            |(id, url, title, content, domain, fetched_at, content_hash)| IndexedDocument {
126                id: id as u64,
127                url,
128                title,
129                content,
130                domain,
131                fetched_at: fetched_at.parse().unwrap_or_else(|_| Utc::now()),
132                content_hash,
133                embedding: None,
134            },
135        ))
136    }
137
138    /// Load multiple documents by their IDs.
139    pub fn get_by_ids(&self, ids: &[u64]) -> Result<Vec<IndexedDocument>, FetchiumError> {
140        if ids.is_empty() {
141            return Ok(Vec::new());
142        }
143        let mut docs = Vec::with_capacity(ids.len());
144        for &id in ids {
145            if let Some(doc) = self.get_by_id(id)? {
146                docs.push(doc);
147            }
148        }
149        Ok(docs)
150    }
151
152    /// Return all documents that don't yet have embeddings.
153    pub fn documents_without_embeddings(&self) -> Result<Vec<IndexedDocument>, FetchiumError> {
154        let conn = self.conn.lock().unwrap();
155        let mut stmt = conn.prepare(
156            "SELECT id, url, title, content, domain, fetched_at, content_hash
157             FROM documents WHERE has_embedding = 0",
158        )?;
159        let rows = stmt.query_map([], |r| {
160            Ok((
161                r.get::<_, i64>(0)?,
162                r.get::<_, String>(1)?,
163                r.get::<_, String>(2)?,
164                r.get::<_, String>(3)?,
165                r.get::<_, String>(4)?,
166                r.get::<_, String>(5)?,
167                r.get::<_, String>(6)?,
168            ))
169        })?;
170
171        let mut docs = Vec::new();
172        for row in rows {
173            let (id, url, title, content, domain, fetched_at, content_hash) = row?;
174            docs.push(IndexedDocument {
175                id: id as u64,
176                url,
177                title,
178                content,
179                domain,
180                fetched_at: fetched_at.parse().unwrap_or_else(|_| Utc::now()),
181                content_hash,
182                embedding: None,
183            });
184        }
185        Ok(docs)
186    }
187
188    /// Mark a document as having an embedding.
189    pub fn mark_embedded(&self, id: u64) -> Result<(), FetchiumError> {
190        let conn = self.conn.lock().unwrap();
191        conn.execute("UPDATE documents SET has_embedding = 1 WHERE id = ?1", [id])?;
192        Ok(())
193    }
194
195    /// Return index statistics.
196    pub fn stats(&self) -> Result<IndexStats, FetchiumError> {
197        let conn = self.conn.lock().unwrap();
198        let doc_count: i64 = conn.query_row("SELECT COUNT(*) FROM documents", [], |r| r.get(0))?;
199        let embedded_count: i64 = conn.query_row(
200            "SELECT COUNT(*) FROM documents WHERE has_embedding = 1",
201            [],
202            |r| r.get(0),
203        )?;
204        Ok(IndexStats {
205            document_count: doc_count as usize,
206            embedded_count: embedded_count as usize,
207            index_size_bytes: 0,       // caller fills this in from disk stat
208            vector_index_ready: false, // caller fills this in
209        })
210    }
211
212    /// Full-text search using SQL LIKE against title and content.
213    ///
214    /// Returns up to `limit` documents ordered by insertion time (newest first).
215    pub fn search(&self, query: &str, limit: usize) -> Result<Vec<IndexedDocument>, FetchiumError> {
216        let conn = self.conn.lock().unwrap();
217        let pattern = format!("%{}%", query.replace('%', r"\%").replace('_', r"\_"));
218        let mut stmt = conn.prepare(
219            "SELECT id, url, title, content, domain, fetched_at, content_hash
220             FROM documents
221             WHERE title LIKE ?1 ESCAPE '\\' OR content LIKE ?1 ESCAPE '\\'
222             ORDER BY id DESC
223             LIMIT ?2",
224        )?;
225        let rows = stmt.query_map(rusqlite::params![pattern, limit as i64], |r| {
226            Ok((
227                r.get::<_, i64>(0)?,
228                r.get::<_, String>(1)?,
229                r.get::<_, String>(2)?,
230                r.get::<_, String>(3)?,
231                r.get::<_, String>(4)?,
232                r.get::<_, String>(5)?,
233                r.get::<_, String>(6)?,
234            ))
235        })?;
236
237        let mut docs = Vec::new();
238        for row in rows {
239            let (id, url, title, content, domain, fetched_at, content_hash) = row?;
240            docs.push(IndexedDocument {
241                id: id as u64,
242                url,
243                title,
244                content,
245                domain,
246                fetched_at: fetched_at.parse().unwrap_or_else(|_| Utc::now()),
247                content_hash,
248                embedding: None,
249            });
250        }
251        Ok(docs)
252    }
253
254    /// Delete all documents from the store. Returns the number of rows removed.
255    pub fn clear(&self) -> Result<usize, FetchiumError> {
256        let conn = self.conn.lock().unwrap();
257        let n = conn.execute("DELETE FROM documents", [])?;
258        debug!("DocumentStore cleared: {n} documents removed");
259        Ok(n)
260    }
261
262    /// Compute SHA-256 hex digest of content.
263    pub fn content_hash(content: &str) -> String {
264        format!("{:x}", Sha256::digest(content.as_bytes()))
265    }
266}
267
268#[cfg(test)]
269mod tests {
270    use super::*;
271    use tempfile::NamedTempFile;
272
273    fn make_store() -> (DocumentStore, NamedTempFile) {
274        let tmp = NamedTempFile::new().unwrap();
275        let store = DocumentStore::new(tmp.path()).unwrap();
276        (store, tmp)
277    }
278
279    fn make_doc(n: u64) -> IndexedDocument {
280        IndexedDocument {
281            id: n,
282            url: format!("https://example.com/{n}"),
283            title: format!("Doc {n}"),
284            content: format!("Content for document {n}"),
285            domain: "example.com".into(),
286            fetched_at: Utc::now(),
287            content_hash: format!("hash{n}"),
288            embedding: None,
289        }
290    }
291
292    #[test]
293    fn upsert_and_get() {
294        let (store, _tmp) = make_store();
295        let doc = make_doc(1);
296        let (id, new) = store.upsert(&doc).unwrap();
297        assert!(new);
298        let loaded = store.get_by_id(id).unwrap().unwrap();
299        assert_eq!(loaded.url, "https://example.com/1");
300    }
301
302    #[test]
303    fn upsert_same_hash_not_changed() {
304        let (store, _tmp) = make_store();
305        let doc = make_doc(2);
306        store.upsert(&doc).unwrap();
307        let (_, changed) = store.upsert(&doc).unwrap();
308        // Same hash → not changed
309        assert!(!changed);
310    }
311
312    #[test]
313    fn stats_count() {
314        let (store, _tmp) = make_store();
315        store.upsert(&make_doc(1)).unwrap();
316        store.upsert(&make_doc(2)).unwrap();
317        let stats = store.stats().unwrap();
318        assert_eq!(stats.document_count, 2);
319        assert_eq!(stats.embedded_count, 0);
320    }
321
322    #[test]
323    fn mark_embedded_updates_flag() {
324        let (store, _tmp) = make_store();
325        let (id, _) = store.upsert(&make_doc(3)).unwrap();
326        store.mark_embedded(id).unwrap();
327        let stats = store.stats().unwrap();
328        assert_eq!(stats.embedded_count, 1);
329    }
330
331    #[test]
332    fn content_hash_deterministic() {
333        let h1 = DocumentStore::content_hash("hello");
334        let h2 = DocumentStore::content_hash("hello");
335        assert_eq!(h1, h2);
336        assert_ne!(h1, DocumentStore::content_hash("world"));
337    }
338}