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

O Operator
Diff

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

deleted on local at 2026-07-12 01:52:03

Added
+0
lines
Removed
-46
lines
Context
0
unchanged
Blobs
from eebf4cc73491
to
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1-- DriftSense -- Restore log + per-site scoping for named releases
2--
3-- Idempotent using the standard _drift_add_col helper.
4
5DELIMITER $$
6DROP PROCEDURE IF EXISTS _drift_add_col $$
7CREATE PROCEDURE _drift_add_col(IN t_name VARCHAR(64), IN c_name VARCHAR(64), IN c_ddl VARCHAR(500))
8BEGIN
9 IF NOT EXISTS (
10 SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
11 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = t_name AND COLUMN_NAME = c_name
12 ) THEN
13 SET @sql = CONCAT('ALTER TABLE ', t_name, ' ADD COLUMN ', c_name, ' ', c_ddl);
14 PREPARE stmt FROM @sql;
15 EXECUTE stmt;
16 DEALLOCATE PREPARE stmt;
17 END IF;
18END $$
19DELIMITER ;
20
21-- Feature 4: NAMED_RELEASES gets an optional monitor scope. NULL means
22-- portfolio-wide (existing default behavior); non-NULL pins only blobs
23-- referenced by that monitor.
24CALL _drift_add_col('NAMED_RELEASES', 'scope_monitor_id',
25 "INT UNSIGNED DEFAULT NULL");
26CALL _drift_add_col('NAMED_RELEASES', 'scope_server_id',
27 "INT UNSIGNED DEFAULT NULL");
28
29DROP PROCEDURE _drift_add_col;
30
31-- Feature 1: RESTORE_LOG -- append-only audit trail of every restore action
32CREATE TABLE IF NOT EXISTS RESTORE_LOG (
33 restore_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
34 restored_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
35 source_change_id BIGINT UNSIGNED NOT NULL, -- FILE_CHANGES.file_changes_id used as source
36 server_id INT UNSIGNED NOT NULL,
37 target_file VARCHAR(1000) NOT NULL,
38 source_blob_sha VARCHAR(64) NOT NULL,
39 backup_path VARCHAR(1000) DEFAULT NULL, -- where the pre-restore content was saved
40 status ENUM('success','failed','dry_run') NOT NULL DEFAULT 'success',
41 error_message VARCHAR(500) DEFAULT NULL,
42 restored_by VARCHAR(120) NOT NULL DEFAULT 'operator',
43 PRIMARY KEY (restore_id),
44 KEY idx_target (target_file(255)),
45 KEY idx_time (restored_at DESC)
46) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;