Setting up Multi-tenant Data Models

Sebastian Scholl (00:04)
Hey there. Sebastian, Product Manager here at 8base. Today we're going to be talking about multi-tenancy. Essentially, let's break down what we mean when we say single instance multi-tenancy. Really all it is at the end of the day is meaning that a single application or a single database is serving multiple customers.

Sebastian Scholl (00:22)
When we start to think of that word tenant in the abstract, a customer could be a user, it could be a company, it could be a user account centred around a specific project. There's many different ways based on the application that you're building the context of tenancy might change.

Sebastian Scholl (00:40)
However, when approaching the architecture of a single instance multi-tenant application in 8base, there's a few tricks that we can show you on how you use a combination of roles and data model to really enable these type of applications to both work as well as work securely and performant for your application.

Sebastian Scholl (01:01)
One of the early use cases that really inspired the vision of 8base was to make the development of single-instance multi-tenant SaaS applications significantly easier. However, let's just break that down really quickly and talk about what we're saying when we say all those words.

Sebastian Scholl (01:17)
When we say single-instance multi-tenant, all we really mean is that a single application or single database is able to serve multiple customers. That is done by making sure that the data is scoped to only a specific tenant. Tenant, customer, we can use these words a little bit interchangeably, but what we're really just talking about is the main table or the main record in our database to which all of that tenant's data is logically scoped.

Sebastian Scholl (01:49)
In 8base, this is super achievable, and it's really powerful tool to enable these types of applications because using data model, with the roles and permissions engine, we are pretty much able to create any type of multi-tenant data model structure.

Sebastian Scholl (02:04)
We're going to jump into 8base and look at a current application that I've set up or the back end for an application and talk about how by default, it already is a single instance multi-tenant database. However, really look through that use case of moving that from supporting multiple users to supporting multiple companies and then company-specific or tenants specific settings.

Sebastian Scholl (02:28)
Without further ado, let's jump in and see how this works. Okay, cool. We're going to start here in one of my favorite workspaces, my super tasks backend. Once again, start with a simple data model, simple application domain, and then abstract the concept from there so we all have a really solid understanding of what's going on.

Sebastian Scholl (02:47)
Inside of this application, it is very straightforward. We have a lists table and a task table, and there is a list can have many tasks that belong to it. There's a few fields on each table, but we can just forget about that for now.

Sebastian Scholl (03:03)
Let's talk about this first in the context of single instance single tenant. That means that this would be an application to only serve one user, which would be or one customer, which let's just say that's me. That means that for me I'm using this as my personal note-taking backend.

Sebastian Scholl (03:24)
That means I don't really have to worry about anything other than authentication because if I can authenticate, I'm the only customer. And so I should be able to see everything in the database because it's all my data.

Sebastian Scholl (03:34)
As soon as you change that to needing to support two users or two or more, then suddenly certain tasks belong to one user, certain tasks belong to another user lists, so on and so forth. We're probably going to be concerned about making sure that I don't see person A tasks and person B doesn't see my tasks or you get what I'm doing there.

Sebastian Scholl (03:57)
That's where we need to start then thinking about, hey, how do we perform this type of scoping to make sure that we are only permissioning certain data to be shown to the right user.

Sebastian Scholl (04:08)
I'm sure that most of the applications that you are designing, you're already thinking about in a single instance multi-tenant way, meaning that you have a single application which you have multiple users using.

Sebastian Scholl (04:19)
The first thing that we're going to do here is just look at the simplest way of setting up a single instance multi-tenant application using our to use backend, which we're first going to migrate it from being a single instance single-tenant application, which it currently is, and move it to being single instance multi-tenant where the tenant is a user.

Sebastian Scholl (04:41)
Here in the data model, we already have a user's table because that's every workspace gives you that. Essentially what we're going to do is we're going to first build a relationship between our tasks table, excuse me, our list table and our user's table.

Sebastian Scholl (05:00)
I'm going to drag and drop that over here and I'm going to say that the relationship here is that every list has an owner, and an owner can have many lists. We're not going to do it the other way around. Once again, you can pretty much create any type of data model you want.

