Skip to content

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

14.6 KB
Raw Blame
import fs from 'fs/promises'
import path from 'path'
import { compact, sortBy } from 'lodash'
import { v4 as uuidv4 } from 'uuid'
import { z } from 'zod'

import { generateDeterministicUuid } from './snippets.browser'
import { SNIPPETS_DIR } from './snippets.constants'

type DeepPartial<T> = T extends object
  ? {
      [P in keyof T]?: DeepPartial<T[P]>
    }
  : T

export const SnippetSchema = z.object({
  id: z.string().uuid(),
  inserted_at: z.string().default(() => new Date().toISOString()),
  updated_at: z.string().default(() => new Date().toISOString()),
  type: z.literal('sql'),
  name: z.string(),
  description: z.string().optional(),
  favorite: z.boolean().default(false),
  content: z.object({
    sql: z.string(),
    content_id: z.string(),
    schema_version: z.literal('1.0'),
  }),
  visibility: z.union([
    z.literal('user'),
    z.literal('project'),
    z.literal('org'),
    z.literal('public'),
  ]),
  project_id: z.number().default(1),
  folder_id: z.string().nullable().default(null),
  owner_id: z.number().default(1),
  owner: z
    .object({
      id: z.number(),
      username: z.string(),
    })
    .default({ id: 1, username: 'johndoe' }),
  updated_by: z
    .object({
      id: z.number(),
      username: z.string(),
    })
    .default({ id: 1, username: 'johndoe' }),
})

export const FolderSchema = z.object({
  id: z.string(),
  name: z.string(),
  owner_id: z.number().default(1),
  parent_id: z.string().nullable(),
  project_id: z.number().default(1),
})

export type Snippet = z.infer<typeof SnippetSchema>
export type Folder = z.infer<typeof FolderSchema>

export type FilesystemEntry = {
  id: string
  name: string
  type: 'file' | 'folder'
  folderId: string | null
  content?: string // Only for files
  createdAt: Date
}

/**
 * Snippet without its SQL body — the shape returned for listings, matching the Management API
 * contract (`GetUserContentFolderResponse.data.contents[]` has no `content`). The body is loaded
 * on demand via the item endpoint (`getSnippet`).
 */
export type SnippetMetadata = Omit<Snippet, 'content'>

const buildSnippetMetadata = (
  filename: string,
  folderId: string | null,
  createdAt: Date
): SnippetMetadata => ({
  id: generateDeterministicUuid([folderId, `${filename}.sql`]),
  inserted_at: createdAt.toISOString(),
  updated_at: createdAt.toISOString(),
  type: 'sql',
  name: filename.replace('.sql', ''),
  description: '',
  favorite: false,
  visibility: 'user',
  project_id: 1,
  folder_id: folderId,
  owner_id: 1,
  owner: { id: 1, username: 'johndoe' },
  updated_by: { id: 1, username: 'johndoe' },
})

const buildSnippet = (
  filename: string,
  content: string,
  folderId: string | null,
  createdAt: Date
): Snippet => ({
  ...buildSnippetMetadata(filename, folderId, createdAt),
  content: {
    sql: content,
    content_id: uuidv4(),
    schema_version: '1.0',
  },
})

const buildFolder = (name: string) => {
  const folder: Folder = {
    id: generateDeterministicUuid([name]),
    name: name,
    owner_id: 1,
    parent_id: null,
    project_id: 1,
  }

  return folder
}

const sanitizeName = (name: string): string => {
  // Remove path traversal sequences and normalize
  const sanitized = path.basename(name)
  if (sanitized !== name || name.includes('\0')) {
    throw new Error('Invalid name: path traversal or null bytes detected')
  }
  return sanitized
}

/**
 * Gets a snapshot of the filesystem structure including files and folders.
 * Pass `includeContent: false` for listings (sidebar/folder tree), which only need metadata —
 * this skips reading every `.sql` file off disk.
 * @returns An array of files and folders with their metadata
 */
