Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

16.1 KB
Raw Blame
import { safeSql } from '@supabase/pg-meta'
import { DatabaseIcon, PlusIcon, SearchIcon } from 'lucide-react'
import { parseAsBoolean, parseAsJson, parseAsString, useQueryState } from 'nuqs'
import { useEffect, useRef, useState } from 'react'
import { toast } from 'sonner'
import {
  AiIconAnimation,
  Button,
  Card,
  Table,
  TableBody,
  TableHead,
  TableHeader,
  TableRow,
} from 'ui'
import { Input } from 'ui-patterns/DataInputs/Input'
import { EmptyStatePresentational } from 'ui-patterns/EmptyStatePresentational'
import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'

import { ProtectedSchemaWarning } from '../../ProtectedSchemaWarning'
import { FunctionList } from './FunctionList'
import { useIsInlineEditorEnabled } from '@/components/interfaces/Account/Preferences/useDashboardSettings'
import { CreateFunction } from '@/components/interfaces/Database/Functions/CreateFunction'
import {
  ReportsSelectFilter,
  selectFilterSchema,
} from '@/components/interfaces/Reports/v2/ReportsSelectFilter'
import { SIDEBAR_KEYS } from '@/components/layouts/ProjectLayout/LayoutSidebar/LayoutSidebarProvider'
import { AlertError } from '@/components/ui/AlertError'
import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
import { SchemaSelector } from '@/components/ui/SchemaSelector'
import { Shortcut } from '@/components/ui/Shortcut'
import { TextConfirmModal } from '@/components/ui/TextConfirmModalWrapper'
import { useDatabaseFunctionDeleteMutation } from '@/data/database-functions/database-functions-delete-mutation'
import type { SavedDatabaseFunction } from '@/data/database-functions/database-functions-query'
import { useDatabaseFunctionsQuery } from '@/data/database-functions/database-functions-query'
import { useSchemasQuery } from '@/data/database/schemas-query'
import { FGA_PERMISSIONS, useAsyncCheckPermissionsV2 } from '@/hooks/misc/useCheckPermissionsV2'
import { useQuerySchemaState } from '@/hooks/misc/useSchemaQueryState'
import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
import { useIsProtectedSchema } from '@/hooks/useProtectedSchemas'
import { onSearchInputEscape } from '@/lib/keyboard'
import { useAiAssistantStateSnapshot } from '@/state/ai-assistant-state'
import { useEditorPanelStateSnapshot } from '@/state/editor-panel-state'
import { SHORTCUT_IDS } from '@/state/shortcuts/registry'
import { useShortcut } from '@/state/shortcuts/useShortcut'
import { useSidebarManagerSnapshot } from '@/state/sidebar-manager-state'

const createFunctionSnippet = safeSql`create function function_name()
returns void
language plpgsql
as $$
begin
  -- Write your function logic here
end;
$$;`

