Read-only Analytics MCP Server

Implementation guide · standalone repo fleetanalytics_mcp, hosted on the tracksolid_db Coolify host · 2026-06-16 · built — pending deploy

1. Purpose & context

The decision & analytics team needs to pull fleet reporting data (fuel, utilisation, driver behaviour, INC tickets, raw telemetry) from tracksolid_db to make decisions — read-only, never edit/delete. The only programmatic surface today is the dashboard_api FastAPI bridge with a fixed set of /analytics/* / /webhook/* endpoints — too rigid for ad-hoc analysis.

This adds a hosted, read-only MCP server that lets analysts query the database directly from Claude: a guarded general SELECT tool plus schema-introspection tools, pointed at the existing PostgreSQL 16 + TimescaleDB + PostGIS database through a new least-privilege analytics_ro role.

The DB is internal-only (DATABASE_URL → timescale_db:5432 on the Docker network, not reachable from a laptop), so the server is hosted on the same Coolify host as the DB. It ships as its own repo with its own Dockerfile (Coolify-buildable) and joins the network that can reach timescale_db; a deploy.sh manual fallback mirrors the proven dashboard_api bridge pattern.

Where this sits

Ports in use: 8890 prod dashboard_api · 8891 staging dashboard_api · 8892 analytics_mcp.

2. Repo contents

3. Step 1 — the analytics_ro role

Modelled on scripts/dashboard_ro_role.sql. Run as the postgres superuser (it does CREATE ROLE), with the password supplied as psql var :'ro_pw' — no secret in the repo.

scripts/analytics_ro_role.sql

-- read-only LOGIN role for the analytics MCP server. Apply via bootstrap_analytics_ro.sh.
\set ON_ERROR_STOP on
DO $role$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'analytics_ro') THEN
    CREATE ROLE analytics_ro LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
  END IF; END $role$;
ALTER ROLE analytics_ro WITH LOGIN PASSWORD :'ro_pw';

GRANT CONNECT ON DATABASE tracksolid_db TO analytics_ro;
GRANT USAGE   ON SCHEMA reporting, tracksolid TO analytics_ro;
GRANT SELECT  ON ALL TABLES IN SCHEMA reporting  TO analytics_ro;  -- tables + views
GRANT SELECT  ON ALL TABLES IN SCHEMA tracksolid TO analytics_ro;
GRANT SELECT  ON reporting.v_trips TO analytics_ro;            -- matview (not in ALL TABLES)
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA reporting TO analytics_ro;
-- future objects auto-grant
ALTER DEFAULT PRIVILEGES FOR ROLE tracksolid_owner IN SCHEMA reporting  GRANT SELECT  ON TABLES TO analytics_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE tracksolid_owner IN SCHEMA tracksolid GRANT SELECT  ON TABLES TO analytics_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE tracksolid_owner IN SCHEMA reporting  GRANT EXECUTE ON FUNCTIONS TO analytics_ro;
-- extra hardening over dashboard_ro: this role serves ad-hoc HUMAN queries
ALTER ROLE analytics_ro SET default_transaction_read_only = on;
ALTER ROLE analytics_ro SET statement_timeout = '30s';
ALTER ROLE analytics_ro SET idle_in_transaction_session_timeout = '60s';

scripts/bootstrap_analytics_ro.sh

Clone of bootstrap_dashboard_ro.sh — generates ~/.analytics_ro.pw (0600) on first run, applies the SQL via docker exec … psql -v ro_pw=…. The password is never printed and never leaves the host.

4. The MCP server (analytics_mcp.py)

FastMCP streamable-HTTP server, served by uvicorn (target analytics_mcp:app). It uses its own read-only psycopg2 pool and a small local logger — it deliberately does not import the backend's ts_shared_rev (that module eagerly requires the Tracksolid ingestion secrets, which this read-only server has no business holding). Tools exposed:

The core guard + connection logic:

# read-only pool: force read-only + statement timeout at connection level (belt + braces)
_pool = psycopg2.pool.ThreadedConnectionPool(1, 8, DATABASE_URL,
    options="-c default_transaction_read_only=on -c statement_timeout=30000")

@contextmanager
def _ro_conn():                              # txn is ALWAYS rolled back — never commits
    conn = _pool.getconn()
    try:
        conn.set_session(readonly=True, autocommit=False)
        yield conn
    finally:
        conn.rollback(); _pool.putconn(conn)

def _guard(sql):                              # single SELECT/WITH, no write/DDL keywords
    stmt = _strip_comments(sql)
    parts = [p for p in stmt.split(";") if p.strip()]
    if len(parts) != 1: raise ValueError("Only a single statement is allowed.")
    stmt = parts[0].strip()
    if not re.match(r"^(select|with)\b", stmt, re.I): raise ValueError("Only SELECT/WITH allowed.")
    if _FORBIDDEN.search(stmt): raise ValueError("Forbidden (write/DDL) keyword.")
    return stmt

Auth is a Starlette BaseHTTPMiddleware that requires Authorization: Bearer <token>. Tokens come from env MCP_AUTH_TOKENS="alice:tok1,bob:tok2" (per-analyst → revocable + attributable in logs); /healthz is exempt. The app is mounted via app = mcp.streamable_http_app(), then app.add_middleware(BearerAuth) and app.add_route("/healthz", …) (Starlette exposes add_route, not a Flask-style @app.route decorator — verified against the installed mcp).

5. Packaging — Dockerfile + pyproject.toml

Self-contained: pyproject.toml declares the deps (mcp[cli], psycopg2-binary, uvicorn[standard]) and the Dockerfile builds a slim image running uvicorn analytics_mcp:app on port 8892. The project is a flat single module, so [tool.uv] package = false and the Dockerfile installs dependencies only (uv sync --no-dev --no-install-project) — no dependency on the backend image.

6. Deploy

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

Recommended — Coolify-managed app. Create a Coolify app from this repo, Dockerfile build, app port 8892, domain fleetmcp.rahamafresh.com (prod) / fleetmcp.fivetitude.com (staging). Set secrets DATABASE_URL=postgresql://analytics_ro:<pw>@timescale_db:5432/tracksolid_db and MCP_AUTH_TOKENS=alice:<tok>,bob:<tok>, then connect the app to the network that can reach timescale_db so the hostname resolves. Coolify manages Traefik + TLS from the domain; auto-deploys on push via the Forgejo webhook.

Fallback — deploy.sh. Check the repo out on the host and run it: it builds the image, resolves the DB network + DSN from the running stack, swaps in the analytics_ro credentials, and runs a standalone Traefik bridge.

cd ~/fleetanalytics_mcp && git pull
MCP_AUTH_TOKENS="alice:$(openssl rand -hex 16)" bash deploy.sh

7. Deploy runbook (ordered)

1. Role (once): scp the role SQL + bootstrap to twala.rahamafresh.com, run bootstrap_analytics_ro.sh (writes ~/.analytics_ro.pw).
2. App: point Coolify at this repo (§6) or run deploy.sh on the host. Record each analyst's token (shown once).
3. Network: ensure the MCP container shares a Docker network with timescale_db so the DSN host resolves.
4. DNS/Traefik: ensure fleetmcp.* resolves to the host; Coolify/Traefik issues the cert.

8. Add to Claude (for analysts)

# Claude Code
claude mcp add --transport http fireside-analytics https://fleetmcp.fivetitude.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 an Authorization: Bearer <your-token> header. Example prompts: "list the schemas", "describe reporting.v_daily_summary", "top 10 cost centres by distance in the last 30 days".

9. Verification checklist

• psql -U analytics_ro … "SELECT count(*) FROM reporting.v_daily_summary" succeeds.
• psql -U analytics_ro … "CREATE TABLE x(i int)" fails (permission denied) — proves read-only.
• the image builds (docker build . or Coolify build); analytics_mcp is Up; the container can reach timescale_db.
• DATABASE_URL shows analytics_ro (pw masked); curl localhost:8892/healthz returns {"ok":true,…}.
• claude mcp list shows connected; list_schemas / describe_table / a real query return data.
• query("UPDATE reporting.refresh_log …") is rejected by the guard.
• A request with a missing/bad bearer token returns 401.
• docker logs analytics_mcp shows one audit line per query (caller, SQL, rows, ms).

10. Security notes

• Four read-only layers: role GRANTs · default_transaction_read_only=on (role + connection) · rolled-back txn · SQL keyword guard.
• Least privilege: analytics_ro only has USAGE+SELECT on reporting/tracksolid and EXECUTE on reporting functions.
• Per-analyst tokens make access revocable and queries attributable; rotate via MCP_AUTH_TOKENS + redeploy (recreate).
• Resource guards: statement_timeout=30s, idle-txn timeout, row cap (1000 default / 10000 ceiling).
• Future: swap static Bearer for OAuth if the team scales; add a column deny-list if PII lives in tracksolid.*.

Companion file: docs/ANALYTICS_MCP.md (full source for all four new files).