Check Batch Status

Command Signature

php artisan specific-viewpoint:check-batch-status {--chunk=10}

Purpose

The specific-viewpoint:check-batch-status command monitors OpenAI batch processing operations, retrieves completed results, and stores the classified viewpoint associations in the database. This command identifies pending batch logs, checks their status with OpenAI's API, downloads completed classification results, and processes them into structured database records for use in trend analysis and customer insight generation. The command ensures continuous monitoring and timely processing of AI classification results.

Sequence Diagram

Step 1: Command Initialization and Log Retrieval

sequenceDiagram
    participant System
    participant StatusCommand as specific-viewpoint:check-batch-status
    participant BatchLogRepo as BatchJobViewpointLogRepository
    participant AnalyzerDB[(gb_analyzer.batch_jobs_vp_logs)]
    participant Logger
    participant Slack
    
    Note over System,Slack: Command Initialization (Every 5 Minutes)
    
    rect rgb(200, 255, 200)
    Note right of System: Happy Case - Command Startup
    System->>StatusCommand: Execute Command
    StatusCommand->>Logger: Log command start with chunk parameter
    
    StatusCommand->>BatchLogRepo: getPendingBatchLogs(chunk_limit)
    BatchLogRepo->>AnalyzerDB: Query WHERE status = 1 (Pending) LIMIT chunk
    AnalyzerDB-->>BatchLogRepo: Return pending batch logs
    BatchLogRepo-->>StatusCommand: Return pending batch logs collection
    StatusCommand->>Logger: Log found pending logs count
    end
    
    rect rgb(255, 200, 200)
    Note right of System: Error Handling
    rect rgb(255, 230, 230)
    alt Database Connection Error
        BatchLogRepo->>Logger: Log database connection error
        BatchLogRepo->>Slack: Send database error notification
    else No Pending Logs
        StatusCommand->>Logger: Log no logs to monitor
    end
    end
    end

Step 2: Job Dispatch and Status Monitoring Setup

sequenceDiagram
    participant StatusCommand as specific-viewpoint:check-batch-status
    participant StatusJob as CheckBatchStatusJob
    participant QueueSystem as Laravel Queue
    participant Logger
    participant Slack
    
    Note over StatusCommand,Slack: Batch Status Job Dispatch Process
    
    rect rgb(200, 255, 200)
    Note right of StatusCommand: Happy Case - Job Processing
    
    rect rgb(200, 230, 255)
    Note right of StatusCommand: Job Dispatch Loop
    loop For each Pending Batch Log
        StatusCommand->>QueueSystem: Dispatch CheckBatchStatusJob(batch_log)
        QueueSystem->>StatusJob: Queue job for processing
        StatusCommand->>Logger: Log job dispatch with batch_log ID
    end
    end
    
    StatusCommand->>Logger: Log monitoring statistics
    StatusCommand->>Slack: Send command completion summary
    end
    
    rect rgb(255, 200, 200)
    Note right of StatusCommand: Error Handling
    rect rgb(255, 230, 230)
    alt Queue System Error
        StatusCommand->>Logger: Log queue dispatch error
        StatusCommand->>Slack: Send queue error notification
    else Job Creation Error
        StatusCommand->>Logger: Log job creation error
        StatusCommand->>Slack: Send job creation error notification
    end
    end
    end

Step 3: OpenAI Batch Status Checking

