Local SQLite memory for AI CLIs: when it replaces vector stores

Your AI coding CLI can keep project memory in a local SQLite file. On one 50,000-line algorithmic trading system, a 105-session evaluation claimed 186 unit tests passed without regressions. The memory stays on the machine and under the operator's control.

The file can replace a bespoke vector store when the work stays inside one repository and retrieval can rely on full-text search, static local embeddings, or both. Engrim is a locally stored, project-scoped SQLite memory layer for AI command-line agents. Engrim names Google Antigravity, Claude Code, Cursor, Windsurf, and Codex as supported environments. Engram is a Go executable that stores data in SQLite with FTS5 full-text search and exposes CLI, HTTP, MCP, and TUI interfaces. It lists Claude Code, OpenCode, Gemini CLI, Codex, VS Code Copilot, Antigravity, Cursor, and Windsurf as compatible agents.

Cloud vector databases add a network path and a place where project context can leak. A local file is easier to inspect, back up, and delete. Engrim stores its database at ~/.engrim/memory.db and uses no telemetry, cloud synchronization, or tracking. Engram treats the local SQLite database as the authoritative store, with cloud capabilities as optional replication or shared access.

The local file is the product

The operator's job is not to tune a hosted index. It is to choose a file, a schema, and a retrieval path the agent can call without leaving the machine. Store the context a new engineer would ask for: architecture choices, rejected alternatives, environment quirks, test commands, and known failure modes. Do not store every line of code. The codebase is already available.

Keep the schema boring. A small table of notes, timestamps, and tags is easier to debug than a complex graph. If the agent cannot explain why a note was retrieved, the retrieval path is too opaque. Local memory should be inspectable with ordinary tools.

Retrieval decides whether the file is useful

Full-text search handles exact matches well: file names, function names, error strings, configuration keys, and quoted decisions. FTS5 full-text search gives the local database a lexical engine without moving data to another system.

Static local embeddings add a second path. Engrim's embedding layer uses model2vec static local embeddings, loads in about 30 ms, and can run pure-lexical with ENGRIM_EMBED=off. That is enough for many project memories. The agent can ask for nearby meaning while staying offline.

The evaluation is a signal, not a proof. It shows a local file can survive a long, bounded coding workload. It does not show lexical memory beats every vector store.

Semantic recall is the objection

Lexical search can miss paraphrase. If the agent saved 'use the retry queue' and later asks about 'how to handle failed jobs', a keyword index may miss the link. A vector store built for large, cross-project corpora can be better at that kind of recall.

For a single project, the miss rate is often acceptable. The memory is smaller, the vocabulary is narrower, and the agent already sees the codebase. Exact identifiers and recent notes carry weight. Static embeddings reduce the gap without adding a hosted service.

When memory grows beyond one repository, the rule flips. If the agent must recall across many codebases, many teams, or a large historical corpus, use a purpose-built vector store. Local file-based memory serves project memory. It does not replace universal semantic search.

Bounded projects keep memory local

Use local SQLite memory when the memory is tied to one project, the operator wants offline storage, and retrieval can rely on full-text search, static local embeddings, or both. Use a vector store when semantic recall across large, cross-project corpora is critical.

Before switching, run a short teardown on your own workload and note what the agent returns.

• Ask the agent to recover a decision from an earlier session.
• Ask it to recover a convention from a previous file.
• Ask it to recover a failure mode from a prior run.

If the local file returns the right notes, keep it local. If it returns weak matches, add a vector store where the recall gap actually hurts.