[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_320":321,"_322":323},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":16,"_17":18,"_38":-7,"_126":-5,"_19":127,"_128":129,"_130":131,"_132":133,"_134":135,"_21":136,"_137":-5,"_138":-5,"_139":24,"_140":141,"_142":24,"_143":-5,"_144":-5,"_145":146},"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","consensus","name","arr-bot","branchName","main","path","analytics.ts","project",{"_32":73,"_13":14,"_36":74,"_75":-5,"_76":24,"_77":78,"_79":78,"_80":81,"_82":-5,"_30":68,"_83":-5,"_84":24,"_85":-5,"_86":-5,"_87":26,"_40":88,"_89":90,"_91":92,"_93":94,"_95":-5,"_96":97,"_98":97,"_99":100,"_101":100,"_102":100,"_103":97,"_104":97,"_53":105,"_106":107,"_108":109,"_110":24},"file",{"_63":64,"_13":18,"_65":66,"_67":24,"_72":-5},"isDirectory",false,"page",0,"history",[31],"hasNext","userId",{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":-5,"_45":-5,"_46":-5,"_47":-5,"_48":-5,"_49":50,"_51":52,"_53":54,"_21":55,"_56":-5,"_57":-5,"_58":-5,"_59":-5,"_60":61},"id","3401264c-afdf-4e63-8204-44f32f561d82","branchId","b4b4d058-5ad4-11f1-9419-ee650bb23af1","createdAt",["D",1789092619737],"version",154,"orgId","019e702f-2fa7-7179-806d-a1cfd68d9f48","parentId","06a294f9-62eb-4b95-968c-e40aed73400b","incomingCommitId","aiChatMessageId","aiChatThreadId","aiChatThreadUserId","aiChatThreadTitle","source","mcp","clientLabel","Claude Code (val-town)","user",{"_32":68,"_40":41,"_11":69,"_70":71},{"_63":64,"_13":18,"_65":66,"_67":24},"pull","merge","revert","multiple","commits",[62],{"_32":33,"_34":35,"_36":37,"_38":39,"_40":41,"_42":43,"_44":-5,"_45":-5,"_46":-5,"_47":-5,"_48":-5,"_49":50,"_51":52,"_53":54,"_21":55,"_56":-5,"_57":-5,"_58":-5},"fileId","f03a5590-ad85-11f1-b64c-1607ee4eb77e","fileType","script","deleted","8ad5f245-a3c1-4df3-9e50-f79fb44e9037","christiansalem","avatar","https://lh3.googleusercontent.com/a/ACg8ocLWWGdAPqkoBRZgnWUdmwgW5h7-7VdrOqSnlLN-K5HQFUAdSLw=s96-c","deletedAtVersion","b4b31b00-5ad4-11f1-9419-ee650bb23af1",["D",1780000503639],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Slack bot: daily Stripe + HubSpot ARR report","imageUrl","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019e702f-9c4a-738c-b373-eee5c08e6c44","tursoName","b4b31b005ad411f19419ee650bb23af1","tursoHostname","b4b31b005ad411f19419ee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789509043237],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_11":12,"_125":-5},"org",{"_11":12,"_120":121,"_70":-5,"_30":-5,"_122":123,"_124":24},"branch",{"_38":111,"_13":16,"_32":35,"_112":73,"_113":-5,"_114":-5,"_115":-5,"_116":-5,"_117":-5,"_118":14,"_119":12},"isOwned",163,"projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Consensus","tier","free","canRestrictVals","profileImageUrl","adoptOrgId",{"_32":73,"_13":14,"_36":315,"_75":-5,"_76":24,"_77":78,"_79":78,"_80":81,"_82":-5,"_30":68,"_83":-5,"_84":24,"_85":-5,"_86":-5,"_87":26,"_40":88,"_89":90,"_91":92,"_93":316,"_95":-5,"_96":97,"_98":97,"_99":100,"_101":100,"_102":100,"_103":97,"_104":97,"_53":317,"_106":318,"_108":319,"_110":24},"tree",[168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183],"files",[168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183],"counts",{"_156":26,"_157":158,"_159":26,"_160":161,"_162":163},"contents",[],{"_32":147,"_13":18,"_63":64,"_42":-5,"_34":35,"_148":39,"_149":150,"_36":151,"_38":26,"_65":66,"_152":153,"_67":24,"_154":-5,"_17":155},"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},"39d871f8-b916-4d3e-a38e-c682f0a9eeb5","branchVersion","latest",true,["D",1789092619737],"content","// Shared client for the Stripe Analytics API (`POST /v2/data/analytics/metric_query`).\n//\n// Stripe limits metric queries to ONE per second per API key, and a digest run\n// issues three (revenue.arr, revenue_growth.arr, usage revenue) — fired in\n// parallel they 429. All calls are serialized through this module with a\n// minimum spacing, and 429s are retried with backoff.\n//\n// Requires STRIPE_SECRET_KEY with the Analytics (write) permission.\n\nconst URL = \"https://api.stripe.com/v2/data/analytics/metric_query\";\nconst VERSION = \"2026-04-22.preview\";\nconst MIN_SPACING_MS = 1100;\nconst MAX_ATTEMPTS = 4;\n\nexport interface MetricBucket {\n timestamp: string;\n dimensions?: Record;\n results?: { name: string; value: string | number }[];\n}\n\nconst sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));\n\nlet queue: Promise = Promise.resolve();\nlet lastCallAt = 0;\n\n// Returns the response's `data` buckets. Callers just await this; the\n// serialization is invisible to them.\nexport function metricQuery(body: Record): Promise {\n const run = queue.then(() => doQuery(body, 1));\n queue = run.catch(() => {}); // a failure must not wedge the queue\n return run;\n}\n\nasync function doQuery(body: Record, attempt: number): Promise {\n const key = Deno.env.get(\"STRIPE_SECRET_KEY\");\n if (!key) throw new Error(\"Missing STRIPE_SECRET_KEY env var\");\n\n const wait = lastCallAt + MIN_SPACING_MS - Date.now();\n if (wait > 0) await sleep(wait);\n lastCallAt = Date.now();\n\n const res = await fetch(URL, {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${key}`,\n \"Stripe-Version\": VERSION,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify(body),\n });\n\n if (res.status === 429 && attempt < MAX_ATTEMPTS) {\n await res.text(); // drain\n await sleep(1500 * attempt);\n return doQuery(body, attempt + 1);\n }\n if (!res.ok) throw new Error(`Stripe Analytics ${res.status}: ${await res.text()}`);\n const data = await res.json() as { data?: MetricBucket[] };\n return data.data ?? [];\n}\n","label",[18],"pulls","branches",1,"forks","envVars",{"_164":165,"_167":26},"connections",{"_164":165,"_166":26},"count",2,"unfilledCount","emptyCount",{"_32":306,"_13":307,"_63":308,"_42":-5,"_34":35,"_148":309,"_149":150,"_36":310,"_38":311,"_65":21,"_152":312,"_67":24,"_154":-5,"_190":313,"_17":314},{"_32":147,"_13":18,"_63":64,"_42":-5,"_34":35,"_148":39,"_149":150,"_36":303,"_38":26,"_65":66,"_152":153,"_67":24,"_154":-5,"_190":304,"_17":305},{"_32":294,"_13":295,"_63":296,"_42":-5,"_34":35,"_148":297,"_149":150,"_36":298,"_38":299,"_65":66,"_152":300,"_67":24,"_154":-5,"_190":301,"_17":302},{"_32":286,"_13":287,"_63":288,"_42":-5,"_34":35,"_148":289,"_149":150,"_36":290,"_38":26,"_65":274,"_152":291,"_67":24,"_154":-5,"_190":292,"_17":293},{"_32":278,"_13":279,"_63":280,"_42":-5,"_34":35,"_148":281,"_149":150,"_36":282,"_38":26,"_65":21,"_152":283,"_67":24,"_154":-5,"_190":284,"_17":285},{"_32":269,"_13":270,"_63":271,"_42":-5,"_34":35,"_148":272,"_149":150,"_36":273,"_38":165,"_65":274,"_152":275,"_67":24,"_154":-5,"_190":276,"_17":277},{"_32":261,"_13":262,"_63":263,"_42":-5,"_34":35,"_148":264,"_149":150,"_36":265,"_38":26,"_65":66,"_152":266,"_67":24,"_154":-5,"_190":267,"_17":268},{"_32":253,"_13":254,"_63":255,"_42":-5,"_34":35,"_148":256,"_149":150,"_36":257,"_38":26,"_65":66,"_152":258,"_67":24,"_154":-5,"_190":259,"_17":260},{"_32":245,"_13":246,"_63":247,"_42":-5,"_34":35,"_148":248,"_149":150,"_36":249,"_38":165,"_65":66,"_152":250,"_67":24,"_154":-5,"_190":251,"_17":252},{"_32":236,"_13":237,"_63":238,"_42":-5,"_34":35,"_148":239,"_149":150,"_36":240,"_38":241,"_65":66,"_152":242,"_67":24,"_154":-5,"_190":243,"_17":244},{"_32":228,"_13":229,"_63":230,"_42":-5,"_34":35,"_148":231,"_149":150,"_36":232,"_38":26,"_65":66,"_152":233,"_67":24,"_154":-5,"_190":234,"_17":235},{"_32":219,"_13":220,"_63":221,"_42":-5,"_34":35,"_148":222,"_149":150,"_36":223,"_38":224,"_65":66,"_152":225,"_67":24,"_154":-5,"_190":226,"_17":227},{"_32":211,"_13":212,"_63":213,"_42":-5,"_34":35,"_148":214,"_149":150,"_36":215,"_38":26,"_65":66,"_152":216,"_67":24,"_154":-5,"_190":217,"_17":218},{"_32":202,"_13":203,"_63":204,"_42":-5,"_34":35,"_148":205,"_149":150,"_36":206,"_38":207,"_65":66,"_152":208,"_67":24,"_154":-5,"_190":209,"_17":210},{"_32":193,"_13":194,"_63":195,"_42":-5,"_34":35,"_148":196,"_149":150,"_36":197,"_38":198,"_65":66,"_152":199,"_67":24,"_154":-5,"_190":200,"_17":201},{"_32":184,"_13":185,"_63":186,"_42":-5,"_34":35,"_148":187,"_149":150,"_36":188,"_38":165,"_65":66,"_152":189,"_67":24,"_154":-5,"_190":191,"_17":192},"4b869639-978f-44ee-b24e-d28c216c7afe","usage.ts","2e542248-6e48-11f1-971d-1607ee4eb77e",157,["D",1789092640239],"// Stripe API usage run-rate via the Analytics API.\n//\n// Run rate = trailing 28 complete days of metered \"usage revenue\" × 13\n// (4 weeks × 13 ≈ a full year). Source metric:\n// `usage_based_billing.gross_usage_revenue` — the same number the Stripe\n// dashboard shows under Billing overview → Usage (\"Usage revenue\"), already\n// FX-converted to USD by Stripe.\n//\n// Window is the 28 days ending YESTERDAY (today's partial day excluded). We\n// request a few extra days and slice(-28) so any timezone-boundary sliver at\n// the start drops off and we always sum exactly 28 complete days.\n//\n// Net change = today's run rate vs the run rate as-of yesterday (the same\n// 28-day window shifted back one day). Computed from the same response, so no\n// snapshot state is needed; it's null until there's a full prior window.\n//\n// Auth: STRIPE_SECRET_KEY must have the Analytics read permission (in addition\n// to the Sigma scopes used by stripe.ts). Calls go through analytics.ts, which\n// rate-limits them (Stripe allows one metric query per second).\n\nimport { metricQuery } from \"./analytics.ts\";\n\nexport interface UsageRunRate {\n runRate: number; // trailing-28d usage revenue × 13, USD\n windowRevenue: number; // trailing-28d usage revenue, USD\n netChange: number | null; // run rate vs prior day's run rate, USD\n netChangePct: number | null; // fraction of prior run rate\n windowStart: string; // first day in window (YYYY-MM-DD)\n windowEnd: string; // last day in window (YYYY-MM-DD)\n}\n\nexport async function fetchApiUsageRunRate(): Promise {\n const now = new Date();\n // today 00:00 UTC — exclusive end, so today's partial day is never counted.\n const endsAt = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));\n const startsAt = new Date(endsAt.getTime() - 35 * 86_400_000); // extra days; sliced to 28 below\n\n const buckets = await metricQuery({\n metrics: [{ name: \"usage_based_billing.gross_usage_revenue\" }],\n starts_at: startsAt.toISOString(),\n ends_at: endsAt.toISOString(),\n granularity: \"day\",\n currency: \"usd\",\n });\n\n const todayStr = endsAt.toISOString().slice(0, 10);\n const series = buckets\n .map((b) => ({\n day: String(b.timestamp ?? \"\").slice(0, 10),\n usd: (b.results ?? []).reduce((s, r) => s + Number(r.value ?? 0), 0) / 100,\n }))\n .filter((d) => d.day < todayStr) // drop today's partial day\n .sort((a, b) => (a.day < b.day ? -1 : 1));\n\n const sum = (xs: { usd: number }[]) => xs.reduce((s, d) => s + d.usd, 0);\n const window28 = series.slice(-28); // trailing 28 complete days (ends yesterday)\n const priorWindow28 = series.slice(-29, -1); // same window shifted back one day\n\n const windowRevenue = sum(window28);\n const runRate = windowRevenue * 13;\n\n let netChange: number | null = null;\n let netChangePct: number | null = null;\n if (priorWindow28.length === 28) {\n const priorRunRate = sum(priorWindow28) * 13;\n netChange = runRate - priorRunRate;\n netChangePct = priorRunRate ? netChange / priorRunRate : null;\n }\n\n return {\n runRate,\n windowRevenue,\n netChange,\n netChangePct,\n windowStart: window28[0]?.day ?? \"\",\n windowEnd: window28[window28.length - 1]?.day ?? \"\",\n };\n}\n","children",[],[185],"c0b04e10-d34d-44dc-afa7-88993d68df99","stripe.ts","ee25b5fa-5ad4-11f1-909e-ee650bb23af1",156,["D",1789092627041],12,"// Self-serve (Stripe) ARR for the daily digest.\n//\n// Primary source: the Stripe Analytics API — `revenue.arr` for the headline\n// (this IS the dashboard's ARR) and `revenue_growth.arr` grouped by change\n// type for the daily net change + new/expansion/contraction/churn breakdown.\n//\n// Fallback + cross-check: our own Sigma computation over\n// `subscription_item_change_events_v2_beta` (cumulative SUM(mrr_change),\n// FX-converted, ×12 — the same method Stripe's \"Total ARR\" Sigma template\n// uses). It runs alongside the metric on every run. resolveStripeARR()\n// decides which number to show:\n// - both agree (≤ ARR_TOLERANCE apart) → Stripe's metric\n// - metric errored → Sigma, with a visible note\n// - they disagree → whichever is consistent with\n// the last validated value, with\n// a note; if neither is, throw\n// (report.ts shows the last good\n// value with a warning)\n// Why both: on 2026-09-09 Stripe backfilled TRIAL_* / CONVERT_TRIAL rows\n// into the Sigma table and inflated the naive sum by ~73%; in Aug 2026 the\n// Analytics API's usage metric was broken server-side for a month. Neither\n// source alone has earned blind trust.\n//\n// Sigma event-type allow-list: every Sigma query is restricted to the ACTIVE_*\n// types, so new types Stripe adds fall out until deliberately classified.\n//\n// All amounts returned in US dollars.\n\nimport { runSigmaQuery } from \"./sigma.ts\";\nimport { type MetricBucket, metricQuery } from \"./analytics.ts\";\n\n// ---- Shared shapes -----------------------------------------------------------\n// One source's answer: headline ARR now, plus the most recent complete day's\n// movement. `day` is the day the net change covers (YYYY-MM-DD).\nexport interface StripeARRCandidate {\n asOfARR: number; // current self-serve ARR (all currencies -> USD)\n day: string;\n net: number;\n netPct: number; // fraction of the day's starting ARR (0.0001 = 0.01%)\n new: number; // incl. reactivations\n expansion: number;\n contraction: number; // negative\n churn: number; // negative\n}\n\nexport interface StripeARRReport extends StripeARRCandidate {\n source: \"analytics\" | \"sigma\";\n note?: string; // rendered under the section in the digest when set\n}\n\nexport const ARR_TOLERANCE = 0.05; // normal drift between the two sources is ~0.1-1%\n\nconst minorToDollars = (s: string | number | undefined) => Number(s ?? 0) / 100;\nconst usd = (n: number) => `$${Math.round(n).toLocaleString(\"en-US\")}`;\nconst pctOff = (a: number, b: number) => Math.abs(a - b) / Math.abs(b);\n\n// =============================================================================\n// Primary: Stripe Analytics API\n// =============================================================================\n// Calls go through analytics.ts, which serializes them (Stripe allows one\n// metric query per second per key) and retries 429s.\n//\n// revenue.arr and revenue_growth.arr live in different namespaces, so they\n// need separate requests. Buckets are UTC days; the last one may be today's\n// partial day, which is what we want for the \"as of now\" headline.\nasync function fetchAnalyticsARR(): Promise {\n const now = new Date();\n const endsAt = new Date(now.getTime() - 60_000); // ends_at may not be in the future\n const startsAt = new Date(endsAt.getTime() - 4 * 86_400_000);\n const common = {\n starts_at: startsAt.toISOString(),\n ends_at: endsAt.toISOString(),\n granularity: \"day\",\n currency: \"usd\",\n timezone: \"UTC\",\n };\n const [arrBuckets, growthBuckets] = await Promise.all([\n metricQuery({ metrics: [{ name: \"revenue.arr\" }], ...common }),\n metricQuery({ metrics: [{ name: \"revenue_growth.arr\" }], group_by: [\"change_type\"], ...common }),\n ]);\n\n const dayOf = (b: MetricBucket) => b.timestamp.slice(0, 10);\n const valueOf = (b: MetricBucket) => minorToDollars(b.results?.[0]?.value);\n\n const arrByDay = new Map();\n for (const b of arrBuckets) arrByDay.set(dayOf(b), valueOf(b));\n const days = [...arrByDay.keys()].sort();\n if (days.length === 0) throw new Error(\"Stripe Analytics returned no revenue.arr buckets\");\n\n const today = now.toISOString().slice(0, 10);\n const latestDay = days[days.length - 1];\n const reportDay = [...days].reverse().find((d) => d < today) ?? latestDay; // most recent complete day\n const priorDay = days[days.indexOf(reportDay) - 1] as string | undefined;\n\n const growth: Record = {};\n for (const b of growthBuckets) {\n if (dayOf(b) === reportDay) growth[b.dimensions?.change_type ?? \"?\"] = valueOf(b);\n }\n const g = (k: string) => growth[k] ?? 0;\n const net = Object.values(growth).reduce((s, v) => s + v, 0); // includes MRR_FX_ADJUSTMENT\n const reportDayARR = arrByDay.get(reportDay)!;\n const priorARR = priorDay !== undefined ? arrByDay.get(priorDay)! : reportDayARR - net;\n\n return {\n asOfARR: arrByDay.get(latestDay)!,\n day: reportDay,\n net,\n netPct: priorARR ? net / priorARR : 0,\n new: g(\"MRR_NEW\") + g(\"MRR_REACTIVATION\"),\n expansion: g(\"MRR_EXPANSION\"),\n contraction: g(\"MRR_CONTRACTION\"),\n churn: g(\"MRR_CHURN\"),\n };\n}\n\n// =============================================================================\n// Fallback / cross-check: Sigma\n// =============================================================================\n// event_type -> movement bucket (taxonomy of subscription_item_change_events_v2_beta):\n// ACTIVE_START -> new (incl. reactivations)\n// ACTIVE_UPGRADE / ACTIVE_QUANTITY_INCREASE -> expansion\n// ACTIVE_DOWNGRADE / ACTIVE_QUANTITY_DECREASE -> contraction\n// ACTIVE_END -> churn\n// TRIAL_START / TRIAL_END / CONVERT_TRIAL -> excluded (CONVERT_TRIAL\n// double-counts ACTIVE_START)\nconst ACTIVE_EVENT_TYPES =\n `('ACTIVE_START','ACTIVE_END','ACTIVE_UPGRADE','ACTIVE_DOWNGRADE','ACTIVE_QUANTITY_INCREASE','ACTIVE_QUANTITY_DECREASE')`;\n\nexport interface DayMovement {\n day: string; // YYYY-MM-DD\n total: number; // total ARR as of end of this day (USD)\n new: number; // new ARR added (incl. reactivations), USD\n expansion: number; // upgrades / quantity increases, USD\n contraction: number; // downgrades / quantity decreases, USD (negative)\n churn: number; // cancellations, USD (negative)\n net: number; // sum of the movement buckets, USD\n}\n\n// Shared FX CTEs: rate map per day (dashboard shifts the rate table back one\n// day) plus the latest published rate as a fallback for unpublished days.\nconst FX_CTES = `\nfx AS (\n SELECT date - INTERVAL '1' DAY AS date,\n CAST(JSON_PARSE(buy_currency_exchange_rates) AS MAP(VARCHAR, DOUBLE)) AS rate\n FROM exchange_rates_from_usd\n),\nfx_latest AS (SELECT rate FROM fx ORDER BY date DESC LIMIT 1)\n`;\n\n// Current MRR per currency = SUM of all mrr_change to date. Convert each\n// currency to USD at the latest published FX rate, sum, * 12.\nconst TOTAL_ARR_SQL = `\nWITH fx_latest AS (\n SELECT CAST(JSON_PARSE(buy_currency_exchange_rates) AS MAP(VARCHAR, DOUBLE)) AS rate\n FROM exchange_rates_from_usd\n ORDER BY date DESC\n LIMIT 1\n),\nmrr AS (\n SELECT currency, SUM(CAST(mrr_change AS DOUBLE)) AS mrr_minor\n FROM subscription_item_change_events_v2_beta\n WHERE event_type IN ${ACTIVE_EVENT_TYPES}\n GROUP BY currency\n)\nSELECT CAST(ROUND(SUM(m.mrr_minor / element_at(f.rate, m.currency)) * 12) AS BIGINT) AS arr_usd_minor\nFROM mrr m\nCROSS JOIN fx_latest f\nWHERE element_at(f.rate, m.currency) IS NOT NULL\n`;\n\nexport async function fetchStripeARR(): Promise {\n const { rows } = await runSigmaQuery(TOTAL_ARR_SQL);\n return minorToDollars(rows[0]?.arr_usd_minor);\n}\n\n// Per local-event day: new / expansion / contraction / churn / net, in USD.\nfunction movementSQL(days: number): string {\n const n = Math.max(1, Math.floor(days));\n return `\nWITH ${FX_CTES},\nev AS (\n SELECT DATE(local_event_timestamp) AS day, currency, event_type,\n CAST(mrr_change AS DOUBLE) AS mrr_change\n FROM subscription_item_change_events_v2_beta\n WHERE DATE(local_event_timestamp) >= CURRENT_DATE - INTERVAL '${n}' DAY\n AND event_type IN ${ACTIVE_EVENT_TYPES}\n),\nconv AS (\n SELECT e.day, e.event_type,\n e.mrr_change / COALESCE(element_at(f.rate, e.currency),\n element_at(lf.rate, e.currency)) AS mrr_usd\n FROM ev e\n LEFT JOIN fx f ON f.date = e.day\n CROSS JOIN fx_latest lf\n)\nSELECT\n CAST(day AS VARCHAR) AS day,\n CAST(ROUND(SUM(IF(event_type = 'ACTIVE_START', mrr_usd, 0)) * 12) AS BIGINT) AS new_minor,\n CAST(ROUND(SUM(IF(event_type IN ('ACTIVE_UPGRADE','ACTIVE_QUANTITY_INCREASE'), mrr_usd, 0)) * 12) AS BIGINT) AS expansion_minor,\n CAST(ROUND(SUM(IF(event_type IN ('ACTIVE_DOWNGRADE','ACTIVE_QUANTITY_DECREASE'), mrr_usd, 0)) * 12) AS BIGINT) AS contraction_minor,\n CAST(ROUND(SUM(IF(event_type = 'ACTIVE_END', mrr_usd, 0)) * 12) AS BIGINT) AS churn_minor,\n CAST(ROUND(SUM(mrr_usd) * 12) AS BIGINT) AS net_minor\nFROM conv\nGROUP BY day\n`;\n}\n\n// Cumulative MRR per currency forward-filled across a daily spine (same\n// approach as the dashboard's ARR template), FX-converted at each day's rate,\n// * 12. Window-limited at the end so the cumulative carries full history.\nfunction dailyTotalSQL(days: number): string {\n const n = Math.max(1, Math.floor(days));\n return `\nWITH ${FX_CTES},\nchanges AS (\n SELECT DATE(local_event_timestamp) AS day, currency,\n SUM(CAST(mrr_change AS DOUBLE)) AS d_mrr\n FROM subscription_item_change_events_v2_beta\n WHERE event_type IN ${ACTIVE_EVENT_TYPES}\n GROUP BY 1, 2\n),\nwindow_days AS (\n SELECT d AS day\n FROM UNNEST(SEQUENCE(CURRENT_DATE - INTERVAL '${n}' DAY, CURRENT_DATE, INTERVAL '1' DAY)) t(d)\n),\n-- cumulative MRR per currency as of each window day = all changes on/before it\ncum AS (\n SELECT wd.day, ch.currency, SUM(ch.d_mrr) AS cum_mrr\n FROM window_days wd\n JOIN changes ch ON ch.day <= wd.day\n GROUP BY wd.day, ch.currency\n)\nSELECT\n CAST(c.day AS VARCHAR) AS day,\n CAST(ROUND(SUM(c.cum_mrr /\n COALESCE(element_at(f.rate, c.currency), element_at(lf.rate, c.currency))) * 12) AS BIGINT) AS total_arr_minor\nFROM cum c\nLEFT JOIN fx f ON f.date = c.day\nCROSS JOIN fx_latest lf\nGROUP BY c.day\n`;\n}\n\nexport async function fetchStripeARRMovementByDay(\n days = 7,\n): Promise {\n // Run movement + running-total queries concurrently, then merge by day.\n const [mv, tot] = await Promise.all([\n runSigmaQuery(movementSQL(days)),\n runSigmaQuery(dailyTotalSQL(days)),\n ]);\n\n const moveByDay = new Map(mv.rows.map((r) => [r.day, r]));\n return tot.rows // dense: one row per day in window\n .map((r) => {\n const m = moveByDay.get(r.day);\n return {\n day: r.day,\n total: minorToDollars(r.total_arr_minor),\n new: minorToDollars(m?.new_minor),\n expansion: minorToDollars(m?.expansion_minor),\n contraction: minorToDollars(m?.contraction_minor),\n churn: minorToDollars(m?.churn_minor),\n net: minorToDollars(m?.net_minor),\n };\n })\n .sort((a, b) => (a.day < b.day ? 1 : -1)); // newest first\n}\n\nasync function fetchSigmaARR(): Promise {\n const series = await fetchStripeARRMovementByDay(8); // newest-first, incl. today\n const today = new Date().toISOString().slice(0, 10);\n const i = series.findIndex((m) => m.day < today); // most recent complete day\n const row = series[i] ?? series[0];\n const prior = series[i + 1]; // the day before the reporting day\n const priorARR = prior?.total ?? row.total - row.net;\n return {\n asOfARR: series[0]?.total ?? row.total,\n day: row.day,\n net: row.net,\n netPct: priorARR ? row.net / priorARR : 0,\n new: row.new,\n expansion: row.expansion,\n contraction: row.contraction,\n churn: row.churn,\n };\n}\n\n// =============================================================================\n// Resolution\n// =============================================================================\n// Pure decision function (no I/O) so every branch can be unit-tested.\n// `lastGoodARR` is the last value that previously passed validation (report.ts\n// keeps it in SQLite) — the tiebreaker when the two sources disagree.\nexport function resolveStripeARR(input: {\n analytics: StripeARRCandidate | null;\n sigma: StripeARRCandidate | null;\n lastGoodARR: number | null;\n}): StripeARRReport {\n const { analytics, sigma, lastGoodARR } = input;\n\n if (analytics && sigma) {\n const off = pctOff(analytics.asOfARR, sigma.asOfARR);\n if (off <= ARR_TOLERANCE) return { ...analytics, source: \"analytics\" };\n\n // The two disagree. Trust whichever is consistent with the last validated\n // value; Stripe's metric wins if both are.\n if (lastGoodARR && lastGoodARR > 0) {\n if (pctOff(analytics.asOfARR, lastGoodARR) <= ARR_TOLERANCE) {\n return {\n ...analytics,\n source: \"analytics\",\n note:\n `⚠️ Sigma cross-check disagrees (${usd(sigma.asOfARR)} vs Stripe's metric ${usd(analytics.asOfARR)}) — showing Stripe's metric`,\n };\n }\n if (pctOff(sigma.asOfARR, lastGoodARR) <= ARR_TOLERANCE) {\n return {\n ...sigma,\n source: \"sigma\",\n note:\n `⚠️ Stripe's ARR metric looks off (${usd(analytics.asOfARR)} vs Sigma ${usd(sigma.asOfARR)}) — showing Sigma computation`,\n };\n }\n }\n throw new Error(\n `Stripe ARR sources disagree by ${(off * 100).toFixed(1)}% (metric ${usd(analytics.asOfARR)} vs Sigma ${usd(sigma.asOfARR)}) and neither matches the last good value ${lastGoodARR ? usd(lastGoodARR) : \"(none)\"} — refusing to post`,\n );\n }\n if (analytics) return { ...analytics, source: \"analytics\" };\n if (sigma) {\n return {\n ...sigma,\n source: \"sigma\",\n note: \"⚠️ Stripe's ARR metric unavailable — computed from Sigma\",\n };\n }\n throw new Error(\"Both Stripe ARR sources failed (Analytics metric and Sigma)\");\n}\n\n// Assembled self-serve report for the daily digest.\nexport async function fetchStripeARRReport(\n opts: { lastGoodARR?: number | null } = {},\n): Promise {\n const settle = async (label: string, p: Promise): Promise => {\n try {\n return await p;\n } catch (err) {\n console.warn(`${label} failed: ${(err as Error).message}`);\n return null;\n }\n };\n const [analytics, sigma] = await Promise.all([\n settle(\"Stripe Analytics ARR\", fetchAnalyticsARR()),\n settle(\"Sigma ARR\", fetchSigmaARR()),\n ]);\n\n const report = resolveStripeARR({ analytics, sigma, lastGoodARR: opts.lastGoodARR ?? null });\n\n const detail = analytics && sigma\n ? ` (metric ${usd(analytics.asOfARR)} / Sigma ${usd(sigma.asOfARR)}, ${(pctOff(analytics.asOfARR, sigma.asOfARR) * 100).toFixed(2)}% apart)`\n : \"\";\n console.log(`Stripe ARR source: ${report.source} ${usd(report.asOfARR)}${detail}${report.note ? ` — ${report.note}` : \"\"}`);\n return report;\n}\n",[],[194],"61a18e65-5191-4894-b83d-517b22edbe42","slack.ts","f6198156-5ad4-11f1-adee-ee650bb23af1",151,["D",1789092511649],26,"// Slack digest formatting + posting via the Slack Web API.\n// Uses SLACK_TOKEN provided by Val Town's Slack integration (dashboard).\n// Bot displays as \"Daily ARR Digest\" (username override in postToSlack).\n\nimport type { ARRReport, ARRSegment } from \"./report.ts\";\nimport { WebClient } from \"npm:@slack/web-api\";\n\ntype SlackBlock = Record;\n\nconst SLACK_CHANNEL = \"C014X5SQ9R7\"; // #general\n\n// ---- Formatting helpers -----------------------------------------------------\nconst DASH = \"—\";\n\nconst EMOJI = {\n total: \"⭐\",\n selfServe: \"👩‍🔬\",\n enterprise: \"🏫\",\n usage: \"🧑‍💻\",\n} as const;\nconst DOT = {\n new: \"🟢\",\n expansion: \"🔵\",\n contraction: \"🟠\",\n churn: \"🔴\",\n} as const;\n\nconst usd = (n: number) =>\n Math.round(Math.abs(n)).toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\",\n maximumFractionDigits: 0,\n });\n\nconst signedUsd = (n: number) => {\n const r = Math.round(n);\n return r === 0 ? \"$0\" : (r > 0 ? \"+\" : \"−\") + usd(r);\n};\n\nconst signedPct = (f: number) => {\n const p = f * 100;\n return (p > 0 ? \"+\" : p < 0 ? \"−\" : \"\") + Math.abs(p).toFixed(2) + \"%\";\n};\n\nconst arrow = (n: number) => (n > 0 ? \"▲\" : n < 0 ? \"▼\" : \"\");\n\nconst arrText = (seg: ARRSegment) => (seg.arr === null ? DASH : usd(seg.arr));\n\nfunction netLine(seg: ARRSegment): string {\n if (seg.netChange === null) return `Net change: ${DASH}`;\n const a = arrow(seg.netChange);\n const pct = seg.netChangePct !== null ? ` (${signedPct(seg.netChangePct)})` : \"\";\n return `Net change: ${a ? a + \" \" : \"\"}${signedUsd(seg.netChange)}${pct}`;\n}\n\n// New enterprise accounts, one per line — rendered as a small context block,\n// same style as the self-serve breakdown.\nfunction newAccountsText(seg: ARRSegment): string | undefined {\n return seg.newAccounts?.length ? seg.newAccounts.join(\"\\n\") : undefined;\n}\n\n// Warning shown under a section: either the stale-value note (source down,\n// showing cached value) or a note the source itself attached (e.g. fallback).\nfunction warningNote(seg: ARRSegment): string | undefined {\n if (seg.staleAsOf) {\n return `⚠️ Source unavailable or failed validation — showing last good value (${seg.staleAsOf})`;\n }\n return seg.note;\n}\n\nfunction breakdownText(seg: ARRSegment): string | undefined {\n const bd = seg.breakdown;\n if (!bd) return undefined;\n return [\n `${DOT.new} New ${signedUsd(bd.new)} ${DOT.expansion} Expansion ${signedUsd(bd.expansion)}`,\n `${DOT.contraction} Contraction ${signedUsd(bd.contraction)} ${DOT.churn} Churn ${signedUsd(bd.churn)}`,\n ].join(\"\\n\");\n}\n\n// ---- Digest formatter -------------------------------------------------------\nexport function formatARRReport(\n r: ARRReport,\n): { text: string; blocks: SlackBlock[] } {\n const warn = (seg: ARRSegment) => {\n const w = warningNote(seg);\n return w ? [` ${w}`] : [];\n };\n const lines = [\n `${EMOJI.total} Total ARR: ${arrText(r.total)}`,\n ` ${netLine(r.total)}`,\n ``,\n `${EMOJI.selfServe} Self Serve ARR (Stripe): ${arrText(r.selfServe)}`,\n ` ${netLine(r.selfServe)}`,\n ...(breakdownText(r.selfServe)?.split(\"\\n\").map((l) => ` ${l}`) ?? []),\n ...warn(r.selfServe),\n ``,\n `${EMOJI.enterprise} Enterprise ARR (HubSpot): ${arrText(r.enterprise)}`,\n ` ${netLine(r.enterprise)}`,\n ...(r.enterprise.newAccounts ?? []).map((n) => ` ${n}`),\n ...warn(r.enterprise),\n ``,\n `${EMOJI.usage} Usage Revenue Run Rate (Stripe): ${arrText(r.usage)}`,\n ` ${netLine(r.usage)}`,\n ...warn(r.usage),\n ];\n const text = lines.join(\"\\n\");\n\n const segment = (\n emoji: string,\n label: string,\n seg: ARRSegment,\n bd?: string,\n ): SlackBlock[] => {\n const blocks: SlackBlock[] = [\n {\n type: \"section\",\n text: {\n type: \"mrkdwn\",\n text: `${emoji} *${label}:* ${arrText(seg)}\\n\\n${netLine(seg)}`,\n },\n },\n ];\n if (bd) {\n blocks.push({\n type: \"context\",\n elements: [{ type: \"mrkdwn\", text: bd }],\n });\n }\n const w = warningNote(seg);\n if (w) {\n blocks.push({\n type: \"context\",\n elements: [{ type: \"mrkdwn\", text: w }],\n });\n }\n return blocks;\n };\n\n const blocks: SlackBlock[] = [\n ...segment(EMOJI.total, \"Total ARR\", r.total),\n { type: \"divider\" },\n ...segment(\n EMOJI.selfServe,\n \"Self Serve ARR (Stripe)\",\n r.selfServe,\n breakdownText(r.selfServe),\n ),\n { type: \"divider\" },\n ...segment(\n EMOJI.enterprise,\n \"Enterprise ARR (HubSpot)\",\n r.enterprise,\n newAccountsText(r.enterprise),\n ),\n { type: \"divider\" },\n ...segment(EMOJI.usage, \"Usage Revenue Run Rate (Stripe)\", r.usage),\n ];\n\n return { text, blocks };\n}\n\n// ---- Post to Slack via Web API ----------------------------------------------\nexport async function postToSlack(payload: {\n text: string;\n blocks?: SlackBlock[];\n}) {\n const token = Deno.env.get(\"SLACK_TOKEN\");\n if (!token) throw new Error(\"Missing SLACK_TOKEN env var\");\n\n const client = new WebClient(token);\n await client.conversations.join({ channel: SLACK_CHANNEL }).catch(() => {});\n await client.chat.postMessage({\n channel: SLACK_CHANNEL,\n text: payload.text,\n blocks: payload.blocks as any,\n username: \"Daily ARR Digest\",\n icon_emoji: \":bar_chart:\",\n });\n}\n",[],[203],"1fa15d5c-5af1-11f1-81a2-ee650bb23af1","sigma.ts","1fa15e24-5af1-11f1-81a2-ee650bb23af1",17,["D",1780012708937],"// Run a SQL query against Stripe Sigma via the Reports API v2 Query Runs.\n// Pattern: submit SQL -> poll until \"succeeded\" -> download CSV -> parse rows.\n//\n// Requires STRIPE_SECRET_KEY with `reporting_write` + `sigma_api_write` scopes\n// (shown as \"Sigma\" and \"Sigma API\" in the dashboard) and an active Sigma sub.\n// Queries cold-start a query engine, so expect ~20-40s latency per call.\n\nconst VERSION = \"2026-04-22.preview\";\nconst BASE = \"https://api.stripe.com/v2/data/reporting/query_runs\";\n\nfunction getKey(): string {\n const k = Deno.env.get(\"STRIPE_SECRET_KEY\");\n if (!k) throw new Error(\"Missing STRIPE_SECRET_KEY env var\");\n return k;\n}\n\ninterface QueryRun {\n id: string;\n status: \"running\" | \"succeeded\" | \"failed\" | \"canceled\";\n status_details?: unknown;\n result?: { file?: { download_url?: { url?: string } } } | null;\n}\n\n// Minimal RFC-4180 CSV parser: quoted fields, embedded commas/newlines, \"\" escapes.\nfunction parseCSV(text: string): string[][] {\n const rows: string[][] = [];\n let row: string[] = [];\n let field = \"\";\n let inQuotes = false;\n for (let i = 0; i < text.length; i++) {\n const c = text[i];\n if (inQuotes) {\n if (c === '\"') {\n if (text[i + 1] === '\"') {\n field += '\"';\n i++;\n } else inQuotes = false;\n } else field += c;\n } else if (c === '\"') inQuotes = true;\n else if (c === \",\") {\n row.push(field);\n field = \"\";\n } else if (c === \"\\n\") {\n row.push(field);\n rows.push(row);\n row = [];\n field = \"\";\n } else if (c !== \"\\r\") field += c;\n }\n if (field.length > 0 || row.length > 0) {\n row.push(field);\n rows.push(row);\n }\n return rows;\n}\n\nexport interface SigmaResult {\n columns: string[];\n rows: Record[];\n}\n\nexport async function runSigmaQuery(\n sql: string,\n { timeoutMs = 150_000, pollMs = 3000 }: { timeoutMs?: number; pollMs?: number } = {},\n): Promise {\n const headers = {\n Authorization: `Bearer ${getKey()}`,\n \"Stripe-Version\": VERSION,\n \"Content-Type\": \"application/json\",\n };\n\n const submit = await fetch(BASE, {\n method: \"POST\",\n headers,\n body: JSON.stringify({ sql }),\n });\n if (!submit.ok) {\n throw new Error(`Sigma submit ${submit.status}: ${await submit.text()}`);\n }\n let run = await submit.json() as QueryRun;\n\n const deadline = Date.now() + timeoutMs;\n while (run.status === \"running\") {\n if (Date.now() > deadline) {\n throw new Error(`Sigma query timed out after ${timeoutMs}ms (id ${run.id})`);\n }\n await new Promise((r) => setTimeout(r, pollMs));\n const res = await fetch(`${BASE}/${run.id}`, { headers });\n if (!res.ok) throw new Error(`Sigma poll ${res.status}: ${await res.text()}`);\n run = await res.json() as QueryRun;\n }\n\n if (run.status !== \"succeeded\") {\n throw new Error(`Sigma query ${run.status}: ${JSON.stringify(run.status_details)}`);\n }\n\n const url = run.result?.file?.download_url?.url;\n if (!url) throw new Error(\"Sigma query succeeded but returned no download URL\");\n const csv = await (await fetch(url)).text();\n\n const table = parseCSV(csv);\n if (table.length === 0) return { columns: [], rows: [] };\n const [columns, ...dataRows] = table;\n const rows = dataRows\n .filter((r) => r.length === columns.length)\n .map((r) => Object.fromEntries(columns.map((c, i) => [c, r[i]])));\n return { columns, rows };\n}\n",[],[212],"b668fed1-818c-44b4-a0f4-21a7d338c5ac","report.ts","8ce396ca-5af4-11f1-9141-ee650bb23af1",150,["D",1789092489989],10,"// Cross-source ARR report model assembled for the Slack digest.\n//\n// Self-serve (Stripe) comes from Stripe's own Analytics ARR metric, with our\n// Sigma computation as fallback + cross-check (see stripe.ts).\n// Enterprise (HubSpot) sums a deal property across closed-won deals, with\n// daily SQLite snapshots for net change tracking.\n// Usage run-rate (Stripe Analytics) is trailing-28d metered usage revenue × 13.\n// Total = self-serve + enterprise + usage run-rate.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { fetchStripeARRReport } from \"./stripe.ts\";\nimport { fetchEnterpriseARR } from \"./hubspot.ts\";\nimport { fetchApiUsageRunRate } from \"./usage.ts\";\n\nexport interface ARRSegment {\n arr: number | null;\n netChange: number | null;\n netChangePct: number | null;\n breakdown?: {\n new: number;\n expansion: number;\n contraction: number;\n churn: number;\n };\n newAccounts?: string[]; // enterprise only: deals closed-won since prior day\n staleAsOf?: string; // set when the source is down and arr is the cached last good value (YYYY-MM-DD)\n note?: string; // source-supplied warning rendered under the section (e.g. fallback in use)\n}\n\nexport interface ARRReport {\n periodLabel: string;\n total: ARRSegment;\n selfServe: ARRSegment;\n enterprise: ARRSegment;\n usage: ARRSegment;\n}\n\n// ---- Last-good cache --------------------------------------------------------\n// A failing source must not take down the whole digest. Each successful fetch\n// caches its headline number; when a source is down we show that cached value,\n// clearly marked as stale, instead of \"—\". (Seen live: Stripe's preview\n// Analytics metric erroring server-side for days at a time.)\n// The self-serve entry doubles as the tiebreaker when Stripe's ARR metric and\n// our Sigma computation disagree (see resolveStripeARR in stripe.ts).\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS segment_last_good (\n segment TEXT PRIMARY KEY,\n arr_usd REAL NOT NULL,\n day TEXT NOT NULL\n )\n`);\n\nasync function rememberGood(segment: string, arr: number) {\n await sqlite.execute({\n sql: \"INSERT OR REPLACE INTO segment_last_good (segment, arr_usd, day) VALUES (?, ?, ?)\",\n args: [segment, arr, new Date().toISOString().slice(0, 10)],\n });\n}\n\nasync function lastGood(segment: string): Promise<{ arr: number; day: string } | null> {\n const res = await sqlite.execute({\n sql: \"SELECT arr_usd, day FROM segment_last_good WHERE segment = ?\",\n args: [segment],\n });\n const row = res.rows[0] as Record | undefined;\n return row ? { arr: row.arr_usd as number, day: row.day as string } : null;\n}\n\n// Segment shown when its source is down: last good value marked stale, or\n// fully blank (\"—\") if the source has never succeeded.\nasync function staleSegment(segment: string): Promise {\n const g = await lastGood(segment);\n return g\n ? { arr: g.arr, netChange: null, netChangePct: null, staleAsOf: g.day }\n : { arr: null, netChange: null, netChangePct: null };\n}\n\nconst settle = (p: Promise, label: string): Promise =>\n p.catch((err) => {\n console.error(`${label} fetch failed: ${(err as Error).message}`);\n return null;\n });\n\nexport async function buildARRReport(): Promise {\n const selfServeLastGood = (await lastGood(\"selfServe\"))?.arr ?? null;\n\n const [s, e, u] = await Promise.all([\n settle(fetchStripeARRReport({ lastGoodARR: selfServeLastGood }), \"self-serve (Stripe)\"),\n settle(fetchEnterpriseARR(), \"enterprise (HubSpot)\"),\n settle(fetchApiUsageRunRate(), \"usage run-rate (Stripe Analytics)\"),\n ]);\n\n const selfServe: ARRSegment = s\n ? {\n arr: s.asOfARR,\n netChange: s.net,\n netChangePct: s.netPct,\n breakdown: {\n new: s.new,\n expansion: s.expansion,\n contraction: s.contraction,\n churn: s.churn,\n },\n note: s.note,\n }\n : await staleSegment(\"selfServe\");\n if (s) await rememberGood(\"selfServe\", s.asOfARR);\n\n const enterprise: ARRSegment = e\n ? {\n arr: e.arr,\n netChange: e.netChange,\n netChangePct: e.netChangePct,\n newAccounts: e.newAccounts,\n }\n : await staleSegment(\"enterprise\");\n if (e) await rememberGood(\"enterprise\", e.arr);\n\n const usage: ARRSegment = u\n ? {\n arr: u.runRate,\n netChange: u.netChange,\n netChangePct: u.netChangePct,\n }\n : await staleSegment(\"usage\");\n if (u) await rememberGood(\"usage\", u.runRate);\n\n // Total = self-serve + enterprise + usage run-rate. Stale values count\n // toward the sum (better than faking a giant one-day drop); the stale note\n // on the affected section flags the caveat. Net change sums whichever\n // segments have a delta (stale segments contribute 0). Total renders \"—\"\n // only when a segment is down with no cached value at all.\n const segments = [selfServe, enterprise, usage];\n const anyDown = segments.some((seg) => seg.arr === null);\n const totalArr = anyDown\n ? null\n : segments.reduce((t, seg) => t + (seg.arr ?? 0), 0);\n const totalNetChange =\n totalArr === null || segments.every((seg) => seg.netChange === null)\n ? null\n : segments.reduce((t, seg) => t + (seg.netChange ?? 0), 0);\n const priorTotal = totalNetChange !== null && totalArr !== null\n ? totalArr - totalNetChange\n : null;\n const totalNetChangePct =\n priorTotal && totalNetChange !== null ? totalNetChange / priorTotal : null;\n\n const total: ARRSegment = {\n arr: totalArr,\n netChange: totalNetChange,\n netChangePct: totalNetChangePct,\n };\n\n const periodLabel = s?.day ?? new Date().toISOString().slice(0, 10);\n return { periodLabel, total, selfServe, enterprise, usage };\n}\n",[],[220],"d838d7dd-2287-4c15-9b89-9b165282e5d8","query-usage.ts","2fe816aa-6e48-11f1-b57c-1607ee4eb77e",89,["D",1782139174552],"// Manual validation: prints the API usage run-rate (trailing 28d × 13) and the\n// window it covers. Spot-check the window revenue against the Stripe dashboard:\n// Billing overview → Usage → \"Usage revenue\" (Last 4 weeks).\n\nimport { fetchApiUsageRunRate } from \"./usage.ts\";\n\nconst usd = (n: number) =>\n n.toLocaleString(\"en-US\", { style: \"currency\", currency: \"USD\", maximumFractionDigits: 2 });\n\nconst r = await fetchApiUsageRunRate();\nconsole.log(`Window: ${r.windowStart} → ${r.windowEnd} (28 complete days)`);\nconsole.log(`Trailing 28d usage revenue: ${usd(r.windowRevenue)}`);\nconsole.log(`API run rate (× 13): ${usd(r.runRate)}`);\n",[],[229],"164d31e8-5af3-11f1-8592-ee650bb23af1","query-stripe.ts","81fe9de0-5ad6-11f1-805b-ee650bb23af1",23,["D",1780013552266],3,"// Manual validation script — run with run_file to check Stripe ARR vs the\n// Stripe dashboard. Prints:\n// 1. Total active ARR (USD)\n// 2. Daily breakdown for the last N days: new / expansion / contraction /\n// churn / net movement, plus total ARR as of end of each day (USD).\n//\n// Source: Stripe Sigma (subscription_item_change_events_v2_beta), the same\n// table the dashboard's ARR derives from. See stripe.ts for the queries.\n\nimport { fetchStripeARR, fetchStripeARRMovementByDay } from \"./stripe.ts\";\n\nconst DAYS = 7;\n\nconst fmt = (n: number) =>\n n.toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\",\n maximumFractionDigits: 0,\n });\nconst c = (n: number) => fmt(n).padStart(12);\nconst cWide = (n: number) => fmt(n).padStart(14);\n\nconst [total, movement] = await Promise.all([\n fetchStripeARR(),\n fetchStripeARRMovementByDay(DAYS),\n]);\n\nconsole.log(\"=\".repeat(92));\nconsole.log(`Total Stripe ARR (current, all currencies -> USD): ${fmt(total)}`);\nconsole.log(\"=\".repeat(92));\nconsole.log();\nconsole.log(`Daily breakdown — last ${DAYS} days (USD):`);\nconsole.log(\n \" note: 'new' includes reactivations; net = new + expansion + contraction + churn\",\n);\nconsole.log(\n \" 'total arr' = running total ARR as of end of that day\",\n);\nconsole.log(\"-\".repeat(92));\nconsole.log(\n \"DAY\".padEnd(12) +\n \"NEW\".padStart(12) +\n \"EXPANSION\".padStart(12) +\n \"CONTRACT\".padStart(12) +\n \"CHURN\".padStart(12) +\n \"NET\".padStart(12) +\n \"TOTAL ARR\".padStart(14),\n);\nconsole.log(\"-\".repeat(92));\n\nfor (const m of movement) {\n console.log(\n m.day.padEnd(12) +\n c(m.new) +\n c(m.expansion) +\n c(m.contraction) +\n c(m.churn) +\n c(m.net) +\n cWide(m.total),\n );\n}\nconsole.log(\"-\".repeat(92));\n\nconst sum = (k: \"new\" | \"expansion\" | \"contraction\" | \"churn\" | \"net\") =>\n movement.reduce((s, m) => s + m[k], 0);\nconsole.log(\n \"SUM(flows)\".padEnd(12) +\n c(sum(\"new\")) +\n c(sum(\"expansion\")) +\n c(sum(\"contraction\")) +\n c(sum(\"churn\")) +\n c(sum(\"net\")) +\n cWide(movement[0]?.total ?? 0), // current ARR (latest day)\n);\n",[],[237],"d4257cc2-5b05-11f1-b2d4-ee650bb23af1","query-hubspot.ts","b4a981d2-5b04-11f1-b0ac-ee650bb23af1",67,["D",1780021601725],"// Manual validation script: queries HubSpot for closed-won deals across all\n// pipelines, prints per-deal amount + term + computed ACV, and shows the total\n// the bot will report. Useful for spot-checking against the source-of-truth\n// sheet whenever the multi-year math feels off.\n\nimport { fetchEnterpriseARR } from \"./hubspot.ts\";\n\nconst usd = (n: number) =>\n Math.round(n).toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\",\n maximumFractionDigits: 0,\n });\n\nconst token = Deno.env.get(\"HUBSPOT_TOKEN\")!;\nconst res = await fetch(\"https://api.hubapi.com/crm/v3/objects/deals/search\", {\n method: \"POST\",\n headers: { Authorization: `Bearer ${token}`, \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n filterGroups: [{\n filters: [{\n propertyName: \"dealstage\",\n operator: \"IN\",\n values: [\"closedwon\", \"238829588\"],\n }],\n }],\n properties: [\"dealname\", \"amount\", \"term_years\", \"pipeline\", \"closedate\"],\n limit: 100,\n }),\n});\nconst data = await res.json();\nconst pipelineLabel = (p: string) =>\n p === \"default\" ? \"Sales\" : p === \"139030823\" ? \"Trial\" : p;\n\nconst rows = (data.results ?? [])\n .map((d: any) => {\n const amount = Number(d.properties.amount ?? 0);\n const term = Number(d.properties.term_years ?? 0);\n const acv = term > 0 ? amount / term : amount;\n return {\n name: d.properties.dealname ?? \"(no name)\",\n amount,\n term,\n acv,\n pipeline: pipelineLabel(d.properties.pipeline ?? \"\"),\n closedate: (d.properties.closedate ?? \"\").slice(0, 10),\n };\n })\n .sort((a: any, b: any) => b.acv - a.acv);\n\nconsole.log(`Closed-won deals across both pipelines: ${rows.length}\\n`);\nconsole.log(\n ` ${\"name\".padEnd(55)} ${\"amount\".padStart(10)} ${\"term\".padStart(5)} ${\"ACV\".padStart(10)} pipeline closedate`,\n);\nfor (const r of rows) {\n const termStr = r.term > 0 ? `${r.term}yr` : \"—\";\n console.log(\n ` ${r.name.slice(0, 55).padEnd(55)} ${usd(r.amount).padStart(10)} ${termStr.padStart(5)} ${usd(r.acv).padStart(10)} ${r.pipeline.padEnd(7)} ${r.closedate}`,\n );\n}\n\nconst totalACV = rows.reduce((t: number, r: any) => t + r.acv, 0);\nconst totalAmount = rows.reduce((t: number, r: any) => t + r.amount, 0);\nconst dealsMissingTerm = rows.filter((r: any) => r.term === 0).length;\n\nconsole.log(`\\nTotal amount (TCV): ${usd(totalAmount)}`);\nconsole.log(`Total ACV (what we send): ${usd(totalACV)}`);\nconsole.log(`Deals missing term_years: ${dealsMissingTerm} of ${rows.length} (treated as 1yr)`);\n\nconsole.log(`\\n--- via fetchEnterpriseARR() (what the bot will report) ---`);\nconst r = await fetchEnterpriseARR();\nconsole.log(`Enterprise ARR: ${usd(r.arr)}`);\nconsole.log(`Net change vs yesterday: ${\n r.netChange === null ? \"— (no prior snapshot yet)\" : usd(r.netChange)\n}`);\n",[],[246],"5c8f53b7-8036-4753-997d-fe201e9ca363","probe-analytics.ts","d2f75846-8f50-11f1-9a8e-1607ee4eb77e",128,["D",1785771272449],"// Throwaway: systematic diagnostic of the failing Analytics usage metric.\n// Captures Stripe request IDs (req_...) for a support ticket.\n\nconst KEY = Deno.env.get(\"STRIPE_SECRET_KEY\")!;\nconst H = {\n Authorization: `Bearer ${KEY}`,\n \"Stripe-Version\": \"2026-04-22.preview\",\n \"Content-Type\": \"application/json\",\n};\n\nasync function q(label: string, body: Record) {\n const res = await fetch(\"https://api.stripe.com/v2/data/analytics/metric_query\", {\n method: \"POST\",\n headers: H,\n body: JSON.stringify(body),\n });\n const text = await res.text();\n let detail = \"\";\n try {\n const j = JSON.parse(text);\n if (res.ok) {\n const buckets = j?.data?.length ?? 0;\n const sum = (j?.data ?? []).reduce(\n (s: number, b: any) => s + (b.results ?? []).reduce((t: number, r: any) => t + Number(r.value ?? 0), 0),\n 0,\n );\n detail = `${buckets} buckets, sum $${(sum / 100).toFixed(2)}`;\n } else {\n const reqId = (j?.error?.request_log_url ?? \"\").split(\"/\").pop();\n detail = `${j?.error?.code} | ${reqId}`;\n }\n } catch {\n detail = text.slice(0, 100);\n }\n console.log(`${res.ok ? \"✅\" : \"❌\"} [${res.status}] ${label} — ${detail}`);\n}\n\nconst USAGE = \"usage_based_billing.gross_usage_revenue\";\n\n// -- Is it still failing right now? Current window.\nawait q(\"usage: current 28d window\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-07-05T00:00:00Z\",\n ends_at: \"2026-08-02T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\n\n// -- Historical windows that DEFINITELY worked before (validated Jun 22 + ran daily Jul 17-30).\nawait q(\"usage: all-June window (worked on Jun 22)\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-06-01T00:00:00Z\",\n ends_at: \"2026-06-22T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\nawait q(\"usage: May window\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-05-01T00:00:00Z\",\n ends_at: \"2026-05-15T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\n\n// -- Single day, minimal request.\nawait q(\"usage: single day Jul 1, no currency\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-07-01T00:00:00Z\",\n ends_at: \"2026-07-02T00:00:00Z\",\n granularity: \"day\",\n});\n\n// -- With group_by / filters (dimensions: price, product, customer, meter).\nawait q(\"usage: group_by meter\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-07-20T00:00:00Z\",\n ends_at: \"2026-07-27T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n group_by: [\"meter\"],\n});\n\n// -- Week/month granularity.\nawait q(\"usage: week granularity\", {\n metrics: [{ name: USAGE }],\n starts_at: \"2026-07-01T00:00:00Z\",\n ends_at: \"2026-07-29T00:00:00Z\",\n granularity: \"week\",\n currency: \"usd\",\n});\n\n// -- Control: other metrics on the same account/key/version still fine?\nawait q(\"control: revenue.mrr same window\", {\n metrics: [{ name: \"revenue.mrr\" }],\n starts_at: \"2026-07-26T00:00:00Z\",\n ends_at: \"2026-08-02T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\nawait q(\"control: revenue.arr same window\", {\n metrics: [{ name: \"revenue.arr\" }],\n starts_at: \"2026-07-26T00:00:00Z\",\n ends_at: \"2026-08-02T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\n\n// -- Both metrics in ONE request: does usage poison a mixed query?\nawait q(\"mixed: mrr + usage in one request\", {\n metrics: [{ name: \"revenue.mrr\" }, { name: USAGE }],\n starts_at: \"2026-07-26T00:00:00Z\",\n ends_at: \"2026-08-02T00:00:00Z\",\n granularity: \"day\",\n currency: \"usd\",\n});\n",[],[254],"a2082b06-5af4-11f1-9dc8-ee650bb23af1","preview.ts","a2082bba-5af4-11f1-9dc8-ee650bb23af1",29,["D",1780014216205],"// Preview the Slack ARR digest WITHOUT posting. Run with run_file.\n// Prints the plain-text fallback and the Block Kit JSON so you can eyeball the\n// exact message the bot will send.\n\nimport { buildARRReport } from \"./report.ts\";\nimport { formatARRReport } from \"./slack.ts\";\n\nconst report = await buildARRReport();\nconst { text, blocks } = formatARRReport(report);\n\nconsole.log(\"====== SLACK MESSAGE (text fallback) ======\\n\");\nconsole.log(text);\nconsole.log(\"\\n====== BLOCK KIT JSON ======\\n\");\nconsole.log(JSON.stringify(blocks, null, 2));\n",[],[262],"3dabdb9e-5af9-11f1-a1d3-ee650bb23af1","main.ts","ea9783aa-5ad4-11f1-8fe8-ee650bb23af1",39,["D",1780016195304],"interval","// arr-bot — daily Stripe + HubSpot ARR digest, posted to Slack.\n// Schedule is configured in the interval settings (default: 14:00 UTC = 10am ET).\n// See README for env var setup.\n//\n// Pipeline: report.ts assembles the cross-source ARR model -> slack.ts formats\n// it into Block Kit -> postToSlack delivers it. HubSpot (enterprise) and the\n// combined total render as \"—\" until HubSpot is wired in.\n\nimport { buildARRReport } from \"./report.ts\";\nimport { formatARRReport, postToSlack } from \"./slack.ts\";\n\nexport default async function (_interval: { lastRunAt?: Date }) {\n const report = await buildARRReport();\n const message = formatARRReport(report);\n await postToSlack(message);\n console.log(`Posted ARR digest (net change for ${report.periodLabel}):\\n${message.text}`);\n}\n",[],[270],"99bfd9ad-6c61-4f15-85cb-31dbb7e00bd2","keepalive.txt","d2e6049a-8e99-11f1-90bd-1607ee4eb77e",126,["D",1785692674453],"last keepalive: (not yet run)\n",[],[279],"22c55fc0-d24e-42f6-a0d6-7e0752b66f77","keepalive.ts","d3d8423c-8e99-11f1-8585-1607ee4eb77e",127,["D",1785692676042],"// Weekly keep-alive: Val Town's free tier pauses ALL scheduled vals after ~14\n// days without account activity — and cron runs themselves don't count, only\n// authenticated account actions do. This cron rewrites keepalive.txt via the\n// Val Town REST API once a week; the API edit registers as account activity\n// and resets the inactivity clock, so the daily digest cron never pauses.\n//\n// Requires VAL_TOWN_API_KEY — create at https://www.val.town/settings/api\n// with \"Val: Read and write\" scope.\n//\n// Chicken-and-egg caveat: if crons are ever paused anyway (e.g. the token\n// expired), this can't revive them — any manual edit/run un-pauses everything,\n// after which this takes over again.\n\nconst TOKEN = Deno.env.get(\"VAL_TOWN_API_KEY\");\n\nexport default async function () {\n if (!TOKEN) throw new Error(\"Missing VAL_TOWN_API_KEY env var\");\n const headers = {\n Authorization: `Bearer ${TOKEN}`,\n \"Content-Type\": \"application/json\",\n };\n\n // Resolve this val's id by alias (org handle / val name).\n const aliasRes = await fetch(\n \"https://api.val.town/v2/alias/vals/consensus/arr-bot\",\n { headers },\n );\n if (!aliasRes.ok) {\n throw new Error(`alias lookup ${aliasRes.status}: ${await aliasRes.text()}`);\n }\n const { id } = await aliasRes.json();\n\n const res = await fetch(\n `https://api.val.town/v2/vals/${id}/files?path=keepalive.txt`,\n {\n method: \"PUT\",\n headers,\n body: JSON.stringify({\n content: `last keepalive: ${new Date().toISOString()}\\n`,\n }),\n },\n );\n if (!res.ok) {\n throw new Error(`keepalive edit ${res.status}: ${await res.text()}`);\n }\n console.log(`Keepalive edit OK at ${new Date().toISOString()}`);\n}\n",[],[287],"b6f19dfe-3048-4b60-9efa-d7ab234a86a8","hubspot.ts","f3a0248e-5ad4-11f1-b57f-ee650bb23af1",103,["D",1784250371119],13,"// Fetch enterprise ARR from HubSpot — sum of (amount / term_years) across\n// closed-won deals in BOTH pipelines (Sales + Trial), with daily SQLite\n// snapshots for net change tracking.\n//\n// Consensus's HubSpot:\n// - Two pipelines: Sales Pipeline (id=\"default\", closed-won stage \"closedwon\")\n// and Trial Pipeline (id=139030823, closed-won stage 238829588).\n// - hs_arr / hs_acv / hs_tcv are not populated (Sales Hub Pro feature),\n// so we derive ACV ourselves: `amount / term_years`.\n// - `term_years` is a custom deal property maintained by sales (Karthik/Eric).\n// When blank or 0, we fall back to `amount` (single-year assumption).\n//\n// Auth: HUBSPOT_TOKEN must be a Service Key (or Private App access token) with\n// scope `crm.objects.deals.read`. Create at app.hubspot.com → Settings →\n// Integrations → Service Keys.\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nconst HUBSPOT_API = \"https://api.hubapi.com\";\n\n// ---- SQLite snapshot tables -------------------------------------------------\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS enterprise_arr_daily (\n day TEXT PRIMARY KEY,\n arr_usd REAL NOT NULL\n )\n`);\n\n// Per-day set of closed-won deals, so each run can diff against the prior\n// day's set and report newly signed accounts by name.\nawait sqlite.execute(`\n CREATE TABLE IF NOT EXISTS enterprise_deals_daily (\n day TEXT NOT NULL,\n deal_id TEXT NOT NULL,\n deal_name TEXT NOT NULL,\n PRIMARY KEY (day, deal_id)\n )\n`);\n\nfunction todayStr(): string {\n return new Date().toISOString().slice(0, 10);\n}\n\nfunction yesterdayStr(): string {\n const d = new Date();\n d.setDate(d.getDate() - 1);\n return d.toISOString().slice(0, 10);\n}\n\n// Closed-won stage IDs across all deal pipelines.\n// Discovered via GET /crm/v3/pipelines/deals (run-once probe; not in this repo).\nconst CLOSED_WON_STAGES = [\n \"closedwon\", // Sales Pipeline\n \"238829588\", // Trial Pipeline → \"Closed Won\"\n];\n\n// ---- HubSpot deal fetching --------------------------------------------------\n// Returns every closed-won deal (across all pipelines) with the requested\n// properties. The search endpoint's `IN` operator on `dealstage` lets us span\n// pipelines in one query.\nasync function fetchClosedWonDeals(\n token: string,\n properties: string[],\n): Promise<{ id: string; properties: Record }[]> {\n const out: { id: string; properties: Record }[] = [];\n let after: string | undefined;\n\n while (true) {\n const body: Record = {\n filterGroups: [\n {\n filters: [\n { propertyName: \"dealstage\", operator: \"IN\", values: CLOSED_WON_STAGES },\n ],\n },\n ],\n properties,\n limit: 100,\n };\n if (after) body.after = after;\n\n const res = await fetch(`${HUBSPOT_API}/crm/v3/objects/deals/search`, {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${token}`,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify(body),\n });\n if (!res.ok) {\n throw new Error(`HubSpot ${res.status}: ${await res.text()}`);\n }\n\n const data = await res.json() as {\n results: { id: string; properties: Record }[];\n paging?: { next?: { after: string } };\n };\n\n out.push(...data.results);\n after = data.paging?.next?.after;\n if (!after) break;\n }\n\n return out;\n}\n\n// ---- Public API -------------------------------------------------------------\nexport interface EnterpriseARRResult {\n arr: number;\n netChange: number | null;\n netChangePct: number | null;\n newAccounts: string[]; // deals closed-won since the prior day's snapshot\n}\n\n// ACV per deal: amount / term_years. Falls back to amount when term_years is\n// missing or 0 (treats blank as single-year).\nfunction dealACV(props: Record): number {\n const amount = Number(props.amount ?? 0);\n const term = Number(props.term_years ?? 0);\n return term > 0 ? amount / term : amount;\n}\n\nexport async function fetchEnterpriseARR(): Promise {\n const token = Deno.env.get(\"HUBSPOT_TOKEN\");\n if (!token) throw new Error(\"Missing HUBSPOT_TOKEN env var\");\n\n const deals = await fetchClosedWonDeals(token, [\"dealname\", \"amount\", \"term_years\"]);\n const totalARR = deals.reduce((t, d) => t + dealACV(d.properties), 0);\n\n // Store today's snapshot.\n await sqlite.execute({\n sql: \"INSERT OR REPLACE INTO enterprise_arr_daily (day, arr_usd) VALUES (?, ?)\",\n args: [todayStr(), totalARR],\n });\n\n // Store today's deal set and diff against yesterday's to find new accounts.\n const dealName = (d: { properties: Record }) =>\n d.properties.dealname ?? \"(unnamed deal)\";\n if (deals.length > 0) {\n await sqlite.batch(deals.map((d) => ({\n sql: \"INSERT OR REPLACE INTO enterprise_deals_daily (day, deal_id, deal_name) VALUES (?, ?, ?)\",\n args: [todayStr(), d.id, dealName(d)],\n })));\n }\n\n const prevDeals = await sqlite.execute({\n sql: \"SELECT deal_id FROM enterprise_deals_daily WHERE day = ?\",\n args: [yesterdayStr()],\n });\n let newAccounts: string[] = [];\n if (prevDeals.rows.length > 0) {\n // Only diff when a prior snapshot exists — on the very first run every\n // deal would otherwise read as \"new\".\n const prevIds = new Set(\n prevDeals.rows.map((r) => String((r as Record).deal_id)),\n );\n newAccounts = deals.filter((d) => !prevIds.has(d.id)).map(dealName);\n }\n\n // Look up yesterday's value for net change.\n const prior = await sqlite.execute({\n sql: \"SELECT arr_usd FROM enterprise_arr_daily WHERE day = ?\",\n args: [yesterdayStr()],\n });\n\n const priorARR = prior.rows.length > 0\n ? (prior.rows[0] as Record).arr_usd as number\n : undefined;\n const netChange = priorARR !== undefined ? totalARR - priorARR : null;\n const netChangePct = priorARR && netChange !== null\n ? netChange / priorARR\n : null;\n\n return { arr: totalARR, netChange, netChangePct, newAccounts };\n}\n",[],[295],["D",1789092619737],[],[18],"f9a88c42-aba6-4be0-b1ab-2b460bccf024","README.md","fbca5e22-5ad4-11f1-8ac7-ee650bb23af1",162,["D",1789092865965],9,"# arr-bot\n\nDaily Stripe + HubSpot ARR digest, posted to Slack (#general).\n\n## Required env vars\n\nSet these in the Val Town env var editor (one click per link):\n\n👉 Add STRIPE_SECRET_KEY here: https://www.val.town/x/consensus/arr-bot/environment-variables?key=STRIPE_SECRET_KEY\n\n👉 Add HUBSPOT_TOKEN here: https://www.val.town/x/consensus/arr-bot/environment-variables?key=HUBSPOT_TOKEN\n\n👉 Add VAL_TOWN_API_KEY here: https://www.val.town/x/consensus/arr-bot/environment-variables?key=VAL_TOWN_API_KEY\n\nSLACK_TOKEN is auto-configured by Val Town's Slack integration (connect via the val dashboard).\n\n## Where to create credentials\n\n- **Stripe** — https://dashboard.stripe.com/apikeys → create a Restricted Key. Needs `reporting_write` + `sigma_api_write` (shown as **Sigma** + **Sigma API**) plus **Analytics read** (for the usage run-rate metric), and an active Sigma subscription. ARR is read from Sigma, not the Subscriptions REST endpoint, so it matches the dashboard.\n- **HubSpot** — app.hubspot.com → ⚙️ Settings → Integrations → **Service Keys** (public beta as of Feb 2026; this replaced Private Apps for data-only integrations). Create a key with scope `crm.objects.deals.read`. Token format `pat-na1-…`. If Service Keys aren't enabled on your account yet, a Private App access token with the same scope works identically — same `pat-na1-` prefix, same `Authorization: Bearer …` header.\n- **Slack** — connected via Val Town's dashboard Slack integration. Posts to `#general` (channel ID hardcoded in `slack.ts`).\n- **Val Town** — https://www.val.town/settings/api → create an API token with **Vals: Read and write** scope (used by the weekly keep-alive, see below).\n\n## HubSpot data model\n\nARR is summed across **closed-won deals in both pipelines**:\n- Sales Pipeline (`default`, stage `closedwon`)\n- Trial Pipeline (`139030823`, stage `238829588`)\n\nHubSpot's built-in `hs_arr` / `hs_acv` / `hs_tcv` properties aren't populated on these deals (require Sales Hub Pro), so per-deal ACV is derived as `amount / term_years`:\n- `amount` — built-in. For single-year deals this is the ACV; for multi-year deals it stores TCV.\n- `term_years` — **custom property maintained by sales** (Karthik/Eric). Required for multi-year deals to report accurate ARR. Blank/0 is treated as 1-year (sensible default).\n\nIf `term_years` isn't yet created in HubSpot, the digest will overcount multi-year deals as TCV. See `query-hubspot.ts` for a per-deal validation dump.\n\nNet change for the enterprise segment uses daily snapshots stored in Val Town's per-val SQLite (`enterprise_arr_daily` table, keyed by day).\n\n## Files\n\n- `main.ts` — interval handler (cron entry point); builds the report and posts it to Slack\n- `report.ts` — `buildARRReport()` assembles the cross-source model (self-serve + enterprise + total)\n- `stripe.ts` — self-serve ARR: Stripe's own `revenue.arr` / `revenue_growth.arr` Analytics metrics (headline + daily new / expansion / contraction / churn), with our Sigma computation (cumulative `mrr_change`, FX → USD, × 12) as fallback and cross-check; `resolveStripeARR()` picks which to show\n- `sigma.ts` — `runSigmaQuery()` client for the Stripe Sigma Query Run API (submit → poll → download CSV)\n- `hubspot.ts` — `fetchEnterpriseARR()` sums `amount / term_years` across both pipelines' closed-won stages, snapshots daily for net change + new-account names\n- `usage.ts` — `fetchApiUsageRunRate()` pulls the dashboard's \"Usage revenue\" metric from the Stripe Analytics API; run rate = trailing 28 complete days × 13\n- `analytics.ts` — shared Stripe Analytics API client: serializes metric queries (Stripe allows one per second per key) and retries 429s\n- `slack.ts` — `formatARRReport()` builds the Block Kit digest; `postToSlack()` delivers via Slack Web API\n- `query-stripe.ts` — manual validation script: prints total ARR + a daily breakdown table\n- `query-hubspot.ts` — manual validation script: prints per-deal amount/term/ACV + totals so you can spot-check against the source-of-truth sheet\n- `query-usage.ts` — manual validation script: prints the usage run-rate window vs the Billing → Usage dashboard\n- `preview.ts` — renders the Slack message without posting (dry run)\n- `keepalive.ts` + `keepalive.txt` — weekly cron that rewrites the txt file via the Val Town REST API; the authenticated edit counts as account activity, preventing the free-tier ~14-day inactivity pause that silently stops all crons (bit us Jul 31–Aug 1). Requires `VAL_TOWN_API_KEY`. The txt file's content on the val drifts from git by design.\n\n## Self-serve ARR: two sources, one answer\n\nThe headline comes from **Stripe's own `revenue.arr` metric** (the dashboard's\nARR) and the daily breakdown from `revenue_growth.arr`. Our **Sigma computation**\n(cumulative `mrr_change` over `subscription_item_change_events_v2_beta`, FX →\nUSD, × 12) runs alongside it every time. `resolveStripeARR()` in `stripe.ts`\ndecides what to show:\n\n- both agree within 5% → Stripe's metric\n- the metric errors → Sigma, with a \"⚠️ … computed from Sigma\" note\n- they disagree → whichever is within 5% of the last validated value (kept in\n SQLite), with a note naming both numbers; if neither is, the run refuses the\n number and the stale-value fallback below kicks in with a warning\n\nWhy both: on 2026-09-09 Stripe backfilled `TRIAL_START` / `TRIAL_END` /\n`CONVERT_TRIAL` rows into the Sigma table; `CONVERT_TRIAL` double-counts MRR\nthat `ACTIVE_START` already carries, which inflated the naive sum by ~73% for\ntwo digests. In Aug 2026 the Analytics API's usage metric was broken\nserver-side for a month. Neither source alone has earned blind trust. The Sigma\nqueries are also restricted to an explicit `ACTIVE_*` event-type allow-list.\n\nStripe allows one Analytics metric query per second per key; `analytics.ts`\nserializes all of them and retries 429s.\n\n## Outage behavior\n\nEach source fetch fails independently. A failing segment shows its last\ngood headline number (cached in SQLite) with a \"⚠️ Source unavailable or failed validation\" note and\n\"—\" for net change; the Total still sums using the cached value. A segment\nthat has never succeeded shows \"—\" and blanks the Total.\n\n## Schedule\n\nInterval is set in the val's interval settings (configured separately from file content). Currently: daily at 01:00 UTC (6pm PT). Note: Val Town cron is UTC-only and does not adjust for DST, so this is 6pm during PDT (summer) and 5pm during PST (winter).\n",[],[307],["D",1780000503639],["D",1789509043237],{"_11":12,"_125":-5},{"_11":12,"_120":121,"_70":-5,"_30":-5,"_122":123,"_124":24},{"_38":111,"_13":16,"_32":35,"_112":73,"_113":-5,"_114":-5,"_115":-5,"_116":-5,"_117":-5,"_118":14,"_119":12},"ctx",{"_53":-5,"_144":353,"_354":355,"_356":357,"_358":24,"_359":24,"_360":24,"_361":150,"_362":-5,"_363":-7,"_364":-7,"_365":26,"_366":24,"_367":24,"_368":-5,"_369":24,"_370":24,"_371":24,"_372":-5},"ENV",{"_324":325,"_326":327,"_328":16,"_329":330,"_331":332,"_333":334,"_335":336,"_337":338,"_339":340,"_341":342,"_343":344,"_345":346,"_347":348,"_349":350,"_351":352},"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"]

History · analytics.ts