// BAD 1: Vague — tells the model nothing actionable
{
  "name": "search",
  "description": "Searches for things"
}

// BAD 2: Implementation-focused instead of behaviour-focused
{
  "name": "db_query",
  "description": "Runs a SQL SELECT on the PostgreSQL users table using pg_pool"
}

// BAD 3: Missing critical information
{
  "name": "send_email",
  "description": "Sends an email"
  // Does it take an address? A template ID? Does it actually send
  // or just queue? What does it return? The model has to guess everything.
}

// GOOD 1: Specific action, clear inputs, defined output
{
  "name": "search_products",
  "description": "Searches the product catalogue by keyword or SKU. Accepts a query string and optional category filter. Returns an array of matching products, each with id, name, price (USD cents), and stock_count. Returns an empty array if no matches are found."
}

// GOOD 2: States side effects explicitly
{
  "name": "create_order",
  "description": "Creates a new order for the given customer. Requires customer_id and an array of line items (product_id + quantity). Charges the customer's default payment method immediately. Returns the order object with status 'confirmed' or an error if payment fails. THIS TOOL HAS SIDE EFFECTS: it charges real money."
}

// GOOD 3: Disambiguates from similar tools
{
  "name": "lookup_user_by_id",
  "description": "Retrieves a single user record by their unique numeric ID. Use this when you already have the user's ID. Returns the full user profile including name, email, and role. Returns a not_found error if the ID does not exist. NOTE: To search users by name or email, use search_users instead."
}

{
  // String with length and pattern constraints
  "sku": {
    "type": "string",
    "description": "Product SKU in format ABC-12345",
    "pattern": "^[A-Z]{3}-\\d{5}$",
    "minLength": 9,
    "maxLength": 9
  },

  // String with format validation
  "email": {
    "type": "string",
    "description": "Customer email address",
    "format": "email"
  },

  // Date string
  "ship_date": {
    "type": "string",
    "description": "Requested shipping date",
    "format": "date"
  },

  // Number with range
  "quantity": {
    "type": "integer",
    "description": "Number of units to order (1–500)",
    "minimum": 1,
    "maximum": 500
  },

  // Float with exclusive bounds
  "discount_rate": {
    "type": "number",
    "description": "Discount as a decimal (e.g. 0.15 for 15%)",
    "minimum": 0,
    "exclusiveMaximum": 1
  },

  // Boolean
  "express_shipping": {
    "type": "boolean",
    "description": "Whether to use express shipping (2-day)"
  },

  // Enum — restricts to exact values
  "priority": {
    "type": "string",
    "description": "Ticket priority level",
    "enum": ["low", "medium", "high", "critical"]
  },

  // Nullable string — allows null explicitly
  "notes": {
    "type": ["string", "null"],
    "description": "Optional order notes. Pass null if none."
  }
}

{
  // Nested object with its own required fields
  "shipping_address": {
    "type": "object",
    "description": "Delivery address",
    "properties": {
      "street": { "type": "string" },
      "city":   { "type": "string" },
      "state":  { "type": "string", "pattern": "^[A-Z]{2}$" },
      "zip":    { "type": "string", "pattern": "^\\d{5}(-\\d{4})?$" }
    },
    "required": ["street", "city", "state", "zip"]
  },

  // Array of objects with min/max items
  "line_items": {
    "type": "array",
    "description": "Products to include in the order",
    "minItems": 1,
    "maxItems": 50,
    "items": {
      "type": "object",
      "properties": {
        "product_id": { "type": "integer" },
        "quantity":   { "type": "integer", "minimum": 1 }
      },
      "required": ["product_id", "quantity"]
    }
  }
}

