How To Design Multi-Tenant RBAC with Supabase RLS and Next.js Middleware

Use RLS as the first boundary and middleware as the second boundary. In this platform, SQL policies enforce tenant and role constraints per table, while middleware routes users by role and subscription status. Service-role clients are limited to specific server-only workflows like webhooks and system notifications.

The SQL policy layer

CREATE POLICY "Staff can view their own appointments"
  ON public.appointments FOR SELECT
  USING (
    get_my_role() IN ('barber', 'receptionist')
    AND staff_id = auth.uid()
    AND salon_id = get_my_salon_id()
  );

CREATE POLICY "Owners can view their salon's credits"
  ON public.salon_credits FOR SELECT
  USING (
    get_my_role() = 'owner'
    AND salon_id = get_my_salon_id()
  );

The middleware layer

if (pathname.startsWith("/dashboard") || pathname === "/onboarding") {
  if (isCustomer) return NextResponse.redirect(new URL("/portal", request.url));
  if (isOwnerOrStaff && profile?.salon_id) {
    const { data: salon } = await supabase
      .from("salons")
      .select("subscription_status, subscription_plan, stripe_customer_id")
      .eq("id", profile.salon_id)
      .single();
    const hasActiveSubscription =
      ["active", "trialing"].includes(salon?.subscription_status || "") ||
      (salon?.subscription_plan === "classic" && !!salon?.stripe_customer_id);
    if (!hasActiveSubscription) {
      return NextResponse.redirect(new URL("/dashboard/settings/billing", request.url));
    }
  }
}

MSIS takeaway

• Put tenant and row ownership in SQL (auth.uid(), get_my_salon_id()).
• Put route and journey decisions in middleware.
• Keep privileged bypasses explicit, narrow, and server-only.

This dual-layer model is what keeps security understandable under rapid product iteration.

Read the full implementation context in the case study: /case-studies/cooard-salon-platform