-- What the telemetry feed says a node is, kept across restarts.
--
-- Keyed on the peer id, never on the feed's `node_id`: that one is per
-- connection and changes every time the telemetry server restarts or the node
-- reconnects, so a table keyed on it would accumulate a new row per reconnect
-- for the same machine.
--
-- One row per node, overwritten whenever the feed says something newer. There
-- is deliberately no history: this is a cache of the current answer, and a node
-- that changes its CPU is telling us the old answer was superseded rather than
-- adding a fact about the past.
create table if not exists node_telemetry (
    peer_id            text primary key,
    -- Which chain we last saw it on. A node reports to one chain's feed, but
    -- an operator may move a machine between chains, and the row follows it.
    chain              text not null references chain (id) on delete cascade,
    name               text not null,
    implementation     text,
    version            text,
    target_os          text,
    target_arch        text,
    target_env         text,
    cpu                text,
    core_count         integer,
    -- Bytes. `bigint` because 64 GiB does not fit in an integer, and the
    -- machines on this network routinely have more.
    memory             bigint,
    linux_kernel       text,
    linux_distro       text,
    is_virtual_machine boolean,
    -- ISO 3166-1 alpha-2, derived from coordinates we do not keep.
    --
    -- No city column, and no latitude or longitude, by design: the feed's
    -- coordinates come from IP geolocation and are wrong at city resolution
    -- often enough that storing them would invite someone to render one. A
    -- column that does not exist cannot be displayed by mistake.
    country_code       text,
    country_name       text,
    -- When the node process started, per the feed. Lets uptime be a fact about
    -- the node rather than about how long this observer has been watching.
    startup_time       timestamptz,
    -- When we last heard any of this.
    seen_at            timestamptz not null default now()
);

-- The join the miner route makes: attribution gives a peer id, and rows are
-- read one at a time by it — which the primary key already serves. This index
-- is for the other direction, listing a chain's nodes.
create index if not exists node_telemetry_chain_idx on node_telemetry (chain, seen_at desc);