# devvault-mcp

A small [MCP](https://modelcontextprotocol.io) server that gives Claude Code
structured access to DevVault's local Postgres database — inspect schema, run
read-only queries, make guarded writes, and seed dummy data for testing.

## Connection

`DATABASE_URL` is resolved in this order:

1. `process.env.DATABASE_URL`
2. `DEVVAULT_ENV_PATH` (path to an `.env` file), if set
3. `../devvault-backend/.env`

So the DB password stays in the backend `.env` and is **not** duplicated into
any MCP config.

## Tools

| Tool | Purpose |
| --- | --- |
| `db_info` | Which database/user we're connected to (no credentials shown). |
| `list_tables` | Public tables + approximate row counts. |
| `describe_table` | Columns (name, type, nullable, default) for a table. |
| `count_rows` | Exact row counts for the main DevVault tables. |
| `run_query` | One **read-only** statement (SELECT/WITH/EXPLAIN/SHOW), run in a `READ ONLY` transaction. Supports `$1,$2…` params. |
| `run_write` | One INSERT/UPDATE/DELETE. DDL and unscoped UPDATE/DELETE (no WHERE) are refused unless `allowDangerous=true`. |
| `seed_dummy` | Insert dummy rows (valid cuid ids + timestamps) into snippets/bookmarks/notes/commands/prompts, attached to existing users. |

### Safety guards

- `run_query` rejects anything that isn't read-only and blocks statement stacking (`;`).
- `run_write` refuses `DROP/TRUNCATE/ALTER/CREATE/GRANT/…` and refuses `UPDATE`/`DELETE`
  with no `WHERE` clause, unless you pass `allowDangerous: true`.
- Writes run inside a transaction and roll back on error.

## Registering with Claude Code

Already wired up via `../.mcp.json` (project scope):

```json
{
  "mcpServers": {
    "devvault-db": {
      "command": "node",
      "args": ["/Users/cookie/project-git/devvault_2/devvault-mcp/server.js"]
    }
  }
}
```

MCP servers load at Claude Code **startup**, so restart / reconnect the session
for the `devvault-db` tools to appear.

## Local checks (no MCP client needed)

```bash
npm install
node smoke.js     # connectivity + row counts
node mcptest.js   # full MCP handshake: list tools + call a few
```

## Dummy data

Seed rows created by `seed_dummy` all have titles beginning with `Dummy `.
To remove them:

```sql
DELETE FROM prompts  WHERE title LIKE 'Dummy %';
DELETE FROM commands WHERE title LIKE 'Dummy %';
DELETE FROM snippets WHERE title LIKE 'Dummy %';
DELETE FROM bookmarks WHERE title LIKE 'Dummy %';
DELETE FROM notes    WHERE title LIKE 'Dummy %';
```