Lantern

Appearance

Admin Billing Portal — Cloud Run + BQ Pipeline Wiring ​

Last Updated: 2026-05-14 Status: Overview + top-nav shipped (local, not yet pushed); Reports / Vendors / Settings deep content not built Branch: claude/billing-admin-bq-wiringLatest commit: ad2fbdccParent: 2026-05-10-bigquery-billing-phase-2.md — this plan consumes the pipeline that one stood up

Task tracker ​

IDTaskStatusNotes / commit
3.1Cloud Run analytics-api /analytics/billing/metricsDone2cfb9ce3 — granularity, per-vendor, drift, subscription, all-time
3.2Overview rewrite (KPI strip, history, 3 donuts, footer)Done2cfb9ce3 + d3c67c7e
3.3Copilot $39 from invoice_actuals.subscription_chargeDone2cfb9ce3 — replaces broken CF individual → $19 detection
3.4Cloud Scheduler migrationDoneShipped separately in PR #519
5.1Top nav restructure + hash routing + dropdown tabsDone42c01933 + ad2fbdcc (overflow-clip fix)
5.2Reports tab content (KPI / chart / cards / tables)NextAll Time / YTD / Monthly / Weekly / Custom — see §5.2
5.3Vendors tab content (per-vendor deep pages + sub-tabs)After 5.2Migrate per-vendor CF fetchers to analytics-api as part of this — §5.3
5.4Settings tab content (drift, seeds, ingest status)After 5.3Drift card moved off Overview, lives here — §5.4
6.aCSV export utility (shared across Reports + Vendors)ParkedClient-side row-to-CSV — surfaces in 5.2 first
6.bOpen-in-BigQuery deep linksParkedGenerated BQ console URL with pre-filled SQL
6.cBackfill pricing seeds for pre-deployment monthsParkedLets drift signals work for Feb–Apr 2026 GitHub history
6.dConfirm Anthropic invoice parser emitting rowsParkedParser landed PR #511; needs spot-check
6.eRetire Firebase Cloud Function getBillingData entirelyAfter 5.3Per [[prefer-cloud-run-over-functions]] — depends on vendor migration
6.fEmail digest ("your spend so far")Parked (v3)Out of scope for this PR
7.aHistorical backfill via synthesized invoice line itemsDoneNew Dataform view invoice_synthetic_line_items, UNIONed into fact_cost_line_items; drift mart excludes synthetic from estimate side. Removes the API-side invoice-fallback fork.

Legend: Done = committed locally on the branch. Next = first thing to pick up. After X = blocked on X. Parked = not in this PR.

Pipeline flow ​

Current configuration ​

All ingest runs as Cloud Run Jobs triggered by Cloud Scheduler at staggered 03:00–03:30 America/Los_Angeles each day. Dataform runs last (03:30) so all raw rows are present before normalization + attribution. GCP and Anthropic don't have ingest jobs — GCP uses Google's native Cloud Billing export (continuous), Anthropic writes per-request from the running app.

