Snowflake Chat

A standalone, Claude-like chat app for your Snowflake account. You ask a question in plain English; Claude discovers the schema, writes and runs read-only SQL through a self-owned MCP server, and streams the answer back as GitHub-flavored markdown tables — reasoning, tool calls, and results all visible as they happen.

No Cortex. No separate API key or bill (it reuses your existing Claude Code login). The tool surface and the read-only safety gate are entirely under our control.

---

1. End-to-end architecture

flowchart TD
    UI["Browser UI<br/>React / Vite"]
    BE["FastAPI backend<br/>backend/app.py"]
    SC["Scope classifier<br/>Haiku · pre-flight"]
    CLI["Claude Code CLI<br/>model engine · your login"]
    MCP["server.py<br/>MCP server + read-only gate"]
    SF[("Snowflake<br/>AIRBNB / …")]

    UI -->|"1 · POST /api/chat (question)"| BE
    BE -->|"2 · classify ALLOW/BLOCK"| SC
    SC -.->|"BLOCK → canned reply (SSE)"| UI
    BE -->|"3 · ALLOW · drive via Agent SDK"| CLI
    CLI -->|"4 · stdio · MCP tool call"| MCP
    MCP -->|"5 · snowflake-connector"| SF
    SF -->|"6 · rows (JSON)"| MCP
    MCP -->|"7 · tool result"| CLI
    CLI -->|"8 · stream-json turn events"| BE
    BE -->|"9 · SSE: text / thinking / tool_use / tool_result / done"| UI

The path of a single question:

