# penelope-mcp

An MCP server that lets Claude answer natural-language questions about NER car
telemetry by writing SQL against the Penelope databases.

**Status: working over stdio and as a hosted LAN server.** Ten tools, read-only.

## Install

```sh
python3 -m venv .venv && .venv/bin/pip install -e .
```

### Local (stdio)

`.mcp.json` in this repo registers the server for this project. To use it from
anywhere instead:

```sh
claude mcp add --scope user penelope /Users/chrispyle/NER/penelope-mcp/.venv/bin/penelope-mcp
```

### Hosted (LAN)

One process serves both the MCP endpoint and the export files:

```sh
export PENELOPE_TOKENS="chris:$(openssl rand -hex 16),jack:$(openssl rand -hex 16)"
export PENELOPE_SIGNING_KEY="$(openssl rand -hex 32)"
export PENELOPE_PUBLIC_URL="http://penelope.local:8000"
.venv/bin/penelope-mcp-serve
```

Teammates then point a client at it — no repo clone, no database credentials:

```json
{ "mcpServers": { "penelope": {
    "type": "http",
    "url": "http://penelope.local:8000/mcp",
    "headers": { "Authorization": "Bearer <their token>" } } } }
```

### Docker (recommended for anything long-lived)

```sh
cp .env.example .env      # then fill in PENELOPE_TOKENS and PENELOPE_SIGNING_KEY
docker compose up -d --wait
```

`--wait` blocks until the container reports healthy, so it fails loudly instead
of returning while the server is still broken.

| Command | Does |
| --- | --- |
| `docker compose up -d --wait` | Start, block until healthy |
| `docker compose down` | Stop and remove the container (exports survive) |
| `docker compose restart` | Restart in place |
| `docker compose ps` | Status and host port |
| `docker compose logs -f` | Follow logs |
| `docker compose up -d --build` | Rebuild after a code change, then restart |
| `docker compose down -v` | Stop **and delete all exported files** |

The container runs as a non-root user (uid 10001), binds `0.0.0.0:8000`
internally, and keeps exports on a named volume so signed URLs issued before a
restart keep working. Set `PENELOPE_HOST_PORT` to publish somewhere other than
3050.

#### Things that will bite you

**You usually don't need `PENELOPE_PUBLIC_URL`.** Leave it unset and each export
link is built from that request's `Host` header — which already records exactly
how the client reached you, published port included. Connect via
`127.0.0.1:3050` and links come back on `127.0.0.1:3050`; connect via
`10.0.0.102:3050` and they come back on that. Set it only when clients reach the
server by a name it cannot observe: behind a proxy that rewrites `Host` without
setting `X-Forwarded-Host`. (`X-Forwarded-Host` and `X-Forwarded-Proto` are
honored, so an ordinary TLS-terminating proxy needs no configuration either.)

If you *do* set it, it must be the URL **clients** use — never `localhost:8000`,
which only resolves inside the container. Getting that wrong is the nastiest
failure in this server: every tool call still succeeds and only the download
fails, silently, from the client's side.

**Set `PENELOPE_SIGNING_KEY` explicitly.** Unset, the server generates a random
one per start, so every outstanding export link breaks on restart — which
containers do far more often than a terminal session does.

**The container needs its own route to the database.** It reaches
`server.finishlinebyner.com:59021` through Docker's NAT, not your host's network
stack, so a split-tunnel VPN that covers your Mac may not cover the container.
If host tools work and the container reports `Connection refused`, that's the
cause, not a config error. Check with:

```sh
docker compose exec penelope-mcp python -c \
  "import socket; socket.create_connection(('server.finishlinebyner.com', 59021), 5)"
```

**Use an IP or a real DNS name, not `.local`.** Claude Code's MCP client does
not resolve mDNS names — `http://mymac.local:3050/mcp` times out after 30 s even
though `curl` resolves the same name in milliseconds. Register the server by LAN
IP (`http://10.0.0.102:3050/mcp`) or a real DNS A-record. Note a DHCP lease will
eventually change; a router reservation or a DNS entry saves re-registering
everyone.

Misconfiguration exits with a single clear line rather than a traceback, so
`docker compose logs` tells you what to fix even under a restart loop.

Auth is a static bearer token, deliberately — this is a LAN tool, not a public
service. Tokens are named so one person can be revoked without rotating everyone,
and `GET /whoami` confirms a token works without an MCP handshake. The server
**refuses to start** without `PENELOPE_TOKENS`: defaulting to open would hand
arbitrary SQL against the car database to anyone who can reach the port.

## Tools

