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

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

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

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):

  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

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