[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_297":298,"_299":300},"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",106,"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","slack.ts","latestPath","fileId","f6198156-5ad4-11f1-adee-ee650bb23af1","name","fileType","script","content","// 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\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 ``,\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(EMOJI.enterprise, \"Enterprise ARR (HubSpot)\", r.enterprise),\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","// 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 ``,\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(EMOJI.enterprise, \"Enterprise ARR (HubSpot)\", r.enterprise),\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","createdAt",["D",1784250395628],"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":292,"_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":293,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":294,"_82":295,"_84":296,"_86":25},"tree",[243,244,245,246,247,248,249,250,251,252,253,254],"files",[243,244,245,246,247,248,249,250,251,252,253,254],"counts",{"_233":61,"_234":147,"_235":61,"_236":237,"_238":239},"contents",[128,129,130,131,132,133,134,135,136,137,138,139],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":225,"_32":226,"_30":227,"_143":-5,"_41":42,"_40":228,"_145":25,"_38":229,"_13":230,"_33":20,"_35":231,"_24":25,"_149":-5,"_27":232},{"_43":217,"_32":218,"_30":219,"_143":-5,"_41":42,"_40":220,"_145":167,"_38":221,"_13":222,"_33":34,"_35":223,"_24":25,"_149":-5,"_27":224},{"_43":209,"_32":210,"_30":211,"_143":-5,"_41":42,"_40":212,"_145":167,"_38":213,"_13":199,"_33":214,"_35":215,"_24":25,"_149":-5,"_27":216},{"_43":202,"_32":203,"_30":204,"_143":-5,"_41":42,"_40":205,"_145":167,"_38":206,"_13":61,"_33":34,"_35":207,"_24":25,"_149":-5,"_27":208},{"_43":194,"_32":195,"_30":196,"_143":-5,"_41":42,"_40":197,"_145":167,"_38":198,"_13":199,"_33":34,"_35":200,"_24":25,"_149":-5,"_27":201},{"_43":186,"_32":187,"_30":188,"_143":-5,"_41":42,"_40":189,"_145":167,"_38":190,"_13":191,"_33":34,"_35":192,"_24":25,"_149":-5,"_27":193},{"_43":179,"_32":180,"_30":181,"_143":-5,"_41":42,"_40":182,"_145":167,"_38":183,"_13":61,"_33":34,"_35":184,"_24":25,"_149":-5,"_27":185},{"_43":171,"_32":172,"_30":173,"_143":-5,"_41":42,"_40":174,"_145":25,"_38":175,"_13":176,"_33":34,"_35":177,"_24":25,"_149":-5,"_27":178},{"_43":163,"_32":164,"_30":165,"_143":-5,"_41":42,"_40":166,"_145":167,"_38":168,"_13":61,"_33":34,"_35":169,"_24":25,"_149":-5,"_27":170},{"_43":159,"_32":28,"_30":31,"_143":-5,"_41":42,"_40":14,"_145":25,"_38":160,"_13":161,"_33":34,"_35":37,"_24":25,"_149":-5,"_27":162},{"_43":151,"_32":152,"_30":153,"_143":-5,"_41":42,"_40":154,"_145":25,"_38":155,"_13":156,"_33":34,"_35":157,"_24":25,"_149":-5,"_27":158},{"_43":140,"_32":141,"_30":142,"_143":-5,"_41":42,"_40":144,"_145":25,"_38":146,"_13":147,"_33":34,"_35":148,"_24":25,"_149":-5,"_27":150},"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",[141],"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",[152],"5cef8033-5602-4364-9026-2a144424d66b",["D",1784250395628],19,[28],"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",[164],"a70718a7-ae3d-46f5-b4d6-015ae9e77b33","report.ts","8ce396ca-5af4-11f1-9141-ee650bb23af1",105,["D",1784250385226],6,"// 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",[172],"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",[180],"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",[187],"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",[195],"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",[203],"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",[210],"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",[218],"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",[226],"pulls","branches","forks","envVars",{"_240":199,"_242":61},"connections",{"_240":199,"_241":61},"count","unfilledCount","emptyCount",{"_43":225,"_32":226,"_30":227,"_143":-5,"_41":42,"_40":228,"_145":25,"_38":289,"_13":230,"_33":20,"_35":231,"_24":25,"_149":-5,"_256":290,"_27":291},{"_43":217,"_32":218,"_30":219,"_143":-5,"_41":42,"_40":220,"_145":167,"_38":286,"_13":222,"_33":34,"_35":223,"_24":25,"_149":-5,"_256":287,"_27":288},{"_43":209,"_32":210,"_30":211,"_143":-5,"_41":42,"_40":212,"_145":167,"_38":283,"_13":199,"_33":214,"_35":215,"_24":25,"_149":-5,"_256":284,"_27":285},{"_43":202,"_32":203,"_30":204,"_143":-5,"_41":42,"_40":205,"_145":167,"_38":280,"_13":61,"_33":34,"_35":207,"_24":25,"_149":-5,"_256":281,"_27":282},{"_43":194,"_32":195,"_30":196,"_143":-5,"_41":42,"_40":197,"_145":167,"_38":277,"_13":199,"_33":34,"_35":200,"_24":25,"_149":-5,"_256":278,"_27":279},{"_43":186,"_32":187,"_30":188,"_143":-5,"_41":42,"_40":189,"_145":167,"_38":274,"_13":191,"_33":34,"_35":192,"_24":25,"_149":-5,"_256":275,"_27":276},{"_43":179,"_32":180,"_30":181,"_143":-5,"_41":42,"_40":182,"_145":167,"_38":271,"_13":61,"_33":34,"_35":184,"_24":25,"_149":-5,"_256":272,"_27":273},{"_43":171,"_32":172,"_30":173,"_143":-5,"_41":42,"_40":174,"_145":25,"_38":268,"_13":176,"_33":34,"_35":177,"_24":25,"_149":-5,"_256":269,"_27":270},{"_43":163,"_32":164,"_30":165,"_143":-5,"_41":42,"_40":166,"_145":167,"_38":265,"_13":61,"_33":34,"_35":169,"_24":25,"_149":-5,"_256":266,"_27":267},{"_43":159,"_32":28,"_30":31,"_143":-5,"_41":42,"_40":14,"_145":25,"_38":262,"_13":161,"_33":34,"_35":37,"_24":25,"_149":-5,"_256":263,"_27":264},{"_43":151,"_32":152,"_30":153,"_143":-5,"_41":42,"_40":154,"_145":25,"_38":259,"_13":156,"_33":34,"_35":157,"_24":25,"_149":-5,"_256":260,"_27":261},{"_43":140,"_32":141,"_30":142,"_143":-5,"_41":42,"_40":144,"_145":25,"_38":255,"_13":147,"_33":34,"_35":148,"_24":25,"_149":-5,"_256":257,"_27":258},["D",1782139607619],"children",[],[141],["D",1780015615508],[],[152],["D",1784250395628],[],[28],["D",1780012708937],[],[164],["D",1784250385226],[],[172],["D",1782139174552],[],[180],["D",1780013552266],[],[187],["D",1780021601725],[],[195],["D",1780014216205],[],[203],["D",1780016195304],[],[210],["D",1784250371119],[],[218],["D",1780016772167],[],[226],["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":330,"_331":332,"_333":334,"_335":25,"_336":25,"_337":25,"_338":167,"_339":-5,"_340":-7,"_341":-7,"_342":61,"_343":25,"_344":25,"_345":-5,"_346":25,"_347":25,"_348":25,"_349":-5},"ENV",{"_301":302,"_303":304,"_305":88,"_306":307,"_308":309,"_310":311,"_312":313,"_314":315,"_316":317,"_318":319,"_320":321,"_322":323,"_324":325,"_326":327,"_328":329},"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 · v106