[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_309":310,"_311":312},"routes/_app.x.$handle.$name",{"_98":97,"_32":45,"_106":88,"_27":107,"_13":14,"_108":-5,"_11":109,"_110":111,"_112":113,"_114":115,"_116":117,"_20":-5,"_118":-5,"_119":-5,"_120":25,"_121":122,"_123":25,"_124":-5,"_125":-5,"_126":127},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_32":45,"_38":46,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":81,"_82":83,"_84":85,"_86":25},"version",117,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":37,"_38":39,"_40":14,"_41":42,"_24":25},"previous",{"_30":31,"_32":28,"_33":34,"_35":36},"deleted",false,"deletedHere","path","report.ts","latestPath","fileId","8ce396ca-5af4-11f1-9141-ee650bb23af1","name","fileType","script","content","// 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 { 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}\n\nexport interface ARRReport {\n periodLabel: string;\n total: ARRSegment;\n selfServe: ARRSegment;\n enterprise: ARRSegment;\n usage: ARRSegment;\n}\n\nexport async function buildARRReport(): Promise {\n const [s, e, u] = await Promise.all([\n fetchStripeARRReport(),\n fetchEnterpriseARR(),\n fetchApiUsageRunRate(),\n ]);\n\n const selfServe: ARRSegment = {\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\n const enterprise: ARRSegment = {\n arr: e.arr,\n netChange: e.netChange,\n netChangePct: e.netChangePct,\n newAccounts: e.newAccounts,\n };\n\n const usage: ARRSegment = {\n arr: u.runRate,\n netChange: u.netChange,\n netChangePct: u.netChangePct,\n };\n\n // Total = self-serve + enterprise + usage run-rate. Net change sums whichever\n // segments have a delta today; a segment with no prior-day figure contributes\n // 0 rather than blanking out the total. Total net change is null only when\n // every segment is null (e.g., very first run with no history anywhere).\n const segments = [selfServe, enterprise, usage];\n const totalArr = segments.reduce((t, seg) => t + (seg.arr ?? 0), 0);\n const totalNetChange = segments.every((seg) => seg.netChange === null)\n ? null\n : segments.reduce((t, seg) => t + (seg.netChange ?? 0), 0);\n const priorTotal = totalNetChange !== null ? totalArr - totalNetChange : 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 return { periodLabel: s.day, total, selfServe, enterprise, usage };\n}\n","// 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 { 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}\n\nexport interface ARRReport {\n periodLabel: string;\n total: ARRSegment;\n selfServe: ARRSegment;\n enterprise: ARRSegment;\n usage: ARRSegment;\n}\n\n// A failing source must not take down the whole digest: degrade that segment\n// to \"—\" and keep posting. (Seen live: Stripe's preview Analytics metric\n// erroring server-side for days at a time.)\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\nconst DOWN: ARRSegment = { arr: null, netChange: null, netChangePct: null };\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 : DOWN;\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 : DOWN;\n\n const usage: ARRSegment = u\n ? {\n arr: u.runRate,\n netChange: u.netChange,\n netChangePct: u.netChangePct,\n }\n : DOWN;\n\n // Total = self-serve + enterprise + usage run-rate. Net change sums whichever\n // segments have a delta today; a segment with no prior-day figure contributes\n // 0 rather than blanking out the total. Total net change is null only when\n // every segment is null (e.g., very first run with no history anywhere).\n const segments = [selfServe, enterprise, usage];\n const totalArr = segments.reduce((t, seg) => t + (seg.arr ?? 0), 0);\n const totalNetChange = segments.every((seg) => seg.netChange === null)\n ? null\n : segments.reduce((t, seg) => t + (seg.netChange ?? 0), 0);\n const priorTotal = totalNetChange !== null ? totalArr - totalNetChange : 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 return { periodLabel: s.day, total, selfServe, enterprise, usage };\n}\n","createdAt",["D",1785691585876],"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",{"_98":97,"_105":-5},"org",{"_98":97,"_99":100,"_101":-5,"_18":-5,"_102":103,"_104":25},"branch",{"_13":87,"_32":88,"_43":42,"_89":44,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":45,"_96":97},"isOwned",163,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","consensus","handle","displayName","Consensus","avatar","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_32":45,"_38":304,"_47":-5,"_48":25,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":-5,"_57":25,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":305,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":306,"_82":307,"_84":308,"_86":25},"tree",[251,252,253,254,255,256,257,258,259,260,261,262,263],"files",[251,252,253,254,255,256,257,258,259,260,261,262,263],"counts",{"_241":61,"_242":148,"_243":61,"_244":245,"_246":247},"contents",[128,129,130,131,132,133,134,135,136,137,138,139,140],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":233,"_32":234,"_30":235,"_144":-5,"_41":42,"_40":236,"_146":25,"_38":237,"_13":238,"_33":20,"_35":239,"_24":25,"_150":-5,"_27":240},{"_43":225,"_32":226,"_30":227,"_144":-5,"_41":42,"_40":228,"_146":172,"_38":229,"_13":230,"_33":34,"_35":231,"_24":25,"_150":-5,"_27":232},{"_43":217,"_32":218,"_30":219,"_144":-5,"_41":42,"_40":220,"_146":172,"_38":221,"_13":200,"_33":222,"_35":223,"_24":25,"_150":-5,"_27":224},{"_43":210,"_32":211,"_30":212,"_144":-5,"_41":42,"_40":213,"_146":172,"_38":214,"_13":61,"_33":34,"_35":215,"_24":25,"_150":-5,"_27":216},{"_43":203,"_32":204,"_30":205,"_144":-5,"_41":42,"_40":206,"_146":25,"_38":207,"_13":148,"_33":34,"_35":208,"_24":25,"_150":-5,"_27":209},{"_43":195,"_32":196,"_30":197,"_144":-5,"_41":42,"_40":198,"_146":172,"_38":199,"_13":200,"_33":34,"_35":201,"_24":25,"_150":-5,"_27":202},{"_43":187,"_32":188,"_30":189,"_144":-5,"_41":42,"_40":190,"_146":172,"_38":191,"_13":192,"_33":34,"_35":193,"_24":25,"_150":-5,"_27":194},{"_43":180,"_32":181,"_30":182,"_144":-5,"_41":42,"_40":183,"_146":172,"_38":184,"_13":61,"_33":34,"_35":185,"_24":25,"_150":-5,"_27":186},{"_43":176,"_32":28,"_30":31,"_144":-5,"_41":42,"_40":14,"_146":25,"_38":177,"_13":178,"_33":34,"_35":37,"_24":25,"_150":-5,"_27":179},{"_43":168,"_32":169,"_30":170,"_144":-5,"_41":42,"_40":171,"_146":172,"_38":173,"_13":61,"_33":34,"_35":174,"_24":25,"_150":-5,"_27":175},{"_43":160,"_32":161,"_30":162,"_144":-5,"_41":42,"_40":163,"_146":25,"_38":164,"_13":165,"_33":34,"_35":166,"_24":25,"_150":-5,"_27":167},{"_43":152,"_32":153,"_30":154,"_144":-5,"_41":42,"_40":155,"_146":25,"_38":156,"_13":157,"_33":34,"_35":158,"_24":25,"_150":-5,"_27":159},{"_43":141,"_32":142,"_30":143,"_144":-5,"_41":42,"_40":145,"_146":25,"_38":147,"_13":148,"_33":34,"_35":149,"_24":25,"_150":-5,"_27":151},"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",[142],"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",[153],"a17407ed-fe24-46a8-adb0-cb9b771c4abc","slack.ts","f6198156-5ad4-11f1-adee-ee650bb23af1",108,["D",1784250400406],21,"// 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\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 ``,\n `${EMOJI.enterprise} Enterprise ARR (HubSpot): ${arrText(r.enterprise)}`,\n ` ${netLine(r.enterprise)}`,\n ...(r.enterprise.newAccounts ?? []).map((n) => ` ${n}`),\n ``,\n `${EMOJI.usage} Usage Revenue Run Rate (Stripe): ${arrText(r.usage)}`,\n ` ${netLine(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 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",[161],"1fa15d5c-5af1-11f1-81a2-ee650bb23af1","sigma.ts","1fa15e24-5af1-11f1-81a2-ee650bb23af1",17,true,["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",[169],"f056780c-e093-4256-b53c-23d1e9c0d918",["D",1785691585876],7,[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",[181],"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",[188],"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",[196],"3f548e7f-36b4-4884-b4e2-42b3cf3133c3","probe-analytics.ts","16900e46-8e97-11f1-9da0-1607ee4eb77e",116,["D",1785691544462],"// Throwaway: discover valid Analytics metric names — try listing endpoints,\n// then brute-force candidate renames of the usage revenue metric.\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\n// 1. Possible metric-listing endpoints\nfor (const path of [\n \"/v2/data/analytics/metrics\",\n \"/v2/data/analytics/metric_definitions\",\n \"/v2/data/analytics/supported_metrics\",\n]) {\n const res = await fetch(`https://api.stripe.com${path}`, { headers: H });\n console.log(`GET ${path} → ${res.status}`);\n if (res.ok) {\n const text = await res.text();\n console.log(text.slice(0, 2000));\n }\n}\n\n// 2. Candidate names for the usage revenue metric\nconst candidates = [\n \"usage_based_billing.gross_usage_revenue\",\n \"usage_based_billing.usage_revenue\",\n \"usage_based_billing.net_usage_revenue\",\n \"usage_based_billing.revenue\",\n \"billing.usage_revenue\",\n \"usage.gross_revenue\",\n \"revenue.usage\",\n];\n\nfor (const name of candidates) {\n const res = await fetch(\"https://api.stripe.com/v2/data/analytics/metric_query\", {\n method: \"POST\",\n headers: H,\n body: JSON.stringify({\n metrics: [{ name }],\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 const text = await res.text();\n let detail = \"\";\n if (!res.ok) {\n try {\n const e = JSON.parse(text)?.error;\n detail = `${e?.code}: ${(e?.message ?? \"\").slice(0, 80)}`;\n } catch { detail = text.slice(0, 80); }\n } else {\n const data = JSON.parse(text);\n const total = (data?.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 = `${data?.data?.length} buckets, sum $${(total / 100).toFixed(2)}`;\n }\n console.log(`${res.ok ? \"✅\" : \"❌\"} ${name} → ${res.status} ${detail}`);\n}\n",[204],"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",[211],"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",[218],"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",[226],"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",[234],"pulls","branches","forks","envVars",{"_248":200,"_250":61},"connections",{"_248":200,"_249":61},"count","unfilledCount","emptyCount",{"_43":233,"_32":234,"_30":235,"_144":-5,"_41":42,"_40":236,"_146":25,"_38":301,"_13":238,"_33":20,"_35":239,"_24":25,"_150":-5,"_265":302,"_27":303},{"_43":225,"_32":226,"_30":227,"_144":-5,"_41":42,"_40":228,"_146":172,"_38":298,"_13":230,"_33":34,"_35":231,"_24":25,"_150":-5,"_265":299,"_27":300},{"_43":217,"_32":218,"_30":219,"_144":-5,"_41":42,"_40":220,"_146":172,"_38":295,"_13":200,"_33":222,"_35":223,"_24":25,"_150":-5,"_265":296,"_27":297},{"_43":210,"_32":211,"_30":212,"_144":-5,"_41":42,"_40":213,"_146":172,"_38":292,"_13":61,"_33":34,"_35":215,"_24":25,"_150":-5,"_265":293,"_27":294},{"_43":203,"_32":204,"_30":205,"_144":-5,"_41":42,"_40":206,"_146":25,"_38":289,"_13":148,"_33":34,"_35":208,"_24":25,"_150":-5,"_265":290,"_27":291},{"_43":195,"_32":196,"_30":197,"_144":-5,"_41":42,"_40":198,"_146":172,"_38":286,"_13":200,"_33":34,"_35":201,"_24":25,"_150":-5,"_265":287,"_27":288},{"_43":187,"_32":188,"_30":189,"_144":-5,"_41":42,"_40":190,"_146":172,"_38":283,"_13":192,"_33":34,"_35":193,"_24":25,"_150":-5,"_265":284,"_27":285},{"_43":180,"_32":181,"_30":182,"_144":-5,"_41":42,"_40":183,"_146":172,"_38":280,"_13":61,"_33":34,"_35":185,"_24":25,"_150":-5,"_265":281,"_27":282},{"_43":176,"_32":28,"_30":31,"_144":-5,"_41":42,"_40":14,"_146":25,"_38":277,"_13":178,"_33":34,"_35":37,"_24":25,"_150":-5,"_265":278,"_27":279},{"_43":168,"_32":169,"_30":170,"_144":-5,"_41":42,"_40":171,"_146":172,"_38":274,"_13":61,"_33":34,"_35":174,"_24":25,"_150":-5,"_265":275,"_27":276},{"_43":160,"_32":161,"_30":162,"_144":-5,"_41":42,"_40":163,"_146":25,"_38":271,"_13":165,"_33":34,"_35":166,"_24":25,"_150":-5,"_265":272,"_27":273},{"_43":152,"_32":153,"_30":154,"_144":-5,"_41":42,"_40":155,"_146":25,"_38":268,"_13":157,"_33":34,"_35":158,"_24":25,"_150":-5,"_265":269,"_27":270},{"_43":141,"_32":142,"_30":143,"_144":-5,"_41":42,"_40":145,"_146":25,"_38":264,"_13":148,"_33":34,"_35":149,"_24":25,"_150":-5,"_265":266,"_27":267},["D",1782139607619],"children",[],[142],["D",1780015615508],[],[153],["D",1784250400406],[],[161],["D",1780012708937],[],[169],["D",1785691585876],[],[28],["D",1782139174552],[],[181],["D",1780013552266],[],[188],["D",1780021601725],[],[196],["D",1785691544462],[],[204],["D",1780014216205],[],[211],["D",1780016195304],[],[218],["D",1784250371119],[],[226],["D",1780016772167],[],[234],["D",1780000503639],["D",1789509043237],{"_98":97,"_105":-5},{"_98":97,"_99":100,"_101":-5,"_18":-5,"_102":103,"_104":25},{"_13":87,"_32":88,"_43":42,"_89":44,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":45,"_96":97},"ctx",{"_80":-5,"_125":342,"_343":344,"_345":346,"_347":25,"_348":25,"_349":25,"_350":172,"_351":-5,"_352":-7,"_353":-7,"_354":61,"_355":25,"_356":25,"_357":-5,"_358":25,"_359":25,"_360":25,"_361":-5},"ENV",{"_313":314,"_315":316,"_317":88,"_318":319,"_320":321,"_322":323,"_324":325,"_326":327,"_328":329,"_330":331,"_332":333,"_334":335,"_336":337,"_338":339,"_340":341},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","5015b8df46402d8bba4f485ecc37aec202b0e06b","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 · v117