[{"_1":2,"_188":-5,"_189":-5},"loaderData",{"_3":4,"_96":97},"root",{"_5":6,"_37":38,"_69":70},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20,"_36":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","isHigherTownieTier","ENV",{"_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":68},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","780097303e6e65120818a306fb5ef83d0e26f8d3","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",{"_71":72},"__internal_clerk_state",{"_73":74,"_75":48,"_76":77,"_78":77,"_79":20,"_80":77,"_81":77,"_82":77,"_83":77,"_84":85,"_94":20,"_95":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",{"_86":20,"_19":20,"_87":77,"_88":89,"_90":77,"_91":48,"_92":20,"_93":77},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_98":99,"_100":101,"_102":103,"_104":105,"_163":164,"_185":186,"_187":-5},"handle","u","name","hireping","path","src/db.ts","project",{"_106":107,"_100":101,"_108":109,"_110":-5,"_111":20,"_112":113,"_114":115,"_116":-5,"_12":117,"_118":-5,"_119":120,"_121":-5,"_122":-5,"_123":33,"_14":124,"_125":126,"_127":128,"_129":130,"_131":-5,"_132":133,"_134":135,"_136":135,"_137":135,"_138":135,"_139":133,"_140":133,"_23":141,"_144":145,"_149":150,"_162":20},"id","9606bff8-2822-11f0-af78-569c3dd06744","createdAt",["D",1746278945949],"deletedAt","legacyEnvVars","privacy","public","description","Tracks new AI jobs.","imageUrl","f49d108f-753c-451b-b8c9-e379c05ac651","pinnedFileId","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","b09ad86d-86e4-49ea-ad5c-79b397709aa9","tursoName","9606bff8282211f0af78569c3dd06744","tursoHostname","9606bff8282211f0af78569c3dd06744-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1775833291366],"pinned","userPerm","r","valPerm","rw","blobPerm","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_98":99,"_142":143},"profileImageUrl","https://images.clerk.dev/oauth_github/img_2OFNbcY0WHcifovSaLzP5H1R9Tg.png","org",{"_98":99,"_146":143,"_12":117,"_147":148},"avatar","tier","free","branch",{"_151":152,"_100":46,"_106":153,"_154":107,"_155":-5,"_156":-5,"_157":-5,"_158":-5,"_159":-5,"_160":101,"_161":99},"version",75,"9606fdba-2822-11f0-af78-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_106":165,"_100":166,"_167":168,"_169":170,"_171":153,"_172":173,"_174":120,"_108":175,"_151":176,"_177":178,"_179":180,"_181":20,"_182":-5,"_102":183},"a158c062-34de-11f1-8be4-42b51c65c3df","db.ts","fileId","edff6c18-342a-11f1-8448-42b51c65c3df","parentId","edb14da8-342a-11f1-8a03-42b51c65c3df","branchId","branchVersion",53,"latest",["D",1775826621925],6,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport type { Job } from \"./jobs.ts\";\n\nexport type SqliteClientLike = {\n execute(sql: string): Promise<{ rows: Array> }>;\n batch(statements: unknown[]): Promise;\n};\n\nconst BATCH_SIZE = 100;\nconst PAYLOAD_BATCH_SIZE = 10;\n\nasync function batchInChunks(\n sqliteClient: SqliteClientLike,\n statements: unknown[],\n label: string,\n) {\n for (let index = 0; index < statements.length; index += BATCH_SIZE) {\n const chunkNumber = Math.floor(index / BATCH_SIZE) + 1;\n const chunk = statements.slice(index, index + BATCH_SIZE);\n\n try {\n await sqliteClient.batch(chunk);\n } catch (error) {\n throw new Error(\n `${label} failed on chunk ${chunkNumber} (${chunk.length} statements): ${\n error instanceof Error ? error.message : String(error)\n }`,\n );\n }\n }\n}\n\nfunction buildMultiRowInsertStatement(\n table: string,\n columns: string[],\n rows: Array>,\n onConflictClause = \"\",\n) {\n const args: Record = {};\n const values = rows.map((row, rowIndex) => {\n const placeholders = columns.map((column) => {\n const key = `${column}_${rowIndex}`;\n args[key] = row[column];\n return `:${key}`;\n });\n\n return `(${placeholders.join(\", \")})`;\n });\n\n return {\n sql: `\n insert into ${table} (${columns.join(\", \")})\n values ${values.join(\",\\n\")}\n ${onConflictClause}\n `,\n args,\n };\n}\n\nexport async function ensureSchema(sqliteClient: SqliteClientLike = sqlite) {\n await sqliteClient.execute(`\n create table if not exists jobs (\n url text primary key,\n source text not null default 'unknown',\n last_seen_at text not null\n )\n `);\n\n await sqliteClient.execute(`\n create table if not exists archived_jobs (\n url text primary key,\n source text not null default 'unknown',\n archived_at text not null\n )\n `);\n\n await sqliteClient.execute(`\n create table if not exists job_observations (\n url text primary key,\n first_seen_at text not null,\n last_seen_at text not null\n )\n `);\n\n await sqliteClient.execute(`\n create table if not exists job_payloads (\n url text primary key,\n source text not null default 'unknown',\n raw_json text not null,\n captured_at text not null\n )\n `);\n}\n\nexport async function getExistingJobs(\n sqliteClient: SqliteClientLike = sqlite,\n): Promise {\n const result = await sqliteClient.execute(`\n select jobs.url, jobs.source, job_payloads.raw_json\n from jobs\n left join job_payloads on job_payloads.url = jobs.url\n `);\n\n return result.rows.map((row: Record) => ({\n url: String(row.url),\n raw_json: row.raw_json == null ? null : String(row.raw_json),\n source: row.source == null ? \"unknown\" : String(row.source),\n }));\n}\n\nexport async function syncJobs(\n jobs: Job[],\n syncedAt: string,\n sqliteClient: SqliteClientLike = sqlite,\n) {\n try {\n await sqliteClient.batch([{ sql: \"delete from jobs\" }]);\n } catch (error) {\n throw new Error(\n `syncJobs failed while clearing jobs table: ${\n error instanceof Error ? error.message : String(error)\n }`,\n );\n }\n\n const statements = [];\n for (let index = 0; index < jobs.length; index += BATCH_SIZE) {\n const chunk = jobs.slice(index, index + BATCH_SIZE).map((job) => ({\n url: job.url,\n source: job.source,\n last_seen_at: syncedAt,\n }));\n\n statements.push(\n buildMultiRowInsertStatement(\n \"jobs\",\n [\"url\", \"source\", \"last_seen_at\"],\n chunk,\n ),\n );\n }\n\n await batchInChunks(sqliteClient, statements, \"syncJobs\");\n}\n\nexport async function recordJobObservations(\n jobs: Job[],\n seenAt: string,\n sqliteClient: SqliteClientLike = sqlite,\n) {\n if (jobs.length === 0) {\n return;\n }\n\n const statements = [];\n for (let index = 0; index < jobs.length; index += BATCH_SIZE) {\n const chunk = jobs.slice(index, index + BATCH_SIZE).map((job) => ({\n url: job.url,\n first_seen_at: seenAt,\n last_seen_at: seenAt,\n }));\n\n statements.push(\n buildMultiRowInsertStatement(\n \"job_observations\",\n [\"url\", \"first_seen_at\", \"last_seen_at\"],\n chunk,\n `\n on conflict(url) do update set\n last_seen_at = excluded.last_seen_at\n `,\n ),\n );\n }\n\n await batchInChunks(sqliteClient, statements, \"recordJobObservations\");\n}\n\nexport async function archiveRemovedJobs(\n removedJobs: Job[],\n archivedAt: string,\n sqliteClient: SqliteClientLike = sqlite,\n) {\n if (removedJobs.length === 0) {\n return;\n }\n\n const statements = [];\n for (let index = 0; index < removedJobs.length; index += BATCH_SIZE) {\n const chunk = removedJobs.slice(index, index + BATCH_SIZE).map((job) => ({\n url: job.url,\n source: job.source,\n archived_at: archivedAt,\n }));\n\n statements.push(\n buildMultiRowInsertStatement(\n \"archived_jobs\",\n [\"url\", \"source\", \"archived_at\"],\n chunk,\n `\n on conflict(url) do update set\n source = excluded.source,\n archived_at = excluded.archived_at\n `,\n ),\n );\n }\n\n await batchInChunks(sqliteClient, statements, \"archiveRemovedJobs\");\n}\n\nexport async function storeNewJobPayloads(\n jobs: Job[],\n capturedAt: string,\n sqliteClient: SqliteClientLike = sqlite,\n) {\n const payloadJobs = jobs.filter((job) => job.raw_json != null);\n if (payloadJobs.length === 0) {\n return;\n }\n\n const statements = payloadJobs.map((job) => ({\n sql: `\n insert into job_payloads (url, source, raw_json, captured_at)\n values (:url, :source, :raw_json, :captured_at)\n on conflict(url) do update set\n source = excluded.source,\n raw_json = excluded.raw_json,\n captured_at = excluded.captured_at\n `,\n args: {\n url: job.url,\n source: job.source,\n raw_json: job.raw_json,\n captured_at: capturedAt,\n },\n }));\n\n for (let index = 0; index < statements.length; index += PAYLOAD_BATCH_SIZE) {\n const chunk = statements.slice(index, index + PAYLOAD_BATCH_SIZE);\n const chunkNumber = Math.floor(index / PAYLOAD_BATCH_SIZE) + 1;\n\n try {\n await sqliteClient.batch(chunk);\n } catch (error) {\n throw new Error(\n `storeNewJobPayloads failed on chunk ${chunkNumber} (${chunk.length} statements): ${\n error instanceof Error ? error.message : String(error)\n }`,\n );\n }\n }\n}\n","deleted","label",[184,166],"src","permalink","https://www.val.town/x/u/hireping/code/src/db.ts","extension","actionData","errors"]