washing-machine-mcp

TL;DR. MCP server for tiered natural-language access to a washing-machine Turso DB. Stdio (local agent) and Streamable HTTP (remote agent). The differentiator is not "uses MCP" — it's that the tiered, injection-resistant authorization layer from a previous deployment was ported to MCP transport without weakening a single guarantee, and the test matrix proves it.

Why this is interesting

Most public MCP servers treat authorization as a footnote and have no real security test suite. This project inverts the priority: the threat model is the lede, the protocol is the transport. A reviewer should be able to read the matrix below and tell within five minutes whether the project's guarantees hold up.

Headlines

• Belt-and-suspenders tier enforcement. The LLM is told the full column/filter space; the server downgrades silently server-side. Tests assert that prompt injection can't escalate access.
• No bearer-token oracle. A failed Authorization: Bearer … always produces the same response shape, the same message, and the same status. Unknown-bearer and DB-error paths are byte-identical.
• Stdout is the protocol. A static rg check forbids console.log anywhere in src/; a functional test spawns the server, sends real JSON-RPC, and asserts every byte on stdout parses as a JSON-RPC frame.
• Two transports, one security model. Stdio (MCP_KEYS alias map) and Streamable HTTP (Authorization: Bearer …) share the query layer byte-for-byte. Neither accepts identity via tool arguments.
• Audit log per request. Every request — success, RBAC denial, auth failure, rate-limit, body-too-large — emits exactly one [audit] JSON record on stderr, never raw token, never natural-language text.

See the threat-model matrix for the enumeration.

Threat model and test matrix

This is the lead section of the README on purpose. Anyone evaluating this project should be able to read this section and walk away with the full story.

Threat	Test(s) that prove it's defended
SQL injection via filter name	tests/sql-identifier.test.ts + tests/sql-injection.test.ts
SQL injection via column name	tests/sql-injection.test.ts (DROP TABLE attempt)
SQL injection in filter value	Kysely's parameter binding (no string concat anywhere) + tests/sql-injection.test.ts
Tier escalation by prompt injection ("pretend I'm admin")	tests/mcp-integration.test.ts + invariant #5 (tool descriptions are tier-agnostic) + invariant #2 (tier derived only from credential)
Tier escalation by tool-argument smuggling	tests/mcp-integration.test.ts ("HTTP-mode tools should not have identity in their schema")
Data leak via fallback to all-rows when filters rejected	tests/tier-enforcement.test.ts
Information disclosure of higher-tier columns in tool descriptions	tests/mcp-integration.test.ts (description regex scan)
Bearer-token oracle (distinguishing unknown-vs-valid tokens)	tests/unknown-identity-oracle.test.ts
Stale-token oracle (distinguishing very-old from fresh tokens)	tests/last-used.test.ts (refusal uses identical message)
Plaintext token disclosure in logs	tests/stdout-purity.test.ts + tests/resolve-tier.test.ts + tests/audit.test.ts (audit never carries raw token)
Plaintext token disclosure in DB	Schema column is api_key_hash (SHA-256); tests/token-hash.test.ts
Stdout pollution breaking the MCP protocol	tests/stdout-purity.test.ts (static + functional)
LLM hang on input	tests/zen-timeout.test.ts
Provider internals leaking via raw exception message	safeErrorKind() in src/mcpServer.ts; covered by tests/tools-rbac.test.ts + tests/mcp-integration.test.ts
DNS rebinding (HTTP only)	src/http.ts checkOriginAndHost + tests/http-hardening.test.ts
Origin/Host deny-by-default in production	tests/http-hardening.test.ts (DEV_MODE=false + empty allowlist → 403)
Bearer-token replay via session-ID reuse with another user's token	src/http.ts sessions keyed by token hash; mismatch → 401
Cleartext bearer on the wire	TLS enforcement at startup; tests/tls-enforce.test.ts
Basic-tier triggering LLM cost	Per-tier tool allowlist; tests/tool-allowlist.test.ts + tests/tools-rbac.test.ts
Oversized JSON-RPC frame DoS	Body-size cap on /mcp (64 KB → 413); tests/http-hardening.test.ts
Brute-force / hammer attack	Rate limit per (IP, bearer-fingerprint) → 429 with Retry-After; tests/http-hardening.test.ts
Bearer replay over very long windows	last_used_at surface; tests/last-used.test.ts
Missing audit trail after an incident	Every request emits exactly one [audit] JSON record; tests/audit.test.ts
Slow query tying up a worker / DoS via expensive SELECT	QUERY_TIMEOUT_MS race in executeTierQuery; QueryTimeoutError mapped to query_timeout audit outcome; loser promise .catch-suppressed to avoid unhandled rejection; tests/query-timeout.test.ts

