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::{Table, TABLES};
use sqlx::{Connection, Row, SqliteConnection};
pub(crate) async fn bootstrap(conn: &mut SqliteConnection) -> Result<(), sqlx::Error> {
sqlx::raw_sql("CREATE TABLE _bulk_files(id INTEGER PRIMARY KEY,table_name TEXT NOT NULL,path TEXT NOT NULL UNIQUE,checksum TEXT,state TEXT NOT NULL CHECK(state IN ('encoding','published','dirty','retired')),rows INTEGER NOT NULL); CREATE TABLE _bulk_legacy_sql(kind TEXT NOT NULL,name TEXT PRIMARY KEY,sql TEXT NOT NULL);").execute(&mut *conn).await?;
for (table_index, table) in TABLES.iter().enumerate() {
bootstrap_table(conn, table_index, table, false).await?;
}
sqlx::query("INSERT INTO _hybrid_migrations VALUES(2,?)")
.bind(super::manifest_checksum())
.execute(&mut *conn)
.await?;
Ok(())
}
async fn bootstrap_table(
conn: &mut SqliteConnection,
table_index: usize,
table: &Table,
in_place: bool,
) -> Result<(), sqlx::Error> {
tracing::info!(table = table.name, "building bulk storage schema");
if table.name == "elements" {
super::elements::bootstrap_mode(conn, in_place).await?;
return Ok(());
}
// Column-aware triggers below own logical changes. Physical sealing
// updates archive locators without invalidating complete read results.
for event in ["INSERT", "UPDATE", "DELETE"] {
sqlx::raw_sql(sqlx::AssertSqlSafe(format!(
"DROP TRIGGER hybrid_revision_{}_{event}",
table.name
)))
.execute(&mut *conn)
.await?;
}
let original_columns: Vec<String> =
sqlx::query_scalar("SELECT name FROM pragma_table_info(?) ORDER BY cid")
.bind(table.name)
.fetch_all(&mut *conn)
.await?;
let legacy=sqlx::query("SELECT type,name,sql FROM sqlite_master WHERE (tbl_name=? AND type='trigger' AND instr(sql,?)>0) OR (type='table' AND name=?) OR (?='audio_transcriptions' AND name IN ('idx_audio_transcription_chunk_text','idx_audio_transcriptions_transcription'))")
.bind(table.name).bind(format!("{}_fts",table.name)).bind(format!("{}_fts",table.name)).bind(table.name).fetch_all(&mut *conn).await?;
for row in legacy {
let kind: &str = row.try_get("type")?;
let name: &str = row.try_get("name")?;
sqlx::query("INSERT INTO _bulk_legacy_sql VALUES(?,?,?)")
.bind(kind)
.bind(name)
.bind(row.try_get::<&str, _>("sql")?)
.execute(&mut *conn)
.await?;
sqlx::raw_sql(sqlx::AssertSqlSafe(format!(
"DROP {} \"{}\"",
kind,
name.replace('"', "\"\"")
)))
.execute(&mut *conn)
.await?;
}
sqlx::raw_sql(sqlx::AssertSqlSafe(format!("ALTER TABLE {t} ADD COLUMN _archive_file INTEGER REFERENCES _bulk_files(id); ALTER TABLE {t} ADD COLUMN _archive_mask INTEGER NOT NULL DEFAULT {mask}; ALTER TABLE {t} ADD COLUMN _archive_generation INTEGER NOT NULL DEFAULT 1; {indexes}",t=table.name,mask=table.mask(),indexes=if in_place {String::new()} else {indexes(table, false)}))).execute(&mut *conn).await?;
if table.name == "audio_transcriptions" {
sqlx::raw_sql(if in_place {"ALTER TABLE audio_transcriptions ADD COLUMN _archive_digest BLOB;"} else {"ALTER TABLE audio_transcriptions ADD COLUMN _archive_digest BLOB; CREATE UNIQUE INDEX _bulk_audio_identity ON audio_transcriptions(audio_chunk_id,CASE WHEN (_archive_mask&1)!=0 THEN screenpipe_payload_sha256(transcription) ELSE _archive_digest END);"}).execute(&mut *conn).await?;
}
let columns=original_columns.iter().map(|name|{
if let Some(index)=table.columns.iter().position(|c|c.name==name){
format!("CASE WHEN (e._archive_mask & {})!=0 THEN e.{name} ELSE screenpipe_bulk(p.path,p.checksum,{table_index},e.id,{index}) END AS {name}",1<<index)
} else {format!("e.\"{name}\"")}
}).collect::<Vec<_>>().join(",");
sqlx::raw_sql(sqlx::AssertSqlSafe(format!("CREATE VIEW {} AS SELECT {columns} FROM {} e LEFT JOIN _bulk_files p ON p.id=e._archive_file",table.view(),table.name))).execute(&mut *conn).await?;
if !table.fts.is_empty() {
sqlx::raw_sql(sqlx::AssertSqlSafe(format!("CREATE VIRTUAL TABLE {t}_fts USING fts5({schema},content='',contentless_delete=1,tokenize='unicode61'); {backfill}",t=table.name,schema=table.fts.join(","),backfill=if in_place {String::new()} else {format!("INSERT INTO {t}_fts(rowid,{columns}) SELECT id,{columns} FROM {t} WHERE {condition};",t=table.name,columns=table.fts_columns(),condition=table.fts_condition)}))).execute(&mut *conn).await?;
}
sqlx::query(sqlx::AssertSqlSafe(format!("UPDATE storage_metadata SET staging_bytes=staging_bytes+COALESCE((SELECT SUM({}) FROM {}),0)",table.local_bytes(""),table.name))).execute(&mut *conn).await?;
sqlx::raw_sql(sqlx::AssertSqlSafe(triggers(table, &original_columns)))
.execute(&mut *conn)
.await?;
crate::storage::schema::construction_checkpoint(conn).await?;
Ok(())
}
fn indexes(table: &Table, idempotent: bool) -> String {
let t = table.name;
let guard = if idempotent { "IF NOT EXISTS " } else { "" };
format!("CREATE INDEX {guard}_bulk_{t}_files ON {t}(_archive_file) WHERE _archive_file IS NOT NULL; CREATE INDEX {guard}_bulk_{t}_pending ON {t}(id) WHERE _archive_mask!=0;")
}
pub(crate) async fn bootstrap_in_place(conn: &mut SqliteConnection) -> Result<(), sqlx::Error> {
sqlx::raw_sql("CREATE TABLE IF NOT EXISTS _bulk_files(id INTEGER PRIMARY KEY,table_name TEXT NOT NULL,path TEXT NOT NULL UNIQUE,checksum TEXT,state TEXT NOT NULL CHECK(state IN ('encoding','published','dirty','retired')),rows INTEGER NOT NULL); CREATE TABLE IF NOT EXISTS _bulk_legacy_sql(kind TEXT NOT NULL,name TEXT PRIMARY KEY,sql TEXT NOT NULL);").execute(&mut *conn).await?;
for (index, table) in TABLES.iter().enumerate() {
let step = format!("bulk-schema-{}", table.name);
if crate::storage::schema::converted_step(conn, &step).await? {
continue;
}
let mut tx = conn.begin().await?;
bootstrap_table(&mut tx, index, table, true).await?;
crate::storage::schema::finish_step(&mut tx, &step).await?;
tx.commit().await?;
crate::storage::schema::construction_checkpoint(conn).await?;
crate::storage::faults::checkpoint("migration_schema_step");
}
sqlx::query("INSERT OR IGNORE INTO _hybrid_migrations VALUES(2,?)")
.bind(super::manifest_checksum())
.execute(conn)
.await?;
Ok(())
}
pub(crate) async fn finish_indexes(conn: &mut SqliteConnection) -> Result<(), sqlx::Error> {
for table in TABLES.iter().filter(|t| t.name != "elements") {
crate::storage::schema::construction_sql(conn, &indexes(table, true)).await?;
}
crate::storage::schema::construction_sql(conn, "CREATE UNIQUE INDEX IF NOT EXISTS _bulk_audio_identity ON audio_transcriptions(audio_chunk_id,CASE WHEN (_archive_mask&1)!=0 THEN screenpipe_payload_sha256(transcription) ELSE _archive_digest END);").await
}
fn reindex(table: &Table) -> String {
if table.fts.is_empty() {
return String::new();
}
format!("DELETE FROM {t}_fts WHERE rowid=NEW.id; INSERT INTO {t}_fts(rowid,{columns}) SELECT id,{columns} FROM {view} WHERE id=NEW.id AND ({condition});",t=table.name,columns=table.fts_columns(),view=table.view(),condition=table.fts_condition)
}
fn triggers(table: &Table, original_columns: &[String]) -> String {
let t = table.name;
let new_bytes = table.all_bytes("NEW.");
let old_bytes = table.local_bytes("OLD.");
let fts = reindex(table);
let fts_delete = if table.fts.is_empty() {
String::new()
} else {
format!("DELETE FROM {t}_fts WHERE rowid=OLD.id;")
};
let mut sql=format!("CREATE TRIGGER hybrid_bulk_{t}_id BEFORE UPDATE OF id ON {t} WHEN OLD._archive_file IS NOT NULL AND NEW.id!=OLD.id BEGIN SELECT RAISE(ABORT,'archived record IDs are immutable'); END; CREATE TRIGGER hybrid_bulk_{t}_insert AFTER INSERT ON {t} WHEN (SELECT maintenance=0 FROM storage_metadata) BEGIN UPDATE storage_metadata SET staging_bytes=staging_bytes+({new_bytes}),revision=revision+1; {fts} END; CREATE TRIGGER hybrid_bulk_{t}_delete BEFORE DELETE ON {t} BEGIN {fts_delete} UPDATE _bulk_files SET state='dirty' WHERE id=OLD._archive_file; UPDATE storage_metadata SET staging_bytes=staging_bytes-({old_bytes}),revision=revision+1; END;");
sql.push_str(&format!("CREATE TRIGGER hybrid_bulk_{t}_revision AFTER UPDATE OF {} ON {t} WHEN (SELECT maintenance=0 FROM storage_metadata) BEGIN UPDATE storage_metadata SET revision=revision+1; END;", original_columns.join(",")));
for (index, column) in table.columns.iter().enumerate() {
let c = column.name;
let bit = 1 << index;
sql.push_str(&format!("CREATE TRIGGER hybrid_bulk_{t}_{c} AFTER UPDATE OF {c} ON {t} WHEN (SELECT maintenance=0 FROM storage_metadata) BEGIN UPDATE {t} SET _archive_mask=_archive_mask|{bit},_archive_generation=_archive_generation+1 WHERE id=NEW.id; UPDATE _bulk_files SET state='dirty' WHERE id=OLD._archive_file; UPDATE storage_metadata SET staging_bytes=staging_bytes+COALESCE(length(CAST(NEW.{c} AS BLOB)),0)-CASE WHEN (OLD._archive_mask & {bit})!=0 THEN COALESCE(length(CAST(OLD.{c} AS BLOB)),0) ELSE 0 END; {fts} END;"));
}
let metadata = table
.fts
.iter()
.map(|c| c.split_whitespace().next().unwrap())
.filter(|name| !table.columns.iter().any(|c| c.name == *name))
.collect::<Vec<_>>();
if !metadata.is_empty() {
sql.push_str(&format!("CREATE TRIGGER hybrid_bulk_{t}_fts_metadata AFTER UPDATE OF {} ON {t} WHEN (SELECT maintenance=0 FROM storage_metadata) BEGIN {fts} END;",metadata.join(",")));
}
sql
}
// Called inside the startup schema transaction; preserve v2's identity while
// upgrading the installed triggers for existing as well as new generations.
pub(crate) async fn upgrade_recording(conn: &mut SqliteConnection) -> Result<(), sqlx::Error> {
for table in TABLES.iter().filter(|t| t.name != "elements") {
let columns: Vec<String> = sqlx::query_scalar(
"SELECT name FROM pragma_table_info(?) WHERE name NOT LIKE '_archive_%' ORDER BY cid",
)
.bind(table.name)
.fetch_all(&mut *conn)
.await?;
let names: Vec<String> = sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type='trigger' AND tbl_name=? AND name GLOB 'hybrid_bulk_*'")
.bind(table.name).fetch_all(&mut *conn).await?;
for name in names {
sqlx::raw_sql(sqlx::AssertSqlSafe(format!(
"DROP TRIGGER \"{}\"",
name.replace('"', "\"\"")
)))
.execute(&mut *conn)
.await?;
}
sqlx::raw_sql(sqlx::AssertSqlSafe(triggers(table, &columns)))
.execute(&mut *conn)
.await?;
}
Ok(())
}
pub(crate) async fn ensure_cleanup_indexes(
conn: &mut SqliteConnection,
) -> Result<bool, sqlx::Error> {
super::elements::ensure_cleanup_index(conn).await
}