konnosDashboard
Reports.constants.ts817 lines · main
1import { literal, safeSql, type SafeSqlFragment } from '@supabase/pg-meta'
2import dayjs from 'dayjs'
3
4import type { DatetimeHelper } from '../Settings/Logs/Logs.types'
5import { PresetConfig, Presets, ReportFilterItem } from './Reports.types'
6import { PlanId } from '@/data/subscriptions/types'
7
8export const LAYOUT_COLUMN_COUNT = 2
9
10export interface ReportsDatetimeHelper extends DatetimeHelper {
11 availableIn: PlanId[]
12}
13
14export enum REPORT_DATERANGE_HELPER_LABELS {
15 LAST_10_MINUTES = 'Last 10 minutes',
16 LAST_30_MINUTES = 'Last 30 minutes',
17 LAST_60_MINUTES = 'Last 60 minutes',
18 LAST_3_HOURS = 'Last 3 hours',
19 LAST_24_HOURS = 'Last 24 hours',
20 LAST_7_DAYS = 'Last 7 days',
21 LAST_14_DAYS = 'Last 14 days',
22 LAST_28_DAYS = 'Last 28 days',
23}
24
25export const REPORTS_DATEPICKER_HELPERS: ReportsDatetimeHelper[] = [
26 {
27 text: REPORT_DATERANGE_HELPER_LABELS.LAST_10_MINUTES,
28 calcFrom: () => dayjs().subtract(10, 'minute').toISOString(),
29 calcTo: () => dayjs().toISOString(),
30 availableIn: ['free', 'pro', 'team', 'enterprise', 'platform'],
31 },
32 {
33 text: REPORT_DATERANGE_HELPER_LABELS.LAST_30_MINUTES,
34 calcFrom: () => dayjs().subtract(30, 'minute').toISOString(),
35 calcTo: () => dayjs().toISOString(),
36 availableIn: ['free', 'pro', 'team', 'enterprise', 'platform'],
37 },
38 {
39 text: REPORT_DATERANGE_HELPER_LABELS.LAST_60_MINUTES,
40 calcFrom: () => dayjs().subtract(1, 'hour').toISOString(),
41 calcTo: () => dayjs().toISOString(),
42 default: true,
43 availableIn: ['free', 'pro', 'team', 'enterprise', 'platform'],
44 },
45 {
46 text: REPORT_DATERANGE_HELPER_LABELS.LAST_3_HOURS,
47 calcFrom: () => dayjs().subtract(3, 'hour').toISOString(),
48 calcTo: () => dayjs().toISOString(),
49 availableIn: ['free', 'pro', 'team', 'enterprise', 'platform'],
50 },
51 {
52 text: REPORT_DATERANGE_HELPER_LABELS.LAST_24_HOURS,
53 calcFrom: () => dayjs().subtract(1, 'day').toISOString(),
54 calcTo: () => dayjs().toISOString(),
55 availableIn: ['free', 'pro', 'team', 'enterprise', 'platform'],
56 },
57 {
58 text: REPORT_DATERANGE_HELPER_LABELS.LAST_7_DAYS,
59 calcFrom: () => dayjs().subtract(7, 'day').toISOString(),
60 calcTo: () => dayjs().toISOString(),
61 availableIn: ['pro', 'team', 'enterprise'],
62 },
63 {
64 text: REPORT_DATERANGE_HELPER_LABELS.LAST_14_DAYS,
65 calcFrom: () => dayjs().subtract(14, 'day').toISOString(),
66 calcTo: () => dayjs().toISOString(),
67 availableIn: ['team', 'enterprise'],
68 },
69 {
70 text: REPORT_DATERANGE_HELPER_LABELS.LAST_28_DAYS,
71 calcFrom: () => dayjs().subtract(28, 'day').toISOString(),
72 calcTo: () => dayjs().toISOString(),
73 availableIn: ['team', 'enterprise'],
74 },
75]
76
77export const DEFAULT_QUERY_PARAMS = {
78 iso_timestamp_start: REPORTS_DATEPICKER_HELPERS[0].calcFrom(),
79 iso_timestamp_end: REPORTS_DATEPICKER_HELPERS[0].calcTo(),
80}
81
82function rewriteWhereToAnd(sql: SafeSqlFragment): SafeSqlFragment {
83 return sql.replace(/^WHERE/, 'AND') as SafeSqlFragment
84}
85
86export const generateRegexpWhere = (filters: ReportFilterItem[], prepend = true) => {
87 if (filters.length === 0) return ''
88 const conditions = filters
89 .map((filter) => {
90 const splitKey = filter.key.split('.')
91 const normalizedKey = [splitKey[splitKey.length - 2], splitKey[splitKey.length - 1]].join('.')
92 const filterKey = filter.key.includes('.') ? normalizedKey : filter.key
93
94 const hasQuotes =
95 filter.value.toString().includes('"') || filter.value.toString().includes("'")
96
97 const valueIsNumber = !isNaN(Number(filter.value))
98 const valueWithQuotes = !valueIsNumber && hasQuotes ? filter.value : `'${filter.value}'`
99 const lowercaseValue = !valueIsNumber && String(valueWithQuotes).toLowerCase()
100
101 const finalValue = valueIsNumber ? filter.value : lowercaseValue
102
103 // Handle different comparison operators
104 switch (filter.compare) {
105 case 'matches':
106 return `REGEXP_CONTAINS(${filterKey}, ${finalValue})`
107 case 'is':
108 return `${filterKey} = ${finalValue}`
109 case '!=':
110 return `${filterKey} != ${finalValue}`
111 case '>=':
112 return `${filterKey} >= ${finalValue}`
113 case '<=':
114 return `${filterKey} <= ${finalValue}`
115 case '>':
116 return `${filterKey} > ${finalValue}`
117 case '<':
118 return `${filterKey} < ${finalValue}`
119 default:
120 // Fallback to exact match for unknown operators
121 return `${filterKey} = ${finalValue}`
122 }
123 })
124 .filter(Boolean) // Remove any null/undefined conditions
125 .join(' AND ')
126
127 if (conditions === '') return ''
128
129 if (prepend) {
130 return 'WHERE ' + conditions
131 } else {
132 return 'AND ' + conditions
133 }
134}
135
136export const PRESET_CONFIG: Record<Presets, PresetConfig> = {
137 [Presets.API]: {
138 title: 'API',
139 queries: {
140 totalRequests: {
141 queryType: 'logs',
142 sql: (filters) => `
143 -- reports-api-total-requests
144 select
145 cast(timestamp_trunc(t.timestamp, hour) as datetime) as timestamp,
146 count(t.id) as count
147 FROM edge_logs t
148 cross join unnest(metadata) as m
149 cross join unnest(m.response) as response
150 cross join unnest(m.request) as request
151 cross join unnest(request.headers) as headers
152 ${generateRegexpWhere(filters)}
153 GROUP BY
154 timestamp
155 ORDER BY
156 timestamp ASC`,
157 },
158 topRoutes: {
159 queryType: 'logs',
160 sql: (filters) => `
161 -- reports-api-top-routes
162 select
163 request.path as path,
164 request.method as method,
165 request.search as search,
166 response.status_code as status_code,
167 count(t.id) as count
168 from edge_logs t
169 cross join unnest(metadata) as m
170 cross join unnest(m.response) as response
171 cross join unnest(m.request) as request
172 cross join unnest(request.headers) as headers
173 ${generateRegexpWhere(filters)}
174 group by
175 request.path, request.method, request.search, response.status_code
176 order by
177 count desc
178 limit 10
179 `,
180 },
181 errorCounts: {
182 queryType: 'logs',
183 sql: (filters) => `
184 -- reports-api-error-counts
185 select
186 cast(timestamp_trunc(t.timestamp, hour) as datetime) as timestamp,
187 count(t.id) as count
188 FROM edge_logs t
189 cross join unnest(metadata) as m
190 cross join unnest(m.response) as response
191 cross join unnest(m.request) as request
192 cross join unnest(request.headers) as headers
193 WHERE
194 response.status_code >= 400
195 ${generateRegexpWhere(filters, false)}
196 GROUP BY
197 timestamp
198 ORDER BY
199 timestamp ASC
200 `,
201 },
202 topErrorRoutes: {
203 queryType: 'logs',
204 sql: (filters) => `
205 -- reports-api-top-error-routes
206 select
207 request.path as path,
208 request.method as method,
209 request.search as search,
210 response.status_code as status_code,
211 count(t.id) as count
212 from edge_logs t
213 cross join unnest(metadata) as m
214 cross join unnest(m.response) as response
215 cross join unnest(m.request) as request
216 cross join unnest(request.headers) as headers
217 where
218 response.status_code >= 400
219 ${generateRegexpWhere(filters, false)}
220 group by
221 request.path, request.method, request.search, response.status_code
222 order by
223 count desc
224 limit 10
225 `,
226 },
227 responseSpeed: {
228 queryType: 'logs',
229 sql: (filters) => `
230 -- reports-api-response-speed
231 select
232 cast(timestamp_trunc(t.timestamp, hour) as datetime) as timestamp,
233 avg(response.origin_time) as avg
234 FROM
235 edge_logs t
236 cross join unnest(metadata) as m
237 cross join unnest(m.response) as response
238 cross join unnest(m.request) as request
239 cross join unnest(request.headers) as headers
240 ${generateRegexpWhere(filters)}
241 GROUP BY
242 timestamp
243 ORDER BY
244 timestamp ASC
245 `,
246 },
247 topSlowRoutes: {
248 queryType: 'logs',
249 sql: (filters) => `
250 -- reports-api-top-slow-routes
251 select
252 request.path as path,
253 request.method as method,
254 request.search as search,
255 response.status_code as status_code,
256 count(t.id) as count,
257 avg(response.origin_time) as avg
258 from edge_logs t
259 cross join unnest(metadata) as m
260 cross join unnest(m.response) as response
261 cross join unnest(m.request) as request
262 cross join unnest(request.headers) as headers
263 ${generateRegexpWhere(filters)}
264 group by
265 request.path, request.method, request.search, response.status_code
266 order by
267 avg desc
268 limit 10
269 `,
270 },
271 networkTraffic: {
272 queryType: 'logs',
273 sql: (filters) => `
274 -- reports-api-network-traffic
275 select
276 cast(timestamp_trunc(t.timestamp, hour) as datetime) as timestamp,
277 coalesce(
278 safe_divide(
279 sum(
280 cast(coalesce(headers.content_length, "0") as int64)
281 ),
282 1000000
283 ),
284 0
285 ) as ingress_mb,
286 coalesce(
287 safe_divide(
288 sum(
289 cast(coalesce(resp_headers.content_length, "0") as int64)
290 ),
291 1000000
292 ),
293 0
294 ) as egress_mb,
295 FROM
296 edge_logs t
297 cross join unnest(metadata) as m
298 cross join unnest(m.response) as response
299 cross join unnest(m.request) as request
300 cross join unnest(request.headers) as headers
301 cross join unnest(response.headers) as resp_headers
302 ${generateRegexpWhere(filters)}
303 GROUP BY
304 timestamp
305 ORDER BY
306 timestamp ASC
307 `,
308 },
309 requestsByCountry: {
310 queryType: 'logs',
311 sql: (filters) => `
312 -- reports-api-requests-by-country
313 select
314 cf.country as country,
315 count(t.id) as count
316 from edge_logs t
317 cross join unnest(metadata) as m
318 cross join unnest(m.response) as response
319 cross join unnest(m.request) as request
320 cross join unnest(request.headers) as headers
321 cross join unnest(request.cf) as cf
322 where
323 cf.country is not null
324 ${generateRegexpWhere(filters, false)}
325 group by
326 cf.country
327 `,
328 },
329 },
330 },
331 [Presets.AUTH]: {
332 title: '',
333 queries: {},
334 },
335 [Presets.STORAGE]: {
336 title: 'Storage',
337 queries: {
338 cacheHitRate: {
339 queryType: 'logs',
340 // storage report does not perform any filtering
341 sql: (filters) => `
342 -- reports-storage-cache-hit-rate
343SELECT
344 timestamp_trunc(timestamp, hour) as timestamp,
345 countif( h.cf_cache_status in ('HIT', 'STALE', 'REVALIDATED', 'UPDATING') ) as hit_count,
346 countif( h.cf_cache_status in ('MISS', 'NONE/UNKNOWN', 'EXPIRED', 'BYPASS', 'DYNAMIC') ) as miss_count
347from edge_logs f
348 cross join unnest(f.metadata) as m
349 cross join unnest(m.request) as r
350 cross join unnest(m.response) as res
351 cross join unnest(res.headers) as h
352where starts_with(r.path, '/storage/v1/object') and r.method = 'GET'
353 ${generateRegexpWhere(filters, false)}
354group by timestamp
355order by timestamp desc
356`,
357 },
358 topCacheMisses: {
359 queryType: 'logs',
360 // storage report does not perform any filtering
361 sql: (filters) => `
362 -- reports-storage-top-cache-misses
363SELECT
364 r.path as path,
365 r.search as search,
366 count(id) as count
367from edge_logs f
368 cross join unnest(f.metadata) as m
369 cross join unnest(m.request) as r
370 cross join unnest(m.response) as res
371 cross join unnest(res.headers) as h
372where starts_with(r.path, '/storage/v1/object')
373 and r.method = 'GET'
374 and h.cf_cache_status in ('MISS', 'NONE/UNKNOWN', 'EXPIRED', 'BYPASS', 'DYNAMIC')
375 ${generateRegexpWhere(filters, false)}
376group by path, search
377order by count desc
378limit 12
379 `,
380 },
381 },
382 },
383 [Presets.QUERY_PERFORMANCE]: {
384 title: 'Query performance',
385 queries: {
386 mostFrequentlyInvoked: {
387 queryType: 'db',
388 safeSql: (
389 _params,
390 where,
391 orderBy,
392 runIndexAdvisor = false,
393 _filterIndexAdvisor = false
394 ) => safeSql`
395 -- reports-query-performance-most-frequently-invoked
396set search_path to public, extensions;
397
398select
399 auth.rolname,
400 statements.query,
401 statements.calls,
402 -- -- Postgres 13, 14, 15
403 statements.total_exec_time + statements.total_plan_time as total_time,
404 statements.min_exec_time + statements.min_plan_time as min_time,
405 statements.max_exec_time + statements.max_plan_time as max_time,
406 statements.mean_exec_time + statements.mean_plan_time as mean_time,
407 -- -- Postgres <= 12
408 -- total_time,
409 -- min_time,
410 -- max_time,
411 -- mean_time,
412 coalesce(statements.rows::numeric / nullif(statements.calls, 0), 0) as avg_rows,
413 statements.rows as rows_read,
414 case
415 when (statements.shared_blks_hit + statements.shared_blks_read) > 0
416 then round(
417 (statements.shared_blks_hit * 100.0) /
418 (statements.shared_blks_hit + statements.shared_blks_read),
419 2
420 )
421 else 0
422 end as cache_hit_rate${
423 runIndexAdvisor
424 ? safeSql`,
425 case
426 when (lower(statements.query) like 'select%' or lower(statements.query) like 'with pgrst%')
427 then (
428 select json_build_object(
429 'has_suggestion', array_length(index_statements, 1) > 0,
430 'startup_cost_before', startup_cost_before,
431 'startup_cost_after', startup_cost_after,
432 'total_cost_before', total_cost_before,
433 'total_cost_after', total_cost_after,
434 'index_statements', index_statements
435 )
436 from index_advisor(statements.query)
437 )
438 else null
439 end as index_advisor_result`
440 : safeSql``
441 }
442 from pg_stat_statements as statements
443 inner join pg_authid as auth on statements.userid = auth.oid
444 -- skip queries that were never actually executed
445 WHERE statements.calls > 0 ${where ? rewriteWhereToAnd(where) : safeSql``}
446 ${orderBy || safeSql`order by statements.calls desc`}
447 limit 20`,
448 },
449 mostTimeConsuming: {
450 queryType: 'db',
451 safeSql: (
452 _,
453 where,
454 orderBy,
455 runIndexAdvisor = false,
456 _filterIndexAdvisor = false
457 ) => safeSql`
458 -- reports-query-performance-most-time-consuming
459set search_path to public, extensions;
460
461-- compute total time once up front so we don't need a window function over all rows
462with grand_total as (
463 select coalesce(nullif(sum(total_exec_time + total_plan_time), 0), 1) as v
464 from pg_stat_statements where calls > 0
465)
466select
467 auth.rolname,
468 statements.query,
469 statements.calls,
470 statements.total_exec_time + statements.total_plan_time as total_time,
471 statements.mean_exec_time + statements.mean_plan_time as mean_time,
472 coalesce(
473 ((statements.total_exec_time + statements.total_plan_time) /
474 (select v from grand_total)) *
475 100,
476 0
477 ) as prop_total_time${
478 runIndexAdvisor
479 ? safeSql`,
480 case
481 when (lower(statements.query) like 'select%' or lower(statements.query) like 'with pgrst%')
482 then (
483 select json_build_object(
484 'has_suggestion', array_length(index_statements, 1) > 0,
485 'startup_cost_before', startup_cost_before,
486 'startup_cost_after', startup_cost_after,
487 'total_cost_before', total_cost_before,
488 'total_cost_after', total_cost_after,
489 'index_statements', index_statements
490 )
491 from index_advisor(statements.query)
492 )
493 else null
494 end as index_advisor_result`
495 : safeSql``
496 }
497 from pg_stat_statements as statements
498 inner join pg_authid as auth on statements.userid = auth.oid
499 -- skip queries that were never actually executed
500 WHERE statements.calls > 0 ${where ? rewriteWhereToAnd(where) : safeSql``}
501 ${orderBy || safeSql`order by total_time desc`}
502 limit 20`,
503 },
504 slowestExecutionTime: {
505 queryType: 'db',
506 safeSql: (
507 _params,
508 where,
509 orderBy,
510 runIndexAdvisor = false,
511 _filterIndexAdvisor = false
512 ) => safeSql`
513 -- reports-query-performance-slowest-execution-time
514set search_path to public, extensions;
515
516select
517 auth.rolname,
518 statements.query,
519 statements.calls,
520 -- -- Postgres 13, 14, 15
521 statements.total_exec_time + statements.total_plan_time as total_time,
522 statements.min_exec_time + statements.min_plan_time as min_time,
523 statements.max_exec_time + statements.max_plan_time as max_time,
524 statements.mean_exec_time + statements.mean_plan_time as mean_time,
525 -- -- Postgres <= 12
526 -- total_time,
527 -- min_time,
528 -- max_time,
529 -- mean_time,
530 coalesce(statements.rows::numeric / nullif(statements.calls, 0), 0) as avg_rows${
531 runIndexAdvisor
532 ? safeSql`,
533 case
534 when (lower(statements.query) like 'select%' or lower(statements.query) like 'with pgrst%')
535 then (
536 select json_build_object(
537 'has_suggestion', array_length(index_statements, 1) > 0,
538 'startup_cost_before', startup_cost_before,
539 'startup_cost_after', startup_cost_after,
540 'total_cost_before', total_cost_before,
541 'total_cost_after', total_cost_after,
542 'index_statements', index_statements
543 )
544 from index_advisor(statements.query)
545 )
546 else null
547 end as index_advisor_result`
548 : safeSql``
549 }
550 from pg_stat_statements as statements
551 inner join pg_authid as auth on statements.userid = auth.oid
552 -- skip queries that were never actually executed
553 WHERE statements.calls > 0 ${where ? rewriteWhereToAnd(where) : safeSql``}
554 ${orderBy || safeSql`order by max_time desc`}
555 limit 20`,
556 },
557 queryHitRate: {
558 queryType: 'db',
559 safeSql: (_params) => safeSql`-- reports-query-performance-cache-and-index-hit-rate
560select
561 'index hit rate' as name,
562 (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) as ratio
563 from pg_statio_user_indexes
564 union all
565 select
566 'table hit rate' as name,
567 sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) as ratio
568 from pg_statio_user_tables;`,
569 },
570 unified: {
571 queryType: 'db',
572 safeSql: (
573 _params,
574 where,
575 orderBy,
576 runIndexAdvisor = false,
577 filterIndexAdvisor = false,
578 page = 1,
579 pageSize = 20
580 ) => {
581 const offset = (page - 1) * pageSize
582 // When filtering by index suggestions we need a larger scan window since we don't
583 // know how many rows will match. Cap at a reasonable upper bound to avoid running
584 // index_advisor() across the entire dataset on any code path where it's active.
585 const INDEX_ADVISOR_SCAN_CAP = 500
586 const baseScanTarget =
587 filterIndexAdvisor && runIndexAdvisor ? offset + pageSize * 10 : offset + pageSize
588 const baseCteLimit = runIndexAdvisor
589 ? Math.min(baseScanTarget, INDEX_ADVISOR_SCAN_CAP)
590 : baseScanTarget
591 const baseQuery = safeSql`
592 -- reports-query-performance-unified
593 set search_path to public, extensions;
594
595 -- compute total time once up front so we don't need a window function over all rows
596 with grand_total as (
597 select coalesce(nullif(sum(total_exec_time + total_plan_time), 0), 1) as v
598 from pg_stat_statements where calls > 0
599 ),
600 base as (
601 select
602 auth.rolname,
603 statements.query,
604 statements.calls,
605 statements.total_exec_time + statements.total_plan_time as total_time,
606 statements.min_exec_time + statements.min_plan_time as min_time,
607 statements.max_exec_time + statements.max_plan_time as max_time,
608 statements.mean_exec_time + statements.mean_plan_time as mean_time,
609 coalesce(statements.rows::numeric / nullif(statements.calls, 0), 0) as avg_rows,
610 statements.rows as rows_read,
611 statements.shared_blks_hit as debug_hit,
612 statements.shared_blks_read as debug_read,
613 case
614 when (statements.shared_blks_hit + statements.shared_blks_read) > 0
615 then (statements.shared_blks_hit::numeric * 100.0) /
616 (statements.shared_blks_hit + statements.shared_blks_read)
617 else 0
618 end as cache_hit_rate,
619 coalesce(
620 ((statements.total_exec_time + statements.total_plan_time) /
621 (select v from grand_total)) *
622 100,
623 0
624 ) as prop_total_time
625 from pg_stat_statements as statements
626 inner join pg_authid as auth on statements.userid = auth.oid
627 -- skip queries that were never actually executed
628 WHERE statements.calls > 0 ${where ? rewriteWhereToAnd(where) : safeSql``}
629 ${orderBy || safeSql`order by total_time desc`}
630 ${baseCteLimit !== null ? safeSql`limit ${literal(baseCteLimit)}` : safeSql``}
631 ),
632 query_results as (
633 select
634 base.*${
635 runIndexAdvisor
636 ? safeSql`,
637 case
638 when (lower(base.query) like 'select%' or lower(base.query) like 'with pgrst%')
639 then (
640 select json_build_object(
641 'has_suggestion', array_length(index_statements, 1) > 0,
642 'startup_cost_before', startup_cost_before,
643 'startup_cost_after', startup_cost_after,
644 'total_cost_before', total_cost_before,
645 'total_cost_after', total_cost_after,
646 'index_statements', index_statements
647 )
648 from index_advisor(base.query)
649 )
650 else null
651 end as index_advisor_result`
652 : safeSql``
653 }
654 from base
655 )
656 select *
657 from query_results
658 ${filterIndexAdvisor && runIndexAdvisor ? safeSql`where (index_advisor_result->>'has_suggestion')::boolean = true` : safeSql``}
659 ${orderBy || safeSql`order by total_time desc`}
660 limit ${literal(pageSize)} offset ${literal(offset)}`
661
662 return baseQuery
663 },
664 },
665 slowQueriesCount: {
666 queryType: 'db',
667 safeSql: () => safeSql`
668 -- reports-query-performance-slow-queries-count
669 set search_path to public, extensions;
670
671 -- Count of slow queries (> 1 second average)
672 SELECT count(*) as slow_queries_count
673 -- alias needed to reference columns in WHERE
674 FROM pg_stat_statements as statements
675 -- skip never-executed queries; mean_exec_time > 1000ms = avg over 1 second
676 WHERE statements.calls > 0 AND statements.mean_exec_time > 1000;`,
677 },
678 queryMetrics: {
679 queryType: 'db',
680 safeSql: (
681 _params,
682 where,
683 orderBy,
684 _runIndexAdvisor = false,
685 _filterIndexAdvisor = false
686 ) => safeSql`
687 -- reports-query-performance-metrics
688 set search_path to public, extensions;
689
690 SELECT
691 COALESCE(ROUND(AVG(statements.rows::numeric / NULLIF(statements.calls, 0)), 1), 0) as avg_rows_per_call,
692 COUNT(*) FILTER (WHERE statements.total_exec_time + statements.total_plan_time > 1000) as slow_queries,
693 COALESCE(
694 ROUND(
695 SUM(statements.shared_blks_hit) * 100.0 /
696 NULLIF(SUM(statements.shared_blks_hit + statements.shared_blks_read), 0),
697 2
698 ), 0
699 ) || '%' as cache_hit_rate
700 FROM pg_stat_statements as statements
701 -- skip queries that were never actually executed
702 WHERE statements.calls > 0 ${where ? rewriteWhereToAnd(where) : safeSql``}
703 ${orderBy || safeSql``}`,
704 },
705 },
706 },
707 [Presets.DATABASE]: {
708 title: 'database',
709 queries: {
710 largeObjects: {
711 queryType: 'db',
712 safeSql: (_) => safeSql`-- reports-database-large-objects
713SELECT
714 SCHEMA_NAME,
715 relname,
716 table_size
717 FROM
718 (SELECT
719 pg_catalog.pg_namespace.nspname AS SCHEMA_NAME,
720 relname,
721 pg_total_relation_size(pg_catalog.pg_class.oid) AS table_size
722 FROM pg_catalog.pg_class
723 JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
724 ) t
725 WHERE SCHEMA_NAME NOT LIKE 'pg_%'
726 ORDER BY table_size DESC
727 LIMIT 5;`,
728 },
729 },
730 },
731}
732
733export const DEPRECATED_REPORTS = [
734 'total_realtime_ingress',
735 'total_rest_options_requests',
736 'total_auth_ingress',
737 'total_auth_get_requests',
738 'total_auth_post_requests',
739 'total_auth_patch_requests',
740 'total_auth_options_requests',
741 'total_storage_options_requests',
742 'total_storage_patch_requests',
743 'total_options_requests',
744 'total_rest_ingress',
745 'total_rest_get_requests',
746 'total_rest_post_requests',
747 'total_rest_patch_requests',
748 'total_rest_delete_requests',
749 'total_storage_get_requests',
750 'total_storage_post_requests',
751 'total_storage_delete_requests',
752 'total_auth_delete_requests',
753 'total_get_requests',
754 'total_patch_requests',
755 'total_post_requests',
756 'total_ingress',
757 'total_delete_requests',
758]
759
760export const EDGE_FUNCTION_REGIONS = [
761 {
762 key: 'ap-northeast-1',
763 label: 'Tokyo',
764 },
765 {
766 key: 'ap-northeast-2',
767 label: 'Seoul',
768 },
769 {
770 key: 'ap-south-1',
771 label: 'Mumbai',
772 },
773 {
774 key: 'ap-southeast-1',
775 label: 'Singapore',
776 },
777 {
778 key: 'ap-southeast-2',
779 label: 'Sydney',
780 },
781 {
782 key: 'ca-central-1',
783 label: 'Canada Central',
784 },
785 {
786 key: 'us-east-1',
787 label: 'N. Virginia',
788 },
789 {
790 key: 'us-west-1',
791 label: 'N. California',
792 },
793 {
794 key: 'us-west-2',
795 label: 'Oregon',
796 },
797 {
798 key: 'eu-central-1',
799 label: 'Frankfurt',
800 },
801 {
802 key: 'eu-west-1',
803 label: 'Ireland',
804 },
805 {
806 key: 'eu-west-2',
807 label: 'London',
808 },
809 {
810 key: 'eu-west-3',
811 label: 'Paris',
812 },
813 {
814 key: 'sa-east-1',
815 label: 'São Paulo',
816 },
817] as const