mcp-federated-data
by baller-coder
README.md
# mcp-federated-data
> **Federated MCP server** that joins relational metadata (MySQL) with timeseries values (InfluxDB) behind a single, LLM-friendly entity layer.
**Languages:** [English](./README.md) · [中文](./README.zh-CN.md)
---
## The problem
When an LLM needs to answer a question like:
> *"show me last-7-day temperature trends for active sensors in zone A"*
it has to traverse **two stores**:
- **Relational metadata** (what sensors exist, where, what kind, what units) — MySQL
- **Timeseries values** (the readings over time) — InfluxDB
Plugging in two **independent** MCP servers forces the LLM to:
1. Query MySQL → find matching entities → extract their ids
2. Push those ids into an InfluxDB query
3. **Stitch the two result sets in its own context**
LLMs reliably stumble at step 3 — especially when the join key is composite (e.g. an InfluxDB tag is the concatenation of two relational fields).
This server collapses all three steps into **one tool call**.
---
## What the LLM sees
Five tools, no SQL or Flux in the prompt:
| Tool | Purpose |
|---|---|
| `list_entities` | Filter business entities by their fields |
| `get_entity` | Look up a single entity by primary key |
| `list_related` | Walk a configured relation between entities |
| `get_entity_timeseries` | **Federated** — metadata + timeseries in one call |
| `compare_timeseries` | Compare 2–20 specific entities over the same window |
Every tool ships with rich JSON-Schema descriptions so the LLM picks the right arguments without prompt tricks.
---
## How it differs from naive two-server setups
| Scenario | Two independent MCP servers | `mcp-federated-data` |
|---|---|---|
| "trends for sensors in zone A" | LLM: query MySQL → extract ids → push into Flux → join in its head | one `get_entity_timeseries` call |
| Composite tag like `{deviceId}.{signalId}` | LLM composes strings in-prompt — error-prone | `composer: "{deviceId}.{signalId}"` in YAML, server does it |
| Metadata ↔ timeseries alignment | LLM does the join, frequently mis-pairs | server joins by configured key |
| Data-volume blow-up | unprotected | enforced max entities + auto-downsampling + per-entity point cap |
| Business semantics for the LLM | raw `CREATE TABLE` only | YAML fields with `description` |
| Audit trail | none | structured per-call audit log |
---
## Quickstart
```bash
git clone https://github.com/baller-coder/mcp-federated-data.git
cd mcp-federated-data
pnpm install
# Sample environment (MySQL + InfluxDB in Docker, with seeded data)
docker compose -f examples/industrial-monitoring/docker-compose.yml up -d
pnpm seed
# Start the MCP server over stdio
pnpm dev -- --config examples/industrial-monitoring/config.yaml
```
Connect from any MCP client (Claude Desktop, Cursor, [mcp-inspector](https://github.com/modelcontextprotocol/inspector)).
### Try it
```jsonc
// list active sites
{
"name": "list_entities",
"arguments": {
"entity": "site",
"filters": [{ "field": "status", "op": "eq", "value": "active" }]
}
}
// list sensors attached to site 1
{
"name": "list_related",
"arguments": {
"source_entity": "site",
"source_id": 1,
"target_entity": "sensor"
}
}
// federated query — metadata + timeseries in ONE call
{
"name": "get_entity_timeseries",
"arguments": {
"entity": "sensor",
"filters": [
{ "field": "site_id", "op": "eq", "value": 1 },
{ "field": "kind", "op": "eq", "value": "temperature" }
],
"time_range": { "start": "-7d" },
"aggregation": { "window": "1h", "fn": "mean" }
}
}
// compare 3 specific sensors over the same window
{
"name": "compare_timeseries",
"arguments": {
"entity": "sensor",
"ids": [101, 201, 301],
"time_range": { "start": "-24h" },
"aggregation": { "window": "10m", "fn": "mean" }
}
}
```
---
## Configuration
The whole server behavior is defined in a single YAML file. Three sections.
### 1. Datasources
```yaml
datasources:
- name: business
type: mysql
host: localhost
port: 3306
database: my_db
username: readonly_user
password: secret
- name: timeseries
type: influxdb
url: http://localhost:8086
token: my-token
org: my_org
bucket: my_bucket
```
### 2. Entities
Each entity binds to a relational table or view, with optional relations and an optional timeseries binding.
```yaml
entities:
- name: site
description: Physical monitoring location.
source:
datasource: business
table: sites
primary_key: id
fields:
- { name: id, type: number }
- { name: name, type: string, description: Display name }
- { name: region, type: string }
- { name: status, type: string, description: "active / inactive / maintenance" }
- name: sensor
description: A sensor attached to a site.
source:
datasource: business
table: sensors
primary_key: id
fields:
- { name: id, type: number }
- { name: site_id, type: number }
- { name: name, type: string }
- { name: kind, type: string, description: "temperature / humidity / voltage / ..." }
- { name: unit, type: string }
relations:
- target: site
type: many-to-one
local_key: site_id
foreign_key: id
timeseries:
datasource: timeseries
measurement: sensor_data
value_field: value
join_key:
local: id
remote_tag: sensor_id
```
### 3. Defaults (guard rails)
```yaml
defaults:
max_entities_per_query: 50
max_points_per_entity: 500
query_timeout_ms: 15000
```
---
## Composite join keys (v0.2 highlight)
When the InfluxDB tag value is a **composite of multiple relational fields** — common in IoT / industrial systems where a tag like `400001240.438000066` encodes `{deviceId}.{signalId}` — declare it like this:
```yaml
timeseries:
measurement: sensor_data
value_field: value
join_key:
local: [device_id, signal_id]
remote_tag: signal_id
composer: "{device_id}.{signal_id}"
```
The server:
1. Pulls metadata from MySQL (the composer's local fields are auto-included in `SELECT`).
2. Composes each row's tag value using the template.
3. Pushes the composed list into the InfluxDB tag filter.
4. Joins results back by the same template.
Single-field bindings (the v0.1 form) keep working unchanged — the server treats them as a one-element composite, so all paths stay uniform.
---
## How it works — the 50-line core
Every federated tool follows the same three steps:
1. **Fetch metadata** — relational query against the business store, with safe-identifier checks and parameterised `WHERE`. Allowed filter fields are restricted to those declared in the entity config.
2. **Fetch timeseries** — pull join-key values from step 1, push them into a tag filter against the timeseries store, with optional aggregation and per-call point cap.
3. **Merge** — group timeseries points by the remote tag value, then stitch each metadata row with its sorted series.
No SQL parser. No cross-store query planner. By design.
---
## Architecture
```
┌──────────────────────────────────┐
│ MCP client (Claude / Cursor) │
└────────────┬─────────────────────┘
│ stdio (JSON-RPC)
▼
┌──────────────────────────────────┐
│ mcp-federated-data │
│ ┌────────────────────────────┐ │
│ │ Tools (5 tools) │ │
│ ├────────────────────────────┤ │
│ │ Entity registry │ │
│ │ Join-key normalizer │ │
│ │ Composer engine │ │
│ │ Guards (limits/timeout) │ │
│ │ Audit logger │ │
│ ├────────────────────────────┤ │
│ │ Datasource adapters │ │
│ └─────────┬────────┬─────────┘ │
└────────────┼────────┼────────────┘
▼ ▼
┌──────┐ ┌──────────┐
│MySQL │ │InfluxDB │
└──────┘ └──────────┘
```
---
## Where this fits
`mcp-federated-data` is **schema-driven**, not domain-specific. It applies anywhere business metadata lives in a relational store and observed values live in a timeseries store:
- IoT device telemetry
- Industrial process monitoring
- Asset performance management
- Building automation
- Energy / power monitoring
- Environmental sensor networks
- Network device monitoring
If your stack is **MySQL + InfluxDB** and you want LLMs to reason over it — this server is for you.
---
## Comparison with related projects
| | mcp-federated-data | mcp-server-mysql | mcp-server-influxdb | Wren AI / Vanna |
|---|:---:|:---:|:---:|:---:|
| Cross-store join | ✅ | ❌ | ❌ | partial (own engine) |
| Composite tag composer | ✅ | n/a | ❌ | ❌ |
| LLM-safe SQL guards | ✅ | varies | n/a | ✅ |
| Schema as semantic layer | ✅ | ❌ | ❌ | ✅ |
| Configuration | YAML | env / args | env / args | dedicated DSL |
| Scope | narrow but deep | thin wrapper | thin wrapper | full BI platform |
---
## Contributing
Issues and PRs welcome. Before opening one:
- For new data-source adapters — open an issue first so we can align on the interface.
- For new tools — match the existing JSON-Schema + audit-log pattern.
- Public APIs must keep backward compatibility within minor versions.
---
## Acknowledgments
- [Model Context Protocol](https://modelcontextprotocol.io) — the standard this server speaks
- [`@modelcontextprotocol/sdk`](https://github.com/modelcontextprotocol/typescript-sdk) — TypeScript reference SDK
- [Cube.dev](https://cube.dev) / [dbt](https://www.getdbt.com/) — prior art on the "semantic layer" idea
- [InfluxDB](https://www.influxdata.com/) and [MySQL](https://www.mysql.com/) communities
---
## License
[MIT](./LICENSE)
This server cannot be deployed
Maintenance
ActivityInactive
ResponsivenessNo issues