# durable-mcp

MCP server for Durable energy trading analytics. Connects Claude Desktop to your Snowflake data so traders can ask plain-English questions about spread portfolios, nodal prices, transmission constraints, generator performance, and PJM markets — without writing SQL or opening the right dashboard.

---

## How it works

```
Trader asks a question in Claude Desktop
          ↓
Claude picks the right tool based on the question
          ↓
MCP server (this repo, runs locally) queries your Snowflake
          ↓
Results come back → Claude reads them → answers the question
```

> **Note on data privacy:** your trading data (PnL, portfolio names, bid paths) passes through Anthropic's servers as part of the Claude conversation. See [Data & Security](#data--security) before using in production.

---

## Setup (30 seconds)

### 1. Add to Claude Desktop

Open your Claude Desktop config file:
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`
- **Mac:** `~/Library/Application Support/Claude/claude_desktop_config.json`

Add this block:

```json
{
  "mcpServers": {
    "durable": {
      "command": "npx",
      "args": ["-y", "github:cshah26/Chatbot-MCP"]
    }
  }
}
```

### 2. Restart Claude Desktop

That's it. The server reads credentials automatically from the Durable desktop app's keyring — no extra configuration needed if you already have the app installed.

### Credential fallback

The server tries credentials in this order:
1. **Durable app keyring** — Windows Credential Manager under `durable-desktop / snowflake-config`. Works automatically if the desktop app is installed.
2. **Environment variables** — copy `.env.example` to `.env` and fill in:

```bash
SNOWFLAKE_ACCOUNT=your_account.region
SNOWFLAKE_USERNAME=your_username
SNOWFLAKE_PASSWORD=your_password
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_ROLE=READ_ONLY_ROLE
```

---

## Available tools

| Tool | What it does |
|------|-------------|
| `get_spread_portfolios` | PnL summary by portfolio — total PnL, win rate, best/worst days |
| `get_spread_analysis` | Breakdown by path (source→sink), book, or book+path — win rate, avg/day |
| `get_nodal_spread` | DA and RT LMP spread between two nodes — hourly stats, peak-hour breakdown, day-of-week heatmap |
| `list_price_nodes` | Search available price nodes by name or ISO |
| `list_constraints` | List all ERCOT transmission constraint names |
| `get_constraint_loading` | Hourly max/min loading % for a specific ERCOT constraint |
| `get_flow_analysis` | All constraint loading for a single date — shows which lines are most at risk |
| `get_gen_comparison` | MUSE forecast vs Edison actual by plant and fuel type |
| `get_gen_detail` | Hourly Edison vs MUSE generation for a single plant |
| `get_pjm_wh` | PJM Western Hub DA/RT LMP prices and load with historical daily averages |
| `get_segment_tracker` | Bid segment counts by day and portfolio — submitted vs unsubmitted |

## Example questions

- *"What was our total PnL last week?"*
- *"Show me the spread between HB_NORTH and HB_SOUTH for July"*
- *"Which constraints were most loaded yesterday?"*
- *"How accurate was MUSE for gas plants this month?"*
- *"What were PJM Western Hub prices on July 10th?"*

---

## Performance

### What was slow (and what's fixed)

**Snowflake warehouse cold start — 10–30s on the first question of the day**
Snowflake auto-suspends idle warehouses. The server now sends a `SELECT 1` immediately on startup — before any trader types anything — so the warehouse is warm and the connection is live by the time the first query arrives.

**No caching — every question hit Snowflake live**
A TTL cache now sits in front of every query:

| Data type | Cache TTL |
|-----------|-----------|
| Historical PnL, spreads, constraint loading | 3 minutes |
| Node list, constraint names (static reference data) | 1 hour |

Repeat questions are served from memory instantly.

**Expected response times**

| Scenario | Before | After |
|----------|--------|-------|
| First question of the day | 15–35s | 1–3s |
| Same question asked again | 3–8s | <100ms |
| New question, warm warehouse | 2–5s | 1–3s |

### Snowflake warehouse tip

Set auto-suspend to **10 minutes** in your Snowflake account settings (default is 5) to give more buffer during trading hours. Optionally schedule a `SELECT 1` at 6am on trading days to pre-warm the warehouse before traders arrive.

---

## Data & Security

### What leaves your network

When a tool runs, the SQL results — PnL numbers, portfolio names, bid paths, generator data — are sent to Anthropic's servers as part of the Claude conversation. This is real trading data: positions, performance, and strategy details.

It does **not** go anywhere else. The MCP server has no telemetry, no external logging, and no analytics calls beyond Snowflake and Claude.

### Anthropic's data policy

| | Claude API | Claude.ai (consumer) |
|---|---|---|
| Trains on your data | No | Depends on settings |
| Data retention | Short-term for safety review | Longer |
| Enterprise agreement / BAA | Available | Not available |

### Options if your firm has data sensitivity requirements

**Option 1 — Claude Enterprise**
Anthropic's enterprise offering includes a data processing agreement, zero retention, and stricter data handling. Designed for financial firms. The MCP server works unchanged.

**Option 2 — Local model**
Run a local LLM (e.g. Llama 3 via Ollama) instead of Claude. Nothing leaves your network. Tradeoff: local models are less capable. The MCP protocol is identical — only the model endpoint changes.

**Option 3 — Data minimization**
Modify tools to send only aggregated summaries to Claude (totals, averages) rather than raw row data. Claude can still answer most questions but sees less sensitive detail.

Consult your compliance team before using this with live trading data.

---

## Architecture

```
src/
  index.ts          — entry point: stdout guard, module loading, server setup, pre-warm
  db.ts             — Snowflake connection, TTL cache, query execution, auto-retry
  keyring.ts        — reads credentials from Windows Credential Manager
  tools/
    spread.ts       — get_spread_portfolios, get_spread_analysis
    nodal.ts        — get_nodal_spread, list_price_nodes
    constraints.ts  — list_constraints, get_constraint_loading, get_flow_analysis
    generators.ts   — get_gen_comparison, get_gen_detail
    pjm.ts          — get_pjm_wh
    segments.ts     — get_segment_tracker
