Stripe billing — architecture

Busymate's billing layer: subscription billing AND metered/usage billing (metric captured_entries). A first-class component (stripe/ is the home) whose deployable code lives under supabase/functions/ + supabase/migrations/. Stripe is the source of truth; our stripe_* tables are a synced projection kept current by webhooks.

It ships across two dashboard surfaces — a per-user page at /billing (your own subscription, invoices, and usage) and an operator section at /billing-admin (the fleet-wide 8-tab Stripe console, gated billing:view) — plus the five-surface MCP/REST/WS parity below. Billing currently runs in Stripe TEST mode — the seeded STRIPE_SB_* Vault keys are sandbox keys; live keys are a later, separate Vault set, so nothing here is mode-specific (a row is whatever the webhook tells us, and the dashboard derives the test/live badge from the publishable-key prefix without ever reading the secret).

The two flows

Subscription. A user subscribes through a Stripe-hosted Checkout session (stripe-checkout), or manages their plan through the Customer Portal. The stripe-webhook Edge function keeps our stripe_subscriptions + stripe_invoices tables in sync on customer.subscription.* and invoice.created|paid. We listen to invoice.created (not just paid) so metered finalization isn't delayed up to 72h.

Metered, metric captured_entries. Usage = the count of public.entries rows per account, per device, per period. Two halves:

1. Emit (hot path, cheap). A statement-level AFTER INSERT trigger on entries (stripe_usage_emit_from_entries, modelled exactly on entries_touch_devices_last_seen) rolls each batch into the OPEN stripe_usage_events (user_id, device_uuid, 'captured_entries', hour) row via one INSERT … ON CONFLICT DO UPDATE SET quantity = quantity + excluded.quantity. A 100-row batch from one device is ONE upsert, not 100; there is NO synchronous Stripe call on the ingest path. An entry whose device has no resolvable owner is skipped (nobody to bill).
2. Report (scheduled, exactly-once). The stripe-usage Edge function (invoked on a schedule via the stripe-usage-report pg_cron job with a shared secret) reads the UNREPORTED, SETTLED rows (stripe_usage_unreported() — period strictly before the current open hour, so an in-progress hour keeps accumulating), pushes each aggregate to Stripe as a Billing Meter Event keyed by a STABLE idempotency_key (bm_usage_<metric>_<id>_<period>), and stamps the row reported (stripe_usage_mark_reported). A retried run pushes the SAME key → Stripe no-op; the unique idempotency_key column + the reported_at is null guard make a double-stamp a DB no-op → a row is billed exactly once.

The user-facing surfaces

/billing — your own subscription (per-user, gated billing:view)

Settings → Billing is owner-scoped: it shows the caller's own subscription/tier card (status, plan price, quantity, current-period-end, trial/cancel-at-period-end badges), a captured-entries usage chart (a bar per UTC day over the last 30 days), and invoice history with hosted-invoice links. Two actions, both routed through the stripe-checkout Edge function — the Stripe customer is resolved SERVER-SIDE from the verified identity, the browser never passes a customer id:

• Subscribe → opens a Stripe-hosted Checkout session for the configured default price.
• Manage Billing → opens the Stripe Customer Portal (update payment method, cancel, switch plan).

The page flips in realtime on a webhook-driven change via the per-user subscriptions:<user_id> broadcast channel — no polling. The dashboard reads no Stripe secret key here; all secret-key work happens in the Edge functions.

/billing-admin — the operator console (fleet-wide, gated billing:view)

A separate top-level section, distinct from the owner-scoped /billing. Every read is fleet-wide and gated billing:view (admins via grants_all). It's an 8-tab shell, and it's read-only today — the editable controls (default-price picker, knob form, "Report now", webhook rotate, cancel subscription) are Phase B (coming):

It subscribes the shared billing_admin:all operator channel (a webhook-events INSERT or a billing_settings UPDATE) and re-fetches the affected tab — realtime, no polling. Value-blindness: Overview shows the mode, the publishable key, and secret NAMES only — the dashboard never reads sk_… or whsec_…; the test/live mode is DERIVED server-side from the publishable-key prefix via the value-blind stripe-admin Edge function.

Tables (all RLS-on; service-role write only)

• stripe_customers — user_id (PK) ↔ stripe_customer_id (1:1). Owner+billing:view read.
• stripe_webhook_events — event_id (Stripe evt_…) PK = the idempotency ledger. A retried delivery hits the PK and is skipped. Admin (billing:view) read only.
• stripe_subscriptions — status/price/product/period/trial/cancel, owner-scoped. Owner+admin read.
• stripe_invoices — status/amounts (minor units)/hosted_invoice_url/period, owner-scoped.
• stripe_usage_events — the metered rollup ledger; one row per (user_id, device_uuid, metric, period_start); idempotency_key UNIQUE; reported_at null = not yet pushed. Owner+admin read.

A billing_settings singleton (modelled on busybro_settings) holds the operator knobs — default_price_id, trial_days, currency, included_quota, overage_rate, grace_days, mode, etc. — read billing:view, write billing:edit + admin.

