[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_251":252,"_253":254},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":16,"_17":18,"_38":-7,"_121":-5,"_19":122,"_123":124,"_125":126,"_127":128,"_129":130,"_21":131,"_132":-5,"_133":-5,"_134":24,"_135":136,"_137":83,"_138":-5,"_139":-5,"_140":141},"routes/_app.x.$handle.$name.branch.$branch.history_.$",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":22,"_23":24,"_25":26,"_27":28,"_29":24,"_30":-5},"handle","sarahmamy","name","trustpilot-tracker","branchName","main","path","dashboard.html","project",{"_32":70,"_13":14,"_36":71,"_72":-5,"_73":24,"_74":75,"_76":75,"_77":78,"_79":-5,"_30":66,"_80":81,"_82":83,"_84":-5,"_85":-5,"_86":26,"_40":41,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":92,"_93":92,"_94":95,"_96":95,"_97":95,"_98":92,"_99":92,"_52":100,"_101":102,"_103":104,"_105":24},"file",{"_62":63,"_13":18,"_64":21,"_65":24,"_69":-5},"isDirectory",false,"page",0,"history",[31],"hasNext","userId",{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":-5,"_45":46,"_47":-5,"_48":-5,"_49":-5,"_50":-5,"_51":-5,"_52":53,"_21":54,"_55":-5,"_56":-5,"_57":-5,"_58":-5,"_59":60},"id","92b2d4fe-8747-4e74-992d-223d83b6fbc1","branchId","76079492-fc89-40e1-83b5-8979f5d78c01","createdAt",["D",1780952538693],"version",7,"orgId","019ea905-06d5-70f8-8e84-44845c33c338","parentId","a99fff55-f687-4bde-8941-7296ba6fa8eb","incomingCommitId","aiChatMessageId","a3ea15b6-276c-495c-b0e6-820527575e86","aiChatThreadId","aiChatThreadUserId","aiChatThreadTitle","source","clientLabel","user",{"_32":66,"_40":41,"_11":12,"_67":68},{"_62":63,"_13":18,"_64":21,"_65":24},"pull","merge","revert","multiple","commits",[61],{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":-5,"_45":46,"_47":-5,"_48":-5,"_49":-5,"_50":-5,"_51":-5,"_52":53,"_21":54,"_55":-5,"_56":-5,"_57":-5},"fileId","55d21350-637d-11f1-ba76-1607ee4eb77e","fileType","deleted","ae142318-b2d5-4fe8-8ad8-60325496b0fd","avatar","https://lh3.googleusercontent.com/a/ACg8ocKWrMlzYAGqZzrImRB8bZ_zNDJ3wR4DHPSalYHWi48KQT3J1WU=s96-c","deletedAtVersion","aad874c4-24f0-499f-ae3d-a3490824c016",["D",1780952426854],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Daily Trustpilot trust score scraper","imageUrl","pinnedFileId","47de676c-637d-11f1-8c5b-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_11":12,"_120":68},"org",{"_11":12,"_115":116,"_67":68,"_30":66,"_117":118,"_119":24},"branch",{"_38":106,"_13":16,"_32":35,"_107":70,"_108":-5,"_109":-5,"_110":-5,"_111":-5,"_112":-5,"_113":14,"_114":12},"isOwned",10,"projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Sarah Mamy","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_32":70,"_13":14,"_36":247,"_72":-5,"_73":24,"_74":75,"_76":75,"_77":78,"_79":-5,"_30":66,"_80":81,"_82":83,"_84":-5,"_85":-5,"_86":26,"_40":41,"_87":-5,"_88":-5,"_89":-5,"_90":-5,"_91":92,"_93":92,"_94":95,"_96":95,"_97":95,"_98":92,"_99":92,"_52":248,"_101":249,"_103":250,"_105":24},"tree",[161,162,163,164,165,166,167,168,169,170],"files",[161,162,163,164,165,166,167,168,169,170],"counts",{"_150":26,"_151":152,"_153":26,"_154":155,"_156":157},"contents",[],{"_32":142,"_13":18,"_62":63,"_42":-5,"_34":35,"_143":39,"_144":83,"_36":145,"_38":26,"_64":21,"_146":147,"_65":24,"_148":-5,"_17":149},"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},"68c20e30-9ec7-41b8-9f54-28192cc33c89","branchVersion","latest",["D",1780952538693],"content","\n\n\n \n \n Trustpilot Tracker\n \n\n\n

