bones_core/db/
mod.rs

1//! `SQLite` projection database utilities.
2//!
3//! Runtime defaults are intentionally conservative:
4//! - `journal_mode = WAL` to allow concurrent readers while writers append
5//! - `busy_timeout = 5s` to reduce transient lock failures under contention
6//! - `foreign_keys = ON` to protect relational integrity in projection tables
7
8pub mod fts;
9pub mod incremental;
10pub mod migrations;
11pub mod project;
12pub mod query;
13pub mod rebuild;
14pub mod schema;
15
16use anyhow::{Context, Result};
17use rusqlite::Connection;
18use std::{path::Path, path::PathBuf, time::Duration};
19use tracing::debug;
20
21/// Busy timeout used for projection DB connections.
22pub const DEFAULT_BUSY_TIMEOUT: Duration = Duration::from_secs(5);
23
24const PROJECTION_DIRTY_MARKER: &str = "cache/projection.dirty";
25
26/// Open (or create) the projection `SQLite` database, apply runtime pragmas,
27/// and migrate schema to the latest version.
28///
29/// # Errors
30///
31/// Returns an error if opening/configuring/migrating the database fails.
32pub fn open_projection(path: &Path) -> Result<Connection> {
33    if let Some(parent) = path.parent() {
34        std::fs::create_dir_all(parent)
35            .with_context(|| format!("create projection db directory {}", parent.display()))?;
36    }
37
38    if let Err(err) = bones_sqlite_vec::register_auto_extension() {
39        debug!(%err, "sqlite-vec auto-extension unavailable");
40    }
41
42    let mut conn = Connection::open(path)
43        .with_context(|| format!("open projection database {}", path.display()))?;
44
45    configure_connection(&conn).context("configure sqlite pragmas")?;
46    migrations::migrate(&mut conn).context("apply projection migrations")?;
47
48    Ok(conn)
49}
50
51/// Ensure the projection database exists and is up-to-date.
52///
53/// If the database is missing, corrupt, or behind the event log, an
54/// incremental apply is triggered automatically. Returns `None` only if
55/// the events directory itself does not exist (no bones project).
56///
57/// This is the recommended entry point for read commands — it eliminates
58/// the need for users to run `bn admin rebuild` manually.
59///
60/// # Arguments
61///
62/// * `bones_dir` — Path to the `.bones/` directory.
63///
64/// # Errors
65///
66/// Returns an error if the rebuild or database open fails.
67pub fn ensure_projection(bones_dir: &Path) -> Result<Option<Connection>> {
68    let events_dir = bones_dir.join("events");
69    if !events_dir.is_dir() {
70        return Ok(None);
71    }
72
73    let db_path = bones_dir.join("bones.db");
74    let dirty_marker = projection_dirty_marker_path(bones_dir);
75    let marker_exists = dirty_marker.exists();
76
77    // Try opening existing projection (raw to avoid recursion).
78    let needs_rebuild = marker_exists
79        || query::try_open_projection_raw(&db_path)?.is_none_or(|conn| {
80            // Check if projection is current by comparing cursor against
81            // shard content. If cursor is at 0 with no hash, the DB was
82            // freshly created and needs a full rebuild.
83            let (offset, hash) = query::get_projection_cursor(&conn).unwrap_or((0, None));
84            if offset == 0 && hash.is_none() {
85                true
86            } else {
87                // Check if cursor and shard content are out of sync (new events beyond cursor, or cursor overshoots after sync).
88                let mgr = crate::shard::ShardManager::new(bones_dir);
89                let total_bytes = mgr.total_content_len().unwrap_or(0);
90                let cursor = usize::try_from(offset).unwrap_or(0);
91                total_bytes != cursor
92            }
93        });
94
95    if needs_rebuild {
96        debug!("projection stale or missing, running incremental rebuild");
97        incremental::incremental_apply(&events_dir, &db_path, false)
98            .context("auto-rebuild projection")?;
99        if dirty_marker.exists() {
100            let _ = std::fs::remove_file(&dirty_marker);
101        }
102    }
103
104    // Re-open after potential rebuild (raw to avoid recursion).
105    query::try_open_projection_raw(&db_path)
106}
107
108fn configure_connection(conn: &Connection) -> anyhow::Result<()> {
109    conn.pragma_update(None, "foreign_keys", "ON")
110        .context("PRAGMA foreign_keys = ON")?;
111    conn.pragma_update(None, "synchronous", "NORMAL")
112        .context("PRAGMA synchronous = NORMAL")?;
113    let _journal_mode: String = conn
114        .query_row("PRAGMA journal_mode = WAL", [], |row| row.get(0))
115        .context("PRAGMA journal_mode = WAL")?;
116    conn.busy_timeout(DEFAULT_BUSY_TIMEOUT)
117        .context("busy_timeout")?;
118    Ok(())
119}
120
121/// Compute the marker path that signals projection drift.
122#[must_use]
123pub fn projection_dirty_marker_path(bones_dir: &Path) -> PathBuf {
124    bones_dir.join(PROJECTION_DIRTY_MARKER)
125}
126
127/// Mark projection state as dirty so read paths trigger deterministic recovery.
128///
129/// # Errors
130///
131/// Returns an error if the marker directory cannot be created or marker file
132/// cannot be written.
133pub fn mark_projection_dirty(bones_dir: &Path, reason: &str) -> Result<()> {
134    let marker = projection_dirty_marker_path(bones_dir);
135    if let Some(parent) = marker.parent() {
136        std::fs::create_dir_all(parent)
137            .with_context(|| format!("create projection marker dir {}", parent.display()))?;
138    }
139
140    let ts = std::time::SystemTime::now()
141        .duration_since(std::time::UNIX_EPOCH)
142        .unwrap_or_default()
143        .as_micros();
144    std::fs::write(&marker, format!("{ts} {reason}\n"))
145        .with_context(|| format!("write projection marker {}", marker.display()))?;
146    Ok(())
147}
148
149/// Mark projection dirty by resolving the active database path from a connection.
150///
151/// # Errors
152///
153/// Returns an error if database metadata cannot be read or if writing the
154/// marker file fails after locating a `.bones` database path.
155pub fn mark_projection_dirty_from_connection(conn: &Connection, reason: &str) -> Result<()> {
156    let mut stmt = conn
157        .prepare("PRAGMA database_list")
158        .context("prepare PRAGMA database_list")?;
159    let mut rows = stmt.query([]).context("query PRAGMA database_list")?;
160
161    while let Some(row) = rows.next().context("iterate PRAGMA database_list")? {
162        let name: String = row.get(1).context("read database_list name")?;
163        if name != "main" {
164            continue;
165        }
166        let path: String = row.get(2).context("read database_list path")?;
167        if path.is_empty() {
168            return Ok(());
169        }
170        if let Some(bones_dir) = std::path::Path::new(&path).parent()
171            && bones_dir.ends_with(".bones")
172        {
173            return mark_projection_dirty(bones_dir, reason);
174        }
175    }
176
177    Ok(())
178}
179
180#[cfg(test)]
181mod tests {
182    use super::{DEFAULT_BUSY_TIMEOUT, open_projection};
183    use crate::db::migrations;
184    use crate::db::{ensure_projection, mark_projection_dirty, projection_dirty_marker_path};
185    use crate::event::Event;
186    use crate::event::data::{CreateData, EventData};
187    use crate::event::types::EventType;
188    use crate::event::writer;
189    use crate::model::item::{Kind, Urgency};
190    use crate::model::item_id::ItemId;
191    use crate::shard::ShardManager;
192    use std::collections::BTreeMap;
193    use tempfile::TempDir;
194
195    fn temp_db_path() -> (TempDir, std::path::PathBuf) {
196        let dir = tempfile::tempdir().expect("create temp dir");
197        let path = dir.path().join("bones-projection.sqlite3");
198        (dir, path)
199    }
200
201    #[test]
202    fn open_projection_sets_wal_busy_timeout_and_fk() {
203        let (_dir, path) = temp_db_path();
204        let conn = open_projection(&path).expect("open projection db");
205
206        let journal_mode: String = conn
207            .pragma_query_value(None, "journal_mode", |row| row.get(0))
208            .expect("query journal_mode");
209        assert_eq!(journal_mode.to_ascii_lowercase(), "wal");
210
211        let busy_timeout_ms: u64 = conn
212            .pragma_query_value(None, "busy_timeout", |row| row.get(0))
213            .expect("query busy_timeout");
214        assert_eq!(
215            u128::from(busy_timeout_ms),
216            DEFAULT_BUSY_TIMEOUT.as_millis()
217        );
218
219        let foreign_keys: i64 = conn
220            .pragma_query_value(None, "foreign_keys", |row| row.get(0))
221            .expect("query foreign_keys");
222        assert_eq!(foreign_keys, 1);
223    }
224
225    #[test]
226    fn open_projection_runs_migrations() {
227        let (_dir, path) = temp_db_path();
228        let conn = open_projection(&path).expect("open projection db");
229
230        let version = migrations::current_schema_version(&conn).expect("schema version query");
231        assert_eq!(version, migrations::LATEST_SCHEMA_VERSION);
232
233        let projection_version: i64 = conn
234            .query_row(
235                "SELECT schema_version FROM projection_meta WHERE id = 1",
236                [],
237                |row| row.get(0),
238            )
239            .expect("projection_meta schema version");
240        assert_eq!(
241            projection_version,
242            i64::from(migrations::LATEST_SCHEMA_VERSION)
243        );
244    }
245
246    #[test]
247    fn mark_projection_dirty_creates_marker_file() {
248        let dir = tempfile::tempdir().expect("create temp dir");
249        let bones_dir = dir.path().join(".bones");
250        std::fs::create_dir_all(bones_dir.join("events")).expect("events dir");
251
252        mark_projection_dirty(&bones_dir, "test reason").expect("mark projection dirty");
253
254        let marker = projection_dirty_marker_path(&bones_dir);
255        assert!(marker.exists(), "dirty marker should be created");
256    }
257
258    #[test]
259    fn ensure_projection_rebuild_clears_dirty_marker() {
260        let dir = tempfile::tempdir().expect("create temp dir");
261        let bones_dir = dir.path().join(".bones");
262        std::fs::create_dir_all(bones_dir.join("events")).expect("events dir");
263        std::fs::create_dir_all(bones_dir.join("cache")).expect("cache dir");
264
265        let shard_mgr = ShardManager::new(&bones_dir);
266        shard_mgr.init().expect("init shard");
267        let (year, month) = shard_mgr
268            .active_shard()
269            .expect("active shard")
270            .expect("some shard");
271
272        let mut create = Event {
273            wall_ts_us: 1_700_000_000_000_000,
274            agent: "test-agent".to_string(),
275            itc: "itc:AQ".to_string(),
276            parents: vec![],
277            event_type: EventType::Create,
278            item_id: ItemId::new_unchecked("bn-marker"),
279            data: EventData::Create(CreateData {
280                title: "marker test".to_string(),
281                kind: Kind::Task,
282                size: None,
283                urgency: Urgency::Default,
284                labels: vec![],
285                parent: None,
286                causation: None,
287                description: None,
288                extra: BTreeMap::new(),
289            }),
290            event_hash: String::new(),
291        };
292        let line = writer::write_event(&mut create).expect("serialize create event");
293        shard_mgr
294            .append_raw(year, month, &line)
295            .expect("append create event");
296
297        mark_projection_dirty(&bones_dir, "simulate projection failure").expect("mark dirty");
298        let marker = projection_dirty_marker_path(&bones_dir);
299        assert!(marker.exists(), "precondition: marker exists");
300
301        let conn = ensure_projection(&bones_dir)
302            .expect("ensure projection")
303            .expect("projection connection");
304        let item_count: i64 = conn
305            .query_row("SELECT COUNT(*) FROM items", [], |row| row.get(0))
306            .expect("count items");
307        assert_eq!(item_count, 1);
308        assert!(
309            !marker.exists(),
310            "dirty marker should be cleared after successful recovery"
311        );
312    }
313}
bones_core/db/mod.rs

bones_core/db/
mod.rs
