Metadata-Version: 2.4
Name: amnesic
Version: 0.1.8
Summary: The MCP server with the most ironic name in the registry — persistent semantic memory for your SQL databases
Project-URL: Homepage, https://github.com/SurajKGoyal/amnesic
Project-URL: Repository, https://github.com/SurajKGoyal/amnesic
Project-URL: Issues, https://github.com/SurajKGoyal/amnesic/issues
Author-email: Suraj Goyal <sgoyal275@gmail.com>
License-Expression: MIT
License-File: LICENSE
Keywords: ai-tools,claude,database,llm,mcp,mssql,mysql,postgres,sql
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Requires-Python: >=3.11
Requires-Dist: click>=8.0
Requires-Dist: mcp[cli]>=1.0.0
Requires-Dist: rich>=13.0
Requires-Dist: sqlalchemy>=2.0
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9; extra == 'all'
Requires-Dist: pymssql>=2.2; extra == 'all'
Requires-Dist: pymysql>=1.1; extra == 'all'
Provides-Extra: dev
Requires-Dist: pytest-asyncio>=0.23; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Provides-Extra: mssql
Requires-Dist: pymssql>=2.2; extra == 'mssql'
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == 'mysql'
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9; extra == 'postgres'
Description-Content-Type: text/markdown

# amnesic — the MCP server with the most ironic name in the registry

**Persistent semantic memory for your SQL databases. The name is ironic — it remembers everything.**

*"The MCP server with the most ironic name in the registry. It's anything but amnesic — it remembers your database so your AI doesn't have to."*

> 🔒 **Read-only by design.** amnesic refuses to execute `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `ALTER`, `CREATE`, `EXEC`, `MERGE`, `GRANT`, `REVOKE` — and any write statement smuggled inside a `WITH` CTE. Two layers of defense: static SQL analysis rejects the statement before connecting, **and** every query runs inside a transaction that is immediately rolled back. Safe to point at prod. [Details ↓](#safety--read-only-enforcement)

---

## The problem

Every session with an AI starts cold. You spend the first few minutes re-explaining what tables exist, what a `status` column value of `3` means, which FK connects `orders` to `users`. Then the session ends, and you do it all over again tomorrow.

**amnesic fixes this.** It gives your AI a persistent SQLite knowledge store — one per database — that survives across sessions. Annotate a status enum once; every future session sees those labels automatically. Discover FK relationships once; every future JOIN query uses that graph.

---

## Quickstart (90 seconds)

```bash
pipx install "amnesic[mssql]"   # swap in [postgres], [mysql], [sqlite], or [all]
amnesic init                    # interactive wizard
```

The wizard:
- Asks for your database type, host, and credentials
- Tests the connection before saving anything
- Stores the password securely in `~/.config/amnesic/.env` (chmod 600)
- Writes the connection block to `~/.config/amnesic/connections.toml`

Then [add amnesic to your AI client](#add-to-your-ai-client) and restart.

<details>
<summary><b>Don't have <code>pipx</code>? Or prefer <code>uv</code> / plain <code>pip</code>?</b></summary>

<br/>

**Install `pipx`** (one-time):

```bash
brew install pipx                                  # macOS
sudo apt install pipx                              # Linux (Debian/Ubuntu)
python -m pip install --user pipx                  # Windows / generic
```

**Or use `uv`** (single-binary alternative — fast, no Python required):

```bash
brew install uv                                            # macOS
curl -LsSf https://astral.sh/uv/install.sh | sh            # Linux / macOS
powershell -c "irm https://astral.sh/uv/install.ps1 | iex" # Windows