sequenceDiagram
    participant StatusJob as CheckBatchStatusJob
    participant BatchHandler as BatchProcessingHandler
    participant OpenAI as OpenAI Batch API
    participant Logger
    participant Slack
    
    Note over StatusJob,Slack: OpenAI Batch Status Verification
    
    rect rgb(200, 255, 200)
    Note right of StatusJob: Happy Case - Status Checking
    
    StatusJob->>Logger: Log job start with batch_log details
    
    rect rgb(200, 230, 255)
    Note right of StatusJob: API Status Request
    StatusJob->>BatchHandler: checkBatchStatus(batch_id)
    BatchHandler->>OpenAI: GET /v1/batches/{batch_id}
    OpenAI-->>BatchHandler: Return batch status and metadata
    BatchHandler->>BatchHandler: validateStatusResponse()
    BatchHandler-->>StatusJob: Return processed status
    StatusJob->>Logger: Log status check result
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of StatusJob: Error Handling
    rect rgb(255, 230, 230)
    alt API Authentication Error
        BatchHandler->>Logger: Log authentication failure
        BatchHandler->>Slack: Send API authentication error
        BatchHandler->>StatusJob: Return authentication error
    else Rate Limiting Error
        BatchHandler->>Logger: Log rate limiting with retry info
        BatchHandler->>BatchHandler: Implement exponential backoff
        BatchHandler->>StatusJob: Schedule retry with delay
    else API Service Error
        BatchHandler->>Logger: Log API service error with response
        BatchHandler->>Slack: Send API service error notification
        BatchHandler->>StatusJob: Return service error
    else Batch Not Found
        BatchHandler->>Logger: Log batch not found error
        BatchHandler->>Slack: Send batch not found notification
        BatchHandler->>StatusJob: Return not found error
    end
    end
    end

Step 4: Completed Batch Result Processing

sequenceDiagram
    participant StatusJob as CheckBatchStatusJob
    participant FileHandler as BatchFileHandler
    participant ResultHandler as BatchResultHandler
    participant OpenAI as OpenAI Files API
    participant Logger
    participant Slack
    
    Note over StatusJob,Slack: Completed Batch Result Processing
    
    rect rgb(200, 255, 200)
    Note right of StatusJob: Happy Case - Status is Completed
    
    rect rgb(200, 230, 255)
    Note right of StatusJob: Result File Download
    StatusJob->>FileHandler: downloadResultFile(output_file_id)
    FileHandler->>OpenAI: GET /v1/files/{file_id}/content
    OpenAI-->>FileHandler: Return result file content (JSONL)
    FileHandler->>FileHandler: validateFileFormat()
    FileHandler-->>StatusJob: Return parsed results
    StatusJob->>Logger: Log file download success with size
    end
    
    rect rgb(255, 255, 200)
    Note right of StatusJob: Result Processing and Validation
    StatusJob->>ResultHandler: processClassificationResults(results)
    ResultHandler->>ResultHandler: parseJSONLResults()
    ResultHandler->>ResultHandler: validateClassificationData()
    ResultHandler->>ResultHandler: mapSentencesToViewpoints()
    ResultHandler-->>StatusJob: Return viewpoint mappings
    StatusJob->>Logger: Log result processing completion
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of StatusJob: Error Handling
    rect rgb(255, 230, 230)
    alt File Download Error
        FileHandler->>Logger: Log download error with file_id
        FileHandler->>Slack: Send download error notification
        FileHandler->>StatusJob: Return download error
    else Invalid File Format
        ResultHandler->>Logger: Log file format validation error
        ResultHandler->>Slack: Send format error notification
        ResultHandler->>StatusJob: Return format error
    else Classification Data Error
        ResultHandler->>Logger: Log classification validation error
        ResultHandler->>Slack: Send validation error notification
        ResultHandler->>StatusJob: Return validation error
    end
    end
    end

Step 5: Database Storage and Status Updates

