Model Context Protocol (MCP) Deep Dive

// .claude/settings.json — MCP server configuration
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "DATABASE_URL": "postgresql://user:pass@localhost:5432/mydb"
      }
    },
    "github": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-github"],
      "env": {
        "GITHUB_TOKEN": "ghp_your_token_here"
      }
    },
    "filesystem": {
      "command": "npx",
      "args": [
        "-y", "@modelcontextprotocol/server-filesystem",
        "/path/to/allowed/directory"
      ]
    },
    "puppeteer": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-puppeteer"]
    }
  }
}

// custom-mcp-server/src/index.ts
import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { z } from "zod";
import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

const server = new McpServer({
  name: "acme-internal",
  version: "1.0.0",
});

// Tool: Query users by various criteria
server.tool(
  "search_users",
  "Search for users by name, email, or role",
  {
    query: z.string().describe("Search term for name or email"),
    role: z.enum(["admin", "editor", "viewer"]).optional()
      .describe("Filter by user role"),
    limit: z.number().default(10).describe("Max results to return"),
  },
  async ({ query, role, limit }) => {
    let sql = "SELECT id, name, email, role, created_at FROM users WHERE 1=1";
    const params: any[] = [];

    if (query) {
      params.push(`%${query}%`);
      sql += ` AND (name ILIKE $${params.length} OR email ILIKE $${params.length})`;
    }

    if (role) {
      params.push(role);
      sql += ` AND role = $${params.length}`;
    }

    params.push(limit);
    sql += ` LIMIT $${params.length}`;

    const result = await pool.query(sql, params);

    return {
      content: [{
        type: "text",
        text: JSON.stringify(result.rows, null, 2),
      }],
    };
  }
);

// Tool: Get application metrics
server.tool(
  "get_metrics",
  "Get application metrics for a date range",
  {
    metric: z.enum(["signups", "revenue", "active_users", "churn"])
      .describe("Which metric to retrieve"),
    startDate: z.string().describe("Start date (YYYY-MM-DD)"),
    endDate: z.string().describe("End date (YYYY-MM-DD)"),
  },
  async ({ metric, startDate, endDate }) => {
    const queries: Record<string, string> = {
      signups: "SELECT DATE(created_at) as date, COUNT(*) as value FROM users WHERE created_at BETWEEN $1 AND $2 GROUP BY DATE(created_at) ORDER BY date",
      revenue: "SELECT DATE(created_at) as date, SUM(amount) as value FROM payments WHERE created_at BETWEEN $1 AND $2 GROUP BY DATE(created_at) ORDER BY date",
      active_users: "SELECT DATE(last_active) as date, COUNT(*) as value FROM users WHERE last_active BETWEEN $1 AND $2 GROUP BY DATE(last_active) ORDER BY date",
      churn: "SELECT DATE(cancelled_at) as date, COUNT(*) as value FROM subscriptions WHERE cancelled_at BETWEEN $1 AND $2 GROUP BY DATE(cancelled_at) ORDER BY date",
    };

    const result = await pool.query(queries[metric], [startDate, endDate]);

    return {
      content: [{
        type: "text",
        text: JSON.stringify({
          metric,
          period: `${startDate} to ${endDate}`,
          data: result.rows,
        }, null, 2),
      }],
    };
  }
);

// Resource: Expose database schema
server.resource(
  "schema",
  "database://schema",
  async () => {
    const result = await pool.query(`
      SELECT table_name, column_name, data_type, is_nullable
      FROM information_schema.columns
      WHERE table_schema = 'public'
      ORDER BY table_name, ordinal_position
    `);

    return {
      contents: [{
        uri: "database://schema",
        mimeType: "application/json",
        text: JSON.stringify(result.rows, null, 2),
      }],
    };
  }
);

// Start the server
const transport = new StdioServerTransport();
await server.connect(transport);

// package.json for the custom MCP server
{
  "name": "acme-mcp-server",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "build": "tsc",
    "start": "node dist/index.js"
  },
  "dependencies": {
    "@modelcontextprotocol/sdk": "^1.0.0",
    "pg": "^8.13.0",
    "zod": "^3.23.0"
  },
  "devDependencies": {
    "@types/pg": "^8.11.0",
    "typescript": "^5.6.0"
  }
}