Three tiers, because the scarce resource is context, not database time.

**1. Discovery** — cheap, resolves a phrase into an exact topic name.

| Tool | Purpose |
| --- | --- |
| `car_info` | Which database is being read, and its tables |
| `search_topics(query, limit, offset)` | Substring/fuzzy topic search; paginated, reports total matches + namespaces |
| `browse_topics(prefix, limit)` | `ls` for the topic tree: immediate children + descendant counts |
| `describe_topic(topic)` | Profile one topic: span, arity, value range, latest samples |
| `list_runs(limit)` | Recent logging sessions, newest first |
| `describe_table(table)` | Columns and types for one table |

**2. Export** — returns a link and statistics, never rows. Use for anything
destined for a chart.

| Tool | Purpose |
| --- | --- |
| `export_series(topics, start/end \| run_id, bucket, index, format)` | Bucketed or raw export to CSV/Parquet |
| `export_query(sql, format)` | Same, for arbitrary read-only SQL |

**3. Escape hatch** — rows inline, capped at 1000, charged to context.

| Tool | Purpose |
| --- | --- |
| `get_series(topic, start, end, bucket, index)` | `time_bucket` downsampling: avg **and** min/max |
| `run_query(sql)` | Arbitrary read-only SQL |

Every tool takes an optional `car`. Both `get_series` and bucketed exports return
min/max alongside avg on purpose: averaging alone hides the spikes and dropouts
that matter most in telemetry.

## How exports keep context flat

The problem: 211k rows is ~5M tokens inline. Even the 1000-row cap on
`get_series` costs ~25k.

So the rows never enter context. `export_series` writes a file, and returns a
signed URL plus a *statistical profile* — n, nulls, true min/max, p01/p50/p99,
median sample interval, largest dropout gap. That profile is what lets a model
choose axis limits and catch a millivolts-vs-volts error without reading a single
row. The agent then reads the URL from its own sandbox:

```python
df = pd.read_csv(URL, parse_dates=["bucket"])
```

Measured against `BMS/Pack/SoC` on 25A:

| Window | Rows | File | Response |
| --- | --- | --- | --- |
| 1 day, 1 s buckets | 12,633 | 0.6 MB | **314 tokens** |
| 120 days, 1 s buckets | 170,152 | 7.8 MB | **322 tokens** |
| 120 days, raw | 211,001 | 11.1 MB | **318 tokens** |

17× the data, the same context cost. Rows stream from a server-side cursor to
disk, so an export is bounded by `PENELOPE_EXPORT_MAX_ROWS`, not by RAM.

Bucketed exports are **wide** (one row per bucket; `__avg`/`__min`/`__max` per
topic). `bucket=null` gives **long** raw output — raw samples from different
topics don't share timestamps, so they can't be aligned into columns.

Export URLs carry an HMAC signature and an expiry, and are served *without* the
auth header: a sandbox running `pd.read_csv(url)` has no way to send one, so the
signature is the credential. Files are swept on each export once they pass
`PENELOPE_EXPORT_TTL` or the directory exceeds `PENELOPE_EXPORT_MAX_BYTES`.

## The topic-naming rule

Telemetry is keyed by ~1500 hierarchical topic names, many confusingly similar
(`BMS/PerCell/Alpha/3/Volts/5` vs `BMS/PerCell/Beta/3/S_Volts/5`). A plausible
but wrong topic is the main failure mode, so the server instructions require the
model to:

1. **Always name the exact topic(s) it queried, verbatim, in its answer.**
2. **Ask the user which topic they meant** when the question is ambiguous —
   rather than guessing or silently averaging across a family of topics.
3. Say a topic doesn't exist rather than substituting a different one.

`search_topics` supports this. It substring-matches, case-insensitively and in
any order, and reports three things a model must read before trusting the list:

- **`total_matches` vs `returned`.** Broad words match far more than one page:
  `"volt"` matches **332** of 25A's 1502 topics. Returning 40 of those while
  implying that was all of them is how a model confidently plots the wrong
  thing, so `truncated` is set and the note says so outright.
- **`namespaces`** — a two-segment prefix breakdown of *all* matches, computed
  in SQL so a broad search doesn't transfer every name. For `"volt"`:
  `BMS/PerCell` 280, `VCU/eFuses` 11, `BMS/Cells` 7, `BMS/Segment_Volt` 5. That
  turns an unusable list into an obvious next query.
- **`mode`** — how the hits were found:

