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

O Operator
Diff

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

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

Added
+159
lines
Removed
-0
lines
Context
0
unchanged
Blobs
from
to 5139d3ccae2d
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 admin -- marketplace API call quotas.
4#
5# Aggregates call counts from price_changes (sync_status='sent') + reprice_runs
6# over the trailing 1h / 24h, broken down by marketplace. Lets admins spot a
7# user who's getting throttled, or notice us approaching Amazon's per-account
8# SP-API token bucket ceiling before SP-API starts 429ing us.
9#
10# This is read-only -- no controls to actually pause traffic from here (yet).
11#======================================================================
12use strict;
13use warnings;
14use lib '/var/www/vhosts/3dshawn.com/repricer.3dshawn.com';
15use CGI;
16use MODS::DBConnect;
17use MODS::Login;
18use MODS::RePricer::Config;
19use MODS::RePricer::Wrapper;
20use MODS::RePricer::Admin;
21
22$|=1;
23my $q = CGI->new;
24my $auth = MODS::Login->new;
25my $admin = MODS::RePricer::Admin->new;
26my $wrap = MODS::RePricer::Wrapper->new;
27my $db = MODS::DBConnect->new;
28my $cfg = MODS::RePricer::Config->new;
29my $DB = $cfg->settings('database_name');
30
31my $userinfo = $auth->login_verify();
32unless ($userinfo) { print "Status: 302 Found\nLocation: /login.cgi\n\n"; exit; }
33$admin->require_admin($userinfo);
34
35sub _h { my $s = shift // ''; $s =~ s/&/&amp;/g; $s =~ s/</&lt;/g; $s =~ s/>/&gt;/g; $s =~ s/"/&quot;/g; $s }
36
37my $dbh = $db->db_connect();
38
39# Aggregate by marketplace -- last 1h, last 24h, last 7d.
40my $bm = $db->db_readwrite_multiple($dbh, qq~
41 SELECT p.marketplace AS mp,
42 COUNT(*) AS total,
43 SUM(pc.created_at > NOW() - INTERVAL 1 HOUR) AS h1,
44 SUM(pc.created_at > NOW() - INTERVAL 1 DAY) AS d1,
45 SUM(pc.created_at > NOW() - INTERVAL 7 DAY) AS w1,
46 SUM(pc.sync_status='failed') AS errs
47 FROM `${DB}`.price_changes pc
48 JOIN `${DB}`.products p ON p.id = pc.product_id
49 WHERE pc.created_at > NOW() - INTERVAL 30 DAY
50 GROUP BY p.marketplace
51 ORDER BY h1 DESC
52~, $ENV{SCRIPT_NAME}, __LINE__);
53
54# Top 10 users by 24h call volume.
55my $top = $db->db_readwrite_multiple($dbh, qq~
56 SELECT u.id AS uid, u.email, u.plan_tier, p.marketplace AS mp,
57 COUNT(*) AS calls_24h
58 FROM `${DB}`.price_changes pc
59 JOIN `${DB}`.products p ON p.id = pc.product_id
60 JOIN `${DB}`.users u ON u.id = pc.user_id
61 WHERE pc.created_at > NOW() - INTERVAL 1 DAY
62 GROUP BY u.id, p.marketplace
63 ORDER BY calls_24h DESC
64 LIMIT 10
65~, $ENV{SCRIPT_NAME}, __LINE__);
66
67# Recent worker run timings.
68my $runs = $db->db_readwrite_multiple($dbh, qq~
69 SELECT id, started_at, finished_at, products_evaluated, products_changed, errors_count,
70 TIMESTAMPDIFF(SECOND, started_at, finished_at) AS secs,
71 UNIX_TIMESTAMP(started_at) AS started_at_epoch
72 FROM `${DB}`.reprice_runs
73 ORDER BY id DESC
74 LIMIT 10
75~, $ENV{SCRIPT_NAME}, __LINE__);
76
77$db->db_disconnect($dbh);
78
79# Documented platform ceilings, hardcoded -- these are the public/published
80# limits and don't change often. Used to color rows red when we're approaching.
81my %caps = (
82 amazon => { h1 => 600, d1 => 14400, note => 'SP-API listings_items burst 5/sec, sustained 0.0042/sec per account.' },
83 ebay => { h1 => 5000, d1 => 5000, note => 'eBay Sell API daily quota; check developer.ebay.com for your app key tier.' },
84 walmart => { h1 => 300, d1 => 7200, note => 'Marketplace API: ~3000/hr Items, 600/hr Price update.' },
85);
86
87my $body = '<div style="max-width:1100px;margin:0 auto;padding:24px 28px;color:#e6ecf6">';
88$body .= '<h1 style="font-size:26px;color:#fff;margin:0 0 6px">API rate limits &amp; quota usage</h1>';
89$body .= '<p style="color:#7e92b6;margin:0 0 18px">Outbound calls we make to each marketplace, last 30 days. Watch the 1h column &mdash; if any row turns red, the worker is at risk of being throttled. (Caps shown are public ceilings; your developer tier may differ.)</p>';
90
91$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;overflow:hidden;margin-bottom:20px">';
92$body .= '<table style="width:100%;font-size:13px;border-collapse:collapse">';
93$body .= '<thead><tr style="background:#0a0f1f;color:#7e92b6;text-align:left;font-size:11px;text-transform:uppercase;letter-spacing:1px"><th style="padding:10px 14px">Marketplace</th><th style="padding:10px 14px">Last hour</th><th style="padding:10px 14px">Last 24h</th><th style="padding:10px 14px">Last 7d</th><th style="padding:10px 14px">Errors (30d)</th><th style="padding:10px 14px">Cap / note</th></tr></thead><tbody>';
94foreach my $r (@$bm) {
95 my $mp = lc($r->{mp} // '');
96 my $cap = $caps{$mp} || { h1=>0, d1=>0, note=>'' };
97 my $h1 = $r->{h1} // 0; my $d1 = $r->{d1} // 0;
98 my $h1_color = $cap->{h1} && $h1 > $cap->{h1} * 0.75 ? '#fca5a5' : ($h1 > 0 ? '#10b981' : '#7e92b6');
99 my $d1_color = $cap->{d1} && $d1 > $cap->{d1} * 0.75 ? '#fca5a5' : ($d1 > 0 ? '#10b981' : '#7e92b6');
100 $body .= '<tr style="border-top:1px solid #1f2a4a">';
101 $body .= '<td style="padding:10px 14px;color:#fff;font-weight:600">' . _h($mp) . '</td>';
102 $body .= qq~<td style="padding:10px 14px;color:$h1_color">$h1~ . ($cap->{h1} ? " <span style=\"color:#7e92b6\">/ $cap->{h1}</span>" : '') . '</td>';
103 $body .= qq~<td style="padding:10px 14px;color:$d1_color">$d1~ . ($cap->{d1} ? " <span style=\"color:#7e92b6\">/ $cap->{d1}</span>" : '') . '</td>';
104 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . ($r->{w1} // 0) . '</td>';
105 $body .= '<td style="padding:10px 14px;color:' . (($r->{errs} // 0) > 0 ? '#fca5a5' : '#7e92b6') . '">' . ($r->{errs} // 0) . '</td>';
106 $body .= '<td style="padding:10px 14px;color:#7e92b6;font-size:11px">' . _h($cap->{note}) . '</td>';
107 $body .= '</tr>';
108}
109unless (@$bm) {
110 $body .= '<tr><td colspan="6" style="padding:14px;color:#7e92b6;text-align:center;font-style:italic">No API calls in the last 30 days.</td></tr>';
111}
112$body .= '</tbody></table></section>';
113
114$body .= '<h2 style="font-size:18px;color:#fff;margin:0 0 8px">Top users by 24-hour call volume</h2>';
115$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;overflow:hidden;margin-bottom:20px">';
116$body .= '<table style="width:100%;font-size:13px;border-collapse:collapse">';
117$body .= '<thead><tr style="background:#0a0f1f;color:#7e92b6;text-align:left;font-size:11px;text-transform:uppercase;letter-spacing:1px"><th style="padding:10px 14px">User</th><th style="padding:10px 14px">Plan</th><th style="padding:10px 14px">Marketplace</th><th style="padding:10px 14px">Calls</th></tr></thead><tbody>';
118foreach my $r (@$top) {
119 $body .= '<tr style="border-top:1px solid #1f2a4a">';
120 $body .= '<td style="padding:10px 14px"><a href="/admin_user.cgi?id=' . $r->{uid} . '" style="color:#14b8a6;text-decoration:none">' . _h($r->{email}) . '</a></td>';
121 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . _h($r->{plan_tier} // 'free') . '</td>';
122 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . _h($r->{mp}) . '</td>';
123 $body .= '<td style="padding:10px 14px;color:#fff;font-weight:600">' . ($r->{calls_24h} // 0) . '</td>';
124 $body .= '</tr>';
125}
126unless (@$top) {
127 $body .= '<tr><td colspan="4" style="padding:14px;color:#7e92b6;text-align:center;font-style:italic">No call activity in the last 24 hours.</td></tr>';
128}
129$body .= '</tbody></table></section>';
130
131$body .= '<h2 style="font-size:18px;color:#fff;margin:0 0 8px">Last 10 worker runs</h2>';
132$body .= '<section style="background:#0b1226;border:1px solid #1f2a4a;border-radius:12px;overflow:hidden">';
133$body .= '<table style="width:100%;font-size:13px;border-collapse:collapse">';
134$body .= '<thead><tr style="background:#0a0f1f;color:#7e92b6;text-align:left;font-size:11px;text-transform:uppercase;letter-spacing:1px"><th style="padding:10px 14px">Started</th><th style="padding:10px 14px">Duration</th><th style="padding:10px 14px">Products</th><th style="padding:10px 14px">Changed</th><th style="padding:10px 14px">Errors</th></tr></thead><tbody>';
135foreach my $r (@$runs) {
136 $body .= '<tr style="border-top:1px solid #1f2a4a">';
137 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . do {
138 my $ep = int($r->{started_at_epoch} || 0);
139 my $s = _h($r->{started_at} // '');
140 ($ep > 0 && length $s)
141 ? qq~<span class="ts" data-ts="$ep" data-fmt="datetime">$s</span>~
142 : $s;
143 } . '</td>';
144 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . _h(($r->{secs} // 0) . 's') . '</td>';
145 $body .= '<td style="padding:10px 14px;color:#cbd5e1">' . ($r->{products_evaluated} // 0) . '</td>';
146 $body .= '<td style="padding:10px 14px;color:#10b981">' . ($r->{products_changed} // 0) . '</td>';
147 $body .= '<td style="padding:10px 14px;color:' . (($r->{errors_count} // 0) > 0 ? '#fca5a5' : '#7e92b6') . '">' . ($r->{errors_count} // 0) . '</td>';
148 $body .= '</tr>';
149}
150unless (@$runs) {
151 $body .= '<tr><td colspan="5" style="padding:14px;color:#7e92b6;text-align:center;font-style:italic">No worker runs logged yet.</td></tr>';
152}
153$body .= '</tbody></table></section>';
154
155$body .= '</div>';
156
157print "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";
158$wrap->render({ userinfo => $userinfo, title => 'Admin -- API rate limits', body => $body });
159exit;