1. The React UI POSTs {prompt, session} to /api/chat and opens an SSE stream.
2. Scope pre-flight: backend/app.py first runs the prompt past a cheap, warm Haiku classifier that returns ALLOW/BLOCK in ~1s. If it's off-topic (not about the user's Snowflake data), the backend streams a canned reply and stops here — the expensive agent turn never runs. On error the classifier fails open (allows). See decision E.
3. On ALLOW, backend/app.py hands the prompt to the Claude Agent SDK, which drives the local Claude Code CLI as the model engine (using your machine's existing login).
4. Claude decides which Snowflake tool to call. The SDK launches server.py as a local MCP subprocess over stdio and forwards the tool call.
5. server.py runs the SQL through its permission gate (read-only by default), executes it via snowflake-connector-python, and returns rows as JSON.
6. The SDK streams every step back to the backend — thinking, tool_use, tool_result, and the final answer text — which the backend re-emits as SSE events.
7. The UI renders the stream live: a "thinking" line, tool chips, then the markdown table.

Everything runs locally. Your Snowflake credentials never leave server.py, and the question/answer traffic goes only to the Claude engine you already use for Claude Code.

---

2. Components

Component	File(s)	Responsibility
MCP server	server.py	Snowflake connection, statement classifier + permission gate, 7 read-only tools. Pure mcp SDK + snowflake-connector-python + sqlglot.
Backend / orchestrator	backend/app.py	FastAPI. Owns the Claude Agent SDK sessions, configures the MCP server inline, streams turns over SSE, serves the built frontend in prod.
Frontend	frontend/	Vite + React + TypeScript + Tailwind v4 + shadcn/ui. Health gate, SSE chat, markdown tables, tool chips, dark theme.
CLI tester	query.py	Run a query straight against server.py's logic without the full stack.
Agents	.claude/agents/*.md	Data-engineer + frontend-engineer subagent personas used during development.
Portable Node	.tools/node	Self-contained Node runtime for the frontend (no global install; moves with the repo).

The 7 MCP tools (mcp__snowflake__*)

• get_connection_info — account / user / role / warehouse / db / schema / version / mode
• execute_query(sql) — run one gated SQL statement, rows as JSON
• list_databases · list_schemas(database) · list_tables(schema, database)
• describe_table(table) — columns & types
• preview_table(table, limit) — sample rows (capped at 100)

SSE event contract (backend ↔ frontend)

• GET /api/health → { ready, checks:[{name, ok, detail}], message } (preflight gate)
• POST /api/chat {prompt, session} → SSE data: {json}\n\n, where type ∈ text | thinking | tool_use | tool_result | error | done
• POST /api/reset {session} → clears one conversation

---

3. Key decisions & why

A. Self-owned MCP server, no Cortex

Decision: Build our own MCP server on the open protocol + the official Snowflake connector, rather than use Cortex or a vendor MCP. Why: We control the exact tool surface and the safety gate, and we depend only on stable, documented interfaces. This is what makes the roadmap (DDL → RCA → self-healing) safe to grow into without re-platforming. Nothing built for A1 gets thrown away later.

B. Claude Agent SDK reusing the Claude Code login (not the raw API)

Decision: The backend drives the local Claude Code CLI via the Agent SDK instead of calling the Claude API with an API key. Why: Zero extra credentials and no separate bill — it runs on the login you already have. It also gives us the full agentic loop (multi-step tool use, thinking, streaming) for free, which is exactly the behavior a data-exploration chat needs.

C. Read-only permission gate, classified by first keyword

Decision: server.py classifies each statement by its leading keyword and, in the default read_only mode, permits only SELECT / SHOW / DESCRIBE / EXPLAIN / USE / VALUES / LIST / GET. It also rejects multi-statement calls and classifies WITH CTEs by their terminal statement. Why: Safety by construction — the model literally cannot mutate data or schema in the default mode; a blocked write returns an explanatory error instead of executing. Consequence (this is why a JOIN runs): the gate cares about what a statement does, not how complex it is. A JOIN, subquery, CTE, window function, or aggregation is still a SELECT — a read — so it's allowed. "Read-only" means no changes to data or schema, not "simple queries only." Only statements whose first keyword writes (INSERT, UPDATE, DELETE, MERGE, COPY, …) or changes schema (CREATE, ALTER, DROP, …) are rejected.

D. ENABLE_TOOL_SEARCH=0 — offer the Snowflake tools directly

Decision: The backend passes env={"ENABLE_TOOL_SEARCH": "0"} to the Agent SDK. Why: With tool search on, the CLI defers MCP tools behind a server-side ToolSearch tool — the model must discover a tool by exact name before it can call it. For a natural question ("what tables are in AIRBNB?") the model searched by keyword, found nothing, and wrongly reported the Snowflake tools as unavailable. Turning tool search off offers all 7 mcp__snowflake__* tools to the model directly, so it just calls them. (Note: setting tools=[] is not a substitute — it also removes ToolSearch itself, leaving no path to the still-deferred MCP tools.)

E. Scope guard — keep it on-topic (Snowflake only)

Decision: Two layers stop the app being used as a general chatbot.

(1) A SCOPE rule in the system prompt tells the model to decline anything unrelated to the user's Snowflake data.

(2) A pre-flight classifier — one warm, shared Haiku client — judges each prompt ALLOW/BLOCK in ~1s before the expensive agent turn runs; off-topic prompts get a canned reply and never reach the agent. The toolset is also locked to Snowflake only (via tools=[]; see decision F), so the model can't fetch external info even if asked. Why: The read-only gate only governs SQL/tools — it can't stop the model answering, say, "latest NBA news" from its own training knowledge (no tool needed). The classifier is the hard, cheap backstop; the system-prompt rule is defense-in-depth for the rare case the classifier fails open (by design, a classifier error allows the message rather than blocking a legitimate user). Verified: NBA/weather/election → declined in ~1.2s; data questions and follow-ups ("and the next 5?") → allowed.

F. Inline MCP config, strict/self-contained SDK options, tool lockdown

Decision: make_options() passes the MCP server config inline and sets strict_mcp_config=True, setting_sources=[], permission_mode="bypassPermissions", and tools=[] (which disables every built-in Claude Code tool — Bash, web, file edit — so only the 7 mcp__snowflake__* tools remain; verified they stay reachable because tool search is off). Why: The app is self-contained and reproducible — it uses only our Snowflake server and ignores any other MCP config or project/user settings on the machine, so it behaves the same everywhere. bypassPermissions is safe here precisely because the read-only gate in server.py and the empty built-in toolset are the real backstops.

G. Single-server production mode

Decision: In prod, app.py mounts the built frontend/dist at /, so one process serves both the UI and the API on port 8000. Dev mode instead runs Vite on 5173 with a /api → 8000 proxy for hot reload. Why: One command, one port, nothing to orchestrate for normal use; the split dev setup exists only when you're actively editing the frontend.

H. Per-session Agent SDK client, streamed over SSE

Decision: Each session id gets its own long-lived ClaudeSDKClient (guarded by an async lock); turns stream to the browser as Server-Sent Events. Why: Conversation memory per session, and the user sees reasoning/tool calls/results as they happen instead of waiting for one big response.

I. Portable Node, venv-based Python

Decision: Node lives in .tools/node (no global install); Python runs from the repo .venv. Why: The toolchain is self-contained and moves with the repo — no machine-wide setup, and the exact runtimes are pinned.

---

4. Running it

Prod (one server):

cd <repo>\snowflake-mcp-claude
.venv\Scripts\python.exe backend\app.py     # serves UI + API on http://127.0.0.1:8000

Dev (hot reload) — two terminals:

# terminal 1 — backend on 8000
.venv\Scripts\python.exe backend\app.py

# terminal 2 — Vite on 5173 (proxies /api -> 8000)
cd frontend
$env:PATH = "<repo>\snowflake-mcp-claude\.tools\node;$env:PATH"   # put portable Node on PATH
npm run dev            # open http://localhost:5173

Rebuild the frontend after edits (prod picks it up on reload):

cd frontend
$env:PATH = "<repo>\snowflake-mcp-claude\.tools\node;$env:PATH"
npm run build

The backend's /api/health is a preflight: it must show all green (server file, .env, Claude engine + login, MCP connection) before chatting.

---

5. Configuration (.env, gitignored)

Var	Purpose
SNOWFLAKE_ACCOUNT / _USER / _PASSWORD	Connection credentials
SNOWFLAKE_ROLE / _WAREHOUSE / _DATABASE / _SCHEMA	Session context (all optional)
SNOWFLAKE_MCP_MODE	Permission mode — read_only (default) / read_write / full
SNOWFLAKE_MCP_MAX_ROWS	Row cap per query (default 1000)

Permission modes:

Mode	Allows
read_only (default)	SELECT / SHOW / DESCRIBE / EXPLAIN / USE / VALUES / LIST / GET
read_write	the above + INSERT / UPDATE / DELETE / MERGE / COPY / PUT
full	the above + DDL (CREATE/ALTER/DROP/…) + admin (GRANT/REVOKE/CALL/…)

Change the mode in .env, then restart the backend.

---

6. Roadmap (additive — nothing gets thrown away)

• A1 (current): read-only SQL + discovery tools.
• A2: DDL/DML via read_write / full mode + confirmation on destructive ops.
• A3: RCA / observability tools (QUERY_HISTORY, TASK_HISTORY, COPY_HISTORY, profiling).
• A4: self-healing actions with dry-run, approval gates, audit log, and a scoped role.

---

7. Operational notes & gotchas

• Run the backend from this folder with the .venv Python. app.py resolves all paths (server.py, .env, frontend/dist) relative to itself. Launching a copy from the wrong directory or with system Python is the usual cause of a red /api/health.
• Port 8000 must match the Vite dev proxy target. If you change the backend port, update frontend/vite.config.ts too.
• The .venv is not relocatable. After moving the repo, recreate it: py -m venv .venv && .venv\Scripts\python.exe -m pip install -r requirements.txt.
• Scoped role for the future. It currently connects as ACCOUNTADMIN; move to a scoped role before enabling write/self-healing modes (A4).
• Primary target data: database AIRBNB, schema DBT_SCHEMA, warehouse COMPUTE_WH.

---

8. Future plans — deploying for colleague testing

Goal: let colleagues try the app. Today it runs locally on one machine. Getting others onto it is not a free choice of "any deployment × any auth" — the two are hard-coupled by Anthropic's terms. Read this constraint first; it decides everything else.

⚠️ The decisive rule: how you deploy dictates how you may authenticate

A Claude subscription (Pro/Max/Team login) may only power ordinary personal use of Claude Code. Per Anthropic's Usage Policy (Feb 2026), a third-party app may not route requests through subscription credentials on behalf of other users — and this is actively enforced. To serve multiple people from a backend you must use an Anthropic API key (Claude Console, per-token billing).

This means there is no "hosted webapp on a subscription" option — not even with each colleague pasting their own subscription token, because the app is still a third party routing their subscription requests. Subscriptions are fine only when each colleague runs the app themselves (that's their own ordinary use).

The allowed combinations (auth × deployment)

Deployment	Claude subscription (Code login / setup-token)	Claude API key
Per-user-local — each colleague runs the app on their own machine	✅ Allowed — their own ordinary use; no per-token bill	✅ Allowed
Hosted webapp — one backend, colleagues just open a URL (no install)	❌ Not permitted — even with per-user pasted tokens	✅ Required — service-account key or per-user keys (per-token bill)

So you pick one coherent bundle — not a mix:

• Bundle A — Subscription → must be Per-user-local. No API cost; each colleague uses their own Claude Code login. Cost: they install & run the app themselves.
• Bundle B — Hosted webapp (no VM) → must use API keys. Zero install for colleagues, deployable to a managed container platform. Cost: per-token API billing, plus real networking/auth work.

Bundle B in detail — hosted webapp, no VM to provision

A "webapp" here still means a container (the app spawns the Claude CLI + MCP subprocesses, holds per-session state, and streams SSE — so not a serverless function). Ship a Dockerfile to a managed container platform (Google Cloud Run, AWS App Runner, Azure Container Apps, Render, Fly.io) — they run the image; you never provision or patch a VM.

1. Claude auth: ANTHROPIC_API_KEY via ClaudeAgentOptions.env. Either one service-account key (simplest; you pay) or each colleague pastes their own key at onboarding (they pay, self-contained). Revisits decision B (login → API key); the rest of the code is unchanged.
2. Snowflake: a dedicated scoped read-only role — not ACCOUNTADMIN. Either one shared role in server config, or each colleague enters their own creds at onboarding.
3. Onboarding wizard (if per-user creds): turn the /api/health gate into a setup screen; a new POST /api/setup validates the pasted API key + Snowflake creds and holds them per-session in memory (never on disk/logs). Inject Snowflake creds as env on that session's MCP subprocess and the API key via the SDK client's env.
4. Access control (mandatory — it's now on a public URL): put it behind company SSO / an identity-aware proxy / VPN, or at minimum a shared password, over HTTPS (the platform provides TLS). Never expose a Snowflake-querying app open.
5. One warm instance: sessions (and any per-user secrets) live in memory → pin min=max=1 or use sticky sessions, else autoscaling splits conversations / loses creds.

Capping API spend to a fixed monthly budget (Pro-plan-style cost)

The API bills per token, but you can bound it to a flat monthly ceiling so it behaves like a subscription — e.g. cap it at ~$20/month to mirror a Claude Pro plan's price. Do it at two layers:

Layer 1 — Anthropic Console (the hard ceiling; ~5 min, no code):

• Create a dedicated workspace for this app and generate its API key there, so limits apply only to this app.
• Set a monthly spend limit on that workspace (Console → Limits/Billing). When hit, the API returns errors and the app stops spending — a true ceiling you can't overrun.
• Set per-workspace rate limits (RPM / input-TPM / output-TPM) to bound burst cost.
• Caveat: Console spend limits are monthly and workspace-wide, not daily or per-user.

Layer 2 — app side (daily + per-user granularity):

• Every turn, the Agent SDK's ResultMessage already carries total_cost_usd and usage (token counts). stream_turn receives it today but only reads duration_ms.
• Accumulate that cost into an in-memory tracker (daily reset; keyed globally and per session/user) and short-circuit new turns past a configurable limit — the same short-circuit pattern the scope classifier uses. Suggested .env knobs: DAILY_COST_LIMIT_USD, PER_USER_DAILY_MSGS, plus a per-turn output cap.

⚠️ "Identical to Pro" means identical price, not identical capacity. A $20/month API cap fixes your cost at Pro's price, but $20 of tokens is a specific token budget — it does not reproduce Pro's interactive usage allowance. Match the billing ceiling, not the usage limits. Treat the Console spend limit as the billing source of truth; total_cost_usd is a real-time gating signal, not an accounting figure.

Bundle A in detail — per-user-local, onboarding screen

Turn the /api/health gate into a setup wizard: detect the Claude Code login → prompt the user to run claude to log in if missing; collect Snowflake creds in a form → a new POST /api/setup validates + persists them + resets the session. Distribute via a one-command setup/start script. This dissolves networking, shared-credential, and access-control concerns entirely (it's all localhost), at the cost of each colleague installing Python + the app and having their own Claude Code login.

Decisions still to be made

• [ ] The bundle: A (subscription + per-user-local, no API cost, they install) vs B (hosted webapp + API keys, zero install, per-token cost). This is the fork above.
• [ ] If B — key model: one service-account key (you pay) vs per-user keys at onboarding (they pay).
• [ ] If B — host & access control: which managed platform (Cloud Run / App Runner / Container Apps), and SSO vs proxy vs shared password.
• [ ] Snowflake role: create the scoped read-only role + warehouse the app connects as (shared, or per-user via onboarding).
• [ ] Speed (either bundle): a quick throwaway test first (tunnel-from-this-machine for B, or a zip for A) vs a proper packaged deploy.

Known technical hurdles

• Cross-platform CLI. The Agent SDK bundles a platform-specific Claude Code binary (currently Windows). A Linux container needs Claude Code for Linux installed and the ANTHROPIC_API_KEY path verified end-to-end — validate this first; it's the riskiest step. (Auth-by-key in a container is far less fragile than a headless login.)
• Bundled Node is Windows-only (.tools/node). A Linux build needs its own Node (handled inside the Docker image).
• Concurrency. Each active session spawns its own Claude CLI subprocess; the scope classifier is a shared singleton behind a lock. Fine for a handful of testers, not a crowd — size the container accordingly.

Mechanism footnotes (verified against Anthropic docs, Feb 2026)

• Subscription headless auth exists (claude setup-token → CLAUDE_CODE_OAUTH_TOKEN, ~1-year token, no CLI revoke yet) — but is only for the account owner's own use, not multi-user serving (see the rule above).
• Both ANTHROPIC_API_KEY and CLAUDE_CODE_OAUTH_TOKEN are settable per-invocation via ClaudeAgentOptions.env.
• Sources: Claude Code Authentication docs; Claude Code Legal & Compliance (Usage Policy, Feb 2026); Agent SDK Python reference.