YC (S26) | Open Computer History | Continuously record your company computer work, map your workflows, help you find work worth automating, and power your agents' context
Rust agentsagiaiai-memoryaudio-recordingcomputer-visionhermeshermes-agentllmlocal-ailocal-firstmachine-learningmcpmultimodalopenclawprivacyrewindscreen-recordingspeech-to-textycombinator
// screenpipe — AI that knows everything you've seen, said, or heard
// https://screenpipe.com
use super::*;
use crate::DatabaseManager;
use sqlx::Row;
use std::sync::{
atomic::{AtomicUsize, Ordering},
Condvar,
};
use std::time::Duration;
struct Pause(Arc<(Mutex<bool>, Condvar)>);
impl Pause {
fn new() -> Self {
Self(Arc::new((Mutex::new(false), Condvar::new())))
}
fn wait(&self) {
let (lock, wake) = &*self.0;
let (_guard, timeout) = wake
.wait_timeout_while(lock.lock().unwrap(), Duration::from_secs(10), |done| !*done)
.unwrap();
assert!(!timeout.timed_out(), "test did not release decoder");
}
fn release(&self) {
*self.0 .0.lock().unwrap() = true;
self.0 .1.notify_all();
}
}
impl Drop for Pause {
fn drop(&mut self) {
self.release();
}
}
#[tokio::test]
async fn resident_tombstone_cleanup_is_indexed_batched_and_upgraded_on_reopen() {
use std::sync::atomic::AtomicU64;
unsafe extern "C" fn count_steps(context: *mut std::ffi::c_void) -> i32 {
let steps = unsafe { &*context.cast::<AtomicU64>() };
steps.fetch_add(1000, Ordering::Relaxed);
0
}
let root = tempfile::tempdir().unwrap();
let path = root.path().join("db.sqlite");
let mut options = crate::storage::MigrationOptions::default();
options.budget.record_bytes = 1024 * 1024;
let db = DatabaseManager::new_hybrid(root.path(), Default::default(), options)
.await
.unwrap();
db.execute_raw_sql_write(
"WITH RECURSIVE n(id) AS (VALUES(1) UNION ALL SELECT id+1 FROM n WHERE id<1000000)
INSERT INTO _bulk_element_rows(id,frame_id,source,role,text,_archive_generation)
SELECT id,1,'accessibility','AXText','retained',1 FROM n;
UPDATE _bulk_element_rows SET text=hex(zeroblob(1048576)) WHERE id=1000000;
ANALYZE;
DELETE FROM sqlite_stat1;
ANALYZE sqlite_schema",
)
.await
.unwrap();
let mut baseline_tx = db.begin_immediate_with_retry().await.unwrap();
sqlx::query("DROP INDEX _bulk_element_resident_tombstones")
.execute(&mut **baseline_tx.conn())
.await
.unwrap();
let baseline_plan: Vec<String> = sqlx::query(
"EXPLAIN QUERY PLAN DELETE FROM _bulk_element_rows
WHERE _archive_deleted=1 AND _archive_file IS NULL",
)
.fetch_all(&mut **baseline_tx.conn())
.await
.unwrap()
.into_iter()
.map(|row| row.try_get(3).unwrap())
.collect();
baseline_tx.commit().await.unwrap();
assert!(baseline_plan
.iter()
.any(|line| line == "SCAN _bulk_element_rows"));
let baseline_steps = Box::new(AtomicU64::new(0));
let mut baseline_tx = db.begin_immediate_with_retry().await.unwrap();
{
let mut handle = baseline_tx.conn().lock_handle().await.unwrap();
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
1000,
Some(count_steps),
(&*baseline_steps as *const AtomicU64).cast_mut().cast(),
);
}
}
let baseline_started = std::time::Instant::now();
sqlx::query(
"DELETE FROM _bulk_element_rows
WHERE _archive_deleted=1 AND _archive_file IS NULL",
)
.execute(&mut **baseline_tx.conn())
.await
.unwrap();
let baseline_elapsed = baseline_started.elapsed();
let baseline_steps = baseline_steps.load(Ordering::Relaxed);
{
let mut handle = baseline_tx.conn().lock_handle().await.unwrap();
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
0,
None,
std::ptr::null_mut(),
);
}
}
baseline_tx.rollback().await.unwrap();
assert!(
baseline_steps >= 2_000_000,
"baseline unexpectedly avoided its full scan: {baseline_steps}"
);
db.close().await;
// Startup must add the index even though this installed database already
// has the current versioned recording migration, and repeated reopen is
// idempotent.
let db = Arc::new(
DatabaseManager::new(path.to_str().unwrap(), Default::default())
.await
.unwrap(),
);
assert!(sqlx::query_scalar::<_, bool>(
"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE name='_bulk_element_resident_tombstones')"
)
.fetch_one(&db.pool)
.await
.unwrap());
let missing_stats_plan: Vec<String> = sqlx::query(sqlx::AssertSqlSafe(format!(
"EXPLAIN QUERY PLAN {}",
elements::cleanup_sql()
)))
.fetch_all(&db.pool)
.await
.unwrap()
.into_iter()
.map(|row| row.try_get(3).unwrap())
.collect();
assert!(missing_stats_plan.iter().all(|line| {
!line.contains("SCAN _bulk_element_rows") || line.contains(elements::TOMBSTONE_INDEX)
}));
db.execute_raw_sql_write(&format!(
"ANALYZE;
UPDATE sqlite_stat1 SET stat='30 30' WHERE tbl='_bulk_element_rows';
UPDATE sqlite_stat1 SET stat='1000000000 1' WHERE idx='{}';
ANALYZE sqlite_schema;
INSERT INTO frames(id,timestamp,full_text) VALUES(1,'2026-10-16','retained');
INSERT INTO _bulk_files(id,table_name,path,checksum,state,rows)
VALUES(1,'elements','preserved.parquet','test','published',1);
INSERT INTO _bulk_element_rows(id,frame_id,source,role,text,_archive_generation,_archive_deleted,_archive_file)
VALUES(1000001,1,'accessibility','AXText','archived tombstone',1,1,1);
WITH RECURSIVE n(id) AS (VALUES(1000002) UNION ALL SELECT id+1 FROM n WHERE id<{})
INSERT INTO _bulk_element_rows(id,frame_id,source,role,text,_archive_generation,_archive_deleted)
SELECT id,1,'accessibility','AXText','resident tombstone',1,1 FROM n",
elements::TOMBSTONE_INDEX,
1_000_001 + FILE_ROWS as i64 + 1
)).await.unwrap();
let fixed_plan: Vec<String> = sqlx::query(sqlx::AssertSqlSafe(format!(
"EXPLAIN QUERY PLAN {}",
elements::cleanup_sql()
)))
.fetch_all(&db.pool)
.await
.unwrap()
.into_iter()
.map(|row| row.try_get(3).unwrap())
.collect();
assert!(fixed_plan.iter().all(|line| {
!line.contains("SCAN _bulk_element_rows") || line.contains(elements::TOMBSTONE_INDEX)
}));
assert!(fixed_plan
.iter()
.filter(|line| line.contains("_bulk_element_rows"))
.all(|line| line.contains(elements::TOMBSTONE_INDEX)));
let storage = db.storage.as_ref().unwrap().clone();
assert!(storage.descriptor.budget.record_bytes < 2_097_152);
let index_path = storage.root.join(&storage.descriptor.index);
let pool = pool_options(Some(storage.clone()), false)
.max_connections(1)
.connect_with(sqlx::sqlite::SqliteConnectOptions::new().filename(&index_path))
.await
.unwrap();
let writer = screenpipe_sqlite_coordinator::SqliteWritePool::new(
pool.clone(),
screenpipe_sqlite_coordinator::sqlite_write_lock(&index_path),
);
let steps = Box::new(AtomicU64::new(0));
{
let mut conn = pool.acquire().await.unwrap();
let mut handle = conn.lock_handle().await.unwrap();
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
1000,
Some(count_steps),
(&*steps as *const AtomicU64).cast_mut().cast(),
);
}
}
let fixed_backlog_started = std::time::Instant::now();
let (cleanup, captured) = tokio::join!(
elements::reclaim(&storage, &pool, &writer),
db.insert_snapshot_frame(
"display",
chrono::Utc::now(),
"",
Some("test"),
None,
None,
true,
Some("test"),
Some("durable capture"),
Some("accessibility"),
None,
None,
None,
)
);
cleanup.unwrap();
let fixed_backlog_elapsed = fixed_backlog_started.elapsed();
let captured = captured.unwrap();
let fixed_steps = steps.load(Ordering::Relaxed);
assert_eq!(sqlx::query_scalar::<_, i64>("SELECT count(*) FROM _bulk_element_rows WHERE _archive_deleted=1 AND _archive_file IS NULL").fetch_one(&pool).await.unwrap(), 1);
assert_eq!(
sqlx::query_scalar::<_, i64>(
"SELECT count(*) FROM _bulk_element_rows WHERE id=1000001 AND _archive_file=1"
)
.fetch_one(&pool)
.await
.unwrap(),
1
);
// The archive row is only a predicate fixture. Remove its fake catalog
// metadata before startup validation, while leaving the resident backlog.
db.execute_raw_sql_write(
"DELETE FROM _bulk_element_rows WHERE id=1000001; DELETE FROM _bulk_files WHERE id=1",
)
.await
.unwrap();
pool.close().await;
drop(writer);
drop(storage);
db.close().await;
let reopened = Arc::new(
DatabaseManager::new(path.to_str().unwrap(), Default::default())
.await
.unwrap(),
);
let payload = reopened
.frame_payloads(&[captured], crate::storage::Projection::All)
.await
.unwrap()
.remove(&captured)
.unwrap();
assert_eq!(
payload.accessibility_text.as_deref(),
Some("durable capture")
);
assert_eq!(
sqlx::query_scalar::<_, i64>("SELECT count(*) FROM frames WHERE id=?")
.bind(captured)
.fetch_one(&reopened.pool)
.await
.unwrap(),
1
);
let storage = reopened.storage.as_ref().unwrap().clone();
let index_path = storage.root.join(&storage.descriptor.index);
let pool = pool_options(Some(storage.clone()), false)
.max_connections(1)
.connect_with(sqlx::sqlite::SqliteConnectOptions::new().filename(&index_path))
.await
.unwrap();
let writer = screenpipe_sqlite_coordinator::SqliteWritePool::new(
pool.clone(),
screenpipe_sqlite_coordinator::sqlite_write_lock(&index_path),
);
elements::reclaim(&storage, &pool, &writer).await.unwrap();
assert_eq!(sqlx::query_scalar::<_, i64>("SELECT count(*) FROM _bulk_element_rows WHERE _archive_deleted=1 AND _archive_file IS NULL").fetch_one(&pool).await.unwrap(), 0);
assert_eq!(
sqlx::query_scalar::<_, i64>(
"SELECT count(*) FROM _bulk_element_rows WHERE _archive_deleted=0"
)
.fetch_one(&pool)
.await
.unwrap(),
1_000_000
);
assert_eq!(
sqlx::query_scalar::<_, i64>(
"SELECT length(text) FROM _bulk_element_rows WHERE id=1000000"
)
.fetch_one(&pool)
.await
.unwrap(),
2_097_152
);
let fixed_zero_steps = Box::new(AtomicU64::new(0));
{
let mut conn = pool.acquire().await.unwrap();
let mut handle = conn.lock_handle().await.unwrap();
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
1000,
Some(count_steps),
(&*fixed_zero_steps as *const AtomicU64).cast_mut().cast(),
);
}
}
let fixed_zero_started = std::time::Instant::now();
elements::reclaim(&storage, &pool, &writer).await.unwrap();
let fixed_zero_elapsed = fixed_zero_started.elapsed();
let fixed_zero_steps = fixed_zero_steps.load(Ordering::Relaxed);
assert!(fixed_zero_steps < baseline_steps / 10);
eprintln!("resident cleanup: baseline_plan={baseline_plan:?}; baseline_steps~={baseline_steps}; baseline_elapsed={baseline_elapsed:?}; missing_stats_plan={missing_stats_plan:?}; fixed_plan={fixed_plan:?}; fixed_backlog_steps~={fixed_steps}; fixed_backlog_elapsed={fixed_backlog_elapsed:?}; fixed_zero_steps~={fixed_zero_steps}; fixed_zero_elapsed={fixed_zero_elapsed:?}");
pool.close().await;
reopened.close().await;
}
#[tokio::test]
async fn offline_selection_seeks_past_retained_history() {
use std::sync::atomic::AtomicU64;
unsafe extern "C" fn count_steps(context: *mut std::ffi::c_void) -> i32 {
let steps = unsafe { &*context.cast::<AtomicU64>() };
// Abort a regressed scan instead of letting large fixtures hang CI.
i32::from(steps.fetch_add(1000, Ordering::Relaxed) > 100_000)
}
let root = tempfile::tempdir().unwrap();
let mut options = crate::storage::MigrationOptions::default();
options.privacy.identity = "migration-cursor-test".into();
options.privacy.required_surfaces = 1;
let db = DatabaseManager::new_hybrid(root.path(), Default::default(), options)
.await
.unwrap();
db.execute_raw_sql_write(
"INSERT INTO frames(id,timestamp,full_text) VALUES(1,'2026-09-19','private frame')",
)
.await
.unwrap();
let count = 200_000_i64;
let mut tx = db.begin_immediate_with_retry().await.unwrap();
// Keep a large privacy-pending prefix. This models an interrupted migration
// after recording recovery has restored all elements to the resident table.
sqlx::query("WITH RECURSIVE n(id) AS (VALUES(1) UNION ALL SELECT id+1 FROM n WHERE id<?) INSERT INTO _bulk_element_rows(id,frame_id,source,role,text,_archive_generation) SELECT id,1,'accessibility','AXText','private',1 FROM n")
.bind(count).execute(&mut **tx.conn()).await.unwrap();
sqlx::query("WITH RECURSIVE n(id) AS (VALUES(1) UNION ALL SELECT id+1 FROM n WHERE id<?) INSERT INTO ui_events(id,timestamp,event_type,text_content) SELECT id,'2026-09-19','text','private' FROM n")
.bind(count).execute(&mut **tx.conn()).await.unwrap();
// In-place migration deliberately postpones these indexes until completion.
sqlx::query("DROP INDEX _bulk_ui_events_pending")
.execute(&mut **tx.conn())
.await
.unwrap();
tx.commit().await.unwrap();
db.execute_raw_sql_write("INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(200001,1,'accessibility','AXText','eligible',1); INSERT INTO ui_events(id,timestamp,event_type,text_content,redacted_at) VALUES(200001,'2026-09-19','text','eligible',1)").await.unwrap();
let storage = db.storage.as_ref().unwrap();
let index = storage.root.join(&storage.descriptor.index);
let pool = pool_options(Some(storage.clone()), false)
.max_connections(1)
.connect_with(sqlx::sqlite::SqliteConnectOptions::new().filename(&index))
.await
.unwrap();
let writer = screenpipe_sqlite_coordinator::SqliteWritePool::new(
pool.clone(),
screenpipe_sqlite_coordinator::sqlite_write_lock(&index),
);
let steps = Box::new(AtomicU64::new(0));
{
let mut conn = pool.acquire().await.unwrap();
let mut handle = conn.lock_handle().await.unwrap();
// Context remains alive until the callback is removed below.
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
1000,
Some(count_steps),
(&*steps as *const AtomicU64).cast_mut().cast(),
);
}
}
let bulk = storage
.select_bulk(&pool, &TABLES[2], None, Some(count))
.await;
let elements = elements::seal_after(storage, &pool, &writer, Some(count)).await;
let work = steps.load(Ordering::Relaxed);
{
let mut conn = pool.acquire().await.unwrap();
let mut handle = conn.lock_handle().await.unwrap();
unsafe {
libsqlite3_sys::sqlite3_progress_handler(
handle.as_raw_handle().as_ptr(),
0,
None,
std::ptr::null_mut(),
);
}
}
pool.close().await;
let bulk = bulk
.unwrap_or_else(|error| panic!("bulk scan exceeded work budget at {work} steps: {error}"));
assert_eq!(
bulk.iter().map(|r| r.id).collect::<Vec<_>>(),
vec![count + 1]
);
assert_eq!(
elements.unwrap_or_else(|error| panic!(
"element scan exceeded work budget at {work} steps: {error}"
)),
(1, Some(count + 1))
);
assert!(
work < 100_000,
"selection revisited retained history: {work}"
);
eprintln!("offline cursor: retained_rows_per_table={count}, sqlite_steps={work}");
assert_eq!(
sqlx::query_scalar::<_, i64>("SELECT count(*) FROM _bulk_element_rows")
.fetch_one(&db.pool)
.await
.unwrap(),
count
);
db.close().await;
}
#[tokio::test]
async fn element_publication_ignores_new_captures_but_rejects_changed_range_members() {
for archived in [false, true] {
for mutation in [
"INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(30,1,'accessibility','AXText','new capture',1)",
"UPDATE elements SET text='edited while encoding' WHERE id=10",
"DELETE FROM elements WHERE id=10",
"INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(15,1,'accessibility','AXText','inserted gap',1)",
"INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(15,1,'accessibility','AXText','private gap',NULL)",
"UPDATE elements SET redacted_at=NULL WHERE id=10",
"DELETE FROM elements WHERE id=10; INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(10,1,'accessibility','AXText','reused ID',1)",
] {
let root = tempfile::tempdir().unwrap();
let db = Arc::new(DatabaseManager::new_hybrid(root.path(), Default::default(),
crate::storage::MigrationOptions {
privacy: crate::storage::PrivacyPolicy { identity: "private".into(), required_surfaces: 1 },
..Default::default()
}).await.unwrap());
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp,full_text) VALUES(1,'2026-09-17','pending frame'); INSERT INTO elements(id,frame_id,source,role,text,redacted_at) VALUES(10,1,'accessibility','AXText','ten',1),(20,1,'accessibility','AXText','twenty',1)").await.unwrap();
if archived {
assert_eq!(db.seal_payloads().await.unwrap(), 2);
db.execute_raw_sql_write("UPDATE elements SET text='restaged ten' WHERE id=10").await.unwrap();
}
let storage = db.storage.as_ref().unwrap();
let encoded = Arc::new(tokio::sync::Notify::new());
let announced = encoded.clone();
let pause = Pause::new();
let held = Pause(pause.0.clone());
*storage.bulk.element_publish_hook.lock().unwrap() = Some(Arc::new(move || {
announced.notify_one();
held.wait();
}));
let sealing = db.clone();
let task = tokio::spawn(async move { sealing.seal_payloads().await });
tokio::time::timeout(Duration::from_secs(10), encoded.notified()).await.unwrap();
// The archive is fully encoded while the writer remains available.
db.execute_raw_sql_write(mutation).await.unwrap();
let expected = db.query_raw_sql("SELECT id,text,redacted_at FROM elements ORDER BY id").await.unwrap();
pause.release();
let count = task.await.unwrap().unwrap();
assert_eq!(count, if mutation.contains("VALUES(30,") || (!archived && mutation.contains("private gap")) { 2 } else { 0 }, "archived={archived}; {mutation}");
*storage.bulk.element_publish_hook.lock().unwrap() = None;
assert_eq!(db.query_raw_sql("SELECT id,text,redacted_at FROM elements ORDER BY id").await.unwrap(), expected);
while db.seal_payloads().await.unwrap() != 0 {}
db.verify_storage().await.unwrap();
assert_eq!(db.query_raw_sql("SELECT id,text,redacted_at FROM elements ORDER BY id").await.unwrap(), expected);
db.close().await;
}
}
}
async fn fixture_mode(hybrid: bool) -> (tempfile::TempDir, Arc<DatabaseManager>) {
let root = tempfile::tempdir().unwrap();
let db = Arc::new(
if hybrid {
DatabaseManager::new_hybrid(root.path(), Default::default(), Default::default()).await
} else {
DatabaseManager::new(
root.path().join("db.sqlite").to_str().unwrap(),
Default::default(),
)
.await
}
.unwrap(),
);
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp,full_text) VALUES(1,'2026-09-12','frame'); INSERT INTO elements(id,frame_id,source,role,text) VALUES(1,1,'accessibility','AXText','original'); INSERT INTO audio_chunks(id,file_path) VALUES(1,'test.wav'); INSERT INTO audio_transcriptions(id,audio_chunk_id,offset_index,timestamp,transcription,device) VALUES(1,1,0,'2026-09-12','audio original','test');").await.unwrap();
if hybrid {
while db.seal_payloads().await.unwrap() != 0 {}
}
(root, db)
}
async fn fixture() -> (tempfile::TempDir, Arc<DatabaseManager>) {
fixture_mode(true).await
}
#[tokio::test]
async fn element_scans_decode_each_file_once_and_point_reads_stay_selective() {
let root = tempfile::tempdir().unwrap();
let db = DatabaseManager::new_hybrid(root.path(), Default::default(), Default::default())
.await
.unwrap();
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp) VALUES(1,'2026-09-15'),(2,'2026-09-15'),(3,'2026-09-15'); INSERT INTO elements(id,frame_id,source,role,text) VALUES(1,1,'accessibility','AXText','one'),(2,2,'accessibility','AXText','two'),(3,3,'accessibility','AXText','three');").await.unwrap();
while db.seal_payloads().await.unwrap() != 0 {}
let storage = db.storage.as_ref().unwrap();
let paths: Vec<String> =
sqlx::query_scalar("SELECT path FROM _bulk_files WHERE table_name='elements'")
.fetch_all(&db.pool)
.await
.unwrap();
assert_eq!(paths.len(), 1);
storage.bulk.cache.retire(&paths).unwrap();
let decoded = Arc::new(Mutex::new(Vec::<String>::new()));
let observed = decoded.clone();
*storage.bulk.decode_hook.lock().unwrap() = Some(Arc::new(move |key| {
observed.lock().unwrap().push(key.to_owned());
}));
// Migration receipts use ID windows across all frames, including multiple
// windows in one file. A frame projection must not be decoded per window.
for (id, expected) in [(1, "one"), (2, "two"), (3, "three")] {
let actual: String =
sqlx::query_scalar("SELECT text FROM elements WHERE id>=? ORDER BY id LIMIT 1")
.bind(id)
.fetch_one(&db.pool)
.await
.unwrap();
assert_eq!(actual, expected);
}
{
let keys = decoded.lock().unwrap();
assert_eq!(keys.iter().filter(|k| k.starts_with("records:")).count(), 1);
assert!(!keys.iter().any(|k| k.starts_with("element-frame:")));
}
for sql in [
"SELECT text FROM elements WHERE id=2",
"SELECT text FROM elements WHERE frame_id=2",
] {
storage.bulk.cache.retire(&paths).unwrap();
decoded.lock().unwrap().clear();
let actual: String = sqlx::query_scalar(sql).fetch_one(&db.pool).await.unwrap();
assert_eq!(actual, "two");
let keys = decoded.lock().unwrap();
assert_eq!(
keys.iter()
.filter(|k| k.starts_with("element-frame:"))
.count(),
1
);
assert!(!keys.iter().any(|k| k.starts_with("records:")));
}
db.close().await;
}
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn cache_hits_and_unrelated_readers_progress_during_a_decode() {
let (_root, db) = fixture().await;
let storage = db.storage.as_ref().unwrap();
let element_path: String =
sqlx::query_scalar("SELECT path FROM _bulk_files WHERE table_name='elements'")
.fetch_one(&db.pool)
.await
.unwrap();
let expected: String =
sqlx::query_scalar("SELECT transcription FROM audio_transcriptions WHERE id=1")
.fetch_one(&db.pool)
.await
.unwrap();
// Force only the element file cold; the audio cache hit must remain usable.
storage.bulk.cache.retire(&[element_path.clone()]).unwrap();
let capacity = storage.decoder.clone().acquire_owned().await.unwrap();
let pause = Pause::new();
let hold = pause.0.clone();
let (started, ready) = tokio::sync::oneshot::channel();
let started = Mutex::new(Some(started));
let calls = Arc::new(AtomicUsize::new(0));
let counter = calls.clone();
*storage.bulk.decode_hook.lock().unwrap() = Some(Arc::new(move |key| {
if key.starts_with("element-frame:") && key.contains(&element_path) {
counter.fetch_add(1, Ordering::SeqCst);
if let Some(started) = started.lock().unwrap().take() {
let _ = started.send(());
}
Pause(hold.clone()).wait();
}
}));
let reader = db.clone();
let reading = tokio::spawn(async move {
sqlx::query_scalar::<_, String>("SELECT text FROM elements WHERE id=1")
.fetch_one(&reader.pool)
.await
});
tokio::time::timeout(Duration::from_secs(2), ready)
.await
.unwrap()
.unwrap();
assert_eq!(storage.decoder.available_permits(), 0);
let hit = tokio::time::timeout(
Duration::from_secs(1),
sqlx::query_scalar::<_, String>(
"SELECT transcription FROM audio_transcriptions WHERE id=1",
)
.fetch_one(&db.pool),
)
.await;
let write = tokio::time::timeout(
Duration::from_secs(1),
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp,full_text) VALUES(2,'2026-09-12','concurrent capture')"),
)
.await;
pause.release();
assert_eq!(reading.await.unwrap().unwrap(), "original");
assert_eq!(
hit.expect("cached reader was blocked by an unrelated decoder")
.unwrap(),
expected
);
write.unwrap().unwrap();
assert_eq!(calls.load(Ordering::SeqCst), 1);
drop(capacity);
db.close().await;
}
#[test]
fn parity_scan_completes_across_many_cold_element_files() {
use crate::storage::parity::{hash_cell, scan};
use rusqlite::types::ValueRef;
use sha2::{Digest, Sha256};
// A regressed blocking scan cannot be aborted by Tokio. Shut the test
// runtime down with a timeout so the regression fails instead of hanging CI.
let runtime = tokio::runtime::Builder::new_current_thread()
.enable_all()
.build()
.unwrap();
let (expected, result) = runtime.block_on(async {
let root = tempfile::tempdir().unwrap();
let db = DatabaseManager::new_hybrid(root.path(), Default::default(), Default::default())
.await
.unwrap();
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp) VALUES(1,'2026-09-22')")
.await
.unwrap();
let mut expected = Sha256::new();
// Two cold projections per file cross Tokio's cooperative budget in
// one synchronous SQLite statement, as migration verification does.
for id in 1..=96 {
db.execute_raw_sql_write(&format!("INSERT INTO elements(id,frame_id,source,role,text) VALUES({id},1,'accessibility','AXText','history {id}')"))
.await.unwrap();
while db.seal_payloads().await.unwrap() != 0 {}
hash_cell(&mut expected, ValueRef::Integer(id));
hash_cell(
&mut expected,
ValueRef::Text(format!("history {id}").as_bytes()),
);
expected.update(b"E");
}
let paths: Vec<String> =
sqlx::query_scalar("SELECT path FROM _bulk_files WHERE table_name='elements'")
.fetch_all(&db.pool)
.await
.unwrap();
assert_eq!(paths.len(), 96);
db.storage
.as_ref()
.unwrap()
.bulk
.cache
.retire(&paths)
.unwrap();
let result = tokio::time::timeout(
Duration::from_secs(10),
scan(
db.pool.clone(),
"elements".into(),
"SELECT id,id,text FROM elements ORDER BY id".into(),
),
)
.await;
if result.is_ok() {
db.close().await;
}
(format!("{:x}", expected.finalize()), result)
});
runtime.shutdown_timeout(Duration::from_secs(1));
let receipt = result
.expect("parity scan stalled while decoding cold element files")
.unwrap();
assert_eq!(receipt.rows, 96);
assert_eq!(receipt.sha256, expected);
}
#[tokio::test]
async fn sql_readers_start_during_file_unlink_without_interrupts() {
let (_root, db) = fixture().await;
let storage = db.storage.as_ref().unwrap();
// Reclamation has passed its reader cutoff. New SQLite snapshots can only
// see current locators, so they can read while obsolete files are unlinked.
let unlink = storage.leases.clone().write_owned().await;
let result = sqlx::query_scalar::<_, String>("SELECT text FROM elements WHERE id=1")
.fetch_one(&db.pool)
.await;
drop(unlink);
assert_eq!(result.unwrap(), "original");
db.close().await;
}
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn cold_files_decode_concurrently_with_shared_bounded_admission() {
let (_root, db) = fixture().await;
let storage = db.storage.as_ref().unwrap();
let paths: Vec<String> = sqlx::query_scalar("SELECT path FROM _bulk_files")
.fetch_all(&db.pool)
.await
.unwrap();
storage.bulk.cache.retire(&paths).unwrap();
let pause = Pause::new();
let hold = pause.0.clone();
let (started, mut ready) = tokio::sync::mpsc::unbounded_channel();
let calls = Arc::new(AtomicUsize::new(0));
let counter = calls.clone();
*storage.bulk.decode_hook.lock().unwrap() = Some(Arc::new(move |key| {
if key.starts_with("records:") || key.starts_with("element-frame:") {
counter.fetch_add(1, Ordering::SeqCst);
started.send(()).unwrap();
Pause(hold.clone()).wait();
}
}));
let mut reads = Vec::new();
for sql in [
"SELECT text FROM elements WHERE id=1",
"SELECT transcription FROM audio_transcriptions WHERE id=1",
"SELECT text FROM elements WHERE id=1",
] {
let db = db.clone();
reads.push(tokio::spawn(async move {
sqlx::query_scalar::<_, String>(sql)
.fetch_one(&db.pool)
.await
}));
}
for _ in 0..2 {
tokio::time::timeout(Duration::from_secs(2), ready.recv())
.await
.expect("independent cold files did not decode concurrently")
.unwrap();
}
assert_eq!(storage.decoder.available_permits(), 0);
tokio::time::timeout(Duration::from_secs(2), async {
while storage.bulk.cache.max_in_flight_readers() < 2 {
tokio::task::yield_now().await;
}
})
.await
.expect("same-file reader did not join the pending decode");
// Frame payloads use the same two decoder slots. Cancelling a queued
// frame read must leave these active bulk jobs and their leases intact.
assert!(tokio::time::timeout(
Duration::from_millis(100),
db.frame_payloads(&[1], crate::storage::Projection::All)
)
.await
.is_err());
assert_eq!(calls.load(Ordering::SeqCst), 2);
pause.release();
for (read, expected) in reads
.into_iter()
.zip(["original", "audio original", "original"])
{
assert_eq!(read.await.unwrap().unwrap(), expected);
}
assert_eq!(
calls.load(Ordering::SeqCst),
2,
"same-file readers must share the decode"
);
// Join the already-dispatched frame decode before checking its permit.
assert_eq!(
db.frame_payloads(&[1], crate::storage::Projection::All)
.await
.unwrap()[&1]
.full_text
.as_deref(),
Some("frame")
);
assert_eq!(storage.decoder.available_permits(), 2);
db.close().await;
}
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn snapshots_and_streams_match_sqlite_while_writing_and_reclaiming() {
use futures::TryStreamExt;
for hybrid in [false, true] {
let (root, db) = fixture_mode(hybrid).await;
let originals: Vec<String> = if hybrid {
sqlx::query_scalar("SELECT path FROM _bulk_files")
.fetch_all(&db.pool)
.await
.unwrap()
} else {
vec![]
};
let mut snapshot = db.pool.begin().await.unwrap();
// Establish the snapshot before requesting any archived column.
let _: i64 = sqlx::query_scalar("SELECT count(*) FROM frames")
.fetch_one(&mut *snapshot)
.await
.unwrap();
let mut connection = db.pool.acquire().await.unwrap();
let mut stream = sqlx::query_scalar::<_, String>("WITH RECURSIVE seq(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM seq WHERE n<1000) SELECT e.text FROM seq CROSS JOIN elements e WHERE e.id=1").fetch(&mut *connection);
assert_eq!(
stream.try_next().await.unwrap().as_deref(),
Some("original")
);
tokio::time::timeout(Duration::from_secs(2), db.execute_raw_sql_write("UPDATE elements SET text='replacement' WHERE id=1; UPDATE audio_transcriptions SET transcription='audio replacement' WHERE id=1;")).await.unwrap().unwrap();
assert_eq!(
sqlx::query_scalar::<_, String>("SELECT text FROM elements WHERE id=1")
.fetch_one(&db.pool)
.await
.unwrap(),
"replacement"
);
if hybrid {
for _ in 0..3 {
db.reclaim_frame_payloads().await.unwrap();
}
assert!(originals.iter().all(|p| root.path().join(p).exists()));
db.storage
.as_ref()
.unwrap()
.bulk
.cache
.retire(&originals)
.unwrap();
}
assert_eq!(
sqlx::query_scalar::<_, String>("SELECT text FROM elements WHERE id=1")
.fetch_one(&mut *snapshot)
.await
.unwrap(),
"original"
);
assert_eq!(
sqlx::query_scalar::<_, String>(
"SELECT transcription FROM audio_transcriptions WHERE id=1"
)
.fetch_one(&mut *snapshot)
.await
.unwrap(),
"audio original"
);
snapshot.rollback().await.unwrap();
if hybrid {
for _ in 0..3 {
db.reclaim_frame_payloads().await.unwrap();
}
assert!(
originals.iter().all(|p| root.path().join(p).exists()),
"stream must pin originals independently of transaction"
);
}
let mut rows = 1;
while let Some(text) = stream.try_next().await.unwrap() {
assert_eq!(text, "original");
rows += 1;
}
assert_eq!(rows, 1000);
drop(stream);
drop(connection);
if hybrid {
for _ in 0..3 {
db.reclaim_frame_payloads().await.unwrap();
}
assert!(originals.iter().all(|p| !root.path().join(p).exists()));
}
db.verify_storage().await.unwrap();
db.close().await;
}
}
#[tokio::test(flavor = "multi_thread", worker_threads = 4)]
async fn shutdown_releases_sql_workers_waiting_for_decoder_admission() {
let (_root, db) = fixture().await;
let storage = db.storage.as_ref().unwrap();
let paths: Vec<String> = sqlx::query_scalar("SELECT path FROM _bulk_files")
.fetch_all(&db.pool)
.await
.unwrap();
storage.bulk.cache.retire(&paths).unwrap();
let capacity = storage.decoder.clone().acquire_many_owned(2).await.unwrap();
let reader = db.clone();
let reading = tokio::spawn(async move {
sqlx::query_scalar::<_, String>("SELECT text FROM elements WHERE id=1")
.fetch_one(&reader.pool)
.await
});
tokio::time::timeout(Duration::from_secs(2), async {
while storage.bulk.cache.max_in_flight_readers() == 0 {
tokio::task::yield_now().await;
}
})
.await
.unwrap();
let owner = db.clone();
let closing = tokio::spawn(async move { owner.close().await });
assert!(tokio::time::timeout(Duration::from_secs(2), reading)
.await
.unwrap()
.unwrap()
.is_err());
drop(capacity);
tokio::time::timeout(Duration::from_secs(2), closing)
.await
.unwrap()
.unwrap();
assert!(!storage.sql_readers_active());
}
#[tokio::test]
async fn resident_tombstone_cleanup_is_indexed_batched_and_upgraded_on_reopen_allows_pinned_readers(
) {
// Ordinary startup may share a WAL with an existing read snapshot. Index
// installation is safe there; an offline FULL checkpoint is not a startup
// prerequisite, including when the index is already installed.
for already_installed in [false, true] {
let root = tempfile::tempdir().unwrap();
let db = DatabaseManager::new_hybrid(root.path(), Default::default(), Default::default())
.await
.unwrap();
if !already_installed {
db.execute_raw_sql_write(&format!("DROP INDEX {}", elements::TOMBSTONE_INDEX))
.await
.unwrap();
}
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp,full_text) VALUES(1,'2026-10-16','first durable frame')")
.await.unwrap();
let mut reader = db.pool.begin().await.unwrap();
assert_eq!(
sqlx::query_scalar::<_, i64>("SELECT count(*) FROM frames")
.fetch_one(&mut *reader)
.await
.unwrap(),
1
);
db.execute_raw_sql_write("INSERT INTO frames(id,timestamp,full_text) VALUES(2,'2026-10-16','second durable frame')")
.await.unwrap();
let result = {
let writer = db.coordinated_writer();
let permit = writer.lock().await.unwrap();
let mut conn = permit.pool().acquire().await.unwrap();
let original_timeout: i64 = sqlx::query_scalar("PRAGMA busy_timeout")
.fetch_one(&mut *conn)
.await
.unwrap();
// A zero test-only busy timeout observes the pinned-reader result
// immediately; the fixture retains the reader until after the call.
sqlx::query("PRAGMA busy_timeout=0")
.execute(&mut *conn)
.await
.unwrap();
let result = elements::ensure_cleanup_index(&mut conn).await;
sqlx::query(sqlx::AssertSqlSafe(format!(
"PRAGMA busy_timeout={original_timeout}"
)))
.execute(&mut *conn)
.await
.unwrap();
result
};
reader.rollback().await.unwrap();
assert!(result.is_ok(),
"normal startup index installation must not require an offline FULL checkpoint while readers pin the WAL; already_installed={already_installed}, result={result:?}; retain construction checkpoints only in offline/bootstrap callers");
assert_eq!(
sqlx::query_scalar::<_, i64>("SELECT count(*) FROM frames")
.fetch_one(&db.pool)
.await
.unwrap(),
2
);
db.close().await;
}
}