ai-ready-warehouse
# 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.
```bash
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 everything
```
Needs Node 22.13 or newer. There are no dependencies, so there's nothing to `npm install`.
---
## Contents
1. [Why build your own](#why-build-your-own)
2. [How it fits together](#how-it-fits-together)
3. [What happens when you ask a question](#what-happens-when-you-ask-a-question)
4. [Build yours, step by step](#build-yours-step-by-step)
5. [Add a tool](#add-a-tool)
6. [Connect to your warehouse](#connect-to-your-warehouse)
7. [Connect to Claude](#connect-to-claude)
8. [Keep the AI's context fresh with dbt](#keep-the-ais-context-fresh-with-dbt)
9. [Security](#security)
10. [Repo layout](#repo-layout) · [Tested](#tested)
---
## 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
```mermaid
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"| DB
```
- **Protocol** (`server/index.js`): MCP is JSON-RPC 2.0, one message per line on stdin/stdout. The server answers `initialize`, `tools/list` and `tools/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 call `ctx.db.run(sql)`, `ctx.db.listTables()` and `ctx.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
```mermaid
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 used
```
Run `npm run demo` to watch these exact steps against the demo data.
## Build yours, step by step
```mermaid
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`](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.
```bash
npm run new-tool -- active_customers_by_plan
```
That creates `server/tools/active_customers_by_plan.js` from [`templates/tool.js`](templates/tool.js). It works against the demo data as-is. Edit four things:
```js
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_month` beats `get_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.log` breaks the connection.
## Connect to your warehouse
| `WAREHOUSE` | Needs | Settings |
|---|---|---|
| `sqlite` (default) | nothing | demo data from `demo/seed.sql` |
| `redshift` | the `aws` CLI, signed in | `REDSHIFT_DATABASE`, plus `REDSHIFT_CLUSTER` + `REDSHIFT_DB_USER` (or `REDSHIFT_WORKGROUP` for Serverless), `AWS_REGION` |
| `postgres` | `psql` | `DATABASE_URL` or the `PG*` variables |
Shared: `AI_SCHEMA` (default `ai_ready`), `MAX_ROWS` (default `5000`).
```bash
WAREHOUSE=postgres DATABASE_URL=postgres://ai_reader@localhost:5432/analytics npm run inspect -- tools
```
**Another 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:**
```bash
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.js
```
**Claude Desktop:** add this to `claude_desktop_config.json` (Settings → Developer → Edit Config), then restart the app:
```json
{
"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:
```mermaid
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"]
```
1. Copy [`dbt/macros/publish_ai_ready.sql`](dbt/macros/publish_ai_ready.sql) into your project's `macros/`.
2. Add [`dbt/dbt_project.snippet.yml`](dbt/dbt_project.snippet.yml) to `dbt_project.yml`.
3. On your next prod run, every model under `models/mart/` lands in `ai_ready` with its descriptions. Dev and CI runs never publish. Opt a model or folder out with `meta: {ai_ready_publish: false}`.
## Security
[`docs/SECURITY.md`](docs/SECURITY.md) has the checklist. The short version:
- **The database is the boundary.** Give the server its own user with `SELECT` on the AI schema only (`dbt/grants.sql` for Redshift, `dbt/grants.postgres.sql` for Postgres), and no `CREATE` anywhere.
- **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 Code
```
## Tested
- **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" outside `ai_ready`).
- **Not yet tested:** Redshift Serverless, and `grants.sql` on Redshift.
## License
MIT
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.