konnosDashboard
FunctionsList.tsx443 lines · main
1import { safeSql } from '@supabase/pg-meta'
2import { PermissionAction } from '@supabase/shared-types/out/constants'
3import { Search } from 'lucide-react'
4import { parseAsBoolean, parseAsJson, parseAsString, useQueryState } from 'nuqs'
5import { useEffect, useRef, useState } from 'react'
6import { toast } from 'sonner'
7import {
8 AiIconAnimation,
9 Button,
10 Card,
11 Table,
12 TableBody,
13 TableHead,
14 TableHeader,
15 TableRow,
16} from 'ui'
17import { Input } from 'ui-patterns/DataInputs/Input'
18import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'
19
20import { ProtectedSchemaWarning } from '../../ProtectedSchemaWarning'
21import FunctionList from './FunctionList'
22import { useIsInlineEditorEnabled } from '@/components/interfaces/Account/Preferences/useDashboardSettings'
23import { CreateFunction } from '@/components/interfaces/Database/Functions/CreateFunction'
24import {
25 ReportsSelectFilter,
26 selectFilterSchema,
27} from '@/components/interfaces/Reports/v2/ReportsSelectFilter'
28import { SIDEBAR_KEYS } from '@/components/layouts/ProjectLayout/LayoutSidebar/LayoutSidebarProvider'
29import ProductEmptyState from '@/components/to-be-cleaned/ProductEmptyState'
30import AlertError from '@/components/ui/AlertError'
31import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
32import SchemaSelector from '@/components/ui/SchemaSelector'
33import { Shortcut } from '@/components/ui/Shortcut'
34import { TextConfirmModal } from '@/components/ui/TextConfirmModalWrapper'
35import { useDatabaseFunctionDeleteMutation } from '@/data/database-functions/database-functions-delete-mutation'
36import type { SavedDatabaseFunction } from '@/data/database-functions/database-functions-query'
37import { useDatabaseFunctionsQuery } from '@/data/database-functions/database-functions-query'
38import { useSchemasQuery } from '@/data/database/schemas-query'
39import { useAsyncCheckPermissions } from '@/hooks/misc/useCheckPermissions'
40import { useQuerySchemaState } from '@/hooks/misc/useSchemaQueryState'
41import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
42import { useIsProtectedSchema } from '@/hooks/useProtectedSchemas'
43import { onSearchInputEscape } from '@/lib/keyboard'
44import { useAiAssistantStateSnapshot } from '@/state/ai-assistant-state'
45import { useEditorPanelStateSnapshot } from '@/state/editor-panel-state'
46import { SHORTCUT_IDS } from '@/state/shortcuts/registry'
47import { useShortcut } from '@/state/shortcuts/useShortcut'
48import { useSidebarManagerSnapshot } from '@/state/sidebar-manager-state'
49
50const createFunctionSnippet = safeSql`create function function_name()
51returns void
52language plpgsql
53as $$
54begin
55 -- Write your function logic here
56end;
57$$;`
58
59export const FunctionsList = () => {
60 const { data: project } = useSelectedProjectQuery()
61 const aiSnap = useAiAssistantStateSnapshot()
62 const { openSidebar } = useSidebarManagerSnapshot()
63 const { selectedSchema, setSelectedSchema } = useQuerySchemaState()
64 const isInlineEditorEnabled = useIsInlineEditorEnabled()
65 const {
66 setValue: setEditorPanelValue,
67 setTemplates: setEditorPanelTemplates,
68 setInitialPrompt: setEditorPanelInitialPrompt,
69 } = useEditorPanelStateSnapshot()
70
71 const createFunction = () => {
72 setSelectedFunctionIdToDuplicate(null)
73 if (isInlineEditorEnabled) {
74 setEditorPanelInitialPrompt('Create a new database function that...')
75 setEditorPanelValue(createFunctionSnippet)
76 setEditorPanelTemplates([])
77 openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
78 } else {
79 setShowCreateFunctionForm(true)
80 }
81 }
82
83 const duplicateFunction = (fn: SavedDatabaseFunction) => {
84 if (isInlineEditorEnabled) {
85 const dupFn = {
86 ...fn,
87 name: `${fn.name}_duplicate`,
88 }
89 setEditorPanelInitialPrompt('Create new database function that...')
90 setEditorPanelValue(dupFn.complete_statement)
91 setEditorPanelTemplates([])
92 openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
93 } else {
94 setSelectedFunctionIdToDuplicate(fn.id.toString())
95 }
96 }
97
98 const editFunction = (fn: SavedDatabaseFunction) => {
99 setSelectedFunctionIdToDuplicate(null)
100 if (isInlineEditorEnabled) {
101 setEditorPanelValue(fn.complete_statement)
102 setEditorPanelTemplates([])
103 openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
104 } else {
105 setSelectedFunctionToEdit(fn.id.toString())
106 }
107 }
108
109 const [filterString, setFilterString] = useQueryState(
110 'search',
111 parseAsString.withDefault('').withOptions({ clearOnDefault: true })
112 )
113
114 // Filters
115 const [returnTypeFilter, setReturnTypeFilter] = useQueryState(
116 'return_type',
117 parseAsJson(selectFilterSchema.parse)
118 )
119 const [securityFilter, setSecurityFilter] = useQueryState(
120 'security',
121 parseAsJson(selectFilterSchema.parse)
122 )
123
124 const [schemaSelectorOpen, setSchemaSelectorOpen] = useState(false)
125 const searchInputRef = useRef<HTMLInputElement>(null)
126
127 const { can: canCreateFunctions } = useAsyncCheckPermissions(
128 PermissionAction.TENANT_SQL_ADMIN_WRITE,
129 'functions'
130 )
131
132 const { isSchemaLocked } = useIsProtectedSchema({ schema: selectedSchema })
133
134 const canAddFunctions = canCreateFunctions && !isSchemaLocked
135
136 useShortcut(
137 SHORTCUT_IDS.LIST_PAGE_FOCUS_SEARCH,
138 () => {
139 searchInputRef.current?.focus()
140 searchInputRef.current?.select()
141 },
142 { label: 'Search functions' }
143 )
144
145 useShortcut(SHORTCUT_IDS.LIST_PAGE_RESET_FILTERS, () => {
146 setFilterString('')
147 setReturnTypeFilter(null)
148 setSecurityFilter(null)
149 })
150
151 // [Joshen] This is to preload the data for the Schema Selector
152 useSchemasQuery({
153 projectRef: project?.ref,
154 connectionString: project?.connectionString,
155 })
156
157 const {
158 data: functions = [],
159 error,
160 isPending: isLoading,
161 isError,
162 isSuccess,
163 } = useDatabaseFunctionsQuery({
164 projectRef: project?.ref,
165 connectionString: project?.connectionString,
166 })
167
168 // Get unique return types from functions in the selected schema
169 const schemaFunctions = functions.filter((fn) => fn.schema === selectedSchema)
170 const uniqueReturnTypes = Array.from(new Set(schemaFunctions.map((fn) => fn.return_type))).sort()
171
172 // Get security options based on what exists in the selected schema
173 const hasDefiner = schemaFunctions.some((fn) => fn.security_definer)
174 const hasInvoker = schemaFunctions.some((fn) => !fn.security_definer)
175 const securityOptions = [
176 ...(hasDefiner ? [{ label: 'Definer', value: 'definer' }] : []),
177 ...(hasInvoker ? [{ label: 'Invoker', value: 'invoker' }] : []),
178 ]
179
180 const [showCreateFunctionForm, setShowCreateFunctionForm] = useQueryState(
181 'new',
182 parseAsBoolean.withDefault(false).withOptions({ history: 'push', clearOnDefault: true })
183 )
184
185 const [functionIdToEdit, setSelectedFunctionToEdit] = useQueryState('edit', parseAsString)
186 const functionToEdit = functions.find((fn) => fn.id.toString() === functionIdToEdit)
187
188 const [functionIdToDuplicate, setSelectedFunctionIdToDuplicate] = useQueryState(
189 'duplicate',
190 parseAsString
191 )
192 const functionToDuplicate = functions.find((fn) => fn.id.toString() === functionIdToDuplicate)
193
194 const [functionIdToDelete, setSelectedFunctionToDelete] = useQueryState('delete', parseAsString)
195 const functionToDelete = functions.find((fn) => fn.id.toString() === functionIdToDelete)
196
197 const {
198 mutate: deleteDatabaseFunction,
199 isPending: isDeletingFunction,
200 isSuccess: isSuccessDelete,
201 } = useDatabaseFunctionDeleteMutation({
202 onSuccess: (_, variables) => {
203 toast.success(`Successfully removed function ${variables.func.name}`)
204 setSelectedFunctionToDelete(null)
205 },
206 })
207
208 const onDeleteFunction = () => {
209 if (!project) return console.error('Project is required')
210 if (!functionToDelete) return console.error('Function is required')
211
212 deleteDatabaseFunction({
213 func: functionToDelete,
214 projectRef: project.ref,
215 connectionString: project.connectionString,
216 })
217 }
218
219 useEffect(() => {
220 if (isSuccess && !!functionIdToEdit && !functionToEdit) {
221 toast('Function not found')
222 setSelectedFunctionToEdit(null)
223 }
224 }, [functionIdToEdit, functionToEdit, isSuccess, setSelectedFunctionToEdit])
225
226 useEffect(() => {
227 if (isSuccess && !!functionIdToDuplicate && !functionToDuplicate) {
228 toast('Function not found')
229 setSelectedFunctionIdToDuplicate(null)
230 }
231 }, [functionIdToDuplicate, functionToDuplicate, isSuccess, setSelectedFunctionIdToDuplicate])
232
233 useEffect(() => {
234 if (isSuccess && !!functionIdToDelete && !functionToDelete && !isSuccessDelete) {
235 toast('Function not found')
236 setSelectedFunctionToDelete(null)
237 }
238 }, [
239 functionIdToDelete,
240 functionToDelete,
241 isSuccess,
242 isSuccessDelete,
243 setSelectedFunctionToDelete,
244 ])
245
246 if (isLoading) return <GenericSkeletonLoader />
247 if (isError) return <AlertError error={error} subject="Failed to retrieve database functions" />
248
249 return (
250 <>
251 {(functions ?? []).length === 0 ? (
252 <div className="flex h-full w-full items-center justify-center">
253 <ProductEmptyState
254 title="Functions"
255 ctaButtonLabel="Create a new function"
256 onClickCta={() => createFunction()}
257 disabled={!canCreateFunctions}
258 disabledMessage="You need additional permissions to create functions"
259 >
260 <p className="text-sm text-foreground-light">
261 PostgreSQL functions are a set of SQL and procedural commands such as declarations,
262 assignments, loops, flow-of-control, etc.
263 </p>
264 <p className="text-sm text-foreground-light">
265 It's stored on the database server and can be invoked using the SQL interface.
266 </p>
267 </ProductEmptyState>
268 </div>
269 ) : (
270 <div className="w-full space-y-4">
271 <div className="flex flex-col lg:flex-row lg:items-center justify-between gap-2 flex-wrap">
272 <div className="flex flex-col lg:flex-row lg:items-center gap-2">
273 <Shortcut
274 id={SHORTCUT_IDS.LIST_PAGE_FOCUS_SCHEMA}
275 onTrigger={() => setSchemaSelectorOpen(true)}
276 side="bottom"
277 tooltipOpen={schemaSelectorOpen ? false : undefined}
278 >
279 <SchemaSelector
280 className="w-full lg:w-[180px]"
281 size="tiny"
282 showError={false}
283 selectedSchemaName={selectedSchema}
284 onSelectSchema={(schema) => {
285 setFilterString('')
286 setSelectedSchema(schema)
287 }}
288 open={schemaSelectorOpen}
289 onOpenChange={setSchemaSelectorOpen}
290 />
291 </Shortcut>
292 <Input
293 ref={searchInputRef}
294 placeholder="Search for a function"
295 size="tiny"
296 icon={<Search />}
297 value={filterString}
298 className="w-full lg:w-52"
299 onChange={(e) => setFilterString(e.target.value)}
300 onKeyDown={onSearchInputEscape(filterString, setFilterString)}
301 />
302 <ReportsSelectFilter
303 label="Return Type"
304 options={uniqueReturnTypes.map((type) => ({
305 label: type,
306 value: type,
307 }))}
308 value={returnTypeFilter ?? []}
309 onChange={setReturnTypeFilter}
310 showSearch
311 />
312 <ReportsSelectFilter
313 label="Security"
314 options={securityOptions}
315 value={securityFilter ?? []}
316 onChange={setSecurityFilter}
317 />
318 </div>
319
320 <div className="flex items-center gap-x-2">
321 {!isSchemaLocked && (
322 <>
323 {canAddFunctions ? (
324 <Shortcut
325 id={SHORTCUT_IDS.LIST_PAGE_NEW_ITEM}
326 label="Create new function"
327 onTrigger={() => createFunction()}
328 side="bottom"
329 >
330 <Button className="grow" onClick={() => createFunction()}>
331 Create a new function
332 </Button>
333 </Shortcut>
334 ) : (
335 <ButtonTooltip
336 disabled
337 className="grow"
338 tooltip={{
339 content: {
340 side: 'bottom',
341 text: 'You need additional permissions to create functions',
342 },
343 }}
344 >
345 Create a new function
346 </ButtonTooltip>
347 )}
348 <ButtonTooltip
349 type="default"
350 disabled={!canCreateFunctions}
351 className="px-1 pointer-events-auto"
352 icon={<AiIconAnimation size={16} />}
353 onClick={() => {
354 openSidebar(SIDEBAR_KEYS.AI_ASSISTANT)
355 aiSnap.newChat({
356 name: 'Create new function',
357 initialInput: `Create a new function for the schema ${selectedSchema} that does ...`,
358 })
359 }}
360 tooltip={{
361 content: {
362 side: 'bottom',
363 text: !canCreateFunctions
364 ? 'You need additional permissions to create functions'
365 : 'Create with Briven Assistant',
366 },
367 }}
368 />
369 </>
370 )}
371 </div>
372 </div>
373
374 {isSchemaLocked && <ProtectedSchemaWarning schema={selectedSchema} entity="functions" />}
375 <Card>
376 <Table className="table-fixed overflow-x-auto">
377 <TableHeader>
378 <TableRow>
379 <TableHead key="name">Name</TableHead>
380 <TableHead key="arguments" className="table-cell">
381 Arguments
382 </TableHead>
383 <TableHead key="return_type" className="table-cell">
384 Return type
385 </TableHead>
386 <TableHead key="security" className="table-cell w-[100px]">
387 Security
388 </TableHead>
389 <TableHead key="buttons" className="w-1/6"></TableHead>
390 </TableRow>
391 </TableHeader>
392 <TableBody>
393 <FunctionList
394 schema={selectedSchema}
395 filterString={filterString}
396 isLocked={isSchemaLocked}
397 returnTypeFilter={returnTypeFilter ?? []}
398 securityFilter={securityFilter ?? []}
399 duplicateFunction={duplicateFunction}
400 editFunction={editFunction}
401 deleteFunction={(fn) => setSelectedFunctionToDelete(fn.id.toString())}
402 functions={functions ?? []}
403 />
404 </TableBody>
405 </Table>
406 </Card>
407 </div>
408 )}
409
410 <CreateFunction
411 func={functionToEdit || functionToDuplicate}
412 visible={showCreateFunctionForm || !!functionToEdit || !!functionToDuplicate}
413 onClose={() => {
414 setShowCreateFunctionForm(false)
415 setSelectedFunctionToEdit(null)
416 setSelectedFunctionIdToDuplicate(null)
417 }}
418 isDuplicating={!!functionToDuplicate}
419 />
420
421 <TextConfirmModal
422 variant={'warning'}
423 visible={!!functionToDelete}
424 onCancel={() => setSelectedFunctionToDelete(null)}
425 onConfirm={onDeleteFunction}
426 title="Delete this function"
427 loading={isDeletingFunction}
428 confirmLabel={`Delete function ${functionToDelete?.name}`}
429 confirmPlaceholder="Type in name of function"
430 confirmString={functionToDelete?.name ?? 'Unknown'}
431 text={
432 <>
433 <span>This will delete the function</span>{' '}
434 <span className="text-bold text-foreground">{functionToDelete?.name}</span>{' '}
435 <span>from the schema</span>{' '}
436 <span className="text-bold text-foreground">{functionToDelete?.schema}</span>
437 </>
438 }
439 alert={{ title: 'You cannot recover this function once deleted.' }}
440 />
441 </>
442 )
443}