Skip to main content

@helix/shared/database

Centralized database service for type-safe access to all 3 database tiers.

Overview​

This service provides a unified interface for accessing:

  • Tier 1: Central platform database (static connection)
  • Tier 2: Tenant databases (dynamic per-tenant)
  • Tier 3: Product databases (dynamic per-tenant-product)

Uses CLS (Continuation-Local Storage) to access tenant and product contexts set by middleware.

Installation​

npm install nestjs-cls

Import in any microservice:

import { DatabaseService } from '@helix/shared/database';

Architecture​

Tier 1: Central Database​

Purpose: Platform metadata, tenant provisioning, audit logs

Connection: Static from CENTRAL_DATABASE_URL environment variable

Models: Tenant, TenantMember, TenantDatabase, TenantProduct, ProductDatabase, AuditLog

Tier 2: Tenant Databases​

Purpose: Shared tenant data (users, files, notifications)

Connection: Dynamic - credentials from CLS tenant context

Database per tenant: tenant_{uuid}_shared

Models: TeamMember, File, Notification, TeamSetting, UserProductAccess

Tier 3: Product Databases​

Purpose: Product-specific business data

Connection: Dynamic - credentials from CLS product context

Database per tenant-product: tenant_{uuid}_{product}

Models: Product-specific (Thread for KIRA, Report for VERA, etc.)

Usage​

Basic Usage​

@Injectable()
export class UserService {
  constructor(private readonly db: DatabaseService) {}

  async getUserData(userId: string) {
    // Tier 1: Central DB
    const tenant = await this.db.central().tenant.findUnique({
      where: { id: 'abc123' }
    });

    // Tier 2: Tenant DB (uses CLS context)
    const member = await this.db.tenant().teamMember.findFirst({
      where: { workosUserId: userId }
    });

    return { tenant, member };
  }
}

Product Service Usage (KIRA Example)​

// apps/kira-service/src/app.module.ts
import { Module } from '@nestjs/common';
import { DatabaseService } from '@helix/shared/database';
import { PrismaClient as KiraPrismaClient } from '@prisma/client'; // Generated in kira-service

@Module({
  providers: [
    {
      provide: DatabaseService,
      useFactory: (cls: ClsService) => {
        const db = new DatabaseService(cls);
        
        // Register KIRA's Prisma client
        db.registerProductClient('kira', KiraPrismaClient);
        
        return db;
      },
      inject: [ClsService],
    },
  ],
})
export class AppModule {}
// apps/kira-service/src/thread/thread.service.ts
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '@helix/shared/database';
import type { PrismaClient as KiraPrismaClient } from '@prisma/client';

@Injectable()
export class ThreadService {
  constructor(private readonly db: DatabaseService) {}

  async getThreads(userId: string) {
    // Tier 2: Get user profile
    const member = await this.db.tenant().teamMember.findFirst({
      where: { workosUserId: userId }
    });

    // Tier 3: Get KIRA threads (automatically uses KIRA DB from CLS)
    const threads = await this.db.product<KiraPrismaClient>().thread.findMany({
      where: { createdBy: userId },
      include: { messages: true }
    });

    return { member, threads };
  }
}

CLS Context Structure​

Set by Middleware​

Middleware sets these contexts in CLS:

// AuthenticationMiddleware
cls.set('auth', {
  userId: 'user_01HXYZ',
  workosOrganizationId: 'org_01ABC',
  role: 'tenant_admin',
  scopes: ['tenant:write', 'user:read']
});

// TenantContextMiddleware  
cls.set('tenant', {
  id: 'tenant_abc123',
  name: 'Acme Corp',
  domain: 'acme.com',
  jurisdiction: 'uk',
  database: {
    host: 'uk-tier2-db.helix.com',
    port: 5432,
    name: 'tenant_abc123_shared',
    username: 'tenant_abc123_user',
    password: 'encrypted_password'
  }
});

// ProductContextMiddleware (optional)
cls.set('product', {
  type: 'kira',
  tenantId: 'tenant_abc123',
  database: {
    host: 'uk-kira-db.helix.com',
    port: 5432,
    name: 'tenant_abc123_kira',
    username: 'tenant_abc123_kira_user',
    password: 'encrypted_password'
  }
});

Used by DatabaseService​