⭐ Trustpilot Tracker

Daily trust scores & review counts scraped at 09:00 UTC from the Metabase URL list.

\n \n

\n\n \n \n\n\n","label",[18],"pulls","branches",1,"forks","envVars",{"_158":26,"_160":26},"connections",{"_158":26,"_159":26},"count","unfilledCount","emptyCount",{"_32":240,"_13":241,"_62":242,"_42":-5,"_34":35,"_143":106,"_144":83,"_36":243,"_38":26,"_64":21,"_146":244,"_65":24,"_148":-5,"_178":245,"_17":246},{"_32":232,"_13":233,"_62":234,"_42":-5,"_34":35,"_143":235,"_144":83,"_36":236,"_38":26,"_64":209,"_146":237,"_65":24,"_148":-5,"_178":238,"_17":239},{"_32":223,"_13":224,"_62":225,"_42":-5,"_34":35,"_143":226,"_144":83,"_36":227,"_38":26,"_64":228,"_146":229,"_65":24,"_148":-5,"_178":230,"_17":231},{"_32":142,"_13":18,"_62":63,"_42":-5,"_34":35,"_143":39,"_144":83,"_36":220,"_38":26,"_64":21,"_146":147,"_65":24,"_148":-5,"_178":221,"_17":222},{"_32":213,"_13":214,"_62":215,"_42":-5,"_34":35,"_143":152,"_144":83,"_36":216,"_38":26,"_64":176,"_146":217,"_65":24,"_148":-5,"_178":218,"_17":219},{"_32":205,"_13":206,"_62":81,"_42":-5,"_34":35,"_143":207,"_144":83,"_36":208,"_38":26,"_64":209,"_146":210,"_65":24,"_148":-5,"_178":211,"_17":212},{"_32":197,"_13":198,"_62":199,"_42":-5,"_34":35,"_143":200,"_144":83,"_36":201,"_38":26,"_64":176,"_146":202,"_65":24,"_148":-5,"_178":203,"_17":204},{"_32":189,"_13":190,"_62":191,"_42":-5,"_34":35,"_143":192,"_144":83,"_36":193,"_38":26,"_64":176,"_146":194,"_65":24,"_148":-5,"_178":195,"_17":196},{"_32":181,"_13":182,"_62":183,"_42":-5,"_34":35,"_143":184,"_144":83,"_36":185,"_38":26,"_64":176,"_146":186,"_65":24,"_148":-5,"_178":187,"_17":188},{"_32":171,"_13":172,"_62":173,"_42":-5,"_34":35,"_143":174,"_144":83,"_36":175,"_38":26,"_64":176,"_146":177,"_65":24,"_148":-5,"_178":179,"_17":180},"ed663528-a6fc-477b-8206-ac73e29879ea","scrape.ts","2dfaf37e-637d-11f1-acf2-1607ee4eb77e",2,["D",1780952471850],"script","// Scraping helpers: gather URLs from Metabase, normalize trustpilot links,\n// and extract trust score + review count from a Trustpilot business page.\n\nconst METABASE_URL =\n \"https://metabase.internal.bigblue.co/public/question/27171a11-3254-4b23-82c0-fcbb8469a671\";\n\n// Metabase public questions expose a JSON results endpoint by appending `.json`\n// to the public question URL. We fall back to CSV if JSON isn't available.\nconst METABASE_JSON = METABASE_URL.replace(\"/question/\", \"/question/\") + \".json\";\n\nconst BROWSER_HEADERS = {\n \"User-Agent\":\n \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0 Safari/537.36\",\n \"Accept\": \"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8\",\n \"Accept-Language\": \"en-US,en;q=0.9\",\n};\n\nconst URL_REGEX = /https?:\\/\\/[^\\s\"'<>)\\]]+/g;\n\n/** Pull every distinct URL out of the Metabase public question results. */\nexport async function gatherUrls(): Promise {\n let text = \"\";\n try {\n const res = await fetch(METABASE_JSON, { headers: BROWSER_HEADERS });\n if (res.ok) {\n text = await res.text();\n }\n } catch (_) { /* ignore, fall through */ }\n\n // Fallback: fetch the raw HTML page if JSON failed.\n if (!text) {\n const res = await fetch(METABASE_URL, { headers: BROWSER_HEADERS });\n text = await res.text();\n }\n\n const found = text.match(URL_REGEX) ?? [];\n const distinct = new Set();\n for (const raw of found) {\n const u = normalizeTrustpilotUrl(raw.replace(/\\\\\\//g, \"/\").trim());\n // Only keep links that look like external review sites (skip metabase/internal/static).\n if (/trustpilot\\.com|google\\.com\\/maps|g\\.page|trustedshops|avis-verifies|verified-reviews/i.test(u)) {\n distinct.add(u);\n }\n }\n return [...distinct];\n}\n\n/** Replace `/evaluate/` with `/review/` in trustpilot links, strip query/hash. */\nexport function normalizeTrustpilotUrl(url: string): string {\n let u = url;\n if (/trustpilot\\.com/i.test(u)) {\n u = u.replace(\"/evaluate/\", \"/review/\");\n // strip query + fragment so the same domain dedupes cleanly\n u = u.split(\"?\")[0].split(\"#\")[0];\n }\n return u;\n}\n\nexport function tenantFromUrl(url: string): string {\n const m = url.match(/\\/review\\/([^/?#]+)/i) || url.match(/\\/evaluate\\/([^/?#]+)/i);\n if (m) return m[1];\n try {\n return new URL(url).hostname;\n } catch {\n return url;\n }\n}\n\nexport function detectPlatform(url: string): string {\n if (/trustpilot\\.com/i.test(url)) return \"trustpilot\";\n if (/google\\.com\\/maps|g\\.page/i.test(url)) return \"google\";\n if (/trustedshops/i.test(url)) return \"trustedshops\";\n if (/avis-verifies|verified-reviews/i.test(url)) return \"verified-reviews\";\n return \"unknown\";\n}\n\nexport interface ScrapeResult {\n trustScore: number | null;\n reviews: number | null;\n lastReviewGiven: string | null;\n error: string | null;\n}\n\n/** Fetch a Trustpilot review page and extract the headline numbers. */\nexport async function scrapeTrustpilot(url: string): Promise {\n try {\n const res = await fetch(url, { headers: BROWSER_HEADERS });\n if (!res.ok) {\n return { trustScore: null, reviews: null, lastReviewGiven: null, error: `HTTP ${res.status}` };\n }\n const html = await res.text();\n\n // Trustpilot embeds structured data in a __NEXT_DATA__ / JSON-LD blob.\n let trustScore: number | null = null;\n let reviews: number | null = null;\n let lastReviewGiven: string | null = null;\n\n // 1) JSON-LD AggregateRating is the most reliable source.\n const ldMatches = [...html.matchAll(/]*type=\"application\\/ld\\+json\"[^>]*>([\\s\\S]*?)<\\/script>/g)];\n for (const m of ldMatches) {\n try {\n const data = JSON.parse(m[1]);\n const nodes = Array.isArray(data) ? data : (data[\"@graph\"] ?? [data]);\n for (const node of nodes) {\n const agg = node.aggregateRating;\n if (agg) {\n if (agg.ratingValue != null) trustScore = Number(agg.ratingValue);\n if (agg.reviewCount != null) reviews = Number(agg.reviewCount);\n else if (agg.ratingCount != null) reviews = Number(agg.ratingCount);\n }\n }\n } catch (_) { /* not valid JSON, skip */ }\n }\n\n // 2) Fallback: parse the meta description / visible text.\n if (trustScore == null) {\n const sm = html.match(/\"trustScore\":\\s*([\\d.]+)/) ||\n html.match(/TrustScore[^0-9]{0,20}([\\d.]+)\\s*out of 5/i);\n if (sm) trustScore = Number(sm[1]);\n }\n if (reviews == null) {\n const rm = html.match(/\"numberOfReviews\":\\s*\\{?\\s*\"total\":\\s*(\\d+)/) ||\n html.match(/([\\d,]+)\\s*reviews?/i);\n if (rm) reviews = Number(rm[1].replace(/,/g, \"\"));\n }\n\n // 3) Date of most recent review (best effort).\n const dm = html.match(/\"datePublished\":\"([^\"]+)\"/);\n if (dm) lastReviewGiven = dm[1];\n\n return { trustScore, reviews, lastReviewGiven, error: null };\n } catch (e) {\n return { trustScore: null, reviews: null, lastReviewGiven: null, error: String(e) };\n }\n}\n","children",[],[172],"d8cd96bc-eb5b-42af-ba26-d9e010a337e9","runScrape.ts","34b71bb6-637d-11f1-9747-1607ee4eb77e",3,["D",1780952483118],"import { initDb, upsertRow, type ScrapeRow } from \"./db.ts\";\nimport {\n detectPlatform,\n gatherUrls,\n scrapeTrustpilot,\n tenantFromUrl,\n} from \"./scrape.ts\";\n\nexport interface RunSummary {\n date: string;\n total: number;\n ok: number;\n failed: number;\n skippedNonTrustpilot: string[];\n rows: ScrapeRow[];\n}\n\n/** Core daily routine, reusable from the cron trigger and the manual button. */\nexport async function runScrape(): Promise {\n await initDb();\n const date = new Date().toISOString().slice(0, 10); // YYYY-MM-DD UTC\n\n const urls = await gatherUrls();\n const rows: ScrapeRow[] = [];\n const skippedNonTrustpilot: string[] = [];\n let ok = 0;\n let failed = 0;\n\n for (const link of urls) {\n const platform = detectPlatform(link);\n\n if (platform !== \"trustpilot\") {\n // The user asked to be guided for non-Trustpilot platforms.\n // We still record the link so they're visible in the dashboard.\n skippedNonTrustpilot.push(link);\n const row: ScrapeRow = {\n scrape_date: date,\n tenant: tenantFromUrl(link),\n link,\n platform,\n trust_score: null,\n reviews: null,\n last_review_given: null,\n raw_error: \"unsupported platform - needs guidance\",\n };\n await upsertRow(row);\n rows.push(row);\n continue;\n }\n\n const result = await scrapeTrustpilot(link);\n const row: ScrapeRow = {\n scrape_date: date,\n tenant: tenantFromUrl(link),\n link,\n platform,\n trust_score: result.trustScore,\n reviews: result.reviews,\n last_review_given: result.lastReviewGiven,\n raw_error: result.error,\n };\n await upsertRow(row);\n rows.push(row);\n if (result.error || result.trustScore == null) failed++;\n else ok++;\n\n // Be polite to Trustpilot — small delay between requests.\n await new Promise((r) => setTimeout(r, 400));\n }\n\n return {\n date,\n total: urls.length,\n ok,\n failed,\n skippedNonTrustpilot,\n rows,\n };\n}\n",[],[182],"b95b917a-94c0-42dc-8857-89f82fc524b0","render.ts","58b28960-637d-11f1-8c5b-1607ee4eb77e",8,["D",1780952543522],"import { readFile } from \"https://esm.town/v/std/utils/index.ts\";\nimport type { PivotResult } from \"./pivot.ts\";\n\n/** Inject the pivot JSON into the static HTML shell. */\nexport function renderDashboard(pivot: PivotResult): string {\n const html = readFile(\"/dashboard.html\", import.meta.url);\n // Escape \"\" so embedded JSON can't break out of the

History · dashboard.html