Process Batches

Command Signature

php artisan specific-viewpoint:process-batches {--log-creation-status=} {--dry-run}

Purpose

The specific-viewpoint:process-batches command creates and dispatches batch jobs that send review sentences to OpenAI for classification against predefined viewpoint criteria. This command identifies pending batch jobs from the Analyzer database, retrieves associated review sentences and viewpoint definitions, then submits structured classification requests to OpenAI's batch processing API. The command ensures efficient processing of large volumes of review data for AI-powered sentiment analysis and viewpoint categorization.

Sequence Diagram

Step 1: Command Initialization and Job Retrieval

sequenceDiagram
    participant System
    participant ProcessCommand as specific-viewpoint:process-batches
    participant BatchJobRepo as BatchJobViewpointRepository
    participant AnalyzerDB[(gb_analyzer.batch_jobs_vps)]
    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->>ProcessCommand: Execute Command
    ProcessCommand->>Logger: Log command start with parameters
    
    ProcessCommand->>BatchJobRepo: getPendingBatchJobs(log_creation_status)
    BatchJobRepo->>AnalyzerDB: Query WHERE log_creation_status = 1 (Pending)
    AnalyzerDB-->>BatchJobRepo: Return pending batch jobs
    BatchJobRepo-->>ProcessCommand: Return pending batch jobs collection
    ProcessCommand->>Logger: Log found jobs count
    end
    
    rect rgb(255, 200, 200)
    Note right of System: Error Handling
    rect rgb(255, 230, 230)
    alt Database Connection Error
        BatchJobRepo->>Logger: Log database connection error
        BatchJobRepo->>Slack: Send database error notification
    else No Pending Jobs
        ProcessCommand->>Logger: Log no jobs to process
    end
    end
    end

Step 2: Job Dispatch and Queue Management

sequenceDiagram
    participant ProcessCommand as specific-viewpoint:process-batches
    participant BatchJob as CreateBatchPredictionJob
    participant QueueSystem as Laravel Queue
    participant Logger
    participant Slack
    
    Note over ProcessCommand,Slack: Batch Job Dispatch Process
    
    rect rgb(200, 255, 200)
    Note right of ProcessCommand: Happy Case - Job Processing
    
    rect rgb(200, 230, 255)
    Note right of ProcessCommand: Dry Run Check
    alt --dry-run Flag Present
        ProcessCommand->>Logger: Log dry-run mode activation
        ProcessCommand->>ProcessCommand: Simulate job dispatch without execution
        ProcessCommand->>Logger: Log simulated operations
    else Normal Execution
        Note right of ProcessCommand: Actual Job Dispatch
        loop For each Pending Batch Job
            ProcessCommand->>QueueSystem: Dispatch CreateBatchPredictionJob(batch_job)
            QueueSystem->>BatchJob: Queue job for processing
            ProcessCommand->>Logger: Log job dispatch with batch job ID
        end
    end
    end
    
    ProcessCommand->>Logger: Log processing statistics
    ProcessCommand->>Slack: Send command completion summary
    end
    
    rect rgb(255, 200, 200)
    Note right of ProcessCommand: Error Handling
    rect rgb(255, 230, 230)
    alt Queue System Error
        ProcessCommand->>Logger: Log queue dispatch error
        ProcessCommand->>Slack: Send queue error notification
    else Job Creation Error
        ProcessCommand->>Logger: Log job creation error
        ProcessCommand->>Slack: Send job creation error notification
    end
    end
    end

Step 3: Data Collection and Preparation

