All topics

Supabase Many to Many Database Structure

Stockton

1 year ago

Okay, I want to have the concept of workspaces in my app.
1 workspace can have many users, and 1 user can have many workspaces.
I saw the Supabase youtube video on it, but wondering if anyone has done it and can point out any gotcha's ways to make it better.
Particularly around how make the queries efficient and useful.
Also, what's the best way to insert new rows into the many-many table?
Looking for thoughts 🙂

Thank you
Victor Giron

1 year ago

Hey, I'm doing this in a multitenant app using Supabase.

What I would do is to have a table called like "user_workspace"
And have at least the following columns: id, user_id, workspace_id
You can even have roles and add "role_id" as a column
Stockton

1 year ago

Thanks Victor! So at what point do you write to that table? Ie when the workspace is created (first person) and when someone is invited into the workspace?
Victor Giron

1 year ago

It depends, normally when you invite a new user to that workspace you need to create a new row assigning that workspa_id to that user_id
Victor Giron

1 year ago

One use case is when a user creates a new workspace. In this case you will first create a new row in your table "workspaces", and the create a new row in "workspace_user" with the recently created "workspace_id" and the creator as "user_id"
Stockton

1 year ago

Right right
And so from Toddle, do you just query that table directly or are you doing like the "related fields" or whatever and joining a few tables into the single response?
Victor Giron

1 year ago

You mean for creating a new workspace/user relation?
Stockton

1 year ago

No for reading what workspaces they have access to in relation to their other user data
Victor Giron

1 year ago

Oh I see, what I do is that I call the workspace_user table, and filter by user_id = the id of the authenticated user
But maybe you would have a row level security set up for this to prevent people reading wrong data
Lucas G

1 year ago

An alternative would be to use custom claims
Custom claims are stored in the auth_token so performance is nice and paired with RLS, you can manage permissions pretty well
Lucas G

1 year ago

I'm currently assigning an account ID to a user via custom claims and RLS policy checks against that
Lucas G

1 year ago

You can assign roles via claims as well if you'd want to and since it is all included in the auth_token, you don't have to make a call to a table to check for info
😮2
❤️1
Lucas G

1 year ago

So for example, you could add the workspaces a user has access to in a claim and for an RLS policy, you'd check if the claim has the workspace ID in it
Stockton

1 year ago

Okay, interesting. Claims are new to me so I'll have to explore those.
Would you then do a separate API requet to get their like user details (not from auth but a users table)
Victor Giron

1 year ago

I need to check this out
Tod

1 year ago

Great energy @Victor Giron! Your continuous contribution to the toddle Community just made you advance to Community Level 11!
Lucas G

1 year ago

I dug into claims recently so feel free to ask any questions about it, I'll do my best to answer
Maybe save you some time in the researching of them lol
Basically claims fit into the JWT
So you don't have to make additional calls to fetch info in them
They are useful for permission related things
JWT does have a limit on the amount of info it can store but for the most part you're good to store all type of account related info
In my use case for example, as I mentioned, I use it to assign account IDs to users. That way multiple users can have access, not just the User ID that created the item
❤️1
@Stockton
Would you then do a separate API requet to get their like user details (not from auth but a users table)
Lucas G

1 year ago

I don't use user tables personally, I use claims
Lucas G

1 year ago

To use custom claims with supabase you need to install these functions:
https://github.com/supabase-community/supabase-custom-claims
@Stockton
Would you then do a separate API requet to get their like user details (not from auth but a users table)
Victor Giron

1 year ago

If this is for the auth user and only for the auth user, you can use user metadata on the auth.user table
If this is for displaying user info for other users, you can have a public.profile table and then yes, make a new query
@Lucas G
To use custom claims with supabase you need to install these functions:
https://github.com/supabase-community/supabase-custom-claims
Victor Giron

1 year ago

This is great! i was needing something like this
🔥1
👍1
Stockton

1 year ago

What are the odds you give us a little tutorial @Lucas G 😉
@Stockton
What are the odds you give us a little tutorial @Lucas G 😉
Lucas G

1 year ago

Hmm I will look to make some time to do a small recording
🔥1
Stockton

1 year ago

@Lucas G Would be super sweet 🙏
Lucas G

1 year ago

@Stockton @Victor Giron https://app.screencast.com/wT5PmV5IOqOcg
🔥2
Hope that is helpful
Stockton

1 year ago

You're a rockstar 🤘

Can't wait to watch it!
🙏1
Lucas G

1 year ago

Let me know if anything is missing
Victor Giron

1 year ago

Amazing!!! I'll watch it right now
Stockton

1 year ago

This is great @Lucas G ! Thanks for the introduction to custom claims!
👍1
Stockton

1 year ago

@Lucas G if you modify the claim do you refresh the session client side? How did you get that part to work in Toddle?
Lucas G

10 months ago

You call /auth/v1/token?grant_type=refresh_token
Body: { "refresh_token" : "user-refresh-token-here" }
Sorry 😅 never replied. Posting it for any future references
Stockton

10 months ago

Thanks for the response 🙂
Janis

5 months ago

@Lucas G I stumbled upon your video and custom claims. I installed the claims but I'm getting the following security advisory. Is that something you've encountered as well?
And thanks for the great video btw 🙂
@Janis
@Lucas G I stumbled upon your video and custom claims. I installed the claims but I'm getting the following security advisory. Is that something you've encountered as well?
Lucas G

5 months ago

Yes, it’s due to how the functions are written. You can add a search path to them if you’d like
It’s not a huge deal on its own though
👍1
@Lucas G
Yes, it’s due to how the functions are written. You can add a search path to them if you’d like
Janis

5 months ago

