konnosDashboard
UnifiedLogs.queries.bq.ts598 lines · main
1// @ts-nocheck
2// Legacy BigQuery unified-logs queries. Kept side-by-side with
3// UnifiedLogs.queries.ts (the OTEL/ClickHouse version) so the
4// `otelUnifiedLogs` feature flag can route traffic between the two paths
5// during the migration. This file should be deleted once the flag is
6// removed.
7
8import dayjs from 'dayjs'
9
10import { DEFAULT_LOG_TYPES } from './UnifiedLogs.constants'
11import { QuerySearchParamsType, SearchParamsType } from './UnifiedLogs.types'
12import {
13 bqIdent,
14 joinSqlFragments,
15 analyticsLiteral as lit,
16 safeSql,
17 type SafeLogSqlFragment,
18} from '@/data/logs/safe-analytics-sql'
19
20// Pagination and control parameters
21const PAGINATION_PARAMS = ['sort', 'start', 'size', 'uuid', 'cursor', 'direction', 'live'] as const
22
23// Special filter parameters that need custom handling
24const SPECIAL_FILTER_PARAMS = ['date'] as const
25
26// Combined list of all parameters to exclude from standard filtering
27const EXCLUDED_QUERY_PARAMS = [...PAGINATION_PARAMS, ...SPECIAL_FILTER_PARAMS] as const
28
29/**
30 * Builds WHERE-clause fragments from a search-param map. Identifier-position
31 * keys are validated via `bqIdent()` (regex allowlist) and value-position
32 * inputs via `analyticsLiteral` — both throw on disallowed input, in which
33 * case we drop the predicate rather than emit unsafe SQL.
34 *
35 * @param search Search params (URL-derived filter values)
36 * @param excludeKey Optional key to skip — used by facet-count branches that
37 * need every filter applied *except* the one being faceted
38 * @returns Array of SafeLogSqlFragment predicates ready to be AND-joined
39 */
40const buildConditions = (
41 search: QuerySearchParamsType,
42 excludeKey?: string
43): SafeLogSqlFragment[] => {
44 const conditions: SafeLogSqlFragment[] = []
45
46 Object.entries(search).forEach(([key, value]) => {
47 if (key === excludeKey) return
48 if ((EXCLUDED_QUERY_PARAMS as readonly string[]).includes(key)) return
49
50 try {
51 // `key` is interpolated as a column identifier. `bqIdent()` rejects
52 // anything outside `[A-Za-z_][A-Za-z0-9_]*` (notably no spaces, so a
53 // crafted URL key like `level OR id IS NOT NULL` is dropped rather
54 // than emitted into the WHERE clause).
55 const col = bqIdent(key)
56
57 if (Array.isArray(value) && value.length > 0) {
58 const inList = joinSqlFragments(
59 value.map((v) => lit(String(v))),
60 ','
61 )
62 conditions.push(safeSql`${col} IN (${inList})`)
63 return
64 }
65
66 if (value !== null && value !== undefined) {
67 if (key === 'host' || key === 'pathname') {
68 conditions.push(safeSql`${col} LIKE ${lit('%' + String(value) + '%')}`)
69 } else {
70 conditions.push(safeSql`${col} = ${lit(String(value))}`)
71 }
72 }
73 } catch {
74 // bqIdent() or analyticsLiteral() rejected the input — drop the predicate.
75 }
76 })
77
78 return conditions
79}
80
81const whereClause = (conditions: SafeLogSqlFragment[]): SafeLogSqlFragment =>
82 conditions.length > 0 ? safeSql`WHERE ${joinSqlFragments(conditions, ' AND ')}` : safeSql``
83
84/**
85 * Calculates how much the chart start datetime should be offset given the current datetime filter params
86 * and determines the appropriate bucketing level (minute, hour, day)
87 * Ported from the older implementation (apps/studio/components/interfaces/Settings/Logs/Logs.utils.ts)
88 */
89type TruncationLevel = 'MINUTE' | 'HOUR' | 'DAY'
90
91const TRUNCATION_LEVEL_SQL: Record<TruncationLevel, SafeLogSqlFragment> = {
92 MINUTE: safeSql`MINUTE`,
93 HOUR: safeSql`HOUR`,
94 DAY: safeSql`DAY`,
95}
96
97const calculateChartBucketing = (
98 search: SearchParamsType | Record<string, unknown>
99): TruncationLevel => {
100 // Extract start and end times from the date array if available
101 const dateRange = (search.date as Array<Date | string | number | null | undefined>) || []
102
103 // Handle timestamps that could be in various formats
104 const convertToMillis = (timestamp: Date | string | number | null | undefined) => {
105 if (!timestamp) return null
106 // If timestamp is a Date object
107 if (timestamp instanceof Date) return timestamp.getTime()
108
109 // If timestamp is a string that needs parsing
110 if (typeof timestamp === 'string') return dayjs(timestamp).valueOf()
111
112 // If timestamp is already a number (unix timestamp)
113 // Check if microseconds (16 digits) and convert to milliseconds
114 if (typeof timestamp === 'number') {
115 const str = timestamp.toString()
116 if (str.length >= 16) return Math.floor(timestamp / 1000)
117 return timestamp
118 }
119
120 return null
121 }
122
123 let startMillis = convertToMillis(dateRange[0])
124 let endMillis = convertToMillis(dateRange[1])
125
126 // Default values if not set
127 if (!startMillis) startMillis = dayjs().subtract(1, 'hour').valueOf()
128 if (!endMillis) endMillis = dayjs().valueOf()
129
130 const startTime = dayjs(startMillis)
131 const endTime = dayjs(endMillis)
132
133 const hourDiff = endTime.diff(startTime, 'hour')
134 const dayDiff = endTime.diff(startTime, 'day')
135
136 if (dayDiff >= 2) return 'DAY'
137 if (hourDiff >= 12) return 'HOUR'
138 return 'MINUTE'
139}
140
141/**
142 * Edge logs query fragment
143 *
144 * excludes `/rest/` in the path
145 */
146const getEdgeLogsQuery = (): SafeLogSqlFragment => safeSql`
147 select
148 id,
149 null as source_id,
150 el.timestamp as timestamp,
151 'edge' as log_type,
152 CAST(edge_logs_response.status_code AS STRING) as status,
153 CASE
154 WHEN edge_logs_response.status_code BETWEEN 200 AND 299 THEN 'success'
155 WHEN edge_logs_response.status_code BETWEEN 400 AND 499 THEN 'warning'
156 WHEN edge_logs_response.status_code >= 500 THEN 'error'
157 ELSE 'success'
158 END as level,
159 edge_logs_request.path as pathname,
160 null as event_message,
161 edge_logs_request.method as method,
162 null as log_count,
163 null as logs
164 from edge_logs as el
165 cross join unnest(metadata) as edge_logs_metadata
166 cross join unnest(edge_logs_metadata.request) as edge_logs_request
167 cross join unnest(edge_logs_metadata.response) as edge_logs_response
168
169 -- ONLY include logs where the path does not include /rest/
170 WHERE edge_logs_request.path NOT LIKE '%/rest/%'
171 AND edge_logs_request.path NOT LIKE '%/storage/%'
172 `
173
174// Postgrest logs — WHERE pathname includes `/rest/`
175const getPostgrestLogsQuery = (): SafeLogSqlFragment => safeSql`
176 select
177 id,
178 null as source_id,
179 el.timestamp as timestamp,
180 'postgrest' as log_type,
181 CAST(edge_logs_response.status_code AS STRING) as status,
182 CASE
183 WHEN edge_logs_response.status_code BETWEEN 200 AND 299 THEN 'success'
184 WHEN edge_logs_response.status_code BETWEEN 400 AND 499 THEN 'warning'
185 WHEN edge_logs_response.status_code >= 500 THEN 'error'
186 ELSE 'success'
187 END as level,
188 edge_logs_request.path as pathname,
189 null as event_message,
190 edge_logs_request.method as method,
191 null as log_count,
192 null as logs
193 from edge_logs as el
194 cross join unnest(metadata) as edge_logs_metadata
195 cross join unnest(edge_logs_metadata.request) as edge_logs_request
196 cross join unnest(edge_logs_metadata.response) as edge_logs_response
197
198 -- ONLY include logs where the path includes /rest/
199 WHERE edge_logs_request.path LIKE '%/rest/%'
200 `
201
202/**
203 * Postgres logs query fragment
204 */
205const getPostgresLogsQuery = (): SafeLogSqlFragment => safeSql`
206 select
207 id,
208 null as source_id,
209 pgl.timestamp as timestamp,
210 'postgres' as log_type,
211 CAST(pgl_parsed.sql_state_code AS STRING) as status,
212 CASE
213 WHEN pgl_parsed.error_severity = 'LOG' THEN 'success'
214 WHEN pgl_parsed.error_severity = 'WARNING' THEN 'warning'
215 WHEN pgl_parsed.error_severity = 'FATAL' THEN 'error'
216 WHEN pgl_parsed.error_severity = 'ERROR' THEN 'error'
217 ELSE null
218 END as level,
219 null as pathname,
220 event_message as event_message,
221 null as method,
222 null as log_count,
223 null as logs
224 from postgres_logs as pgl
225 cross join unnest(pgl.metadata) as pgl_metadata
226 cross join unnest(pgl_metadata.parsed) as pgl_parsed
227 `
228
229/**
230 * Edge function logs query fragment
231 */
232const getEdgeFunctionLogsQuery = (): SafeLogSqlFragment => safeSql`
233 select
234 id,
235 null as source_id,
236 fel.timestamp as timestamp,
237 'edge function' as log_type,
238 CAST(fel_response.status_code AS STRING) as status,
239 CASE
240 WHEN fel_response.status_code BETWEEN 200 AND 299 THEN 'success'
241 WHEN fel_response.status_code BETWEEN 400 AND 499 THEN 'warning'
242 WHEN fel_response.status_code >= 500 THEN 'error'
243 ELSE 'success'
244 END as level,
245 fel_request.pathname as pathname,
246 COALESCE(function_logs_agg.last_event_message, '') as event_message,
247 fel_request.method as method,
248 function_logs_agg.function_log_count as log_count,
249 null as logs
250 from function_edge_logs as fel
251 cross join unnest(metadata) as fel_metadata
252 cross join unnest(fel_metadata.response) as fel_response
253 cross join unnest(fel_metadata.request) as fel_request
254 left join (
255 SELECT
256 fl_metadata.request_id,
257 COUNT(fl.id) as function_log_count,
258 ANY_VALUE(fl.event_message) as last_event_message
259 FROM function_logs as fl
260 CROSS JOIN UNNEST(fl.metadata) as fl_metadata
261 WHERE fl_metadata.request_id IS NOT NULL
262 GROUP BY fl_metadata.request_id
263 ) as function_logs_agg on fel_metadata.request_id = function_logs_agg.request_id
264 `
265
266/**
267 * Auth logs query fragment
268 */
269const getAuthLogsQuery = (): SafeLogSqlFragment => safeSql`
270 select
271 el_in_al.id as id,
272 al.id as source_id,
273 el_in_al.timestamp as timestamp,
274 'auth' as log_type,
275 CAST(el_in_al_response.status_code AS STRING) as status,
276 CASE
277 WHEN el_in_al_response.status_code BETWEEN 200 AND 299 THEN 'success'
278 WHEN el_in_al_response.status_code BETWEEN 400 AND 499 THEN 'warning'
279 WHEN el_in_al_response.status_code >= 500 THEN 'error'
280 ELSE 'success'
281 END as level,
282 el_in_al_request.path as pathname,
283 null as event_message,
284 el_in_al_request.method as method,
285 null as log_count,
286 null as logs
287 from auth_logs as al
288 cross join unnest(metadata) as al_metadata
289 left join (
290 edge_logs as el_in_al
291 cross join unnest (metadata) as el_in_al_metadata
292 cross join unnest (el_in_al_metadata.response) as el_in_al_response
293 cross join unnest (el_in_al_response.headers) as el_in_al_response_headers
294 cross join unnest (el_in_al_metadata.request) as el_in_al_request
295 )
296 on al_metadata.request_id = el_in_al_response_headers.cf_ray
297 WHERE al_metadata.request_id is not null
298 `
299
300/**
301 * Briven storage logs query fragment
302 */
303const getBrivenStorageLogsQuery = (): SafeLogSqlFragment => safeSql`
304 select
305 id,
306 null as source_id,
307 el.timestamp as timestamp,
308 'storage' as log_type,
309 CAST(edge_logs_response.status_code AS STRING) as status,
310 CASE
311 WHEN edge_logs_response.status_code BETWEEN 200 AND 299 THEN 'success'
312 WHEN edge_logs_response.status_code BETWEEN 400 AND 499 THEN 'warning'
313 WHEN edge_logs_response.status_code >= 500 THEN 'error'
314 ELSE 'success'
315 END as level,
316 edge_logs_request.path as pathname,
317 null as event_message,
318 edge_logs_request.method as method,
319 null as log_count,
320 null as logs
321 from edge_logs as el
322 cross join unnest(metadata) as edge_logs_metadata
323 cross join unnest(edge_logs_metadata.request) as edge_logs_request
324 cross join unnest(edge_logs_metadata.response) as edge_logs_response
325 -- ONLY include logs where the path includes /storage/
326 WHERE edge_logs_request.path LIKE '%/storage/%'
327 `
328
329const LOG_TYPE_QUERIES: Record<string, () => SafeLogSqlFragment> = {
330 edge: getEdgeLogsQuery,
331 postgrest: getPostgrestLogsQuery,
332 postgres: getPostgresLogsQuery,
333 'edge function': getEdgeFunctionLogsQuery,
334 auth: getAuthLogsQuery,
335 storage: getBrivenStorageLogsQuery,
336}
337
338/**
339 * Combine the requested log sources to create the unified logs CTE.
340 * Defaults to postgres + postgrest on first load to reduce query cost.
341 */
342export const getUnifiedLogsCTE = (
343 logTypes: string[] = [...DEFAULT_LOG_TYPES]
344): SafeLogSqlFragment => {
345 const queries = logTypes
346 .filter((type) => type in LOG_TYPE_QUERIES)
347 .map((type) => LOG_TYPE_QUERIES[type]())
348
349 const effective =
350 queries.length > 0 ? queries : DEFAULT_LOG_TYPES.map((t) => LOG_TYPE_QUERIES[t]())
351
352 return safeSql`
353WITH unified_logs AS (
354 ${joinSqlFragments(effective, ' union all ')}
355)
356 `
357}
358
359/**
360 * Unified logs SQL query
361 */
362export const getUnifiedLogsQuery = (search: QuerySearchParamsType): SafeLogSqlFragment => {
363 const conditions = buildConditions(search)
364 const effectiveLogTypes = search.log_type?.length ? search.log_type : [...DEFAULT_LOG_TYPES]
365
366 return safeSql`
367${getUnifiedLogsCTE(effectiveLogTypes)}
368SELECT
369 id,
370 source_id,
371 timestamp,
372 log_type,
373 status,
374 level,
375 pathname,
376 event_message,
377 method,
378 log_count,
379 logs
380FROM unified_logs
381${whereClause(conditions)}
382`
383}
384
385export const getFacetCountCTE = ({
386 search,
387 facet,
388 facetSearch,
389}: {
390 search: QuerySearchParamsType
391 facet: string
392 facetSearch?: string
393}): SafeLogSqlFragment => {
394 const MAX_FACETS_QUANTITY = 20
395
396 // `facet` is used both as a column reference and to derive a CTE name;
397 // quote each appropriately with bqIdent() to reject non-identifier inputs.
398 const facetCol = bqIdent(facet)
399 const facetCte = bqIdent(facet + '_count')
400 const baseConditions = buildConditions(search, facet)
401 const facetSearchClause = facetSearch
402 ? safeSql`AND ${facetCol} LIKE ${lit('%' + facetSearch + '%')}`
403 : safeSql``
404
405 const where =
406 baseConditions.length > 0
407 ? safeSql`WHERE ${joinSqlFragments(baseConditions, ' AND ')} AND ${facetCol} IS NOT NULL`
408 : safeSql`WHERE ${facetCol} IS NOT NULL`
409
410 return safeSql`
411${facetCte} AS (
412 SELECT ${lit(facet)} as dimension, ${facetCol} as value, COUNT(*) as count
413 FROM unified_logs
414 ${where}
415 ${facetSearchClause}
416 GROUP BY ${facetCol}
417 LIMIT ${lit(MAX_FACETS_QUANTITY)}
418)
419`
420}
421
422export const getUnifiedLogsCountCTE = (): SafeLogSqlFragment => safeSql`
423WITH unified_logs AS (
424 -- Single scan of edge_logs covering edge gateway, postgrest, and storage
425 select
426 id,
427 CASE
428 WHEN edge_logs_request.path LIKE '%/rest/%' THEN 'postgrest'
429 WHEN edge_logs_request.path LIKE '%/storage/%' THEN 'storage'
430 ELSE 'edge'
431 END as log_type,
432 CAST(edge_logs_response.status_code AS STRING) as status,
433 CASE
434 WHEN edge_logs_response.status_code BETWEEN 200 AND 299 THEN 'success'
435 WHEN edge_logs_response.status_code BETWEEN 400 AND 499 THEN 'warning'
436 WHEN edge_logs_response.status_code >= 500 THEN 'error'
437 ELSE 'success'
438 END as level,
439 edge_logs_request.path as pathname,
440 edge_logs_request.method as method
441 from edge_logs as el
442 cross join unnest(metadata) as edge_logs_metadata
443 cross join unnest(edge_logs_metadata.request) as edge_logs_request
444 cross join unnest(edge_logs_metadata.response) as edge_logs_response
445
446 union all
447
448 -- Postgres logs
449 select
450 id,
451 'postgres' as log_type,
452 CAST(pgl_parsed.sql_state_code AS STRING) as status,
453 CASE
454 WHEN pgl_parsed.error_severity = 'LOG' THEN 'success'
455 WHEN pgl_parsed.error_severity = 'WARNING' THEN 'warning'
456 WHEN pgl_parsed.error_severity = 'FATAL' THEN 'error'
457 WHEN pgl_parsed.error_severity = 'ERROR' THEN 'error'
458 ELSE null
459 END as level,
460 null as pathname,
461 null as method
462 from postgres_logs as pgl
463 cross join unnest(pgl.metadata) as pgl_metadata
464 cross join unnest(pgl_metadata.parsed) as pgl_parsed
465
466 union all
467
468 -- Edge function logs
469 select
470 fel.id,
471 'edge function' as log_type,
472 CAST(fel_response.status_code AS STRING) as status,
473 CASE
474 WHEN fel_response.status_code BETWEEN 200 AND 299 THEN 'success'
475 WHEN fel_response.status_code BETWEEN 400 AND 499 THEN 'warning'
476 WHEN fel_response.status_code >= 500 THEN 'error'
477 ELSE 'success'
478 END as level,
479 fel_request.pathname as pathname,
480 fel_request.method as method
481 from function_edge_logs as fel
482 cross join unnest(metadata) as fel_metadata
483 cross join unnest(fel_metadata.response) as fel_response
484 cross join unnest(fel_metadata.request) as fel_request
485
486 union all
487
488 -- Auth logs
489 select
490 el_in_al.id as id,
491 'auth' as log_type,
492 CAST(el_in_al_response.status_code AS STRING) as status,
493 CASE
494 WHEN el_in_al_response.status_code BETWEEN 200 AND 299 THEN 'success'
495 WHEN el_in_al_response.status_code BETWEEN 400 AND 499 THEN 'warning'
496 WHEN el_in_al_response.status_code >= 500 THEN 'error'
497 ELSE 'success'
498 END as level,
499 el_in_al_request.path as pathname,
500 el_in_al_request.method as method
501 from auth_logs as al
502 cross join unnest(metadata) as al_metadata
503 left join (
504 edge_logs as el_in_al
505 cross join unnest(metadata) as el_in_al_metadata
506 cross join unnest(el_in_al_metadata.response) as el_in_al_response
507 cross join unnest(el_in_al_response.headers) as el_in_al_response_headers
508 cross join unnest(el_in_al_metadata.request) as el_in_al_request
509 )
510 on al_metadata.request_id = el_in_al_response_headers.cf_ray
511 WHERE al_metadata.request_id is not null
512)
513 `
514
515export const getLogsCountQuery = (search: QuerySearchParamsType): SafeLogSqlFragment => {
516 const effectiveLogTypes = search.log_type?.length ? search.log_type : [...DEFAULT_LOG_TYPES]
517 const logTypeConditions = buildConditions(search, 'log_type')
518 const levelConditions = buildConditions(search, 'level')
519 const logTypeWhere: SafeLogSqlFragment =
520 logTypeConditions.length > 0
521 ? safeSql`WHERE ${joinSqlFragments(logTypeConditions, ' AND ')}`
522 : safeSql`WHERE log_type IS NOT NULL`
523 const levelWhere: SafeLogSqlFragment =
524 levelConditions.length > 0
525 ? safeSql`WHERE ${joinSqlFragments(levelConditions, ' AND ')}`
526 : safeSql`WHERE level IS NOT NULL`
527
528 return safeSql`
529${getUnifiedLogsCTE(effectiveLogTypes)},
530
531-- Single COUNTIF pass for all log_type buckets + total (no GROUP BY / sort needed)
532log_type_counts AS (
533 SELECT
534 COUNT(*) AS total,
535 COUNTIF(log_type = 'edge') AS edge_count,
536 COUNTIF(log_type = 'postgrest') AS postgrest_count,
537 COUNTIF(log_type = 'storage') AS storage_count,
538 COUNTIF(log_type = 'postgres') AS postgres_count,
539 COUNTIF(log_type = 'edge function') AS edge_function_count,
540 COUNTIF(log_type = 'auth') AS auth_count
541 FROM unified_logs
542 ${logTypeWhere}
543),
544
545-- Single COUNTIF pass for all level buckets
546level_counts AS (
547 SELECT
548 COUNTIF(level = 'success') AS success_count,
549 COUNTIF(level = 'warning') AS warning_count,
550 COUNTIF(level = 'error') AS error_count
551 FROM unified_logs
552 ${levelWhere}
553),
554
555-- Variable facets: open-ended values still need GROUP BY
556${getFacetCountCTE({ search, facet: 'method' })},
557${getFacetCountCTE({ search, facet: 'status' })},
558${getFacetCountCTE({ search, facet: 'pathname' })}
559
560SELECT 'total' AS dimension, 'all' AS value, total AS count FROM log_type_counts
561UNION ALL SELECT 'log_type', 'edge', edge_count FROM log_type_counts
562UNION ALL SELECT 'log_type', 'postgrest', postgrest_count FROM log_type_counts
563UNION ALL SELECT 'log_type', 'storage', storage_count FROM log_type_counts
564UNION ALL SELECT 'log_type', 'postgres', postgres_count FROM log_type_counts
565UNION ALL SELECT 'log_type', 'edge function', edge_function_count FROM log_type_counts
566UNION ALL SELECT 'log_type', 'auth', auth_count FROM log_type_counts
567UNION ALL SELECT 'level', 'success', success_count FROM level_counts
568UNION ALL SELECT 'level', 'warning', warning_count FROM level_counts
569UNION ALL SELECT 'level', 'error', error_count FROM level_counts
570UNION ALL SELECT dimension, value, count FROM method_count
571UNION ALL SELECT dimension, value, count FROM status_count
572UNION ALL SELECT dimension, value, count FROM pathname_count
573`
574}
575
576/**
577 * Enhanced logs chart query with dynamic bucketing based on time range
578 * Incorporates dynamic bucketing from the older implementation
579 */
580export const getLogsChartQuery = (search: QuerySearchParamsType): SafeLogSqlFragment => {
581 const conditions = buildConditions(search)
582 const truncationLevel = calculateChartBucketing(search)
583 const effectiveLogTypes = search.log_type?.length ? search.log_type : [...DEFAULT_LOG_TYPES]
584
585 return safeSql`
586${getUnifiedLogsCTE(effectiveLogTypes)}
587SELECT
588 TIMESTAMP_TRUNC(timestamp, ${TRUNCATION_LEVEL_SQL[truncationLevel]}) as time_bucket,
589 COUNTIF(level = 'success') as success,
590 COUNTIF(level = 'warning') as warning,
591 COUNTIF(level = 'error') as error,
592 COUNT(*) as total_per_bucket
593FROM unified_logs
594${whereClause(conditions)}
595GROUP BY time_bucket
596ORDER BY time_bucket ASC
597`
598}