Multi-tenancy

Source: g2labs-in/billuu-docs@9f1dcd7 · content/architecture/multi-tenancy.md · updated 2026-09-16

The problem this page solves

One Node process serves every restaurant on the platform. There is no server per customer and no database per customer. A request arrives with no idea which customer sent it, and the backend has to decide before the first query runs. Get that wrong and one restaurant reads another restaurant’s orders.

Read this page before you touch a controller, a service, or a model. Every database call in the backend passes through the rules below.

One database, two kinds of schema

Production is a single PostgreSQL database. It holds two kinds of schema.

Kind	Schema name	Holds	Declared as
Control plane	public	Business, User, TenantRegistry, Organization, Subscription, Plan, OutletUser, AuditLog	CONTROL_MODELS in brewlabs-billu-backend/src/utils/constants.js
Tenant	tenant_<businessId>	Orders, products, outlets, inventory, payments, roles, permissions, and the rest of the business data	TENANT_MODELS in the same file

src/utils/constants.js names the two constants. CONTROL_PLANE is the string 'control_plane', and it is the only tenant id allowed to touch public. PUBLIC_SCHEMA is 'public', and TENANT_SCHEMA_PREFIX is 'tenant_'.

Both lists are frozen at module load, so no code can add a model to a schema at runtime.

Object.freeze(module.exports.CONTROL_MODELS);
Object.freeze(module.exports.TENANT_MODELS);

The split is deliberate. OutletUser stays in public because it holds a foreign key to users. Role, Permission, and RolePermission moved the other way, into each tenant schema, so every tenant owns its own role table and its own permission rows.

The backend uses one Sequelize instance for both kinds of schema. src/config/control_plane_db.js exports controlPlaneSequelize as an alias of the same object. The comment on that alias gives the reason. Atomic onboarding needs cross-schema work inside one transaction.

How a tenant schema is named

The tenant id is a UUID. The schema name is the prefix plus that UUID.

const schemaName = `${TENANT_SCHEMA_PREFIX}${tenantId}`;

Two layers build it, and both agree.

Layer	File	Expression
Data access	src/core/database/databaseExecutor.js	PUBLIC_SCHEMA for the control plane, otherwise the prefix plus tenantId
Request context	src/core/tenant/unifiedTenantMiddleware.js	The prefix plus organizationId

On every provisioning path the schema is named from the business id. src/services/onboardingService.js creates the business and its organization row from one UUID, so organizationId and businessId hold the same value for an onboarded tenant.

const businessId = uuidv4();
const tenantId = businessId;
const schemaName = `tenant_${businessId}`;

src/services/franchiseService.js does the same when it creates a sub-franchise, and says so in a comment.

const newBusinessId = uuidv4();
const newOrgId = newBusinessId; // Use same ID for business and organization

Not every path that creates an Organization pairs the two ids. src/services/organizationService.js creates one with id: uuidv4() and a separate businessId field, and src/modules/organizations/services/organizationService.js takes businessId as an argument and derives the schema name from it. The middleware resolves the tenant id as organizationId || businessId, so read the creating path before you assume the two ids match for a given tenant.

The row in public.tenant_registries records the result. TenantRegistry.schemaName maps to schema_name, which is unique and allowNull: false, and the status setter forces every status to uppercase so 'active' and 'ACTIVE' cannot both exist. src/control_plane_models/tenantRegistryModel.js defines both.

src/utils/schemaEnforcement.js refuses to work with a name that does not match. enforceSchema(schemaName, tenantId) throws SCHEMA MISMATCH when schemaName is not exactly tenant_ plus the tenant id, and throws SECURITY VIOLATION when a tenant id other than control_plane asks for public.

Which model lives where

Model binding happens through Sequelize’s .schema() call. model.schema(schemaName) returns a copy of the model whose table name carries the schema, so every query Sequelize builds names the schema in the SQL. No connection state is involved.

Two code paths do the binding.

src/architecture/modelFactory.js marks every model in TENANT_MODELS with _requiresSchemaBinding = true and wraps each method named in BLOCKED_OPERATIONS, which includes findAll, findOne, create, update, and destroy, in a guard. If a guarded method runs while the model is on public and was never bound, it throws:

SCHEMA LEAKAGE PREVENTED: Tenant model '<name>' attempted operation "<op>" on the "public" schema.

