-- login log / security monitor: fast time and IP lookups in the audit log, one alert ticket per finding
ALTER TABLE audit_log ADD KEY created (created_at), ADD KEY ip (ip, created_at);
CREATE TABLE security_alerts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  rule VARCHAR(32) NOT NULL,
  akey VARCHAR(190) NOT NULL,
  ticket_id INT UNSIGNED NULL,
  details VARCHAR(1000) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  UNIQUE KEY one (rule, akey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;