Super-Admin Billing CSV — Stripe enrichment

Date: 2026-06-15 Ticket context: HODOR-1159 (admin panel reports enhancements)

Goal

Enrich the super-admin analytics Download CSV export with per-account Stripe data: last payment in the selected month, next invoice, what the payment is for, trial status, and the explicit AI free/bought split for the selected month.

Background (current state)

  • SuperAdmin::AnalyticsController#billing_csv builds the CSV synchronously in-request via fetch_per_account_billing and returns it with send_data.
  • It iterates every active account in scope (all accounts when no account_id filter is set), joining AiMonthlyUsage for the selected month.
  • Stripe data is only available per-account via BillingService.get_account_subscription(account_id, admin_id) — an HTTP call to the billing microservice, each fanning out to ~5 Stripe API calls.
  • The dedicated per-account Stripe page renders _stripe_billing / _ai_usage_history from that same service response.

Why the architecture must change

Adding Stripe data to a full export means one HTTP roundtrip per account (each = multiple Stripe calls). For hundreds of accounts that is hundreds of sequential calls → request timeout + Stripe rate-limit risk. Therefore CSV generation moves to a background job that emails the result.

Decisions (confirmed with user)

  1. Full-export handling: background Sidekiq job + email (not synchronous, not single-account-only).
  2. Last payment: month-scoped — show only if the payment falls within the selected analytics month; otherwise blank.
  3. Mailer: new lightweight SuperAdmin::BillingCsvMailer (plain, no account branding), sent to current_super_admin.email.
  4. Per-account resilience: rescue per account; on Stripe/billing failure put an error marker in that account’s Stripe columns and continue.
  5. Month semantics: last-payment is month-scoped; Stripe subscription fields (trial, next invoice, product, status) reflect live state at export time. A CSV footer line documents this.
  6. Columns: add all proposed columns (see below).

Architecture

Download CSV link (GET billing_csv)
  -> AnalyticsController#billing_csv
       enqueues SuperAdmin::BillingCsvJob(month, account_id, super_admin_id)
       redirect_back with flash notice
  -> SuperAdmin::BillingCsvJob#perform
       SuperAdmin::BillingCsvBuilder.new(month, account_filter, super_admin_id).generate  # CSV string
       SuperAdmin::BillingCsvMailer.with(...).billing_csv.deliver_now

Components

  • SuperAdmin::AnalyticsController#billing_csv (modified): parse month / account_id, enqueue job, redirect back to super_admin_analytics_path (with the same month / account_id params) and a flash notice. No longer renders the CSV inline.

  • SuperAdmin::BillingCsvJob (new, app/jobs/super_admin/billing_csv_job.rb): perform(month_str, account_id, super_admin_id). Looks up the SuperAdmin (for email + audit id), calls the builder, hands CSV to the mailer. Runs reads on the replica where the builder touches the DB.

  • SuperAdmin::BillingCsvBuilder (new, app/services/super_admin/billing_csv_builder.rb): owns all CSV logic moved out of the controller (base_accounts_scope, AI-usage join, row formatting, totals, footer) plus the new Stripe enrichment. Single public method #generate returns the CSV string. This keeps the job thin and the logic unit-testable without HTTP/Sidekiq.
    • DRY (decided): the pricing/scope constants (PLAN_PRICES, OVERFLOW_PRICE, INBOX_PRICE, FREE_INBOXES, INTERNAL_ACCOUNT_IDS) are extracted into a single shared module SuperAdmin::BillingConstants (app/services/super_admin/billing_constants.rb or app/controllers/super_admin/concerns/billing_constants.rb). Both SuperAdmin::AnalyticsController and SuperAdmin::BillingCsvBuilder include it, so the values are defined once. The controller’s existing literal constants are removed and replaced by the shared ones.
    • Per-account Stripe fetch wrapped in begin/rescue StandardError → on failure the account’s Stripe columns carry an error marker (e.g. "ERROR"), logged via Rails.logger.error, and the row still emits.
  • SuperAdmin::BillingCsvMailer (new, app/mailers/super_admin/billing_csv_mailer.rb): billing_csv action, to: super_admin.email, attaches billing_<YYYY-MM>.csv, plain template (app/views/super_admin/billing_csv_mailer/billing_csv.*). Subject + body via i18n (English first, then yarn i18n:sync not needed — backend locale).

