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:

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

Generate encryption key:

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

Update `.env` with the generated key and database URLs:

```env
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:

```bash
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:

```bash
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

- [x] Central database schema (Tenant, Domain models)
- [x] Knex + Objection.js installation
- [x] TenantDatabaseService with dynamic connections
- [x] Password encryption/decryption (AES-256-CBC)
- [x] Base Objection.js models (User, Role, Permission, etc.)
- [x] Knex migrations for base tenant schema
- [x] Tenant middleware with subdomain extraction
- [x] Tenant provisioning service (create/destroy)
- [x] 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):**

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

**After (Objection + Knex):**

```typescript
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:

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

### Migration Issues

Run migrations manually:

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

### Encryption Key Issues

If `ENCRYPTION_KEY` is not set, generate one:

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

## Next Steps

1. **Generate Central DB Schema**

   ```bash
   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