Architecture Advice Needed – Multi-Tenant Business Platform

It sounds like you are potentially trying to rewrite your entire application. If you're looking at switching to a shared-tenancy model and possibly breaking into microservices, these are large changes, especially if you need to do this without affecting your existing customers. There are a huge number of non-technical constraints here – "what would be your priority roadmap" is something that depends a lot on your company's specific needs.

To put a little context on it, in my previous job my main project was designing and supporting a microservice platform with the goal of eventually migrating our monolith application to it while still keeping customers running and adding new features, and after six years the monolith application hadn't really been reduced at all.

I'd suggest actually hiring an architect to design this for you; they can give you advice based on what your company is actually doing and your actual code base. Stack Overflow is much more for specific programming questions. What sort of answer are you hoping to get from the broader community?

In broad strokes, it sounds like configuration and settings vary by tenant. Your first big shift is too make sure branding and data connections are all configurable. Next I would work on the infrastructure of the application so passing a tenant ID allows you to look up relevant configs.

It's almost like you need N+1 databases. That "+1" database acts more like a registry of tenants that provides basic configuration information — a tenant module, so to speak.

Once the non-user facing components are changed to fetch tenant configs, work on the user-facing components. This is where end user authentication presents some challenges. You will need some way to associate users with a tenant. A single sign on scenario would work, and I imagine you can associate tenants with email domain names; there's your link.

It feels like you need to separate end user authentication and tenant configuration into their own modules. Upon successful authentication, get the tenant configuration from the tenants module. Maybe store the tenant ID in a secure cookie marked HttpOnly so every request has access to that Id.

This will be a big structural shift. It's really hard to give meaningful advice for something like this unless you are deeply embedded in the codebase and organization. At that point, I'm your coworker, not some stranger on the Internet.

Definitely don't move to micro services. That just adds layers of complexity for no benefit if you don't need the ability to scale and deploy modules independently.

My advice would be to make it work with multiple tenants on a single DB and single ERP system.

Having a dynamic connection string is a pain, affects performance and doesn't really give you any more security.

Setting up the multiple DBs, although much easier in these days of containers is still a pain and maintaining them all is worse.

A single set of infrastructure allows that fully automated client onboarding you want to be able to scale.

You can break out duplicate stacks for big customers if required.

If you move to PostgreSQL, you can use django-tenants this works with schema-based tenants. So each table is replicated per tenant in a separate schema.

It uses the hostname to determine what the tenant is, and has some tools to migrate all schemas.

This thus means you have a single web server that serves all tenants, but depending on the hostname, it routes to a different schema, and works thus with one connection to the database.

It does that by injecting a set search_path into the query mechanism, that thus specifies what schemas to search for.

It will require some work to migrate all the data of the different databases in the PostgreSQL database. Especially the fact that migrating from a different database dialect always has a few caveats.

The advantage is that normally you then no longer have to maintain the tenants separately: you can run migrations on all tenants at the same time, and you thus have one single code base to maintain.