Module-Rust/server/db/schema.sql

-- ── The schema fragment ───────────────────────────────────────────────────
--
-- Core replays this file on EVERY boot, statement by statement, immediately
-- after its own schema.sql and before it seeds defaults (MODULE_API.md §2.6).
--
-- There is no migration runner anywhere in this project. A module's schema is
-- not a sequence of changes to apply once — it is a statement of what the tables
-- should look like, written so that running it against a database that already
-- matches does nothing. Every CREATE carries IF NOT EXISTS; **changing a table
-- is an ALTER below the CREATE, never an edit to the CREATE**, because
-- `CREATE TABLE IF NOT EXISTS` does nothing at all when the table is already
-- there and an edited column would reach fresh installs only.
--
-- Every table here is prefixed `rust_`, which is this module's id and the only
-- prefix it may create under.
--
-- ── Four kinds of table, and the split between them is the whole design ───
--
-- CONFIGURATION   `rust_servers` — rows an operator writes, from Admin → Rust.
-- OBSERVED STATE  `rust_server_state`, `rust_presence` — what a sidecar last
--                 reported, replaced rather than appended.
-- THE RECORD      `rust_wipes`, `rust_players`, `rust_player_wipe_stats`,
--                 `rust_gather_totals` — permanent, and the reason a wipe does
--                 not erase a player's history.
-- THE WINDOW      `rust_events` — recent detail, bounded by a sweep.
--
-- They are separate tables rather than columns on one because they have
-- different writers, different lifetimes and different audiences — and because
-- a purge of observed state while keeping the configuration is a thing an
-- operator will eventually want.
--
-- Teardown is `purge.sql`, which no boot ever runs.


