# 🔐 MCP Server — secure AI access to Postgres & Google Sheets

> A Model Context Protocol server that lets **Claude, ChatGPT, Cursor** or any MCP client answer questions about company
> data — sales, customers, orders, spreadsheets — **without exporting files and without write access**. Read-only by
> design (SQL guard + read-only DB role), with row limits, pagination, PII masking, bearer-token auth for remote use and
> a full audit log.

![Agent answering from the database](docs/screenshots/05-agent-top-customers.png)

## The problem
Managers want to ask *"What were last month's top customers?"* or *"Which orders are late?"* in plain English.
Today that means CSV exports or giving an AI tool a production connection string. This server gives AI assistants a
**narrow, audited, read-only** window into the data instead.

## Tools, resources, prompts
| Type | Name | What it does |
|---|---|---|
| tool | `list_tables` | allowed tables with row counts and descriptions (sensitive tables are hidden) |
| tool | `describe_table` | columns, types, PII flags, 3 masked sample rows |
| tool | `run_readonly_query` | one `SELECT`/`WITH` statement, validated by an AST-based SQL guard; paginated (`limit`/`offset`/`next_offset`), row + byte caps, timeout |
| tool | `get_sales_summary` | revenue / orders / AOV for a period (`last_month`, `last_30_days`, `ytd`, `YYYY-MM`…) grouped by region, category, customer, segment or day |
| tool | `find_customer` | fuzzy search by name/company/email with lifetime revenue and last order |
| tool | `late_orders` | undelivered orders past their promised date, most overdue first |
| tool | `inactive_customers` | customers with no orders in N days (churn risk), by lifetime value |
| tool | `sheets_read_range` | read a named range / A1 range from a Google Sheet (service account, read-only scope) |
| resource | `schema://shop`, `glossary://business-terms` | schema docs + business definitions (what "revenue" means) |
| prompt | `weekly_sales_report`, `late_orders_check` | reusable analysis workflows |

All tools carry typed JSON input/output schemas and `readOnlyHint` annotations.

## Security model (defense in depth)
1. **SQL guard** (sqlglot AST): exactly one statement; only `SELECT`/set operations; rejects INSERT/UPDATE/DELETE/MERGE/DDL,
   `SELECT … INTO`, `FOR UPDATE`, `COPY`, `SET`, `PRAGMA`, `ATTACH`, transactions, dangerous functions (`pg_sleep`,
   `pg_read_file`, `load_extension`, `dblink`…), system catalogs, and tables outside `ALLOWED_TABLES`.
2. **Read-only database**: Postgres role `mcp_readonly` with `SELECT` grants only (not on `staff_salaries`) and
   `default_transaction_read_only`; every connection also runs `BEGIN READ ONLY` + `statement_timeout`.
   SQLite is opened with `mode=ro` + `PRAGMA query_only`. Tests bypass the guard on purpose to prove the DB still refuses.
3. **Result limits**: `MAX_ROWS` (500), `MAX_RESULT_BYTES`, pagination, query timeout.
4. **PII masking**: configurable columns (`PII_COLUMNS=email,phone`) are masked in every result (`m***@example.com`,
   `***67`); PII columns can't be wrapped in expressions/aliases to dodge the mask. `PII_MASKING=off` for trusted users.
5. **Auth**: Streamable HTTP requires `Authorization: Bearer <token>` (per-user tokens, constant-time compare); optional
   DNS-rebinding protection via `MCP_ALLOWED_HOSTS`.
6. **Audit log**: every tool call → JSONL with timestamp, user (token owner / `local-stdio`), tool, arguments, status
   (`ok` / `rejected` / `error`), row count, duration.

## Architecture
```mermaid
flowchart LR
    C1[Claude Desktop / Claude Code] -- stdio --> S
    C2[Cursor] -- stdio --> S
    C3[ChatGPT connectors / remote agents] -- Streamable HTTP + Bearer --> AUTH[Bearer auth] --> S
    subgraph S[MCP server · Python MCP SDK]
      T[Tools · Resources · Prompts] --> G[SQL guard<br/>AST allow-list]
      G --> LIM[Row/byte caps<br/>pagination · timeout]
      LIM --> PII[PII masking]
      T --> AUD[(Audit log JSONL)]
    end
    LIM -->|read-only role<br/>BEGIN READ ONLY| PG[(PostgreSQL / SQLite<br/>shop data)]
    T -->|spreadsheets.readonly| GS[(Google Sheets<br/>SalesTargets)]
```

## Screenshots
| | |
|---|---|
| ![](docs/screenshots/01-mcp-inspector-tools.png) MCP Inspector: typed, read-only tools | ![](docs/screenshots/03-mcp-inspector-delete-refused.png) `DELETE` refused by the guard |
| ![](docs/screenshots/04-mcp-inspector-pii-masked.png) PII masked + pagination | ![](docs/screenshots/06-agent-delete-refused.png) Assistant asked to delete a table |
| ![](docs/screenshots/07-agent-sheet-vs-db.png) Google Sheet targets vs DB actuals | ![](docs/screenshots/08-audit-log.png) Audit log |
| ![](docs/screenshots/11-http-bearer-auth.png) Remote HTTP with bearer token | ![](docs/screenshots/10-tests.png) 50 tests incl. Postgres |

