TGM Manager implements a two-level multi-tenancy architecture that provides complete client isolation at the database level while maintaining sandbox functionality within each client's environment.

Architecture Overview¶

┌─────────────────────────────────────────────────────────────────────────────┐
│                         Platform (Master Database)                           │
│  ┌─────────────────────────────────────────────────────────────────────────┐│
│  │                         Clients Table                                    ││
│  │  - Client registry with database connection details                      ││
│  │  - Platform-level administration                                         ││
│  └─────────────────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────────────────┘
                    │
    ┌───────────────┼───────────────┬───────────────┐
    ▼               ▼               ▼               ▼
┌────────┐    ┌────────┐    ┌────────┐    ┌────────┐
│Client A│    │Client B│    │Client C│    │Client D│
│Database│    │Database│    │Database│    │Database│
├────────┤    ├────────┤    ├────────┤    ├────────┤
│ public │    │ public │    │ public │    │ public │
│(prod)  │    │(prod)  │    │(prod)  │    │(prod)  │
├────────┤    ├────────┤    ├────────┤    ├────────┤
│sandbox1│    │sandbox1│    │sandbox1│    │sandbox1│
├────────┤    ├────────┤    ├────────┤    ├────────┤
│sandbox2│    │sandbox2│    │   ...  │    │   ...  │
└────────┘    └────────┘    └────────┘    └────────┘

Level 1: Client Isolation (Database)¶

Each client (organization/company) gets their own separate PostgreSQL database:

• Complete data isolation - No possibility of data leakage between clients
• Independent backups - Each client's data can be backed up independently
• Custom resource allocation - Different database sizes/performance per client
• Compliance friendly - Data residency requirements can be met per client

Level 2: Sandbox Isolation (Schema)¶

Within each client's database, sandboxes use schema-based isolation:

• Production schema (public) - Live data
• Sandbox schemas (sandbox_*) - Testing/development environments
• Instant sandbox creation - Create test environments from production data
• Safe experimentation - Test changes without affecting production

Configuration¶

Environment Variables¶

# Enable multi-tenancy (enabled by default)
MULTITENANCY_DATABASE_ENABLED=true

# Base domain for subdomain-based client resolution
MULTITENANCY_BASE_DOMAIN=tgm.ensolutions.ca

# Default database host for new client databases
MULTITENANCY_DB_HOST=localhost

# Default database port
MULTITENANCY_DB_PORT=5432

application.yml¶

multitenancy:
  database:
    enabled: ${MULTITENANCY_DATABASE_ENABLED:true}
    base-domain: ${MULTITENANCY_BASE_DOMAIN:tgm.ensolutions.ca}
    default-host: ${MULTITENANCY_DB_HOST:localhost}
    default-port: ${MULTITENANCY_DB_PORT:5432}

Frontend Integration Guide¶

This section explains how frontend applications should integrate with the multi-tenant backend.

Request Headers¶

The backend uses HTTP headers to determine which client (database) and sandbox (schema) to use:

Two-Level Resolution Flow¶

┌─────────────────────────────────────────────────────────────────┐
│                       Incoming Request                           │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│          Step 1: Client Resolution (Which DATABASE?)             │
│                                                                  │
│  Priority order:                                                 │
│  1. X-Client-ID header    → "X-Client-ID: acme-corp"            │
│  2. ?client= query param  → "?client=acme-corp"                 │
│  3. Subdomain             → "acme-corp.tgm.ensolutions.ca"      │
│  4. Custom domain         → "maintenance.acme.com"              │
│  5. Default               → master database                      │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│          Step 2: Sandbox Resolution (Which SCHEMA?)              │
│                                                                  │
│  Priority order:                                                 │
│  1. X-Tenant-ID header    → "X-Tenant-ID: sandbox_dev"          │
│  2. ?sandbox= query param → "?sandbox=dev-team"                 │
│  3. Default               → "public" (production data)          │
└─────────────────────────────────────────────────────────────────┘

Angular Examples¶

Tenant Configuration Service¶

// tenant.service.ts
import { Injectable } from '@angular/core';
import { BehaviorSubject } from 'rxjs';

export interface TenantConfig {
  clientId: string;
  sandboxId?: string;  // Optional - omit for production
}

