Multi-tenant DB and privacy rules

Need help Database
1

Hi Bubblers! I started to build a multitenant SaaS and I’m trying to figure out the best way to structure my database and privacy rules:

Requirements:

  • One user can create multiple companies.
  • The user can invite people to each one the the companies
  • An user can belong to one or multiple companies
  • All the data will be filtered (securely) by the current company the user started a session on, this means every data collection will have a relation column “company”
  • The same user can have a different role in every company he belongs to (Admin in Company A, Editor in Company B)

The way I’m trying to manage this is having a table for the relation company<>User<>Role

My question is how can I load the current company and role to the user session so the user can only see information from that company only and with the proper role permissions securely?

Any help or example is appreciated.

image
image760×610 46.1 KB

2

I add the company and role to user on login (or when switching companies). So then you have 2 fields on user: active_company and active_role (or even active permissions with a list of permission strings if needed).

That way you can easily do all that whats needed for privacy rules.

4 Likes
3

This is a really interesting approach. What’s the best workflow or action to load that information on the user when the user signs up or log in?

1 Like
4

When they select a company or if there is only one company.

With privacy rule a user can always see all the companies and roles associated. But to get the data associated with those company or role you can use the active company/role field.

1 Like