# Vizru MCP server

Reads the PostgreSQL mirrors of Vizru spreadsheets on Neon, for the two workflow
blocks that need relational access.

| Block | Uses |
|---|---|
| Relational Filter | `POST /api/query` — one read-only SELECT, rows back |
| Agent Node | `POST /api/query` as its `query_database` tool, and `GET /api/schema` to describe each configured spreadsheet |

`Sys\Workflow\Block\McpClient` in `v1-web-app` is the client. Its expectations are
the contract: HTTP basic auth, a **bare JSON array** of row objects from
`/api/query`, and a **200 with a JSON body** from `/api/schema`. Anything else is
read by the caller as the service being unavailable.

`POST /mcp` offers the same two capabilities as MCP tools over JSON-RPC, for
clients that speak the protocol rather than REST.

## Before you deploy: there is already an `mcp-server`

The `vizru-docker` compose project defines a service called `mcp-server`
(`D:\Platform\Vizru-Docker\mcp-server`, image `mcp-server:5.1`). It serves the
same two endpoints on 9101, plus write endpoints and an MCP SSE server on 9100,
and it points at a **local `mcp-postgres` container** rather than Neon.

Two containers cannot share a name, so decide which you are doing:

- **Replacing it** — the clean blocks only ever read, so `/api/execute` and the
  insert/update/delete/upsert endpoints have no caller left in the new code.
  Check nothing else uses them, and note that this service exposes MCP as
  JSON-RPC on `9101/mcp` rather than SSE on `9100`; anything pointed at 9100
  needs moving.
- **Running both** — set `MCP_CONTAINER_NAME` here and `McpServerUrl` in
  `config/env-*.php` to the same new name.

## Pointing the blocks at this server

Nothing to configure in the blocks themselves. `McpClient` falls back to
`http://mcp-server:9101` with `vizru` / `vizru_secret_key` when the platform
defines no `McpServerUrl` / `McpApiUser` / `McpApiSecret`, and `.env.example`
ships those same values — so a container named `mcp-server` on the platform's
network is picked up by both blocks with no further wiring. Override all three
in `config/env-*.php` for production, keeping them in step with `.env` here.

Two prerequisites are outside this service and easy to miss:

- **The mirrors have to be in the database this service reads.** `ss-sync-pipeline`
  writes them, and it points at whatever `PG_HOST` it is given. If it still
  writes to the local `mcp-postgres` while this service reads Neon, every query
  finds no `ss_*` tables and every schema lookup 404s.
- **Nothing else may hold the `mcp-server` container name** — see below.

## Configuration

Copy `.env.example` to `.env` and fill it in. Two values have to agree with the
platform or every block call fails:

| This service | Platform (`config/env-*.php`) |
|---|---|
| `MCP_API_USER` | `McpApiUser` |
| `MCP_API_SECRET` | `McpApiSecret` |
| container name + `9101` | `McpServerUrl` |

`McpServerUrl`, `McpApiUser` and `McpApiSecret` are optional on the platform
side; leaving them undefined falls back to `http://mcp-server:9101` with
`vizru` / `vizru_secret_key`. Set them in production.

Use Neon's **pooled** endpoint (the host containing `-pooler`). This service
holds a small pool of its own, and the direct endpoint has a lower connection
ceiling.

## Run it

### Option A — standalone

```bash
cp .env.example .env && docker compose up -d --build
```

It joins the platform's existing network as an external one. Check it:

```bash
docker compose logs -f mcp-server
```

### Option B — inside the platform's compose project

Drop the directory in next to the other services and add this to
`Vizru-Docker/docker-compose.yaml`, which matches the conventions there
(project-local network name, image tag variables, profiles, file secrets):

```yaml
  mcp-server:
    restart: always
    build:
      context: ./mcp-server
      dockerfile: Dockerfile
    container_name: mcp-server
    environment:
      - MCP_API_USER=vizru
      - STATEMENT_TIMEOUT_MS=10000
      - MAX_ROWS=500
    secrets:
      - neon-url
    expose:
      - 9101
    networks:
      - vizru-network
    image: ${DOCKER_IMAGE}mcp-server:${DOCKER_TAG}
    profiles:
      - dev
      - prod
```

Reading the URL and secret from files rather than the environment needs two
lines in `db.py` / `main.py` (`open("/run/secrets/neon-url").read().strip()`);
the rest is unchanged. Doing it that way also lets `mcp-postgres` be removed
from the project.

## Endpoints

```
GET  /health                      liveness plus a real database round trip
POST /api/query                   {"query": "SELECT ..."}  -> [ {...}, ... ]
GET  /api/schema?shortcode=EMP001 -> {"table":"ss_emp001","columns":[...]}
POST /mcp                         JSON-RPC: initialize, tools/list, tools/call
```

All except `/health` require basic auth.

A spreadsheet's short code names its mirror table: `EMP001` is `ss_emp001`,
lower-cased because Postgres folds unquoted identifiers and the blocks reference
these tables unquoted.

## What stops a bad query

Four layers, because the SQL is written by people and by language models:

1. **The block.** `SqlReadGuard.php` refuses anything but a single read-only
   SELECT before the request is even made, and the Agent Node additionally
   restricts the query to the spreadsheets that block was configured with.
2. **This service.** `guard.py` applies the identical rules again — the workflow
   engine is not the only thing that can reach this port.
3. **The session.** Every pooled connection runs with
   `default_transaction_read_only = on`, so Postgres itself refuses a write.
   This catches what a regex cannot: `SELECT … FOR SHARE` takes row locks and
   trips no keyword, and the database rejects it.
4. **The clock and the row count.** `statement_timeout` (10s by default) kills a
   runaway query, and `MAX_ROWS` (500) caps what comes back.

For a fifth, give the service a Postgres role with only `SELECT` on the `ss_*`
tables. Nothing here depends on being able to write.

## Local development

```bash
python -m venv .venv && . .venv/bin/activate     # Windows: .venv\Scripts\activate
pip install -r requirements.txt
DATABASE_URL='postgresql://...?sslmode=require' \
MCP_API_SECRET=dev \
uvicorn main:app --port 9101 --reload
```

## Files

```
main.py       FastAPI app: auth, the REST endpoints, the MCP entry point
tools.py      the two capabilities, shared by both surfaces
guard.py      read-only SQL check, mirroring SqlReadGuard.php
db.py         Neon connection pool, read-only session, row serialization
mcp.py        JSON-RPC framing for tools/list and tools/call
```