gandalf/schema.sql

-- Gandalf Network Monitor – Database Schema
-- Run on MariaDB LXC 149 (10.10.10.50)

CREATE DATABASE IF NOT EXISTS gandalf
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gandalf;

-- ── Network events (open and resolved alerts) ─────────────────────────
CREATE TABLE IF NOT EXISTS network_events (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  event_type          VARCHAR(60)  NOT NULL,
  severity            ENUM('critical','warning','info') NOT NULL DEFAULT 'warning',
  source_type         VARCHAR(20)  NOT NULL,           -- 'prometheus', 'unifi', 'ping'
  target_name         VARCHAR(255) NOT NULL,           -- hostname or device name
  target_detail       VARCHAR(255) NOT NULL DEFAULT '', -- interface name, device type, IP
  description         TEXT,
  first_seen          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  last_seen           TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resolved_at         TIMESTAMP    NULL,
  consecutive_failures INT          NOT NULL DEFAULT 1,
  ticket_id           VARCHAR(20)  NULL,
  recovery_notified   TINYINT(1)   NOT NULL DEFAULT 0, -- recovery comment posted on ticket_id

  INDEX idx_active      (resolved_at),
  INDEX idx_target       (target_name, target_detail),
  INDEX idx_type         (event_type),
  -- Compound index for hot upsert_event lookup: event_type+target+resolved_at
  INDEX idx_event_lookup (event_type, target_name, target_detail, resolved_at)
) ENGINE=InnoDB;

-- ── Suppression rules ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS suppression_rules (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  target_type   VARCHAR(50)  NOT NULL,   -- 'host', 'interface', 'unifi_device', 'all'
  target_name   VARCHAR(255) NOT NULL DEFAULT '',
  target_detail VARCHAR(255) NOT NULL DEFAULT '',
  reason        TEXT         NOT NULL,
  suppressed_by VARCHAR(255) NOT NULL,
  created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  expires_at    TIMESTAMP    NULL,       -- NULL = manual (never auto-expires)
  active        BOOLEAN      NOT NULL DEFAULT TRUE,

  INDEX idx_active_exp  (active, expires_at),
  -- For suppression lookup by type+name
  INDEX idx_sup_lookup  (active, target_type, target_name, target_detail)
) ENGINE=InnoDB;

-- ── WAN modem optical history (one row per poll) ──────────────────────
-- Also created automatically at monitor startup (db.ensure_modem_schema).
CREATE TABLE IF NOT EXISTS modem_samples (
  sampled_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY,
  rx_power_dbm DECIMAL(5,1) NULL,
  tx_power_dbm DECIMAL(5,1) NULL,
  temp_c       DECIMAL(5,1) NULL
) ENGINE=InnoDB;

-- ── Monitor state (key/value store for snapshot + baseline) ───────────
CREATE TABLE IF NOT EXISTS monitor_state (
  key_name   VARCHAR(100) PRIMARY KEY,
  value      MEDIUMTEXT   NOT NULL,
  updated_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Cutsheets (planned cabling) ────────────────────────────────────────
-- Also created automatically on first use (db.ensure_cutsheet_schema).
CREATE TABLE IF NOT EXISTS cutsheet_items (       -- panels, jacks, shelves, rack positions
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  kind        VARCHAR(20)  NOT NULL,              -- patch_panel, wall_jack, device, shelf, other
  port_count  SMALLINT     NULL,
  location    VARCHAR(255) NOT NULL DEFAULT '',
  rack_u      SMALLINT     NULL,                  -- bottom rack unit
  rack_height SMALLINT     NULL,
  notes       TEXT         NULL,
  created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_item_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cutsheet_cables (      -- one row per physical cable
  id            INT AUTO_INCREMENT PRIMARY KEY,
  label         VARCHAR(32)  NULL,                -- the ID on the cable, e.g. C07
  a_device      VARCHAR(100) NOT NULL,            -- device or item name
  a_port        VARCHAR(64)  NOT NULL DEFAULT '', -- switch port number, interface, or panel port
  a_side        VARCHAR(5)   NOT NULL DEFAULT '', -- front/rear, for panels and jacks
  b_device      VARCHAR(100) NOT NULL DEFAULT '', -- blank = unterminated
  b_port        VARCHAR(64)  NOT NULL DEFAULT '',
  b_side        VARCHAR(5)   NOT NULL DEFAULT '',
  cable_type    VARCHAR(32)  NOT NULL DEFAULT '',
  length_m      DECIMAL(5,2) NULL,
  color         VARCHAR(32)  NOT NULL DEFAULT '',
  status        VARCHAR(10)  NOT NULL DEFAULT 'installed', -- planned, installed, dark, retired
  label_applied BOOLEAN      NOT NULL DEFAULT FALSE,
  notes         TEXT         NULL,
  created_by    VARCHAR(255) NOT NULL DEFAULT '',
  created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  updated_by    VARCHAR(255) NOT NULL DEFAULT '',
  updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cable_label (label),
  INDEX idx_cable_a (a_device, a_port),
  INDEX idx_cable_b (b_device, b_port)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cutsheet_port_labels ( -- e.g. the room name on a panel port
  item_id INT         NOT NULL,
  port    VARCHAR(64) NOT NULL,
  label   VARCHAR(64) NOT NULL DEFAULT '',
  applied BOOLEAN     NOT NULL DEFAULT FALSE,
  PRIMARY KEY (item_id, port)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cutsheet_history (     -- who changed what, and when
  id          INT AUTO_INCREMENT PRIMARY KEY,
  changed_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  changed_by  VARCHAR(255) NOT NULL,
  entity      VARCHAR(12)  NOT NULL,              -- cable, item, port_label
  entity_id   INT          NOT NULL,
  action      VARCHAR(10)  NOT NULL,              -- create, update, delete
  summary     VARCHAR(500) NOT NULL,
  before_json TEXT         NULL,
  after_json  TEXT         NULL,
  INDEX idx_hist_time (changed_at),
  INDEX idx_hist_entity (entity, entity_id)
) ENGINE=InnoDB;