sequenceDiagram
    participant BatchJob as CreateBatchPredictionJob
    participant ReviewRepo as ReviewSentenceRepository
    participant ViewpointRepo as WishlistSpecificViewpointRepository
    participant AnalyzerDB[(gb_analyzer)]
    participant ConsoleDB[(gb_console)]
    participant Logger
    participant Slack
    
    Note over BatchJob,Slack: Data Collection Phase
    
    rect rgb(200, 255, 200)
    Note right of BatchJob: Happy Case - Data Retrieval
    
    BatchJob->>Logger: Log job start with batch job details
    
    rect rgb(200, 230, 255)
    Note right of BatchJob: Review Sentences Collection
    BatchJob->>ReviewRepo: getReviewSentences(product_ids, last_sentence_id)
    ReviewRepo->>AnalyzerDB: Query review_sentences WHERE product_id IN (ids)
    AnalyzerDB-->>ReviewRepo: Return review sentences collection
    ReviewRepo-->>BatchJob: Return review sentences with metadata
    BatchJob->>Logger: Log review sentences count
    end
    
    rect rgb(200, 230, 255)
    Note right of BatchJob: Viewpoint Definitions Collection
    BatchJob->>ViewpointRepo: getViewpointDefinitions(wl_spec_vp_ids)
    ViewpointRepo->>ConsoleDB: Query wl_spec_vps with wl_cat_vp_details
    ConsoleDB-->>ViewpointRepo: Return viewpoint definitions
    ViewpointRepo-->>BatchJob: Return viewpoint data with categories
    BatchJob->>Logger: Log viewpoint definitions count
    end
    
    rect rgb(255, 255, 200)
    Note right of BatchJob: Data Preparation
    BatchJob->>BatchJob: preparePromptsAndViewpoints()
    BatchJob->>BatchJob: validateDataCompleteness()
    BatchJob->>Logger: Log data preparation completion
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of BatchJob: Error Handling
    rect rgb(255, 230, 230)
    alt Insufficient Review Data
        BatchJob->>Logger: Log insufficient review sentences
        BatchJob->>BatchJob: Skip batch job processing
    else Missing Viewpoint Definitions
        BatchJob->>Logger: Log missing viewpoint data
        BatchJob->>Slack: Send viewpoint configuration error
    else Data Validation Error
        BatchJob->>Logger: Log data validation failure
        BatchJob->>Slack: Send data validation error notification
    end
    end
    end

Step 4: OpenAI API Integration

sequenceDiagram
    participant BatchJob as CreateBatchPredictionJob
    participant ViewpointAPI as ViewpointClassifyProcessingInterface
    participant OpenAI as OpenAI Batch API
    participant Logger
    participant Slack
    
    Note over BatchJob,Slack: OpenAI Batch Processing Integration
    
    rect rgb(200, 255, 200)
    Note right of BatchJob: Happy Case - API Communication
    
    rect rgb(200, 230, 255)
    Note right of BatchJob: Batch Request Preparation
    BatchJob->>ViewpointAPI: prepareStructuredPrompts(sentences, viewpoints)
    ViewpointAPI->>ViewpointAPI: formatBatchRequest(prompts)
    ViewpointAPI->>ViewpointAPI: validateRequestStructure()
    ViewpointAPI-->>BatchJob: Return formatted batch request
    BatchJob->>Logger: Log batch request preparation
    end
    
    rect rgb(255, 255, 200)
    Note right of BatchJob: API Submission
    BatchJob->>ViewpointAPI: submitBatchPredictionRequest(batch_request)
    ViewpointAPI->>OpenAI: POST /v1/batches (batch processing request)
    OpenAI-->>ViewpointAPI: Return batch_id and status
    ViewpointAPI-->>BatchJob: Return OpenAI batch_id
    BatchJob->>Logger: Log successful API submission with batch_id
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of BatchJob: Error Handling
    rect rgb(255, 230, 230)
    alt API Authentication Error
        ViewpointAPI->>Logger: Log authentication failure
        ViewpointAPI->>Slack: Send API authentication error
        ViewpointAPI->>BatchJob: Return authentication error
    else Rate Limiting Error
        ViewpointAPI->>Logger: Log rate limiting with retry info
        ViewpointAPI->>ViewpointAPI: Implement exponential backoff
        ViewpointAPI->>OpenAI: Retry request after delay
    else API Service Error
        ViewpointAPI->>Logger: Log API service error with response
        ViewpointAPI->>Slack: Send API service error notification
        ViewpointAPI->>BatchJob: Return service error
    else Request Validation Error
        ViewpointAPI->>Logger: Log request validation failure
        ViewpointAPI->>Slack: Send request format error
        ViewpointAPI->>BatchJob: Return validation error
    end
    end
    end

Step 5: Batch Log Creation and Status Updates

