neo/docs/MULTI_TENANT_IMPLEMENTATION.md

Multi-Tenant Migration - Implementation Summary

Overview

The platform has been migrated from a single-database multi-tenant architecture to a one database per tenant architecture with subdomain-based tenant identification.

Architecture Changes

Database Layer

• Central Database (Prisma): Stores tenant metadata, domain mappings, encrypted credentials
• Tenant Databases (Knex.js + Objection.js): One MySQL database per tenant with isolated data

Tenant Identification

• Before: x-tenant-id header
• After: Subdomain extraction from hostname (e.g., acme.routebox.co → tenant acme)
• Fallback: x-tenant-id header for local development

Technology Stack

• Central DB ORM: Prisma 5.8.0
• Tenant DB Migration: Knex.js 3.x
• Tenant DB ORM: Objection.js 3.x
• Database Driver: mysql2

File Structure

Backend - Tenant Management

src/tenant/
├── tenant-database.service.ts       # Knex connection manager with encryption
├── tenant-provisioning.service.ts   # Create/destroy tenant databases
├── tenant-provisioning.controller.ts # API for tenant provisioning
├── tenant.middleware.ts              # Subdomain extraction & tenant injection
└── tenant.module.ts                  # Module configuration

migrations/tenant/                    # Knex migrations for tenant databases
├── 20250126000001_create_users_and_rbac.js
├── 20250126000002_create_object_definitions.js
├── 20250126000003_create_apps.js
└── 20250126000004_create_standard_objects.js

Backend - Models (Objection.js)

src/models/
├── base.model.ts                # Base model with timestamps
├── user.model.ts                # User with roles
├── role.model.ts                # Role with permissions
├── permission.model.ts          # Permission
├── user-role.model.ts           # User-Role join table
├── role-permission.model.ts     # Role-Permission join table
├── object-definition.model.ts   # Dynamic object metadata
├── field-definition.model.ts    # Field metadata
├── app.model.ts                 # Application
├── app-page.model.ts            # Application pages
└── account.model.ts             # Standard Account object

Backend - Schema Management

src/object/
├── schema-management.service.ts  # Dynamic table creation from ObjectDefinitions
└── object.service.ts             # Object CRUD operations (needs migration)

Central Database Schema (Prisma)

prisma/
├── schema-central.prisma         # Tenant, Domain models
└── migrations/                   # Will be created when generating

Setup Instructions

1. Environment Configuration

Copy .env.example to .env and configure:

cd /root/neo/backend
cp .env.example .env

Generate encryption key:

node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

Update .env with the generated key and database URLs:

CENTRAL_DATABASE_URL="mysql://user:password@platform-db:3306/central_platform"
ENCRYPTION_KEY="<generated-32-byte-hex-key>"
DB_ROOT_USER="root"
DB_ROOT_PASSWORD="root"

2. Central Database Setup

Generate Prisma client and run migrations:

cd /root/neo/backend
npx prisma generate --schema=./prisma/schema-central.prisma
npx prisma migrate dev --schema=./prisma/schema-central.prisma --name init

3. Tenant Provisioning

Create a new tenant via API:

curl -X POST http://localhost:3000/setup/tenants \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Acme Corporation",
    "slug": "acme",
    "primaryDomain": "acme"
  }'

This will:

1. Create MySQL database tenant_acme
2. Create database user tenant_acme_user
3. Run all Knex migrations on the new database
4. Seed default roles and permissions
5. Store encrypted credentials in central database
6. Create domain mapping (acme → tenant)

4. Testing Subdomain Routing

Update your hosts file or DNS to point subdomains to your server:

127.0.0.1  acme.localhost
127.0.0.1  demo.localhost

Access the application:

• Central setup: http://localhost:3000/setup/tenants
• Tenant app: http://acme.localhost:3000/
• Different tenant: http://demo.localhost:3000/

Migration Status

✅ Completed

• Central database schema (Tenant, Domain models)
• Knex + Objection.js installation
• TenantDatabaseService with dynamic connections
• Password encryption/decryption (AES-256-CBC)
• Base Objection.js models (User, Role, Permission, etc.)
• Knex migrations for base tenant schema
• Tenant middleware with subdomain extraction
• Tenant provisioning service (create/destroy)
• Schema management service (dynamic table creation)

🔄 Pending

• Generate Prisma client for central database
• Run Prisma migrations for central database
• Migrate AuthService from Prisma to Objection.js
• Migrate RBACService from Prisma to Objection.js
• Migrate ObjectService from Prisma to Objection.js
• Migrate AppBuilderService from Prisma to Objection.js
• Update frontend to work with subdomains
• Test tenant provisioning flow
• Test subdomain routing
• Test database isolation

Service Migration Guide

Example: Migrating a Service from Prisma to Objection

Before (Prisma):

async findUser(email: string) {
  return this.prisma.user.findUnique({ where: { email } });
}

After (Objection + Knex):

constructor(private readonly tenantDbService: TenantDatabaseService) {}

async findUser(tenantId: string, email: string) {
  const knex = await this.tenantDbService.getTenantKnex(tenantId);
  return User.query(knex).findOne({ email });
}

Key Changes

1. Inject TenantDatabaseService instead of PrismaService
2. Get tenant Knex connection: await this.tenantDbService.getTenantKnex(tenantId)
3. Use Objection models: User.query(knex).findOne({ email })
4. Pass tenantId to all service methods (extract from request in controller)

API Changes

Tenant Provisioning Endpoints

Create Tenant

POST /setup/tenants
Content-Type: application/json

{
  "name": "Company Name",
  "slug": "company-slug",
  "primaryDomain": "company",
  "dbHost": "platform-db",  // optional
  "dbPort": 3306            // optional
}

Response:
{
  "tenantId": "uuid",
  "dbName": "tenant_company-slug",
  "dbUsername": "tenant_company-slug_user",
  "dbPassword": "generated-password"
}

Delete Tenant

DELETE /setup/tenants/:tenantId

Response:
{
  "success": true
}

Security Considerations

1. Encryption: Tenant database passwords are encrypted with AES-256-CBC before storage
2. Isolation: Each tenant has a dedicated MySQL database and user
3. Credentials: Database credentials stored in central DB, never exposed to tenants
4. Subdomain Validation: Middleware validates tenant exists and is active before processing requests

Troubleshooting

Connection Issues

Check tenant connection cache:

await this.tenantDbService.disconnectTenant(tenantId);
const knex = await this.tenantDbService.getTenantKnex(tenantId); // Fresh connection

Migration Issues

Run migrations manually:

cd /root/neo/backend
npx knex migrate:latest --knexfile=knexfile.js

Encryption Key Issues

If ENCRYPTION_KEY is not set, generate one:

node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

Next Steps

1. Generate Central DB Schema

   npx prisma generate --schema=./prisma/schema-central.prisma
   npx prisma migrate dev --schema=./prisma/schema-central.prisma
   

2. Migrate Existing Services

   • Start with AuthService (most critical)
   • Then RBACService, ObjectService, AppBuilderService
   • Update all controllers to extract tenantId from request

3. Frontend Updates

   • Update API calls to include subdomain
   • Test cross-tenant isolation
   • Update login flow to redirect to tenant subdomain

4. Testing

   • Create multiple test tenants
   • Verify data isolation
   • Test subdomain routing
   • Performance testing with multiple connections

5. Production Deployment

   • Set up wildcard DNS for subdomains
   • Configure SSL certificates for subdomains
   • Set up database backup strategy per tenant
   • Monitor connection pool usage