Schemagate MCP Server

schemagate — fewer tokens for text-to-SQL, and only the tables the caller may read

Most text-to-SQL prompts carry the whole schema, and on a big database that is most of the prompt, on every question. schemagate sends the model only the tables the question needs: 91.1% fewer schema tokens (median) on databases with 50+ tables (benchmark). It filters by the caller's grants first, so restricted tables are absent from the prompt rather than ranked low. Works with LangChain, LlamaIndex, MCP, or any SQL agent, on Postgres, Oracle, MySQL, SQL Server and SQLite.

Measured, not drawn: python docs/media/make_token_gif.py rebuilds it from the seeded 1,201-object fixture, counting with tiktoken's cl100k_base.

#1 on BIRD's Mini-Dev leaderboard in all three dialects: SQLite 81.0, PostgreSQL 72.6, MySQL 72.4 execution accuracy, with Claude Opus 5.5 and one model call per question (leaderboard, run files). Smaller prompts, no accuracy given up.

If it cut your prompt, a ⭐ helps other people building SQL agents find it.

With row-level security alone the failure is quiet: the model writes valid SQL against a table the caller cannot read, RLS strips every row, and the user is told "no records found" — indistinguishable from "this data does not exist."

Demo · Install · Benchmarks · Local models · What it costs · Coming from Vanna

Same question, two callers, no database and no key:

schemagate demo "salary by employee"                                     # hr_compensation absent
schemagate demo "salary by employee" --principal okta:hr --role payroll  # now it is first

Absent, not ranked low. A table the caller may not read never enters the prompt, so no rewording of the question reaches it and there is nothing to filter out of the answer afterwards.

Try it in the browser — no install, no database, no model call.

And it answers

The selection is a prompt, so the rest follows:

pip install schemagate
schemagate demo "which customers owe us money" --answer --provider <provider> --model <model-id>

main.crm_customer   main.crm_contact   main.v_customer_balance   (+5)
8 of 42 objects  ·  ~383 prompt tokens instead of ~2,036

-- SQL written by <provider> / <model-id>, from 8 tables
SELECT c.id, p.display_name, v.account_number, v.invoiced, v.paid,
       (v.invoiced - v.paid) AS balance_due
FROM v_customer_balance v
JOIN crm_customer c ON c.id = v.id_customer
JOIN core_party  p ON p.id = c.id_party
WHERE v.invoiced > v.paid

id  display_name       account_number  invoiced  paid     balance_due
--  -----------------  --------------  --------  -------  -----------
1   Northwind Trading  ACC-1001        33960.0   22080.0  11880.0
2   Kellner GmbH       ACC-1002        8760.0    3000.0   5760.0

Rows, from a question, with no database to set up — that runs against a bundled 42-object schema. <provider> is the model service you choose: openai, anthropic, gemini, oci, local (no key at all), or auto (whichever key is set). Point it at your own database with --url:

schemagate select "which customers owe us money" \
  --url "postgresql+psycopg://user:pw@host/db" --answer --provider <provider> --model <model-id>

No key? Drop --provider and it prints a prompt to paste into any chat, then run the SQL it gives you back with --sql "SELECT ...".

More of the bundled schema, with the questions people actually type:

schemagate demo "which customers owe us money"
schemagate demo "late shipments by carrier" --prompt   # the DDL the model gets

Against your own database it's the same shape:

schemagate select "revenue by month" --url postgresql://localhost/app --principal okta:jdoe --role finance
schemagate studio --url postgresql://localhost/app        # the same thing, as a page

schemagate studio opens a local page where you type questions, switch the caller's roles, edit hints, and watch what reaches the prompt and what doesn't. The same page runs publicly at https://ashishsinha1602.github.io/schemagate/ on the six bundled schemas, in your browser, with no server behind it. The selector on that page is a JavaScript port of this library, and a test runs both against 1,789 cases and requires identical rankings.

If you're coming from Vanna (archived March 2026), docs/migrating-from-vanna.md is the short version: Vanna applied identity when the SQL ran; schemagate applies it before the model sees the schema. Your User maps to a Principal in one line.

What it saves

Every text-to-SQL call pays for the schema in the prompt. Dump the whole thing and you pay for every table on every question; hand the model six tables and you pay for six. Measured on the test schemas, average over their golden questions, same built-in estimator as tests/bench.py:

