# pie-ai-fastmcp-madison

A basic [FastMCP](https://gofastmcp.com) 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

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```json
{
  "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.