Skip to main content

Prisma Database Management

Helix Platform Multi-Database Strategy​

Overview​

Helix uses a 3-tier database architecture with Prisma ORM for type-safe database access:

  1. Central Database (Tier 1) - Platform metadata, NO PII
  2. Tenant Databases (Tier 2) - One per tenant, shared data WITH PII
  3. Product Databases (Tier 3) - One per tenant-product, business data

Centralized Prisma Version​

All schemas use the same Prisma version managed at the root level:

{
  "devDependencies": {
    "prisma": "^5.7.1"
  },
  "dependencies": {
    "@prisma/client": "^5.7.1"
  }
}

Benefits:

  • Single CLI version for migrations
  • Consistent type generation
  • Unified upgrade path
  • No version conflicts

Schema Organization​

Tier 1: Central Database Schema (Root Level)​

Prisma Client: @prisma/client-central (CentralPrismaClient)

Location: prisma/central/schema.prisma

Purpose: Used by ALL services for platform metadata, tenant provisioning, audit logs

Generator Config:

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/@prisma/client-central"
}

Import:

import { PrismaClient } from '@prisma/client-central';

Product Schemas (Service-Owned)​

Location: apps/{service}/prisma/schema.prisma

Purpose: Service-specific business data

Examples:

  • apps/kira-service/prisma/schema.prisma - KIRA threads, messages
  • apps/vera-service/prisma/schema.prisma - VERA reports, templates
  • apps/cleverscreen-service/prisma/schema.prisma - Screening data

Generator Config:

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/@prisma/client"
}

Import:

import { PrismaClient } from '@prisma/client';

Available Scripts​

Central Database​

# Generate Prisma client
npm run prisma:generate

# Run migrations in development
npm run prisma:migrate:dev

# Deploy migrations to production
npm run prisma:migrate:deploy

# Open Prisma Studio
npm run prisma:studio

Product-Specific Schemas​

# From root or service directory
npx prisma generate --schema=apps/kira-service/prisma/schema.prisma

# Run migrations for service
cd apps/kira-service
npx prisma migrate dev --name add_new_field

Development Workflow​

1. Initial Setup​

# Clone repository
git clone <repo-url>
cd HelixCore

# Install dependencies
npm install

# Generate Prisma client
npm run prisma:generate

# Start infrastructure
docker-compose up -d

# Run migrations
npm run prisma:migrate:dev

2. Making Schema Changes​

For Central Database:

# Edit prisma/central/schema.prisma
# Add new model or field

# Create and apply migration
npm run prisma:migrate:dev --name add_new_model

# Regenerate client
npm run prisma:generate

For Product Database:

# Edit apps/kira-service/prisma/schema.prisma
# Add new model or field

# Navigate to service directory
cd apps/kira-service

# Create and apply migration
npx prisma migrate dev --name add_new_field

# Client automatically regenerated

3. Viewing Data​

# Central database
npm run prisma:studio

# Product database
cd apps/kira-service
npx prisma studio

Database Connection Pattern​

Central Database (Used by ALL Services)​

import { PrismaClient as CentralPrismaClient } from '@prisma/client-central';

@Injectable()
export class AuthService {
  private centralDb: CentralPrismaClient;

  constructor() {
    this.centralDb = new CentralPrismaClient({
      datasources: {
        db: {
          url: process.env.CENTRAL_DATABASE_URL
        }
      }
    });
  }

  async getTenantByDomain(domain: string) {
    return this.centralDb.tenant.findUnique({
      where: { domain }
    });
  }
}

Product Database (Service-Specific)​

import { PrismaClient } from '@prisma/client';

@Injectable()
export class KiraService {
  private kiraDb: PrismaClient;

  constructor() {
    // Connection URL resolved dynamically per tenant
    this.kiraDb = new PrismaClient({
      datasources: {
        db: {
          url: this.getKiraDbUrl(tenantId) // From TenantProduct record
        }
      }
    });
  }

  async getThreads(tenantId: string) {
    return this.kiraDb.thread.findMany({
      where: { tenantId }
    });
  }
}

Migration Strategy​

Development​

# Create migration
npm run prisma:migrate:dev --name descriptive_name

# Prisma will:
# 1. Create migration SQL file
# 2. Apply migration to database
# 3. Regenerate Prisma client

Production​

# Deploy migrations (no prompts)
npm run prisma:migrate:deploy

# This is run automatically in CI/CD

Rollback​

# Prisma doesn't support automatic rollback
# Manual process:
# 1. Identify migration to rollback
# 2. Create new migration that reverses changes
# 3. Apply forward migration

Multi-Database Architecture​

Which Services Use Which Databases​

ServiceCentral DBTenant DBProduct DB
api-gateway✅❌❌
auth-service✅✅❌
tenant-service✅✅❌
admin-service✅✅❌
file-service✅✅❌
kira-service✅✅✅ KIRA
vera-service✅✅✅ VERA
cleverscreen✅✅✅ Screen

Database URLs by Environment​

Development (Docker Compose):

CENTRAL_DATABASE_URL=postgresql://helix:helix_dev_password@localhost:5432/helix_central

Production (AWS RDS):

CENTRAL_DATABASE_URL=postgresql://user:pass@helix-central.xxx.rds.amazonaws.com:5432/helix_central

Common Tasks​

Adding a New Model to Central DB​

// prisma/central/schema.prisma

model NewModel {
  id        String   @id @default(uuid())
  tenantId  String   @map("tenant_id")
  name      String
  createdAt DateTime @default(now()) @map("created_at")

  tenant Tenant @relation(fields: [tenantId], references: [id])

  @@map("new_models")
}
npm run prisma:migrate:dev --name add_new_model

Adding a Field to Existing Model​

model Tenant {
  // ... existing fields
  newField String? // Make optional for existing records
}
npm run prisma:migrate:dev --name add_tenant_new_field

Creating a New Product Database​

# 1. Create schema in service
mkdir -p apps/new-service/prisma
touch apps/new-service/prisma/schema.prisma

# 2. Define schema
# See apps/kira-service/prisma/schema.prisma for example

# 3. Generate client
cd apps/new-service
npx prisma generate

# 4. Create initial migration
npx prisma migrate dev --name init

Best Practices​

DO ✅​

  • Always use migrations for schema changes
  • Write descriptive migration names
  • Test migrations in development first
  • Use nullable fields for backward compatibility
  • Index foreign keys
  • Use @@map() for custom table names
  • Keep central DB schema minimal (NO PII)
  • Document schema changes in commit messages

DON'T ❌​

  • Don't modify migration files after creation
  • Don't skip migrations in production
  • Don't store PII in central database
  • Don't create foreign keys across databases
  • Don't use prisma db push in production
  • Don't share database URLs in code
  • Don't forget to regenerate client after schema changes

Troubleshooting​

Client Out of Sync​

# Regenerate Prisma client
npm run prisma:generate

# If that fails, clear node_modules
rm -rf node_modules
npm install
npm run prisma:generate

Migration Conflicts​

# Reset development database (CAUTION: Data loss)
npx prisma migrate reset

# Or resolve manually:
# 1. Check migration history
npx prisma migrate status

# 2. Create new migration to resolve
npm run prisma:migrate:dev --name fix_conflict

Connection Issues​

# Test database connection
npx prisma db pull

# Check environment variables
echo $CENTRAL_DATABASE_URL

# Verify database is running
docker-compose ps

Reference​

Last Updated: 2025-10-21
Maintained By: CleverChain Platform Team