Avoca — Internal

Appearance

Strict-RLS migration blast radius (Twilio incident + sweep) ​

What happened ​

Migration 20260507130000_strict_rls_members_and_invites.sql enabled and forced RLS on public.members, dropped every existing policy, revoked all privileges from public, anon, authenticated, and granted only to service_role. Same for invites, enterprise_invites, portfolio_invites. The migration's stated intent (cited in its own header) was that application access should go through service-role-backed routes with explicit authz. The blast radius was understated.

Most team-scoped tables in the schema have policies of the form USING (allow_if_team_member((team_id)::bigint)). The helper function reads members to verify the caller has a membership row. The function defaults to SECURITY INVOKER, so when called from a user session it runs as the caller, and the caller cannot read members after the migration. The helper returns false. The parent write or read is denied with no error from the helper itself (zero rows is not an error). Avoca staff bypass the membership branch via is_avoca_staff(), which is why internal testing missed the failure mode; only real non-staff customer use triggered it.

The loudest symptom: Twilio brand-onboarding orphaned live subaccounts on retry. Brand-onboarding ran the ISV Twilio subaccount creation first (succeeded upstream), then upserted into team_twilio_configs via the user-session Supabase client. The upsert was silently denied. The UI saw "no Twilio config" and the operator retried, orphaning another live Twilio subaccount each time. Kareem reported this trying to provision phone numbers for a customer.

Why we care ​

Any user-session-bound Supabase write or read that touches a team-scoped table has the same failure mode for non-Avoca users. Our AutoOps work writes to team-scoped tables (autoops_team_configs, calls, etc.) and runs in environments where the test path is often "logged in as Avoca staff", which masks the problem. If a write surface ever ships behind a non-Avoca user session, this pattern is the failure mode to expect.

Saba (2026-05-12 04:05 ET) posted: ":alert: RLS predicate function silently denies every query made via the session Supabase client". That's the exact failure mode named explicitly. The naming elevates this from a one-off Twilio fix to a documented pattern at Avoca.

Bharat (2026-05-11 21:47 PM, reshared from #eng-pr-reviews): "Parts of the product broke, customers ran into issues, campaigns were affected, and we risked data integrity. Even under urgency, we cannot skip basic communication and coordination. A quick heads-up in #eng-convos, either before or immediately after, should be the minimum bar. Also, it is not clear why this needed to be applied so broadly across modules instead of being more targeted." This confirms the rollout-comms concern is shared at the engineering level, not just our outside read.

The fix ​

PR #10201 (709208be0b, Jackson, merged 2026-05-11 ~16:24 UTC) migrated the brand-onboarding actions into apps/web/app/api/team/[teamId]/brand-onboarding/handlers.ts using createServiceClient from @/utils/supabase/service. The authorization decision happens explicitly at the route handler before the service client is used.

PR #10201 is one entry in a broader sweep responding to the same migration:

PRArea
#10049Migration that introduced the strict RLS (root cause)
#10161[Auth] Profile + team access behind service role
#10163[Auth] Onboarding + team layout reliance on RLS
#10164[Speed-to-Lead] Empty Lead Source dropdown for non-Avoca
#10165[Scheduling] Booking-windows page reads relying on RLS
#10166[Auth] Sweep remaining post-auth user-scoped reads
#10167[Admin] RLS errors in admin API routes (session client)
#10170[Campaigns] RLS-strict campaign bootstrap reads
#10171[Auth] Call-details + SSO config reliance on RLS
#10174[Auth] updateCallFieldAction RLS denial (PGRST116)
#10179[Web] Admin auth vulnerability cleanup
#10193[Thumbtack] Server data access behind service role
#10200Coach RLS issues
#10201[Onboarding] [Twilio] (this incident)

Cleanup of orphan Twilio subaccounts from the prior 2 days was handled manually by Jackson (PSA 2026-05-11 PM).

What we should mirror ​

  • Every new gated write we ship in apps/web should use the service client behind an API endpoint with explicit authz, not the user-session client through a server action.
  • The SECURITY INVOKER vs SECURITY DEFINER choice on RLS helpers is invisible from policy syntax. If we author a helper used by an RLS policy, it almost certainly needs SECURITY DEFINER with SET search_path = '' and an explicit role guard.
  • Silent denials look like generic Supabase errors. If we see PostgrestError without explicit policy-violation detail in a user-facing surface, RLS is the first thing to check.
  • Don't rely on Avoca-staff testing to validate non-staff paths. Any policy with an OR is_avoca_staff() branch needs at least one rehearsal as a non-staff user.

Unsolicited Opinion

The helper allow_if_team_member(team_id) is not in the visible migrations directory (likely defined via the Supabase dashboard or in an older migration outside our checkout window). What's verifiable is the policy pattern: many tables have policies named "All access for team or avoca staff (fixed)" with USING (allow_if_team_member((team_id)::bigint)). See add_team_id_rls_to_bookings.sql for the canonical shape.