pg-schema-scout

Schema access for an agent is a retrieval problem, not a dump.

An MCP server that hands a model only the tables relevant to its question, and enforces read-only in three layers rather than annotating it.

---

The number

Measured against the seeded AdventureWorks database in this repo — 68 tables across five schemas — over the 26 questions in evals/questions.yaml:

	Tokens
Full schema DDL, dumped in one blob	10,927
Retrieved slice, mean over 26 questions (k=8)	1,816
	6.0× less schema context (83% fewer tokens)

Median slice 1,858; range 1,071–2,368. Tokenizer: tiktoken/cl100k_base. Backend: BM25.

Reproduce with python evals/measure_context.py. Every figure in this README comes from a real run against the seeded database; none is estimated. CI re-measures all of them on every push from a clean machine, and reproduces them exactly — so they are properties of the schema, not of the laptop they were first taken on.

That ratio is the boring half of the argument. The interesting half is what happens on a schema too wide to dump at all: a dump-everything server does not degrade gracefully, it truncates, and if the relevant table falls outside the cutoff it is gone and the model has no way to ask for it. This server's answer to that is describe_table, below.

---

Quickstart

docker compose up -d --wait

That builds Postgres, downloads the AdventureWorks OLTP sample, seeds 68 tables across five schemas, and creates both roles. First run takes a few minutes; afterwards it is instant.

pip install -e .

Then point your MCP client at it:

{
  "mcpServers": {
    "pg-schema-scout": {
      "command": "pg-schema-scout",
      "env": {
        "PGSS_DSN": "postgresql://scout_ro:scout_ro_pw@localhost:55432/adventureworks"
      }
    }
  }
}

stdio by default. pg-schema-scout --http serves Streamable HTTP on port 3000 instead.

The credentials above are for a local throwaway container holding public sample data. There are no real credentials in this repo.

---

The four tools

Four, not fourteen. Every tool description is paid for on every request, so mirroring a REST surface into tools is a tax on each call. These four match the loop an agent actually runs.

Tool	Signature	Annotations
search_schema	(question: str, limit: int = 8) -> SchemaSlice	readOnlyHint=true, idempotentHint=true, openWorldHint=false, destructiveHint=false
describe_table	(name: str) -> TableDetail	same
explain_query	(sql: str) -> QueryPlan	same
run_query	(sql: str, max_rows: int = 200) -> QueryResult	same

All four return Pydantic models, so outputSchema and structuredContent are generated rather than hand-written.

Annotation defaults in the spec are pessimistic — omitting them declares a tool destructive, open-world and non-idempotent — so all four are set explicitly. They are returned in a fixed order, because the SDK preserves registration order and the spec cites prompt-cache hit rate as the reason to keep it stable. tools/list carries ttlMs: 300000 and cacheScope: "public": the tool list is derived from this server's own code, so it is identical for every caller and stable for the life of the process, but a redeploy can change it, so it is not cached longer.

---

Safety model

Three layers, weakest first.

Layer	What it does	Why it is not enough alone
readOnlyHint=true	Tells the client the tool is safe to auto-approve	An untrusted hint. Decorative
AST policy (guard.py)	Parses with sqlglot and rejects anything that is not a single read-only SELECT	A parser bug is a bypass
scout_ro database role	Holds SELECT and nothing else, anywhere	This is the layer that actually holds

The annotation is the weakest layer, and deliberately so. The specification states that clients must treat tool annotations from an untrusted server as untrusted — the SDK's own ToolAnnotations docstring says clients should never make tool-use decisions based on annotations received from untrusted servers. A malicious server can set readOnlyHint=true and delete your data. It is documentation, not a control. Anyone who claims their database MCP server is read-only because of the annotation has misread the spec.

The query a regex guard misses

WITH gone AS (DELETE FROM customer RETURNING *) SELECT * FROM gone;

It begins with WITH. It contains SELECT. It empties the table. A regex allowlist that checks the statement's prefix passes it straight through.

sqlglot parses it to a top-level Select whose tree contains a Delete node, so walking the AST rejects it. The same argument runs the other way — a text denylist wrongly rejects

SELECT 'DROP TABLE customer' AS note FROM person.person;

which is an ordinary read-only query. Both directions are in the fault matrix.

The guard fails closed: unparseable input, empty input, statement stacking, and anything sqlglot can only represent as an opaque Command are all refused. It also blocks SELECT … FOR UPDATE (row locks are not read-only), SELECT … INTO (DDL wearing a SELECT costume), and a denylist of filesystem, network, administrative and denial-of-service functions checked against every function node in the tree rather than against the raw string.

Blocked is a tool error, not a protocol error

A rejected query returns HTTP 200 with a JSON-RPC result carrying isError: true and a message the model can act on. Only genuine protocol faults become JSON-RPC errors. Verified on the wire:

