arch: dual plan mutation paths (mock RPC vs Stripe webhooks) can desync state

[drozhzhin-n-e]

Problem

Two paths can mutate organizations.plan_id and seats_limit:

1. Webhook path — Stripe webhook → Edge Function → apply_billing_subscription (service_role)
2. RPC path — update_organization_plan (granted to authenticated, admin check only)

The RPC path is intentional for mock billing, but in production Stripe mode an org admin can still call it directly via PostgREST, potentially putting Postgres plan state out of sync with Stripe.

Impact

• Plan/seat state in Postgres can diverge from Stripe subscription
• Entitlements (seat limits, email quotas) may not match what customer is actually paying for
• Hard to detect unless monitoring is in place

Proposed fix

Option A: Revoke EXECUTE on update_organization_plan from authenticated when Stripe is the billing provider (requires app-level enforcement or a DB flag).

Option B: Add a guard inside update_organization_plan that checks organization_billing.provider; if Stripe, reject the call.

Option C: Document as intentional design for mixed providers and accept the risk.

Files

• supabase/migrations/0008_org_billing_plan.sql
• supabase/migrations/0017_update_organization_plan_seats.sql
• supabase/migrations/0019_guard_seats_limit_override.sql
• docs/adr/0002-billing-multi-provider.md

[hipfeed]

I would make Stripe the only production writer for plan/seat state and keep the mock RPC behind an explicit provider guard.

A practical shape:

1. Add a billing_provider or billing_mode source of truth at org or app config level.
2. Inside update_organization_plan, reject direct mutations when provider is stripe, even for org admins.
3. Keep the service-role webhook path as the only writer for Stripe-backed plan_id and seats_limit.
4. Log attempted RPC mutations in Stripe mode so you can detect frontend/client drift.
5. Add a regression test that proves an authenticated org admin cannot change seats_limit directly while Stripe mode is active.
6. Add a reconciliation query that compares Postgres plan state against the last processed Stripe subscription event.

Option C seems risky for production because the entitlement surface is exactly where customers notice mismatch first. If mock billing is needed, I would isolate it behind a dev/test provider flag instead of sharing the same mutation path.