[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_176":177,"_178":179},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_89":-7,"_90":91,"_29":-7,"_92":-5,"_15":93,"_94":95,"_96":97,"_98":99,"_100":101,"_102":-5,"_103":-5,"_104":-5,"_105":38,"_106":107,"_108":50,"_109":-5,"_110":-5,"_111":112},"routes/_app.x.$handle.$name.branches",{"_11":12,"_13":14,"_15":16,"_17":18},"handle","sawyeraltman","name","mini-ats","project",{"_20":28,"_13":14,"_22":36,"_35":-5,"_37":38,"_39":40,"_41":40,"_42":43,"_44":-5,"_45":46,"_47":48,"_49":50,"_51":-5,"_52":-5,"_53":54,"_55":56,"_57":-5,"_58":-5,"_59":-5,"_60":-5,"_61":62,"_63":62,"_64":65,"_66":65,"_67":65,"_68":62,"_69":62,"_70":71,"_72":73,"_74":75,"_76":38},"branches",[19],{"_20":21,"_22":23,"_24":25,"_13":26,"_27":28,"_29":30,"_31":-5,"_32":-5,"_33":34,"_35":-5},"id","5a7d57eb-74d9-48a3-b178-163a90d765c3","createdAt",["D",1784991101212],"updatedAt",["D",1787240606299],"main","projectId","04c0f7c6-4c1f-4b9e-aab6-d9c106c4ea6d","version",25,"forkedBranchId","forkedBranchVersion","lockfileKey","1787240606519-esm.town_v_sawyeraltman_mini-ats@25-main","deletedAt",["D",1784991101203],"legacyEnvVars",false,"privacy","public","httpPrivacy","description","Simple ATS for candidate tracking","imageUrl","userId","9f5b2c99-966e-4045-8d52-ec0106bbec23","pinnedFileId","5d23079c-8838-11f1-9b3d-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","d187c0c4-be62-436b-95a1-8e9a7449c340","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_11":12,"_88":84},"org",{"_11":12,"_82":-5,"_83":84,"_45":46,"_85":86,"_87":38},"branch",{"_29":30,"_13":26,"_20":21,"_27":28,"_31":-5,"_32":-5,"_77":-5,"_78":-5,"_79":-5,"_80":14,"_81":12},"isOwned","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18ybndOSkVHc0U0QnJqZkRma3pwMW9KSm44b3QifQ","tier","free","canRestrictVals","profileImageUrl","branchName","path","","adoptOrgId",{"_20":28,"_13":14,"_22":172,"_35":-5,"_37":38,"_39":40,"_41":40,"_42":43,"_44":-5,"_45":46,"_47":48,"_49":50,"_51":-5,"_52":-5,"_53":54,"_55":56,"_57":-5,"_58":-5,"_59":-5,"_60":-5,"_61":62,"_63":62,"_64":65,"_66":65,"_67":65,"_68":62,"_69":62,"_70":173,"_72":174,"_74":175,"_76":38},"tree",[159,160,161],"files",[159,160,161],"counts",{"_149":54,"_17":150,"_151":54,"_152":153,"_154":155},"contents",[113,114,115],"file","upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_20":141,"_13":142,"_118":143,"_120":-5,"_121":21,"_122":144,"_123":50,"_22":145,"_29":54,"_126":146,"_128":147,"_130":38,"_131":-5,"_90":148},{"_20":133,"_13":134,"_118":48,"_120":-5,"_121":21,"_122":135,"_123":50,"_22":136,"_29":137,"_126":138,"_128":139,"_130":38,"_131":-5,"_90":140},{"_20":116,"_13":117,"_118":119,"_120":-5,"_121":21,"_122":30,"_123":50,"_22":124,"_29":125,"_126":127,"_128":129,"_130":38,"_131":-5,"_90":132},"53e57657-5000-4df6-b7f7-4b754ec10b3d","ui.ts","fileId","272f4dc2-8904-11f1-8594-1607ee4eb77e","parentId","branchId","branchVersion","latest",["D",1787240606299],4,"fileType","script","content","// The single-page UI. It lives in its own file because val.town caps a file at 80k\n// characters, and the app plus this template is past that.\ntype PageConsts = {\n\tDEFAULT_STAGES: readonly string[];\n\tPEOPLE: readonly string[];\n\tGRADES: readonly string[];\n\tMEETINGS: readonly string[];\n\tLOST_REASONS: readonly string[];\n\tSOURCES: readonly string[];\n\tSTALE_DAYS: number;\n};\n\nexport const makePage = ({\n\tDEFAULT_STAGES,\n\tPEOPLE,\n\tGRADES,\n\tMEETINGS,\n\tLOST_REASONS,\n\tSOURCES,\n\tSTALE_DAYS,\n}: PageConsts) => `\n\n\n\n\nMini ATS\n\n\n\n

\n\t

Mini ATS

\n\t\n\t\n\t

\n\t\t\n\t\t

\n\t

Viewing as \n\t\t\n\t

\n\n

