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

O Operator
Diff

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

added on local at 2026-07-10 22:19:05

Added
+171
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to dc2eb4ad0c0f
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 ----------------------------------------------------
33my $file_today = $db->db_readwrite($dbh, q~
34 SELECT COUNT(*) AS n FROM FILE_CHANGES
35 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
36~, __FILE__, __LINE__);
37
38my $schema_7d = $db->db_readwrite($dbh, q~
39 SELECT COUNT(*) AS n FROM SCHEMA_CHANGE
40 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
41~, __FILE__, __LINE__);
42
43my $releases_ct = $db->db_readwrite($dbh, q~
44 SELECT COUNT(*) AS n FROM NAMED_RELEASES
45~, __FILE__, __LINE__);
46
47my $drifted_ct = $db->db_readwrite($dbh, q~
48 SELECT COUNT(*) AS n FROM CONFIG_DRIFT_BASELINES WHERE is_drifted = 1
49~, __FILE__, __LINE__);
50
51# ----- Recent activity (merged file + schema changes, newest first) --
52my @file_recent = $db->db_readwrite_multiple($dbh, q~
53 SELECT 'file' AS kind,
54 file_changes_id AS id,
55 file_name AS what,
56 status AS detail,
57 date_time AS ts,
58 server_id
59 FROM FILE_CHANGES
60 ORDER BY date_time DESC
61 LIMIT 20
62~, __FILE__, __LINE__);
63my @schema_recent = $db->db_readwrite_multiple($dbh, q~
64 SELECT 'schema' AS kind,
65 schema_change_id AS id,
66 CONCAT(database_name, '.', table_name) AS what,
67 changes AS detail,
68 change_datetime AS ts,
69 server_id
70 FROM SCHEMA_CHANGE
71 ORDER BY change_datetime DESC
72 LIMIT 20
73~, __FILE__, __LINE__);
74my @recent = sort { ($b->{ts} || '') cmp ($a->{ts} || '') } (@file_recent, @schema_recent);
75@recent = splice(@recent, 0, 20) if @recent > 20;
76foreach my $r (@recent) {
77 my $d = $r->{detail} // '';
78 $d =~ s/\s+/ /g;
79 $r->{detail} = length($d) > 90 ? substr($d, 0, 87) . '...' : $d;
80 $r->{what} = length($r->{what} // '') > 60 ? substr($r->{what}, 0, 57) . '...' : $r->{what};
81 $r->{diff_url} = ($r->{kind} eq 'file'
82 ? "/diff.cgi?kind=file&id=$r->{id}"
83 : "/diff.cgi?kind=schema&id=$r->{id}");
84 $r->{ts} //= '';
85 $r->{kind_pill} = ($r->{kind} eq 'file') ? 'pill-info' : 'pill-warn';
86}
87
88# ----- 90-day activity timeline (stacked bars) -----------------------
89my $today_dow = 0;
90my @file_by_day = $db->db_readwrite_multiple($dbh, q~
91 SELECT DATE_FORMAT(date_time, '%Y-%m-%d') AS d, COUNT(*) AS n
92 FROM FILE_CHANGES
93 WHERE date_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
94 GROUP BY d
95~, __FILE__, __LINE__);
96my @schema_by_day = $db->db_readwrite_multiple($dbh, q~
97 SELECT DATE_FORMAT(change_datetime, '%Y-%m-%d') AS d, COUNT(*) AS n
98 FROM SCHEMA_CHANGE
99 WHERE change_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY)
100 GROUP BY d
101~, __FILE__, __LINE__);
102my (%file_map, %schema_map);
103$file_map{$_->{d}} = $_->{n} for @file_by_day;
104$schema_map{$_->{d}} = $_->{n} for @schema_by_day;
105
106use POSIX ();
107my (@days, @file_series, @schema_series);
108my $today = time;
109for (my $i = 89; $i >= 0; $i--) {
110 my $t = $today - ($i * 86400);
111 my $key = POSIX::strftime('%Y-%m-%d', localtime($t));
112 my $lbl = POSIX::strftime('%b %d', localtime($t));
113 push @days, $lbl;
114 push @file_series, ($file_map{$key} || 0);
115 push @schema_series, ($schema_map{$key} || 0);
116}
117my $timeline_svg = MODS::Charts::stacked_bars(
118 days => \@days,
119 series => { file => \@file_series, schema => \@schema_series },
120 width => 1080,
121 height => 180,
122);
123
124# ----- Named releases strip (last 5) ---------------------------------
125my @releases = $db->db_readwrite_multiple($dbh, q~
126 SELECT named_release_id, name, description,
127 DATE_FORMAT(released_at, '%b %d, %Y') AS released_h,
128 released_by
129 FROM NAMED_RELEASES
130 ORDER BY released_at DESC
131 LIMIT 5
132~, __FILE__, __LINE__);
133
134# ----- Server status strip -------------------------------------------
135my @servers = $db->db_readwrite_multiple($dbh, q~
136 SELECT server_id, server_name, hostname, role, status,
137 DATE_FORMAT(last_heartbeat_at, '%b %d %H:%i') AS last_heartbeat_h
138 FROM SERVERS
139 ORDER BY (status='active') DESC, server_id ASC
140 LIMIT 8
141~, __FILE__, __LINE__);
142foreach my $s (@servers) {
143 $s->{status_pill} = $s->{status} eq 'active' ? 'pill-ok'
144 : $s->{status} eq 'offline' ? 'pill-bad'
145 : 'pill-mute';
146 $s->{last_heartbeat_h} //= 'never';
147}
148
149$db->db_disconnect($dbh);
150
151# ----- Render body ---------------------------------------------------
152my $tvars = {
153 kpi_file_today => ($file_today && $file_today->{n}) || 0,
154 kpi_schema_7d => ($schema_7d && $schema_7d->{n}) || 0,
155 kpi_releases => ($releases_ct && $releases_ct->{n}) || 0,
156 kpi_drifted => ($drifted_ct && $drifted_ct->{n}) || 0,
157 timeline_svg => $timeline_svg,
158 has_recent => scalar(@recent) ? 1 : 0,
159 recent => \@recent,
160 has_releases => scalar(@releases) ? 1 : 0,
161 releases => \@releases,
162 servers => \@servers,
163};
164my @body = $tpl->template('dashboard.html', $tvars);
165
166$wrap->wrapper(
167 page_title => 'Dashboard',
168 page_key => 'dashboard',
169 body_html => join('', @body),
170 userinfo => { display_name => 'Operator' }, # single-user install; auth added later
171);