Specific Viewpoint Overview

Description

The Specific Viewpoint Operations component manages the processing and analysis of review sentences to classify them according to predefined viewpoints using OpenAI's AI capabilities. This component processes product review sentences from the Analyzer database (gb_analyzer) and associates them with specific viewpoints defined in the Console database (gb_console). Through batch processing, it efficiently handles large volumes of review data, enabling structured analysis of customer feedback based on customizable viewpoint categories for trend analysis and pattern recognition.

Overview System Diagram

---
config:
  theme: base
  layout: dagre
  flowchart:
    curve: linear
    htmlLabels: true
  themeVariables:
    edgeLabelBackground: "transparent"
---
flowchart TD
    %% External services
    OpenAI[[OpenAI API]]
    
    %% Commands
    ProcessCommand[specific-viewpoint:process-batches]
    StatusCommand[specific-viewpoint:check-batch-status]
    
    %% Database tables grouped by database
    subgraph ConsoleDB["<div style='width: 300px'>Console Database (gb_console)</div>"]
        direction LR
        WishlistSpecificVP[(wl_spec_vps)]
        WishlistCatVPDetail[(wl_cat_vp_details)]
    end
    
    subgraph AnalyzerDB["<div style='width: 300px'>Analyzer Database (gb_analyzer)</div>"]
        direction TB
        ReviewSentences[(review_sentences)]
        BatchJobs[(batch_jobs_vps)]
        BatchJobLogs[(batch_jobs_vp_logs)]
        ReviewSentenceVP[(review_sentences_wl_spec_vp)]
    end
    
    subgraph Commands["Scheduled Commands"]
        ProcessCommand
        StatusCommand
    end
    
    %% Process Command Steps (1A to 5A)
    subgraph ProcessSteps["Process Command Steps"]
        direction LR
        ProcessCmdStep1A[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>1A</span>
                <p style='margin-top: 8px'>Retrieve Pending Batches</p>
            </div>
        ]
        ProcessCmdStep2A[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>2A</span>
                <p style='margin-top: 8px'>Get Review Sentences</p>
            </div>
        ]
        ProcessCmdStep3A[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>3A</span>
                <p style='margin-top: 8px'>Get Viewpoint Definitions</p>
            </div>
        ]
        ProcessCmdStep4A[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>4A</span>
                <p style='margin-top: 8px'>Send Batch Request</p>
            </div>
        ]
        ProcessCmdStep5A[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #6699cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>5A</span>
                <p style='margin-top: 8px'>Create Log Record</p>
            </div>
        ]
        
        ProcessCmdStep1A --> ProcessCmdStep2A
        ProcessCmdStep2A --> ProcessCmdStep3A
        ProcessCmdStep3A --> ProcessCmdStep4A
        ProcessCmdStep4A --> ProcessCmdStep5A
    end
    
    %% Status Command Steps (1B to 5B)
    subgraph StatusSteps["Status Command Steps"]
        direction LR
        StatusCmdStep1B[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>1B</span>
                <p style='margin-top: 8px'>Retrieve Pending Logs</p>
            </div>
        ]
        StatusCmdStep2B[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>2B</span>
                <p style='margin-top: 8px'>Check Status</p>
            </div>
        ]
        StatusCmdStep3B[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>3B</span>
                <p style='margin-top: 8px'>Download Results</p>
            </div>
        ]
        StatusCmdStep4B[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>4B</span>
                <p style='margin-top: 8px'>Store Results</p>
            </div>
        ]
        StatusCmdStep5B[
            <div style='text-align: center'>
                <span style='display: inline-block; background-color: #99cc66 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>5B</span>
                <p style='margin-top: 8px'>Update Status</p>
            </div>
        ]
        
        StatusCmdStep1B --> StatusCmdStep2B
        StatusCmdStep2B --> StatusCmdStep3B
        StatusCmdStep3B --> StatusCmdStep4B
        StatusCmdStep4B --> StatusCmdStep5B
    end
    
    %% Connections from commands to step groups
    ProcessCommand --> ProcessSteps
    StatusCommand --> StatusSteps
    
    %% Connections from steps to databases/services
    ProcessCmdStep1A -.-> BatchJobs
    ProcessCmdStep2A -.-> ReviewSentences
    ProcessCmdStep3A -.-> WishlistSpecificVP
    ProcessCmdStep4A -.-> OpenAI
    ProcessCmdStep5A -.-> BatchJobLogs
    
    StatusCmdStep1B -.-> BatchJobLogs
    StatusCmdStep2B -.-> OpenAI
    StatusCmdStep3B -.-> OpenAI
    StatusCmdStep4B -.-> ReviewSentenceVP
    StatusCmdStep5B -.-> BatchJobLogs
    
    %% Database styles
    style ConsoleDB fill:#d9f2d9,stroke:#339933,stroke-width:2px
    style AnalyzerDB fill:#d9d9f2,stroke:#6666cc,stroke-width:2px
    
    %% Table styles
    classDef consoleTable fill:#e6ffe6,stroke:#339933,stroke-width:1px
    classDef analyzerTable fill:#e6e6ff,stroke:#6666cc,stroke-width:1px
    
    %% Apply table styles
    class WishlistSpecificVP,WishlistCatVPDetail consoleTable
    class ReviewSentences,BatchJobs,BatchJobLogs,ReviewSentenceVP analyzerTable
    
    %% Other styles
    style OpenAI fill:#fcf3d2,stroke:#cc9900,stroke-width:2px
    style ProcessCommand fill:#d9f2d9
    style StatusCommand fill:#d9f2d9
    style Commands fill:#f9f9f9
    style ProcessSteps fill:#f0f8ff,stroke:#6699cc,stroke-width:1px
    style StatusSteps fill:#f0fff0,stroke:#99cc66,stroke-width:1px
    
    %% Transparent connection steps
    style ProcessCmdStep1A fill:transparent,stroke:transparent,stroke-width:1px
    style ProcessCmdStep2A fill:transparent,stroke:transparent,stroke-width:1px
    style ProcessCmdStep3A fill:transparent,stroke:transparent,stroke-width:1px
    style ProcessCmdStep4A fill:transparent,stroke:transparent,stroke-width:1px
    style ProcessCmdStep5A fill:transparent,stroke:transparent,stroke-width:1px
    style StatusCmdStep1B fill:transparent,stroke:transparent,stroke-width:1px
    style StatusCmdStep2B fill:transparent,stroke:transparent,stroke-width:1px
    style StatusCmdStep3B fill:transparent,stroke:transparent,stroke-width:1px
    style StatusCmdStep4B fill:transparent,stroke:transparent,stroke-width:1px
    style StatusCmdStep5B fill:transparent,stroke:transparent,stroke-width:1px

