Skip to main content
Glama
ai-ready-warehouse

ai-ready-warehouse

by KNMDeep

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 everything

Needs Node 22.13 or newer. There are no dependencies, so there's nothing to npm install.


Contents

  1. Why build your own

  2. How it fits together

  3. What happens when you ask a question

  4. Build yours, step by step

  5. Add a tool

  6. Connect to your warehouse

  7. Connect to Claude

  8. Keep the AI's context fresh with dbt

  9. Security

  10. Repo layout · Tested


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"| 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

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

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_plan

That 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_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).

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:

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:

{
  "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"]
  1. Copy dbt/macros/publish_ai_ready.sql into your project's macros/.

  2. Add 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 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

Available Tools

3 tools
describe_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

    A3.9/5.0

    Scored across 3 tools

    Disambiguation5/5

    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.

    Naming Consistency4/5

    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.

    Tool Count4/5

    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.

    Completeness4/5

    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

    ActivityMaintained
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      C
      maintenance
      Query 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.
      25
      3
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Gives 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
    • A
      license
      Not graded
      quality
      C
      maintenance
      Lets an AI assistant explore and query Postgres, DuckDB, or Snowflake with read-only SQL, table and schema discovery, sampling, and explainable guardrails.
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Gives 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 PyPI
      MIT