-- ── The configured servers ────────────────────────────────────────────────
--
-- One row per Rust game server, and therefore one row per sidecar: the bridge is
-- one server to one sidecar, on that server's own host (R8). A community running
-- six servers has six rows here, each with its own base URL and its own token.
--
-- `id` is the operator's own slug and is what every URL under `/rust/servers/`
-- carries. It is deliberately NOT auto-increment: it appears in links people
-- share, and a row rebuilt after a mistake should be able to keep its address.
--
-- `sidecar_token_enc` holds the sidecar's shared secret **encrypted at rest**
-- through `ctx.secretBox` (MODULE_API.md §2.3), like every other secret this
-- platform stores. It is write-only in the API: the admin surface accepts a new
-- value and never returns the stored one, so a compromised admin session cannot
-- read back the credential that reaches the game host.
--
-- `protocol` records the wire version this row was configured against. It is
-- stored rather than assumed because a fleet is upgraded one host at a time, and
-- an operator needs to see WHICH server disagrees rather than that one does.
CREATE TABLE IF NOT EXISTS rust_servers (
  id                 VARCHAR(64)   NOT NULL PRIMARY KEY,
  name               VARCHAR(120)  NOT NULL,
  sidecar_base_url   VARCHAR(255)  NOT NULL,
  sidecar_token_enc  TEXT          NULL,
  protocol           INT UNSIGNED  NOT NULL DEFAULT 1,
  enabled            TINYINT(1)    NOT NULL DEFAULT 1,
  sort_order         INT           NOT NULL DEFAULT 0,
  created_at         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- ── What each server last said about itself ───────────────────────────────
--
-- One row per configured server, replaced whole each time this module reads a
-- sidecar. It is the table that lets the site render while every game server is
-- off, which is the point of the sidecar holding a store at all.
--
-- `updated_at` carries no `ON UPDATE CURRENT_TIMESTAMP`, deliberately. That
-- clause fires only when an UPDATE actually CHANGES a value, so a writer sending
-- the same numbers back — which is exactly what a quiet server looks like —
-- would leave the timestamp frozen at the first write and the row would look
-- stale while nothing was wrong. The writer sets the column explicitly instead.
--
-- `boot_id` is the game process's own identity, not the sidecar's and not the
-- plugin's. It changes when the world started over and at no other time, which
-- is what makes it the thing to watch: a reconnect of either bridge component
-- loses nothing, and a game restart loses everything an event put in the world.
--
-- `raw` keeps the whole frame. This module indexes the columns it serves and
-- stores the rest verbatim, so a protocol version that adds a field needs no
-- migration here — the same dumb-forwarder property the sidecar has, one hop
-- further along.
CREATE TABLE IF NOT EXISTS rust_server_state (
  server_id        VARCHAR(64)   NOT NULL PRIMARY KEY,
  reachable        TINYINT(1)    NOT NULL DEFAULT 0,
  online           TINYINT(1)    NOT NULL DEFAULT 0,
  players          INT UNSIGNED  NOT NULL DEFAULT 0,
  max_players      INT UNSIGNED  NOT NULL DEFAULT 0,
  hostname         VARCHAR(191)  NULL,
  level            VARCHAR(120)  NULL,
  seed             BIGINT        NULL,
  world_size       INT UNSIGNED  NULL,
  boot_id          VARCHAR(64)   NULL,
  save_created_at  VARCHAR(32)   NULL,
  protocol         INT UNSIGNED  NULL,
  raw              LONGTEXT      NULL,
  updated_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rust_server_state_server
    FOREIGN KEY (server_id) REFERENCES rust_servers (id) ON DELETE CASCADE
);


-- ── The read path ─────────────────────────────────────────────────────────
--
-- Protocol 2 turned the bridge from a greeting into a catalogue, and these are
-- the tables that hold it. They divide on one line, and it is the line R12 drew:
--
--   PERMANENT   `rust_wipes`, `rust_players`, `rust_player_wipe_stats`,
--               `rust_gather_totals` — a player's record, kept for ever. All-time
--               is a SUM across wipes rather than a second set of counters, so
--               there is no second number that can disagree with the first.
--
--   BOUNDED     `rust_events` — the recent raw window the killfeed reads, pruned
--               on a sweep. It is detail, not record: losing last month's
--               individual deaths costs a scroll-back, losing last month's
--               totals costs a player their history.
--
--   DERIVED     `rust_presence` — who is on right now, replaced wholesale from
--               the `players.online` board. Never a history, never appended.
--
-- The sidecar keeps its own bounded copy of the same events (default 14 days),
-- so shortening either window loses recent detail and neither loses a total.


-- ── Wipes ─────────────────────────────────────────────────────────────────
--
-- One row per (server, wipe). The id is the plugin's, derived from the save's
-- creation time and stamped on every frame (PROTOCOL.md §8.2) — this module
-- never derives one, because two derivations of one fact eventually disagree
-- about a boundary.
--
-- Rows appear by being MENTIONED: the first frame carrying a wipe id this module
-- has not seen creates it. There is no "start a wipe" call and there must not be
-- one, because the website is not present when a wipe happens — a wipe is a fact
-- about a world that was restarted while nobody was watching.
CREATE TABLE IF NOT EXISTS rust_wipes (
  server_id   VARCHAR(64)  NOT NULL,
  wipe_id     VARCHAR(48)  NOT NULL,
  save_created_at VARCHAR(32) NULL,
  first_seen  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (server_id, wipe_id),
  CONSTRAINT fk_rust_wipes_server
    FOREIGN KEY (server_id) REFERENCES rust_servers (id) ON DELETE CASCADE
);


-- ── Players ───────────────────────────────────────────────────────────────
--
-- Identity, and deliberately nothing else. It is keyed on the Steam id alone
-- and carries no server: a player is the same person on all six of a community's
-- servers, and everything that is per-server lives in the stats table.
--
-- `user_id` is NOT here. Linking a Steam id to a website account is phase 6's
-- work (R1), and a column waiting for it would be a column every read has to
-- remember is always null.
CREATE TABLE IF NOT EXISTS rust_players (
  steam_id    VARCHAR(32)  NOT NULL PRIMARY KEY,
  name        VARCHAR(191) NULL,
  first_seen  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);


-- ── The permanent record ──────────────────────────────────────────────────
--
-- One row per player per wipe per server, and the only counters this module
-- keeps. R12's "per-wipe detail plus all-time rollups" is satisfied by SUMming
-- this rather than by maintaining a second all-time row, because two counters
-- for one fact drift the first time an ingest is replayed.
--
-- Every column is a COUNT that only ever goes up within a wipe, which is what
-- makes ingest idempotent-ish in the only way that matters: the cursor advances
-- only after the batch commits, so a crash re-reads a batch it has not counted.
--
-- `playtime_sec` comes from `sessionSec` on a disconnect, and a session whose
-- start this module never saw contributes NOTHING rather than zero — the plugin
-- omits the field, the ingest skips it, and the number stays honestly short
-- instead of quietly wrong.
CREATE TABLE IF NOT EXISTS rust_player_wipe_stats (
  server_id     VARCHAR(64)     NOT NULL,
  wipe_id       VARCHAR(48)     NOT NULL,
  steam_id      VARCHAR(32)     NOT NULL,
  kills         INT UNSIGNED    NOT NULL DEFAULT 0,
  deaths        INT UNSIGNED    NOT NULL DEFAULT 0,
  suicides      INT UNSIGNED    NOT NULL DEFAULT 0,
  npc_kills     INT UNSIGNED    NOT NULL DEFAULT 0,
  structures    INT UNSIGNED    NOT NULL DEFAULT 0,
  sessions      INT UNSIGNED    NOT NULL DEFAULT 0,
  playtime_sec  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  last_seen     DATETIME        NULL,
  PRIMARY KEY (server_id, wipe_id, steam_id),
  KEY idx_rust_stats_kills (server_id, wipe_id, kills DESC),
  KEY idx_rust_stats_player (steam_id)
);


-- ── What they gathered ────────────────────────────────────────────────────
--
-- A row per resource rather than a JSON blob on the stats row, for one reason:
-- the leaderboard question is "who gathered the most sulfur this wipe", and that
-- is an ORDER BY over a column in every SQL engine and a JSON function call in
-- exactly one. The resource name is the game's own shortname, unknown in advance
-- and not worth a lookup table.
CREATE TABLE IF NOT EXISTS rust_gather_totals (
  server_id  VARCHAR(64)     NOT NULL,
  wipe_id    VARCHAR(48)     NOT NULL,
  steam_id   VARCHAR(32)     NOT NULL,
  resource   VARCHAR(64)     NOT NULL,
  amount     BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (server_id, wipe_id, steam_id, resource),
  KEY idx_rust_gather_top (server_id, wipe_id, resource, amount DESC)
);


-- ── The recent raw window ─────────────────────────────────────────────────
--
-- Every ingested event, whole, for as long as the retention sweep keeps it. The
-- killfeed reads this; so does an admin looking at what happened.
--
-- `raw` holds the entire frame and the columns beside it are only what a query
-- needs to reach — the same rule the sidecar's own store follows, one hop along:
-- a protocol version that adds a field needs no migration here.
--
-- **`kind` is a security boundary, not a label.** Some kinds carry IP addresses
-- and player reports (PROTOCOL.md §8.4), and what makes them safe is that the
-- public read is filtered by an allowlist this module holds, default-deny. The
-- rows are stored either way, because an operator chasing ban evasion needs them.
CREATE TABLE IF NOT EXISTS rust_events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  server_id   VARCHAR(64)     NOT NULL,
  wipe_id     VARCHAR(48)     NULL,
  kind        VARCHAR(64)     NOT NULL,
  t           BIGINT          NOT NULL,
  steam_id    VARCHAR(32)     NULL,
  raw         LONGTEXT        NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rust_events_server (server_id, id DESC),
  KEY idx_rust_events_kind (server_id, kind, id DESC),
  KEY idx_rust_events_wipe (server_id, wipe_id, id DESC),
  KEY idx_rust_events_created (created_at)
);


