[{"_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","e3ca0a77ab07ff2ce3097208ffd6aa0e378d3c47","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,"_147":148,"_170":171,"_172":-5},"handle","jpd3v","name","offx","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":72,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":116,"_14":117,"_118":119,"_120":119,"_121":122,"_123":122,"_124":122,"_125":119,"_126":119,"_23":127,"_130":131,"_133":134,"_146":20},"id","a93616c0-55f2-11f0-8cdc-f69ea79377d9","createdAt",["D",1751316115780],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","da7efaa0-3a43-11f0-b284-7173a8a6f515","pinnedFileId","pinPreview","screenshotVersion","likeCount",1,"f1d76965-949f-4699-906d-07e921194179","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_128":129},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yeGRlTk9ERTV0VzdNb2FqR0NxOGRuM0NlaTEifQ","org",{"_93":94,"_132":129},"avatar","branch",{"_135":136,"_95":43,"_101":137,"_138":102,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":96,"_145":94},"version",98,"a93670de-55f2-11f0-8cdc-f69ea79377d9","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":149,"_95":150,"_151":152,"_153":154,"_155":137,"_156":157,"_158":159,"_103":160,"_135":32,"_161":162,"_163":164,"_165":20,"_166":-5,"_97":167},"854fe786-8a7c-11f0-85b0-0224a6c84d84","queries.ts","fileId","854fe858-8a7c-11f0-85b0-0224a6c84d84","parentId","06a4dc18-55f5-11f0-9d32-f69ea79377d9","branchId","branchVersion",75,"latest",true,["D",1757092784273],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./migrations.ts\";\n\n// Type definitions\nexport interface Topic {\n id?: number;\n title: string;\n description?: string;\n created_at?: string;\n updated_at?: string;\n}\n\nexport interface Post {\n id?: number;\n external_id: string;\n content: string;\n author: string;\n author_username: string;\n platform: string;\n created_at: string;\n fetched_at?: string;\n url?: string;\n media?: PostMedia[];\n}\n\nexport interface PostMedia {\n id?: number;\n post_id: number;\n media_url: string;\n media_type: string;\n alt_text?: string;\n created_at?: string;\n}\n\nexport interface Summarization {\n id?: number;\n topic_id: number;\n content: string;\n prompt: string;\n post_count: number;\n created_at?: string;\n time_period_start: string;\n time_period_end: string;\n time_period?: string; // For compatibility with existing code\n}\n\nexport interface TopicPost {\n topic_id: number;\n post_id: number;\n added_at?: string;\n}\n\nexport interface PostSentiment {\n id?: number;\n post_id: number;\n sentiment: \"positive\" | \"negative\" | \"neutral\";\n score: number; // -1.0 to 1.0\n confidence: number; // 0.0 to 1.0\n keywords: string[];\n entities?: string[];\n analyzed_at?: string;\n}\n\n// Topic operations\nexport async function createTopic(topic: Topic): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.TOPICS} (title, description) VALUES (?, ?)`,\n [topic.title, topic.description || null],\n );\n return Number(result.lastInsertRowid) || 0;\n}\n\nexport async function getTopicById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.TOPICS} WHERE id = ?`,\n [id],\n );\n return result.rows.length > 0 ? result.rows[0] as unknown as Topic : null;\n}\n\nexport async function getAllTopics(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.TOPICS} ORDER BY title`,\n );\n return result.rows as unknown as Topic[];\n}\n\nexport async function updateTopic(topic: Topic): Promise {\n if (!topic.id) return false;\n\n await sqlite.execute(\n `UPDATE ${TABLES.TOPICS} SET title = ?, description = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,\n [topic.title, topic.description || null, topic.id],\n );\n return true;\n}\n\nexport async function deleteTopic(id: number): Promise {\n await sqlite.execute(`DELETE FROM ${TABLES.TOPICS} WHERE id = ?`, [id]);\n return true;\n}\n\n// Post operations\nexport async function createPost(post: Post): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.POSTS} \n (external_id, content, author, author_username, platform, created_at, url) \n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n [\n post.external_id,\n post.content,\n post.author,\n post.author_username,\n post.platform,\n post.created_at,\n post.url || null,\n ],\n );\n\n const postId = Number(result.lastInsertRowid) || 0;\n\n // Insert media if provided\n if (post.media && post.media.length > 0) {\n for (const media of post.media) {\n await createPostMedia({\n ...media,\n post_id: postId,\n });\n }\n }\n\n return postId;\n}\n\nexport async function getPostById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.POSTS} WHERE id = ?`,\n [id],\n );\n\n if (result.rows.length === 0) return null;\n\n const post = result.rows[0] as unknown as Post;\n post.media = await getMediaForPost(post.id!);\n\n return post;\n}\n\nexport async function getPostByExternalId(\n externalId: string,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.POSTS} WHERE external_id = ?`,\n [externalId],\n );\n\n if (result.rows.length === 0) return null;\n\n const post = result.rows[0] as unknown as Post;\n post.media = await getMediaForPost(post.id!);\n\n return post;\n}\n\nexport async function getRecentPosts(limit: number = 100): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.POSTS} ORDER BY created_at DESC LIMIT ?`,\n [limit],\n );\n\n const posts = result.rows as unknown as Post[];\n\n // Get media for each post\n for (const post of posts) {\n post.media = await getMediaForPost(post.id!);\n }\n\n return posts;\n}\n\nexport async function deletePost(id: number): Promise {\n await sqlite.execute(`DELETE FROM ${TABLES.POSTS} WHERE id = ?`, [id]);\n return true;\n}\n\n// Post Media operations\nexport async function createPostMedia(media: PostMedia): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.POST_MEDIA} \n (post_id, media_url, media_type, alt_text) \n VALUES (?, ?, ?, ?)`,\n [\n media.post_id,\n media.media_url,\n media.media_type,\n media.alt_text || null,\n ],\n );\n\n return Number(result.lastInsertRowid) || 0;\n}\n\nexport async function getMediaForPost(postId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.POST_MEDIA} WHERE post_id = ?`,\n [postId],\n );\n\n return result.rows as unknown as PostMedia[];\n}\n\n/**\n * Get media items for a post by post ID (alias for getMediaForPost)\n * @param postId ID of the post\n * @returns Array of media items for the post\n */\nexport async function getPostMediaByPostId(\n postId: number,\n): Promise {\n return getMediaForPost(postId);\n}\n\n// Topic-Post relationship operations\nexport async function addPostToTopic(\n topicId: number,\n postId: number,\n): Promise {\n try {\n await sqlite.execute(\n `INSERT INTO ${TABLES.TOPIC_POSTS} (topic_id, post_id) VALUES (?, ?)`,\n [topicId, postId],\n );\n return true;\n } catch (error: unknown) {\n // Likely a duplicate entry, which is fine\n return false;\n }\n}\n\nexport async function removePostFromTopic(\n topicId: number,\n postId: number,\n): Promise {\n await sqlite.execute(\n `DELETE FROM ${TABLES.TOPIC_POSTS} WHERE topic_id = ? AND post_id = ?`,\n [topicId, postId],\n );\n return true;\n}\n\nexport async function getPostsForTopic(\n topicId: number,\n limit: number = 100,\n): Promise {\n const result = await sqlite.execute(\n `SELECT p.* FROM ${TABLES.POSTS} p\n JOIN ${TABLES.TOPIC_POSTS} tp ON p.id = tp.post_id\n WHERE tp.topic_id = ?\n ORDER BY p.created_at DESC\n LIMIT ?`,\n [topicId, limit],\n );\n\n const posts = result.rows as unknown as Post[];\n\n // Get media for each post\n for (const post of posts) {\n post.media = await getMediaForPost(post.id!);\n }\n\n return posts;\n}\n\n// Summarization operations\nexport async function createSummarization(\n summarization: Summarization,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.SUMMARIZATIONS} \n (topic_id, content, prompt, post_count, time_period_start, time_period_end) \n VALUES (?, ?, ?, ?, ?, ?)`,\n [\n summarization.topic_id,\n summarization.content,\n summarization.prompt,\n summarization.post_count,\n summarization.time_period_start,\n summarization.time_period_end,\n ],\n );\n\n return Number(result.lastInsertRowid) || 0;\n}\n\nexport async function getSummarizationById(\n id: number,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.SUMMARIZATIONS} WHERE id = ?`,\n [id],\n );\n\n return result.rows.length > 0 ? result.rows[0] as unknown as Summarization : null;\n}\n\nexport async function getSummarizationsForTopic(\n topicId: number,\n limit: number = 10,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.SUMMARIZATIONS} \n WHERE topic_id = ? \n ORDER BY created_at DESC \n LIMIT ?`,\n [topicId, limit],\n );\n\n return result.rows as unknown as Summarization[];\n}\n\nexport async function getRecentSummarizations(\n limit: number = 10,\n): Promise {\n const result = await sqlite.execute(\n `SELECT s.*, t.title as topic_title \n FROM ${TABLES.SUMMARIZATIONS} s\n JOIN ${TABLES.TOPICS} t ON s.topic_id = t.id\n ORDER BY s.created_at DESC \n LIMIT ?`,\n [limit],\n );\n\n return result.rows as unknown as (Summarization & { topic_title: string })[];\n}\n\nexport async function deleteSummarization(id: number): Promise {\n await sqlite.execute(\n `DELETE FROM ${TABLES.SUMMARIZATIONS} WHERE id = ?`,\n [id],\n );\n return true;\n}\n\n// Sentiment analysis operations\nexport async function createPostSentiment(\n sentiment: PostSentiment,\n): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${TABLES.POST_SENTIMENT} \n (post_id, sentiment, score, confidence, keywords, entities) \n VALUES (?, ?, ?, ?, ?, ?)`,\n [\n sentiment.post_id,\n sentiment.sentiment,\n sentiment.score,\n sentiment.confidence,\n JSON.stringify(sentiment.keywords),\n sentiment.entities ? JSON.stringify(sentiment.entities) : null,\n ],\n );\n\n return Number(result.lastInsertRowid) || 0;\n}\n\nexport async function getPostSentiment(\n postId: number,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLES.POST_SENTIMENT} WHERE post_id = ?`,\n [postId],\n );\n\n if (result.rows.length === 0) return null;\n\n const sentiment = result.rows[0] as any;\n\n // Parse JSON strings back to arrays\n return {\n ...sentiment,\n keywords: JSON.parse(sentiment.keywords || \"[]\"),\n entities: sentiment.entities ? JSON.parse(sentiment.entities) : undefined,\n };\n}\n\nexport async function updatePostSentiment(\n sentiment: PostSentiment,\n): Promise {\n await sqlite.execute(\n `UPDATE ${TABLES.POST_SENTIMENT} \n SET sentiment = ?, score = ?, confidence = ?, keywords = ?, entities = ?, analyzed_at = CURRENT_TIMESTAMP\n WHERE post_id = ?`,\n [\n sentiment.sentiment,\n sentiment.score,\n sentiment.confidence,\n JSON.stringify(sentiment.keywords),\n sentiment.entities ? JSON.stringify(sentiment.entities) : null,\n sentiment.post_id,\n ],\n );\n\n return true;\n}\n\nexport async function createOrUpdatePostSentiment(\n sentiment: PostSentiment,\n): Promise {\n // Check if sentiment analysis already exists for this post\n const existingSentiment = await getPostSentiment(sentiment.post_id);\n\n if (existingSentiment) {\n await updatePostSentiment(sentiment);\n return existingSentiment.id!;\n } else {\n return await createPostSentiment(sentiment);\n }\n}\n\nexport async function getPostsWithSentiment(\n sentiment?: \"positive\" | \"negative\" | \"neutral\",\n minConfidence: number = 0.7,\n limit: number = 100,\n): Promise {\n let query = `\n SELECT p.* FROM ${TABLES.POSTS} p\n JOIN ${TABLES.POST_SENTIMENT} ps ON p.id = ps.post_id\n WHERE ps.confidence >= ?\n `;\n\n const params: any[] = [minConfidence];\n\n if (sentiment) {\n query += ` AND ps.sentiment = ?`;\n params.push(sentiment);\n }\n\n query += ` ORDER BY p.created_at DESC LIMIT ?`;\n params.push(limit);\n\n const result = await sqlite.execute(query, params);\n\n const posts = result.rows as unknown as Post[];\n\n // Get media for each post\n for (const post of posts) {\n post.media = await getMediaForPost(post.id!);\n }\n\n return posts;\n}\n\nexport async function getPostsWithoutSentimentAnalysis(\n limit: number = 100,\n): Promise {\n const result = await sqlite.execute(\n `SELECT p.* FROM ${TABLES.POSTS} p\n LEFT JOIN ${TABLES.POST_SENTIMENT} ps ON p.id = ps.post_id\n WHERE ps.id IS NULL\n ORDER BY p.created_at DESC\n LIMIT ?`,\n [limit],\n );\n\n const posts = result.rows as unknown as Post[];\n\n // Get media for each post\n for (const post of posts) {\n post.media = await getMediaForPost(post.id!);\n }\n\n return posts;\n}\n\nexport async function getSentimentStats(): Promise<{\n positive: number;\n negative: number;\n neutral: number;\n total: number;\n avgScore: number;\n}> {\n const result = await sqlite.execute(`\n SELECT \n COUNT(*) as total,\n SUM(CASE WHEN sentiment = 'positive' THEN 1 ELSE 0 END) as positive,\n SUM(CASE WHEN sentiment = 'negative' THEN 1 ELSE 0 END) as negative,\n SUM(CASE WHEN sentiment = 'neutral' THEN 1 ELSE 0 END) as neutral,\n AVG(score) as avgScore\n FROM ${TABLES.POST_SENTIMENT}\n `);\n\n if (result.rows.length === 0) {\n return {\n positive: 0,\n negative: 0,\n neutral: 0,\n total: 0,\n avgScore: 0,\n };\n }\n\n return result.rows[0] as unknown as any;\n}\n","deleted","label",[168,169,150],"backend","database","permalink","https://www.val.town/x/jpd3v/offx/code/backend/database/queries.ts","extension","actionData","errors"]