The last row is the one that matters: the selection stays around six tables no matter how big the schema is, so the saving grows with the schema. Real databases are the last row, not the first.

The same count on public schemas nobody here wrote (no model called; BENCHMARKS.md has the script). On Spider 2.0's real BigQuery and Snowflake warehouses, the median database with 50 or more tables went from its full schema to a selection 91.1% smaller (24 databases). On BIRD, whose databases have a median of seven tables, the median saving was 8.8%: there was little to cut. The price of cutting is that a needed table can be left out. On the warehouses, every table the gold query reads was in the selection for 86.7% of questions. Against the retrievers other tools use, at the same number of tables and no model calls, schemagate selected every needed table for 85.3% of warehouse questions where BM25 managed 78.7% and embedding search 78.2%, and 98.7% of BIRD questions against 81.0% and 89.4% (head-to-head). Worked example, with a price you should replace with your own: a 260-object schema, 5,000 questions a day, an input price of $3 per million tokens. Full schema: 16,095 × 5,000 × 30 = 2.4 billion tokens a month, about $7,200. With schemagate: 444 × 5,000 × 30 = 67 million, about $200. The browser demo has these two numbers as editable fields under the stats, so you can put in your own volume and price and watch it recompute against whatever question you ask.

Two more things that cost nothing here and money elsewhere: the selector itself never calls a model (BM25 plus a hashed embedder, offline, milliseconds), and the optional descriptions can be written by any chat window you already pay for instead of an API key — see Without an API key.

The problem this solves

Two things go wrong when you point an LLM at a database schema.

The first is cost. Most systems paste the whole schema into the prompt on every question. That's fine for twenty tables and ruinous for two thousand.

The second is worse, and it's the reason I wrote this. Schema selection happens before the query runs, so it happens before row-level security can do anything. If your selection step isn't identity-aware, the model gets handed a table the caller can't read. It writes perfectly good SQL. RLS or VPD filters every row out. The user sees "no records found" and believes it.

That's not an access-denied message. It's a wrong answer with a confident tone, and the user has no way to tell the difference. Filtering the catalog by identity first is the only way I know to avoid it.

from schemagate import Catalog, Principal

cat = Catalog().bootstrap("postgresql://localhost/app")   # or: from schemagate.demo_schema import demo_catalog; cat = demo_catalog()
cat.hint("invoice_draft", "pre-issue drafts only, not real revenue")
cat.restrict("hr_compensation", ["payroll"])

sel = cat.select("revenue by month", top_k=6,
                 principal=Principal("okta:jdoe", roles={"finance"}))

sel.prompt_fragment()   # compact DDL, ready for the system prompt
sel.object_list         # [{'owner': ..., 'name': ...}]
sel.explain()           # why each object was picked

hr_compensation is not in that result and its name does not appear anywhere in the prompt text.

Install

pip install schemagate

That's the whole thing. One dependency (SQLAlchemy), no API key, no model download. The default embedder is a hashed n-gram vectoriser that runs offline and gives byte-identical results on every machine.

Extras, all optional:

pip install 'schemagate[postgres]'     'schemagate[oracle]'
pip install 'schemagate[mssql]'        'schemagate[mysql]'
pip install 'schemagate[anthropic]'    'schemagate[openai]'      'schemagate[gemini]'
pip install 'schemagate[huggingface]'

huggingface is the no-key, nothing-leaves-the-machine path, and it is the one extra that is heavy: about 2 GB of wheels plus a 3.1 GB model download the first time you use it. It is deliberately kept out of schemagate[all]. docs/local-models.md has the whole story — the downloads, the load you wait through once, what it is good at and where it is worse than a hosted model.

Every release is signed. The wheels carry PEP 740 attestations — a signature from GitHub naming the workflow, repository and commit that built that exact file. Nothing is uploaded by hand and there is no API token to steal. Check one yourself with gh attestation verify <wheel> --repo ashishsinha1602/schemagate.

Quick start

pip install schemagate            # add an extra for your driver, below
schemagate                        # opens http://127.0.0.1:8770 on the 42-object demo

Every wheel on PyPI carries a signed provenance attestation naming the commit that built it: gh attestation verify <wheel> --repo ashishsinha1602/schemagate.