\n\n\n`;\n","deleted","label",[117],"e33e860f-b9b6-4e0d-aa73-529881f9f0fc","main.tsx",20,["D",1785078721161],18,"http","import { Hono } from \"npm:hono\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport { makePage } from \"./ui.ts\";\n\nconst DEFAULT_STAGES = [\"Applied\", \"Screening\", \"Interview\", \"Offer\", \"Hired\"] as const;\n\nconst LOST_REASONS = [\"Rejected\", \"They declined\", \"Ghosted\", \"Bad timing\", \"Needs remote\", \"Other\"] as const;\n\nconst PEOPLE = [\"Sawyer\", \"Alp\", \"Andy\"] as const;\n\nconst GRADES = [\"Strong yes\", \"Yes\", \"Neutral\", \"No\", \"Strong no\"] as const;\n\nconst MEETINGS = [\"In-person\", \"Video\", \"Phone\"] as const;\n\nconst SOURCES = [\"Referral\", \"Inbound\", \"Outbound\", \"Event\", \"Agency\", \"Other\"] as const;\n\n// A candidate who sits in one stage longer than this gets flagged in the pipeline.\nconst STALE_DAYS = 7;\n\nawait sqlite.batch([\n\t`CREATE TABLE IF NOT EXISTS ats_people (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\tname TEXT NOT NULL,\n\t\temail TEXT DEFAULT '',\n\t\tnotes TEXT DEFAULT '',\n\t\tcreated_at TEXT DEFAULT (datetime('now')),\n\t\tupdated_at TEXT DEFAULT (datetime('now'))\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_roles (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\ttitle TEXT NOT NULL,\n\t\tarchived INTEGER DEFAULT 0,\n\t\tcreated_at TEXT DEFAULT (datetime('now'))\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_applications (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\tperson_id INTEGER NOT NULL,\n\t\trole_id INTEGER NOT NULL,\n\t\tstage TEXT NOT NULL DEFAULT 'Applied',\n\t\towner TEXT DEFAULT '',\n\t\tgrade TEXT DEFAULT '',\n\t\tnotes TEXT DEFAULT '',\n\t\tcreated_at TEXT DEFAULT (datetime('now')),\n\t\tupdated_at TEXT DEFAULT (datetime('now')),\n\t\tUNIQUE(person_id, role_id)\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_stage_owners (\n\t\trole_id INTEGER NOT NULL,\n\t\tstage TEXT NOT NULL,\n\t\towner TEXT NOT NULL DEFAULT '',\n\t\tUNIQUE(role_id, stage)\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_role_stages (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\trole_id INTEGER NOT NULL,\n\t\tposition INTEGER NOT NULL,\n\t\tname TEXT NOT NULL,\n\t\tUNIQUE(role_id, name)\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_events (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\tperson_id INTEGER NOT NULL,\n\t\trole_id INTEGER,\n\t\tauthor TEXT DEFAULT '',\n\t\tkind TEXT NOT NULL DEFAULT 'note',\n\t\tbody TEXT NOT NULL,\n\t\tcreated_at TEXT DEFAULT (datetime('now'))\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_settings (\n\t\tkey TEXT PRIMARY KEY,\n\t\tvalue TEXT NOT NULL DEFAULT ''\n\t)`,\n\t// Granola charges a detail request per note, so every note we resolve is cached here\n\t// and never fetched twice.\n\t`CREATE TABLE IF NOT EXISTS ats_granola_notes (\n\t\tnote_id TEXT PRIMARY KEY,\n\t\tcalendar_event_id TEXT DEFAULT '',\n\t\tweb_url TEXT DEFAULT '',\n\t\ttitle TEXT DEFAULT '',\n\t\tattendees TEXT DEFAULT '',\n\t\tstarted_at TEXT DEFAULT '',\n\t\tfetched_at TEXT DEFAULT (datetime('now'))\n\t)`,\n\t`CREATE TABLE IF NOT EXISTS ats_sync_runs (\n\t\tid INTEGER PRIMARY KEY AUTOINCREMENT,\n\t\tran_at TEXT DEFAULT (datetime('now')),\n\t\tok INTEGER NOT NULL DEFAULT 1,\n\t\treport TEXT NOT NULL DEFAULT ''\n\t)`,\n]);\n\nfor (const statement of [\n\t`ALTER TABLE ats_events ADD COLUMN calendar_event_id TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_events ADD COLUMN granola_note_id TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_events ADD COLUMN granola_url TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_applications ADD COLUMN meeting TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_applications ADD COLUMN lost_reason TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_applications ADD COLUMN stage_since TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_people ADD COLUMN starred INTEGER DEFAULT 0`,\n\t`ALTER TABLE ats_people ADD COLUMN source TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_people ADD COLUMN source_detail TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_people ADD COLUMN linkedin TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_people ADD COLUMN github TEXT DEFAULT ''`,\n\t`ALTER TABLE ats_people ADD COLUMN website TEXT DEFAULT ''`,\n]) {\n\ttry {\n\t\tawait sqlite.execute(statement);\n\t} catch {\n\t\t// column already exists\n\t}\n}\n\n// stage_since drives the stale flag. Rows that predate the column fall back to their\n// last recorded move, then to when the application was created.\nawait sqlite.execute(`UPDATE ats_applications SET stage_since = COALESCE(\n\t(SELECT MAX(e.created_at) FROM ats_events e\n\t\tWHERE e.person_id = ats_applications.person_id\n\t\tAND e.role_id = ats_applications.role_id\n\t\tAND e.kind = 'system'\n\t\tAND e.body LIKE 'Moved from %'),\n\tcreated_at,\n\tdatetime('now')\n) WHERE stage_since IS NULL OR stage_since = ''`);\n\nconst getRoleStages = async (roleId: number) => {\n\tconst rs = await sqlite.execute({\n\t\tsql: \"SELECT name FROM ats_role_stages WHERE role_id = ? ORDER BY position\",\n\t\targs: [roleId],\n\t});\n\treturn rs.rows.map((r: any) => r[0] as string);\n};\n\nconst setRoleStages = async (roleId: number, names: readonly string[]) => {\n\tawait sqlite.execute({ sql: \"DELETE FROM ats_role_stages WHERE role_id = ?\", args: [roleId] });\n\tfor (const [i, name] of names.entries()) {\n\t\tawait sqlite.execute({\n\t\t\tsql: \"INSERT INTO ats_role_stages (role_id, position, name) VALUES (?, ?, ?)\",\n\t\t\targs: [roleId, i, name],\n\t\t});\n\t}\n};\n\n// Roles created before funnels were configurable keep the original five stages,\n// plus any stage name their applications already use.\nconst allRoles = await sqlite.execute(\"SELECT id FROM ats_roles\");\nfor (const row of allRoles.rows) {\n\tconst roleId = row[0] as number;\n\tif ((await getRoleStages(roleId)).length > 0) continue;\n\tconst used = await sqlite.execute({\n\t\tsql: \"SELECT DISTINCT stage FROM ats_applications WHERE role_id = ?\",\n\t\targs: [roleId],\n\t});\n\tconst extra = used.rows\n\t\t.map((r: any) => r[0] as string)\n\t\t.filter((s) => s && !DEFAULT_STAGES.includes(s as any));\n\tawait setRoleStages(roleId, [...DEFAULT_STAGES, ...extra]);\n}\n\nconst app = new Hono();\n\napp.use(\"*\", async (c, next) => {\n\tawait next();\n\tc.header(\"cache-control\", \"no-store\");\n});\n\n// This val is public and its source is readable by anyone, so the passphrase lives in\n// val.town environment settings and never in this file.\nconst PASSPHRASE = Deno.env.get(\"ATS_PASSPHRASE\") ?? \"\";\n\nconst SESSION_COOKIE = \"ats_session\";\n\nconst SESSION_MAX_AGE = 30 * 24 * 60 * 60;\n\nconst encoder = new TextEncoder();\n\nconst sign = async (message: string) => {\n\tconst key = await crypto.subtle.importKey(\n\t\t\"raw\",\n\t\tencoder.encode(PASSPHRASE),\n\t\t{ name: \"HMAC\", hash: \"SHA-256\" },\n\t\tfalse,\n\t\t[\"sign\"],\n\t);\n\tconst mac = await crypto.subtle.sign(\"HMAC\", key, encoder.encode(message));\n\treturn [...new Uint8Array(mac)].map((b) => b.toString(16).padStart(2, \"0\")).join(\"\");\n};\n\nconst equals = (a: string, b: string) => {\n\tif (a.length !== b.length) return false;\n\tlet diff = 0;\n\tfor (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);\n\treturn diff === 0;\n};\n\nconst makeToken = async () => {\n\tconst expiry = String(Date.now() + SESSION_MAX_AGE * 1000);\n\treturn `${expiry}.${await sign(expiry)}`;\n};\n\nconst isValidToken = async (token: string) => {\n\tconst [expiry, mac] = token.split(\".\");\n\tif (!expiry || !mac || !/^\\d+$/.test(expiry)) return false;\n\tif (Number(expiry) < Date.now()) return false;\n\treturn equals(mac, await sign(expiry));\n};\n\nconst setSession = (c: any, token: string) =>\n\tc.header(\n\t\t\"set-cookie\",\n\t\t`${SESSION_COOKIE}=${token}; Max-Age=${SESSION_MAX_AGE}; HttpOnly; Secure; SameSite=Lax; Path=/`,\n\t\t{ append: true },\n\t);\n\nconst readCookie = (c: any) =>\n\t(c.req.header(\"cookie\") ?? \"\")\n\t\t.split(\";\")\n\t\t.map((part: string) => part.trim())\n\t\t.find((part: string) => part.startsWith(`${SESSION_COOKIE}=`))\n\t\t?.slice(SESSION_COOKIE.length + 1) ?? \"\";\n\nconst SETUP_PAGE = `\nMini ATS -- setup\n\n

Mini ATS is locked

No ATS_PASSPHRASE is set, so the app will not serve any data.

Open the val on val.town, go to Environment variables, add ATS_PASSPHRASE with the shared passphrase, then reload this page.

\n`;\n\nconst LOGIN_PAGE = `\n\nMini ATS\n\n\n\n`;\n\napp.post(\"/api/login\", async (c) => {\n\tif (!PASSPHRASE) return c.json({ error: \"not configured\" }, 503);\n\tconst body = await c.req.json().catch(() => ({}));\n\tif (!equals(String(body.passphrase ?? \"\"), PASSPHRASE)) {\n\t\treturn c.json({ error: \"wrong passphrase\" }, 401);\n\t}\n\tsetSession(c, await makeToken());\n\treturn c.json({ ok: true });\n});\n\napp.post(\"/api/logout\", (c) => {\n\tc.header(\"set-cookie\", `${SESSION_COOKIE}=; Max-Age=0; HttpOnly; Secure; SameSite=Lax; Path=/`);\n\treturn c.json({ ok: true });\n});\n\napp.use(\"*\", async (c, next) => {\n\tif (!PASSPHRASE) return c.html(SETUP_PAGE, 503);\n\t// The scheduled sync val has no cookie jar, so it presents the passphrase directly.\n\tconst bearer = (c.req.header(\"authorization\") ?? \"\").replace(/^Bearer\\s+/i, \"\");\n\tif (bearer && equals(bearer, PASSPHRASE)) return next();\n\tif (await isValidToken(readCookie(c))) {\n\t\t// Sliding window: an active device pushes its expiry out and never sees the\n\t\t// login page again.\n\t\tsetSession(c, await makeToken());\n\t\treturn next();\n\t}\n\tif (c.req.path.startsWith(\"/api/\")) return c.json({ error: \"unauthorized\" }, 401);\n\treturn c.html(LOGIN_PAGE, 401);\n});\n\nconst asPerson = (r: any) => ({\n\tid: r[0],\n\tname: r[1],\n\temail: r[2],\n\tnotes: r[3],\n\tstarred: !!r[4],\n\tsource: r[5] ?? \"\",\n\tsource_detail: r[6] ?? \"\",\n\tlinkedin: r[7] ?? \"\",\n\tgithub: r[8] ?? \"\",\n\twebsite: r[9] ?? \"\",\n});\n\nconst asRole = (r: any) => ({ id: r[0], title: r[1], archived: !!r[2] });\n\nconst logEvent = async (e: {\n\tperson_id: number;\n\trole_id?: number | null;\n\tauthor?: string;\n\tkind: \"note\" | \"system\";\n\tbody: string;\n}) => {\n\tawait sqlite.execute({\n\t\tsql: \"INSERT INTO ats_events (person_id, role_id, author, kind, body) VALUES (?, ?, ?, ?, ?)\",\n\t\targs: [e.person_id, e.role_id ?? null, PEOPLE.includes(e.author as any) ? e.author : \"\", e.kind, e.body],\n\t});\n};\n\nconst getRoleTitle = async (roleId: number) => {\n\tconst rs = await sqlite.execute({ sql: \"SELECT title FROM ats_roles WHERE id = ?\", args: [roleId] });\n\treturn (rs.rows[0]?.[0] as string) ?? \"?\";\n};\n\nconst asApplication = (r: any) => ({\n\tid: r[0],\n\tperson_id: r[1],\n\trole_id: r[2],\n\tstage: r[3],\n\towner: r[4],\n\tgrade: r[5],\n\tnotes: r[6],\n\tmeeting: r[7],\n\tlost_reason: r[8],\n\tstage_since: r[9],\n});\n\napp.get(\"/api/state\", async (c) => {\n\tconst [people, roles, applications, stageOwners, events, roleStages] = await sqlite.batch([\n\t\t\"SELECT id, name, email, notes, starred, source, source_detail, linkedin, github, website FROM ats_people ORDER BY name\",\n\t\t\"SELECT id, title, archived FROM ats_roles ORDER BY created_at\",\n\t\t\"SELECT id, person_id, role_id, stage, owner, grade, notes, meeting, lost_reason, stage_since FROM ats_applications ORDER BY updated_at DESC\",\n\t\t\"SELECT role_id, stage, owner FROM ats_stage_owners\",\n\t\t\"SELECT id, person_id, role_id, author, kind, body, created_at, granola_url FROM ats_events ORDER BY id DESC\",\n\t\t\"SELECT role_id, position, name FROM ats_role_stages ORDER BY role_id, position\",\n\t]);\n\treturn c.json({\n\t\tpeople: people.rows.map(asPerson),\n\t\troles: roles.rows.map(asRole),\n\t\tapplications: applications.rows.map(asApplication),\n\t\tstage_owners: stageOwners.rows.map((r: any) => ({ role_id: r[0], stage: r[1], owner: r[2] })),\n\t\trole_stages: roleStages.rows.map((r: any) => ({ role_id: r[0], position: r[1], name: r[2] })),\n\t\tevents: events.rows.map((r: any) => ({\n\t\t\tid: r[0],\n\t\t\tperson_id: r[1],\n\t\t\trole_id: r[2],\n\t\t\tauthor: r[3],\n\t\t\tkind: r[4],\n\t\t\tbody: r[5],\n\t\t\tcreated_at: r[6],\n\t\t\tgranola_url: r[7] ?? \"\",\n\t\t})),\n\t});\n});\n\napp.put(\"/api/stage-owners\", async (c) => {\n\tconst b = await c.req.json();\n\tconst roleId = Number(b.role_id);\n\tif (!roleId || !(await getRoleStages(roleId)).includes(b.stage)) return c.json({ error: \"bad input\" }, 400);\n\tif (b.owner !== \"\" && !PEOPLE.includes(b.owner)) return c.json({ error: \"bad owner\" }, 400);\n\tawait sqlite.execute({\n\t\tsql: \"INSERT INTO ats_stage_owners (role_id, stage, owner) VALUES (?, ?, ?) ON CONFLICT(role_id, stage) DO UPDATE SET owner = excluded.owner\",\n\t\targs: [roleId, b.stage, b.owner],\n\t});\n\treturn c.json({ ok: true });\n});\n\napp.post(\"/api/roles\", async (c) => {\n\tconst b = await c.req.json();\n\tif (!b.title?.trim()) return c.json({ error: \"title required\" }, 400);\n\tconst rs = await sqlite.execute({\n\t\tsql: \"INSERT INTO ats_roles (title) VALUES (?) RETURNING id\",\n\t\targs: [b.title.trim()],\n\t});\n\tconst roleId = rs.rows[0][0] as number;\n\tconst seed = Array.isArray(b.stages) && b.stages.length > 0 ? b.stages : DEFAULT_STAGES;\n\tawait setRoleStages(roleId, seed);\n\treturn c.json({ id: roleId }, 201);\n});\n\napp.put(\"/api/roles/:id/stages\", async (c) => {\n\tconst roleId = Number(c.req.param(\"id\"));\n\tconst b = await c.req.json();\n\tconst incoming: { name: string; was?: string }[] = Array.isArray(b.stages) ? b.stages : [];\n\tconst stages = incoming\n\t\t.map((s) => ({ name: String(s.name ?? \"\").trim(), was: s.was ? String(s.was) : undefined }))\n\t\t.filter((s) => s.name);\n\tif (stages.length === 0) return c.json({ error: \"a funnel needs at least one stage\" }, 400);\n\tconst lowered = stages.map((s) => s.name.toLowerCase());\n\tif (new Set(lowered).size !== lowered.length) return c.json({ error: \"stage names must be unique\" }, 400);\n\n\tconst current = await getRoleStages(roleId);\n\tif (current.length === 0) return c.json({ error: \"unknown role\" }, 404);\n\n\tfor (const s of stages) {\n\t\tif (!s.was || s.was === s.name) continue;\n\t\tawait sqlite.execute({\n\t\t\tsql: \"UPDATE ats_applications SET stage = ? WHERE role_id = ? AND stage = ?\",\n\t\t\targs: [s.name, roleId, s.was],\n\t\t});\n\t\tawait sqlite.execute({\n\t\t\tsql: \"UPDATE ats_stage_owners SET stage = ? WHERE role_id = ? AND stage = ?\",\n\t\t\targs: [s.name, roleId, s.was],\n\t\t});\n\t}\n\n\tconst kept = new Set(stages.map((s) => s.was ?? s.name));\n\tconst moves = (b.moves ?? {}) as Record;\n\tconst names = stages.map((s) => s.name);\n\tfor (const gone of current.filter((n) => !kept.has(n))) {\n\t\tconst target = names.includes(moves[gone]) ? moves[gone] : names[0];\n\t\tawait sqlite.execute({\n\t\t\tsql: \"UPDATE ats_applications SET stage = ? WHERE role_id = ? AND stage = ?\",\n\t\t\targs: [target, roleId, gone],\n\t\t});\n\t\tawait sqlite.execute({\n\t\t\tsql: \"DELETE FROM ats_stage_owners WHERE role_id = ? AND stage = ?\",\n\t\t\targs: [roleId, gone],\n\t\t});\n\t}\n\n\tawait setRoleStages(roleId, names);\n\treturn c.json({ ok: true, stages: names });\n});\n\napp.patch(\"/api/roles/:id\", async (c) => {\n\tconst b = await c.req.json();\n\tconst fields: string[] = [];\n\tconst args: unknown[] = [];\n\tif (typeof b.title === \"string\" && b.title.trim()) {\n\t\tfields.push(\"title = ?\");\n\t\targs.push(b.title.trim());\n\t}\n\tif (b.archived !== undefined) {\n\t\tfields.push(\"archived = ?\");\n\t\targs.push(b.archived ? 1 : 0);\n\t}\n\tif (fields.length === 0) return c.json({ error: \"no fields\" }, 400);\n\targs.push(Number(c.req.param(\"id\")));\n\tawait sqlite.execute({ sql: `UPDATE ats_roles SET ${fields.join(\", \")} WHERE id = ?`, args });\n\treturn c.json({ ok: true });\n});\n\nconst findOrCreatePerson = async (person: { name?: string; email?: string }) => {\n\tconst name = person.name?.trim() ?? \"\";\n\tconst email = person.email?.trim() ?? \"\";\n\tif (!name) return null;\n\tconst existing = await sqlite.execute({\n\t\tsql: \"SELECT id FROM ats_people WHERE (email != '' AND email = ?) OR lower(name) = lower(?) LIMIT 1\",\n\t\targs: [email, name],\n\t});\n\tif (existing.rows.length > 0) return existing.rows[0][0] as number;\n\tconst rs = await sqlite.execute({\n\t\tsql: \"INSERT INTO ats_people (name, email) VALUES (?, ?) RETURNING id\",\n\t\targs: [name, email],\n\t});\n\treturn rs.rows[0][0] as number;\n};\n\napp.post(\"/api/people\", async (c) => {\n\tconst b = await c.req.json();\n\tconst id = await findOrCreatePerson(b);\n\tif (id === null) return c.json({ error: \"name required\" }, 400);\n\treturn c.json({ id }, 201);\n});\n\n// People type \"sawyeraltman\" or \"linkedin.com/in/x\" as often as a full URL.\nconst normalizeLink = (kind: \"linkedin\" | \"github\" | \"website\", raw: string) => {\n\tconst value = raw.trim();\n\tif (!value) return \"\";\n\tif (/^https?:\\/\\//i.test(value)) return value;\n\tconst bare = value.replace(/^\\/+/, \"\");\n\tif (!value.includes(\".\")) {\n\t\tif (kind === \"github\") return `https://github.com/${bare}`;\n\t\tif (kind === \"linkedin\") return `https://linkedin.com/in/${bare.replace(/^in\\//, \"\")}`;\n\t}\n\treturn `https://${bare}`;\n};\n\napp.patch(\"/api/people/:id\", async (c) => {\n\tconst b = await c.req.json();\n\tconst id = Number(c.req.param(\"id\"));\n\tconst fields: string[] = [];\n\tconst args: unknown[] = [];\n\tfor (const key of [\"name\", \"email\", \"notes\", \"source_detail\"]) {\n\t\tif (typeof b[key] === \"string\") {\n\t\t\tfields.push(`${key} = ?`);\n\t\t\targs.push(b[key]);\n\t\t}\n\t}\n\tfor (const key of [\"linkedin\", \"github\", \"website\"] as const) {\n\t\tif (typeof b[key] === \"string\") {\n\t\t\tfields.push(`${key} = ?`);\n\t\t\targs.push(normalizeLink(key, b[key]));\n\t\t}\n\t}\n\tif (typeof b.source === \"string\") {\n\t\tif (b.source !== \"\" && !SOURCES.includes(b.source as any)) return c.json({ error: \"bad source\" }, 400);\n\t\tfields.push(\"source = ?\");\n\t\targs.push(b.source);\n\t}\n\tif (b.starred !== undefined) {\n\t\tfields.push(\"starred = ?\");\n\t\targs.push(b.starred ? 1 : 0);\n\t\tawait logEvent({ person_id: id, author: b.author, kind: \"system\", body: b.starred ? \"Starred\" : \"Unstarred\" });\n\t}\n\tif (fields.length === 0) return c.json({ error: \"no fields\" }, 400);\n\tfields.push(\"updated_at = datetime('now')\");\n\targs.push(id);\n\tawait sqlite.execute({ sql: `UPDATE ats_people SET ${fields.join(\", \")} WHERE id = ?`, args });\n\treturn c.json({ ok: true });\n});\n\napp.delete(\"/api/people/:id\", async (c) => {\n\tconst id = Number(c.req.param(\"id\"));\n\tawait sqlite.execute({ sql: \"DELETE FROM ats_applications WHERE person_id = ?\", args: [id] });\n\tawait sqlite.execute({ sql: \"DELETE FROM ats_events WHERE person_id = ?\", args: [id] });\n\tawait sqlite.execute({ sql: \"DELETE FROM ats_people WHERE id = ?\", args: [id] });\n\treturn c.json({ ok: true });\n});\n\napp.post(\"/api/applications\", async (c) => {\n\tconst b = await c.req.json();\n\tconst roleId = Number(b.role_id);\n\tif (!roleId) return c.json({ error: \"role_id required\" }, 400);\n\tlet personId = b.person_id ? Number(b.person_id) : null;\n\tif (!personId) personId = await findOrCreatePerson(b.person ?? {});\n\tif (!personId) return c.json({ error: \"person required\" }, 400);\n\tconst owner = PEOPLE.includes(b.owner) ? b.owner : \"\";\n\tconst roleStages = await getRoleStages(roleId);\n\tif (roleStages.length === 0) return c.json({ error: \"unknown role\" }, 404);\n\tconst stage = roleStages.includes(b.stage) ? b.stage : roleStages[0];\n\tconst grade = GRADES.includes(b.grade) ? b.grade : \"\";\n\tconst notes = typeof b.notes === \"string\" ? b.notes : \"\";\n\tconst lostReason = LOST_REASONS.includes(b.lost_reason) ? b.lost_reason : \"\";\n\ttry {\n\t\tawait sqlite.execute({\n\t\t\tsql: \"INSERT INTO ats_applications (person_id, role_id, stage, owner, grade, notes, lost_reason, stage_since) VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))\",\n\t\t\targs: [personId, roleId, stage, owner, grade, notes, lostReason],\n\t\t});\n\t} catch {\n\t\treturn c.json({ error: \"already in this role\" }, 409);\n\t}\n\tconst title = await getRoleTitle(roleId);\n\tawait logEvent({\n\t\tperson_id: personId,\n\t\trole_id: roleId,\n\t\tauthor: b.author,\n\t\tkind: \"system\",\n\t\tbody: `Added to ${title} in ${stage}`,\n\t});\n\treturn c.json({ ok: true }, 201);\n});\n\napp.post(\"/api/events\", async (c) => {\n\tconst b = await c.req.json();\n\tconst personId = Number(b.person_id);\n\tconst body = typeof b.body === \"string\" ? b.body.trim() : \"\";\n\tif (!personId || !body) return c.json({ error: \"person_id and body required\" }, 400);\n\tawait logEvent({\n\t\tperson_id: personId,\n\t\trole_id: b.role_id ? Number(b.role_id) : null,\n\t\tauthor: b.author,\n\t\tkind: \"note\",\n\t\tbody,\n\t});\n\treturn c.json({ ok: true }, 201);\n});\n\napp.delete(\"/api/events/:id\", async (c) => {\n\tawait sqlite.execute({ sql: \"DELETE FROM ats_events WHERE id = ?\", args: [Number(c.req.param(\"id\"))] });\n\treturn c.json({ ok: true });\n});\n\nconst patchApplication = async (appId: number, b: any) => {\n\tconst before = await sqlite.execute({\n\t\tsql: \"SELECT person_id, role_id, stage, grade, meeting, lost_reason FROM ats_applications WHERE id = ?\",\n\t\targs: [appId],\n\t});\n\tif (before.rows.length === 0) return { status: 404, body: { error: \"not found\" } };\n\tconst prev = {\n\t\tperson_id: before.rows[0][0] as number,\n\t\trole_id: before.rows[0][1] as number,\n\t\tstage: (before.rows[0][2] as string) ?? \"\",\n\t\tgrade: (before.rows[0][3] as string) ?? \"\",\n\t\tmeeting: (before.rows[0][4] as string) ?? \"\",\n\t\tlost_reason: (before.rows[0][5] as string) ?? \"\",\n\t};\n\tconst fields: string[] = [];\n\tconst args: unknown[] = [];\n\tlet restartStageClock = false;\n\tif (b.stage !== undefined) {\n\t\tif (!(await getRoleStages(prev.role_id)).includes(b.stage)) return { status: 400, body: { error: \"bad stage\" } };\n\t\tfields.push(\"stage = ?\");\n\t\targs.push(b.stage);\n\t\tif (b.stage !== prev.stage) restartStageClock = true;\n\t}\n\tif (b.owner !== undefined) {\n\t\tif (b.owner !== \"\" && !PEOPLE.includes(b.owner)) return { status: 400, body: { error: \"bad owner\" } };\n\t\tfields.push(\"owner = ?\");\n\t\targs.push(b.owner);\n\t}\n\tif (b.grade !== undefined) {\n\t\tif (b.grade !== \"\" && !GRADES.includes(b.grade)) return { status: 400, body: { error: \"bad grade\" } };\n\t\tfields.push(\"grade = ?\");\n\t\targs.push(b.grade);\n\t}\n\tif (b.meeting !== undefined) {\n\t\tif (b.meeting !== \"\" && !MEETINGS.includes(b.meeting)) return { status: 400, body: { error: \"bad meeting\" } };\n\t\tfields.push(\"meeting = ?\");\n\t\targs.push(b.meeting);\n\t}\n\tif (b.lost_reason !== undefined) {\n\t\tif (b.lost_reason !== \"\" && !LOST_REASONS.includes(b.lost_reason)) {\n\t\t\treturn { status: 400, body: { error: \"bad lost_reason\" } };\n\t\t}\n\t\tfields.push(\"lost_reason = ?\");\n\t\targs.push(b.lost_reason);\n\t\t// Someone brought back into the funnel starts a fresh clock.\n\t\tif (b.lost_reason === \"\" && prev.lost_reason) restartStageClock = true;\n\t}\n\tif (typeof b.notes === \"string\") {\n\t\tfields.push(\"notes = ?\");\n\t\targs.push(b.notes);\n\t}\n\tif (fields.length === 0) return { status: 400, body: { error: \"no fields\" } };\n\tif (restartStageClock) fields.push(\"stage_since = datetime('now')\");\n\tfields.push(\"updated_at = datetime('now')\");\n\targs.push(appId);\n\tawait sqlite.execute({ sql: `UPDATE ats_applications SET ${fields.join(\", \")} WHERE id = ?`, args });\n\n\tconst title = await getRoleTitle(prev.role_id);\n\tconst changes: string[] = [];\n\tif (b.stage !== undefined && b.stage !== prev.stage) changes.push(`Moved from ${prev.stage} to ${b.stage} in ${title}`);\n\tif (b.lost_reason !== undefined && b.lost_reason !== prev.lost_reason) {\n\t\tchanges.push(b.lost_reason ? `Out of funnel for ${title}: ${b.lost_reason}` : `Back in the funnel for ${title}`);\n\t}\n\tif (b.grade !== undefined && b.grade !== prev.grade) {\n\t\tchanges.push(b.grade ? `Graded ${b.grade} for ${title}` : `Cleared grade for ${title}`);\n\t}\n\tif (b.meeting !== undefined && b.meeting !== prev.meeting && b.meeting) changes.push(`Met ${b.meeting} for ${title}`);\n\tif (b.owner !== undefined && b.owner !== \"\") changes.push(`Assigned to ${b.owner} for ${title}`);\n\tfor (const body of changes) {\n\t\tawait logEvent({ person_id: prev.person_id, role_id: prev.role_id, author: b.author, kind: \"system\", body });\n\t}\n\treturn { status: 200, body: { ok: true } };\n};\n\napp.patch(\"/api/applications/:id\", async (c) => {\n\tconst result = await patchApplication(Number(c.req.param(\"id\")), await c.req.json());\n\treturn c.json(result.body, result.status as any);\n});\n\napp.post(\"/api/applications/bulk\", async (c) => {\n\tconst b = await c.req.json();\n\tconst ids = (Array.isArray(b.ids) ? b.ids : []).map(Number).filter((n: number) => Number.isFinite(n) && n > 0);\n\tif (ids.length === 0) return c.json({ error: \"ids required\" }, 400);\n\tconst patch: Record = { author: b.author };\n\tfor (const key of [\"stage\", \"lost_reason\", \"owner\", \"grade\"]) {\n\t\tif (b[key] !== undefined) patch[key] = b[key];\n\t}\n\tif (Object.keys(patch).length === 1) return c.json({ error: \"nothing to change\" }, 400);\n\tconst failed: number[] = [];\n\tlet changed = 0;\n\tfor (const id of ids) {\n\t\tconst result = await patchApplication(id, patch);\n\t\tif (result.status === 200) changed++;\n\t\telse failed.push(id);\n\t}\n\treturn c.json({ ok: true, changed, failed });\n});\n\napp.delete(\"/api/applications/:id\", async (c) => {\n\tawait sqlite.execute({\n\t\tsql: \"DELETE FROM ats_applications WHERE id = ?\",\n\t\targs: [Number(c.req.param(\"id\"))],\n\t});\n\treturn c.json({ ok: true });\n});\n\n// Anyone on these domains is us, not a candidate.\nconst INTERNAL_DOMAINS = (Deno.env.get(\"ATS_INTERNAL_DOMAINS\") ?? \"goblinsapp.com\")\n\t.split(\",\")\n\t.map((d) => d.trim().toLowerCase())\n\t.filter(Boolean);\n\n// How far back the calendar feed is read on each run. Google serves a wide window in one\n// response, so this only bounds how much we bother to look at.\nconst SYNC_LOOKBACK_DAYS = 60;\n\nconst SYNC_LOOKAHEAD_DAYS = 14;\n\n// Each unresolved Granola note costs one detail request. Resolved notes are cached, so a\n// steady-state run does far fewer than this.\nconst GRANOLA_FETCH_BUDGET = 40;\n\nconst getSetting = async (key: string) => {\n\tconst rs = await sqlite.execute({ sql: \"SELECT value FROM ats_settings WHERE key = ?\", args: [key] });\n\treturn (rs.rows[0]?.[0] as string) ?? \"\";\n};\n\nconst setSetting = async (key: string, value: string) =>\n\tsqlite.execute({\n\t\tsql: \"INSERT INTO ats_settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value\",\n\t\targs: [key, value],\n\t});\n\n// Folded ICS lines continue with a leading space or tab.\nconst unfoldIcs = (text: string) => text.replace(/\\r?\\n[ \\t]/g, \"\");\n\nconst unescapeIcsText = (value: string) =>\n\tvalue.replace(/\\\\n/gi, \"\\n\").replace(/\\\\([,;\\\\])/g, \"$1\").trim();\n\nconst parseIcsLine = (line: string) => {\n\tlet quoted = false;\n\tfor (let i = 0; i < line.length; i++) {\n\t\tconst ch = line[i];\n\t\tif (ch === '\"') quoted = !quoted;\n\t\telse if (ch === \":\" && !quoted) {\n\t\t\tconst [name, ...params] = line.slice(0, i).split(\";\");\n\t\t\treturn { name: name.toUpperCase(), params, value: line.slice(i + 1) };\n\t\t}\n\t}\n\treturn null;\n};\n\nconst paramValue = (params: readonly string[], key: string) => {\n\tconst hit = params.find((p) => p.toUpperCase().startsWith(`${key}=`));\n\tif (!hit) return \"\";\n\treturn unescapeIcsText(hit.slice(key.length + 1).replace(/^\"|\"$/g, \"\"));\n};\n\n// Google emits UTC stamps in its ICS feeds. A TZID-qualified or floating time is kept as\n// wall-clock, which is close enough: times are only used for display and day matching.\nconst parseIcsDate = (value: string) => {\n\tconst bare = value.trim();\n\tconst dateOnly = /^(\\d{4})(\\d{2})(\\d{2})$/.exec(bare);\n\tif (dateOnly) return `${dateOnly[1]}-${dateOnly[2]}-${dateOnly[3]}T00:00:00Z`;\n\tconst stamp = /^(\\d{4})(\\d{2})(\\d{2})T(\\d{2})(\\d{2})(\\d{2})Z?$/.exec(bare);\n\tif (!stamp) return \"\";\n\treturn `${stamp[1]}-${stamp[2]}-${stamp[3]}T${stamp[4]}:${stamp[5]}:${stamp[6]}Z`;\n};\n\nconst isInternal = (email: string) => {\n\tconst domain = email.split(\"@\")[1]?.toLowerCase() ?? \"\";\n\treturn INTERNAL_DOMAINS.includes(domain);\n};\n\n// \"jane.doe@x.com\" reads better as \"Jane Doe\" than as a raw address, and the ATS needs a\n// name to create a person at all.\nconst nameFromEmail = (email: string) =>\n\temail\n\t\t.split(\"@\")[0]\n\t\t.split(/[._-]+/)\n\t\t.filter(Boolean)\n\t\t.map((part) => part.charAt(0).toUpperCase() + part.slice(1))\n\t\t.join(\" \") || email;\n\ntype CalendarEvent = {\n\tuid: string;\n\teventId: string;\n\ttitle: string;\n\tstart: string;\n\torganizer: string;\n\tguests: { name: string; email: string }[];\n};\n\nconst parseIcs = (text: string): CalendarEvent[] => {\n\tconst events: CalendarEvent[] = [];\n\tlet current: CalendarEvent | null = null;\n\tfor (const line of unfoldIcs(text).split(/\\r?\\n/)) {\n\t\tif (line === \"BEGIN:VEVENT\") {\n\t\t\tcurrent = { uid: \"\", eventId: \"\", title: \"\", start: \"\", organizer: \"\", guests: [] };\n\t\t\tcontinue;\n\t\t}\n\t\tif (line === \"END:VEVENT\") {\n\t\t\tif (current?.uid) events.push(current);\n\t\t\tcurrent = null;\n\t\t\tcontinue;\n\t\t}\n\t\tif (!current) continue;\n\t\tconst parsed = parseIcsLine(line);\n\t\tif (!parsed) continue;\n\t\tif (parsed.name === \"UID\") {\n\t\t\tcurrent.uid = parsed.value.trim();\n\t\t\t// Granola stores the bare Google event id, without the \"@google.com\" suffix.\n\t\t\tcurrent.eventId = current.uid.replace(/@google\\.com$/i, \"\");\n\t\t} else if (parsed.name === \"SUMMARY\") {\n\t\t\tcurrent.title = unescapeIcsText(parsed.value);\n\t\t} else if (parsed.name === \"DTSTART\") {\n\t\t\tcurrent.start = parseIcsDate(parsed.value);\n\t\t} else if (parsed.name === \"ORGANIZER\") {\n\t\t\tcurrent.organizer = parsed.value.replace(/^mailto:/i, \"\").trim().toLowerCase();\n\t\t} else if (parsed.name === \"ATTENDEE\") {\n\t\t\tconst email = parsed.value.replace(/^mailto:/i, \"\").trim().toLowerCase();\n\t\t\tif (email.includes(\"@\")) {\n\t\t\t\tcurrent.guests.push({ name: paramValue(parsed.params, \"CN\"), email });\n\t\t\t}\n\t\t}\n\t}\n\treturn events;\n};\n\nconst fetchCalendar = async () => {\n\tconst configured = Deno.env.get(\"ATS_CALENDAR_ICS\") ?? \"\";\n\tif (!configured) throw new Error(\"ATS_CALENDAR_ICS is not set\");\n\tconst url = configured.trim().replace(/^webcal:\\/\\//i, \"https://\");\n\tconst r = await fetch(url, { headers: { accept: \"text/calendar\" } });\n\tif (!r.ok) throw new Error(`calendar feed returned ${r.status}`);\n\treturn parseIcs(await r.text());\n};\n\nconst granolaKey = () => Deno.env.get(\"GRANOLA_API_KEY\") ?? \"\";\n\nconst granolaGet = async (path: string) => {\n\tconst r = await fetch(`https://public-api.granola.ai/v1${path}`, {\n\t\theaders: { authorization: `Bearer ${granolaKey()}`, accept: \"application/json\" },\n\t});\n\tif (!r.ok) throw new Error(`granola ${path} returned ${r.status}`);\n\treturn r.json();\n};\n\nconst cacheGranolaNote = async (note: any) => {\n\tconst event = note.calendar_event ?? {};\n\tconst attendees = (note.attendees ?? [])\n\t\t.map((a: any) => String(a?.email ?? \"\").toLowerCase())\n\t\t.filter(Boolean);\n\tawait sqlite.execute({\n\t\tsql: `INSERT INTO ats_granola_notes (note_id, calendar_event_id, web_url, title, attendees, started_at)\n\t\t\tVALUES (?, ?, ?, ?, ?, ?)\n\t\t\tON CONFLICT(note_id) DO UPDATE SET\n\t\t\t\tcalendar_event_id = excluded.calendar_event_id,\n\t\t\t\tweb_url = excluded.web_url,\n\t\t\t\ttitle = excluded.title,\n\t\t\t\tattendees = excluded.attendees,\n\t\t\t\tstarted_at = excluded.started_at`,\n\t\targs: [\n\t\t\tnote.id,\n\t\t\tString(event.calendar_event_id ?? \"\"),\n\t\t\tString(note.web_url ?? \"\"),\n\t\t\tString(note.title ?? \"\"),\n\t\t\tattendees.join(\",\"),\n\t\t\tString(event.scheduled_start_time ?? note.created_at ?? \"\"),\n\t\t],\n\t});\n};\n\n// Granola cannot filter by calendar event id or attendee, so the only way to find a\n// meeting's note is to walk recent notes and resolve the ones we have not seen.\nconst refreshGranolaNotes = async (since: string) => {\n\tif (!granolaKey()) return { listed: 0, fetched: 0, budgetHit: false };\n\tlet cursor = \"\";\n\tlet listed = 0;\n\tlet fetched = 0;\n\tlet budgetHit = false;\n\tfor (let page = 0; page < 12; page++) {\n\t\tconst query = new URLSearchParams({ page_size: \"30\", created_after: since });\n\t\tif (cursor) query.set(\"cursor\", cursor);\n\t\tconst body = await granolaGet(`/notes?${query}`);\n\t\tconst notes = Array.isArray(body.notes) ? body.notes : [];\n\t\tlisted += notes.length;\n\t\tfor (const note of notes) {\n\t\t\tconst known = await sqlite.execute({\n\t\t\t\tsql: \"SELECT 1 FROM ats_granola_notes WHERE note_id = ?\",\n\t\t\t\targs: [note.id],\n\t\t\t});\n\t\t\tif (known.rows.length > 0) continue;\n\t\t\tif (fetched >= GRANOLA_FETCH_BUDGET) {\n\t\t\t\tbudgetHit = true;\n\t\t\t\tbreak;\n\t\t\t}\n\t\t\tawait cacheGranolaNote(await granolaGet(`/notes/${note.id}`));\n\t\t\tfetched++;\n\t\t\t// Stay under Granola's sustained rate limit.\n\t\t\tawait new Promise((resolve) => setTimeout(resolve, 150));\n\t\t}\n\t\tif (budgetHit || !body.hasMore || !body.cursor) break;\n\t\tcursor = body.cursor;\n\t}\n\treturn { listed, fetched, budgetHit };\n};\n\nconst getGranolaByEventId = async () => {\n\tconst rs = await sqlite.execute(\n\t\t\"SELECT calendar_event_id, note_id, web_url FROM ats_granola_notes WHERE calendar_event_id != ''\",\n\t);\n\tconst map = new Map();\n\tfor (const row of rs.rows) {\n\t\tmap.set(String(row[0]), { noteId: String(row[1]), url: String(row[2]) });\n\t}\n\treturn map;\n};\n\nconst getSyncRoleId = async () => {\n\tconst configured = Number(await getSetting(\"sync_role_id\"));\n\tif (Number.isFinite(configured) && configured > 0) {\n\t\tconst rs = await sqlite.execute({\n\t\t\tsql: \"SELECT id FROM ats_roles WHERE id = ? AND archived = 0\",\n\t\t\targs: [configured],\n\t\t});\n\t\tif (rs.rows.length > 0) return configured;\n\t}\n\tconst newest = await sqlite.execute(\"SELECT id FROM ats_roles WHERE archived = 0 ORDER BY id DESC LIMIT 1\");\n\treturn (newest.rows[0]?.[0] as number) ?? null;\n};\n\nconst runSync = async () => {\n\tconst events = await fetchCalendar();\n\tconst now = Date.now();\n\tconst from = now - SYNC_LOOKBACK_DAYS * 86400000;\n\tconst until = now + SYNC_LOOKAHEAD_DAYS * 86400000;\n\tconst inWindow = events.filter((e) => {\n\t\tif (!e.start) return false;\n\t\tconst at = new Date(e.start).getTime();\n\t\treturn at >= from && at <= until;\n\t});\n\n\tconst earliest = inWindow\n\t\t.map((e) => e.start)\n\t\t.sort()\n\t\t.at(0) ?? new Date(from).toISOString();\n\tlet granola = { listed: 0, fetched: 0, budgetHit: false };\n\tlet granolaError = \"\";\n\ttry {\n\t\tgranola = await refreshGranolaNotes(earliest.slice(0, 10));\n\t} catch (err) {\n\t\t// A Granola outage must not stop candidates from being added.\n\t\tgranolaError = String(err instanceof Error ? err.message : err);\n\t}\n\tconst notesByEvent = await getGranolaByEventId();\n\n\tconst roleId = await getSyncRoleId();\n\tconst stages = roleId === null ? [] : await getRoleStages(roleId);\n\tconst firstStage = stages[0] ?? DEFAULT_STAGES[0];\n\n\tlet addedPeople = 0;\n\tlet addedApplications = 0;\n\tlet addedMeetings = 0;\n\tlet linkedNotes = 0;\n\tlet skipped = 0;\n\n\tfor (const event of inWindow) {\n\t\tconst guests = event.guests.filter((g) => !isInternal(g.email) && g.email !== event.organizer);\n\t\tif (guests.length === 0) {\n\t\t\tskipped++;\n\t\t\tcontinue;\n\t\t}\n\t\tconst note = notesByEvent.get(event.eventId);\n\t\tconst day = event.start.slice(0, 10);\n\t\tfor (const guest of guests) {\n\t\t\tconst existing = await sqlite.execute({\n\t\t\t\tsql: \"SELECT id FROM ats_people WHERE email != '' AND lower(email) = ? LIMIT 1\",\n\t\t\t\targs: [guest.email],\n\t\t\t});\n\t\t\tconst knownBefore = existing.rows.length > 0;\n\t\t\tconst personId = knownBefore\n\t\t\t\t? (existing.rows[0][0] as number)\n\t\t\t\t: await findOrCreatePerson({ name: guest.name || nameFromEmail(guest.email), email: guest.email });\n\t\t\tif (personId === null) continue;\n\t\t\tif (!knownBefore) addedPeople++;\n\n\t\t\t// The person needs somewhere to live. Existing candidates keep whatever roles\n\t\t\t// they already have; only brand new faces get dropped into the target role.\n\t\t\tconst apps = await sqlite.execute({\n\t\t\t\tsql: \"SELECT role_id FROM ats_applications WHERE person_id = ?\",\n\t\t\t\targs: [personId],\n\t\t\t});\n\t\t\tlet eventRoleId = (apps.rows[0]?.[0] as number) ?? null;\n\t\t\tif (apps.rows.length === 0 && roleId !== null) {\n\t\t\t\tawait sqlite.execute({\n\t\t\t\t\tsql: `INSERT INTO ats_applications (person_id, role_id, stage, stage_since)\n\t\t\t\t\t\tVALUES (?, ?, ?, datetime('now'))\n\t\t\t\t\t\tON CONFLICT(person_id, role_id) DO NOTHING`,\n\t\t\t\t\targs: [personId, roleId, firstStage],\n\t\t\t\t});\n\t\t\t\teventRoleId = roleId;\n\t\t\t\taddedApplications++;\n\t\t\t}\n\n\t\t\tconst already = await sqlite.execute({\n\t\t\t\tsql: \"SELECT id, granola_url FROM ats_events WHERE calendar_event_id = ? AND person_id = ? LIMIT 1\",\n\t\t\t\targs: [event.eventId, personId],\n\t\t\t});\n\t\t\tif (already.rows.length > 0) {\n\t\t\t\t// The meeting is already on the timeline. A note written after the last\n\t\t\t\t// sync still needs attaching.\n\t\t\t\tif (note && !String(already.rows[0][1] ?? \"\")) {\n\t\t\t\t\tawait sqlite.execute({\n\t\t\t\t\t\tsql: \"UPDATE ats_events SET granola_note_id = ?, granola_url = ? WHERE id = ?\",\n\t\t\t\t\t\targs: [note.noteId, note.url, already.rows[0][0] as number],\n\t\t\t\t\t});\n\t\t\t\t\tlinkedNotes++;\n\t\t\t\t}\n\t\t\t\tcontinue;\n\t\t\t}\n\n\t\t\tconst title = event.title || \"Meeting\";\n\t\t\tawait sqlite.execute({\n\t\t\t\tsql: `INSERT INTO ats_events (person_id, role_id, author, kind, body, calendar_event_id, granola_note_id, granola_url, created_at)\n\t\t\t\t\tVALUES (?, ?, '', 'meeting', ?, ?, ?, ?, ?)`,\n\t\t\t\targs: [\n\t\t\t\t\tpersonId,\n\t\t\t\t\teventRoleId,\n\t\t\t\t\t`${title} on ${day}`,\n\t\t\t\t\tevent.eventId,\n\t\t\t\t\tnote?.noteId ?? \"\",\n\t\t\t\t\tnote?.url ?? \"\",\n\t\t\t\t\tevent.start.replace(\"T\", \" \").replace(\"Z\", \"\"),\n\t\t\t\t],\n\t\t\t});\n\t\t\taddedMeetings++;\n\t\t\tif (note) linkedNotes++;\n\t\t}\n\t}\n\n\treturn {\n\t\tevents: inWindow.length,\n\t\tskipped,\n\t\tadded_people: addedPeople,\n\t\tadded_applications: addedApplications,\n\t\tadded_meetings: addedMeetings,\n\t\tlinked_notes: linkedNotes,\n\t\tgranola_listed: granola.listed,\n\t\tgranola_fetched: granola.fetched,\n\t\tgranola_budget_hit: granola.budgetHit,\n\t\tgranola_error: granolaError,\n\t\ttarget_role_id: roleId,\n\t};\n};\n\n// Shared by the \"Sync now\" button and the scheduled run, so both leave the same trail.\nexport const syncAndRecord = async () => {\n\ttry {\n\t\tconst report = await runSync();\n\t\tawait sqlite.execute({\n\t\t\tsql: \"INSERT INTO ats_sync_runs (ok, report) VALUES (1, ?)\",\n\t\t\targs: [JSON.stringify(report)],\n\t\t});\n\t\treturn { ok: true as const, report };\n\t} catch (err) {\n\t\tconst error = String(err instanceof Error ? err.message : err);\n\t\tawait sqlite.execute({\n\t\t\tsql: \"INSERT INTO ats_sync_runs (ok, report) VALUES (0, ?)\",\n\t\t\targs: [JSON.stringify({ error })],\n\t\t});\n\t\treturn { ok: false as const, error };\n\t}\n};\n\nexport const isSyncConfigured = () => !!Deno.env.get(\"ATS_CALENDAR_ICS\") && !!granolaKey();\n\napp.post(\"/api/sync\", async (c) => {\n\tconst result = await syncAndRecord();\n\tif (!result.ok) return c.json({ error: result.error }, 500);\n\treturn c.json(result);\n});\n\napp.get(\"/api/sync\", async (c) => {\n\tconst rs = await sqlite.execute(\"SELECT ran_at, ok, report FROM ats_sync_runs ORDER BY id DESC LIMIT 10\");\n\treturn c.json({\n\t\tconfigured: {\n\t\t\tcalendar: !!Deno.env.get(\"ATS_CALENDAR_ICS\"),\n\t\t\tgranola: !!granolaKey(),\n\t\t},\n\t\ttarget_role_id: await getSyncRoleId(),\n\t\truns: rs.rows.map((r: any) => ({\n\t\t\tran_at: r[0],\n\t\t\tok: !!r[1],\n\t\t\treport: JSON.parse(String(r[2] ?? \"{}\")),\n\t\t})),\n\t});\n});\n\napp.put(\"/api/sync/role\", async (c) => {\n\tconst b = await c.req.json();\n\tawait setSetting(\"sync_role_id\", String(Number(b.role_id) || \"\"));\n\treturn c.json({ ok: true, target_role_id: await getSyncRoleId() });\n});\n\napp.get(\"/\", (c) =>\n\tc.html(makePage({ DEFAULT_STAGES, PEOPLE, GRADES, MEETINGS, LOST_REASONS, SOURCES, STALE_DAYS })));\n\nexport default app.fetch;\n",[134],"f81b584c-2c56-411c-a6e3-584a97ab7bd7","cron.ts","5a5b9412-8904-11f1-9eb8-1607ee4eb77e",21,["D",1785078721390],"interval","import { isSyncConfigured, syncAndRecord } from \"./main.tsx\";\n\n// Scheduled candidate sync. It imports the app rather than calling it over HTTP, so the\n// passphrase never has to travel anywhere.\nexport default async function () {\n\tif (!isSyncConfigured()) {\n\t\t// Nothing to do until ATS_CALENDAR_ICS and GRANOLA_API_KEY exist. Stay quiet\n\t\t// instead of failing every run and mailing an error each time.\n\t\tconsole.log(\"sync skipped: calendar feed or granola key not set\");\n\t\treturn;\n\t}\n\tconst result = await syncAndRecord();\n\tif (!result.ok) throw new Error(result.error);\n\tconsole.log(\"sync ok\", JSON.stringify(result.report));\n}\n",[142],"pulls",1,"forks","envVars",{"_156":150,"_158":54},"connections",{"_156":54,"_157":54},"count","unfilledCount","emptyCount",{"_20":141,"_13":142,"_118":143,"_120":-5,"_121":21,"_122":144,"_123":50,"_22":169,"_29":54,"_126":146,"_128":147,"_130":38,"_131":-5,"_163":170,"_90":171},{"_20":133,"_13":134,"_118":48,"_120":-5,"_121":21,"_122":135,"_123":50,"_22":166,"_29":137,"_126":138,"_128":139,"_130":38,"_131":-5,"_163":167,"_90":168},{"_20":116,"_13":117,"_118":119,"_120":-5,"_121":21,"_122":30,"_123":50,"_22":162,"_29":125,"_126":127,"_128":129,"_130":38,"_131":-5,"_163":164,"_90":165},["D",1787240606299],"children",[],[117],["D",1785078721161],[],[134],["D",1785078721390],[],[142],["D",1784991101203],{"_11":12,"_88":84},{"_11":12,"_82":-5,"_83":84,"_45":46,"_85":86,"_87":38},{"_29":30,"_13":26,"_20":21,"_27":28,"_31":-5,"_32":-5,"_77":-5,"_78":-5,"_79":-5,"_80":14,"_81":12},"ctx",{"_70":-5,"_110":209,"_210":211,"_212":213,"_214":38,"_215":38,"_216":38,"_217":50,"_218":-5,"_219":-7,"_220":-7,"_221":54,"_222":38,"_223":38,"_224":-5,"_225":38,"_226":38,"_227":38,"_228":-5},"ENV",{"_180":181,"_182":183,"_184":26,"_185":186,"_187":188,"_189":190,"_191":192,"_193":194,"_195":196,"_197":198,"_199":200,"_201":202,"_203":204,"_205":206,"_207":208},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","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","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Branches1

Branches
1