{
  "name": "create_order",
  "description": "Creates and submits a new order for a customer. Charges the customer's payment method on file immediately. Returns the order object with confirmation number and estimated delivery date, or a structured error if payment fails or inventory is insufficient.",
  "input_schema": {
    "type": "object",
    "properties": {
      "customer_id": {
        "type": "integer",
        "description": "Unique customer identifier"
      },
      "line_items": {
        "type": "array",
        "description": "One or more products to order",
        "minItems": 1,
        "maxItems": 50,
        "items": {
          "type": "object",
          "properties": {
            "product_id": { "type": "integer" },
            "quantity":   { "type": "integer", "minimum": 1, "maximum": 500 },
            "gift_note":  { "type": ["string", "null"], "maxLength": 200 }
          },
          "required": ["product_id", "quantity"]
        }
      },
      "shipping_address": {
        "type": "object",
        "properties": {
          "street":  { "type": "string", "maxLength": 200 },
          "city":    { "type": "string", "maxLength": 100 },
          "state":   { "type": "string", "pattern": "^[A-Z]{2}$" },
          "zip":     { "type": "string", "pattern": "^\\d{5}(-\\d{4})?$" },
          "country": { "type": "string", "enum": ["US", "CA", "MX"] }
        },
        "required": ["street", "city", "state", "zip", "country"]
      },
      "priority": {
        "type": "string",
        "enum": ["standard", "express", "overnight"],
        "description": "Shipping speed. Defaults to standard if omitted."
      },
      "coupon_code": {
        "type": ["string", "null"],
        "description": "Optional promotional code. Pass null if none.",
        "pattern": "^[A-Z0-9]{6,12}$"
      }
    },
    "required": ["customer_id", "line_items", "shipping_address"]
  }
}

interface ToolError {
  error: true;                          // Always true — lets Claude detect errors reliably
  errorCategory:
    | "validation"                      // Bad input from the model
    | "not_found"                       // Resource does not exist
    | "permission"                      // Auth/authz failure
    | "rate_limit"                      // Throttled — try again later
    | "conflict"                        // State conflict (e.g. duplicate)
    | "internal";                       // Unexpected server failure
  isRetryable: boolean;                 // Can the model try the same call again?
  message: string;                      // Human-readable explanation
  retryAfter?: number;                  // Seconds to wait before retrying (rate_limit)
  invalidFields?: Record<string, string>; // Field-level validation errors
}

// BAD: Claude has no idea what to do with this
{
  "result": "Error: invalid request"
}

// BAD: Stack trace is noise for the model
{
  "error": "TypeError: Cannot read property 'id' of undefined\n    at Object.handler (/app/src/orders.js:42:15)\n    at ..."
}

// GOOD: Validation error — Claude can fix and retry
{
  "error": true,
  "errorCategory": "validation",
  "isRetryable": true,
  "message": "The shipping address is missing a required field.",
  "invalidFields": {
    "shipping_address.zip": "ZIP code is required and must be 5 or 9 digits"
  }
}

// GOOD: Rate limit — Claude knows to wait
{
  "error": true,
  "errorCategory": "rate_limit",
  "isRetryable": true,
  "message": "API rate limit exceeded. Try again after the specified delay.",
  "retryAfter": 30
}

// GOOD: Not found — Claude can inform the user
{
  "error": true,
  "errorCategory": "not_found",
  "isRetryable": false,
  "message": "No customer found with ID 99421. Verify the customer ID and try again."
}

// Tool definition as returned by tools/list
{
  "name": "query_customers",
  "description": "Queries the customer database. Accepts a SQL WHERE clause (read-only). Returns up to 100 matching rows as JSON objects.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "where_clause": {
        "type": "string",
        "description": "SQL WHERE condition, e.g. \"status = 'active' AND country = 'US'\""
      },
      "limit": {
        "type": "integer",
        "minimum": 1,
        "maximum": 100
      }
    },
    "required": ["where_clause"]
  }
}

// Resource definition as returned by resources/list
{
  "uri": "db://customers/{id}",
  "name": "Customer Record",
  "description": "Full customer profile including contact info, order history summary, and account status.",
  "mimeType": "application/json"
}

// Prompt definition as returned by prompts/list
{
  "name": "code_review",
  "description": "Generates a structured code review for a given file",
  "arguments": [
    {
      "name": "file_path",
      "description": "Path to the source file to review",
      "required": true
    },
    {
      "name": "focus_area",
      "description": "Specific aspect to focus on: security, performance, readability",
      "required": false
    }
  ]
}

Routing Agent (3 tools)
  ├── route_to_product_agent    → Product Agent (6 tools)
  │                                ├── search_products
  │                                ├── get_product
  │                                ├── create_product
  │                                ├── update_product
  │                                ├── delete_product
  │                                └── get_product_reviews
  │
  ├── route_to_order_agent      → Order Agent (5 tools)
  │                                ├── create_order
  │                                ├── get_order
  │                                ├── cancel_order
  │                                ├── refund_order
  │                                └── list_customer_orders
  │
  └── route_to_support_agent    → Support Agent (6 tools)
                                   ├── search_tickets
                                   ├── create_ticket
                                   ├── update_ticket
                                   ├── assign_ticket
                                   ├── resolve_ticket
                                   └── get_ticket_history

