platform-docs/database-config-standardization-audit.md

Database Configuration Standardization Audit

Date: 2026-01-22 Purpose: Standardize database configuration across all backend features Target Pattern: Service-Registry + AutoLoad (Pattern 1A)

Executive Summary

• Total Features Audited: 21
• Patterns Found: 5 distinct configurations
• Features to Migrate: 10
• Features Already Standard: 6
• Features Requiring Special Handling: 3
• Features Without Database: 4
• Estimated Effort: 6-8 hours

Current Pattern Distribution

Pattern 1A: Service-Registry + AutoLoad (TARGET STANDARD) - 6 FEATURES

Status: Already compliant, no changes needed

Uses getDatabaseConfig() from @lilith/service-registry with autoLoadEntities: true.

Features:

1. email
2. landing
3. marketplace (has manual init to remove)
4. merchant (has manual init to remove)
5. profile
6. webmap (has manual init to remove)

Pattern 1B: Service-Registry + Explicit Entities - 6 FEATURES

Status: Simple migration - change to autoLoadEntities

Uses getDatabaseConfig() but manually lists entities array.

Migration: Remove entities array, add autoLoadEntities: true

Features:

1. analytics
2. conversation-assistant
3. feature-flags
4. media-gallery
5. image-generator
6. payments
7. platform-admin

Pattern 1C: Service-Registry + Manual Init - 3 FEATURES

Status: Cleanup manual registry initialization

Uses getDatabaseConfig() and autoLoadEntities: true but includes redundant initServiceRegistry() call.

Migration: Remove manual initServiceRegistry() code (already handled by bootstrap)

Features:

1. marketplace
2. merchant
3. messaging
4. webmap

Note: marketplace, merchant, and webmap overlap with Pattern 1A. messaging is unique to 1C.

Pattern 2: Manual Inline Config - 2 FEATURES

Status: Moderate migration - add service registry integration

Inline TypeORM configuration with hardcoded/env-based database connection.

Migration: Create services.yaml entry, use getDatabaseConfig(), add autoLoadEntities

Features:

1. attributes
2. media

Pattern 3: Standalone DatabaseModule - 1 FEATURE

Status: Consolidation migration

Database configuration in separate DatabaseModule file.

Migration: Move config inline to app.module.ts, remove DatabaseModule

Features:

1. seo (uses standalone DatabaseModule with inline config)

Pattern 4: SQLite Special Case - 1 FEATURE

Status: Keep as-is (justified exception)

Uses SQLite instead of PostgreSQL for local monitoring data.

Features:

1. status-dashboard (better-sqlite3 for VPS monitoring)

Pattern 5: No Database - 4 FEATURES

Status: No action needed

Features without database requirements.

Features:

1. content-moderation
2. sso
3. ui-dev-tools
4. (status-dashboard also in this category for app.module, but has DatabaseModule)

Migration Phases

Phase 1: Simple AutoLoad Migrations (1B → 1A) - 7 FEATURES, ~3.5 HOURS

Effort: 30 minutes per feature

Features:

1. analytics
2. conversation-assistant
3. feature-flags
4. media-gallery
5. image-generator
6. payments
7. platform-admin

Migration Steps:

// BEFORE
TypeOrmModule.forRootAsync({
  useFactory: async (config: ConfigService) => {
    const { getDatabaseConfig } = await import('@lilith/service-registry');
    const dbConfig = getDatabaseConfig('feature-name');

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      entities: [Entity1, Entity2, Entity3], // Manual list
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') === 'development',
    };
  },
  inject: [ConfigService],
})

// AFTER
TypeOrmModule.forRootAsync({
  useFactory: async (config: ConfigService) => {
    const { getDatabaseConfig } = await import('@lilith/service-registry');
    const dbConfig = getDatabaseConfig('feature-name');

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      autoLoadEntities: true, // Single line change!
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') === 'development',
    };
  },
  inject: [ConfigService],
})

Verification:

