I have an application that I'm building with supabase that means that there is a many users to many orgs relationship. There's a couple of things I'm trying to work out on a performance basis: 1. Should the user's org association be an array field on the user table or should it be in a joining table of orgs to users? (I think I saw something about it not being recommended to modify supabase tables) 2. It seems like the second option would be a big perf hit if it was used in a security definer function, am I being too cautious with this?

You do not want to modify auth schema or storage schema. You can use a trigger function to create a public table, or your joining table idea is fine. For security RLS, using a security definer stable function is very fast if the user/info is stable for every row in the table. So for org membership or uuid user type stuff per request is fine.

Perfect thanks @garyaustin . While I have you, is it not possible to create a foreign key in the public schema against a field in the auth schema?

Yes. It will keep a user from being deleted if you don't remove all such references, similar to files uploaded by user have foreign key on user table.

I shouldn't have used a double negative

Well if I'm wrong it's your fault for the double negative...

Haha!