Skip to content

MCP Diagnostic Server

A read-only MCP server (services/mcp_diagnostics) that lets an AI agent triage the two most common tracker support complaints directly, instead of an engineer running ad hoc queries:

  • A tracker hasn't reported for a few days.
  • A tracker hasn't shown as arrived in a delivery/storage zone, even though the customer knows it has physically arrived.

Diagnosing either usually comes down to two questions: has Apple actually seen the device recently, and is the zone/geofence itself configured where the customer thinks it is. The server has read-only access to the database and, for the "has Apple seen it" question, can make a live call to Apple.

Access

Local dev: an MCP container with direct read credentials in the local docker-compose setup. No access-control problem worth solving there.

Staging/Production: rather than an always-on service (a new public/ALB-fronted endpoint or a persistent bastion — more attack surface than a rarely-used diagnostic tool justifies, for something used on-demand rather than continuously), it runs as a one-off ECS task, started only when needed, reached over an SSM Session Manager port-forward. No open ports, no bastion host, no VPN — access is gated entirely by IAM identity plus the database role's own grants.

This reused infrastructure that already existed in this repo rather than adding new plumbing:

  • infra/modules/ecs/variables.tf's jobs map for one-off task definitions (already used for migrations and seed-users), launched with aws ecs run-task rather than as a long-running service.
  • infra/modules/ecs/main.tf (aws_iam_role_policy.task_exec) already grants the shared ECS task role ssmmessages:CreateControlChannel / OpenDataChannel etc. to every task — exactly what ECS Exec / SSM port forwarding needs, so no IAM change was needed for the transport itself.
  • scripts/open_tracker_ecs_shell.sh's lifecycle (run-task --enable-execute-command, poll until the exec agent is RUNNING, open a session, stop-task in an EXIT trap) was forked into scripts/open_tracker_mcp_tunnel.sh, swapping the final aws ecs execute-command --command /bin/sh step for aws ssm start-session --document-name AWS-StartPortForwardingSession --target ecs:<cluster>_<task-id>_<runtime-id> --parameters portNumber=<mcp-port>,localPortNumber=<local-port>. The agent then points its MCP client at localhost:<local-port>.
  • The existing NAT gateway (infra/modules/network/main.tf) already gives private-subnet tasks the egress needed to reach the SSM endpoints for the port-forwarding data channel.

What was built on top of that:

  1. jobs["mcp-diagnostics"] in both infra/envs/staging/main.tf and infra/envs/prod/main.tf, reusing the existing tracker-services image (just a different command) and the existing ECS security group.
  2. A dedicated read-only tracker_readonly Postgres role, created by alembic/versions/29938722c984_create_mcp_readonly_role.py (SELECT-only, including future tables via ALTER DEFAULT PRIVILEGES), backed by a manually-managed mcp_readonly_password secret (see Manual Bootstrap below — the role can't be created by the app's own migrations and the password is deliberately not Terraform-managed).
  3. scripts/open_tracker_mcp_tunnel.sh — forks open_tracker_ecs_shell.sh, swapping the interactive shell step for aws ssm start-session --document-name AWS-StartPortForwardingSession.
  4. aws_iam_policy.mcp_diagnostics_access in both envs, scoping ecs:RunTask to the mcp-diagnostics task-definition family and ssm:StartSession/ecs:ExecuteCommand to that cluster's tasks only. Not attached to anything by Terraform — attach it to the relevant engineer/CI IAM role/user out of band.

Not done: a self-idle-timeout inside the MCP container as a backstop if a session is killed uncleanly and the script's EXIT trap doesn't fire (Fargate bills while the task is running). Left for later — a cost/hygiene concern, not a security one.

Tools

Grounded in the schema and existing diagnostic code/endpoints:

Lookup

  • find_tracker(query) — resolve a MAC address (with or without separators) or name substring to a tracker_id. Added after actually needing raw DB access mid-investigation to look up a tracker from a MAC address alone - every other tool takes tracker_id, but that's rarely what support starts with.

"Hasn't reported for days"

  • get_tracker_reporting_status(tracker_id) — last_report_received, last_processed_at, last_status_check, current_status, and the same COALESCE(last_report_received, max(location_history.timestamp)) check app/api/routes/trackers/dark.py (GET /trackers/gone-dark) already uses.
  • get_daily_report_counts(tracker_id, days) — wraps the existing GET /trackers/{id}/daily-reports logic; shows whether reports trailed off gradually or stopped dead.
  • check_apple_live(tracker_id) — a live call to Apple via the findmy library, as scripts/check_tracker_activity.py already does, bypassing our DB. This is what separates "Apple genuinely hasn't seen this device" (customer-side) from "Apple has seen it but our pipeline hasn't ingested it yet" (our bug).
  • check_fetcher_health(tracker_id) — is the tracker actually being polled (last_processed_at recency / round-robin position), or stuck behind a distributed lock (scripts/inspect_tracker_locks.py).
  • check_duplicate_advertisement_key(tracker_id) — wraps scripts/diagnose_duplicate_trackers.sql; two trackers sharing a hashed_advertisement_key is a real recurring failure mode.