@Injectable({
  providedIn: 'root'
})
export class TenantService {
  private tenantConfig$ = new BehaviorSubject<TenantConfig | null>(null);

  setTenant(config: TenantConfig): void {
    this.tenantConfig$.next(config);
    localStorage.setItem('tenantConfig', JSON.stringify(config));
  }

  getTenant(): TenantConfig | null {
    if (!this.tenantConfig$.value) {
      const stored = localStorage.getItem('tenantConfig');
      if (stored) {
        this.tenantConfig$.next(JSON.parse(stored));
      }
    }
    return this.tenantConfig$.value;
  }

  getClientId(): string | null {
    return this.getTenant()?.clientId || null;
  }

  getSandboxId(): string | null {
    return this.getTenant()?.sandboxId || null;
  }

  clearTenant(): void {
    this.tenantConfig$.next(null);
    localStorage.removeItem('tenantConfig');
  }
}

HTTP Interceptor for Multi-Tenancy¶

// tenant.interceptor.ts
import { Injectable } from '@angular/core';
import {
  HttpInterceptor,
  HttpRequest,
  HttpHandler,
  HttpEvent
} from '@angular/common/http';
import { Observable } from 'rxjs';
import { TenantService } from './tenant.service';

@Injectable()
export class TenantInterceptor implements HttpInterceptor {
  constructor(private tenantService: TenantService) {}

  intercept(req: HttpRequest<any>, next: HttpHandler): Observable<HttpEvent<any>> {
    const clientId = this.tenantService.getClientId();
    const sandboxId = this.tenantService.getSandboxId();

    let headers = req.headers;

    if (clientId) {
      headers = headers.set('X-Client-ID', clientId);
    }

    if (sandboxId) {
      headers = headers.set('X-Tenant-ID', sandboxId);
    }

    const clonedRequest = req.clone({ headers });
    return next.handle(clonedRequest);
  }
}

// app.module.ts (or app.config.ts for standalone)
import { HTTP_INTERCEPTORS } from '@angular/common/http';
import { TenantInterceptor } from './interceptors/tenant.interceptor';

// Add to providers array:
// { provide: HTTP_INTERCEPTORS, useClass: TenantInterceptor, multi: true }

API Service with Multi-Tenancy¶

// api.service.ts
import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';
import { environment } from '../environments/environment';

@Injectable({
  providedIn: 'root'
})
export class ApiService {
  private baseUrl = environment.apiUrl;

  constructor(private http: HttpClient) {}

  // Headers are automatically added by TenantInterceptor
  get<T>(endpoint: string): Observable<T> {
    return this.http.get<T>(`${this.baseUrl}${endpoint}`);
  }

  post<T>(endpoint: string, data: any): Observable<T> {
    return this.http.post<T>(`${this.baseUrl}${endpoint}`, data);
  }

  put<T>(endpoint: string, data: any): Observable<T> {
    return this.http.put<T>(`${this.baseUrl}${endpoint}`, data);
  }

  delete<T>(endpoint: string): Observable<T> {
    return this.http.delete<T>(`${this.baseUrl}${endpoint}`);
  }
}

// Usage in component
@Component({
  selector: 'app-units',
  template: `
    <div *ngFor="let unit of units$ | async">
      {{ unit.name }}
    </div>
  `
})
export class UnitsComponent implements OnInit {
  units$: Observable<Unit[]>;

  constructor(private api: ApiService) {}

  ngOnInit(): void {
    this.units$ = this.api.get<Unit[]>('/api/units');
  }
}

Sandbox Selector Component¶

// sandbox-selector.component.ts
import { Component, OnInit } from '@angular/core';
import { TenantService, TenantConfig } from '../services/tenant.service';
import { ApiService } from '../services/api.service';

interface Sandbox {
  id: string;
  name: string;
  description: string;
}

@Component({
  selector: 'app-sandbox-selector',
  template: `
    <select [(ngModel)]="selectedSandbox" (ngModelChange)="onSandboxChange($event)">
      <option [value]="null">Production</option>
      <option *ngFor="let sandbox of sandboxes" [value]="sandbox.id">
        {{ sandbox.name }}
      </option>
    </select>
  `
})
export class SandboxSelectorComponent implements OnInit {
  sandboxes: Sandbox[] = [];
  selectedSandbox: string | null = null;

