faircomteam/faircom-mcp repository overview

PROJECT_VERSION="$(make version)"

┌─────────────────────────────────────────────────────────────┐
│  Your AI Assistant (Claude, Copilot, etc.)                  │
└────────────────────┬────────────────────────────────────────┘
                     │ MCP Protocol
                     │ (HTTP + JSON-RPC)
┌────────────────────▼────────────────────────────────────────┐
│  FairCom MCP Server                                         │
│  • Session management                                       │
│  • Write safety enforcement (confirm_write=true)            │
│  • Tool exposure control                                    │
│  • Rate limiting, observability                             │
└────────────────────┬────────────────────────────────────────┘
                     │ FairCom JSON API
                     │ (HTTP REST)
┌────────────────────▼────────────────────────────────────────┐
│  FairCom Database                                           │
│  (Edge, DB, RTG, ISAM, MQ)                                  │
└─────────────────────────────────────────────────────────────┘

# Preview a deletion without changing data
preview = faircom_mcp.sql_execute(
    "DELETE FROM staging_orders WHERE created_at < '2026-01-01'",
    dry_run=True,
)

if preview["would_succeed"]:
    # Only after review, run the real write
    faircom_mcp.sql_execute(
        "DELETE FROM staging_orders WHERE created_at < '2026-01-01'",
        confirm_write=True,
        dry_run=False,
    )

# FairCom MCP exposes:
# sql_query(statement, params?) → fetch data
# list_tables(name_like?) → discover schema
# list_table_columns(table_name) → understand structure

# The AI assistant can:
# 1. List available tables (list_tables)
# 2. Inspect target schema (describe_table)
# 3. Execute transformations (sql_execute with confirm_write=true)
# 4. Validate results (sql_query to spot-check)

# FairCom MCP provides:
# - /metrics → Prometheus-compatible metrics
# - /diagnostics → System health
# - sql_query → Run diagnostic queries
# Combine for full observability loop

# Sandbox the chatbot with:
# FAIRCOM_TOOL_GROUP_ALLOWLIST=metadata,query
# (write tools disabled for read-only workflows)
#
# FAIRCOM_SQL_DENYLIST=DELETE,DROP
# (prevent destructive operations)

# Start FairCom MCP pointing to your FairCom instance
docker run -d --name faircom-mcp \
  -p 8000:8000 \
  -e FAIRCOM_API_BASE_URL=http://faircom-host:8080 \
  -e FAIRCOM_API_USERNAME=ADMIN \
  -e FAIRCOM_API_PASSWORD=ADMIN \
  faircomteam/faircom-mcp:latest --transport http

-e FAIRCOM_API_BASE_URL=http://host.docker.internal:8080

sudo apt-get install -y "./faircom-mcp_${PROJECT_VERSION}_all.deb"
sudo systemctl enable --now faircom-mcp

sudo dnf install -y "./faircom-mcp-${PROJECT_VERSION}-1.noarch.rpm"
sudo systemctl enable --now faircom-mcp

# Health check
curl -fsS http://127.0.0.1:8000/health
# Output: {"status":"ok"}

# List available tables
curl -i -X POST http://127.0.0.1:8000/mcp \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  --data '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {
      "protocolVersion": "2025-03-26",
      "capabilities": {},
      "clientInfo": {"name": "test", "version": "1.0"}
    }
  }' | head -20

# Default current image
docker pull faircomteam/faircom-mcp:latest

# Pin to an immutable release for production
docker pull faircomteam/faircom-mcp:vX.Y.Z

docker run -d --name faircom-mcp \
  -p 8000:8000 \
  -e FAIRCOM_API_BASE_URL=http://faircom-host:8080 \
  -e FAIRCOM_API_USERNAME=ADMIN \
  -e FAIRCOM_API_PASSWORD=ADMIN \
  faircomteam/faircom-mcp:latest --transport http

