Skip to main content

Enhanced AI Report Generation - Integration Guide

Overview​

This guide explains how to integrate the enhanced AI report generation system that leverages your existing TableMetadata infrastructure for more accurate and robust report generation.

Key Components​

1. Schema Context Service (services/ai/schemaContextService.js)​

  • Reads from your TableMetadata collection
  • Provides complete database schema context to AI
  • Caches schema information for performance
  • Formats schema for AI prompts

2. Enhanced Report AI Service (services/reportaiservice-enhanced.js)​

  • Improved version of the original ReportAIService
  • Uses pattern matching for common queries
  • Stores successful queries for reuse
  • Better error handling and fallbacks
  • Schema-aware field validation

3. Query Validation Service (services/ai/queryValidationService.js)​

  • Validates MongoDB aggregation pipelines
  • Checks field references against schema
  • Provides optimization suggestions
  • Test runs queries with small samples

Integration Steps​

Step 1: Update Report Routes​

Update your backend/routes/reports.js to use the enhanced service:

// At the top of the file
const ReportAIService = require('../services/reportaiservice-enhanced'); // Use enhanced version
const queryValidationService = require('../services/ai/queryValidationService');

// In the /generate endpoint, add validation
router.post('/generate', auth, async (req, res) => {
  try {
    const { prompt, collection, filters } = req.body;
    
    if (!prompt || !collection) {
      return res.status(400).json({ message: 'Prompt and collection are required' });
    }
    
    // ... existing code ...
    
    // Generate pipeline
    const pipeline = await ReportAIService.generateAggregationPipeline(prompt, collection);
    
    // Validate pipeline before execution
    const validation = await queryValidationService.validatePipeline(pipeline, collection);
    
    if (!validation.isValid) {
      return res.status(400).json({ 
        message: 'Invalid query generated', 
        errors: validation.errors,
        warnings: validation.warnings 
      });
    }
    
    // Execute the pipeline
    const data = await model.aggregate(pipeline);
    
    // ... rest of the code ...
  } catch (error) {
    // ... error handling ...
  }
});

Step 2: Update Reporting Service​

In backend/services/reportingservice.js, update the imports:

const ReportAIService = require('./reportaiservice-enhanced');
const schemaContextService = require('./ai/schemaContextService');

Step 3: Add Schema Refresh Endpoint (Optional)​

Add an admin endpoint to refresh the schema cache:

// In routes/reports.js
router.post('/admin/refresh-schema', auth, requireAdmin, async (req, res) => {
  try {
    schemaContextService.clearCache();
    await schemaContextService.getCompleteSchemaContext();
    res.json({ message: 'Schema cache refreshed successfully' });
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

Step 4: Update Frontend to Show Validation Info​

In your frontend ReportForm.tsx, you can show validation warnings:

// When generating a report
const generateReport = async () => {
  try {
    const response = await reportService.generateReport(prompt, name);
    
    // Check for warnings in response
    if (response.warnings && response.warnings.length > 0) {
      showWarning(`Report generated with warnings: ${response.warnings.join(', ')}`);
    }
    
    // ... rest of the code ...
  } catch (error) {
    // ... error handling ...
  }
};

Configuration​

Environment Variables​

No new environment variables needed - uses existing MongoDB connection.

Performance Tuning​

  1. Schema Cache Duration: Adjust in schemaContextService.js:

    this.cacheExpiry = 60 * 60 * 1000; // 1 hour (adjust as needed)

  2. Query Pattern Library: Add custom patterns in reportaiservice-enhanced.js:

    this.queryPatternLibrary.set('custom_pattern', {
      pattern: /your pattern regex/,
      template: (params) => [ /* your pipeline */ ]
    });

  3. Successful Query Cache Size: Adjust in reportaiservice-enhanced.js:

    if (this.successfulQueries.size > 100) { // Increase if needed

Testing​

Run the Test Suite​

cd backend
node test-enhanced-report-ai.js

Test Individual Components​

// Test schema context
const context = await schemaContextService.getCompleteSchemaContext();
console.log(context);

// Test query generation
const pipeline = await enhancedReportAI.generateAggregationPipeline(
  "Show servers by memory",
  "cis"
);
console.log(pipeline);

// Test validation
const validation = await queryValidationService.validatePipeline(
  pipeline,
  "cis"
);
console.log(validation);

Benefits​

  1. Higher Success Rate: AI has complete schema context
  2. Better Validation: Queries are validated before execution
  3. Performance: Pattern matching and query caching
  4. Error Prevention: Schema-aware field validation
  5. Fallback Strategies: Always returns meaningful results
  6. Learning System: Stores successful queries for reuse

Troubleshooting​

Common Issues​

  1. "Field not found" warnings

    • Check if TableMetadata is up to date
    • Run schema scan: await tableMetadataService.scanCollections()

  2. Slow query generation

    • Check schema cache is working
    • Reduce AI temperature for faster responses

  3. Invalid pipelines

    • Check validation errors
    • Review generated pipeline structure
    • Test with simpler queries first

Debug Mode​

Enable detailed logging:

// In enhanced report AI service
logger.level = 'debug'; // Show all logs

Best Practices​

  1. Keep TableMetadata Updated: Run schema scans after model changes
  2. Add Common Patterns: Extend pattern library for your use cases
  3. Monitor Success Rate: Track which queries work well
  4. Use Descriptive Prompts: More context = better results
  5. Test Complex Queries: Validate before production use

Example Queries That Work Well​

  • "Show all servers grouped by memory capacity"
  • "Count CIs by type in a bar chart"
  • "Display servers with more than 16GB RAM"
  • "List top 10 users by login count"
  • "Show tasks created this month"
  • "Group incidents by priority and status"
  • "Display average response time by day"

Migration from Old System​

  1. Keep both services running initially
  2. A/B test with enhanced service
  3. Monitor success rates
  4. Gradually migrate all reports
  5. Remove old service when stable