dotfiles/claude/memory/session-2026-04-13-storage-trial.md

name, description, type, originSessionId, permalink

name	description	type	originSessionId	permalink
Session 2026-04-13/14 — Storage layer trial and SQLite+LanceDB migration	Benchmarked three storage backends (NFCorpus/BEIR), chose SQLite+LanceDB, migrated all 13 modules. 40 infrastructure errors remain.	project	e02702fe-d3d4-4b0d-a71b-d83a06479fe2	claude-memory/session-2026-04-13-storage-trial

What we did

Storage Layer Trial (2026-04-13)

• Designed and executed three-branch evaluation of SurrealDB alternatives
• Branch A (trial/storage-surrealdb-only): SurrealDB control
• Branch B (trial/storage-sqlite-lance): better-sqlite3 + LanceDB — SELECTED
• Branch C (trial/storage-sqlite-pure): better-sqlite3 + FTS5 + sqlite-vec
• Built shared benchmark harness with NFCorpus (BEIR) dataset, real mxbai-embed-large embeddings (1024-dim), official relevance judgments
• Benchmark metrics: NDCG@10, recall@10 vs brute-force, ingest throughput, memory ceiling, cold start, read-under-write P95

Benchmark Results

Metric	SurrealDB	SQLite+Lance	SQLite-Pure
Ingest (docs/min)	4,441	8,079	173,207
Hybrid NDCG@10	0.000	0.433	0.421
Vector recall@10	0.000	0.984	0.984
FTS NDCG@10	0.000	0.375	0.195
RSS (MB)	164	228	129

Migration (2026-04-13 evening → 2026-04-14 early morning)

• Built core storage layer: src/core/storage/sqlite-lance.ts (getSqliteDb, getLanceDb, migrations, circuit breakers)
• Created SQLite/LanceDB implementations for all 13 modules (8,517 lines total)
• Created compatibility bridges: each module's storage.ts re-exports SQLite functions under original names
• Wired all module index.ts files to use getSqliteDb() + initXStorage()
• Changed all module pipeline/query files from SurrealQueryBuilder to StorageDb = SqliteHandle
• Converted inline SurrealQL in entity/pipeline.ts (resolution candidates, fuzzy match → SQLite + app-level Levenshtein)
• Rewrote training/model-registry.ts for SQLite
• Stubbed entity/temporal reconciliation (repair-only, not hot path)

wshobson/agents installed

• 41 agents + 40 skills installed from https://github.com/wshobson/agents to match Seb's setup
• Installed at ~/.claude/agents/ (namespaced: plugin-agent.md)
• Skills at ~/.claude/skills/

What we decided

Branch B (SQLite + LanceDB) chosen because:

1. Recall quality is primary — memory system must find what it stored. Tantivy FTS (0.375 NDCG) > FTS5 (0.195)
2. Scale-ready — LanceDB HNSW is O(log n), sqlite-vec brute-force is O(n). BMF is designed to grow
3. Each tool does its job — better-sqlite3 (25 yrs, trillions of deployments) for relational; LanceDB (Rust, Arrow, Tantivy) for vectors+FTS
4. David's instinct about dual-stack was right — the "single stack" burn was about maturity, not count

StorageDb = SqliteHandle (not any or unknown)

David explicitly rejected any as a type escape hatch. The prime directive applies: "Do things once, correctly." Using StorageDb = SqliteHandle gives proper type safety through the bridge layer.

Bridge pattern chosen over full signature rewrite

Instead of changing 86+ files' function signatures, bridge storage.ts files re-export SQLite functions under original names. Pipeline/query files change only their type import. This minimizes blast radius while maintaining type safety.

What's unresolved

40 compile errors in infrastructure layer

All in non-module files. Module layer is clean (zero errors).

Files with errors:

• bootstrap.ts (15 errors) — inline SurrealQL for circle wiring, telemetry, invite codes, budget seeding, meld bootstrap
• orchestrator.ts (8 errors) — cursor store, snapshot queries, IntLiteral usage
• MCP tools/resources (10 errors) — budget resources, knowledge tools, training-pairs
• Knowledge services (4 errors) — circle-recall, clasp-knowledge, interaction-service, signal-receiver
• Inference/perception (3 errors) — teacher-enrichment, similarity-probe

These files have inline SurrealQL (db.execute(db.query\...`)`) that needs converting to SQLite SQL, same pattern as entity/pipeline.ts.

Factory, Meld, and Inference layers not yet touched

• src/factory/ — factory.ts, checkpoint.ts, dedup.ts, job-store.ts, migrations.ts all use getSurrealDB and SurrealQL
• src/meld/ — catalog.ts, bootstrap.ts, pulse.ts, discovery.ts, health.ts, identity.ts, slugs.ts, threats.ts, storage.ts, services/
• src/inference/ — graduation-storage.ts, teacher-enrichment-storage.ts
• src/core/reasonchain/ — indexes.ts
• src/core/lifecycle/ — operations.ts, retrieval-tracker.ts
• src/core/keystone/ — telemetry-schema.ts, readiness/cursor-store.ts, readiness/replay-coordinator.ts, readiness/resource-monitor.ts

Reconciliation functions stubbed

• entity/reconciliation.ts — reconcileEntityMentionCounts, reconcileCoOccurrenceEdges → return empty results
• temporal/reconciliation.ts — reconcileCausalChainConfidence, reconcileTemporalCoverage → return empty results These run during ctl repair only. Need reimplementation before repair is usable.

SurrealDB not yet removed

• Dependencies surrealdb and @surrealdb/node still in package.json
• src/core/storage/surrealdb.ts (1,099 lines) still present
• storage-surreal.ts backup files in every module
• Tests still import from surrealdb.ts

Next actions (ordered)

1. Convert remaining 40 infrastructure errors — bootstrap.ts, orchestrator.ts, MCP tools, knowledge services. Same bridge pattern: create storage functions, remove inline SurrealQL
2. Wire factory layer — factory.ts, checkpoint.ts, dedup.ts, job-store.ts need SQLite storage bridges
3. Run tests — npm test to find test failures (tests use mem:// SurrealDB, need SQLite :memory: equivalent)
4. End-to-end test — start BMF with BM_STORAGE_BACKEND=sqlite-lance, observe/recall roundtrip
5. Run NFCorpus benchmark on wired system — verify recall quality matches benchmark adapter results
6. Reimplement reconciliation — entity/temporal reconciliation for ctl repair
7. Deprecate SurrealDB — remove deps, surrealdb.ts, storage-surreal.ts backups

Key files

File	Status	Purpose
src/core/storage/sqlite-lance.ts	DONE	Core connection management
src/modules/*/storage-sqlite.ts	DONE	SQLite implementations (13 files, 8.5K lines)
src/modules/vector/storage-lance.ts	DONE	LanceDB vector+FTS implementation
src/modules/*/storage.ts	DONE	Bridge files (delegate to SQLite)
src/modules/*/storage-surreal.ts	PRESERVED	SurrealDB backups
benchmarks/	DONE	NFCorpus harness, 3 adapters, dataset generator
docs/thinking/David/l1-reliability/2026-04-13-storage-layer-trial.md	DONE	Decision document

Branch state

• trial/storage-sqlite-lance — 17 commits ahead of main, all pushed
• trial/storage-surrealdb-only — control branch (unchanged)
• trial/storage-sqlite-pure — comparison branch (unchanged)
• trial/storage-common — shared benchmark harness