pie-ai-fastmcp-madison

A basic FastMCP server example, backed by Postgres.

It exposes:

• Tools — list_datasets(), get_record(dataset, id) and query_records(...)
• Resource — config://version
• Prompt — summarize(text)

A "dataset" is a table in the database's demo schema. Add a table there and it shows up in list_datasets automatically.

Layout

File	What it does
main.py	The MCP surface — tools, resource, prompt, and the connection-pool lifespan
db.py	All the SQL: pooling, catalog lookups, query building
db/init.sql	Schema and seed data, run by Postgres on first boot
client.py	A small MCP client that exercises the server end to end
ui.py / static/ui.html	The browser dashboard — holds MCP sessions open and relays notifications
lb/haproxy.cfg	Load balancer config: round-robin plus the session-affinity stick table

Run everything with Docker

docker compose up --build

That starts Postgres, waits for it to be healthy, starts the MCP server over HTTP, then runs client.py against it.

Changing db/init.sql? Postgres only runs the scripts in /docker-entrypoint-initdb.d/ when its data volume is empty, so edits are ignored on subsequent boots. Run docker compose down -v to drop the volume and re-seed.

The dashboard

docker compose up -d also starts a small web UI at http://localhost:8080. It is the easiest way to drive the whole demo:

• Request flow — a live diagram of client → lb → {server-a, server-b} → db. Each wire is two lanes: requests travel out along the top, notifications come back along the bottom. Every dot is a real event, so the return lane filling up while a call is still outstanding is the proof that MCP is not request/response. Transit durations are stretched to be visible; the tools answer in milliseconds.

  Steps are left behind: once traffic crosses a wire it stays tinted in that replica's colour and keeps a label saying what last travelled it and how much has (select from demo.pr… ×3). So the path a request took is still readable after the animation stops. Underneath, a numbered trace keeps every step with its full payload. Clear trace resets both.

• Sessions — open sessions and keep them open. Each row shows its Mcp-Session-Id, the replica it's pinned to, and a call counter. Call a session repeatedly and watch the id and replica stay put while the count rises.

• Infrastructure — HAProxy requests per replica and Postgres connections, refreshing every 2s

Two things the diagram makes visible that are easy to miss otherwise:

The load balancer is a decision point, not a pipe. Requests leave the client grey and only acquire their replica's colour as they exit the LB, which labels why it chose — round-robin · session just created for a brand-new session, stick-table hit · pinned afterwards, and stateless · new session each request in step 3. Open two sessions and run analyze on both: blue and orange travel the same wires at once, to different replicas, into one shared Postgres.

Tool calls cost more queries than they look like. get_record pulses the database twice and query_records three times, because _resolve_table checks the catalog before any name can be spliced into SQL. That is the price of the allowlist, and normally it's invisible.

ui.py is not an MCP client itself. The browser talks plain JSON to it, and it runs the real fastmcp.Client server-side exactly as client.py does — so the sessions are genuine and session affinity is demonstrated rather than faked.

When routing is broken, the flow halts red at the LB with the real Session terminated error and an explanation of the mechanism — while the HAProxy tiles below still read UP. That is the whole lesson on one screen: the infrastructure is healthy and the protocol is still broken.

The page itself is static/ui.html, bind-mounted into the container — edit it and reload the browser. ui.py is baked into the image, so changes there need docker compose up -d --build ui, not restart.

Scaling demo

docker compose up runs two server replicas (server-a, server-b) behind HAProxy, sharing one Postgres:

                        ┌─ server-a ─┐
client ──→ haproxy ─────┤            ├──→ db
           :8000        └─ server-b ─┘
           :8404 (stats)

client.py finishes by opening three sessions and printing which replica served each call:

Routing (3 sessions, 2 calls each):
  session 1: server-b / server-b   (pool 1 conns)
  session 2: server-a / server-a   (pool 1 conns)
  session 3: server-b / server-b   (pool 1 conns)

Two things are visible at once: the replica is the same within a session (affinity) and different across sessions (balancing).

Step 1 — break it

Comment out the three stick lines in lb/haproxy.cfg, then:

docker compose restart lb && docker compose run --rm client

!! McpError: Session terminated
!! A replica was handed a session it doesn't own, and answered 404.

MCP's HTTP transport is session-oriented. initialize creates a session in one replica's memory and returns an Mcp-Session-Id; the SSE GET /mcp opens a second connection, round-robin sends it to the other replica, and that replica returns 404 for a session it has never seen.

Step 2 — fix it

Restore the stick lines and docker compose restart lb. HAProxy learns the session id from the initialize response, remembers which replica issued it, and routes accordingly.

Note that balance hdr(Mcp-Session-Id) looks like the obvious fix and is wrong: hashing the id picks a replica with no relationship to the one that owns the session, so it fails about half the time.

Step 3 — or drop sessions entirely

Uncomment FASTMCP_STATELESS_HTTP in docker-compose.yml, comment the stick lines back out, and docker compose up -d --force-recreate server-a server-b lb. Every request gets a fresh transport, so plain round-robin works with no affinity at all.

What this costs is session identity, not two-way traffic. Progress and log notifications still arrive, because they travel on the tool call's own response stream rather than on the standalone GET /mcp. What you lose is continuity: the Mcp-Session-Id changes on every request, and two calls on the same client can be served by different replicas — visible in the dashboard as a session whose id and replica both change under it.

The database is the shared state

The replicas are interchangeable because they hold none. Scaling them isn't free, though — each keeps its own pool:

docker compose exec db psql -U demo -d demo \
  -c "select application_name, count(*) from pg_stat_activity where datname='demo' group by 1;"

Two replicas at min_size=1 means two connections idling; at max_size=5 under load it's ten. Multiply by replica count and this is the arithmetic that eventually puts a pooler like pgbouncer in front of Postgres.

HAProxy's stats page at http://localhost:8404 shows the same story from the infrastructure side.

Run the server locally

The server needs a database, so start that first:

uv sync
docker compose up -d db

# Run over stdio (the default transport)
uv run main.py

# Or via the FastMCP CLI
uv run fastmcp run main.py

# Explore interactively in the MCP Inspector (FastMCP v3 syntax)
uv run fastmcp dev main.py

Compose publishes port 5432, and DATABASE_URL defaults to postgresql://demo:demo@localhost:5432/demo, so no configuration is needed. Set DATABASE_URL to point somewhere else.

Use from a client

Point any MCP client (Claude Desktop, Claude Code, Cursor, ...) at the server:

{
  "mcpServers": {
    "madison": {
      "command": "uv",
      "args": ["run", "main.py"],
      "cwd": "/path/to/pie-ai-fastmcp-madison"
    }
  }
}

Postgres has to be running for this to work — the server exits at startup if it can't reach the database.