Skip to main content

Expose Schema and Docs as MCP Resources

When you wire an AI agent to flAPI, it can only call tools intelligently if it knows what fields exist, what they mean, and what shape the data is in. Telling the agent "call customer_lookup" is not enough — it also needs to know that c_mktsegment accepts one of five enum values, that c_acctbal is a DOUBLE, and that the table is sourced from TPC-H.

This is what MCP resources are for: read-only, semi-static payloads that an agent fetches once (or on demand) to ground itself before it starts invoking tools.

What You'll Build​

Two MCP resources served by a single flAPI server:

  1. customer_schema — a JSON description of the customer table's columns, types, and descriptions.
  2. customer_data_dictionary — a Markdown reference that explains business meaning, segment definitions, and known gotchas.

An MCP-compatible client (Claude Desktop, Claude Code, a custom agent) will discover both via resources/list, fetch them via resources/read, and use the information to issue better tool calls.

How It Works​

Resources are addressed by a flat URI scheme — flapi://<resource_name> — and returned as either text or base64-encoded blob content. There are no path components; the server resolves the name directly against the mcp-resource.name declared in each YAML file (see src/mcp_route_handlers.cpp).

Prerequisites​

  • flAPI installed (Quickstart)
  • A Parquet file of customer data (we use the TPC-H sample)

Project Layout​

schema-resources/
├── flapi.yaml
├── data/
│   └── customers.parquet
└── sqls/
    ├── customer-common.yaml
    ├── customer-schema-resource.yaml
    ├── customer-schema.sql
    ├── customer-data-dictionary.yaml
    └── customer-data-dictionary.sql

We are not using DuckLake or caching here — resources are typically tiny and either constant or generated from information_schema.

Step-by-Step​

1. Main Configuration​

flapi.yaml:

project-name: schema-resources
project-description: Expose customer schema and dictionary as MCP resources

template:
  path: './sqls'

connections:
  customers-parquet:
    properties:
      path: './data/customers.parquet'

duckdb:
  access_mode: READ_ONLY

mcp:
  enabled: true

Setting mcp.enabled: true is optional — flAPI auto-enables MCP whenever any endpoint declares an mcp-tool, mcp-resource, or mcp-prompt block — but it makes the intent explicit.

2. Shared Connection​

sqls/customer-common.yaml:

# Reusable connection reference used by every resource and tool
connection:
  - customers-parquet

This mirrors the include pattern from the upstream examples/sqls/customers/customer-common.yaml.

3. The Schema Resource​

sqls/customer-schema-resource.yaml:

mcp-resource:
  name: customer_schema
  description: Customer table schema, column types, and field descriptions
  mime-type: application/json

# Resources don't accept request parameters — they're read by URI alone
template-source: customer-schema.sql

{{include:connection from customer-common.yaml}}

# Resources change rarely; light rate limit is plenty
rate-limit:
  enabled: true
  max: 10
  interval: 60

This is the canonical mcp-resource shape: name, description, and mime-type at the top, a template-source pointing at the SQL that generates the body, and an included connection. The schema accepts no parameters, so we omit the request: block entirely.

sqls/customer-schema.sql:

-- Returns one row whose only column is a JSON document describing
-- the customer table. The MCP layer serialises this row as the
-- resource body.
SELECT json_object(
  'table_name', 'customers',
  'source',     'TPC-H customer.parquet',
  'description', 'Customer master records used for lookups and analytics',
  'columns', json_array(
    json_object('name', 'c_custkey',    'type', 'INTEGER',  'description', 'Unique customer key (primary key)'),
    json_object('name', 'c_name',       'type', 'VARCHAR',  'description', 'Customer display name'),
    json_object('name', 'c_address',    'type', 'VARCHAR',  'description', 'Postal address (free-form)'),
    json_object('name', 'c_nationkey',  'type', 'INTEGER',  'description', 'FK into nation dimension'),
    json_object('name', 'c_phone',      'type', 'VARCHAR',  'description', 'Phone number (free-form formatting)'),
    json_object('name', 'c_acctbal',    'type', 'DOUBLE',   'description', 'Account balance in account currency'),
    json_object('name', 'c_mktsegment', 'type', 'VARCHAR',  'description', 'One of: AUTOMOBILE, BUILDING, FURNITURE, HOUSEHOLD, MACHINERY'),
    json_object('name', 'c_comment',    'type', 'VARCHAR',  'description', 'Free-form notes')
  )
) AS schema_definition;

The result has a single column and a single row. flAPI's MCP layer takes that row and emits it inside the contents[0].text field of the resources/read response.

4. The Data Dictionary Resource​

The second resource shows that a resource is just "text addressed by a URI" — it doesn't have to be JSON.

sqls/customer-data-dictionary.yaml:

mcp-resource:
  name: customer_data_dictionary
  description: Business glossary and segment definitions for the customer dataset
  mime-type: text/markdown

template-source: customer-data-dictionary.sql

{{include:connection from customer-common.yaml}}

rate-limit:
  enabled: true
  max: 10
  interval: 60

sqls/customer-data-dictionary.sql:

-- Inline Markdown reference. The body is just a string column.
SELECT '
# Customer Data Dictionary

## Market Segments (`c_mktsegment`)

| Code        | Meaning                              |
|-------------|--------------------------------------|
| AUTOMOBILE  | Auto OEMs and dealers                |
| BUILDING    | Construction, real estate developers |
| FURNITURE   | Home and office furnishing retail    |
| HOUSEHOLD   | Consumer goods, supermarkets         |
| MACHINERY   | Industrial machinery customers       |

