43 lines
1.6 KiB
SQL
43 lines
1.6 KiB
SQL
-- 0014_notifications.sql
|
|
--
|
|
-- Notification channels (operator-configured destinations: webhook,
|
|
-- ntfy, SMTP) and the dispatch log. Both are net-new — no rebuild
|
|
-- pattern needed.
|
|
--
|
|
-- config is an AEAD-encrypted JSON blob. Per-kind shape lives in
|
|
-- internal/notification/{webhook,ntfy,smtp}.go. The CHECK keeps wire
|
|
-- consistency — adding a new kind requires a follow-up migration
|
|
-- (forces the implementer to think about it).
|
|
|
|
CREATE TABLE notification_channels (
|
|
id TEXT PRIMARY KEY,
|
|
kind TEXT NOT NULL CHECK (kind IN ('webhook', 'ntfy', 'smtp')),
|
|
name TEXT NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
|
|
config BLOB NOT NULL, -- AEAD-encrypted JSON; per-kind shape
|
|
default_priority TEXT, -- ntfy only; null for webhook + smtp
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
last_fired_at TEXT
|
|
);
|
|
|
|
CREATE INDEX notification_channels_enabled
|
|
ON notification_channels(enabled) WHERE enabled = 1;
|
|
|
|
CREATE TABLE notification_log (
|
|
id TEXT PRIMARY KEY,
|
|
channel_id TEXT NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
|
|
alert_id TEXT REFERENCES alerts(id) ON DELETE SET NULL,
|
|
event TEXT NOT NULL, -- alert.raised | alert.acknowledged | alert.resolved | alert.test
|
|
ok INTEGER NOT NULL CHECK (ok IN (0, 1)),
|
|
status_code INTEGER,
|
|
latency_ms INTEGER,
|
|
error TEXT,
|
|
fired_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX notification_log_channel
|
|
ON notification_log(channel_id, fired_at DESC);
|
|
CREATE INDEX notification_log_alert
|
|
ON notification_log(alert_id);
|