Or without installing anything, with every driver already in the image:

docker run -p 8770:8770 -e SCHEMAGATE_DATABASE_URL=postgresql://…   ghcr.io/ashishsinha1602/schemagate

Leave the URL off and it opens on a 42-object sample schema with data in it, so there is something to ask questions of before you point it at your own.

Then, in the page:

1. Connect. Paste a URL — postgres://…, postgresql://…, mysql://…, oracle://… and a JDBC string all work, as does the wallet form for an Autonomous Database. Tick Save this connection and give it a name and the next start reconnects on its own.
2. Catalogue. Settings → Model → pick a provider, paste a key, Save model (it is saved, so a restart does not ask again). Then Catalogue this database in the rail. One sentence per object, cached to disk, so a second run costs nothing.
3. Ask. Type a question in your own words. You get the objects that answer it, the DDL a model would receive, the SQL, and the rows.

Drivers come as extras — schemagate[postgres], [oracle], [mysql], [mssql], or schemagate[all] for the lot:

pip install 'schemagate[postgres]'

When a question picks the wrong table

Two levers, both per database and both applied on every reconnect:

• Hints (rail → Hints): one object, in your words. "MyConvo campaigns: personal-inbox sends from a user's own mailbox."
• Glossary (POST /api/glossary): one word, everywhere. A term here is fed to the cataloguing prompt, so every description uses your vocabulary, and expanded into questions that mention it.

A hint beats a generated description everywhere, and neither needs re-cataloguing.

Commands

Every subcommand, and what it is for. schemagate <command> --help prints the same thing.

schemagate demo      [question]          run against the bundled 42-object schema
schemagate select    --url URL [question]  select against your own database
schemagate studio    [--url URL]         the Studio page, served locally
schemagate describe  --url URL           write AI descriptions for your objects
schemagate certify   URL                 end-to-end check on a real engine

demo and select

select is demo pointed at a real database; they take the same flags.

schemagate demo "who reports to whom"
schemagate select --url postgresql+psycopg://user:pw@host/db "unpaid invoices"
schemagate select --schema APP --url "oracle+oracledb://ADMIN:pw@/?dsn=(description=(address=(protocol=tcps)(port=1522)(host=adb.us-phoenix-1.oraclecloud.com))(connect_data=(service_name=abc123_mydb_low.adb.oraclecloud.com)))" "unpaid invoices"   # Autonomous Database

studio

schemagate                              # the demo, or your remembered connection
schemagate studio                       # empty, connect from the page
schemagate studio --demo                # the bundled sample schema
schemagate studio --url postgresql://localhost/app
schemagate studio --remember            # save the connection, reconnect next time
schemagate studio --forget              # delete the saved connection and exit

In the page: Catalogue this database describes what has no description yet, Re-catalogue all rewrites every one, and Resync schema re-reflects the database while keeping the descriptions you already have.

describe

# with a key
schemagate describe --url postgresql://localhost/app --provider anthropic --model claude-sonnet-5

# without one: write the prompt out, paste it into any chat, apply the reply
schemagate describe --url postgresql://localhost/app --out prompt.txt
schemagate describe --url postgresql://localhost/app --apply reply.json

certify

schemagate certify "postgresql+psycopg://user:pw@host/db"

Reflects, selects, and reports what a real engine actually did — the check to run before trusting a new database or driver.

Environment

How it picks

1. Reflect the schema through SQLAlchemy. No vendor SQL anywhere.
2. Index names, columns, comments, hints, and view definitions. That last one matters more than it sounds: a view exposes only its output columns, so v_stock_shortfall looks like it's about "shortfall" when the thing you'd search for, reorder_point, is buried in its SELECT.
3. Retrieve with reciprocal-rank fusion over BM25 and vector similarity. Neither alone is good enough. Vectors miss exact identifiers; BM25 misses "owe us money" → balance.
4. Walk foreign keys to pull in join tables the question never mentions. In my experience this is the single biggest cause of generated SQL that parses but won't run.
5. Apply the caller's identity at every step above.

Numbers

On access control, scored by someone else's benchmark. The RBAC Text-to-SQL benchmark (SIGMOD'27) gives each question a role and a column-level policy and scores answering versus refusing with its authors' own evaluator. With claude-sonnet-4-5, five trials:

