[{"_1":2,"_183":-5,"_184":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":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","isTeamOrgMember","ENV",{"_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":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","a45694a1faeed7a48e6b6123449288e884a42bca","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",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":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",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_161":162,"_180":181,"_182":-5},"handle","dcm31","name","github-leads-with-import","path","db.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":113,"_12":114,"_115":116,"_117":118,"_119":-5,"_120":-5,"_121":32,"_14":122,"_123":-5,"_124":-5,"_125":126,"_127":126,"_128":129,"_130":129,"_131":129,"_132":126,"_133":126,"_23":134,"_137":138,"_142":143,"_160":20},"id","e8da699e-f245-11f0-9257-42dde27851f2","createdAt",["D",1768504252144],"deletedAt","legacyEnvVars","privacy","unlisted","description","Find customers to sell to from GitHub activity","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/37849e88-afe2-4cab-8fff-ad3f050d1200/square512","9319c175-2593-4f88-ba21-04f2ef7b1c28","pinnedFileId","019bc311-5741-73cf-a4dc-4e33d37c0fb5","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","d663a026-6449-42f6-9156-1d4a2d64d38c","tursoName","tursoHostname","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_135":136},"profileImageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/260ec866-bb5d-4641-4bfe-526cca896a00/square512","org",{"_94":95,"_139":136,"_12":114,"_140":141},"avatar","tier","pro","branch",{"_144":145,"_96":44,"_102":146,"_147":103,"_148":149,"_150":151,"_152":153,"_154":155,"_156":157,"_158":97,"_159":95},"version",6,"e8df038c-f245-11f0-9257-42dde27851f2","projectId","forkedBranchId","2beae49c-ed74-11f0-997b-42dde27851f2","forkedBranchVersion",94,"forkedProjectPrivacy","public","forkedProjectId","2be6e306-ed74-11f0-997b-42dde27851f2","forkedProjectOrgId","019aa807-52c2-709c-928b-2029bae9a558","projectName","userHandle","isOwned","file",{"_102":163,"_96":99,"_164":165,"_166":-5,"_167":146,"_168":169,"_170":118,"_104":171,"_144":172,"_173":174,"_175":176,"_177":20,"_178":-5,"_98":179},"2f718b5c-f247-11f0-b60b-42dde27851f2","fileId","019bc311-5741-73cf-a4dc-412ecb30a3e3","parentId","branchId","branchVersion",3,"latest",["D",1768504800088],2,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst TABLE_NAME = \"github_leads_v5\";\n\nawait createTable();\n\nexport interface Lead {\n id: number;\n timestamp: number;\n input_data: string;\n output_data: string;\n}\n\n// Export format - matches multi-company export structure for compatibility\nexport interface ExportData {\n leads: Array<{\n timestamp: number;\n input_data: string;\n output_data: string;\n }>;\n exported_at: number;\n source?: string; // Optional: where this data came from\n}\n\n// Multi-company export format (for import compatibility)\nexport interface MultiCompanyExportData {\n company: {\n slug: string;\n github_org: string;\n display_name: string;\n prompt: string;\n recipients: string;\n active: number;\n created_at: number;\n };\n leads: Array<{\n timestamp: number;\n input_data: string;\n output_data: string;\n }>;\n exported_at: number;\n}\n\nexport async function storeLead(\n inputData: Record | Record[],\n outputData: Record,\n) {\n // Always store as array\n const inputArray = Array.isArray(inputData) ? inputData : [inputData];\n await sqlite.execute(\n `INSERT INTO ${TABLE_NAME} (timestamp, input_data, output_data)\n VALUES (?, ?, ?)`,\n [Date.now(), JSON.stringify(inputArray), JSON.stringify(outputData)],\n );\n}\n\nexport async function updateLeadOutput(\n id: number,\n outputData: Record,\n) {\n await sqlite.execute(\n `UPDATE ${TABLE_NAME} SET output_data = ? WHERE id = ?`,\n [JSON.stringify(outputData), id],\n );\n}\n\nexport async function getAllLeads(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLE_NAME} ORDER BY timestamp DESC`,\n );\n return result.rows as unknown as Lead[];\n}\n\nexport async function getLeadById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${TABLE_NAME} WHERE id = ?`,\n [id],\n );\n return result.rows[0] as unknown as Lead;\n}\n\nexport async function findLeadByInputPath(\n path: string,\n value: string,\n): Promise {\n const leads = await getAllLeads();\n return leads.find((lead) => {\n const input = JSON.parse(lead.input_data);\n // Check first event in array for the path\n const firstEvent = Array.isArray(input) ? input[0] : input;\n return path.split(\".\").reduce((obj, key) => obj?.[key], firstEvent) ===\n value;\n }) || null;\n}\n\nexport async function appendFollowup(\n id: number,\n data: Record | Record[],\n) {\n const lead = await getLeadById(id);\n const input = JSON.parse(lead!.input_data);\n const events = Array.isArray(input) ? input : [input];\n if (Array.isArray(data)) events.push(...data);\n else events.push(data);\n await sqlite.execute(\n `UPDATE ${TABLE_NAME} SET input_data = ? WHERE id = ?`,\n [JSON.stringify(events), id],\n );\n}\n\n// ============================================================================\n// Export\n// ============================================================================\n\nexport async function exportAllLeads(): Promise {\n const leads = await getAllLeads();\n return {\n leads: leads.map((l) => ({\n timestamp: l.timestamp,\n input_data: l.input_data,\n output_data: l.output_data,\n })),\n exported_at: Date.now(),\n source: \"github-leads-with-import\",\n };\n}\n\n// ============================================================================\n// Import\n// ============================================================================\n\n/** Check if a lead already exists (by matching first event's actor.login or email) */\nasync function leadExists(inputData: string): Promise {\n const leads = await getAllLeads();\n const input = JSON.parse(inputData);\n const firstEvent = Array.isArray(input) ? input[0] : input;\n \n // Try to find by actor.login (GitHub events)\n const login = firstEvent?.actor?.login;\n if (login) {\n const existing = leads.find((l) => {\n const existingInput = JSON.parse(l.input_data);\n const existingFirst = Array.isArray(existingInput) ? existingInput[0] : existingInput;\n return existingFirst?.actor?.login === login;\n });\n if (existing) return true;\n }\n \n // Try to find by email (webhook leads)\n const email = firstEvent?.email;\n if (email) {\n const existing = leads.find((l) => {\n const existingInput = JSON.parse(l.input_data);\n const existingFirst = Array.isArray(existingInput) ? existingInput[0] : existingInput;\n return existingFirst?.email === email;\n });\n if (existing) return true;\n }\n \n return false;\n}\n\n/** Import leads from export data. Returns count of imported leads. */\nexport async function importLeads(\n data: ExportData | MultiCompanyExportData,\n options: { skipDuplicates?: boolean } = {}\n): Promise<{ imported: number; skipped: number; errors: string[] }> {\n const { skipDuplicates = true } = options;\n let imported = 0;\n let skipped = 0;\n const errors: string[] = [];\n \n const leads = data.leads;\n \n for (const lead of leads) {\n try {\n // Check for duplicates\n if (skipDuplicates && await leadExists(lead.input_data)) {\n skipped++;\n continue;\n }\n \n // Insert lead with original timestamp\n await sqlite.execute(\n `INSERT INTO ${TABLE_NAME} (timestamp, input_data, output_data)\n VALUES (?, ?, ?)`,\n [lead.timestamp, lead.input_data, lead.output_data],\n );\n imported++;\n } catch (error: any) {\n errors.push(`Failed to import lead: ${error.message}`);\n }\n }\n \n return { imported, skipped, errors };\n}\n\n/** Clear all leads (for fresh import) */\nexport async function clearAllLeads(): Promise {\n const result = await sqlite.execute(`DELETE FROM ${TABLE_NAME}`);\n return result.rowsAffected;\n}\n\nasync function createTable() {\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n timestamp INTEGER NOT NULL,\n input_data TEXT NOT NULL,\n output_data TEXT NOT NULL\n )`,\n );\n}\n","deleted","label",[99],"permalink","https://www.val.town/x/dcm31/github-leads-with-import/code/db.ts","extension","actionData","errors"]