src/architecture/tenantModelBinder.js reads that flag. bindTenantModels(schemaName) binds the flagged models with model.schema(schemaName) and passes the rest through untouched. ModelFactory.getModels(schemaName) calls it and caches the result per schema.

src/core/database/databaseExecutor.js does its own binding in getTenantModels(tenantId). It walks sequelize.models and sends every model in CONTROL_MODELS to public, plus the User model, which needs to stay reachable from tenant contexts for cross-schema associations. Everything else binds to the tenant schema.

TENANT_MODELS is not consulted in that loop. It is the declared inventory, used by src/architecture/tenantModelLoader.js to bind and verify a tenant schema, and by src/utils/schemaValidator.js to check that a schema has the tables it should.

Associations are applied once per schema

Sequelize needs the relationships declared on each bound model set. If the same association is declared twice, Sequelize throws on the duplicate alias, and if a tenant’s association points at a model still bound to public, an include reads the wrong schema.

src/architecture/centralAssociations.js is the single registry that prevents both. applyAssociations(models, schemaName) declares every relationship for one schema-bound model set. src/core/database/databaseExecutor.js calls it the first time it builds models for a schema and then sets __associationsApplied on the cache entry, so a warm cache never pays for it again. src/architecture/tenantModelLoader.js calls the same function during schema initialization.

hasAssociation(model, alias, schemaName) decides whether an association can be reused. It returns true for a shared public model whose association was declared on public. For a tenant schema it checks the target model’s schema and, for a belongsToMany, the through model’s schema too, because a join table left in public would read the wrong rows. A mismatch forces the association to be declared again on the tenant-bound model.

How a request gets a tenant

src/architecture/databaseMiddlewareChain.js mounts every route group with a chain. Tenant routes are /api/tenant, /api/inventory, /api/reports, and the SaaS routes. They run this chain in order:

1. requestTimingMiddleware
2. requestTimeoutMiddleware(30000)
3. standardResponse
4. UnifiedAuthMiddleware.authenticate
5. UnifiedTenantMiddleware.setupTenantContext
6. modelsSafetyLayer

setupTenantContext in src/core/tenant/unifiedTenantMiddleware.js is where the tenant is decided. It runs six steps in one pass.

1. Resolve the tenant id from the user.
2. Resolve the organization, the outlet, and the subscription, each through a process-level cache with a 5 minute TTL.
3. Copy the results onto the request as req.businessId, req.organizationId, req.tenantId, req.outletId, and req.schemaName.
4. Validate the schema with databaseExecutor.validateSchemaHealth.
5. Inject the bound models into req.models through ModelFactory.getModels(schemaName).
6. Install req.dbContext with executeInTenant and readInTenant closures that already carry the tenant and outlet.

Public routes mount a shorter chain with no tenant setup. /api/admin uses the adminChain, which adds UnifiedAuthMiddleware.adminOnly and skips tenant setup. /api/auth, /api/super-admin, /api/sms, /api/user, /api/upload, /api/menu, /api/qr, /api/sessions, /api/customer, and /api/onboarding use publicChain, which reaches the database only through databaseExecutor.readInPublic and executeInPublic.

Tenant resolution from the request

The tenant id comes from the authenticated user, never from a client header.

const tenantId = req.user.organizationId || req.user.businessId;

req.user is set by UnifiedAuthMiddleware.authenticate in src/core/auth/unifiedAuthMiddleware.js, which verifies the JWT and loads the user row. When neither id is present, setupTenantContext stops the request with a 403 and the message Tenant context required. UnifiedAuthMiddleware.tenantOnly returns the same 403 for routes that opt into it.

So a caller cannot choose a tenant by sending a header. Only the token decides, and the token belongs to one user in one organization. The security test src/tests/security/tenantIsolationSecurity.test.js locks this in. The case named Should prevent token reuse across tenants sends tenant 1’s token with tenant 2’s X-Tenant-ID header and expects a 401.

The outlet is different, because a user can legitimately work across several. x-outlet-id or req.body.outletId selects one, but only when the role allows switching. UnifiedAuthMiddleware sets req.canSwitchOutlets from MULTI_OUTLET_ROLES, and resolveOutletContext falls back to the user’s assigned outlet when that flag is false.

The executor is the only door

