3e8b29f2ee
All checks were successful
Test Asgard Runner / test (push) Successful in 2s
Proactive monitoring infrastructure for server health: **Alert Service:** - Population drop detection (configurable % threshold) - FPS degradation monitoring (configurable FPS threshold) - Multi-channel notifications (Discord, Pushbullet, Email) - Spam prevention (30-min duplicate suppression) - Severity levels (Info, Warning, Critical) **Database:** - alert_config table (thresholds per license) - alert_history table (event log with metadata) - 90-day retention with cleanup job **Integration:** - Discord/Pushbullet service integration - Notification config retrieval from public_site_config - Ready for stats pipeline integration Purpose: Server admins get alerted when anomalies occur (population crashes, performance degradation). Configurable thresholds enable proactive server management. Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
60 lines
2.4 KiB
SQL
60 lines
2.4 KiB
SQL
-- Alert configuration and history tables for Phase 2 anomaly detection
|
|
|
|
-- Alert configuration per license (threshold settings)
|
|
CREATE TABLE IF NOT EXISTS alert_config (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
license_id UUID NOT NULL REFERENCES licenses(id) ON DELETE CASCADE,
|
|
|
|
-- Population monitoring
|
|
population_drop_enabled BOOLEAN DEFAULT true,
|
|
population_drop_threshold_percent INTEGER DEFAULT 30, -- Alert if player count drops >30% in 1 hour
|
|
|
|
-- Performance monitoring
|
|
fps_degradation_enabled BOOLEAN DEFAULT true,
|
|
fps_threshold INTEGER DEFAULT 30, -- Alert if FPS below 30
|
|
|
|
-- Notification channels
|
|
notify_discord BOOLEAN DEFAULT true,
|
|
notify_pushbullet BOOLEAN DEFAULT false,
|
|
notify_email BOOLEAN DEFAULT false,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_alert_config_license FOREIGN KEY (license_id) REFERENCES licenses(id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_alert_config_license ON alert_config(license_id);
|
|
|
|
-- Alert history log
|
|
CREATE TABLE IF NOT EXISTS alert_history (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
license_id UUID NOT NULL REFERENCES licenses(id) ON DELETE CASCADE,
|
|
alert_type VARCHAR(50) NOT NULL, -- 'population_drop', 'fps_degradation', 'crash', 'wipe_failed'
|
|
severity VARCHAR(20) NOT NULL, -- 'info', 'warning', 'critical'
|
|
title VARCHAR(255) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
metadata JSONB, -- Additional context (current_fps, player_count_before, player_count_after, etc.)
|
|
|
|
-- Notification status
|
|
notified_discord BOOLEAN DEFAULT false,
|
|
notified_pushbullet BOOLEAN DEFAULT false,
|
|
notified_email BOOLEAN DEFAULT false,
|
|
|
|
triggered_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_alert_history_license FOREIGN KEY (license_id) REFERENCES licenses(id)
|
|
);
|
|
|
|
CREATE INDEX idx_alert_history_license ON alert_history(license_id);
|
|
CREATE INDEX idx_alert_history_triggered ON alert_history(triggered_at DESC);
|
|
CREATE INDEX idx_alert_history_type ON alert_history(alert_type);
|
|
|
|
-- Insert default alert config for existing licenses
|
|
INSERT INTO alert_config (license_id)
|
|
SELECT id FROM licenses
|
|
ON CONFLICT (license_id) DO NOTHING;
|
|
|
|
COMMENT ON TABLE alert_config IS 'Phase 2 alert configuration per license with threshold settings';
|
|
COMMENT ON TABLE alert_history IS 'Alert event log for anomaly detection and notifications';
|