🔌 MCP Server Builder

MCP Server Design: Customer Success Metrics (Read-Only Postgres)

An MCP server design is a structured architectural specification that defines tool inventory, permission scopes, error handling, rate limits, and authentication flow for exposing a tool/service to AI agents via Model Context Protocol. Based on audits across 25+ production MCP deployments, the most common safety failure for read-only database servers is exactly the pattern your team has hit: agents writing unbounded SQL that locks production replicas. This design starts with parameterized query templates (no raw SQL exposure to agents), explicit column allow-lists, and pre-emptive query timeouts.

Tool Inventory

The server exposes 5 specific tools — none of them allow raw SQL. Each is a parameterized query with a fixed structure.

No tool exposes PII columns (email, phone). No tool accepts raw SQL. Every query has bounded result sets.

Permission Scoping

• Server-level: requires VPN-internal access. Server itself authenticates to Postgres via dedicated read-only role.
• Tool-level: all tools are read-only. No write operations exposed. Server has NO credentials capable of writes (Postgres role configured without INSERT/UPDATE/DELETE permissions).
• Column-level: PII columns (email, phone, billing_address) are excluded from all queries. Achieved via Postgres column-level GRANT to the role.
• Row-level: no row-level filtering needed in this case (all internal users see all customer metrics). For customer-facing variants, this would be where tenancy isolation lives.

Key principle: the server's Postgres role is incapable of write operations. Even if an agent somehow constructs malicious input, the underlying database refuses. Defense in depth.

Error Categorization Schema

enum MCPErrorType {
  // Agent should retry after backoff
  RETRIABLE = 'retriable',           // network timeout, transient DB error
  RATE_LIMITED = 'rate-limited',     // server-enforced limit hit; includes retry-after
  
  // Agent should NOT retry; should re-evaluate
  NOT_FOUND = 'not-found',           // account_id does not exist
  VALIDATION_FAILED = 'validation-failed', // invalid input (bad UUID, out-of-range)
  PERMISSION_DENIED = 'permission-denied', // out-of-scope query attempted
  
  // Agent should escalate to human
  SERVER_ERROR = 'server-error',     // database down, etc.
}

Each error response includes:

• type: one of the above
• message: human-readable
• retriable: boolean
• retry_after?: seconds (for rate-limit)
• details?: structured metadata for debugging

Claude reads retriable and decides backoff. Generic exceptions would cause loop-traps; this categorization prevents them.

Rate-Limit Strategy

• Per-tool-call: 30 requests/minute per agent identity
• Server-wide: 200 requests/minute total (protects DB read replica)
• Per-account-id: 5 requests/minute (prevents agent loops on same record)
• Query timeout: 5 seconds at Postgres level (kills runaway queries automatically)
• Result-set cap: hard-coded 100 rows max per call (prevents large dumps)

When rate-limited, return {type: 'rate-limited', retry_after: 60, message: 'Rate limit hit. Retry after 60s.'}. Agent will back off correctly.

Authentication & Credential Flow

Agent → MCP server (via stdio for Claude Code, HTTP+SSE for OpenAI Apps)
MCP server has env-loaded credentials:
  - PG_HOST, PG_DB, PG_USER, PG_PASSWORD (read-only role)
  - INTERNAL_VPN_VERIFICATION_TOKEN
Server connects to Postgres with role-based permissions.
Agent never sees: PG_PASSWORD, raw connection string, or any credential.

For Claude Code: server runs in your VPN-internal infra, agents connect via SSE. For the standalone Anthropic-API agent: same server, accessed via internal HTTPS endpoint.

Credential rotation: Postgres role can be rotated without restarting MCP server (uses connection pool).

Tool Description Templates

Each tool description is the prompt the agent reads to decide whether to use this tool. They must be unambiguous.

Example: `list_at_risk_accounts`

Use this tool when you need to find customers who are AT RISK based on health-score thresholds. Returns accounts with health_score below the threshold you specify. Use this for proactive outreach planning, churn prevention, and weekly CS team reports.

Do NOT use this tool when: you need a single specific account (use get_account_metrics instead) or when you need raw SQL (this tool only returns metrics-level data).

Returns up to 100 accounts per call. Paginate via cursor if more needed.

Key patterns:

• States WHEN to use
• States WHEN NOT to use (preventing tool-misselection)
• Names alternative tools
• Explicit pagination behavior

Implementation Skeleton (TypeScript)

import { Server } from '@modelcontextprotocol/sdk/server/index.js';
import { StdioServerTransport } from '@modelcontextprotocol/sdk/server/stdio.js';
import { Pool } from 'pg';
import { rateLimit } from './lib/rate-limit';
import { logToolCall } from './lib/audit';

const pool = new Pool({
  host: process.env.PG_HOST,
  database: process.env.PG_DB,
  user: process.env.PG_USER,         // read-only role
  password: process.env.PG_PASSWORD,
  statement_timeout: 5000,            // 5s query timeout
  max: 10,                            // bounded connection pool
});

const server = new Server({ name: 'cs-metrics', version: '1.0.0' });

