The feature you are looking is called multi tenacy and it can be achieved in two ways:
TL;DR: In query prefix multi tenacy, each tenant have their own tables and data, which provides the security guarantees we need. On the other hand, such approach for multi tenancy may be too expensive, as each schema needs to be created, migrated, and versioned separately.
Therefore, some applications may prefer a cheaper mechanism for multi tenancy, by relying on foreign keys. The idea here is that most - if not all - resources in the system belong to a tenant. The tenant is typically an organisation or a user and all resources have an org_id (or user_id) foreign key pointing directly to it.
Multi tenancy with query prefixes
If you maintain separate db for each customer, you are going to have to do migration for each one separately, which gets unwieldy.
See: Ecto Hex Docs on Multi Tenacy with Query Prefixes
Multi tenancy with foreign keys
It’s better to go with the foreign key approach.
See: Ecto Hex Docs on Multi Tenacy with Foreign Keys
Just implement good authorisation rules, so the boundary is clear between tenants.
P.S. Checkout Plausible Analytics Git Repo, they have lots of customers, and foreign key multi tenacy.
