postgresql - Rails + Postgres: Multi-tenancy done right? -

i going build app using rails. uses multi-tenancy using apartment gem , postgresql. app have users, each of have account. implies each user has it's own postgressql schema; users table in default schema.

each user has own list of customers in own schema. customer same email (essentially same customer) can appear in multiple schemas. want customer able log in , see users he's associated with. can't put customers table in default/public schema because it's related other tables not in default schema.

what thought create link table between customers , users in public schema. table contain email of customer , id of user. issue don't understand how work rails. achieve customer.users.

so question is: how should approach problem?

i suggest differ between users (who log in, not part of tenant), , customers (which kept separately, , located in each tenant). users table (possibly accompanied other tables) can hold information assignment user schema/customer etc. not use foreign keys link user table tables in tenant, keep them separate.

in short, user table serves authenticate , authorize only.

update: question describes multi-tenancy approach using separate database schemas individual tenants. in setup up, not link users customers database foreign keys, , not query them together. authenticate against users, , assigned tenant(s). after switch tenant.

if want query both items (users , customers) in 1 run, not use separate schemas: 1 schema, create tenant table, , put foreign key other tables (customers etc.). in scenario without separate user table, , query (single) customer table.

update 2: answer query question:

you can query schemas in postgresql's meta data:

select schemaname pg_tables tablename = 'customer' 

which gives schemas customer table. using information can dynamically build union select:

select name schema1.customer union select name schema2.customer union  [...repeat schemas...] 

to query tables across schemas. use group by eliminate duplicates.