sqlite-local-ai-memory

# 🧠 SQLite Local AI Memory

> **Simple, fast, long-term memory for your AI chatbots, agents, and MCP tools.**  
> Give your LLM a persistent memory using a local SQLite file. No complex database servers, no heavy cloud setup, and zero C++ compilation errors. Works everywhere.

---

## 💡 Overview

Most LLM long-term memory frameworks require running complex Docker microservices (Milvus, Qdrant, Weaviate) or paying for cloud APIs (Mem0, Zep). 

**SQLite Local AI Memory** provides a **100% In-Process, Bare-Metal Memory Architecture** designed for TypeScript/Node.js environments. It achieves **sub-2ms retrieval latencies** without external database servers, network IPC roundtrips, or native C++ compilation issues.

---

## 🎯 What It Is & How It Works (In Plain English)

Imagine hiring a brilliant assistant who **forgets everything the moment you close the chat window**. Next time you talk, you have to explain your project, your servers, and your preferences all over again. Or worse: you paste a massive chat history into the prompt, burning thousands of tokens until the AI gets confused and starts making things up.

**`sqlite-local-ai-memory` gives your AI a permanent, organized brain stored in a single file on your own computer**—no cloud accounts, no monthly fees, and no complex database servers required.

Instead of dumping a giant, messy text log into the AI's prompt on every turn, this library organizes memory the same way the human brain does:

1. **Working Memory (*What we are doing right now*):** Keeps track of the active task, current status, and immediate next steps.
2. **Semantic Knowledge (*Facts that rarely change*):** Stores concrete facts—like your server IPs, user preferences, or project rules—organized by category. If a fact changes (e.g., a server moves to a new IP), it updates the current fact while keeping an audit trail of the old one.
3. **Episodic History (*Past conversations & logs*):** Saves the chronological flow of everything that has happened so the AI can search back in time whenever you ask *"What did we do last Tuesday?"*
4. **Sleep Consolidation (*AutoDream*):** Periodically, a background AI agent wakes up, reads recent messy chat logs, extracts new permanent facts, cleans up outdated notes, and keeps active troubleshooting cases open until you confirm they are solved—just like human memory consolidation during sleep.

---

## ⚡ What Makes This Unique? (The "Secret Sauce")

Unlike basic vector wrappers or chat buffer memory systems, this architecture incorporates 6 specialized cognitive mechanisms driven by dedicated AI agents and bare-metal algorithms:

1. **🌙 AutoDream Dual-Output Engine** *(Powered by State Orchestrator Agent - Gemini / Ollama)*:  
   Operates in the background via time-based pruning (12h TTL) and *Semantic Bumping*. Produces a **Dual Output**:
   - **Active Dashboard:** Live executive summary (`narrativeSummary`) and active working state (`dashboard`).
   - **Long-Term Triage Memory:** High-density 4-block technical facts (`triageMemory`) compiled directly into SQLite.
   - **Unresolved Case Archiving:** If an open case expires from the dashboard without resolution, it is auto-compiled into SQLite as `[UNRESOLVED_CASE]` before pruning, preserving historical diagnostic evidence.

2. **🐣 Open Case Incubator & Dynamic Query Window (Option B)**:  
   Unresolved issues stay safely in the active dashboard incubator (`openCases`). When AutoDream consolidates an open case, it dynamically expands its query window (`getRecentFactsSince`) back to the initial timestamp when the case was opened (`minTs`), delivering **100% of the historical working facts and context** to the LLM to compile the 4-block triage record without semantic loss.

3. **⚖️ Real-Time Semantic Arbiter** *(Powered by Semantic Arbiter Agent)*:  
   Solves vector RAG state blindness. When a new fact enters (e.g., *"Server IP moved to .205"*), the Arbiter evaluates whether it is **Additive** (co-exists with past context) or **Exclusive** (replaces an old state), soft-deleting contradictory records in real time.

4. **🚀 Embedded In-Process Storage (No Dedicated Vector DB Required)**:  
   Zero database infrastructure to deploy. You don't need to spin up heavy Docker containers for Qdrant/Chroma, configure PostgreSQL with `pgvector`, or pay for cloud vector databases like Pinecone. The entire hybrid index (FTS5 BM25 + Int8 vectors) lives inside a single local SQLite file handled by `better-sqlite3` (in safe `TRUNCATE` mode). Reads active dashboard state in **`< 0.1 ms`** and memories in **`< 1.5 ms`**.  
   *(Note: While database operations are local in-process, computing vector embeddings requires an embedder model — either local via Ollama `nomic-embed-text` or via cloud API. If no embedder is configured, search gracefully degrades to pure keyword FTS5).*

5. **🛡️ 2-Layer Noise Firewall**:  
   Protects your database and API bill from clutter:
   - *Layer 1 (Regex, 0 ms):* Intercepts "hello", "thanks", "ok" instantly.
   - *Layer 2 (Local SLM Qwen 1.5B, 10 ms):* Uses a bare-metal mini-model to drop non-knowledge chatter before calling cloud APIs.

