Diff -- /var/www/vhosts/3dshawn.com/site1/_schema/drift_sense_pins_freq_purge.sql

O Operator
Diff

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

added on local at 2026-07-12 00:01:59

Added
+83
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to e6dfff0e5074
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 -- pins, frequency alerts, purge log
2--
3-- Feature 5 (bulk pin): NAMED_RELEASE_PINS explicitly link a release
4-- to specific file_changes_id rows (rather than "everything captured
5-- up to release moment"). Auto-purge honors both.
6--
7-- Feature 4 (frequency alerts): extend ALERT_RULES with two integer
8-- columns. When frequency_threshold > 0 the dispatcher treats the rule
9-- as "fire when file matches the glob and has crossed N changes in the
10-- last M minutes", instead of per-row match.
11--
12-- Feature 2 (auto-purge): PURGE_LOG audit trail of every dropped blob
13-- so operators can trace disk-space reclamation.
14
15CREATE TABLE IF NOT EXISTS NAMED_RELEASE_PINS (
16 pin_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
17 named_release_id INT UNSIGNED NOT NULL,
18 file_changes_id BIGINT UNSIGNED NOT NULL,
19 file_name VARCHAR(1000) DEFAULT NULL,
20 blob_sha VARCHAR(64) DEFAULT NULL,
21 pinned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
22 PRIMARY KEY (pin_id),
23 KEY idx_release (named_release_id),
24 KEY idx_blob (blob_sha),
25 KEY idx_change (file_changes_id)
26) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
27
28CREATE TABLE IF NOT EXISTS PURGE_LOG (
29 purge_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
30 purged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
31 blob_sha VARCHAR(64) NOT NULL,
32 freed_bytes BIGINT UNSIGNED DEFAULT 0,
33 reason VARCHAR(200) DEFAULT NULL,
34 dry_run TINYINT(1) NOT NULL DEFAULT 0,
35 PRIMARY KEY (purge_id),
36 KEY idx_time (purged_at DESC),
37 KEY idx_sha (blob_sha)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
39
40-- Frequency alert columns on ALERT_RULES
41DELIMITER $$
42DROP PROCEDURE IF EXISTS _drift_add_col $$
43CREATE PROCEDURE _drift_add_col(IN t_name VARCHAR(64), IN c_name VARCHAR(64), IN c_ddl VARCHAR(500))
44BEGIN
45 IF NOT EXISTS (
46 SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
47 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = t_name AND COLUMN_NAME = c_name
48 ) THEN
49 SET @sql = CONCAT('ALTER TABLE ', t_name, ' ADD COLUMN ', c_name, ' ', c_ddl);
50 PREPARE stmt FROM @sql;
51 EXECUTE stmt;
52 DEALLOCATE PREPARE stmt;
53 END IF;
54END $$
55DELIMITER ;
56
57CALL _drift_add_col('ALERT_RULES', 'frequency_threshold',
58 'INT UNSIGNED NOT NULL DEFAULT 0');
59CALL _drift_add_col('ALERT_RULES', 'frequency_window_min',
60 'INT UNSIGNED NOT NULL DEFAULT 60');
61CALL _drift_add_col('ALERT_RULES', 'last_frequency_fire_at',
62 'DATETIME DEFAULT NULL');
63
64DROP PROCEDURE _drift_add_col;
65
66-- Global scan settings (single-row table). Ignore-list is UNION'd with
67-- each monitor's own ignore_list; file_type_filter is used when the
68-- monitor's own is empty; max_file_size_bytes is used when monitor's
69-- own is NULL/zero. Sensible defaults for a code-monitoring product.
70CREATE TABLE IF NOT EXISTS SCAN_GLOBALS (
71 id TINYINT UNSIGNED NOT NULL DEFAULT 1,
72 global_ignore_list TEXT DEFAULT NULL,
73 global_file_type_filter VARCHAR(500) DEFAULT NULL,
74 global_max_file_size_bytes BIGINT UNSIGNED DEFAULT NULL,
75 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
76 ON UPDATE CURRENT_TIMESTAMP,
77 PRIMARY KEY (id)
78) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
79
80INSERT IGNORE INTO SCAN_GLOBALS (id, global_ignore_list, global_max_file_size_bytes)
81VALUES (1,
82 '*.swp,*.swo,*~,*.tmp,*.bak,*.pid,*.sock,*.log,*.core,*.pyc,*.class,__pycache__/*,node_modules/*,vendor/*,.git/*,.svn/*,.hg/*,.DS_Store,Thumbs.db,logs/*,cache/*,tmp/*,temp/*,*.min.js.map,*.min.css.map,*.iso,*.zip,*.tar,*.tar.gz,*.tgz,*.rar,*.7z,*.mp4,*.mp3,*.mov,*.avi,*.mkv',
83 10485760);