Multi-Database Architecture for Enterprise Customers: How We Built BYODB at Positeasy

The Problem

Positeasy started life as a typical SaaS POS platform — single monolithic PostgreSQL database, ~98 Sequelize models, row-level tenancy via a merchantId column on every operational table. It worked. For thousands of small merchants on shared infra, it still works great.

Then enterprise prospects started showing up with the same three asks:

• Compliance — "Our data cannot share a database with anyone else's."
• Isolation — "A noisy-neighbor query from another merchant should never affect us."
• Contractual — "We need the right to take a full DB backup and walk away."

Row-level tenancy can't satisfy any of these. We needed real, physical database separation — but only for the customers who actually need it.

The Goal

A hybrid architecture where:

• Shared SaaS merchants → continue using the existing platform DB for everything. Zero behavior change. Zero migration.
• Enterprise merchants → operational tables (orders, products, payments, inventory…) hit a dedicated tenant database. Platform tables (merchant record, subscription, roles, integrations) stay on the shared control plane.

The Architecture

Two Classes of Model

Every Sequelize model in the codebase is now classified as exactly one of:

• SHARED — lives on the platform DB. Merchant, user, role, subscription, integration credentials, push tokens, summaries, offline bill staging.
• TENANT — lives on the tenant DB for enterprise merchants, and on the platform DB (logically) for shared merchants.

The classification lives in a single file: src/database/tenant/modelClassification.js. Two arrays — SHARED_MODEL_KEYS and TENANT_MODEL_KEYS — plus a TENANT_MODEL_FILES map that lets the tenant model factory load each model on a per-tenant Sequelize instance.

A boot-time check (assertClassificationCoverage) blocks startup if any model registered in src/models/index.js is missing from the lists. Forgetting to classify a new model is one of the easiest ways to silently leak cross-tenant data — so the app refuses to start until it's fixed.

Per-Request Routing

Every authenticated request flows through two middlewares in order:

verifyMerchantToken → resolveTenantDb → handlerflow

resolveTenantDb reads the JWT-derived merchantId, looks up the merchant's deploymentType from a small in-memory cache (5-min TTL), and attaches the right model bag to ctx:

• ctx.sharedDb → always the platform Sequelize singleton.
• ctx.tenantDb → for SHARED merchants, the shared operational view of the platform DB. For ENTERPRISE merchants, a per-tenant Sequelize cached by merchantId.

Controllers then access models through a tiny helper:

const { tenantModels, sharedModels } = require("../database/tenant/getModels");

async function placeOrder(ctx) {
  const { booking: Booking, payment: Payment } = tenantModels(ctx);
  const { merchant: Merchant } = sharedModels(ctx);
  // ...
}node.js

SHARED merchants pass through the exact same code path with zero behavioral change. ENTERPRISE merchants' writes route to their dedicated database.

Connection Caching

Per-request new Sequelize(...) would be a disaster — connection pools are expensive and slow to build. The connection manager keeps a Map<merchantId, { sequelize, models, lastUsedAt }>, lazily builds the pool on first hit, and sweeps idle entries (>30 min unused) every 5 minutes. Graceful shutdown closes them all.

Credentials at Rest

Tenant DB credentials are stored encrypted in a tenantDatabaseConfigs table on the platform DB using AES-256-GCM (reusing the existing EIP_ENCRYPTION_KEY). The control plane is the only thing that ever holds decrypted credentials, in-memory, during a connection build.

One-Call Provisioning

We did not want a runbook every time enterprise sales closed a deal. Provisioning is one POST:

POST /api/v2/POS/admin/tenant/provisionhttp

The endpoint does the whole flow:

If any step fails, the whole thing rolls back cleanly and the merchant stays on the shared DB.

Migrations

Schema changes split by destination:

• src/migrations/ — shared migrations, applied to the platform DB via npx sequelize-cli db:migrate.
• src/migrations/tenant/ — tenant migrations, applied per-enterprise-merchant via a custom runner:

node scripts/migrate-tenant.js --merchantId=<uuid>
node scripts/migrate-tenant.js --all-enterprisebash

The runner uses umzug v3 under the hood with a small adapter that bridges sequelize-cli's (queryInterface, Sequelize) signature into umzug's context-based one. It tracks SequelizeMeta per tenant DB, so each merchant's migration history is independent.

Background Jobs

Kafka consumers, BullMQ workers and cron jobs don't have a Koa ctx. They resolve the tenant DB directly from merchantId:

const { tenantDb } = await resolveTenantDbForJob(merchantId);node.js

Same connection cache, same model bag, no request scope required.

What We Deliberately Did Not Build

A few things we considered and explicitly skipped:

Architecture Diagrams

Three views of the system that mattered most while building it: how a single request gets routed, how the connection cache stays warm, and what happens end-to-end when an enterprise tenant gets provisioned.

1. Per-Request Routing

Two middlewares decide which database a controller sees. The decision is driven entirely by deploymentType on the merchant record — the controllers themselves never branch.

2. Connection Cache

One Map keyed by merchantId. Lazy build on first hit, idle eviction by a sweeper, full close on shutdown. Only enterprise merchants ever land in this cache — shared merchants always go through the platform singleton.

3. Provisioning Sequence

One POST. Seven steps. Atomic from the caller's point of view — if any step fails the merchant stays on the shared DB and no partial state is left behind.

What We Learned

Classification is the bug surface, not connection management

Once the connection manager works, it just works. The thing that bites you is a developer adding a new model and forgetting to classify it. Then enterprise customers' writes silently land on the platform DB, and you don't notice until someone reads back stale data months later. The boot-time check + a written onboarding guide for src/models/ is more valuable than the entire connection cache.

A kill switch is non-negotiable

Deploying a routing layer without a way to force everything to the old path is how you turn a Tuesday into a Saturday. The env flag costs nothing and buys infinite peace of mind.

Reclassification is expensive — make the default TENANT

We already had to move a handful of models the other direction (summaries and offline-bill staging from TENANT → SHARED). When in doubt, mark new models TENANT.

Pick the cheap mistake.

One-call provisioning pays for itself the first time

Building the admin endpoint took a day. Doing it by hand would have taken longer than that on the first enterprise customer alone — and would have been wrong at least once.

Built on Koa + Sequelize + PostgreSQL.