I want to design a multi-tenant SAAS database with PostgreSQL and RLS. I want to be able to host all users and the tenants in the same database and isolate their data with RLS.
In my use case it makes sense for tenants to share a certain user data to avoid duplication of those data. The tenants will request a permission from users and these permissions are stored in a permissions table and these permissions are used to control the RLS.
However, I need tenants to have user data that's unique to their organization such as join date or other data. What I need to know is how I can design the database to hold all the organization specific data. I have thought of using JSONB to store this data like
user_data_for_tenant
id
user_id
tenant_id
data JSONB
this data is decided by the tenant from the front-end.
Is this okay? How better can I design the database to fit this use case?
I will be using Supabase for DB, Auth, Storage and other uses so the RLS can control access.
