mirror of
https://github.com/ajvpot/meshexplorer.git
synced 2026-07-06 01:30:58 +02:00
Alex Vanderpot
c5ee493d8c
Replace the hand-curated region groups with data-driven ones and make the region_groups ClickHouse table the single source of truth. - scripts/generate-region-groups.ts: offline generator — clusters regions by cross-region packet co-occurrence (min-share single-linkage) at two levels (broad "region" + tight "metro"), names clusters via `claude -p`, reconciles codes by member overlap so permalinks stay stable, and emits the region_groups seed. Migration 004 reseeded with the resulting 39 groups. - Groups are DB-sourced: getRegionGroups() (cached) feeds /api/regions and the dropdown/labels; filtering resolves a selector in SQL to a region (region = 'X') or a group (region IN / hasAny ... SELECT region_code FROM region_groups WHERE group_code = ...). No hardcoded membership in TS; resolveSelector removed. - Drop the TS<->SQL parity script (no membership left to sync); regionSql and the migration ALIAS are kept in sync by hand. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
698 lines
30 KiB
SQL
698 lines
30 KiB
SQL
-- +goose Up
|
||
-- Region system rework: derive an uppercase IATA region code from the base topic and surface
|
||
-- it as an in-DB handle shared by the web app and Grafana, seed region groups, rework the
|
||
-- neighbor graph to be strictly per-region, and add a scheduled region index.
|
||
--
|
||
-- The topic->region derivation below is the canonical SQL form. Keep every occurrence here
|
||
-- byte-for-byte identical to regionSql('topic') in meshexplorer/src/lib/regions.ts (by hand).
|
||
-- Canonical:
|
||
-- multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '')
|
||
|
||
-- 1. Read-time `region` ALIAS column on the base table (for direct meshcore_packets queries:
|
||
-- Grafana packets panels + the packets streamer). No storage, no backfill.
|
||
-- +goose StatementBegin
|
||
ALTER TABLE meshcore_packets
|
||
ADD COLUMN IF NOT EXISTS region String
|
||
ALIAS multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '')
|
||
COMMENT 'Derived IATA region from base topic (read-time ALIAS, mirrors regionSql in src/lib/regions.ts)';
|
||
-- +goose StatementEnd
|
||
|
||
-- 2. Region groups: named sets of IATA codes. The region_groups table is the single source of
|
||
-- truth (the app reads it cached; Grafana reads it directly), regenerated by
|
||
-- meshexplorer/scripts/generate-region-groups.ts.
|
||
CREATE TABLE IF NOT EXISTS region_groups (
|
||
group_code LowCardinality(String) COMMENT 'Group key (slug), e.g. pacific-northwest',
|
||
group_name String COMMENT 'Display name, e.g. Pacific Northwest',
|
||
region_code LowCardinality(String) COMMENT 'Member IATA region code, e.g. SEA',
|
||
updated_at DateTime DEFAULT now()
|
||
) ENGINE = ReplacingMergeTree(updated_at)
|
||
ORDER BY (group_code, region_code)
|
||
COMMENT 'Region-group membership (single source of truth). Regenerated by scripts/generate-region-groups.ts.';
|
||
|
||
-- Data-driven (generated by meshexplorer/scripts/generate-region-groups.ts):
|
||
INSERT INTO region_groups (group_code, group_name, region_code) VALUES
|
||
('britain-ireland','Britain & Ireland','BHX'),
|
||
('britain-ireland','Britain & Ireland','MAN'),
|
||
('britain-ireland','Britain & Ireland','BRS'),
|
||
('britain-ireland','Britain & Ireland','LCY'),
|
||
('britain-ireland','Britain & Ireland','OXF'),
|
||
('britain-ireland','Britain & Ireland','MME'),
|
||
('britain-ireland','Britain & Ireland','BOH'),
|
||
('britain-ireland','Britain & Ireland','LTN'),
|
||
('britain-ireland','Britain & Ireland','CEG'),
|
||
('britain-ireland','Britain & Ireland','LON'),
|
||
('britain-ireland','Britain & Ireland','CAX'),
|
||
('britain-ireland','Britain & Ireland','SOU'),
|
||
('britain-ireland','Britain & Ireland','BHD'),
|
||
('britain-ireland','Britain & Ireland','UPV'),
|
||
('britain-ireland','Britain & Ireland','BSH'),
|
||
('britain-ireland','Britain & Ireland','LBA'),
|
||
('britain-ireland','Britain & Ireland','CBG'),
|
||
('britain-ireland','Britain & Ireland','DSA'),
|
||
('britain-ireland','Britain & Ireland','HYC'),
|
||
('britain-ireland','Britain & Ireland','LGW'),
|
||
('britain-ireland','Britain & Ireland','NQT'),
|
||
('britain-ireland','Britain & Ireland','SNN'),
|
||
('britain-ireland','Britain & Ireland','DUB'),
|
||
('britain-ireland','Britain & Ireland','VLY'),
|
||
('pacific-northwest','Pacific Northwest','SEA'),
|
||
('pacific-northwest','Pacific Northwest','PDX'),
|
||
('pacific-northwest','Pacific Northwest','BLI'),
|
||
('pacific-northwest','Pacific Northwest','KLS'),
|
||
('pacific-northwest','Pacific Northwest','YVR'),
|
||
('pacific-northwest','Pacific Northwest','KEH'),
|
||
('pacific-northwest','Pacific Northwest','PAE'),
|
||
('pacific-northwest','Pacific Northwest','OLM'),
|
||
('pacific-northwest','Pacific Northwest','SLE'),
|
||
('pacific-northwest','Pacific Northwest','TTD'),
|
||
('pacific-northwest','Pacific Northwest','EUG'),
|
||
('pacific-northwest','Pacific Northwest','CVO'),
|
||
('pacific-northwest','Pacific Northwest','MMV'),
|
||
('pacific-northwest','Pacific Northwest','MFR'),
|
||
('pacific-northwest','Pacific Northwest','LMT'),
|
||
('pacific-northwest','Pacific Northwest','GTP'),
|
||
('germany-poland','Germany & Poland','BWE'),
|
||
('germany-poland','Germany & Poland','LEJ'),
|
||
('germany-poland','Germany & Poland','HAM'),
|
||
('germany-poland','Germany & Poland','WRO'),
|
||
('germany-poland','Germany & Poland','ERF'),
|
||
('germany-poland','Germany & Poland','POZ'),
|
||
('germany-poland','Germany & Poland','HAJ'),
|
||
('germany-poland','Germany & Poland','DRS'),
|
||
('germany-poland','Germany & Poland','BYU'),
|
||
('germany-poland','Germany & Poland','KZG'),
|
||
('germany-poland','Germany & Poland','ZNO'),
|
||
('germany-poland','Germany & Poland','KSF'),
|
||
('germany-poland','Germany & Poland','AOC'),
|
||
('germany-poland','Germany & Poland','BFE'),
|
||
('southern-appalachia','Southern Appalachia','RDU'),
|
||
('southern-appalachia','Southern Appalachia','TYS'),
|
||
('southern-appalachia','Southern Appalachia','AVL'),
|
||
('southern-appalachia','Southern Appalachia','CHA'),
|
||
('southern-appalachia','Southern Appalachia','TRI'),
|
||
('southern-appalachia','Southern Appalachia','GSO'),
|
||
('southern-appalachia','Southern Appalachia','GSP'),
|
||
('southern-appalachia','Southern Appalachia','CLT'),
|
||
('southern-appalachia','Southern Appalachia','LEX'),
|
||
('netherlands','Netherlands','AMS'),
|
||
('netherlands','Netherlands','RTM'),
|
||
('netherlands','Netherlands','GRQ'),
|
||
('netherlands','Netherlands','ENS'),
|
||
('netherlands','Netherlands','UTC'),
|
||
('netherlands','Netherlands','LEY'),
|
||
('netherlands','Netherlands','AMF'),
|
||
('netherlands','Netherlands','LWR'),
|
||
('netherlands','Netherlands','QYM'),
|
||
('czech-republic','Czech Republic','PRG'),
|
||
('czech-republic','Czech Republic','BRQ'),
|
||
('czech-republic','Czech Republic','VOD'),
|
||
('czech-republic','Czech Republic','PED'),
|
||
('czech-republic','Czech Republic','JCL'),
|
||
('czech-republic','Czech Republic','ZLN'),
|
||
('czech-republic','Czech Republic','HRA'),
|
||
('czech-republic','Czech Republic','OSR'),
|
||
('northern-california','Northern California','SJC'),
|
||
('northern-california','Northern California','OAK'),
|
||
('northern-california','Northern California','SFO'),
|
||
('northern-california','Northern California','MRY'),
|
||
('northern-california','Northern California','PRB'),
|
||
('northern-california','Northern California','MCE'),
|
||
('northern-california','Northern California','COA'),
|
||
('alpine-region','Alpine Region','STR'),
|
||
('alpine-region','Alpine Region','GVA'),
|
||
('alpine-region','Alpine Region','MUC'),
|
||
('alpine-region','Alpine Region','ZRH'),
|
||
('alpine-region','Alpine Region','ACH'),
|
||
('alpine-region','Alpine Region','FMM'),
|
||
('alpine-region','Alpine Region','OBF'),
|
||
('southern-france','Southern France','LYS'),
|
||
('southern-france','Southern France','EBU'),
|
||
('southern-france','Southern France','TLN'),
|
||
('southern-france','Southern France','FNI'),
|
||
('southern-france','Southern France','MPL'),
|
||
('southern-france','Southern France','VAF'),
|
||
('southern-france','Southern France','XBK'),
|
||
('southern-california','Southern California','SAN'),
|
||
('southern-california','Southern California','LAX'),
|
||
('southern-california','Southern California','OXR'),
|
||
('southern-california','Southern California','SBA'),
|
||
('southern-california','Southern California','ONT'),
|
||
('southern-california','Southern California','PSP'),
|
||
('southern-california','Southern California','SNA'),
|
||
('central-europe','Central Europe','BTS'),
|
||
('central-europe','Central Europe','BUD'),
|
||
('central-europe','Central Europe','VIE'),
|
||
('central-europe','Central Europe','ILZ'),
|
||
('central-europe','Central Europe','TAT'),
|
||
('central-europe','Central Europe','KSC'),
|
||
('denmark','Denmark','AAR'),
|
||
('denmark','Denmark','KEL'),
|
||
('denmark','Denmark','ODE'),
|
||
('denmark','Denmark','SGD'),
|
||
('denmark','Denmark','QXV'),
|
||
('southern-netherlands','Southern Netherlands','EIN'),
|
||
('southern-netherlands','Southern Netherlands','UDE'),
|
||
('southern-netherlands','Southern Netherlands','QAR'),
|
||
('southern-netherlands','Southern Netherlands','GLZ'),
|
||
('belgium','Belgium','LGG'),
|
||
('belgium','Belgium','MST'),
|
||
('belgium','Belgium','BRU'),
|
||
('belgium','Belgium','ANR'),
|
||
('northern-new-zealand','Northern New Zealand','AKL'),
|
||
('northern-new-zealand','Northern New Zealand','HLZ'),
|
||
('northern-new-zealand','Northern New Zealand','TRG'),
|
||
('northern-new-zealand','Northern New Zealand','WRE'),
|
||
('south-florida','South Florida','PPM'),
|
||
('south-florida','South Florida','FPR'),
|
||
('south-florida','South Florida','FLL'),
|
||
('southern-norway','Southern Norway','OSL'),
|
||
('southern-norway','Southern Norway','TRF'),
|
||
('southern-norway','Southern Norway','RYG'),
|
||
('colorado-front-range','Colorado Front Range','DEN'),
|
||
('colorado-front-range','Colorado Front Range','FNL'),
|
||
('colorado-front-range','Colorado Front Range','COS'),
|
||
('southern-idaho','Southern Idaho','IDA'),
|
||
('southern-idaho','Southern Idaho','PIH'),
|
||
('southern-idaho','Southern Idaho','TWF'),
|
||
('mid-atlantic','Mid-Atlantic','IAD'),
|
||
('mid-atlantic','Mid-Atlantic','SCE'),
|
||
('mid-atlantic','Mid-Atlantic','MDT'),
|
||
('puget-sound','Puget Sound','SEA'),
|
||
('puget-sound','Puget Sound','BLI'),
|
||
('puget-sound','Puget Sound','KLS'),
|
||
('puget-sound','Puget Sound','YVR'),
|
||
('puget-sound','Puget Sound','KEH'),
|
||
('puget-sound','Puget Sound','PAE'),
|
||
('puget-sound','Puget Sound','OLM'),
|
||
('central-germany','Central Germany','BWE'),
|
||
('central-germany','Central Germany','LEJ'),
|
||
('central-germany','Central Germany','ERF'),
|
||
('central-germany','Central Germany','HAJ'),
|
||
('central-germany','Central Germany','ZNO'),
|
||
('central-germany','Central Germany','KSF'),
|
||
('greater-london','Greater London','OXF'),
|
||
('greater-london','Greater London','LTN'),
|
||
('greater-london','Greater London','LON'),
|
||
('greater-london','Greater London','UPV'),
|
||
('greater-london','Greater London','CBG'),
|
||
('san-francisco-bay-area','San Francisco Bay Area','SJC'),
|
||
('san-francisco-bay-area','San Francisco Bay Area','OAK'),
|
||
('san-francisco-bay-area','San Francisco Bay Area','SFO'),
|
||
('san-francisco-bay-area','San Francisco Bay Area','MRY'),
|
||
('east-midlands','East Midlands','LBA'),
|
||
('east-midlands','East Midlands','DSA'),
|
||
('east-midlands','East Midlands','NQT'),
|
||
('south-coast','South Coast','BOH'),
|
||
('south-coast','South Coast','SOU'),
|
||
('south-coast','South Coast','BSH'),
|
||
('central-california','Central California','PRB'),
|
||
('central-california','Central California','MCE'),
|
||
('central-california','Central California','COA'),
|
||
('zurich-area','Zurich Area','ZRH'),
|
||
('zurich-area','Zurich Area','ACH'),
|
||
('zurich-area','Zurich Area','FMM'),
|
||
('southern-oregon','Southern Oregon','MFR'),
|
||
('southern-oregon','Southern Oregon','LMT'),
|
||
('southern-oregon','Southern Oregon','GTP'),
|
||
('vienna-budapest','Vienna–Budapest','BTS'),
|
||
('vienna-budapest','Vienna–Budapest','BUD'),
|
||
('vienna-budapest','Vienna–Budapest','VIE'),
|
||
('irish-sea','Irish Sea','CAX'),
|
||
('irish-sea','Irish Sea','BHD'),
|
||
('surrey','Surrey','HYC'),
|
||
('surrey','Surrey','LGW'),
|
||
('greater-manchester','Greater Manchester','MAN'),
|
||
('greater-manchester','Greater Manchester','CEG'),
|
||
('ireland','Ireland','SNN'),
|
||
('ireland','Ireland','DUB'),
|
||
('franconia','Franconia','BYU'),
|
||
('franconia','Franconia','KZG'),
|
||
('munich','Munich','MUC'),
|
||
('munich','Munich','OBF'),
|
||
('northern-slovakia','Northern Slovakia','ILZ'),
|
||
('northern-slovakia','Northern Slovakia','TAT'),
|
||
('brussels-antwerp','Brussels–Antwerp','BRU'),
|
||
('brussels-antwerp','Brussels–Antwerp','ANR'),
|
||
('meuse-valley','Meuse Valley','LGG'),
|
||
('meuse-valley','Meuse Valley','MST');
|
||
|
||
-- 3. Recreate the 001 views to expose `region` (scalar) / `regions` (array). The derivation is
|
||
-- inlined (concrete column) so app + Grafana queries against these views never depend on
|
||
-- resolving the base-table ALIAS through nested view layers.
|
||
CREATE OR REPLACE VIEW meshcore_adverts AS
|
||
SELECT
|
||
ingest_timestamp,
|
||
origin,
|
||
origin_pubkey,
|
||
mesh_timestamp,
|
||
packet,
|
||
path_len,
|
||
path,
|
||
broker,
|
||
topic,
|
||
multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '') AS region,
|
||
hex(substring(payload, 1, 32)) AS public_key,
|
||
reinterpretAsUInt32(substring(payload, 33, 4)) AS adv_timestamp,
|
||
hex(substring(payload, 37, 64)) AS signature,
|
||
reinterpretAsUInt8(substring(payload, 101, 1)) AS appdata_flags,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x01) = 0x01 AS is_chat_node,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x02) = 0x02 AS is_repeater,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x03) = 0x03 AS is_room_server,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10 AS has_location,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x20) = 0x20 AS has_feature1,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x40) = 0x40 AS has_feature2,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x80) = 0x80 AS has_name,
|
||
CASE WHEN bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10
|
||
THEN reinterpretAsInt32(substring(payload, 102, 4))
|
||
ELSE NULL
|
||
END AS latitude_i,
|
||
CASE WHEN bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10
|
||
THEN reinterpretAsInt32(substring(payload, 106, 4))
|
||
ELSE NULL
|
||
END AS longitude_i,
|
||
latitude_i * 1e-6 AS latitude,
|
||
longitude_i * 1e-6 AS longitude,
|
||
substring(
|
||
payload,
|
||
102
|
||
+ multiIf(bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10, 8, 0)
|
||
) AS node_name,
|
||
hex(substring(payload, 1, 1)) AS node_hash,
|
||
packet_hash
|
||
FROM meshcore_packets
|
||
WHERE payload_type = 4;
|
||
|
||
CREATE OR REPLACE VIEW meshcore_adverts_latest AS
|
||
SELECT
|
||
public_key,
|
||
min(ingest_timestamp) AS first_heard,
|
||
max(ingest_timestamp) AS last_seen,
|
||
argMax(broker, ingest_timestamp) AS broker,
|
||
argMax(topic, ingest_timestamp) AS topic,
|
||
argMax(region, ingest_timestamp) AS region,
|
||
argMax(origin, ingest_timestamp) AS origin,
|
||
argMax(mesh_timestamp, ingest_timestamp) AS mesh_timestamp,
|
||
argMax(packet, ingest_timestamp) AS packet,
|
||
argMax(path_len, ingest_timestamp) AS path_len,
|
||
argMax(path, ingest_timestamp) AS path,
|
||
argMax(adv_timestamp, ingest_timestamp) AS adv_timestamp,
|
||
argMax(signature, ingest_timestamp) AS signature,
|
||
argMax(appdata_flags, ingest_timestamp) AS appdata_flags,
|
||
argMax(is_chat_node, ingest_timestamp) AS is_chat_node,
|
||
argMax(is_repeater, ingest_timestamp) AS is_repeater,
|
||
argMax(is_room_server, ingest_timestamp) AS is_room_server,
|
||
argMax(has_location, ingest_timestamp) AS has_location,
|
||
argMax(has_feature1, ingest_timestamp) AS has_feature1,
|
||
argMax(has_feature2, ingest_timestamp) AS has_feature2,
|
||
argMax(has_name, ingest_timestamp) AS has_name,
|
||
argMax(latitude_i, ingest_timestamp) AS latitude_i,
|
||
argMax(longitude_i, ingest_timestamp) AS longitude_i,
|
||
argMax(latitude, ingest_timestamp) AS latitude,
|
||
argMax(longitude, ingest_timestamp) AS longitude,
|
||
argMax(node_name, ingest_timestamp) AS node_name,
|
||
argMax(node_hash, ingest_timestamp) AS node_hash,
|
||
argMax(packet_hash, ingest_timestamp) AS packet_hash
|
||
FROM meshcore_adverts
|
||
GROUP BY public_key
|
||
ORDER BY last_seen DESC;
|
||
|
||
CREATE OR REPLACE VIEW meshcore_public_channel_messages AS
|
||
SELECT
|
||
max(ingest_timestamp) AS ingest_timestamp,
|
||
min(mesh_timestamp) AS mesh_timestamp,
|
||
groupArray(origin) AS origins,
|
||
any(packet) AS packet,
|
||
any(path_len) AS path_len,
|
||
hex(substring(payload, 1, 1)) AS channel_hash,
|
||
hex(substring(payload, 2, 2)) AS mac,
|
||
substring(payload, 4) AS encrypted_message,
|
||
count() AS message_count,
|
||
groupArray((origin, hex(path))) AS origin_path_array, --deprecated
|
||
groupArray((origin, hex(origin_pubkey), hex(path))) AS origin_key_path_array, --deprecated
|
||
groupArray((broker, topic)) AS topic_broker_array, --deprecated
|
||
groupArray((origin, hex(origin_pubkey), hex(path), broker, topic)) AS origin_path_info,
|
||
arrayDistinct(arrayFilter(r -> r != '', groupArray(multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '')))) AS regions,
|
||
any(packet_hash) AS message_id
|
||
FROM meshcore_packets
|
||
WHERE payload_type = 5
|
||
GROUP BY payload
|
||
ORDER BY ingest_timestamp DESC;
|
||
|
||
-- 4. Rework the global neighbor edge graph: region is derived inline from each source's topic
|
||
-- (not the hardcoded broker/topic CTE), and direct edges are now region-scoped so a node in
|
||
-- one region is never counted as a neighbor in another (§6a: drop the cross-join over regions).
|
||
DROP VIEW IF EXISTS meshcore_all_neighbor_edges;
|
||
-- +goose StatementBegin
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS meshcore_all_neighbor_edges
|
||
REFRESH EVERY 1 HOUR
|
||
ENGINE = MergeTree
|
||
ORDER BY (region, source_node, target_node)
|
||
AS
|
||
WITH
|
||
node_details AS (
|
||
SELECT
|
||
public_key,
|
||
node_name,
|
||
last_seen,
|
||
ifNull(latitude, 0.) AS lat,
|
||
ifNull(longitude, 0.) AS lon,
|
||
-- (0,0) / missing coords are treated as "no usable location" (avoids edges drawn to null island)
|
||
(has_location AND abs(ifNull(latitude, 0.)) > 0.01 AND abs(ifNull(longitude, 0.)) > 0.01) AS loc_ok
|
||
FROM meshcore_adverts_latest
|
||
),
|
||
-- Region-tagged latest adverts (region derived once from the latest topic per node)
|
||
adverts_latest_r AS (
|
||
SELECT
|
||
public_key,
|
||
is_repeater,
|
||
last_seen,
|
||
multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '') AS region
|
||
FROM meshcore_adverts_latest
|
||
),
|
||
-- Repeater prefixes per region (exactly one repeater per 2-char prefix), last 2 days
|
||
repeater_prefixes AS (
|
||
SELECT
|
||
region,
|
||
substring(public_key, 1, 2) AS prefix,
|
||
count() AS node_count,
|
||
any(public_key) AS representative_key
|
||
FROM adverts_latest_r
|
||
WHERE is_repeater = 1 AND last_seen >= now() - INTERVAL 2 DAY AND region != ''
|
||
GROUP BY region, prefix
|
||
HAVING node_count = 1
|
||
),
|
||
-- Distinct multi-hop path packets (last 1 day), region derived from the packet topic
|
||
path_src AS (
|
||
SELECT DISTINCT
|
||
payload,
|
||
path,
|
||
path_len,
|
||
multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '') AS region
|
||
FROM meshcore_packets
|
||
WHERE path_len >= 2 AND ingest_timestamp >= now() - INTERVAL 1 DAY
|
||
),
|
||
path_pairs AS (
|
||
SELECT DISTINCT
|
||
region,
|
||
payload,
|
||
-- path is a hex string of 1-byte hop prefixes; take 2 hex chars per hop.
|
||
upper(substring(path, 2 * i - 1, 2)) AS source_prefix,
|
||
upper(substring(path, 2 * i + 1, 2)) AS target_prefix
|
||
FROM path_src
|
||
ARRAY JOIN range(1, path_len) AS i
|
||
WHERE i < path_len AND region != ''
|
||
),
|
||
path_neighbors AS (
|
||
SELECT region, source_prefix, target_prefix, count() AS packet_count
|
||
FROM path_pairs
|
||
WHERE source_prefix != target_prefix
|
||
GROUP BY region, source_prefix, target_prefix
|
||
),
|
||
path_connections AS (
|
||
SELECT
|
||
pn.region AS region,
|
||
sm.representative_key AS source_node,
|
||
tm.representative_key AS target_node,
|
||
pn.packet_count AS packet_count
|
||
FROM path_neighbors AS pn
|
||
INNER JOIN repeater_prefixes AS sm ON sm.region = pn.region AND sm.prefix = pn.source_prefix
|
||
INNER JOIN repeater_prefixes AS tm ON tm.region = pn.region AND tm.prefix = pn.target_prefix
|
||
),
|
||
-- Direct connections (path_len = 0 adverts), last 7 days, region-scoped per the advert's
|
||
-- own topic. No cross-region duplication: an edge belongs only to the region it was heard in.
|
||
direct_connections AS (
|
||
SELECT DISTINCT
|
||
multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '') AS region,
|
||
hex(origin_pubkey) AS source_node,
|
||
public_key AS target_node
|
||
FROM meshcore_adverts
|
||
WHERE path_len = 0
|
||
AND hex(origin_pubkey) != public_key
|
||
AND ingest_timestamp >= now() - INTERVAL 7 DAY
|
||
),
|
||
-- Per-region edges: path edges + direct edges not already covered by a path edge
|
||
edges AS (
|
||
SELECT region, source_node, target_node, 'path' AS connection_type, packet_count
|
||
FROM path_connections
|
||
UNION ALL
|
||
SELECT d.region, d.source_node, d.target_node, 'direct' AS connection_type, CAST(1 AS UInt64) AS packet_count
|
||
FROM direct_connections AS d
|
||
WHERE d.region != ''
|
||
AND (d.region, d.source_node, d.target_node) NOT IN (SELECT region, source_node, target_node FROM path_connections)
|
||
AND (d.region, d.target_node, d.source_node) NOT IN (SELECT region, source_node, target_node FROM path_connections)
|
||
)
|
||
SELECT
|
||
e.region AS region,
|
||
e.source_node AS source_node,
|
||
e.target_node AS target_node,
|
||
e.connection_type AS connection_type,
|
||
e.packet_count AS packet_count,
|
||
sd.node_name AS source_name,
|
||
if(sd.loc_ok, sd.lat, NULL) AS source_latitude,
|
||
if(sd.loc_ok, sd.lon, NULL) AS source_longitude,
|
||
toUInt8(sd.loc_ok) AS source_has_location,
|
||
sd.last_seen AS source_last_seen,
|
||
td.node_name AS target_name,
|
||
if(td.loc_ok, td.lat, NULL) AS target_latitude,
|
||
if(td.loc_ok, td.lon, NULL) AS target_longitude,
|
||
toUInt8(td.loc_ok) AS target_has_location,
|
||
td.last_seen AS target_last_seen
|
||
FROM edges AS e
|
||
INNER JOIN node_details AS sd ON e.source_node = sd.public_key
|
||
INNER JOIN node_details AS td ON e.target_node = td.public_key
|
||
-- Drop geographically implausible adjacencies: across a wide aggregated topic (e.g. a
|
||
-- letsmesh meshcore/{IATA} feed covering a whole region) the 1-byte path-hop prefixes
|
||
-- collide, stitching together repeaters hundreds of km apart that were never adjacent.
|
||
-- A real LoRa hop between two located nodes is within ~150 km.
|
||
WHERE NOT (sd.loc_ok AND td.loc_ok AND greatCircleDistance(sd.lon, sd.lat, td.lon, td.lat) > 150000);
|
||
-- +goose StatementEnd
|
||
|
||
-- 5. Scheduled region index: a tiny precomputed table of active regions (last 30 days) so the
|
||
-- app's region dropdown and Grafana's $region variable read a small table, not a live scan.
|
||
-- +goose StatementBegin
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS meshcore_regions
|
||
REFRESH EVERY 1 HOUR
|
||
ENGINE = MergeTree
|
||
ORDER BY region
|
||
AS
|
||
WITH adverts_latest_r AS (
|
||
SELECT
|
||
public_key,
|
||
last_seen,
|
||
multiIf(lower(topic) IN ('meshcore','meshcore/salish'), 'SEA', match(splitByChar('/', lower(topic))[2], '^[a-z]{3}$'), upper(splitByChar('/', lower(topic))[2]), '') AS region
|
||
FROM meshcore_adverts_latest
|
||
WHERE last_seen >= now() - INTERVAL 30 DAY
|
||
)
|
||
SELECT
|
||
region,
|
||
count() AS node_count,
|
||
max(last_seen) AS last_seen
|
||
FROM adverts_latest_r
|
||
WHERE region != ''
|
||
GROUP BY region;
|
||
-- +goose StatementEnd
|
||
|
||
-- +goose Down
|
||
DROP VIEW IF EXISTS meshcore_regions;
|
||
|
||
DROP VIEW IF EXISTS meshcore_all_neighbor_edges;
|
||
-- +goose StatementBegin
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS meshcore_all_neighbor_edges
|
||
REFRESH EVERY 1 HOUR
|
||
ENGINE = MergeTree
|
||
ORDER BY (region, source_node, target_node)
|
||
AS
|
||
WITH
|
||
regions_topics AS (
|
||
SELECT 'seattle' AS region, 'wss://mqtt-us-v1.letsmesh.net:443' AS broker, arrayJoin(['meshcore/SEA']) AS topic
|
||
UNION ALL SELECT 'portland', 'tcp://mqtt.davekeogh.com:1883', 'meshcore/pdx'
|
||
UNION ALL SELECT 'boston', 'tcp://mqtt.davekeogh.com:1883', 'meshcore/bos'
|
||
),
|
||
node_details AS (
|
||
SELECT public_key, node_name, latitude, longitude, has_location, last_seen
|
||
FROM meshcore_adverts_latest
|
||
),
|
||
repeater_prefixes AS (
|
||
SELECT
|
||
rt.region AS region,
|
||
substring(a.public_key, 1, 2) AS prefix,
|
||
count() AS node_count,
|
||
any(a.public_key) AS representative_key
|
||
FROM meshcore_adverts_latest AS a
|
||
INNER JOIN regions_topics AS rt ON a.broker = rt.broker AND a.topic = rt.topic
|
||
WHERE a.is_repeater = 1 AND a.last_seen >= now() - INTERVAL 2 DAY
|
||
GROUP BY rt.region, prefix
|
||
HAVING node_count = 1
|
||
),
|
||
path_src AS (
|
||
SELECT DISTINCT payload, path, path_len, broker, topic
|
||
FROM meshcore_packets
|
||
WHERE path_len >= 2 AND ingest_timestamp >= now() - INTERVAL 1 DAY
|
||
),
|
||
path_pairs AS (
|
||
SELECT DISTINCT
|
||
rt.region AS region,
|
||
payload,
|
||
upper(substring(path, 2 * i - 1, 2)) AS source_prefix,
|
||
upper(substring(path, 2 * i + 1, 2)) AS target_prefix
|
||
FROM path_src AS p
|
||
INNER JOIN regions_topics AS rt ON p.broker = rt.broker AND p.topic = rt.topic
|
||
ARRAY JOIN range(1, path_len) AS i
|
||
WHERE i < path_len
|
||
),
|
||
path_neighbors AS (
|
||
SELECT region, source_prefix, target_prefix, count() AS packet_count
|
||
FROM path_pairs
|
||
WHERE source_prefix != target_prefix
|
||
GROUP BY region, source_prefix, target_prefix
|
||
),
|
||
path_connections AS (
|
||
SELECT
|
||
pn.region AS region,
|
||
sm.representative_key AS source_node,
|
||
tm.representative_key AS target_node,
|
||
pn.packet_count AS packet_count
|
||
FROM path_neighbors AS pn
|
||
INNER JOIN repeater_prefixes AS sm ON sm.region = pn.region AND sm.prefix = pn.source_prefix
|
||
INNER JOIN repeater_prefixes AS tm ON tm.region = pn.region AND tm.prefix = pn.target_prefix
|
||
),
|
||
direct_connections AS (
|
||
SELECT DISTINCT hex(origin_pubkey) AS source_node, public_key AS target_node
|
||
FROM meshcore_adverts
|
||
WHERE path_len = 0
|
||
AND hex(origin_pubkey) != public_key
|
||
AND ingest_timestamp >= now() - INTERVAL 7 DAY
|
||
),
|
||
edges AS (
|
||
SELECT region, source_node, target_node, 'path' AS connection_type, packet_count
|
||
FROM path_connections
|
||
UNION ALL
|
||
SELECT r.region, d.source_node, d.target_node, 'direct' AS connection_type, CAST(1 AS UInt64) AS packet_count
|
||
FROM direct_connections AS d
|
||
CROSS JOIN (SELECT DISTINCT region FROM regions_topics) AS r
|
||
WHERE (r.region, d.source_node, d.target_node) NOT IN (SELECT region, source_node, target_node FROM path_connections)
|
||
AND (r.region, d.target_node, d.source_node) NOT IN (SELECT region, source_node, target_node FROM path_connections)
|
||
)
|
||
SELECT
|
||
e.region AS region,
|
||
e.source_node AS source_node,
|
||
e.target_node AS target_node,
|
||
e.connection_type AS connection_type,
|
||
e.packet_count AS packet_count,
|
||
sd.node_name AS source_name,
|
||
sd.latitude AS source_latitude,
|
||
sd.longitude AS source_longitude,
|
||
sd.has_location AS source_has_location,
|
||
sd.last_seen AS source_last_seen,
|
||
td.node_name AS target_name,
|
||
td.latitude AS target_latitude,
|
||
td.longitude AS target_longitude,
|
||
td.has_location AS target_has_location,
|
||
td.last_seen AS target_last_seen
|
||
FROM edges AS e
|
||
INNER JOIN node_details AS sd ON e.source_node = sd.public_key
|
||
INNER JOIN node_details AS td ON e.target_node = td.public_key;
|
||
-- +goose StatementEnd
|
||
|
||
CREATE OR REPLACE VIEW meshcore_public_channel_messages AS
|
||
SELECT
|
||
max(ingest_timestamp) AS ingest_timestamp,
|
||
min(mesh_timestamp) AS mesh_timestamp,
|
||
groupArray(origin) AS origins,
|
||
any(packet) AS packet,
|
||
any(path_len) AS path_len,
|
||
hex(substring(payload, 1, 1)) AS channel_hash,
|
||
hex(substring(payload, 2, 2)) AS mac,
|
||
substring(payload, 4) AS encrypted_message,
|
||
count() AS message_count,
|
||
groupArray((origin, hex(path))) AS origin_path_array, --deprecated
|
||
groupArray((origin, hex(origin_pubkey), hex(path))) AS origin_key_path_array, --deprecated
|
||
groupArray((broker, topic)) AS topic_broker_array, --deprecated
|
||
groupArray((origin, hex(origin_pubkey), hex(path), broker, topic)) AS origin_path_info,
|
||
any(packet_hash) AS message_id
|
||
FROM meshcore_packets
|
||
WHERE payload_type = 5
|
||
GROUP BY payload
|
||
ORDER BY ingest_timestamp DESC;
|
||
|
||
CREATE OR REPLACE VIEW meshcore_adverts_latest AS
|
||
SELECT
|
||
public_key,
|
||
min(ingest_timestamp) AS first_heard,
|
||
max(ingest_timestamp) AS last_seen,
|
||
argMax(broker, ingest_timestamp) AS broker,
|
||
argMax(topic, ingest_timestamp) AS topic,
|
||
argMax(origin, ingest_timestamp) AS origin,
|
||
argMax(mesh_timestamp, ingest_timestamp) AS mesh_timestamp,
|
||
argMax(packet, ingest_timestamp) AS packet,
|
||
argMax(path_len, ingest_timestamp) AS path_len,
|
||
argMax(path, ingest_timestamp) AS path,
|
||
argMax(adv_timestamp, ingest_timestamp) AS adv_timestamp,
|
||
argMax(signature, ingest_timestamp) AS signature,
|
||
argMax(appdata_flags, ingest_timestamp) AS appdata_flags,
|
||
argMax(is_chat_node, ingest_timestamp) AS is_chat_node,
|
||
argMax(is_repeater, ingest_timestamp) AS is_repeater,
|
||
argMax(is_room_server, ingest_timestamp) AS is_room_server,
|
||
argMax(has_location, ingest_timestamp) AS has_location,
|
||
argMax(has_feature1, ingest_timestamp) AS has_feature1,
|
||
argMax(has_feature2, ingest_timestamp) AS has_feature2,
|
||
argMax(has_name, ingest_timestamp) AS has_name,
|
||
argMax(latitude_i, ingest_timestamp) AS latitude_i,
|
||
argMax(longitude_i, ingest_timestamp) AS longitude_i,
|
||
argMax(latitude, ingest_timestamp) AS latitude,
|
||
argMax(longitude, ingest_timestamp) AS longitude,
|
||
argMax(node_name, ingest_timestamp) AS node_name,
|
||
argMax(node_hash, ingest_timestamp) AS node_hash,
|
||
argMax(packet_hash, ingest_timestamp) AS packet_hash
|
||
FROM meshcore_adverts
|
||
GROUP BY public_key
|
||
ORDER BY last_seen DESC;
|
||
|
||
CREATE OR REPLACE VIEW meshcore_adverts AS
|
||
SELECT
|
||
ingest_timestamp,
|
||
origin,
|
||
origin_pubkey,
|
||
mesh_timestamp,
|
||
packet,
|
||
path_len,
|
||
path,
|
||
broker,
|
||
topic,
|
||
hex(substring(payload, 1, 32)) AS public_key,
|
||
reinterpretAsUInt32(substring(payload, 33, 4)) AS adv_timestamp,
|
||
hex(substring(payload, 37, 64)) AS signature,
|
||
reinterpretAsUInt8(substring(payload, 101, 1)) AS appdata_flags,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x01) = 0x01 AS is_chat_node,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x02) = 0x02 AS is_repeater,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x03) = 0x03 AS is_room_server,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10 AS has_location,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x20) = 0x20 AS has_feature1,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x40) = 0x40 AS has_feature2,
|
||
bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x80) = 0x80 AS has_name,
|
||
CASE WHEN bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10
|
||
THEN reinterpretAsInt32(substring(payload, 102, 4))
|
||
ELSE NULL
|
||
END AS latitude_i,
|
||
CASE WHEN bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10
|
||
THEN reinterpretAsInt32(substring(payload, 106, 4))
|
||
ELSE NULL
|
||
END AS longitude_i,
|
||
latitude_i * 1e-6 AS latitude,
|
||
longitude_i * 1e-6 AS longitude,
|
||
substring(
|
||
payload,
|
||
102
|
||
+ multiIf(bitAnd(reinterpretAsUInt8(substring(payload, 101, 1)), 0x10) = 0x10, 8, 0)
|
||
) AS node_name,
|
||
hex(substring(payload, 1, 1)) AS node_hash,
|
||
packet_hash
|
||
FROM meshcore_packets
|
||
WHERE payload_type = 4;
|
||
|
||
DROP TABLE IF EXISTS region_groups;
|
||
|
||
-- +goose StatementBegin
|
||
ALTER TABLE meshcore_packets DROP COLUMN IF EXISTS region;
|
||
-- +goose StatementEnd
|