minecraft-atm10-stats/backend/migrations/001_init.sql

-- Kernschema (Spec §6)

CREATE TABLE players (
  uuid        uuid PRIMARY KEY,
  name        text,
  first_seen  timestamptz NOT NULL DEFAULT now(),
  last_seen   timestamptz,
  hidden      boolean NOT NULL DEFAULT false
);
CREATE INDEX players_name_lower ON players (lower(name));

CREATE TABLE name_history (
  uuid     uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  name     text NOT NULL,
  seen_at  timestamptz NOT NULL,
  PRIMARY KEY (uuid, name)
);
CREATE INDEX name_history_name_lower ON name_history (lower(name));

CREATE TABLE teams (
  team_id  uuid PRIMARY KEY,
  name     text,
  type     text
);

CREATE TABLE team_members (
  team_id  uuid NOT NULL REFERENCES teams(team_id) ON DELETE CASCADE,
  uuid     uuid NOT NULL,
  PRIMARY KEY (team_id, uuid)
);
CREATE INDEX team_members_uuid ON team_members (uuid);

CREATE TABLE events (
  event_id     uuid PRIMARY KEY,
  server_id    text NOT NULL,
  type         text NOT NULL,
  ts           timestamptz NOT NULL,
  received_at  timestamptz NOT NULL DEFAULT now(),
  source       text NOT NULL CHECK (source IN ('push', 'sftp')),
  payload      jsonb NOT NULL
);
CREATE INDEX events_ts ON events (ts);
CREATE INDEX events_source_received ON events (source, received_at DESC);
CREATE INDEX events_player ON events ((payload->>'uuid'), ts);
CREATE INDEX events_type_ts ON events (type, ts);

CREATE TABLE sessions (
  id          bigserial PRIMARY KEY,
  uuid        uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  started_at  timestamptz NOT NULL,
  ended_at    timestamptz,
  end_reason  text
);
CREATE INDEX sessions_open ON sessions (uuid) WHERE ended_at IS NULL;
CREATE INDEX sessions_uuid_started ON sessions (uuid, started_at DESC);

CREATE TABLE stat_current (
  uuid         uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  category     text NOT NULL,
  key          text NOT NULL,
  value        bigint NOT NULL,
  observed_at  timestamptz NOT NULL,
  source       text NOT NULL,
  PRIMARY KEY (uuid, category, key)
);
CREATE INDEX stat_current_board ON stat_current (category, key, value DESC);

CREATE TABLE stat_snapshots (
  uuid         uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  metric       text NOT NULL,
  bucket       timestamptz NOT NULL,
  granularity  text NOT NULL CHECK (granularity IN ('hour', 'day')),
  value        bigint NOT NULL,
  PRIMARY KEY (uuid, metric, granularity, bucket)
);
CREATE INDEX stat_snapshots_bucket ON stat_snapshots (granularity, bucket);

CREATE TABLE deaths (
  event_id     uuid PRIMARY KEY,
  uuid         uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  ts           timestamptz NOT NULL,
  dimension    text NOT NULL,
  cause        text NOT NULL,
  killer_type  text,
  killer_name  text
);
CREATE INDEX deaths_uuid ON deaths (uuid, ts DESC);

CREATE TABLE kills (
  event_id     uuid PRIMARY KEY,
  uuid         uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  ts           timestamptz NOT NULL,
  dimension    text NOT NULL,
  victim_type  text NOT NULL,
  is_boss      boolean NOT NULL,
  weapon       text
);
CREATE INDEX kills_uuid ON kills (uuid, ts DESC);

CREATE TABLE advancements (
  uuid            uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  advancement_id  text NOT NULL,
  achieved_at     timestamptz NOT NULL,
  title           text,
  PRIMARY KEY (uuid, advancement_id)
);

CREATE TABLE dimension_visits (
  uuid       uuid NOT NULL REFERENCES players(uuid) ON DELETE CASCADE,
  dimension  text NOT NULL,
  first_at   timestamptz NOT NULL,
  PRIMARY KEY (uuid, dimension)
);

CREATE TABLE quest_defs (
  quest_id       text PRIMARY KEY,
  chapter_id     text,
  chapter_title  text,
  title          text,
  def_hash       text
);

CREATE TABLE quest_progress (
  team_id       uuid NOT NULL,
  quest_id      text NOT NULL,
  completed_at  timestamptz NOT NULL,
  PRIMARY KEY (team_id, quest_id)
);

CREATE TABLE server_info (
  id                bigserial PRIMARY KEY,
  started_at        timestamptz NOT NULL,
  mc_version        text NOT NULL,
  neoforge_version  text NOT NULL,
  java_version      text NOT NULL,
  mods              jsonb NOT NULL
);

CREATE TABLE server_health (
  bucket        timestamptz PRIMARY KEY,
  mspt_avg      real NOT NULL,
  mspt_p95      real NOT NULL,
  mspt_max      real NOT NULL,
  tps           real NOT NULL,
  players       integer NOT NULL,
  chunks        integer NOT NULL,
  heap_used_mb  real NOT NULL,
  heap_max_mb   real NOT NULL
);

CREATE TABLE sync_state (
  path       text PRIMARY KEY,
  size       bigint NOT NULL,
  mtime      bigint NOT NULL,
  "offset"   bigint NOT NULL DEFAULT 0,
  hash       text,
  synced_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE admin_users (
  id             bigserial PRIMARY KEY,
  username       text NOT NULL UNIQUE,
  password_hash  text NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE admin_sessions (
  id          text PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
  csrf        text NOT NULL,
  expires_at  timestamptz NOT NULL
);

CREATE TABLE ingest_tokens (
  id          bigserial PRIMARY KEY,
  hash        text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  revoked_at  timestamptz
);