sequenceDiagram
    participant StatusJob as CheckBatchStatusJob
    participant ReviewVPRepo as ReviewSentenceSpecificViewpointRepository
    participant BatchLogRepo as BatchJobViewpointLogRepository
    participant AnalyzerDB[(gb_analyzer)]
    participant Logger
    participant Slack
    
    Note over StatusJob,Slack: Database Storage and Status Management
    
    rect rgb(200, 255, 200)
    Note right of StatusJob: Happy Case - Data Storage
    
    rect rgb(200, 230, 255)
    Note right of StatusJob: Viewpoint Associations Creation
    StatusJob->>ReviewVPRepo: bulkInsertViewpointAssociations(mappings)
    ReviewVPRepo->>AnalyzerDB: BEGIN TRANSACTION
    ReviewVPRepo->>AnalyzerDB: INSERT INTO review_sentences_wl_spec_vp (bulk)
    AnalyzerDB-->>ReviewVPRepo: Return inserted record count
    ReviewVPRepo->>AnalyzerDB: COMMIT TRANSACTION
    ReviewVPRepo-->>StatusJob: Confirm associations created
    StatusJob->>Logger: Log bulk insert success with count
    end
    
    rect rgb(200, 230, 255)
    Note right of StatusJob: Batch Log Status Update
    StatusJob->>BatchLogRepo: updateBatchLogStatus(batch_log_id, COMPLETED)
    BatchLogRepo->>AnalyzerDB: UPDATE batch_jobs_vp_logs SET status = 2
    AnalyzerDB-->>BatchLogRepo: Confirm status update
    BatchLogRepo-->>StatusJob: Return update confirmation
    StatusJob->>Logger: Log status update completion
    end
    
    rect rgb(230, 200, 255)
    Note right of StatusJob: Success Monitoring
    StatusJob->>Logger: Log processing success with statistics
    StatusJob->>Slack: Send completion notification with metrics
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of StatusJob: Error Handling
    rect rgb(255, 230, 230)
    alt Database Transaction Error
        ReviewVPRepo->>AnalyzerDB: ROLLBACK TRANSACTION
        ReviewVPRepo->>Logger: Log transaction rollback error
        ReviewVPRepo->>Slack: Send database error notification
        ReviewVPRepo->>StatusJob: Return transaction error
    else Bulk Insert Constraint Error
        ReviewVPRepo->>Logger: Log constraint violation error
        ReviewVPRepo->>Slack: Send constraint error notification
        ReviewVPRepo->>StatusJob: Return constraint error
    else Status Update Failure
        BatchLogRepo->>Logger: Log status update failure
        BatchLogRepo->>Slack: Send status update error notification
        BatchLogRepo->>StatusJob: Return update error
    end
    end
    end

Step 6: Failed Batch Handling

sequenceDiagram
    participant StatusJob as CheckBatchStatusJob
    participant BatchLogRepo as BatchJobViewpointLogRepository
    participant AnalyzerDB[(gb_analyzer.batch_jobs_vp_logs)]
    participant Logger
    participant Slack
    
    Note over StatusJob,Slack: Failed Batch Processing
    
    rect rgb(255, 255, 200)
    Note right of StatusJob: Status is Failed
    
    StatusJob->>BatchLogRepo: updateBatchLogStatus(batch_log_id, FAILED, error_details)
    BatchLogRepo->>AnalyzerDB: UPDATE batch_jobs_vp_logs SET status = 9, log_details = error
    AnalyzerDB-->>BatchLogRepo: Confirm error status update
    BatchLogRepo-->>StatusJob: Confirm error logged
    
    StatusJob->>Logger: Log batch failure details with OpenAI error
    StatusJob->>Slack: Send failure notification with batch_id
    end
    
    rect rgb(200, 230, 255)
    Note right of StatusJob: Status is In Progress
    
    StatusJob->>Logger: Log batch still processing with estimated completion
    StatusJob->>Logger: Log no action required for in-progress batch
    end

Step 7: Job Completion and System Monitoring

