Backend Documentation

Notification Overview

Description

The Notification System component manages real-time notifications for sales data changes in wishlist products and groups. It leverages Laravel's notification system to process data from the Console database (gb_console) and Analyzer database (gb_analyzer), identify significant changes based on user-defined thresholds (ratio or amount types), and deliver notifications through both database storage and real-time Pusher service. This component enables automated monitoring of sales performance changes for products and product groups according to user-defined criteria, providing timely awareness of important business metrics for end users.

Overview System Diagram

---
config:
  theme: base
  layout: dagre
  flowchart:
    curve: linear
    htmlLabels: true
  themeVariables:
    edgeLabelBackground: "transparent"
---
flowchart TB
    %% External services
    PusherAPI((Pusher API))
    
    %% Commands
    SendNotification[notification:sale-one-month]
    
    %% Database tables grouped by database
    subgraph ConsoleDB["Console Database (gb_console)"]
        direction TB
        NotificationSettings[(notification_settings)]
        Notifications[(notifications)]
        Groups[(groups)]
        GroupMembers[(group_members)]
        Users[(users)]
        WishlistProducts[(wishlist_products)]
        WishlistGroups[(wishlist_groups)]
        Subscriptions[(subscriptions)]
    end
    
    subgraph AnalyzerDB["<div style='width: 300px'>Analyzer Database (gb_analyzer)</div>"]
        direction TB
        Products[(products)]
        ProductDetails[(product_details)]
    end
    
    subgraph Commands["Scheduled Commands"]
        SendNotification
    end
    
    subgraph JobProcessing["Job Processing"]
        SendNotificationJob[SendNotificationJob]
    end
    
    SendNotification --- Step1[
        <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'>1</span>
            <p style='margin-top: 8px'>Retrieve Settings</p>
        </div>
    ]
    Step1 --> NotificationSettings
    
    SendNotification --- Step2[
        <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'>2</span>
            <p style='margin-top: 8px'>Dispatch Jobs</p>
        </div>
    ]
    Step2 --> SendNotificationJob
    
    SendNotificationJob --- Step3[
        <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'>3</span>
            <p style='margin-top: 8px'>Get Product Data</p>
        </div>
    ]
    Step3 --> Products
    Step3 --> ProductDetails
    
    SendNotificationJob --- Step4[
        <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'>4</span>
            <p style='margin-top: 8px'>Get Group Members</p>
        </div>
    ]
    Step4 --> GroupMembers
    
    SendNotificationJob --- Step5[
        <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'>5</span>
            <p style='margin-top: 8px'>Store Notifications</p>
        </div>
    ]
    Step5 --> Notifications
    
    SendNotificationJob --- Step6[
        <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'>6</span>
            <p style='margin-top: 8px'>Real-time Delivery</p>
        </div>
    ]
    Step6 --> PusherAPI
    
    PusherAPI --- Step7[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #cc66cc !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>7</span>
            <p style='margin-top: 8px'>WebSocket</p>
        </div>
    ]
    Step7 --> Users
    
    %% Implicit relationships
    NotificationSettings -.-> WishlistProducts
    NotificationSettings -.-> WishlistGroups
    Groups -.-> Subscriptions
    
    %% 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 NotificationSettings,Notifications,Groups,GroupMembers,Users,WishlistProducts,WishlistGroups,Subscriptions consoleTable
    class Products,ProductDetails analyzerTable
    
    %% Other styles
    style PusherAPI fill:#fcd9d9,stroke:#cc6666,stroke-width:2px
    style SendNotification fill:#d9f2d9
    style Commands fill:#f9f9f9
    style JobProcessing fill:#f0e6ff,stroke:#9966cc,stroke-width:1px
    style SendNotificationJob fill:#f5e6ff
    
    %% Transparent connection steps
    style Step1 fill:transparent,stroke:transparent,stroke-width:1px
    style Step2 fill:transparent,stroke:transparent,stroke-width:1px
    style Step3 fill:transparent,stroke:transparent,stroke-width:1px
    style Step4 fill:transparent,stroke:transparent,stroke-width:1px
    style Step5 fill:transparent,stroke:transparent,stroke-width:1px
    style Step6 fill:transparent,stroke:transparent,stroke-width:1px
    style Step7 fill:transparent,stroke:transparent,stroke-width:1px

Detail Dataflow Dependency