export async function getFilesystemEntries({
  includeContent = true,
}: { includeContent?: boolean } = {}): Promise<FilesystemEntry[]> {
  if (SNIPPETS_DIR === '') {
    throw new Error(
      'SNIPPETS_MANAGEMENT_FOLDER env var is not set. Please set it to use snippets properly.'
    )
  }

  // Ensure the snippets directory exists
  try {
    await fs.access(SNIPPETS_DIR)
  } catch {
    await fs.mkdir(SNIPPETS_DIR, { recursive: true })
  }

  const entries: FilesystemEntry[] = []

  const readEntriesRecursively = async (
    dirPath: string,
    folderName: string | null
  ): Promise<void> => {
    const items = await fs.readdir(dirPath, { withFileTypes: true })
    const folderId = folderName ? generateDeterministicUuid([folderName]) : null

    for (const item of items) {
      const itemPath = path.join(dirPath, item.name)

      if (item.isDirectory()) {
        // if the folder entry is under another folder, skip it. Subdirectories are not supported.
        if (folderName) {
          continue
        }

        const stats = await fs.stat(itemPath)

        // Add folder entry
        entries.push({
          id: generateDeterministicUuid([folderId, item.name]),
          name: item.name,
          type: 'folder',
          // Folders are always at root level in this implementation
          folderId: null,
          createdAt: stats.birthtime,
        })

        await readEntriesRecursively(itemPath, item.name)
      } else if (item.isFile() && item.name.endsWith('.sql')) {
        const [content, stats] = await Promise.all([
          includeContent ? fs.readFile(itemPath, 'utf-8') : Promise.resolve(undefined),
          fs.stat(itemPath),
        ])
        const snippetName = item.name.replace('.sql', '')

        entries.push({
          id: generateDeterministicUuid([folderId, `${snippetName}.sql`]),
          name: snippetName,
          type: 'file',
          folderId: folderId,
          content,
          createdAt: stats.birthtime,
        })
      }
    }
  }

  await readEntriesRecursively(SNIPPETS_DIR, null)
  return entries
}

export const getSnippet = async (snippetId: string) => {
  const entries = await getFilesystemEntries()
  const foundSnippet = entries.find((e) => e.type === 'file' && e.id === snippetId)

  if (!foundSnippet) {
    throw new Error(`Snippet with id ${snippetId} not found`)
  }

  return buildSnippet(
    foundSnippet.name,
    foundSnippet.content || '',
    foundSnippet.folderId,
    foundSnippet.createdAt
  )
}

interface GetSnippetsParams {
  searchTerm?: string
  limit?: number
  cursor?: string
  sortOrder?: 'asc' | 'desc'
  sort?: 'name' | 'inserted_at'
  folderId?: string | null
  /**
   * Listings (sidebar/folder tree) only need metadata; the SQL body is loaded on demand via the
   * item endpoint. Pass `false` to omit `content` and skip reading files off disk. Defaults to
   * `true` for the flat `/content` endpoint, which the contract returns with content.
   */
  includeContent?: boolean
}

/**
 * Gets a filtered paginated list of snippets based on the provided criteria
 */