  constructor(
    private tenantService: TenantService,
    private api: ApiService
  ) {}

  ngOnInit(): void {
    // Load available sandboxes
    this.api.get<Sandbox[]>('/sandboxes').subscribe(sandboxes => {
      this.sandboxes = sandboxes;
    });

    // Set initial value
    this.selectedSandbox = this.tenantService.getSandboxId() || null;
  }

  onSandboxChange(sandboxId: string | null): void {
    const currentConfig = this.tenantService.getTenant();
    if (currentConfig) {
      this.tenantService.setTenant({
        clientId: currentConfig.clientId,
        sandboxId: sandboxId || undefined
      });
      // Reload current page to fetch data from new sandbox
      window.location.reload();
    }
  }
}

cURL Examples¶

Production Request (no sandbox)¶

curl -X GET "https://api.tgm.ensolutions.ca/api/units" \
  -H "Authorization: Bearer $JWT_TOKEN" \
  -H "X-Client-ID: acme-corp"

Sandbox Request¶

curl -X GET "https://api.tgm.ensolutions.ca/api/units" \
  -H "Authorization: Bearer $JWT_TOKEN" \
  -H "X-Client-ID: acme-corp" \
  -H "X-Tenant-ID: sandbox_dev_team"

Using Query Parameters (for testing)¶

curl -X GET "https://api.tgm.ensolutions.ca/api/units?client=acme-corp&sandbox=dev-team" \
  -H "Authorization: Bearer $JWT_TOKEN"

Using Subdomain (web applications)¶

curl -X GET "https://acme-corp.tgm.ensolutions.ca/api/units" \
  -H "Authorization: Bearer $JWT_TOKEN"

Response Headers¶

The API returns these headers for debugging:

Common Scenarios¶

Error Responses¶

Client Resolution (Detailed)¶

The system resolves which client database to use based on the incoming request. Resolution order:

1. X-Client-ID Header - Explicit client identifier (recommended)

   X-Client-ID: acme-corp

2. Query Parameter - For testing/debugging

   GET /api/units?client=acme-corp

3. Subdomain - Primary method for web applications

   https://acme-corp.tgm.ensolutions.ca/api/units

4. Custom Domain - For white-label deployments

   https://maintenance.acme.com/api/units

5. Default - Falls back to master database

API Endpoints¶

Platform Administration¶

These endpoints are available on the master database for platform administrators:

POST   /api/platform/clients                    Create a new client
GET    /api/platform/clients                    List all clients
GET    /api/platform/clients/active             List active clients
GET    /api/platform/clients/{id}               Get client details
PUT    /api/platform/clients/{id}               Update client
DELETE /api/platform/clients/{id}               Delete client (soft delete)
POST   /api/platform/clients/{id}/provision     Provision database for client
POST   /api/platform/clients/{id}/suspend       Suspend client access
POST   /api/platform/clients/{id}/activate      Reactivate client

# Migration Management
POST   /api/platform/clients/migrate            Run migrations on all client databases
POST   /api/platform/clients/{id}/migrate       Run migrations on specific client
GET    /api/platform/clients/migration-status   Get migration status for all clients

Create Client Example¶

curl -X POST http://localhost:1337/api/platform/clients \
  -H "Authorization: Bearer $ADMIN_JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "identifier": "acme-corp",
    "name": "Acme Corporation",
    "subdomain": "acme-corp",
    "contactEmail": "admin@acme.com",
    "maxUsers": 50,
    "maxSandboxes": 5
  }'

Provision Database Example¶

curl -X POST http://localhost:1337/api/platform/clients/1/provision \
  -H "Authorization: Bearer $ADMIN_JWT"

Migration Examples¶

Check migration status for all clients:

curl -X GET http://localhost:1337/api/platform/clients/migration-status \
  -H "Authorization: Bearer $ADMIN_JWT"

Response:

{
  "data": {
    "acme-corp": {
      "currentVersion": "33",
      "pendingCount": 2,
      "pendingVersions": ["34", "35"]
    },
    "beta-company": {
      "currentVersion": "35",
      "pendingCount": 0,
      "pendingVersions": []
    }
  },
  "totalClients": 2,
  "clientsWithPendingMigrations": 1
}

