ArchitectureAdrADR-0025: Domain-Aware SQL Generation Architecture

ADR-0025: Domain-Aware SQL Generation Architecture

ADR-0025: Domain-Aware SQL Generation Architecture

Status: Accepted
Date: 2026-02-03
Deciders: ant11 (Architect), cl1 (Implementer), George (Product Owner)
Technical Story: Multi-brand support for SQL generation (gaming, automotive, weather)

Context

Jorvis initially supported only gaming analytics (iGaming brands like SPW, IRS, Winpot). With the addition of jorvis_auto (cars inventory) and future domains, we need a clean architecture for:

  1. Routing questions to appropriate database schemas
  2. Injecting domain-specific business context
  3. Avoiding gaming-specific logic for non-gaming brands

Current State (Before Refactor)

  • Hardcoded gaming table maps in OptimizedAiService
  • Business context assumed gaming domain
  • Performance tables always included in schema
  • No brand-to-domain mapping

Decision Drivers

  1. Extensibility — Easy to add new domains (weather, finance, etc.)
  2. Backwards Compatibility — Gaming behavior unchanged
  3. Separation of Concerns — Domain logic isolated from LLM code
  4. Maintainability — Config-driven, not code-driven

Decision

Implement a config-driven domain routing architecture with brand-to-domain mapping.

Implementation (Completed by cl1)

1. Configuration Layer

File: analytics-platform/config/schema-config.json

{
  "domains": {
    "gaming": {
      "brands": ["default", "irs", "spw", "winpot"],
      "tables": ["dim_performance", "dim_crm_player", "agg_game_round"],
      "defaultSchema": "dim_irs"
    },
    "automotive": {
      "brands": ["cars", "auto", "jorvis_auto"],
      "tables": ["vehicles"],
      "defaultSchema": "cars"
    }
  }
}

2. DynamicSchemaService Enhancements

// New methods
lookupFromConfig(tableName: string, brand?: string): TableConfig | undefined
resolveSchemaKey(brand?: string): string
getTableDescription(tableName: string, brand?: string): string
getTableColumns(tableName: string, brand?: string): string[]
getAvailableMetrics(brand?: string): string[]

3. BusinessContextService Brand-Aware

// Map-based context storage
private contexts: Map<string, BusinessContext> = new Map();

// Domain resolution
private resolveDomainKey(brand?: string): string {
  if (!brand) return 'gaming';
  const lower = brand.toLowerCase();
  if (['cars', 'auto', 'jorvis_auto'].includes(lower)) return 'automotive';
  return 'gaming';
}

// Brand-aware context
getFormattedContext(brand?: string): string {
  const domain = this.resolveDomainKey(brand);
  if (domain !== 'gaming') return ''; // Non-gaming has no business context
  return this.formatGamingContext();
}

4. OptimizedAiService Brand Gating

private isGamingBrand(brand?: string): boolean {
  if (!brand) return true; // Default to gaming for backwards compatibility
  const gamingBrands = new Set(['default', 'irs', 'winpot', 'spw']);
  return gamingBrands.has(brand.toLowerCase());
}

// Usage in table identification
if (isGaming) {
  const performanceTables = this.findTablesContaining('performance');
  tables.push(...performanceTables);
}

5. Call Site Updates

// conversation-question.service.ts
const result = await this.optimizedAiService.generateOptimizedSql(
  numberOfTries,
  question,
  fullSchema,
  conversationHistory,
  currentDateTime,
  language,
  providerConfig,
  effectiveBrand  // ← NEW: passes brand through pipeline
);

// prompt-manager.service.ts
const businessContext = this.businessContextService.getFormattedContext(brand);

Architecture Diagram

flowchart TB
    Q[User Question] --> R{Brand Router}
    R -->|gaming| G[Gaming Domain]
    R -->|cars| C[Automotive Domain]
    R -->|weather| W[Weather Domain]
    
    G --> GS[dim_irs/dim_spw Schema]
    G --> GC[Gaming Business Context]
    G --> GP[Performance Tables]
    
    C --> CS[cars Schema]
    C --> CC[Empty Context]
    C --> CP[vehicles Table]
    
    GS & GC & GP --> SQL[SQL Generator]
    CS & CC & CP --> SQL
    
    SQL --> LLM[LLM]

Consequences

Positive

  • Clean Separation — Gaming logic isolated behind isGamingBrand() checks
  • Extensible — New domains added via config, minimal code changes
  • Backwards Compatible — brand=undefined defaults to gaming behavior
  • Testable — Domain routing easily unit-tested

Negative

  • Slight Complexity — Brand parameter flows through many functions
  • Pattern Debt — isGamingBrand() inline set should migrate to config in v0.9

Neutral

  • Config Duplication — Domain knowledge exists in both config and services
  • Test Coverage — 27 new tests added (1192 total)

Migration Path (v0.9)

// Current (v0.8) - inline brand check
private isGamingBrand(brand?: string): boolean {
  const gamingBrands = new Set(['default', 'irs', 'winpot']);
  return gamingBrands.has(brand?.toLowerCase() ?? 'default');
}

// Future (v0.9) - config-driven
private isGamingBrand(brand?: string): boolean {
  return this.schemaConfigService.getDomainForBrand(brand) === 'gaming';
}

Verification

  • ✅ 27 new unit tests for domain routing
  • ✅ 1192 total tests passing
  • ✅ Commit f50f67b pushed to develop
  • ⏳ Production verification pending

References