fleetanalytics_mcp/README.md

# Fleet Analytics MCP (read-only)

A **read-only MCP server** that lets the decision & analytics team query the Fireside
fleet database (`tracksolid_db` — PostgreSQL 16 + TimescaleDB + PostGIS) directly from
**Claude** — for reporting and decisions, **never edit/delete**.

It exposes a guarded general `SELECT` tool plus schema-introspection tools over the
`reporting.*` (curated analytics layer) and `tracksolid.*` (raw telemetry) schemas,
connecting as a dedicated least-privilege **`analytics_ro`** role. It is hosted on the
same Coolify host as the database (the DB is internal-only and not reachable from a
laptop), and authed with per-analyst Bearer tokens.

> Sibling of the `tracksolid_timescale_grafana_prod` backend (the DB/ingestion stack)
> and the `dashboard_api` read bridge. This repo owns only the analytics MCP server and
> its `analytics_ro` role.

## Read-only is enforced at four layers

1. **Role GRANTs** — `analytics_ro` has only `USAGE`+`SELECT` on `reporting`/`tracksolid`
   and `EXECUTE` on `reporting` functions; no INSERT/UPDATE/DELETE, not the matview owner.
2. **`default_transaction_read_only = on`** — set on the role and on every connection.
3. **Rolled-back transactions** — every query runs in a txn that is rolled back, never committed.
4. **SQL guard** — the `query` tool accepts a single `SELECT`/`WITH` statement only and
   rejects write/DDL keywords (clean errors instead of DB faults).

It deliberately does **not** import the backend's `ts_shared_rev`, so it carries none of
the Tracksolid ingestion secrets — it needs only `DATABASE_URL` + `MCP_AUTH_TOKENS`.

## Tools

| Tool | Purpose |
|---|---|
| `query(sql, max_rows=1000)` | guarded read-only SELECT/WITH; auto-LIMIT; returns `{row_count, truncated, rows}` |
| `list_schemas()` | readable schemas + object counts |
| `list_tables(schema)` | tables + views in a schema |
| `describe_table(schema, table)` | columns, types, nullability, defaults |
| `list_functions(schema='reporting')` | `reporting.fn_*` signatures |
| `sample_table(schema, table, n=20)` | first `n` rows (wrapper over `query`) |

## Layout

```
analytics_mcp.py                 # the MCP server (FastMCP streamable-HTTP; uvicorn target analytics_mcp:app)
Dockerfile                       # Coolify-buildable image (port 8892)
deploy.sh                        # manual host deploy (standalone Traefik bridge) — fallback to Coolify
scripts/analytics_ro_role.sql    # the read-only role DDL
scripts/bootstrap_analytics_ro.sh# host bootstrap: generate pw → apply role SQL
docs/ANALYTICS_MCP.md / .html    # full implementation guide + runbook
```

## Deploy

The DB is internal-only, so the server runs on the **same Coolify host as `timescale_db`**.

**0. Create the read-only role (once, on the host):**
```bash
scp scripts/analytics_ro_role.sql scripts/bootstrap_analytics_ro.sh kianiadee@twala.rahamafresh.com:~/
ssh kianiadee@twala.rahamafresh.com 'bash ~/bootstrap_analytics_ro.sh'   # writes ~/.analytics_ro.pw (0600)
```

**1a. Coolify-managed app (recommended):** create a Coolify application from this repo
(Forgejo `repo.rahamafresh.com/kianiadee/fleetanalytics_mcp.git`), Dockerfile build, app
port `8892`, attach the domain `fleetmcp.rahamafresh.com` (prod) / `fleetmcp.fivetitude.com`
(staging). Set as **secrets**:
- `DATABASE_URL=postgresql://analytics_ro:<pw>@timescale_db:5432/tracksolid_db`
- `MCP_AUTH_TOKENS=alice:<tok>,bob:<tok>` (per-analyst)

Then **connect the app to the network that can reach `timescale_db`** (the tracksolid
stack's network) so the `timescale_db` hostname resolves. Coolify manages the Traefik
labels + TLS from the domain you set.

Optional env (sensible defaults):
- `MCP_MAX_ROWS` (default `10000`) — hard ceiling on rows a query may return.
- `MCP_DNS_REBINDING_PROTECTION` (default `0`/off) — the MCP SDK's localhost Host-header
  guard. It returns `421` behind a reverse proxy, so it's off by default (the service is
  TLS-terminated + Bearer-authed). Set `1` to enforce, with `MCP_ALLOWED_HOSTS`.
- `MCP_ALLOWED_HOSTS` — comma-separated allowlist used only when the guard is on
  (default: the two `fleetmcp.*` domains + localhost).

**1b. Manual host deploy (fallback):** check this repo out on the host and run `deploy.sh`
— it builds the image, derives the read-only `DATABASE_URL` from the running stack, and
runs a standalone Traefik bridge. See the script header.

## Add to Claude (for analysts)

```bash
claude mcp add --transport http fireside-analytics https://fleetmcp.rahamafresh.com \
  --header "Authorization: Bearer <your-token>"
claude mcp list      # → "fireside-analytics: connected"
```
Claude Desktop / claude.ai: add a custom connector with the same URL and
`Authorization: Bearer <your-token>` header.

## Local dev

```bash
uv sync
DATABASE_URL=postgresql://... MCP_AUTH_TOKENS=me:dev uv run uvicorn analytics_mcp:app --port 8892
```

Full reference, security notes, and verification checklist: [`docs/ANALYTICS_MCP.md`](docs/ANALYTICS_MCP.md).