PricingDocsAcademy
Bluesky ...
Wed, Dec 11, 10:42 PM
Log inSign up
Log inSign up
All topics

Supabase Many to Many Database Structure

  • Stockton

    10 months 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

    10 months 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

    10 months 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

    10 months 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

    10 months 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

    10 months 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

    10 months ago

    You mean for creating a new workspace/user relation?

  • Stockton

    10 months ago

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

  • Victor Giron

    10 months 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

    10 months 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

    10 months ago

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

  • Lucas G

    10 months 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

    10 months 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

    10 months 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

    10 months ago

    I need to check this out

  • Tod

    10 months ago

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

  • Lucas G

    10 months 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

    10 months ago

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

  • Lucas G

    10 months 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

    10 months 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

    10 months ago

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

  • Stockton

    10 months 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

    10 months ago

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

  • Stockton

    10 months ago

    @Lucas G Would be super sweet 🙏

  • Lucas G

    10 months ago

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

  • Stockton

    10 months ago

    You're a rockstar 🤘

    Can't wait to watch it!
    🙏1

  • Lucas G

    10 months ago

    Let me know if anything is missing

  • Victor Giron

    10 months ago

    Amazing!!! I'll watch it right now

  • Stockton

    9 months ago

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

  • Stockton

    9 months 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

    7 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

    7 months ago

    Thanks for the response 🙂
  • /attachments/1202338426209452092/1296019066808438804/image.png

    Janis

    1 month 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?
    image.png
  • 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

    1 month 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

    1 month 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

    1 month ago

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

  • Lucas G

    1 month 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

    1 month ago

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

  • Lucas G

    1 month ago

    Glad it helped
    🔥1

  • Victor Giron

    1 month 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

    1 month 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

    1 month 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

    1 month 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

    1 month ago

    Supabase Many to Many Database Structure
  • @Lucas G
    You call /auth/v1/token?grant_type=refresh_token
    Body: { "refresh_token" : "user-refresh-token-here" }

    Lucas G

    1 month ago

    @Janis
    👍1

  • Janis

    1 month ago

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

  • Lucas G

    1 month 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

    1 month ago

    This is interesting, thanks for that video Lucas
    👍1

  • Janis

    1 month 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

    1 month 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

    1 month 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

    1 month 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

    29 days 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

    29 days 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

    29 days 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

    28 days 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

    28 days 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

    27 days ago

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

Stop scrolling. Start building.

toddle is a visual web app builder that rivals custom code — but accessible to your entire team!

Try toddle — it's free!
The two toddle Co-Founders Andreas and Kasper

Chat with @team-toddle

CEO and Co-Founder of toddle Andreas Møller

Video call our CEO

Hello, toddle!

Get in touch!

Product

PricingPackagesT&CsPrivacy Policy

Company

Press kitContact
Become partnerRefer and earn

Resources

DocumentationAcademyIntegrationsBlogFounder storiesHire experts

Community

DiscordLinkedInXYouTube

© Copyright 2024 toddle. All rights reserved.

toddle logo

Built in toddle