sequenceDiagram
    participant StatusJob as CheckBatchStatusJob
    participant StatusCommand as specific-viewpoint:check-batch-status
    participant QueueSystem as Laravel Queue
    participant Logger
    participant Slack
    
    Note over StatusJob,Slack: Job Completion and System Monitoring
    
    rect rgb(200, 255, 200)
    Note right of StatusJob: Happy Case - Successful Completion
    
    rect rgb(230, 200, 255)
    Note right of StatusJob: Job Finalization
    StatusJob->>Logger: Log job completion with processing metrics
    StatusJob->>Logger: Log OpenAI API response times and statistics
    StatusJob->>QueueSystem: Mark job as completed
    QueueSystem-->>StatusCommand: Update job completion status
    end
    
    rect rgb(230, 200, 255)
    Note right of StatusCommand: Command Summary
    StatusCommand->>Logger: Log command completion with overall statistics
    StatusCommand->>Logger: Log total logs processed and success rate
    StatusCommand->>Slack: Send command completion summary with metrics
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of StatusJob: Error Handling and Recovery
    rect rgb(255, 230, 230)
    alt Job Processing Failure
        StatusJob->>Logger: Log job failure with error details
        StatusJob->>QueueSystem: Mark job as failed for retry
        StatusJob->>Slack: Send job failure notification
    else API Timeout Issues
        StatusJob->>Logger: Log API timeout error
        StatusJob->>QueueSystem: Release job for retry with delay
        StatusJob->>Slack: Send timeout error notification
    else Critical System Error
        StatusJob->>Logger: Log critical system error
        StatusJob->>Slack: Send critical error alert
        StatusJob->>StatusCommand: Halt further processing
    end
    end
    end

Detail

Parameters

• {--chunk=10}: Optional parameter to limit the number of batch logs processed in a single execution
  • Default value: 10
  • Controls the maximum number of pending batch logs to check in one command run
  • Helps prevent API rate limiting and manages system resource usage
  • Can be increased for faster processing or decreased for resource-constrained environments

Frequency

• Scheduled Execution: Every 5 minutes
  • Configured in routes/console.php using Laravel's scheduler
  • Example: Schedule::command('specific-viewpoint:check-batch-status')->everyFiveMinutes();
• Manual Execution: Can be triggered manually for immediate status checking
  • Used for debugging or urgent result processing

Dependencies

Database Dependencies:

• Analyzer database (gb_analyzer) connection for batch logs and result storage
• Existing records in batch_jobs_vp_logs with pending status
• Transaction support for atomic result processing
• Proper foreign key relationships for viewpoint associations

External Service Dependencies:

• OpenAI API service availability for status queries and file downloads
• Valid API credentials configured in environment variables
• Network connectivity to OpenAI's API endpoints
• API rate limiting compliance for batch status checks

System Dependencies:

• Queue system configuration for job processing (config/queue.php)
• Storage for temporary result file processing
• Configuration in config/specific_viewpoint.php for processing settings
• Error handling and notification systems properly configured

Output

Tables

The check batch status command interacts with multiple database tables. For complete table structures, field definitions, and relationships, see the Database Schema section.

Primary Output Tables:

• review_sentences_wl_spec_vp: Creates viewpoint associations from classification results
• batch_jobs_vp_logs: Updates status and processing statistics

Command-Specific Operations:

• Creates: New records in review_sentences_wl_spec_vp linking sentences to viewpoints
• Updates: Status transitions in batch_jobs_vp_logs (Pending → Completed/Failed)
• Logs: Processing statistics including sentence counts and success rates

Services

OpenAI Integration:

• BatchProcessingHandlerInterface: Checks batch status with OpenAI API
• BatchFileHandlerInterface: Downloads result files from OpenAI
• BatchResultHandlerInterface: Processes and parses classification results
• Implements exponential backoff for rate limiting and error recovery

Repository Services:

• BatchJobViewpointLogRepositoryInterface: Retrieves pending logs and updates status
• ReviewSentenceSpecificViewpointRepositoryInterface: Bulk inserts viewpoint associations
• Transaction management for atomic result processing
• Optimized bulk operations for large result sets

Background Job Services:

• CheckBatchStatusJob: Dispatched for each pending log to handle status checking
• Queue management for asynchronous status monitoring
• Job failure handling with automatic retry mechanisms and backoff strategies

Error Handling

Log

The system generates comprehensive logs for troubleshooting status monitoring issues:

API Communication Errors:

• OpenAI API request failures with full error response details and status codes
• File download failures with retry attempt information and file ID details
• Rate limiting errors with backoff strategy implementation and timing
• Authentication failures with credential validation suggestions

Result Processing Errors:

