ERP Sync Architecture

This document describes the architecture for synchronizing data between ERP systems and the local database.

Overview

The ERP sync system maintains local replicas of ERP data for performance optimization and offline capabilities. It provides full-text search, connection isolation, and comprehensive sync tracking.

Architecture Diagram

graph TB
    subgraph "ERP Systems"
        ERP1[Prophet 21]
        ERP2[SAP]
        ERP3[Oracle]
    end

    subgraph "Sync Layer"
        SF[Sync Factory]
        PS[Prophet21 Sync Service]
        SS[SAP Sync Service]
        OS[Oracle Sync Service]
    end

    subgraph "Local Database"
        EP[erp_products]
        EC[erp_customers]
        ESA[erp_shipping_addresses]
        EIL[erp_inventory_locations]
        ESL[erp_sync_logs]
    end

    subgraph "Search Layer"
        FTS[Full-Text Search]
        IDX[GIN Indexes]
        VEC[tsvector Columns]
    end

    subgraph "Application Layer"
        API[ERP Service API]
        CACHE[Redis Cache]
        APP[Web Application]
    end

    ERP1 --> PS
    ERP2 --> SS
    ERP3 --> OS

    SF --> PS
    SF --> SS
    SF --> OS

    PS --> EP
    PS --> EC
    PS --> ESA
    PS --> EIL
    PS --> ESL

    SS --> EP
    SS --> EC
    SS --> ESA
    SS --> EIL
    SS --> ESL

    OS --> EP
    OS --> EC
    OS --> ESA
    OS --> EIL
    OS --> ESL

    EP --> FTS
    EC --> FTS
    ESA --> FTS

    FTS --> IDX
    FTS --> VEC

    EP --> API
    EC --> API
    ESA --> API
    EIL --> API

    API --> CACHE
    CACHE --> APP

    style SF fill:#fff3e0
    style FTS fill:#e8f5e8
    style API fill:#e1f5fe

Sync Process Flow

sequenceDiagram
    participant App as Application
    participant Factory as Sync Factory
    participant Service as ERP Sync Service
    participant ERP as ERP System
    participant DB as Local Database
    participant Log as Sync Logs

    App->>Factory: createSyncService(connection)
    Factory->>Service: new ERPSyncService(db, redis)

    App->>Service: syncProducts()
    Service->>Log: createSyncLog(start)

    Service->>ERP: fetchProducts(lastSync)
    ERP-->>Service: productData[]

    loop For each product
        Service->>DB: upsertProduct(product)
        Service->>DB: updateSearchVector(product)
    end

    Service->>Log: updateSyncLog(completed)
    Service-->>App: syncResults

    Note over Service,DB: Full-text search vectors updated
    Note over Log: Audit trail maintained

Data Synchronization Strategy

Entity Types

Products: Items, parts, inventory data
Customers: Customer master data with pricing
Shipping Addresses: Customer delivery locations
Inventory: Real-time stock levels by location

Sync Frequency

Scheduled: Daily at 2 AM UTC
Manual: On-demand via API triggers
Incremental: Based on last sync timestamp
Full: Weekly complete refresh

Connection Isolation

graph LR
    subgraph "Connection A"
        CA[Prophet21 A]
        PA[Products A]
        CUA[Customers A]
        SAA[Addresses A]
    end

    subgraph "Connection B"
        CB[Prophet21 B]
        PB[Products B]
        CUB[Customers B]
        SAB[Addresses B]
    end

    subgraph "Shared Infrastructure"
        SF[Sync Factory]
        SL[Sync Logs]
        FTS[Full-Text Search]
    end

    CA --> PA
    CA --> CUA
    CA --> SAA

    CB --> PB
    CB --> CUB
    CB --> SAB

    PA --> FTS
    PB --> FTS
    CUA --> FTS
    CUB --> FTS

    SF --> SL

    style SF fill:#fff3e0
    style FTS fill:#e8f5e8

Search Optimization

Full-Text Search Implementation

tsvector columns: Optimized search vectors
GIN indexes: Fast text search performance
Multi-language support: Configurable search languages
Fuzzy matching: Similarity-based product matching

Search Performance

-- Example full-text search query
SELECT * FROM erp_products
WHERE product_search_vector @@ plainto_tsquery('pump valve')
ORDER BY ts_rank(product_search_vector, plainto_tsquery('pump valve')) DESC;

Error Handling and Recovery

Sync Failure Scenarios

Network timeouts: Retry with exponential backoff
Authentication failures: Refresh tokens automatically
Data validation errors: Log and continue with valid records
Partial failures: Track progress and resume from last success

Monitoring and Alerting

Sync health dashboards: Real-time status monitoring
Failure notifications: Automated alerts for sync issues
Performance metrics: Sync duration and throughput tracking
Data quality checks: Validation of synchronized data

Configuration

Sync Settings

interface SyncConfig {
  batchSize: number; // Records per batch (default: 1000)
  maxRetries: number; // Retry attempts (default: 3)
  retryDelay: number; // Base delay in ms (default: 1000)
  fullSyncInterval: string; // Cron expression (default: weekly)
  enableIncrementalSync: boolean;
  searchLanguage: string; // PostgreSQL text search config
}

Connection Management

Connection pooling: Efficient database connections
Health monitoring: Automatic connection validation
Failover support: Backup connection handling
Rate limiting: Respect ERP API limits

Integration Points

Database Schema

erp_products: Product catalog with search vectors
erp_customers: Customer data with pricing methods
erp_shipping_addresses: Delivery locations
erp_inventory_locations: Stock levels by warehouse
erp_sync_logs: Comprehensive audit trail

External Dependencies

ERP APIs: Prophet21, SAP, Oracle integrations
Redis: Caching and coordination
PostgreSQL: Data persistence and search
Trigger.dev: Scheduled task execution

Performance Considerations

Batch processing: Efficient bulk operations
Index optimization: Strategic database indexing
Memory management: Controlled resource usage
Parallel processing: Concurrent sync operations