How to keep membership in sync in a multi tenant architecture with per tenant database?
I'm working on a project where we aim to have a separate database for each tenant. In our setup, there is a central database (and API) containing a "users" table that stores usernames and passwords for all users. Additionally, this central database includes a "tenants" table to track all tenants and a "tenants_users" table to associate users with specific tenants. Notably, a user can belong to multiple tenants.
My goal is to allow users to log in at auth.example.com and then choose the tenant they want to access. Each tenant has its own {tenant}.example.com domain and we manage sessions server-side with a samesite cookie at example.com to track the login state across tenant domains.
Furthermore, each tenant maintains a "members" table to store additional member information, with membership roles stored in this table for authorization management. Before a user is added to this table we utilize an invitation system that is managed by the central server.
Now, here comes the challenge: we need the "tenant_users" table to display a list of tenants to which the user belongs, facilitating easy switching between them. Simultaneously, we want to store the membership role in the "members" table of the tenant database. The issue arises when the admin of a tenant wants to remove a member; in that case we also have to remove the user from the "tenant_users" table in the central database. This seems cumbersome and susceptible to synchronization issues.
Additionally, we have a central admin where a superadmin can add/remove users to any tenant, requiring data synchronization from the central server to all tenant databases.
The central server offers a REST API with various endpoints enabling a tenant to add a member to the central "tenant_users" table on the central server. In this case the tenant server communicates directly with the central server. Currently, I'm implementing JWT with RS256, where the server retains the public key of each tenant. The tenant signs the data, which is then utilized to enact the necessary changes in the database.
For authorization of a request from a tenant, we perform a database query to ascertain the role of the logged-in user who initiated the request. The logged-in state is maintained on the server using Redis.
I'm uncertain if I'm overthinking this or if this is a genuine concern. Should I reconsider the architecture, or is there a better way to address these challenges? My main concern is around keeping the "user_tenants" and "members" data synchronized. I also imagine that using oauth2 for server-to-server communication is another option, however I'm not certain if the current approach with JWT and public/private keys is incorrect, mainly because I'm aware people are opinionated when it comes to JWT.