The Notification System component follows a sequential data flow with the following steps:

  1. Retrieve Settings: The notification:sale-one-month command retrieves notification settings from the database:

    • Queries the notification_settings table for active configurations
    • Filters by active group subscriptions using whereHas('group.activeSubscription')
    • Processes settings in chunks (default: 100 records per chunk)
    • Uses NotificationSettingRepositoryInterface with chunkDataSendNotification() method

  2. Dispatch Jobs: For each chunk of notification settings:

    • Dispatches SendNotificationJob with settings collection data
    • Applies Laravel queue configuration for reliable background processing
    • Logs dispatch events for monitoring and debugging

  3. Get Product Data: The job retrieves relevant product and sales data:

    • For individual product notifications: Gets specific wishlist product and corresponding analyzer product
    • For group notifications: Gets aggregated data across multiple products in the wishlist group
    • Retrieves current and historical sales data from product_details table using date ranges
    • Accesses data through repository interfaces: ProductRepository, ProductDetailRepository, WishlistProductRepository

  4. Evaluate Conditions: The system analyzes data based on notification settings:

    • For ratio-based notifications: Calculates percentage changes between current and historical periods using calculate_percentage_change()
    • For amount-based notifications: Compares absolute sales values against fixed thresholds
    • Uses sales calculation: sales_one_month * price for sales value determination
    • Determines if notification criteria are met based on comparison types:
      • Increase/Decrease (for ratio type with percentage thresholds)
      • Exceed/Fall Below (for amount type with absolute value thresholds)

  5. Get Group Members: When conditions are met, the system:

    • Identifies group members associated with the notification setting through group_id
    • Retrieves user records through group-member relationships using GroupRepository
    • Prepares personalized notification data with user-specific context

  6. Store Notifications: For each qualifying notification:

    • Creates a record in the notifications table using Laravel's notification system
    • Stores structured JSON data including comparison values, entity references, and timestamps
    • Uses SendNotification class for database storage with proper data formatting
    • Maintains delivery status for auditing and read tracking

  7. Real-time Delivery: Finally, the system delivers notifications:

    • Sends real-time updates via Pusher WebSockets using toPusher() method
    • Tracks delivery status through Laravel notification event listeners
    • Handles errors and provides fallback to database-only delivery
    • Logs successful and failed notification attempts for monitoring

Database Schema

Console Database Tables (gb_console)

