[{"_1":2,"_173":-5,"_174":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","703ea01c1077155a6b2e005f3a31351a5b041073","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_146":147,"_170":171,"_172":-5},"handle","Downchuck","name","con-juan","path","src/database/index.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","142cb1ae-36b9-11f0-bfb2-9e149126039e","createdAt",["D",1747882898379],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","8741e3d0-33a8-11f0-b519-ff88f6bc2fe6","pinnedFileId","pinPreview","screenshotVersion","likeCount","b59a870a-87ea-4ce7-9c06-6d74e6a28a6c","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeEZ1MGptcVM3Z0J2Z3BTckZ3NFdscVN6RWkifQ","org",{"_93":94,"_131":128},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",83,"142ce0e8-36b9-11f0-bfb2-9e149126039e","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":160,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"7ed54ea6-36c0-11f0-a6eb-9e149126039e","index.ts","fileId","5c7b334e-36bf-11f0-83f3-9e149126039e","parentId","5ecbda08-fcb7-474f-81e9-fcc67f998e67","branchId","branchVersion",39,"latest",true,["D",1747886083795],1,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport logger from '../utils/logger.ts';\nimport { DatabaseError } from '../utils/error-handler.ts';\n\n// Create a logger for database operations\nconst log = logger.createLogger('Database');\n\n// Table names - use versioning to allow for schema changes\nconst SESSIONS_TABLE = \"abuse_analyzer_sessions_v1\";\nconst MESSAGES_TABLE = \"abuse_analyzer_messages_v1\";\nconst PATTERNS_TABLE = \"abuse_analyzer_patterns_v1\";\nconst EXAMPLES_TABLE = \"abuse_analyzer_examples_v1\";\n\n// Types for our database entities\nexport interface Session {\n id: string;\n title: string;\n created_at: number;\n updated_at: number;\n}\n\nexport interface Message {\n id: number;\n session_id: string;\n content: string;\n sender: \"user\" | \"bot\";\n timestamp: number;\n parent_id?: number | null;\n pattern_id?: number | null;\n}\n\nexport interface Pattern {\n id: number;\n session_id: string;\n message_id: number;\n pattern_type: string;\n explanation: string;\n severity: \"subtle\" | \"moderate\" | \"overt\";\n confidence: number;\n examples?: Example[];\n}\n\nexport interface Example {\n id: number;\n pattern_id: number;\n example_text: string;\n user_provided: boolean;\n timestamp: number;\n}\n\n/**\n * Initialize database tables\n */\nexport async function initDatabase(): Promise {\n try {\n log.info(\"Initializing database tables\");\n \n // Create sessions table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${SESSIONS_TABLE} (\n id TEXT PRIMARY KEY,\n title TEXT NOT NULL,\n created_at INTEGER NOT NULL,\n updated_at INTEGER NOT NULL\n )\n `);\n\n // Create messages table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${MESSAGES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n session_id TEXT NOT NULL,\n content TEXT NOT NULL,\n sender TEXT NOT NULL,\n timestamp INTEGER NOT NULL,\n parent_id INTEGER,\n pattern_id INTEGER,\n FOREIGN KEY (session_id) REFERENCES ${SESSIONS_TABLE}(id),\n FOREIGN KEY (parent_id) REFERENCES ${MESSAGES_TABLE}(id),\n FOREIGN KEY (pattern_id) REFERENCES ${PATTERNS_TABLE}(id)\n )\n `);\n\n // Create patterns table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${PATTERNS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n session_id TEXT NOT NULL,\n message_id INTEGER NOT NULL,\n pattern_type TEXT NOT NULL,\n explanation TEXT NOT NULL,\n severity TEXT NOT NULL,\n confidence REAL NOT NULL,\n FOREIGN KEY (session_id) REFERENCES ${SESSIONS_TABLE}(id),\n FOREIGN KEY (message_id) REFERENCES ${MESSAGES_TABLE}(id)\n )\n `);\n\n // Create examples table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${EXAMPLES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n pattern_id INTEGER NOT NULL,\n example_text TEXT NOT NULL,\n user_provided BOOLEAN NOT NULL,\n timestamp INTEGER NOT NULL,\n FOREIGN KEY (pattern_id) REFERENCES ${PATTERNS_TABLE}(id)\n )\n `);\n \n log.info(\"Database tables initialized successfully\");\n } catch (error) {\n log.error(\"Failed to initialize database tables:\", error);\n throw new DatabaseError(\"Failed to initialize database\", error instanceof Error ? error : undefined);\n }\n}\n\n// Session operations\n/**\n * Create a new session\n */\nexport async function createSession(title: string): Promise {\n try {\n const id = crypto.randomUUID();\n const now = Date.now();\n \n await sqlite.execute(\n `INSERT INTO ${SESSIONS_TABLE} (id, title, created_at, updated_at) VALUES (?, ?, ?, ?)`,\n [id, title, now, now]\n );\n \n log.info(`Created new session: ${id} - ${title}`);\n \n return {\n id,\n title,\n created_at: now,\n updated_at: now\n };\n } catch (error) {\n log.error(`Failed to create session \"${title}\":`, error);\n throw new DatabaseError(\"Failed to create session\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get a session by ID\n */\nexport async function getSession(id: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${SESSIONS_TABLE} WHERE id = ?`,\n [id]\n );\n \n if (result.length === 0) {\n log.info(`Session not found: ${id}`);\n return null;\n }\n \n log.debug(`Retrieved session: ${id}`);\n return result[0] as Session;\n } catch (error) {\n log.error(`Failed to get session ${id}:`, error);\n throw new DatabaseError(\"Failed to get session\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Update a session's timestamp\n */\nexport async function updateSessionTimestamp(id: string): Promise {\n try {\n const now = Date.now();\n \n await sqlite.execute(\n `UPDATE ${SESSIONS_TABLE} SET updated_at = ? WHERE id = ?`,\n [now, id]\n );\n \n log.debug(`Updated timestamp for session: ${id}`);\n } catch (error) {\n log.error(`Failed to update session timestamp ${id}:`, error);\n throw new DatabaseError(\"Failed to update session timestamp\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get all sessions\n */\nexport async function getAllSessions(): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${SESSIONS_TABLE} ORDER BY updated_at DESC`\n );\n \n log.info(`Retrieved ${result.length} sessions`);\n return result as Session[];\n } catch (error) {\n log.error(\"Failed to get all sessions:\", error);\n throw new DatabaseError(\"Failed to get all sessions\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Update a session's title\n */\nexport async function updateSessionTitle(id: string, title: string): Promise {\n try {\n const now = Date.now();\n \n await sqlite.execute(\n `UPDATE ${SESSIONS_TABLE} SET title = ?, updated_at = ? WHERE id = ?`,\n [title, now, id]\n );\n \n log.info(`Updated title for session ${id}: ${title}`);\n } catch (error) {\n log.error(`Failed to update session title ${id}:`, error);\n throw new DatabaseError(\"Failed to update session title\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Delete a session and all related data\n */\nexport async function deleteSession(id: string): Promise {\n try {\n // Get all patterns for this session\n const patterns = await getSessionPatterns(id);\n \n // Delete examples for each pattern\n for (const pattern of patterns) {\n await sqlite.execute(\n `DELETE FROM ${EXAMPLES_TABLE} WHERE pattern_id = ?`,\n [pattern.id]\n );\n }\n \n // Delete patterns\n await sqlite.execute(\n `DELETE FROM ${PATTERNS_TABLE} WHERE session_id = ?`,\n [id]\n );\n \n // Delete messages\n await sqlite.execute(\n `DELETE FROM ${MESSAGES_TABLE} WHERE session_id = ?`,\n [id]\n );\n \n // Delete session\n await sqlite.execute(\n `DELETE FROM ${SESSIONS_TABLE} WHERE id = ?`,\n [id]\n );\n \n log.info(`Deleted session: ${id}`);\n } catch (error) {\n log.error(`Failed to delete session ${id}:`, error);\n throw new DatabaseError(\"Failed to delete session\", error instanceof Error ? error : undefined);\n }\n}\n\n// Message operations\n/**\n * Add a message to a session\n */\nexport async function addMessage(\n session_id: string,\n content: string,\n sender: \"user\" | \"bot\",\n parent_id?: number | null,\n pattern_id?: number | null\n): Promise {\n try {\n const timestamp = Date.now();\n \n const result = await sqlite.execute(\n `INSERT INTO ${MESSAGES_TABLE} (session_id, content, sender, timestamp, parent_id, pattern_id)\n VALUES (?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [session_id, content, sender, timestamp, parent_id || null, pattern_id || null]\n );\n \n // Update session timestamp\n await updateSessionTimestamp(session_id);\n \n const messageId = result[0].id as number;\n log.debug(`Added message ${messageId} to session ${session_id}`);\n \n return messageId;\n } catch (error) {\n log.error(`Failed to add message to session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to add message\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get all messages for a session\n */\nexport async function getSessionMessages(session_id: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${MESSAGES_TABLE} WHERE session_id = ? ORDER BY timestamp ASC`,\n [session_id]\n );\n \n log.debug(`Retrieved ${result.length} messages for session ${session_id}`);\n return result as Message[];\n } catch (error) {\n log.error(`Failed to get messages for session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to get session messages\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get messages in a threaded conversation using recursive CTE\n */\nexport async function getThreadedMessages(session_id: string, root_message_id?: number): Promise {\n try {\n let query: string;\n let params: any[];\n \n if (root_message_id) {\n // Get a specific thread starting from root_message_id\n query = `\n WITH RECURSIVE thread AS (\n SELECT * FROM ${MESSAGES_TABLE} WHERE id = ?\n UNION ALL\n SELECT m.* FROM ${MESSAGES_TABLE} m\n JOIN thread t ON m.parent_id = t.id\n )\n SELECT * FROM thread WHERE session_id = ? ORDER BY timestamp ASC\n `;\n params = [root_message_id, session_id];\n } else {\n // Get all top-level messages and their threads\n query = `\n WITH RECURSIVE thread AS (\n SELECT * FROM ${MESSAGES_TABLE} WHERE session_id = ? AND parent_id IS NULL\n UNION ALL\n SELECT m.* FROM ${MESSAGES_TABLE} m\n JOIN thread t ON m.parent_id = t.id\n )\n SELECT * FROM thread ORDER BY timestamp ASC\n `;\n params = [session_id];\n }\n \n const result = await sqlite.execute(query, params);\n log.debug(`Retrieved ${result.length} threaded messages for session ${session_id}`);\n \n return result as Message[];\n } catch (error) {\n log.error(`Failed to get threaded messages for session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to get threaded messages\", error instanceof Error ? error : undefined);\n }\n}\n\n// Pattern operations\n/**\n * Add a pattern\n */\nexport async function addPattern(\n session_id: string,\n message_id: number,\n pattern_type: string,\n explanation: string,\n severity: \"subtle\" | \"moderate\" | \"overt\",\n confidence: number\n): Promise {\n try {\n const result = await sqlite.execute(\n `INSERT INTO ${PATTERNS_TABLE} (session_id, message_id, pattern_type, explanation, severity, confidence)\n VALUES (?, ?, ?, ?, ?, ?)\n RETURNING id`,\n [session_id, message_id, pattern_type, explanation, severity, confidence]\n );\n \n const patternId = result[0].id as number;\n log.debug(`Added pattern ${patternId} to session ${session_id}: ${pattern_type}`);\n \n return patternId;\n } catch (error) {\n log.error(`Failed to add pattern to session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to add pattern\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get all patterns for a session\n */\nexport async function getSessionPatterns(session_id: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${PATTERNS_TABLE} WHERE session_id = ?`,\n [session_id]\n );\n \n log.debug(`Retrieved ${result.length} patterns for session ${session_id}`);\n return result as Pattern[];\n } catch (error) {\n log.error(`Failed to get patterns for session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to get session patterns\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get a pattern by ID\n */\nexport async function getPattern(pattern_id: number): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${PATTERNS_TABLE} WHERE id = ?`,\n [pattern_id]\n );\n \n if (result.length === 0) {\n log.info(`Pattern not found: ${pattern_id}`);\n return null;\n }\n \n log.debug(`Retrieved pattern: ${pattern_id}`);\n return result[0] as Pattern;\n } catch (error) {\n log.error(`Failed to get pattern ${pattern_id}:`, error);\n throw new DatabaseError(\"Failed to get pattern\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get patterns by type\n */\nexport async function getPatternsByType(session_id: string, pattern_type: string): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${PATTERNS_TABLE} WHERE session_id = ? AND pattern_type LIKE ?`,\n [session_id, `%${pattern_type}%`]\n );\n \n log.debug(`Retrieved ${result.length} patterns of type \"${pattern_type}\" for session ${session_id}`);\n return result as Pattern[];\n } catch (error) {\n log.error(`Failed to get patterns by type for session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to get patterns by type\", error instanceof Error ? error : undefined);\n }\n}\n\n// Example operations\n/**\n * Add an example to a pattern\n */\nexport async function addExample(\n pattern_id: number,\n example_text: string,\n user_provided: boolean\n): Promise {\n try {\n const timestamp = Date.now();\n \n const result = await sqlite.execute(\n `INSERT INTO ${EXAMPLES_TABLE} (pattern_id, example_text, user_provided, timestamp)\n VALUES (?, ?, ?, ?)\n RETURNING id`,\n [pattern_id, example_text, user_provided, timestamp]\n );\n \n // Update the session timestamp for the pattern\n const pattern = await getPattern(pattern_id);\n if (pattern) {\n await updateSessionTimestamp(pattern.session_id);\n }\n \n const exampleId = result[0].id as number;\n log.debug(`Added example ${exampleId} to pattern ${pattern_id}`);\n \n return exampleId;\n } catch (error) {\n log.error(`Failed to add example to pattern ${pattern_id}:`, error);\n throw new DatabaseError(\"Failed to add example\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get examples for a pattern\n */\nexport async function getPatternExamples(pattern_id: number): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM ${EXAMPLES_TABLE} WHERE pattern_id = ? ORDER BY timestamp ASC`,\n [pattern_id]\n );\n \n log.debug(`Retrieved ${result.length} examples for pattern ${pattern_id}`);\n return result as Example[];\n } catch (error) {\n log.error(`Failed to get examples for pattern ${pattern_id}:`, error);\n throw new DatabaseError(\"Failed to get pattern examples\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get a pattern with its examples\n */\nexport async function getPatternWithExamples(pattern_id: number): Promise {\n try {\n const pattern = await getPattern(pattern_id);\n \n if (!pattern) {\n return null;\n }\n \n const examples = await getPatternExamples(pattern_id);\n return {\n ...pattern,\n examples\n };\n } catch (error) {\n log.error(`Failed to get pattern with examples ${pattern_id}:`, error);\n throw new DatabaseError(\"Failed to get pattern with examples\", error instanceof Error ? error : undefined);\n }\n}\n\n/**\n * Get all patterns with their examples for a session\n */\nexport async function getSessionPatternsWithExamples(session_id: string): Promise {\n try {\n const patterns = await getSessionPatterns(session_id);\n \n const patternsWithExamples = await Promise.all(\n patterns.map(async (pattern) => {\n const examples = await getPatternExamples(pattern.id);\n return {\n ...pattern,\n examples\n };\n })\n );\n \n log.debug(`Retrieved ${patterns.length} patterns with examples for session ${session_id}`);\n return patternsWithExamples;\n } catch (error) {\n log.error(`Failed to get patterns with examples for session ${session_id}:`, error);\n throw new DatabaseError(\"Failed to get session patterns with examples\", error instanceof Error ? error : undefined);\n }\n}","deleted","label",[168,169,149],"src","database","permalink","https://www.val.town/x/Downchuck/con-juan/code/src/database/index.ts","extension","actionData","errors"]