Groups membership join X create
I’m building a database with Supabase and I need to manage group memberships. The database has two tables:
What’s the best way to handle these cases while keeping RLS rules intact, ensuring secure validation, and without using Edge Functions?
- groups: Contains group info (id, name, join_code, etc.).
- group_members: Tracks group members (group_id, user_id, role, join_code, etc.).
- When a group is created:
• The creator should be automatically added to group_members with is_admin = TRUE. - When a user joins a group using a join_code:
• The user only enters the join_code shared by another member; they don’t have access to the group’s name or id.
• If the join_code is valid, the user is added to group_members.
- Automatic insertion for creators:
• A trigger should add the creator to group_members when a group is created.
• Problem: RLS on group_members blocks this insertion because it requires a valid join_code. - Joining a group with a join_code:
• A user must be added to group_members if the join_code is valid.
• Problem: Checking the join_code in the same table (group_members) can cause a circular dependency. - RLS constraints:
• RLS must enforce:
• Only users with a valid join_code can join.
• Creators can be added automatically during group creation.
What’s the best way to handle these cases while keeping RLS rules intact, ensuring secure validation, and without using Edge Functions?
