Hello good folks. The purpose of this question is to get insight from the more experienced community.

I'm trying to build a B2B system with data isolation concerns. For now, I chose to go with a schema-per-tenant data partition strategy(PostgreSQL). All tenants will share a common set of tables attached to their schema. But, the problem with this approach is the schema managment. To change even a single column across all schemas when you have many tenants is a challenge.

I've tried an approach where I use drizzleORM to generate the "template" migration scripts. Then when a request comes in, I read the sql content from the file and replace the placeholder with the tenant's schema name. This works fine for creating new tenants. But the real problem now is global updates. I've not found a reliable way to do this so far.

It's a greenfield project, so I'm trying to avoid tech debt to the best of my ability. How would you go about building such a system?