Back the dummyOptimizer with a shared SQLite instance and basic similarity search

[jerm-dro]

Context

The current dummyOptimizer (pkg/vmcp/optimizer/dummy_optimizer.go) uses an in-memory map with simple case-insensitive substring matching. This doesn't scale to production workloads and duplicates tool state across every optimizer instance (one per MCP session).

Prototype reference: #3516 (see pkg/vmcp/optimizer/internal/db/ for prior art on SQLite + FTS5)

Requirements

1. Introduce a storage interface that abstracts how tools are stored and searched. The dummyOptimizer should depend on this interface rather than directly on SQLite. This enables:
   • Unit testing the optimizer with a mock/in-memory store
   • Integration testing the SQLite implementation independently
2. Implement the storage interface with a shared SQLite backend backed by FTS5 full-text search. The SQLite instance must be shared across all optimizer instances created by the OptimizerFactory (pkg/vmcp/server/server.go:134), deduplicating tool storage.
3. Despite the shared database, each optimizer instance must only return tools for the capabilities passed at factory creation time (i.e., the []server.ServerTool slice passed to NewDummyOptimizer). This preserves per-session scoping.
4. Implement a basic BM25/FTS5-based similarity search in FindTool, replacing the current substring matching.
5. The Optimizer interface (pkg/vmcp/optimizer/optimizer.go) should gain a Close() method so the database can be cleaned up. Update the server integration in pkg/vmcp/server/server.go to call Close() on session teardown.
6. The factory function signature in server.Config.OptimizerFactory may need to change to accept a shared storage reference. Consider a two-phase pattern: a top-level "optimizer manager" (holds the storage instance) and per-session optimizer instances.

High-Level Implementation

• Define a storage interface (e.g., ToolStore) in pkg/vmcp/optimizer/ with methods for tool ingestion, search, and cleanup
• Create an internal SQLite implementation under pkg/vmcp/optimizer/internal/db/ that satisfies the interface:
  • A single shared *sql.DB connection with FTS5 virtual table for tool search
  • Tool ingestion (upsert tools with name, description, input schema)
  • FTS5-based search that returns ranked results
• Update dummyOptimizer to accept a ToolStore and a scoping set of tool names
• FindTool queries the store, filters to the scoped tool set, and returns matches
• CallTool remains unchanged (direct tool handler invocation)
• Update cmd/vmcp/app/commands.go to create the shared SQLite-backed store and pass it into the factory closure

Acceptance Tests

• Storage interface unit tests: Optimizer logic tested against a mock ToolStore — verifies search delegation, scoping, and error handling without any database
• SQLite integration tests: The SQLite ToolStore implementation tested independently — verifies FTS5 indexing, upsert behavior, and search ranking
• Shared state: Two optimizer instances created from the same factory share the same ToolStore and can see each other's tools
• Per-session scoping: An optimizer instance only returns tools from its own []server.ServerTool slice, even when the store contains tools from other sessions
• FTS5 search quality: FindTool with a natural-language description returns relevant tools (not just substring matches)
• Close cleanup: Calling Close() on an optimizer properly releases resources
• Concurrent access: Multiple goroutines can call FindTool and CallTool on different optimizer instances simultaneously without data races
• Existing adapter compatibility: The find_tool and call_tool MCP tools exposed via pkg/vmcp/server/adapter/optimizer_adapter.go continue to work unchanged

[JAORMX]

Hey, related to my comment on #3516: the issue doesn't mention anything about how the SQLite database gets deployed in Kubernetes. Given that SQLite on network filesystems (NFS, CephFS) leads to instant corruption, and RWO PVCs have stuck-volume failure modes on node failure, I think we should make an explicit call on the storage strategy here.

Since the data is reconstructable (tools come from discovery, embeddings from the EmbeddingServer), we might not even need persistence. Worth considering emptyDir with rebuild-on-start as the simplest path.

At minimum, the acceptance criteria should include PRAGMA configuration guidance and a warning against NFS-backed StorageClasses.

[aponcedeleonch]

Yes, I was planning to go in this direction:

emptyDir with rebuild-on-start as the simplest path