| Mode | Meaning |
| --- | --- |
| `all-terms` | Every term appears in the name. A real hit. |
| `any-term` | Nothing contained all terms, so they're OR-ed. Near misses. |
| `fuzzy` | No substring matched at all; approximate per-segment matching. |

`"pack temp"` finds nothing on 25A — pack temperature is `BMS/Segment_Temp/0..N`
— so it falls back to `any-term`. A typo like `"volatge"` matches no substring at
all and falls back to `fuzzy`, which still finds `VCU/LV/voltage`.

Broad searches paginate: pass `offset`, or the `next_offset` the response hands
back. Ordering is `(length(name), name)` — total and stable — so pages never
repeat or skip a topic between calls.

Fuzzy matching is stdlib `difflib`, scored against each `/`-separated **segment**
rather than the whole name — whole-name similarity scores `"volt"` against
`BMS/Segment_Volt/0` near zero on length alone. It costs ~35 ms over 1502 names,
after a one-time 350 ms vocabulary fetch that's cached for the client's lifetime.
Postgres would do this better with `pg_trgm`, but that extension is available and
*not installed*, and `CREATE EXTENSION` needs privileges the `readonly` account
doesn't have.

## Browsing the topic tree

Search finds; `browse_topics` explores. The two are not interchangeable, because
of how the vocabulary is shaped on 25A:

| Name depth | 1 | 2 | 3 | 4 | 5 | 6 segments |
| --- | --- | --- | --- | --- | --- | --- |
| Topics | 1 | 4 | 213 | 252 | 111 | **921** |

Most names are six segments deep, so a namespace can hold hundreds of topics
behind two or three children. `BMS/PerCell` is the extreme case — **970 of the
1502 topics**, 65% of the whole vocabulary, behind exactly two children:

```
browse_topics()                    -> BMS(1105) VCU(190) SYS_tpu(58) DTI(45) …
browse_topics("BMS")               -> PerCell(970) Segment_Onboard_Temps(30) …
browse_topics("BMS/PerCell")       -> Alpha(485) Beta(485)
browse_topics("BMS/PerCell/Alpha") -> 0(97) 1(97) 2(97) 3(97) 4(97)
browse_topics("BMS/Segment_Volt")  -> 0* 1* 2* 3* 4*        (* = a real topic)
```

`search_topics("BMS/PerCell")` returns 40 of those 970 as flat leaf names
(`BMS/PerCell/Alpha/0/Burning/11` and 39 siblings) — the least useful possible
view. Browsing returns two lines. That's the whole reason the tool exists.

Each child reports `topics` (count at or below it), so you can see where the mass
is before descending, and `is_topic` — a path can be both a folder and a logged
topic. Numeric segments sort naturally (`0,1,2,3,10,11`, not `0,1,10,11,2,3`).
It runs entirely against the cached topic list, so it costs no query.

## Which car

Every tool takes an optional `car` argument. Unset, it reads the default:
`NER_DB_CAR` if set, else `LATEST_CAR` in `config.py` (currently `V25A`). Bump
that constant when a new car comes online. The server instructions tell the model
to omit `car` unless the user names one, so year-over-year archaeology is
available without every question paying for it.

| Enum | Database | Topic table | Topics |
| --- | --- | --- | --- |
| `Car.V22A` | `penelope22a` | `"dataType"` | 151 |
| `Car.V24A` | `penelope24a` | `data_type` | 1412 |
| `Car.V25A` | `penelope25a` | `data_type` | 1502 |

## Schema (verified against `penelope25a`)

Four tables in `public`:

| Table | Columns |
| --- | --- |
| `data` | `values` (float[]), `"time"`, `"dataTypeName"`, `"runId"` |
| `data_type` | `name` — topic names |
| `run` | `id`, `"runId"`, `"driverName"`, `"locationName"`, `notes`, `"time"` |
| `_prisma_migrations` | ignore |

### Gotchas

- **`data` is a TimescaleDB hypertable** (~434M rows on 25A), compressed with
  `segmentby = "dataTypeName"`, `orderby = time DESC`. This is why filtering by
  topic is cheap at *any* time range — a per-topic `count(*)` over all 434M rows
  answers from compressed batch metadata in ~0.1 s.
- **The join key**: `data."runId"` holds a UUID matching **`run.id`** — *not*
  `run."runId"`, which is an unrelated small integer counter. Joining the
  same-named columns is the single easiest mistake to make here.
- **`runId` is not indexed or segmented**, only `"time"` is. A bare
  `WHERE "runId" = ...` scans all 434M rows and hits the statement timeout;
  bounding it by `"time"` lets chunk exclusion work and takes it from >30 s to
  0.2 s. This is why `resolve_run_window` looks only 24 h past a run's start.
