Database Schema

Understanding the database structure is crucial for working with the LPO system.

Core Tables

eva_ocr_lpos (Main LPO Records)

Primary table storing LPO document information and processing status.

CREATE TABLE eva_ocr_lpos (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sat_reference_id VARCHAR(36) UNIQUE NOT NULL, -- UUID for tracking
    outlet_id BIGINT NULL,
    customer_set_id BIGINT NULL,
    order_id BIGINT NULL,
    lpo_number VARCHAR(255) NULL,
    delivery_date DATE NULL,
    page_count INT DEFAULT 1,
    status ENUM('PENDING', 'PROCESSING', 'PROCESSED', 'CONVERTED', 'FAILED', 'ARCHIVED') NOT NULL,
    status_reason TEXT NULL,
    prefer_lpo_prices BOOLEAN DEFAULT FALSE,
    converted_uoms BOOLEAN DEFAULT FALSE,
    attachment_link VARCHAR(500) NOT NULL,
    source ENUM('dashboard', 'email') DEFAULT 'dashboard',
    email_address VARCHAR(255) NULL,
    created_by BIGINT NOT NULL,
    entry_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_sat_reference (sat_reference_id),
    INDEX idx_status (status),
    INDEX idx_outlet (outlet_id),
    INDEX idx_customer (customer_set_id),
    INDEX idx_created_by (created_by),
    INDEX idx_source (source),
    
    FOREIGN KEY (outlet_id) REFERENCES outlets(id),
    FOREIGN KEY (customer_set_id) REFERENCES customer_sets(id),
    FOREIGN KEY (order_id) REFERENCES sales_orders(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

Key Fields:

sat_reference_id: UUID for external tracking and callbacks
status: Document processing status (see lifecycle documentation)
attachment_link: Relative path to file in CDN storage
source: Indicates upload method (dashboard vs email)
prefer_lpo_prices: When true, extracted prices override system prices

eva_ocr_lpo_items (LPO Line Items)

Stores individual line items extracted from LPO documents.

CREATE TABLE eva_ocr_lpo_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    sat_reference_id VARCHAR(36) NOT NULL,
    product_id BIGINT NULL,
    product_barcode VARCHAR(255) NULL,
    eva_barcode VARCHAR(255) NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 0,
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    total_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    lpo_unit_price DECIMAL(10,2) NULL, -- Original price from LPO
    lpo_total_price DECIMAL(10,2) NULL, -- Original total from LPO
    description TEXT NULL,
    packaging_id BIGINT NULL,
    uom_id BIGINT NULL,
    price_updated_manually BOOLEAN DEFAULT FALSE,
    if_product_empties BOOLEAN DEFAULT FALSE,
    product_set_id BIGINT NULL, -- For fuzzy match suggestions
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_sat_reference (sat_reference_id),
    INDEX idx_product (product_id),
    INDEX idx_barcode (product_barcode),
    INDEX idx_eva_barcode (eva_barcode),
    INDEX idx_product_set (product_set_id),
    
    FOREIGN KEY (sat_reference_id) REFERENCES eva_ocr_lpos(sat_reference_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (packaging_id) REFERENCES packagings(id),
    FOREIGN KEY (uom_id) REFERENCES unit_of_measures(id),
    FOREIGN KEY (product_set_id) REFERENCES frequent_product_sets(id)
);

Key Fields:

sat_reference_id: Links to parent LPO document
lpo_unit_price/lpo_total_price: Original prices from document
price_updated_manually: Tracks manual price overrides
product_set_id: Links to fuzzy match suggestions

eva_ocr_lpo_item_histories (Change Tracking)

Audit trail for all changes made to LPO items.

CREATE TABLE eva_ocr_lpo_item_histories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    lpo_item_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    changed_by BIGINT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_lpo_item (lpo_item_id),
    INDEX idx_action_type (action_type),
    INDEX idx_changed_by (changed_by),
    INDEX idx_changed_at (changed_at),
    
    FOREIGN KEY (lpo_item_id) REFERENCES eva_ocr_lpo_items(id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES users(id)
);

Action Types:

product_match: Product assignment/change
price_update: Price modifications
uom_conversion: Unit of measure changes
quantity_update: Quantity adjustments
manual_edit: General manual edits

Supporting Tables

EvaDocsProductCode (Customer-Specific Product Mappings)

Maps customer-specific product codes to system products.

CREATE TABLE eva_docs_product_codes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_account_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    lpo_product_code VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_customer_code (customer_account_id, lpo_product_code),
    INDEX idx_customer (customer_account_id),
    INDEX idx_product (product_id),
    INDEX idx_lpo_code (lpo_product_code),
    
    FOREIGN KEY (customer_account_id) REFERENCES accounts(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Usage:

Handles cases where customers use internal product codes on LPOs
Enables customer-specific product matching rules
Supports historical code mappings for legacy customers

FrequentProductSets (Fuzzy Match Suggestions)

Stores AI-powered product suggestions when exact matching fails.

CREATE TABLE frequent_product_sets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_set_id VARCHAR(36) NOT NULL, -- UUID for grouping suggestions
    product_id BIGINT NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    priority INT DEFAULT 1,
    source VARCHAR(100) DEFAULT 'ai_matching',
    confidence_score DECIMAL(5,2) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_product_set (product_set_id),
    INDEX idx_product (product_id),
    INDEX idx_confidence (confidence_score),
    
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Features:

Groups multiple product suggestions by product_set_id
Confidence scores from MeiliSearch AI matching
Priority ordering for suggestion display

EvaUomConfiguration (UOM Settings per Outlet)

Configures unit of measure conversion preferences per outlet.

CREATE TABLE eva_uom_configurations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    outlet_id BIGINT NOT NULL,
    uom_size ENUM('largest', 'smallest', 'custom') NOT NULL,
    configuration_details JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_outlet_config (outlet_id),
    
    FOREIGN KEY (outlet_id) REFERENCES outlets(id)
);

Configuration Examples:

// Custom UOM rules
{
    "rules": {
        "beer": "cases",
        "spirits": "bottles",
        "default": "largest"
    },
    "conversion_factors": {
        "bottles_to_cases": 24,
        "cans_to_cases": 12
    }
}

Email Integration Tables

lpo_email_integrations

CREATE TABLE lpo_email_integrations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    email VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    sync_status VARCHAR(100) NULL,
    last_sync_at TIMESTAMP NULL,
    added_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_user_email (user_id, email),
    INDEX idx_status (status),
    INDEX idx_last_sync (last_sync_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (added_by) REFERENCES users(id)
);

Database Relationships

Entity Relationship Diagram

erDiagram
    eva_ocr_lpos ||--o{ eva_ocr_lpo_items : contains
    eva_ocr_lpo_items ||--o{ eva_ocr_lpo_item_histories : tracks
    eva_ocr_lpo_items }o--|| products : references
    eva_ocr_lpo_items }o--|| packagings : uses
    eva_ocr_lpo_items }o--|| unit_of_measures : measured_in
    eva_ocr_lpo_items }o--o| frequent_product_sets : suggests
    eva_ocr_lpos }o--|| outlets : belongs_to
    eva_ocr_lpos }o--|| customer_sets : serves
    eva_ocr_lpos }o--|| users : created_by
    eva_ocr_lpos }o--o| sales_orders : converts_to
    eva_docs_product_codes }o--|| accounts : maps_for
    eva_docs_product_codes }o--|| products : maps_to
    eva_uom_configurations }o--|| outlets : configures
    lpo_email_integrations }o--|| users : belongs_to

Database Schema

On this page