konnosDashboard
ServiceFlow.sql.ts394 lines · main
1/**
2 * Service Flow SQL Queries
3 *
4 * This file contains SQL queries for fetching enriched service flow data
5 * showing how requests flow through different layers of the infrastructure.
6 */
7
8// Service configuration for different log types
9const SERVICE_CONFIGS = {
10 postgrest: {
11 pathFilter: '%/rest/%',
12 },
13 auth: {
14 pathFilter: '%/auth/%',
15 },
16 'edge-function': {
17 pathFilter: '%/functions/%',
18 },
19 storage: {
20 pathFilter: '%/storage/%',
21 },
22} as const
23
24type EdgeServiceType = keyof typeof SERVICE_CONFIGS
25
26/**
27 * Base Edge Logs Service Flow Query
28 * Consolidated query for all edge-based services (postgrest, auth, edge-function, storage)
29 * to eliminate 500+ lines of SQL duplication
30 */
31const getBaseEdgeServiceFlowQuery = (logId: string, serviceType: EdgeServiceType): string => {
32 return `
33 select
34 id,
35 el.timestamp as timestamp,
36 '${serviceType}' as log_type,
37 CAST(edge_logs_response.status_code AS STRING) as status,
38 CASE
39 WHEN edge_logs_response.status_code BETWEEN 200 AND 299 THEN 'success'
40 WHEN edge_logs_response.status_code BETWEEN 400 AND 499 THEN 'warning'
41 WHEN edge_logs_response.status_code >= 500 THEN 'error'
42 ELSE 'success'
43 END as level,
44
45 -- Request data
46 edge_logs_request.path as request_path,
47 edge_logs_request.host as request_host,
48 edge_logs_request.method as request_method,
49 edge_logs_request.url as request_url,
50 edge_logs_request.search as request_search,
51
52 -- Response data
53 edge_logs_response.origin_time as response_origin_time,
54 edge_logs_response_headers.content_type as response_content_type,
55 edge_logs_response_headers.cf_cache_status as response_cache_status,
56
57 -- Client location data
58 edge_logs_cf.continent as client_continent,
59 edge_logs_cf.country as client_country,
60 edge_logs_cf.city as client_city,
61 edge_logs_cf.region as client_region,
62 edge_logs_cf.regionCode as client_region_code,
63 edge_logs_cf.latitude as client_latitude,
64 edge_logs_cf.longitude as client_longitude,
65 edge_logs_cf.timezone as client_timezone,
66
67 -- Network data
68 edge_logs_cf.httpProtocol as network_protocol,
69 edge_logs_cf.colo as network_datacenter,
70
71 -- Request headers
72 edge_logs_request_headers.user_agent as headers_user_agent,
73 edge_logs_request_headers.x_client_info as headers_x_client_info,
74 edge_logs_request_headers.x_forwarded_proto as headers_x_forwarded_proto,
75 edge_logs_request_headers.x_real_ip as headers_x_real_ip,
76 edge_logs_request_headers.referer as headers_referer,
77
78 -- Auth data
79 authorization_payload.role as api_role,
80 COALESCE(sb.auth_user, null) as auth_user,
81
82 -- JWT Key Authentication (old keys)
83 CASE
84 WHEN apikey_payload.algorithm = 'HS256' AND
85 apikey_payload.issuer = 'briven' AND
86 apikey_payload.role IN ('anon', 'service_role')
87 THEN apikey_payload.role
88 WHEN apikey_payload IS NOT NULL THEN '<unrecognized>'
89 ELSE NULL
90 END as jwt_key_role,
91
92 apikey_payload.signature_prefix as jwt_key_prefix,
93
94 -- API Key Authentication (new keys from sb.apikey[0].apikey[0])
95 CASE
96 WHEN sb_apikey_inner.prefix LIKE '%publishable%' THEN 'anon'
97 WHEN sb_apikey_inner.prefix LIKE '%secret%' THEN 'service_role'
98 WHEN sb_apikey_inner.prefix IS NOT NULL THEN 'unknown'
99 ELSE NULL
100 END as api_key_role,
101
102 sb_apikey_inner.prefix as api_key_prefix,
103 sb_apikey_inner.error as api_key_error,
104 sb_apikey_inner.hash as api_key_hash,
105
106 -- User Authorization
107 authorization_payload.role as authorization_role,
108 null as user_id,
109 null as user_email,
110
111 -- Cloudflare Network Info
112 edge_logs_response_headers.cf_ray as cf_ray,
113 edge_logs_request_headers.cf_ipcountry as cf_country,
114 edge_logs_cf.colo as cf_datacenter,
115 edge_logs_request_headers.cf_connecting_ip as client_ip,
116
117 -- JWT data
118 apikey_payload.role as jwt_apikey_role,
119 apikey_payload.algorithm as jwt_apikey_algorithm,
120 apikey_payload.expires_at as jwt_apikey_expires_at,
121 apikey_payload.issuer as jwt_apikey_issuer,
122 apikey_payload.signature_prefix as jwt_apikey_signature_prefix,
123 null as jwt_apikey_key_id,
124 null as jwt_apikey_session_id,
125 apikey_payload.subject as jwt_apikey_subject,
126
127 authorization_payload.role as jwt_auth_role,
128 authorization_payload.algorithm as jwt_auth_algorithm,
129 authorization_payload.expires_at as jwt_auth_expires_at,
130 authorization_payload.issuer as jwt_auth_issuer,
131 authorization_payload.signature_prefix as jwt_auth_signature_prefix,
132 authorization_payload.key_id as jwt_auth_key_id,
133 authorization_payload.session_id as jwt_auth_session_id,
134 authorization_payload.subject as jwt_auth_subject,
135
136 -- Storage specific data (included for all but only populated for storage)
137 edge_logs_response_headers.sb_gateway_mode as storage_edge_gateway_mode,
138 edge_logs_response_headers.sb_gateway_version as storage_edge_gateway_version,
139 edge_logs_response_headers.cf_ray as correlation_cf_ray,
140
141 -- Raw data
142 el as raw_log_data
143
144 from edge_logs as el
145 cross join unnest(metadata) as edge_logs_metadata
146 cross join unnest(edge_logs_metadata.request) as edge_logs_request
147 cross join unnest(edge_logs_metadata.response) as edge_logs_response
148 left join unnest(edge_logs_response.headers) as edge_logs_response_headers
149 left join unnest(edge_logs_request.headers) as edge_logs_request_headers
150 left join unnest(edge_logs_request.cf) as edge_logs_cf
151 left join unnest(edge_logs_request.sb) as sb
152 left join unnest(sb.jwt) as jwt
153 left join unnest(COALESCE(jwt.apikey, [])) as apikey
154 left join unnest(COALESCE(apikey.payload, [])) as apikey_payload
155 left join unnest(COALESCE(jwt.authorization, [])) as auth
156 left join unnest(COALESCE(auth.payload, [])) as authorization_payload
157 left join unnest(COALESCE(sb.apikey, [])) as sb_apikey_outer
158 left join unnest(COALESCE(sb_apikey_outer.apikey, [])) as sb_apikey_inner
159
160WHERE
161 el.id = '${logId}'
162`
163}
164
165/**
166 * PostgREST Service Flow Query for /rest/ requests
167 * Fetches enriched edge log data for PostgREST requests with additional service-specific metadata
168 */
169export const getPostgrestServiceFlowQuery = (logId: string): string => {
170 return getBaseEdgeServiceFlowQuery(logId, 'postgrest')
171}
172
173/**
174 * Auth Service Flow Query for /auth/ requests
175 * Fetches enriched edge log data for GoTrue auth requests with service-specific metadata
176 */
177export const getAuthServiceFlowQuery = (logId: string): string => {
178 return getBaseEdgeServiceFlowQuery(logId, 'auth')
179}
180
181/**
182 * Edge Function Service Flow Query for /functions/ requests
183 * Fetches enriched edge log data for Edge Function requests with service-specific metadata
184 * NOTE: Uses function_edge_logs table instead of edge_logs, so kept separate like postgres
185 */
186export const getEdgeFunctionServiceFlowQuery = (logId: string): string => {
187 return `
188 select
189 fel.id as id,
190 fel.timestamp as timestamp,
191 'edge-function' as log_type,
192 CAST(fel_response.status_code AS STRING) as status,
193 CASE
194 WHEN fel_response.status_code BETWEEN 200 AND 299 THEN 'success'
195 WHEN fel_response.status_code BETWEEN 400 AND 499 THEN 'warning'
196 WHEN fel_response.status_code >= 500 THEN 'error'
197 ELSE 'success'
198 END as level,
199
200 -- Request data
201 fel_request.pathname as request_path,
202 fel_request.host as request_host,
203 fel_request.method as request_method,
204 fel_request.url as request_url,
205 fel_request.search as request_search,
206 fel_request.protocol as request_protocol,
207 fel_request.port as request_port,
208
209 -- Response data (no origin_time in function_edge_logs)
210 fel_response_headers.content_type as response_content_type,
211 fel_response_headers.content_length as response_content_length,
212 fel_response_headers.date as response_date,
213 fel_response_headers.server as response_server,
214 fel_response_headers.x_sb_edge_region as response_edge_region,
215 fel_response_headers.x_sb_compute_multiplier as response_compute_multiplier,
216 fel_response_headers.x_sb_resource_multiplier as response_resource_multiplier,
217 fel_response_headers.x_served_by as response_served_by,
218
219 -- Function specific data
220 fel_metadata.execution_id as execution_id,
221 fel_metadata.function_id as function_id,
222 fel_metadata.deployment_id as deployment_id,
223 fel_metadata.execution_time_ms as execution_time_ms,
224 fel_metadata.project_ref as project_ref,
225 fel_metadata.version as function_version,
226
227 -- Request headers
228 fel_request_headers.user_agent as headers_user_agent,
229 fel_request_headers.x_client_info as headers_x_client_info,
230 fel_request_headers.accept as headers_accept,
231 fel_request_headers.accept_encoding as headers_accept_encoding,
232 fel_request_headers.connection as headers_connection,
233 fel_request_headers.cookie as headers_cookie,
234 fel_request_headers.host as headers_host,
235
236 -- Auth data
237 authorization_payload.role as api_role,
238 COALESCE(sb.auth_user, null) as auth_user,
239
240 -- JWT Key Authentication (old keys)
241 CASE
242 WHEN apikey_payload.algorithm = 'HS256' AND
243 apikey_payload.issuer = 'briven' AND
244 apikey_payload.role IN ('anon', 'service_role')
245 THEN apikey_payload.role
246 WHEN apikey_payload IS NOT NULL THEN '<unrecognized>'
247 ELSE NULL
248 END as jwt_key_role,
249
250 apikey_payload.signature_prefix as jwt_key_prefix,
251
252 -- API Key Authentication (new keys from sb.apikey[0].apikey[0])
253 CASE
254 WHEN sb_apikey_inner.prefix LIKE '%publishable%' THEN 'anon'
255 WHEN sb_apikey_inner.prefix LIKE '%secret%' THEN 'service_role'
256 WHEN sb_apikey_inner.prefix IS NOT NULL THEN 'unknown'
257 ELSE NULL
258 END as api_key_role,
259
260 sb_apikey_inner.prefix as api_key_prefix,
261 sb_apikey_inner.error as api_key_error,
262 sb_apikey_inner.hash as api_key_hash,
263
264 -- User Authorization
265 authorization_payload.role as authorization_role,
266 null as user_id,
267 null as user_email,
268
269 -- JWT data
270 apikey_payload.role as jwt_apikey_role,
271 apikey_payload.algorithm as jwt_apikey_algorithm,
272 apikey_payload.expires_at as jwt_apikey_expires_at,
273 apikey_payload.issuer as jwt_apikey_issuer,
274 apikey_payload.signature_prefix as jwt_apikey_signature_prefix,
275 null as jwt_apikey_key_id,
276 null as jwt_apikey_session_id,
277 apikey_payload.subject as jwt_apikey_subject,
278
279 authorization_payload.role as jwt_auth_role,
280 authorization_payload.algorithm as jwt_auth_algorithm,
281 authorization_payload.expires_at as jwt_auth_expires_at,
282 authorization_payload.issuer as jwt_auth_issuer,
283 authorization_payload.signature_prefix as jwt_auth_signature_prefix,
284 authorization_payload.key_id as jwt_auth_key_id,
285 authorization_payload.session_id as jwt_auth_session_id,
286 authorization_payload.subject as jwt_auth_subject,
287
288 -- Function logs aggregation
289 function_logs_agg.function_log_count as function_log_count,
290 function_logs_agg.logs as function_logs,
291 function_logs_agg.last_event_message as last_event_message,
292
293 -- Raw data
294 fel as raw_log_data
295
296 from function_edge_logs as fel
297 cross join unnest(metadata) as fel_metadata
298 cross join unnest(fel_metadata.response) as fel_response
299 cross join unnest(fel_metadata.request) as fel_request
300 left join unnest(fel_response.headers) as fel_response_headers
301 left join unnest(fel_request.headers) as fel_request_headers
302 left join unnest(fel_request.sb) as sb
303 left join unnest(sb.jwt) as jwt
304 left join unnest(COALESCE(jwt.apikey, [])) as apikey
305 left join unnest(COALESCE(apikey.payload, [])) as apikey_payload
306 left join unnest(COALESCE(jwt.authorization, [])) as auth
307 left join unnest(COALESCE(auth.payload, [])) as authorization_payload
308 left join unnest(COALESCE(sb.apikey, [])) as sb_apikey_outer
309 left join unnest(COALESCE(sb_apikey_outer.apikey, [])) as sb_apikey_inner
310 left join (
311 SELECT
312 fl_metadata.execution_id,
313 COUNT(fl.id) as function_log_count,
314 ANY_VALUE(fl.event_message) as last_event_message,
315 ARRAY_AGG(STRUCT(fl.id, fl.timestamp, fl.event_message, fl_metadata.level, fl_metadata.event_type)) as logs
316 FROM function_logs as fl
317 CROSS JOIN UNNEST(fl.metadata) as fl_metadata
318 WHERE fl_metadata.execution_id IS NOT NULL
319 GROUP BY fl_metadata.execution_id
320 ) as function_logs_agg on fel_metadata.execution_id = function_logs_agg.execution_id
321
322WHERE
323 fel.id = '${logId}'
324`
325}
326
327/**
328 * Storage Service Flow Query for /storage/ requests
329 * Fetches enriched edge log data for Storage requests with service-specific metadata
330 */
331export const getStorageServiceFlowQuery = (logId: string): string => {
332 return getBaseEdgeServiceFlowQuery(logId, 'storage')
333}
334
335/**
336 * Postgres Service Flow Query for database operations
337 * Fetches enriched postgres log data with database-specific metadata
338 *
339 * This handles direct database operations, connections, and queries
340 * NOTE: Uses postgres_logs table instead of edge_logs, so kept separate
341 */
342export const getPostgresServiceFlowQuery = (logId: string): string => {
343 return `
344 select
345 pgl.id as id,
346 pgl.timestamp as timestamp,
347 'postgres' as log_type,
348 pgl_parsed.sql_state_code as status,
349 CASE
350 WHEN pgl_parsed.error_severity = 'LOG' THEN 'success'
351 WHEN pgl_parsed.error_severity = 'WARNING' THEN 'warning'
352 WHEN pgl_parsed.error_severity = 'FATAL' THEN 'error'
353 WHEN pgl_parsed.error_severity = 'ERROR' THEN 'error'
354 ELSE 'success'
355 END as level,
356
357 -- Database connection details
358 pgl_parsed.database_name as database_name,
359 pgl_parsed.user_name as database_user,
360 pgl_parsed.connection_from as connection_from,
361 pgl_metadata.host as database_host,
362
363 -- Query/Operation details
364 pgl_parsed.command_tag as command_tag,
365 pgl_parsed.backend_type as backend_type,
366 pgl_parsed.query_id as query_id,
367
368 -- Session details
369 pgl_parsed.session_id as session_id,
370 pgl_parsed.process_id as process_id,
371 pgl_parsed.virtual_transaction_id as virtual_transaction_id,
372 pgl_parsed.transaction_id as transaction_id,
373 pgl_parsed.session_start_time as session_start_time,
374 pgl_parsed.session_line_num as session_line_num,
375
376 -- Error/Status details
377 pgl_parsed.error_severity as error_severity,
378 pgl_parsed.sql_state_code as sql_state_code,
379 pgl.event_message as event_message,
380
381 -- Timing
382 pgl_parsed.timestamp as operation_timestamp,
383
384 -- Raw data
385 pgl as raw_log_data
386
387 from postgres_logs as pgl
388 cross join unnest(pgl.metadata) as pgl_metadata
389 cross join unnest(pgl_metadata.parsed) as pgl_parsed
390
391WHERE
392 pgl.id = '${logId}'
393`
394}