I have developed an internal tool that addresses a serious problem in one of the markets in my area. There are 800 SMB client organizations in the market that potentially could be interested in using the tool.
I am thinking that ideally all of the closed clients would get their own Retool workspace and they all operate only within their workspace. This makes sure there is no possibility of data leaks, and managing certain customizations can be done client by client basis.
My question is: what would you suggest as the model of operation from the point of view of source control and deployments? Right now I am thinking that in the ideal scenario, I would be able to develop the app in only one place and then update all the client organization workspaces when we've passed the QA. Currently, I can only think of exporting some Toolscript from the development workspace and one by one apply the updates on those workspaces. Is there a better and more modern way to accomplish what I am trying to accomplish?
Thank you guys, your product kicks some serious ass.
I have thought about this a lot. There are also some other posts discussing this around the forum.
I think the only way to do this with more than a few clients is to have a single database in a Multi-Tenant "mode". If you try and keep a single code source and copy them over to each client's own retool instance - you will quickly start hating life!
Each client has one or more Retool logins (see retool Portals).
You have a table that links their Retool ID to their Customer ID.
3a. Your queries are set up that only that user can see his own
3b. Use the database's Row Level Security to enforce which user can see what records.
If each customer will have a large amount of data, you can also consider database sharding to prevent the database form getting too large and slow.
Doing this can be moderate or very complex depending on your application's and your client's specific needs.
If this all just went over your head (as it did mine at first!) spend some time googling database multi-tenant, RLS, sharding and you will learn a lot about strategies and their pro and cons)
Many of us in the forum would love to hear how it goes and your challenges and wins as you tackle this!
Thank you for your reply. Your suggestion makes a lot of sense. I must admit I puked a little because of the thought of upgrading each separate customer tenant one by one.
I’ll look into your suggestion and try to create a POC with multi-tenant database design and access controls. At the very least this is a valuable exercise skill-wise.
If someone has some other ideas or points of view, please let it be known in this thread!
I researched some options for RLS and while it probably could be done, I am having a competence issue making sure the Retool & Supabase authentication is synced. If I understood correctly, an intricate SSO setup could help here, however, this is only available in Enterprise plan.
Thus far I am opting for managing ”organizations” and ”users” tables in Supabase and creating new organization-linked accounts there when needed. Obviously, all data in other tables are linked to an organization id.
I was hoping to get the full benefits of RLS, however, my current solution seems to work fairly well. I have some database functions in Supabase where I am making sure user email and their ”retool_sid” is linked to an organization and only then return the correct rows. This means that the JOINs and WHEREs are encapsulated within the PostgreSQL functions and I only query from Retool using those functions.
I am thinking this is a fairly secure setup as long as I have the functions set up correctly and I am only querying Supabase using the functions. At the very least this is far better than crafting the queries manually in Retool each time and trying to remember all WHERE clause checks.
Any opinions on such an approach? Each organization will have max 5k rows in the tables in total, so I am not expecting huge scalability issues at least yet.