[{"_1":2,"_168":-5,"_169":-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","73d011c55f6ff6e543547a2f801bf2081197a5a4","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,"_165":166,"_167":-5},"handle","Downchuck","name","con-juan","path","database.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":98,"_149":150,"_151":-5,"_152":136,"_153":154,"_155":156,"_103":157,"_134":32,"_158":159,"_160":161,"_162":20,"_163":-5,"_97":164},"6e3cc996-36bd-11f0-96a3-9e149126039e","fileId","6e3cca5e-36bd-11f0-96a3-9e149126039e","parentId","branchId","branchVersion",10,"latest",true,["D",1747884767462],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\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// Initialize database tables\nexport async function initDatabase() {\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\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;\n pattern_id?: number;\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// Session operations\nexport async function createSession(title: string): Promise {\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 return {\n id,\n title,\n created_at: now,\n updated_at: now\n };\n}\n\nexport async function getSession(id: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${SESSIONS_TABLE} WHERE id = ?`,\n [id]\n );\n \n if (result.length === 0) {\n return null;\n }\n \n return result[0] as Session;\n}\n\nexport async function updateSessionTimestamp(id: string): Promise {\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\nexport async function getAllSessions(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${SESSIONS_TABLE} ORDER BY updated_at DESC`\n );\n \n return result as Session[];\n}\n\n// Message operations\nexport async function addMessage(\n session_id: string,\n content: string,\n sender: \"user\" | \"bot\",\n parent_id?: number,\n pattern_id?: number\n): Promise {\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 return result[0].id;\n}\n\nexport async function getSessionMessages(session_id: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${MESSAGES_TABLE} WHERE session_id = ? ORDER BY timestamp ASC`,\n [session_id]\n );\n \n return result as Message[];\n}\n\n// Get messages in a threaded conversation using recursive CTE\nexport async function getThreadedMessages(session_id: string, root_message_id?: number): Promise {\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 return result as Message[];\n}\n\n// Pattern operations\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 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 return result[0].id;\n}\n\nexport async function getSessionPatterns(session_id: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PATTERNS_TABLE} WHERE session_id = ?`,\n [session_id]\n );\n \n return result as Pattern[];\n}\n\nexport async function getPattern(pattern_id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${PATTERNS_TABLE} WHERE id = ?`,\n [pattern_id]\n );\n \n if (result.length === 0) {\n return null;\n }\n \n return result[0] as Pattern;\n}\n\nexport async function getPatternsByType(session_id: string, pattern_type: string): Promise {\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 return result as Pattern[];\n}\n\n// Example operations\nexport async function addExample(\n pattern_id: number,\n example_text: string,\n user_provided: boolean\n): Promise {\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 return result[0].id;\n}\n\nexport async function getPatternExamples(pattern_id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${EXAMPLES_TABLE} WHERE pattern_id = ? ORDER BY timestamp ASC`,\n [pattern_id]\n );\n \n return result as Example[];\n}\n\nexport async function getPatternWithExamples(pattern_id: number): Promise {\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}\n\n// Get all patterns with their examples for a session\nexport async function getSessionPatternsWithExamples(session_id: string): Promise {\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 return patternsWithExamples;\n}","deleted","label",[98],"permalink","https://www.val.town/x/Downchuck/con-juan/code/database.ts","extension","actionData","errors"]