mermaid
flowchart LR
  %% ── Ingest sources ──
  subgraph SCH["Cloud Scheduler · daily · America/Los_Angeles"]
    direction TB
    s1["03:00 · ingest-cloudflare"]
    s2["03:10 · ingest-github"]
    s3["03:20 · ingest-invoices"]
    s4["03:30 · dataform-billing-daily"]
  end

  gcp_export["GCP Cloud Billing<br/>native export<br/><i>(continuous, Google-managed)</i>"]
  anth_app["Anthropic per-request<br/>logger in services/api/assistant<br/><i>(continuous, app-driven)</i>"]

  %% ── Raw layer ──
  subgraph RAW["billing_raw — sources"]
    direction TB
    r_cf["cloudflare_raw"]
    r_gh["github_raw<br/>github_account_raw"]
    r_inv["invoice_actuals<br/><i>(Gmail-parsed)</i>"]
    r_anth["anthropic_request_usage"]
    r_gcp["billing_export_gcp.<br/>gcp_billing_export_*"]
  end

  s1 --> r_cf
  s2 --> r_gh
  s3 --> r_inv
  anth_app --> r_anth
  gcp_export --> r_gcp

  %% ── Norm layer ──
  norm["billing_norm.fact_cost_line_items<br/><i>(incremental · daily 2-day reprocess)</i>"]
  synth["billing_norm.invoice_synthetic_line_items<br/><i>(view · gap-fills pre-ingest history)</i>"]

  r_cf --> norm
  r_gh --> norm
  r_anth --> norm
  r_gcp --> norm
  r_inv --> synth
  synth -->|"UNION ALL"| norm

  %% ── Attrib seeds ──
  d_alloc[("dim_cost_allocation_rules")]
  d_ghpr[("dim_github_plan_pricing")]
  d_anthpr[("dim_anthropic_pricing")]
  d_tax[("dim_taxonomy")]

  d_ghpr -. "JOIN @ usage_start" .-> norm
  d_anthpr -. "JOIN per-request" .-> norm

  %% ── Attributed fact ──
  attr["billing_attrib.fact_cost_attributed<br/><i>(incremental)</i>"]
  norm --> attr
  d_alloc -. "lowest-priority rule wins<br/>→ (env, app, attributed_service)" .-> attr
  d_tax -.-> attr

  s4 -. "compiles + runs<br/>norm + attrib" .-> norm
  s4 -.-> attr

  %% ── Marts ──
  subgraph MARTS["billing_marts — consumption views"]
    direction TB
    m_recon["invoice_reconciliation<br/><i>(view)</i>"]
    m_unatt["unattributed_cost_daily<br/><i>(view)</i>"]
  end

  attr --> m_recon
  r_inv --> m_recon
  attr --> m_unatt

  %% ── Consumers ──
  api["Cloud Run · analytics-api<br/>GET /analytics/billing/metrics<br/>GET /analytics/billing/report"]
  ui["Admin /admin/billing<br/>Overview · Reports · Vendors · Settings"]

  attr --> api
  m_recon --> api
  r_inv --> api
  api --> ui

What we expect to build ​

Pipeline gap closed by §7.a (synthetic invoice-derived line items, now landed and shown above as part of the current configuration). Remaining work is admin-portal-side: the per-vendor deep pages (§5.3) and the diagnostics/settings surface (§5.4).

mermaid
flowchart LR
  api["analytics-api<br/><i>(existing)</i>"]:::existing
  ui["Admin /admin/billing<br/><i>(existing shell)</i>"]:::existing

  ov["Overview · §3.2<br/><i>(done)</i>"]:::existing
  rp["Reports · §5.2<br/><i>(done)</i>"]:::existing

  v["Vendors tab · §5.3<br/><i>per-vendor deep-dive<br/>Summary / Subscription /<br/>Usage / Line Items / Drift</i>"]:::proposed
  st["Settings tab · §5.4<br/><i>drift signals · pricing seeds ·<br/>ingest-job status · BQ troubleshooting</i>"]:::proposed

  api --> ov
  api --> rp
  api --> v
  api --> st
  ov --> ui
  rp --> ui
  v --> ui
  st --> ui

  classDef existing stroke:#6b7280,fill:#1f2937,color:#e5e7eb
  classDef proposed stroke-dasharray: 5 5,stroke:#a855f7,fill:#1f1132,color:#e5e7eb

Synthetic backfill — what landed: A new billing_norm.invoice_synthetic_line_items view splits each Gmail-parsed invoice into one row per (vendor, day, segment) for periods whose invoice_period_end is strictly before the vendor's earliest raw-ingest date. Segments are subscription / metered / tax / credit when the parser surfaced the breakdown, or a single invoice_total when only the grand total was parseable. UNIONed into fact_cost_line_items so attribution + downstream queries see synthetic rows identically to real ones. The drift mart (invoice_reconciliation) excludes synthetic from estimated_usd so historical drift signals don't self-compare invoice-against-its-own-synthesized-estimate. The "entire period before" gap filter guarantees no double-counting in months where raw ingest started mid-month.

