OpenNIT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

-- Migration 233: Statusseite – Besucher über Störungen benachrichtigen
--
-- Wer wissen will, ob eine Störung begonnen hat oder behoben ist, musste die
-- Statusseite selbst aufrufen. Besucher können ihre Adresse jetzt eintragen
-- und bekommen beide Nachrichten zugeschickt.
--
-- Der Versand läuft über eine Warteschlange statt direkt aus dem Request: Eine
-- Störung wird gemeldet, während Leute darauf warten – das Melden darf nicht
-- so lange dauern, wie der Mailserver für hundert Adressen braucht.

-- ------------------------------------------------------------
-- 1. Eintragungen
-- ------------------------------------------------------------
-- Eine Adresse gilt erst als eingetragen, wenn sie bestätigt wurde
-- (`confirmed_at`). Bis dahin liegt nur die Bitte um Bestätigung vor –
-- so lässt sich niemand mit einer fremden Adresse anmelden.
CREATE TABLE IF NOT EXISTS `status_subscribers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `token` CHAR(64) NOT NULL COMMENT 'Bestätigen und Abmelden – ein Wert für beides',
    `confirmed_at` DATETIME NULL,
    `last_sent_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_status_subscribers_email` (`email`),
    UNIQUE KEY `uq_status_subscribers_token` (`token`),
    INDEX `idx_status_subscribers_confirmed` (`confirmed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Besucher, die über Störungen der Statusseite benachrichtigt werden';

-- ------------------------------------------------------------
-- 2. Warteschlange
-- ------------------------------------------------------------
-- Eine Zeile je Adresse und Ereignis. `sent_at` trennt Offenes von Erledigtem,
-- `attempts` begrenzt das Wiederholen einer dauerhaft scheiternden Zustellung.
CREATE TABLE IF NOT EXISTS `status_notification_queue` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `subscriber_id` BIGINT UNSIGNED NOT NULL,
    `incident_id` BIGINT UNSIGNED NULL,
    `kind` ENUM('opened','resolved') NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `body_html` MEDIUMTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `last_error` VARCHAR(500) NULL,
    `sent_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_status_queue_offen` (`sent_at`, `attempts`, `id`),
    INDEX `idx_status_queue_incident` (`incident_id`),
    CONSTRAINT `fk_status_queue_subscriber` FOREIGN KEY (`subscriber_id`)
        REFERENCES `status_subscribers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_status_queue_incident` FOREIGN KEY (`incident_id`)
        REFERENCES `status_incidents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Ausstehende Benachrichtigungen an Statusseiten-Besucher';

-- ------------------------------------------------------------
-- 3. Versand als Aufgabe der Zeitsteuerung
-- ------------------------------------------------------------
INSERT IGNORE INTO `cron_tasks`
    (`name`, `label`, `description`, `handler_class`, `frequency_minutes`, `enabled`, `run_in_background`, `max_runtime_seconds`)
VALUES
    ('status_notify', 'Statusseite: Benachrichtigungen versenden',
     'Verschickt die ausstehenden Störungs- und Entwarnungsnachrichten an die eingetragenen Besucher der Statusseite.',
     'OpenNIT\\Cron\\Handlers\\StatusNotifyHandler', 5, 1, 0, 300);