Handlers do not call Sequelize directly. They call the closures that setupTenantContext put on the request, and those land in src/core/database/databaseExecutor.js. src/services/databaseExecutor.js is a deprecated re-export of the same object, kept for old imports. There is no src/utils/databaseExecutor.js.

Method	Transaction	Use
executeWithTenant(tenantId, operation, options)	yes	Writes
readWithTenant(tenantId, operation, options)	no	Reads
readWithCache(tenantId, key, operation, options)	no	Reads through Redis
executeInPublic(operation, options)	yes	Control plane writes
readInPublic(operation)	no	Control plane reads

req.dbContext.executeInTenant sends a GET to readInTenant instead of executeWithTenant unless the caller passes forceTransaction: true. That is why most reads never open a transaction.

Every tenant call does three things before the operation runs.

1. Rejects the tenant ids public, health_check, and anything starting with tenant_public.
2. Builds the schema name and runs securityCheck, which throws on any attempt to reach public as a tenant.
3. Fetches the bound models and wraps them in a Proxy.

The Proxy is where outlet isolation is enforced for anything the caller did not write by hand. When options.outletId is set and the model has an outletId or outlet_id attribute, the proxy adds where.outletId = outletId to findAll, findOne, count, destroy, findByPk, update, increment, and decrement. It leaves create and bulkCreate alone, because injecting a where into an insert would corrupt it.

A tenant write opens one transaction per call. A nested executeWithTenant that receives options.transaction reuses the outer transaction instead of opening a second one, and only the outermost caller commits or rolls back.

Why not SET search_path

SET search_path changes a property of a database connection. Supabase puts a pooler in front of PostgreSQL. The pooler hands one physical connection to many requests, in sequence. A session-level SET search_path TO tenant_a issued while serving tenant A survives the end of that request, and the next request that gets the same connection inherits it. If that next request is tenant B, the unqualified query reads tenant A’s tables.

SET LOCAL fixes the leak by scoping the change to a transaction, and that is why src/architecture/migrationRunner.js and src/migrations/simpleMigrationRunner.js still use it. Those calls are safe because they run inside a transaction, on purpose, and the runner says so in a comment:

// transaction-scoped search_path. Plain SET leaks
// across pooled connections (Supavisor/PgBouncer) and breaks
// later unqualified queries on other requests.

Request-time reads have no transaction to attach SET LOCAL to, so they cannot use it even if they wanted to.

What the backend does instead is bind each model to a schema. model.schema('tenant_abc').findAll() makes Sequelize name the schema in the SQL, as in FROM "tenant_abc"."orders". The schema is in the SQL text, so the query returns the same rows no matter which pooled connection runs it, and no connection state survives the request.

src/architecture/modelFactory.js states the rule at the definition of the binding cache. src/core/database/databaseExecutor.js leaves the same note in three places where a SET search_path would otherwise sit. brewlabs-billu-backend/app.js used to reset search_path after every request; that middleware is gone, and the commented block that replaced it explains why.

src/utils/schemaEnforcement.js keeps assertSchemaContext as a debugging net. It runs SHOW search_path only when STRICT_SCHEMA_ASSERTIONS=true, because the check costs a round trip and the bound models already make it redundant.

Entity scope

A tenant schema is not the only boundary. Inside a tenant schema, some tables hold data per outlet and some hold data shared by every outlet in the business. src/utils/entityScopeRegistry.js is the register of which is which.

Scope	Meaning	Examples
outlet	The row belongs to one branch	Order, Product, Inventory, Customer, Table, Payment
business	Shared across every outlet of one business	Supplier, Setting, Role, Permission, ExpenseType
organization	The tenant itself	Outlet, Organization, SchemaVersion

getScope(modelName) throws ENTITY_SCOPE_MISSING for a model that is not registered, so an unregistered model cannot be queried through this path by accident. validateModelRegistered(modelName) throws UNREGISTERED_MODEL for the same reason.

getScopedWhere(req, modelName) in src/utils/tenantScope.js turns a scope into a where clause. It returns {} for organization scope, because the schema is already the boundary. It returns { businessId } for business scope. It builds an outlet filter for outlet scope. These controllers import it: src/controllers/tenant/areaController.js, src/controllers/tenant/controlCenterController.js, src/controllers/tenant/inventoryCategoryController.js, src/controllers/tenant/purchaseController.js, and src/controllers/tenant/rollTrackingController.js.