Alright I see - thank you! Thanks to your video I finally got my user invite system working. Upon signing up they get the "admin" claim and invitees get their claim based on a helper table via buildship. Amazing 🙂
@Janis
Alright I see - thank you! Thanks to your video I finally got my user invite system working. Upon signing up they get the "admin" claim and invitees get their claim based on a helper table via buildship. Amazing 🙂
Lucas G

5 months ago

Just to clarify, admin as in admin in your app or claims_admin?
Lucas G

5 months ago

No one should ever get claims_admin true
@Lucas G
Just to clarify, admin as in admin in your app or claims_admin?
Janis

5 months ago

Admin in my app! I did not touch claims_admin like you suggested 🙂
Lucas G

5 months ago

Glad it helped
🔥1
Victor Giron

5 months ago

Hey @Lucas G I just installed this yestedray and had the same alerts, is it possible to solve them without affectiv the functionality? Those are making me nervous 😂
@Victor Giron
Hey @Lucas G I just installed this yestedray and had the same alerts, is it possible to solve them without affectiv the functionality? Those are making me nervous 😂
Lucas G

5 months ago

I get back tonight so I’ll post a longer answer however the gist of it is that there’s no path since it can run for any schema
Most people only use the default ‘public’ so it’s probably fine to update the functions accordingly
Lucas G

5 months ago

The errors are not a huge deal in my opinion as the security settings for the function mean that only someone with claims admin role can change them (or service role of course)
So as long as things are managed properly, it shouldn’t really matter
Janis

4 months ago

@Lucas G One more custom claims question: How do you solve the issue that the jwt is static and the claims are not added right away to the token? Like, I have an onboarding flow where I set the custom claim via buildship but the user currently has to log in and out for them to come into effect.
Lucas G

4 months ago

https://discord.com/channels/972416966683926538/1202338426209452092/1237442859389419636
@Lucas G
You call /auth/v1/token?grant_type=refresh_token
Body: { "refresh_token" : "user-refresh-token-here" }
Lucas G

4 months ago

@Janis
👍1
Janis

4 months ago

sorry one more: where do you get the refresh token from?
Lucas G

4 months ago

In your workflow when they sign up then it triggers your claims flow in build ship I’m assuming
When they sign up/login you get the refresh token along with the access token
Wait until the claims flow is finished then refresh the access token
👍1
yoelfdz

4 months ago

This is interesting, thanks for that video Lucas
👍1
Janis

4 months ago

Yes that part I understood! I’m just wondering where I should store the refresh_token in toddle so that I can easily access it 🤔 I cannot set an http secure cookie but I’m not sure if just using a variable is fine
Lucas G

4 months ago

The way I do it is on sign up when the user clicks on their confirmation link they are taken to an “activation” page
Where they get a quick welcome message
Something like Welcome, your account is being set up (with a small Lottie animation)
In the background the claims are being set and all the other things their account needs
Then they click on a take me to my dashboard type of button where the token is refreshed
That was my first setup as I wanted to welcome the user but I may change it later on to go straight to dashboard
Lucas G

4 months ago

In which case the flow would still be similar, the initial confirmation/login API call has all the info needed so I can handle the refresh at any time without having to store the refresh token
👍1
Which generally isn’t a safe thing to do (storing it locally I mean)
Lucas G

4 months ago

My background process takes less than a second so I could do it either way, but I wanted the lottie animation lol
👍1
@Lucas G
You call /auth/v1/token?grant_type=refresh_token
Body: { "refresh_token" : "user-refresh-token-here" }
Victor Giron

4 months ago

Hi Lucas, so to be sure that the role within app_metadata is always updated on the frontend, can I call this endpoint for instance everytime the page is loaded so this will update the token therefore update the app_metadata on the frontend?
Janis

4 months ago

@Victor Giron but where would you store the refresh token in this case?
@Janis
@Victor Giron but where would you store the refresh token in this case?
Victor Giron

4 months ago

I assume the token is the one stored as access_token in Toddle, so my question is if this refresh the token or if after refreshing I need to update the access_token in Toddle
@Victor Giron
Hi Lucas, so to be sure that the role within app_metadata is always updated on the frontend, can I call this endpoint for instance everytime the page is loaded so this will update the token therefore update the app_metadata on the frontend?
Lucas G

4 months ago

I don't think there's a need for that
Normally roles aren't something that change that often
It only needs to be done any time a user's claim/role changes
Lucas G

4 months ago

Refresh tokens should be stored server side. It isn't something that should be stored in things like local storage.
@Victor Giron
I assume the token is the one stored as access_token in Toddle, so my question is if this refresh the token or if after refreshing I need to update the access_token in Toddle
Lucas G

4 months ago

You refresh the access_token with the refresh_token
You get both in the API response when a user logs in

Supabase Many to Many Database Structure

Stockton

Victor Giron

Stockton

Victor Giron

Victor Giron

Stockton

Victor Giron

Stockton

Victor Giron

Lucas G

Lucas G

Lucas G

Lucas G

Stockton

Victor Giron

Tod

Lucas G

Lucas G

Lucas G

Victor Giron

Victor Giron

Stockton

Lucas G

Stockton

Lucas G

Stockton

Lucas G

Victor Giron

Stockton

Stockton

Lucas G

Stockton

Janis

Lucas G

Janis

Lucas G

Lucas G

Janis

Lucas G

Victor Giron

Lucas G

Lucas G

Janis

Lucas G

Lucas G

Janis

Lucas G

yoelfdz

Janis

Lucas G

Lucas G

Lucas G

Victor Giron

Janis

Victor Giron

Lucas G

Lucas G

Lucas G

Stop scrolling. Start building.

Chat with @team-toddle

Video call our CEO

Get in touch!

Product

Company

Resources

Community