- **Quoting**: `"dataTypeName"`, `"runId"`, `"time"`, `"driverName"` are camelCase
  and need double quotes. `values` and `name` do not.
- **Units are not in the database.** `units_22a.tsv` is bundled and used to
  annotate topics opportunistically, but it was written for 22A: ~99/151 of its
  names still exist on 24A, 72/151 on 25A. No unit shown means unknown.
- **`driverName` / `locationName` are empty strings** on all recent runs, so
  "who was driving" is generally unanswerable.
- **Data is batch-uploaded after test days.** Nothing here is live; the newest
  rows can lag today by over a week.
- 22A's `data` table has an extra `id` column that later cars dropped.

## Guardrails

Model-authored SQL goes straight to `run_query`, so:

- Queries run in a Postgres `READ ONLY` transaction — writes are rejected
  server-side, not by inspecting the SQL string. (Verified: `CREATE TABLE` →
  `cannot execute CREATE TABLE in a read-only transaction`.)
- `statement_timeout` is 30 s. The worst measured query — `avg(values[1])`
  grouped over all 434M rows, forcing full decompression — took ~17 s.
- Results cap at 1000 rows; the response sets `truncated` when rows were dropped.
  Exports get their own, much larger cap and a 120 s timeout, and **error rather
  than truncate** — a silently short chart is worse than a failed call.
- DB errors come back as a one-line message, not a multi-page traceback.

There is deliberately **no SQL string validation**. The read-only transaction is
the real boundary; a parser would only add false negatives.

## Configuration

| Variable | Default |
| --- | --- |
| `NER_DB_SERVER` | `server.finishlinebyner.com:59021` |
| `NER_DB_USER` | `readonly` |
| `NER_DB_PASSWORD` | (baked-in read-only password) |
| `NER_DB_CAR` | unset → `LATEST_CAR` |

Hosted mode only:

| Variable | Default |
| --- | --- |
| `PENELOPE_TOKENS` | **required** — `name:token,name:token` |
| `PENELOPE_SIGNING_KEY` | random per start (export links die on restart) |
| `PENELOPE_PUBLIC_URL` | unset → derived per-request from `Host` (usually correct) |
| `PENELOPE_HOST` / `PENELOPE_PORT` | `0.0.0.0` / `8000` |
| `PENELOPE_EXPORT_DIR` | `<tmp>/penelope-exports` |
| `PENELOPE_EXPORT_TTL` | `3600` s |
| `PENELOPE_EXPORT_MAX_BYTES` | `5000000000` |
| `PENELOPE_EXPORT_MAX_ROWS` | `5000000` (errors rather than truncating) |
| `PENELOPE_EXPORT_TIMEOUT_MS` | `120000` |

The server is reachable from NEU's network; off campus you'll need the VPN or a
local mirror (`~/NER/penelope` has a compose file and a `backup.sql` dump).

## Tests

```sh
.venv/bin/pip install -e '.[dev]'
.venv/bin/python -m pytest -q          # unit only, offline
.venv/bin/python -m pytest -q -m e2e   # end to end, needs the database
```

**77 unit tests**, no database required: token signing (tamper, expiry, path
traversal), token parsing and the auth middleware, the export writer's profiling,
row cap, and retention sweep, and topic search's namespace grouping, fuzzy
matching, pagination, and tree browsing.

**19 end-to-end tests** in `tests/test_e2e.py` that spawn a real
`penelope-mcp-serve` process on a free port and speak the MCP wire protocol over
HTTP: auth boundary, handshake, discovery, pagination, browsing, and the full
export round trip including downloading the signed URL without a header. They
**skip automatically** when the Penelope host is unreachable, so `pytest` stays
green off the NEU network.

Two things worth knowing if you extend them: they talk raw httpx rather than
using the SDK client, because the point is to exercise the deployed surface
(middleware, transport, export route) exactly as a teammate's client hits it; and
the fixture terminates the server **by process handle, never by name** — a
`pkill -f penelope-mcp-serve` would also kill your own server on another port.

## Next steps

- An eval set of ~20 real questions, run end to end and checked by hand. The
  failure mode of NL-over-SQL isn't crashes, it's confident wrong answers. The
  one to watch here: does the model actually read export URLs from its sandbox,
  or does it try to fetch them into context?
- TLS, if this ever leaves the LAN.
- A server-side `plot_series` returning a PNG, for clients with no sandbox.
- A `values[]` arity/meaning map for multi-element topics.
- Units for 25A topic names, if the team has them anywhere.