[{"_1":2,"_178":-5,"_179":-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","a6fade7d134bf0c84edaf185baa97bac064639ed","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,"_156":157,"_175":176,"_177":-5},"handle","marvinkennis","name","github-leads","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":124,"_125":124,"_126":127,"_128":127,"_129":127,"_130":124,"_131":124,"_23":132,"_135":136,"_138":139,"_155":20},"id","a48ab63e-f175-11f0-9c39-42dde27851f2","createdAt",["D",1768414802213],"deletedAt","legacyEnvVars","privacy","public","description","Find customers to sell to from GitHub activity","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/37849e88-afe2-4cab-8fff-ad3f050d1200/square512","01999d8a-2e2b-7334-a10f-d67134ebdcef","pinnedFileId","019bbdbc-7172-724f-8ea5-5a3b64ab4931","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","01999d8a-2e2c-77ac-a0ce-c774027c8395","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_133":134},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzMzUmZFdlo2amNOdlJuQU9kMFZEbk03SWFYYSJ9","org",{"_94":95,"_137":134,"_12":114},"avatar","branch",{"_140":141,"_96":44,"_102":142,"_143":103,"_144":145,"_146":147,"_148":109,"_149":150,"_151":152,"_153":97,"_154":95},"version",40,"a48e8b7e-f175-11f0-9c39-42dde27851f2","projectId","forkedBranchId","2beae49c-ed74-11f0-997b-42dde27851f2","forkedBranchVersion",94,"forkedProjectPrivacy","forkedProjectId","2be6e306-ed74-11f0-997b-42dde27851f2","forkedProjectOrgId","019aa807-52c2-709c-928b-2029bae9a558","projectName","userHandle","isOwned","file",{"_102":158,"_96":99,"_159":160,"_161":-5,"_162":142,"_163":164,"_165":118,"_104":166,"_140":167,"_168":169,"_170":171,"_172":20,"_173":-5,"_98":174},"ebd1c436-f177-11f0-926a-42dde27851f2","fileId","019bbdbc-7172-724f-8ea5-4dcf911779c2","parentId","branchId","branchVersion",26,"latest",["D",1768415780821],6,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst TABLE_NAME = \"github_leads_v4\";\nconst REPO_STATE_TABLE = \"repo_state_v1\";\n\nawait createTable();\nawait createRepoStateTable();\n\nexport interface Lead {\n id: number;\n timestamp: number;\n input_data: string;\n output_data: string;\n}\n\nexport interface RepoState {\n repo_name: string;\n lastChecked: string;\n historicScrapeComplete: boolean;\n historicScrapeProgress?: number; // Page number for gradual scraping\n totalStars?: number; // Total stars when scraping started\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\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\nasync function createRepoStateTable() {\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS ${REPO_STATE_TABLE} (\n repo_name TEXT PRIMARY KEY,\n last_checked TEXT NOT NULL,\n historic_scrape_complete BOOLEAN NOT NULL DEFAULT FALSE,\n historic_scrape_progress INTEGER DEFAULT 0,\n total_stars INTEGER DEFAULT 0\n )`,\n );\n}\n\nexport async function getRepoState(repoName: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${REPO_STATE_TABLE} WHERE repo_name = ?`,\n [repoName]\n );\n \n if (result.rows.length === 0) return null;\n \n const row = result.rows[0] as any;\n return {\n repo_name: row.repo_name,\n lastChecked: row.last_checked,\n historicScrapeComplete: Boolean(row.historic_scrape_complete),\n historicScrapeProgress: row.historic_scrape_progress || 0,\n totalStars: row.total_stars || 0,\n };\n}\n\nexport async function updateRepoState(\n repoName: string, \n state: { \n lastChecked: string; \n historicScrapeComplete: boolean;\n historicScrapeProgress?: number;\n totalStars?: number;\n }\n) {\n await sqlite.execute(\n `INSERT OR REPLACE INTO ${REPO_STATE_TABLE} \n (repo_name, last_checked, historic_scrape_complete, historic_scrape_progress, total_stars) \n VALUES (?, ?, ?, ?, ?)`,\n [\n repoName, \n state.lastChecked, \n state.historicScrapeComplete,\n state.historicScrapeProgress || 0,\n state.totalStars || 0\n ]\n );\n}","deleted","label",[99],"permalink","https://www.val.town/x/marvinkennis/github-leads/code/db.ts","extension","actionData","errors"]