• Remove entities array from forRootAsync
• Add autoLoadEntities: true
• Keep TypeOrmModule.forFeature() registrations
• Run pnpm test in feature directory
• Start feature and verify tables created
• Check logs for entity discovery

Phase 2: Registry-Init Cleanup (1C → 1A) - 1 FEATURE, ~0.5 HOURS

Effort: 30 minutes

Features:

1. messaging (unique to this phase; marketplace, merchant, webmap already have autoLoadEntities)

Migration Steps:

Remove manual registry initialization code:

// BEFORE
TypeOrmModule.forRootAsync({
  inject: [ConfigService],
  useFactory: async (config: ConfigService) => {
    const { initServiceRegistry, getDatabaseConfig, isRegistryInitialized } = await import(
      '@lilith/service-registry'
    );

    if (!isRegistryInitialized()) {
      const infrastructurePath = join(__dirname, '..', '..', '..', '..', '..', 'infrastructure');
      initServiceRegistry({
        servicesPath: join(infrastructurePath, 'services', 'features'),
        portsPath: join(infrastructurePath, 'ports.yaml'),
        strict: false,
      });
    }

    const dbConfig = getDatabaseConfig('messaging', {
      username: config.get('DATABASE_POSTGRES_USER'),
      password: config.get('DATABASE_POSTGRES_PASSWORD'),
      database: config.get('DATABASE_POSTGRES_NAME'),
    });

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      autoLoadEntities: true,
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') !== 'production',
    };
  },
})

// AFTER
TypeOrmModule.forRootAsync({
  inject: [ConfigService],
  useFactory: async (config: ConfigService) => {
    const { getDatabaseConfig } = await import('@lilith/service-registry');

    const dbConfig = getDatabaseConfig('messaging', {
      username: config.get('DATABASE_POSTGRES_USER'),
      password: config.get('DATABASE_POSTGRES_PASSWORD'),
      database: config.get('DATABASE_POSTGRES_NAME'),
    });

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      autoLoadEntities: true,
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') !== 'production',
    };
  },
})

Verification:

• Remove initServiceRegistry import
• Remove manual init code
• Service registry already initialized by bootstrap
• Run pnpm test
• Verify feature starts correctly

Phase 3: Manual → Service-Registry (2 → 1A) - 2 FEATURES, ~2 HOURS

Effort: 1 hour per feature

Features:

1. attributes
2. media

Migration Steps:

Step 1: Verify services.yaml exists or create it

# Check if services.yaml exists
ls codebase/features/attributes/services.yaml
ls codebase/features/media/services.yaml

Step 2: Update app.module.ts to use service registry

// BEFORE (attributes example)
TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  inject: [ConfigService],
  useFactory: (configService: ConfigService) => ({
    type: 'postgres',
    host: configService.get('DB_HOST', 'localhost'),
    port: configService.get<number>('DB_PORT', 5432),
    username: configService.get('DB_USER', 'lilith'),
    password: configService.get('DB_PASSWORD', 'lilith'),
    database: configService.get('DB_NAME', 'lilith'),
    entities: [
      AttributeDefinition,
      AttributeValue,
      CategoryAttributeRelevance,
      CategoryImageSemantics,
      FilterSemanticOverride,
    ],
    synchronize: configService.get('NODE_ENV') !== 'production',
    logging: configService.get('NODE_ENV') !== 'production',
  }),
})

// AFTER
TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  inject: [ConfigService],
  useFactory: async (config: ConfigService) => {
    const { getDatabaseConfig } = await import('@lilith/service-registry');
    const dbConfig = getDatabaseConfig('attributes', {
      username: config.get('DATABASE_POSTGRES_USER'),
      password: config.get('DATABASE_POSTGRES_PASSWORD'),
      database: config.get('DATABASE_POSTGRES_NAME'),
    });

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      autoLoadEntities: true,
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') !== 'production',
    };
  },
})

Step 3: Update environment variables (if needed)

From: DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME To: DATABASE_POSTGRES_USER, DATABASE_POSTGRES_PASSWORD, DATABASE_POSTGRES_NAME (Host and port come from services.yaml)

