bin/store.py

#!/usr/bin/env python3 """ store.py - the Recall store. One SQLite file, owned end to end. WHAT IS DELIBERATELY ABSENT --------------------------- * no sync_outbox, sync_state, sync_dead_letter - there is no cloud leg, so there is no queue that a config flip could drain somewhere * no origin_device_id / install_id - nothing here correlates this box to a vendor's analytics * no resident vector daemon - the predecessor kept a 1.1GB vector process pinned at 19% CPU to serve search that FTS5 does in-process for free Search is SQLite FTS5, which runs inside this process against this one file. If semantic search is ever added it belongs in this same file as blobs computed on demand - never as a resident process, and never as a network call. """ from __future__ import annotations import os import sqlite3 import sys from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parent)) def _default_db() -> Path: """Resolved per-platform by paths.py; FRIDAY_RECALL_DB still wins.""" from paths import db return db() DB_PATH = _default_db() SCHEMA_VERSION = 2 _SCHEMA = """ PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA foreign_keys=ON; CREATE TABLE IF NOT EXISTS meta ( k TEXT PRIMARY KEY, v TEXT ); CREATE TABLE IF NOT EXISTS sessions ( session_uid TEXT PRIMARY KEY, content_session_id TEXT, project TEXT NOT NULL, platform TEXT NOT NULL DEFAULT 'claude', title TEXT, opening_prompt TEXT, started_at TEXT NOT NULL, started_at_epoch INTEGER NOT NULL, ended_at TEXT, ended_at_epoch INTEGER, status TEXT NOT NULL DEFAULT 'active', prompt_count INTEGER DEFAULT 0, legacy_id INTEGER ); CREATE INDEX IF NOT EXISTS ix_sessions_epoch ON sessions(started_at_epoch DESC); CREATE INDEX IF NOT EXISTS ix_sessions_project ON sessions(project, started_at_epoch DESC); CREATE TABLE IF NOT EXISTS observations ( id INTEGER PRIMARY KEY AUTOINCREMENT, session_uid TEXT, project TEXT NOT NULL, type TEXT NOT NULL, title TEXT, subtitle TEXT, body TEXT, facts TEXT, narrative TEXT, concepts TEXT, files_read TEXT, files_modified TEXT, prompt_number INTEGER, model TEXT, agent_type TEXT, created_at TEXT NOT NULL, created_at_epoch INTEGER NOT NULL, redactions INTEGER DEFAULT 0, source TEXT NOT NULL DEFAULT 'transcript', legacy_id INTEGER ); CREATE INDEX IF NOT EXISTS ix_obs_epoch ON observations(created_at_epoch DESC); CREATE INDEX IF NOT EXISTS ix_obs_project ON observations(project, created_at_epoch DESC); CREATE INDEX IF NOT EXISTS ix_obs_session ON observations(session_uid); CREATE INDEX IF NOT EXISTS ix_obs_type ON observations(type); CREATE UNIQUE INDEX IF NOT EXISTS ux_obs_legacy ON observations(legacy_id) WHERE legacy_id IS NOT NULL; CREATE TABLE IF NOT EXISTS summaries ( id INTEGER PRIMARY KEY AUTOINCREMENT, session_uid TEXT, project TEXT NOT NULL, request TEXT, investigated TEXT, learned TEXT, completed TEXT, next_steps TEXT, files_read TEXT, files_edited TEXT, notes TEXT, prompt_number INTEGER, created_at TEXT NOT NULL, created_at_epoch INTEGER NOT NULL, redactions INTEGER DEFAULT 0, legacy_id INTEGER ); CREATE INDEX IF NOT EXISTS ix_sum_epoch ON summaries(created_at_epoch DESC); CREATE INDEX IF NOT EXISTS ix_sum_session ON summaries(session_uid); CREATE UNIQUE INDEX IF NOT EXISTS ux_sum_legacy ON summaries(legacy_id) WHERE legacy_id IS NOT NULL; CREATE TABLE IF NOT EXISTS prompts ( id INTEGER PRIMARY KEY AUTOINCREMENT, session_uid TEXT, project TEXT, prompt_number INTEGER, body TEXT NOT NULL, created_at TEXT NOT NULL, created_at_epoch INTEGER NOT NULL, redactions INTEGER DEFAULT 0, source TEXT NOT NULL DEFAULT 'transcript', legacy_id INTEGER ); CREATE INDEX IF NOT EXISTS ix_prompts_epoch ON prompts(created_at_epoch DESC); CREATE UNIQUE INDEX IF NOT EXISTS ux_prompts_legacy ON prompts(legacy_id) WHERE legacy_id IS NOT NULL; -- Full-text search. External-content tables so text is stored once. CREATE VIRTUAL TABLE IF NOT EXISTS observations_fts USING fts5( title, subtitle, body, facts, narrative, concepts, content='observations', content_rowid='id', tokenize='porter unicode61' ); CREATE VIRTUAL TABLE IF NOT EXISTS summaries_fts USING fts5( request, investigated, learned, completed, next_steps, notes, content='summaries', content_rowid='id', tokenize='porter unicode61' ); CREATE VIRTUAL TABLE IF NOT EXISTS prompts_fts USING fts5( body, content='prompts', content_rowid='id', tokenize='porter unicode61' ); CREATE TRIGGER IF NOT EXISTS trg_obs_ai AFTER INSERT ON observations BEGIN INSERT INTO observations_fts(rowid,title,subtitle,body,facts,narrative,concepts) VALUES (new.id,new.title,new.subtitle,new.body,new.facts,new.narrative,new.concepts); END; CREATE TRIGGER IF NOT EXISTS trg_obs_ad AFTER DELETE ON observations BEGIN INSERT INTO observations_fts(observations_fts,rowid,title,subtitle,body,facts,narrative,concepts) VALUES('delete',old.id,old.title,old.subtitle,old.body,old.facts,old.narrative,old.concepts); END; CREATE TRIGGER IF NOT EXISTS trg_obs_au AFTER UPDATE ON observations BEGIN INSERT INTO observations_fts(observations_fts,rowid,title,subtitle,body,facts,narrative,concepts) VALUES('delete',old.id,old.title,old.subtitle,old.body,old.facts,old.narrative,old.concepts); INSERT INTO observations_fts(rowid,title,subtitle,body,facts,narrative,concepts) VALUES (new.id,new.title,new.subtitle,new.body,new.facts,new.narrative,new.concepts); END; CREATE TRIGGER IF NOT EXISTS trg_sum_ai AFTER INSERT ON summaries BEGIN INSERT INTO summaries_fts(rowid,request,investigated,learned,completed,next_steps,notes) VALUES (new.id,new.request,new.investigated,new.learned,new.completed,new.next_steps,new.notes); END; CREATE TRIGGER IF NOT EXISTS trg_sum_ad AFTER DELETE ON summaries BEGIN INSERT INTO summaries_fts(summaries_fts,rowid,request,investigated,learned,completed,next_steps,notes) VALUES('delete',old.id,old.request,old.investigated,old.learned,old.completed,old.next_steps,old.notes); END; CREATE TRIGGER IF NOT EXISTS trg_prompt_ai AFTER INSERT ON prompts BEGIN INSERT INTO prompts_fts(rowid,body) VALUES (new.id,new.body); END; CREATE TRIGGER IF NOT EXISTS trg_prompt_ad AFTER DELETE ON prompts BEGIN INSERT INTO prompts_fts(prompts_fts,rowid,body) VALUES('delete',old.id,old.body); END; """ # Columns added after the first stores were created. CREATE TABLE IF NOT EXISTS # will not add them to an existing file, so they are applied explicitly. _ADD_COLUMNS = {} _UID_INDEX = "" def _migrate(con: sqlite3.Connection) -> None: """No-op. The standalone build ships one schema and no sync columns. The internal build carries uid / origin / ingested_at to reconcile several machines. None of that exists here: this build has no sync, no peers and no network, so the bookkeeping those columns exist for has nothing to do. """ return def connect(path: Path | str | None = None, readonly: bool = False) -> sqlite3.Connection: p = Path(path or DB_PATH) if readonly: if not p.exists(): # Point at commands that EXIST on the reader's machine. This used to # end with "recall-archive restore", which install.py does not create # and no entry point provides - so the first thing a stranger was told # to run on their very first error was a command that is not there. raise SystemExit( f"recall: no store yet at {p}\n" f"\n" f" If you have not finished installing:\n" f" python3 {Path(__file__).resolve().parent.parent / 'install.py'}\n" f"\n" f" If you only want the database, without wiring anything up:\n" f" python3 {Path(__file__).resolve()}\n" f"\n" f" Then bring in the history already on this disk:\n" f" recall import") con = sqlite3.connect(f"file:{p}?mode=ro", uri=True, timeout=10) else: p.parent.mkdir(parents=True, exist_ok=True) con = sqlite3.connect(p, timeout=30) con.executescript(_SCHEMA) _migrate(con) con.execute("INSERT OR REPLACE INTO meta(k,v) VALUES('schema_version',?)", (str(SCHEMA_VERSION),)) con.commit() con.row_factory = sqlite3.Row return con if __name__ == "__main__": c = connect() tabs = [r[0] for r in c.execute( "SELECT name FROM sqlite_master WHERE type IN ('table','view') ORDER BY 1")] print(f"recall store ready: {DB_PATH}") print("tables:", ", ".join(t for t in tabs if not t.startswith("sqlite_")))