Skip to content
Back to search

schemagate

Text-to-SQL access control: schema selection that gives the LLM only the tables the caller is allowed to read. Row-level-security-aware table pruning for LangChain, MCP and any SQL agent. Postgres, Oracle, MySQL, SQL Server. 75% fewer prompt tokens.

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today

Check it deeper on your machine

It has changed since its scan on 11 Nov 2026.

As a Claude Code plugin, install it and then ask Claude Code:

/plugin marketplace add keithmackay/mackayi/plugin install scanskillsafety@mackayiDo a deep scan of https://github.com/ashishsinha1602/schemagate with scanskillsafety

Or from a terminal, clone it and run it:

git clone https://github.com/keithmackay/scanskillsafety-skill ~/.claude/skills/scanskillsafetynode ~/.claude/skills/scanskillsafety/cli.cjs --deep 'https://github.com/ashishsinha1602/schemagate'

It checks the exact commit: every file, its packages against OSV (api.osv.dev), how it compares with this page's scan (findsafeskills.com receives the repository's address), and, if you agree, Claude reading every file with your own Claude subscription. It never runs the code.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/ashishsinha1602--schemagate/badge.svg)](/listings/ashishsinha1602--schemagate)

HTML

<a href="/listings/ashishsinha1602--schemagate"><img src="/api/listings/ashishsinha1602--schemagate/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

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](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](https://peps.python.org/pep-0740/) 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`.

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

.claude-plugin/marketplace.json · Manifest · 20 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. {
  2. "$schema": "https://anthropic.com/claude-code/marketplace.schema.json",
  3. "name": "schemagate",
  4. "owner": {
  5. "name": "Ashish Sinha",
  6. "url": "https://github.com/ashishsinha1602"
  7. },
  8. "metadata": {
  9. "description": "schemagate for Claude Code: text-to-SQL with only the tables a question needs"
  10. },
  11. "plugins": [
  12. {
  13. "name": "schemagate",
  14. "description": "Text-to-SQL with ~91% fewer schema tokens: Claude sees only the tables a question needs, and only the ones the caller may read. #1 on BIRD Mini-Dev.",
  15. "source": "./plugins/schemagate",
  16. "category": "database",
  17. "homepage": "https://github.com/ashishsinha1602/schemagate"
  18. }
  19. ]
  20. }
README.md · README · 1,500 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. # schemagate — fewer tokens for text-to-SQL, and only the tables the caller may read
  2. [![PyPI](https://img.shields.io/pypi/v/schemagate.svg)](https://pypi.org/project/schemagate/)
  3. [![Python](https://img.shields.io/pypi/pyversions/schemagate.svg)](https://pypi.org/project/schemagate/)
  4. [![CI](https://github.com/ashishsinha1602/schemagate/actions/workflows/ci.yml/badge.svg)](https://github.com/ashishsinha1602/schemagate/actions/workflows/ci.yml)
  5. [![License](https://img.shields.io/badge/license-Apache--2.0-blue.svg)](LICENSE)
  6. [![Patent pending: US 64/183,987](https://img.shields.io/badge/patent-pending%20(US%2064%2F183%2C987)-555555)](#status)
  7. [![BIRD Mini-Dev: #1 on SQLite, PostgreSQL and MySQL](https://img.shields.io/badge/BIRD%20Mini--Dev-%231%20on%20SQLite%20%C2%B7%20PostgreSQL%20%C2%B7%20MySQL-0F7B6C)](https://bird-bench.github.io/)
  8. [![UniQL Oracle: 63.7% first try](https://img.shields.io/badge/UniQL%20Oracle-63.7%25%20first%20try-C74634)](benchmarks/results/uniql_oracle/README.md)
  9. [![OpenSSF Best Practices](https://www.bestpractices.dev/projects/15353/badge)](https://www.bestpractices.dev/projects/15353)
  10. [![OpenSSF Scorecard](https://api.securityscorecards.dev/projects/github.com/ashishsinha1602/schemagate/badge)](https://scorecard.dev/viewer/?uri=github.com/ashishsinha1602/schemagate)
  11. [![REUSE status](https://api.reuse.software/badge/github.com/ashishsinha1602/schemagate)](https://api.reuse.software/info/github.com/ashishsinha1602/schemagate)
  12. [![PyPI Downloads](https://static.pepy.tech/personalized-badge/schemagate?period=total&units=INTERNATIONAL_SYSTEM&left_color=BLACK&right_color=GREEN&left_text=downloads)](https://pepy.tech/projects/schemagate)
  13. [![MCP Registry](https://img.shields.io/badge/MCP%20Registry-listed-16a34a.svg)](https://registry.modelcontextprotocol.io/v0/servers?search=io.github.ashishsinha1602/schemagate)
  14. [![Try it in the browser](https://img.shields.io/badge/demo-in%20your%20browser-0F7B6C)](https://ashishsinha1602.github.io/schemagate/)
  15. 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](BENCHMARKS.md)). 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.
  16. ![One question on a 1,201-object schema: the whole schema is 146,695 prompt tokens; schemagate sends 12 tables, including all 3 the query needs, in 1,348.](https://raw.githubusercontent.com/ashishsinha1602/schemagate/main/docs/media/token-savings.gif)
  17. <sub>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.</sub>
  18. **#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](https://bird-bench.github.io/), [run files](benchmarks/results/bird_minidev/README.md)). Smaller prompts, no accuracy given up.
  19. If it cut your prompt, a ⭐ helps other people building SQL agents find it.
  20. ![An analyst asks "salary by employee": hr_compensation is absent from the shortlist. Grant the payroll role and it becomes the top match.](https://raw.githubusercontent.com/ashishsinha1602/schemagate/main/docs/media/schemagate-demo.gif)
  21. 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."
  22. [Demo](https://ashishsinha1602.github.io/schemagate/) · [Install](https://ashishsinha1602.github.io/schemagate/install/) · [Benchmarks](https://ashishsinha1602.github.io/schemagate/benchmarks/) · [Local models](https://ashishsinha1602.github.io/schemagate/local-models/) · [What it costs](https://ashishsinha1602.github.io/schemagate/cost/) · [Coming from Vanna](https://ashishsinha1602.github.io/schemagate/vanna-alternative/)
  23. Same question, two callers, no database and no key:
  24. ```bash
  25. schemagate demo "salary by employee" # hr_compensation absent
  26. schemagate demo "salary by employee" --principal okta:hr --role payroll # now it is first
  27. ```
  28. Absent, not ranked low. A table the caller may not read never enters the
  29. prompt, so no rewording of the question reaches it and there is nothing to
  30. filter out of the answer afterwards.
  31. *[Try it in the browser](https://ashishsinha1602.github.io/schemagate/) — no
  32. install, no database, no model call.*
  33. ## And it answers
  34. The selection is a prompt, so the rest follows:
  35. ```bash
  36. pip install schemagate
  37. schemagate demo "which customers owe us money" --answer --provider <provider> --model <model-id>
  38. ```
  39. ```
  40. main.crm_customer main.crm_contact main.v_customer_balance (+5)
  41. 8 of 42 objects · ~383 prompt tokens instead of ~2,036
  42. -- SQL written by <provider> / <model-id>, from 8 tables
  43. SELECT c.id, p.display_name, v.account_number, v.invoiced, v.paid,
  44. (v.invoiced - v.paid) AS balance_due
  45. FROM v_customer_balance v
  46. JOIN crm_customer c ON c.id = v.id_customer
  47. JOIN core_party p ON p.id = c.id_party
  48. WHERE v.invoiced > v.paid
  49. id display_name account_number invoiced paid balance_due
  50. -- ----------------- -------------- -------- ------- -----------
  51. 1 Northwind Trading ACC-1001 33960.0 22080.0 11880.0
  52. 2 Kellner GmbH ACC-1002 8760.0 3000.0 5760.0
  53. ```
  54. Rows, from a question, with no database to set up — that runs against a
  55. bundled 42-object schema. `<provider>` is the model service you choose:
  56. `openai`, `anthropic`, `gemini`, `oci`, `local` (no key at all), or `auto`
  57. (whichever key is set). Point it at your own database with `--url`:
  58. ```bash
  59. schemagate select "which customers owe us money" \
  60. --url "postgresql+psycopg://user:pw@host/db" --answer --provider <provider> --model <model-id>
  61. ```
  62. No key? Drop `--provider` and it prints a prompt to paste into any chat, then
  63. run the SQL it gives you back with `--sql "SELECT ..."`.
  64. More of the bundled schema, with the questions people actually type:
  65. ```bash
  66. schemagate demo "which customers owe us money"
  67. schemagate demo "late shipments by carrier" --prompt # the DDL the model gets
  68. ```
  69. Against your own database it's the same shape:
  70. ```bash
  71. schemagate select "revenue by month" --url postgresql://localhost/app --principal okta:jdoe --role finance
  72. schemagate studio --url postgresql://localhost/app # the same thing, as a page
  73. ```
  74. `schemagate studio` opens a local page where you type questions, switch the caller's
  75. roles, edit hints, and watch what reaches the prompt and what doesn't. The same
  76. page runs publicly at **https://ashishsinha1602.github.io/schemagate/** on the six
  77. bundled schemas, in your browser, with no server behind it. The selector on that page is a JavaScript
  78. port of this library, and a test runs both against 1,789 cases and requires
  79. identical rankings.
  80. If you're coming from Vanna (archived March 2026), `docs/migrating-from-vanna.md`
  81. is the short version: Vanna applied identity when the SQL *ran*; schemagate applies
  82. it before the model sees the schema. Your `User` maps to a `Principal` in one
  83. line.
  84. ## What it saves
  85. Every text-to-SQL call pays for the schema in the prompt. Dump the whole thing
  86. and you pay for every table on every question; hand the model six tables and
  87. you pay for six. Measured on the test schemas, average over their golden
  88. questions, same built-in estimator as `tests/bench.py`:
  89. | schema | objects | full schema, every call | schemagate, average | reduction |
  90. |---|---:|---:|---:|---:|
  91. | Commerce | 42 | 2,483 tokens | 604 | 76% |
  92. | Clinical claims | 27 | 1,568 | 543 | 65% |
  93. | Claims warehouse (star) | 51 | 3,312 | 880 | 73% |
  94. | Bank ledger and trading | 39 | 2,255 | 637 | 72% |
  95. | IoT telemetry | 40 | 2,125 | 448 | 79% |
  96. | Hostile (4 schemas, copies of everything) | 260 | 16,095 | 444 | **97%** |
  97. The last row is the one that matters: the selection stays around six tables
  98. no matter how big the schema is, so the saving grows with the schema. Real
  99. databases are the last row, not the first.
  100. The same count on public schemas nobody here wrote (no model called;
  101. [BENCHMARKS.md](BENCHMARKS.md#prompt-tokens-on-public-schemas-the-saving-grows-with-the-schema)
  102. has the script). On Spider 2.0's real BigQuery and Snowflake warehouses, the
  103. median database with 50 or more tables went from its full schema to a
  104. selection **91.1% smaller** (24 databases). On BIRD, whose databases have a median of seven
  105. tables, the median saving was 8.8%: there was little to cut. The price of
  106. cutting is that a needed table can be left out. On the warehouses, every
  107. table the gold query reads was in the selection for 86.7% of questions.
  108. Against the retrievers other tools use, at the same number of tables and no
  109. model calls, schemagate selected every needed table for 85.3% of warehouse
  110. questions where BM25 managed 78.7% and embedding search 78.2%, and 98.7% of
  111. BIRD questions against 81.0% and 89.4%
  112. ([head-to-head](BENCHMARKS.md#head-to-head-schemagate-against-the-retrievers-other-tools-use)).
  113. On Snowflake's SQL-Schema-Retrieval benchmark (table level, recall@10) the
  114. hashed embedder scores Spider 100.0, BIRD 99.2, BEAVER 65.9, LiveSQLBench 95.3
  115. and LiveSQLBench-Large 64.9 (73.9 with all-MiniLM-L6-v2), above the paper's best
  116. model on four of the five and below Qwen3-Embedding-4B (75.8) on the fifth
  117. ([details](BENCHMARKS.md#schema-retrieval-scored-by-snowflakes-benchmark)).
  118. Worked example, with a price you should replace with your own: a 260-object
  119. schema, 5,000 questions a day, an input price of $3 per million tokens. Full
  120. schema: 16,095 × 5,000 × 30 = 2.4 billion tokens a month, about $7,200. With
  121. schemagate: 444 × 5,000 × 30 = 67 million, about $200. The
  122. [browser demo](https://ashishsinha1602.github.io/schemagate/) has these two
  123. numbers as editable fields under the stats, so you can put in your own volume
  124. and price and watch it recompute against whatever question you ask.
  125. Two more things that cost nothing here and money elsewhere: the selector
  126. itself never calls a model (BM25 plus a hashed embedder, offline,
  127. milliseconds), and the optional descriptions can be written by any chat window
  128. you already pay for instead of an API key — see
  129. [Without an API key](#without-an-api-key).
  130. ## The problem this solves
  131. Two things go wrong when you point an LLM at a database schema.
  132. The first is cost. Most systems paste the whole schema into the prompt on every
  133. question. That's fine for twenty tables and ruinous for two thousand.
  134. The second is worse, and it's the reason I wrote this. Schema selection happens
  135. *before* the query runs, so it happens before row-level security can do
  136. anything. If your selection step isn't identity-aware, the model gets handed a
  137. table the caller can't read. It writes perfectly good SQL. RLS or VPD filters
  138. every row out. The user sees "no records found" and believes it.
  139. That's not an access-denied message. It's a wrong answer with a confident tone,
  140. and the user has no way to tell the difference. Filtering the catalog by
  141. identity first is the only way I know to avoid it.
  142. ```python
  143. from schemagate import Catalog, Principal
  144. cat = Catalog().bootstrap("postgresql://localhost/app") # or: from schemagate.demo_schema import demo_catalog; cat = demo_catalog()
  145. cat.hint("invoice_draft", "pre-issue drafts only, not real revenue")
  146. cat.restrict("hr_compensation", ["payroll"])
  147. sel = cat.select("revenue by month", top_k=6,
  148. principal=Principal("okta:jdoe", roles={"finance"}))
  149. sel.prompt_fragment() # compact DDL, ready for the system prompt
  150. sel.object_list # [{'owner': ..., 'name': ...}]
  151. sel.explain() # why each object was picked
  152. ```
  153. `hr_compensation` is not in that result and its name does not appear anywhere
  154. in the prompt text.
  155. ## Install
  156. ```bash
  157. pip install schemagate
  158. ```
  159. That's the whole thing. One dependency (SQLAlchemy), no API key, no model
  160. download. The default embedder is a hashed n-gram vectoriser that runs offline
  161. and gives byte-identical results on every machine.
  162. Extras, all optional:
  163. ```bash
  164. pip install 'schemagate[postgres]' 'schemagate[oracle]'
  165. pip install 'schemagate[mssql]' 'schemagate[mysql]'
  166. pip install 'schemagate[anthropic]' 'schemagate[openai]' 'schemagate[gemini]'
  167. pip install 'schemagate[huggingface]'
  168. ```
  169. `huggingface` is the no-key, nothing-leaves-the-machine path, and it is the
  170. one extra that is heavy: about 2 GB of wheels plus a 3.1 GB model download the
  171. first time you use it. It is deliberately kept out of `schemagate[all]`.
  172. [docs/local-models.md](docs/local-models.md) has the whole story — the
  173. downloads, the load you wait through once, what it is good at and where it is worse
  174. than a hosted model.
  175. **Every release is signed.** The wheels carry [PEP 740](https://peps.python.org/pep-0740/)
  176. attestations — a signature from GitHub naming the workflow, repository and
  177. commit that built that exact file. Nothing is uploaded by hand and there is no
  178. API token to steal. Check one yourself with
  179. `gh attestation verify <wheel> --repo ashishsinha1602/schemagate`.
  180. ## Quick start
  181. ```bash
  182. pip install schemagate # add an extra for your driver, below
  183. schemagate # opens http://127.0.0.1:8770 on the 42-object demo
  184. ```
  185. Every wheel on PyPI carries a signed provenance attestation naming the commit that built it:
  186. `gh attestation verify <wheel> --repo ashishsinha1602/schemagate`.
  187. Or without installing anything, with every driver already in the image:
  188. ```bash
  189. docker run -p 8770:8770 -e SCHEMAGATE_DATABASE_URL=postgresql://… ghcr.io/ashishsinha1602/schemagate
  190. ```
  191. Leave the URL off and it opens on a 42-object sample schema with data in it,
  192. so there is something to ask questions of before you point it at your own.
  193. Then, in the page:
  194. 1. **Connect.** Paste a URL — `postgres://…`, `postgresql://…`, `mysql://…`,
  195. `oracle://…` and a JDBC string all work, as does the wallet form for an
  196. Autonomous Database. Tick **Save this connection** and give it a name and
  197. the next start reconnects on its own.
  198. 2. **Catalogue.** *Settings → Model* → pick a provider, paste a key, **Save
  199. model** (it is saved, so a restart does not ask again). Then **Catalogue
  200. this database** in the rail. One sentence per object, cached to disk, so a
  201. second run costs nothing.
  202. 3. **Ask.** Type a question in your own words. You get the objects that answer
  203. it, the DDL a model would receive, the SQL, and the rows.
  204. Drivers come as extras — `schemagate[postgres]`, `[oracle]`, `[mysql]`,
  205. `[mssql]`, or `schemagate[all]` for the lot:
  206. ```bash
  207. pip install 'schemagate[postgres]'
  208. ```
  209. ### When a question picks the wrong table
  210. Two levers, both per database and both applied on every reconnect:
  211. * **Hints** (rail → Hints): one object, in your words. *"MyConvo campaigns:
  212. personal-inbox sends from a user's own mailbox."*
  213. * **Glossary** (`POST /api/glossary`): one *word*, everywhere. A term here is
  214. fed to the cataloguing prompt, so every description uses your vocabulary,
  215. and expanded into questions that mention it.
  216. A hint beats a generated description everywhere, and neither needs
  217. re-cataloguing.
  218. ## Commands
  219. Every subcommand, and what it is for. `schemagate <command> --help` prints the
  220. same thing.
  221. ```
  222. schemagate demo [question] run against the bundled 42-object schema
  223. schemagate select --url URL [question] select against your own database
  224. schemagate studio [--url URL] the Studio page, served locally
  225. schemagate describe --url URL write AI descriptions for your objects
  226. schemagate certify URL end-to-end check on a real engine
  227. ```
  228. ### `demo` and `select`
  229. `select` is `demo` pointed at a real database; they take the same flags.
  230. ```bash
  231. schemagate demo "who reports to whom"
  232. schemagate select --url postgresql+psycopg://user:pw@host/db "unpaid invoices"
  233. 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
  234. ```
  235. | flag | what it does |
  236. |---|---|
  237. | `--top-k N` | how many objects to select (default 6) |
  238. | `--principal SOURCE:ID` | who is asking, e.g. `okta:jdoe`, `db:APPUSER`. Must be namespaced |
  239. | `--memory PATH\|1` | remember question → SQL pairs that ran and use them next time (pins + worked examples). `1` for `~/.schemagate/memory/`. Off by default |
  240. | `--role ROLE` | a role the caller holds; repeatable |
  241. | `--prompt` | print the prompt instead of the selection |
  242. | `--explain` | show why each object was picked, and what was withheld |
  243. | `--answer` | write the SQL and run it (needs a provider) |
  244. | `--provider NAME` / `--model ID` | which model to use |
  245. | `--limit N` | row cap for `--answer` |
  246. | `--restrict-from-grants` | take visibility from the database's own GRANTs |
  247. | `--rerank` | let the model reorder the shortlist the maths produced (pooled Spider 80.3% -> 96.5%, BIRD 88.6% -> 96.7% of questions with every gold table in the top 5; [BENCHMARKS.md](BENCHMARKS.md)) |
  248. | `--values` | sample short, non-personal column values |
  249. | `--include` / `--exclude PATTERN` | narrow what is reflected (`select` only) |
  250. | `--schema NAME`, `--no-fk`, `--config JSON`, `--sql SELECT` | `select` only |
  251. ### `studio`
  252. ```bash
  253. schemagate # the demo, or your remembered connection
  254. schemagate studio # empty, connect from the page
  255. schemagate studio --demo # the bundled sample schema
  256. schemagate studio --url postgresql://localhost/app
  257. schemagate studio --remember # save the connection, reconnect next time
  258. schemagate studio --forget # delete the saved connection and exit
  259. ```
  260. | flag | what it does |
  261. |---|---|
  262. | `--url URL` | connect at startup instead of from the page |
  263. | `--host` / `--port` | default `127.0.0.1:8770` |
  264. | `--no-browser` | do not open a browser |
  265. | `--demo` | open on the bundled sample schema |
  266. | `--remember` | save this connection to `~/.schemagate/connection.json` (`0600`) and replay it on the next start. Includes the database and wallet passwords, so it is off unless asked for |
  267. | `--forget` | delete that file and exit |
  268. | `--allow-connect` / `--no-connect` | whether the page may open a database itself. On by default on loopback, off when bound anywhere else |
  269. | `--restrict-from-grants` | derive visibility from GRANTs at startup |
  270. | `--values` | sample column values while reflecting |
  271. | `--include` / `--exclude` / `--config` | as for `select` |
  272. In the page: **Catalogue this database** describes what has no description yet,
  273. **Re-catalogue all** rewrites every one, and **Resync schema** re-reflects the
  274. database while keeping the descriptions you already have.
  275. ### `describe`
  276. ```bash
  277. # with a key
  278. schemagate describe --url postgresql://localhost/app --provider anthropic --model claude-sonnet-5
  279. # without one: write the prompt out, paste it into any chat, apply the reply
  280. schemagate describe --url postgresql://localhost/app --out prompt.txt
  281. schemagate describe --url postgresql://localhost/app --apply reply.json
  282. ```
  283. | flag | what it does |
  284. |---|---|
  285. | `--out FILE` | write the prompt instead of calling a model |
  286. | `--apply REPLY.json` | apply a reply produced that way |
  287. | `--all` | re-describe everything, not only what is missing |
  288. | `--cache FILE` | where to keep generated descriptions; re-runs are then free |
  289. | `--provider` / `--model` | which model to use |
  290. | `--include` / `--exclude` / `--schema` / `--config` | as for `select` |
  291. ### `certify`
  292. ```bash
  293. schemagate certify "postgresql+psycopg://user:pw@host/db"
  294. ```
  295. Reflects, selects, and reports what a real engine actually did — the check to
  296. run before trusting a new database or driver.
  297. ```bash
  298. schemagate certify "postgresql+psycopg://user:pw@host/db" --golden --save golden.jsonl
  299. ```
  300. `--golden` certifies *your* schema instead of the engine: one question per table
  301. written from the table's own words (its comment, or its name, and its most
  302. distinctive columns), one per foreign key, one per view, each with the answer known
  303. by construction, run through `select()`. Nothing is created and no model is called.
  304. What it prints that matters is the list of tables not found even by their own
  305. description, because no question will reach those; the fix is a comment or a hint.
  306. `--save` writes the set as JSONL (`{"question": ..., "tables": [...]}`) so you can
  307. edit the questions into real ones and rerun with `--golden golden.jsonl`;
  308. `--provider ... --model ...` has a model rewrite each question the way a person
  309. would ask it, which is the harder test. Exit status is 1 when a table is unreachable.
  310. ### Environment
  311. | variable | what it does |
  312. |---|---|
  313. | `SCHEMAGATE_CONNECT_ARGS` | JSON passed to `create_engine(connect_args=...)`, for connections a URL cannot express (an Autonomous Database wallet) |
  314. | `SCHEMAGATE_REMEMBER=1` | save the connection without passing `--remember` |
  315. | `SCHEMAGATE_HOME` | where `connection.json` lives (default `~/.schemagate`) |
  316. | `ANTHROPIC_API_KEY`, `OPENAI_API_KEY`, `GEMINI_API_KEY` / `GOOGLE_API_KEY`, `OCI_COMPARTMENT_ID` | picked up automatically by `--provider` |
  317. | `SCHEMAGATE_STUDIO_LOG=1` | log Studio requests |
  318. | `SCHEMAGATE_STUDIO_ALLOWED_HOSTS` | comma-separated host names the Studio answers to, besides IP addresses and `localhost` (for a Studio reached by a DNS name) |
  319. | `SCHEMAGATE_VALUES=1` | MCP server: read column values at startup, like `--values` (`SCHEMAGATE_VALUES_BUDGET` = seconds, default 30) |
  320. | `SCHEMAGATE_RESTRICT_FROM_GRANTS=1` | MCP server: take visibility from the database's GRANTs, like `--restrict-from-grants` (PostgreSQL, MySQL, Oracle) |
  321. | `SCHEMAGATE_CATALOG_MAX_AGE` | MCP server: seconds a catalogue may be used before it is re-read; if the re-read fails, the scoping tools refuse until one succeeds (see below) |
  322. | `SCHEMAGATE_REREAD_TIMEOUT` | MCP server: seconds a call waits for that re-read before it is refused as stale (default 30); the re-read keeps running and is served if it returns |
  323. | `SCHEMAGATE_MCP_RERANK=1` | MCP server: `select_schema` lets the server's model (`SCHEMAGATE_MCP_PROVIDER` + `SCHEMAGATE_MCP_MODEL`) reorder the top 20, like `--rerank`; one model call per question; set without a model, the server will not start |
  324. ## How it picks
  325. 1. Reflect the schema through SQLAlchemy. No vendor SQL anywhere.
  326. 2. Index names, columns, comments, hints, and view definitions. That last one
  327. matters more than it sounds: a view exposes only its output columns, so
  328. `v_stock_shortfall` looks like it's about "shortfall" when the thing you'd
  329. search for, `reorder_point`, is buried in its SELECT.
  330. 3. Retrieve with reciprocal-rank fusion over BM25 and vector similarity.
  331. Neither alone is good enough. Vectors miss exact identifiers; BM25 misses
  332. "owe us money" → `balance`.
  333. 4. Walk foreign keys to pull in join tables the question never mentions. In my
  334. experience this is the single biggest cause of generated SQL that parses
  335. but won't run.
  336. 5. Apply the caller's identity at every step above.
  337. ## Numbers
  338. **On access control, scored by someone else's benchmark.** The RBAC Text-to-SQL benchmark
  339. (SIGMOD'27) gives each question a role and a column-level policy and scores answering versus
  340. refusing with its authors' own evaluator. With claude-sonnet-4-5, five trials:
  341. | | AC-F1 | violations | SQL that read forbidden data |
  342. |---|---|---|---|
  343. | BIRD, schemagate | **90.1** | **5.4%** | **0** |
  344. | BIRD, paper's best (same model, policy in the prompt) | 88.1 | 7.4% | not reported |
  345. | Spider, schemagate | 93.0 | 5.1% | **0** |
  346. | Spider, paper with the same model | 93.0 | 4.6% | not reported |
  347. | Spider, paper's best (DeepSeek V3.2-Reasoning) | 95.9 | 2.0% | not reported |
  348. The model never sees the restricted part of the schema. Caveats and every column are in
  349. [BENCHMARKS.md](BENCHMARKS.md#access-control-scored-by-someone-else-the-rbac-text-to-sql-benchmark).
  350. **And on a second one.** Spider-ACL (EACL 2026) labels each of 19,624 question-and-role pairs with what
  351. PostgreSQL itself allowed. With claude-sonnet-5-5, all 19,624:
  352. | | refusal F1 | false permits | queries that read data the role was not granted |
  353. |---|---|---|---|
  354. | schemagate | **0.984** | **1.8%** | **0 of 8,128 answered** |
  355. | paper's best (LoRA-tuned Mistral-7B) | 0.933 | -- | not reported |
  356. The paper's systems are 2024-25 models; details and limits in
  357. [BENCHMARKS.md](BENCHMARKS.md#access-control-a-second-benchmark-spider-acl), and both results in one
  358. [technical report (PDF)](docs/papers/schemagate_access_control.pdf), [DOI 10.5281/zenodo.23446210](https://doi.org/10.5281/zenodo.23446210).
  359. **On execution accuracy, scored by the benchmarks' own evaluators.** One model
  360. call per question, about 2,100 prompt tokens, no candidates, no voting, no agent loop:
  361. | | Model | EX | Best published, 2026-10-19 |
  362. |---|---|---|---|
  363. | BIRD dev, all 1,534 questions | Claude Opus 5.5 | **77.12** | hidden test set, scored by BIRD: 72.11 for this setup; 74.34 with 4 training examples per prompt (dev 84.03) |
  364. | BIRD Mini-Dev, SQLite / PostgreSQL / MySQL | Claude Opus 5.5 | **81.0 / 72.6 / 72.4**, #1 on all three | next best on BIRD's board, 2026-10-31: 75.6 / 69.0 / 49.2 |
  365. | UniQL, Oracle dialect, first try, re-audited 2026-11-12 release | Gemini 3.8 Flash | **63.69** | 49.61 (Claude Sonnet 4.5, scored before the re-audit) |
  366. | Spider 2.0-lite, the 135 SQLite questions only | Claude Sonnet 5.5 | **71.9** | 57.78 Pass@1 (FlexSQL agent, gpt-oss-120b) |
  367. | Spider 2.0-lite, the 205 BigQuery questions | Claude Sonnet 5.5 | **45.9** | no BigQuery-only row published |
  368. Opus varies about ±1 run to run. The Mini-Dev rows are on BIRD's own leaderboard
  369. ([PR #251](https://github.com/bird-bench/bird-bench.github.io/pull/251), merged 2026-10-25). Files and commands:
  370. [`benchmarks/results/`](benchmarks/results/); the context is in
  371. [BENCHMARKS.md](BENCHMARKS.md#execution-accuracy-scored-by-each-benchmarks-own-evaluator).
  372. **On public benchmarks, so you can check them without trusting me:**
  373. [BENCHMARKS.md](BENCHMARKS.md) has schemagate on Spider and BIRD, with the
  374. scripts in [`benchmarks/`](benchmarks/) and the data downloaded from the
  375. original sources. The headline is the pooled setting — every Spider database
  376. merged into one 876-table catalog, no hint about which one to look in:
  377. | Spider dev, 876 tables pooled | `pip install schemagate` | `[huggingface]` | `[huggingface]` + `--rerank` |
  378. |---|---|---|---|
  379. | top_k=5 | 76.7% | 80.3% | **96.5%** |
  380. | top_k=10 | 86.1% | 90.7% | |
  381. | top_k=20 | 94.2% | 97.0% | |
  382. BIRD dev, 1,534 questions: 96.7% per-database at top_k=5, 91.3% pooled at
  383. top_k=10; pooled at top_k=5, 83.8% hashed, 88.6% with `[huggingface]`, 96.7% with `--rerank`.
  384. Re-measured on 1.5.2; reranked with claude-haiku-5-5, one call per question.
  385. And **Spider 2.0-lite**, the benchmark built for real warehouses — 162
  386. databases, 8,255 tables, a median of 16 per database and a maximum of 785:
  387. **79.8% at top_k=10** over all 247 usable questions, none excluded (84.5% on
  388. the 233 whose gold tables resolve), no pooling needed because the databases
  389. are already big. An earlier version of this page said 82.9%; that number was
  390. measured while Windows had silently made thousands of the schema files
  391. unreadable, and a second re-run was owed after the loader was found to be
  392. reading the benchmark's per-column descriptions as one table description.
  393. Both re-runs are done and [BENCHMARKS.md](BENCHMARKS.md) keeps the
  394. corrections rather than deleting them.
  395. **End to end, on the metric those boards actually score:** schemagate plus
  396. Claude Opus 5.5 gets **77.12% execution accuracy on all 1,534 BIRD dev
  397. questions**, one model call per question (table above). The BIRD Mini-Dev
  398. rows are on BIRD's own leaderboard. A main-board placing needs the held-out
  399. test set, which only the BIRD team runs: 72.11 execution accuracy for this
  400. setup, and 74.34 for the second configuration, which adds the four closest
  401. BIRD training questions to each prompt.
  402. Everything below is measured on schemas I invented, which is worth less and
  403. is why the public numbers come first.
  404. Six test schemas ship with the library. Run `python tests/bench.py` and you
  405. get all of this printed back. `TESTING.md` is the full record of what was
  406. tested, what broke, and what was found to be the database rather than schemagate.
  407. Every number below is printed by that run, and the run fails if any of them
  408. stops matching — `bench.py` reads this table back and compares.
  409. **recall@6** here is the share of *gold tables* retrieved in the top six,
  410. micro-averaged over questions. The **literal** column asks questions that
  411. reuse the schema's own vocabulary; the **business words** column asks for the
  412. same things the way a person does, with no vocabulary overlap. Both matter and
  413. they disagree, which is the point of showing both.
  414. | schema | objects | recall@6, literal | recall@6, business words |
  415. |---|---|---|---|
  416. | commerce | 42 | 100% | 50.0% |
  417. | clinical claims | 27 | 100% | 46.7% |
  418. | claims warehouse (star) | 51 | 100% | 58.3% |
  419. | bank ledger and trading | 39 | 100% | 64.3% |
  420. | IoT telemetry | 40 | 100% | 60.0% |
  421. | hostile (4 schemas, copies of everything) | 260 | 100% | 85.7% |
  422. | | |
  423. |---|---|
  424. | real table beats its backup/staging copy, 19 cases across schemas | 19/19 |
  425. | recall without foreign-key expansion | 100% |
  426. | prompt tokens, full schema every call | 2,812 |
  427. | prompt tokens, schemagate average | 757 (−73.1%) |
  428. Measured with the **hashed embedder** — what `pip install schemagate` gives
  429. you, no extras. `schemagate[huggingface]` swaps in sentence-transformers and
  430. the business-word numbers move a long way: on the held-out paraphrase set
  431. `tests/run_paraphrase_eval.py` reports 58.6% overall hashed and 82.8% with
  432. MiniLM. That harness counts a question as hit if *any* gold table is
  433. retrieved, which is a looser predicate than this table's, so its figures are
  434. not comparable with these — it prints which embedder it used for the same
  435. reason.
  436. Token counts come from an estimator built into the benchmark so the number is
  437. reproducible with no network and no extra install. `pip install tiktoken` and
  438. the same script switches to exact `cl100k_base` counts. The ratio holds either
  439. way.
  440. Six schemas rather than one because a single schema whose questions happen to
  441. share vocabulary with its own table names will flatter any retriever. The
  442. second is a different domain entirely. The third is 260 objects of deliberate
  443. sabotage: an `_archive` and `_stg` copy of every table, the same table name in
  444. three schemas, an 8-deep foreign-key chain, a reference cycle, composite keys,
  445. a 320-column table, 100-character identifiers, and names in Spanish and
  446. Japanese. The fourth is a claims warehouse star schema built so that several
  447. tables are plausible for every question and one is right: the same fact at
  448. four grains, a slowly-changing member dimension with a history table, one date
  449. dimension joined five different ways, bridge tables, and fifteen `_bkp`,
  450. `_old`, `_v2`, `_tmp` and `stg_` copies of the important ones. The fifth is a
  451. bank: a ledger at three grains, trades versus positions versus settlements,
  452. FX both as a daily table and an as-of view, lending, and the KYC and AML
  453. tables most callers must never see. The sixth is an IoT fleet: readings at
  454. raw, one-minute and hourly grains, six monthly partition tables, an alarm
  455. lifecycle spread across three tables. All six are invented. No real schema
  456. from anywhere is in this repo.
  457. That 50% row is the honest one. Read it before you adopt this.
  458. ## The 50% row, and what to do about it
  459. The default embedder matches subwords, not meaning. Ask it for "things we're
  460. running out of" and it will not find `v_stock_shortfall`, because those two
  461. strings have nothing in common. Ask it about `stock_shortfall` and it's
  462. excellent.
  463. If your users type identifier-shaped questions, you're done, and you never need
  464. an API key. If they type like people, give the catalog descriptions. There are
  465. two ways, and neither is required.
  466. **With an API key in the environment, you get them without asking.** Every path
  467. that answers a question — `schemagate select`, `--answer`, the MCP server, the
  468. Studio's connect — describes the catalogue first, caches the result per
  469. connection under `~/.schemagate/descriptions/`, and re-describes an object only
  470. when its structure changes. A hint you wrote, or a database comment that says
  471. something, is never overwritten; a comment that only restates the object's
  472. name in the schema's own boilerplate is replaced, because it was diluting
  473. every word it contained. Measured on a 1,200-object schema, that is the
  474. difference between six and eight of eight complex questions producing SQL that
  475. runs. `SCHEMAGATE_AUTO_DESCRIBE=0` turns it off; with no key present nothing
  476. is called and nothing changes.
  477. ### Without an API key
  478. Any chat window you already have — ChatGPT, Gemini, Copilot, a
  479. local model — can write the descriptions. schemagate gives you the prompt and
  480. takes the reply:
  481. ```bash
  482. schemagate describe --url postgresql://localhost/app --out prompt.txt
  483. # paste prompt.txt into a chat; save its JSON reply as reply.json
  484. schemagate describe --url postgresql://localhost/app --apply reply.json --config catalog.json
  485. schemagate select --url postgresql://localhost/app "things we're running out of" --config catalog.json
  486. ```
  487. The prompt is metadata only — names, types, comments, foreign keys, never rows
  488. — and one paste covers every undescribed object. The reply lands in the
  489. `describe` block of `catalog.json`, next to your `restrict` and `hint` blocks,
  490. and `select`, `studio` and the MCP server (`SCHEMAGATE_CATALOG_CONFIG`) all
  491. read it. From Python it's the same idea: `cat.describe_prompt()` and
  492. `cat.describe({"v_stock_shortfall": "Items below their reorder level."})`.
  493. ### With a local model, and no key at all
  494. ```bash
  495. pip install 'schemagate[huggingface]'
  496. schemagate describe --url postgresql://localhost/app --provider local --model Qwen/Qwen2.5-1.5B-Instruct --cache .schemagate-cache.json
  497. ```
  498. or, in the Studio, *Settings → Model* → **Local (transformers)**. No key
  499. field appears, because there is no key.
  500. ```python
  501. from schemagate.ai import SchemaDescriber, LocalProvider
  502. cat.describe(SchemaDescriber(LocalProvider(), cache_path=".schemagate-cache.json"))
  503. ```
  504. Writing one sentence per table is a small enough job that a 1.5B model does it
  505. acceptably. Writing multi-table SQL is not, and the Studio uses the same
  506. provider for both — so if you have a key, catalogue locally but answer with
  507. the key. Read [docs/local-models.md](docs/local-models.md) before you turn it
  508. on: it covers the two downloads, the load you wait through once, the ~3 GB of RAM, the
  509. caching that makes the second run free, and why a weak model's bad description
  510. can no longer bury the object it describes.
  511. ### With your own key
  512. ```python
  513. from schemagate.ai import SchemaDescriber, AnthropicProvider
  514. cat.describe(SchemaDescriber(AnthropicProvider(model="claude-sonnet-4-5"),
  515. cache_path=".schemagate-cache.json"))
  516. ```
  517. One sentence per table, written by the model, indexed like any other schema
  518. text. On the bundled schema that takes the business-words row from 50% to 100%
  519. with no change to the identifier-style questions.
  520. Anthropic, OpenAI and Gemini are supported. Anything else goes through
  521. `CallableProvider`, which is also your escape hatch when a vendor changes their
  522. SDK and you don't want to wait for a release from me.
  523. ```python
  524. from schemagate.ai import (AnthropicProvider, OpenAIProvider, GeminiProvider,
  525. CallableProvider, auto_provider, available_providers)
  526. AnthropicProvider(model="claude-sonnet-4-5") # ANTHROPIC_API_KEY
  527. OpenAIProvider(model="gpt-4.1-mini") # OPENAI_API_KEY
  528. GeminiProvider(model="gemini-2.5-flash") # GEMINI_API_KEY
  529. OpenAIProvider(model="…", base_url="http://localhost:11434/v1") # anything local
  530. CallableProvider(lambda system, prompt: my_llm(system, prompt))
  531. available_providers() # ['AnthropicProvider'] — names, never key values
  532. auto_provider(model="…") # picks whichever key is set
  533. ```
  534. `model` is required. I'm not shipping a default model ID, because model IDs
  535. change every few months and a hardcoded one eventually 404s for everybody who
  536. installed the version before the fix.
  537. Three things worth knowing before you turn this on:
  538. **What leaves your network.** Table names, column names, types, nullability,
  539. existing comments, foreign keys. Not one row of data — `ObjectDoc` has no field
  540. that could hold one, and there are tests asserting both halves of that. Nothing
  541. is sent unless you call `describe()`.
  542. **What it costs.** One short call per undescribed object, once. Objects that
  543. already have a database comment or a hint are skipped by default. Results cache
  544. by content, so re-running is free and only changed tables get re-described. Ask
  545. before you pay:
  546. ```python
  547. describer.estimate_calls(docs) # calls describe() would actually bill for
  548. describer.preview(doc) # the exact text that would be sent
  549. ```
  550. **What happens when it fails.** The object is skipped, cataloging continues, and
  551. `describer.failures` lists what was missed. Pass `strict=True` if you'd rather
  552. it raise. A `hint()` you wrote by hand always beats a generated description, so
  553. fixing a bad one costs nothing.
  554. ### The embedder picks itself
  555. `pip install schemagate` uses the hashed n-gram vectoriser: offline, instant,
  556. byte-identical on every machine. Install `schemagate[huggingface]` and a
  557. sentence model is used automatically instead -- no flag, no benchmark, no
  558. decision for you. Measured across the six bundled schemas, 98 questions, no
  559. descriptions:
  560. | | recall@6 |
  561. |---|---|
  562. | hashed n-gram (base install) | 90/98 |
  563. | all-MiniLM-L6-v2 (`[huggingface]`) | 93/98 |
  564. The gain is concentrated exactly where the hashed embedder is documented to be
  565. weak -- questions phrased the way people speak. On the commerce schema, which
  566. carries that set, it goes 15/18 to 18/18.
  567. It is not the base default because that would trade one dependency for torch,
  568. and the guarantee that the same text gives the same vector everywhere.
  569. `SCHEMAGATE_AUTO_EMBEDDER=0` keeps the hashed one if you need to reproduce an
  570. older index.
  571. You can swap in a hosted embedder too, though on identifier-heavy schema text
  572. the offline ones are often just as good and cost nothing per query.
  573. ```python
  574. from schemagate.ai import APIEmbedder, OpenAIProvider
  575. provider = OpenAIProvider(model="gpt-4.1-mini",
  576. embed_model="text-embedding-3-small")
  577. cat = Catalog(embedder=APIEmbedder(provider, dim=1536))
  578. ```
  579. ### Your organisation's words: a glossary
  580. Some questions share nothing with any identifier, and no description or model
  581. reliably bridges them: "money we gave back to shoppers" is `billing_credit_note`.
  582. State it once, in the same `catalog.json` as your access rules:
  583. ```json
  584. {"terms": {"gave back": "billing_credit_note",
  585. "revenue": ["billing_invoice", "v_monthly_revenue"]}}
  586. ```
  587. or `cat.term("gave back", "billing_credit_note")` in Python. A term matches as a
  588. phrase, plurals folded, and only the most specific term counts ("take things
  589. offline" beats "offline"). It is applied when the question is asked, so nothing
  590. is re-indexed, and it never widens access: a term for a table the caller may
  591. not read does nothing for that caller. A term naming an object that does not
  592. exist is an error, not a silent no-op.
  593. What it is for, measured on the 58 business-language questions across the six
  594. bundled schemas (`tests/run_terms_repair.py`): every question that missed was
  595. fixed by adding **one** short term taken from it, and no other question broke --
  596. 23 of 23 on the base embedder, 11 of 11 with MiniLM. What it is not: a glossary
  597. written in advance, from table names alone, barely moved the held-out questions
  598. (18/26 to 19/26 on the base embedder, 21/26 unchanged with MiniLM;
  599. `tests/run_terms_eval.py`), because people's words are hard to guess. Add terms
  600. for the questions you see missing.
  601. ### The full ontology: concepts, rules, a hierarchy
  602. A glossary term is the simplest concept. The full form says what a word means,
  603. precisely, so the model writes the right SQL rather than a plausible one:
  604. ```json
  605. {"ontology": {"concepts": {
  606. "revenue": {"synonyms": ["turnover", "sales"],
  607. "maps": ["billing_invoice.total_net"],
  608. "filter": "billing_invoice.status = 'issued'",
  609. "definition": "Issued invoices only; drafts and void excluded.",
  610. "broader": ["money in"]},
  611. "money in": {"maps": ["v_monthly_revenue"]}}}}
  612. ```
  613. A question that uses a concept's name or a synonym brings its objects in, and
  614. its meaning is written above the DDL:
  615. ```
  616. -- What the business terms in this question mean:
  617. -- revenue (also: turnover, sales): main.billing_invoice.total_net; only where billing_invoice.status = 'issued'. ...
  618. ```
  619. Concepts one step away (`broader`, or narrower) add a weaker signal. A meaning
  620. line is left out for any caller who may not see everything it names -- the mapped
  621. table and column, and any table or column its rule or definition mentions -- so a
  622. concept never puts a withheld name back into the prompt.
  623. Where a vocabulary comes from:
  624. ```bash
  625. schemagate ontology import semantic_manifest.json --from dbt --url ... --config catalog.json --save
  626. schemagate ontology import model.yaml --from snowflake --url ... --config catalog.json --save
  627. schemagate ontology import glossary.csv --from csv --url ... --config catalog.json --save
  628. schemagate ontology suggest history.jsonl --url ... --config catalog.json # review, then --save
  629. schemagate ontology check --url ... --config catalog.json
  630. ```
  631. `suggest` learns concepts from questions people asked and the SQL that answered
  632. them (`--memory` reads schemagate's own query memory). CSV headers from Collibra
  633. and Microsoft Purview exports work as they are.
  634. **Or pull them from the database, and get the schema as a property graph.** No file
  635. and no model call: `schemagate ontology pull --url ...` proposes concepts from table
  636. and column comments, view filters and computed view columns, for you to review.
  637. `schemagate graph --url ...` writes tables, columns, foreign keys, joins read from
  638. view SQL and concepts as JSON or GraphML, for one caller, so nothing they may not see
  639. is in it. `--query-log` adds the joins people's own queries declare, read from the
  640. engine's statement store with every value stripped first. On Oracle 23ai and later,
  641. `--format sql` writes `CREATE PROPERTY GRAPH` for you to review and run.
  642. `schemagate certify <url>` runs a golden set of questions with known answers through
  643. all of it on your engine. See [docs/property-graph.md](docs/property-graph.md).
  644. **What it does to accuracy, measured.**
  645. | | setting | without | with |
  646. |---|---|---|---|
  647. | Concepts learned from history (`benchmarks/learn_concepts.py`) | Spider dev, 166 databases pooled (876 tables), learned from half of each database's questions, scored on the other half: all gold tables in the top 5 | 73.3% | **82.8%** (+49 / −0, p<0.0001; MiniLM 79.1% → 85.9%) |
  648. | A complete glossary (`benchmarks/bird_ontology.py`, arm D) | BIRD dev, 201 held-out questions asked **without** their evidence, execution accuracy, Claude Sonnet 5 | 44.8% | **54.7%** (+28 / −8, p=0.001) |
  649. | Planted definitions (`benchmarks/planted_definitions.py`) | a seeded SQLite with 8 business terms whose obvious reading is wrong (revenue net of discounts, refunds and internal SKUs; "active" from recent orders, not a stale flag; churn with a 30-day grace), 21 questions with answers computed from the definitions, execution accuracy, Claude Sonnet 5 | 4.8% | 71.4% (+14 / −0, p=0.0001); **100%** once a definition brings the terms it cites (21/21, +6 / −0) |
  650. For scale on BIRD: pasting each question's own evidence into it (BIRD's usual
  651. setting) scores 61.2%, so the ontology delivers about 60% of that gain with no
  652. evidence typed per question; two identical runs without it differ by 18 answers.
  653. One result did not hold: concepts built from *other* questions' evidence gave
  654. 46.8% against 44.8% -- inside that noise -- because BIRD's evidence is mostly
  655. one-off formulas that rarely recur. An ontology helps with the definitions it
  656. actually holds.
  657. The planted-definition test (suggested in review) writes the definitions first
  658. and computes every expected answer from them, so it measures whether the SQL is
  659. *right*, not whether the tables were. A repeat run without the glossary changed
  660. no answers. The six questions the glossary first missed were one pattern: a
  661. term defined through another ("partner sales is revenue on partner orders")
  662. retrieved itself but not the term it leans on, so the model guessed what
  663. revenue means -- confidently, on four questions it had declined without the
  664. glossary. A matched concept now brings the concepts its definition or filter
  665. cites (`Ontology.with_cited`, followed three steps, visibility applied to each),
  666. and the same run scores 21/21. Compiling revenue into a view the dependent
  667. terms point at, with the gross columns still queryable, scored 19/21, and
  668. neither of its misses involved revenue: the gap was retrieval, not the model
  669. re-deriving a definition it had been given.
  670. Join-path completion through the schema graph was measured and not built:
  671. on Spider only 2 of 266 misses at top_k=5 are a missing bridge table, because
  672. foreign-key expansion already brings bridges in (`benchmarks/join_paths.py`).
  673. ## Connecting to what you actually have
  674. Most people do not have a SQLAlchemy URL. They have a wallet zip, a JDBC
  675. string out of a config file, or a host and a port. `schemagate.connect`
  676. turns any of those into the two things SQLAlchemy needs:
  677. ```python
  678. from sqlalchemy import create_engine
  679. from schemagate import Catalog
  680. from schemagate.connect import resolve
  681. url, connect_args = resolve("jdbc:oracle:thin:@//host:1521/ORCLPDB1")
  682. cat = Catalog().bootstrap(create_engine(url, connect_args=connect_args))
  683. ```
  684. `connect_args` is not optional. An Autonomous Database has no URL worth the
  685. name — the wallet directory, the wallet password and the TNS alias have
  686. nowhere to live in one — so the URL degenerates to `oracle+oracledb://@` and
  687. the connection travels beside it:
  688. ```python
  689. url, connect_args = resolve({
  690. "kind": "wallet",
  691. "wallet": "~/Downloads/Wallet_mydb.zip", # the zip as downloaded
  692. "alias": "mydb_high",
  693. "user": "ADMIN", "password": "...",
  694. })
  695. ```
  696. The zip is extracted next to itself, because the driver re-reads it on every
  697. reconnect — a temporary directory gives you a connection that works once.
  698. Same thing from the Studio, with a dropdown instead of a dict:
  699. ```bash
  700. pip install "schemagate[all]" # every driver, the model SDKs, MCP
  701. schemagate
  702. ```
  703. On Oracle Cloud, add the OCI SDK so cataloguing can go through OCI Generative
  704. AI with no API key at all:
  705. ```bash
  706. pip install "schemagate[all,oci]"
  707. ```
  708. It is a separate word because it is a separate size: the OCI SDK is 488 MB and
  709. 17,505 modules, against 217 MB for everything else together.
  710. | you have | pick |
  711. |---|---|
  712. | `postgresql+psycopg://...` | SQLAlchemy URL |
  713. | `jdbc:oracle:thin:@//host:1521/SVC` | JDBC URL |
  714. | `Wallet_mydb.zip` + `mydb_high` | Oracle wallet |
  715. | a host, a port and a database | the engine by name |
  716. Once connected, the Studio shows the command that reproduces it — the
  717. `schemagate select ... --url ...` line and the Python equivalent, with the
  718. password as `$DB_PASSWORD` rather than the real one. Try it in the page, then
  719. take the command.
  720. Connecting works out of the box on `localhost`, where the only person who can
  721. reach the page is already sitting at a shell on that machine. Serve the Studio
  722. on any other address and it takes `--allow-connect`, because there it becomes
  723. a URL box anyone on the network can use to make your server connect to hosts
  724. only it can see. `--no-connect` turns it off anywhere.
  725. Other web pages open in the same browser can reach `localhost` too, so the
  726. Studio only takes a request from the page it served: every POST must carry a
  727. random per-session token that is written into that page, a POST from any other
  728. origin is refused, and the Studio answers only to an IP address, `localhost`, or
  729. a name listed in `SCHEMAGATE_STUDIO_ALLOWED_HOSTS`, so a DNS-rebinding page
  730. cannot load it under its own name. A failed connection reports the
  731. exception type and nothing else, because driver errors quote the URL they
  732. were given and a URL carries a password.
  733. ## Restricting one column, and reading the ACL you already have
  734. An object-level rule cannot express the common case: the table is the right
  735. answer and one column in it is not.
  736. ```python
  737. from schemagate import Catalog, Principal
  738. cat = Catalog().bootstrap("postgresql+psycopg://user:pw@host/db")
  739. cat.restrict_column("employee", "salary", ["payroll"])
  740. cat.index()
  741. analyst = Principal("okta:jdoe")
  742. print(cat.select("who reports to whom", principal=analyst).prompt_fragment())
  743. ```
  744. `salary` is **absent** from that fragment — not `REDACTED`, not renamed. The
  745. name is itself the disclosure: a model that knows the column exists can ask
  746. about it, join on it, or mention it in an explanation. Any `-- FK` line naming
  747. a withheld column is dropped too, since it would put the identifier straight
  748. back.
  749. ### What was shown, and to whom
  750. ```python
  751. sel = cat.select("who reports to whom", principal=analyst)
  752. sel.to_dict()
  753. # {'question': 'who reports to whom',
  754. # 'principal': 'okta:jdoe', 'roles': [], 'total_objects': 219,
  755. # 'hits': [{'object': 'hr.employee', 'kind': 'TABLE', 'score': 0.031,
  756. # 'reason': 'hybrid', 'columns_shown': 3, 'columns_withheld': 1}]}
  757. ```
  758. The record an auditor asks for after the fact, and the one thing that cannot
  759. be reconstructed later — the catalog will have changed, roles will have
  760. changed, and the question is gone. It carries the **count** of withheld
  761. columns, never their names: a log that lists what it withheld has disclosed it
  762. to everyone who can read the log.
  763. ### Deriving visibility from GRANTs
  764. At forty tables a hand-written `restrict` map is fine. At four hundred it is a
  765. second copy of an ACL that already exists in the database, and two copies
  766. drift.
  767. ```bash
  768. schemagate select "what do we pay our doctors" \
  769. --url "postgresql+psycopg://user:pw@host/db" --restrict-from-grants
  770. ```
  771. ```python
  772. from schemagate.grants import restrict_from_grants
  773. report = restrict_from_grants(cat, engine, report=True)
  774. # then the half grants cannot see: objects under a row-level policy are
  775. # flagged in the prompt, probed per role on PostgreSQL, and the views that
  776. # bypass the policy are named -- docs/row-level-security.md
  777. from schemagate.rls import restrict_from_policies
  778. print(restrict_from_policies(cat, engine, report=True))
  779. print(report)
  780. # postgresql: 629 object(s) seen, 218 restricted, 1 public, 0 unmatched, 1 role(s) expanded
  781. ```
  782. PostgreSQL and Oracle. Nested roles are flattened transitively, so a user
  783. whose group maps to a role that inherits the granted one still reaches the
  784. object. An object with no grant row is **left untouched** and named in
  785. `report.objects_unmatched` — silence is not a denial, and restricting on
  786. absence would break a working catalog the first time a connection could not
  787. see everything.
  788. **It reads grants, so it does not see row-level policies.** Measured on Oracle
  789. 26ai: a caller whose Virtual Private Database policy admits zero rows still
  790. holds `SELECT` in `ALL_TAB_PRIVS`, still appears in `ALL_TABLES`, and is still
  791. put in front of the model with every column. Nothing leaks -- the database
  792. enforces the policy -- but the prompt names a table that caller cannot get a
  793. row out of. [docs/row-level-security.md](docs/row-level-security.md) has the
  794. measurement, what to do about it today, and the fix.
  795. ### Where the caller's roles come from
  796. Grants answer which roles may see an object. The other half — which roles
  797. *this caller* holds — used to be whatever the caller said, which is fine for
  798. a desktop client on its own database and no check at all for a hosted
  799. server. A `groups` block in the catalog config reads it from where it is
  800. already kept, and the roles a client sends are then **ignored**:
  801. ```json
  802. {"groups": {"sources": [
  803. {"type": "entra", "tenant": "contoso.onmicrosoft.com",
  804. "client_id": "…", "client_secret": "${ENTRA_CLIENT_SECRET}"},
  805. {"type": "native"}],
  806. "map": {"Payroll Team": "payroll"}}}
  807. ```
  808. Five sources: **`entra`** (Microsoft Entra ID through Graph, transitive
  809. group membership, ids and display names both), **`native`** (the database's
  810. own role graph — the same views `restrict_from_grants` reads, walked upward
  811. from the user, so a two-level `GRANT` chain resolves), **`sql`** (a
  812. membership table, one bound `:subject`), **`http`** (any endpoint returning
  813. groups as JSON), **`static`** (a mapping in the file). Each answers only for
  814. the subject namespaces it serves; results are a union; a `map` turns group
  815. ids into role names. Answers are cached for `ttl` seconds.
  816. A source that cannot answer is an error to that caller, not an anonymous
  817. selection: "no groups" and "could not ask" are different answers and only
  818. one is safe to act on. Verified live on PostgreSQL 16, MySQL 8.4 and Oracle
  819. Autonomous Database 26ai: the resolver and the grants-restricted catalog
  820. agree with `has_table_privilege` and with an actual `SELECT`.
  821. [docs/groups.md](docs/groups.md) has the block, every source, and what was
  822. tested.
  823. ## Databases
  824. Reflection uses only SQLAlchemy's dialect-agnostic Inspector. There's no
  825. hand-written SQL in `schemagate.introspect` and a test fails the build if any
  826. appears, so in principle any dialect SQLAlchemy supports will work.
  827. In principle isn't evidence, so there's a script:
  828. ```bash
  829. python scripts/certify_dialect.py 'postgresql+psycopg://user:pw@host/db'
  830. python scripts/certify_dialect.py 'oracle+oracledb://user:pw@host:1521/?service_name=FREEPDB1'
  831. python scripts/certify_dialect.py 'mssql+pyodbc://user:pw@host/db?driver=ODBC+Driver+18+for+SQL+Server'
  832. python scripts/certify_dialect.py 'mysql+pymysql://user:pw@host/db'
  833. ```
  834. It creates three `schemagate_cert_` tables, reflects them, runs selection and
  835. identity scoping end to end, drops them again, and exits non-zero if anything
  836. failed. Point it at a scratch schema.
  837. | | |
  838. |---|---|
  839. | SQLite | certified, 10/10, in CI |
  840. | PostgreSQL | certified, 10/10 on PostgreSQL 16, plus the full 260-object suite |
  841. | Oracle | certified live on Oracle AI Database 26ai (Autonomous Database), Sep 2026: certify script 10/10, the native `VECTOR(512, FLOAT32)` store conformance suite, and the dialect suite. Also stress-tested against a 127-object, 3-domain schema with ~7M rows |
  842. | SQL Server | certified live on SQL Server 2022 (16.0.4295.3), 10/10, **in CI on every push** against a service container, plus 13 live dialect tests covering alias types, `hierarchyid`/`sql_variant`, and `max_length` being bytes |
  843. | MySQL / MariaDB | certified live on MySQL 8.4.11, 10/10, **in CI on every push** against a service container, plus the GRANT reader suite: all three privilege levels, the role graph, and the role-only blind spot MySQL cannot report. MariaDB has not been run |
  844. | DuckDB | 9/10 locally, Oct 2026, on DuckDB 2.0.0 through `duckdb-engine` 0.17.0 (`pip install duckdb-engine`, URL `duckdb:///path/to/file.duckdb`). Tables, columns, foreign keys, selection and identity scoping pass; primary keys are not reported by the driver. Needs SQLAlchemy 2.0.44 or older: from 2.0.45, `duckdb-engine` cannot reflect columns at all ([duckdb_engine#1379](https://github.com/Mause/duckdb_engine/issues/1379)). Not in CI |
  845. Every row above has a real database behind it. The one thing still worth
  846. saying plainly: MariaDB is inferred from MySQL rather than run. Point the
  847. script at one and tell me what happens. DuckDB has no `SERIAL` type, so its
  848. run created the three test tables with `autoincrement=False` on their keys
  849. (the suite inserts no rows).
  850. The same checks run under pytest if you export a URL, which is how CI certifies
  851. a dialect for good:
  852. ```bash
  853. export SCHEMAGATE_POSTGRES_URL='postgresql+psycopg://…'
  854. export SCHEMAGATE_ORACLE_URL='oracle+oracledb://…'
  855. export SCHEMAGATE_MSSQL_URL='mssql+pyodbc://…'
  856. export SCHEMAGATE_MYSQL_URL='mysql+pymysql://…'
  857. pytest tests/test_dialects.py -v
  858. ```
  859. ## Using it from an agent
  860. > **The MCP server trusts the identity it is handed.** `principal` and `roles`
  861. > come from the client and are not authenticated -- there is no token and no
  862. > session. Anyone who can reach the transport can claim a role and read what
  863. > that role may read, and since `run_query` returns rows, that is data, not
  864. > just schema. Run it over stdio (the caller is your own desktop client), or
  865. > over HTTP behind something that authenticates the user and sets the
  866. > principal for them. It is a scoping mechanism, not a lock. With a
  867. > `groups` block in `SCHEMAGATE_CATALOG_CONFIG` the *roles* stop being the
  868. > client's to claim — they come from the directory or the database and the
  869. > ones in the request are ignored ([docs/groups.md](docs/groups.md)); the
  870. > subject is still whatever the transport hands over.
  871. >
  872. > **Or turn on OAuth and it is a lock.** Set `SCHEMAGATE_MCP_OAUTH_ISSUER`
  873. > and `SCHEMAGATE_MCP_OAUTH_AUDIENCE`, and over HTTP every request needs a
  874. > token signed by your identity provider (Entra ID, Okta, Auth0, Keycloak).
  875. > Who is asking and which roles they hold come from that token, never from
  876. > the client. No gateway needed ([docs/oauth.md](docs/oauth.md)).
  877. If you already have an agent that writes SQL, the fastest way in is to let it
  878. call schemagate as a tool rather than wiring the library into your code.
  879. **MCP.** Cursor, Windsurf, Zed, or anything else that speaks the
  880. Model Context Protocol:
  881. <!-- mcp-name: io.github.ashishsinha1602/schemagate -->
  882. One line, nothing to install first (needs [uv](https://docs.astral.sh/uv/)); `demo` is a
  883. bundled sample schema, so this works before you point it at anything:
  884. ```bash
  885. uvx --from "schemagate[mcp]" schemagate-mcp --url demo
  886. ```
  887. Claude Code:
  888. ```bash
  889. claude mcp add schemagate -- uvx --from "schemagate[mcp]" schemagate-mcp --url demo
  890. ```
  891. Any other client (Claude Desktop, Cursor, Windsurf, Zed), with your database. Add
  892. your database's driver extra next to `mcp`: `postgres`, `mysql`, `mssql` or `oracle`
  893. (SQLite needs none):
  894. ```json
  895. {"mcpServers": {"schemagate": {
  896. "command": "uvx", "args": ["--from", "schemagate[mcp,postgres]", "schemagate-mcp"],
  897. "env": {"SCHEMAGATE_DATABASE_URL": "postgresql+psycopg://user:password@localhost/app"}}}}
  898. ```
  899. Keep a URL with a password in `env`, as above, not in `--url`: command arguments are
  900. visible to every process on the machine. Without uv: `pip install 'schemagate[mcp,postgres]'`,
  901. then run `schemagate-mcp` (or `python -m schemagate.mcp_server`, which still works).
  902. Nine tools, all read-only: `select_schema` (the DDL for a question, scoped to
  903. the caller), `list_objects` (what this caller can see), `describe_object` (one
  904. object's full DDL), `run_query` (runs a single read, refused if it names
  905. anything the caller may not see), `semantic_query` (a read whose rows the
  906. server's model then filters or annotates by what a text column says),
  907. `answer`, `deep_answer` (like `answer`, but may explore in steps),
  908. `refresh_catalog` and `health`.
  909. They take `principal` and `roles`. If the client leaves them out, the caller
  910. is anonymous and sees only unrestricted objects. A restricted object and a
  911. missing one return the same error, so existence doesn't leak.
  912. **Every decision is recorded.** Each call to those tools, and to `run_query`
  913. and `answer`, writes one line: when, which principal with which roles, what
  914. they asked, what they were shown, how many objects and columns were held
  915. back, what SQL ran and how many rows came back, and whether the call was
  916. refused and why. Never row data, never the names of what was withheld, never
  917. the database URL. It stays in memory (the last 500, counted in `health`)
  918. unless `SCHEMAGATE_AUDIT_LOG=<path>` — or `=1` for `~/.schemagate/audit.jsonl`
  919. — turns the file on; a tool whose pitch is that it stores nothing does not
  920. start writing files on its own. The log is for the operator, from the file;
  921. it is deliberately not a tool, because "recent decisions" handed to any
  922. client is every caller's questions handed to every other caller.
  923. **And the record can be proven, years later.** Turn on signing and every
  924. record is chained to the one before it and signed with **ML-DSA-65, the NIST
  925. post-quantum signature standard (FIPS 204)**: editing a record, deleting one,
  926. inserting one or reordering them is caught by `schemagate audit verify`, which
  927. names the line and the reason. Post-quantum because audit logs are kept for
  928. years, long enough that today's RSA and ECDSA signatures may be forgeable by
  929. then. It answers the auditor's question "can you prove the model was never
  930. shown the salary table for this user?" with a check anyone holding the public
  931. key can run.
  932. ```bash
  933. pip install "schemagate[pq]" # PyCA cryptography >= 48 (OpenSSL)
  934. schemagate audit keygen --out ./keys # private key stays with the server
  935. SCHEMAGATE_AUDIT_LOG=1 SCHEMAGATE_AUDIT_SIGNING_KEY=./keys/audit-signing-key.pem \
  936. python -m schemagate.mcp_server
  937. schemagate audit verify ~/.schemagate/audit.jsonl --public-key ./keys/audit-signing-key.pub.pem
  938. # OK 1204 signed records (ML-DSA-65), seq 0..1203, no edits, gaps or reordering
  939. schemagate audit head ~/.schemagate/audit.jsonl # keep this SEQ:HASH elsewhere
  940. ```
  941. What it does not claim: someone who can write the file can still delete the
  942. *newest* records, because a shorter chain is still a valid chain. Keep the
  943. `head` somewhere else (a ticket, another system) and `verify --head SEQ:HASH`
  944. fails if the log no longer reaches it. Signing does not encrypt: the log is as
  945. readable as before, and still holds no rows and no names of what was withheld.
  946. It proves what schemagate decided, which is only as right as the roles it was
  947. given. One server process writes one log; a configured key that cannot be
  948. loaded stops the server at start-up rather than writing unsigned evidence.
  949. **It learns from SQL that ran.** When `answer` produces a query that
  950. executes, the question and the query are remembered -- never the rows. The
  951. next similar question gets the tables that query read pinned into its
  952. selection, and the pair shown to the model as a worked example between the
  953. DDL and the question. Neither can widen what a caller sees: a pin goes
  954. through the same visibility gate as any pin, and an example is shown only
  955. when every table it names is visible to that caller. Every stored query is
  956. re-checked read-only on the way in and the way out. Memory-only unless
  957. `SCHEMAGATE_MEMORY=<path>` (or `=1` for `~/.schemagate/memory/<db>.jsonl`);
  958. with nothing remembered the prompt is byte-identical to the one before this
  959. existed. The CLI has `--memory`, the Studio uses it at connect. Similarity is
  960. the catalog's own embedder -- deterministic, offline, and a weak notion of
  961. "similar": it matches wording, not meaning, which is acceptable because the
  962. examples are advisory and the pins are gated.
  963. `SCHEMAGATE_DATABASE_URL=demo` serves the bundled schema.
  964. `SCHEMAGATE_VALUES=1` is the server's `--values`: it reads the distinct values of
  965. short, non-personal columns at startup so the model writes `'CARD'`, not `'Card'`.
  966. It reads rows, so it stays off unless set; `SCHEMAGATE_VALUES_BUDGET` caps it in
  967. seconds (default 30 -- raise it for a large schema behind a wallet).
  968. To host it for a team rather than one desktop:
  969. ```bash
  970. SCHEMAGATE_MCP_TRANSPORT=streamable-http SCHEMAGATE_MCP_PORT=8765 python -m schemagate.mcp_server
  971. ```
  972. and put your identity provider in front of it, so each person's own token
  973. decides what they see:
  974. ```bash
  975. SCHEMAGATE_MCP_OAUTH_ISSUER=https://login.microsoftonline.com/<tenant>/v2.0 \
  976. SCHEMAGATE_MCP_OAUTH_AUDIENCE=api://schemagate \
  977. SCHEMAGATE_MCP_OAUTH_RESOURCE=https://schemagate.example.com/mcp \
  978. SCHEMAGATE_MCP_TRANSPORT=streamable-http SCHEMAGATE_MCP_HOST=0.0.0.0 python -m schemagate.mcp_server
  979. ```
  980. It's built not to die. The index lives in memory after startup, so the
  981. database going away does not take the server with it — `select_schema` keeps
  982. answering from the last good reflection, and `refresh_catalog` reports the
  983. failure instead of raising.
  984. **How old may the permissions be?** The server reads them when it builds its
  985. catalogue: restrictions from the config file and, with
  986. `SCHEMAGATE_RESTRICT_FROM_GRANTS=1`, the database's own GRANTs. By default that
  987. catalogue is used until `refresh_catalog` is called, so a REVOKE in the
  988. database reaches the server only then. Set `SCHEMAGATE_CATALOG_MAX_AGE=300` and
  989. a catalogue older than five minutes is re-read before it is used: a GRANT or a
  990. REVOKE takes effect **within** that window, not on the next call. If the
  991. re-read fails, the server does not keep answering from the old permissions:
  992. `select_schema`, `list_objects`, `describe_object`, `run_query` and `answer`
  993. refuse with "permissions are stale" until a re-read succeeds (retried at most
  994. every 5 seconds), and `health` shows `catalog_stale` and the reason. A re-read
  995. that neither fails nor returns, a database that took the connection and went
  996. quiet, is bounded too: callers wait at most `SCHEMAGATE_REREAD_TIMEOUT` seconds
  997. (default 30) and are then refused the same way, while the re-read runs on; if it
  998. returns, the next call is served from what it brought. Because callers are
  999. bounded, their latency won't show a re-read that never finishes; `health` does,
  1000. in `reread_running_seconds`, which you can alert on. Caller
  1001. roles from a `groups` resolver have their own bound, its `ttl`. Every tool catches everything and returns
  1002. `{"error": ...}`; a bad request cannot end the session for other clients.
  1003. `health` tells a load balancer what state it's in. A test throws 125 kinds of
  1004. garbage at every tool and then checks the next good request still works, and
  1005. another does the same through a real client over stdio. Works on MCP SDK 1.x
  1006. and 2.x; the 2.0 rename broke a fresh install once and there's a shim and a
  1007. test for it now.
  1008. **LangChain.** A proper `BaseRetriever`, so it composes:
  1009. ```bash
  1010. pip install 'schemagate[langchain]'
  1011. ```
  1012. ```python
  1013. from schemagate.integrations.langchain import SchemagateRetriever, prompt_fragment
  1014. retriever = SchemagateRetriever(catalog=cat, top_k=6,
  1015. principal=Principal("okta:jdoe", roles={"finance"}))
  1016. chain = retriever | RunnableLambda(prompt_fragment) | your_sql_prompt | llm
  1017. ```
  1018. The principal is bound at construction on purpose. Build one retriever per
  1019. caller; a chain can't forget to pass identity if the retriever already has it.
  1020. **LlamaIndex.** The same retriever as a LlamaIndex `BaseRetriever`:
  1021. ```bash
  1022. pip install 'schemagate[llamaindex]'
  1023. ```
  1024. ```python
  1025. from schemagate.integrations.llamaindex import SchemagateRetriever, prompt_fragment
  1026. retriever = SchemagateRetriever(cat, top_k=6, principal=Principal("okta:jdoe", roles={"finance"}))
  1027. ddl = prompt_fragment(retriever.retrieve("revenue by month"))
  1028. ```
  1029. See [docs/llamaindex.md](docs/llamaindex.md).
  1030. **Pydantic AI.** A capability that gives the agent one tool, `select_schema(question)`. The tool takes a question and
  1031. nothing else, so the model can't choose who it's asking as:
  1032. ```bash
  1033. pip install 'schemagate[pydantic-ai]'
  1034. ```
  1035. ```python
  1036. from schemagate.integrations.pydantic_ai import SchemagateCapability
  1037. agent = Agent("anthropic:claude-haiku-4-5", capabilities=[
  1038. SchemagateCapability(cat, principal=lambda ctx: ctx.deps.user)])
  1039. ```
  1040. See [docs/pydantic-ai.md](docs/pydantic-ai.md).
  1041. **jev4pg.** `schemagate.integrations.jev4pg.ScopedCatalog` wraps
  1042. [jev4pg](https://github.com/Sheltercosmo/jev4pg)'s per-tenant catalogue so its
  1043. planner, SQL validator and semantic operators see only what each caller may
  1044. read. See [docs/jev4pg.md](docs/jev4pg.md).
  1045. **Questions about what text says.** `semantic_query` (MCP, off by default) runs
  1046. your SQL under the same scope check, then asks the model which rows' text meets
  1047. a criterion, or for one short answer per row. See
  1048. [docs/semantic-operators.md](docs/semantic-operators.md).
  1049. **Looking at the data before answering.** `deep_answer` (MCP, off by default)
  1050. lets the model run small exploring reads, for example to find that the column
  1051. says `Praha`, not `Prague`, before it writes the final query. Every step goes
  1052. through the same scope check and audit as `run_query`. In the default
  1053. adaptive mode it explores only when the plain query fails, comes back empty
  1054. or returns only zeros. On 220 BIRD questions with Gemini 2.5 Flash, plain
  1055. scored 36.8 and adaptive 54.5. See [docs/deep-answer.md](docs/deep-answer.md).
  1056. **Business rules.** "Fiscal year starts 1 July", "leave out the test tenant":
  1057. knowledge the schema doesn't carry. Write it in a Markdown file or in Studio's
  1058. *Business rules* box. Each rule can be tied to tables and kept from roles, and
  1059. only the relevant ones reach the prompt. See
  1060. [docs/business-rules.md](docs/business-rules.md).
  1061. ## On Oracle Cloud
  1062. Certified live on Oracle AI Database 26ai. Two ways in, neither of which needs
  1063. an API key — cataloguing runs on OCI Generative AI under your own OCI identity,
  1064. so the prompts (schema metadata only, never rows) stay in your tenancy.
  1065. **From Cloud Shell, about a minute, no VM:**
  1066. ```bash
  1067. pip install --user 'schemagate[oracle,oci]'
  1068. schemagate describe --url 'oracle+oracledb://@' --provider oci \
  1069. --model google.gemini-2.5-pro --config catalog.json
  1070. ```
  1071. **Or one click, for an MCP endpoint that stays up for your team:**
  1072. [![Deploy to Oracle Cloud](https://oci-resourcemanager-plugin.plugins.oci.oraclecloud.com/latest/deploy-to-oracle-cloud.svg)](https://cloud.oracle.com/resourcemanager/stacks/create?zipUrl=https://github.com/ashishsinha1602/schemagate/releases/latest/download/schemagate-oci-stack.zip)
  1073. That opens Resource Manager in your own tenancy with the stack loaded — an
  1074. Always-Free-eligible VM running the MCP server against an Autonomous Database
  1075. it creates, or one you already have. Details and the Terraform: [`oci/`](oci/).
  1076. ### OCI Generative AI from your own machine
  1077. Everything runs under your OCI identity and is billed to your tenancy. Nothing
  1078. is installed in the database — no Select AI profile, no grants beyond `SELECT`.
  1079. ```bash
  1080. pip install 'schemagate[oracle,oci,mcp]'
  1081. ```
  1082. On Windows the OCI SDK has very long file paths: install into a short path
  1083. (`python -m venv C:\sg`) or enable long paths, or pip fails part-way.
  1084. **Sign-in** is the usual OCI one: `~/.oci/config` from an API key (Console ›
  1085. Profile › API keys), or a resource / instance principal when running inside OCI.
  1086. **Permission** is one policy, for your group — no dynamic group, nothing on the
  1087. database:
  1088. ```
  1089. allow group <your-group> to use generative-ai-family in compartment <compartment>
  1090. ```
  1091. | variable | |
  1092. |---|---|
  1093. | `OCI_COMPARTMENT_ID` | turns OCI Generative AI on (it is the provider picked when no other key is set) |
  1094. | `OCI_REGION` | the Generative AI region, e.g. `us-chicago-1`, `us-phoenix-1` |
  1095. | `SCHEMAGATE_DESCRIBE_MODEL` | the model for automatic descriptions. **Required for OCI** — there is no default, because model ids differ by region |
  1096. **Pick a model your region serves on demand.** The catalogue in a region lists
  1097. models that are dedicated-cluster only; calling one fails with
  1098. `404 Entity with key <model> not found`. Choose another (in `us-phoenix-1`,
  1099. September 2026, `google.gemini-2.5-flash` was on demand; several listed Llama
  1100. and Cohere models were not). The MCP server still starts when the model is
  1101. wrong — it skips the descriptions — so check its log.
  1102. **An MCP server on an Autonomous Database**, with values and descriptions:
  1103. ```json
  1104. {"mcpServers": {"schemagate": {
  1105. "command": "python", "args": ["-m", "schemagate.mcp_server"],
  1106. "env": {
  1107. "SCHEMAGATE_DATABASE_URL": "oracle+oracledb://@",
  1108. "SCHEMAGATE_CONNECT_ARGS": "{\"config_dir\": \"C:/wallet\", \"wallet_location\": \"C:/wallet\", \"wallet_password\": \"...\", \"user\": \"APP\", \"password\": \"...\", \"dsn\": \"mydb_high\"}",
  1109. "SCHEMAGATE_VALUES": "1",
  1110. "SCHEMAGATE_VALUES_BUDGET": "120",
  1111. "OCI_COMPARTMENT_ID": "ocid1.compartment.oc1..…",
  1112. "OCI_REGION": "us-phoenix-1",
  1113. "SCHEMAGATE_DESCRIBE_MODEL": "google.gemini-2.5-flash"
  1114. }}}}
  1115. ```
  1116. `SCHEMAGATE_VALUES_BUDGET` is seconds; the default 30 can cut value reading short
  1117. on a large schema behind a wallet. Oracle-maintained schemas — including the
  1118. `SH` and `SSB` samples every Autonomous Database ships — are left out, so the
  1119. catalog is the schemas you own or were granted. From the CLI, `--answer
  1120. --provider oci --model <id>` writes the SQL with the same model and runs it
  1121. read-only over the same connection.
  1122. ## Keeping the index in Oracle
  1123. `MemoryStore` rebuilds on every process start. Fine for a few hundred objects,
  1124. wrong for a long-lived service. `OracleStore` keeps vectors in Oracle 23ai's
  1125. native `VECTOR` type so the nearest-neighbour search runs in the database:
  1126. ```python
  1127. from schemagate.stores.oracle import OracleStore
  1128. store = OracleStore(dsn="user/pw@host:1521/FREEPDB1", dim=512)
  1129. store.create_schema() # idempotent
  1130. cat = Catalog(store=store).bootstrap("oracle+oracledb://…")
  1131. ```
  1132. Pass `connection=` instead of `dsn=` to reuse your app's pool. It won't close a
  1133. connection it didn't open.
  1134. Scoping is a predicate inside the scored subquery, not a filter applied after
  1135. the rows come back. A row the caller can't see is never ranked and never leaves
  1136. the database.
  1137. Same caveat as above: 26 tests pin the SQL, the bind types and the scope
  1138. predicate, and every statement is checked against an independent Oracle parser,
  1139. but none of it has run against a live 23ai instance yet. To do that:
  1140. ```bash
  1141. export SCHEMAGATE_ORACLE_DSN='user/password@host:1521/FREEPDB1'
  1142. pytest tests/test_store_conformance.py -v
  1143. ```
  1144. Oracle Cloud's Always Free ATP is enough.
  1145. ## Things that will bite you
  1146. **Archive and staging twins are handled, but know how.** If your warehouse has
  1147. `orders`, `orders_bkp` and `stg_orders`, the copies carry the same name words in
  1148. a shorter document, and cosine similarity likes short documents. Left alone,
  1149. a three-column `_tmp` copy beats the twenty-five-column table it was copied
  1150. from, even with a hint on the real one — I watched it happen. So an object
  1151. whose name is a real object's name plus `_bkp`, `_old`, `_tmp`, `_v2`,
  1152. `_archive` and so on, or `stg_`/`tmp_` in front, is ranked below the object it
  1153. shadows. Only when that object exists: a lone `pricing_v2` with no `pricing`
  1154. is left alone. Only in the same schema. And never when you name the copy
  1155. outright — asking for `fact_claim_line_v2` gets you `fact_claim_line_v2`. The
  1156. lists are `DEFAULT_SHADOW_SUFFIXES` and `DEFAULT_SHADOW_PREFIXES`; pass your
  1157. own to `Catalog(...)`, or empty tuples to switch it off. `cat.shadows()` shows
  1158. what was detected.
  1159. **Identifier length.** PostgreSQL truncates names to 63 bytes at creation.
  1160. That's the database doing it, not schemagate, and there's nothing to be done from
  1161. this side.
  1162. **Non-English schemas** work, including Chinese, Japanese and Korean, and
  1163. accents fold both ways so a search for `facturacion` finds `facturación`. But a
  1164. question in English will not find a table named in Spanish. Nothing lexical can
  1165. bridge that. Descriptions can.
  1166. **`top_k` is not a hard cap.** Foreign-key expansion runs after selection and
  1167. adds join tables on top. That's deliberate — SQL that references a table you
  1168. didn't include won't run — but size your prompt budget for it.
  1169. ## Status
  1170. v1.0. The public API is stable.
  1171. The names this README documents -- `Catalog` and its methods, `Principal`,
  1172. `Selection`, the `schemagate` CLI and its flags, the MCP tool names, the
  1173. catalog config file and the LangChain retriever -- keep working without a
  1174. breaking change until 2.0. Anything underscore-prefixed is internal and may
  1175. move in any release.
  1176. Two things are deliberately not covered by that promise. The rows below marked
  1177. unfinished, until they say done. And the *ranking*: retrieval is tuned release
  1178. to release, so the set of tables a question returns can change between 1.x
  1179. versions. What does not change is the part that matters -- a table the caller
  1180. may not read is never in it.
  1181. | | |
  1182. |---|---|
  1183. | Reflection | certified on SQLite, PostgreSQL 16, Oracle 26ai, MySQL 8.4 and SQL Server 2022 |
  1184. | `MemoryStore` | done |
  1185. | AI cataloging | done, tested offline against fake providers |
  1186. | CLI | done |
  1187. | Studio (`schemagate studio`, and the hosted demo) | done, driven by a real browser in tests |
  1188. | MCP server | done, tested through a real MCP client |
  1189. | LangChain retriever | done, tested against langchain-core |
  1190. | LlamaIndex retriever | done, tested against llama-index-core |
  1191. | Pydantic AI capability | done, tested against pydantic-ai-slim |
  1192. | `OracleStore` | written and statically verified, needs a live run |
  1193. | pgvector store | not started |
  1194. `import schemagate` never imports any provider SDK, and there's a test asserting it.
  1195. Default embeddings are stable across processes, machines and Python versions,
  1196. so cached or persisted vectors stay valid. That one is enforced by a test that
  1197. runs the embedder in fresh subprocesses under different `PYTHONHASHSEED`
  1198. values, because it was broken once and nothing else caught it.
  1199. Apache-2.0. Ashish Sinha.
  1200. Patent pending: US provisional application 64/183,987, filed 25 October 2026. Use of this code is
  1201. licensed under Apache-2.0, including the patent licence in its section 3.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.