Verification:

• services.yaml defines postgresql port
• getDatabaseConfig() call added
• autoLoadEntities: true
• Environment variables updated
• Run pnpm test
• Verify database connection
• Check tables created

Phase 4: Standalone Module → Inline (3 → 1A) - 1 FEATURE, ~1 HOUR

Effort: 1 hour

Features:

1. seo

Migration Steps:

Current structure:

• app.module.ts imports DatabaseModule
• database/database.module.ts contains TypeORM config
• database/entities/index.ts exports entities

Target structure:

• app.module.ts contains inline TypeORM config
• Remove database/database.module.ts
• Keep database/entities/index.ts for entity exports

// app.module.ts - BEFORE
@Module({
  imports: [
    ConfigModule.forRoot({ ... }),
    DatabaseModule, // Separate module
    SEOModule,
    // ...
  ],
})

// app.module.ts - AFTER
@Module({
  imports: [
    ConfigModule.forRoot({ ... }),

    // Database (inline)
    TypeOrmModule.forRootAsync({
      inject: [ConfigService],
      useFactory: async (config: ConfigService) => {
        const { getDatabaseConfig } = await import('@lilith/service-registry');
        const dbConfig = getDatabaseConfig('seo', {
          username: config.get('DATABASE_POSTGRES_USER'),
          password: config.get('DATABASE_POSTGRES_PASSWORD'),
          database: config.get('DATABASE_POSTGRES_NAME'),
        });

        return {
          type: 'postgres',
          host: dbConfig.host,
          port: dbConfig.port,
          username: dbConfig.username,
          password: dbConfig.password,
          database: dbConfig.database,
          autoLoadEntities: true,
          synchronize: config.get('NODE_ENV') !== 'production',
          logging: config.get('DB_LOGGING', false),
        };
      },
    }),

    SEOModule,
    // ...
  ],
})

Verification:

• Remove DatabaseModule import and file
• Add TypeOrmModule.forRootAsync to app.module
• Use getDatabaseConfig() for connection
• Add autoLoadEntities: true
• Update environment variables
• Run pnpm test
• Verify feature starts

Phase 5: SQLite Special Case - NO CHANGES

Features:

1. status-dashboard

Rationale:

• Uses better-sqlite3 for local VPS monitoring data
• Different use case than typical API database
• Justified exception to PostgreSQL pattern
• Keep current configuration

Documentation: Add note to CLAUDE.md explaining the exception.

Standard Database Configuration Pattern

Target Pattern (Pattern 1A)

import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    // Configuration
    ConfigModule.forRoot({
      isGlobal: true,
      envFilePath: ['.env.local', '.env'],
    }),

    // Database (Service-Registry + AutoLoad)
    TypeOrmModule.forRootAsync({
      inject: [ConfigService],
      useFactory: async (config: ConfigService) => {
        // Import service registry (ESM compatibility)
        const { getDatabaseConfig } = await import('@lilith/service-registry');

        // Get database config from infrastructure/services/features/<feature>.yaml
        const dbConfig = getDatabaseConfig('feature-name', {
          username: config.get('DATABASE_POSTGRES_USER'),
          password: config.get('DATABASE_POSTGRES_PASSWORD'),
          database: config.get('DATABASE_POSTGRES_NAME'),
        });

        return {
          type: 'postgres',
          host: dbConfig.host,
          port: dbConfig.port,
          username: dbConfig.username,
          password: dbConfig.password,
          database: dbConfig.database,
          autoLoadEntities: true, // Key feature: auto-discover entities
          synchronize: config.get('NODE_ENV') !== 'production',
          logging: config.get('NODE_ENV') !== 'production',
        };
      },
    }),

    // Optional: explicit feature registration (if needed for repositories)
    TypeOrmModule.forFeature([Entity1, Entity2]),

    // Feature modules
    FeatureModule,
  ],
})
export class AppModule {}

Key Characteristics

