[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_343":344,"_345":346},"routes/_app.x.$handle.$name",{"_97":96,"_32":44,"_105":87,"_27":106,"_13":14,"_107":-5,"_11":108,"_109":110,"_111":112,"_113":114,"_115":116,"_20":-5,"_117":-5,"_118":-5,"_119":26,"_120":121,"_122":26,"_123":-5,"_124":-5,"_125":126},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_42":43,"_32":44,"_37":45,"_46":-5,"_47":26,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":-5,"_56":26,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":64,"_65":66,"_67":68,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":80,"_81":82,"_83":84,"_85":26},"version",139,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":26,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":36,"_37":38,"_39":14,"_40":41,"_23":26},"previous","deleted",true,"deletedHere",false,"path","refund-check.ts","latestPath","fileId","01239fa8-a262-11f1-b3cf-1607ee4eb77e","name","fileType","script","content","// Throwaway: verify gross volume does NOT exclude refunded charges.\n// Same window (28 complete days ending yesterday). Shows that charges later\n// refunded are still fully counted in the gross sum.\n\nimport { runSigmaQuery } from \"./sigma.ts\";\n\nconst SQL = `\nWITH fx 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),\nw AS (\n SELECT c.amount, c.amount_refunded, c.refunded, c.currency, DATE(c.created) AS day\n FROM charges c\n WHERE c.paid = true AND c.captured = true\n AND DATE(c.created) >= CURRENT_DATE - INTERVAL '28' DAY\n AND DATE(c.created) < CURRENT_DATE\n),\nconv AS (\n SELECT w.amount / COALESCE(element_at(f.rate, w.currency), element_at(lf.rate, w.currency)) / 100.0 AS usd,\n w.amount_refunded / COALESCE(element_at(f.rate, w.currency), element_at(lf.rate, w.currency)) / 100.0 AS refunded_usd,\n (COALESCE(w.amount_refunded, 0) > 0) AS was_refunded\n FROM w\n LEFT JOIN fx f ON f.date = w.day\n CROSS JOIN fx_latest lf\n)\nSELECT\n ROUND(SUM(usd), 2) AS gross_28d,\n COUNT(*) AS n_charges,\n COUNT(IF(was_refunded, 1)) AS n_refunded_charges,\n ROUND(SUM(IF(was_refunded, usd, 0)), 2) AS refunded_charges_gross_usd,\n ROUND(SUM(refunded_usd), 2) AS amount_refunded_usd\nFROM conv\n`;\n\nconst { rows } = await runSigmaQuery(SQL);\nconst r = rows[0] as Record;\nconst usd = (v: string) =>\n Number(v).toLocaleString(\"en-US\", { style: \"currency\", currency: \"USD\", maximumFractionDigits: 2 });\n\nconsole.log(`Gross 28d (all paid+captured charges): ${usd(r.gross_28d)} (${r.n_charges} charges)`);\nconsole.log(`Charges in window later (partially) refunded: ${r.n_refunded_charges} charges, ${usd(r.refunded_charges_gross_usd)} of gross`);\nconsole.log(`Amount refunded on those in-window charges: ${usd(r.amount_refunded_usd)}`);\nconsole.log(``);\nconsole.log(`If gross excluded refunded charges it would be ${usd(String(Number(r.gross_28d) - Number(r.refunded_charges_gross_usd)))} — it doesn't.`);\n","createdAt",["D",1787867723469],"branchVersion","branchId","b4b4d058-5ad4-11f1-9419-ee650bb23af1","id","b4b31b00-5ad4-11f1-9419-ee650bb23af1","arr-bot",["D",1780000503639],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Slack bot: daily Stripe + HubSpot ARR report","imageUrl","8ad5f245-a3c1-4df3-9e50-f79fb44e9037","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","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","user",{"_97":96,"_104":-5},"org",{"_97":96,"_98":99,"_100":-5,"_18":-5,"_101":102,"_103":26},"branch",{"_13":86,"_32":87,"_42":41,"_88":43,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":44,"_95":96},"isOwned",163,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","consensus","handle","displayName","Consensus","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_42":43,"_32":44,"_37":338,"_46":-5,"_47":26,"_48":49,"_50":49,"_51":52,"_53":-5,"_18":54,"_55":-5,"_56":26,"_57":-5,"_58":-5,"_59":60,"_61":62,"_63":64,"_65":66,"_67":339,"_69":-5,"_70":71,"_72":71,"_73":74,"_75":74,"_76":74,"_77":71,"_78":71,"_79":340,"_81":341,"_83":342,"_85":26},"tree",[273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288],"files",[273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288],"counts",{"_263":60,"_264":150,"_265":60,"_266":267,"_268":269},"contents",[127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_42":255,"_32":256,"_30":257,"_146":-5,"_40":41,"_39":258,"_148":26,"_37":259,"_13":260,"_33":20,"_35":261,"_23":26,"_152":-5,"_27":262},{"_42":247,"_32":248,"_30":249,"_146":-5,"_40":41,"_39":250,"_148":24,"_37":251,"_13":252,"_33":34,"_35":253,"_23":26,"_152":-5,"_27":254},{"_42":240,"_32":241,"_30":242,"_146":-5,"_40":41,"_39":243,"_148":24,"_37":244,"_13":60,"_33":230,"_35":245,"_23":26,"_152":-5,"_27":246},{"_42":233,"_32":234,"_30":235,"_146":-5,"_40":41,"_39":236,"_148":24,"_37":237,"_13":60,"_33":20,"_35":238,"_23":26,"_152":-5,"_27":239},{"_42":225,"_32":226,"_30":227,"_146":-5,"_40":41,"_39":228,"_148":24,"_37":229,"_13":208,"_33":230,"_35":231,"_23":26,"_152":-5,"_27":232},{"_42":218,"_32":219,"_30":220,"_146":-5,"_40":41,"_39":221,"_148":24,"_37":222,"_13":60,"_33":34,"_35":223,"_23":26,"_152":-5,"_27":224},{"_42":211,"_32":212,"_30":213,"_146":-5,"_40":41,"_39":214,"_148":24,"_37":215,"_13":60,"_33":34,"_35":216,"_23":26,"_152":-5,"_27":217},{"_42":203,"_32":204,"_30":205,"_146":-5,"_40":41,"_39":206,"_148":24,"_37":207,"_13":208,"_33":34,"_35":209,"_23":26,"_152":-5,"_27":210},{"_42":195,"_32":196,"_30":197,"_146":-5,"_40":41,"_39":198,"_148":24,"_37":199,"_13":200,"_33":34,"_35":201,"_23":26,"_152":-5,"_27":202},{"_42":188,"_32":189,"_30":190,"_146":-5,"_40":41,"_39":191,"_148":24,"_37":192,"_13":60,"_33":34,"_35":193,"_23":26,"_152":-5,"_27":194},{"_42":185,"_32":28,"_30":31,"_146":-5,"_40":41,"_39":14,"_148":26,"_37":186,"_13":60,"_33":34,"_35":36,"_23":26,"_152":-5,"_27":187},{"_42":177,"_32":178,"_30":179,"_146":-5,"_40":41,"_39":180,"_148":26,"_37":181,"_13":182,"_33":34,"_35":183,"_23":26,"_152":-5,"_27":184},{"_42":170,"_32":171,"_30":172,"_146":-5,"_40":41,"_39":173,"_148":24,"_37":174,"_13":60,"_33":34,"_35":175,"_23":26,"_152":-5,"_27":176},{"_42":162,"_32":163,"_30":164,"_146":-5,"_40":41,"_39":165,"_148":26,"_37":166,"_13":167,"_33":34,"_35":168,"_23":26,"_152":-5,"_27":169},{"_42":154,"_32":155,"_30":156,"_146":-5,"_40":41,"_39":157,"_148":26,"_37":158,"_13":159,"_33":34,"_35":160,"_23":26,"_152":-5,"_27":161},{"_42":143,"_32":144,"_30":145,"_146":-5,"_40":41,"_39":147,"_148":26,"_37":149,"_13":150,"_33":34,"_35":151,"_23":26,"_152":-5,"_27":153},"c13e4033-0ceb-41e7-bd71-8ee6f1bf53a0","usage.ts","2e542248-6e48-11f1-971d-1607ee4eb77e","parentId",91,"latest",["D",1782139607619],1,"// 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).\n\nconst ANALYTICS_URL = \"https://api.stripe.com/v2/data/analytics/metric_query\";\nconst VERSION = \"2026-04-22.preview\";\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 key = Deno.env.get(\"STRIPE_SECRET_KEY\");\n if (!key) throw new Error(\"Missing STRIPE_SECRET_KEY env var\");\n\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 res = await fetch(ANALYTICS_URL, {\n method: \"POST\",\n headers: {\n Authorization: `Bearer ${key}`,\n \"Stripe-Version\": VERSION,\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\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 if (!res.ok) {\n throw new Error(`Stripe Analytics ${res.status}: ${await res.text()}`);\n }\n\n const data = await res.json() as {\n data?: { timestamp?: string; results?: { value?: number }[] }[];\n };\n\n const todayStr = endsAt.toISOString().slice(0, 10);\n const series = (data.data ?? [])\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","label",[144],"e4178caa-5af7-11f1-bb95-ee650bb23af1","stripe.ts","ee25b5fa-5ad4-11f1-909e-ee650bb23af1",37,["D",1780015615508],8,"// Stripe ARR via Sigma (Reports API v2 Query Runs) — matches the dashboard.\n//\n// Why Sigma instead of summing the /v1/subscriptions REST endpoint:\n// The dashboard's MRR/ARR is NOT a naive sum of subscription line items.\n// Stripe pre-computes a signed `mrr_change` for every subscription-item\n// event (already accounting for discounts, trials=$0, proration, quantity,\n// upgrades/downgrades, cancellations) in the table\n// `subscription_item_change_events_v2_beta`. Cumulatively summing those\n// deltas reproduces the dashboard MRR exactly; ARR = MRR * 12.\n// This is the same source Stripe's own \"Total ARR\" Sigma template uses.\n//\n// It's also multi-currency: mrr_change is in each sub's own currency, so we\n// convert to USD using the daily `exchange_rates_from_usd` table (mirroring\n// the dashboard's FX handling).\n//\n// All amounts returned in US dollars.\n\nimport { runSigmaQuery } from \"./sigma.ts\";\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\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\nconst minorToDollars = (s: string | undefined) => Number(s ?? 0) / 100;\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// ---- Total current ARR ------------------------------------------------------\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 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// ---- Daily ARR movement, bucketed ------------------------------------------\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),\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// ---- Total ARR as of the end of each day -----------------------------------\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 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\n// ---- Assembled report for the daily digest ---------------------------------\n// Headline ARR = current total; \"net change\" covers the most recent complete\n// day (the day before today), with % expressed against that day's starting ARR.\nexport interface StripeARRReport {\n asOfARR: number; // current self-serve ARR (all currencies -> USD)\n day: string; // the day the net change covers (YYYY-MM-DD)\n net: number;\n netPct: number; // fraction of prior-day ARR (0.0001 = 0.01%)\n new: number;\n expansion: number;\n contraction: number;\n churn: number;\n}\n\nexport async function fetchStripeARRReport(): 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",[155],"a1f05c4d-b28c-4988-8047-d5b803fa1ac3","slack.ts","f6198156-5ad4-11f1-adee-ee650bb23af1",123,["D",1785692604348],24,"// 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// Outage note shown when a segment is rendering its cached last-good value.\nfunction staleNote(seg: ARRSegment): string | undefined {\n return seg.staleAsOf\n ? `⚠️ Source outage — showing last good value (${seg.staleAsOf})`\n : undefined;\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 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 ...(staleNote(r.selfServe) ? [` ${staleNote(r.selfServe)}`] : []),\n ``,\n `${EMOJI.enterprise} Enterprise ARR (HubSpot): ${arrText(r.enterprise)}`,\n ` ${netLine(r.enterprise)}`,\n ...(r.enterprise.newAccounts ?? []).map((n) => ` ${n}`),\n ...(staleNote(r.enterprise) ? [` ${staleNote(r.enterprise)}`] : []),\n ``,\n `${EMOJI.usage} Usage Revenue Run Rate (Stripe): ${arrText(r.usage)}`,\n ` ${netLine(r.usage)}`,\n ...(staleNote(r.usage) ? [` ${staleNote(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 stale = staleNote(seg);\n if (stale) {\n blocks.push({\n type: \"context\",\n elements: [{ type: \"mrkdwn\", text: stale }],\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",[163],"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",[171],"ba43d37a-928c-4b7f-b1dd-b558c613ac53","report.ts","8ce396ca-5af4-11f1-9141-ee650bb23af1",120,["D",1785692586029],9,"// Cross-source ARR report model assembled for the Slack digest.\n//\n// Self-serve (Stripe) is live via Sigma.\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}\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.)\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\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 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\n ? {\n arr: row.arr_usd as number,\n netChange: null,\n netChangePct: null,\n staleAsOf: row.day as string,\n }\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 [s, e, u] = await Promise.all([\n settle(fetchStripeARRReport(), \"self-serve (Stripe Sigma)\"),\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 }\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",[178],"1958bb06-16ea-4b74-8ff2-cca0c005df99",["D",1787867723469],[28],"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",[189],"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",[196],"d4257cc2-5b05-11f1-b2d4-ee650bb23af1","query-hubspot.ts","b4a981d2-5b04-11f1-b0ac-ee650bb23af1",67,["D",1780021601725],2,"// 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",[204],"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",[212],"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",[219],"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",[226],"99bfd9ad-6c61-4f15-85cb-31dbb7e00bd2","keepalive.txt","d2e6049a-8e99-11f1-90bd-1607ee4eb77e",126,["D",1785692674453],"last keepalive: (not yet run)\n",[234],"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",[241],"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",[248],"9580e160-5afa-11f1-a203-ee650bb23af1","README.md","fbca5e22-5ad4-11f1-8ac7-ee650bb23af1",44,["D",1780016772167],5,"# arr-bot\n\nDaily Stripe + HubSpot ARR digest, posted to Slack.\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\nSLACK_TOKEN is auto-configured by Val Town's Slack integration (connect via the val dashboard).\n\n## Optional env vars\n\n👉 Add HUBSPOT_ARR_PROPERTY here: https://www.val.town/x/consensus/arr-bot/environment-variables?key=HUBSPOT_ARR_PROPERTY\n\n👉 Add HUBSPOT_PIPELINE_STAGE here: https://www.val.town/x/consensus/arr-bot/environment-variables?key=HUBSPOT_PIPELINE_STAGE\n\n- `HUBSPOT_ARR_PROPERTY` — deal property summed for ARR. Default `amount`. Set to `hs_arr` or your custom property if different.\n- `HUBSPOT_PIPELINE_STAGE` — dealstage filter. Default `closedwon`. Use the internal stage ID if you have custom pipelines.\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**), and an active Sigma subscription. ARR is read from Sigma, not the Subscriptions REST endpoint, so it matches the dashboard.\n- **HubSpot** — https://app.hubspot.com/private-apps → create Private App with scope `crm.objects.deals.read`.\n- **Slack** — connected via Val Town's dashboard Slack integration. Posts to `#test` (channel ID hardcoded in `slack.ts`).\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 via Stripe Sigma: current total + daily movement (new / expansion / contraction / churn), multi-currency → USD. Matches the dashboard (sums pre-computed `mrr_change`, × 12).\n- `sigma.ts` — `runSigmaQuery()` client for the Stripe Sigma Query Run API (submit → poll → download CSV)\n- `hubspot.ts` — `fetchHubSpotARR()` sums a deal property across a pipeline stage (enterprise segment; not yet wired into the digest)\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- `preview.ts` — renders the Slack message without posting (dry run)\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",[256],"pulls","branches","forks","envVars",{"_270":208,"_272":60},"connections",{"_270":208,"_271":60},"count","unfilledCount","emptyCount",{"_42":255,"_32":256,"_30":257,"_146":-5,"_40":41,"_39":258,"_148":26,"_37":335,"_13":260,"_33":20,"_35":261,"_23":26,"_152":-5,"_290":336,"_27":337},{"_42":247,"_32":248,"_30":249,"_146":-5,"_40":41,"_39":250,"_148":24,"_37":332,"_13":252,"_33":34,"_35":253,"_23":26,"_152":-5,"_290":333,"_27":334},{"_42":240,"_32":241,"_30":242,"_146":-5,"_40":41,"_39":243,"_148":24,"_37":329,"_13":60,"_33":230,"_35":245,"_23":26,"_152":-5,"_290":330,"_27":331},{"_42":233,"_32":234,"_30":235,"_146":-5,"_40":41,"_39":236,"_148":24,"_37":326,"_13":60,"_33":20,"_35":238,"_23":26,"_152":-5,"_290":327,"_27":328},{"_42":225,"_32":226,"_30":227,"_146":-5,"_40":41,"_39":228,"_148":24,"_37":323,"_13":208,"_33":230,"_35":231,"_23":26,"_152":-5,"_290":324,"_27":325},{"_42":218,"_32":219,"_30":220,"_146":-5,"_40":41,"_39":221,"_148":24,"_37":320,"_13":60,"_33":34,"_35":223,"_23":26,"_152":-5,"_290":321,"_27":322},{"_42":211,"_32":212,"_30":213,"_146":-5,"_40":41,"_39":214,"_148":24,"_37":317,"_13":60,"_33":34,"_35":216,"_23":26,"_152":-5,"_290":318,"_27":319},{"_42":203,"_32":204,"_30":205,"_146":-5,"_40":41,"_39":206,"_148":24,"_37":314,"_13":208,"_33":34,"_35":209,"_23":26,"_152":-5,"_290":315,"_27":316},{"_42":195,"_32":196,"_30":197,"_146":-5,"_40":41,"_39":198,"_148":24,"_37":311,"_13":200,"_33":34,"_35":201,"_23":26,"_152":-5,"_290":312,"_27":313},{"_42":188,"_32":189,"_30":190,"_146":-5,"_40":41,"_39":191,"_148":24,"_37":308,"_13":60,"_33":34,"_35":193,"_23":26,"_152":-5,"_290":309,"_27":310},{"_42":185,"_32":28,"_30":31,"_146":-5,"_40":41,"_39":14,"_148":26,"_37":305,"_13":60,"_33":34,"_35":36,"_23":26,"_152":-5,"_290":306,"_27":307},{"_42":177,"_32":178,"_30":179,"_146":-5,"_40":41,"_39":180,"_148":26,"_37":302,"_13":182,"_33":34,"_35":183,"_23":26,"_152":-5,"_290":303,"_27":304},{"_42":170,"_32":171,"_30":172,"_146":-5,"_40":41,"_39":173,"_148":24,"_37":299,"_13":60,"_33":34,"_35":175,"_23":26,"_152":-5,"_290":300,"_27":301},{"_42":162,"_32":163,"_30":164,"_146":-5,"_40":41,"_39":165,"_148":26,"_37":296,"_13":167,"_33":34,"_35":168,"_23":26,"_152":-5,"_290":297,"_27":298},{"_42":154,"_32":155,"_30":156,"_146":-5,"_40":41,"_39":157,"_148":26,"_37":293,"_13":159,"_33":34,"_35":160,"_23":26,"_152":-5,"_290":294,"_27":295},{"_42":143,"_32":144,"_30":145,"_146":-5,"_40":41,"_39":147,"_148":26,"_37":289,"_13":150,"_33":34,"_35":151,"_23":26,"_152":-5,"_290":291,"_27":292},["D",1782139607619],"children",[],[144],["D",1780015615508],[],[155],["D",1785692604348],[],[163],["D",1780012708937],[],[171],["D",1785692586029],[],[178],["D",1787867723469],[],[28],["D",1782139174552],[],[189],["D",1780013552266],[],[196],["D",1780021601725],[],[204],["D",1785771272449],[],[212],["D",1780014216205],[],[219],["D",1780016195304],[],[226],["D",1785692674453],[],[234],["D",1785692676042],[],[241],["D",1784250371119],[],[248],["D",1780016772167],[],[256],["D",1780000503639],["D",1789509043237],{"_97":96,"_104":-5},{"_97":96,"_98":99,"_100":-5,"_18":-5,"_101":102,"_103":26},{"_13":86,"_32":87,"_42":41,"_88":43,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":44,"_95":96},"ctx",{"_79":-5,"_124":376,"_377":378,"_379":380,"_381":26,"_382":26,"_383":26,"_384":24,"_385":-5,"_386":-7,"_387":-7,"_388":60,"_389":26,"_390":26,"_391":-5,"_392":26,"_393":26,"_394":26,"_395":-5},"ENV",{"_347":348,"_349":350,"_351":87,"_352":353,"_354":355,"_356":357,"_358":359,"_360":361,"_362":363,"_364":365,"_366":367,"_368":369,"_370":371,"_372":373,"_374":375},"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"]

Diff · v139