erDiagram
    notification_settings {
        bigint id PK
        string notifiable_type "Polymorphic type (used for entity type identification in job processing)"
        bigint notifiable_id "Polymorphic ID referencing the entity (used for entity lookup in job processing)"
        bigint group_id FK "Foreign key to groups table (used for group member notification and subscription filtering)"
        string type "Notification type: ratio, amount (used for processing logic selection)"
        string frequency "Notification frequency: previous_day, one_week_ago (used for date range calculation)"
        string comparison_type "Type of comparison: increase, decrease, exceed, fall_below (used for threshold evaluation)"
        decimal comparison_value "Threshold value for comparison (used for notification condition checking)"
        timestamp created_at
        timestamp updated_at
    }
    
    notifications {
        uuid id PK
        string type "Notification class name (used for notification type identification)"
        string notifiable_type "Polymorphic type (used for user identification in Pusher delivery)"
        bigint notifiable_id "Polymorphic ID referencing the user (used for notification targeting)"
        text data "JSON notification data with setting and compare information (used for Pusher payload)"
        timestamp read_at "When notification was read (used for read status tracking)"
        timestamp created_at
        timestamp updated_at
    }
    
    groups {
        bigint id PK
        string name "Group name (used for notification context)"
        timestamp created_at
        timestamp updated_at
    }
    
    group_members {
        bigint id PK
        bigint group_id FK "Foreign key to groups table (used for user lookup in notification delivery)"
        bigint user_id FK "Foreign key to users table (used for notification targeting)"
        timestamp created_at
        timestamp updated_at
    }
    
    users {
        bigint id PK
        string name "User name (used for notification personalization)"
        string email "User email (used for notification delivery)"
        timestamp created_at
        timestamp updated_at
    }
    
    subscriptions {
        bigint id PK
        bigint group_id FK "Foreign key to groups table (used for active subscription filtering)"
        string status "Subscription status (filtered for active in chunkDataSendNotification)"
        timestamp created_at
        timestamp updated_at
    }
    
    wishlist_products {
        bigint id PK
        bigint wishlist_group_id FK "Foreign key to wishlist_groups table (used for group association)"
        text summary_product "JSON data containing product input information (used for product ID extraction)"
        timestamp created_at
        timestamp updated_at
    }
    
    wishlist_groups {
        bigint id PK
        string name "Wishlist group name (used for notification context and group processing)"
        timestamp created_at
        timestamp updated_at
    }
    
    notification_settings ||--o{ groups : "belongs to"
    groups ||--o{ group_members : "has many"
    groups ||--o{ subscriptions : "has many"
    group_members ||--o{ users : "belongs to"
    notifications ||--o{ users : "notifiable"
    notification_settings ||--o{ wishlist_products : "notifiable (polymorphic)"
    notification_settings ||--o{ wishlist_groups : "notifiable (polymorphic)"
    wishlist_products ||--o{ wishlist_groups : "belongs to"

Analyzer Database Tables (gb_analyzer)

erDiagram
    products {
        bigint id PK
        integer mall_id "Mall identifier (used for product filtering and matching)"
        string mall_product_id "Product ID from the mall (used for product lookup by input)"
        string unique_key "Unique identifier (used for product deduplication)"
        timestamp created_at
        timestamp updated_at
    }
    
    product_details {
        bigint id PK
        bigint product_id FK "Foreign key to products table (used for sales data association)"
        decimal price "Product price (used for sales value calculation: sales_one_month * price)"
        integer sales_one_month "Sales count for one month period (used for sales value calculation)"
        date metrics_date "Date of the metrics (used for date range filtering in historical comparisons)"
        timestamp created_at
        timestamp updated_at
        timestamp crawl_created_at "When the data was crawled (used for latest data selection)"
    }
    
    products ||--o{ product_details : "has many"

Notification Command Logic Fields

Notification Settings Retrieval:

Command Level (notification:sale-one-month):

  • notification_settings.id - Primary key for chunk processing
  • notification_settings.group_id - Used for active subscription filtering via whereHas('group.activeSubscription')

Job Level (SendNotificationJob):

  • notification_settings.notifiable_type - Used to determine if entity is WishlistProduct or WishlistGroup
  • notification_settings.notifiable_id - Used for entity lookup (getEntity() method)
  • notification_settings.type - Used for processing logic selection (ratio vs amount)
  • notification_settings.frequency - Used for date range calculation (getDateRangeForFrequency())
  • notification_settings.comparison_type - Used for threshold evaluation logic
  • notification_settings.comparison_value - Used for notification condition checking
  • notification_settings.group_id - Used for group member lookup and notification delivery

Product Data Processing:

Product Lookup:

  • products.mall_product_id - Used for product matching via getByMallProductId()
  • products.id - Used for product details association

Sales Data Calculation:

  • product_details.product_id - Used for product association
  • product_details.sales_one_month - Used in sales value calculation
  • product_details.price - Used in sales value calculation (sales_one_month * price)
  • product_details.metrics_date - Used for date range filtering in historical comparisons
  • product_details.crawl_created_at - Used for latest data selection

Group Member Notification:

Group Processing:

  • groups.id - Used for group lookup via findById()
  • group_members.group_id - Used for member association
  • group_members.user_id - Used for user lookup
  • users.id - Used for notification targeting and Pusher delivery
  • users.name - Used for notification personalization
  • users.email - Used for notification delivery

Notification Storage and Delivery:

Database Storage:

  • notifications.id - Generated UUID for notification identification
  • notifications.type - Set to notification class name
  • notifications.notifiable_type - Set to 'App\Models\User'
  • notifications.notifiable_id - Set to user ID for targeting
  • notifications.data - Stores JSON payload with setting and compare data
  • notifications.read_at - Used for read status tracking

Entity Context:

  • wishlist_products.summary_product - Used for product input extraction
  • wishlist_groups.name - Used for notification context

Sales Value Calculation Logic

The notification system uses the following calculation for sales monitoring:

Sales Value = product_details.sales_one_month * product_details.price

Ratio Type Notifications:

  • Compare current period vs historical period sales values
  • Calculate percentage change using calculate_percentage_change()
  • Trigger notifications based on increase/decrease thresholds

Amount Type Notifications:

  • Compare current sales value against absolute thresholds
  • Trigger notifications when values exceed or fall below configured amounts

Date Range Processing

The system uses product_details.metrics_date for:

  • Current period filtering (current month)
  • Historical period filtering (previous month)
  • Latest data selection within date ranges

Subscription Filtering

Active subscription filtering is implemented via:

whereHas('group.activeSubscription')

This ensures only groups with active subscriptions receive notifications.

Frequency Overview

Timeline

timeline
    title Notification System Schedule
    section Sales Notifications
        Daily<br>(Ex. 00.00) : notification sale-one-month
                             : Process notification settings and trigger alerts

Expected Outcomes

When these commands execute successfully, the system delivers:

  • Automated Sales Monitoring: Continuous monitoring of sales performance changes for wishlist products and groups based on user-defined thresholds
  • Real-time Notification Delivery: Immediate delivery of notifications through both database storage and Pusher WebSocket service for instant user awareness
  • Configurable Threshold Management: Support for both ratio-based (percentage) and amount-based (absolute value) notification criteria with flexible comparison types
  • Group-based Notification Distribution: Efficient delivery to relevant users through group membership relationships with subscription-based filtering
  • Persistent Notification History: Complete audit trail of all notifications with read status tracking and structured JSON data storage
  • Background Job Processing: Reliable asynchronous processing of notification chunks to handle large volumes without blocking system performance
  • Error Handling and Fallback: Robust error handling with fallback to database-only delivery when external services fail
  • Data-driven Business Intelligence: Timely alerts enabling quick response to significant sales changes and market trends

Batch List

Name Description
Sale Notifications Command that processes notification settings for groups with active subscriptions to detect sales changes in wishlist products and groups