Diff

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

added on local at 2026-07-12 00:19:30

Added

+52

lines

Removed

-0

lines

Context

unchanged

Blobs

from
to 7b9981390290

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 wave 4 -- per-monitor retention, pin-drift alerts, drift log
	2
	3	DELIMITER $$
	4	DROP PROCEDURE IF EXISTS _drift_add_col $$
	5	CREATE PROCEDURE _drift_add_col(IN t_name VARCHAR(64), IN c_name VARCHAR(64), IN c_ddl VARCHAR(500))
	6	BEGIN
	7	IF NOT EXISTS (
	8	SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
	9	WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = t_name AND COLUMN_NAME = c_name
	10	) THEN
	11	SET @sql = CONCAT('ALTER TABLE ', t_name, ' ADD COLUMN ', c_name, ' ', c_ddl);
	12	PREPARE stmt FROM @sql;
	13	EXECUTE stmt;
	14	DEALLOCATE PREPARE stmt;
	15	END IF;
	16	END $$
	17	DELIMITER ;
	18
	19	-- Per-monitor retention. NULL = fall back to global config retention_days.
	20	CALL _drift_add_col('FILE_MONITOR_SETTINGS', 'retention_days',
	21	'INT UNSIGNED DEFAULT NULL');
	22
	23	-- Pin-drift alerts on named releases. When alert_delivery_url is set,
	24	-- the dispatcher checks each pin's file_name and fires an alert whenever
	25	-- the current-latest blob_sha differs from the pinned SHA.
	26	CALL _drift_add_col('NAMED_RELEASES', 'alert_delivery_kind',
	27	"ENUM('none','generic_webhook','slack','discord') NOT NULL DEFAULT 'none'");
	28	CALL _drift_add_col('NAMED_RELEASES', 'alert_delivery_url',
	29	'VARCHAR(2000) DEFAULT NULL');
	30	CALL _drift_add_col('NAMED_RELEASES', 'last_pin_drift_check_at',
	31	'DATETIME DEFAULT NULL');
	32
	33	DROP PROCEDURE _drift_add_col;
	34
	35	-- Append-only trail of every pin drift detected. `notified=1` means the
	36	-- alert delivery for this specific (pin_id, current_sha) tuple has been
	37	-- fired; prevents re-sending on each dispatcher tick.
	38	CREATE TABLE IF NOT EXISTS PIN_DRIFT_LOG (
	39	drift_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	40	detected_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	41	named_release_id INT UNSIGNED NOT NULL,
	42	pin_id BIGINT UNSIGNED NOT NULL,
	43	file_name VARCHAR(1000) NOT NULL,
	44	pinned_sha VARCHAR(64) NOT NULL,
	45	current_sha VARCHAR(64) DEFAULT NULL,
	46	file_changes_id BIGINT UNSIGNED DEFAULT NULL,
	47	notified TINYINT(1) NOT NULL DEFAULT 0,
	48	PRIMARY KEY (drift_id),
	49	KEY idx_release (named_release_id),
	50	KEY idx_pin (pin_id),
	51	KEY idx_time (detected_at DESC)
	52	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;