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:35:26

Added
+33
lines
Removed
-0
lines
Context
328
unchanged
Blobs
from b7658e07661d
to d13583ca8b7d
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)
3636~, __FILE__, __LINE__);
3737
3838my $schema_7d = $db->db_readwrite($dbh, q~
3939 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
4040 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
4141~, __FILE__, __LINE__);
4242
4343my $releases_ct = $db->db_readwrite($dbh, q~
4444 SELECT COUNT(*) AS n FROM NAMED_RELEASES
4545~, __FILE__, __LINE__);
4646
4747my $drifted_ct = $db->db_readwrite($dbh, q~
4848 SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1
4949~, __FILE__, __LINE__);
5050
5151# ----- Recent activity (merged file + schema changes, newest first) --
5252my @file_recent = $db->db_readwrite_multiple($dbh, q~
5353 SELECT 'file' AS kind,
5454 file_changes_id AS id,
5555 file_name AS what,
5656 status AS detail,
5757 date_time AS ts,
5858 UNIX_TIMESTAMP(date_time) AS ts_epoch,
5959 server_id
6060 FROM FILE_CHANGES
6161 ORDER BY date_time DESC
6262 LIMIT 20
6363~, __FILE__, __LINE__);
6464my @schema_recent = $db->db_readwrite_multiple($dbh, q~
6565 SELECT 'schema' AS kind,
6666 schema_change_id AS id,
6767 CONCAT(database_name, '.', table_name) AS what,
6868 changes AS detail,
6969 change_datetime AS ts,
7070 UNIX_TIMESTAMP(change_datetime) AS ts_epoch,
7171 server_id
7272 FROM SCHEMA_CHANGE
7373 ORDER BY change_datetime DESC
7474 LIMIT 20
7575~, __FILE__, __LINE__);
7676my @recent = sort { ($b->{ts} || '') cmp ($a->{ts} || '') } (@file_recent, @schema_recent);
7777@recent = splice(@recent, 0, 20) if @recent > 20;
7878foreach my $r (@recent) {
7979 my $d = $r->{detail} // '';
8080 $d =~ s/\s+/ /g;
8181 $r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d;
8282 $r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what};
8383 $r->{diff_url} = ($r->{kind} eq 'file'
8484 ? "/diff.cgi?kind=file&id=$r->{id}"
8585 : "/diff.cgi?kind=schema&id=$r->{id}");
8686 $r->{ts} //= '';
8787 $r->{ts_epoch} //= 0;
8888 $r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn';
8989}
9090
9191# ----- 90-day activity timeline (stacked bars) -----------------------
9292my $today_dow = 0;
9393my @file_by_day = $db->db_readwrite_multiple($dbh, q~
9494 SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n
9595 FROM FILE_CHANGES
9696 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
9797 GROUP BY d
9898~, __FILE__, __LINE__);
9999my @schema_by_day = $db->db_readwrite_multiple($dbh, q~
100100 SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n
101101 FROM SCHEMA_CHANGE
102102 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY)
103103 GROUP BY d
104104~, __FILE__, __LINE__);
105105my (%file_map, %schema_map);
106106$file_map{$_->{d}} = $_->{n} for @file_by_day;
107107$schema_map{$_->{d}} = $_->{n} for @schema_by_day;
108108
109109use POSIX ();
110110my (@days, @day_keys, @file_series, @schema_series);
111111my $today = time;
112112for (my $i = 89; $i >= 0; $i--) {
113113 my $t = $today - ($i * 86400);
114114 my $key = POSIX::strftime('%Y-%m-%d', localtime($t));
115115 my $lbl = POSIX::strftime('%b %d', localtime($t));
116116 push @days, $lbl;
117117 push @day_keys, $key;
118118 push @file_series, ($file_map{$key} || 0);
119119 push @schema_series, ($schema_map{$key} || 0);
120120}
121121my $timeline_svg = MODS::Charts::stacked_bars(
122122 days => \@days,
123123 day_keys => \@day_keys,
124124 series => { file => \@file_series, schema => \@schema_series },
125125 width => 1080,
126126 height => 180,
127127);
128128
129129# ----- Per-site health (Feature 2) -----------------------------------
130130# One row per active file monitor: total captured, active (non-deleted) files,
131131# last change timestamp. Sparkline of last 30 days per site.
132132my @per_site = $db->db_readwrite_multiple($dbh, q~
133133 SELECT m.file_monitor_list_id AS mid,
134134 m.scan_name,
135135 m.scan_path,
136136 s.server_name,
137137 s.kind AS server_kind,
138138 COUNT(fc.file_changes_id) AS captured_ct,
139139 COUNT(DISTINCT CASE WHEN fc.status != 'deleted'
140140 THEN fc.file_name END) AS active_files,
141141 SUM(CASE WHEN fc.date_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
142142 THEN 1 ELSE 0 END) AS changes_24h,
143143 MAX(UNIX_TIMESTAMP(fc.date_time)) AS last_change_epoch,
144144 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS last_change_h
145145 FROM FILE_MONITOR_SETTINGS m
146146 LEFT JOIN SERVERS s ON s.server_id = m.server_id
147147 LEFT JOIN FILE_CHANGES fc ON fc.file_monitor_list_id = m.file_monitor_list_id
148148 WHERE m.status = 1
149149 GROUP BY m.file_monitor_list_id, m.scan_name, m.scan_path, s.server_name, s.kind
150150 ORDER BY captured_ct DESC
151151~, __FILE__, __LINE__);
152152
153153# 30-day sparkline per monitor (one query, bucket in Perl)
154154my %spark_by_mid;
155155{
156156 my @day_rows = $db->db_readwrite_multiple($dbh, q~
157157 SELECT file_monitor_list_id AS mid,
158158 DATE_FORMAT(date_time, '%Y-%m-%d') AS d,
159159 COUNT(*) AS n
160160 FROM FILE_CHANGES
161161 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
162162 GROUP BY file_monitor_list_id, d
163163 ~, __FILE__, __LINE__);
164164 my %by;
165165 foreach my $r (@day_rows) { $by{$r->{mid}}{$r->{d}} = $r->{n}; }
166166 my $now = time;
167167 my @keys;
168168 for (my $i = 29; $i >= 0; $i--) {
169169 push @keys, POSIX::strftime('%Y-%m-%d', localtime($now - $i * 86400));
170170 }
171171 foreach my $r (@per_site) {
172172 my @series;
173173 for my $k (@keys) { push @series, ($by{$r->{mid}}{$k} || 0); }
174174 $spark_by_mid{$r->{mid}} = \@series;
175175 }
176176}
177177foreach my $r (@per_site) {
178178 $r->{active_files} //= 0;
179179 $r->{captured_ct} //= 0;
180180 $r->{changes_24h} //= 0;
181181 $r->{last_change_epoch} //= 0;
182182 $r->{last_change_h} //= 'never';
183183 $r->{kind_pill} = ($r->{server_kind} // '') eq 'ssh_agent' ? 'pill-info' : 'pill-mute';
184184 my $s = $spark_by_mid{$r->{mid}};
185185 $r->{spark_svg} = $s ? MODS::Charts::sparkline(values => $s, width => 120, height => 28) : '';
186186 $r->{drilldown_url} = "/file_changes.cgi?monitor=" . $r->{mid};
187187 $r->{path_short} = length($r->{scan_path} // '') > 55
188188 ? '...' . substr($r->{scan_path}, -52) : ($r->{scan_path} // '');
189189}
190190
191191# ----- Change-frequency ranking (Feature 5) --------------------------
192192# Top-20 files by change count in the last 30 days -- the "live-config"
193193# heatmap. Shows which files are being edited most, per-site.
194194my @top_files = $db->db_readwrite_multiple($dbh, q~
195195 SELECT fc.file_name,
196196 m.scan_name,
197197 COUNT(*) AS change_ct,
198198 MAX(fc.file_changes_id) AS latest_id,
199199 MAX(UNIX_TIMESTAMP(fc.date_time)) AS latest_epoch,
200200 DATE_FORMAT(MAX(fc.date_time), '%b %d %H:%i') AS latest_h
201201 FROM FILE_CHANGES fc
202202 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
203203 WHERE fc.date_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
204204 GROUP BY fc.file_name, m.scan_name
205205 ORDER BY change_ct DESC, latest_epoch DESC
206206 LIMIT 20
207207~, __FILE__, __LINE__);
208208foreach my $t (@top_files) {
209209 $t->{file_short} = length($t->{file_name} // '') > 70
210210 ? '...' . substr($t->{file_name}, -67) : ($t->{file_name} // '');
211211 $t->{diff_url} = "/diff.cgi?kind=file&id=" . ($t->{latest_id} || 0);
212212 $t->{scan_name} //= 'unknown';
213213 $t->{latest_epoch} //= 0;
214214}
215215
216216# ----- Cross-site dedup surfacing (Feature 3, dashboard preview) -----
217217# Blobs that appear in FILE_CHANGES rows tied to >1 distinct monitor.
218218my @shared_blobs = $db->db_readwrite_multiple($dbh, q~
219219 SELECT fc.blob_sha,
220220 COUNT(DISTINCT fc.file_monitor_list_id) AS site_ct,
221221 COUNT(DISTINCT fc.file_name) AS distinct_names,
222222 GROUP_CONCAT(DISTINCT m.scan_name ORDER BY m.scan_name SEPARATOR ' | ') AS scans,
223223 MAX(fc.file_changes_id) AS example_id,
224224 bs.size_uncompressed,
225225 bs.ref_count
226226 FROM FILE_CHANGES fc
227227 JOIN BLOB_STORE bs ON bs.blob_sha = fc.blob_sha
228228 LEFT JOIN FILE_MONITOR_SETTINGS m ON m.file_monitor_list_id = fc.file_monitor_list_id
229229 WHERE fc.blob_sha IS NOT NULL
230230 GROUP BY fc.blob_sha, bs.size_uncompressed, bs.ref_count
231231 HAVING site_ct > 1
232232 ORDER BY site_ct DESC, distinct_names DESC
233233 LIMIT 8
234234~, __FILE__, __LINE__);
235235foreach my $b (@shared_blobs) {
236236 $b->{sha_short} = substr($b->{blob_sha} // '', 0, 12);
237237 $b->{size_h} = _fmt_bytes($b->{size_uncompressed} || 0);
238238 $b->{diff_url} = "/diff.cgi?kind=file&id=" . ($b->{example_id} || 0);
239239 $b->{scans_h} = length($b->{scans} // '') > 90 ? substr($b->{scans}, 0, 87) . '...' : $b->{scans};
240240}
241241
242242sub _fmt_bytes {
243243 my $n = shift; $n //= 0;
244244 return "$n B" if $n < 1024;
245245 return sprintf('%.1f KB', $n/1024) if $n < 1024*1024;
246246 return sprintf('%.1f MB', $n/1024/1024);
247247}
248248
249249# ----- Named releases strip (last 5) ---------------------------------
250250my @releases = $db->db_readwrite_multiple($dbh, q~
251251 SELECT named_release_id, name, description,
252252 DATE_FORMAT(released_at, '%b %d, %Y') AS released_h,
253253 UNIX_TIMESTAMP(released_at) AS released_epoch,
254254 released_by
255255 FROM NAMED_RELEASES
256256 ORDER BY released_at DESC
257257 LIMIT 5
258258~, __FILE__, __LINE__);
259259$_->{released_epoch} //= 0 for @releases;
260260
261261# ----- Server status strip -------------------------------------------
262262my @servers = $db->db_readwrite_multiple($dbh, q~
263263 SELECT server_id, server_name, hostname, role, status,
264264 DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h,
265265 UNIX_TIMESTAMP(last_heartbeat_at) AS heartbeat_epoch
266266 FROM SERVERS
267267 ORDER BY (status='active') DESC, server_id ASC
268268 LIMIT 8
269269~, __FILE__, __LINE__);
270270foreach my $s (@servers) {
271271 $s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok'
272272 : $s->{status} eq 'offline' ? 'pill-bad'
273273 : 'pill-mute';
274274 $s->{last_heartbeat_h} //= 'never';
275275 $s->{heartbeat_epoch} //= 0;
276}
277
278# ----- Pin-drift tile (Wave 5 feature 1) ----------------------------
279my $drift_kpi = $db->db_readwrite($dbh, q~
280 SELECT COUNT(*) AS drifted_ct,
281 COUNT(DISTINCT named_release_id) AS releases_affected
282 FROM PIN_DRIFT_LOG
283 WHERE notified = 0
284 AND detected_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
285~, __FILE__, __LINE__);
286
287my @drift_rows = $db->db_readwrite_multiple($dbh, q~
288 SELECT d.file_name, d.pinned_sha, d.current_sha,
289 r.name AS release_name, r.named_release_id AS rid,
290 UNIX_TIMESTAMP(d.detected_at) AS detected_epoch,
291 DATE_FORMAT(d.detected_at, '%b %d %H:%i') AS detected_h
292 FROM PIN_DRIFT_LOG d
293 LEFT JOIN NAMED_RELEASES r ON r.named_release_id = d.named_release_id
294 WHERE d.notified = 0
295 ORDER BY d.drift_id DESC
296 LIMIT 5
297~, __FILE__, __LINE__);
298foreach my $d (@drift_rows) {
299 $d->{file_short} = length($d->{file_name} // '') > 55
300 ? '...' . substr($d->{file_name}, -52) : $d->{file_name};
301 $d->{pinned_short} = substr($d->{pinned_sha} // '', 0, 12);
302 $d->{current_short} = substr($d->{current_sha} // '', 0, 12);
303 $d->{restore_url} = "/restore_release.cgi?release_id=" . ($d->{rid} || 0);
304 $d->{detected_epoch} //= 0;
276305}
277306
278307# ----- Recent restores (last 5) --------------------------------------
279308my @recent_restores = $db->db_readwrite_multiple($dbh, q~
280309 SELECT rl.restore_id, rl.target_file, rl.status,
281310 UNIX_TIMESTAMP(rl.restored_at) AS restored_epoch,
282311 DATE_FORMAT(rl.restored_at, '%b %d %H:%i') AS restored_h,
283312 LEFT(rl.error_message, 100) AS err_short,
284313 s.server_name
285314 FROM RESTORE_LOG rl
286315 LEFT JOIN SERVERS s ON s.server_id = rl.server_id
287316 ORDER BY rl.restore_id DESC
288317 LIMIT 5
289318~, __FILE__, __LINE__);
290319foreach my $r (@recent_restores) {
291320 $r->{status_pill} = $r->{status} eq 'success' ? 'pill-ok' :
292321 $r->{status} eq 'failed' ? 'pill-bad' : 'pill-mute';
293322 $r->{restored_epoch} //= 0;
294323 $r->{file_short} = length($r->{target_file} // '') > 55
295324 ? '...' . substr($r->{target_file}, -52) : $r->{target_file};
296325}
297326
298327$db->db_disconnect($dbh);
299328
300329# ----- Render body ---------------------------------------------------
301330my $tvars = {
302331 kpi_file_today => ($file_today && $file_today->{n}) || 0,
303332 kpi_schema_7d => ($schema_7d && $schema_7d->{n}) || 0,
304333 kpi_releases => ($releases_ct && $releases_ct->{n}) || 0,
305334 kpi_drifted => ($drifted_ct && $drifted_ct->{n}) || 0,
306335 timeline_svg => $timeline_svg,
307336 per_site => \@per_site,
308337 has_per_site => scalar(@per_site) ? 1 : 0,
309338 top_files => \@top_files,
310339 has_top_files => scalar(@top_files) ? 1 : 0,
311340 shared_blobs => \@shared_blobs,
312341 has_shared_blobs => scalar(@shared_blobs) ? 1 : 0,
313342 recent_restores => \@recent_restores,
314343 has_restores => scalar(@recent_restores) ? 1 : 0,
344 drifted_ct => ($drift_kpi && $drift_kpi->{drifted_ct}) || 0,
345 releases_affected=> ($drift_kpi && $drift_kpi->{releases_affected}) || 0,
346 drift_rows => \@drift_rows,
347 has_drifts => scalar(@drift_rows) ? 1 : 0,
315348 has_recent => scalar(@recent) ? 1 : 0,
316349 recent => \@recent,
317350 has_releases => scalar(@releases) ? 1 : 0,
318351 releases => \@releases,
319352 servers => \@servers,
320353};
321354my @body = $tpl->template('dashboard.html', $tvars);
322355
323356$wrap->wrapper(
324357 page_title => 'Dashboard',
325358 page_key => 'dashboard',
326359 body_html => join('', @body),
327360 userinfo => { display_name => 'Operator' }, # single-user install; auth added later
328361);