Detail Dataflow Dependency

The Specific Viewpoint Operations component follows a bidirectional data flow with two main processes that align with the numbered steps in the diagram:

Batch Creation and Processing (Steps 1A-5A)

1A. Retrieve Pending Batches: The specific-viewpoint:process-batches command identifies batch jobs:

Queries the batch_jobs_vps table for jobs with pending log creation status
Applies filtering based on the optional --log-creation-status parameter
Uses the BatchJobViewpointRepositoryInterface for retrieving records
Groups jobs into chunks for efficient processing

2A. Get Review Sentences: For each batch job, the system retrieves related data:

Collects unprocessed review sentences from review_sentences table
Filters by the product IDs specified in the batch job
Applies a maximum limit from configuration (specific_viewpointbatch_processing.max_processing_sentences)
Tracks the last processed review sentence ID to support pagination

3A. Get Viewpoint Definitions: The system loads viewpoint configuration:

Retrieves specific viewpoint definitions from wl_spec_vps table
Loads category viewpoint details from wl_cat_vp_details table
Uses these definitions to structure the AI prompt
Validates the input data for completeness and correctness

4A. Send Batch Request: Data is structured and sent to OpenAI:

Uses the ViewpointClassifyProcessingInterface to communicate with OpenAI
Prepares prompts with review sentences and viewpoint definitions
Sends batch processing requests to OpenAI's API
Stores the OpenAI batch ID for future reference

5A. Create Log Record: Upon successful request, the system records the operation:

Creates a new record in batch_jobs_vp_logs table
Stores the OpenAI batch ID, sentence counts, and processing metadata
Sets initial status to "Pending"
Uses transactions to ensure data consistency
Implements idempotency checks to prevent duplicate processing

Status Monitoring and Result Processing (Steps 1B-5B)

1B. Retrieve Pending Logs: The specific-viewpoint:check-batch-status command identifies logs to check:

Queries the batch_jobs_vp_logs table for logs with pending status
Prioritizes older pending logs
Applies limits based on the --chunk parameter (default: 10)
Dispatches CheckBatchStatusJob for each pending log

2B. Check Status: For each pending log:

Uses BatchProcessingHandlerInterface to communicate with OpenAI
Calls OpenAI's API to check the current status of the batch
Interprets the response to determine if the batch is completed or failed
Manages retries for transient failures

3B. Download Results: When batches are complete:

