[{"_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","be06b7c9e201d45bb63462287eead209c63edff4","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","dcm31","name","github-leads-multi-company","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","05ee4c20-eff9-11f0-898e-42dde27851f2","createdAt",["D",1768251327355],"deletedAt","legacyEnvVars","privacy","public","description","Find people to hire or sell to from GitHub activity","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/37849e88-afe2-4cab-8fff-ad3f050d1200/square512","9319c175-2593-4f88-ba21-04f2ef7b1c28","pinnedFileId","019bb3fe-03ba-7068-ab27-547e5d74e6ea","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","d663a026-6449-42f6-9156-1d4a2d64d38c","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_133":134},"profileImageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/260ec866-bb5d-4641-4bfe-526cca896a00/square512","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",52,"05f120da-eff9-11f0-898e-42dde27851f2","projectId","forkedBranchId","2beae49c-ed74-11f0-997b-42dde27851f2","forkedBranchVersion",88,"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},"fa460754-f099-11f0-93a9-42dde27851f2","fileId","019bb3fe-03ba-7068-ab27-4addfd2583d7","parentId","branchId","branchVersion",44,"latest",["D",1768320456796],12,"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\n\nconst COMPANIES_TABLE = \"github_leads_companies_v1\";\nconst LEADS_TABLE = \"github_leads_v4\";\n\nawait createTables();\n\n// ============================================================================\n// Types\n// ============================================================================\n\nexport interface Company {\n id: number;\n slug: string;\n github_org: string;\n display_name: string;\n prompt: string;\n recipients: string; // JSON array of email addresses\n active: number; // 1 = active, 0 = paused\n created_at: number;\n last_processed_at: number | null; // null = never processed, do full lookback\n last_events_fetched: number | null;\n last_new_events: number | null;\n last_new_leads: number | null;\n share_token: string | null; // for public share links\n}\n\nexport interface Lead {\n id: number;\n company_id: number;\n timestamp: number;\n input_data: string;\n output_data: string;\n}\n\n// ============================================================================\n// Company Operations\n// ============================================================================\n\nfunction generateToken(): string {\n const chars = \"abcdefghijkmnpqrstuvwxyz23456789\"; // no confusing chars\n let token = \"\";\n for (let i = 0; i < 12; i++) {\n token += chars[Math.floor(Math.random() * chars.length)];\n }\n return token;\n}\n\nexport async function createCompany(data: {\n slug: string;\n github_org: string;\n display_name: string;\n prompt: string;\n recipients?: string[];\n}): Promise {\n const result = await sqlite.execute(\n `INSERT INTO ${COMPANIES_TABLE} (slug, github_org, display_name, prompt, recipients, active, created_at, share_token)\n VALUES (?, ?, ?, ?, ?, 1, ?, ?)\n RETURNING *`,\n [\n data.slug,\n data.github_org,\n data.display_name,\n data.prompt,\n JSON.stringify(data.recipients || []),\n Date.now(),\n generateToken(),\n ],\n );\n return result.rows[0] as unknown as Company;\n}\n\nexport async function updateCompany(\n id: number,\n data: Partial<{\n slug: string;\n github_org: string;\n display_name: string;\n prompt: string;\n recipients: string[];\n active: boolean;\n }>,\n): Promise {\n const updates: string[] = [];\n const values: unknown[] = [];\n\n if (data.slug !== undefined) {\n updates.push(\"slug = ?\");\n values.push(data.slug);\n }\n if (data.github_org !== undefined) {\n updates.push(\"github_org = ?\");\n values.push(data.github_org);\n }\n if (data.display_name !== undefined) {\n updates.push(\"display_name = ?\");\n values.push(data.display_name);\n }\n if (data.prompt !== undefined) {\n updates.push(\"prompt = ?\");\n values.push(data.prompt);\n }\n if (data.recipients !== undefined) {\n updates.push(\"recipients = ?\");\n values.push(JSON.stringify(data.recipients));\n }\n if (data.active !== undefined) {\n updates.push(\"active = ?\");\n values.push(data.active ? 1 : 0);\n }\n\n if (updates.length === 0) return getCompanyById(id);\n\n values.push(id);\n const result = await sqlite.execute(\n `UPDATE ${COMPANIES_TABLE} SET ${updates.join(\", \")} WHERE id = ? RETURNING *`,\n values,\n );\n return result.rows[0] as unknown as Company || null;\n}\n\nexport async function deleteCompany(id: number): Promise {\n // Also delete all leads for this company\n await sqlite.execute(`DELETE FROM ${LEADS_TABLE} WHERE company_id = ?`, [id]);\n const result = await sqlite.execute(\n `DELETE FROM ${COMPANIES_TABLE} WHERE id = ?`,\n [id],\n );\n return result.rowsAffected > 0;\n}\n\nexport async function getAllCompanies(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${COMPANIES_TABLE} ORDER BY display_name ASC`,\n );\n return result.rows as unknown as Company[];\n}\n\nexport async function getActiveCompanies(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${COMPANIES_TABLE} WHERE active = 1 ORDER BY display_name ASC`,\n );\n return result.rows as unknown as Company[];\n}\n\nexport async function getCompanyById(id: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${COMPANIES_TABLE} WHERE id = ?`,\n [id],\n );\n return (result.rows[0] as unknown as Company) || null;\n}\n\nexport async function getCompanyBySlug(slug: string): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${COMPANIES_TABLE} WHERE slug = ?`,\n [slug],\n );\n return (result.rows[0] as unknown as Company) || null;\n}\n\nexport async function getCompanyByShareToken(\n slug: string,\n token: string,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${COMPANIES_TABLE} WHERE slug = ? AND share_token = ?`,\n [slug, token],\n );\n return (result.rows[0] as unknown as Company) || null;\n}\n\n/** Ensure company has a share token, generate one if missing */\nexport async function ensureShareToken(id: number): Promise {\n const company = await getCompanyById(id);\n if (!company) throw new Error(\"Company not found\");\n \n if (company.share_token) return company.share_token;\n \n const token = generateToken();\n await sqlite.execute(\n `UPDATE ${COMPANIES_TABLE} SET share_token = ? WHERE id = ?`,\n [token, id],\n );\n return token;\n}\n\n/** Regenerate share token (invalidates old links) */\nexport async function regenerateShareToken(id: number): Promise {\n const token = generateToken();\n await sqlite.execute(\n `UPDATE ${COMPANIES_TABLE} SET share_token = ? WHERE id = ?`,\n [token, id],\n );\n return token;\n}\n\nexport async function updateCompanyLastProcessed(\n id: number,\n stats?: { eventsFetched: number; newEvents: number; newLeads: number },\n): Promise {\n await sqlite.execute(\n `UPDATE ${COMPANIES_TABLE} \n SET last_processed_at = ?, last_events_fetched = ?, last_new_events = ?, last_new_leads = ?\n WHERE id = ?`,\n [\n Date.now(),\n stats?.eventsFetched ?? null,\n stats?.newEvents ?? null,\n stats?.newLeads ?? null,\n id,\n ],\n );\n}\n\nexport async function resetCompanyForRescan(id: number): Promise {\n await sqlite.execute(\n `UPDATE ${COMPANIES_TABLE} SET last_processed_at = NULL WHERE id = ?`,\n [id],\n );\n}\n\n// ============================================================================\n// Lead Operations (Company-Scoped)\n// ============================================================================\n\nexport async function storeLead(\n companyId: number,\n inputData: Record | Record[],\n outputData: Record,\n) {\n const inputArray = Array.isArray(inputData) ? inputData : [inputData];\n await sqlite.execute(\n `INSERT INTO ${LEADS_TABLE} (company_id, timestamp, input_data, output_data)\n VALUES (?, ?, ?, ?)`,\n [companyId, 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 ${LEADS_TABLE} SET output_data = ? WHERE id = ?`,\n [JSON.stringify(outputData), id],\n );\n}\n\nexport async function getLeadsByCompany(companyId: number): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${LEADS_TABLE} WHERE company_id = ? ORDER BY timestamp DESC`,\n [companyId],\n );\n return result.rows as unknown as Lead[];\n}\n\nexport async function getAllLeads(): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${LEADS_TABLE} 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 ${LEADS_TABLE} WHERE id = ?`,\n [id],\n );\n return (result.rows[0] as unknown as Lead) || null;\n}\n\nexport async function findLeadByInputPathAndCompany(\n path: string,\n value: string,\n companyId: number,\n): Promise {\n const leads = await getLeadsByCompany(companyId);\n return (\n leads.find((lead) => {\n const input = JSON.parse(lead.input_data);\n const firstEvent = Array.isArray(input) ? input[0] : input;\n return path.split(\".\").reduce((obj, key) => obj?.[key], firstEvent) === value;\n }) || null\n );\n}\n\nexport async function appendFollowup(\n id: number,\n data: Record | Record[],\n) {\n const lead = await getLeadById(id);\n if (!lead) return;\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 ${LEADS_TABLE} SET input_data = ? WHERE id = ?`,\n [JSON.stringify(events), id],\n );\n}\n\n// ============================================================================\n// Stats\n// ============================================================================\n\nexport async function getCompanyStats(\n companyId: number,\n): Promise<{ total: number; matches: number }> {\n const leads = await getLeadsByCompany(companyId);\n const matches = leads.filter((l) => {\n try {\n return JSON.parse(l.output_data).match === true;\n } catch {\n return false;\n }\n });\n return { total: leads.length, matches: matches.length };\n}\n\n/** Get qualified leads missing score (need reanalyzing after prompt update) */\nexport async function getQualifiedLeadsNeedingReanalyze(\n companyId: number,\n): Promise {\n const result = await sqlite.execute(\n `SELECT * FROM ${LEADS_TABLE} \n WHERE company_id = ? \n AND json_extract(output_data, '$.match') = true\n AND json_extract(output_data, '$.score') IS NULL\n ORDER BY timestamp DESC`,\n [companyId],\n );\n return result.rows as unknown as Lead[];\n}\n\n// ============================================================================\n// Export (for splitting off a company later)\n// ============================================================================\n\nexport async function exportCompanyData(companySlug: string): Promise<{\n company: Omit;\n leads: Array>;\n exported_at: number;\n}> {\n const company = await getCompanyBySlug(companySlug);\n if (!company) throw new Error(`Company \"${companySlug}\" not found`);\n\n const leads = await getLeadsByCompany(company.id);\n\n return {\n company: {\n slug: company.slug,\n github_org: company.github_org,\n display_name: company.display_name,\n prompt: company.prompt,\n recipients: company.recipients,\n active: company.active,\n created_at: company.created_at,\n },\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 };\n}\n\n// ============================================================================\n// Table Setup\n// ============================================================================\n\nasync function createTables() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${COMPANIES_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n slug TEXT UNIQUE NOT NULL,\n github_org TEXT NOT NULL,\n display_name TEXT NOT NULL,\n prompt TEXT NOT NULL,\n recipients TEXT DEFAULT '[]',\n active INTEGER DEFAULT 1,\n created_at INTEGER NOT NULL\n )\n `);\n\n // Add share_token column if it doesn't exist\n try {\n await sqlite.execute(\n `ALTER TABLE ${COMPANIES_TABLE} ADD COLUMN share_token TEXT`,\n );\n } catch {\n // Column already exists\n }\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${LEADS_TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n company_id INTEGER NOT NULL,\n timestamp INTEGER NOT NULL,\n input_data TEXT NOT NULL,\n output_data TEXT NOT NULL,\n FOREIGN KEY (company_id) REFERENCES ${COMPANIES_TABLE}(id)\n )\n `);\n}\n","deleted","label",[99],"permalink","https://www.val.town/x/dcm31/github-leads-multi-company/code/db.ts","extension","actionData","errors"]