Sebastian Scholl (05:15)
If yours needs to be different, play with relationships as is necessary. But right now we're going to say the user can have many lists and every list requires actually, we're going to leave that unchecked right now because I have some dummy data in here. I don't want to have to delete some of that data.

Sebastian Scholl (05:31)
Every list belongs to a user. That's what we're pretty much saying. And a user has many lists. I'm going to create that field. What I'm going to do, I'm going to go into my list table and I'm going to update the two lists that I have in here to say that Sam is the owner of this list and I'm going to leave this other list blank. Sam does not have... On the other list the other list has no owner.

Sebastian Scholl (05:57)
We've established a situation, if you think of it, kind of in a hierarchy where we're treating our user's table as the top of the hierarchy that is the tenant. Everything that is tenant-specific trickles down from there.

Sebastian Scholl (06:12)
We have the user's table, which has lists and lists of tasks. We actually know that a task belongs to a user, not because they created that record, even though that could be one way of doing it, but because that record trickles right back up to that tenant's table.

Sebastian Scholl (06:31)
Now that our data model is set up, we need to now reflect our roles on the API to accommodate that. I'm going to go here to the app services and I'm going to click to our user role where you create a user role. Here now we're going to be able to set those permissions.

Sebastian Scholl (06:52)
Things like creating records, we want our users to do that. We want them to be able to create lists and create tasks. However, we now want to make sure that they are only seeing or updating or deleting the tasks and lists that are relevant to them or that they own.

Sebastian Scholl (07:09)
It could be confusing. This would actually work by just staying the user's records because every record has a relationship to the user that created it. However, that's actually not going to work for us. The reason being is because we want to be specific about how that record is related to the tenant, not just the fact that a certain user created it.

Sebastian Scholl (07:33)
What we're first going to do is we're going to jump into our list table and here we're going to create a custom filter. When I open that filter, what I want to do here is say that, hey, a user can only see a list if they are the owner of that list. That means that if they send a query to the API and say, hey, show me all the lists, the API is only going to return the ones that they are the owner of.

Sebastian Scholl (07:59)
I can do that by saying that only allow a list to be read if this filter passes, this custom filter passes, the owner "is_self". That means that you are the owner of that list.

Sebastian Scholl (08:21)
If you're confused at all about what we're doing here, we have two other videos that really break down roles and permissions, as well as custom filters. I will link them in the video description so you can jump in there, get familiar, and then come right back to where we are.

Sebastian Scholl (08:35)
I'm going to copy that as well as save that and drop that right over here for the update permission as well. You can only update the list if you are the owner.

Sebastian Scholl (08:46)
Before I save it, one thing I just want to mention is that you can have multiple relationships on any record. These types of filters can be a lot more dynamic than this. You could say that one person is the owner, but you could also assign someone as a manager. All these types of configurations are totally feasible. And save that.

Sebastian Scholl (09:05)
Our lists table is properly set up with our user's table treating the user's table as the tenant record. We want to look at tasks. Once again, the way that we want to relate tasks to our tenant is through their relationship to the lists table.

Sebastian Scholl (09:27)
We only want a person to be able to read tasks or update tasks if it belongs to a list of which they are the owner. We're going to do the exact same thing with the custom filter but now on the task table. Go to the custom filter, and on the tasks, we're going to look for the list it belongs to.

Sebastian Scholl (09:49)
Then on that list, we're going to look for the owner, and then that owner we're going to make sure that it is the requesting user, "is_self": true. One thing I was throwing in here, "is_self": true is just an alias for essentially created by "id" "equals" and then a dynamic variable "_loggedInUserId" exact same thing, much more simply written as "is_self": true.

Sebastian Scholl (10:21)
We could say "is_self": false if that's relevant to your use case. I'm going to take that, copy it, save it, and add it to the task table over here. For the simple use case, we've literally accomplished it at this point.

Sebastian Scholl (10:41)
This means that now if you have let's imagine that we log into our task management front end. I log in as one of the users, I start hitting the API. It doesn't matter if there's a filter applied to that.

Sebastian Scholl (10:54)
If they say show us all the records or anything like that, these roles will be enforced and only return the data that's relevant to that authenticated user through the roles and permissions that we just set up.

Sebastian Scholl (11:05)
As long as, of course, the user is attributed the user role. If they have no role, they're going to have no permissions. They are not going to get anything back.