mkdir mcp-database-server && cd mcp-database-server
npm init -y
npm install @modelcontextprotocol/sdk better-sqlite3
npm install -D typescript @types/better-sqlite3
npx tsc --init

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

// Open (or create) the database
const db = new Database("./store.db");

// Create the MCP server
const server = new McpServer({
  name: "database-server",
  version: "1.0.0",
});

// ── Tool 1: Query Customers ────────────────────────────
server.tool(
  "query_customers",
  "Searches the customers table. Accepts a search term that matches against " +
  "name or email (case-insensitive). Returns up to 20 matching customer " +
  "records with id, name, email, and status. Returns an empty array if " +
  "no customers match.",
  {
    search_term: z.string().describe("Name or email substring to search for"),
    limit: z.number().min(1).max(20).default(10).describe("Max results to return"),
  },
  async ({ search_term, limit }) => {
    const rows = db
      .prepare(
        `SELECT id, name, email, status FROM customers
         WHERE name LIKE ? OR email LIKE ?
         LIMIT ?`
      )
      .all(`%${search_term}%`, `%${search_term}%`, limit);

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

// ── Tool 2: Get Order Details ──────────────────────────
server.tool(
  "get_order",
  "Retrieves a single order by its numeric ID. Returns the order with " +
  "customer name, line items, total amount (USD cents), and current status. " +
  "Returns a not_found error if the order ID does not exist.",
  {
    order_id: z.number().int().positive().describe("The unique order ID"),
  },
  async ({ order_id }) => {
    const order = db
      .prepare(
        `SELECT o.id, o.status, o.total_cents, o.created_at, c.name as customer_name
         FROM orders o JOIN customers c ON o.customer_id = c.id
         WHERE o.id = ?`
      )
      .get(order_id);

    if (!order) {
      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            error: true,
            errorCategory: "not_found",
            isRetryable: false,
            message: `No order found with ID ${order_id}`,
          }),
        }],
        isError: true,
      };
    }

    const items = db
      .prepare(
        `SELECT p.name, li.quantity, li.unit_price_cents
         FROM line_items li JOIN products p ON li.product_id = p.id
         WHERE li.order_id = ?`
      )
      .all(order_id);

    return {
      content: [{
        type: "text",
        text: JSON.stringify({ ...order, line_items: items }, null, 2),
      }],
    };
  }
);

// ── Tool 3: Check Inventory ────────────────────────────
server.tool(
  "check_inventory",
  "Checks current stock levels for a product by its ID or SKU. " +
  "Returns the product name, SKU, current quantity in stock, and " +
  "reorder threshold. Use this before creating orders to verify availability.",
  {
    product_id: z.number().int().positive().optional()
      .describe("Product ID (provide this OR sku, not both)"),
    sku: z.string().optional()
      .describe("Product SKU (provide this OR product_id, not both)"),
  },
  async ({ product_id, sku }) => {
    if (!product_id && !sku) {
      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            error: true,
            errorCategory: "validation",
            isRetryable: true,
            message: "Provide either product_id or sku",
          }),
        }],
        isError: true,
      };
    }

    const row = product_id
      ? db.prepare("SELECT * FROM products WHERE id = ?").get(product_id)
      : db.prepare("SELECT * FROM products WHERE sku = ?").get(sku);

    if (!row) {
      return {
        content: [{
          type: "text",
          text: JSON.stringify({
            error: true,
            errorCategory: "not_found",
            isRetryable: false,
            message: `Product not found`,
          }),
        }],
        isError: true,
      };
    }

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

// ── Start the server ───────────────────────────────────
async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
  console.error("MCP Database Server running on stdio");
}

main().catch(console.error);

{
  "mcpServers": {
    "database": {
      "command": "npx",
      "args": ["tsx", "src/index.ts"],
      "cwd": "/path/to/mcp-database-server",
      "env": {
        "DATABASE_PATH": "./store.db"
      }
    }
  }
}

{
  "mcpServers": {
    "database": {
      "command": "npx",
      "args": ["tsx", "/absolute/path/to/mcp-database-server/src/index.ts"],
      "env": {
        "DATABASE_PATH": "/absolute/path/to/store.db"
      }
    }
  }
}

npx @modelcontextprotocol/inspector npx tsx src/index.ts