Access level data sharing between tenants

[suyashcyber94]

Description

We already have data-sharing between central and tenants but there is no mechanism using which a tenant can share data with other tenants based on access level.

Why this should be added

This is useful in the case when 2 businesses are using our application and they both are associated as partners as well and their data is shared among them. In my case, one business can onboard a client and that client's work is outsourced to the other company. In this case, organisation #2 wants to see the client's details, documents etc. I can also edit things and change status so that Organisation #1 can be notified and see the changes. This is more like assigned_to if we imagine it in a single database architecture, however, that becomes complicated in a multi-db arch.

I hope we can have a solution to this problem or at least if someone can suggest a workaround.

Partial Solution

I am thinking of assigning an organisation code to the tenant and having a table in the tenant shared_assets (id, org_code, model, model_id, access_level) and then whenever an asset or record is shared with another organisation on the basis of organisation code, an entry will be made in shared_assets. This table will be synced with central which can be accessed by all tenants to check if any other organisation is sharing any data with them.
Now, the problem persists in accessing the record from other tenants' DB, remember we want to restrict access only to shared assets.

Possible problems with my Partial Solution

The shared data should be in relation to my existing table somehow so that I might not need to make extra efforts explicitly to get the shared data as it will result in fetching clients in 2 different ways and then merging shared data with my dataset and then showing in a table view. It might be like Clients::with('shared_clients')->get(); resulting in a combined result.

Any views on this?