{"jsonrpc":"2.0","id":1,"result":{
  "content":[{"type":"text","text":"Error executing tool run_query: Blocked: the statement contains a DELETE nested inside the query (in a CTE or subquery), which a prefix check would miss. This server is read-only. Rewrite the request as a plain SELECT."}],
  "isError":true,"resultType":"complete"}}

Below the parser

The server authenticates as scout_ro, which is granted SELECT and nothing else, with TEMPORARY revoked on the database and no CREATE on any schema. Sessions run with default_transaction_read_only=on, statement_timeout and idle_in_transaction_session_timeout, and every query runs in an explicit read-only transaction with a row cap.

tests/test_readonly_role.py sends DELETE, UPDATE, INSERT, TRUNCATE, DROP, ALTER, CREATE TABLE, CREATE TEMP TABLE, CREATE INDEX and GRANT straight to Postgres, bypassing the guard entirely, and asserts the database refuses each one. That test is the proof the bottom layer holds independently of the parser.

---

Evals

Both figures below are from python evals/run_evals.py against the seeded database. It exits non-zero on any regression and runs in CI.

Fault matrix — 12/12 blocked, 9/9 allowed, 9/9 executed.

Two-sided on purpose. The must_allow half is the half people skip, and it is the half that proves the guard is a guard rather than a policy that refuses everything. Every must_allow query is executed against the database, not merely parsed, so "allowed" means it actually runs.

Retrieval — recall@8 0.955, precision@8 0.260, 24/26 questions with complete recall.

precision@8 is structurally capped: a question needs 1–4 tables but 8 slots come back, so the ceiling is around 0.5. Recall is the number that matters here — a missing table breaks the query, a spare one costs a few tokens.

Foreign-key propagation, and what it bought

The first eval run failed. Purely lexical ranking scored recall@8 0.840, with nine questions missing a required table, and the misses had a shape: production.product missed four times, person.person twice. These are join hubs. A slice containing salesorderdetail without product names a productid the model cannot resolve, so it is not a usable slice.

So ranking adds one damped round of score propagation across foreign keys: a table adjacent to a strong match is likely to be needed, precisely because you cannot join to what you have not been shown. Measured over the 26 questions:

neighbour weight	recall@8	complete recall
0.00 (off)	0.840	17/26
0.25	0.955	24/26
0.35 (shipped)	0.955	24/26
0.50	0.965	24/26

0.35 takes nearly all the gain; past 0.5 the curve flattens because the graph term starts swamping the lexical signal, which is how you end up returning the whole schema again.

Two honest caveats. The weight was chosen on the same 26 questions the recall is measured on, so 0.955 is optimistic — it is not a held-out score. And better recall costs tokens: the mean slice grew from 1,684 to 1,816, moving the headline ratio from 6.5× to 6.0×. Recall was judged worth more than the ratio.

BM25 vs Titan embeddings: the lexical backend wins

Both backends were measured against the same 26 questions. Titan Text Embeddings V2 ran against the real Bedrock service — 94 invocations, 68 table documents plus 26 queries.

Backend	FK propagation	recall@8	precision@8	complete recall
BM25 (default)	0.35	0.955	0.260	24/26
Titan V2	0.35	0.926	0.255	22/26
BM25	off	0.840	0.226	17/26
Titan V2	off	0.833	0.226	17/26

The embeddings lose. Before propagation the two are indistinguishable (0.840 vs 0.833), so the embeddings were not finding anything the lexical path missed; after propagation BM25 pulls ahead. This is a reasonable outcome rather than a surprising one: a table's document here is mostly identifiers and a one-line comment, not prose, which is the regime BM25 is built for. Foreign-key structure turned out to matter far more than semantic similarity.

Two caveats that cut against the winner. Titan was handed the same identifier-heavy document text as BM25, and a prose rendering of each table might suit it better. And the lexicon and stemmer were written with BM25 in mind, so the comparison is not perfectly even-handed.

BM25 stays the default — which also means the repo runs offline, with no credentials and no per-query cost. That was already the plan; it is now the plan and the better-measuring option. Reproduce with PGSS_RETRIEVAL_BACKEND=titan.

---

Known limits