• Invalid result file format with parsing error details and sample data
• Classification result validation errors with specific viewpoint mapping issues
• Database constraint violations during bulk insert operations
• Transaction rollback scenarios with affected record counts

Log Locations:

• Application logs: storage/logs/laravel.log with contextual batch log information
• Command-specific logs with execution statistics and API response times
• Error logs with full stack traces and OpenAI API request/response details

Slack

Automated Slack notifications are sent via AIBatchPredictSlackChannel for operational monitoring:

Success Notifications:

• Batch completion notifications with processing statistics and timing
• Result processing summaries with viewpoint association counts and success rates
• Performance metrics including API response times and processing throughput

Error Notifications:

• OpenAI API communication failures with error codes and suggested resolution steps
• File download failures with retry information and file availability status
• Database operation failures with affected batch log details and rollback information
• Critical system errors requiring immediate administrative attention

Notification Format:

• Command name and execution timestamp for operational tracking
• Error type and severity level for incident prioritization
• Affected batch log IDs and OpenAI batch details for investigation
• Suggested troubleshooting steps and documentation references

Troubleshooting

Check Data

Verify Pending Batch Logs:

-- Check batch logs awaiting status updates
SELECT id, batch_job_id, batch_id, total_sentences, processed_sentences, 
       status, log_details, created_at, updated_at
FROM batch_jobs_vp_logs
WHERE status = 1 -- Pending
ORDER BY created_at ASC;

Check Stuck Batch Logs:

-- Find batch logs stuck in pending state (older than 2 hours)
SELECT id, batch_job_id, batch_id, total_sentences, 
       TIMESTAMPDIFF(MINUTE, updated_at, NOW()) as minutes_stuck
FROM batch_jobs_vp_logs
WHERE status = 1 -- Pending
AND updated_at < DATE_SUB(NOW(), INTERVAL 2 HOUR)
ORDER BY updated_at ASC;

Check Recent Classification Results:

-- Review recent viewpoint associations created
SELECT COUNT(*) as new_associations, wl_spec_vp_id
FROM review_sentences_wl_spec_vp
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY wl_spec_vp_id
ORDER BY new_associations DESC;

Check Logs

Application Logs:

# Check recent check-batch-status command logs
tail -f storage/logs/laravel.log | grep -E "specific-viewpoint:check-batch-status"

# Check batch status checking logs
grep "CheckBatchStatusJob" storage/logs/laravel.log | tail -20

# Check OpenAI API communication logs
grep "BatchProcessingHandler" storage/logs/laravel.log | tail -10

# Check error patterns
grep -E "(ERROR|CRITICAL)" storage/logs/laravel.log | grep "check-batch-status" | tail -10

Database Logs:

-- Check recent status updates
SELECT bjvl.*, 
       CASE bjvl.status 
           WHEN 1 THEN 'Pending'
           WHEN 2 THEN 'Completed' 
           WHEN 9 THEN 'Failed'
       END as status_name
FROM batch_jobs_vp_logs bjvl
WHERE bjvl.updated_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY bjvl.updated_at DESC;

-- Check failed batch processing
SELECT id, batch_job_id, batch_id, log_details, updated_at
FROM batch_jobs_vp_logs
WHERE status = 9 -- Failed
AND updated_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY updated_at DESC;

OpenAI API Validation:

# Test OpenAI batch status endpoint
curl -X GET "https://api.openai.com/v1/batches/BATCH_ID" \
  -H "Authorization: Bearer YOUR_OPENAI_API_KEY"

# Check file download endpoint
curl -X GET "https://api.openai.com/v1/files/FILE_ID/content" \
  -H "Authorization: Bearer YOUR_OPENAI_API_KEY"

# Check API rate limits
curl -I "https://api.openai.com/v1/batches" \
  -H "Authorization: Bearer YOUR_OPENAI_API_KEY"

Performance Monitoring:

• Monitor command execution times for performance degradation
• Check OpenAI API response times and timeout configurations
• Verify database bulk insert performance for large result sets
• Review queue processing performance for job dispatch efficiency
• Monitor file download times and storage usage for result processing