konnosDashboard
QueryInsightsTable.tsx588 lines · main
1import { safeSql, type SafeSqlFragment } from '@supabase/pg-meta'
2import { wrapWithRollback } from '@supabase/pg-meta/src/query'
3import { useParams } from 'common'
4import { Search, TextSearch, X } from 'lucide-react'
5import { useRouter } from 'next/router'
6import { parseAsArrayOf, parseAsString, useQueryStates } from 'nuqs'
7import { useCallback, useEffect, useMemo, useRef, useState } from 'react'
8// eslint-disable-next-line no-restricted-imports
9import DataGrid, { DataGridHandle, Row } from 'react-data-grid'
10import { Button, cn, Tabs_Shadcn_, TabsList_Shadcn_, TabsTrigger_Shadcn_ } from 'ui'
11import { Input } from 'ui-patterns/DataInputs/Input'
12import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'
13
14import { buildQueryInsightFixPrompt } from '../../QueryPerformance/QueryPerformance.ai'
15import type { QueryPerformanceRow } from '../../QueryPerformance/QueryPerformance.types'
16import { useQueryInsightsIssues } from '../hooks/useQueryInsightsIssues'
17import { useQueryInsightsTableColumns } from '../hooks/useQueryInsightsTableColumns'
18import type { ClassifiedQuery } from '../QueryInsightsHealth/QueryInsightsHealth.types'
19import { QueryInsightsDetailSheet } from './QueryInsightsDetailSheet'
20import type { IssueFilter, Mode } from './QueryInsightsTable.types'
21import {
22 formatDuration,
23 getColumnName,
24 getQueryType,
25 getTableName,
26} from './QueryInsightsTable.utils'
27import type { QueryPlanRow } from '@/components/interfaces/ExplainVisualizer/ExplainVisualizer.types'
28import { FilterPill } from '@/components/interfaces/QueryPerformance/components/FilterPill'
29import { SIDEBAR_KEYS } from '@/components/layouts/ProjectLayout/LayoutSidebar/LayoutSidebarProvider'
30import { FilterPopover } from '@/components/ui/FilterPopover'
31import { TwoOptionToggle } from '@/components/ui/TwoOptionToggle'
32import { useExecuteSqlMutation } from '@/data/sql/execute-sql-mutation'
33import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
34import { useAiAssistantStateSnapshot } from '@/state/ai-assistant-state'
35import { useSidebarManagerSnapshot } from '@/state/sidebar-manager-state'
36
37interface QueryInsightsTableProps {
38 data: QueryPerformanceRow[]
39 isLoading: boolean
40 currentSelectedQuery?: string | null
41 onCurrentSelectQuery?: (query: string | null) => void
42}
43
44export const QueryInsightsTable = ({
45 data,
46 isLoading,
47 currentSelectedQuery,
48 onCurrentSelectQuery,
49}: QueryInsightsTableProps) => {
50 const [mode, setMode] = useState<Mode>('triage')
51 const [filter, setFilter] = useState<IssueFilter>('all')
52 const [
53 { search: urlSearch, sort: urlSortCol, order: urlSortOrder, source: urlSource },
54 setQueryStates,
55 ] = useQueryStates({
56 search: parseAsString.withDefault(''),
57 sort: parseAsString,
58 order: parseAsString,
59 source: parseAsArrayOf(parseAsString).withDefault([]),
60 })
61 const [searchQuery, setSearchQuery] = useState(urlSearch || '')
62 const appNameFilter = urlSource
63 const setAppNameFilter = (names: string[]) =>
64 setQueryStates({ source: names.length ? names : null })
65
66 const appNameOptions = useMemo(() => {
67 const names = Array.from(
68 new Set(data.map((r) => r.application_name).filter(Boolean))
69 ) as string[]
70 return names.map((name) => ({ value: name, label: name }))
71 }, [data])
72
73 const filteredData = useMemo(() => {
74 if (appNameFilter.length === 0) return data
75 return data.filter((r) => appNameFilter.includes(r.application_name ?? ''))
76 }, [data, appNameFilter])
77
78 const { classified, errors, indexIssues, slowQueries } = useQueryInsightsIssues(filteredData)
79 const [selectedRow, setSelectedRow] = useState<number>()
80 const [selectedTriageRow, setSelectedTriageRow] = useState<number | undefined>()
81 const [sheetView, setSheetView] = useState<'details' | 'indexes' | 'explain'>('details')
82 const gridRef = useRef<DataGridHandle>(null)
83 const triageGridRef = useRef<DataGridHandle>(null)
84 const dataGridContainerRef = useRef<HTMLDivElement>(null)
85 const triageContainerRef = useRef<HTMLDivElement>(null)
86 const scrollContainerRef = useRef<HTMLDivElement>(null)
87 const [triageContainerWidth, setTriageContainerWidth] = useState(0)
88 const sort = useMemo<{ column: string; order: 'asc' | 'desc' }>(
89 () =>
90 urlSortCol && urlSortOrder && ['asc', 'desc'].includes(urlSortOrder)
91 ? { column: urlSortCol, order: urlSortOrder as 'asc' | 'desc' }
92 : { column: 'prop_total_time', order: 'desc' },
93 [urlSortCol, urlSortOrder]
94 )
95 const setSort = useCallback(
96 (config: { column: string; order: 'asc' | 'desc' } | null) =>
97 setQueryStates(
98 config ? { sort: config.column, order: config.order } : { sort: null, order: null }
99 ),
100 [setQueryStates]
101 )
102
103 const [explainResults, setExplainResults] = useState<Record<string, QueryPlanRow[]>>({})
104 const [explainLoadingQuery, setExplainLoadingQuery] = useState<string | null>(null)
105
106 const { ref } = useParams()
107 const router = useRouter()
108 const { openSidebar } = useSidebarManagerSnapshot()
109 const aiSnap = useAiAssistantStateSnapshot()
110
111 const { data: project } = useSelectedProjectQuery()
112
113 const { mutate: executeExplain } = useExecuteSqlMutation()
114
115 const triageItems = useMemo(() => classified.filter((q) => q.issueType !== null), [classified])
116
117 const filteredTriageItems = useMemo(() => {
118 const filtered =
119 filter === 'all' ? triageItems : triageItems.filter((q) => q.issueType === filter)
120 return filtered.map((item) => ({
121 ...item,
122 queryType: getQueryType(item.query),
123 }))
124 }, [triageItems, filter])
125
126 const explorerItems = useMemo(() => {
127 let items = [...classified]
128
129 if (searchQuery.trim()) {
130 const searchLower = searchQuery.toLowerCase()
131 items = items.filter((item) => {
132 const queryType = getQueryType(item.query) ?? ''
133 const tableName = getTableName(item.query) ?? ''
134 const columnName = getColumnName(item.query) ?? ''
135 const appName = item.application_name ?? ''
136 const query = item.query ?? ''
137
138 return (
139 queryType.toLowerCase().includes(searchLower) ||
140 tableName.toLowerCase().includes(searchLower) ||
141 columnName.toLowerCase().includes(searchLower) ||
142 appName.toLowerCase().includes(searchLower) ||
143 query.toLowerCase().includes(searchLower)
144 )
145 })
146 }
147
148 if (sort) {
149 items.sort((a, b) => {
150 if (sort.column === 'query') {
151 const aDate = a.first_seen ? new Date(a.first_seen).getTime() : 0
152 const bDate = b.first_seen ? new Date(b.first_seen).getTime() : 0
153 return sort.order === 'asc' ? aDate - bDate : bDate - aDate
154 }
155
156 const aValue: unknown = a[sort.column as keyof typeof a]
157 const bValue: unknown = b[sort.column as keyof typeof b]
158
159 if (typeof aValue === 'number' && typeof bValue === 'number') {
160 return sort.order === 'asc' ? aValue - bValue : bValue - aValue
161 }
162
163 return 0
164 })
165 }
166
167 return items
168 }, [classified, searchQuery, sort])
169
170 const activeSheetRow: ClassifiedQuery | undefined = useMemo(() => {
171 if (mode === 'triage') {
172 return selectedTriageRow !== undefined ? filteredTriageItems[selectedTriageRow] : undefined
173 }
174 return selectedRow !== undefined ? (explorerItems[selectedRow] as ClassifiedQuery) : undefined
175 }, [mode, selectedTriageRow, selectedRow, filteredTriageItems, explorerItems])
176
177 const runExplain = useCallback(
178 (query: SafeSqlFragment) => {
179 if (explainResults[query]) return
180 if (explainLoadingQuery) return
181 const requestQuery = query
182 setExplainLoadingQuery(requestQuery)
183 executeExplain(
184 {
185 projectRef: project?.ref,
186 connectionString: project?.connectionString,
187 sql: wrapWithRollback(safeSql`EXPLAIN ANALYZE ${requestQuery}`),
188 },
189 {
190 onSuccess(data) {
191 setExplainResults((prev) => ({ ...prev, [requestQuery]: data.result }))
192 setExplainLoadingQuery(null)
193 },
194 onError() {
195 setExplainLoadingQuery(null)
196 },
197 }
198 )
199 },
200 [explainResults, explainLoadingQuery, executeExplain, project]
201 )
202
203 const handleGoToLogs = useCallback(() => {
204 router.push(`/project/${ref}/logs/postgres-logs`)
205 }, [router, ref])
206
207 const handleAiSuggestedFix = useCallback(
208 (item: ClassifiedQuery) => {
209 const { query, prompt } = buildQueryInsightFixPrompt(item)
210 openSidebar(SIDEBAR_KEYS.AI_ASSISTANT)
211 aiSnap.newChat({
212 sqlSnippets: [{ label: 'Query', content: query }],
213 initialMessage: prompt,
214 })
215 },
216 [openSidebar, aiSnap]
217 )
218
219 useEffect(() => {
220 if (sheetView === 'explain' && activeSheetRow?.query) {
221 runExplain(activeSheetRow.query)
222 }
223 // eslint-disable-next-line react-hooks/exhaustive-deps
224 }, [sheetView, activeSheetRow?.query])
225
226 useEffect(() => {
227 const currentPath = router.asPath.split('?')[0]
228 const handleRouteChange = (url: string) => {
229 if (url.split('?')[0] !== currentPath) {
230 setQueryStates({ search: null })
231 onCurrentSelectQuery?.(null)
232 }
233 }
234 router.events.on('routeChangeStart', handleRouteChange)
235 return () => router.events.off('routeChangeStart', handleRouteChange)
236 // eslint-disable-next-line react-hooks/exhaustive-deps
237 }, [])
238
239 const timeConsumedWidth = useMemo(() => {
240 if (!explorerItems.length) return 150
241 let maxWidth = 150
242 explorerItems.forEach((row) => {
243 const pct = row.prop_total_time || 0
244 const total = row.total_time || 0
245 if (pct && total) {
246 const text = `${pct.toFixed(1)}% / ${formatDuration(total)}`
247 maxWidth = Math.max(maxWidth, text.length * 8 + 40)
248 }
249 })
250 return Math.min(maxWidth, 300)
251 }, [explorerItems])
252
253 const triageQueryColWidth = useMemo(() => {
254 if (!triageContainerWidth) return 380
255 const fixed = timeConsumedWidth + 100 + 300 + 4
256 return Math.max(380, triageContainerWidth - fixed - 120)
257 }, [triageContainerWidth, timeConsumedWidth])
258
259 const { columns, triageColumns } = useQueryInsightsTableColumns({
260 sort,
261 setSort,
262 timeConsumedWidth,
263 triageQueryColWidth,
264 gridRef,
265 setSelectedRow,
266 setSelectedTriageRow,
267 setSheetView,
268 handleGoToLogs,
269 handleAiSuggestedFix,
270 })
271
272 const handleKeyDown = useCallback(
273 (event: KeyboardEvent) => {
274 if (!explorerItems.length || selectedRow === undefined) return
275
276 if (event.key !== 'ArrowUp' && event.key !== 'ArrowDown') return
277
278 event.stopPropagation()
279
280 let nextIndex = selectedRow
281 if (event.key === 'ArrowUp' && selectedRow > 0) {
282 nextIndex = selectedRow - 1
283 } else if (event.key === 'ArrowDown' && selectedRow < explorerItems.length - 1) {
284 nextIndex = selectedRow + 1
285 }
286
287 if (nextIndex !== selectedRow) {
288 setSelectedRow(nextIndex)
289 gridRef.current?.scrollToCell({ idx: 0, rowIdx: nextIndex })
290 }
291 },
292 [explorerItems, selectedRow]
293 )
294
295 useEffect(() => {
296 window.addEventListener('keydown', handleKeyDown, true)
297 return () => {
298 window.removeEventListener('keydown', handleKeyDown, true)
299 }
300 }, [handleKeyDown])
301
302 useEffect(() => {
303 setSelectedRow(undefined)
304 }, [searchQuery, sort])
305
306 useEffect(() => {
307 if (mode === 'triage') {
308 triageGridRef.current?.scrollToCell({ idx: 0, rowIdx: 0 })
309 } else {
310 gridRef.current?.scrollToCell({ idx: 0, rowIdx: 0 })
311 }
312 }, [mode])
313
314 useEffect(() => {
315 const el = triageContainerRef.current
316 if (!el) return
317 const observer = new ResizeObserver(([entry]) => {
318 setTriageContainerWidth(entry.contentRect.width)
319 })
320 observer.observe(el)
321 return () => observer.disconnect()
322 }, [])
323
324 useEffect(() => {
325 if (urlSearch !== searchQuery) {
326 setQueryStates({ search: searchQuery || null })
327 }
328 // eslint-disable-next-line react-hooks/exhaustive-deps
329 }, [searchQuery])
330
331 const errorCount = errors.length
332 const indexCount = indexIssues.length
333 const slowCount = slowQueries.length
334
335 return (
336 <div className="flex flex-col flex-1 min-h-0">
337 <div className="overflow-x-auto shrink-0 bg-surface-100 border-b">
338 <div className="flex items-center justify-between px-6 h-10 min-w-max">
339 <div className="flex items-center gap-x-1.5">
340 <TwoOptionToggle
341 width={75}
342 options={['explorer', 'triage']}
343 activeOption={mode}
344 borderOverride="border"
345 onClickOption={(mode) => setMode(mode as Mode)}
346 />
347 {appNameFilter.length > 0 ? (
348 <FilterPill
349 label="Source"
350 value={appNameFilter.join(', ')}
351 onClear={() => setAppNameFilter([])}
352 />
353 ) : (
354 <FilterPopover
355 name="Source"
356 options={appNameOptions}
357 activeOptions={appNameFilter}
358 valueKey="value"
359 labelKey="label"
360 onSaveFilters={setAppNameFilter}
361 showOnlyButton={false}
362 />
363 )}
364 </div>
365
366 <div className="flex items-center">
367 {mode === 'triage' ? (
368 <Tabs_Shadcn_ value={filter} onValueChange={(v) => setFilter(v as IssueFilter)}>
369 <TabsList_Shadcn_ className="flex gap-x-4 rounded-none mt-0! pt-0 border-none!">
370 <TabsTrigger_Shadcn_
371 value="all"
372 className="text-xs py-3 border-b font-mono uppercase"
373 >
374 All{triageItems.length > 0 && ` (${triageItems.length})`}
375 </TabsTrigger_Shadcn_>
376 <TabsTrigger_Shadcn_
377 value="error"
378 className="text-xs py-3 border-b font-mono uppercase"
379 >
380 Errors{errorCount > 0 && ` (${errorCount})`}
381 </TabsTrigger_Shadcn_>
382 <TabsTrigger_Shadcn_
383 value="index"
384 className="text-xs py-3 border-b font-mono uppercase"
385 >
386 Index{indexCount > 0 && ` (${indexCount})`}
387 </TabsTrigger_Shadcn_>
388 <TabsTrigger_Shadcn_
389 value="slow"
390 className="text-xs py-3 border-b font-mono uppercase"
391 >
392 Slow{slowCount > 0 && ` (${slowCount})`}
393 </TabsTrigger_Shadcn_>
394 </TabsList_Shadcn_>
395 </Tabs_Shadcn_>
396 ) : (
397 <Input
398 size="tiny"
399 autoComplete="off"
400 icon={<Search className="h-4 w-4" />}
401 value={searchQuery}
402 onChange={(e) => setSearchQuery(e.target.value)}
403 name="search"
404 id="search"
405 placeholder="Search queries..."
406 className="w-64"
407 actions={[
408 searchQuery && (
409 <Button
410 key="clear"
411 size="tiny"
412 type="text"
413 icon={<X className="h-4 w-4" />}
414 onClick={() => setSearchQuery('')}
415 className="p-0 h-5 w-5"
416 />
417 ),
418 ]}
419 />
420 )}
421 </div>
422 </div>
423 </div>
424
425 <div
426 ref={scrollContainerRef}
427 className="relative flex-1 min-h-0 flex flex-col overflow-hidden"
428 >
429 <div
430 className={[
431 'absolute bottom-8 left-0 right-0 flex justify-center z-10 pointer-events-none',
432 'transition-all duration-200',
433 currentSelectedQuery
434 ? 'opacity-100 translate-y-0 pointer-events-auto'
435 : 'opacity-0 translate-y-4 pointer-events-none',
436 ].join(' ')}
437 >
438 <Button
439 type="default"
440 size="tiny"
441 className="rounded-full shadow-md"
442 onClick={() => onCurrentSelectQuery?.(null)}
443 >
444 Clear query
445 </Button>
446 </div>
447 {isLoading ? (
448 <div className="px-6 py-4">
449 <GenericSkeletonLoader />
450 </div>
451 ) : mode === 'triage' ? (
452 <div ref={triageContainerRef} className="flex-1 min-h-0 min-w-0 overflow-x-auto">
453 <DataGrid
454 ref={triageGridRef}
455 style={{ height: '100%' }}
456 className="flex-1 grow h-full"
457 rowHeight={60}
458 headerRowHeight={36}
459 columns={triageColumns}
460 rows={filteredTriageItems}
461 rowClass={(_, idx) => {
462 const isSelected = idx === selectedTriageRow
463 const query = filteredTriageItems[idx]?.query
464 const isCharted = currentSelectedQuery ? currentSelectedQuery === query : false
465 return [
466 `${isSelected ? 'bg-surface-300 dark:bg-surface-300' : isCharted ? 'bg-surface-200 dark:bg-surface-200' : 'bg-200 hover:bg-surface-200'} cursor-pointer`,
467 '[&>div:first-child]:border-l-4 [&>div:first-child]:pl-5 [&>div:last-child]:pr-6',
468 `${isSelected || isCharted ? '[&>div:first-child]:border-l-foreground' : '[&>div:first-child]:border-l-transparent'}`,
469 '[&>.rdg-cell]:box-border [&>.rdg-cell]:outline-hidden [&>.rdg-cell]:shadow-none [&>.rdg-cell]:py-3',
470 '[&>.rdg-cell.column-prop_total_time]:relative',
471 ].join(' ')
472 }}
473 renderers={{
474 renderRow(idx, props) {
475 return (
476 <Row
477 {...props}
478 key={`triage-row-${props.rowIdx}`}
479 onClick={(event) => {
480 event.stopPropagation()
481 if (typeof idx === 'number' && idx >= 0) {
482 const query = filteredTriageItems[idx]?.query
483 if (query && onCurrentSelectQuery) {
484 onCurrentSelectQuery(currentSelectedQuery === query ? null : query)
485 }
486 }
487 }}
488 />
489 )
490 },
491 noRowsFallback: (
492 <div className="absolute top-20 px-6 flex flex-col items-center justify-center w-full gap-y-2">
493 <TextSearch className="text-foreground-muted" strokeWidth={1} />
494 <div className="text-center">
495 <p className="text-foreground">No issues found</p>
496 <p className="text-foreground-light">
497 {data.length === 0
498 ? 'No query data available yet'
499 : 'No issues detected for the selected filter'}
500 </p>
501 </div>
502 </div>
503 ),
504 }}
505 />
506 </div>
507 ) : (
508 <div ref={dataGridContainerRef} className="flex-1 min-h-0 min-w-0 overflow-x-auto">
509 <DataGrid
510 ref={gridRef}
511 style={{ height: '100%' }}
512 className={cn('flex-1 grow h-full')}
513 rowHeight={44}
514 headerRowHeight={36}
515 columns={columns}
516 rows={explorerItems}
517 rowClass={(_, idx) => {
518 const isSelected = idx === selectedRow
519 const query = explorerItems[idx]?.query
520 const isCharted = currentSelectedQuery ? currentSelectedQuery === query : false
521 return [
522 `${isSelected ? 'bg-surface-300 dark:bg-surface-300' : isCharted ? 'bg-surface-200 dark:bg-surface-200' : 'bg-200 hover:bg-surface-200'} cursor-pointer`,
523 '[&>div:first-child]:border-l-4 [&>div:first-child]:pl-5 [&>div:last-child]:pr-6',
524 `${isSelected || isCharted ? '[&>div:first-child]:border-l-foreground' : '[&>div:first-child]:border-l-transparent'}`,
525 '[&>.rdg-cell]:box-border [&>.rdg-cell]:outline-hidden [&>.rdg-cell]:shadow-none [&>.rdg-cell]:py-3',
526 '[&>.rdg-cell.column-prop_total_time]:relative',
527 ].join(' ')
528 }}
529 renderers={{
530 renderRow(idx, props) {
531 return (
532 <Row
533 {...props}
534 key={`explorer-row-${props.rowIdx}`}
535 onClick={(event) => {
536 event.stopPropagation()
537 if (typeof idx === 'number' && idx >= 0) {
538 const query = explorerItems[idx]?.query
539 if (query && onCurrentSelectQuery) {
540 onCurrentSelectQuery(currentSelectedQuery === query ? null : query)
541 }
542 }
543 }}
544 />
545 )
546 },
547 noRowsFallback: (
548 <div className="absolute top-20 px-6 flex flex-col items-center justify-center w-full gap-y-2">
549 <TextSearch className="text-foreground-muted" strokeWidth={1} />
550 <div className="text-center">
551 <p className="text-foreground">No queries found</p>
552 <p className="text-foreground-light">
553 {searchQuery.trim()
554 ? 'No queries match your search criteria'
555 : 'No query data available yet'}
556 </p>
557 </div>
558 </div>
559 ),
560 }}
561 />
562 </div>
563 )}
564 </div>
565
566 <QueryInsightsDetailSheet
567 open={activeSheetRow !== undefined}
568 onOpenChange={(open) => {
569 if (!open) {
570 setSelectedTriageRow(undefined)
571 setSelectedRow(undefined)
572 }
573 }}
574 activeSheetRow={activeSheetRow}
575 sheetView={sheetView}
576 onSheetViewChange={setSheetView}
577 onClose={() => {
578 setSelectedTriageRow(undefined)
579 setSelectedRow(undefined)
580 }}
581 dataGridContainerRef={dataGridContainerRef}
582 triageContainerRef={triageContainerRef}
583 explainLoadingQuery={explainLoadingQuery}
584 explainResults={explainResults}
585 />
586 </div>
587 )
588}