Diff -- /var/www/vhosts/3dshawn.com/repricer.3dshawn.com/admin_health.cgi

O Operator
Diff

/var/www/vhosts/3dshawn.com/repricer.3dshawn.com/admin_health.cgi

added on local at 2026-07-11 18:33:29

Added
+252
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 3e7467861e7d
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# RePricer -- platform health dashboard (admins only).
4#
5# Reads the things you need to know when something feels wrong:
6# - Reprice worker: last run, throughput, error count
7# - Subscription/billing worker last run
8# - Marketplace API call log: last 1h success rate by platform
9# - Open marketplace_accounts in error state
10# - User signup velocity (today / 7d / 30d)
11# - Active subscription counts by tier
12# - Recent Stripe webhook events (when we have a stripe_events table)
13# - DB size + table row counts of the high-traffic tables
14#======================================================================
15use strict;
16use warnings;
17
18use lib '/var/www/vhosts/3dshawn.com/repricer.3dshawn.com';
19use CGI;
20use MODS::DBConnect;
21use MODS::Login;
22use MODS::RePricer::Config;
23use MODS::RePricer::Wrapper;
24use MODS::RePricer::Permissions;
25
26$|=1;
27
28my $q = CGI->new;
29my $auth = MODS::Login->new;
30my $wrap = MODS::RePricer::Wrapper->new;
31my $db = MODS::DBConnect->new;
32my $cfg = MODS::RePricer::Config->new;
33my $perm = MODS::RePricer::Permissions->new;
34my $DB = $cfg->settings('database_name');
35
36my $userinfo = $auth->login_verify();
37unless ($userinfo) { print "Status: 302 Found\nLocation: /login.cgi\n\n"; exit; }
38my $uid = $userinfo->{user_id}; $uid =~ s/[^0-9]//g;
39unless ($userinfo->{is_admin}) {
40 print "Status: 302 Found\nLocation: /dashboard.cgi?denied=admin\n\n"; exit;
41}
42
43sub _h { my $s = shift // ''; $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g; $s }
44
45my $dbh = $db->db_connect();
46
47sub _scalar {
48 my $sql = shift;
49 my $r = $db->db_readwrite($dbh, $sql, $ENV{SCRIPT_NAME}, __LINE__);
50 return ($r && defined $r->{n}) ? $r->{n} : 0;
51}
52sub _has_table {
53 my $t = shift;
54 my $r = $db->db_readwrite($dbh, qq~
55 SELECT COUNT(*) AS n FROM information_schema.tables
56 WHERE table_schema=DATABASE() AND table_name='$t'
57 ~, $ENV{SCRIPT_NAME}, __LINE__);
58 return $r && $r->{n} ? 1 : 0;
59}
60
61# ---- Reprice worker stats ------------------------------------------
62my $last_run = _has_table('reprice_runs') ? $db->db_readwrite($dbh, qq~
63 SELECT id, started_at, finished_at, products_evaluated, products_changed,
64 errors_count, triggered_by, TIMESTAMPDIFF(MINUTE, started_at, NOW()) AS mins_ago
65 FROM `${DB}`.reprice_runs ORDER BY id DESC LIMIT 1
66~, $ENV{SCRIPT_NAME}, __LINE__) : undef;
67my $runs_24h = _has_table('reprice_runs')
68 ? _scalar("SELECT COUNT(*) n FROM `${DB}`.reprice_runs WHERE started_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)")
69 : 0;
70my $reprice_errors_24h = _has_table('reprice_runs')
71 ? _scalar("SELECT IFNULL(SUM(errors_count),0) n FROM `${DB}`.reprice_runs WHERE started_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)")
72 : 0;
73
74# ---- Marketplace API call log ---------------------------------------
75my @platforms = qw(amazon ebay walmart);
76my %api_stats;
77if (_has_table('marketplace_call_log')) {
78 foreach my $plat (@platforms) {
79 my $total = _scalar("SELECT COUNT(*) n FROM `${DB}`.marketplace_call_log WHERE platform='$plat' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)");
80 my $ok = _scalar("SELECT COUNT(*) n FROM `${DB}`.marketplace_call_log WHERE platform='$plat' AND ok=1 AND created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)");
81 my $rate = $total ? int(($ok / $total) * 100) : undef;
82 my $last = $db->db_readwrite($dbh, qq~
83 SELECT created_at,
84 UNIX_TIMESTAMP(created_at) AS created_at_epoch,
85 http_status, ok, error_msg
86 FROM `${DB}`.marketplace_call_log WHERE platform='$plat'
87 ORDER BY id DESC LIMIT 1
88 ~, $ENV{SCRIPT_NAME}, __LINE__);
89 $api_stats{$plat} = { total => $total, ok => $ok, rate => $rate, last => $last };
90 }
91}
92
93# ---- Marketplace accounts in error ----------------------------------
94my $errored_accts = _has_table('marketplace_accounts') ? $db->db_readwrite_multiple($dbh, qq~
95 SELECT a.user_id, a.platform, a.account_handle, a.last_error,
96 u.email
97 FROM `${DB}`.marketplace_accounts a
98 JOIN `${DB}`.users u ON u.id = a.user_id
99 WHERE a.status='error' OR (a.last_error IS NOT NULL AND a.last_error<>'')
100 ORDER BY a.updated_at DESC LIMIT 20
101~, $ENV{SCRIPT_NAME}, __LINE__) : [];
102
103# ---- Signup velocity ------------------------------------------------
104my $sign_today = _scalar("SELECT COUNT(*) n FROM `${DB}`.users WHERE DATE(created_at)=DATE(NOW())");
105my $sign_7d = _scalar("SELECT COUNT(*) n FROM `${DB}`.users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)");
106my $sign_30d = _scalar("SELECT COUNT(*) n FROM `${DB}`.users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
107my $users_total = _scalar("SELECT COUNT(*) n FROM `${DB}`.users");
108
109# ---- Subscription counts by tier ------------------------------------
110my %tier_counts;
111if (_has_table('subscriptions')) {
112 foreach my $tier (qw(free starter pro studio)) {
113 $tier_counts{$tier} = _scalar(
114 "SELECT COUNT(*) n FROM `${DB}`.subscriptions s "
115 . "JOIN `${DB}`.billing_plans p ON p.id=s.plan_id "
116 . "WHERE p.tier='$tier' AND s.status IN ('active','trialing','past_due')"
117 );
118 }
119}
120
121# ---- Big-table row counts (for capacity sense) ----------------------
122my %row_counts;
123foreach my $t (qw(users products price_changes competitor_snapshots
124 buybox_history marketplace_call_log notifications)) {
125 $row_counts{$t} = _has_table($t) ? _scalar("SELECT COUNT(*) n FROM `${DB}`.$t") : '--';
126}
127
128$db->db_disconnect($dbh);
129
130# ---- Render ---------------------------------------------------------
131my $body = '<div style="max-width:1100px;margin:0 auto;padding:24px 28px">';
132
133$body .= '<div style="font-size:12px;color:#7e92b6;margin-bottom:8px"><a href="/admin.cgi" style="color:#7e92b6">&larr; Admin</a></div>';
134$body .= '<h1 style="font-size:24px;color:#fff;margin:0 0 6px;font-weight:700">Platform health</h1>';
135$body .= '<p style="color:#7e92b6;font-size:14px;margin:0 0 18px">The page you live on when something feels off. All counts are live.</p>';
136
137# Reprice worker
138$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;padding:18px 22px;margin-bottom:14px">';
139$body .= ' <div style="font-size:11px;letter-spacing:1.5px;color:#7e92b6;text-transform:uppercase;font-weight:600;margin-bottom:10px">Reprice worker</div>';
140if ($last_run && $last_run->{id}) {
141 my $err_col = $last_run->{errors_count} ? '#ef4444' : '#10b981';
142 my $stale = ($last_run->{mins_ago} || 0) > 20;
143 my $stale_html = $stale ? '<span style="color:#fbbf24;font-weight:600">STALE</span>' : '<span style="color:#10b981;font-weight:600">HEALTHY</span>';
144 $body .= ' <div style="display:grid;grid-template-columns:repeat(4,1fr);gap:12px;font-size:13px">';
145 $body .= ' <div><div style="color:#7e92b6;font-size:11px">Last run</div><div style="color:#e6ecf6;font-size:16px;font-weight:600">' . _h($last_run->{mins_ago}) . ' min ago</div></div>';
146 $body .= ' <div><div style="color:#7e92b6;font-size:11px">Runs (24h)</div><div style="color:#e6ecf6;font-size:16px;font-weight:600">' . _h($runs_24h) . '</div></div>';
147 $body .= ' <div><div style="color:#7e92b6;font-size:11px">Errors (24h)</div><div style="color:' . $err_col . ';font-size:16px;font-weight:600">' . _h($reprice_errors_24h) . '</div></div>';
148 $body .= ' <div><div style="color:#7e92b6;font-size:11px">Status</div><div style="font-size:16px">' . $stale_html . '</div></div>';
149 $body .= ' </div>';
150 $body .= ' <div style="margin-top:10px;color:#7e92b6;font-size:12px">Last run #' . _h($last_run->{id}) . ': evaluated ' . _h($last_run->{products_evaluated}) . ', changed ' . _h($last_run->{products_changed}) . ', via ' . _h($last_run->{triggered_by}) . '.</div>';
151} else {
152 $body .= ' <div style="color:#fbbf24;font-size:14px">No reprice runs recorded. Verify cron is wired up: <code>perl /var/www/vhosts/3dshawn.com/repricer.3dshawn.com/_reprice_worker.pl</code></div>';
153}
154$body .= '</section>';
155
156# Marketplace API health
157$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;padding:18px 22px;margin-bottom:14px">';
158$body .= ' <div style="font-size:11px;letter-spacing:1.5px;color:#7e92b6;text-transform:uppercase;font-weight:600;margin-bottom:10px">Marketplace APIs (last 1 hour)</div>';
159$body .= ' <table style="width:100%;font-size:13px;color:#e6ecf6"><thead><tr style="color:#7e92b6;text-align:left;font-size:11px;letter-spacing:1px;text-transform:uppercase"><th style="padding:6px">Platform</th><th style="padding:6px">Calls</th><th style="padding:6px">OK rate</th><th style="padding:6px">Last call</th><th style="padding:6px">Last error</th></tr></thead><tbody>';
160foreach my $plat (@platforms) {
161 my $s = $api_stats{$plat} || {};
162 my $rate = defined $s->{rate} ? "$s->{rate}%" : '--';
163 my $rate_col = !defined $s->{rate} ? '#7e92b6'
164 : $s->{rate} >= 95 ? '#10b981'
165 : $s->{rate} >= 80 ? '#fbbf24'
166 : '#ef4444';
167 my $last_str = '';
168 if ($s->{last} && ref $s->{last} eq 'HASH') {
169 my $err = $s->{last}{error_msg} // '';
170 my $ca = $s->{last}{created_at} // '';
171 my $cae = int($s->{last}{created_at_epoch} || 0);
172 my $ca_h = _h($ca);
173 $last_str = ($cae > 0 && length $ca_h)
174 ? qq~<span class="ts" data-ts="$cae" data-fmt="relative">$ca_h</span>~
175 : $ca_h;
176 $last_str .= ' (' . _h($s->{last}{http_status}) . ')' if $s->{last}{http_status};
177 }
178 my $err_str = '';
179 if ($s->{last} && $s->{last}{error_msg}) {
180 $err_str = _h(substr($s->{last}{error_msg}, 0, 80));
181 }
182 $body .= sprintf(
183 '<tr style="border-top:1px solid #1f2a4a"><td style="padding:8px 6px;color:#14b8a6;font-weight:600">%s</td><td style="padding:8px 6px">%s</td><td style="padding:8px 6px;color:%s;font-weight:600">%s</td><td style="padding:8px 6px;color:#7e92b6">%s</td><td style="padding:8px 6px;color:#fca5a5">%s</td></tr>',
184 ucfirst($plat), _h($s->{total} // 0), $rate_col, $rate, $last_str, $err_str,
185 );
186}
187$body .= '</tbody></table>';
188$body .= '</section>';
189
190# Errored accounts
191$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;padding:18px 22px;margin-bottom:14px">';
192$body .= ' <div style="font-size:11px;letter-spacing:1.5px;color:#7e92b6;text-transform:uppercase;font-weight:600;margin-bottom:10px">Marketplace accounts needing attention</div>';
193if (@$errored_accts) {
194 $body .= ' <table style="width:100%;font-size:13px;color:#e6ecf6"><thead><tr style="color:#7e92b6;text-align:left;font-size:11px;letter-spacing:1px;text-transform:uppercase"><th style="padding:6px">User</th><th style="padding:6px">Platform</th><th style="padding:6px">Account</th><th style="padding:6px">Error</th></tr></thead><tbody>';
195 foreach my $a (@$errored_accts) {
196 $body .= sprintf(
197 '<tr style="border-top:1px solid #1f2a4a"><td style="padding:8px 6px">%s</td><td style="padding:8px 6px">%s</td><td style="padding:8px 6px;font-family:ui-monospace,Menlo,monospace;color:#7e92b6">%s</td><td style="padding:8px 6px;color:#fca5a5;font-size:12px">%s</td></tr>',
198 _h($a->{email}), _h(ucfirst($a->{platform} // '')),
199 _h($a->{account_handle} // ''), _h(substr($a->{last_error} // '', 0, 120)),
200 );
201 }
202 $body .= '</tbody></table>';
203} else {
204 $body .= ' <div style="color:#10b981;font-size:14px">&check; All connected accounts healthy.</div>';
205}
206$body .= '</section>';
207
208# Signups + revenue
209$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;padding:18px 22px;margin-bottom:14px">';
210$body .= ' <div style="font-size:11px;letter-spacing:1.5px;color:#7e92b6;text-transform:uppercase;font-weight:600;margin-bottom:10px">Growth</div>';
211$body .= ' <div style="display:grid;grid-template-columns:repeat(4,1fr);gap:12px;font-size:13px">';
212foreach my $k (
213 [$users_total, 'Total users'],
214 [$sign_today, 'Sign-ups today'],
215 [$sign_7d, 'Sign-ups 7d'],
216 [$sign_30d, 'Sign-ups 30d'],
217) {
218 my $val = _h($k->[0]);
219 my $lbl = _h($k->[1]);
220 $body .= qq~<div><div style="color:#7e92b6;font-size:11px">$lbl</div><div style="color:#e6ecf6;font-size:20px;font-weight:700">$val</div></div>~;
221}
222$body .= ' </div>';
223$body .= ' <div style="margin-top:14px;padding-top:14px;border-top:1px dashed #1f2a4a;display:grid;grid-template-columns:repeat(4,1fr);gap:12px;font-size:13px">';
224foreach my $tier (qw(free starter pro studio)) {
225 my $n = _h($tier_counts{$tier} // 0);
226 my $lbl = ucfirst $tier;
227 $body .= qq~<div><div style="color:#7e92b6;font-size:11px">$lbl subs</div><div style="color:#14b8a6;font-size:18px;font-weight:700">$n</div></div>~;
228}
229$body .= ' </div>';
230$body .= '</section>';
231
232# Row counts
233$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;padding:18px 22px;margin-bottom:14px">';
234$body .= ' <div style="font-size:11px;letter-spacing:1.5px;color:#7e92b6;text-transform:uppercase;font-weight:600;margin-bottom:10px">Table sizes</div>';
235$body .= ' <div style="display:grid;grid-template-columns:repeat(4,1fr);gap:12px;font-size:13px">';
236foreach my $t (qw(users products price_changes competitor_snapshots buybox_history marketplace_call_log notifications)) {
237 my $n = _h($row_counts{$t});
238 $body .= qq~<div><div style="color:#7e92b6;font-size:11px;font-family:ui-monospace,Menlo,monospace">$t</div><div style="color:#e6ecf6;font-size:16px;font-weight:600">$n</div></div>~;
239}
240$body .= ' </div>';
241$body .= '</section>';
242
243$body .= '</div>';
244
245print "Content-Type: text/html; charset=utf-8\nCache-Control: no-store, no-cache, must-revalidate, max-age=0\nPragma: no-cache\nExpires: 0\n\n";
246$wrap->render({
247 userinfo => $userinfo,
248 page_key => 'admin',
249 title => 'Platform health',
250 body => $body,
251});
252exit;