190 lines
9.2 KiB
SQL
190 lines
9.2 KiB
SQL
-- Monitoring + notification subsystem (see docs/monitoring-design.md).
|
|
--
|
|
-- This migration is the consolidation of what were originally migrations
|
|
-- 003_monitoring .. 010_notification_rules. Existing databases already recorded
|
|
-- those versions in schema_migrations and have this schema, so they skip this
|
|
-- file; only fresh databases run it (top to bottom). Columns that were once
|
|
-- added by later ALTERs are folded into their CREATE TABLE definitions here.
|
|
--
|
|
-- Standalone / project-free (SSH-broker model): monitor scope is 'global'
|
|
-- (org-wide, admin) or 'personal' (owner-only, private). Numeric FKs internally,
|
|
-- public_id at the API boundary. Sharing of global monitors is via
|
|
-- monitor_targets; alert routing is via notification_rules + channels.
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Monitors + status + heartbeat history (orig. 003; facets from 004; cert
|
|
-- fields from 006; cert_issuer_known from 009)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS monitors (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
scope TEXT NOT NULL DEFAULT 'global',
|
|
owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
target TEXT NOT NULL DEFAULT '',
|
|
interval_sec INTEGER NOT NULL DEFAULT 60,
|
|
timeout_sec INTEGER NOT NULL DEFAULT 10,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
failure_threshold INTEGER NOT NULL DEFAULT 1,
|
|
config TEXT NOT NULL DEFAULT '{}',
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS monitor_status (
|
|
monitor_id INTEGER PRIMARY KEY REFERENCES monitors(id) ON DELETE CASCADE,
|
|
state TEXT NOT NULL DEFAULT 'pending',
|
|
running INTEGER NOT NULL DEFAULT 0,
|
|
last_check_at INTEGER NOT NULL DEFAULT 0,
|
|
next_check_at INTEGER NOT NULL DEFAULT 0,
|
|
last_latency_ms INTEGER NOT NULL DEFAULT 0,
|
|
last_error TEXT NOT NULL DEFAULT '',
|
|
consecutive_failures INTEGER NOT NULL DEFAULT 0,
|
|
last_state_change_at INTEGER NOT NULL DEFAULT 0,
|
|
cert_expires_at INTEGER NOT NULL DEFAULT 0,
|
|
cert_subject TEXT NOT NULL DEFAULT '',
|
|
cert_issuer TEXT NOT NULL DEFAULT '',
|
|
cert_sans TEXT NOT NULL DEFAULT '',
|
|
cert_host_mismatch INTEGER NOT NULL DEFAULT 0,
|
|
cert_issuer_known INTEGER NOT NULL DEFAULT 0,
|
|
resolved_value TEXT NOT NULL DEFAULT '',
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS monitor_checks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
|
|
started_at INTEGER NOT NULL DEFAULT 0,
|
|
finished_at INTEGER NOT NULL DEFAULT 0,
|
|
ok INTEGER NOT NULL DEFAULT 0,
|
|
state TEXT NOT NULL DEFAULT '',
|
|
latency_ms INTEGER NOT NULL DEFAULT 0,
|
|
message TEXT NOT NULL DEFAULT '',
|
|
facets TEXT NOT NULL DEFAULT '[]'
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_monitors_scope_user ON monitors(scope, owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_status_due ON monitor_status(running, next_check_at);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_checks_monitor_started ON monitor_checks(monitor_id, started_at DESC);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Principal-target sharing for global monitors (orig. 007)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS monitor_targets (
|
|
monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
|
|
principal_kind TEXT NOT NULL, -- 'user' | 'group'
|
|
principal_id INTEGER NOT NULL, -- users(id) or user_groups(id) per kind
|
|
access TEXT NOT NULL DEFAULT 'view', -- 'view' | 'manage'
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (monitor_id, principal_kind, principal_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_targets_principal ON monitor_targets(principal_kind, principal_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Generic event / notification feed (orig. 005; notified_at outbox marker from
|
|
-- 010). Monitoring is the first producer; other subsystems emit the same shape.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
kind TEXT NOT NULL,
|
|
severity TEXT NOT NULL DEFAULT 'info',
|
|
scope TEXT NOT NULL DEFAULT 'global',
|
|
owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
|
|
source_type TEXT NOT NULL DEFAULT '',
|
|
source_id TEXT NOT NULL DEFAULT '',
|
|
title TEXT NOT NULL DEFAULT '',
|
|
body TEXT NOT NULL DEFAULT '',
|
|
payload TEXT NOT NULL DEFAULT '{}',
|
|
dedup_key TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
notified_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Per-user read marker for the in-app feed.
|
|
CREATE TABLE IF NOT EXISTS event_reads (
|
|
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
last_read_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_events_scope_project_created ON events(scope, owner_project_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_events_scope_user_created ON events(scope, owner_user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_events_notified_at ON events(notified_at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Notification transports + channels + monitor attachment (orig. 008)
|
|
-- Layer 1 transport_providers — the mechanism + server config (admin)
|
|
-- Layer 3 notification_channels — named, scoped, selectable objects
|
|
-- monitor_channels — monitor <-> channel (manager-assigned, m:n)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS transport_providers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL, -- 'webhook' | 'smtp' | 'microsoft_graph' | 'ms_teams' | 'slack'
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
config TEXT NOT NULL DEFAULT '{}', -- non-secret config JSON
|
|
secret TEXT NOT NULL DEFAULT '', -- encrypted secret blob, never returned (json:"-")
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_channels (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
scope TEXT NOT NULL DEFAULT 'global', -- 'global' | 'personal'
|
|
owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
transport_provider_id INTEGER NOT NULL REFERENCES transport_providers(id) ON DELETE CASCADE,
|
|
target_kind TEXT NOT NULL DEFAULT 'literal', -- 'literal' | 'user' | 'group'
|
|
address TEXT NOT NULL DEFAULT '', -- literal destination
|
|
principal_id INTEGER, -- users(id)/user_groups(id) when target_kind != 'literal'
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_channels_owner ON notification_channels(owner_user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS monitor_channels (
|
|
monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
|
|
channel_id INTEGER NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (monitor_id, channel_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_monitor_channels_channel ON monitor_channels(channel_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Attribute-matched alert routing (orig. 010). Project/personal rules are OWNED
|
|
-- by that principal and cascade-delete with it (real FKs).
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_rules (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
scope TEXT NOT NULL DEFAULT 'global', -- 'global' | 'project'
|
|
owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
kinds TEXT NOT NULL DEFAULT '', -- comma-separated kinds/prefixes ('repo.mirror_failed', 'repo.*'); empty = all
|
|
min_severity TEXT NOT NULL DEFAULT 'warning', -- 'info' | 'warning' | 'critical'
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_rules_scope ON notification_rules(scope);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_rules_owner_project ON notification_rules(owner_project_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_rule_channels (
|
|
rule_id INTEGER NOT NULL REFERENCES notification_rules(id) ON DELETE CASCADE,
|
|
channel_id INTEGER NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (rule_id, channel_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_notification_rule_channels_channel ON notification_rule_channels(channel_id);
|