High RAM usage due to in-memory SQLite database

Start in crates/capsem-logger/src/schema.rs, then read DbReader::open in crates/capsem-logger/src/reader.rs and the synchronization path in crates/capsem-logger/src/schema/memory_sync.rs. Confirm the two security event tables are excluded from the in-memory mirror while event queries still use the disk database, then run the capsem-logger tests.

I noticed Capsem could take a lot of RAM after running a long time, which could make my machine unresponsive. It seems that this is coming from a large SQLite database loaded in memory even though it seems that it could be kept on disk. Below is an AI-generated bug report with a fix suggesting to add security_rule_events and security_decision_events to DISK_ONLY_TABLES (which already has event_body_blobs).

AI-generated below

Summary

When a persistent sandbox runs workloads involving many file or network operations (such as benchmarks or builds), session.db accumulates large volumes of security_rule_events and security_decision_events (often hundreds of thousands of rows totaling 15–25+ GiB).

On service startup, capsem-service hydrates database handles for persistent sandboxes via DbReader::open, which unconditionally copies all non-DISK_ONLY_TABLES from disk into an in-memory SQLite database (mem). Because only event_body_blobs is currently marked disk-only, capsem-service copies all serialized security event JSON payloads into RAM, resulting in:

1. Multi-minute startup freezes: A 22 GiB database logged a 165-second freeze during register_session_db_handle.
2. Process memory exhaustion: Service RSS balloons to 40–94+ GiB.
3. Host thrashing and OOM kills: Drives heavy swap thrashing, triggers systemd-oomd, and can render the host unresponsive.

Root Cause

1. In crates/capsem-service/src/session_db_handles.rs, ServiceState::register_session_db_handle calls capsem_logger::DbHandle::open_external_reader.
2. In crates/capsem-logger/src/reader.rs (DbReader::open), the reader attempts to populate an in-memory mirror:

   schema::with_memory_schema_lock(|| {
       schema::create_memory_tables(&conn, &memory_uri)?;
       schema::rehydrate_memory_tables_from_disk_once(&conn, schema::hot_ledger_tables())?;
       schema::create_memory_read_views(&conn)
   })?;
   

3. In crates/capsem-logger/src/schema.rs, hot_ledger_tables() selects all schema tables except DISK_ONLY_TABLES:

   const DISK_ONLY_TABLES: &[&str] = &["event_body_blobs"];
   

4. In crates/capsem-logger/src/schema/memory_sync.rs (sync_memory_tables_from_disk), it executes:

   INSERT OR REPLACE INTO mem.{table} SELECT * FROM main.{table};
   

   For security_rule_events (13.2 GiB) and security_decision_events (4.5 GiB), this reads gigabytes of records off disk and allocates them directly into the process heap.

Suggested Fix

Mark security_rule_events and security_decision_events as disk-only tables. Like event_body_blobs, read queries will query the disk database directly using SQLite's 256 MiB mmap window (SQLITE_MMAP_SIZE_BYTES) and existing B-tree indexes (idx_security_rule_events_timestamp, idx_security_rule_events_event_id, idx_security_rule_events_rule_id).

Diff

--- a/crates/capsem-logger/src/schema.rs
+++ b/crates/capsem-logger/src/schema.rs
@@ -7,7 +7,11 @@
 use tracing::warn;
 
 /// Tables that must never be mirrored into the DB-owned in-memory schema.
-const DISK_ONLY_TABLES: &[&str] = &["event_body_blobs"];
+const DISK_ONLY_TABLES: &[&str] = &[
+    "event_body_blobs",
+    "security_rule_events",
+    "security_decision_events",
+];
 
 pub const READY_SCHEMA_COLUMNS: &[(&str, &[&str])] = &[

Impact & Verification

• Startup time: Reduced from ~165 seconds to sub-second.
• Service memory footprint: Drops from 57+ GiB to ~50 MiB.
• Enforcement speed: Unaffected; live security policy decisions are evaluated in-memory using compiled CEL rules in capsem-core and do not read SQLite.
• Query performance: Read queries (GET /vms/:id/security/events, capsem triage) hit the indexed B-tree on disk in < 1–2 ms without loading the entire historical ledger into memory.