H2-backed MCP snapshot refresh pitfalls

Use this for Traccar/FT360-style MCP servers that read a remote H2 database by copying database.mv.db locally and querying with Java H2 Shell.

Parser pitfall: H2 header rows

H2 Shell output includes a header row like:

ID | NAME | UNIQUEID | ...

If the MCP parser returns that as data, code like int(row[0]) fails with invalid literal for int() with base 10: 'ID'.

Parser must skip header rows:

if cells[0].upper() in {"ID", "DEVICEID", "LATITUDE", "NAME"}:
    continue

Also skip summary rows beginning with ( and separator rows beginning with ---.

Snapshot freshness pitfall

Do not copy the remote H2 DB only when the local file is missing. That causes stale dashboard/MCP data for hours or days. If the tool has its own short response cache, refresh the local DB snapshot on every actual query after the cache expires:

def _query_db(sql):
    db_path = _scp_db()  # always refresh snapshot when query runs
    if db_path is None:
        return []
    ...

A 30-second in-memory cache is enough to avoid excessive SCPs while keeping live tracking data fresh.

Contact freshness vs GPS freshness

For Traccar-like data, do not equate device online or lastupdate with a fresh GPS fix. Traccar can update device contact time when a mobile client sends a heartbeat/notification-token payload without new coordinates.

Expose both concepts separately:

Field	Meaning
`last_contact_time` / `last_contact_age_minutes`	Device/server contact freshness (`tc_devices.lastupdate`)
`gps_fix_time` / `gps_age_minutes`	Real GPS fix age (`tc_positions.fixtime`, fallback to `devicetime`)
`is_contact_fresh`	`last_contact_age_minutes <= threshold`
`is_location_fresh`	`gps_age_minutes <= threshold`

A valid OsmAnd GPS payload looks like:

id=612982&lat=10.417...&lon=-75.551...&timestamp=...&accuracy=...

A heartbeat/notification-token payload may look like:

id=612982&notificationToken=...

The latter can cause Traccar to keep the device online while reusing the last known coordinates. Dashboard wording should say contact live / GPS stale or equivalent, not live location.

Static dashboard export pattern

For a static ops page, generate a JSON file from the MCP/backend on a short no_agent=True cron:

/root/.hermes/scripts/ft360-export.py -> /var/www/ops/data/ft360-devices.json
schedule: every 1m
no_agent: true

If the exporter imports MCP code that depends on a venv (for example FastMCP in /opt/ops-portal/venv), either run the exporter with the venv Python or add that venv's site-packages before importing:

import site
site.addsitedir('/opt/ops-portal/venv/lib/python3.13/site-packages')

The page should fetch the JSON with cache: 'no-store' and display contact age separately from GPS fix age.

Verification

After patching, restart the MCP service and call the MCP tool, not just hermes mcp test. hermes mcp test verifies tool discovery only; a real get_devices call verifies parser and freshness.

3.1 KiB Raw Permalink Blame History