## Known Gotchas

- `c_acctbal` may be negative (post-paid customers).
- `c_phone` is not normalised — do not match by raw string.
- `c_address` includes line breaks; strip them before display.
' AS markdown_body;

The MIME type is text/markdown, so a client that knows how to render Markdown can do so directly. An LLM reading the body as plain text also gets the same information.

Important: URIs are flat​

flAPI's resource URI scheme has no path component. Both resources above are accessible only at:

  • flapi://customer_schema
  • flapi://customer_data_dictionary

There is no flapi://docs/customer style URI — the substring after flapi:// is matched literally against an mcp-resource.name (see src/mcp_route_handlers.cpp:955). If you want to parameterise the content, expose an MCP tool instead — tools accept structured arguments, resources do not.

Running the Server​

$ ./flapi -c flapi.yaml

ok  Loaded 2 MCP resources
ok  MCP server ready at POST /mcp/jsonrpc
ok  Server listening on :8080

End-to-End JSON-RPC Walkthrough​

The MCP server speaks JSON-RPC 2.0 over POST /mcp/jsonrpc. See MCP Protocol Reference for the full transport spec.

1. Initialize the session​

$ curl -sS -i -X POST http://localhost:8080/mcp/jsonrpc \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {
      "protocolVersion": "2025-11-25",
      "clientInfo": {"name": "curl-demo", "version": "1.0.0"}
    }
  }'

HTTP/1.1 200 OK
Mcp-Session-Id: 7a1c-...-9f88
Content-Type: application/json

{"jsonrpc":"2.0","id":1,"result":{"protocolVersion":"2025-11-25","capabilities":{...},"serverInfo":{"name":"flapi-mcp-server","version":"0.3.0"}}}

Capture the session id and reuse it on every subsequent request:

$ SID=7a1c-...-9f88

2. Discover resources​

$ curl -sS -X POST http://localhost:8080/mcp/jsonrpc \
  -H "Content-Type: application/json" \
  -H "Mcp-Session-Id: $SID" \
  -d '{"jsonrpc":"2.0","id":2,"method":"resources/list","params":{}}' | jq
{
  "jsonrpc": "2.0",
  "id": 2,
  "result": {
    "resources": [
      {
        "name": "customer_schema",
        "description": "Customer table schema, column types, and field descriptions",
        "mimeType": "application/json",
        "uri": "flapi://customer_schema"
      },
      {
        "name": "customer_data_dictionary",
        "description": "Business glossary and segment definitions for the customer dataset",
        "mimeType": "text/markdown",
        "uri": "flapi://customer_data_dictionary"
      }
    ]
  }
}

3. Read the schema​

$ curl -sS -X POST http://localhost:8080/mcp/jsonrpc \
  -H "Content-Type: application/json" \
  -H "Mcp-Session-Id: $SID" \
  -d '{
    "jsonrpc": "2.0",
    "id": 3,
    "method": "resources/read",
    "params": { "uri": "flapi://customer_schema" }
  }' | jq
{
  "jsonrpc": "2.0",
  "id": 3,
  "result": {
    "contents": [
      {
        "uri": "flapi://customer_schema",
        "mimeType": "application/json",
        "text": "{\"table_name\":\"customers\",\"source\":\"TPC-H customer.parquet\",\"columns\":[...]}"
      }
    ]
  }
}

The text field is a JSON string (note the escaped quotes) — clients are expected to JSON-parse it themselves before display.

4. Read the data dictionary​

$ curl -sS -X POST http://localhost:8080/mcp/jsonrpc \
  -H "Content-Type: application/json" \
  -H "Mcp-Session-Id: $SID" \
  -d '{
    "jsonrpc": "2.0",
    "id": 4,
    "method": "resources/read",
    "params": { "uri": "flapi://customer_data_dictionary" }
  }' | jq -r '.result.contents[0].text'
# Customer Data Dictionary

## Market Segments (`c_mktsegment`)
...

The agent now has both a machine-readable schema and a human-readable glossary. It can use the schema to validate tool arguments before calling customer_lookup, and the dictionary to write better natural-language answers.

When Resources Beat Tools​

Resources and tools overlap — both can return JSON, both are described to the agent at startup. Reach for a resource when the answer is essentially read-only reference material:

Choose a resource when…Choose a tool when…
The content is static or rarely changesThe content depends on user input
The agent should read it once and cache the resultEvery call needs fresh data
There are no meaningful argumentsThe call accepts filters, IDs, dates, etc.
You want it to appear in resources/list for context loadingYou want it in tools/list for invocation

A useful rule of thumb: if the agent would ask "what's available?" before calling anything, that "what's available?" answer belongs in a resource. Catalogues, schemas, enum definitions, policy documents, sample payloads, and onboarding instructions all fit naturally.

For everything else — anything that filters, paginates, or writes — define an mcp-tool instead. See MCP Overview for the side-by-side YAML shapes.

Securing Sensitive Resources​

By default, resources are unauthenticated — the same as resources/list. If a resource exposes sensitive metadata (internal column names, regulated schemas), attach the same auth and rate-limit blocks you would on a tool:

mcp-resource:
  name: pii_schema
  description: PII column inventory (restricted)
  mime-type: application/json

template-source: pii-schema.sql

{{include:connection from customer-common.yaml}}
{{include:auth from customer-common.yaml}}

rate-limit:
  enabled: true
  max: 5
  interval: 60

The auth block resolves to the same basic/JWT/bearer schemes documented in Authentication.

See Also​

🍪 Cookie Settings