Fixes a 25.x global memory-tracker drift where the tracker pinned at the
max-memory cap (RSS far below it), causing the OvercommitTracker to kill every
query (map/stats/neighbors all 500ing). Deployed in-place on prod over the
existing data dir after a cold backup.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The /api/chat endpoint queried the meshcore_public_channel_messages
VIEW, which does GROUP BY payload over all payload_type=5 packets.
Filtering its output on ingest_timestamp/channel_hash can't push below
the GROUP BY (they're max(...)/derived-from-grouped-payload), so every
call re-aggregated the entire history (~8M rows / 1.2 GiB / ~700ms),
ignoring the ingest_timestamp primary key.
Replace the view reference with an inline subquery
(publicChannelMessagesSubquery) that pushes the time/channel filters
into the inner meshcore_packets scan, so partition + primary-key
pruning applies. Region filtering stays on the outer query since
origin_path_info only exists post-aggregation. Same change to the chat
streaming poller.
Verified on prod: identical output, 8.06M->114K rows read, ~700ms->28ms.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- Point the seattle region at the letsmesh broker (wss://mqtt-us-v1.letsmesh.net:443,
topic meshcore/SEA) where Seattle traffic now lives.
- Fix a pre-existing bug in the path-edge extraction: `path` is a hex string of
1-byte hop prefixes, so use substring(path, 2*i-1, 2) instead of
hex(substring(path, i, 1)) (which re-hexed a single hex char and never matched
the 2-char repeater prefixes -> path edges were always empty). Seattle now yields
path edges again.
Verified on a full prod snapshot: the MV-backed "show all neighbors" query drops
from ~1.6s / 145M rows / 11.8 GiB to ~1ms / 108 rows / 3.8 KiB.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The two slow neighbor queries are converted to read precomputed tables that an
hourly REFRESH EVERY 1 HOUR materialized view maintains, instead of re-aggregating
meshcore_packets per request:
- meshcore_all_neighbor_edges: the global per-region edge graph (direct path_len=0
adverts + repeater-prefix path edges) with endpoint details. getAllNodeNeighbors
now filters it by region + bbox + lastSeen + has_location.
- meshcore_node_direct_neighbors: per-node direct adjacency (both directions) with
neighbor details. getMeshcoreNodeNeighbors now filters it by node_public_key.
Also add the meshcore/SEA topic to the seattle region. Validated on a clean local
stack: migration 001->003 applies, both refreshable MVs create + refresh + populate.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The readonly profile's max_rows_to_read / max_bytes_to_read (500MB) is exceeded by
the map/stats views, which scan the full (growing) meshcore_packets table -> the web
app failed with TOO_MANY_BYTES. Remove the read-size caps; readonly=1, allow_ddl=0,
max_memory_usage and max_execution_time remain the guardrails.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
ClickHouse's internal diagnostics grew unbounded (text_log at Trace level and the
1s query profiler -> trace_log accumulated ~160G over months). Add short TTLs to
all system *_log tables, cap text_log at warning level, and disable the query
profiler in both profiles so trace_log stays empty.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Pin the bundled service images to known versions for reproducible releases and
safe in-place reuse of an existing data dir (matching the production deployment).
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Add the MeshCore dashboard (exported from prod) as a provisioned file
dashboard, with a file provider config. Pin the ClickHouse datasource
uid to "clickhouse" so the dashboard's panel datasource references
resolve at provision time.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Bundle Grafana (127.0.0.1:3000) with the grafana-clickhouse-datasource plugin
and an auto-provisioned ClickHouse datasource using the read-only user. Adds
GRAFANA_ADMIN_PASSWORD to .env.example. Verified: datasource health returns OK.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Module is now github.com/ajvpot/meshexplorer/ingest (the code lives under
ingest/ in the meshexplorer repo), updated from the old standalone
clickhouse-meshingest path. build/vet/test pass.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
- Add a deploy-focused root README; update the web app README (meshcore-only,
point Docker usage at the unified root compose).
- Fix the migration runner: set the goose clickhouse dialect (it defaulted to
postgres and failed to create its version table). Migrations now apply cleanly.
- Remove the unused meshcore decrypt UDF (meshcore_try_decrypt was never called
by any view/query/code) and simplify the ClickHouse image to a single stage.
Verified end-to-end: `docker compose up` brings up clickhouse -> migrate ->
meshcoreingest + meshexplorer; live ingestion from the real MQTT brokers lands
packets in ClickHouse and the web API serves decoded meshcore nodes via the
readonly user.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Single root compose brings up the whole stack on one internal network:
clickhouse (healthchecked) -> migrate (one-shot) -> meshcoreingest + meshexplorer,
with the discord-bot behind a "bot" profile. Web app/bot connect as the readonly
ClickHouse user; ingest/migrate use the default user. Named volume replaces the
host /tank path. .env.example documents every variable with placeholders; root
.gitignore keeps real .env out of git. Drops the per-project compose files.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Meshtastic was UI-filtering only (no meshtastic data backend). Drop it as a
node type/option, and simplify the map marker/cluster/popup rendering now that
every node is meshcore. Update product copy to MeshCore-only. The nodeTypes
query plumbing stays (the unified view's type is always 'meshcore').
Production build passes.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Vendor the ingest service under ingest/ and move the web app under meshexplorer/.
The ingest builds the meshcoreingest daemon and the goose migration runner,
applies the meshcore ClickHouse schema (packets, adverts, unified node view),
and loads its MQTT broker list and ClickHouse settings entirely from environment
variables (MQTT_BROKERS as a JSON array, CLICKHOUSE_*). No credentials are baked
into the source.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Alex Vanderpot