Guarantees and non-guarantees

What this project commits to, and what it does NOT commit to. Reading this section is faster than reading every line of src/.

✓ Guarantees

These hold today, are tested, and are documented as security invariants in AGENTS.md:

• Tier projection is a pure function of the resolved transport credential. Stdio: alias from MCP_KEYS. HTTP: Authorization: Bearer SHA-256 hash. Neither accepts identity from tool arguments.
• The LLM is told the full column/filter space; tier enforcement happens server-side after the LLM returns. Prompt-injection cannot escalate access.
• All identifiers (filter names, column names) come from a regex whitelist (/^[a-z_][a-z0-9_]*$/, length ≤ 64) before reaching Kysely. Values are bound via Kysely parameterization.
• Identifier, filter, and column rejections are silent and uniform. No response shape distinguishes "unknown tier" from "unknown column" from "DB error".
• All rejected filters ⇒ empty results + error message. The auto-injected LIMIT does not count as a user-applied filter for this check.
• STDOUT is only JSON-RPC. Logs go to stderr; logs are funneled through two named writers only (src/logger.ts, src/audit.ts).
• One [audit] record per request, in a finally block, even on exception. Never raw token. Never natural-language text.
• users.last_used_at is touched on every successful auth (fire-and-forget). Tokens older than STALE_TOKEN_REFUSE_DAYS (default 365d) get the identical IDENTITY_NOT_RESOLVED response — no stale-token oracle.

✗ Non-guarantees (or scoped)

These are deliberately deferred and called out so a reviewer doesn't discover them silently:

• Silent column / filter downgrades. When a caller asks for a column or filter they don't have, the server silently drops it rather than returning an error. Rows are returned with whatever subset was allowed. The audit log and the response payload's requestedColumns / rejectedOperations arrays expose what was dropped, but a programmatic caller must inspect those — the response code is still 200. We trade explicitness for usability: a tier-restricted column is the common case, not the error case. If you'd rather get a 4xx on every rejected column, see the wrapper in src/mcpServer.ts (wrapTool).
• Rate limiter is in-memory. Single-process, single-instance. Behind a load balancer with N replicas, the effective limit is N × 60/min. Multi-instance deployments must use a shared KV (the legacy Worker used Cloudflare KV; this project doesn't ship one). Tracked as a follow-up.
• Query-timeout loser is abandoned, not cancelled. When QUERY_TIMEOUT_MS fires, we stop awaiting the DB promise and surface query_timeout to the caller, but we don't cancel the underlying statement — Kysely + libsql don't expose a statement-cancel API here, and the HTTP transport to Turso means the abandoned JS-side promise resolves on its own once Turso finishes. The loser has .catch(() => {}) attached so the eventual settle doesn't surface as an unhandled rejection.
• SOURCE_IP extraction trusts the first X-Forwarded-For hop. Set TRUSTED_PROXY_HOPS to match your edge proxy topology. With TRUSTED_PROXY_HOPS=0 (default), only the immediate connection peer is trusted.
• OAuth 2.1 introspection (RFC 7662) not implemented. Bearer validation is local-DB lookup, not a remote AS call. True federated identity is a separate workstream.
• Tokens are SHA-256-hashed, not argon2id. For high-entropy server-generated tokens the choice is fine; documented in src/security/tokenHash.ts. If your threat model includes low-entropy tokens, swap the hash function there.
• Tools are advertised regardless of tier. tools/list returns the same shape to every caller; the per-tier filter fires at invocation. This is a deliberate trade — see the per-tier tool allowlist test comment.
• HTTP_PUBLIC_URL is the external URL. The TLS proxy assumption is that an upstream terminates TLS and forwards cleartext to this server. We do not enforce that the local listen socket is bound on https://.

Why this project exists

The same data platform had a Cloudflare Worker HTTP API exposing this. That deployment worked and the security work was done (SECURITY_FIXES.md in the legacy repo documents the threat model and the three classes of bugs that were fixed). This project is the updated canonical implementation:

• Same authorization layer, lifted from src/filters/* and src/handlers/*
• Same code paths; transport swapped from HTTP-POST to MCP
• MCP-native: tools, not REST endpoints
• Two transports shipped: stdio for local agents, Streamable HTTP for remote
• No console.log to stdout; no tool-argument identity in HTTP mode

Architecture

                    ┌───────────────────────┐
                    │  MCP client          │
                    │  (Claude / Inspector │
                    │   / opencode / etc.)  │
                    └──────────┬────────────┘
                               │  JSON-RPC
            ┌──────────────────┴──────────────────┐
            │                                     │
        stdio transport                  Streamable HTTP transport
            │                                     │
   ┌────────▼────────┐                  ┌──────────▼──────────┐
   │   src/stdio.ts  │                  │     src/http.ts    │
   │                 │                  │                    │
   │ identity from   │                  │ identity from       │
   │ MCP_KEYS env    │                  │ Authorization:      │
   │ (alias map)     │                  │ Bearer <token>      │
   └────────┬────────┘                  └──────────┬──────────┘
            │                                     │
            └─────────────┬───────────────────────┘
                          │
                  ┌───────▼────────┐
                  │ src/mcpServer │
                  │  (shared)     │
                  │  4 tools +    │
                  │  1 resource   │
                  └───────┬────────┘
                          │
        ┌─────────────────┼─────────────────┐
        │                 │                 │
  query_natural_     query_structured    get_machine_by_id,
  language tool      tool, filter        list_available_columns
  (calls Zen LLM)    whitelist            (no LLM)
                          │
                  ┌───────▼────────┐
                  │ src/filters/   │
                  │ queryBuilder   │  ← SQL identifier regex whitelist
                  │ tierFilters    │  ← per-tier allowlists
                  └───────┬────────┘
                          │
                  ┌───────▼────────┐
                  │  Turso / libsql│
                  │  (hashed       │
                  │   tokens)      │
                  └────────────────┘

Setup

bun install

# 1. Create your Turso DB (one-time)
turso db create washing-machine-mcp
turso db shell washing-machine-mcp < db/schema.sql

# 2. Configure environment
cp .env.example .env
# Fill TURSO_DATABASE_URL, TURSO_AUTH_TOKEN, API_KEY_ZEN, MCP_KEYS

# 3. Seed (one-time, idempotent)
bun run seed

# 4. Enable the git hooks (recommended)
git config core.hooksPath .githooks
# pre-commit: typecheck. pre-push: typecheck + full bun test.

# 5. Run
bun run start              # stdio (spawned by MCP clients)
bun run start:http         # streamable HTTP on $HTTP_PORT

MCP client configuration

Claude Desktop

{
  "mcpServers": {
    "washing-machines": {
      "command": "/absolute/path/to/bun",
      "args": ["run", "/absolute/path/to/washing-machine-mcp/index.ts"],
      "env": {
        "TURSO_DATABASE_URL": "libsql://...",
        "TURSO_AUTH_TOKEN": "...",
        "API_KEY_ZEN": "...",
        "MCP_KEYS": "basic:dk-demo-basic,medium:dk-demo-medium,high:dk-demo-high,ultra:dk-demo-ultra",
        "MCP_DEFAULT_IDENTITY": "medium"
      }
    }
  }
}

Pitfall: Claude Desktop does NOT inherit PATH reliably across platforms. The command MUST be an absolute path to bun.

Inspector

npx @modelcontextprotocol/inspector bun run /path/to/index.ts

This is the demo environment. Pick an identity, run the same query in two sessions, watch the row set change.

Tools and resources

Tool	Args	Auth	Returns
query_structured	{ filters: [{filter, value}], columns?: string[] }	bearer (HTTP) / env alias (stdio)	Tier-projected rows + applied/rejected operations + generated SQL
query_natural_language	{ naturalLanguage: string }	bearer (HTTP) / env alias (stdio)	Same shape as query_structured after the LLM extracts structure
get_machine_by_id	{ id: number }	bearer (HTTP) / env alias (stdio)	One row projected to tier columns. Ids are enumerable within a tier.
list_available_columns	{}	bearer (HTTP) / env alias (stdio)	{ tier, columns } for the calling identity only

Resource	Mode	Contents
schema://current-tier	stdio only	Column list for the server's MCP_DEFAULT_IDENTITY. Reflects the default identity, not the calling identity of any tool call.

Two-tier demonstration

Same agent, same query, different identity → different rows.

stdio — restart the server with a different MCP_DEFAULT_IDENTITY:

# Step 1: ask for a supplier-cost filter as basic tier
MCP_DEFAULT_IDENTITY=basic bun run start:http
# Tool call: "Samsung under 1000"
# Result: 1 row (Samsung EcoWash 3000), 3 columns

# Step 2: same query as ultra tier
MCP_DEFAULT_IDENTITY=ultra bun run start:http
# Tool call: "Samsung under 1000"
# Result: still 1 row but 12 columns including supplierCost, marginPercent, ...

HTTP — curl with two different bearer tokens:

# dk-demo-basic — gets 3 columns, no supplier cost
curl -X POST http://localhost:3000/mcp \
     -H 'Authorization: Bearer dk-demo-basic' \
     -H 'Content-Type: application/json' \
     -d '{"jsonrpc":"2.0","id":1,"method":"tools/call",
          "params":{"name":"query_structured",
                    "arguments":{"filters":[{"filter":"brand","value":"Samsung"}]}}}'

# dk-demo-ultra — gets 12 columns
curl -X POST http://localhost:3000/mcp \
     -H 'Authorization: Bearer dk-demo-ultra' \
     -H 'Content-Type: application/json' \
     -d '... same body ...'

Difference between the two transports

	stdio	HTTP
Identity source	MCP_DEFAULT_IDENTITY env	Authorization: Bearer ... header
Tool arguments for identity?	No — schema forbids it	No — schema forbids it
Switching tiers	Restart the server	Change the bearer
TLS	N/A (local pipe)	Required upstream
DNS rebinding	N/A	HTTP_ALLOWED_ORIGINS
Resource registered	yes (schema://current-tier)	no (resources can't take per-call args)
RFC 9728 metadata	n/a	/.well-known/oauth-protected-resource
401 includes WWW-Authenticate	n/a	yes

In both transports, the security model is identical. Switching transport doesn't relax any rule.

Tests

bun test
# 103 pass, 0 fail, 253 expect() calls, ~21s

The suite includes a tests/stdout-purity.test.ts that spawns the actual stdio server, sends a real JSON-RPC ping, and asserts that every byte on stdout is a parseable JSON-RPC frame. This catches the most common way to silently break MCP clients (a stray console.log somewhere).

The protocol integration test in tests/mcp-integration.test.ts uses the SDK's InMemoryTransport to drive two clients through the same server in one process, with two different identities, and asserts that the tool results differ in exactly the way tier enforcement requires.

Pre-commit / pre-push hooks

.githooks/ ships two shell-script hooks. Enable them once per clone:

git config core.hooksPath .githooks

Hook	Runs	Why
pre-commit	bunx --bun tsc --noEmit	Fast type-check on every commit. Skipped automatically for chore: / docs: / ci: subjects, or when WASHING_MACHINE_MCP_SKIP_HOOKS=1.
pre-push	bunx --bun tsc --noEmit + bun test	Full suite (~21s). The "before pushing up" gate.

If the pre-push fails, the push is blocked — git push --no-verify is the escape hatch, but please don't reach for it without reading the failure.

MCP mechanics (the boring bit, kept here)

MCP mechanics (the boring bit, kept here)

• SDK: @modelcontextprotocol/sdk@1.30.0
• MCP spec version: 2025-06-18
• Transport 1: StdioServerTransport
• Transport 2: WebStandardStreamableHTTPServerTransport
• Tool registration: McpServer.registerTool(name, {description, inputSchema}, handler)
• Output: content: [{ type: "text", text: JSON.stringify(...) }] plus structuredContent for schema-aware clients

Lineage

This repo	Source it descends from
src/filters/queryBuilder.ts (SQL identifier whitelist + whitelist enforcement)	Legacy Worker src/filters/queryBuilder.ts (file diff in SECURITY_FIXES.md)
src/handlers/llm.ts (tier-agnostic system prompt + tier-agnostic retry)	Legacy Worker src/handlers/llm.ts
src/handlers/washingMachines.ts (tier column arrays)	Legacy Worker src/handlers/washingMachines.ts
src/filters/tierFilters.ts (filter definitions + per-tier allowlists)	Legacy Worker src/filters/tierFilters.ts

The query layer is byte-for-byte equivalent. The transport is the only meaningful difference.

License

MPL-2.0 — see LICENSE. Source files that are derivative works of the licensed Covered Software should carry the MPL notice; if you redistribute a modified version, that modification is also under the MPL.