Run migrations on all client databases:

curl -X POST http://localhost:1337/api/platform/clients/migrate \
  -H "Authorization: Bearer $ADMIN_JWT"

Run migration on a specific client:

curl -X POST http://localhost:1337/api/platform/clients/1/migrate \
  -H "Authorization: Bearer $ADMIN_JWT"

Auto-Migration on Startup¶

By default, when the application starts, it automatically runs Flyway migrations on all active client databases. This ensures all clients are always on the latest schema version after a deployment.

Configuration:

multitenancy:
  database:
    enabled: true
    auto-migrate: true  # Default: true - runs migrations on startup

Environment Variable:

MULTITENANCY_DATABASE_AUTO_MIGRATE=true  # Set to false to disable

Startup Behavior:

1. Application starts and initializes DataSources for all active clients
2. If auto-migrate=true, Flyway migrations run on each client database
3. Results are logged (success/failure counts per client)
4. Application continues to serve requests

Startup Logs:

INFO  - Initializing multi-tenant system...
INFO  - Multi-tenant client DataSources initialized successfully
INFO  - Running migrations for all client databases (auto-migrate enabled)...
INFO  - Migration completed for client acme-corp: 2 migrations applied
INFO  - Migration completed for client beta-company: 2 migrations applied
INFO  - All client migrations completed successfully. Total migrations applied: 4

When to Disable Auto-Migration:

• Large number of clients (migration can slow down startup)
• Prefer manual control over when migrations run
• CI/CD pipeline handles migrations separately

If disabled, use the admin endpoints to run migrations manually after deployment:

# Check status first
curl -X GET http://localhost:1337/api/platform/clients/migration-status \
  -H "Authorization: Bearer $ADMIN_JWT"

# Then run migrations
curl -X POST http://localhost:1337/api/platform/clients/migrate \
  -H "Authorization: Bearer $ADMIN_JWT"

Client Entity¶

@Entity
@Table(name = "clients")
public class Client {
    private Long id;
    private String identifier;        // Unique identifier (slug)
    private String companyName;       // Display name
    private String subdomain;         // For subdomain routing
    private String customDomain;      // Optional custom domain

    // Database connection
    private String databaseHost;
    private Integer databasePort;
    private String databaseName;
    private String databaseUsername;
    private String databasePassword;  // Encrypted

    // Status
    private ClientStatus status;      // PENDING, PROVISIONING, ACTIVE, SUSPENDED, DELETED

    // Limits
    private Integer maxUsers;
    private Integer maxSandboxes;

    // Timestamps
    private LocalDateTime provisionedAt;
    private LocalDateTime lastAccessAt;
}

Database Provisioning Flow¶

When a new client is created:

1. Create Client record (status: PENDING)
           │
           ▼
2. Trigger async provisioning (status: PROVISIONING)
           │
           ▼
3. Create new PostgreSQL database
           │
           ▼
4. Run Flyway migrations on new database
           │
           ▼
5. Seed initial data (roles, default user)
           │
           ▼
6. Register DataSource in routing pool
           │
           ▼
7. Update client status (status: ACTIVE)

Request Flow¶

┌─────────────────────────────────────────────────────────────────────────────┐
│                           Incoming Request                                   │
│                    GET https://acme.tgm.ensolutions.ca/api/units            │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         ClientFilter (Order: 0)                              │
│  - Extract subdomain: "acme"                                                │
│  - Look up client by subdomain                                               │
│  - Set ClientContext.setClient("acme")                                       │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         TenantFilter (Order: 1)                              │
│  - Check for sandbox header/param                                            │
│  - Set TenantContext.setTenant("public" or "sandbox_xyz")                    │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                    MultiTenantRoutingDataSource                              │
│  - Read ClientContext.getClient() → "acme"                                   │
│  - Route to acme's DataSource                                                │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                    HikariCP Connection Pool (Acme)                           │
│  - Get connection to acme_tgm database                                       │
│  - Set schema: TenantContext.getTenant() → "public"                          │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                           JPA Repository                                     │
│  - Execute query on acme_tgm.public schema                                   │
└─────────────────────────────────────────────────────────────────────────────┘

Security Considerations¶

