Database Schema Evolution

Patterns and practices for evolving database schemas in production applications, particularly adding explicit type classification to replace heuristic-based logic and integrating business intelligence requirements.

Schema Enhancement Patterns

Type Classification Implementation

Replacing implicit type detection with explicit classification:

// Before: Heuristic-based client classification
const clientType = inferTypeFromName(client.name)

// After: Explicit schema field
model Client {
  id        String @id @default(cuid())
  name      String
  type      ClientType // Explicit classification
  // ... other fields
}

enum ClientType {
  DISTRIBUTOR
  CHR
  RETAIL
  EXPORT
  PARTICULIER
}

Business Intelligence Schema Extensions

Adding forecast and objective tracking to existing sales systems:

// Core business data
model Forecast {
  id              String   @id @default(cuid())
  channel         String   // J.Milliet, CHR, Retail, etc.
  channelType     String   // Distributor, Direct, Export
  product         String   // Figuier, Cassis, etc.
  format          String   // 0.33L, 0.75L, 30L
  month           DateTime // Start of month, UTC
  forecastCA      Float    // Predicted revenue
  unitPrice       Float?   // Price per unit
  currency        String   @default("EUR")
  
  // Foreign key relationships
  catalogProduct  Product? @relation(fields: [catalogProductId], references: [id])
  catalogProductId String?
  
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
}

// Commercial objectives
model SalesObjective {
  id          String      @id @default(cuid())
  period      String      // Q1 2026, Q2 2026, etc.
  objectType  ObjectType  // CA_OBJECTIVE, ACCOUNT_OPENING
  target      Float       // Target value
  description String?     // Additional context
  createdAt   DateTime    @default(now())
}

Data Migration Strategies

Gradual Type Migration

When adding explicit type fields to replace heuristic logic:

1. Add nullable type field: Allow existing records to continue without types
2. Populate existing data: Run migration script to classify existing records
3. Update application logic: Switch from heuristic to explicit type checking
4. Make field required: After all data is classified, enforce non-null constraint

Excel Integration Patterns

Handling business data imports from Excel sources:

// Flexible product matching for forecast imports
const matchProductToActual = (forecastProduct: string): Product | null => {
  const patterns = FORECAST_TO_CATALOG_PRODUCT[forecastProduct.toLowerCase()]
  if (!patterns) return null
  
  return products.find(p => 
    patterns.some(pattern => 
      p.shortName.toLowerCase().includes(pattern)
    )
  )
}

// Channel mapping between forecast names and database entities
const mapForecastChannel = (channelName: string) => {
  // Direct channels map to client types
  if (channelName.includes('CHR')) return { type: 'CHR', isClientType: true }
  if (channelName.includes('Retail')) return { type: 'RETAIL', isClientType: true }
  
  // Named distributors map to specific clients
  return { name: channelName, isClientType: false }
}

Date Handling in Business Data

UTC-first approach for consistent time-based calculations:

// Excel serial date conversion
const serialDateToUTC = (serialDate: number): Date => {
  const utcDays = serialDate - EXCEL_EPOCH_OFFSET
  return new Date(utcDays * 86400000) // Milliseconds in a day
}

// Month-start normalization for consistent querying
const normalizeToMonthStart = (date: Date): string => {
  const year = date.getUTCFullYear()
  const month = String(date.getUTCMonth() + 1).padStart(2, '0')
  return `${year}-${month}-01T00:00:00.000Z`
}

Relationship Design Patterns

Flexible Foreign Keys

When integrating forecast data with existing catalogs:

// Optional relationship allows for unmapped forecast items
model Forecast {
  catalogProduct    Product? @relation(fields: [catalogProductId], references: [id])
  catalogProductId  String?
  
  // Always store original forecast identifiers
  product          String   // Original product name from forecast
  channel          String   // Original channel name
}

// Enables queries that work with both mapped and unmapped data
const forecastQuery = await prisma.forecast.groupBy({
  by: ['catalogProductId', 'product'],
  _sum: { forecastCA: true },
  where: {
    month: targetMonth,
    // Can filter by either mapped products or original names
    OR: [
      { catalogProductId: { not: null } },
      { product: { contains: searchTerm } }
    ]
  }
})

Performance Considerations

Indexing strategies for business intelligence queries:

model Forecast {
  // ... fields
  
  @@index([month, channel])           // Time-based channel analysis
  @@index([catalogProductId, month])   // Product performance over time
  @@index([channelType, month])       // Channel type comparisons
}

model Sale {
  // ... existing fields
  
  @@index([month, clientType])        // Align with forecast queries
  @@index([productId, month])         // Product actual vs forecast
}

Validation and Quality Assurance

Data Integrity Checks

Ensuring imported business data maintains consistency:

// Validate forecast data during import
const validateForecastEntry = (entry: ForecastEntry) => {
  const validations = [
    () => entry.forecastCA >= 0, // Non-negative revenue
    () => isValidDate(entry.month), // Valid month
    () => VALID_CURRENCIES.includes(entry.currency),
    () => entry.format.match(/^\d+(\.\d+)?[LML]?$/), // Format pattern
  ]
  
  return validations.every(check => check())
}

// Monitor matching success rates
const trackMatchingQuality = (forecasts: Forecast[]) => {
  const mapped = forecasts.filter(f => f.catalogProductId).length
  const total = forecasts.length
  const matchRate = (mapped / total) * 100
  
  console.log(`Product matching: ${mapped}/${total} (${matchRate.toFixed(1)}%)`)
  
  if (matchRate < 90) {
    console.warn('Low matching rate - review product mapping logic')
  }
}

Schema Documentation

Maintaining clear documentation for business stakeholders:

/**
 * Forecast Model
 * 
 * Stores monthly revenue forecasts by channel and product.
 * Imported from Excel planning files and matched against catalog.
 * 
 * Key relationships:
 * - catalogProduct: Links to Product when mapping succeeds
 * - channel: Original channel name from forecast (e.g., "J.Milliet")
 * - channelType: Classified type (Distributor, Direct, Export)
 * 
 * Query patterns:
 * - Compare with Sale records by month + product/channel
 * - Group by channelType for high-level analysis
 * - Track forecast accuracy over time
 */

See also

• Prisma ORM Patterns
• Business Intelligence Integration
• excel-data-processing
• Time Series Data Modeling