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

O Operator
Diff

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

modified on local at 2026-07-12 00:48:46

Added
+50
lines
Removed
-0
lines
Context
361
unchanged
Blobs
from d13583ca8b7d
to 0fee4fd03c84
Restore this content →
Unified diff
Naive LCS-based line diff. Additions in emerald, deletions in rose. Both sides decompressed live from BLOB_STORE.
11#!/usr/bin/perl
22#======================================================================
33# DriftSense -- /dashboard.cgi
44#
55# Landing page. Shows portfolio-wide health at a glance:
66# * KPI tiles: file changes today | schema changes 7d | named releases | drift alerts
77# * Recent activity table (last 30 file + schema changes, merged, newest first)
88# * Named releases strip (last 5 bookmarks)
99# * Servers status strip
1010#======================================================================
1111use strict;
1212use warnings;
1313use CGI ();
1414use MODS::Config;
1515use MODS::DBConnect;
1616use MODS::Template;
1717use MODS::PageWrapper;
1818use MODS::Charts;
1919
2020my $cfg = MODS::Config->new;
2121my $db = MODS::DBConnect->new;
2222my $tpl = MODS::Template->new;
2323my $wrap = MODS::PageWrapper->new;
2424
2525# Headers
2626$|=1;
2727print "Content-Type: text/html; charset=utf-8\nCache-Control: no-cache\n\n";
2828
2929my $dbh = $db->db_connect
3030 or die "DriftSense: cannot connect to storage DB. Check /etc/drift_sense/drift_sense.conf.\n";
3131
3232# ----- KPI queries ----------------------------------------------------
3333my $file_today = $db->db_readwrite($dbh, q~
3434 SELECT COUNT(*) AS n FROM FILE_CHANGES
3535 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
36~, __FILE__, __LINE__);
37my $file_prev = $db->db_readwrite($dbh, q~
38 SELECT COUNT(*) AS n FROM FILE_CHANGES
39 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 2 DAY)
40 AND date_time < DATE_SUB(NOW(), INTERVAL 1 DAY)
3641~, __FILE__, __LINE__);
3742
3843my $schema_7d = $db->db_readwrite($dbh, q~
3944 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
4045 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
46~, __FILE__, __LINE__);
47my $schema_prev = $db->db_readwrite($dbh, q~
48 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
49 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 14 DAY)
50 AND change_datetime < DATE_SUB(NOW(), INTERVAL 7 DAY)
4151~, __FILE__, __LINE__);
4252
4353my $releases_ct = $db->db_readwrite($dbh, q~
4454 SELECT COUNT(*) AS n FROM NAMED_RELEASES
4555~, __FILE__, __LINE__);
4656
4757my $drifted_ct = $db->db_readwrite($dbh, q~
4858 SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1
4959~, __FILE__, __LINE__);
5060
5161# ----- Recent activity (merged file + schema changes, newest first) --
5262my @file_recent = $db->db_readwrite_multiple($dbh, q~
5363 SELECT 'file' AS kind,
5464 file_changes_id AS id,
5565 file_name AS what,
5666 status AS detail,
5767 date_time AS ts,
5868 UNIX_TIMESTAMP(date_time) AS ts_epoch,
5969 server_id
6070 FROM FILE_CHANGES
6171 ORDER BY date_time DESC
6272 LIMIT 20
6373~, __FILE__, __LINE__);
6474my @schema_recent = $db->db_readwrite_multiple($dbh, q~
6575 SELECT 'schema' AS kind,
6676 schema_change_id AS id,
6777 CONCAT(database_name, '.', table_name) AS what,
6878 changes AS detail,
6979 change_datetime AS ts,
7080 UNIX_TIMESTAMP(change_datetime) AS ts_epoch,
7181 server_id
7282 FROM SCHEMA_CHANGE
7383 ORDER BY change_datetime DESC
7484 LIMIT 20
7585~, __FILE__, __LINE__);
7686my @recent = sort { ($b->{ts} || '') cmp ($a->{ts} || '') } (@file_recent, @schema_recent);
7787@recent = splice(@recent, 0, 20) if @recent > 20;
7888foreach my $r (@recent) {
7989 my $d = $r->{detail} // '';
8090 $d =~ s/\s+/ /g;
8191 $r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d;
8292 $r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what};
8393 $r->{diff_url} = ($r->{kind} eq 'file'
8494 ? "/diff.cgi?kind=file&id=$r->{id}"
8595 : "/diff.cgi?kind=schema&id=$r->{id}");
8696 $r->{ts} //= '';
8797 $r->{ts_epoch} //= 0;
8898 $r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn';
8999}
90100
91101# ----- 90-day activity timeline (stacked bars) -----------------------
92102my $today_dow = 0;
93103my @file_by_day = $db->db_readwrite_multiple($dbh, q~
94104 SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n
95105 FROM FILE_CHANGES
96106 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
97107 GROUP BY d
98108~, __FILE__, __LINE__);
99109my @schema_by_day = $db->db_readwrite_multiple($dbh, q~
100110 SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n
101111 FROM SCHEMA_CHANGE
102112 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY)
103113 GROUP BY d
104114~, __FILE__, __LINE__);
105115my (%file_map, %schema_map);
106116$file_map{$_->{d}} = $_->{n} for @file_by_day;
107117$schema_map{$_->{d}} = $_->{n} for @schema_by_day;
108118
109119use POSIX ();
110120my (@days, @day_keys, @file_series, @schema_series);
111121my $today = time;
112122for (my $i = 89; $i >= 0; $i--) {
113123 my $t = $today - ($i * 86400);
114124 my $key = POSIX::strftime('%Y-%m-%d', localtime($t));
115125 my $lbl = POSIX::strftime('%b %d', localtime($t));
116126 push @days, $lbl;
117127 push @day_keys, $key;
118128 push @file_series, ($file_map{$key} || 0);
119129 push @schema_series, ($schema_map{$key} || 0);
120130}
121131my $timeline_svg = MODS::Charts::stacked_bars(
122132 days => \@days,
123133 day_keys => \@day_keys,
124134 series => { file => \@file_series, schema => \@schema_series },
125135 width => 1080,
126136 height => 180,
127137);
128138
129139# ----- Per-site health (Feature 2) -----------------------------------
130140# One row per active file monitor: total captured, active (non-deleted) files,
131141# last change timestamp. Sparkline of last 30 days per site.
132142my @per_site = $db->db_readwrite_multiple($dbh, q~
133143 SELECT m.file_monitor_list_id AS mid,
134144 m.scan_name,
135145 m.scan_path,
136146 s.server_name,
137147 s.kind AS server_kind,
138148 COUNT(fc.file_changes_id) AS captured_ct,
139149 COUNT(DISTINCT CASE WHEN fc.status != 'deleted'
140150 THEN fc.file_name END) AS active_files,
141151 SUM(CASE WHEN fc.date_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
142152 THEN 1 ELSE 0 END) AS changes_24h,
143153 MAX(UNIX_TIMESTAMP(fc.date_time)) AS last_change_epoch,
144154 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS last_change_h
145155 FROM FILE_MONITOR_SETTINGS m
146156 LEFT JOIN SERVERS s ON s.server_id = m.server_id
147157 LEFT JOIN FILE_CHANGES fc ON fc.file_monitor_list_id = m.file_monitor_list_id
148158 WHERE m.status = 1
149159 GROUP BY m.file_monitor_list_id, m.scan_name, m.scan_path, s.server_name, s.kind
150160 ORDER BY captured_ct DESC
151161~, __FILE__, __LINE__);
152162
153163# 30-day sparkline per monitor (one query, bucket in Perl)
154164my %spark_by_mid;
155165{
156166 my @day_rows = $db->db_readwrite_multiple($dbh, q~
157167 SELECT file_monitor_list_id AS mid,
158168 DATE_FORMAT(date_time, '%Y-%m-%d') AS d,
159169 COUNT(*) AS n
160170 FROM FILE_CHANGES
161171 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
162172 GROUP BY file_monitor_list_id, d
163173 ~, __FILE__, __LINE__);
164174 my %by;
165175 foreach my $r (@day_rows) { $by{$r->{mid}}{$r->{d}} = $r->{n}; }
166176 my $now = time;
167177 my @keys;
168178 for (my $i = 29; $i >= 0; $i--) {
169179 push @keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
170180 }
171181 foreach my $r (@per_site) {
172182 my @series;
173183 for my $k (@keys) { push @series, ($by{$r->{mid}}{$k} || 0); }
174184 $spark_by_mid{$r->{mid}} = \@series;
175185 }
176186}
177187foreach my $r (@per_site) {
178188 $r->{active_files} //= 0;
179189 $r->{captured_ct} //= 0;
180190 $r->{changes_24h} //= 0;
181191 $r->{last_change_epoch} //= 0;
182192 $r->{last_change_h} //= 'never';
183193 $r->{kind_pill} = ($r->{server_kind} // '') eq 'ssh_agent' ? 'pill-info' : 'pill-mute';
184194 my $s = $spark_by_mid{$r->{mid}};
185195 $r->{spark_svg} = $s ? MODS::Charts::sparkline(values => $s, width => 120, height => 28) : '';
186196 $r->{drilldown_url} = "/file_changes.cgi?monitor=" . $r->{mid};
187197 $r->{path_short} = length($r->{scan_path} // '') > 55
188198 ? '...' . substr($r->{scan_path}, -52) : ($r->{scan_path} // '');
189199}
190200
191201# ----- Change-frequency ranking (Feature 5) --------------------------
192202# Top-20 files by change count in the last 30 days -- the "live-config"
193203# heatmap. Shows which files are being edited most, per-site.
194204my @top_files = $db->db_readwrite_multiple($dbh, q~
195205 SELECT fc.file_name,
196206 m.scan_name,
197207 COUNT(*) AS change_ct,
198208 MAX(fc.file_changes_id) AS latest_id,
199209 MAX(UNIX_TIMESTAMP(fc.date_time)) AS latest_epoch,
200210 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS latest_h
201211 FROM FILE_CHANGES fc
202212 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
203213 WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
204214 GROUP BY fc.file_name, m.scan_name
205215 ORDER BY change_ct DESC, latest_epoch DESC
206216 LIMIT 20
207217~, __FILE__, __LINE__);
208218foreach my $t (@top_files) {
209219 $t->{file_short} = length($t->{file_name} // '') > 70
210220 ? '...' . substr($t->{file_name}, -67) : ($t->{file_name} // '');
211221 $t->{diff_url} = "/diff.cgi?kind=file&id=" . ($t->{latest_id} || 0);
212222 $t->{scan_name} //= 'unknown';
213223 $t->{latest_epoch} //= 0;
214224}
215225
216226# ----- Cross-site dedup surfacing (Feature 3, dashboard preview) -----
217227# Blobs that appear in FILE_CHANGES rows tied to >1 distinct monitor.
218228my @shared_blobs = $db->db_readwrite_multiple($dbh, q~
219229 SELECT fc.blob_sha,
220230 COUNT(DISTINCT fc.file_monitor_list_id) AS site_ct,
221231 COUNT(DISTINCT fc.file_name) AS distinct_names,
222232 GROUP_CONCAT(DISTINCT m.scan_name ORDER BY m.scan_name SEPARATOR ' | ') AS scans,
223233 MAX(fc.file_changes_id) AS example_id,
224234 bs.size_uncompressed,
225235 bs.ref_count
226236 FROM FILE_CHANGES fc
227237 JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
228238 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
229239 WHERE fc.blob_sha IS NOT NULL
230240 GROUP BY fc.blob_sha, bs.size_uncompressed, bs.ref_count
231241 HAVING site_ct > 1
232242 ORDER BY site_ct DESC, distinct_names DESC
233243 LIMIT 8
234244~, __FILE__, __LINE__);
235245foreach my $b (@shared_blobs) {
236246 $b->{sha_short} = substr($b->{blob_sha} // '', 0, 12);
237247 $b->{size_h} = _fmt_bytes($b->{size_uncompressed} || 0);
238248 $b->{diff_url} = "/diff.cgi?kind=file&id=" . ($b->{example_id} || 0);
239249 $b->{scans_h} = length($b->{scans} // '') > 90 ? substr($b->{scans}, 0, 87) . '...' : $b->{scans};
240250}
241251
242252sub _fmt_bytes {
243253 my $n = shift; $n //= 0;
244254 return "$n B" if $n < 1024;
245255 return sprintf('%.1f KB', $n/1024) if $n < 1024*1024;
246256 return sprintf('%.1f MB', $n/1024/1024);
247257}
248258
249259# ----- Named releases strip (last 5) ---------------------------------
250260my @releases = $db->db_readwrite_multiple($dbh, q~
251261 SELECT named_release_id, name, description,
252262 DATE_FORMAT(released_at, '%b %d, %Y') AS released_h,
253263 UNIX_TIMESTAMP(released_at) AS released_epoch,
254264 released_by
255265 FROM NAMED_RELEASES
256266 ORDER BY released_at DESC
257267 LIMIT 5
258268~, __FILE__, __LINE__);
259269$_->{released_epoch} //= 0 for @releases;
260270
261271# ----- Server status strip -------------------------------------------
262272my @servers = $db->db_readwrite_multiple($dbh, q~
263273 SELECT server_id, server_name, hostname, role, status,
264274 DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h,
265275 UNIX_TIMESTAMP(last_heartbeat_at) AS heartbeat_epoch
266276 FROM SERVERS
267277 ORDER BY (status='active') DESC, server_id ASC
268278 LIMIT 8
269279~, __FILE__, __LINE__);
270280foreach my $s (@servers) {
271281 $s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok'
272282 : $s->{status} eq 'offline' ? 'pill-bad'
273283 : 'pill-mute';
274284 $s->{last_heartbeat_h} //= 'never';
275285 $s->{heartbeat_epoch} //= 0;
286}
287
288# ----- Stable versions tile (Wave 5 F2 / user ask) ------------------
289my @stable_versions = $db->db_readwrite_multiple($dbh, q~
290 SELECT nr.named_release_id AS id, nr.name, nr.version_label, nr.tier,
291 nr.description,
292 DATE_FORMAT(nr.released_at, '%b %d, %Y') AS released_h,
293 UNIX_TIMESTAMP(nr.released_at) AS released_epoch,
294 (SELECT COUNT(*) FROM NAMED_RELEASE_PINS p WHERE p.named_release_id = nr.named_release_id) AS pin_ct
295 FROM NAMED_RELEASES nr
296 WHERE nr.tier IN ('stable', 'working')
297 ORDER BY (nr.tier = 'stable') DESC, nr.released_at DESC
298 LIMIT 6
299~, __FILE__, __LINE__);
300foreach my $r (@stable_versions) {
301 $r->{label} = $r->{version_label} || $r->{name};
302 $r->{tier_pill} = $r->{tier} eq 'stable' ? 'pill-ok' : 'pill-info';
303 $r->{tier_lbl} = uc($r->{tier});
304 $r->{restore_url} = "/restore_release.cgi?release_id=$r->{id}";
305 $r->{released_epoch} //= 0;
306 $r->{pin_ct} //= 0;
276307}
277308
278309# ----- Pin-drift tile (Wave 5 feature 1) ----------------------------
279310my $drift_kpi = $db->db_readwrite($dbh, q~
280311 SELECT COUNT(*) AS drifted_ct,
281312 COUNT(DISTINCT named_release_id) AS releases_affected
282313 FROM PIN_DRIFT_LOG
283314 WHERE notified = 0
284315 AND detected_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
285316~, __FILE__, __LINE__);
286317
287318my @drift_rows = $db->db_readwrite_multiple($dbh, q~
288319 SELECT d.file_name, d.pinned_sha, d.current_sha,
289320 r.name AS release_name, r.named_release_id AS rid,
290321 UNIX_TIMESTAMP(d.detected_at) AS detected_epoch,
291322 DATE_FORMAT(d.detected_at, '%b %d %H:%i') AS detected_h
292323 FROM PIN_DRIFT_LOG d
293324 LEFT JOIN NAMED_RELEASES r ON r.named_release_id = d.named_release_id
294325 WHERE d.notified = 0
295326 ORDER BY d.drift_id DESC
296327 LIMIT 5
297328~, __FILE__, __LINE__);
298329foreach my $d (@drift_rows) {
299330 $d->{file_short} = length($d->{file_name} // '') > 55
300331 ? '...' . substr($d->{file_name}, -52) : $d->{file_name};
301332 $d->{pinned_short} = substr($d->{pinned_sha} // '', 0, 12);
302333 $d->{current_short} = substr($d->{current_sha} // '', 0, 12);
303334 $d->{restore_url} = "/restore_release.cgi?release_id=" . ($d->{rid} || 0);
304335 $d->{detected_epoch} //= 0;
305336}
306337
307338# ----- Recent restores (last 5) --------------------------------------
308339my @recent_restores = $db->db_readwrite_multiple($dbh, q~
309340 SELECT rl.restore_id, rl.target_file, rl.status,
310341 UNIX_TIMESTAMP(rl.restored_at) AS restored_epoch,
311342 DATE_FORMAT(rl.restored_at, '%b %d %H:%i') AS restored_h,
312343 LEFT(rl.error_message, 100) AS err_short,
313344 s.server_name
314345 FROM RESTORE_LOG rl
315346 LEFT JOIN SERVERS s ON s.server_id = rl.server_id
316347 ORDER BY rl.restore_id DESC
317348 LIMIT 5
318349~, __FILE__, __LINE__);
319350foreach my $r (@recent_restores) {
320351 $r->{status_pill} = $r->{status} eq 'success' ? 'pill-ok' :
321352 $r->{status} eq 'failed' ? 'pill-bad' : 'pill-mute';
322353 $r->{restored_epoch} //= 0;
323354 $r->{file_short} = length($r->{target_file} // '') > 55
324355 ? '...' . substr($r->{target_file}, -52) : $r->{target_file};
325356}
326357
327358$db->db_disconnect($dbh);
359
360sub _trend {
361 my ($cur, $prev) = @_;
362 $cur //= 0; $prev //= 0;
363 if ($prev == 0 && $cur == 0) { return ('flat', '&mdash;'); }
364 if ($prev == 0) { return ('up', '&uarr; new'); }
365 my $delta = ($cur - $prev) / $prev * 100;
366 my $sign = $delta > 5 ? 'up' : $delta < -5 ? 'down' : 'flat';
367 my $arrow = $sign eq 'up' ? '&uarr;' : $sign eq 'down' ? '&darr;' : '&#8226;';
368 return ($sign, sprintf('%s %+.0f%%', $arrow, $delta));
369}
370my ($ft_class, $ft_txt) = _trend(($file_today && $file_today->{n}), ($file_prev && $file_prev->{n}));
371my ($st_class, $st_txt) = _trend(($schema_7d && $schema_7d->{n}), ($schema_prev && $schema_prev->{n}));
328372
329373# ----- Render body ---------------------------------------------------
330374my $tvars = {
331375 kpi_file_today => ($file_today && $file_today->{n}) || 0,
376 kpi_file_prev => ($file_prev && $file_prev->{n}) || 0,
377 kpi_file_class => $ft_class, kpi_file_txt => $ft_txt,
332378 kpi_schema_7d => ($schema_7d && $schema_7d->{n}) || 0,
379 kpi_schema_prev => ($schema_prev && $schema_prev->{n}) || 0,
380 kpi_schema_class => $st_class, kpi_schema_txt => $st_txt,
333381 kpi_releases => ($releases_ct && $releases_ct->{n}) || 0,
334382 kpi_drifted => ($drifted_ct && $drifted_ct->{n}) || 0,
335383 timeline_svg => $timeline_svg,
336384 per_site => \@per_site,
337385 has_per_site => scalar(@per_site) ? 1 : 0,
338386 top_files => \@top_files,
339387 has_top_files => scalar(@top_files) ? 1 : 0,
340388 shared_blobs => \@shared_blobs,
341389 has_shared_blobs => scalar(@shared_blobs) ? 1 : 0,
342390 recent_restores => \@recent_restores,
343391 has_restores => scalar(@recent_restores) ? 1 : 0,
344392 drifted_ct => ($drift_kpi && $drift_kpi->{drifted_ct}) || 0,
345393 releases_affected=> ($drift_kpi && $drift_kpi->{releases_affected}) || 0,
346394 drift_rows => \@drift_rows,
347395 has_drifts => scalar(@drift_rows) ? 1 : 0,
396 stable_versions => \@stable_versions,
397 has_stables => scalar(@stable_versions) ? 1 : 0,
348398 has_recent => scalar(@recent) ? 1 : 0,
349399 recent => \@recent,
350400 has_releases => scalar(@releases) ? 1 : 0,
351401 releases => \@releases,
352402 servers => \@servers,
353403};
354404my @body = $tpl->template('dashboard.html', $tvars);
355405
356406$wrap->wrapper(
357407 page_title => 'Dashboard',
358408 page_key => 'dashboard',
359409 body_html => join('', @body),
360410 userinfo => { display_name => 'Operator' }, # single-user install; auth added later
361411);