Supabase "infinite recursion detected in policy": why your team policy loops and how to fix it

You add teams to your Supabase app, write a policy so members can see their own team, and every query on the table starts failing with infinite recursion detected in policy for relation (code 42P17).