I’ve never built multi-tenant apps, but could an accounts table be located in the public schema? You need to know somehow which accounts exist, who is paid/free/blocked/etc. You could put there the schema name associated with the account. Just an idea
I’m also working with schema-based multi-tenancy in a new project. I don’t know if it helps, but this is my checklist so far:
1- Triplex to do the “heavy lifting”
2- “Tenant specific” routes scoped with the tenant name (slug)
3 - All tenant-related data is inside the tenant’s schema; the rest is considered “global” scoped and goes inside the public schema (platform configuration, views, etc)
4- Users access only one tenant “area” (~/tenant-name-slug/resource) at a time
5- We use the :prefix option in our queries, to get tenant-related data
I don’t know exactly what you are building, but I guess you could still store tenant-specific information in the public schema - depending on what you consider part of your global scope. There’s certainly no right answers.
Also, take into consideration that if you need to implement something like search or report, there are pros and cons of both approaches. For instance: queries may be faster inside the tenant’s scope because the schema will have fewer rows in total to be queried against; but if you need to collect global information about all tenants it’ll be more expensive since you’ll have to query all schemas at once.
There are a lot of approaches around these scenarios and all depends on what you want to achieve. In the end it depends on how you are separating the scopes of your application.
We have a multitenant app that uses schemas, here are few basic points.
Triplex is a low level library that abstracts away few things that you will need and gets out of the way otherwise. It will help with migrations, prefix validation etc.
Similar to what others mentioned we have global stuff in public schema and a schema per tenant, these tenants are managed on the global level - public schema has a table with tenants and their prefixes.
We use plugs in tenant space to load and validate current prefix (schema) as well as to ensure the user only uses their own schema.
Most of the tenant context functions get an extra parameter for prefix which goes to repo calls (Ecto basically supports it out of the box).
We also have global users in public schema that with enough permissions can access any tenant.
All of that seems to work nicely, one thing you might want to know - it’s good if you have few big tenants, but not too many. After about 100 of them (somewhere between 100 and 900) you might experience problems running migrations.
Using either Triplex or some other library is it possible to achieve MultiTenancy without using separate schema or separate database for each tenant? i.e. just one Customer table whose foreign_key is in every child table and all queries are scoped to that tenant only? This is something easily implemented in Ruby gem acts_as_tenant