Jared Vititoe
8f852ed830
network_events: idx_event_lookup (event_type, target_name, target_detail, resolved_at)
- Covers the upsert_event SELECT which runs every cycle per monitored entity
- Replaces three separate single-column index scans with one covering lookup
suppression_rules: idx_sup_lookup (active, target_type, target_name, target_detail)
- Covers is_suppressed() queries (now redundant for runtime due to in-memory
check_suppressed, but ensures fast get_active_suppressions() loading per cycle)
Both indexes created on live DB (MariaDB LXC 149).
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
55 lines
2.6 KiB
SQL
55 lines
2.6 KiB
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,
|
||
|
||
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;
|
||
|
||
-- ── 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;
|