uv tool install "amnesic[mssql]"
```

**Or plain `pip`** (installs into your active Python env):

```bash
pip install "amnesic[mssql]"
```

</details>

After install, `amnesic --help` works from any terminal.

### Where amnesic stores things

| File | macOS / Linux | Windows |
|---|---|---|
| Config | `~/.config/amnesic/connections.toml` | `%APPDATA%\amnesic\connections.toml` |
| Secrets | `~/.config/amnesic/.env` (chmod 600) | `%APPDATA%\amnesic\.env` (user profile ACL) |
| Knowledge | `~/.config/amnesic/knowledge_<name>.db` | `%APPDATA%\amnesic\knowledge_<name>.db` |

Set `$AMNESIC_HOME` (or `$XDG_CONFIG_HOME` on Linux) to override the location.

### Adding more connections later

```bash
amnesic add          # add another connection to existing config
amnesic test         # verify all connections
amnesic test orders.prod  # verify one connection
```

### Setting and rotating passwords

`amnesic init` and `amnesic add` save your password automatically — for the typical setup flow, you never need to think about this section.

Use `set-secret` when you need to change a stored password later — IT rotated it, you mistyped it during setup, or you're hand-editing the config.

```bash
$ amnesic set-secret ORDERS_PROD_PASSWORD
Value: ****            ← hidden input (your typing is invisible)
Confirm: ****
✓ Set ORDERS_PROD_PASSWORD in ~/.config/amnesic/.env
```

**What's the variable name?** It's the env var your `connections.toml` references for that connection's password. The wizard auto-generates these as `<CONNECTION_NAME_UPPERCASE_WITH_UNDERSCORES>_PASSWORD`:

| Connection name | Generated env var |
|---|---|
| `orders.prod` | `ORDERS_PROD_PASSWORD` |
| `analytics` | `ANALYTICS_PASSWORD` |
| `drive.staging` | `DRIVE_STAGING_PASSWORD` |

To see the exact name your config uses, check `~/.config/amnesic/connections.toml` — anything inside `${...}` is the variable to pass to `set-secret`.

**Under the hood**: writes (or replaces) the line in `~/.config/amnesic/.env`, sets file permission to `chmod 600` (only your user can read it), preserves all other entries.

---

## Add to your AI client

Once amnesic is installed with the right driver extras (see [Quickstart](#quickstart-90-seconds)), the `amnesic` command is on your PATH. Use the same snippet across every MCP client:

### Claude Code

Add to `~/.claude/mcp.json`:

```json
{
  "mcpServers": {
    "amnesic": {
      "command": "amnesic"
    }
  }
}
```

### Claude Desktop

Add to your platform's Claude Desktop config:

- **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows**: `%APPDATA%\Claude\claude_desktop_config.json`
- **Linux**: `~/.config/Claude/claude_desktop_config.json`

```json
{
  "mcpServers": {
    "amnesic": {
      "command": "amnesic"
    }
  }
}
```

### Cursor

Add to `.cursor/mcp.json` in your project (or `~/.cursor/mcp.json` globally):

```json
{
  "mcpServers": {
    "amnesic": {
      "command": "amnesic"
    }
  }
}
```

### Without a global install (ephemeral)

If you'd rather not install amnesic on your system, use `uvx` or `pipx` to fetch it each time the MCP client starts. Note the driver extras must be passed explicitly:

```json
// uvx — requires `uv` installed (see Install section for per-OS instructions)
{
  "mcpServers": {
    "amnesic": {
      "command": "uvx",
      "args": ["--from", "amnesic[mssql]", "amnesic"]
    }
  }
}

// pipx — usually pre-installed via Homebrew or system package manager
{
  "mcpServers": {
    "amnesic": {
      "command": "pipx",
      "args": ["run", "--spec", "amnesic[mssql]", "amnesic"]
    }
  }
}
```

Swap `mssql` for `postgres`, `mysql`, or `all` to match the driver(s) you need.

### VS Code (with MCP extension)

Add to `.vscode/mcp.json`:

```json
{
  "servers": {
    "amnesic": {
      "type": "stdio",
      "command": "amnesic"
    }
  }
}
```

---

## Tools

| Tool | Description |
|------|-------------|
| `db_list_connections()` | List all configured connections (no secrets exposed) |
| `db_list_tables(connection)` | All known tables with descriptions and column counts |
| `db_search(query, connection, target, limit)` | BM25 search over table/column descriptions and aliases |
| `db_get_schema(table, connection)` | Column schema merged with saved annotations |
| `db_query(sql, connection)` | Execute a read-only SELECT query |
| `db_annotate(table, connection, ...)` | Persist semantic annotations for tables/columns |
| `db_sync_knowledge(from, to)` | Copy annotations between connections (e.g. staging → prod) |
| `db_discover_relationships(connection)` | Discover all FK relationships from the live DB |
| `db_get_relationships(table, connection)` | Navigate the FK graph for JOIN planning |

---

## Searching the knowledge base

For large schemas, `db_list_tables` is impractical — you'd dump 500+ rows into Claude's context. Use `db_search` to find the relevant tables/columns by keyword instead:

```
"What table tracks customer payments?"
  → db_search("payments")
    Top results:
      - dbo.payments  (table) "Customer payment records..."
      - dbo.orders.payment_method  (column) "Mode of payment..."