Sebastian Scholl (11:12)
Let's go to the next use case. As I'm sure if you're familiar with 8base or familiar with software, this is bread and butter for you. The next use case that we want to accommodate is, okay, I've been using the supertask backend as the single-tenant user and I realized it was super valuable that I wanted to give access to my friends.

Sebastian Scholl (11:37)
So then I made a single instant multi-tenant application to where now my friends can log in and do their tasks. But now one of my friends says, "Hey, this thing's so great. I want to bring it to my company. I want us to be able to, at my company level, collaborate on tasks and create lists. I want that tenant record to be my company, to which the company actually has many users and no longer is the user of the tenant."

Sebastian Scholl (12:04)
Let's now take that next step of creating the data model needed and then the roles and permissions that are required for that type of enablement. We go back to data, and now I'm going to create a new table.

Sebastian Scholl (12:20)
I'm going to create a table called, I'm going to call it Tenant for the sake of example, or Tenants. We could call it Company, Groups, whatever we want to call it projects. Doesn't matter. Tenants will work, though.

Sebastian Scholl (12:34)
We're just going to give a tenant a name because we can. I'm going to create my first tenant record in my database called Company or Big Co. Big Co is now using our tenant or task management application. I'll do another one called Small Co. Remember, we don't want Big Co to see Small Co's data that would be catastrophic for our business, our up and coming task management startup.

Sebastian Scholl (13:10)
What we want to be able to do here is make sure that we are securely permissioning or securely scoping all the data that belongs to Big Co or Small Co only to Big Co or Small Co.

Sebastian Scholl (13:22)
Let's go back to our schemer. The first thing that we're going to do is build a relationship between the tenant and the user's table. Remember, a tenant is like the company, so a tenant is going to have many users, and we also want to make sure that the resources within that tenant are shared by the tenant.

Sebastian Scholl (13:39)
That means that the users at the company can see the list that people are working on and collaborate on the tasks and whatnot. No longer does our paradigm of a user has their own lists really work. Now, of course, we could create an application where users are going to have their own list and the company list.

Sebastian Scholl (13:55)
This is all possible. But for the sake of simple example, we're just going to say that now the company has the list, and only if you work at the company can you see those lists and work on tasks, stuff like that. Here we're going to say that a company or a tenant has many users. That's totally fine. And a user belongs to a tenant.

Sebastian Scholl (14:18)
Once again, we can make that mandatory if we want to. I'm just not going to do it for now. Once again, if we needed to say that a user can belong to multiple tenants, that's possible as well. In this case, we're just going to say one account, one tenant for a user. First we're going to create that relationship.

Sebastian Scholl (14:34)
I'm going to go back to my lists here and I'm going to say that I'm a delete this owner relationship. Actually, I'm gonna leave it. I've changed my mind. All I'm going to do now is I'm going to say that the list belongs to a tenant and an owner and a tenant can have multiple lists. Perfect.

Sebastian Scholl (14:59)
A tenant has list. We have this tenant record. What we want to do is we want to make sure that there's two ways that you can read a list. We're actually going to dice up here.

Sebastian Scholl (15:15)
Well, the first way that you can read a list is either if you are the owner or if you belong to the tenant, which that list belongs to. Then the only way you're going to be able to update a list or a task that belongs to that list is if you're the owner. If you're part of the company, you can read all the lists and the tasks, but only if you're the owner can you actually perform updates. Let's establish that now.

Sebastian Scholl (15:45)
The first thing I'm going to do is I'm going to go in go back to my list here. Even though we're not running these queries, it's just for the sake of example, it's healthy. For the tenant, we're going to say that this list, Sam's list belongs to Big Co. Sam is the owner and the list belongs to Big Co for which Sam works.

Sebastian Scholl (16:06)
Let's go back to our role. We'll use the user role. We'll just keep working with the user role here. Okay. When it comes to our lists, once again, we're saying that as a user, you can create the list, but which ones can you read?

Sebastian Scholl (16:24)
First off, we can say that, well, if you're the owner of the list, you can read it. That makes perfect sense. But remember, we also want to now add that, well, maybe you're not the owner, but maybe you work for the same company as the owner and that list belongs to that company.

