konnosDashboard
QueryInsights.constants.ts66 lines · main
1export const SUPAMONITOR_EXCLUDED_ROLES = [
2 'briven_admin',
3 'briven_auth_admin',
4 'briven_storage_admin',
5 'briven_realtime_admin',
6 'pgbouncer',
7 'dashboard_user',
8] as const
9
10export const SUPAMONITOR_EXCLUDED_APP_NAMES = ['briven-dashboard', 'mgmt-api'] as const
11
12export const TRANSACTION_CONTROL_REGEX =
13 /^\s*(BEGIN|COMMIT|ROLLBACK|SET\s|RESET\s|DISCARD|DEALLOCATE|SHOW\s)/i
14
15export const SCHEMA_INTROSPECTION_REGEX =
16 /\bFROM\s+(?:pg_catalog\.|information_schema\.|pg_class\b|pg_attribute\b|pg_type\b|pg_namespace\b)/i
17
18export const getSupamonitorLogsQuery = (startTime: string, endTime: string) => {
19 // Validate and canonicalize to ISO 8601 UTC before embedding in SQL.
20 // new Date().toISOString() throws RangeError on invalid input and always
21 // produces "YYYY-MM-DDTHH:mm:ss.mmmZ" which contains no SQL special characters.
22 const safeStart = new Date(startTime).toISOString()
23 const safeEnd = new Date(endTime).toISOString()
24
25 return `
26-- This query is run by Briven Query Insights to aggregate pg_stat_statements
27-- data collected by the supamonitor extension. It reads from Logflare and groups
28-- execution metrics (timing, call counts, percentiles) by query and minute so
29-- the dashboard can surface slow queries, high-call patterns, and planning overhead.
30-- If you see this query in your logs, it is a read-only analytics query and safe to ignore.
31select
32 TIMESTAMP_TRUNC(sml.timestamp, MINUTE) as timestamp,
33 CAST(sml_parsed.application_name AS STRING) as application_name,
34 SUM(sml_parsed.calls) as calls,
35 CAST(sml_parsed.database_name AS STRING) as database_name,
36 CAST(sml_parsed.query AS STRING) as query,
37 sml_parsed.query_id as query_id,
38 SUM(sml_parsed.total_exec_time) as total_exec_time,
39 SUM(sml_parsed.total_plan_time) as total_plan_time,
40 CAST(sml_parsed.user_name AS STRING) as user_name,
41 CASE WHEN SUM(sml_parsed.calls) > 0
42 THEN SUM(sml_parsed.total_exec_time) / SUM(sml_parsed.calls)
43 ELSE 0
44 END as mean_exec_time,
45 MIN(NULLIF(sml_parsed.total_exec_time, 0)) as min_exec_time,
46 MAX(sml_parsed.total_exec_time) as max_exec_time,
47 CASE WHEN SUM(sml_parsed.calls) > 0
48 THEN SUM(sml_parsed.total_plan_time) / SUM(sml_parsed.calls)
49 ELSE 0
50 END as mean_plan_time,
51 MIN(NULLIF(sml_parsed.total_plan_time, 0)) as min_plan_time,
52 MAX(sml_parsed.total_plan_time) as max_plan_time,
53 APPROX_QUANTILES(sml_parsed.total_exec_time, 100)[OFFSET(50)] as p50_exec_time,
54 APPROX_QUANTILES(sml_parsed.total_exec_time, 100)[OFFSET(95)] as p95_exec_time,
55 APPROX_QUANTILES(sml_parsed.total_plan_time, 100)[OFFSET(50)] as p50_plan_time,
56 APPROX_QUANTILES(sml_parsed.total_plan_time, 100)[OFFSET(95)] as p95_plan_time
57from supamonitor_logs as sml
58cross join unnest(sml.metadata) as sml_metadata
59cross join unnest(sml_metadata.supamonitor) as sml_parsed
60WHERE sml.event_message = 'log'
61 AND sml.timestamp >= CAST('${safeStart}' AS TIMESTAMP)
62 AND sml.timestamp <= CAST('${safeEnd}' AS TIMESTAMP)
63GROUP BY timestamp, user_name, database_name, application_name, query_id, query
64ORDER BY timestamp DESC
65`.trim()
66}