[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_106":107,"_108":109},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","kfieseler","name","ndx-parts-dev","path","fuel_site.ts","project",{"_22":45,"_11":12,"_33":46,"_47":-5,"_48":42,"_49":50,"_51":50,"_52":53,"_54":-5,"_55":56,"_57":58,"_59":32,"_60":-5,"_61":-5,"_62":63,"_64":65,"_66":67,"_68":69,"_70":71,"_72":-5,"_73":74,"_75":74,"_76":77,"_78":77,"_79":77,"_80":74,"_81":74,"_82":83,"_84":85,"_86":87,"_88":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":-5,"_13":44},"permalink","https://www.val.town/x/kfieseler/ndx-parts-dev/code/fuel_site.ts","extension","id","040d9a72-9d50-4531-9b13-9160edc8c1ea","fileId","dc8fb6f8-9bbe-11f1-9507-1607ee4eb77e","parentId","branchId","1ba53fed-b2e4-413d-8a29-62afbe01b059","branchVersion",1509,"latest",true,"createdAt",["D",1787138135940],"version",3,"fileType","script","content","// WHO A SITE IS — as opposed to what it is currently called.\n//\n// This file exists because of two bugs found on 8/18-8/19, which were the same\n// bug twice:\n//\n// 1. Tenna spelled a job \"Chinnatown Relocate Contaminated Soils (G)\".\n// Kaden fixed it to \"Chinatown\" on 8/18. Our stored copies still said\n// \"Chinnatown\", so every cross-check on that job silently failed to join.\n// The Yesterday screen reported it as a data-quality complaint — which\n// HID a real finding underneath: a machine ran 1.3h at that site while a\n// truck was standing on it and never made the list.\n//\n// 2. Tenna renamed \"Sloan Visitor Center\" to \"Sloan Canyon Visitor Center\n// (G/U)\" between 8/17 and 8/18. Same place, same one machine. We carried\n// both, geocoded neither, and counted it twice in the unpinned list.\n//\n// The root cause in both: WE JOIN ON A DISPLAY NAME, AND A DISPLAY NAME IS A\n// FIELD A HUMAN CAN RETYPE AT ANY TIME. A rename upstream turns into a silent\n// join failure downstream, and a silent join failure on a trust screen reads\n// as agreement. That is the worst possible failure mode for this app — the\n// whole point of the Yesterday screen is to be unable to falsely agree.\n//\n// So: sites get an identity, and names become labels hanging off it.\n//\n// WHAT KEY. Probed live 8/19 across all four feeds we read (274 sites, 49\n// active):\n//\n// site_id UNIQUE, never missing, but present on only TWO feeds\n// (/v1/assets and /v1/fuel-entries).\n// site_number present on ALL FOUR (as asset_site_number on the daily\n// feeds), but 62 sites have none and 4 numbers collide —\n// \"1/\" alone is shared by five unrelated schools.\n// name present everywhere and trustworthy nowhere.\n//\n// Neither key covers everything alone, so the resolver is layered, strongest\n// first, and collided numbers are dropped rather than guessed at. Measured\n// against live data that resolves EVERY row of all four feeds:\n//\n// assets n=319 byId=319 unresolved 0\n// fuel-entries n=78 byId=78 unresolved 0\n// utilizations-daily n=121 byNumber=120 byName=1 unresolved 0\n// readings-daily n=236 byNumber=223 byName=13 unresolved 0\n//\n// A name we have ever seen is never forgotten — it becomes an alias pointing\n// at the site_id. That is what makes an old snapshot, an old route card or an\n// old export still join after a rename.\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { rows } from \"./core.ts\";\n\nexport async function siteInit() {\n // the site itself, keyed by the one field Tenna will not let a human retype\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fl_site (\n site_id TEXT PRIMARY KEY,\n name TEXT NOT NULL, number TEXT DEFAULT '', status TEXT DEFAULT '',\n addr TEXT DEFAULT '', at TEXT)`);\n // every name this site has EVER been called, including the current one\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fl_site_alias (\n name TEXT PRIMARY KEY,\n site_id TEXT NOT NULL, why TEXT DEFAULT '', first_seen TEXT, last_seen TEXT)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS fl_site_alias_s ON fl_site_alias (site_id)`);\n // the audit trail. Nothing in here is required to make the app work — it is\n // here so that a rewrite of stored history is visible and reversible rather\n // than something the app did to itself quietly at 4 AM.\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS fl_site_rename (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n site_id TEXT NOT NULL, old_name TEXT NOT NULL, new_name TEXT NOT NULL,\n detected TEXT, migrated TEXT DEFAULT '')`);\n}\n\nconst clean = (s: unknown) => String(s ?? \"\").trim();\n\n// 274 sites x 2 statements is 548 sequential round trips to the database, which\n// on 8/19 was enough to kill the run outright — the libSQL client died trying to\n// read an error body and the whole val exited mid-migration. Writes go out in\n// chunks now. Falls back to one-at-a-time if the client has no batch().\nasync function runAll(stmts: { sql: string; args: any[] }[]) {\n if (!stmts.length) return;\n const client = sqlite as any;\n if (typeof client.batch === \"function\") {\n for (let i = 0; i < stmts.length; i += 50) {\n await client.batch(stmts.slice(i, i + 50), \"write\");\n }\n return;\n }\n for (const s of stmts) await sqlite.execute(s);\n}\n\n// A number carried by more than one site is not an identifier, it is a\n// placeholder somebody typed to get past a required field. \"1/\" is shared by\n// five unrelated schools. Drop them rather than join on them.\nfunction numberIndex(sites: { site_id: string; name: string; number: string }[]) {\n const tally: Record = {};\n for (const s of sites) if (s.number) tally[s.number] = (tally[s.number] ?? 0) + 1;\n const out: Record = {};\n for (const s of sites) if (s.number && tally[s.number] === 1) out[s.number] = s.site_id;\n return out;\n}\n\n// ---------- THE RESOLVER ----------\n// Everything downstream should go through this rather than comparing two\n// strings and hoping. Give it whatever a feed happened to carry.\nexport type SiteRef = { id?: unknown; number?: unknown; name?: unknown };\nexport type Resolved = { site_id: string; name: string; number: string; status: string } | null;\n\nexport async function siteResolver() {\n await siteInit();\n const all = rows(await sqlite.execute(\"SELECT site_id, name, number, status FROM fl_site\"))\n .map((r) => ({\n site_id: String(r.site_id),\n name: String(r.name),\n number: clean(r.number),\n status: clean(r.status),\n }));\n const byId: Record = {};\n for (const s of all) byId[s.site_id] = s;\n const byNum = numberIndex(all);\n const byName: Record = {};\n for (const s of all) byName[s.name.toLowerCase()] = s.site_id;\n const byAlias: Record = {};\n for (const r of rows(await sqlite.execute(\"SELECT name, site_id FROM fl_site_alias\"))) {\n byAlias[String(r.name).toLowerCase()] = String(r.site_id);\n }\n\n function resolve(x: SiteRef): Resolved {\n const id = clean(x.id);\n if (id && byId[id]) return byId[id];\n const num = clean(x.number);\n if (num && byNum[num]) return byId[byNum[num]] ?? null;\n const nm = clean(x.name).toLowerCase();\n if (!nm) return null;\n if (byName[nm]) return byId[byName[nm]] ?? null;\n if (byAlias[nm]) return byId[byAlias[nm]] ?? null;\n return null;\n }\n\n // the name to SHOW and to JOIN ON. An unknown name is returned unchanged —\n // this layer never invents a site, it only ever collapses two names that are\n // provably one place.\n function canon(name: unknown, number?: unknown): string {\n const raw = clean(name);\n if (!raw) return raw;\n const hit = resolve({ name: raw, number });\n return hit ? hit.name : raw;\n }\n\n return { resolve, canon, count: all.length, aliases: Object.keys(byAlias).length };\n}\n\n// ---------- MIGRATION ----------\n// Where a site name is stored as a join key. If a table is added that stores\n// one, it belongs in this list or it will rot the next time Kaden fixes a typo.\nasync function migrateName(oldName: string, newName: string) {\n if (!oldName || !newName || oldName === newName) return {};\n const hits: Record = {};\n const bump = (k: string, n: number) => { if (n) hits[k] = (hits[k] ?? 0) + n; };\n\n bump(\"fl_asset_snap\", (await sqlite.execute({\n sql: \"UPDATE fl_asset_snap SET site = ? WHERE site = ?\", args: [newName, oldName],\n })).rowsAffected ?? 0);\n bump(\"fl_job\", (await sqlite.execute({\n sql: \"UPDATE fl_job SET jobname = ? WHERE jobname = ?\", args: [newName, oldName],\n })).rowsAffected ?? 0);\n bump(\"fl_site_link\", (await sqlite.execute({\n sql: \"UPDATE fl_site_link SET tenna_site = ? WHERE tenna_site = ?\", args: [newName, oldName],\n })).rowsAffected ?? 0);\n\n // fl_site_geo is keyed BY THE NAME, so a rename can collide with a row that\n // already exists under the new name. Merging has one hard rule, the same one\n // that governs the whole geo file: a pin Kaden dropped by hand always wins\n // and is never overwritten. After that, a real coordinate beats a null.\n const g = rows(await sqlite.execute({ sql: \"SELECT * FROM fl_site_geo WHERE site = ?\", args: [oldName] }))[0];\n if (g) {\n const n = rows(await sqlite.execute({ sql: \"SELECT * FROM fl_site_geo WHERE site = ?\", args: [newName] }))[0];\n if (!n) {\n await sqlite.execute({ sql: \"UPDATE fl_site_geo SET site = ? WHERE site = ?\", args: [newName, oldName] });\n bump(\"fl_site_geo\", 1);\n } else {\n const manual = (r: any) => String(r.source) === \"manual\";\n const placed = (r: any) => r.lat != null && r.lon != null;\n const keep = manual(n) ? n : manual(g) ? g : placed(n) ? n : placed(g) ? g : n;\n await sqlite.execute({\n sql: `UPDATE fl_site_geo SET lat = ?, lon = ?, source = ?, matched = ?, addr = ?, note = ?, at = ?\n WHERE site = ?`,\n args: [\n keep.lat ?? null, keep.lon ?? null, clean(keep.source) || \"none\", clean(keep.matched),\n clean(n.addr) || clean(g.addr), clean(n.note) || clean(g.note),\n new Date().toISOString(), newName,\n ],\n });\n await sqlite.execute({ sql: \"DELETE FROM fl_site_geo WHERE site = ?\", args: [oldName] });\n bump(\"fl_site_geo\", 1);\n bump(\"fl_site_geo_merged\", 1);\n }\n }\n\n // cached drafts hold the name inside a JSON blob\n bump(\"fl_draft\", (await sqlite.execute({\n sql: \"UPDATE fl_draft SET json = REPLACE(json, ?, ?) WHERE json LIKE ?\",\n args: [oldName, newName, \"%\" + oldName + \"%\"],\n })).rowsAffected ?? 0);\n\n return hits;\n}\n\nasync function noteAlias(name: string, siteId: string, why: string) {\n if (!name) return;\n const now = new Date().toISOString();\n await sqlite.execute({\n sql: `INSERT INTO fl_site_alias (name, site_id, why, first_seen, last_seen) VALUES (?, ?, ?, ?, ?)\n ON CONFLICT (name) DO UPDATE SET site_id = excluded.site_id, last_seen = excluded.last_seen`,\n args: [name, siteId, why, now, now],\n });\n}\n\n// ---------- REFRESH ----------\n// Pull the live site book, detect renames against what we stored last time,\n// and carry our own history forward onto the new name.\nexport async function refreshSites(migrate = true) {\n await siteInit();\n const KEY = Deno.env.get(\"TENNA_KEY\") ?? \"\";\n if (!KEY) return { sites: 0, renamed: [] as any[], added: 0, note: \"no TENNA_KEY\" };\n const H = { Authorization: \"Bearer \" + KEY };\n let live: any[] = [];\n for (let off = 0; off < 4000; off += 100) {\n const r = await fetch(\"https://api.tenna.com/v1/sites?limit=100&offset=\" + off, { headers: H });\n if (!r.ok) return { sites: 0, renamed: [], added: 0, note: \"sites HTTP \" + r.status };\n const g = (await r.json()).results ?? [];\n live = live.concat(g);\n if (g.length < 100) break;\n await new Promise((s) => setTimeout(s, 350));\n }\n if (!live.length) return { sites: 0, renamed: [], added: 0, note: \"empty site list\" };\n\n const stored: Record = {};\n for (const r of rows(await sqlite.execute(\"SELECT site_id, name FROM fl_site\"))) {\n stored[String(r.site_id)] = { name: String(r.name) };\n }\n\n const renamed: { site_id: string; from: string; to: string; moved: Record }[] = [];\n const queue: { sql: string; args: any[] }[] = [];\n let added = 0;\n const now = new Date().toISOString();\n\n for (const s of live) {\n const id = clean(s.site_id);\n const name = clean(s.name);\n if (!id || !name) continue;\n const was = stored[id];\n if (!was) added++;\n\n if (was && was.name !== name) {\n // Tenna renamed this site. Our stored rows still carry the old label.\n const moved = migrate ? await migrateName(was.name, name) : {};\n const ins = await sqlite.execute({\n sql: `INSERT INTO fl_site_rename (site_id, old_name, new_name, detected, migrated)\n VALUES (?, ?, ?, ?, ?)`,\n args: [id, was.name, name, now, JSON.stringify(moved)],\n });\n void ins;\n await noteAlias(was.name, id, \"renamed away \" + now.slice(0, 10));\n renamed.push({ site_id: id, from: was.name, to: name, moved });\n console.log(\"SITE RENAMED \" + JSON.stringify(was.name) + \" -> \" + JSON.stringify(name) +\n \" moved \" + (Object.keys(moved).length ? JSON.stringify(moved) : \"nothing\"));\n }\n\n queue.push({\n sql: `INSERT INTO fl_site (site_id, name, number, status, addr, at) VALUES (?, ?, ?, ?, ?, ?)\n ON CONFLICT (site_id) DO UPDATE SET name = excluded.name, number = excluded.number,\n status = excluded.status, addr = excluded.addr, at = excluded.at`,\n args: [id, name, clean(s.number), clean(s.status),\n [clean(s.address1), clean(s.city)].filter(Boolean).join(\", \"), now],\n });\n queue.push({\n sql: `INSERT INTO fl_site_alias (name, site_id, why, first_seen, last_seen) VALUES (?, ?, ?, ?, ?)\n ON CONFLICT (name) DO UPDATE SET site_id = excluded.site_id, last_seen = excluded.last_seen`,\n args: [name, id, \"current\", now, now],\n });\n }\n\n await runAll(queue);\n\n return { sites: live.length, added, renamed, note: \"\" };\n}\n\n// ---------- SEED ----------\n// One-time catch-up for names we were already holding before this file existed.\n// Only migrates where there is EVIDENCE, never a guess: a snapshot row carries\n// the site number Tenna gave it, so a stale name whose number resolves to a\n// live site under a different name is a proven rename. Anything without that\n// evidence is reported and left alone for a human to look at.\nexport async function seedAliases(apply = false) {\n await siteInit();\n const R = await siteResolver();\n\n // every site name we hold anywhere, with whatever number we stored beside it\n const held = new Map(); // name -> best known number\n for (const r of rows(await sqlite.execute(\n \"SELECT site AS n, site_no AS num, COUNT(*) AS c FROM fl_asset_snap WHERE site <> '' GROUP BY site, site_no\",\n ))) {\n const n = clean(r.n), num = clean(r.num);\n if (!n) continue;\n if (num || !held.has(n)) held.set(n, num || held.get(n) || \"\");\n }\n for (const t of [\n \"SELECT DISTINCT jobname AS n FROM fl_job WHERE jobname <> ''\",\n \"SELECT DISTINCT site AS n FROM fl_site_geo WHERE site <> ''\",\n \"SELECT DISTINCT tenna_site AS n FROM fl_site_link WHERE tenna_site <> ''\",\n ]) {\n for (const r of rows(await sqlite.execute(t))) {\n const n = clean(r.n);\n if (n && !held.has(n)) held.set(n, \"\");\n }\n }\n\n const okAlready: string[] = [];\n const proven: { from: string; to: string; via: string }[] = [];\n const unknown: string[] = [];\n\n for (const [name, num] of held) {\n const byName = R.resolve({ name });\n if (byName && byName.name === name) { okAlready.push(name); continue; }\n // the name does not match a live site. Can the stored number prove which\n // site it used to be?\n const byNum = num ? R.resolve({ number: num }) : null;\n if (byNum && byNum.name !== name) { proven.push({ from: name, to: byNum.name, via: \"number \" + num }); continue; }\n if (byName) { proven.push({ from: name, to: byName.name, via: \"existing alias\" }); continue; }\n unknown.push(name);\n }\n\n const applied: any[] = [];\n if (apply) {\n for (const p of proven) {\n const hit = R.resolve({ name: p.to });\n if (!hit) continue;\n const moved = await migrateName(p.from, p.to);\n await noteAlias(p.from, hit.site_id, \"seed: \" + p.via);\n await sqlite.execute({\n sql: `INSERT INTO fl_site_rename (site_id, old_name, new_name, detected, migrated)\n VALUES (?, ?, ?, ?, ?)`,\n args: [hit.site_id, p.from, p.to, new Date().toISOString(), JSON.stringify(moved)],\n });\n applied.push({ ...p, moved });\n }\n }\n\n return { held: held.size, okAlready: okAlready.length, proven, unknown, applied };\n}\n","deleted",false,"label",[14],"1730b37d-28eb-4a6b-8ba7-7294e47bbdb7",["D",1785517935233],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","DEV copy of NDX Parts — staging. Writes to TEST board.","imageUrl","userId","ab88c973-4988-481a-90fe-82231ca2dbda","pinnedFileId","ffd44ea0-8d02-11f1-95b0-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019fb4e5-7efe-7110-bc8f-c886bb63dd3f","tursoName","1730b37d28eb4a6b8ba77294e47bbdb7","tursoHostname","1730b37d28eb4a6b8ba77294e47bbdb7-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789452068287],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_105":100},"org",{"_9":10,"_99":100,"_101":100,"_55":56,"_102":103,"_104":42},"branch",{"_35":89,"_11":90,"_22":28,"_91":45,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned",2869,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","","avatar","tier","pro","canRestrictVals","profileImageUrl","ctx",{"_82":-5,"_139":140,"_141":142,"_143":144,"_145":42,"_146":42,"_147":42,"_148":32,"_149":-5,"_150":-7,"_151":-7,"_152":63,"_153":42,"_154":42,"_155":-5,"_156":42,"_157":42,"_158":42,"_159":-5},"ENV",{"_110":111,"_112":113,"_114":90,"_115":116,"_117":118,"_119":120,"_121":122,"_123":124,"_125":126,"_127":128,"_129":130,"_131":132,"_133":134,"_135":136,"_137":138},"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","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]