Jj0UdZddlmZddlZddlmZddlmZddlm Z mZddl m Z dd d d Z d e d <GddZdgZy)zGSQLite storage foundation with versioned schema and explicit retention.) annotationsN)Iterator)contextmanager)UTCdatetime)Path) a CREATE TABLE tasks ( task_id TEXT PRIMARY KEY, handler TEXT NOT NULL, payload_json TEXT NOT NULL, status TEXT NOT NULL, result_json TEXT, error TEXT, attempts INTEGER NOT NULL DEFAULT 0, created_at REAL NOT NULL, started_at REAL, completed_at REAL ) zBCREATE INDEX tasks_status_created_idx ON tasks(status, created_at)a CREATE TABLE approval_requests ( request_id TEXT PRIMARY KEY, agent_json TEXT NOT NULL, action TEXT NOT NULL, resource TEXT, context_json TEXT NOT NULL, risk_score REAL NOT NULL, status TEXT NOT NULL, created_at REAL NOT NULL, reviewed_at REAL, reviewer TEXT, reason TEXT NOT NULL, decision TEXT, metadata_json TEXT NOT NULL ) zRCREATE INDEX approvals_status_created_idx ON approval_requests(status, created_at)a CREATE TABLE approval_records ( request_id TEXT PRIMARY KEY REFERENCES approval_requests(request_id) ON DELETE CASCADE, agent_id TEXT NOT NULL, action TEXT NOT NULL, resource TEXT, decision TEXT NOT NULL, reviewer TEXT NOT NULL, timestamp REAL NOT NULL, reason TEXT NOT NULL ) aH CREATE TABLE audit_events ( event_id TEXT PRIMARY KEY, event_type TEXT NOT NULL, timestamp REAL NOT NULL, actor TEXT NOT NULL, agent_id TEXT NOT NULL, task_id TEXT NOT NULL, model TEXT NOT NULL, tool TEXT NOT NULL, action TEXT NOT NULL, resource TEXT NOT NULL, policy_decision TEXT NOT NULL, approval_id TEXT NOT NULL, result TEXT NOT NULL, error TEXT NOT NULL, resource_usage_json TEXT NOT NULL, latency_ms REAL NOT NULL, network_action TEXT NOT NULL, memory_write TEXT NOT NULL, security_alert TEXT NOT NULL, metadata_json TEXT NOT NULL, status TEXT NOT NULL ) zDCREATE INDEX audit_task_time_idx ON audit_events(task_id, timestamp)zGCREATE INDEX audit_type_time_idx ON audit_events(event_type, timestamp)a^ CREATE TABLE project_memories ( memory_id TEXT PRIMARY KEY, project_id TEXT NOT NULL, content TEXT NOT NULL, trust_level TEXT NOT NULL, provenance_id TEXT, metadata_json TEXT NOT NULL, created_at REAL NOT NULL, expires_at REAL ) zRCREATE INDEX memories_project_time_idx ON project_memories(project_id, created_at)z@CREATE INDEX memories_trust_idx ON project_memories(trust_level)aF CREATE TABLE provenance_records ( content_id TEXT PRIMARY KEY, content_json TEXT NOT NULL, sha256 TEXT NOT NULL, metadata_json TEXT NOT NULL, labels_json TEXT NOT NULL, watermark_json TEXT, created_at REAL NOT NULL ) z CREATE TABLE schema_migrations ( version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL ) )a CREATE TABLE knowledge_documents ( document_id TEXT PRIMARY KEY, source_id TEXT NOT NULL, source_uri TEXT NOT NULL, content_sha256 TEXT NOT NULL, media_type TEXT NOT NULL, trust_level TEXT NOT NULL, sensitivity TEXT NOT NULL, status TEXT NOT NULL, metadata_json TEXT NOT NULL, created_at REAL NOT NULL, updated_at REAL NOT NULL, UNIQUE(source_id, content_sha256) ) zYCREATE INDEX knowledge_documents_source_idx ON knowledge_documents(source_id, updated_at)zVCREATE INDEX knowledge_documents_status_idx ON knowledge_documents(status, updated_at))z/ALTER TABLE audit_events ADD COLUMN seq INTEGERz2ALTER TABLE audit_events ADD COLUMN prev_hash TEXTz3ALTER TABLE audit_events ADD COLUMN entry_hash TEXTz1CREATE INDEX audit_chain_idx ON audit_events(seq))zdict[int, tuple[str, ...]] _MIGRATIONSceZdZdZeeZddddZeddZ eddZ ddZ ddZ dd Z d d dd Zdd ZddZy )SQLiteDatabaseaGLocal SQLite database shared by durable control-plane repositories. The database is created with owner-only file permissions where supported. Retention is explicit: callers invoke :meth:`prune_expired` from a deployment scheduler or maintenance command rather than deleting records on application startup. Z)retention_daysc|dkr tdt|}|dk(s|jdr tdt|j j |_||_|j jj}|j jjddd|s& |j jjd|jy#t$rYwxYw) Nrzretention_days must be positivez:memory:zfile:z.SQLiteDatabase requires a persistent file pathTparentsexist_okmode) ValueErrorstr startswithr expanduserresolvepathrparentexistsmkdirchmodOSError _initialize)selfrr path_textparent_existeds J/home/oem/CyberBrain26-unified/src/cyberbrain26/data_plane/sqlite_store.py__init__zSQLiteDatabase.__init__s Q >? ?I  "i&:&:7&C MN NJ))+335 ,))002 tdG    &&u-   s?%C55 DDc#4Ktj|jdd}tj|_|j d|j d|j d ||j y#|j wxYww)zA2A A2 A>A//A22A;7A>ct|jj}|jj ddd|j 5}t j|} |j||j ddd |jd|S#|jwxYw#1swY2xYw#t$rY|SwxYw)z>Create a consistent SQLite backup using the online backup API.TrrN) rrrrrr3r,r-backupr1r r!)r# destinationtargetsourcer<s r&r<zSQLiteDatabase.backupsk"--/779 D4eD __  &__V,F  f%      LL          s6C-B+>CC +B==CC  CCcp|j5}|jddddy#1swYyxYw)z@Checkpoint the WAL so the main database contains committed data.zPRAGMA wal_checkpoint(TRUNCATE)N)r3r0r2s r& checkpointzSQLiteDatabase.checkpoints6 __  B*   @ A B B Bs,5c|j5}|jd|fj}dddtdS#1swYxYw)urCount audit records older than ``cutoff``. Split out from :meth:`prune_expired` so the caller can inspect the chain *before* the evidence is removed. Retention is a deadline and must not be refused because the chain is broken — but whether it is broken has to be known first, otherwise the purge cannot record what it removed. z:SELECT COUNT(*) AS n FROM audit_events WHERE timestamp < ?Nn)r3r0fetchoneint)r#cutoffr3rows r&count_audit_beforez!SQLiteDatabase.count_audit_beforesY__  *$$L hj   3s8}   s "A  AN)nowc|'tjtj}||jdzdzdzz }|j 5}|j d|fj}|j d|fj}|j d|fj}|j d|fj}ddddS#1swYxYw) axApply the configured retention window to terminal records. Pending approval requests and unfinished tasks are never removed. Completed/failed tasks, decided approvals, and audit events older than the cutoff are deleted. This operation is explicit and auditable by the caller; SQLite does not securely erase old pages or WAL fragments. N<zkDELETE FROM tasks WHERE status IN ('completed', 'failed') AND completed_at IS NOT NULL AND completed_at < ?zoDELETE FROM approval_requests WHERE status IN ('approved', 'denied', 'expired', 'cancelled') AND created_at < ?z,DELETE FROM audit_events WHERE timestamp < ?zMDELETE FROM project_memories WHERE expires_at IS NOT NULL AND expires_at <= ?)tasks approvals audit_eventsproject_memories)rrIr timestamprr9r0rowcount)r#rIrFr3rMrNauditmemoriess r& prune_expiredzSQLiteDatabase.prune_expireds  ;,,s#--/Ct**R/"4r99     :&&D h  #**T h  &&> h "))_h  &"! (   %  s A5CCc 4|j5}|jd|jdt|jdjd}||jkDrt d|d|jt |dz|jdzD]}tj|}|t d||jd  |D]}|j||jd |tjtjf|jd ||j ddd|j!y#t$r|jwxYw#1swY8xYw) NzPRAGMA journal_mode = WALr+zPRAGMA user_versionrzdatabase schema z is newer than supported schema r zmissing SQLite migration zBEGIN EXCLUSIVEz@INSERT INTO schema_migrations(version, applied_at) VALUES (?, ?)zPRAGMA user_version = )r3r0rErDCURRENT_SCHEMA_VERSION RuntimeErrorranger getrrIr isoformatr5r7r8r6)r#r3currentversion statements statements r&r"zSQLiteDatabase._initializes __  *   : ;   : ;*,,-BCLLNqQRG444"&wi/O2235!1d.I.IA.MN (__W5 %&)B7)'LMM""#45 %/6 "**956&&Z (,,s"3"="="?@&&)?y'IJ%%'  4 '')%'')/  s%C FA5E0F0F  FFcZ |jjdy#t$rYywxYw)Nr;)rr r!)r#s r&r6z)SQLiteDatabase._restrict_file_permissions6s)  IIOOE "   s  **)r str | PathrrEreturnNone)rbzIterator[sqlite3.Connection])r=rarbr)rbrc)rFfloatrbrE)rIz float | Nonerbzdict[str, int])__name__ __module__ __qualname____doc__maxr rWr'rr3r9r<rArHrUr"r6r&rrsi!-BD*   2 2 B 48" H*:rkr)rh __future__rr,collections.abcr contextlibrrrpathlibrr __annotations__r__all__rjrkr&rrsUM"$%"hR( }H+ 'HVddN  rk