1import { query, type QueryCtx } from "../_generated/server";2import { ConvexError, v, type Infer } from "convex/values";3import type { Doc, Id } from "../_generated/dataModel";4 5import {6 resolveProjectByApiKeyFromDb,7 resolveProjectByIdForCurrentUserFromDb,8} from "../projects/helpers";9import { monthlyMicrosForSub } from "./monthlyMicros";10import { selectMostRecentlyUpdatedSubscription } from "./selectLatest";11import {12 DEFAULT_REPORTING_CURRENCY,13 normalizeReportingCurrencyOrDefault,14} from "../utils/currency";15import { subscriptionStateValidator } from "../utils/validation";16 17const subscriptionFields = {18 id: v.id("subscriptions"),19 productId: v.string(),20 platform: v.union(v.literal("IOS"), v.literal("Android")),21 state: subscriptionStateValidator,22 expiresAt: v.optional(v.number()),23 renewsAt: v.optional(v.number()),24 willRenew: v.optional(v.boolean()),25 cancellationReason: v.optional(v.string()),26 currency: v.optional(v.string()),27 priceAmountMicros: v.optional(v.number()),28 startedAt: v.number(),29 updatedAt: v.number(),30 // SECURITY DEBT: on Android this is the credential a server accepts as proof31 // of purchase, and these rows reach routes that answer to a publishable key32 // shipped inside the app. It cannot be removed additively: MAUI declares it33 // `required`, and its tolerant deserializer turns a missing field into a null34 // subscription and silently drops entitlement rows — every installed MAUI app35 // would lose entitlement. Removal needs an explicit version transition.36 purchaseToken: v.string(),37 originalTransactionId: v.optional(v.string()),38 userId: v.optional(v.string()),39};40const subscriptionShape = v.object(subscriptionFields);41const subscriptionV2Shape = v.object({42 id: v.id("subscriptions"),43 productId: v.string(),44 platform: v.union(v.literal("IOS"), v.literal("Android")),45 state: subscriptionStateValidator,46 expiresAt: v.optional(v.number()),47 renewsAt: v.optional(v.number()),48 willRenew: v.optional(v.boolean()),49 cancellationReason: v.optional(v.string()),50 currency: v.optional(v.string()),51 priceAmountMicros: v.optional(v.number()),52 startedAt: v.number(),53 updatedAt: v.number(),54 userId: v.optional(v.string()),55});56const subscriptionEvaluationRowShape = v.object({57 ...subscriptionFields,58 createdAt: v.number(),59});60type SubscriptionRow = Infer<typeof subscriptionShape>;61type SubscriptionV2Row = Infer<typeof subscriptionV2Shape>;62type SubscriptionEvaluationRow = Infer<typeof subscriptionEvaluationRowShape>;63export const MAX_USER_SUBSCRIPTION_ROWS = 200;64 65/**66 * SPEC.md 2.3 entitlement gate as a pure predicate: only Active and67 * InGracePeriod grant access, an omitted expiry means none is known, and the68 * boundary is exclusive — expiresAt == now is NOT entitled. Exported so the69 * conformance adapter certifies the same predicate every read uses.70 */71export function isEntitledAt(72 state: string,73 expiresAt: number | null | undefined,74 now: number,75): boolean {76 if (state !== "Active" && state !== "InGracePeriod") return false;77 if (expiresAt != null && expiresAt <= now) return false;78 return true;79}80 81function isActive(sub: Doc<"subscriptions">, now: number): boolean {82 return isEntitledAt(sub.state, sub.expiresAt, now);83}84 85function isEntitledState(sub: Doc<"subscriptions">): boolean {86 return sub.state === "Active" || sub.state === "InGracePeriod";87}88 89export function assertUserSubscriptionRowLimit(90 rows: Array<Doc<"subscriptions">>,91): void {92 if (rows.length <= MAX_USER_SUBSCRIPTION_ROWS) return;93 throw new ConvexError({94 code: "ENTITLEMENT_SNAPSHOT_TOO_LARGE",95 message:96 "This user has more than 200 subscription rows. Contact IAPKit support before retrying.",97 });98}99 100async function userSubscriptionRows(101 ctx: QueryCtx,102 projectId: Id<"projects">,103 userId: string,104): Promise<Array<Doc<"subscriptions">>> {105 const rows = await ctx.db106 .query("subscriptions")107 .withIndex("by_project_and_user_and_updated", (q) =>108 q.eq("projectId", projectId).eq("userId", userId),109 )110 .order("desc")111 .take(MAX_USER_SUBSCRIPTION_ROWS + 1);112 assertUserSubscriptionRowLimit(rows);113 return rows;114}115 116export function shapeSubscriptionEvaluationSnapshot(117 rows: Array<Doc<"subscriptions">>,118): {119 candidates: SubscriptionEvaluationRow[];120 fallback: SubscriptionEvaluationRow | null;121} {122 const fallback = selectMostRecentlyUpdatedSubscription(rows);123 return {124 candidates: rows125 .filter(isEntitledState)126 .map(shapeSubscriptionEvaluationRow),127 fallback: fallback ? shapeSubscriptionEvaluationRow(fallback) : null,128 };129}130 131function shapeSubscriptionEvaluationRow(132 sub: Doc<"subscriptions">,133): SubscriptionEvaluationRow {134 return {135 ...shapeSubscriptionRow(sub),136 createdAt: sub._creationTime,137 };138}139 140export function shapeSubscriptionRow(141 sub: Doc<"subscriptions">,142): SubscriptionRow {143 const row: SubscriptionRow = {144 id: sub._id,145 productId: sub.productId,146 platform: sub.platform,147 state: sub.state,148 expiresAt: sub.expiresAt,149 renewsAt: sub.renewsAt,150 willRenew: sub.willRenew,151 cancellationReason: sub.cancellationReason,152 currency: sub.currency,153 priceAmountMicros: sub.priceAmountMicros,154 startedAt: sub.startedAt,155 updatedAt: sub.updatedAt,156 purchaseToken: sub.purchaseToken,157 userId: sub.userId,158 };159 160 if (sub.platform === "IOS") {161 return {162 ...row,163 originalTransactionId: sub.purchaseToken,164 };165 }166 167 return row;168}169 170export function shapeSubscriptionV2Row(171 sub: Doc<"subscriptions">,172): SubscriptionV2Row {173 return {174 id: sub._id,175 productId: sub.productId,176 platform: sub.platform,177 state: sub.state,178 expiresAt: sub.expiresAt,179 renewsAt: sub.renewsAt,180 willRenew: sub.willRenew,181 cancellationReason: sub.cancellationReason,182 currency: sub.currency,183 priceAmountMicros: sub.priceAmountMicros,184 startedAt: sub.startedAt,185 updatedAt: sub.updatedAt,186 userId: sub.userId,187 };188}189 190async function projectByApiKey(191 ctx: QueryCtx,192 apiKey: string | undefined,193 requiredAccess: "client" | "admin" = "client",194): Promise<Doc<"projects"> | null> {195 if (!apiKey) return null;196 const resolved = await resolveProjectByApiKeyFromDb(197 ctx,198 apiKey,199 requiredAccess,200 );201 return resolved?.project ?? null;202}203 204async function requireAdminProjectByApiKey(205 ctx: QueryCtx,206 apiKey: string,207): Promise<Doc<"projects">> {208 const project = await projectByApiKey(ctx, apiKey, "admin");209 if (!project) {210 throw new ConvexError({211 code: "INVALID_API_KEY",212 message: "API key is invalid or inactive",213 });214 }215 return project;216}217 218async function projectByIdForCurrentUser(219 ctx: QueryCtx,220 projectId: Id<"projects"> | undefined,221): Promise<Doc<"projects"> | null> {222 if (!projectId) return null;223 const resolved = await resolveProjectByIdForCurrentUserFromDb(ctx, projectId);224 return resolved?.project ?? null;225}226 227async function projectForReadArgs(228 ctx: QueryCtx,229 args: {230 apiKey?: string;231 projectId?: Id<"projects">;232 },233 apiKeyAccess: "client" | "admin" = "client",234): Promise<Doc<"projects"> | null> {235 if (args.projectId) {236 return projectByIdForCurrentUser(ctx, args.projectId);237 }238 239 if (args.apiKey !== undefined) {240 return projectByApiKey(ctx, args.apiKey, apiKeyAccess);241 }242 243 throw new Error("apiKey or projectId is required.");244}245 246export interface MrrCurrencyEntry {247 currency: string;248 mrrMicros: number;249}250 251/**252 * Selects the headline MRR entry for a project's reporting currency.253 *254 * `reportingCurrency` is normalized via `normalizeReportingCurrencyOrDefault`,255 * so unknown or invalid input falls back to `DEFAULT_REPORTING_CURRENCY`.256 * If no entry matches the normalized currency, returned `mrrMicros` is `0`.257 * Returned `excludedMrrByCurrency` contains every non-reporting-currency entry.258 *259 * @param entries Per-currency MRR rows already summed for the project.260 * @param reportingCurrency Project-configured reporting currency, raw or normalized.261 * @returns The normalized `currency`, selected `mrrMicros`, and excluded rows.262 */263export function selectReportingMrr(264 entries: MrrCurrencyEntry[],265 reportingCurrency: string | null | undefined,266): {267 currency: string;268 mrrMicros: number;269 excludedMrrByCurrency: MrrCurrencyEntry[];270} {271 const normalizedReportingCurrency =272 normalizeReportingCurrencyOrDefault(reportingCurrency);273 const reportingEntry = entries.find(274 (entry) => entry.currency === normalizedReportingCurrency,275 );276 277 return {278 currency: normalizedReportingCurrency,279 mrrMicros: reportingEntry?.mrrMicros ?? 0,280 excludedMrrByCurrency: entries.filter(281 (entry) => entry.currency !== normalizedReportingCurrency,282 ),283 };284}285 286// Time-independent evaluation snapshot for the Fly HTTP boundary. It excludes287// refunded, revoked, and other historical rows except for the single latest288// fallback needed by status. State-entitled candidates may include a row whose289// expiresAt has just passed; Fly removes it with its own current clock before290// producing the public HTTP response. Convex may safely cache the snapshot and291// invalidates it when a dependent row changes.292export const subscriptionEvaluationSnapshot = query({293 args: {294 apiKey: v.string(),295 userId: v.string(),296 },297 returns: v.object({298 projectId: v.union(v.id("projects"), v.null()),299 candidates: v.array(subscriptionEvaluationRowShape),300 fallback: v.union(subscriptionEvaluationRowShape, v.null()),301 }),302 handler: async (ctx, args) => {303 const project = await projectByApiKey(ctx, args.apiKey);304 if (!project) return { projectId: null, candidates: [], fallback: null };305 306 const rows = await userSubscriptionRows(ctx, project._id, args.userId);307 return {308 projectId: project._id,309 ...shapeSubscriptionEvaluationSnapshot(rows),310 };311 },312});313 314// `/v2` account reads are server-to-server. Convex repeats the admin check and315// shapes the response without store credentials before it crosses the HTTP316// boundary.317export const subscriptionStatusV2 = query({318 args: { apiKey: v.string(), userId: v.string(), now: v.number() },319 returns: v.object({320 active: v.boolean(),321 subscription: v.union(subscriptionV2Shape, v.null()),322 }),323 handler: async (ctx, args) => {324 const project = await requireAdminProjectByApiKey(ctx, args.apiKey);325 326 const subs = await userSubscriptionRows(ctx, project._id, args.userId);327 const activeSubs = subs.filter((candidate) =>328 isActive(candidate, args.now),329 );330 const selected = selectMostRecentlyUpdatedSubscription(331 activeSubs.length > 0 ? activeSubs : subs,332 );333 334 return {335 active: activeSubs.length > 0,336 subscription: selected ? shapeSubscriptionV2Row(selected) : null,337 };338 },339});340 341export const entitlementsV2 = query({342 args: { apiKey: v.string(), userId: v.string(), now: v.number() },343 returns: v.object({344 userId: v.string(),345 productIds: v.array(v.string()),346 subscriptions: v.array(subscriptionV2Shape),347 }),348 handler: async (ctx, args) => {349 const project = await requireAdminProjectByApiKey(ctx, args.apiKey);350 351 const all = await userSubscriptionRows(ctx, project._id, args.userId);352 const active = all.filter((sub) => isActive(sub, args.now));353 return {354 userId: args.userId,355 productIds: Array.from(new Set(active.map((sub) => sub.productId))),356 subscriptions: active.map(shapeSubscriptionV2Row),357 };358 },359});360 361// Authoritative server-credential gate with no side effect and no data. The362// commerce bindPurchase handler calls this before it parses store evidence, so363// an unknown or under-scoped key is rejected (UNAUTHORIZED / FORBIDDEN) before364// it can learn which stores bind or which evidence a store requires.365export const assertServerAccess = query({366 args: { apiKey: v.string() },367 returns: v.object({ ok: v.boolean() }),368 handler: async (ctx, args) => {369 await requireAdminProjectByApiKey(ctx, args.apiKey);370 return { ok: true };371 },372});373 374export const userErasureStatusV2 = query({375 args: {376 apiKey: v.string(),377 // The id arrives from a URL path; a malformed one must read as "no such378 // job" (404), not fail argument validation into a 500.379 jobId: v.string(),380 },381 returns: v.union(382 v.null(),383 v.object({384 jobId: v.id("subscriptionUserErasureJobs"),385 status: v.union(386 v.literal("queued"),387 v.literal("running"),388 v.literal("completed"),389 ),390 subscriptionsErased: v.number(),391 commerceEventsErased: v.number(),392 createdAt: v.number(),393 updatedAt: v.number(),394 completedAt: v.optional(v.number()),395 }),396 ),397 handler: async (ctx, args) => {398 const project = await requireAdminProjectByApiKey(ctx, args.apiKey);399 const jobId = ctx.db.normalizeId("subscriptionUserErasureJobs", args.jobId);400 if (!jobId) return null;401 const job = await ctx.db.get(jobId);402 if (!job || job.projectId !== project._id) return null;403 return {404 jobId: job._id,405 status: job.status,406 subscriptionsErased: job.subscriptionsErased,407 commerceEventsErased: job.commerceEventsErased,408 createdAt: job.createdAt,409 updatedAt: job.updatedAt,410 completedAt: job.completedAt,411 };412 },413});414 415// Match onesub's `/onesub/status?userId=` — returns the most-recently-416// updated active subscription when the user is entitled, otherwise the417// most-recently-updated subscription overall, plus one `active` boolean418// for simple gating.419export const subscriptionStatus = query({420 args: { apiKey: v.string(), userId: v.string() },421 returns: v.object({422 active: v.boolean(),423 subscription: v.union(subscriptionShape, v.null()),424 }),425 handler: async (ctx, args) => {426 const project = await projectByApiKey(ctx, args.apiKey);427 if (!project) return { active: false, subscription: null };428 429 const subs = await userSubscriptionRows(ctx, project._id, args.userId);430 431 const now = Date.now();432 const activeSubs = subs.filter((candidate) => isActive(candidate, now));433 const sub = selectMostRecentlyUpdatedSubscription(434 activeSubs.length > 0 ? activeSubs : subs,435 );436 if (!sub) return { active: false, subscription: null };437 438 return {439 active: activeSubs.length > 0,440 subscription: shapeSubscriptionRow(sub),441 };442 },443});444 445// Match onesub's entitlement evaluation — every productId the user446// currently has rights to. Aggregates across all subscription rows so447// a user with multiple offers (resub, family share, cross-grade) sees448// the union.449export const entitlements = query({450 args: { apiKey: v.string(), userId: v.string() },451 returns: v.object({452 userId: v.string(),453 productIds: v.array(v.string()),454 subscriptions: v.array(subscriptionShape),455 }),456 handler: async (ctx, args) => {457 const project = await projectByApiKey(ctx, args.apiKey);458 if (!project) {459 return { userId: args.userId, productIds: [], subscriptions: [] };460 }461 462 const all = await userSubscriptionRows(ctx, project._id, args.userId);463 464 const now = Date.now();465 const active = all.filter((sub) => isActive(sub, now));466 return {467 userId: args.userId,468 productIds: Array.from(new Set(active.map((sub) => sub.productId))),469 subscriptions: active.map(shapeSubscriptionRow),470 };471 },472});473 474// Filtered list for the dashboard's subscriptions page. Mirrors475// onesub's `SubscriptionStore.listFiltered` API.476export const listSubscriptions = query({477 args: {478 apiKey: v.optional(v.string()),479 projectId: v.optional(v.id("projects")),480 state: v.optional(subscriptionStateValidator),481 productId: v.optional(v.string()),482 userId: v.optional(v.string()),483 limit: v.optional(v.number()),484 },485 returns: v.object({486 items: v.array(subscriptionShape),487 total: v.number(),488 }),489 handler: async (ctx, args) => {490 const project = await projectForReadArgs(ctx, args, "admin");491 if (!project) return { items: [], total: 0 };492 493 const limit = Math.min(Math.max(args.limit ?? 50, 1), 200);494 495 // userId path: subscriptions per user is a small population496 // (single digits in practice — a user with 50 subscriptions on a497 // single project is pathological), so we collect the entire498 // by_project_and_user slice and apply state/productId filters in499 // memory rather than throwing. Earlier behaviour rejected the500 // combo with an error, which made the dashboard "filter user X by501 // state Active" path unusable (PR #124502 // (https://github.com/hyodotdev/openiap/pull/124) review).503 if (args.userId) {504 const userRows = await ctx.db505 .query("subscriptions")506 .withIndex("by_project_and_user", (q) =>507 q.eq("projectId", project._id).eq("userId", args.userId),508 )509 .order("desc")510 .collect();511 const filtered = userRows.filter((sub) => {512 if (args.state && sub.state !== args.state) return false;513 if (args.productId && sub.productId !== args.productId) return false;514 return true;515 });516 return {517 items: filtered.slice(0, limit).map(shapeSubscriptionRow),518 total: filtered.length,519 };520 }521 522 // Pick the most-selective index for the supplied filters. Schema523 // covers single-filter combinations directly; the composite524 // (projectId, state, productId) index handles the dashboard's525 // common "filter by state and SKU" combination so we don't need526 // an over-fetch + in-memory post-filter that could miss rows527 // past the take() boundary.528 let rows: Array<Doc<"subscriptions">>;529 if (args.state && args.productId) {530 rows = await ctx.db531 .query("subscriptions")532 .withIndex("by_project_and_state_and_product", (q) =>533 q534 .eq("projectId", project._id)535 .eq("state", args.state!)536 .eq("productId", args.productId!),537 )538 .order("desc")539 .take(limit);540 } else if (args.state) {541 rows = await ctx.db542 .query("subscriptions")543 .withIndex("by_project_and_state", (q) =>544 q.eq("projectId", project._id).eq("state", args.state!),545 )546 .order("desc")547 .take(limit);548 } else if (args.productId) {549 rows = await ctx.db550 .query("subscriptions")551 .withIndex("by_project_and_product", (q) =>552 q.eq("projectId", project._id).eq("productId", args.productId!),553 )554 .order("desc")555 .take(limit);556 } else {557 rows = await ctx.db558 .query("subscriptions")559 .withIndex("by_project_and_updated", (q) =>560 q.eq("projectId", project._id),561 )562 .order("desc")563 .take(limit);564 }565 566 // All filter combinations hit an index that covers the supplied567 // columns now (the (state + productId) composite was added in568 // schema.ts), so no in-memory post-filter is needed here.569 570 // `total` reflects the filtered window we actually materialized,571 // not the full server-side count. Computing a true total would572 // require a separate aggregate scan that defeats the take() bound573 // we just put in. The dashboard treats `total` as "rows shown574 // matching the current filter" and surfaces "+ more" affordances575 // via the next page request.576 return {577 items: rows.slice(0, limit).map(shapeSubscriptionRow),578 total: rows.length,579 };580 },581});582 583// Metrics aggregation. Reads incrementally-maintained per-currency584// counters out of `subscriptionStats` for the live state buckets +585// MRR (O(currencies-per-project) — typically 1-3 rows), and bounded586// indexed scans over `by_project_and_state` for the 30-day rolling587// counters. The prior implementation took up to 10,000 subscriptions588// off the by_project_and_updated index and aggregated in memory,589// which silently undercounted projects above that cap.590//591// Migration safety: when the stats table is empty for a project592// (pre-rollout state) we fall through to a one-shot recompute via593// the same statsContributionFor logic so the dashboard stays594// correct on first read after deploy. The595// `recomputeSubscriptionStats` internal mutation populates rows for596// future reads.597export const metricsSummary = query({598 args: {599 apiKey: v.optional(v.string()),600 projectId: v.optional(v.id("projects")),601 },602 returns: v.object({603 activeSubs: v.number(),604 inGracePeriod: v.number(),605 inBillingRetry: v.number(),606 refunded30d: v.number(),607 canceled30d: v.number(),608 // Headline MRR in the project's reporting currency, normalized609 // to monthly. Historical field name kept for dashboard / MCP610 // consumers, but the value is no longer a cross-currency or611 // "most popular currency" total.612 mrrMicros: v.number(),613 currency: v.optional(v.string()),614 reportingCurrency: v.string(),615 // Full per-currency breakdown so consumers that care about616 // multi-currency aren't left guessing. Each entry's `mrrMicros`617 // is summed only over subscriptions in that currency, normalized618 // to monthly via the product's billingPeriod.619 mrrByCurrency: v.array(620 v.object({ currency: v.string(), mrrMicros: v.number() }),621 ),622 excludedMrrByCurrency: v.array(623 v.object({ currency: v.string(), mrrMicros: v.number() }),624 ),625 }),626 handler: async (ctx, args) => {627 const project = await projectForReadArgs(ctx, args, "admin");628 if (!project) {629 return {630 activeSubs: 0,631 inGracePeriod: 0,632 inBillingRetry: 0,633 refunded30d: 0,634 canceled30d: 0,635 mrrMicros: 0,636 currency: DEFAULT_REPORTING_CURRENCY,637 reportingCurrency: DEFAULT_REPORTING_CURRENCY,638 mrrByCurrency: [],639 excludedMrrByCurrency: [],640 };641 }642 const now = Date.now();643 const cutoff = now - 30 * 24 * 60 * 60 * 1000;644 645 // Live state counters + MRR — read out of the incrementally646 // maintained `subscriptionStats` table.647 const statsRows = await ctx.db648 .query("subscriptionStats")649 .withIndex("by_project", (q) => q.eq("projectId", project._id))650 .collect();651 652 let activeSubs = 0;653 let inGracePeriod = 0;654 let inBillingRetry = 0;655 const mrrAccumulators = new Map<string, number>();656 657 if (statsRows.length > 0) {658 for (const row of statsRows) {659 activeSubs += row.activeSubs;660 inGracePeriod += row.inGracePeriod;661 inBillingRetry += row.inBillingRetry;662 if (row.currency && row.mrrMicros > 0) {663 mrrAccumulators.set(664 row.currency,665 (mrrAccumulators.get(row.currency) ?? 0) + row.mrrMicros,666 );667 }668 }669 } else {670 // No stats rows yet — pre-rollout state for this project.671 // Compute on the fly so the dashboard isn't blank on first672 // read after deploy. Bounded by the same per-project scan the673 // backfill mutation does; for projects past the prior 10k cap674 // this is a one-time cost until `recomputeSubscriptionStats`675 // populates the table.676 //677 // Bounded by FALLBACK_SCAN_CAP so a project that's hugely past678 // the prior 10k scan limit can't crash the dashboard render.679 // The cap matches the previous implementation's bound; the680 // first read after deploy schedules an async backfill via the681 // drift-correction cron, after which subsequent reads come682 // out of subscriptionStats and have no scan at all.683 const FALLBACK_SCAN_CAP = 10_000;684 const periodByProductId = await loadPeriodByProductId(ctx, project._id);685 const allSubs = await ctx.db686 .query("subscriptions")687 .withIndex("by_project_and_updated", (q) =>688 q.eq("projectId", project._id),689 )690 .order("desc")691 .take(FALLBACK_SCAN_CAP);692 for (const sub of allSubs) {693 if (sub.state === "Active" && isActive(sub, now)) {694 activeSubs += 1;695 if (typeof sub.priceAmountMicros === "number" && sub.currency) {696 const monthly = monthlyMicrosForSub(697 sub,698 periodByProductId.get(sub.productId),699 );700 mrrAccumulators.set(701 sub.currency,702 (mrrAccumulators.get(sub.currency) ?? 0) + monthly,703 );704 }705 } else if (sub.state === "InGracePeriod") {706 inGracePeriod += 1;707 } else if (sub.state === "InBillingRetry") {708 inBillingRetry += 1;709 }710 }711 }712 713 // 30-day rolling counters — bounded by churn rather than by714 // historical state archive. The previous implementation walked715 // every `Refunded` row + every (Active|InGracePeriod|InBillingRetry716 // |Expired) row for the project and filtered in memory, which717 // grew unbounded as the historical archive accumulated. We now718 // do a single time-windowed scan via `by_project_and_updated`719 // with `gte(cutoff)`, then derive both refunded + canceled720 // counters in one pass. The candidate set is bounded by the721 // last 30 days of state changes (typically thousands per722 // project, never the full lifetime).723 // Cap the windowed scan so a project with > 10k state changes724 // in 30 days can't exceed Convex's 40k document-read limit. The725 // rolling counters degrade gracefully — if a project genuinely726 // hits this bound the dashboard shows an approximate count that727 // still tracks the cohort closely (this is the same trade-off728 // the previous SUBS_SCAN_CAP made for active counts, before the729 // incremental subscriptionStats path replaced it). Real-world730 // monthly churn is well under 10k for any realistic deployment.731 const ROLLING_SCAN_CAP = 10_000;732 const recentlyChanged = await ctx.db733 .query("subscriptions")734 .withIndex("by_project_and_updated", (q) =>735 q.eq("projectId", project._id).gte("updatedAt", cutoff),736 )737 .take(ROLLING_SCAN_CAP);738 let refunded30d = 0;739 let canceled30d = 0;740 const CANCELED_STATES = new Set([741 "Active",742 "InGracePeriod",743 "InBillingRetry",744 "Expired",745 ]);746 for (const sub of recentlyChanged) {747 if (sub.state === "Refunded") {748 refunded30d += 1;749 }750 if (751 sub.willRenew === false &&752 sub.cancellationReason === "UserCanceled" &&753 CANCELED_STATES.has(sub.state)754 ) {755 canceled30d += 1;756 }757 }758 759 // Sort per-currency MRR for deterministic UI rendering. The760 // headline `mrrMicros` below intentionally uses only the761 // project's reporting currency; other currencies remain visible762 // in `excludedMrrByCurrency` instead of being silently summed763 // by IAPKit.764 const sorted = Array.from(mrrAccumulators.entries()).sort(765 ([a, av], [b, bv]) => (bv !== av ? bv - av : a.localeCompare(b)),766 );767 const mrrByCurrency = sorted.map(([currency, mrrMicros]) => ({768 currency,769 mrrMicros,770 }));771 const reportingMrr = selectReportingMrr(772 mrrByCurrency,773 project.reportingCurrency,774 );775 776 return {777 activeSubs,778 inGracePeriod,779 inBillingRetry,780 refunded30d,781 canceled30d,782 mrrMicros: reportingMrr.mrrMicros,783 currency: reportingMrr.currency,784 reportingCurrency: reportingMrr.currency,785 mrrByCurrency,786 excludedMrrByCurrency: reportingMrr.excludedMrrByCurrency,787 };788 },789});790 791// Daily revenue + lifecycle metrics for the Analytics dashboard. Reads792// pre-computed rollups from `revenueMetricsDaily` (populated by the793// `recomputeAllRevenueMetrics` cron) so the dashboard never scans the794// raw webhookEvents log on render.795//796// `fromDay` and `toDay` are inclusive ISO date strings (YYYY-MM-DD,797// UTC) — same format `revenueMetricsDaily.day` is stored under, so798// the index range is a direct string comparison.799//800// Return shape: one entry per rollup row, i.e. one per801// (day, currency, productId, platform). Aggregation across rows802// happens client-side (`analytics.tsx`) so the dashboard can switch803// between filter combinations without re-querying. Summing across804// currencies is a UI-side concern — `revenueMicros` from a USD row805// and a EUR row cannot be added without an FX rate.806const platformValidator = v.union(v.literal("IOS"), v.literal("Android"));807 808export const getRevenueMetrics = query({809 args: {810 apiKey: v.optional(v.string()),811 projectId: v.optional(v.id("projects")),812 fromDay: v.string(),813 toDay: v.string(),814 // Server-side `productId` / `currency` / `platform` filters were815 // removed because the dashboard does all of that filtering816 // client-side (the unfiltered fetch is what backs the filter-817 // dropdown population — narrowing the scan would defeat that),818 // and a server-side narrowing path was incompatible with that819 // contract: when a productId was pinned the dropdowns silently820 // collapsed to that SKU's currencies / platforms only. If a821 // future caller needs server-side narrowing for a non-dashboard822 // surface, add a separate query — don't reintroduce these as823 // optional args on this one.824 },825 returns: v.object({826 days: v.array(827 v.object({828 day: v.string(),829 currency: v.string(),830 productId: v.string(),831 platform: platformValidator,832 activeSubs: v.number(),833 newSubs: v.number(),834 renewals: v.number(),835 cancellations: v.number(),836 refunds: v.number(),837 revenueMicros: v.number(),838 }),839 ),840 // Available filter values surfaced to the dashboard so the UI841 // can render dropdowns / chiclets for everything the project842 // actually has data for, without a second round-trip.843 currencies: v.array(v.string()),844 productIds: v.array(v.string()),845 platforms: v.array(platformValidator),846 // True when the underlying scan hit `REVENUE_SCAN_CAP` and the847 // returned rows are a partial view of the requested window. The848 // dashboard surfaces this as a banner so a truncated chart is849 // visible to the operator instead of silently rendering a850 // partial tail.851 truncated: v.boolean(),852 }),853 handler: async (ctx, args) => {854 const project = await projectForReadArgs(ctx, args, "admin");855 if (!project) {856 return {857 days: [],858 currencies: [],859 productIds: [],860 platforms: [],861 truncated: false,862 };863 }864 865 // Reject ranges past the dashboard's longest preset (90 days)866 // before issuing the index scan. A misbehaving client can867 // otherwise request `fromDay = "1970-01-01"` and force the868 // server to materialize every rollup row in the project. The869 // 90-day cap matches `RANGES` in `analytics.tsx`; widening870 // there should bump this in lockstep.871 const MAX_RANGE_DAYS = 92;872 if (args.fromDay > args.toDay) {873 throw new Error(874 `getRevenueMetrics: fromDay (${args.fromDay}) is after toDay (${args.toDay}).`,875 );876 }877 const fromMs = Date.parse(`${args.fromDay}T00:00:00.000Z`);878 const toMs = Date.parse(`${args.toDay}T00:00:00.000Z`);879 if (Number.isNaN(fromMs) || Number.isNaN(toMs)) {880 throw new Error(881 `getRevenueMetrics: invalid ISO date(s) fromDay=${args.fromDay} toDay=${args.toDay}.`,882 );883 }884 const spanDays = Math.round((toMs - fromMs) / 86_400_000) + 1;885 if (spanDays > MAX_RANGE_DAYS) {886 throw new Error(887 `getRevenueMetrics: span of ${spanDays} days exceeds MAX_RANGE_DAYS=${MAX_RANGE_DAYS}.`,888 );889 }890 891 // Range scan over `revenueMetricsDaily` via892 // `by_project_and_day_and_currency` (`[projectId, day, currency]`).893 // The dashboard does all filtering (currency / product /894 // platform) client-side, so we deliberately return the full895 // window — narrowing here would prune the data the dashboard896 // needs to populate its filter dropdowns.897 //898 // Capped at REVENUE_SCAN_CAP to stay under Convex's 32k899 // document-scan limit per query. A 92-day range across a900 // maximalist project (30 SKUs × 3 currencies × 2 platforms =901 // 180 rows/day → ~16.5k rows for 92 days) fits inside this902 // cap; truncation surfaces as the `truncated` flag below and903 // an amber banner on the dashboard so a partial chart is904 // never silently rendered.905 const REVENUE_SCAN_CAP = 20_000;906 const allRows = await ctx.db907 .query("revenueMetricsDaily")908 .withIndex("by_project_and_day_and_currency", (q) =>909 q910 .eq("projectId", project._id)911 .gte("day", args.fromDay)912 .lte("day", args.toDay),913 )914 .take(REVENUE_SCAN_CAP);915 const truncated = allRows.length === REVENUE_SCAN_CAP;916 if (truncated) {917 console.warn(918 `[getRevenueMetrics] revenueMetricsDaily scan hit REVENUE_SCAN_CAP=${REVENUE_SCAN_CAP} for project=${project._id} range=${args.fromDay}..${args.toDay}; chart will undercount the tail.`,919 );920 }921 922 // Populate filter-dropdown choices from the unfiltered range923 // scan so the UI can render every available currency /924 // productId / platform regardless of which filter the user925 // currently has active.926 const currencies = new Set<string>();927 const productIds = new Set<string>();928 const platforms = new Set<"IOS" | "Android">();929 for (const row of allRows) {930 if (row.currency) currencies.add(row.currency);931 productIds.add(row.productId);932 platforms.add(row.platform);933 }934 935 return {936 days: allRows.map((row) => ({937 day: row.day,938 currency: row.currency,939 productId: row.productId,940 platform: row.platform,941 activeSubs: row.activeSubs,942 newSubs: row.newSubs,943 renewals: row.renewals,944 cancellations: row.cancellations,945 refunds: row.refunds,946 revenueMicros: row.revenueMicros,947 })),948 currencies: Array.from(currencies).sort(),949 productIds: Array.from(productIds).sort(),950 platforms: Array.from(platforms).sort(),951 truncated,952 };953 },954});955 956async function loadPeriodByProductId(957 ctx: QueryCtx,958 projectId: Id<"projects">,959): Promise<Map<string, string | undefined>> {960 const periodByProductId = new Map<string, string | undefined>();961 for (const platform of ["IOS", "Android"] as const) {962 const productRows = await ctx.db963 .query("products")964 .withIndex("by_project_and_platform", (q) =>965 q.eq("projectId", projectId).eq("platform", platform),966 )967 .collect();968 for (const product of productRows) {969 if (970 !periodByProductId.has(product.productId) ||971 (periodByProductId.get(product.productId) === undefined &&972 product.billingPeriod !== undefined)973 ) {974 periodByProductId.set(product.productId, product.billingPeriod);975 }976 }977 }978 return periodByProductId;979}980