The model never sees the restricted part of the schema. Caveats and every column are in BENCHMARKS.md.

And on a second one. Spider-ACL (EACL 2026) labels each of 19,624 question-and-role pairs with what PostgreSQL itself allowed. With claude-sonnet-5-5, all 19,624:

The paper's systems are 2024-25 models; details and limits in BENCHMARKS.md, and both results in one technical report (PDF), DOI 10.5281/zenodo.23446210.

On execution accuracy, scored by the benchmarks' own evaluators. One model call per question, about 2,100 prompt tokens, no candidates, no voting, no agent loop:

Opus varies about ±1 run to run. The Mini-Dev rows are on BIRD's own leaderboard (PR #251, merged 2026-10-25). Files and commands: benchmarks/results/; the context is in BENCHMARKS.md.

On public benchmarks, so you can check them without trusting me: BENCHMARKS.md has schemagate on Spider and BIRD, with the scripts in benchmarks/ and the data downloaded from the original sources. The headline is the pooled setting — every Spider database merged into one 876-table catalog, no hint about which one to look in:

BIRD dev, 1,534 questions: 96.7% per-database at top_k=5, 91.3% pooled at top_k=10; pooled at top_k=5, 83.8% hashed, 88.6% with [huggingface], 96.7% with --rerank. Re-measured on 1.5.2; reranked with claude-haiku-5-5, one call per question.

And Spider 2.0-lite, the benchmark built for real warehouses — 162 databases, 8,255 tables, a median of 16 per database and a maximum of 785: 79.8% at top_k=10 over all 247 usable questions, none excluded (84.5% on the 233 whose gold tables resolve), no pooling needed because the databases are already big. An earlier version of this page said 82.9%; that number was measured while Windows had silently made thousands of the schema files unreadable, and a second re-run was owed after the loader was found to be reading the benchmark's per-column descriptions as one table description. Both re-runs are done and BENCHMARKS.md keeps the corrections rather than deleting them.

End to end, on the metric those boards actually score: schemagate plus Claude Opus 5.5 gets 77.12% execution accuracy on all 1,534 BIRD dev questions, one model call per question (table above). The BIRD Mini-Dev rows are on BIRD's own leaderboard. A main-board placing needs the held-out test set, which only the BIRD team runs: 72.11 execution accuracy for this setup, and 74.34 for the second configuration, which adds the four closest BIRD training questions to each prompt.

Everything below is measured on schemas I invented, which is worth less and is why the public numbers come first.

Six test schemas ship with the library. Run python tests/bench.py and you get all of this printed back. TESTING.md is the full record of what was tested, what broke, and what was found to be the database rather than schemagate.

Every number below is printed by that run, and the run fails if any of them stops matching — bench.py reads this table back and compares.

recall@6 here is the share of gold tables retrieved in the top six, micro-averaged over questions. The literal column asks questions that reuse the schema's own vocabulary; the business words column asks for the same things the way a person does, with no vocabulary overlap. Both matter and they disagree, which is the point of showing both.

Measured with the hashed embedder — what pip install schemagate gives you, no extras. schemagate[huggingface] swaps in sentence-transformers and the business-word numbers move a long way: on the held-out paraphrase set tests/run_paraphrase_eval.py reports 58.6% overall hashed and 82.8% with MiniLM. That harness counts a question as hit if any gold table is retrieved, which is a looser predicate than this table's, so its figures are not comparable with these — it prints which embedder it used for the same reason.

Token counts come from an estimator built into the benchmark so the number is reproducible with no network and no extra install. pip install tiktoken and the same script switches to exact cl100k_base counts. The ratio holds either way.

Six schemas rather than one because a single schema whose questions happen to share vocabulary with its own table names will flatter any retriever. The second is a different domain entirely. The third is 260 objects of deliberate sabotage: an _archive and _stg copy of every table, the same table name in three schemas, an 8-deep foreign-key chain, a reference cycle, composite keys, a 320-column table, 100-character identifiers, and names in Spanish and Japanese. The fourth is a claims warehouse star schema built so that several tables are plausible for every question and one is right: the same fact at four grains, a slowly-changing member dimension with a history table, one date dimension joined five different ways, bridge tables, and fifteen _bkp, _old, _v2, _tmp and stg_ copies of the important ones. The fifth is a bank: a ledger at three grains, trades versus positions versus settlements, FX both as a daily table and an as-of view, lending, and the KYC and AML tables most callers must never see. The sixth is an IoT fleet: readings at raw, one-minute and hourly grains, six monthly partition tables, an alarm lifecycle spread across three tables. All six are invented. No real schema from anywhere is in this repo.

