Multitenancy shared vs seperate schema

I never used Triplex, so I can’t say anything on that, but Ecto has a few guides on handling Multi tenancy:

• Multi tenancy with query prefixes — Ecto v3.14.0
• Multi tenancy with foreign keys — Ecto v3.14.0

The company I work at uses FK on all schemas similar to the second guide of Ecto (shared schema). However we never implemented the prepare_query/3 to add the tenant id to all queries of a repo, since we didn’t like the “magic” of it. Instead we just pass the tenant struct to each function that controls anything related to a tenant (e.g. list_users(%Tenant{id: tenant_id}), do: %User{} |> where([u], u.tenant_id == ^tenant_id) |> Repo.all()). It is a bit cumbersome but does provide flexibility and is a bit more explicit on what’s happening. It just comes down to personal taste I guess.

hi there, not sure if:

• you went through it already.
• you found/will find it relevant.

but here’s, kind of related - and interesting IMO - exchange: Dynamic database creation for SaaS product platform : Need help

Hello .. thank you .. we did a small prototype based on the informations shared from that conversation

I didn’t realize Ecto had guides for this! That’s awesome, I read through the Triplex code this morning and frankly I don’t think there is a lot going on there that you can’t just do with Ecto.
Initially, I leaned heavily on the query prefixes approach because this is a large codebase and scoping and adding an organization ID to every record is going to be a headache. However, that “magic” you mention with prepare_query may be a good way to find/prevent unscoped queries.

I’ve been looking into this a bit more I think the multi-schema approach may work, however the major blocker I’m facing is how to move existing data into the new schemas? At the moment the app already has two customers that I need to somehow move into their respective schemas.
I’ve been looking at just doing this with SQL (INSERT into prefix1.customers select * from public.customer where: customer.location = "1234") or using Ecto.put_meta/2 to update the prefix and then insert it deleting the original record afterwards.
However; with both approaches you quickly run into constraint errors which wouldn’t be a problem for a few associations but this is a big legacy app it’s kind of turtles all the way down. Do I just need to bite the bullet and write all these as some kind of complex migration script or is there a better way?