export const FunctionsList = () => {
  const { data: project } = useSelectedProjectQuery()
  const aiSnap = useAiAssistantStateSnapshot()
  const { openSidebar } = useSidebarManagerSnapshot()
  const { selectedSchema, setSelectedSchema } = useQuerySchemaState()
  const isInlineEditorEnabled = useIsInlineEditorEnabled()
  const {
    setValue: setEditorPanelValue,
    setTemplates: setEditorPanelTemplates,
    setInitialPrompt: setEditorPanelInitialPrompt,
  } = useEditorPanelStateSnapshot()

  const createFunction = () => {
    setSelectedFunctionIdToDuplicate(null)
    if (isInlineEditorEnabled) {
      setEditorPanelInitialPrompt('Create a new database function that...')
      setEditorPanelValue(createFunctionSnippet)
      setEditorPanelTemplates([])
      openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
    } else {
      setShowCreateFunctionForm(true)
    }
  }

  const duplicateFunction = (fn: SavedDatabaseFunction) => {
    if (isInlineEditorEnabled) {
      const dupFn = {
        ...fn,
        name: `${fn.name}_duplicate`,
      }
      setEditorPanelInitialPrompt('Create new database function that...')
      setEditorPanelValue(dupFn.complete_statement)
      setEditorPanelTemplates([])
      openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
    } else {
      setSelectedFunctionIdToDuplicate(fn.id.toString())
    }
  }

  const editFunction = (fn: SavedDatabaseFunction) => {
    setSelectedFunctionIdToDuplicate(null)
    if (isInlineEditorEnabled) {
      setEditorPanelValue(fn.complete_statement)
      setEditorPanelTemplates([])
      openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
    } else {
      setSelectedFunctionToEdit(fn.id.toString())
    }
  }

  const [filterString, setFilterString] = useQueryState(
    'search',
    parseAsString.withDefault('').withOptions({ clearOnDefault: true })
  )

  // Filters
  const [returnTypeFilter, setReturnTypeFilter] = useQueryState(
    'return_type',
    parseAsJson(selectFilterSchema.parse)
  )
  const [securityFilter, setSecurityFilter] = useQueryState(
    'security',
    parseAsJson(selectFilterSchema.parse)
  )

  const [schemaSelectorOpen, setSchemaSelectorOpen] = useState(false)
  const searchInputRef = useRef<HTMLInputElement>(null)

  const { can: canCreateFunctions } = useAsyncCheckPermissionsV2(
    FGA_PERMISSIONS.PROJECT.DATABASE_WRITE
  )

  const { isSchemaLocked } = useIsProtectedSchema({ schema: selectedSchema })

  useShortcut(
    SHORTCUT_IDS.LIST_PAGE_FOCUS_SEARCH,
    () => {
      searchInputRef.current?.focus()
      searchInputRef.current?.select()
    },
    { label: 'Search functions' }
  )

  useShortcut(SHORTCUT_IDS.LIST_PAGE_RESET_FILTERS, () => {
    setFilterString('')
    setReturnTypeFilter(null)
    setSecurityFilter(null)
  })

  // [Joshen] This is to preload the data for the Schema Selector
  useSchemasQuery({
    projectRef: project?.ref,
    connectionString: project?.connectionString,
  })

  const {
    data: functions = [],
    error,
    isPending: isLoading,
    isError,
    isSuccess,
  } = useDatabaseFunctionsQuery({
    projectRef: project?.ref,
    connectionString: project?.connectionString,
    schema: selectedSchema,
  })

  // Get unique return types from functions in the selected schema
  const schemaFunctions = functions.filter((fn) => fn.schema === selectedSchema)
  const uniqueReturnTypes = Array.from(new Set(schemaFunctions.map((fn) => fn.return_type)))
    .filter(Boolean)
    .sort()

  // Get security options based on what exists in the selected schema
  const hasDefiner = schemaFunctions.some((fn) => fn.security_definer)
  const hasInvoker = schemaFunctions.some((fn) => !fn.security_definer)
  const securityOptions = [
    ...(hasDefiner ? [{ label: 'Definer', value: 'definer' }] : []),
    ...(hasInvoker ? [{ label: 'Invoker', value: 'invoker' }] : []),
  ]

  const [showCreateFunctionForm, setShowCreateFunctionForm] = useQueryState(
    'new',
    parseAsBoolean.withDefault(false).withOptions({ history: 'push', clearOnDefault: true })
  )

  const [functionIdToEdit, setSelectedFunctionToEdit] = useQueryState('edit', parseAsString)
  const functionToEdit = functions.find((fn) => fn.id.toString() === functionIdToEdit)

  const [functionIdToDuplicate, setSelectedFunctionIdToDuplicate] = useQueryState(
    'duplicate',
    parseAsString
  )
  const functionToDuplicate = functions.find((fn) => fn.id.toString() === functionIdToDuplicate)

  const [functionIdToDelete, setSelectedFunctionToDelete] = useQueryState('delete', parseAsString)
  const functionToDelete = functions.find((fn) => fn.id.toString() === functionIdToDelete)

  const {
    mutate: deleteDatabaseFunction,
    isPending: isDeletingFunction,
    isSuccess: isSuccessDelete,
  } = useDatabaseFunctionDeleteMutation({
    onSuccess: (_, variables) => {
      toast.success(`Successfully removed function ${variables.func.name}`)
      setSelectedFunctionToDelete(null)
    },
  })

  const onDeleteFunction = () => {
    if (!project) return console.error('Project is required')
    if (!functionToDelete) return console.error('Function is required')

    deleteDatabaseFunction({
      func: functionToDelete,
      projectRef: project.ref,
      connectionString: project.connectionString,
    })
  }

  useEffect(() => {
    if (isSuccess && !!functionIdToEdit && !functionToEdit) {
      toast('Function not found')
      setSelectedFunctionToEdit(null)
    }
  }, [functionIdToEdit, functionToEdit, isSuccess, setSelectedFunctionToEdit])

  useEffect(() => {
    if (isSuccess && !!functionIdToDuplicate && !functionToDuplicate) {
      toast('Function not found')
      setSelectedFunctionIdToDuplicate(null)
    }
  }, [functionIdToDuplicate, functionToDuplicate, isSuccess, setSelectedFunctionIdToDuplicate])

  useEffect(() => {
    if (isSuccess && !!functionIdToDelete && !functionToDelete && !isSuccessDelete) {
      toast('Function not found')
      setSelectedFunctionToDelete(null)
    }
  }, [
    functionIdToDelete,
    functionToDelete,
    isSuccess,
    isSuccessDelete,
    setSelectedFunctionToDelete,
  ])

  if (isLoading) return <GenericSkeletonLoader />
  if (isError) return <AlertError error={error} subject="Failed to retrieve database functions" />

  return (
    <>
      <div className="w-full space-y-4">
        <div className="flex flex-col lg:flex-row lg:items-center justify-between gap-2 flex-wrap">
          <div className="flex flex-col lg:flex-row lg:items-center gap-2">
            <Shortcut
              id={SHORTCUT_IDS.LIST_PAGE_FOCUS_SCHEMA}
              onTrigger={() => setSchemaSelectorOpen(true)}
              side="bottom"
              tooltipOpen={schemaSelectorOpen ? false : undefined}
            >
              <SchemaSelector
                className="w-full lg:w-[180px]"
                size="tiny"
                showError={false}
                selectedSchemaName={selectedSchema}
                onSelectSchema={(schema) => {
                  setFilterString('')
                  setSelectedSchema(schema)
                }}
                open={schemaSelectorOpen}
                onOpenChange={setSchemaSelectorOpen}
              />
            </Shortcut>
            <Input
              ref={searchInputRef}
              placeholder="Search for a function"
              size="tiny"
              icon={<SearchIcon />}
              value={filterString}
              className="w-full lg:w-52"
              onChange={(e) => setFilterString(e.target.value)}
              onKeyDown={onSearchInputEscape(filterString, setFilterString)}
            />
            <ReportsSelectFilter
              label="Return Type"
              options={uniqueReturnTypes.map((type) => ({
                label: type,
                value: type,
              }))}
              value={returnTypeFilter ?? []}
              onChange={setReturnTypeFilter}
              showSearch
            />
            <ReportsSelectFilter
              label="Security"
              options={securityOptions}
              value={securityFilter ?? []}
              onChange={setSecurityFilter}
            />
          </div>

          <div className="flex items-center gap-x-2">
            {!isSchemaLocked && (
              <>
                <CreateFunctionButton createFunction={createFunction} />
                <ButtonTooltip
                  disabled={!canCreateFunctions}
                  className="px-1 pointer-events-auto"
                  icon={<AiIconAnimation size={16} />}
                  onClick={() => {
                    openSidebar(SIDEBAR_KEYS.AI_ASSISTANT)
                    aiSnap.newChat({
                      name: 'Create new function',
                      initialInput: `Create a new function for the schema ${selectedSchema} that does ...`,
                    })
                  }}
                  tooltip={{
                    content: {
                      side: 'bottom',
                      text: !canCreateFunctions
                        ? 'You need additional permissions to create functions'
                        : 'Create with Supabase Assistant',
                    },
                  }}
                />
              </>
            )}
          </div>
        </div>

        {isSchemaLocked && <ProtectedSchemaWarning schema={selectedSchema} entity="functions" />}

        {functions.length === 0 ? (
          <>
            <EmptyStatePresentational
              icon={DatabaseIcon}
              title="Add your first function"
              description="PostgreSQL functions are a set of SQL and procedural commands such as declarations, assignments, loops, or flow-of-control."
            >
              <CreateFunctionButton hideIcon variant="default" createFunction={createFunction} />
            </EmptyStatePresentational>
          </>
        ) : (
          <Card>
            <Table className="table-fixed overflow-x-auto">
              <TableHeader>
                <TableRow>
                  <TableHead key="name">Name</TableHead>
                  <TableHead key="type">Type</TableHead>
                  <TableHead key="arguments" className="table-cell">
                    Arguments
                  </TableHead>
                  <TableHead key="return_type" className="table-cell">
                    Return type
                  </TableHead>
                  <TableHead key="security" className="table-cell w-[100px]">
                    Security
                  </TableHead>
                  <TableHead key="buttons" className="w-1/6"></TableHead>
                </TableRow>
              </TableHeader>
              <TableBody>
                <FunctionList
                  filterString={filterString}
                  isLocked={isSchemaLocked}
                  returnTypeFilter={returnTypeFilter ?? []}
                  securityFilter={securityFilter ?? []}
                  duplicateFunction={duplicateFunction}
                  editFunction={editFunction}
                  deleteFunction={(fn) => setSelectedFunctionToDelete(fn.id.toString())}
                />
              </TableBody>
            </Table>
          </Card>
        )}
      </div>

      <CreateFunction
        func={functionToEdit || functionToDuplicate}
        visible={showCreateFunctionForm || !!functionToEdit || !!functionToDuplicate}
        onClose={() => {
          setShowCreateFunctionForm(false)
          setSelectedFunctionToEdit(null)
          setSelectedFunctionIdToDuplicate(null)
        }}
        isDuplicating={!!functionToDuplicate}
      />

      <TextConfirmModal
        variant="destructive"
        visible={!!functionToDelete}
        onCancel={() => setSelectedFunctionToDelete(null)}
        onConfirm={onDeleteFunction}
        title="Delete this function"
        loading={isDeletingFunction}
        confirmLabel={`Delete function ${functionToDelete?.name}`}
        confirmPlaceholder="Type in name of function"
        confirmString={functionToDelete?.name ?? 'Unknown'}
        text={
          <>
            <span>This will delete the function</span>{' '}
            <span className="text-bold text-foreground">{functionToDelete?.name}</span>{' '}
            <span>from the schema</span>{' '}
            <span className="text-bold text-foreground">{functionToDelete?.schema}</span>
          </>
        }
        alert={{ title: 'You cannot recover this function once deleted.' }}
      />
    </>
  )
}