export function getSnippets(
  params: GetSnippetsParams & { includeContent: false }
): Promise<{ cursor: string | undefined; snippets: SnippetMetadata[] }>
export function getSnippets(
  params?: GetSnippetsParams & { includeContent?: true }
): Promise<{ cursor: string | undefined; snippets: Snippet[] }>
export async function getSnippets({
  searchTerm,
  limit,
  cursor,
  sort,
  sortOrder,
  folderId,
  includeContent = true,
}: GetSnippetsParams = {}): Promise<{
  cursor: string | undefined
  snippets: Snippet[] | SnippetMetadata[]
}> {
  // Normalize and set default values
  const normalizedSearchTerm = searchTerm?.trim() ?? ''
  const normalizedLimit = limit ?? 100
  const normalizedSort = sort ?? 'inserted_at'
  const normalizedSortOrder = sortOrder ?? 'desc'
  const normalizedCursor = cursor ?? undefined
  const normalizedFolderId = folderId ?? null

  // Validate inputs
  if (normalizedLimit <= 0) {
    throw new Error('Limit must be a positive number')
  }
  if (normalizedLimit > 1000) {
    throw new Error('Limit cannot exceed 1000')
  }

  const entries = await getFilesystemEntries({ includeContent })
  const files = entries.filter(
    (entry): entry is FilesystemEntry & { type: 'file' } => entry.type === 'file'
  )

  // Filter snippets based on search term or folder
  let filteredSnippets = files
  if (normalizedSearchTerm) {
    // When searching, look across all folders and support case-insensitive search
    filteredSnippets = files.filter((file) =>
      file.name.toLowerCase().includes(normalizedSearchTerm.toLowerCase())
    )
  } else {
    // Filter by specific folder or root (null)
    filteredSnippets = files.filter((file) => file.folderId === normalizedFolderId)
  }

  // Sort snippets
  const sortedSnippets = sortBy(filteredSnippets, (snippet) => {
    if (normalizedSort === 'inserted_at') {
      return snippet.createdAt.getTime()
    }
    return snippet.name.toLowerCase() // Case-insensitive name sorting
  })

  if (normalizedSortOrder === 'desc') {
    sortedSnippets.reverse()
  }

  // Apply cursor-based pagination
  let paginatedSnippets = sortedSnippets
  if (normalizedCursor) {
    const cursorIndex = sortedSnippets.findIndex((s) => s.id === normalizedCursor)
    if (cursorIndex !== -1) {
      paginatedSnippets = sortedSnippets.slice(cursorIndex + 1)
    }
    // If cursor not found, return all snippets (graceful degradation)
  }

  // Apply limit and determine next cursor
  let nextCursor: string | undefined = undefined
  let finalSnippets = paginatedSnippets

  if (normalizedLimit && paginatedSnippets.length > normalizedLimit) {
    finalSnippets = paginatedSnippets.slice(0, normalizedLimit)
    nextCursor = finalSnippets[finalSnippets.length - 1].id
  }

  return {
    cursor: nextCursor,
    snippets: finalSnippets.map((file) =>
      includeContent
        ? buildSnippet(file.name, file.content ?? '', file.folderId, file.createdAt)
        : buildSnippetMetadata(file.name, file.folderId, file.createdAt)
    ),
  }
}

/**
 * Saves a snippet to the filesystem
 */
export async function saveSnippet(snippet: Snippet): Promise<Snippet> {
  const entries = await getFilesystemEntries()
  const existingSnippet = entries.find((entry) => entry.id === snippet.id && entry.type === 'file')

  if (existingSnippet) {
    throw new Error(`Snippet with id ${snippet.id} already exists`)
  }

  // check if the folder exists
  if (snippet.folder_id !== null) {
    const existingFolder = entries.find(
      (entry) => entry.id === snippet.folder_id && entry.type === 'folder'
    )
    if (existingFolder === undefined) {
      throw new Error(`Folder with id ${snippet.folder_id} not found`)
    }
  }

  const snippetName = sanitizeName(snippet.name)
  const content = snippet.content.sql || ''
  const folderId = snippet.folder_id || null
  const folder = entries.find((f) => f.id === folderId && f.type === 'folder')

  const folderPath = folder ? path.join(SNIPPETS_DIR, folder.name) : SNIPPETS_DIR
  const filePath = path.join(folderPath, `${snippetName}.sql`)
  await fs.writeFile(filePath, content || '', 'utf-8')
  const stats = await fs.stat(filePath)

  const result = buildSnippet(snippetName, content, snippet.folder_id, stats.birthtime)
  return result
}

/**
 * Deletes a snippet from the filesystem
 */