// Tier 2: Reads 'tenant' from CLS
const tenant = this.cls.get('tenant');
const url = `postgresql://${tenant.database.username}:...`;
new TenantPrismaClient({ datasources: { db: { url } } });

// Tier 3: Reads 'product' from CLS
const product = this.cls.get('product');
const url = `postgresql://${product.database.username}:...`;
new KiraPrismaClient({ datasources: { db: { url } } });

Multi-Jurisdiction Support​

Each tenant can be in a different jurisdiction with different database servers:

// UK Tenant
{
  jurisdiction: 'uk',
  database: {
    host: 'uk-tier2-db.helix.com',
    // ...
  }
}

// EU Tenant
{
  jurisdiction: 'eu',
  database: {
    host: 'eu-tier2-db.helix.com',
    // ...
  }
}

// US Tenant
{
  jurisdiction: 'us',
  database: {
    host: 'us-tier2-db.helix.com',
    // ...
  }
}

The middleware loads the correct database credentials from Central DB based on the tenant's jurisdiction.

Connection Pooling​

DatabaseService maintains connection pools:

private tenantClients = new Map<string, TenantPrismaClient>();
private productClients = new Map<string, any>();

Benefits:

  • Reuses connections across requests for same tenant/product
  • Automatic connection management
  • Memory efficient (only creates connections as needed)
  • Cleaned up on module destroy

Pool Keys:

  • Tier 2: tenantId
  • Tier 3: tenantId:productType

Error Handling​

// Tenant context not initialized
try {
  await this.db.tenant().teamMember.findFirst(...);
} catch (error) {
  // Error: Tenant context not initialized. Ensure TenantContextMiddleware has run.
}

// Product client not registered
try {
  await this.db.product().thread.findMany(...);
} catch (error) {
  // Error: Product client for 'kira' not registered. 
  //        Call db.registerProductClient('kira', PrismaClient)
}

// Product context not initialized
try {
  await this.db.product().thread.findMany(...);
} catch (error) {
  // Error: Product context not initialized. Ensure ProductContextMiddleware has run.
}

Complete Service Example​

// apps/kira-service/src/app.module.ts
import { Module } from '@nestjs/common';
import { ClsModule } from 'nestjs-cls';
import { DatabaseService } from '@helix/shared/database';
import { PrismaClient as KiraPrismaClient } from '@prisma/client';
import {
  AuthenticationMiddleware,
  TenantContextMiddleware,
  ProductContextMiddleware,
  RequestLoggingMiddleware,
  RateLimitMiddleware,
} from '@helix/shared/middleware';

@Module({
  imports: [
    // Enable CLS
    ClsModule.forRoot({
      global: true,
      middleware: { mount: true },
    }),
  ],
  providers: [
    // Database service with KIRA client
    {
      provide: DatabaseService,
      useFactory: (cls: ClsService) => {
        const db = new DatabaseService(cls);
        db.registerProductClient('kira', KiraPrismaClient);
        return db;
      },
      inject: [ClsService],
    },
    // Other services
    ThreadService,
    MessageService,
  ],
})
export class AppModule implements NestModule {
  configure(consumer: MiddlewareConsumer) {
    consumer
      .apply(
        RequestLoggingMiddleware,
        AuthenticationMiddleware,
        TenantContextMiddleware,
        ProductContextMiddleware, // Loads KIRA DB credentials
        RateLimitMiddleware,
      )
      .forRoutes('*');
  }
}

Why Product Clients Are Injected​

Problem: Each product has its own Prisma schema in its service directory:

/apps/kira-service/prisma/schema.prisma      → Generates @prisma/client
/apps/vera-service/prisma/schema.prisma      → Generates @prisma/client  
/apps/cleverscreen-service/prisma/schema.prisma → Generates @prisma/client

Solution: DatabaseService doesn't import product clients. Product services register their own client during bootstrap.

Benefits:

  • ✅ DatabaseService has no dependencies on product schemas
  • ✅ Product services own their Prisma clients
  • ✅ Adding new products doesn't require changing shared library
  • ✅ Product schemas can evolve independently

Development​

# Build the library
nx build shared-database

# Lint the library
nx lint shared-database

# Test the library
nx test shared-database

License​

Proprietary - CleverChain Limited