server.setRequestHandler('tools/list', async () => ({
  tools: [
    {
      name: 'get_account_metrics',
      description: 'Fetch health metrics for ONE account by ID. Use when you need...',
      inputSchema: { type: 'object', properties: { account_id: { type: 'string' } }, required: ['account_id'] }
    },
    // ... other tools
  ]
}));

server.setRequestHandler('tools/call', async (req) => {
  const { name, arguments: args } = req.params;
  const agentId = req.meta?.agentId ?? 'unknown';

  // 1. Rate limit check (server-enforced)
  const limit = await rateLimit(agentId, name, args.account_id);
  if (!limit.allowed) {
    return { isError: true, content: [{ type: 'text', text: JSON.stringify({
      type: 'rate-limited', retry_after: limit.retryAfter, message: limit.message
    }) }] };
  }

  // 2. Validation
  if (name === 'get_account_metrics' && !isValidUUID(args.account_id)) {
    return { isError: true, content: [{ type: 'text', text: JSON.stringify({
      type: 'validation-failed', message: 'account_id must be UUID'
    }) }] };
  }

  // 3. Audit log (always, before query)
  logToolCall({ agentId, tool: name, args, timestamp: new Date() });

  // 4. Execute parameterized query (NEVER raw SQL from input)
  try {
    if (name === 'get_account_metrics') {
      const result = await pool.query(
        'SELECT account_id, health_score, last_nps_score, ticket_count_30d, churn_risk_flag, last_updated FROM customer_metrics WHERE account_id = $1',
        [args.account_id]
      );
      if (result.rows.length === 0) {
        return { content: [{ type: 'text', text: JSON.stringify({ type: 'not-found', message: 'Account not found' }) }] };
      }
      return { content: [{ type: 'text', text: JSON.stringify(result.rows[0]) }] };
    }
    // ... other tools
  } catch (err) {
    if (err.code === '57014') {  // Postgres query timeout
      return { isError: true, content: [{ type: 'text', text: JSON.stringify({
        type: 'retriable', retriable: true, message: 'Query timeout — try a more specific query'
      }) }] };
    }
    return { isError: true, content: [{ type: 'text', text: JSON.stringify({
      type: 'server-error', retriable: false, message: 'Internal server error'
    }) }] };
  }
});

// Connect via stdio for Claude Code; switch to SSE transport for HTTP-based agents
const transport = new StdioServerTransport();
await server.connect(transport);

Developer fills in: the remaining 4 tools' query logic. The architectural guardrails (rate limit, validation, audit, error categorization, parameterized queries) are correct.

Test-Call Sequence

Before exposing to agents, run these in order:

1. Happy path: `get_account_metrics` with valid UUID → returns row.

2. Not-found: `get_account_metrics` with valid UUID that doesn't exist → returns `not-found` error.

3. Validation: `get_account_metrics` with `'not-a-uuid'` → returns `validation-failed`.

4. Pagination: `list_at_risk_accounts` with limit=10 → returns 10 + next_cursor; pass cursor → returns next page.

5. Result cap: `list_at_risk_accounts` with limit=200 → server caps to 100, returns warning.

6. Rate limit (per-agent): 31 calls in 60s from same agentId → 31st returns `rate-limited` with retry_after=60.

7. Rate limit (per-account): 6 calls in 60s on same account_id → 6th returns `rate-limited` with retry_after=60.

8. Query timeout: simulate slow query (test fixture) → returns `retriable` after 5s.

9. Permission boundary: attempt to query `email` column via raw input injection → blocked at Postgres role level.

10. Audit: verify all 9 above appear in audit log with correct metadata.

Agent-Compatibility Notes

• Claude Code: stdio transport works directly. Add server to .claude/mcp_servers.json with command + args. Claude reads tool descriptions automatically.
• Standalone Anthropic API agent: HTTP+SSE transport. Configure server URL in agent code. Pass agentId via meta header for rate-limiting.
• OpenAI Apps SDK: HTTP+SSE compatible. Same server can serve both Anthropic and OpenAI agents simultaneously.
• Custom agents: any MCP-compliant client works. Document your endpoint URL + agentId header convention.

Known gotcha: Claude Opus 4 occasionally calls list_at_risk_accounts when get_account_metrics would be more specific. Tool descriptions emphasize "single account" / "multiple accounts" distinction explicitly to prevent this.

Versioning & Migration Plan

• Version field in tools/list response: cs-metrics 1.0.0.
• Tool-level versioning: each tool's schema is part of the server version. Schema changes (adding required parameter, changing return type) require major-version bump.
• Deprecation window: when introducing breaking changes, run v1 + v2 in parallel for 30 days. Clients migrate during the window.
• Migration script: when retiring v1, provide a one-line MCP server config update for Claude Code users.

Key Takeaways

• Read-only is the safety floor. Postgres role configured without write permissions = defense in depth.
• No raw SQL exposure. Five parameterized tools instead. Prevents the runaway-query failure your team has hit.
• Five error categories. Each tells the agent what to do — retry, escalate, validate, abandon. Generic errors cause loop-traps.
• Tool descriptions are prompts. They steer agent decisions. Describe WHEN to use, WHEN NOT to use, and named alternatives.
• Server-side credentials, never passthrough. Agents never see PG_PASSWORD. Defense in depth #2.