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

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

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

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

```typescript
// 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)

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

<!-- wordcount:off -->
| 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 |
<!-- wordcount:on -->

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

## Testing Strategy

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

### Integration Tests
```bash
# 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
```bash
# 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**
   ```bash
   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:

```markdown
## 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

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

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