That 50% row is the honest one. Read it before you adopt this.

The 50% row, and what to do about it

The default embedder matches subwords, not meaning. Ask it for "things we're running out of" and it will not find v_stock_shortfall, because those two strings have nothing in common. Ask it about stock_shortfall and it's excellent.

If your users type identifier-shaped questions, you're done, and you never need an API key. If they type like people, give the catalog descriptions. There are two ways, and neither is required.

With an API key in the environment, you get them without asking. Every path that answers a question — schemagate select, --answer, the MCP server, the Studio's connect — describes the catalogue first, caches the result per connection under ~/.schemagate/descriptions/, and re-describes an object only when its structure changes. A hint you wrote, or a database comment that says something, is never overwritten; a comment that only restates the object's name in the schema's own boilerplate is replaced, because it was diluting every word it contained. Measured on a 1,200-object schema, that is the difference between six and eight of eight complex questions producing SQL that runs. SCHEMAGATE_AUTO_DESCRIBE=0 turns it off; with no key present nothing is called and nothing changes.

Without an API key

Any chat window you already have — ChatGPT, Gemini, Copilot, a local model — can write the descriptions. schemagate gives you the prompt and takes the reply:

schemagate describe --url postgresql://localhost/app --out prompt.txt
# paste prompt.txt into a chat; save its JSON reply as reply.json
schemagate describe --url postgresql://localhost/app --apply reply.json --config catalog.json
schemagate select   --url postgresql://localhost/app "things we're running out of" --config catalog.json

The prompt is metadata only — names, types, comments, foreign keys, never rows — and one paste covers every undescribed object. The reply lands in the describe block of catalog.json, next to your restrict and hint blocks, and select, studio and the MCP server (SCHEMAGATE_CATALOG_CONFIG) all read it. From Python it's the same idea: cat.describe_prompt() and cat.describe({"v_stock_shortfall": "Items below their reorder level."}).

With a local model, and no key at all

pip install 'schemagate[huggingface]'
schemagate describe --url postgresql://localhost/app                     --provider local --model Qwen/Qwen2.5-1.5B-Instruct                     --cache .schemagate-cache.json

or, in the Studio, Settings → Model → Local (transformers). No key field appears, because there is no key.

from schemagate.ai import SchemaDescriber, LocalProvider
cat.describe(SchemaDescriber(LocalProvider(), cache_path=".schemagate-cache.json"))

Writing one sentence per table is a small enough job that a 1.5B model does it acceptably. Writing multi-table SQL is not, and the Studio uses the same provider for both — so if you have a key, catalogue locally but answer with the key. Read docs/local-models.md before you turn it on: it covers the two downloads, the load you wait through once, the ~3 GB of RAM, the caching that makes the second run free, and why a weak model's bad description can no longer bury the object it describes.

With your own key

from schemagate.ai import SchemaDescriber, AnthropicProvider

cat.describe(SchemaDescriber(AnthropicProvider(model="claude-sonnet-4-5"),
                             cache_path=".schemagate-cache.json"))

One sentence per table, written by the model, indexed like any other schema text. On the bundled schema that takes the business-words row from 50% to 100% with no change to the identifier-style questions.

Anthropic, OpenAI and Gemini are supported. Anything else goes through CallableProvider, which is also your escape hatch when a vendor changes their SDK and you don't want to wait for a release from me.

from schemagate.ai import (AnthropicProvider, OpenAIProvider, GeminiProvider,
                      CallableProvider, auto_provider, available_providers)

AnthropicProvider(model="claude-sonnet-4-5")                    # ANTHROPIC_API_KEY
OpenAIProvider(model="gpt-4.1-mini")                            # OPENAI_API_KEY
GeminiProvider(model="gemini-2.5-flash")                        # GEMINI_API_KEY
OpenAIProvider(model="…", base_url="http://localhost:11434/v1") # anything local
CallableProvider(lambda system, prompt: my_llm(system, prompt))