• Retrieval has a recall ceiling, and this is the design's real failure mode. At k=8 it misses a required table on 2 of 26 questions: revenue-by-category (needs a four-table join through the category hierarchy) and reseller-locations (the store→address path runs through a cross-reference table that matches nothing in the question's wording). When it misses, the table is simply absent and the slice alone cannot recover.
• describe_table is the escape hatch for exactly that. It does not use the ranking, so a model that knows or guesses a table name can always reach it. Every SchemaSlice carries a note telling the model to do this, and the failure is named in the search_schema docstring the model reads.
• The lexical backend cannot do irregular morphology. Stemming reduces employees to employee, but paid does not reduce to pay. There is a test asserting the limit rather than hiding it. Embeddings were the obvious fix and were measured; they did not help overall (see above), so this ceiling stands.
• The identifier lexicon is schema-specific. AdventureWorks names are lowercase concatenations (salesorderheader), and splitting them needs a word list. The one in retrieval.py covers this schema's vocabulary; another database would want its own.
• The Titan/Bedrock backend is measured but not the default, because it measured worse. It has been run against the real service, so the numbers above are real; it is not a code path to trust blindly at scale, since it has only ever been exercised over 68 documents and 26 queries with no retry, batching or rate-limit handling.
• Not implemented, deliberately: no prompts, no resources, no write path of any kind, no EXPLAIN ANALYZE (it would execute the query), no cross-database or multi-tenant support, no auth — the server is expected to run locally over stdio, and the security boundary is the database role, not the transport.

Conformance: does not pass, and here is why

npx @modelcontextprotocol/conformance@0.1.16 server --url http://localhost:3000/mcp reports 11 passed, 21 failed. That is not a green check, and it is not a defect in this server:

1. The tool has no knowledge of protocol revision 2026-07-28. --spec-version accepts only 2025-03-26, 2025-06-18, 2025-11-25, draft and extension. It therefore exercised the legacy handshake path that the SDK still supports for older clients, which is why server-initialize and ping appear in the passing column despite both being removed in the revision this server targets.
2. Its scenarios assert against a reference fixture server. The failures are things like Unknown resource: test://static-text, Unknown prompt: test_simple_prompt and No image content found — a server with four tools and no prompts or resources cannot satisfy them. logging-set-level fails with Method not found, which is correct behaviour for this revision.

Every scenario that tests protocol behaviour rather than fixture presence passes: tools-list, tools-call-simple-text, tools-call-error, resources-list, prompts-list, ping, server-initialize, server-sse-multiple-streams, dns-rebinding-protection.

It is not wired into CI, because doing so would mean checking in a baseline of 21 expected failures, and a baseline that large hides regressions rather than catching them.

---

Spec version

Built against MCP protocol revision 2026-07-28, on the official Python SDK mcp==2.0.0 (pinned exactly; the v1 line continues in parallel and the APIs are not compatible).

That revision made the protocol stateless: the initialize handshake, notifications/initialized, sessions and Mcp-Session-Id are gone, replaced by server/discover plus a per-request _meta envelope carrying the protocol version and the client's capabilities. There is no session handling anywhere in this codebase for that reason. Every result now carries a resultType, cacheable methods carry ttlMs/cacheScope, HTTP+SSE is deprecated in favour of stdio and Streamable HTTP, and sampling, roots and logging are deprecated — none of which this server builds on.

The v2 SDK also removed FastMCP: from mcp.server.fastmcp import FastMCP raises ModuleNotFoundError, and the entry point is MCPServer from mcp.server. (The fastmcp package on PyPI is a separate, independent project.)

Two notes for anyone reading the code against the docs:

• mcp.types is now a mirror of a standalone mcp-types package. Both spellings work.
• ToolAnnotations fields are snake_case in Python (read_only_hint) and camelCase on the wire (readOnlyHint). Both are accepted at runtime, but only snake_case type-checks under mypy, so the source uses snake_case and this README quotes the wire form.

---

Design decisions not dictated by the brief

• The five shorthand view schemas are excluded from the index. The upstream loader also creates pe/hr/pr/pu/sa, 68 convenience views over the same 68 tables. Indexing them would double every table and inflate the full-schema baseline this README compares against. Excluding them makes the headline ratio harder to hit, not easier.
• Foreign-key edges leaving the slice are dropped from SchemaSlice.foreign_keys. The model cannot join to a table whose columns it has not been shown, so listing those edges spends tokens on an unusable hint.
• DDL is rendered, not pg_dumped. No storage parameters, tablespaces or ownership: they cost tokens and answer no question a query author has.
• Decimal is returned as a string, not a float. These are money and quantity columns, and a silent rounding is worse than a visible type change.
• Sample data is downloaded at image build time, not vendored, so no 40 MB blob lives in git. Both sources are pinned — the loader scripts by commit SHA, the data to Microsoft's immutable release asset.
• server.py uses absolute imports while the rest of the package uses relative ones. mcp dev loads the file by path rather than as a package member, and relative imports fail under that loader.

---

Development

pip install -e ".[dev,bedrock]"
docker compose up -d --wait

pytest                      # 100 tests
pytest -m "not db"          # skip anything needing Postgres
python evals/run_evals.py   # fault matrix + retrieval, non-zero exit on regression
python evals/measure_context.py
ruff check . && mypy
mcp dev src/pg_schema_scout/server.py   # Inspector

CI runs ruff, ruff format, mypy, pytest and the evals, with the seeded database as a compose service.

License

MIT. The AdventureWorks sample data is Microsoft's, downloaded at build time and not redistributed here.