Sebastian Scholl (16:43)
What we're going to do here is we're going to open up new brackets and do an "AND" excuse me, an "OR" so paste that right back in there. Got to get a little better formatter in here. There we go.

Sebastian Scholl (17:01)
The first condition that would allow you to read the list is whether you're the owner. Then the second one is where now we're going to look through that tenant relationship back to the user's table. Remember, we're tying this back to the authenticated user.

Sebastian Scholl (17:15)
We're going to say here. Okay, well, does this list belong to a tenant that has users where... One second, let me try that again. I think I may have gotten the wrong relationship type on there. I'm just going to save that really quickly. Go back in here. Allow multiple users per tenant. I had it backwards. Now it's set up.

Sebastian Scholl (17:57)
Now multiple users per tenant, not the other way around. Then I'm going to go back to app services, go to my user, go to my list, custom filter, and I'm going to go in there. Now when I add that back, I'm only allowing lists to be read that belong to a tenant that has users of which one of the users "is_self".

Sebastian Scholl (18:28)
Essentially saying, if you belong to the tenant to which the list belongs, then you can read this list. Once again, like last time, I'm going to copy this and save it. Now our lists are good. Let's go over that update.

Sebastian Scholl (18:43)
Here what I'm going to do is I'm going to copy it over. But like we said last time, we don't want you to be able to update the list. Only if you're the owner can you update the list.

Sebastian Scholl (18:55)
However, let's think of the example of what if you transferred ownership to another tenant. Maybe in this scenario, you may have transferred a list, there's like a feature where you can transfer the list to another company.

Sebastian Scholl (19:10)
In that scenario, if you're still the owner, you're technically seeing data that you shouldn't be seen. What we're going to do here is instead of saying "OR" we're going to say "AND". In this scenario, we're pretty much saying, hey, you can only update the list if you are the owner and you belong to the tenants that the list belongs to. So a double validation there.

Sebastian Scholl (19:34)
I'm going to copy this, save it, and we're good on the list. Let's do the same thing for the tasks. Here we're going to say, hey, you can read the tasks if the task belongs to a list that belongs to a tenant which has users of which some or any of them we can think of that word in many contexts is you.

Sebastian Scholl (20:10)
Once again, we're going to do the exact same thing for the task custom filter or for update. However, here we're going to say that if the task belongs to a list of which you are a tenant, actually, we just want to make sure that you are the tenant owner. It's actually the same one as back here. Let me steal this one here again.

Sebastian Scholl (20:46)
Here for the task, once again, we want to make sure that you own the list if you're adding tasks to the list. Or we could just say that you belong to the tenant of which the list belongs to. Once again, this is application-specific, but you get the concept.

Sebastian Scholl (21:00)
Here what we're just going to say is that you have to belong to the tenant, the list has to belong to the tenant of which you belong to, and then you can collaborate on the list. That makes sense. I'm going to save that. I think it prettys it up once we click... Yes, there we go.

Sebastian Scholl (21:27)
Once again, at this point, we've accomplished this next level of our application to now, our data model is set up to only allow us to see certain records or perform certain actions on those records as long as the user that's making that request belongs to a tenant record of which those records also belong to.

Sebastian Scholl (21:47)
That's a lot of the word record. I apologize. However, we're going to take it up one last level for this video. What we are assuming at this point is the permissions that are set per tenant are all the same, meaning that okay, well, if you can't or every tenant doesn't want the non-owner to be able to update the list, every tenant wants all their users to be able to read all their lists.

Sebastian Scholl (22:22)
However, as you start to build out more sophisticated applications or have more demanding customers, those things start to change and you have to be able to accommodate those types of changes. Once again, this is totally possible in a pretty straightforward way. What we're going to now do is come up with tenant-level permissions.

Sebastian Scholl (22:46)
Here are how we're going to do that. I'm going to create a new table here called TenantSettings. On this table, I'm going to say non-owners can update lists. Here I'm going to say it's a switch. Excuse me. And true/false. By default, let's just leave it as false. Say it's mandatory.

Sebastian Scholl (23:29)
Non-list owners can update tasks and do the exact same thing here. And false. Let's see. False again, by default, mandatory.

