konnosDashboard
supamonitor.utils.ts210 lines · main
1import { type SafeSqlFragment } from '@supabase/pg-meta'
2
3import type { QueryPerformanceRow } from '../../QueryPerformance/QueryPerformance.types'
4import type { Logs } from '../../Settings/Logs/Logs.types'
5import {
6 SCHEMA_INTROSPECTION_REGEX,
7 SUPAMONITOR_EXCLUDED_APP_NAMES,
8 SUPAMONITOR_EXCLUDED_ROLES,
9 TRANSACTION_CONTROL_REGEX,
10} from '../QueryInsights.constants'
11import type { ChartDataPoint, ParsedLogEntry } from '../QueryInsights.types'
12
13export function filterSystemLogs(
14 logs: ParsedLogEntry[],
15 { includeIntrospection = false }: { includeIntrospection?: boolean } = {}
16): ParsedLogEntry[] {
17 return logs.filter((log) => {
18 if (log.user_name && (SUPAMONITOR_EXCLUDED_ROLES as readonly string[]).includes(log.user_name))
19 return false
20 if (
21 log.application_name &&
22 (SUPAMONITOR_EXCLUDED_APP_NAMES as readonly string[]).includes(log.application_name)
23 )
24 return false
25 if (log.query && TRANSACTION_CONTROL_REGEX.test(log.query)) return false
26 if (!includeIntrospection && log.query && SCHEMA_INTROSPECTION_REGEX.test(log.query))
27 return false
28 return true
29 })
30}
31
32function asString(unknown: unknown): string | undefined {
33 if (typeof unknown === 'string') return unknown
34 if (unknown === null || unknown === undefined) return undefined
35 return String(unknown)
36}
37
38function asNumber(unknown: unknown): number | undefined {
39 if (typeof unknown === 'number') return unknown
40 if (unknown === null || unknown === undefined) return undefined
41 const parsed = Number(unknown)
42 return Number.isNaN(parsed) ? undefined : parsed
43}
44
45export function parseSupamonitorLogs(logData: Logs['result']): ParsedLogEntry[] {
46 if (!logData || logData.length === 0) return []
47
48 return logData.map((log) => ({
49 timestamp: asString(log.timestamp),
50 application_name: asString(log.application_name),
51 calls: asNumber(log.calls),
52 database_name: asString(log.database_name),
53 query: log.query,
54 query_id: asNumber(log.query_id),
55 total_exec_time: asNumber(log.total_exec_time),
56 total_plan_time: asNumber(log.total_plan_time),
57 user_name: asString(log.user_name),
58 mean_exec_time: asNumber(log.mean_exec_time),
59 mean_plan_time: asNumber(log.mean_plan_time),
60 min_exec_time: asNumber(log.min_exec_time),
61 max_exec_time: asNumber(log.max_exec_time),
62 min_plan_time: asNumber(log.min_plan_time),
63 max_plan_time: asNumber(log.max_plan_time),
64 p50_exec_time: asNumber(log.p50_exec_time),
65 p95_exec_time: asNumber(log.p95_exec_time),
66 p50_plan_time: asNumber(log.p50_plan_time),
67 p95_plan_time: asNumber(log.p95_plan_time),
68 }))
69}
70
71export function transformLogsToChartData(parsedLogs: ParsedLogEntry[]): ChartDataPoint[] {
72 if (!parsedLogs || parsedLogs.length === 0) return []
73
74 return parsedLogs
75 .map((log: ParsedLogEntry) => {
76 if (!log.timestamp) return null
77
78 const periodStart = new Date(log.timestamp).getTime()
79 if (isNaN(periodStart)) return null
80
81 const meanExecTime = parseFloat(String(log.mean_exec_time ?? 0))
82 const meanPlanTime = parseFloat(String(log.mean_plan_time ?? 0))
83 const calls = parseInt(String(log.calls ?? 0), 10)
84
85 return {
86 period_start: periodStart,
87 timestamp: log.timestamp,
88 query_latency: meanExecTime + meanPlanTime,
89 mean_time: meanExecTime,
90 min_time: (log.min_exec_time ?? 0) + (log.min_plan_time ?? 0),
91 max_time: (log.max_exec_time ?? 0) + (log.max_plan_time ?? 0),
92 stddev_time: 0,
93 p50_time: (log.p50_exec_time ?? 0) + (log.p50_plan_time ?? 0),
94 p95_time: (log.p95_exec_time ?? 0) + (log.p95_plan_time ?? 0),
95 rows_read: 0,
96 calls,
97 cache_hits: 0,
98 cache_misses: 0,
99 }
100 })
101 .filter((item): item is NonNullable<typeof item> => item !== null)
102 .sort((a, b) => a.period_start - b.period_start)
103}
104
105function normalizeQuery(query: string): string {
106 return query.replace(/\s+/g, ' ').trim()
107}
108
109export function aggregateLogsByQuery(parsedLogs: ParsedLogEntry[]): QueryPerformanceRow[] {
110 if (!parsedLogs || parsedLogs.length === 0) return []
111
112 const queryGroups = new Map<string, ParsedLogEntry[]>()
113
114 parsedLogs.forEach((log) => {
115 const query = normalizeQuery(log.query || '')
116 if (!query) return
117
118 if (!queryGroups.has(query)) {
119 queryGroups.set(query, [])
120 }
121 queryGroups.get(query)!.push(log)
122 })
123
124 const aggregatedData: QueryPerformanceRow[] = []
125 let totalExecutionTime = 0
126
127 const queryStats = Array.from(queryGroups.entries()).map(([query, logs]) => {
128 const count = logs.length
129 let totalCalls = 0
130 let totalExecTime = 0
131 let totalPlanTime = 0
132 let p95Sum = 0
133 let p95Count = 0
134 let minTime = Infinity
135 let maxTime = -Infinity
136 const rolname = logs[0]?.user_name || ''
137 const applicationName = logs[0]?.application_name || ''
138 let firstSeen = logs[0]?.timestamp ?? ''
139
140 logs.forEach((log) => {
141 if (log.timestamp && (!firstSeen || log.timestamp < firstSeen)) firstSeen = log.timestamp
142 const logCalls = parseInt(String(log.calls ?? 0), 10)
143 totalCalls += logCalls
144 totalExecTime += parseFloat(String(log.total_exec_time ?? 0))
145 totalPlanTime += parseFloat(String(log.total_plan_time ?? 0))
146 const logP95 =
147 parseFloat(String(log.p95_exec_time ?? 0)) + parseFloat(String(log.p95_plan_time ?? 0))
148 if (logP95 > 0) {
149 p95Sum += logP95
150 p95Count++
151 }
152 minTime = Math.min(
153 minTime,
154 parseFloat(String(log.min_exec_time ?? 0)) + parseFloat(String(log.min_plan_time ?? 0))
155 )
156 maxTime = Math.max(
157 maxTime,
158 parseFloat(String(log.max_exec_time ?? 0)) + parseFloat(String(log.max_plan_time ?? 0))
159 )
160 })
161
162 const totalTime = totalExecTime + totalPlanTime
163 const avgMeanTime = totalCalls > 0 ? totalTime / totalCalls : 0
164 const avgP95Time = p95Count > 0 ? p95Sum / p95Count : 0
165 const finalMinTime = minTime === Infinity ? 0 : minTime
166 const finalMaxTime = maxTime === -Infinity ? 0 : maxTime
167
168 totalExecutionTime += totalTime
169
170 return {
171 query,
172 rolname,
173 applicationName,
174 firstSeen,
175 count,
176 avgMeanTime,
177 avgP95Time,
178 minTime: finalMinTime,
179 maxTime: finalMaxTime,
180 totalCalls,
181 totalTime,
182 }
183 })
184
185 queryStats.forEach((stats) => {
186 const propTotalTime = totalExecutionTime > 0 ? (stats.totalTime / totalExecutionTime) * 100 : 0
187
188 aggregatedData.push({
189 query: stats.query as SafeSqlFragment,
190 rolname: stats.rolname,
191 application_name: stats.applicationName,
192 calls: stats.totalCalls,
193 mean_time: stats.avgMeanTime,
194 p95_time: stats.avgP95Time,
195 min_time: stats.minTime,
196 max_time: stats.maxTime,
197 total_time: stats.totalTime,
198 rows_read: 0,
199 cache_hit_rate: 0,
200 prop_total_time: propTotalTime,
201 index_advisor_result: null,
202 _total_cache_hits: 0,
203 _total_cache_misses: 0,
204 _count: stats.count,
205 first_seen: stats.firstSeen,
206 })
207 })
208
209 return aggregatedData.sort((a, b) => b.total_time - a.total_time)
210}