# SQLite MCP Memory

Namespace-scoped, persistent notes exposed through a real MCP stdio server.

A focused engineering reference implementation with a runnable local example, behavioral tests and an explicit scope. It is intended for evaluation and extension; it is not a claim of a deployed production service.

[Quick start](#quick-start) · [Architecture](#architecture) · [API and input contract](#api-and-input-contract) · [Validation](#validation) · [Limitations](#limitations)

## Why this project exists

Agent memory needs an inspectable persistence boundary. This project separates a small SQLite note store from its MCP transport so retrieval and namespace behavior can be tested without a model provider.

## Quick start

Requires Python 3.11 or newer. The core uses the Python standard library and needs no API keys, model downloads or external services.

```bash
git clone https://github.com/ryash14/sqlite-mcp-memory.git
cd sqlite-mcp-memory
python -m sqlite_mcp_memory examples/demo.json
python -m unittest discover -s tests -v
```

The checked-in example is synthetic. Its complete input and recorded output are in [examples/demo.json](examples/demo.json) and [examples/expected.json](examples/expected.json). A shortened result is shown below when the full report is long:

```text
{
  "ids": [
    "8b0d7a9d48148f826805435c",
    "4d2304ebd8a45e2f668dce5a"
  ],
  "matches": [
    {
      "id": "8b0d7a9d48148f826805435c",
      "title": "Deployment decision",
      "body": "Use a local SQLite store for the single-user prototype."
    }
  ]
}
```

To process your own input, use the same JSON shape or pipe it through stdin:

```bash
python -m sqlite_mcp_memory path/to/input.json
cat examples/demo.json | python -m sqlite_mcp_memory -
```

## Example interpretation

The query returns the deployment note because its body contains the literal substring `SQLite`. The namespace is part of every database operation. The SDK smoke test also verifies that the same query in another namespace returns no results.

## Architecture

```mermaid
flowchart TD
    N0["Trusted local MCP client or JSON CLI"]
    N1["FastMCP typed tools or direct adapter"]
    N2["Namespace-scoped SQLite note store"]
    N3["Literal substring lookup or mutation"]
    N4["Content IDs and structured note results"]
    N0 --> N1
    N1 --> N2
    N2 --> N3
    N3 --> N4
```

Content-derived note IDs make repeated writes idempotent. Each query binds namespace and escapes SQL LIKE metacharacters. The optional official MCP SDK adapter exposes add_note, search_notes and delete_note; the stdlib JSON CLI exercises the same store.

The diagram describes the implemented path. Module responsibilities are deliberately small:

| Component | Responsibility |
| --- | --- |
| [`core.py`](sqlite_mcp_memory/core.py) | Domain algorithm, state transitions and public Python API |
| [`validation.py`](sqlite_mcp_memory/validation.py) | Shared finite-number, integer and text checks |
| [`__main__.py`](sqlite_mcp_memory/__main__.py) | JSON file/stdin adapter and structured error boundary |
| [`tests/`](tests/) | Behavioral regressions and executable CLI contract |
| [`DESIGN.md`](DESIGN.md) | Decisions, failure boundaries and regression map |

## API and input contract

`Memory.add(namespace, title, body), search(namespace, query, limit), delete(namespace, id)`

The Python entry point for the complete example is `sqlite_mcp_memory.core.run(data)`. Use the checked-in fixture as the canonical request shape; it includes the required top-level fields: `namespace`, `notes`, `query`.

The CLI writes one JSON result to stdout. Invalid requests write an `error` object to stderr and exit with status 2. Floating-point values in CLI JSON are rounded to 12 decimal places for portable fixture comparisons. The Python API retains normal floating-point precision.

For persisted state, pass a `database` path in the JSON request instead of the default `:memory:` database. Own and close Python API connections explicitly. The demo starts from an in-memory database, so rerunning it is deterministic.

## Validation

```bash
python -m unittest discover -s tests -v
python -m compileall -q sqlite_mcp_memory
```

Behavioral tests cover idempotent, namespace, literal wildcard, delete, bound, persist. CLI tests also verify the recorded fixture, stdin equivalence and invalid-input behavior. [GitHub Actions](.github/workflows/test.yml) runs the suite on Python 3.11, 3.12 and 3.13.

No latency, throughput or accuracy result is asserted without a measured workload. The example demonstrates behavior only.

## Container execution

```bash
docker build -t sqlite-mcp-memory .
docker run --rm sqlite-mcp-memory
docker run --rm -i sqlite-mcp-memory - < examples/demo.json
```

The image runs as an unprivileged user. It exposes no network port because the default entry point is a JSON command, not an HTTP service. Mount a writable volume and supply an appropriate path when retaining local state. Docker is optional; the standard-library quick start is sufficient for the core.

## MCP integration

Install the pinned official Python MCP SDK and run the local stdio server:

```bash
python -m pip install '.[mcp]'
NOTES_DATABASE=/absolute/path/notes.sqlite3 python -m sqlite_mcp_memory.server
```

Configure your MCP client to launch that command as a subprocess with the environment variable set. The server advertises `add_note(namespace, title, body)`, `search_notes(namespace, query, limit)` and `delete_note(namespace, ident)`. Keep the database writable only by its intended user. The namespace argument is logical organization, not an authorization credential.

```bash
python scripts/mcp_smoke.py
```

The smoke test performs real stdio initialization, tool discovery, persistence, cross-namespace isolation and deletion through the [official SDK](https://github.com/modelcontextprotocol/python-sdk). The core JSON CLI does not require the SDK.

## Limitations

Namespaces partition queries but do not authenticate callers. Search is a literal substring scan, not semantic retrieval. The stdio server is intended for a trusted local MCP client; it has no HTTP endpoint or remote access policy.

## Development and provenance

This is original, AI-assisted implementation work published with current commit timestamps. No third-party project has been relabeled as this repository. Python standard-library components retain their own upstream provenance; the optional MCP adapter uses the official SDK under its upstream license.

See [CONTRIBUTING.md](CONTRIBUTING.md) for changes and regression expectations, [SECURITY.md](SECURITY.md) for reporting guidance and [CODE_OF_CONDUCT.md](CODE_OF_CONDUCT.md) for participation rules.

## License

[MIT](LICENSE) · Maintained by [ryash14](https://github.com/ryash14).