sequenceDiagram
    participant BatchJob as CreateBatchPredictionJob
    participant BatchLogRepo as BatchJobViewpointLogRepository
    participant BatchJobRepo as BatchJobViewpointRepository
    participant AnalyzerDB[(gb_analyzer)]
    participant Logger
    participant Slack
    
    Note over BatchJob,Slack: Batch Log Management
    
    rect rgb(200, 255, 200)
    Note right of BatchJob: Happy Case - Log Creation
    
    rect rgb(200, 230, 255)
    Note right of BatchJob: Batch Log Creation
    BatchJob->>BatchLogRepo: createBatchLog(batch_id, sentences_count, metadata)
    BatchLogRepo->>AnalyzerDB: INSERT INTO batch_jobs_vp_logs
    AnalyzerDB-->>BatchLogRepo: Return log record ID
    BatchLogRepo-->>BatchJob: Confirm log created with ID
    BatchJob->>Logger: Log batch log creation success
    end
    
    rect rgb(200, 230, 255)
    Note right of BatchJob: Status Update
    BatchJob->>BatchJobRepo: updateLogCreationStatus(batch_job_id, PROCESSING)
    BatchJobRepo->>AnalyzerDB: UPDATE batch_jobs_vps SET log_creation_status = 2
    AnalyzerDB-->>BatchJobRepo: Confirm status update
    BatchJobRepo-->>BatchJob: Return update confirmation
    BatchJob->>Logger: Log status update completion
    end
    
    rect rgb(230, 200, 255)
    Note right of BatchJob: Success Monitoring
    BatchJob->>Logger: Log batch creation success with statistics
    BatchJob->>Slack: Send batch creation notification with details
    end
    end
    
    rect rgb(255, 200, 200)
    Note right of BatchJob: Error Handling
    rect rgb(255, 230, 230)
    alt Database Transaction Error
        BatchLogRepo->>Logger: Log database transaction error
        BatchLogRepo->>AnalyzerDB: ROLLBACK transaction
        BatchLogRepo->>Slack: Send database error notification
        BatchLogRepo->>BatchJob: Return transaction error
    else Log Creation Failure
        BatchJob->>Logger: Log creation failure with batch details
        BatchJob->>BatchJobRepo: Mark batch job as failed (status = 9)
        BatchJob->>Slack: Send log creation failure notification
    else Status Update Failure
        BatchJob->>Logger: Log status update failure
        BatchJob->>Slack: Send status update error notification
    end
    end
    end

Step 6: Job Completion and Monitoring

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

Detail

Parameters

• {--log-creation-status=}: Optional parameter to filter batch jobs by their log creation status

  • 1: Pending (default if not specified)
  • 2: Processing
  • 3: Completed
  • 9: Failed
  • When omitted, defaults to processing jobs with Pending status
  • Used for selective processing and debugging specific job states

• {--dry-run}: Optional flag to execute without actually dispatching jobs

  • When provided, the command will simulate the process without creating actual batch jobs
  • Useful for testing and verification of job selection logic
  • Logs all operations that would be performed without side effects

Frequency

• Scheduled Execution: Every 5 minutes
  • Configured in routes/console.php using Laravel's scheduler
  • Example: Schedule::command('specific-viewpoint:process-batches')->everyFiveMinutes();
• Manual Execution: Can be triggered manually for specific log creation statuses
  • Used for debugging or processing specific job states

Dependencies

Database Dependencies:

• Analyzer database (gb_analyzer) connection for batch jobs and review sentences
• Console database (gb_console) connection for viewpoint definitions
• Sufficient review sentence data to process for the specified products
• Properly defined viewpoint categories in the Console database

External Service Dependencies:

• OpenAI API service availability and authentication
• Valid API credentials configured in environment variables
• Batch processing capability enabled in the OpenAI account
• Sufficient API quota for batch operations and rate limiting compliance

System Dependencies:

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

Output

Tables

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

Primary Output Tables:

• batch_jobs_vp_logs: Creates new log records with OpenAI batch IDs
• batch_jobs_vps: Updates log creation status for processed jobs

Command-Specific Operations:

• Creates: New records in batch_jobs_vp_logs with OpenAI batch ID and sentence counts
• Updates: Log creation status in batch_jobs_vps from Pending to Processing
• Reads: Review sentences from review_sentences and viewpoint definitions from wl_spec_vps

Services

OpenAI Integration:

• ViewpointClassifyProcessingInterface: Communicates with OpenAI batch processing API
• Prepares structured prompts with review sentences and viewpoint definitions
• Submits batch requests and handles API response validation
• Implements retry logic with exponential backoff for rate limiting