const CreateFunctionButton = ({
  variant = 'primary',
  hideIcon = false,
  createFunction,
}: {
  variant?: 'default' | 'primary'
  hideIcon?: boolean
  createFunction: () => void
}) => {
  const { selectedSchema } = useQuerySchemaState()
  const { can: canCreateFunctions } = useAsyncCheckPermissionsV2(
    FGA_PERMISSIONS.PROJECT.DATABASE_WRITE
  )
  const { isSchemaLocked } = useIsProtectedSchema({ schema: selectedSchema })
  const canAddFunctions = canCreateFunctions && !isSchemaLocked

  if (canAddFunctions) {
    return (
      <Shortcut
        id={SHORTCUT_IDS.LIST_PAGE_NEW_ITEM}
        label="Create new function"
        onTrigger={() => createFunction()}
        side="bottom"
      >
        <Button
          variant={variant}
          className="grow"
          onClick={() => createFunction()}
          icon={hideIcon ? null : <PlusIcon />}
        >
          New function
        </Button>
      </Shortcut>
    )
  } else {
    return (
      <ButtonTooltip
        disabled
        variant={variant}
        className="grow"
        icon={hideIcon ? null : <PlusIcon />}
        tooltip={{
          content: {
            side: 'bottom',
            text: 'You need additional permissions to create functions',
          },
        }}
      >
        New function
      </ButtonTooltip>
    )
  }
}