```

`db_search` uses **SQLite FTS5 with BM25 ranking** — fast, local, no embeddings or external services. Search syntax supports:

| Syntax | Effect |
|--------|--------|
| `payment` | Match the word (with stemming — also matches "payments", "paying") |
| `"payment method"` | Exact phrase |
| `pay*` | Prefix match — "payment", "payable", etc. |
| `payment AND status` | Both terms required |
| `payment OR refund` | Either term |

Results return ranked table/column rows with descriptions and highlighted snippets.

---

## The knowledge layer

The core differentiator. Every annotation survives restarts, model updates, and new sessions.

### Session 1 — you discover something

```
You: What does status=3 mean in the orders table?
AI: Let me check. [runs db_query: SELECT DISTINCT status FROM dbo.orders]
    I see values 1, 2, 3, 4. Let me look at some examples...
    Based on the data, 3 appears to be "cancelled".

You: Save that. And status=1 is "pending", 2 is "confirmed", 4 is "delivered".
AI: [calls db_annotate]
    db_annotate(
      table="dbo.orders",
      column="status",
      column_description="Order lifecycle status",
      enum_values={"1": "pending", "2": "confirmed", "3": "cancelled", "4": "delivered"}
    )
    Saved. Future sessions will see these labels automatically.
```

### Session 2 — the knowledge is already there

```
You: How many cancelled orders are there this month?
AI: [calls db_get_schema("dbo.orders")]
    Schema response includes:
      column: "status"
      description: "Order lifecycle status"
      enum_values: {"1": "pending", "2": "confirmed", "3": "cancelled", "4": "delivered"}

    [writes correct SQL immediately]
    SELECT COUNT(*) FROM dbo.orders WHERE status = 3 AND ...
```

No re-discovery. No wasted turns. The annotation persisted.

---

## Relationship graph

Understand your schema's JOIN structure once, reuse it forever.

```
AI: [db_discover_relationships(connection="orders.prod")]
    Discovered 47 foreign key relationships.

AI: [db_get_relationships(table="orders", depth=2)]
    neighbors:
      orders → users (via user_id → id)
      orders → order_items (via id ← order_id)
    paths:
      orders -> users
      orders -> order_items
      order_items -> products
```

Now the AI knows exactly how to JOIN across your schema without guessing.

---

## Sync between environments

Build up annotations in staging, then promote to prod:

```
db_sync_knowledge(from_connection="orders.staging", to_connection="orders.prod")
```

Returns `{synced: [...], skipped: [{table, reason}], warnings: [{table, column, reason}]}`.

Tables missing from the target schema cache are skipped with a clear reason. Columns missing from target schema are warned but don't block the rest of the sync.

---

## Advanced: hand-edit the TOML

If you prefer to manage the config file yourself, generate a blank template:

```bash
amnesic init --template
```

This writes `~/.config/amnesic/connections.toml` with commented examples and exits — no wizard. Edit the file directly:

```toml
# ~/.config/amnesic/connections.toml

# Nested style: [connections.product.env]
[connections.orders.prod]
driver = "mssql"
server = "localhost"
port = 11433
database = "OrdersDB"
user = "${ORDERS_USER}"
password = "${ORDERS_PROD_PASSWORD}"
tunnel_script = "~/.scripts/mssql-tunnel.sh"     # macOS / Linux (bash)
# tunnel_script = "C:/scripts/mssql-tunnel.ps1"  # Windows (PowerShell)