Stripe field mapping (from get_account_subscription response data)

  • Has Billing ← data.hasBilling (Yes/No).
  • Subscription Status ← data.subscription.status.
  • In Trial ← data.subscription.trial.isTrial (Yes/No).
  • Trial Ends ← data.subscription.trial.trialEnd (unix → %Y-%m-%d).
  • Stripe Product ← data.subscription.productName.
  • Billing Interval ← map data.subscription.interval (month→Monthly, year→Annual).
  • Last Payment (Month) / Last Payment Date ← from data.paymentHistory (current-year paid invoices), select the entry whose date falls in the selected month; format amount as <amount> <CURRENCY>. Blank if none.
  • Next Invoice Amount / Next Invoice Date ← data.upcomingInvoice.amountDue / .periodEnd. Blank if absent.

Money formatting mirrors the _stripe_billing partial: cents → "%.2f CUR". Unix → Time.zone.at(...).strftime('%Y-%m-%d').

AI free/bought split

The existing CSV already carries AI Conversations Used (total) and AI Overflow (bought = used - plan_limit). To mirror the stripe_billing AI page, add AI Free Used = min(used, plan_limit). (Bought/overflow already present; this completes the free/bought pair for the selected month.)

Final column order

Existing: Account ID, Account Name, Plan, Agents, Subscription Revenue, Additional Inboxes, Inbox Revenue, AI Conversations Used, AI Free Used, AI Overflow, AI Overflow Cost, Total Revenue (AI Free Used inserted right after AI Conversations Used.)

Appended Stripe columns: Has Billing, Subscription Status, In Trial, Trial Ends, Stripe Product, Billing Interval, Last Payment (Month), Last Payment Date, Next Invoice Amount, Next Invoice Date

Totals row

  • Numeric existing columns summed as today; AI Free Used added to the sum.
  • All appended Stripe columns left blank in the TOTAL row (mixed currencies / live state are not summable).

Keep existing period/snapshot footer lines; add: "Stripe subscription fields (trial, next invoice, product, status) reflect live state at export time, not the selected month."

Error handling / edge cases

  • Billing-service/Stripe failure for an account → "ERROR" markers in that account’s Stripe columns, logged, row still emitted (job does not abort).
  • Account with no Stripe customer → Has Billing = No, other Stripe columns blank.
  • Account with customer but no subscription → Has Billing = Yes, subscription-derived columns blank, last payment still month-scoped.
  • No payment in selected month → last-payment columns blank.
  • Empty account scope → CSV with headers + totals + footer only (current behavior).
  • month param parsing reuses the controller’s existing parse_month semantics (invalid → current month beginning).

Testing

  • SuperAdmin::BillingCsvBuilder spec (primary): with BillingService stubbed, assert —
    • all new columns present and in the documented order;
    • last-payment shown only when in selected month, blank otherwise;
    • trial Yes/No, product, interval mapping, next-invoice mapping;
    • AI Free Used = min(used, limit), overflow unchanged;
    • per-account Stripe failure → "ERROR" markers, other rows intact;
    • no-customer (hasBilling=false) and customer-without-subscription cases;
    • totals row sums numeric cols incl. AI Free Used; Stripe cols blank in totals;
    • footer note present.
  • SuperAdmin::BillingCsvJob spec: enqueues/performs, calls builder, triggers mailer with correct recipient/attachment (builder + mailer stubbed/mocked).
  • SuperAdmin::BillingCsvMailer spec: recipient = super-admin email, attachment filename billing_<YYYY-MM>.csv, subject from i18n.
  • Controller spec (billing_csv): enqueues SuperAdmin::BillingCsvJob with parsed month / account_id / super_admin_id; redirects with flash; access still gated by authorize_analytics_access!.

Out of scope

  • No bulk subscription endpoint added to the billing microservice (infra change not permitted; sequential calls in a background job are acceptable).
  • No change to the per-account Stripe page or the on-screen analytics dashboard.
  • No new dependencies.