Diff

/var/www/vhosts/3dshawn.com/site1/_schema/drift_sense_alerts.sql

added on local at 2026-07-11 18:22:55

Added

+75

lines

Removed

-0

lines

Context

unchanged

Blobs

from
to 1fbe0a889983

Restore this content →

Unified diff

Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.

	1	-- DriftSense -- alert rules + delivery schema (Session 2 addition)
	2	--
	3	-- ALERT_RULES: user-authored matchers that fire on FILE_CHANGES /
	4	-- SCHEMA_CHANGE row insert.
	5	-- ALERT_DELIVERIES: append-only audit of every attempted delivery,
	6	-- including HTTP status + response body for debugging.
	7	-- Also stores cursor state for the dispatcher.
	8	-- ALERT_CURSOR: one-row bookmark of the last-scanned change ID per
	9	-- source table. Kept separate from ALERT_DELIVERIES
	10	-- so the dispatcher can advance atomically without
	11	-- racing on a big INSERT.
	12
	13	CREATE TABLE IF NOT EXISTS ALERT_RULES (
	14	alert_rule_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	15	rule_name VARCHAR(200) NOT NULL,
	16	is_active TINYINT(1) NOT NULL DEFAULT 1,
	17
	18	-- Matcher: what kind of change fires this rule
	19	match_kind ENUM('file','schema','either') NOT NULL DEFAULT 'either',
	20	-- glob-style pattern (?, , *) matched against FILE_CHANGES.file_name
	21	-- or SCHEMA_CHANGE.database_name.table_name. Empty = match all.
	22	match_path_glob VARCHAR(500) DEFAULT NULL,
	23	-- comma-separated statuses to match (added, modified, deleted). Empty = all.
	24	match_status_list VARCHAR(200) DEFAULT NULL,
	25	-- if 1, do NOT fire for is_ts_only rows
	26	exclude_ts_only TINYINT(1) NOT NULL DEFAULT 1,
	27
	28	-- Delivery: where alerts go
	29	delivery_kind ENUM('slack','discord','generic_webhook','email') NOT NULL DEFAULT 'generic_webhook',
	30	delivery_url VARCHAR(2000) DEFAULT NULL,
	31	delivery_email VARCHAR(500) DEFAULT NULL,
	32
	33	-- Rate limit: don't fire more than N times per minute (0 = no limit)
	34	rate_limit_per_min INT UNSIGNED NOT NULL DEFAULT 0,
	35	-- Coalesce: group N events into one delivery within a window
	36	coalesce_window_s INT UNSIGNED NOT NULL DEFAULT 0,
	37
	38	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	39	last_fired_at DATETIME DEFAULT NULL,
	40	fire_count INT UNSIGNED NOT NULL DEFAULT 0,
	41	PRIMARY KEY (alert_rule_id),
	42	KEY idx_active (is_active)
	43	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
	44
	45	CREATE TABLE IF NOT EXISTS ALERT_DELIVERIES (
	46	delivery_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	47	alert_rule_id INT UNSIGNED NOT NULL,
	48	fired_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	49	source_kind ENUM('file','schema') NOT NULL,
	50	source_id BIGINT UNSIGNED NOT NULL, -- FILE_CHANGES.file_changes_id or SCHEMA_CHANGE.schema_change_id
	51	match_summary VARCHAR(500) DEFAULT NULL,
	52	delivery_status ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
	53	http_status INT DEFAULT NULL,
	54	http_response TEXT DEFAULT NULL,
	55	error_message TEXT DEFAULT NULL,
	56	PRIMARY KEY (delivery_id),
	57	KEY idx_rule (alert_rule_id, fired_at DESC),
	58	KEY idx_fired (fired_at DESC),
	59	KEY idx_source (source_kind, source_id)
	60	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
	61
	62	CREATE TABLE IF NOT EXISTS ALERT_CURSOR (
	63	source_kind ENUM('file','schema') NOT NULL,
	64	last_seen_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
	65	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	66	PRIMARY KEY (source_kind)
	67	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
	68
	69	-- Seed the cursor at the current maxima so the dispatcher only fires on
	70	-- NEW changes captured after alerts are enabled. Otherwise the first run
	71	-- would spam webhooks with the entire retention window.
	72	INSERT IGNORE INTO ALERT_CURSOR (source_kind, last_seen_id)
	73	SELECT 'file', COALESCE(MAX(file_changes_id), 0) FROM FILE_CHANGES;
	74	INSERT IGNORE INTO ALERT_CURSOR (source_kind, last_seen_id)
	75	SELECT 'schema', COALESCE(MAX(schema_change_id), 0) FROM SCHEMA_CHANGE;