6. **🔎 Dual Parallel Hybrid RAG**:  
   Executes exact **SQLite FTS5 BM25** (keyword/IP matching in `< 0.5 ms`) and **Int8 Scalar Quantized Vector Search** (`nomic-embed-text`) in parallel with reciprocal rank scoring.

---

## 📦 Installation & Quickstart

### Option 1: Embedded SDK / Library (TypeScript / Node.js)
```bash
npm install sqlite-local-ai-memory
```

### Option 2: Model Context Protocol (MCP) Server (Zero-Install)
Connect your AI assistant (**Claude Desktop, Cursor, Antigravity, Windsurf, Cline**) directly to a private local SQLite memory without writing code:

Add to your `claude_desktop_config.json`, `mcp_config.json`, or Cursor MCP settings:
```json
{
  "mcpServers": {
    "sqlite-memory": {
      "command": "npx",
      "args": ["-y", "sqlite-local-ai-memory"],
      "env": {
        "SQLITE_MEM_PATH": "~/.local-ai-memory/memoria.db",
        "GEMINI_API_KEY": "your-api-key"
      }
    }
  }
}
```
*Exposes 4 native MCP tools: `save_fact`, `search_memory`, `get_current_state`, and `consolidate`.*

---

## 📊 Real-World Production Benchmarks
Battle-tested in a 24/7 production autonomous WhatsApp assistant and engineering agents:
* **0.00 MB Idle RAM** (vs 3,250 MB with Qdrant container).
* **97.6% Retrieval Success** across 168 live conversational user interactions.
* **15,900+ Vectors** maintained continuously for 5+ months without index corruption.

👉 **Read the full empirical benchmark report:** [**`BENCHMARKS.md`**](BENCHMARKS.md)

---

## 💻 Quick Example (SDK)

```bash
npm install sqlite-local-ai-memory
```

```typescript
import { MemoryEngine } from "sqlite-local-ai-memory";

const memory = new MemoryEngine();

// 1. Save a new fact (automatically filtered & audited for collisions)
await memory.save("Production database server is running on IP 10.0.0.50");

// 2. Recall relevant facts instantly (FTS5 + Vector hybrid search)
const facts = await memory.search("Where is the database server hosted?");
console.log(facts); 
// -> [{ id: "...", data: "[TECHNICAL] Production database server is running on IP 10.0.0.50", score: 0.95 }]

// 3. Inspect active project dashboard (instant 0 ms SQLite read)
const dashboard = memory.getDashboard();
console.log(dashboard?.data);
// -> "Active working context and unresolved incubator cases..."

// 4. Asynchronous lifecycle maintenance (AutoDream - run at session end or idle timer)
// await memory.consolidate();
```

---

## 🧩 Cognitive Roles & Model Sizing: Bigger is Not Always Better

You don't need a massive 70B or 120B model to manage long-term memory. In our real-world tests, giant models often underperformed because they chat too much instead of returning clean, machine-readable data.

What this system actually needs is **discipline, not raw parameter size**: models that follow strict instructions, never hallucinate conversational filler, and return 100% valid JSON schemas every time.

The engine divides memory management into specialized roles so you can run cheap, fast models where they shine and save heavy compute:

```text
[User Message]
       │
       ▼
1. GATEKEEPER ───────► Casual chatter? ("hello", "thanks") ──► [YES] ──► DROP (0 cloud cost)
   (Local 1.5B SLM)          │ [NO]
                             ▼
2. NOTARY ───────────► Extract clean facts in 3rd person ────► Requires Strict JSON Schema
                             ▼
3. SEMANTIC ARBITER ─► Check conflicts (Old IP vs. New IP) ──► Requires Relational Logic
                             ▼
4. AUTODREAM ────────► Multi-hour synthesis & open issues ───► Requires Context + Complex Schemas
```

### Role Breakdown

| Role | What it does | What it needs | Default / Recommended | Override Env |
| :--- | :--- | :--- | :--- | :--- |
| **`GATEKEEPER`** | **The Bouncer:** Drops conversational noise ("hello", "thanks", "ok") before touching expensive APIs. | **Ultra-Low:** Simple YES/NO classification. No JSON output, no memory history. Runs easily on low-end CPUs in <1GB RAM. | `ollama/qwen2.5-coder:1.5b` *(or llama3.2:1b)* | `GATEKEEPER_MODEL` |
| **`QUERY_EXPANDER`** | **The Librarian:** Turns vague questions (*"what happened to that server?"*) into sharp search queries using recent conversation context. | **Low-Medium:** Contextual reasoning. **OPTIONAL:** Set to `none` to disable. Disabling provides 0 ms search latency, $0 cost, and zero conversational noise in search tokens. | `gemini/gemini-2.5-flash-lite` *(or `none` to disable)* | `EXPANDER_MODEL` |
| **`NOTARY`** | **The Clerk:** Extracts permanent facts, rules, and preferences into clean JSON. Small local models fail here because they add chat filler (*"Sure! Here are your facts:"*) which breaks JSON parsers. | **Medium:** Strict JSON Mode / Structured Outputs. Distinguishes permanent facts from temporary remarks. | `gemini/gemini-2.5-flash-lite` | `NOTARY_MODEL` |
| **`SEMANTIC_ARB