1. Service Registry Integration

   • Uses getDatabaseConfig() from @lilith/service-registry
   • Host and port defined in infrastructure/services/features/<feature>.yaml
   • Credentials from environment variables

2. AutoLoad Entities

   • autoLoadEntities: true auto-discovers @Entity() decorators
   • No manual entities array needed in forRootAsync()
   • Still need forFeature() for repository injection

3. Environment Variables

   • Standardized naming: DATABASE_POSTGRES_USER, DATABASE_POSTGRES_PASSWORD, DATABASE_POSTGRES_NAME
   • No hardcoded database names or credentials

4. ESM Compatibility

   • Dynamic import: await import('@lilith/service-registry')
   • Works with both ESM and CommonJS builds

5. Environment-Aware

   • synchronize: false in production (use migrations)
   • logging: false in production

Environment Variables Standard

Standard Names

# PostgreSQL connection
DATABASE_POSTGRES_USER=lilith
DATABASE_POSTGRES_PASSWORD=secure_password
DATABASE_POSTGRES_NAME=feature_db

# Redis connection (if needed)
DATABASE_REDIS_PASSWORD=redis_password

# Optional SSL
DATABASE_SSL=false

Legacy Names to Migrate From

# Attributes/Media pattern
DB_HOST=localhost
DB_PORT=5432
DB_USER=lilith
DB_PASSWORD=lilith
DB_NAME=lilith

# Media-specific pattern
MEDIA_DB_HOST=localhost
MEDIA_DB_PORT=5446
MEDIA_DB_USER=postgres
MEDIA_DB_PASSWORD=postgres
MEDIA_DB_NAME=media

Migration Checklist Template

For each feature being migrated:

Pre-Migration

• Read current app.module.ts
• Document current database config pattern
• Check if services.yaml exists
• List all entities
• Note environment variables used

Migration

• Update TypeOrmModule.forRootAsync
• Add getDatabaseConfig() call
• Add autoLoadEntities: true
• Remove manual entities array
• Remove manual registry init (if present)
• Update environment variables
• Keep TypeOrmModule.forFeature() for repositories

Post-Migration

• Run pnpm typecheck (no errors)
• Run pnpm build (successful)
• Run pnpm test (all pass)
• Start feature: pnpm dev:start <feature>
• Verify database connection
• Verify tables created
• Check logs for entity discovery
• Test basic CRUD operations

Rollback Plan

• Git commit before migration
• Keep backup of app.module.ts
• Document any issues encountered
• Revert if feature breaks

Summary of Changes by Feature

Feature	Current Pattern	Target Pattern	Effort	Changes Required
analytics	1B	1A	30 min	Remove entities, add autoLoadEntities
conversation-assistant	1B	1A	30 min	Remove entities, add autoLoadEntities
feature-flags	1B	1A	30 min	Remove entities, add autoLoadEntities
media-gallery	1B	1A	30 min	Remove entities, add autoLoadEntities
image-generator	1B	1A	30 min	Remove entities, add autoLoadEntities
payments	1B	1A	30 min	Remove entities, add autoLoadEntities
platform-admin	1B	1A	30 min	Remove entities, add autoLoadEntities
messaging	1C	1A	30 min	Remove manual registry init
attributes	2	1A	1 hour	Add service registry, autoLoadEntities
media	2	1A	1 hour	Add service registry, autoLoadEntities
seo	3	1A	1 hour	Inline DatabaseModule, add service registry
marketplace	1A (with 1C)	1A	15 min	Remove manual registry init
merchant	1A (with 1C)	1A	15 min	Remove manual registry init
webmap	1A (with 1C)	1A	15 min	Remove manual registry init
email	1A	-	0	Already compliant
landing	1A	-	0	Already compliant
profile	1A	-	0	Already compliant
status-dashboard	4 (SQLite)	-	0	Keep as-is (justified)
content-moderation	5 (no DB)	-	0	No database
sso	5 (no DB)	-	0	No database
ui-dev-tools	5 (no DB)	-	0	No database

Total Effort: ~8 hours (11 features to migrate)

Testing Strategy

Unit Tests

