Skip to main content

Model Context Protocol (MCP) Integration

flAPI has built-in support for the Model Context Protocol (MCP)—a standard way to expose structured tools that AI agents like Claude and GPT-4 can call. With MCP, your SQL templates become AI-accessible tools with zero duplicate work.

What is MCP?​

The Model Context Protocol is a standardized interface that allows AI agents to:

  • Discover available tools
  • Understand what each tool does
  • Call tools with structured parameters
  • Receive structured responses

Think of MCP as OpenAPI/Swagger, but designed specifically for AI agents instead of human developers.

Why MCP + flAPI?​

Traditional approaches require building separate integrations:

  • REST API for applications
  • Custom tool definitions for AI agents
  • Duplicate validation logic
  • Separate documentation

With flAPI's MCP support:

  • ✅ One configuration creates both REST and MCP interfaces
  • ✅ Same validation, same security, same data
  • ✅ AI agents get structured access to your enterprise data
  • ✅ No duplicate work

Quick Example​

Define Once, Use Twice​

# sqls/campaign-stats.yaml
url-path: /campaign-stats

# This makes it an MCP tool too!
mcp-tool:
  name: get_campaign_performance
  description: |
    Retrieves marketing campaign performance metrics by country.
    Returns click counts, revenue, and conversion rates.

request:
  - field-name: country
    field-in: query
    description: Two-letter country code (e.g., US, DE, FR)
    required: false
    validators:
      - type: string
        pattern: "^[A-Z]{2}$"

template-source: campaign-stats.sql
connection:
  - bigquery-marketing

The SQL Template​

-- sqls/campaign-stats.sql
SELECT 
  campaign_type,
  country,
  SUM(clicks) as total_clicks,
  SUM(revenue) as total_revenue,
  SUM(conversions) as total_conversions,
  ROUND(SUM(revenue) / SUM(clicks), 2) as revenue_per_click
