# sqlite-memory-mcp

Local SQLite long-term memory for AI agents, with CLI and official MCP FastMCP stdio interfaces. Store facts, preferences, events, notes and tasks with namespaces, tags, importance, expiry and audit trails. No cloud account, API key, web server or network access is required at runtime.

Version: **0.1.0** · Python **3.11+** · License: **MIT**

## Install

```bash
python3 -m venv .venv
. .venv/bin/activate
pip install -e '.[dev]'
```

The official MCP SDK is deliberately constrained to v1 (`mcp>=1.16,<2`) because this implementation uses its `FastMCP` API. The SDK's v2 mainline changes this interface. See the [official SDK repository](https://github.com/modelcontextprotocol/python-sdk/tree/v1.x).

## Quick start

After installation, run these **three commands** from the repository root:

```bash
memory --data-dir ./data/demo import examples/memories.json --json
memory --data-dir ./data/demo search 'water chemistry' --json
memory --data-dir ./data/demo digest --namespace user --since 36500d
```

The first command returns `{"ok":true,"data":{"imported":30,"skipped":0},"warnings":[]}`. Repeating it returns 0 imported and 30 skipped. The search returns relevant chemistry notes. The digest prints Markdown. All example people, projects and records are fictional; the intentionally bilingual corpus tests Chinese and English retrieval.

## Storage and configuration