Retrieves the output file ID from the status response
Uses BatchFileHandlerInterface to download the result file
Handles potential download failures with appropriate error recording
Implements exponential backoff for rate limiting

4B. Store Results: The system processes the classification results:

Uses BatchResultHandlerInterface to parse the raw results
Extracts viewpoint mappings from successful responses
Formats data for database storage
Uses ReviewSentenceSpecificViewpointRepositoryInterface to bulk insert associations between review sentences and specific viewpoints
Counts processed sentences for status reporting

5B. Update Status: Based on processing results:

Updates the log status in batch_jobs_vp_logs (Pending → Completed/Failed)
Updates the batch job status in batch_jobs_vps based on all related logs
Records statistics and error details
Sends Slack notifications for important events
Updates timestamps for auditing and monitoring

Database Schema

Console Database Tables (gb_console)

erDiagram
    wl_spec_vps {
        bigint id PK
        bigint wl_cat_vp_detail_id FK "Foreign key to wl_cat_vp_details (cascade delete)"
        string name "Viewpoint name (max 100 chars)"
        text description "Viewpoint description"
        timestamp created_at
        timestamp updated_at
    }
    
    wl_cat_vp_details {
        bigint id PK
        text detail "Category viewpoint detail description"
        timestamp created_at
        timestamp updated_at
    }
    
    wl_cat_vp_details ||--o{ wl_spec_vps : "has many"

Analyzer Database Tables (gb_analyzer)

erDiagram
    batch_jobs_vps {
        bigint id PK
        json product_ids "List of product IDs to process"
        json wl_spec_vp_ids "List of specific viewpoint IDs to process"
        bigint wl_cat_vp_detail_id "Reference to wishlist_category_viewpoint_details"
        bigint last_review_sentence_id "Last processed review ID (default: 0)"
        tinyint log_creation_status "Log creation status (pending/processing/completed/failed)"
        tinyint status "Job status (pending/processing/completed/failed)"
        timestamp created_at
        timestamp updated_at
    }
    
    batch_jobs_vp_logs {
        bigint id PK
        bigint batch_job_id "Reference to batch_jobs_vps"
        string batch_id "OpenAI batch ID"
        integer total_sentences "Total sentences to process (default: 0)"
        integer processed_sentences "Number of processed sentences (default: 0)"
        json log_details "Details of any errors (nullable)"
        integer status "Log status (default: Pending)"
        timestamp created_at
        timestamp updated_at
    }
    
    review_sentences {
        bigint sentence_id PK
        string mall_product_id "Product ID from the mall"
        text content "Sentence content extracted from review"
        timestamp created_at
        timestamp updated_at
    }
    
    review_sentences_wl_spec_vp {
        bigint review_sentence_id PK "review_sentences.sentence_id"
        bigint wl_spec_vp_id PK "wishlist_specific_viewpoints.id"
    }
    
    batch_jobs_vps ||--o{ batch_jobs_vp_logs : "has many"
    review_sentences ||--o{ review_sentences_wl_spec_vp : "has many"

Frequency Overview

Timeline

timeline
    title Specific Viewpoint Operations Schedule
    section Batch Processing
        Every 5 minutes <br>(Ex. 08.00, 08.05, etc.) : specific-viewpoint process-batches
                                                    : Creates and dispatches specific viewpoint classification jobs
                                                    : specific-viewpoint check-batch-status
                                                    : Checks batch status and processes results from OpenAI

Expected Outcomes

When these commands execute successfully, the system delivers:

Automated AI-Powered Classification: Regular processing of review sentences using OpenAI's capabilities to classify content into meaningful viewpoint categories
Structured Sentiment Analysis: Organized analysis of product reviews grouped by specific aspects of interest enabling targeted insights
Scalable Batch Processing: Efficient handling of large volumes of review data through chunked processing and queue management
Real-Time Status Monitoring: Continuous tracking of OpenAI batch processing status with automatic result retrieval and storage
Data Quality Assurance: Validation of input data completeness and correctness before AI processing to ensure meaningful classification results
Comprehensive Audit Trail: Complete logging of batch operations including creation events, status changes, and error conditions for troubleshooting
Pattern Recognition Capabilities: Ability to identify trends and patterns in customer feedback at scale across multiple products and categories
Decision Support Data: Supporting data for informed product development and marketing decisions based on structured viewpoint analysis

Batch List

Name	Description
Process Batches	Command that creates and dispatches batch jobs to send review sentences to OpenAI for viewpoint classification
Check Batch Status	Command that monitors OpenAI batch processing operations, retrieves completed results, and stores classified viewpoint associations

Backend Documentation