Rails + Postgres: Multi-tenancy done right?

Question

I am going to build an app using Rails. It uses multi-tenancy using the Apartment gem and PostgreSQL. The app will have users, each of which have an account. This implies that each user has it's own PostgresSQL schema; the users table is in the default schema.

Each user has his own list of customers in his own schema. A customer with the same email (essentially the same customer) can appear in multiple schemas. I want a customer to be able to log in and see all the users he's associated with. I can't put the customers table in the default/public schema because it's related to other tables that are not in the default schema.

What I thought I would do is create a link table between customers and users in the public schema. This table would contain the email of the customer and the id of the user. My issue with that is that I don't understand how well this would work with Rails. What I would like to achieve is something like customer.users.

So the question is: How should I approach this problem?

Answer 1

I created this lib to help us solve this issue

https://github.com/Dandush03/pg_rls

Currently the most famous implementation are Apartment Gem (from Influitive), ActiveRecordMultiTenant (from Citus) and the rails 6.1 way, DB sharding

there are many issues with the Apartment and rails 6.1 approach when dealing with a huge amount of schemas/databases mainly when you most run a scale migration or when you have to change default values on a table, this is because you would need to run this migration on each tenant, and that very cost-efficient. and Citus's approach gets expensive in the long run. 

thankfully PostgreSQL came with a great solution on pg V-9 but it had some performance issue that was solved on pg V-10. This approach allows you to keep specific tables behind a 'special id' which can be later on partitioned with pg new tools

my approach is mainly focused on PG and how they recommend you to implement RLS, and most of my queries are executed with SQL statements which help us a bit when dealing with performance issues when running the migration. I tried to mix the best of rails with the best of pg functions.

what is even better on my approach is that when you start running your special pg search function, there will be no downfall because all data is secure and under the same DB. also you will gain the ability to log as a superuser and get your statistics.

I can keep going but I think I make my point clear, if you'd like I encourage you to check out the gem (prob still some bugs to handle like right now it only handle setting the Tenant from subdomain) but it does make my life easier on my ongoing projects. and if I get some more supports (like) I would keep on maintaining it and upgrading it to be a more generic tool