Repository Services:

• BatchJobViewpointRepositoryInterface: Retrieves pending batch jobs with filtering
• ReviewSentenceRepositoryInterface: Fetches review sentences by product IDs
• WishlistSpecificViewpointRepositoryInterface: Loads viewpoint definitions
• BatchJobViewpointLogRepositoryInterface: Creates batch processing logs

Background Job Services:

• CreateBatchPredictionJob: Dispatched for each batch job to handle processing
• Queue management for asynchronous batch creation
• Job failure handling with automatic retry mechanisms

Error Handling

Log

The system generates comprehensive logs for troubleshooting batch creation issues:

Batch Creation Errors:

• OpenAI API communication failures with full request/response details
• Data validation errors with specific review sentence and viewpoint information
• Database transaction failures with rollback details and affected records
• Configuration errors with missing parameter information and suggestions

Job Processing Errors:

• Insufficient review sentence data with product ID details and availability checks
• Invalid viewpoint configuration with specific viewpoint ID validation
• Queue system failures with job dispatch details and retry information
• Memory or timeout issues during large batch processing

Log Locations:

• Application logs: storage/logs/laravel.log with contextual batch job information
• Command-specific logs with execution statistics and processing metrics
• 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 creation completion with processing statistics and timing
• Job dispatch summaries with success/failure counts and performance metrics
• OpenAI API usage statistics including batch counts and sentence volumes

Error Notifications:

• OpenAI API communication failures with error codes and suggested resolution steps
• Database operation failures with affected batch job details and rollback information
• Configuration issues with resolution suggestions and documentation links
• 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 job IDs and product details for investigation
• Suggested troubleshooting steps and documentation references

Troubleshooting

Check Data

Verify Pending Batch Jobs:

-- Check batch jobs awaiting log creation
SELECT id, product_ids, wl_spec_vp_ids, wl_cat_vp_detail_id, 
       last_review_sentence_id, status, log_creation_status, created_at
FROM batch_jobs_vps
WHERE log_creation_status = 1 -- Pending
ORDER BY created_at ASC;

Verify Review Sentence Availability:

-- Check review sentences for specific products
SELECT COUNT(*) as sentence_count, product_id
FROM review_sentences
WHERE product_id IN (SELECT JSON_UNQUOTE(JSON_EXTRACT(product_ids, '$[0]')) FROM batch_jobs_vps WHERE id = BATCH_JOB_ID)
GROUP BY product_id;

Check Viewpoint Definitions:

-- Verify viewpoint definitions exist
SELECT wsvp.id, wsvp.name, wsvp.description, wcvd.detail
FROM wl_spec_vps wsvp
JOIN wl_cat_vp_details wcvd ON wsvp.wl_cat_vp_detail_id = wcvd.id
WHERE wsvp.id IN (SELECT JSON_UNQUOTE(JSON_EXTRACT(wl_spec_vp_ids, '$[0]')) FROM batch_jobs_vps WHERE id = BATCH_JOB_ID);

Check Logs

Application Logs:

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

# Check batch job creation logs
grep "CreateBatchPredictionJob" storage/logs/laravel.log | tail -20

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

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

Database Logs:

-- Check recent batch log creation
SELECT bjvl.*, bjv.product_ids
FROM batch_jobs_vp_logs bjvl
JOIN batch_jobs_vps bjv ON bjvl.batch_job_id = bjv.id
WHERE bjvl.created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY bjvl.created_at DESC;

-- Check failed batch jobs
SELECT id, product_ids, wl_spec_vp_ids, status, log_creation_status, updated_at
FROM batch_jobs_vps
WHERE log_creation_status = 9 -- Failed
AND updated_at > DATE_SUB(NOW(), INTERVAL 1 DAY);

OpenAI API Validation:

# Test OpenAI API connectivity
curl -X GET "https://api.openai.com/v1/batches" \
  -H "Authorization: Bearer YOUR_OPENAI_API_KEY"

Performance Monitoring:

• Monitor command execution times for performance degradation
• Check queue processing performance for job dispatch efficiency
• Verify OpenAI API response times and timeout configurations
• Review memory usage during batch preparation phases for optimization opportunities