1. Goal ​

Connect the admin billing surface to the BigQuery pipeline via Cloud Run (analytics-api), retire the on-demand Firebase Cloud Function path, and restructure the surface into a coherent IA:

  • Overview — status board, "am I spending OK?" in 5 seconds
  • Reports — time-period deep reports (All Time / YTD / Monthly / Weekly / Custom) with exports + raw-BQ deep-links
  • Vendors — per-vendor drilldown pages with horizontal sub-tabs
  • Settings — config + diagnostics (drift signals land here)

Bundle into a single PR (per [[bundle-related-work-into-fewer-prs]]) and target dev, draft-only (per [[open-prs-as-drafts]]).

2. IA decisions locked in ​

  • Top nav: Overview · Reports ▾ · Vendors ▾ · Settings (hybrid dropdown + breadcrumb)
    • Dropdowns on top nav; active sub-selection echoes as a breadcrumb (Reports › Monthly › May 2026) where each segment is clickable
  • Report page layout: linear + dashboard combo
    • Page header with [↻ refresh] / [Export CSV] / [Open in BQ ↗]
    • KPI strip → 12-month stacked bar → 2-up dashboard (spend-by-day + donut) → vendor breakdown table → line items table (top 20)
  • Vendor page layout: page header (vendor name + headline KPIs) → horizontal tab strip Summary / Subscription / Usage / Line Items / Drift

3. Done ​

3.1 Cloud Run analytics-api (services/api/analytics/src/) ​

  • GET /analytics/billing/metrics?granularity=weekly|monthly|yearly returns:
    • Totals: totalCurrentMonth, totalLastMonth, totalYtd, totalAllTime
    • Per-vendor: currentMonthByVendor, lastMonthByVendor, ytdByVendor, totalByVendor
    • Per-vendor/per-service: currentMonthByVendorService (Copilot vs Actions, GCP service mix)
    • History: monthlyHistory[] (per-granularity)
    • Latest invoiced subscription fee: latestSubscriptionByVendor
    • Drift signals: driftSignals[]
  • Auth: verifyFirebaseToken + requireRole('admin')
  • Source-of-truth choices: monthly/yearly/total totals from invoice_reconciliation (COALESCE invoiced/estimated with current-month CASE prefering estimated); per-line-item breakdown from fact_cost_attributed; subscription fee from invoice_actuals

