[{"_1":2,"_182":-5,"_183":-5},"loaderData",{"_3":4,"_95":96},"root",{"_5":6,"_36":37,"_68":69},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isBusinessOrgMember","ENV",{"_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,"_64":65,"_66":67},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","f33c6f02e3b9304fe5cb194294cedf6a64b697a5","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","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","API_URL","https://api.val.town","clerkState",{"_70":71},"__internal_clerk_state",{"_72":73,"_74":47,"_75":76,"_77":76,"_78":20,"_79":76,"_80":76,"_81":76,"_82":76,"_83":84,"_93":20,"_94":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",{"_85":20,"_19":20,"_86":76,"_87":88,"_89":76,"_90":47,"_91":20,"_92":76},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_97":98,"_99":100,"_101":102,"_103":104,"_156":157,"_178":179,"_180":181},"handle","Tamas_gr","name","ai-feed","path","src/lib/db.ts","project",{"_105":106,"_99":100,"_107":108,"_109":-5,"_110":20,"_111":112,"_113":76,"_114":-5,"_12":115,"_116":-5,"_117":20,"_118":-5,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":-5,"_125":126,"_127":126,"_128":129,"_130":129,"_131":129,"_132":126,"_133":126,"_23":134,"_137":138,"_142":143,"_155":20},"id","6d5266d2-2c0b-11f1-aabf-42dde27851f2","createdAt",["D",1774856301553],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","019d3d8a-7c54-7244-9df3-c7566f8043d2","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019d3d8a-7c54-7244-9df3-cb76fe1ca972","tursoName","tursoHostname","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_97":98,"_135":136},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18zQmVlU3pHM2tJT1VyUTZBU1lPNHA4aExLMEUifQ","org",{"_97":98,"_139":136,"_12":115,"_140":141},"avatar","tier","free","branch",{"_144":145,"_99":45,"_105":146,"_147":106,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":100,"_154":98},"version",9,"6d52cd20-2c0b-11f1-aabf-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_105":158,"_99":159,"_160":161,"_162":163,"_164":146,"_165":166,"_167":168,"_107":169,"_144":33,"_170":156,"_171":172,"_173":20,"_174":-5,"_101":175},"30113ff8-2c0d-11f1-b338-42dde27851f2","db.ts","fileId","301140c0-2c0d-11f1-b338-42dde27851f2","parentId","f05af2f7-155e-4d94-962c-00472e65160e","branchId","branchVersion",2,"latest",true,["D",1774857057776],"fileType","content","import sqlite from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { DEFAULT_POST_TEMPLATE } from \"./types.ts\";\nimport type { ArticleRow, CategoryRow, PostTemplate } from \"./types.ts\";\n\n// ─── Schema initialization ─────────────────────────────────────────────────\nexport async function initDB() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS articles (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n url TEXT UNIQUE NOT NULL,\n title TEXT DEFAULT '',\n note TEXT DEFAULT '',\n source TEXT DEFAULT 'unknown',\n status TEXT DEFAULT 'pending',\n url_type TEXT,\n extracted_data TEXT,\n summary TEXT,\n category TEXT,\n relevance_score INTEGER,\n slack_ts TEXT,\n preview_data TEXT,\n created_at TEXT DEFAULT (datetime('now')),\n processed_at TEXT\n )\n `);\n\n // Migrations for existing databases\n await sqlite.execute(`ALTER TABLE articles ADD COLUMN preview_data TEXT`).catch(() => {});\n\n // Performance indexes for dashboard queries\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_articles_created_at ON articles(created_at)`);\n\n // ── Categories table ──────────────────────────────────────────────────────\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS categories (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT UNIQUE NOT NULL,\n description TEXT NOT NULL DEFAULT '',\n is_system INTEGER NOT NULL DEFAULT 0,\n active INTEGER NOT NULL DEFAULT 1,\n sort_order INTEGER NOT NULL DEFAULT 0,\n created_at TEXT DEFAULT (datetime('now'))\n )\n `);\n\n // Seed default categories if table is empty\n const existing = await sqlite.execute(`SELECT COUNT(*) FROM categories`);\n if ((existing.rows[0]?.[0] as number) === 0) {\n await sqlite.batch([\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"AI/ML Research\", \"Academic papers, benchmarks, new model architectures, training techniques\", 0] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"AI Tools\", \"New developer tools, IDE features, productivity apps, SaaS products with AI\", 1] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"LLM Updates\", \"Model releases, API changes, provider announcements, pricing, capabilities\", 2] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"AI Business\", \"Funding rounds, acquisitions, partnerships, market analysis, regulation\", 3] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"Tutorial/Guide\", \"How-tos, best practices, prompt engineering, workflow guides\", 4] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"Open Source\", \"Notable repos, library releases, framework updates, community tools\", 5] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, is_system, sort_order) VALUES (?, ?, ?, ?)`, args: [\"Internal\", \"Internal resources from designatives repos\", 1, 6] },\n { sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`, args: [\"Other\", \"Anything worth sharing that doesn't fit the above\", 7] },\n ]);\n }\n\n // ── Settings table ──────────────────────────────────────────────────────\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS settings (\n key TEXT PRIMARY KEY,\n value TEXT NOT NULL,\n updated_at TEXT DEFAULT (datetime('now'))\n )\n `);\n}\n\n// ─── Settings ──────────────────────────────────────────────────────────────\nexport async function getSetting(key: string): Promise {\n const result = await sqlite.execute({ sql: `SELECT value FROM settings WHERE key = ?`, args: [key] });\n return result.rows.length > 0 ? (result.rows[0][0] as string) : null;\n}\n\nexport async function setSetting(key: string, value: string): Promise {\n await sqlite.execute({\n sql: `INSERT INTO settings (key, value, updated_at) VALUES (?, ?, datetime('now'))\n ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at`,\n args: [key, value],\n });\n}\n\nexport async function getPostTemplate(): Promise {\n const raw = await getSetting(\"post_template\");\n if (!raw) return DEFAULT_POST_TEMPLATE;\n try {\n return JSON.parse(raw) as PostTemplate;\n } catch {\n return DEFAULT_POST_TEMPLATE;\n }\n}\n\n// ─── Insert a new article into the queue ────────────────────────────────────\n// Returns true if inserted, false if duplicate\nexport async function insertArticle(\n url: string,\n title: string,\n note: string,\n source: string,\n): Promise {\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO articles (url, title, note, source) VALUES (?, ?, ?, ?)`,\n args: [url, title, note, source],\n });\n return (result.rowsAffected ?? 0) > 0;\n}\n\n// ─── Fetch pending items for processing ─────────────────────────────────────\nexport async function getPending(limit = 5): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM articles WHERE status = 'pending' ORDER BY created_at ASC LIMIT ?`,\n args: [limit],\n });\n\n return result.rows.map(mapRow);\n}\n\nfunction mapRow(row: any): ArticleRow {\n return {\n id: row[0],\n url: row[1],\n title: row[2],\n note: row[3],\n source: row[4],\n status: row[5],\n url_type: row[6],\n extracted_data: row[7],\n summary: row[8],\n category: row[9],\n relevance_score: row[10],\n slack_ts: row[11],\n preview_data: row[12],\n created_at: row[13],\n processed_at: row[14],\n };\n}\n\n// ─── Status updates ─────────────────────────────────────────────────────────\nexport async function markProcessing(id: number) {\n await sqlite.execute({\n sql: `UPDATE articles SET status = 'processing' WHERE id = ?`,\n args: [id],\n });\n}\n\nexport async function markPublished(\n id: number,\n slackTs: string,\n summary: string,\n category: string,\n relevanceScore: number,\n) {\n await sqlite.execute({\n sql: `UPDATE articles SET \n status = 'published', \n slack_ts = ?, \n summary = ?, \n category = ?,\n relevance_score = ?,\n processed_at = datetime('now') \n WHERE id = ?`,\n args: [slackTs, summary, category, relevanceScore, id],\n });\n}\n\nexport async function markSkipped(id: number, category: string, relevanceScore: number) {\n await sqlite.execute({\n sql: `UPDATE articles SET \n status = 'skipped', \n category = ?,\n relevance_score = ?,\n processed_at = datetime('now') \n WHERE id = ?`,\n args: [category, relevanceScore, id],\n });\n}\n\nexport async function markFailed(id: number, error: string) {\n await sqlite.execute({\n sql: `UPDATE articles SET \n status = 'failed', \n extracted_data = ?,\n processed_at = datetime('now') \n WHERE id = ?`,\n args: [JSON.stringify({ error }), id],\n });\n}\n\nexport async function storeExtractedData(id: number, urlType: string, data: string) {\n await sqlite.execute({\n sql: `UPDATE articles SET url_type = ?, extracted_data = ? WHERE id = ?`,\n args: [urlType, data, id],\n });\n}\n\n// ─── Human-in-the-loop preview ────────────────────────────────────────────\nexport async function markPendingApproval(\n id: number,\n previewData: string,\n summary: string,\n category: string,\n relevanceScore: number,\n) {\n await sqlite.execute({\n sql: `UPDATE articles SET\n status = 'pending_approval',\n preview_data = ?,\n summary = ?,\n category = ?,\n relevance_score = ?,\n processed_at = datetime('now')\n WHERE id = ?`,\n args: [previewData, summary, category, relevanceScore, id],\n });\n}\n\nexport async function getArticleById(id: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM articles WHERE id = ?`,\n args: [id],\n });\n return result.rows.length > 0 ? mapRow(result.rows[0]) : null;\n}\n\nexport async function getPendingApproval(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM articles WHERE status = 'pending_approval' ORDER BY processed_at DESC`,\n );\n return result.rows.map(mapRow);\n}\n\n// ─── Dashboard queries ──────────────────────────────────────────────────────\nexport async function getStats(): Promise<{\n total: number;\n published: number;\n skipped: number;\n failed: number;\n pending: number;\n pending_approval: number;\n today: number;\n}> {\n const countsResult = await sqlite.execute(\n `SELECT status, COUNT(*) as count FROM articles GROUP BY status`,\n );\n const counts: Record = {};\n for (const row of countsResult.rows) {\n counts[row[0] as string] = row[1] as number;\n }\n\n const todayResult = await sqlite.execute(\n `SELECT COUNT(*) FROM articles WHERE date(created_at) = date('now')`,\n );\n\n return {\n total: Object.values(counts).reduce((a, b) => a + b, 0),\n published: counts.published || 0,\n skipped: counts.skipped || 0,\n failed: counts.failed || 0,\n pending: (counts.pending || 0) + (counts.processing || 0),\n pending_approval: counts.pending_approval || 0,\n today: (todayResult.rows[0]?.[0] as number) || 0,\n };\n}\n\nexport async function getRecentPublished(limit = 20): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM articles WHERE status = 'published' ORDER BY processed_at DESC LIMIT ?`,\n args: [limit],\n });\n return result.rows.map(mapRow);\n}\n\nexport async function getFailedItems(limit = 20): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM articles WHERE status = 'failed' ORDER BY created_at DESC LIMIT ?`,\n args: [limit],\n });\n return result.rows.map(mapRow);\n}\n\nexport async function resetToPending(id: number) {\n await sqlite.execute({\n sql: `UPDATE articles SET status = 'pending', extracted_data = NULL, processed_at = NULL, preview_data = NULL WHERE id = ?`,\n args: [id],\n });\n}\n\n// ─── Category queries ──────────────────────────────────────────────────────\nfunction mapCategoryRow(row: any): CategoryRow {\n return {\n id: row[0],\n name: row[1],\n description: row[2],\n is_system: row[3] === 1,\n active: row[4] === 1,\n sort_order: row[5],\n };\n}\n\nexport async function getActiveCategories(): Promise {\n const result = await sqlite.execute(\n `SELECT id, name, description, is_system, active, sort_order FROM categories WHERE active = 1 ORDER BY sort_order ASC`,\n );\n return result.rows.map(mapCategoryRow);\n}\n\nexport async function getAllCategories(): Promise {\n const result = await sqlite.execute(\n `SELECT id, name, description, is_system, active, sort_order FROM categories ORDER BY sort_order ASC`,\n );\n return result.rows.map(mapCategoryRow);\n}\n\nexport async function addCategory(name: string, description: string): Promise {\n const maxOrder = await sqlite.execute(`SELECT COALESCE(MAX(sort_order), 0) FROM categories`);\n const nextOrder = ((maxOrder.rows[0]?.[0] as number) || 0) + 1;\n const result = await sqlite.execute({\n sql: `INSERT OR IGNORE INTO categories (name, description, sort_order) VALUES (?, ?, ?)`,\n args: [name, description, nextOrder],\n });\n return (result.rowsAffected ?? 0) > 0;\n}\n\nexport async function updateCategory(id: number, fields: { name?: string; description?: string }): Promise {\n const sets: string[] = [];\n const args: any[] = [];\n if (fields.name !== undefined) { sets.push(\"name = ?\"); args.push(fields.name); }\n if (fields.description !== undefined) { sets.push(\"description = ?\"); args.push(fields.description); }\n if (sets.length === 0) return;\n args.push(id);\n await sqlite.execute({ sql: `UPDATE categories SET ${sets.join(\", \")} WHERE id = ?`, args });\n}\n\nexport async function toggleCategory(id: number, active: boolean): Promise {\n await sqlite.execute({\n sql: `UPDATE categories SET active = ? WHERE id = ? AND is_system = 0`,\n args: [active ? 1 : 0, id],\n });\n}\n\nexport async function getCategoryCounts(): Promise> {\n const result = await sqlite.execute(\n `SELECT category, COUNT(*) FROM articles WHERE category IS NOT NULL GROUP BY category`,\n );\n const counts: Record = {};\n for (const row of result.rows) {\n counts[row[0] as string] = row[1] as number;\n }\n return counts;\n}\n","deleted","label",[176,177,159],"src","lib","permalink","https://www.val.town/x/Tamas_gr/ai-feed/code/src/lib/db.ts","extension","ts","actionData","errors"]