FROM marketing_campaigns
WHERE active = true
{{#params.country}}
  AND country = '{{{params.country}}}'
{{/params.country}}
GROUP BY 1, 2
ORDER BY total_revenue DESC;

Usage: REST API​

curl http://localhost:8080/campaign-stats?country=US
{
  "data": [
    {
      "campaign_type": "social",
      "country": "US",
      "total_clicks": 150000,
      "total_revenue": 45000.00,
      "total_conversions": 1200,
      "revenue_per_click": 0.30
    }
  ]
}

Usage: AI Agent (MCP)​

// AI agent discovers tool
{
  "name": "get_campaign_performance",
  "description": "Retrieves marketing campaign performance metrics by country...",
  "parameters": {
    "country": {
      "type": "string",
      "description": "Two-letter country code (e.g., US, DE, FR)",
      "required": false,
      "pattern": "^[A-Z]{2}$"
    }
  }
}

// AI agent calls tool
{
  "tool": "get_campaign_performance",
  "parameters": {
    "country": "US"
  }
}

// Agent receives structured data
// (same as REST response)

The AI agent can now reason about campaign performance and make data-driven recommendations!

MCP Configuration Options​

Basic Tool Definition​

mcp-tool:
  name: tool_name
  description: What this tool does

With Examples (Helps AI Understand)​

mcp-tool:
  name: get_customer_orders
  description: Retrieve all orders for a specific customer
  examples:
    - description: Get orders for customer 12345
      params:
        customer_id: "12345"
    - description: Get recent orders from last 30 days
      params:
        customer_id: "12345"
        days: 30

Output Schema (Optional)​

mcp-tool:
  name: get_sales_summary
  description: Daily sales summary
  output_schema:
    type: object
    properties:
      date:
        type: string
        format: date
      total_sales:
        type: number
      order_count:
        type: integer

Real-World Use Cases​

1. Customer Support Agent​

Scenario: AI support agent needs to look up customer information

# Customer lookup tool
url-path: /customers/lookup
mcp-tool:
  name: lookup_customer
  description: Find customer details by email, phone, or ID
  
request:
  - field-name: email
    field-in: query
    description: Customer email address
  - field-name: phone
    field-in: query
    description: Customer phone number
  - field-name: customer_id
    field-in: query
    description: Customer ID

Agent Conversation:

User: "I need help with my order, my email is john@example.com"

Agent: [calls lookup_customer with email=john@example.com]
Agent: "Hi John! I found your account. I see you have 3 orders..."

2. Sales Intelligence Agent​

Scenario: Sales AI that analyzes opportunities

# Opportunity analysis
url-path: /sales/opportunities
mcp-tool:
  name: analyze_sales_pipeline
  description: Get sales opportunities with revenue forecasts
  
request:
  - field-name: stage
    description: Pipeline stage (prospecting, negotiation, closing)
  - field-name: rep
    description: Sales rep name
  - field-name: min_value
    description: Minimum deal value

Agent Usage:

Sales Manager: "What deals over $100k are in negotiation?"

Agent: [calls analyze_sales_pipeline with stage=negotiation, min_value=100000]
Agent: "There are 12 deals over $100k in negotiation, totaling $2.4M..."

3. Data Analysis Agent​

Scenario: Claude Desktop analyzing business metrics

# Revenue analytics
url-path: /analytics/revenue
mcp-tool:
  name: get_revenue_breakdown
  description: Revenue analysis by product, region, and time period
  
request:
  - field-name: product
    description: Product name or ID
  - field-name: region
    description: Geographic region
  - field-name: period
    description: Time period (week, month, quarter, year)

Analyst Workflow:

Analyst (to Claude): "Compare Q4 revenue by region vs last year"

Claude: [calls get_revenue_breakdown multiple times with different params]
Claude: "Here's the comparison: North America is up 23%, Europe is..."
[Generates charts and insights]

Connecting AI Agents​

Claude Desktop​

  1. Configure MCP Server

Create ~/Library/Application Support/Claude/claude_desktop_config.json:

{
  "mcpServers": {
    "flapi": {
      "command": "curl",
      "args": ["http://localhost:8080/mcp/tools"]
    }
  }
}

  1. Restart Claude Desktop

  2. Tools Appear Automatically

Claude can now see and call all your flAPI endpoints marked with mcp-tool.

Custom Integration​

# Python example
import requests

# Discover available tools
tools = requests.get("http://localhost:8080/mcp/tools").json()

# Call a tool
response = requests.post(
    "http://localhost:8080/mcp/call",
    json={
        "tool": "get_campaign_performance",
        "parameters": {"country": "US"}
    }
)

data = response.json()

OpenAI Function Calling​

flAPI can also generate OpenAI-compatible function definitions:

import openai

# Get function definitions from flAPI
functions = requests.get("http://localhost:8080/openai/functions").json()

# Use with OpenAI
response = openai.ChatCompletion.create(
    model="gpt-4",
    messages=[{"role": "user", "content": "What are our best campaigns?"}],
    functions=functions
)

Security Considerations​

Authentication​

MCP tools respect the same authentication as REST endpoints:

url-path: /sensitive-data
mcp-tool:
  name: get_sensitive_data
  
authentication:
  type: jwt
  required: true

# AI agent must provide valid JWT token

Row-Level Security​

Use the same SQL templates with user context:

SELECT * FROM customer_data
WHERE 1=1
{{#context.user.roles.admin}}
  -- Admins see all
{{/context.user.roles.admin}}
{{^context.user.roles.admin}}
  -- Regular users see only their region
  AND region = '{{{context.user.region}}}'
{{/context.user.roles.admin}}

The AI agent inherits the permissions of the authenticated user.

Rate Limiting​

rate-limit:
  enabled: true
  max: 100  # AI agents can make 100 calls per interval
  interval: 60

Best Practices​

1. Write Clear Descriptions​

# ❌ Bad
mcp-tool:
  name: get_data
  description: Gets data

# ✅ Good
mcp-tool:
  name: get_customer_orders
  description: |
    Retrieves all orders for a specific customer, including order status,
    items, and total value. Optionally filter by date range.

2. Provide Examples​

mcp-tool:
  name: search_products
  description: Search product catalog
  examples:
    - description: Search for laptops under $1000
      params:
        query: "laptop"
        max_price: 1000
    - description: Find products in electronics category
      params:
        category: "electronics"

3. Use Meaningful Names​

# Follow naming conventions
mcp-tool:
  name: get_customer_by_id      # ✅ Clear action + resource
  name: search_products          # ✅ Clear verb
  name: calculate_revenue        # ✅ Descriptive
  
  name: endpoint1                # ❌ Not descriptive
  name: data                     # ❌ Too vague

4. Document Parameters​

request:
  - field-name: customer_id
    description: | 
      Unique customer identifier. Can be found in the customer 
      profile or order confirmation email.
    required: true
    example: "CUST-12345"

5. Handle Edge Cases​

SELECT 
  COALESCE(SUM(revenue), 0) as total_revenue,
  COALESCE(COUNT(*), 0) as order_count
FROM orders
WHERE customer_id = '{{{params.customer_id}}}'
  AND date >= CURRENT_DATE - INTERVAL '{{params.days|30}} days';

Return meaningful results even when no data exists.

Debugging MCP Tools​

List Available Tools​

curl http://localhost:8080/mcp/tools | jq

Test Tool Directly​

curl -X POST http://localhost:8080/mcp/call \
  -H "Content-Type: application/json" \
  -d '{
    "tool": "get_campaign_performance",
    "parameters": {"country": "US"}
  }'

Check Tool Schema​

curl http://localhost:8080/mcp/tools/get_campaign_performance | jq

Next Steps​

Need help building AI-powered data tools? Check out our professional services.

🍪 Cookie Settings