-- ── Who is on right now ───────────────────────────────────────────────────
--
-- Replaced wholesale every time the `players.online` board arrives, which is on
-- every bridge connect and every 60 seconds. It is a BOARD, and the reason it is
-- its own table rather than rows in `rust_events` is that a board answers "now"
-- and an event answers "then"; storing a board as history is the mistake the
-- wire's `type` field exists to prevent, and it would be a shame to make it here
-- after the sidecar went to the trouble of not making it there.
CREATE TABLE IF NOT EXISTS rust_presence (
  server_id     VARCHAR(64)  NOT NULL,
  steam_id      VARCHAR(32)  NOT NULL,
  name          VARCHAR(191) NULL,
  sleeping      TINYINT(1)   NOT NULL DEFAULT 0,
  connected_at  DATETIME     NULL,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (server_id, steam_id)
);


-- ── The ingest cursor ─────────────────────────────────────────────────────
--
-- Where this module has read up to in each sidecar's feed. One row per server.
--
-- It is persisted rather than held in memory because the alternative is a module
-- that re-reads everything on every boot or nothing at all, and both are wrong in
-- a way that only shows up in production. The cursor advances **after** the batch
-- is written, never before: a crash mid-batch re-reads rows it has not counted,
-- which is the safe direction to be wrong in.
--
-- A NEW server starts at the sidecar's current end rather than at zero (see
-- `GET /feed` with no `since`). A module installed today against a sidecar that
-- has been running a month wants what happens next — replaying a fortnight of
-- deaths into stats whose wipes it never saw is not a catch-up, it is a
-- fabrication of history it was not present for.
CREATE TABLE IF NOT EXISTS rust_ingest_cursor (
  server_id      VARCHAR(64)     NOT NULL PRIMARY KEY,
  last_event_id  BIGINT UNSIGNED NOT NULL DEFAULT 0,
  events_seen    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  updated_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_rust_cursor_server
    FOREIGN KEY (server_id) REFERENCES rust_servers (id) ON DELETE CASCADE
);


-- ── Changes to tables that already shipped ────────────────────────────────
--
-- An ALTER below the CREATE, never an edit to it: `CREATE TABLE IF NOT EXISTS`
-- does nothing against a database that already has the table, so an edited column
-- would reach fresh installs only — which is the worst possible distribution for
-- a schema change, because it works everywhere it is tested.
ALTER TABLE rust_server_state ADD COLUMN IF NOT EXISTS wipe_id VARCHAR(48) NULL;

-- Phase 4. `updated_at` is when THIS module last wrote the row, which is not the
-- same fact as when the server last said something — and the pages were reading
-- the first as if it were the second, so a server that had been down for three
-- days rendered "last reported just now" on every failed poll.
--
-- They are genuinely two facts and both are wanted: `updated_at` decides whether
-- the row is stale (a module that stopped polling must not leave a page claiming
-- a server is up), and `last_seen_at` is when a `server.hello` last arrived. Only
-- a successful refresh moves it.
ALTER TABLE rust_server_state ADD COLUMN IF NOT EXISTS last_seen_at DATETIME NULL;