export async function deleteSnippet(id: string): Promise<void> {
  const entries = await getFilesystemEntries()
  const found = entries.find((entry) => entry.id === id && entry.type === 'file')

  if (!found) {
    throw new Error(`Snippet with id ${id} not found`)
  }

  const filename = `${found.name}.sql`
  const currentFolder = entries.find((f) => f.id === found.folderId && f.type === 'folder')
  const paths = compact([SNIPPETS_DIR, currentFolder?.name, filename])
  const filePath = path.join(...paths)

  try {
    await fs.unlink(filePath)
  } catch (error) {
    if ((error as NodeJS.ErrnoException).code !== 'ENOENT') {
      throw error
    }
  }
}

/**
 * Updates a snippet in the filesystem. It also handles renaming and moving.
 */
export async function updateSnippet(id: string, updates: DeepPartial<Snippet>): Promise<Snippet> {
  const entries = await getFilesystemEntries()
  const foundSnippet = entries
    .filter(
      (entry): entry is FilesystemEntry & { type: 'file'; content: string } => entry.type === 'file'
    )
    .find((s) => s.id === id)

  if (!foundSnippet) {
    throw new Error(`Snippet with id ${id} not found`)
  }

  const newId = generateDeterministicUuid([
    updates.folder_id !== undefined ? updates.folder_id : foundSnippet.folderId,
    `${updates.name ?? foundSnippet.name}.sql`,
  ])

  const snippetAtTargetLocation = entries.find(
    (entry) => entry.id === newId && entry.type === 'file'
  )

  if (snippetAtTargetLocation && snippetAtTargetLocation.id !== foundSnippet.id) {
    throw new Error(
      `Snippet named "${updates.name ?? foundSnippet.name}" already exists in the specified folder`
    )
  }

  const snippet = buildSnippet(
    foundSnippet.name,
    foundSnippet.content || '',
    foundSnippet.folderId,
    foundSnippet.createdAt
  )

  // it's easier to delete the old file first and then recreate a new one
  await deleteSnippet(snippet.id)

  const updatedSnippet = await saveSnippet({
    name: updates.name ?? snippet.name,
    content: updates.content ?? snippet.content,
    // folder_id can be null
    folder_id: updates.folder_id !== undefined ? updates.folder_id : snippet.folder_id,
  } as Snippet)

  return updatedSnippet
}

export const getFolders = async (folderId: string | null = null): Promise<Folder[]> => {
  const entries = await getFilesystemEntries({ includeContent: false })
  const folders = entries
    .filter(
      (entry): entry is FilesystemEntry & { type: 'folder' } =>
        entry.type === 'folder' && entry.folderId === folderId
    )
    .map((folder) => buildFolder(folder.name))
  return folders
}

/**
 * Creates a new folder as an actual directory
 */
export async function createFolder(_folderName: string): Promise<Folder> {
  const folderName = sanitizeName(_folderName)

  const entries = await getFilesystemEntries()
  const existingFolder = entries.find((folder) => folder.name === folderName)

  if (existingFolder) {
    throw new Error(`Folder with name ${folderName} already exists`)
  }

  const folderPath = path.join(SNIPPETS_DIR, folderName)

  await fs.mkdir(folderPath, { recursive: true })
  const newFolder = buildFolder(folderName)

  return newFolder
}

/**
 * Deletes a folder directory from the filesystem
 * @throws {Error} If the folder doesn't exist
 */
export async function deleteFolder(id: string): Promise<void> {
  const entries = await getFilesystemEntries()
  const folder = entries.find((f) => f.id === id && f.type === 'folder')

  if (!folder) {
    throw new Error(`Folder with id ${id} not found`)
  }

  const folderPath = path.join(SNIPPETS_DIR, folder.name)
  try {
    await fs.rm(folderPath, { recursive: true, force: true })
  } catch (error) {
    if ((error as NodeJS.ErrnoException).code !== 'ENOENT') {
      throw error
    }
    // If folder doesn't exist, still throw the original error
    throw new Error(`Folder with id ${id} not found`)
  }
}