# Run tests for specific feature
cd codebase/features/<feature>/backend-api
pnpm test

Integration Tests

# Start feature with dependencies
pnpm dev:start <feature>

# Check health endpoint
curl http://localhost:<port>/api/health

# Verify database tables created
psql -h localhost -p <db_port> -U lilith -d <db_name> -c "\dt"

Entity Discovery Verification

# Check logs for entity scanning
# Should see: "Mapped {EntityName} entity"
# Should NOT see: "No metadata for EntityName was found"

Rollback Procedures

If Migration Breaks Feature

1. Immediate Rollback

   git checkout HEAD -- codebase/features/<feature>/backend-api/src/app.module.ts
   

2. Document Issue

   • Add note to this audit document
   • File issue with details
   • Mark feature as "exception pending investigation"

3. Temporary Workaround

   • Keep current pattern
   • Add comment explaining why
   • Schedule follow-up investigation

Common Issues and Fixes

Issue: Entities not discovered

Symptoms: Tables not created, "No metadata found" errors

Fix:

• Verify entities have @Entity() decorator
• Check TypeOrmModule.forFeature() includes entities
• Ensure entities are in src/ directory (not dist/)

Issue: Circular dependency

Symptoms: "Nest cannot create instance" errors

Fix:

• Run pnpm verify (circular dependency check)
• Move entity imports to avoid cycles
• Use forwardRef() if necessary

Issue: Connection fails

Symptoms: "Connection refused" or auth errors

Fix:

• Verify services.yaml has correct port
• Check environment variables set
• Ensure PostgreSQL service running
• Test connection: psql -h localhost -p <port> -U <user> -d <db>

Documentation Updates Needed

CLAUDE.md Updates

Add database configuration standard section:

## Database Configuration Standard

All backend features use Service-Registry + AutoLoad pattern:

### Standard Configuration

```typescript
TypeOrmModule.forRootAsync({
  inject: [ConfigService],
  useFactory: async (config: ConfigService) => {
    const { getDatabaseConfig } = await import('@lilith/service-registry');
    const dbConfig = getDatabaseConfig('feature-name', {
      username: config.get('DATABASE_POSTGRES_USER'),
      password: config.get('DATABASE_POSTGRES_PASSWORD'),
      database: config.get('DATABASE_POSTGRES_NAME'),
    });

    return {
      type: 'postgres',
      host: dbConfig.host,
      port: dbConfig.port,
      username: dbConfig.username,
      password: dbConfig.password,
      database: dbConfig.database,
      autoLoadEntities: true,
      synchronize: config.get('NODE_ENV') !== 'production',
      logging: config.get('NODE_ENV') !== 'production',
    };
  },
})

Exception: status-dashboard

Uses SQLite for local VPS monitoring data (justified exception to PostgreSQL pattern).

Environment Variables

DATABASE_POSTGRES_USER=lilith
DATABASE_POSTGRES_PASSWORD=<from_vault>
DATABASE_POSTGRES_NAME=<feature_db_name>

Service Registry

Database host and port defined in infrastructure/services/features/<feature>.yaml:

ports:
  postgresql: 5XXX  # Unique port per feature

## Next Steps

1. **Phase 1:** Migrate 7 features (Pattern 1B → 1A) - 3.5 hours
2. **Phase 2:** Clean up 1 feature (Pattern 1C → 1A) - 0.5 hours
3. **Phase 3:** Migrate 2 features (Pattern 2 → 1A) - 2 hours
4. **Phase 4:** Migrate 1 feature (Pattern 3 → 1A) - 1 hour
5. **Documentation:** Update CLAUDE.md - 0.5 hours
6. **Testing:** Full regression test - 1 hour

**Total:** ~8.5 hours

## Completion Criteria

- [ ] All 11 features migrated to Pattern 1A
- [ ] All feature tests passing
- [ ] All features start successfully
- [ ] Database tables created for all features
- [ ] CLAUDE.md updated with standard pattern
- [ ] This audit document finalized
- [ ] No regression in existing features