available_providers()      # ['AnthropicProvider'] — names, never key values
auto_provider(model="…")   # picks whichever key is set

model is required. I'm not shipping a default model ID, because model IDs change every few months and a hardcoded one eventually 404s for everybody who installed the version before the fix.

Three things worth knowing before you turn this on:

What leaves your network. Table names, column names, types, nullability, existing comments, foreign keys. Not one row of data — ObjectDoc has no field that could hold one, and there are tests asserting both halves of that. Nothing is sent unless you call describe().

What it costs. One short call per undescribed object, once. Objects that already have a database comment or a hint are skipped by default. Results cache by content, so re-running is free and only changed tables get re-described. Ask before you pay:

describer.estimate_calls(docs)   # calls describe() would actually bill for
describer.preview(doc)           # the exact text that would be sent

What happens when it fails. The object is skipped, cataloging continues, and describer.failures lists what was missed. Pass strict=True if you'd rather it raise. A hint() you wrote by hand always beats a generated description, so fixing a bad one costs nothing.

The embedder picks itself

pip install schemagate uses the hashed n-gram vectoriser: offline, instant, byte-identical on every machine. Install schemagate[huggingface] and a sentence model is used automatically instead -- no flag, no benchmark, no decision for you. Measured across the six bundled schemas, 98 questions, no descriptions:

The gain is concentrated exactly where the hashed embedder is documented to be weak -- questions phrased the way people speak. On the commerce schema, which carries that set, it goes 15/18 to 18/18.

It is not the base default because that would trade one dependency for torch, and the guarantee that the same text gives the same vector everywhere. SCHEMAGATE_AUTO_EMBEDDER=0 keeps the hashed one if you need to reproduce an older index.

You can swap in a hosted embedder too, though on identifier-heavy schema text the offline ones are often just as good and cost nothing per query.

from schemagate.ai import APIEmbedder, OpenAIProvider

provider = OpenAIProvider(model="gpt-4.1-mini",
                          embed_model="text-embedding-3-small")
cat = Catalog(embedder=APIEmbedder(provider, dim=1536))

Your organisation's words: a glossary

Some questions share nothing with any identifier, and no description or model reliably bridges them: "money we gave back to shoppers" is billing_credit_note. State it once, in the same catalog.json as your access rules:

{"terms": {"gave back": "billing_credit_note",
           "revenue": ["billing_invoice", "v_monthly_revenue"]}}

or cat.term("gave back", "billing_credit_note") in Python. A term matches as a phrase, plurals folded, and only the most specific term counts ("take things offline" beats "offline"). It is applied when the question is asked, so nothing is re-indexed, and it never widens access: a term for a table the caller may not read does nothing for that caller. A term naming an object that does not exist is an error, not a silent no-op.

What it is for, measured on the 58 business-language questions across the six bundled schemas (tests/run_terms_repair.py): every question that missed was fixed by adding one short term taken from it, and no other question broke -- 23 of 23 on the base embedder, 11 of 11 with MiniLM. What it is not: a glossary written in advance, from table names alone, barely moved the held-out questions (18/26 to 19/26 on the base embedder, 21/26 unchanged with MiniLM; tests/run_terms_eval.py), because people's words are hard to guess. Add terms for the questions you see missing.

The full ontology: concepts, rules, a hierarchy

A glossary term is the simplest concept. The full form says what a word means, precisely, so the model writes the right SQL rather than a plausible one:

{"ontology": {"concepts": {
  "revenue":  {"synonyms": ["turnover", "sales"],
               "maps": ["billing_invoice.total_net"],
               "filter": "billing_invoice.status = 'issued'",
               "definition": "Issued invoices only; drafts and void excluded.",
               "broader": ["money in"]},
  "money in": {"maps": ["v_monthly_revenue"]}}}}

A question that uses a concept's name or a synonym brings its objects in, and its meaning is written above the DDL:

-- What the business terms in this question mean:
-- revenue (also: turnover, sales): main.billing_invoice.total_net; only where billing_invoice.status = 'issued'. ...

Documentation truncated — see the full README on GitHub.