The RLS predicate everywhere is user_id = auth.uid() OR has_capability('billing','view') (webhook_events is has_capability('billing','view') only). There is NO client write policy → RLS-on + no permissive write policy = deny; only the service-role webhook/checkout/usage paths write. The stripe_* tables are DELIBERATELY absent from the MCP db_* allowlists (the MCP runs service-role / RLS-bypass) — billing is reachable over MCP ONLY through the dedicated owner-scoped tools below.

RBAC — the billing section

A billing section (view/edit) composes with has_capability exactly like tester/users/devices — there is no section catalog table; a section "exists" the moment has_capability('billing', action) is referenced (it reads the role's capabilities JSON matrix). Admins pass via grants_all. Owner access is the implicit user_id = auth.uid() RLS fallback. billing:edit is reserved for the Phase-B write controls; today only an admin holds it.

Edge functions (all verify_jwt = false)

Shared: _shared/stripeClient.ts (lazy Vault getAppSecret, pinned apiVersion, fail-closed StripeConfigError), _shared/stripeWebhookHandler.ts (the PURE handler + routeEvent projection, hermetically unit-tested). The webhook signature is verified over the RAW body via the SDK's async WebCrypto verifier (webhooks.constructEventAsync + createSubtleCryptoProvider) — the sync constructEvent uses Node crypto and is unavailable in the edge runtime.

Secrets (Vault, NAME-only)

STRIPE_SB_SECRET_KEY (sk_) + STRIPE_SB_PUB_KEY (pk_) are seeded; STRIPE_WEBHOOK_SECRET (whsec_) + STRIPE_USAGE_REPORT_SECRET are user-set after deploy (Dashboard → Environment → Secrets). All secret-key work is Edge-only; the browser uses the publishable key. The /billing-admin Overview tab reports secret presence by NAME — the secret value is never read back on any surface. See the matrix + the one-time setup in stripe/CLAUDE.md and the Vault model in app-secrets-vault.md.

Five-surface parity

• Dashboard — /billing (per-user: subscribe/manage + a captured_entries UsageChart + invoices; nav gated billing:view; Realtime flip on subscriptions:<user_id>; the default plan is read from DB, no redeploy to change it) and /billing-admin (the fleet operator console, gated billing:view; Realtime on billing_admin:all) — now fully editable for billing:edit admins: the default-price picker, the billing_settings knob form, Report-usage-now, rotate-webhook, and cancel-subscription, each {canEdit}-gated in the UI and server-enforced.
• MCP (188 tools) — get_subscription / list_invoices / get_usage (owner-scoped reads via ctx.sub, cap:'billing'; all_users:true for billing:view) + create_checkout_session (confirm-gated mutator) + the Stripe-admin Phase-A reads get_stripe_config (value-blind: { mode, publishable_key } only — mode derived from the pub-key prefix, secret/whsec_ never read) / list_prices / list_customers / get_webhook_events / get_billing_settings (all billing:view; the fleet reads hit the local stripe_* tables service-role, never the db_* allowlists). The 5 admin reads flow to BusyBro automatically (no secret values). Plus the Stripe-admin Phase-B mutators (billing:edit + admin + confirm): set_default_price / set_billing_settings / report_usage_now (NON-secret config — route through the set_billing_settings / stripe_usage_report_now DEFINER RPCs, which re-check billing:edit+is_admin() in-body) + cancel_subscription (DESTRUCTIVE — straight to Stripe via the Vault-backed client). The secret-touching rotate_webhook is a stripe-admin Edge-fn WRITE action ONLY — deliberately not an MCP tool.
• REST — stripe-checkout (POST) + the value-blind stripe-admin (POST: reads billing:view, writes billing:edit+admin) + the stripe_* PostgREST reads + the billing-admin DEFINER RPCs.
• WS — subscriptions:<user_id> (per-user broadcast) + billing_admin:all (the operator webhook-events/billing_settings firehose) Realtime channels (documented in the /ws explorer). Each realtime.messages read arm is folded into the consolidated realtime_messages_read_human CASE (a subscriptions:<uuid> arm gated owner-or-billing:view; the billing_admin:all arm gated billing:view) — NOT a separate policy, so it can't slip into the trailing ELSE true.
• BusyBro — the billing reads + the 3 NON-secret config mutators (set_default_price / set_billing_settings / report_usage_now, parity with set_busybro_settings) flow automatically; create_checkout_session + cancel_subscription are denylisted (money/state — the create_app_secret precedent). rotate_webhook isn't an MCP tool at all.

TEST-1

• _shared/stripeWebhookHandler.test.ts, stripe-checkout/core.test.ts, stripe-usage/core.test.ts, stripe-admin/core.test.ts — deno units, SDK/fetch STUBBED, no Stripe spend; the admin suite asserts value-blindness (no sk_/whsec_ returned).
• supabase/tests/0014_stripe_rls.sql — pgTAP RLS truth-matrix + the captured_entries emitter + the reporter mark-once idempotency (preview branch).
• Path manifest notes/test-paths/stripe.md (critical: true); coverage-map rows; the tester/skills/test-stripe/SKILL.md; ship-gate tier contract.