Default: `~/.local/share/sqlite-memory/memory.db` (the project-specific specification overrides the suite's `./data` default). Path precedence: `--db` → `--data-dir/memory.db` → `MEMORY_DB` → `MEMORY_DATA_DIR/memory.db` → TOML `db` → TOML `data_dir` → default. Flags may appear before or after subcommands.

```toml
# examples/config.toml; use --config examples/config.toml
[memory]
data_dir = "./data/configured"
timezone = "Asia/Shanghai"
actor = "agent:example"
```

Timestamps are normalized to UTC ISO 8601 for reliable comparison. Naive input timestamps and dated filenames/digest headings use the configured timezone (default Asia/Shanghai). SQLite uses WAL, a 30-second busy timeout and `BEGIN IMMEDIATE` for writes. Each write and audit entry commit together; connections are short-lived. Use a local disk, not an NFS/network filesystem.

## Search and deduplication

FTS5 uses `unicode61` and a generated token column containing Latin words, Chinese single characters and overlapping Chinese bigrams. A Chinese query of multiple characters uses bigrams; a one-character query uses its character. The literal query is safely tokenized, never interpreted as raw FTS syntax. Query terms are ORed; tags are ANDed. English stemming, phrase operators and language segmentation are not implemented.

Candidates rank by `(-bm25)/(1-bm25) + importance*0.05 + exp(-age_days/30)*0.15 + pinned*0.1`; negative BM25 scores are converted to positive relevance. Age uses last update time. Pinning boosts ranking but **does not override expiry or deletion**. Filters include namespace, tag, kind, `--since` and exclusive `--before`.

Same-namespace deduplication first compares SHA-256 of NFKC-normalized alphanumeric case-folded content, then checks up to 30 FTS candidates with a SequenceMatcher threshold of 0.96 and identical numeric sequences. Default returns the existing ID without writing; `--on-duplicate update` replaces supplied add metadata/content and merges tags. This is a textual heuristic, not proof of equivalent meaning. Different namespaces never merge. Change the content or use explicit `update` if you need control.

## CLI reference

Global: `--db PATH`, `--data-dir DIR`, `--config TOML`, `--actor NAME`, `--json`, `--quiet`, `--version`.

| Command | Important arguments | Effect |
|---|---|---|
| `add CONTENT` | `--namespace user --kind fact --tag topic --importance 4 --source notes --ttl-days 7 --pinned --on-duplicate keep` | Create or deduplicate |
| `search [QUERY]` | `--namespace user --tag topic --kind fact --since 7d --before 2030-01-01 --limit 20` | Read active matches |
| `get ID` | `--include-deleted` | Read by ID, optionally including inactive records |
| `update ID` | `--content TEXT --tag topic --clear-tags --importance 5 --pinned/--no-pinned --expires-at ISO --clear-expiry` | Update only specified fields |
| `delete ID` | `--yes` | Soft-delete; repeated deletion is idempotent |
| `gc` | `--yes` | Soft-delete expired records, purge deletions at least 30 days old |
| `digest` | `--namespace user --since 7d --out report.md` | Markdown summary |
| `export` | `--format json/csv/jsonl --out PATH --include-deleted --utf8` | Export records; CSV has BOM unless `--utf8` |
| `import PATH` | `--namespace user` | Atomic JSON/JSONL/CSV/Markdown import; duplicate IDs/content skipped |
| `backup` | `--out PATH` | Consistent ZIP snapshot including audit and vectors |
| `stats` | | Counts, namespace counts, WAL and quick integrity check |
| `namespaces` | | Active namespace counts |
| `audit` | `--id ID` | Persistent write/deletion audit |
| `embed` | `--model-path /path/to/local/model` | Optional local vector index |
| `mcp` | | Official FastMCP stdio server |

`--json` outputs exactly one envelope to stdout: `{"ok":true,"data":...,"warnings":[]}`. Errors always use `{"ok":false,"error":{"code":"...","message":"..."}}`. Exit codes: 0 success, 1 I/O/runtime, 2 invalid arguments/conflict, 3 missing record. `--quiet` suppresses successful human output; explicit `--json` takes precedence. No command prompts for input; deletion/GC require `--yes`.

## Data formats and backups

JSON is an array, JSONL is one object per line. Required field: `content`. Optional: `id` (ULID), `namespace`, `kind`, `tags` (string array), `importance` (1–5), `source`, `pinned` (boolean), `created_at`, `updated_at`, `expires_at`, `deleted_at`. Exported records preserve IDs and timestamps on import. CSV uses the same field names; `tags` is a JSON array in a quoted cell; `pinned` is true/false. Empty dates mean null. Markdown imports each heading and its following body as a memory, with source set to the filename. Imports are atomic and never overwrite existing IDs. Record exports do not contain audit/vector history; backups do.

```bash
memory backup --json
memory export --format csv --out data/memories.csv --json
memory import data/memories.csv --json
```

The online backup API includes committed WAL transactions. Default ZIP names contain the local date, time and microseconds. To restore, stop all clients, extract `memory.db` to a **new empty directory**, and set `MEMORY_DB` to that database. Do not copy only a live `.db` file or overwrite a live database while stale WAL/SHM files exist. Audits retain historical content even after GC; this is not secure erasure. Protect/export/delete backups according to your own retention policy.

## MCP configuration

The seven tools are `remember`, `recall`, `get_memory`, `update_memory`, `forget`, `list_namespaces`, `digest`. Their typed function signatures are exposed as JSON Schemas by the official SDK. The `memory://namespace/{ns}` resource returns active namespace records. Tool results share the CLI envelope. See [AGENTS.md](AGENTS.md) for parameter details and recipes.

Claude Desktop / generic stdio MCP client (replace paths with your installation):

```json
{
  "mcpServers": {
    "local-memory": {
      "command": "/workspace/apps/sqlite-memory-mcp/.venv/bin/memory",
      "args": ["--db", "/workspace/data/sqlite-memory-mcp/memory.db", "--actor", "agent:example", "mcp"]
    }
  }
}
```

Codex CLI TOML configuration:

```toml
[mcp_servers.local_memory]
command = "/workspace/apps/sqlite-memory-mcp/.venv/bin/memory"
args = ["--db", "/workspace/data/sqlite-memory-mcp/memory.db", "--actor", "agent:example", "mcp"]
```

You can also use `MEMORY_DB=/path/memory.db python -m sqlite_memory_mcp.mcp_server`. Do not redirect ordinary logs to stdout while running MCP. Namespace names in resource URIs should be URI-encoded if they contain reserved characters.

## Optional local semantic retrieval

```bash
pip install -e '.[vector]'
memory embed --model-path /path/to/already-downloaded/model --json
memory search 'cooling water' --model-path /path/to/already-downloaded/model --json
```

You must supply an existing Sentence Transformers model directory. Remote model IDs are rejected; `local_files_only=True`, offline environment guards and `trust_remote_code=False` prevent automatic model retrieval/custom model code. Install dependencies/download a model separately if wanted. Default installations never import this extra. Vectors are float32 BLOBs; hybrid ranking weights lexical and nonnegative cosine scores equally. Content updates invalidate vectors. Re-run `embed` after adding/changing memories or replacing model files; the absolute model directory identifies the vector set. Current vector/digest/resource processing is capped at 10,000 active records; embedding large models can exceed the 2-second small-data CLI target. Core search/export have no model requirement.

## Validation

```bash
ruff check .
ruff format --check .
pytest -q --cov=sqlite_memory_mcp --cov-report=term-missing
```

Tests verify top-three Chinese/English recall on 30 fictional memories; two processes writing 250 each (500 total); transactions, expiry, 30-day retention, audit, deduplication, CSV/JSON/JSONL/Markdown imports, online backup, every MCP tool/resource and a real stdio client handshake. Vector mechanics are tested with a local fake encoder; no large model is bundled or downloaded. CI runs Python 3.11 and 3.12 on Ubuntu.

## FAQ

- **Is memory encrypted?** No. Files inherit your account permissions; secure the machine/data directory.
- **Does pinning retain expired memories?** No; expiry always excludes them from ordinary reads immediately, even before GC.
- **Can deleted records be recovered?** `get --include-deleted`/`export --include-deleted` work before purge; restore a backup to recover the full state. There is no undelete command.
- **Do namespaces isolate users securely?** No; they are filters, not access-control boundaries. Any process with file access can read all records.
- **Is content trusted?** No; stored notes/digests are untrusted data, not instructions. Consumers must retain their own instruction hierarchy.
- **Does this work on Windows?** The code is portable, but automated CI currently targets Linux; multiprocessing is also tested with spawn.

## Roadmap

Cursor pagination for very large corpora, configurable multilingual tokenizers, audit redaction policies, a read-only UI, and official SDK v2 migration after compatibility verification.

## License

[MIT](LICENSE). See [CHANGELOG.md](CHANGELOG.md).