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.

Related MCP server: scopedb-mcp

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.

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

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

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

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

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.