ai-ready-warehouse
Integrates with dbt projects through macros and project configuration to publish mart models and their descriptions into an ai_ready schema on production runs, keeping the AI's table and column context fresh.
Includes a built-in SQLite adapter seeded with demo data, allowing read-only exploration of tables, schema descriptions, and queries without external warehouse credentials.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@ai-ready-warehouse How many active customers are on each plan?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
ai-ready-warehouse
Build your own MCP server for your data warehouse. It's small, read-only, and you own every line.
Clone it and it runs immediately on built-in demo data, with no install, no warehouse and no credentials. Add a business tool with one command. Point it at Redshift or Postgres when you're ready.
git clone https://github.com/KNMDeep/ai-ready-warehouse
cd ai-ready-warehouse
npm run demo # a guided tour: what the model sees, step by step
npm test # starts the real server and checks everythingNeeds Node 22.13 or newer. There are no dependencies, so there's nothing to npm install.
Contents
Related MCP server: mcp-sql-agent
Why build your own
Generic MCP connectors | This | |
Tools | often many, built to do everything | 3 core tools plus the ones you add |
Reach | whatever the credentials allow | one schema, enforced by the reader's database grants |
Writes | depends on the credentials | blocked: every query runs in a read-only transaction |
Business context | column names and types | your descriptions: what each column means |
Your definitions | the model re-derives "revenue" every time | curated tools answer the same way every time |
The three core tool definitions are about 0.8 KB (~200 tokens). The model finds tables when it needs them, instead of carrying the whole warehouse in its context.
How it fits together
flowchart LR
CLIENT["Claude<br/>(Desktop, Code, or any MCP client)"]
subgraph SERVER["MCP server: this repo"]
direction TB
PROTO["server/index.js<br/>JSON-RPC over stdio"]
TOOLS["server/tools/*.js<br/>list_tables · describe_table · query<br/>+ your curated tools"]
GUARDS["server/lib/guards.js<br/>input checks"]
ADAPTERS["server/adapters/*.js<br/>sqlite demo · redshift · postgres"]
PROTO --> TOOLS
TOOLS --> GUARDS
TOOLS --> ADAPTERS
end
DB[("ai_ready schema<br/>curated tables + descriptions")]
CLIENT <-->|"tools/list, tools/call"| PROTO
ADAPTERS -->|"read-only user<br/>read-only transaction"| DBProtocol (
server/index.js): MCP is JSON-RPC 2.0, one message per line on stdin/stdout. The server answersinitialize,tools/listandtools/call. That's all a tools server needs.Tools (
server/tools/): one file per tool, loaded automatically. Add a file and you've added a tool.Adapters (
server/adapters/): one file per warehouse. Tools never contain warehouse-specific code; they callctx.db.run(sql),ctx.db.listTables()andctx.db.describeTable().Guards (
server/lib/guards.js): input checks that give the model clear errors. They're a convenience. The database is the boundary.
What happens when you ask a question
sequenceDiagram
autonumber
actor You
participant Claude as Claude (MCP client)
participant Server as MCP server
participant DB as Warehouse
Claude->>Server: initialize
Server-->>Claude: protocol version, "I have tools"
Claude->>Server: tools/list
Server-->>Claude: 3 tools: name, description, input schema
You->>Claude: How many active customers on each plan?
Claude->>Server: tools/call list_tables
Server->>DB: catalog query
DB-->>Server: tables + descriptions
Server-->>Claude: customers, invoices
Claude->>Server: tools/call describe_table(customers)
Server-->>Claude: columns + "Active means is_active = 1"
Claude->>Server: tools/call query(SELECT ...)
Server->>DB: BEGIN READ ONLY, SELECT ...
DB-->>Server: rows
Server-->>Claude: rows (capped at MAX_ROWS)
Claude-->>You: the answer, and the SQL it usedRun npm run demo to watch these exact steps against the demo data.
Build yours, step by step
flowchart TD
A["1. Clone"] --> B["2. npm run demo<br/>see what the model sees"]
B --> C["3. npm test<br/>the real server, end to end"]
C --> D{"4. A question people<br/>ask every week?"}
D -->|yes| E["npm run new-tool -- my_tool<br/>edit description, inputs, SQL"]
E --> C
D -->|no| F["5. Point it at your warehouse<br/>WAREHOUSE=redshift or postgres"]
F --> G["6. Give it its own read-only user<br/>dbt/grants*.sql"]
G --> H["7. Connect Claude<br/>.mcp.json, claude mcp add, or Desktop config"]
H --> I["8. Optional: publish marts on every merge<br/>dbt/macros/publish_ai_ready.sql"]The full walkthrough, with the reasoning behind each step, is in docs/GUIDE.md.
Add a tool
The three core tools let the model explore. Curated tools pin down the numbers people argue about (active customers, revenue, churn), so they're answered one reviewed way every time.
npm run new-tool -- active_customers_by_planThat creates server/tools/active_customers_by_plan.js from templates/tool.js. It works against the demo data as-is. Edit four things:
module.exports = {
name: 'active_customers_by_plan',
// The model picks tools by reading this. Say WHEN to use it, in business words.
description: ({ schema }) => 'Active customers, grouped by plan. Use this for any question about how many customers we have...',
// Inputs as narrow as possible: enums and dates, not free text.
inputSchema: { type: 'object', properties: { region: { type: 'string', enum: ['NA', 'EMEA', 'APAC'] } } },
example: { region: 'EMEA' }, // npm test calls the tool with this
async handler(args, ctx) { // ctx = { db, guards, schema, maxRows }
const region = ctx.guards.oneOf(args.region, ['NA', 'EMEA', 'APAC'], 'region');
return ctx.db.run(`select plan, count(*) ... where region = ${ctx.guards.literal(region)} ...`);
}
};Then run npm test. Every tool in server/tools/ is loaded, checked, and called with its example, so a broken tool fails the tests rather than a conversation. Call it yourself with npm run inspect -- active_customers_by_plan '{"region":"NA"}'.
Rules that keep tools good:
One question per tool.
revenue_by_monthbeatsget_data.Validate every input with
ctx.guards(oneOf,isoDate,int,ident) before it touches SQL.Return rows, not prose. The model writes the prose.
Log to stderr only. stdout carries the protocol, and one stray
console.logbreaks the connection.
Connect to your warehouse
| Needs | Settings |
| nothing | demo data from |
| the |
|
|
|
|
Shared: AI_SCHEMA (default ai_ready), MAX_ROWS (default 5000).
WAREHOUSE=postgres DATABASE_URL=postgres://ai_reader@localhost:5432/analytics npm run inspect -- toolsAnother warehouse? Copy server/adapters/postgres.js to server/adapters/<name>.js and implement run(sql), listTables(filter) and describeTable(table). Each returns { columns, rows, truncated }. Run every query inside a read-only transaction. Then use WAREHOUSE=<name>.
Connect to Claude
Claude Code, in this repo: .mcp.json registers the demo server as warehouse-demo. Open the folder in Claude Code and approve it when asked. Then try "How many active customers on each plan?"
Claude Code, against your warehouse:
claude mcp add warehouse -e WAREHOUSE=redshift -e REDSHIFT_CLUSTER=my-cluster -e REDSHIFT_DATABASE=analytics -e REDSHIFT_DB_USER=ai_reader -e AWS_REGION=us-east-1 -- node /absolute/path/to/ai-ready-warehouse/server/index.jsClaude Desktop: add this to claude_desktop_config.json (Settings → Developer → Edit Config), then restart the app:
{
"mcpServers": {
"warehouse": {
"command": "node",
"args": ["/absolute/path/to/ai-ready-warehouse/server/index.js"],
"env": { "WAREHOUSE": "postgres", "DATABASE_URL": "postgres://ai_reader@host:5432/analytics" }
}
}
}Sharing it with a team: this server runs locally over stdio, as whoever starts it. To host it for others, put it behind your identity provider (OAuth) so every query is logged with a person, and keep the database user as the boundary. Don't swap that boundary for a shared API key.
Keep the AI's context fresh with dbt
The server is only as good as the tables and descriptions behind it. If you use dbt, two pieces in dbt/ do that work automatically on every prod run:
flowchart LR
PR["PR merged<br/>models + docs + tests reviewed"] --> RUN["dbt prod run"]
RUN --> MART["models/mart/*"]
MART -->|"persist_docs"| DOCS["descriptions become<br/>table + column comments"]
MART -->|"post-hook publish_ai_ready<br/>build, then swap"| AI[("ai_ready schema")]
DOCS --> AI
AI --> MCP["describe_table returns<br/>your dbt descriptions"]Copy
dbt/macros/publish_ai_ready.sqlinto your project'smacros/.Add
dbt/dbt_project.snippet.ymltodbt_project.yml.On your next prod run, every model under
models/mart/lands inai_readywith its descriptions. Dev and CI runs never publish. Opt a model or folder out withmeta: {ai_ready_publish: false}.
Security
docs/SECURITY.md has the checklist. The short version:
The database is the boundary. Give the server its own user with
SELECTon the AI schema only (dbt/grants.sqlfor Redshift,dbt/grants.postgres.sqlfor Postgres), and noCREATEanywhere.Every query runs in a read-only transaction. The tests prove a write that gets past the tool's guard is still refused by the database.
Guards are convenience, not security. A string check in code can be talked around. A grant can't.
Repo layout
server/
index.js MCP protocol: JSON-RPC over stdio
tools/ one file per tool (auto-loaded)
adapters/ one file per warehouse: sqlite (demo), redshift, postgres
lib/guards.js input checks for tools
lib/tools.js tool loading + validation
templates/tool.js what `npm run new-tool` copies
scripts/ client.js (a tiny MCP client), inspect.js (demo + CLI), new-tool.js
demo/seed.sql the demo business: customers, invoices, descriptions
test/ end-to-end tests (node:test, no dependencies)
dbt/ publish macro, config snippet, reader grants
bench/ token_tax.py + the accuracy benchmark protocol
docs/ GUIDE.md, SECURITY.md
CLAUDE.md how to extend this repo with Claude Code
.mcp.json registers the demo server for Claude CodeTested
CI on every push (Node 22 and 24): the test suite, the demo tour, and the tool-definition size.
Redshift (provisioned cluster, Data API): all three tools, the row cap, and writes rejected by the database.
Postgres 16: the dbt macro (prod publishes, dev doesn't, re-runs swap cleanly, rename and opt-out work, descriptions carry over, misplaced calls fail at parse);
grants.postgres.sql; and the server as the restricted reader ("permission denied" outsideai_ready).Not yet tested: Redshift Serverless, and
grants.sqlon Redshift.
License
MIT
Available Tools
3 toolsdescribe_tableA
Columns, types and business descriptions for one ai_ready table. Read the descriptions before writing SQL against the table.
Inputs: table (required).
list_tablesA
List the tables in the ai_ready schema: curated, documented, production models. Returns each table with its description. Optional name filter.
Inputs: filter.
queryA
Run one read-only SELECT or WITH query. Prefix every table with ai_ready. (for example ai_ready.customers). Results are capped at 5000 rows, so aggregate or add LIMIT on large tables.
Inputs: sql (required).
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 3 tools
The three tools form a clean discovery-then-execute pipeline: list_tables finds tables, describe_table inspects a single table's columns, and query executes SQL. Each has a distinct scope with no functional overlap, so an agent can select unambiguously.
list_tables and describe_table follow a consistent verb_noun snake_case pattern, and query fits the short imperative style of the set. The only minor deviation is that query is a bare verb rather than verb_noun, but it is still predictable and readable.
Three tools is on the lean side but well matched to a read-only warehouse exploration surface — discovery, inspection, and execution each earn their place. A fourth tool (e.g. sample rows or schema listing) could add value, but nothing here is redundant.
The core read-only lifecycle — find tables, understand their columns, and run SELECT/WITH queries — is fully covered, and query acts as a universal escape hatch for anything not directly supported. Minor gaps like previewing sample rows or listing distinct schemas are workable via SQL, though write operations are intentionally absent.
Maintenance
Related MCP Connectors
Ask business questions in plain English. Get instant answers from your database, no SQL needed.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Ask your databases questions in plain English, run read-only SQL and set up data alerts.
Query your org's data in natural language — read-only MCP access to SQL, NoSQL, files & warehouses.
Related MCP Servers
- AlicenseAqualityCmaintenanceQuery SQL databases (SQLite, PostgreSQL, BigQuery, Databricks) in natural language through a business semantic layer — glossary, metrics, and a data dictionary grounded against your real schema. Read-only by default, with an embedded SQLite + sqlite-vec metadata store and no external infra required.253MIT
- AlicenseNot gradedqualityCmaintenanceGives an LLM read-only, schema-aware SQL database access so users can ask business questions in plain English and receive the exact result set, with malformed queries returned as errors for automatic retry and correction.MIT
- AlicenseNot gradedqualityCmaintenanceLets an AI assistant explore and query Postgres, DuckDB, or Snowflake with read-only SQL, table and schema discovery, sampling, and explainable guardrails.MIT
- AlicenseNot gradedqualityBmaintenanceGives AI agents governed context about a data warehouse — table meaning, ownership, lineage, freshness, and PII flags — plus read-only, row-limited, PII-protected querying across DuckDB, Snowflake, and Databricks. Guardrails enforced at parsing, connection, and result layers keep agents from writing, running away, or leaking personal data.86 PyPIMIT