[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_207":208,"_209":210},"routes/_app.x.$handle.$name",{"_101":100,"_37":49,"_110":91,"_32":111,"_13":14,"_112":-5,"_11":113,"_114":115,"_116":117,"_118":119,"_120":121,"_26":-5,"_122":-5,"_123":-5,"_124":30,"_125":126,"_127":30,"_128":-5,"_129":-5,"_130":131},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":18,"_19":-5},"project",{"_47":48,"_37":49,"_42":50,"_51":-5,"_52":30,"_53":54,"_55":54,"_56":57,"_58":-5,"_19":59,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":-5,"_71":-5,"_72":-5,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":30},"version",5,"changes",[25],"aiChatInfo",{"_20":21,"_22":-5,"_23":-5,"_24":-5},"userId","aiChatMessageId","a3ea15b6-276c-495c-b0e6-820527575e86","aiChatThreadId","aiChatThreadUserId","aiChatThreadTitle",{"_26":27,"_28":-7,"_29":30,"_31":30,"_32":33,"_34":33},"file",{"_35":36,"_37":33,"_38":39,"_40":41,"_42":43,"_44":14,"_45":46,"_29":30},"previous","deleted",false,"deletedHere","path","pivot.ts","latestPath","fileId","41a8d0a8-637d-11f1-9747-1607ee4eb77e","name","fileType","script","content","import { getAllRows, type ScrapeRow } from \"./db.ts\";\n\nexport interface PivotCell {\n trustScore: number | null;\n reviews: number | null;\n lastReviewGiven: string | null;\n error: string | null;\n}\n\nexport interface PivotedTenant {\n tenant: string;\n link: string;\n platform: string;\n cells: Record; // keyed by scrape_date\n scoreGrowth: number | null; // latest - earliest trust score\n reviewsGrowth: number | null; // latest - earliest reviews count\n reviewsGrowthPct: number | null; // % change in reviews\n}\n\nexport interface PivotResult {\n dates: string[]; // sorted ascending\n tenants: PivotedTenant[];\n}\n\n/** Turn the long table into the wide Tenant | ...DayX | ...DayY shape. */\nexport async function buildPivot(): Promise {\n const rows = await getAllRows();\n\n const dateSet = new Set();\n const byLink = new Map();\n for (const r of rows) {\n dateSet.add(r.scrape_date);\n const arr = byLink.get(r.link) ?? [];\n arr.push(r);\n byLink.set(r.link, arr);\n }\n const dates = [...dateSet].sort(); // ascending\n\n const tenants: PivotedTenant[] = [];\n for (const [link, group] of byLink) {\n const cells: Record = {};\n for (const r of group) {\n cells[r.scrape_date] = {\n trustScore: r.trust_score,\n reviews: r.reviews,\n lastReviewGiven: r.last_review_given,\n error: r.raw_error,\n };\n }\n\n // Growth = latest available value minus earliest available value.\n const scored = dates\n .map((d) => cells[d])\n .filter((c): c is PivotCell => !!c);\n const firstScore = scored.find((c) => c.trustScore != null)?.trustScore ?? null;\n const lastScore = [...scored].reverse().find((c) => c.trustScore != null)?.trustScore ?? null;\n const firstReviews = scored.find((c) => c.reviews != null)?.reviews ?? null;\n const lastReviews = [...scored].reverse().find((c) => c.reviews != null)?.reviews ?? null;\n\n const scoreGrowth = firstScore != null && lastScore != null\n ? Number((lastScore - firstScore).toFixed(2))\n : null;\n const reviewsGrowth = firstReviews != null && lastReviews != null\n ? lastReviews - firstReviews\n : null;\n const reviewsGrowthPct = firstReviews && lastReviews != null && firstReviews > 0\n ? Number((((lastReviews - firstReviews) / firstReviews) * 100).toFixed(1))\n : null;\n\n tenants.push({\n tenant: group[0].tenant,\n link,\n platform: group[0].platform,\n cells,\n scoreGrowth,\n reviewsGrowth,\n reviewsGrowthPct,\n });\n }\n\n tenants.sort((a, b) => a.tenant.localeCompare(b.tenant));\n return { dates, tenants };\n}\n","createdAt",["D",1780952504866],"branchVersion","branchId","76079492-fc89-40e1-83b5-8979f5d78c01","id","aad874c4-24f0-499f-ae3d-a3490824c016","trustpilot-tracker",["D",1780952426854],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Daily Trustpilot trust score scraper","imageUrl","ae142318-b2d5-4fe8-8ad8-60325496b0fd","pinnedFileId","47de676c-637d-11f1-8c5b-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019ea905-06d5-70f8-8e84-44845c33c338","tursoName","tursoHostname","tursoMeasuredAt","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_101":100,"_109":105},"org",{"_101":100,"_102":103,"_104":105,"_19":59,"_106":107,"_108":30},"branch",{"_13":90,"_37":91,"_47":46,"_92":48,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":49,"_99":100},"isOwned",10,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","sarahmamy","handle","displayName","Sarah Mamy","avatar","https://lh3.googleusercontent.com/a/ACg8ocKWrMlzYAGqZzrImRB8bZ_zNDJ3wR4DHPSalYHWi48KQT3J1WU=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_47":48,"_37":49,"_42":203,"_51":-5,"_52":30,"_53":54,"_55":54,"_56":57,"_58":-5,"_19":59,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":-5,"_71":-5,"_72":-5,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":204,"_85":205,"_87":206,"_89":30},"tree",[182,183,184,185,186],"files",[182,183,184,185,186],"counts",{"_172":67,"_173":160,"_174":67,"_175":176,"_177":178},"contents",[132,133,134,135,136],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_47":164,"_37":165,"_35":166,"_140":-5,"_45":46,"_44":167,"_142":63,"_42":168,"_13":67,"_38":169,"_40":170,"_29":30,"_145":-5,"_32":171},{"_47":157,"_37":158,"_35":159,"_140":-5,"_45":46,"_44":160,"_142":63,"_42":161,"_13":67,"_38":39,"_40":162,"_29":30,"_145":-5,"_32":163},{"_47":154,"_37":33,"_35":36,"_140":-5,"_45":46,"_44":14,"_142":63,"_42":155,"_13":67,"_38":39,"_40":41,"_29":30,"_145":-5,"_32":156},{"_47":147,"_37":148,"_35":149,"_140":-5,"_45":46,"_44":150,"_142":63,"_42":151,"_13":67,"_38":39,"_40":152,"_29":30,"_145":-5,"_32":153},{"_47":137,"_37":138,"_35":139,"_140":-5,"_45":46,"_44":141,"_142":63,"_42":143,"_13":67,"_38":39,"_40":144,"_29":30,"_145":-5,"_32":146},"ed663528-a6fc-477b-8206-ac73e29879ea","scrape.ts","2dfaf37e-637d-11f1-acf2-1607ee4eb77e","parentId",2,"latest",["D",1780952471850],"// 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","label",[138],"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",[148],"f7e55cf7-2f92-438e-b434-261a1a9c624f",["D",1780952504866],[33],"38e4b41c-057d-457f-ac8f-5175405bcbfc","db.ts","1e1e38e4-637d-11f1-aef6-1607ee4eb77e",1,["D",1780952445188],"import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// One row per (tenant/link) per scrape day.\n// We store the raw scraped values; the dashboard pivots them into\n// the wide Tenant | TrustScoreDayX | ... format requested.\nexport const TABLE = \"trustpilot_scores_v1\";\n\nexport async function initDb() {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${TABLE} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n scrape_date TEXT NOT NULL, -- YYYY-MM-DD (UTC)\n tenant TEXT NOT NULL, -- domain / display name\n link TEXT NOT NULL, -- the trustpilot review URL visited\n platform TEXT NOT NULL DEFAULT 'trustpilot',\n trust_score REAL, -- e.g. 4.1\n reviews INTEGER, -- e.g. 45\n last_review_given TEXT, -- e.g. \"2 days ago\" / date string\n raw_error TEXT, -- populated if scrape failed\n created_at TEXT NOT NULL DEFAULT (datetime('now'))\n )\n `);\n // Avoid duplicate rows for the same link on the same day.\n await sqlite.execute(`\n CREATE UNIQUE INDEX IF NOT EXISTS ${TABLE}_uniq\n ON ${TABLE} (scrape_date, link)\n `);\n}\n\nexport interface ScrapeRow {\n scrape_date: string;\n tenant: string;\n link: string;\n platform: string;\n trust_score: number | null;\n reviews: number | null;\n last_review_given: string | null;\n raw_error: string | null;\n}\n\nexport async function upsertRow(row: ScrapeRow) {\n await sqlite.execute({\n sql: `\n INSERT INTO ${TABLE}\n (scrape_date, tenant, link, platform, trust_score, reviews, last_review_given, raw_error)\n VALUES (:scrape_date, :tenant, :link, :platform, :trust_score, :reviews, :last_review_given, :raw_error)\n ON CONFLICT(scrape_date, link) DO UPDATE SET\n tenant = excluded.tenant,\n platform = excluded.platform,\n trust_score = excluded.trust_score,\n reviews = excluded.reviews,\n last_review_given = excluded.last_review_given,\n raw_error = excluded.raw_error\n `,\n args: row as unknown as Record,\n });\n}\n\nexport async function getAllRows(): Promise {\n const res = await sqlite.execute(\n `SELECT scrape_date, tenant, link, platform, trust_score, reviews, last_review_given, raw_error\n FROM ${TABLE} ORDER BY scrape_date DESC, tenant ASC`,\n );\n return res.rows.map((r) => ({\n scrape_date: r[0] as string,\n tenant: r[1] as string,\n link: r[2] as string,\n platform: r[3] as string,\n trust_score: r[4] as number | null,\n reviews: r[5] as number | null,\n last_review_given: r[6] as string | null,\n raw_error: r[7] as string | null,\n }));\n}\n",[158],"f690961e-6e85-4d09-a998-452d54211462","daily.ts","38fd6ffe-637d-11f1-9bb5-1607ee4eb77e",4,["D",1780952490282],"interval","import { runScrape } from \"./runScrape.ts\";\n\n// Runs every day at 09:00 UTC.\n// Cron expressions on Val Town run in UTC — adjust if you want 9 AM in a\n// specific timezone (e.g. Paris is UTC+1/+2, so use \"0 7 * * *\" or \"0 8 * * *\").\nexport default async function () {\n const summary = await runScrape();\n console.log(\n `[trustpilot-tracker] ${summary.date}: ${summary.ok} ok, ${summary.failed} failed, ` +\n `${summary.skippedNonTrustpilot.length} non-trustpilot, ${summary.total} total`,\n );\n if (summary.skippedNonTrustpilot.length) {\n console.log(\"Non-Trustpilot links needing guidance:\", summary.skippedNonTrustpilot);\n }\n}\n",[165],"pulls","branches","forks","envVars",{"_179":67,"_181":67},"connections",{"_179":67,"_180":67},"count","unfilledCount","emptyCount",{"_47":164,"_37":165,"_35":166,"_140":-5,"_45":46,"_44":167,"_142":63,"_42":200,"_13":67,"_38":169,"_40":170,"_29":30,"_145":-5,"_188":201,"_32":202},{"_47":157,"_37":158,"_35":159,"_140":-5,"_45":46,"_44":160,"_142":63,"_42":197,"_13":67,"_38":39,"_40":162,"_29":30,"_145":-5,"_188":198,"_32":199},{"_47":154,"_37":33,"_35":36,"_140":-5,"_45":46,"_44":14,"_142":63,"_42":194,"_13":67,"_38":39,"_40":41,"_29":30,"_145":-5,"_188":195,"_32":196},{"_47":147,"_37":148,"_35":149,"_140":-5,"_45":46,"_44":150,"_142":63,"_42":191,"_13":67,"_38":39,"_40":152,"_29":30,"_145":-5,"_188":192,"_32":193},{"_47":137,"_37":138,"_35":139,"_140":-5,"_45":46,"_44":141,"_142":63,"_42":187,"_13":67,"_38":39,"_40":144,"_29":30,"_145":-5,"_188":189,"_32":190},["D",1780952471850],"children",[],[138],["D",1780952483118],[],[148],["D",1780952504866],[],[33],["D",1780952445188],[],[158],["D",1780952490282],[],[165],["D",1780952426854],{"_101":100,"_109":105},{"_101":100,"_102":103,"_104":105,"_19":59,"_106":107,"_108":30},{"_13":90,"_37":91,"_47":46,"_92":48,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":-5,"_98":49,"_99":100},"ctx",{"_83":-5,"_129":240,"_241":242,"_243":244,"_245":30,"_246":30,"_247":30,"_248":63,"_249":-5,"_250":-7,"_251":-7,"_252":67,"_253":30,"_254":30,"_255":-5,"_256":30,"_257":30,"_258":30,"_259":-5},"ENV",{"_211":212,"_213":214,"_215":91,"_216":217,"_218":219,"_220":221,"_222":223,"_224":225,"_226":227,"_228":229,"_230":231,"_232":233,"_234":235,"_236":237,"_238":239},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","fdb67791413f4386b4dbf18f354029a617f6160f","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"]

Diff · v5