[{"_1":2,"_174":-5,"_175":-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","2f607f7b74b4e52529fb75176d403d8b5b284eb1","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,"_149":150,"_171":172,"_173":-5},"handle","boucher","name","vt-discord","path","backend/database.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":72,"_110":-5,"_12":111,"_112":113,"_114":115,"_116":-5,"_117":118,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","2e9210fa-2711-11f0-a12e-569c3dd06744","createdAt",["D",1746161519737],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","f48e9547-27bd-4117-9554-682f2eb67a4b","pinnedFileId","751ff414-2884-11f0-b137-569c3dd06744","pinPreview",true,"screenshotVersion","likeCount",2,"4f5bd3a8-17ca-44d2-b569-1c9fcdb094b9","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_130":131},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJObXh1eU1FSEZMMTNaWFM1Rm5rYXBrd0pBRyIsImluaXRpYWxzIjoiUkIifQ","org",{"_93":94,"_134":131},"avatar","branch",{"_137":138,"_95":43,"_101":139,"_140":102,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":96,"_147":94},"version",34,"2e944ece-2711-11f0-a12e-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":151,"_95":152,"_153":154,"_155":156,"_157":139,"_158":159,"_160":115,"_103":161,"_137":162,"_163":164,"_165":166,"_167":20,"_168":-5,"_97":169},"053d6176-288a-11f0-84dc-569c3dd06744","database.ts","fileId","e8fa4766-2859-11f0-96d8-569c3dd06744","parentId","e89244b8-2859-11f0-b4f5-569c3dd06744","branchId","branchVersion",31,"latest",["D",1746323370671],1,"fileType","script","content","// Database operations for the Discord bot\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\n// Database initialization function - call this when the application starts\nexport async function initDatabase() {\n try {\n // Messages table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS messages (\n id TEXT PRIMARY KEY,\n channel_id TEXT NOT NULL,\n user_id TEXT NOT NULL,\n content TEXT NOT NULL,\n timestamp INTEGER NOT NULL,\n has_links BOOLEAN DEFAULT FALSE,\n is_command BOOLEAN DEFAULT FALSE\n )\n `);\n \n // Links table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS links (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n message_id TEXT NOT NULL,\n url TEXT NOT NULL,\n domain TEXT NOT NULL,\n timestamp INTEGER NOT NULL,\n category TEXT,\n FOREIGN KEY(message_id) REFERENCES messages(id)\n )\n `);\n \n // Projects table (for explicit grouping)\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS projects (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n description TEXT,\n created_at INTEGER NOT NULL,\n is_active BOOLEAN DEFAULT TRUE\n )\n `);\n \n // Project messages mapping\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS project_messages (\n project_id INTEGER NOT NULL,\n message_id TEXT NOT NULL,\n PRIMARY KEY(project_id, message_id),\n FOREIGN KEY(project_id) REFERENCES projects(id),\n FOREIGN KEY(message_id) REFERENCES messages(id)\n )\n `);\n \n // Topics table (for AI-categorized grouping)\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS topics (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n description TEXT,\n created_at INTEGER NOT NULL\n )\n `);\n \n // Topic messages mapping\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS topic_messages (\n topic_id INTEGER NOT NULL,\n message_id TEXT NOT NULL,\n PRIMARY KEY(topic_id, message_id),\n FOREIGN KEY(topic_id) REFERENCES topics(id),\n FOREIGN KEY(message_id) REFERENCES messages(id)\n )\n `);\n \n console.log(\"Database initialized successfully\");\n return true;\n } catch (error) {\n console.error(\"Database initialization error:\", error);\n return false;\n }\n}\n\n// Types\nexport interface Message {\n id: string;\n channel_id: string;\n user_id: string;\n content: string;\n timestamp: number;\n has_links: boolean;\n is_command: boolean;\n}\n\nexport interface Link {\n id?: number;\n message_id: string;\n url: string;\n domain: string;\n timestamp: number;\n category?: string;\n}\n\nexport interface Project {\n id?: number;\n name: string;\n description?: string;\n created_at: number;\n is_active: boolean;\n}\n\nexport interface Topic {\n id?: number;\n name: string;\n description?: string;\n created_at: number;\n}\n\n// URL regex pattern for link extraction\nconst URL_REGEX = /(https?:\\/\\/[^\\s]+)/g;\nconst DOMAIN_REGEX = /^(?:https?:\\/\\/)?(?:[^@\\n]+@)?(?:www\\.)?([^:/\\n?]+)/i;\n\n// Message operations\nexport async function saveMessage(message: Message): Promise {\n try {\n await sqlite.execute(\n `INSERT INTO messages (id, channel_id, user_id, content, timestamp, has_links, is_command)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT(id) DO UPDATE SET\n content = excluded.content,\n has_links = excluded.has_links,\n is_command = excluded.is_command`,\n [\n message.id,\n message.channel_id,\n message.user_id,\n message.content,\n message.timestamp,\n message.has_links ? 1 : 0,\n message.is_command ? 1 : 0\n ]\n );\n return true;\n } catch (error) {\n console.error(\"Error saving message:\", error);\n return false;\n }\n}\n\nexport async function getMessage(messageId: string): Promise {\n try {\n const result = await sqlite.execute(\n \"SELECT * FROM messages WHERE id = ?\",\n [messageId]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n const row = result.rows[0];\n return {\n id: row.id,\n channel_id: row.channel_id,\n user_id: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n has_links: Boolean(row.has_links),\n is_command: Boolean(row.is_command)\n };\n } catch (error) {\n console.error(\"Error getting message:\", error);\n return null;\n }\n}\n\nexport async function getMessagesByUserIds(userIds: string[], limit = 1000, offset = 0): Promise {\n try {\n // Create placeholders for the SQL IN clause\n const placeholders = userIds.map(() => '?').join(',');\n \n const result = await sqlite.execute(\n `SELECT * FROM messages\n WHERE user_id IN (${placeholders})\n ORDER BY timestamp DESC\n LIMIT ? OFFSET ?`,\n [...userIds, limit, offset]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n channel_id: row.channel_id,\n user_id: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n has_links: Boolean(row.has_links),\n is_command: Boolean(row.is_command)\n }));\n } catch (error) {\n console.error(\"Error getting messages by user ids:\", error);\n return [];\n }\n}\n\nexport async function searchMessages(query: string, limit = 100): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM messages\n WHERE content LIKE ?\n ORDER BY timestamp DESC\n LIMIT ?`,\n [`%${query}%`, limit]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n channel_id: row.channel_id,\n user_id: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n has_links: Boolean(row.has_links),\n is_command: Boolean(row.is_command)\n }));\n } catch (error) {\n console.error(\"Error searching messages:\", error);\n return [];\n }\n}\n\n// Link operations\nexport function extractLinks(content: string): { urls: string[], domains: string[] } {\n const urls: string[] = [];\n const domains: string[] = [];\n \n const matches = content.match(URL_REGEX);\n \n if (matches) {\n matches.forEach(url => {\n urls.push(url);\n \n const domainMatch = url.match(DOMAIN_REGEX);\n if (domainMatch && domainMatch[1]) {\n domains.push(domainMatch[1]);\n }\n });\n }\n \n return { urls, domains };\n}\n\nexport async function saveLink(link: Link): Promise {\n try {\n const result = await sqlite.execute(\n `INSERT INTO links (message_id, url, domain, timestamp, category)\n VALUES (?, ?, ?, ?, ?)\n RETURNING id`,\n [\n link.message_id,\n link.url,\n link.domain,\n link.timestamp,\n link.category || null\n ]\n );\n \n if (result.rows.length > 0) {\n return result.rows[0].id;\n }\n return null;\n } catch (error) {\n console.error(\"Error saving link:\", error);\n return null;\n }\n}\n\nexport async function getLinks(limit = 50, offset = 0): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM links\n ORDER BY timestamp DESC\n LIMIT ? OFFSET ?`,\n [limit, offset]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n message_id: row.message_id,\n url: row.url,\n domain: row.domain,\n timestamp: row.timestamp,\n category: row.category\n }));\n } catch (error) {\n console.error(\"Error getting links:\", error);\n return [];\n }\n}\n\nexport async function searchLinks(query: string, limit = 50): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM links\n WHERE url LIKE ? OR domain LIKE ? OR category LIKE ?\n ORDER BY timestamp DESC\n LIMIT ?`,\n [`%${query}%`, `%${query}%`, `%${query}%`, limit]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n message_id: row.message_id,\n url: row.url,\n domain: row.domain,\n timestamp: row.timestamp,\n category: row.category\n }));\n } catch (error) {\n console.error(\"Error searching links:\", error);\n return [];\n }\n}\n\nexport async function updateLinkCategory(linkId: number, category: string): Promise {\n try {\n await sqlite.execute(\n `UPDATE links\n SET category = ?\n WHERE id = ?`,\n [category, linkId]\n );\n return true;\n } catch (error) {\n console.error(\"Error updating link category:\", error);\n return false;\n }\n}\n\n// Project operations\nexport async function createProject(project: Project): Promise {\n try {\n const result = await sqlite.execute(\n `INSERT INTO projects (name, description, created_at, is_active)\n VALUES (?, ?, ?, ?)\n RETURNING id`,\n [\n project.name,\n project.description || null,\n project.created_at,\n project.is_active ? 1 : 0\n ]\n );\n \n if (result.rows.length > 0) {\n return result.rows[0].id;\n }\n return null;\n } catch (error) {\n console.error(\"Error creating project:\", error);\n return null;\n }\n}\n\nexport async function getProjects(includeInactive = false): Promise {\n try {\n let query = `SELECT * FROM projects`;\n const params = [];\n \n if (!includeInactive) {\n query += ` WHERE is_active = 1`;\n }\n \n query += ` ORDER BY created_at DESC`;\n \n const result = await sqlite.execute(query, params);\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n created_at: row.created_at,\n is_active: Boolean(row.is_active)\n }));\n } catch (error) {\n console.error(\"Error getting projects:\", error);\n return [];\n }\n}\n\nexport async function getProject(projectId: number): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM projects WHERE id = ?`,\n [projectId]\n );\n \n if (result.rows.length === 0) {\n return null;\n }\n \n const row = result.rows[0];\n return {\n id: row.id,\n name: row.name,\n description: row.description,\n created_at: row.created_at,\n is_active: Boolean(row.is_active)\n };\n } catch (error) {\n console.error(\"Error getting project:\", error);\n return null;\n }\n}\n\nexport async function addMessageToProject(projectId: number, messageId: string): Promise {\n try {\n await sqlite.execute(\n `INSERT INTO project_messages (project_id, message_id)\n VALUES (?, ?)\n ON CONFLICT DO NOTHING`,\n [projectId, messageId]\n );\n return true;\n } catch (error) {\n console.error(\"Error adding message to project:\", error);\n return false;\n }\n}\n\nexport async function getProjectMessages(projectId: number, limit = 100, offset = 0): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT m.*\n FROM messages m\n JOIN project_messages pm ON m.id = pm.message_id\n WHERE pm.project_id = ?\n ORDER BY m.timestamp DESC\n LIMIT ? OFFSET ?`,\n [projectId, limit, offset]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n channel_id: row.channel_id,\n user_id: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n has_links: Boolean(row.has_links),\n is_command: Boolean(row.is_command)\n }));\n } catch (error) {\n console.error(\"Error getting project messages:\", error);\n return [];\n }\n}\n\n// Topic operations\nexport async function createTopic(topic: Topic): Promise {\n try {\n const result = await sqlite.execute(\n `INSERT INTO topics (name, description, created_at)\n VALUES (?, ?, ?)\n RETURNING id`,\n [\n topic.name,\n topic.description || null,\n topic.created_at\n ]\n );\n \n if (result.rows.length > 0) {\n return result.rows[0].id;\n }\n return null;\n } catch (error) {\n console.error(\"Error creating topic:\", error);\n return null;\n }\n}\n\nexport async function getTopics(): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT * FROM topics ORDER BY name ASC`\n );\n \n return result.rows.map(row => ({\n id: row.id,\n name: row.name,\n description: row.description,\n created_at: row.created_at\n }));\n } catch (error) {\n console.error(\"Error getting topics:\", error);\n return [];\n }\n}\n\nexport async function addMessageToTopic(topicId: number, messageId: string): Promise {\n try {\n await sqlite.execute(\n `INSERT INTO topic_messages (topic_id, message_id)\n VALUES (?, ?)\n ON CONFLICT DO NOTHING`,\n [topicId, messageId]\n );\n return true;\n } catch (error) {\n console.error(\"Error adding message to topic:\", error);\n return false;\n }\n}\n\nexport async function getTopicMessages(topicId: number, limit = 100, offset = 0): Promise {\n try {\n const result = await sqlite.execute(\n `SELECT m.*\n FROM messages m\n JOIN topic_messages tm ON m.id = tm.message_id\n WHERE tm.topic_id = ?\n ORDER BY m.timestamp DESC\n LIMIT ? OFFSET ?`,\n [topicId, limit, offset]\n );\n \n return result.rows.map(row => ({\n id: row.id,\n channel_id: row.channel_id,\n user_id: row.user_id,\n content: row.content,\n timestamp: row.timestamp,\n has_links: Boolean(row.has_links),\n is_command: Boolean(row.is_command)\n }));\n } catch (error) {\n console.error(\"Error getting topic messages:\", error);\n return [];\n }\n}","deleted","label",[170,152],"backend","permalink","https://www.val.town/x/boucher/vt-discord/code/backend/database.ts","extension","actionData","errors"]