```

### Key design decisions

**stdout guard**
Snowflake's SDK (winston) writes logs directly to `process.stdout`, which would corrupt the MCP JSON-RPC stream. `index.ts` patches `process.stdout.write` before any modules load, redirecting anything that isn't a JSON-RPC 2.0 message to `stderr`. The filter checks for `"jsonrpc":"2.0"` specifically to avoid false positives.

**Read-only enforcement**
`db.ts` rejects any SQL that does not start with `SELECT` or `WITH`. No writes possible.

**Connection auto-retry**
If the Snowflake connection drops between queries (idle timeout, network reset), the next query transparently resets the connection state and retries once with a fresh connection before surfacing an error to the caller.

---

## Development

```bash
npm install
npm run build   # compile TypeScript → dist/
npm start       # run the compiled server
```

The `dist/` directory is committed so `npx github:...` works without a build step on the consumer side.

### Publishing updates

Push to GitHub — team members get the latest automatically on the next Claude Desktop restart:

```bash
git add -A && git commit -m "your message" && git push
```

---

## Changelog

### `391cbaf` — fix+perf: resolve 9 code review issues

**Bugs fixed**
- `logLevel` restored from `'OFF'` to `'ERROR'` — Snowflake SDK error events (TLS drops, connection resets) are visible on stderr again; the stdout intercept already handled MCP stream safety
- Auto-retry on stale connection — if the connection drops between queries, it resets and retries once transparently instead of failing
- stdout filter tightened from `"jsonrpc"` to `"jsonrpc":"2.0"` — eliminates false positives from any library that logs JSON containing the word "jsonrpc"
- Comment corrected: snowflake-sdk uses **winston** internally, not log4js

**Performance**
- Snowflake connection and warehouse pre-warmed on startup — first question no longer pays 10–30s resume cost
- TTL query cache added — repeat questions served from memory; static reference data (node list, constraint names) cached for 1 hour
- Dynamic imports parallelised with `Promise.all` — all 8 tool modules load concurrently at startup

**Cleanup**
- `console.error` and `console.warn` overrides removed — dead code (those methods write to stderr internally and never touched stdout)

### `3f4f7d2` — fix: intercept process.stdout.write to block snowflake log4js from corrupting MCP stream

### `1017fff` — fix: redirect all stdout to stderr to keep MCP protocol stream clean

### `f706a7b` — fix: include dist/ in repo so npx github: works without build step