mirror of
https://github.com/MeshCore-Beacon/beacon-server.git
synced 2026-10-16 11:39:53 +00:00
MrAlders0n
98006a9364
Stats endpoints read hourly rollup tables filled once per closed hour by one advisory-locked task, instead of materialized views that re-scanned every observation on each refresh. IATA-set tables give exact distinct counts for any IATA set or region. Cleanup holds raw rows for unrolled hours (up to 24h). Adds /stats/series; ingest keeps packet observation counts, observer last IATA, channel message counts and trace tag summaries. 001_baseline.sql is rewritten, so pre-release 2.0 databases must be recreated. See docs/historical-stats.md.
10 lines
586 B
SQL
10 lines
586 B
SQL
-- Copyright 2026 Beacon Contributors
|
|
-- SPDX-License-Identifier: AGPL-3.0-or-later
|
|
|
|
-- sqlc type-check stub only; db/rollup.go creates this as a TEMP table per rolled hour.
|
|
CREATE TABLE rollup_obs (
|
|
packet_hash bytea NOT NULL, observer_id uuid NOT NULL, iata character(3) NOT NULL,
|
|
heard_at timestamptz NOT NULL, path_length_byte smallint NOT NULL, hash_size smallint NOT NULL,
|
|
hop_count smallint NOT NULL, path_len integer NOT NULL, rssi smallint, snr real, airtime_ms real,
|
|
payload_type smallint, route_type smallint NOT NULL, origin_pubkey bytea, scope_id integer);
|