[connections.orders.staging]
driver = "mssql"
server = "localhost"
port = 11434
database = "OrdersDB_Staging"
user = "${ORDERS_USER}"
password = "${ORDERS_STAGING_PASSWORD}"

# Flat style: [connections.name]
[connections.analytics]
driver = "postgres"
server = "analytics.company.com"
port = 5432
database = "warehouse"
user = "${ANALYTICS_DB_USER}"
password = "${ANALYTICS_DB_PASSWORD}"

# SQLite — no credentials needed
[connections.local]
driver = "sqlite"
database = "/absolute/path/to/local.db"       # macOS / Linux
# database = "C:/path/to/local.db"            # Windows (use forward slashes)
```

Use `${ENV_VAR}` for credentials — never hardcode passwords.

Secrets are loaded from `~/.config/amnesic/.env` automatically (format: `KEY=VALUE`, one per line, `#` for comments). For each `${VAR_NAME}` referenced in your TOML, populate the matching `.env` entry with [`amnesic set-secret VAR_NAME`](#setting-and-rotating-passwords) (hidden input, chmod 600), or write `.env` yourself.

Canonical connection names use dot notation: `orders.prod`, `orders.staging`, `analytics`, `local`.

---

## Supported databases

| Database | Driver | Extra |
|----------|--------|-------|
| PostgreSQL | psycopg2 | `pip install "amnesic[postgres]"` |
| MySQL / MariaDB | pymysql | `pip install "amnesic[mysql]"` |
| Microsoft SQL Server | pymssql | `pip install "amnesic[mssql]"` |
| SQLite | built-in | no extra needed |

---

## Safety & read-only enforcement

amnesic is built to be safe to point at production databases.

### Why your AI can't damage your data

Every query passes through **two independent layers** before reaching the database:

1. **Static analysis** (in `amnesic/readonly.py`) — the SQL is tokenized and rejected if it contains any of:
   `INSERT`, `UPDATE`, `DELETE`, `DROP`, `TRUNCATE`, `ALTER`, `CREATE`, `EXEC`, `EXECUTE`, `MERGE`, `BULK`, `GRANT`, `REVOKE`, `DENY`.
   This includes write statements smuggled inside CTEs (`WITH x AS (SELECT ...) UPDATE ...` is caught and refused).
2. **Transaction rollback** — even if a write statement somehow gets past the static check, the query runs inside `BEGIN TRANSACTION ... ROLLBACK` so nothing is ever committed. Belt and suspenders.

Only `SELECT` and `WITH ... SELECT` reach the database. Comments are stripped before analysis so `/* DELETE FROM users */` can't be used to hide an attack.

### Other safety measures

- **No credentials in responses**: `db_list_connections` strips passwords and usernames from its output. The AI can see *which* connections exist, never *how to authenticate* to them.
- **Credentials via env vars only**: `${ENV_VAR}` expansion at config-load time — passwords never touch `connections.toml` on disk.
- **Secure `.env` storage**: on macOS/Linux `chmod 0o600` (owner read/write only); on Windows the `.env` lives in `%APPDATA%` which is restricted to your user profile by Windows ACL.
- **Identifier validation**: table/schema/database names are checked against `[A-Za-z0-9_]+` before any string interpolation into SQL.
- **Tested**: 40+ unit tests in `tests/test_readonly.py` cover every write keyword, comment-stripping edge case, CTE-with-write attempts, semicolon-separated multi-statements, and identifier injection attempts. `pytest tests/test_readonly.py` to verify on your machine.

---

## Roadmap

What's coming: knowledge lifecycle management (v0.2 — `db_deprecate`, drift detection, export/import for team handoff), query intelligence (v0.3 — `db_explain`, query history), team sharing (v0.4), and more. See [ROADMAP.md](./ROADMAP.md) for the full picture.

Have an idea? [Open an issue.](https://github.com/SurajKGoyal/amnesic/issues/new)

---

## Track usage

[pypistats.org/packages/amnesic](https://pypistats.org/packages/amnesic)

---

## License

MIT — see [LICENSE](LICENSE).

---

## MCP Registry

This server is registered on the [official MCP Registry](https://registry.modelcontextprotocol.io).

```
mcp-name: io.github.SurajKGoyal/amnesic
```