curl -i -X POST http://127.0.0.1:8000/mcp \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  --data '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {
      "protocolVersion": "2025-03-26",
      "capabilities": {},
      "clientInfo": {"name": "my-client", "version": "1.0"}
    }
  }' 2>&1 | grep -i "mcp-session-id"

# Save the session ID from the response, e.g.: abc123
SESSION_ID="abc123"

curl -X POST http://127.0.0.1:8000/mcp \
  -H "Mcp-Session-Id: $SESSION_ID" \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  --data '{
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/list",
    "params": {}
  }' 2>&1 | grep -A 5 "list_tables"

# Let's examine the "customers" table
curl -X POST http://127.0.0.1:8000/mcp \
  -H "Mcp-Session-Id: $SESSION_ID" \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  --data '{
    "jsonrpc": "2.0",
    "id": 3,
    "method": "tools/call",
    "params": {
      "name": "describe_table",
      "arguments": {"table_name": "customers"}
    }
  }' 2>&1 | tail -20

# Count customers
curl -X POST http://127.0.0.1:8000/mcp \
  -H "Mcp-Session-Id: $SESSION_ID" \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  --data '{
    "jsonrpc": "2.0",
    "id": 4,
    "method": "tools/call",
    "params": {
      "name": "sql_query",
      "arguments": {
        "statement": "SELECT COUNT(*) as total FROM customers"
      }
    }
  }' 2>&1 | tail -20

{
  "mcpServers": {
    "faircom": {
      "type": "http",
      "url": "http://127.0.0.1:8000/mcp"
    }
  }
}

{
  "mcpServers": {
    "faircom": {
      "type": "http",
      "url": "http://127.0.0.1:8000/mcp"
    }
  }
}

# Required: FairCom connectivity
FAIRCOM_API_BASE_URL=https://faircom.example.com:9443
FAIRCOM_API_USERNAME=ADMIN           # or use FAIRCOM_API_TOKEN
FAIRCOM_API_PASSWORD=ADMIN

# Optional: Server binding
FAIRCOM_HTTP_HOST=0.0.0.0
FAIRCOM_HTTP_PORT=8000

# Optional: TLS

## Connector Management

FairCom MCP exposes connector inspection and lifecycle operations for FairCom Edge input and output connectors.

Read-oriented connector tools:

- `list_inputs(payload?)`
- `describe_inputs(payload)` — `payload.inputNames` (non-empty list of strings) is required; call `list_inputs` first to discover names.
- `list_outputs(payload?)`
- `describe_outputs(payload?)`

CamelCase parity aliases are also available for API-name alignment:

- `listInputs(payload?)`
- `describeInputs(payload?)`
- `listOutputs(payload?)`
- `describeOutputs(payload?)`

Write-oriented connector tools:

- `create_input(payload, confirm_write=False, dry_run=False)`
- `alter_input(payload, confirm_write=False, dry_run=False)`
- `delete_input(payload, confirm_write=False, dry_run=False)`
- `create_output(payload, confirm_write=False, dry_run=False)`
- `alter_output(payload, confirm_write=False, dry_run=False)`
- `delete_output(payload, confirm_write=False, dry_run=False)`

CamelCase parity aliases are also available for write operations:

- `createInput(payload, confirm_write=False, dry_run=False)`
- `alterInput(payload, confirm_write=False, dry_run=False)`
- `deleteInput(payload, confirm_write=False, dry_run=False)`
- `createOutput(payload, confirm_write=False, dry_run=False)`
- `alterOutput(payload, confirm_write=False, dry_run=False)`
- `deleteOutput(payload, confirm_write=False, dry_run=False)`

Connector writes follow the same explicit safety model as SQL writes:

1. Use `dry_run=True` first to preview the intended connector change.
2. Review the returned action and target payload.
3. Re-run with `confirm_write=True` to apply the change.

Example preview:

```json
{
  "name": "create_output",
  "arguments": {
    "payload": {
      "outputName": "writeTemperatureToModbus",
      "serviceName": "modbus",
      "tableName": "modbusTableTCP",
      "sourceFields": ["source_payload"],
      "modbusProtocol": "TCP",
      "modbusServer": "127.0.0.1",
      "modbusServerPort": 502
    },
    "dry_run": true
  }
}

{
  "name": "create_output",
  "arguments": {
    "payload": {
      "outputName": "writeTemperatureToModbus",
      "serviceName": "modbus",
      "tableName": "modbusTableTCP",
      "sourceFields": ["source_payload"],
      "modbusProtocol": "TCP",
      "modbusServer": "127.0.0.1",
      "modbusServerPort": 502
    },
    "confirm_write": true
  }
}

{
  "name": "configure_topic",
  "arguments": {
    "payload": {
      "topic": "factory/line-1/mixing_tank/temperature",
      "databaseName": "faircom",
      "tableName": "modbus_mixing_tank_temp"
    },
    "confirm_write": true
  }
}

## Available Tools

| Tool | Purpose | Safety |
|---|---|---|
| `list_tables(name_like?)` | Discover tables | Read-only |
| `describe_table(table_name)` | Get columns, indexes, constraints (falls back to integration table metadata) | Read-only |
| `list_table_columns(table_name)` | Column names and types (works for integration tables too) | Read-only |
| `list_table_indexes(table_name)` | Index details | Read-only |
| `sql_query(statement, params?)` | Execute SELECT (read-only) | Read-only |
| `sql_query_page(statement, params?, page, page_size)` | Paginated SELECT | Read-only |
| `sql_execute(statement, params?, confirm_write, dry_run)` | INSERT/UPDATE/DELETE (requires `confirm_write=true` unless `dry_run=true`) | Write |
| `list_services(payload?)` | List Edge connector services and runtime state | Read-only |
| `manage_service(payload, confirm_write, dry_run)` | Start/stop/restart a connector service | Write |
| `describe_connector_schema(payload?)` | Local payload schema profiles and known-good examples per connector service and direction (input/output) | Read-only |
| `validate_connector_payloads(payload)` | Preflight-validate connector payloads without mutating backend state, including cross-checking `serviceName` against `list_services` | Read-only |
| `get_usage_contract()` | Canonical args, aliases, transport/session guidance, examples | Read-only |
| `runtime_status()` | Health, version, diagnostics | Read-only |
| `capabilities_summary()` | Discover enabled tool groups and policy preset | Read-only |
| `observability_metrics()` | Snapshot of internal runtime metrics | Read-only |
| `observability_audit()` | Snapshot of the write/audit event log | Read-only |
| `observability_health()` | Readiness/liveness state as an MCP tool call | Read-only |
| `list_topics(payload?)` | List MQTT topic names being tracked | Read-only |
| `describe_topics(payload?)` | Describe MQTT topics, including bound table and transform settings | Read-only |
| `configure_topic(payload, confirm_write, dry_run)` | Upsert an MQTT topic binding to an integration table | Write |
| `delete_topic(payload, confirm_write, dry_run)` | Delete an MQTT topic binding | Write |

See [Connector Management](#connector-management) for input/output connector tools, [Integration Tables & Code Packages](#integration-tables--code-packages) for transform pipeline tools, and [MQTT Delivery (MQ Topics)](#mqtt-delivery-mq-topics) for MQTT publish tools.

## Integration Tables & Code Packages

Integration tables capture data landed by an input connector and apply `transformSteps` to it. A transform step's JavaScript logic lives in a separately registered code package; a table then references it by `codeName`. There is no single "transform" object — FairCom splits this across the `hub` API (integration tables) and the `admin` API (code packages), and FairCom MCP routes each tool call to the correct one for you.

Read-oriented tools:

- `list_integration_tables(payload?)` — list integration tables visible to the configured access context.
- `describe_integration_tables(payload)` — describe tables including their `fields` and `transformSteps`. Pass a `tables` array, not a bare `tableName`.
- `list_code_packages(payload?)` — list registered code package names for a database/owner.
- `describe_code_packages(payload)` — describe registered code packages, including source code.

Write-oriented tools (same `dry_run` / `confirm_write` safety model as SQL and connector writes):

- `create_integration_table(payload, confirm_write, dry_run)` — create a table, optionally with `fields` and `transformSteps` in the same call.
- `alter_integration_table(payload, confirm_write, dry_run)` — alter a table's fields, transform steps, or retention policy. The server polls `describe_integration_tables` after the write and reports `mutation_applied` / `mutation_verification` in the response, because FairCom can return success while silently not applying some field or transform-step changes.
- `delete_integration_tables(payload, confirm_write, dry_run)`
- `register_code_package(payload, confirm_write, dry_run)` — create or update a code package (`createCodePackage`/`alterCodePackage`). Accepts `input_fields` (list of field names the transform reads) and `output_field_definitions` (list of `{name, type}` objects the transform writes); both are merged into `metadata.inputFields`/`metadata.outputFieldDefinitions`.
- `clone_code_package(payload, confirm_write, dry_run)` — clone an existing code package under a new name.
- `revert_code_package(payload, confirm_write, dry_run)` — revert a code package to a prior version. There is no delete; re-registering the same `code_name` is how you update it.
- `test_integration_table_transform_steps(payload, confirm_write, dry_run)` — dry-run transform steps against a table. `payload.testTransformScope` is required and validated against the known enum (`allRecords`, `stop`, `firstRecord`, `lastRecord`, `specificRecords`) since FairCom's own error does not list valid values.

Important, field-tested gotchas:

- Declare every target field in `create_integration_table`'s `fields` array up front. Neither the transform nor `alter_integration_table` can reliably add fields to an existing table afterward.
- Put `databaseName` and `ownerName` inside each transform step object, not only at the table's top level, or FairCom rejects the step with a missing-default-database error.
- A `transformStepMethod` of `"javascript"` requires `transformStepService: "v8TransformService"` alongside it.
- Set `input_fields`/`output_field_definitions` on `register_code_package` for `integrationTableTransform` packages. Without `metadata.inputFields`/`metadata.outputFieldDefinitions`, the code package is created successfully but the FairCom Edge Explorer wizard reports "no suitable Integration Table Transform Code Packages" and cannot find it — the Code Editor GUI sets these automatically, but the Code Package API does not. `register_code_package` returns a `warnings` entry naming the missing property when this happens. **Unverified:** the exact shape this tool writes (`metadata.inputFields`/`metadata.outputFieldDefinitions`) has not been confirmed to resolve wizard visibility in all environments — `register_code_package` always includes an `UNVERIFIED` warning when these fields are set as a reminder to check the wizard after registering. If the wizard still can't find the package, compare against the metadata a Code Editor-created package actually has.

## Common AI Client Mistakes (And Fixes)

These are the most common payload issues across Claude, Copilot, ChatGPT, Gemini, and custom agents.

### 1) Wrong key for `sql_query`

Wrong:
```json
{"name":"sql_query","arguments":{"sql":"SELECT COUNT(*) FROM demo_assets"}}

{"name":"sql_query","arguments":{"statement":"SELECT COUNT(*) FROM demo_assets"}}

{"name":"describe_table","arguments":{"table":"demo_assets"}}

{"name":"describe_table","arguments":{"table_name":"demo_assets"}}

{"name":"list_tables","arguments":{"table_like":"demo_%"}}

{"name":"list_tables","arguments":{"name_like":"demo_%"}}

SELECT * FROM demo_assets ORDER BY id DESC LIMIT 25 OFFSET 10

SELECT SKIP 10 TOP 25 * FROM demo_assets ORDER BY id DESC