Postgres MCP Server + Recommender

An MCP server that lets any MCP-compatible client or agent — Claude Desktop, Claude Code, Cursor, or a custom LLM harness — do two things over one e-commerce Postgres database: read-only SQL analytics and ML recommendations (ALS collaborative filtering). Because it speaks the open Model Context Protocol, any agentic host that supports MCP can drive it — the same data, two capabilities, one server.

Analytics, an ML recommendation, and a refused DROP — all driven through the MCP server. Scripted walkthrough built with Remotion, using real values from the live database and trained model.

Quickstart

# 1. Postgres in Docker (schema.sql seeds the table + read-only role on first boot)
docker compose up -d

# 2. Python env + deps, then install this package (needed so the server imports cleanly)
uv venv --python 3.11 .venv
uv pip install "mcp[cli]" "psycopg[binary]" sqlglot pytest implicit scipy numpy pandas openpyxl
uv pip install -e .

# 3. Load the real UCI Online Retail dataset (~398k transactions)
#    Download to data/online_retail.xlsx first:
#    https://archive.ics.uci.edu/ml/machine-learning-databases/00352/Online%20Retail.xlsx
python scripts/load_data.py

# 4. Train + offline-eval the recommender (writes models/als.pkl)
python scripts/train_recs.py

# 5. Inspect all tools locally
mcp dev src/mcp_postgres_explorer/server.py

Connect a client

Any MCP-compatible host works (Cursor, Cline, Zed, or a custom agent using an MCP SDK). Claude is shown below as the tested example.

Use the Python from the .venv you installed into — clients launch the command directly and do not activate a virtualenv. On Windows that's .venv\Scripts\python.exe; on macOS/Linux .venv/bin/python. RECS_MODEL must be an absolute path so the recommender finds the model regardless of working directory.

Claude Code:

claude mcp add postgres-explorer \
  -e DATABASE_URL=postgresql://readonly:readonly@localhost:5432/demo \
  -e RECS_MODEL=/ABS/PATH/models/als.pkl \
  -- /ABS/PATH/.venv/bin/python /ABS/PATH/src/mcp_postgres_explorer/server.py

Claude Desktop — add to claude_desktop_config.json (Settings → Developer → Edit Config), restart:

{
  "mcpServers": {
    "postgres-explorer": {
      "command": "/ABS/PATH/.venv/bin/python",
      "args": ["/ABS/PATH/src/mcp_postgres_explorer/server.py"],
      "env": {
        "DATABASE_URL": "postgresql://readonly:readonly@localhost:5432/demo",
        "RECS_MODEL": "/ABS/PATH/models/als.pkl"
      }
    }
  }
}

Tools / resources / prompts

Name	Kind	Description
list_tables	tool	List tables in the public schema.
describe_table	tool	Columns and types for a table.
query	tool	Run a read-only SQL query (rejects any write/DDL).
recommend_for_user	tool	Top-k product recommendations for a customer (ALS).
similar_items	tool	"Customers also bought" item-item neighbors.
schema://public	resource	Full DB schema as text.
analyze_table	prompt	Template to profile a table.

Safety model (three read-only layers)

A DROP is rejected three independent ways:

1. DB role — the server connects as readonly, which only holds SELECT (see schema.sql).
2. Read-only transactions — SET default_transaction_read_only = on at the driver layer (db.py).
3. SQL-parser allowlist — guard.assert_readonly parses with sqlglot and permits only SELECT/WITH.

Unit-tested in tests/test_guard.py (run pytest -q).

Live in Claude Desktop: the agent can query freely but a DROP TABLE is refused by the guardrail.

Recommender

• Algorithm: ALS matrix factorization (implicit), 64 factors, trained on customer × product purchase confidence.
• Offline eval (leave-last-out, 4,247 held-out users): recall@10 = 0.1095, precision@10 = 0.0109.
• Serving: the trained model is pickled to models/als.pkl and loaded lazily by recs.py. Because it's trained on your own DB, run scripts/train_recs.py before serving — the model is not shipped.

Live in Claude Desktop: recommend_for_user returns ranked products with ALS scores for a customer.

License

MIT