// Slow query discovered in production
SELECT * FROM orders WHERE customer_id = ? AND created_at > ?;

// Quick fix: throw an index at it
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at);

// Result: Works for now, but no tenant isolation, no monitoring, no lifecycle management

// Schema-first approach with tenant safety
export const OrderIndexSchema = z.object({
  tenant_id: z.string().uuid(),
  customer_id: z.string().uuid(),
  created_at: z.date(),
  status: z.enum(['pending', 'completed', 'cancelled'])
});

// Migration with proper naming and coverage
CREATE INDEX CONCURRENTLY idx_orders_tenant_customer_created 
ON orders(tenant_id, customer_id, created_at) 
INCLUDE (status, total_amount)
WHERE deleted_at IS NULL;

// Before: Shared indexes with filter-based isolation
GET /orders/_search
{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "tenant-123" } }
      ]
    }
  }
}

// After: Routing-based isolation with proper templates
@Injectable()
export class OrderSearchService {
  async searchOrders(query: string, tenantId: string) {
    return this.elasticsearchClient.search({
      index: 'orders_current',
      routing: tenantId, // Physical separation
      body: {
        query: {
          bool: {
            must: [
              { match: { description: query } },
              { term: { tenant_id: tenantId } } // Defense in depth
            ]
          }
        }
      }
    });
  }
}

// Built-in performance tracking
@Injectable()
export class DatabaseService {
  async executeQuery(sql: string, params: any[], tenantId: string) {
    const start = Date.now();
    try {
      const result = await this.pool.query(sql, params);
      
      this.logger.info('Query executed', {
        op: 'query',
        entity: this.extractTableName(sql),
        tenantId,
        durationMs: Date.now() - start,
        rowCount: result.rowCount
      });
      
      return result;
    } catch (error) {
      this.alertService.fireAlert('query_failed', { sql, tenantId, error });
      throw error;
    }
  }
}

# Install dependencies
npm install @nestjs/elasticsearch @nestjs/bull zod pg typeorm

# Update tsconfig.json
{
  "compilerOptions": {
    "strict": true,
    "strictNullChecks": true,
    "noImplicitAny": true
  }
}

// tenant-context.provider.ts
@Injectable({ scope: Scope.REQUEST })
export class TenantContext {
  private tenantId: string;

  setTenantId(tenantId: string) {
    this.tenantId = tenantId;
  }

  getTenantId(): string {
    if (!this.tenantId) {
      throw new Error('Tenant context not initialized');
    }
    return this.tenantId;
  }
}

// tenant.guard.ts
@Injectable()
export class TenantGuard implements CanActivate {
  constructor(private tenantContext: TenantContext) {}

  canActivate(context: ExecutionContext): boolean {
    const request = context.switchToHttp().getRequest();
    const tenantId = request.headers['x-tenant-id'];
    
    if (!tenantId) {
      throw new UnauthorizedException('Tenant ID required');
    }
    
    this.tenantContext.setTenantId(tenantId);
    return true;
  }
}

// index-management.service.ts
@Injectable()
export class IndexManagementService {
  constructor(
    private dataSource: DataSource,
    private elasticsearchClient: ElasticsearchService,
    private tenantContext: TenantContext
  ) {}

  async createTenantSafeIndex(
    tableName: string,
    columns: string[],
    options: IndexOptions = {}
  ) {
    const tenantId = this.tenantContext.getTenantId();
    const indexName = `idx_${tableName}_${columns.join('_')}_tenant`;
    
    const sql = `
      CREATE INDEX CONCURRENTLY ${indexName}
      ON ${tableName}(tenant_id, ${columns.join(', ')})
      ${options.include ? `INCLUDE (${options.include.join(', ')})` : ''}
      ${options.where ? `WHERE ${options.where}` : ''}
    `;

    const start = Date.now();
    await this.dataSource.query(sql);
    
    this.logger.info('Index created', {
      op: 'create_index',
      entity: tableName,
      tenantId,
      indexName,
      durationMs: Date.now() - start
    });
  }
}

// elasticsearch-setup.service.ts
@Injectable()
export class ElasticsearchSetupService implements OnModuleInit {
  async onModuleInit() {
    await this.createIndexTemplate();
  }

  private async createIndexTemplate() {
    await this.elasticsearchClient.indices.putTemplate({
      name: 'orders_template',
      body: {
        index_patterns: ['orders_v*'],
        settings: {
          number_of_shards: 3,
          number_of_replicas: 1,
          'index.routing.allocation.require.storage_type': 'ssd'
        },
        mappings: {
          properties: {
            tenant_id: { type: 'keyword' },
            customer_id: { type: 'keyword' },
            description: { 
              type: 'text', 
              analyzer: 'standard' 
            },
            created_at: { type: 'date' },
            total_amount: { 
              type: 'scaled_float', 
              scaling_factor: 100 
            }
          }
        },
        aliases: {
          'orders_current': {}
        }
      }
    });
  }
}

// monitoring.service.ts
@Injectable()
export class MonitoringService {
  constructor(private logger: Logger) {}

  async checkIndexHealth() {
    // PostgreSQL index analysis
    const missingIndexes = await this.dataSource.query(`
      SELECT schemaname, tablename, attname, n_distinct, correlation
      FROM pg_stats
      WHERE schemaname = 'public'
      AND n_distinct > 100
      AND correlation < 0.1
    `);

    if (missingIndexes.length > 0) {
      this.logger.warn('Potential missing indexes detected', {
        candidates: missingIndexes
      });
    }

    // Elasticsearch cluster health
    const clusterHealth = await this.elasticsearchClient.cluster.health();
    if (clusterHealth.body.status !== 'green') {
      this.logger.error('Elasticsearch cluster unhealthy', {
        status: clusterHealth.body.status
      });
    }
  }
}