[{"_1":2,"_182":-5,"_183":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"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","isTeamOrgMember","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},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","939580425be10a31a46c456dba7cc90bab2194e6","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_156":157,"_179":180,"_181":-5},"handle","paulkinlan","name","email-agent","path","db/queries.ts","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":20,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":-5,"_115":20,"_116":-5,"_117":-5,"_118":33,"_14":119,"_120":121,"_122":123,"_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","2229998a-17f4-11f1-9791-42dde27851f2","createdAt",["D",1772647273772],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","25f41957-737e-448b-bae4-dec9937eed5b","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","f5d3fee2-e1c2-4f2b-b60d-716ec388aac8","tursoName","2229998a17f411f1979142dde27851f2","tursoHostname","2229998a17f411f1979142dde27851f2-val-town.aws-us-east-2.turso.io","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_135":136},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18yWG9oT0R2UTMxam83NXp1d0RRaGJYVTY0aFYifQ","org",{"_95":96,"_139":136,"_12":113,"_140":141},"avatar","tier","pro","branch",{"_144":145,"_97":45,"_103":146,"_147":104,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":98,"_154":96},"version",55,"222a1edc-17f4-11f1-9791-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":158,"_97":159,"_160":161,"_162":163,"_164":146,"_165":166,"_167":168,"_105":169,"_144":170,"_171":172,"_173":174,"_175":20,"_176":-5,"_99":177},"bc71a476-181a-11f1-845e-42dde27851f2","queries.ts","fileId","07d912a6-1810-11f1-8d14-42dde27851f2","parentId","ede42228-180f-11f1-9915-42dde27851f2","branchId","branchVersion",35,"latest",true,["D",1772663853383],3,"fileType","script","content","/**\n * Database query helpers — unified entries model.\n *\n * Everything is an entry with a type. One set of CRUD functions.\n * FTS sync happens automatically on every insert.\n */\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// ============ TYPES ============\n\nexport interface Entry {\n id: number;\n user_id: number;\n type: string;\n status: string;\n title: string | null;\n content: string;\n tags: string | null;\n metadata: Record | null;\n source_entry_id: number | null;\n next_run_at: string | null;\n schedule_description: string | null;\n created_at: string;\n updated_at: string;\n}\n\nexport interface User {\n id: number;\n email: string;\n name: string | null;\n system_prompt: string;\n created_at: string;\n updated_at: string;\n}\n\nexport interface SearchResult {\n type: string;\n id: number;\n snippet: string;\n rank: number;\n}\n\n// ============ USERS ============\n\nexport async function getOrCreateUser(\n email: string,\n name?: string,\n): Promise {\n const existing = await sqlite.execute({\n sql: `SELECT id, email, name, system_prompt, created_at, updated_at FROM users WHERE email = ?`,\n args: [email.toLowerCase()],\n });\n\n if (existing.rows.length > 0) {\n const row = existing.rows[0] as Record;\n if (name && row.name !== name) {\n await sqlite.execute({\n sql: `UPDATE users SET name = ?, updated_at = datetime('now') WHERE id = ?`,\n args: [name, row.id as number],\n });\n }\n return {\n id: row.id as number,\n email: row.email as string,\n name: (name || row.name) as string | null,\n system_prompt: (row.system_prompt as string) || \"\",\n created_at: row.created_at as string,\n updated_at: row.updated_at as string,\n };\n }\n\n const result = await sqlite.execute({\n sql: `INSERT INTO users (email, name) VALUES (?, ?)`,\n args: [email.toLowerCase(), name || null],\n });\n\n return {\n id: Number(result.lastInsertRowid),\n email: email.toLowerCase(),\n name: name || null,\n system_prompt: \"\",\n created_at: new Date().toISOString(),\n updated_at: new Date().toISOString(),\n };\n}\n\nexport async function getUser(userId: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM users WHERE id = ?`,\n args: [userId],\n });\n if (result.rows.length === 0) return undefined;\n const row = result.rows[0] as Record;\n return {\n id: row.id as number,\n email: row.email as string,\n name: row.name as string | null,\n system_prompt: (row.system_prompt as string) || \"\",\n created_at: row.created_at as string,\n updated_at: row.updated_at as string,\n };\n}\n\nexport async function updateSystemPrompt(\n userId: number,\n newInstructions: string[],\n): Promise {\n const user = await getUser(userId);\n if (!user) throw new Error(`User ${userId} not found`);\n\n // Append new instructions to existing system prompt\n let prompt = user.system_prompt.trim();\n for (const instruction of newInstructions) {\n const line = `- ${instruction.trim()}`;\n // Don't add duplicates\n if (!prompt.includes(instruction.trim())) {\n prompt += (prompt ? \"\\n\" : \"\") + line;\n }\n }\n\n await sqlite.execute({\n sql: `UPDATE users SET system_prompt = ?, updated_at = datetime('now') WHERE id = ?`,\n args: [prompt, userId],\n });\n\n return prompt;\n}\n\nexport async function getSystemPrompt(userId: number): Promise {\n const user = await getUser(userId);\n return user?.system_prompt || \"\";\n}\n\n// ============ ENTRIES (unified CRUD) ============\n\nexport async function addEntry(\n userId: number,\n opts: {\n type: string;\n title?: string;\n content: string;\n tags?: string;\n status?: string;\n metadata?: Record;\n sourceEntryId?: number;\n nextRunAt?: string;\n scheduleDescription?: string;\n },\n): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO entries (user_id, type, status, title, content, tags, metadata, source_entry_id, next_run_at, schedule_description)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n userId,\n opts.type,\n opts.status || \"active\",\n opts.title || null,\n opts.content,\n opts.tags || null,\n opts.metadata ? JSON.stringify(opts.metadata) : null,\n opts.sourceEntryId || null,\n opts.nextRunAt || null,\n opts.scheduleDescription || null,\n ],\n });\n const id = Number(result.lastInsertRowid);\n\n // Sync FTS\n await sqlite.execute({\n sql: `INSERT INTO entries_fts (rowid, title, content, tags) VALUES (?, ?, ?, ?)`,\n args: [id, opts.title || \"\", opts.content, opts.tags || \"\"],\n });\n\n return id;\n}\n\nexport async function getEntries(\n userId: number,\n opts?: {\n type?: string;\n status?: string;\n limit?: number;\n orderBy?: string;\n },\n): Promise {\n let sql = `SELECT * FROM entries WHERE user_id = ?`;\n const args: unknown[] = [userId];\n\n if (opts?.type) {\n sql += ` AND type = ?`;\n args.push(opts.type);\n }\n if (opts?.status) {\n sql += ` AND status = ?`;\n args.push(opts.status);\n }\n\n sql += ` ORDER BY ${opts?.orderBy || \"created_at DESC\"}`;\n sql += ` LIMIT ?`;\n args.push(opts?.limit || 50);\n\n const result = await sqlite.execute({ sql, args });\n return (result.rows as Record[]).map(parseEntryRow);\n}\n\nexport async function getEntry(entryId: number): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM entries WHERE id = ?`,\n args: [entryId],\n });\n if (result.rows.length === 0) return undefined;\n return parseEntryRow(result.rows[0] as Record);\n}\n\nexport async function updateEntryStatus(\n entryId: number,\n status: string,\n resultText?: string,\n) {\n const metadataUpdate = resultText\n ? `, metadata = json_set(COALESCE(metadata, '{}'), '$.result', ?)`\n : \"\";\n const args: unknown[] = resultText\n ? [status, resultText, entryId]\n : [status, entryId];\n\n await sqlite.execute({\n sql: `UPDATE entries SET status = ?, updated_at = datetime('now')${metadataUpdate} WHERE id = ?`,\n args,\n });\n}\n\nexport async function updateEntryMetadata(\n entryId: number,\n updates: Record,\n) {\n // Merge new fields into existing metadata\n const entry = await getEntry(entryId);\n if (!entry) return;\n\n const existing = entry.metadata || {};\n const merged = { ...existing, ...updates };\n\n await sqlite.execute({\n sql: `UPDATE entries SET metadata = ?, updated_at = datetime('now') WHERE id = ?`,\n args: [JSON.stringify(merged), entryId],\n });\n}\n\nexport async function rescheduleEntry(entryId: number, nextRunAt: string) {\n await sqlite.execute({\n sql: `UPDATE entries SET status = 'pending', next_run_at = ?, updated_at = datetime('now') WHERE id = ?`,\n args: [nextRunAt, entryId],\n });\n}\n\n// ============ TASK-SPECIFIC QUERIES ============\n\nexport async function getTasksDue(): Promise<\n (Entry & { user_email: string; user_name: string | null })[]\n> {\n // Compare using strftime to normalize both ISO 8601 (with T/Z) and SQLite\n // datetime formats into a consistent comparable string.\n // We use replace to strip T and Z so '2026-03-04T21:55:26Z' becomes '2026-03-04 21:55:26'\n // which is directly comparable to datetime('now') output.\n const now = new Date().toISOString().replace(\"T\", \" \").replace(\"Z\", \"\");\n const result = await sqlite.execute({\n sql: `SELECT e.*, u.email as user_email, u.name as user_name\n FROM entries e\n JOIN users u ON e.user_id = u.id\n WHERE e.type = 'task'\n AND e.status = 'pending'\n AND e.next_run_at IS NOT NULL\n AND REPLACE(REPLACE(e.next_run_at, 'T', ' '), 'Z', '') <= ?\n ORDER BY e.created_at ASC\n LIMIT 20`,\n args: [now],\n });\n return (result.rows as Record[]).map((row) => ({\n ...parseEntryRow(row),\n user_email: row.user_email as string,\n user_name: row.user_name as string | null,\n }));\n}\n\n// ============ ENTRY LINKS ============\n\nexport async function linkEntries(\n fromId: number,\n toId: number,\n relationship: string = \"related\",\n) {\n try {\n await sqlite.execute({\n sql: `INSERT OR IGNORE INTO entry_links (from_entry_id, to_entry_id, relationship) VALUES (?, ?, ?)`,\n args: [fromId, toId, relationship],\n });\n } catch {\n // Ignore duplicate link errors\n }\n}\n\nexport async function getLinkedEntries(\n entryId: number,\n): Promise<{ entry: Entry; relationship: string; direction: string }[]> {\n const result = await sqlite.execute({\n sql: `SELECT e.*, el.relationship,\n CASE WHEN el.from_entry_id = ? THEN 'outgoing' ELSE 'incoming' END as direction\n FROM entry_links el\n JOIN entries e ON (\n CASE WHEN el.from_entry_id = ? THEN el.to_entry_id ELSE el.from_entry_id END = e.id\n )\n WHERE el.from_entry_id = ? OR el.to_entry_id = ?`,\n args: [entryId, entryId, entryId, entryId],\n });\n return (result.rows as Record[]).map((row) => ({\n entry: parseEntryRow(row),\n relationship: row.relationship as string,\n direction: row.direction as string,\n }));\n}\n\n// ============ FULL-TEXT SEARCH ============\n\nexport async function searchEntries(\n userId: number,\n query: string,\n opts?: { type?: string; limit?: number },\n): Promise {\n const limit = opts?.limit || 20;\n let sql = `SELECT e.id, e.type, snippet(entries_fts, 1, '', '', '...', 32) as snippet, rank\n FROM entries_fts\n JOIN entries e ON entries_fts.rowid = e.id\n WHERE e.user_id = ? AND entries_fts MATCH ?`;\n const args: unknown[] = [userId, query];\n\n if (opts?.type) {\n sql += ` AND e.type = ?`;\n args.push(opts.type);\n }\n\n sql += ` ORDER BY rank LIMIT ?`;\n args.push(limit);\n\n const result = await sqlite.execute({ sql, args });\n return (result.rows as Record[]).map((row) => ({\n type: row.type as string,\n id: row.id as number,\n snippet: row.snippet as string,\n rank: row.rank as number,\n }));\n}\n\n// ============ USER CONTEXT ============\n\n/**\n * Get a full context bundle for a user — used before agent execution.\n * Returns memories, active tasks, recent ideas, recent interactions, and system prompt.\n */\nexport async function getUserContext(userId: number) {\n const [memories, activeTasks, recentIdeas, recentInteractions, user] =\n await Promise.all([\n getEntries(userId, { type: \"memory\", limit: 30 }),\n getEntries(userId, { type: \"task\", status: \"pending\", limit: 20 }),\n getEntries(userId, { type: \"idea\", limit: 10 }),\n getEntries(userId, {\n type: \"interaction\",\n limit: 10,\n orderBy: \"created_at DESC\",\n }),\n getUser(userId),\n ]);\n\n return {\n memories,\n activeTasks,\n recentIdeas,\n recentInteractions,\n systemPrompt: user?.system_prompt || \"\",\n };\n}\n\n// ============ ATTACHMENTS ============\n\nexport interface Attachment {\n id: number;\n entry_id: number;\n user_id: number;\n filename: string;\n mime_type: string;\n size_bytes: number;\n blob_key: string;\n is_text: boolean;\n created_at: string;\n}\n\nexport async function addAttachment(opts: {\n entryId: number;\n userId: number;\n filename: string;\n mimeType: string;\n sizeBytes: number;\n blobKey: string;\n isText: boolean;\n}): Promise {\n const result = await sqlite.execute({\n sql: `INSERT INTO attachments (entry_id, user_id, filename, mime_type, size_bytes, blob_key, is_text)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n args: [\n opts.entryId,\n opts.userId,\n opts.filename,\n opts.mimeType,\n opts.sizeBytes,\n opts.blobKey,\n opts.isText ? 1 : 0,\n ],\n });\n return Number(result.lastInsertRowid);\n}\n\nexport async function getAttachmentsForEntry(\n entryId: number,\n): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM attachments WHERE entry_id = ? ORDER BY created_at ASC`,\n args: [entryId],\n });\n return (result.rows as Record[]).map(parseAttachmentRow);\n}\n\nexport async function getAttachmentsForUser(\n userId: number,\n opts?: { limit?: number },\n): Promise {\n const result = await sqlite.execute({\n sql: `SELECT * FROM attachments WHERE user_id = ? ORDER BY created_at DESC LIMIT ?`,\n args: [userId, opts?.limit || 50],\n });\n return (result.rows as Record[]).map(parseAttachmentRow);\n}\n\nfunction parseAttachmentRow(row: Record): Attachment {\n return {\n id: row.id as number,\n entry_id: row.entry_id as number,\n user_id: row.user_id as number,\n filename: row.filename as string,\n mime_type: row.mime_type as string,\n size_bytes: row.size_bytes as number,\n blob_key: row.blob_key as string,\n is_text: (row.is_text as number) === 1,\n created_at: row.created_at as string,\n };\n}\n\n// ============ HELPERS ============\n\nfunction parseEntryRow(row: Record): Entry {\n let metadata: Record | null = null;\n if (row.metadata) {\n try {\n metadata = JSON.parse(row.metadata as string);\n } catch {\n metadata = null;\n }\n }\n\n return {\n id: row.id as number,\n user_id: row.user_id as number,\n type: row.type as string,\n status: row.status as string,\n title: row.title as string | null,\n content: row.content as string,\n tags: row.tags as string | null,\n metadata,\n source_entry_id: row.source_entry_id as number | null,\n next_run_at: row.next_run_at as string | null,\n schedule_description: row.schedule_description as string | null,\n created_at: row.created_at as string,\n updated_at: row.updated_at as string,\n };\n}\n","deleted","label",[178,159],"db","permalink","https://www.val.town/x/paulkinlan/email-agent/code/db/queries.ts","extension","actionData","errors"]