Produced by `scripts/demo_screenshots.py`. Agent screenshots 05–07 use a real LLM (Kimi K3 on Amazon Bedrock) via
`scripts/ask_agent.py`; 11 uses the offline scripted plan (the provider was rate-limiting at capture time) — the
MCP calls, auth and audit entries are identical either way.

## Quick start (local, SQLite, 2 minutes)
```bash
python -m venv .venv && . .venv/bin/activate && pip install -r requirements.txt
python scripts/seed.py                         # data/shop.db with ~8k rows of fake data + data/sheets/SalesTargets.csv
npx @modelcontextprotocol/inspector --web $PWD/scripts/run_stdio.sh     # poke at it in MCP Inspector
python scripts/ask_agent.py "Top 5 customers by revenue last month and who hasn't ordered in 60 days?"
```
`ask_agent.py` is a tiny tool-calling agent (any OpenAI-compatible LLM via `LLM_BASE_URL/LLM_API_KEY/LLM_MODEL`;
scripted offline plan if unset) that prints each MCP tool call — useful to demo the server without a desktop client.

### Docker (Postgres + server)
```bash
docker compose up        # Postgres 17 seeded + read-only role, MCP over HTTP on http://localhost:8000/mcp
```

### Remote (Streamable HTTP)
```bash
MCP_API_TOKENS="alice@acme:$(openssl rand -hex 24)" python -m bizdata_mcp.server --transport http --host 0.0.0.0 --port 8000
```
Put it behind HTTPS (Caddy/nginx/Cloudflare Tunnel) and set `MCP_ALLOWED_HOSTS`.

## Client configuration
**Claude Desktop** (`claude_desktop_config.json`) — see [`docs/client-configs/claude_desktop_config.json`](docs/client-configs/claude_desktop_config.json):
```json
{ "mcpServers": { "business-data": {
    "command": "/ABSOLUTE/PATH/mcp-business-data-server/scripts/run_stdio.sh",
    "env": { "DATABASE_URL": "postgresql://mcp_readonly:CHANGE_ME@localhost:5432/shop" } } } }
```
**Claude Code**
```bash
claude mcp add business-data -e DATABASE_URL=sqlite:///$PWD/data/shop.db -- $PWD/scripts/run_stdio.sh
claude mcp add --transport http business-data https://mcp.example.com/mcp --header "Authorization: Bearer $MCP_TOKEN"
```
**Cursor** (`.cursor/mcp.json`) — see [`docs/client-configs/cursor_mcp.json`](docs/client-configs/cursor_mcp.json).
**ChatGPT** — add the HTTPS `/mcp` URL as a custom connector (developer mode) with the bearer token.

### Google Sheets
Create a service account, download its JSON key, share the sheet with the service-account email as **Viewer**, then set
`GOOGLE_APPLICATION_CREDENTIALS` and `SHEETS_SPREADSHEET_ID`. Scope is `spreadsheets.readonly`. Without them the tool
reads `data/sheets/<name>.csv` (same shape) so the demo runs offline.

## Acceptance criteria → evidence
| # | Criterion | Evidence |
|---|---|---|
| 1 | stdio + Streamable HTTP with bearer token | `test_stdio_transport`, `test_streamable_http_requires_bearer_token_and_audits_user`; screenshot 11 |
| 2 | Rejects writes/DDL/multi-statements; DB read-only anyway | `tests/test_sql_guard.py` (30+ attack & allow cases); `test_postgres_read_only_role_defense_in_depth`, `test_sqlite_connection_is_read_only_even_without_guard` |
| 3 | Descriptions + typed schemas; capped & paginated results | `test_every_tool_has_description_and_typed_schema`, `test_query_pagination_and_row_cap`, `test_byte_cap_truncates` |
| 4 | Configurable PII masking; audit log | `test_pii_masking_configurable`, `test_write_attempt_is_rejected_and_audited` |
| 5 | Sheets named range | `test_sheets_named_and_a1_range` (CSV mode; Google mode uses the Sheets v4 `values.get` API) |
| 6 | `docker compose up` + client snippets | `docker-compose.yml`, `docs/client-configs/` |
| 7 | ≥ 15 tests in CI | 50 tests; `docs/ci/github-actions-ci.yml` (GitHub Actions config with a Postgres service; copy to `.github/workflows/` to enable) |
| 8 | MCP Inspector screenshot | screenshots 01–04 |

## Tests
```bash
pytest -v                                                             # 49 tests (SQLite)
TEST_PG_ADMIN_URL=postgresql://postgres@localhost:5432/shop pytest -v   # + Postgres read-only-role test
python scripts/demo_screenshots.py                                    # scripted demo → docs/screenshots/*.png
```

## Portfolio write-up & demo video script
See [PORTFOLIO.md](PORTFOLIO.md).

## License
MIT — all data is fake.