Sebastian Scholl (23:48)
We could go way down the rabbit hole with this, but this will satisfy what we're going to demonstrate here. Essentially what we're saying is that, okay, we're going to create two types of tenant-level permissions.

Sebastian Scholl (24:01)
Pretty much as the customer administrator, excuse me. They will be able to permission whether or not the users in their tenant can or can't update tasks or lists, depending on whether or not they are the owner of the list.

Sebastian Scholl (24:22)
Here what we're going to do is we're going to create two different scenarios. First off is we're going to say that for Big Co or actually for Big Co, we're going to leave it false, false. Oh, sorry, I forgot to do one thing here.

Sebastian Scholl (24:36)
We have to make the relationship between the tenant table and the tenant settings. Here what we're doing is we're going to do a one to one relationship and say that it is mandatory.

Sebastian Scholl (24:47)
The tenant settings needs to belong to a tenant. That's because we only want one settings configuration per tenant. We don't want a tenant with multiple setting configurations which contradict one another.

Sebastian Scholl (25:03)
Here we're going to say that this is the tenant and the related field name is tenant settings. Now when I go back here and let me update this guy.

Sebastian Scholl (25:14)
When it comes to Big Co, Big Co isn't letting their non-owners update anything, you have to be the owner to update it. Even though you can see it if you're not owner because that's how we set up our application. Submit those changes. We're going to add another one for Small Co where they let their users do everything.

Sebastian Scholl (25:34)
We can see that these two tenants have these different settings, which are these values saved in the database. Now we want to tie those back into our roles and permissions engine to make sure that depending on not only the users that send in the query, but the tenant that they are sending that query on behalf of, we can think of it that way that these permissions are taken into consideration.

Sebastian Scholl (25:58)
I'm going to go back to my app services and go to my user role and I'm going to go to my list update and custom filter. What we are going to check is couple things. The first one is we want to still maintain that if you are an owner, you can do the update. We don't want to touch that one.

Sebastian Scholl (26:28)
But now here we have to check two things. We have to check on the second one, that not only do you belong to the tenant, but also that as a non-owner, if you are a non-owner, the settings for that tenant need to be enabled for you to perform an update.

Sebastian Scholl (26:54)
What we are going to do is we have that "AND" statement there. We're going to do a little bit extra nesting because remember, we want to touch it. We're going to say an "OR" statement here and then put that whole thing right in there. That's scenario number one which we already worked through.

Sebastian Scholl (27:18)
We're going to do scenario number two, which is two things have to check out for this one, because essentially in this case you're not the owner. First off is we're going to check that you belong to the tenant, which we have that one right here.

Sebastian Scholl (27:41)
We'll drop that in there. The next one is we want to check that the tenant's permissions allow you as a non-owner to perform that update on the list. If the list belongs to a tenant which has tenant settings of which non-owners can update lists, and if that "equals": true, you're good to go. If it "equals": false, you're not good to go.

Sebastian Scholl (28:13)
Copy that. Save that. Boom. We just knocked that one out. We're new same thing for tasks. Custom filter for tasks. Once again we're going to copy this whole thing here. Actually we don't have to do the double predicate here because we're just making sure that you belong to the first one or that the first one is valid which is you are a tenant, you are a user of the tenant. It's a lot of wordplay.

Sebastian Scholl (28:46)
We're just going to do the second check now. Does that task belong to a list which belongs to a tenant which has tenant settings of which non list owners can update the tasks? If that "equals": true, you're good to go. Save that.

Sebastian Scholl (29:04)
Once again, we've updated our permissions accordingly to now accommodate our final scenario which is not only do you belong to a tenant but that tenant has tenant-level settings which the roles and permissions engine is taking into account when scoping the data it's returning or when permission in the operation that you are sending to the API.

Sebastian Scholl (29:26)
I hope you found this video valuable. Interesting thought-provoking. There's a lot of different ways that you can take this. However, what it does is it gives you a little bit of a foundation on how you can start to think about roles and permissions and data model really working together to create those more, call it sophisticated scenarios for the applications that you're building.

Sebastian Scholl (29:48)
Thank you again for watching. If you have any questions please leave comments, reach out in the 8base community. Looking forward to hearing from you and looking forward also seeing you in future videos. Take care.

‍