music on atproto
plyr.fm
zzstoatzz.io
1-- Client Traffic Dashboard
2-- Shows traffic breakdown by client type (SDK, MCP, browser)
3-- Uses client_type and client_version attributes added via request_attributes_mapper
4
5-- Main Dashboard: Traffic by Client Type
6-- Shows request counts, latency, and error rates grouped by client source
7-- Groups by client_type only (version shown in separate query for SDK/MCP)
8WITH client_requests AS (
9 SELECT
10 attributes->>'client_type' AS client_type,
11 span_name,
12 duration,
13 start_timestamp,
14 otel_status_code,
15 (attributes->>'http.status_code')::int AS status_code
16 FROM records
17 WHERE
18 kind = 'span'
19 AND attributes->>'client_type' IS NOT NULL
20 AND (span_name LIKE 'GET %' OR span_name LIKE 'POST %' OR span_name LIKE 'DELETE %' OR span_name LIKE 'PATCH %')
21)
22SELECT
23 client_type AS "Client Type",
24 COUNT(*) AS "Requests",
25 ROUND(AVG(duration * 1000)::numeric, 2) AS "Avg (ms)",
26 ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration * 1000)::numeric, 2) AS "p95 (ms)",
27 COUNT(*) FILTER (WHERE status_code >= 400) AS "Errors",
28 ROUND((COUNT(*) FILTER (WHERE status_code >= 400)::numeric / NULLIF(COUNT(*), 0) * 100), 2) AS "Error %"
29FROM client_requests
30GROUP BY client_type
31ORDER BY COUNT(*) DESC;
32
33
34-- Alternative: Traffic Over Time by Client Type
35-- Uncomment to see hourly breakdown of traffic sources
36/*
37SELECT
38 DATE_TRUNC('hour', start_timestamp) AS "Hour",
39 attributes->>'client_type' AS "Client Type",
40 COUNT(*) AS "Requests",
41 ROUND(AVG(duration * 1000)::numeric, 2) AS "Avg (ms)"
42FROM records
43WHERE
44 kind = 'span'
45 AND attributes->>'client_type' IS NOT NULL
46 AND (span_name LIKE 'GET %' OR span_name LIKE 'POST %' OR span_name LIKE 'DELETE %')
47GROUP BY DATE_TRUNC('hour', start_timestamp), attributes->>'client_type'
48ORDER BY "Hour" DESC, "Requests" DESC
49LIMIT 100;
50*/
51
52
53-- Alternative: Top Endpoints by Client Type
54-- Uncomment to see which endpoints each client type uses most
55/*
56SELECT
57 attributes->>'client_type' AS "Client Type",
58 span_name AS "Endpoint",
59 COUNT(*) AS "Requests",
60 ROUND(AVG(duration * 1000)::numeric, 2) AS "Avg (ms)"
61FROM records
62WHERE
63 kind = 'span'
64 AND attributes->>'client_type' IS NOT NULL
65 AND (span_name LIKE 'GET %' OR span_name LIKE 'POST %' OR span_name LIKE 'DELETE %')
66GROUP BY attributes->>'client_type', span_name
67ORDER BY attributes->>'client_type', COUNT(*) DESC
68LIMIT 50;
69*/
70
71
72-- Alternative: SDK/MCP Version Distribution
73-- Uncomment to see version adoption for programmatic clients
74/*
75SELECT
76 attributes->>'client_type' AS "Client Type",
77 attributes->>'client_version' AS "Version",
78 COUNT(*) AS "Requests",
79 MIN(start_timestamp) AS "First Seen",
80 MAX(start_timestamp) AS "Last Seen"
81FROM records
82WHERE
83 kind = 'span'
84 AND attributes->>'client_type' IN ('sdk', 'mcp')
85 AND attributes->>'client_version' IS NOT NULL
86GROUP BY attributes->>'client_type', attributes->>'client_version'
87ORDER BY attributes->>'client_type', COUNT(*) DESC;
88*/
89
90
91-- Alternative: Recent SDK/MCP Requests (Debug View)
92-- Uncomment to see individual requests from programmatic clients
93/*
94SELECT
95 start_timestamp AS "Time",
96 attributes->>'client_type' AS "Client",
97 attributes->>'client_version' AS "Version",
98 span_name AS "Endpoint",
99 ROUND(duration * 1000, 2) AS "Duration (ms)",
100 (attributes->>'http.status_code')::int AS "Status"
101FROM records
102WHERE
103 kind = 'span'
104 AND attributes->>'client_type' IN ('sdk', 'mcp')
105ORDER BY start_timestamp DESC
106LIMIT 25;
107*/