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

O Operator
Diff

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

added on local at 2026-07-10 18:57:33

Added
+298
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 3ffaff2dd973
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1-- =====================================================================
2-- DriftSense -- v2 schema (fresh install)
3-- =====================================================================
4-- Idempotent CREATE TABLE IF NOT EXISTS throughout. Run against a
5-- fresh MariaDB/MySQL database named drift_sense (or whatever the
6-- installer's config chose).
7--
8-- v2 changes vs the original SYSTEM_MONITOR schema:
9-- * BLOB_STORE -- content-addressable snapshot storage, gzipped, deduped
10-- * NAMED_RELEASES -- user-named bookmark moments ("before refactor", etc.)
11-- * SERVERS -- multi-server aggregation (agent -> central node)
12-- * AGENT_UPLOADS -- audit log of HMAC-signed agent POSTs
13-- * CONTAINER_TARGETS -- Docker/K8s container awareness (overlay2 paths)
14-- * CONFIG_DRIFT_BASELINES -- baseline for "should-be-immutable" containers
15-- * FILE_CHANGES -- adds blob_sha / diff_blob_sha (references BLOB_STORE),
16-- server_id, container_id, named_release_id, is_ts_only
17-- * SCHEMA_CHANGE -- adds server_id, named_release_id, is_ts_only,
18-- schema_blob_sha (dedup of full schema snapshots)
19--
20-- MariaDB 5.5+ landmine: only ONE auto-CURRENT_TIMESTAMP column per table.
21-- =====================================================================
22
23-- ---------------------------------------------------------------------
24-- BLOB_STORE -- content-addressable, gzipped, dedup'd snapshot storage.
25-- ---------------------------------------------------------------------
26-- Every unique file-content-state is stored once, keyed by SHA-256.
27-- FILE_CHANGES and SCHEMA_CHANGE reference blob_sha -- so if a file
28-- reverts to a previous state, no new storage is used. Compression is
29-- inline gzip; typical text-file dedup ratio is 3-5x plus dedup means
30-- long histories cost single-digit MB per repo per year.
31-- ---------------------------------------------------------------------
32CREATE TABLE IF NOT EXISTS BLOB_STORE (
33 blob_sha CHAR(64) NOT NULL, -- SHA-256 hex
34 content_gz LONGBLOB NOT NULL, -- gzip-compressed content
35 size_uncompressed BIGINT UNSIGNED NOT NULL,
36 size_compressed BIGINT UNSIGNED NOT NULL,
37 ref_count INT UNSIGNED NOT NULL DEFAULT 0, -- how many rows reference this blob
38 first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
39 PRIMARY KEY (blob_sha),
40 KEY idx_ref_count (ref_count),
41 KEY idx_first_seen (first_seen_at)
42) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
43
44-- ---------------------------------------------------------------------
45-- NAMED_RELEASES -- user-named bookmark moments.
46-- ---------------------------------------------------------------------
47-- The killer feature: retroactively label a moment ("before schema
48-- refactor", "customer verified this worked", "before I fired the
49-- intern"). Full-file blobs are guaranteed to persist for any file
50-- referenced by a named release (auto-purge respects this).
51-- ---------------------------------------------------------------------
52CREATE TABLE IF NOT EXISTS NAMED_RELEASES (
53 named_release_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
54 name VARCHAR(120) NOT NULL,
55 description VARCHAR(500) NULL,
56 released_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- moment it applies to
57 released_by VARCHAR(120) NULL, -- operator name/email
58 is_locked TINYINT(1) NOT NULL DEFAULT 1, -- 1 = auto-purge exempt
59 notes TEXT NULL,
60 created_at TIMESTAMP NULL DEFAULT NULL,
61 PRIMARY KEY (named_release_id),
62 KEY idx_released_at (released_at)
63) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
64
65-- ---------------------------------------------------------------------
66-- SERVERS -- multi-server aggregation targets.
67-- ---------------------------------------------------------------------
68-- For customers running DriftSense on multiple servers. Each remote
69-- agent registers here on first heartbeat; changes it POSTs get
70-- stamped with this server_id.
71-- ---------------------------------------------------------------------
72CREATE TABLE IF NOT EXISTS SERVERS (
73 server_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
74 server_name VARCHAR(120) NOT NULL,
75 hostname VARCHAR(255) NULL,
76 ip_address VARCHAR(45) NULL, -- IPv4 or IPv6
77 role ENUM('central','agent','both') NOT NULL DEFAULT 'agent',
78 agent_version VARCHAR(40) NULL,
79 hmac_secret_last_rotated TIMESTAMP NULL DEFAULT NULL,
80 last_heartbeat_at TIMESTAMP NULL DEFAULT NULL,
81 status ENUM('active','offline','disabled') NOT NULL DEFAULT 'active',
82 notes VARCHAR(500) NULL,
83 created_at TIMESTAMP NULL DEFAULT NULL,
84 PRIMARY KEY (server_id),
85 UNIQUE KEY uq_server_name (server_name),
86 KEY idx_heartbeat (last_heartbeat_at),
87 KEY idx_status (status)
88) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
89
90-- Bootstrap: every install has at least one entry for itself.
91INSERT IGNORE INTO SERVERS (server_name, hostname, role, status, created_at)
92VALUES ('local', 'localhost', 'both', 'active', NOW());
93
94-- ---------------------------------------------------------------------
95-- AGENT_UPLOADS -- audit log for HMAC-signed remote agent POSTs.
96-- ---------------------------------------------------------------------
97CREATE TABLE IF NOT EXISTS AGENT_UPLOADS (
98 agent_upload_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
99 server_id INT UNSIGNED NOT NULL,
100 endpoint VARCHAR(80) NOT NULL, -- e.g. 'file_change', 'schema_change'
101 payload_bytes INT UNSIGNED NULL,
102 received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
103 verdict ENUM('accepted','rejected_signature','rejected_stale','rejected_body','error')
104 NOT NULL DEFAULT 'accepted',
105 error_message VARCHAR(500) NULL,
106 PRIMARY KEY (agent_upload_id),
107 KEY idx_server_time (server_id, received_at),
108 KEY idx_verdict (verdict, received_at)
109) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
110
111-- ---------------------------------------------------------------------
112-- CONTAINER_TARGETS -- Docker/K8s container awareness.
113-- ---------------------------------------------------------------------
114-- When docker_awareness_enabled=1, the monitor enumerates containers
115-- via the Docker API + reads their OverlayFS `upper` dirs on the host.
116-- No agent-in-container needed. Records here track which containers we
117-- know about + where their overlay lives.
118-- ---------------------------------------------------------------------
119CREATE TABLE IF NOT EXISTS CONTAINER_TARGETS (
120 container_target_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
121 server_id INT UNSIGNED NOT NULL,
122 orchestrator ENUM('docker','k8s','compose','other') NOT NULL DEFAULT 'docker',
123 container_id VARCHAR(80) NOT NULL, -- short ID (12 chars typical)
124 container_name VARCHAR(200) NULL,
125 image VARCHAR(300) NULL,
126 started_at TIMESTAMP NULL DEFAULT NULL,
127 stopped_at TIMESTAMP NULL DEFAULT NULL,
128 overlay_upper_path VARCHAR(500) NULL, -- /var/lib/docker/overlay2/<id>/diff
129 is_monitored TINYINT(1) NOT NULL DEFAULT 1,
130 last_scanned_at TIMESTAMP NULL DEFAULT NULL,
131 PRIMARY KEY (container_target_id),
132 UNIQUE KEY uq_server_container (server_id, container_id),
133 KEY idx_monitored (is_monitored, last_scanned_at),
134 KEY idx_started (started_at)
135) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
136
137-- ---------------------------------------------------------------------
138-- CONFIG_DRIFT_BASELINES -- "supposedly-immutable container" tracking.
139-- ---------------------------------------------------------------------
140-- Adjacent feature: when a container first launches, we snapshot its
141-- overlay state. Any file written to that overlay AFTER launch =
142-- config drift on a container that shouldn't be self-modifying. Great
143-- signal for compliance officers.
144-- ---------------------------------------------------------------------
145CREATE TABLE IF NOT EXISTS CONFIG_DRIFT_BASELINES (
146 baseline_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
147 container_target_id INT UNSIGNED NOT NULL,
148 baseline_captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
149 file_path VARCHAR(1000) NOT NULL,
150 baseline_blob_sha CHAR(64) NOT NULL, -- ref BLOB_STORE
151 is_drifted TINYINT(1) NOT NULL DEFAULT 0,
152 drift_first_seen_at TIMESTAMP NULL DEFAULT NULL,
153 PRIMARY KEY (baseline_id),
154 KEY idx_container_path (container_target_id),
155 KEY idx_drifted (is_drifted, drift_first_seen_at)
156) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
157
158-- =====================================================================
159-- Legacy tables carried forward from SYSTEM_MONITOR (v1), with v2 adds
160-- =====================================================================
161
162CREATE TABLE IF NOT EXISTS DATABASE_MONITOR_SETTINGS (
163 database_list_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
164 server_id INT UNSIGNED NOT NULL DEFAULT 1, -- which server this DB lives on
165 status TINYINT(1) NOT NULL DEFAULT 0,
166 db_name VARCHAR(255) NULL,
167 hostname VARCHAR(255) NULL,
168 port VARCHAR(255) NULL,
169 username VARCHAR(255) NULL,
170 password VARCHAR(255) NULL,
171 connection_status TINYINT(1) NOT NULL DEFAULT 0,
172 last_connection DATETIME NULL,
173 track_row_count TINYINT(1) NOT NULL DEFAULT 0,
174 notify_emails VARCHAR(1000) NULL,
175 ignore_tables TEXT NULL,
176 track_tables_silently TEXT NULL,
177 PRIMARY KEY (database_list_id),
178 KEY idx_server (server_id)
179) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
180
181CREATE TABLE IF NOT EXISTS FILE_MONITOR_SETTINGS (
182 file_monitor_list_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
183 server_id INT UNSIGNED NOT NULL DEFAULT 1,
184 container_target_id INT UNSIGNED NULL, -- optional, for container scans
185 datetime_last_change DATETIME NULL,
186 scan_path VARCHAR(1000) NULL,
187 ignore_list TEXT NULL,
188 max_file_size_bytes BIGINT UNSIGNED NULL, -- v2: per-scan override
189 file_type_filter VARCHAR(500) NULL, -- v2: e.g. '.php,.pl,.py,.conf'
190 last_scanned DATETIME NULL,
191 status TINYINT(1) NOT NULL DEFAULT 0,
192 scan_name VARCHAR(250) NULL,
193 PRIMARY KEY (file_monitor_list_id),
194 KEY idx_server (server_id),
195 KEY idx_container (container_target_id)
196) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
197
198CREATE TABLE IF NOT EXISTS FILE_CHANGES (
199 file_changes_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
200 file_monitor_list_id INT UNSIGNED NULL,
201 server_id INT UNSIGNED NOT NULL DEFAULT 1,
202 container_target_id INT UNSIGNED NULL,
203 file_name VARCHAR(1000) NULL,
204 blob_sha CHAR(64) NULL, -- v2: ref BLOB_STORE (full content)
205 diff_blob_sha CHAR(64) NULL, -- v2: ref BLOB_STORE (unified diff)
206 named_release_id INT UNSIGNED NULL, -- v2: link if part of a bookmark
207 is_ts_only TINYINT(1) NOT NULL DEFAULT 0, -- v2: timestamp-only change flag
208 date_time DATETIME NULL,
209 status VARCHAR(20) NULL,
210 uid VARCHAR(50) NULL,
211 gid VARCHAR(50) NULL,
212 permissions VARCHAR(20) NULL,
213 comments VARCHAR(400) NULL,
214 PRIMARY KEY (file_changes_id),
215 KEY idx_file_time (file_name(255), date_time),
216 KEY idx_blob (blob_sha),
217 KEY idx_server_time (server_id, date_time),
218 KEY idx_release (named_release_id)
219 -- (FULLTEXT index on file_name was dropped -- older InnoDB doesn't
220 -- support it. Regular prefix key above handles LIKE queries.)
221) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
222
223CREATE TABLE IF NOT EXISTS SCHEMA_CHANGE (
224 schema_change_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
225 server_id INT UNSIGNED NOT NULL DEFAULT 1,
226 database_name VARCHAR(255) NULL,
227 table_name VARCHAR(255) NULL,
228 changes TEXT NULL, -- unified diff of DDL
229 schema_blob_sha CHAR(64) NULL, -- v2: ref BLOB_STORE (full DDL snapshot)
230 named_release_id INT UNSIGNED NULL, -- v2: link if part of a bookmark
231 is_ts_only TINYINT(1) NOT NULL DEFAULT 0,
232 change_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
233 PRIMARY KEY (schema_change_id),
234 KEY idx_server_time (server_id, change_datetime),
235 KEY idx_db_table (database_name, table_name),
236 KEY idx_release (named_release_id)
237) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
238
239CREATE TABLE IF NOT EXISTS TABLE_DATA_CHANGES (
240 table_data_changes_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
241 server_id INT UNSIGNED NOT NULL DEFAULT 1,
242 db_name VARCHAR(255) NULL,
243 table_name VARCHAR(255) NULL,
244 record_count INT UNSIGNED NOT NULL DEFAULT 0,
245 record_count_change VARCHAR(255) NULL,
246 missing_pk_gap_count INT UNSIGNED NULL, -- v2: from Shawn's own notes -- detect deletes
247 date_time DATETIME NULL,
248 PRIMARY KEY (table_data_changes_id),
249 KEY idx_server_time (server_id, date_time),
250 KEY idx_db_table (db_name, table_name)
251) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
252
253CREATE TABLE IF NOT EXISTS TABLE_INFO (
254 table_info_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
255 server_id INT UNSIGNED NOT NULL DEFAULT 1,
256 record_count INT UNSIGNED NOT NULL DEFAULT 0,
257 db_name VARCHAR(255) NULL,
258 table_name VARCHAR(255) NULL,
259 date_time DATETIME NULL,
260 PRIMARY KEY (table_info_id),
261 KEY idx_server (server_id),
262 KEY idx_db_table (db_name, table_name)
263) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
264
265CREATE TABLE IF NOT EXISTS LOCKED_TABLE_LOGS (
266 locked_table_logs_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
267 server_id INT UNSIGNED NOT NULL DEFAULT 1,
268 db_name VARCHAR(255) NULL,
269 table_name VARCHAR(255) NULL,
270 in_use INT UNSIGNED NOT NULL DEFAULT 0,
271 name_locked INT UNSIGNED NOT NULL DEFAULT 0,
272 date_time DATETIME NULL,
273 PRIMARY KEY (locked_table_logs_id),
274 KEY idx_server_time (server_id, date_time),
275 KEY idx_db_table (db_name, table_name)
276) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
277
278CREATE TABLE IF NOT EXISTS FAILED_SCAN_LOGS (
279 failed_scan_logs_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
280 server_id INT UNSIGNED NOT NULL DEFAULT 1,
281 db_name VARCHAR(255) NULL,
282 date_time DATETIME NULL,
283 PRIMARY KEY (failed_scan_logs_id),
284 KEY idx_db (db_name),
285 KEY idx_server_time (server_id, date_time)
286) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
287
288-- ---------------------------------------------------------------------
289-- MONITOR_SETTING -- kept for backward compat + global k/v store
290-- ---------------------------------------------------------------------
291CREATE TABLE IF NOT EXISTS MONITOR_SETTING (
292 monitor_setting_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
293 key_name VARCHAR(150) NULL,
294 key_value VARCHAR(1000) NULL,
295 timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
296 PRIMARY KEY (monitor_setting_id),
297 UNIQUE KEY uq_key_name (key_name)
298) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;