Database Credentials¶

• Each client has unique database credentials
• Passwords are encrypted in the master database
• Connection details never exposed via API

Access Control¶

• Platform admin endpoints require SUPER_ADMIN role
• Client admins can only access their own client's data
• Sandbox access controlled per user

Isolation Guarantees¶

• Database level: Physical separation of client data
• Connection pool: Separate HikariCP pools per client
• Schema level: PostgreSQL schema isolation for sandboxes
• Application level: TenantContext/ClientContext thread-local isolation

Monitoring¶

Health Checks¶

Each client database connection is monitored:

GET /actuator/health

Returns health status for all registered client DataSources.

Metrics¶

Prometheus metrics available for:

• Active connections per client
• Query latency per client
• Connection pool utilization
• Failed connection attempts

Best Practices¶

1. Client Onboarding¶

# 1. Create client (returns immediately)
POST /admin/clients

# 2. Provision database (async)
POST /admin/clients/{id}/provision

# 3. Poll for completion
GET /admin/clients/{id}  # Wait for status: ACTIVE

2. Sandbox Usage¶

# Create sandbox within client's database
POST /sandboxes
X-Client-ID: acme-corp
{
  "name": "Testing Q1 Release",
  "copyData": true
}

# Access sandbox data
GET /api/units
X-Client-ID: acme-corp
X-Sandbox-ID: sandbox_123

3. Client Suspension¶

# Suspend client (blocks all access)
POST /admin/clients/{id}/suspend

# Reactivate when issues resolved
POST /admin/clients/{id}/activate

Troubleshooting¶

Client Database Not Accessible¶

1. Check client status: GET /admin/clients/{id}
2. Verify database connectivity from application server
3. Check HikariCP pool status in logs
4. Ensure Flyway migrations completed

Sandbox Schema Not Found¶

1. Verify sandbox exists: GET /sandboxes
2. Check schema exists in client database
3. Verify user has sandbox access permissions

Performance Issues¶

1. Monitor connection pool utilization per client
2. Check for slow queries in specific client databases
3. Consider increasing pool size for high-traffic clients
4. Review index usage per client

Scheduled Tasks (Cron Jobs)¶

Scheduled tasks must be multi-tenant aware to run for all clients, not just the master database.

Using MultiTenantSchedulerSupport¶

Inject MultiTenantSchedulerSupport and wrap your task logic:

@Slf4j
@Component
@RequiredArgsConstructor
public class MyScheduler {

    private final MyService myService;
    private final MultiTenantSchedulerSupport multiTenantSchedulerSupport;

    @Value("${app.cron.enabled:true}")
    private boolean cronEnabled;

    @Scheduled(cron = "0 0 8 * * *")
    public void myScheduledTask() {
        if (!cronEnabled) return;

        // Executes for all active clients (or once if multitenancy disabled)
        multiTenantSchedulerSupport.executeForAllTenants(
            "my-task-name",
            this::executeTask
        );
    }

    @Transactional
    protected void executeTask() {
        // Your task logic here
        // ClientContext is already set for the current tenant
        myService.doSomething();
    }
}

Behavior¶

Already Updated Schedulers¶

The following schedulers are multi-tenant aware:

• MaintenanceReminderScheduler - Inspection/intervention reminders

Schedulers Requiring Update¶

These schedulers still need to be updated to use MultiTenantSchedulerSupport:

• LicenseAndWarrantyScheduler
• OverdueMaintenanceScheduler
• EquipmentAndMaterialScheduler
• FailureAndCorrectiveActionScheduler
• UserAccountScheduler
• ReportingScheduler
• AssetReplacementScheduler
• ProgrammableInspectionScheduler
• DataCleanupScheduler
• SensorDataCleanupScheduler

Migration Pattern¶

To make any scheduler multi-tenant aware:

1. Add MultiTenantSchedulerSupport as a dependency
2. Extract task logic into a separate @Transactional method
3. Wrap the call with multiTenantSchedulerSupport.executeForAllTenants()

Migration from Single-Tenant¶

If migrating from single-tenant to multi-tenant:

1. Create client record for existing data
2. Point client to existing database
3. Enable multi-tenancy
4. Existing data becomes the first client

No data migration required - the existing database becomes a client database.