Diff -- /var/www/vhosts/3dshawn.com/site1/dashboard.cgi
Diff

/var/www/vhosts/3dshawn.com/site1/dashboard.cgi

added on local at 2026-07-13 15:47:48

Added
+415
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 26965f2169c9
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
1#!/usr/bin/perl
2#======================================================================
3# DriftSense -- /dashboard.cgi
4#
5# Landing page. Shows portfolio-wide health at a glance:
6# * KPI tiles: file changes today | schema changes 7d | named releases | drift alerts
7# * Recent activity table (last 30 file + schema changes, merged, newest first)
8# * Named releases strip (last 5 bookmarks)
9# * Servers status strip
10#======================================================================
11use strict;
12use warnings;
13use CGI ();
14use MODS::Config;
15use MODS::DBConnect;
16use MODS::Template;
17use MODS::PageWrapper;
18use MODS::Charts;
19
20my $cfg = MODS::Config->new;
21my $db = MODS::DBConnect->new;
22my $tpl = MODS::Template->new;
23my $wrap = MODS::PageWrapper->new;
24
25# Headers
26$|=1;
27print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n";
28
29my $dbh = $db->db_connect
30 or die "DriftSense: cannot connect to storage DB. Check /etc/drift_sense/drift_sense.conf.\n";
31
32# ----- KPI queries ----------------------------------------------------
33# Exclude is_baseline=1 rows -- those are initial-inventory captures
34# from a fresh monitor scan, not real "changes" the operator should see.
35my $file_today = $db->db_readwrite($dbh, q~
36 SELECT COUNT(*) AS n FROM FILE_CHANGES
37 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
38 AND is_baseline = 0
39~, __FILE__, __LINE__);
40my $file_prev = $db->db_readwrite($dbh, q~
41 SELECT COUNT(*) AS n FROM FILE_CHANGES
42 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 2 DAY)
43 AND date_time < DATE_SUB(NOW(), INTERVAL 1 DAY)
44 AND is_baseline = 0
45~, __FILE__, __LINE__);
46
47my $schema_7d = $db->db_readwrite($dbh, q~
48 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
49 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
50~, __FILE__, __LINE__);
51my $schema_prev = $db->db_readwrite($dbh, q~
52 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
53 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 14 DAY)
54 AND change_datetime < DATE_SUB(NOW(), INTERVAL 7 DAY)
55~, __FILE__, __LINE__);
56
57my $releases_ct = $db->db_readwrite($dbh, q~
58 SELECT COUNT(*) AS n FROM NAMED_RELEASES
59~, __FILE__, __LINE__);
60
61my $drifted_ct = $db->db_readwrite($dbh, q~
62 SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1
63~, __FILE__, __LINE__);
64
65# ----- Recent activity (merged file + schema changes, newest first) --
66my @file_recent = $db->db_readwrite_multiple($dbh, q~
67 SELECT 'file' AS kind,
68 file_changes_id AS id,
69 file_name AS what,
70 status AS detail,
71 date_time AS ts,
72 UNIX_TIMESTAMP(date_time) AS ts_epoch,
73 server_id
74 FROM FILE_CHANGES
75 ORDER BY date_time DESC
76 LIMIT 20
77~, __FILE__, __LINE__);
78my @schema_recent = $db->db_readwrite_multiple($dbh, q~
79 SELECT 'schema' AS kind,
80 schema_change_id AS id,
81 CONCAT(database_name, '.', table_name) AS what,
82 changes AS detail,
83 change_datetime AS ts,
84 UNIX_TIMESTAMP(change_datetime) AS ts_epoch,
85 server_id
86 FROM SCHEMA_CHANGE
87 ORDER BY change_datetime DESC
88 LIMIT 20
89~, __FILE__, __LINE__);
90my @recent = sort { ($b->{ts} || '') cmp ($a->{ts} || '') } (@file_recent, @schema_recent);
91@recent = splice(@recent, 0, 20) if @recent > 20;
92foreach my $r (@recent) {
93 my $d = $r->{detail} // '';
94 $d =~ s/\s+/ /g;
95 $r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d;
96 $r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what};
97 $r->{diff_url} = ($r->{kind} eq 'file'
98 ? "/diff.cgi?kind=file&id=$r->{id}"
99 : "/diff.cgi?kind=schema&id=$r->{id}");
100 $r->{ts} //= '';
101 $r->{ts_epoch} //= 0;
102 $r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn';
103}
104
105# ----- 90-day activity timeline (stacked bars) -----------------------
106my $today_dow = 0;
107my @file_by_day = $db->db_readwrite_multiple($dbh, q~
108 SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n
109 FROM FILE_CHANGES
110 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
111 GROUP BY d
112~, __FILE__, __LINE__);
113my @schema_by_day = $db->db_readwrite_multiple($dbh, q~
114 SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n
115 FROM SCHEMA_CHANGE
116 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY)
117 GROUP BY d
118~, __FILE__, __LINE__);
119my (%file_map, %schema_map);
120$file_map{$_->{d}} = $_->{n} for @file_by_day;
121$schema_map{$_->{d}} = $_->{n} for @schema_by_day;
122
123use POSIX ();
124my (@days, @day_keys, @file_series, @schema_series);
125my $today = time;
126for (my $i = 89; $i >= 0; $i--) {
127 my $t = $today - ($i * 86400);
128 my $key = POSIX::strftime('%Y-%m-%d', localtime($t));
129 my $lbl = POSIX::strftime('%b %d', localtime($t));
130 push @days, $lbl;
131 push @day_keys, $key;
132 push @file_series, ($file_map{$key} || 0);
133 push @schema_series, ($schema_map{$key} || 0);
134}
135my $timeline_svg = MODS::Charts::stacked_bars(
136 days => \@days,
137 day_keys => \@day_keys,
138 series => { file => \@file_series, schema => \@schema_series },
139 width => 1080,
140 height => 180,
141);
142
143# ----- Per-site health (Feature 2) -----------------------------------
144# One row per active file monitor: total captured, active (non-deleted) files,
145# last change timestamp. Sparkline of last 30 days per site.
146my @per_site = $db->db_readwrite_multiple($dbh, q~
147 SELECT m.file_monitor_list_id AS mid,
148 m.scan_name,
149 m.scan_path,
150 s.server_name,
151 s.kind AS server_kind,
152 COUNT(fc.file_changes_id) AS captured_ct,
153 COUNT(DISTINCT CASE WHEN fc.status != 'deleted'
154 THEN fc.file_name END) AS active_files,
155 SUM(CASE WHEN fc.date_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
156 THEN 1 ELSE 0 END) AS changes_24h,
157 MAX(UNIX_TIMESTAMP(fc.date_time)) AS last_change_epoch,
158 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS last_change_h
159 FROM FILE_MONITOR_SETTINGS m
160 LEFT JOIN SERVERS s ON s.server_id = m.server_id
161 LEFT JOIN FILE_CHANGES fc ON fc.file_monitor_list_id = m.file_monitor_list_id
162 WHERE m.status = 1
163 GROUP BY m.file_monitor_list_id, m.scan_name, m.scan_path, s.server_name, s.kind
164 ORDER BY captured_ct DESC
165~, __FILE__, __LINE__);
166
167# 30-day sparkline per monitor (one query, bucket in Perl)
168my %spark_by_mid;
169{
170 my @day_rows = $db->db_readwrite_multiple($dbh, q~
171 SELECT file_monitor_list_id AS mid,
172 DATE_FORMAT(date_time, '%Y-%m-%d') AS d,
173 COUNT(*) AS n
174 FROM FILE_CHANGES
175 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
176 GROUP BY file_monitor_list_id, d
177 ~, __FILE__, __LINE__);
178 my %by;
179 foreach my $r (@day_rows) { $by{$r->{mid}}{$r->{d}} = $r->{n}; }
180 my $now = time;
181 my @keys;
182 for (my $i = 29; $i >= 0; $i--) {
183 push @keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
184 }
185 foreach my $r (@per_site) {
186 my @series;
187 for my $k (@keys) { push @series, ($by{$r->{mid}}{$k} || 0); }
188 $spark_by_mid{$r->{mid}} = \@series;
189 }
190}
191foreach my $r (@per_site) {
192 $r->{active_files} //= 0;
193 $r->{captured_ct} //= 0;
194 $r->{changes_24h} //= 0;
195 $r->{last_change_epoch} //= 0;
196 $r->{last_change_h} //= 'never';
197 $r->{kind_pill} = ($r->{server_kind} // '') eq 'ssh_agent' ? 'pill-info' : 'pill-mute';
198 my $s = $spark_by_mid{$r->{mid}};
199 $r->{spark_svg} = $s ? MODS::Charts::sparkline(values => $s, width => 120, height => 28) : '';
200 $r->{drilldown_url} = "/file_changes.cgi?monitor=" . $r->{mid};
201 $r->{path_short} = length($r->{scan_path} // '') > 55
202 ? '...' . substr($r->{scan_path}, -52) : ($r->{scan_path} // '');
203}
204
205# ----- Change-frequency ranking (Feature 5) --------------------------
206# Top-20 files by change count in the last 30 days -- the "live-config"
207# heatmap. Shows which files are being edited most, per-site.
208my @top_files = $db->db_readwrite_multiple($dbh, q~
209 SELECT fc.file_name,
210 m.scan_name,
211 COUNT(*) AS change_ct,
212 MAX(fc.file_changes_id) AS latest_id,
213 MAX(UNIX_TIMESTAMP(fc.date_time)) AS latest_epoch,
214 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS latest_h
215 FROM FILE_CHANGES fc
216 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
217 WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
218 GROUP BY fc.file_name, m.scan_name
219 ORDER BY change_ct DESC, latest_epoch DESC
220 LIMIT 20
221~, __FILE__, __LINE__);
222foreach my $t (@top_files) {
223 $t->{file_short} = length($t->{file_name} // '') > 70
224 ? '...' . substr($t->{file_name}, -67) : ($t->{file_name} // '');
225 $t->{diff_url} = "/diff.cgi?kind=file&id=" . ($t->{latest_id} || 0);
226 $t->{scan_name} //= 'unknown';
227 $t->{latest_epoch} //= 0;
228}
229
230# ----- Cross-site dedup surfacing (Feature 3, dashboard preview) -----
231# Blobs that appear in FILE_CHANGES rows tied to >1 distinct monitor.
232my @shared_blobs = $db->db_readwrite_multiple($dbh, q~
233 SELECT fc.blob_sha,
234 COUNT(DISTINCT fc.file_monitor_list_id) AS site_ct,
235 COUNT(DISTINCT fc.file_name) AS distinct_names,
236 GROUP_CONCAT(DISTINCT m.scan_name ORDER BY m.scan_name SEPARATOR ' | ') AS scans,
237 MAX(fc.file_changes_id) AS example_id,
238 bs.size_uncompressed,
239 bs.ref_count
240 FROM FILE_CHANGES fc
241 JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
242 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
243 WHERE fc.blob_sha IS NOT NULL
244 GROUP BY fc.blob_sha, bs.size_uncompressed, bs.ref_count
245 HAVING site_ct > 1
246 ORDER BY site_ct DESC, distinct_names DESC
247 LIMIT 8
248~, __FILE__, __LINE__);
249foreach my $b (@shared_blobs) {
250 $b->{sha_short} = substr($b->{blob_sha} // '', 0, 12);
251 $b->{size_h} = _fmt_bytes($b->{size_uncompressed} || 0);
252 $b->{diff_url} = "/diff.cgi?kind=file&id=" . ($b->{example_id} || 0);
253 $b->{scans_h} = length($b->{scans} // '') > 90 ? substr($b->{scans}, 0, 87) . '...' : $b->{scans};
254}
255
256sub _fmt_bytes {
257 my $n = shift; $n //= 0;
258 return "$n B" if $n < 1024;
259 return sprintf('%.1f KB', $n/1024) if $n < 1024*1024;
260 return sprintf('%.1f MB', $n/1024/1024);
261}
262
263# ----- Named releases strip (last 5) ---------------------------------
264my @releases = $db->db_readwrite_multiple($dbh, q~
265 SELECT named_release_id, name, description,
266 DATE_FORMAT(released_at, '%b %d, %Y') AS released_h,
267 UNIX_TIMESTAMP(released_at) AS released_epoch,
268 released_by
269 FROM NAMED_RELEASES
270 ORDER BY released_at DESC
271 LIMIT 5
272~, __FILE__, __LINE__);
273$_->{released_epoch} //= 0 for @releases;
274
275# ----- Server status strip -------------------------------------------
276my @servers = $db->db_readwrite_multiple($dbh, q~
277 SELECT server_id, server_name, hostname, role, status,
278 DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h,
279 UNIX_TIMESTAMP(last_heartbeat_at) AS heartbeat_epoch
280 FROM SERVERS
281 ORDER BY (status='active') DESC, server_id ASC
282 LIMIT 8
283~, __FILE__, __LINE__);
284foreach my $s (@servers) {
285 $s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok'
286 : $s->{status} eq 'offline' ? 'pill-bad'
287 : 'pill-mute';
288 $s->{last_heartbeat_h} //= 'never';
289 $s->{heartbeat_epoch} //= 0;
290}
291
292# ----- Stable versions tile (Wave 5 F2 / user ask) ------------------
293my @stable_versions = $db->db_readwrite_multiple($dbh, q~
294 SELECT nr.named_release_id AS id, nr.name, nr.version_label, nr.tier,
295 nr.description,
296 DATE_FORMAT(nr.released_at, '%b %d, %Y') AS released_h,
297 UNIX_TIMESTAMP(nr.released_at) AS released_epoch,
298 (SELECT COUNT(*) FROM NAMED_RELEASE_PINS p WHERE p.named_release_id = nr.named_release_id) AS pin_ct
299 FROM NAMED_RELEASES nr
300 WHERE nr.tier IN ('stable', 'working')
301 ORDER BY (nr.tier = 'stable') DESC, nr.released_at DESC
302 LIMIT 6
303~, __FILE__, __LINE__);
304foreach my $r (@stable_versions) {
305 $r->{label} = $r->{version_label} || $r->{name};
306 $r->{tier_pill} = $r->{tier} eq 'stable' ? 'pill-ok' : 'pill-info';
307 $r->{tier_lbl} = uc($r->{tier});
308 $r->{restore_url} = "/restore_release.cgi?release_id=$r->{id}";
309 $r->{released_epoch} //= 0;
310 $r->{pin_ct} //= 0;
311}
312
313# ----- Pin-drift tile (Wave 5 feature 1) ----------------------------
314my $drift_kpi = $db->db_readwrite($dbh, q~
315 SELECT COUNT(*) AS drifted_ct,
316 COUNT(DISTINCT named_release_id) AS releases_affected
317 FROM PIN_DRIFT_LOG
318 WHERE notified = 0
319 AND detected_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
320~, __FILE__, __LINE__);
321
322my @drift_rows = $db->db_readwrite_multiple($dbh, q~
323 SELECT d.file_name, d.pinned_sha, d.current_sha,
324 r.name AS release_name, r.named_release_id AS rid,
325 UNIX_TIMESTAMP(d.detected_at) AS detected_epoch,
326 DATE_FORMAT(d.detected_at, '%b %d %H:%i') AS detected_h
327 FROM PIN_DRIFT_LOG d
328 LEFT JOIN NAMED_RELEASES r ON r.named_release_id = d.named_release_id
329 WHERE d.notified = 0
330 ORDER BY d.drift_id DESC
331 LIMIT 5
332~, __FILE__, __LINE__);
333foreach my $d (@drift_rows) {
334 $d->{file_short} = length($d->{file_name} // '') > 55
335 ? '...' . substr($d->{file_name}, -52) : $d->{file_name};
336 $d->{pinned_short} = substr($d->{pinned_sha} // '', 0, 12);
337 $d->{current_short} = substr($d->{current_sha} // '', 0, 12);
338 $d->{restore_url} = "/restore_release.cgi?release_id=" . ($d->{rid} || 0);
339 $d->{detected_epoch} //= 0;
340}
341
342# ----- Recent restores (last 5) --------------------------------------
343my @recent_restores = $db->db_readwrite_multiple($dbh, q~
344 SELECT rl.restore_id, rl.target_file, rl.status,
345 UNIX_TIMESTAMP(rl.restored_at) AS restored_epoch,
346 DATE_FORMAT(rl.restored_at, '%b %d %H:%i') AS restored_h,
347 LEFT(rl.error_message, 100) AS err_short,
348 s.server_name
349 FROM RESTORE_LOG rl
350 LEFT JOIN SERVERS s ON s.server_id = rl.server_id
351 ORDER BY rl.restore_id DESC
352 LIMIT 5
353~, __FILE__, __LINE__);
354foreach my $r (@recent_restores) {
355 $r->{status_pill} = $r->{status} eq 'success' ? 'pill-ok' :
356 $r->{status} eq 'failed' ? 'pill-bad' : 'pill-mute';
357 $r->{restored_epoch} //= 0;
358 $r->{file_short} = length($r->{target_file} // '') > 55
359 ? '...' . substr($r->{target_file}, -52) : $r->{target_file};
360}
361
362$db->db_disconnect($dbh);
363
364sub _trend {
365 my ($cur, $prev) = @_;
366 $cur //= 0; $prev //= 0;
367 if ($prev == 0 && $cur == 0) { return ('flat', '&mdash;'); }
368 if ($prev == 0) { return ('up', '&uarr; new'); }
369 my $delta = ($cur - $prev) / $prev * 100;
370 my $sign = $delta > 5 ? 'up' : $delta < -5 ? 'down' : 'flat';
371 my $arrow = $sign eq 'up' ? '&uarr;' : $sign eq 'down' ? '&darr;' : '&#8226;';
372 return ($sign, sprintf('%s %+.0f%%', $arrow, $delta));
373}
374my ($ft_class, $ft_txt) = _trend(($file_today && $file_today->{n}), ($file_prev && $file_prev->{n}));
375my ($st_class, $st_txt) = _trend(($schema_7d && $schema_7d->{n}), ($schema_prev && $schema_prev->{n}));
376
377# ----- Render body ---------------------------------------------------
378my $tvars = {
379 kpi_file_today => ($file_today && $file_today->{n}) || 0,
380 kpi_file_prev => ($file_prev && $file_prev->{n}) || 0,
381 kpi_file_class => $ft_class, kpi_file_txt => $ft_txt,
382 kpi_schema_7d => ($schema_7d && $schema_7d->{n}) || 0,
383 kpi_schema_prev => ($schema_prev && $schema_prev->{n}) || 0,
384 kpi_schema_class => $st_class, kpi_schema_txt => $st_txt,
385 kpi_releases => ($releases_ct && $releases_ct->{n}) || 0,
386 kpi_drifted => ($drifted_ct && $drifted_ct->{n}) || 0,
387 timeline_svg => $timeline_svg,
388 per_site => \@per_site,
389 has_per_site => scalar(@per_site) ? 1 : 0,
390 top_files => \@top_files,
391 has_top_files => scalar(@top_files) ? 1 : 0,
392 shared_blobs => \@shared_blobs,
393 has_shared_blobs => scalar(@shared_blobs) ? 1 : 0,
394 recent_restores => \@recent_restores,
395 has_restores => scalar(@recent_restores) ? 1 : 0,
396 drifted_ct => ($drift_kpi && $drift_kpi->{drifted_ct}) || 0,
397 releases_affected=> ($drift_kpi && $drift_kpi->{releases_affected}) || 0,
398 drift_rows => \@drift_rows,
399 has_drifts => scalar(@drift_rows) ? 1 : 0,
400 stable_versions => \@stable_versions,
401 has_stables => scalar(@stable_versions) ? 1 : 0,
402 has_recent => scalar(@recent) ? 1 : 0,
403 recent => \@recent,
404 has_releases => scalar(@releases) ? 1 : 0,
405 releases => \@releases,
406 servers => \@servers,
407};
408my @body = $tpl->template('dashboard.html', $tvars);
409
410$wrap->wrapper(
411 page_title => 'Dashboard',
412 page_key => 'dashboard',
413 body_html => join('', @body),
414 userinfo => { display_name => 'Operator' }, # single-user install; auth added later
415);
Keyboard: j next diff k previous diff g top G bottom r restore c compile-check ? help