konnosDashboard
snippets.utils.ts466 lines · main
1import fs from 'fs/promises'
2import path from 'path'
3import { compact, sortBy } from 'lodash'
4import { v4 as uuidv4 } from 'uuid'
5import { z } from 'zod'
6
7import { generateDeterministicUuid } from './snippets.browser'
8import { SNIPPETS_DIR } from './snippets.constants'
9
10type DeepPartial<T> = T extends object
11 ? {
12 [P in keyof T]?: DeepPartial<T[P]>
13 }
14 : T
15
16export const SnippetSchema = z.object({
17 id: z.string().uuid(),
18 inserted_at: z.string().default(() => new Date().toISOString()),
19 updated_at: z.string().default(() => new Date().toISOString()),
20 type: z.literal('sql'),
21 name: z.string(),
22 description: z.string().optional(),
23 favorite: z.boolean().default(false),
24 content: z.object({
25 sql: z.string(),
26 content_id: z.string(),
27 schema_version: z.literal('1.0'),
28 }),
29 visibility: z.union([
30 z.literal('user'),
31 z.literal('project'),
32 z.literal('org'),
33 z.literal('public'),
34 ]),
35 project_id: z.number().default(1),
36 folder_id: z.string().nullable().default(null),
37 owner_id: z.number().default(1),
38 owner: z
39 .object({
40 id: z.number(),
41 username: z.string(),
42 })
43 .default({ id: 1, username: 'johndoe' }),
44 updated_by: z
45 .object({
46 id: z.number(),
47 username: z.string(),
48 })
49 .default({ id: 1, username: 'johndoe' }),
50})
51
52export const FolderSchema = z.object({
53 id: z.string(),
54 name: z.string(),
55 owner_id: z.number().default(1),
56 parent_id: z.string().nullable(),
57 project_id: z.number().default(1),
58})
59
60export type Snippet = z.infer<typeof SnippetSchema>
61export type Folder = z.infer<typeof FolderSchema>
62
63export type FilesystemEntry = {
64 id: string
65 name: string
66 type: 'file' | 'folder'
67 folderId: string | null
68 content?: string // Only for files
69 createdAt: Date
70}
71
72const buildSnippet = (
73 filename: string,
74 content: string,
75 folderId: string | null,
76 createdAt: Date
77) => {
78 const snippet: Snippet = {
79 id: generateDeterministicUuid([folderId, `${filename}.sql`]),
80 inserted_at: createdAt.toISOString(),
81 updated_at: createdAt.toISOString(),
82 type: 'sql',
83 name: filename.replace('.sql', ''),
84 description: '',
85 favorite: false,
86 content: {
87 sql: content,
88 content_id: uuidv4(),
89 schema_version: '1.0',
90 },
91 visibility: 'user',
92 project_id: 1,
93 folder_id: folderId,
94 owner_id: 1,
95 owner: { id: 1, username: 'johndoe' },
96 updated_by: { id: 1, username: 'johndoe' },
97 }
98
99 return snippet
100}
101
102const buildFolder = (name: string) => {
103 const folder: Folder = {
104 id: generateDeterministicUuid([name]),
105 name: name,
106 owner_id: 1,
107 parent_id: null,
108 project_id: 1,
109 }
110
111 return folder
112}
113
114const sanitizeName = (name: string): string => {
115 // Remove path traversal sequences and normalize
116 const sanitized = path.basename(name)
117 if (sanitized !== name || name.includes('\0')) {
118 throw new Error('Invalid name: path traversal or null bytes detected')
119 }
120 return sanitized
121}
122
123/**
124 * Gets a complete snapshot of the filesystem structure including files and folders
125 * @returns An array of files and folders with their metadata
126 */
127export async function getFilesystemEntries(): Promise<FilesystemEntry[]> {
128 if (SNIPPETS_DIR === '') {
129 throw new Error(
130 'SNIPPETS_MANAGEMENT_FOLDER env var is not set. Please set it to use snippets properly.'
131 )
132 }
133
134 // Ensure the snippets directory exists
135 try {
136 await fs.access(SNIPPETS_DIR)
137 } catch {
138 await fs.mkdir(SNIPPETS_DIR, { recursive: true })
139 }
140
141 const entries: FilesystemEntry[] = []
142
143 const readEntriesRecursively = async (
144 dirPath: string,
145 folderName: string | null
146 ): Promise<void> => {
147 const items = await fs.readdir(dirPath, { withFileTypes: true })
148 const folderId = folderName ? generateDeterministicUuid([folderName]) : null
149
150 for (const item of items) {
151 const itemPath = path.join(dirPath, item.name)
152
153 if (item.isDirectory()) {
154 // if the folder entry is under another folder, skip it. Subdirectories are not supported.
155 if (folderName) {
156 continue
157 }
158
159 const stats = await fs.stat(itemPath)
160
161 // Add folder entry
162 entries.push({
163 id: generateDeterministicUuid([folderId, item.name]),
164 name: item.name,
165 type: 'folder',
166 // Folders are always at root level in this implementation
167 folderId: null,
168 createdAt: stats.birthtime,
169 })
170
171 await readEntriesRecursively(itemPath, item.name)
172 } else if (item.isFile() && item.name.endsWith('.sql')) {
173 const [content, stats] = await Promise.all([
174 fs.readFile(itemPath, 'utf-8'),
175 fs.stat(itemPath),
176 ])
177 const snippetName = item.name.replace('.sql', '')
178
179 entries.push({
180 id: generateDeterministicUuid([folderId, `${snippetName}.sql`]),
181 name: snippetName,
182 type: 'file',
183 folderId: folderId,
184 content: content,
185 createdAt: stats.birthtime,
186 })
187 }
188 }
189 }
190
191 await readEntriesRecursively(SNIPPETS_DIR, null)
192 return entries
193}
194
195export const getSnippet = async (snippetId: string) => {
196 const entries = await getFilesystemEntries()
197 const foundSnippet = entries.find((e) => e.type === 'file' && e.id === snippetId)
198
199 if (!foundSnippet) {
200 throw new Error(`Snippet with id ${snippetId} not found`)
201 }
202
203 return buildSnippet(
204 foundSnippet.name,
205 foundSnippet.content || '',
206 foundSnippet.folderId,
207 foundSnippet.createdAt
208 )
209}
210
211/**
212 * Gets a filtered paginated list of snippets based on the provided criteria
213 */
214export const getSnippets = async ({
215 searchTerm,
216 limit,
217 cursor,
218 sort,
219 sortOrder,
220 folderId,
221}: {
222 searchTerm?: string
223 limit?: number
224 cursor?: string
225 sortOrder?: 'asc' | 'desc'
226 sort?: 'name' | 'inserted_at'
227 folderId?: string | null
228}): Promise<{ cursor: string | undefined; snippets: Snippet[] }> => {
229 // Normalize and set default values
230 const normalizedSearchTerm = searchTerm?.trim() ?? ''
231 const normalizedLimit = limit ?? 100
232 const normalizedSort = sort ?? 'inserted_at'
233 const normalizedSortOrder = sortOrder ?? 'desc'
234 const normalizedCursor = cursor ?? undefined
235 const normalizedFolderId = folderId ?? null
236
237 // Validate inputs
238 if (normalizedLimit <= 0) {
239 throw new Error('Limit must be a positive number')
240 }
241 if (normalizedLimit > 1000) {
242 throw new Error('Limit cannot exceed 1000')
243 }
244
245 const entries = await getFilesystemEntries()
246 const files = entries.filter(
247 (entry): entry is FilesystemEntry & { type: 'file'; content: string } =>
248 entry.type === 'file' && entry.content !== undefined && entry.content !== null
249 )
250
251 // Filter snippets based on search term or folder
252 let filteredSnippets = files
253 if (normalizedSearchTerm) {
254 // When searching, look across all folders and support case-insensitive search
255 filteredSnippets = files.filter((file) =>
256 file.name.toLowerCase().includes(normalizedSearchTerm.toLowerCase())
257 )
258 } else {
259 // Filter by specific folder or root (null)
260 filteredSnippets = files.filter((file) => file.folderId === normalizedFolderId)
261 }
262
263 // Sort snippets
264 const sortedSnippets = sortBy(filteredSnippets, (snippet) => {
265 if (normalizedSort === 'inserted_at') {
266 return snippet.createdAt.getTime()
267 }
268 return snippet.name.toLowerCase() // Case-insensitive name sorting
269 })
270
271 if (normalizedSortOrder === 'desc') {
272 sortedSnippets.reverse()
273 }
274
275 // Apply cursor-based pagination
276 let paginatedSnippets = sortedSnippets
277 if (normalizedCursor) {
278 const cursorIndex = sortedSnippets.findIndex((s) => s.id === normalizedCursor)
279 if (cursorIndex !== -1) {
280 paginatedSnippets = sortedSnippets.slice(cursorIndex + 1)
281 }
282 // If cursor not found, return all snippets (graceful degradation)
283 }
284
285 // Apply limit and determine next cursor
286 let nextCursor: string | undefined = undefined
287 let finalSnippets = paginatedSnippets
288
289 if (normalizedLimit && paginatedSnippets.length > normalizedLimit) {
290 finalSnippets = paginatedSnippets.slice(0, normalizedLimit)
291 nextCursor = finalSnippets[finalSnippets.length - 1].id
292 }
293
294 return {
295 cursor: nextCursor,
296 snippets: finalSnippets.map((file) =>
297 buildSnippet(file.name, file.content, file.folderId, file.createdAt)
298 ),
299 }
300}
301
302/**
303 * Saves a snippet to the filesystem
304 */
305export async function saveSnippet(snippet: Snippet): Promise<Snippet> {
306 const entries = await getFilesystemEntries()
307 const existingSnippet = entries.find((entry) => entry.id === snippet.id && entry.type === 'file')
308
309 if (existingSnippet) {
310 throw new Error(`Snippet with id ${snippet.id} already exists`)
311 }
312
313 // check if the folder exists
314 if (snippet.folder_id !== null) {
315 const existingFolder = entries.find(
316 (entry) => entry.id === snippet.folder_id && entry.type === 'folder'
317 )
318 if (existingFolder === undefined) {
319 throw new Error(`Folder with id ${snippet.folder_id} not found`)
320 }
321 }
322
323 const snippetName = sanitizeName(snippet.name)
324 const content = snippet.content.sql || ''
325 const folderId = snippet.folder_id || null
326 const folder = entries.find((f) => f.id === folderId && f.type === 'folder')
327
328 const folderPath = folder ? path.join(SNIPPETS_DIR, folder.name) : SNIPPETS_DIR
329 const filePath = path.join(folderPath, `${snippetName}.sql`)
330 await fs.writeFile(filePath, content || '', 'utf-8')
331 const stats = await fs.stat(filePath)
332
333 const result = buildSnippet(snippetName, content, snippet.folder_id, stats.birthtime)
334 return result
335}
336
337/**
338 * Deletes a snippet from the filesystem
339 */
340export async function deleteSnippet(id: string): Promise<void> {
341 const entries = await getFilesystemEntries()
342 const found = entries.find((entry) => entry.id === id && entry.type === 'file')
343
344 if (!found) {
345 throw new Error(`Snippet with id ${id} not found`)
346 }
347
348 const filename = `${found.name}.sql`
349 const currentFolder = entries.find((f) => f.id === found.folderId && f.type === 'folder')
350 const paths = compact([SNIPPETS_DIR, currentFolder?.name, filename])
351 const filePath = path.join(...paths)
352
353 try {
354 await fs.unlink(filePath)
355 } catch (error) {
356 if ((error as NodeJS.ErrnoException).code !== 'ENOENT') {
357 throw error
358 }
359 }
360}
361
362/**
363 * Updates a snippet in the filesystem. It also handles renaming and moving.
364 */
365export async function updateSnippet(id: string, updates: DeepPartial<Snippet>): Promise<Snippet> {
366 const entries = await getFilesystemEntries()
367 const foundSnippet = entries
368 .filter(
369 (entry): entry is FilesystemEntry & { type: 'file'; content: string } => entry.type === 'file'
370 )
371 .find((s) => s.id === id)
372
373 if (!foundSnippet) {
374 throw new Error(`Snippet with id ${id} not found`)
375 }
376
377 const newId = generateDeterministicUuid([
378 updates.folder_id !== undefined ? updates.folder_id : foundSnippet.folderId,
379 `${updates.name ?? foundSnippet.name}.sql`,
380 ])
381
382 const snippetAtTargetLocation = entries.find(
383 (entry) => entry.id === newId && entry.type === 'file'
384 )
385
386 if (snippetAtTargetLocation && snippetAtTargetLocation.id !== foundSnippet.id) {
387 throw new Error(
388 `Snippet named "${updates.name ?? foundSnippet.name}" already exists in the specified folder`
389 )
390 }
391
392 const snippet = buildSnippet(
393 foundSnippet.name,
394 foundSnippet.content || '',
395 foundSnippet.folderId,
396 foundSnippet.createdAt
397 )
398
399 // it's easier to delete the old file first and then recreate a new one
400 await deleteSnippet(snippet.id)
401
402 const updatedSnippet = await saveSnippet({
403 name: updates.name ?? snippet.name,
404 content: updates.content ?? snippet.content,
405 // folder_id can be null
406 folder_id: updates.folder_id !== undefined ? updates.folder_id : snippet.folder_id,
407 } as Snippet)
408
409 return updatedSnippet
410}
411
412export const getFolders = async (folderId: string | null = null): Promise<Folder[]> => {
413 const entries = await getFilesystemEntries()
414 const folders = entries
415 .filter(
416 (entry): entry is FilesystemEntry & { type: 'folder' } =>
417 entry.type === 'folder' && entry.folderId === folderId
418 )
419 .map((folder) => buildFolder(folder.name))
420 return folders
421}
422
423/**
424 * Creates a new folder as an actual directory
425 */
426export async function createFolder(_folderName: string): Promise<Folder> {
427 const folderName = sanitizeName(_folderName)
428
429 const entries = await getFilesystemEntries()
430 const existingFolder = entries.find((folder) => folder.name === folderName)
431
432 if (existingFolder) {
433 throw new Error(`Folder with name ${folderName} already exists`)
434 }
435
436 const folderPath = path.join(SNIPPETS_DIR, folderName)
437
438 await fs.mkdir(folderPath, { recursive: true })
439 const newFolder = buildFolder(folderName)
440
441 return newFolder
442}
443
444/**
445 * Deletes a folder directory from the filesystem
446 * @throws {Error} If the folder doesn't exist
447 */
448export async function deleteFolder(id: string): Promise<void> {
449 const entries = await getFilesystemEntries()
450 const folder = entries.find((f) => f.id === id && f.type === 'folder')
451
452 if (!folder) {
453 throw new Error(`Folder with id ${id} not found`)
454 }
455
456 const folderPath = path.join(SNIPPETS_DIR, folder.name)
457 try {
458 await fs.rm(folderPath, { recursive: true, force: true })
459 } catch (error) {
460 if ((error as NodeJS.ErrnoException).code !== 'ENOENT') {
461 throw error
462 }
463 // If folder doesn't exist, still throw the original error
464 throw new Error(`Folder with id ${id} not found`)
465 }
466}