The executor proxy described above enforces the outlet part for both query methods and class-level updates. src/utils/tenantScope.js also exports autoTenantFilter, and that one is a stub that calls next(). The global hook installer initGlobalTenantHooks in src/utils/globalTenantHooks.js is written but brewlabs-billu-backend/app.js never calls it. Treat the registry plus the executor proxy as the live enforcement, and the hook file as an available tool.

Caches keyed by schema

Model caches are keyed by schema name, which is the tenant id in another form.

Cache	File	Key	Limit
Bound models	src/architecture/modelFactory.js	schema name	200
Executor models	src/core/database/databaseExecutor.js	schema name	500
Request context	src/core/tenant/unifiedTenantMiddleware.js	org_, outlet_, sub_ plus an id	500, 5 minute TTL

Both model caches evict the oldest key when they fill. Clearing one tenant is explicit. databaseExecutor.clearTenantCache(tenantId) deletes that schema’s entry and its health entry, and ModelFactory.clearBoundModelCache(schemaName) does the same for the other cache. Call both after you change a tenant’s schema by hand.

Redis keys carry the tenant and the outlet. src/core/tenant/tenantCacheService.js prefixes every key as tenant:{tenantId}:..., and warns when a key has no outlet segment unless the caller passed allowGlobal. src/utils/cacheKeyBuilder.js provides hasOutletIsolation, which the executor calls as a safety check. A cache key without outlet isolation can hand one branch another branch’s data, so treat that warning as a bug.

Failures you will see

Error	Thrown by	Meaning
INVALID TENANT ID	getTenantModels, executeWithTenant, readWithTenant	The caller passed public, health_check, or a tenant_public id
SECURITY VIOLATION	securityCheck in src/utils/schemaEnforcement.js	A tenant id tried to reach public
SCHEMA MISMATCH	enforceSchema in the same file	The schema name does not match the tenant id
Schema '<name>' does not exist	validateSchemaHealth	The tenant schema is gone
TENANT_SCHEMA_CORRUPTED, status 503	validateSchemaHealth	The schema exists but is missing outlets, roles, permissions, or role_permissions
SCHEMA LEAKAGE PREVENTED	the guard in src/architecture/modelFactory.js	A tenant model ran while bound to public

A corrupted schema returns 503 with code: 'TENANT_SCHEMA_CORRUPTED' and does not self-repair during a request. The startup path repairs schemas. The request path refuses.

Sources

• brewlabs-billu-backend/src/utils/constants.js
• brewlabs-billu-backend/src/core/database/databaseExecutor.js
• brewlabs-billu-backend/src/services/databaseExecutor.js
• brewlabs-billu-backend/src/utils/schemaEnforcement.js
• brewlabs-billu-backend/src/utils/schemaValidator.js
• brewlabs-billu-backend/src/utils/entityScopeRegistry.js
• brewlabs-billu-backend/src/utils/tenantScope.js
• brewlabs-billu-backend/src/utils/globalTenantHooks.js
• brewlabs-billu-backend/src/architecture/centralAssociations.js
• brewlabs-billu-backend/src/architecture/modelFactory.js
• brewlabs-billu-backend/src/architecture/tenantModelBinder.js
• brewlabs-billu-backend/src/architecture/tenantModelLoader.js
• brewlabs-billu-backend/src/architecture/databaseMiddlewareChain.js
• brewlabs-billu-backend/src/architecture/migrationRunner.js
• brewlabs-billu-backend/src/core/tenant/unifiedTenantMiddleware.js
• brewlabs-billu-backend/src/core/tenant/tenantCacheService.js
• brewlabs-billu-backend/src/core/auth/unifiedAuthMiddleware.js
• brewlabs-billu-backend/src/config/unified_database.js
• brewlabs-billu-backend/src/config/control_plane_db.js
• brewlabs-billu-backend/src/config/corsConfig.js
• brewlabs-billu-backend/src/control_plane_models/tenantRegistryModel.js
• brewlabs-billu-backend/src/services/onboardingService.js
• brewlabs-billu-backend/src/services/franchiseService.js
• brewlabs-billu-backend/src/services/organizationService.js
• brewlabs-billu-backend/src/modules/organizations/services/organizationService.js
• brewlabs-billu-backend/src/migrations/simpleMigrationRunner.js
• brewlabs-billu-backend/src/tests/security/tenantIsolationSecurity.test.js
• brewlabs-billu-backend/app.js