3.2 Overview rewrite (apps/admin/src/admin/billing/Billing.jsx) ​

  • Cost Summary KPIs: Daily Average / Monthly / Yearly / Total
  • Spending History: full-width stacked bar with Weekly/Monthly/Yearly granularity toggle
  • At a glance: 3-up donuts (Monthly / Yearly / Total vendor mix) — replaces old drift section + Trend & Top Spend
  • Providers grid (timeframe-labeled, navigates to per-vendor tabs)
  • Quick links footer (View this month's report →, Run a custom BigQuery query →)
  • Cut: MoM bar chart, Trend & Top Spend section, standalone Drift section (drift parked for Settings tab)

3.3 Copilot $39 fix ​

  • Reads invoice_actuals.subscription_charge_usd for the latest invoice per vendor — the real bill, not pipeline guesswork
  • Overrides providers.github.usage.copilot.monthlyEstimate in admin client

3.4 Cloud Scheduler migration (PR #519, separate) ​

  • Dataform cron moved from internal workflowConfig.cronSchedule to Cloud Scheduler. Closed split-permission gotcha.

4. In progress / next ​

Nothing actively being edited. Next chunk picks up at §5.1.

5. Designed, not yet built ​

5.1 Top nav restructure (prerequisite for 5.2 + 5.3) ​

  • Replace flat Overview / Cloudflare / GCP/Firebase / GitHub / Railway tabs with Overview · Reports ▾ · Vendors ▾ · Settings
  • Build a <DropdownTab> component (hover/click opens menu; active sub-selection shows as chip on the tab label)
  • Build a <Breadcrumb> row that renders below the tabs when a sub-selection is active
  • Route via hash: #/admin/billing/reports/monthly/2026-05 etc. so deep links work

5.2 Reports tab ​

  • Sub-options: All Time / YTD / Monthly / Weekly / Custom range
  • Layout per §2:
    • KPI strip (4 metrics — period-scoped totals + comparison)
    • 12-month stacked bar (reuses SpendingHistory with locked granularity per report type)
    • 2-up dashboard cards: Spend by day (area / sparkline) + Distribution (donut)
    • Vendor breakdown table — sortable, columns: vendor, spend, % total, vs last period, trend
    • Line items table — top 20, link to "show all in BQ"
    • Page header: [Export CSV] (client-side from current rows) + [Open in BigQuery ↗] (deep link with pre-filled SQL)
  • Custom range needs a date-range picker — punt to v2 if time-boxed

5.3 Vendors tab ​

  • Sub-options: GitHub / GCP / Cloudflare / Railway / Anthropic
  • Per-vendor page: vendor header (name + headline KPIs) then horizontal sub-tabs
    • Summary: vendor-only 12mo chart, KPI strip, quick links into other sub-tabs
    • Subscription: tier, seats, $/seat, next bill date, tier-change history
    • Usage: vendor-specific metered (Actions minutes for GitHub, service mix for GCP, etc.)
    • Line Items: full line-item table for the vendor
    • Drift: vendor-specific drift signals (subset of the global drift feed)
  • Migration work: as each Vendor page goes live, move that vendor's on-demand fetcher from services/functions/firebase/modules/billing.js to analytics-api. Goal: retire the CF entirely. See [[prefer-cloud-run-over-functions]].

5.4 Settings tab ​

  • Drift signals diagnostics panel (the pipeline-health view we cut from Overview)
  • Pricing-seed visibility (read-only display of dim_github_plan_pricing etc.)
  • Ingest-job status panel (last-run timestamps from ops.pipeline_runs)
  • "BQ pipeline unreachable" troubleshooting affordances

6. Parked / future ​

  • CSV export — client-side row-to-CSV utility (shared across Reports + Vendors tables)
  • Open-in-BigQuery deep links — generate BQ console URL with ?p=<project>&page=queryresults&sq=<encoded SQL>
  • Backfill pricing seeds for pre-deployment months — currently history is invoice-only for Feb-Apr 2026 GitHub; seeding rule-based estimates would let drift signals work historically
  • Anthropic invoice parser — landed (PR #511); confirm it's emitting rows
  • Forecast / projected-EOM — was on Overview as "vs last month" hint; cut for now, could resurface in Reports
  • Per-vendor SLO tiles — uptime / error rate on Vendor → Summary? out of scope here
  • Email digest — weekly "your spend so far" email; v3

7. PR / shipping discipline ​

  • Bundle 5.1 + 5.2 + 5.3 into the same PR (claude/billing-admin-bq-wiring) per [[bundle-related-work-into-fewer-prs]]
  • Open as draft (per [[open-prs-as-drafts]]). User marks ready for review herself.
  • PR base: dev (per [[never-base-pr-on-main-without-explicit-ask]] / project default)
  • Don't push the branch until user explicitly approves (per [[no-unprompted-push]])

8. Open questions ​

  • Custom date range picker UX — calendar popover, or two inline inputs? Defer to first build iteration.
  • Vendor sub-tab persistence — should Vendors › GitHub › Usage survive page reloads? Hash routing handles it, but need to wire.
  • "Total" math when pipeline has been running >1 year — currently Total = invoice_reconciliation rolled up. Confirm semantics when we cross the 12-month mark.

9. Related memory ​

  • [[prefer-cloud-run-over-functions]] — durable directive
  • [[bundle-related-work-into-fewer-prs]] — single-PR scope
  • [[open-prs-as-drafts]] — --draft always
  • [[billing-pipeline-state]] — what the BQ pipeline currently captures
  • [[operator-copilot-tier]] — Pro+ at $39, seeded correctly

Built with VitePress

Copyright © 2026 Lantern