"Not in the zone we expected"

  • get_current_location(tracker_id) — latest location_history row (lat/lng, confidence, accuracy, timestamp).
  • get_status_history(tracker_id) — status_history transitions.
  • get_geofence_events(tracker_id) — geofence_events entry/exit/dwell records against delivery/storage locations, with confidence.
  • get_zone_config(zone_id) — the delivery/storage location's registered lat/lng and geofence_size_meters, for the case where the zone itself is mis-registered rather than the tracker being lost.
  • list_nearby_zones(tracker_id, radius) — given the tracker's actual location, which zones exist nearby that didn't match. Surfaces "radius too small" or "coordinates off by 200m" directly.
  • find_status_zone_mismatches(limit) — fleet-wide scan for trackers physically inside a delivery/storage geofence (scoped to their own production run, or a global zone) whose current_status hasn't caught up — e.g. sitting inside a storage zone while still IN_TRANSIT. See Known Limitations below for what this turned up.

Bundle

  • get_tracker_overview(tracker_id) — combines reporting status, current location, and status history into the single call that should cover most support triage.

All 12 tools are live in local dev, staging, and (once applied) prod.

Status

  • Local dev: fully working, verified live against real dev data through the actual MCP protocol (all tools except check_apple_live, which needs a real Apple session this sandbox doesn't have).
  • Staging: fully verified end-to-end (2026-07-23) — secret created, tracker_readonly role created on the real DB host, scripts/open_tracker_mcp_tunnel.sh started a real one-off ECS task, opened a real SSM port-forward, and a real MCP client connected through it - all 12 tools present, find_status_zone_mismatches returned real staging data (6 trackers stuck IN_TRANSIT ~50-72m inside a zone named "Test Storage Location #1" - worth checking whether that zone is misregistered as a delivery_locations row when the name suggests storage). Tunnel torn down cleanly, task went RUNNING → DEPROVISIONING → STOPPED via the script's EXIT trap - no orphaned task left billing.
  • Prod: infra defined but not yet applied/bootstrapped. See Next Steps.

Two real infra bugs turned up applying this to staging (not caught by local dev, since its Postgres container runs everything as superuser):

  • scripts/run_staging_migrations.sh used a terraform output -raw >file
  • read -r VAR <file pattern for CLUSTER_ARN/TASK_DEFINITION_ARN/ LOG_GROUP_NAME. terraform output -raw doesn't emit a trailing newline, so read returned non-zero on EOF and set -e killed the script right after the first read - before ever calling aws ecs run-task. Fixed by switching those three to direct command substitution (VAR="$(terraform ...)"), matching the pattern scripts/open_tracker_ecs_shell.sh already used safely.
  • The migration itself then failed: psycopg.errors.InsufficientPrivilege: permission denied to create role. Staging's tracker app role deliberately doesn't have CREATEROLE. Fixed by making role creation a one-time manual step, not part of the migration chain — see Manual Bootstrap below.

check_apple_live mounts the same EFS-backed account.json the fetcher services use (read-only, so it can never race the fetcher's periodic token-refresh writes), reusing the existing Apple session instead of needing its own login. This does mean it shares Apple's rate limits and session state with the production fetcher - "use sparingly, not in a loop", same as local dev.

Manual Bootstrap (staging/prod)

The tracker_readonly role can't be created by the app's own migrations (see above), and its password is deliberately not managed by Terraform - mcp_readonly_password_secret_arn in each env's terraform.tfvars is a plain out-of-band ARN, same pattern as google_client_id_secret_arn. Do this once per environment:

  1. Create the secret with a password of your choosing: 
    aws secretsmanager create-secret \
  --name tracker/staging/mcp-readonly-password \
  --secret-string '<password>' \
  --profile glimpse-staging --region eu-west-2
    If it already exists (e.g. left over from before this section existed, when it was briefly Terraform-managed), update its value instead: aws secretsmanager put-secret-value --secret-id tracker/staging/mcp-readonly-password --secret-string '<password>'. If Terraform still has it in state from that earlier attempt, remove it first so a future apply doesn't destroy it: terraform state rm 'module.secrets.aws_secretsmanager_secret.this["mcp_readonly_password"]' (and the matching ..._version and random_password entries).
  2. Put its ARN into infra/envs/staging/terraform.tfvars as mcp_readonly_password_secret_arn, then terraform apply.
  3. SSM onto the database host (terraform output -raw database_instance_id) and create the role as the Postgres superuser, using the same password: 
    aws ssm start-session --target <instance-id> --profile glimpse-staging --region eu-west-2
sudo -u postgres psql -d tracker

    CREATE ROLE tracker_readonly WITH LOGIN PASSWORD '<same password>';
GRANT CONNECT ON DATABASE tracker TO tracker_readonly;
GRANT USAGE ON SCHEMA public TO tracker_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tracker_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE tracker IN SCHEMA public
  GRANT SELECT ON TABLES TO tracker_readonly;
    (Same SQL build/db/create-readonly-role.sh runs automatically for local dev - just run manually here since the app role can't run it itself.)

Known Limitations

  • No "expected zone" is stored anywhere — not on Tracker, not on ProductionRun. So "didn't arrive where expected" can only be answered as "here's where it actually is and what zones are nearby," not "here's why it missed its target," unless that expectation lives in a client's own system outside this DB.
  • Status/zone containment bug: a tracker can sit inside the correct zone's geofence with current_status still IN_TRANSIT instead of DELIVERED/IN_STORAGE - a status-update pipeline bug, not a missing-expectation problem. find_status_zone_mismatches detects this directly and found 4 real cases in dev data and 6 in staging (see Status above). Tracked as a follow-up in docs/development/TODO.md.

Next Steps

  • Attach aws_iam_policy.mcp_diagnostics_access to whichever engineer/CI role should be allowed to use it - it isn't attached to anything yet.
  • Apply to prod once its secret/role are bootstrapped the same way as staging.