DocumentEventHandler

Publisher Platform Document Event Processing & SQL Synchronization Microservice

Service Type: Kubernetes Microservice
Technology: Node.js (JavaScript) with Sequelize ORM
Runtime: Node.js Container
Last Updated: 2025-07-01

---

📋 Overview

The DocumentEventHandler microservice is a critical data synchronization service that processes document change events from the Publisher platform and maintains synchronized copies in SQL Server databases. It acts as a bridge between the NoSQL document storage (Cosmos DB) and relational SQL databases, ensuring data consistency across different storage systems and enabling SQL-based reporting and analytics.

Key Features

• Event-Driven Synchronization: Processes document change events in real-time
• Multi-Collection Support: Handles campaigns, vendors, and report status synchronization
• SQL Server Integration: Maintains synchronized SQL Server tables using Sequelize ORM
• Retry Mechanism: Robust retry logic with configurable retry attempts
• Report Processing: Specialized handling for report generation events
• Error Handling: Comprehensive error handling with alerting for failed operations
• Traffic Type Mapping: Intelligent mapping of document types to SQL schema

🛠 Technology Stack

Component	Technology
Language	JavaScript (Node.js)
Runtime	Node.js Container
Framework	Event-driven processing
ORM	Sequelize
Database	SQL Server (via Tedious driver)
NoSQL	Azure Cosmos DB
Build Tool	Webpack
Container	Docker
Orchestration	Kubernetes

🏗 Architecture

Service Architecture

• Deployment Pattern: Kubernetes Deployment with Event Hub integration
• Input: Event Hub messages from document change events
• Processing: Model-based document synchronization
• Output: SQL Server database updates and error logging
• Retry Strategy: Configurable retry mechanism with exponential backoff
• Monitoring: Comprehensive logging and alerting

System Flow

graph TD
    A[Document Change Event] --> B[DocumentEventHandler]
    B --> C[Event Hub Message Processing]
    C --> D[Message Parsing & Validation]
    D --> E{Event Hub Type}
    E -->|documentevent| F[Document Event Processing]
    E -->|report/generatedreport/applog| G[Report Status Processing]
    F --> H{Collection Type}
    H -->|campaigns| I[Campaign Model]
    H -->|vendors| J[Vendor Model]
    I --> K[SQL Server Sync]
    J --> K
    G --> L[Report Status Model]
    L --> M[Report Database Sync]
    K --> N[Success Response]
    M --> N
    K --> O[Error Handling & Retry]
    M --> O

🔄 Processing Pipeline

Document Event Processing Flow

sequenceDiagram
    participant EventHub as Event Hub
    participant Handler as DocumentEventHandler
    participant Model as Data Model
    participant SQL as SQL Server
    participant Cosmos as Cosmos DB

    EventHub->>Handler: Document Change Event
    Handler->>Handler: Parse Message
    Handler->>Handler: Determine Collection Type
    Handler->>Model: Route to Appropriate Model
    Model->>Cosmos: Get Additional Data (if needed)
    Cosmos-->>Model: Document Data
    Model->>SQL: Execute SQL Operation
    SQL-->>Model: Operation Result
    Model-->>Handler: Processing Result
    Handler-->>EventHub: Success/Error Response

Retry Mechanism Flow

graph TD
    A[Processing Failure] --> B{Retry Attempt < Max?}
    B -->|Yes| C[Increment Retry Count]
    B -->|No| D[Log Alert & Fail]
    C --> E[Wait for Backoff]
    E --> F[Retry Processing]
    F --> G{Success?}
    G -->|Yes| H[Complete]
    G -->|No| A
    D --> I[Send Alert]

📡 Event Processing

Supported Event Hubs

Event Hub	Purpose	Models Used
documentevent	Document CRUD operations	Campaigns, Vendors
report	Report generation events	ReportStatus
generatedreport	Generated report events	ReportStatus
applog	Application log events	ReportStatus

Document Event Structure

{
  "recordid": "unique-event-id",
  "ts": 1640995200000,
  "collection": "campaigns",
  "action": "CREATE|UPDATE|DELETE",
  "organizationId": "vendor-123",
  "auth": {
    "name": "user@example.com"
  },
  "record": {
    "id": "campaign-456",
    "friendlyName": "Campaign Name",
    "campaignId": "campaign-456",
    "vendorId": "vendor-123",
    "product": "auto",
    "status": "active"
  },
  "oldRecord": {
    // Previous state for UPDATE operations
  }
}

Report Event Structure

{
  "reportId": "report-123",
  "status": "PROCESSING|COMPLETED|FAILED",
  "timestamp": "2025-01-01T12:00:00.000Z",
  "metadata": {
    "reportType": "campaign-performance",
    "parameters": { /* report parameters */ }
  }
}

🔧 Business Logic

Collection Processing

Campaigns Collection

SQL Table: CAMPAIGN Primary Keys: CAMPAIGN_ID, VENDOR_ID

Supported Operations: - CREATE: Insert new campaign record - UPDATE: Update existing campaign record - DELETE: Remove campaign record

Data Mapping:

// Document to SQL mapping
{
  CAMPAIGN_ID: record.campaignId,
  VENDOR_ID: record.vendorId,
  TYPE: trafficTypeMapping[record.trafficType] || record.trafficType,
  PRODUCT: record.product,
  CBRAND: record.brand || "QW",
  COST_TYPE: record.costType,
  PIXEL: record.pixel,
  STATUS: record.status,
  MARGIN: record.margin,
  TIER: record.tier,
  CREATE_DTG: formatDate(record.createdDate),
  UPDATE_DTG: formatDate(record.updatedDate)
}

Vendors Collection

SQL Table: VENDOR Primary Key: VENDOR_ID

Supported Operations: - CREATE: Insert new vendor record - UPDATE: Update existing vendor record - DELETE: Remove vendor record

Report Status Processing

Purpose: Track report generation status and metadata Event Hubs: report, generatedreport, applog

Processing Logic: - Updates report status in tracking database - Maintains report generation history - Handles report completion notifications

Traffic Type Mapping

The service uses a configuration file (trafficTypeMapping.json) to map document traffic types to SQL schema values:

{
  "web-form": "WEB",
  "mobile-app": "MOBILE",
  "api-integration": "API",
  "widget-embed": "WIDGET"
}

Error Handling Strategy

Retry Logic

• Max Retries: Configurable (default: 3 attempts)
• Retry Header: x-retries header tracks attempt count
• Connection Errors: Special handling for database connection failures
• Alert Thresholds: Alerts triggered after max retries exceeded

Error Categories

1. Connection Errors: Database connectivity issues
2. Validation Errors: Invalid message format or missing required fields
3. Business Logic Errors: Constraint violations or business rule failures
4. Unknown Errors: Unexpected processing failures

⚙️ Configuration

Environment Variables

• SQL Server Configuration: Connection strings and database settings
• Cosmos DB Configuration: Connection strings for additional data lookups
• Retry Settings: Max retry attempts and backoff configuration
• Logging: Log levels and debug settings

Key Configuration Files

• config/config.js: Main configuration
• config/config.int.js: Integration environment
• config/config.prod.js: Production environment

Database Configuration

{
  "SqlServer": {
    "QwSqlServer_DocumentEventHandler": "connection-string"
  },
  "PublisherCosmos": {
    "connectionString": "cosmos-connection-string",
    "database": "publishers",
    "container": "Vendors"
  },
  "maxRetries": 3,
  "LogLevel": "INFO"
}

🚀 Deployment

Kubernetes Deployment

# Apply Kubernetes manifests
kubectl apply -f k8s/

# Check deployment status
kubectl get pods -l app=documenteventhandler

# View logs
kubectl logs -l app=documenteventhandler -f

Local Development

# Install dependencies
npm install

# Run tests
npm test

# Start service
npm start

📊 Monitoring & Health Checks

Health Endpoints

• Liveness: /live - Basic health check
• Readiness: /ready - Service readiness check

Key Metrics to Monitor

• Event processing rate by collection type
• SQL Server operation success rates
• Retry attempt frequency
• Error rates by error type
• Database connection health
• Processing latency per event type

Business Metrics

• Data Synchronization Lag: Time between document change and SQL update
• Sync Success Rate: Percentage of successful synchronizations
• Collection Coverage: Number of collections being synchronized
• Report Processing Performance: Report event processing metrics

🔧 Troubleshooting

Common Issues

SQL Server Connection Failures

1. Check SQL Server connectivity and credentials
2. Verify connection string configuration
3. Monitor connection pool status
4. Check network connectivity between service and database

Event Processing Failures

# Check Event Hub connectivity
# Verify message format and required fields
# Review retry attempt logs

Data Synchronization Issues

1. Missing Records: Check for processing errors in logs
2. Stale Data: Verify event ordering and processing delays
3. Constraint Violations: Review SQL schema constraints and data validation

Performance Issues

• Slow Processing: Monitor SQL Server performance and query execution
• High Memory Usage: Check Sequelize connection pooling and memory leaks
• Event Backlog: Monitor Event Hub consumer lag

🧪 Testing

Unit Tests

npm test

Integration Testing

# Test with sample document events
node testReport.js

# Test specific collection processing
node test.js

Manual Testing

Send test events to Event Hub and monitor processing:

{
  "collection": "campaigns",
  "action": "CREATE",
  "record": {
    "campaignId": "test-123",
    "vendorId": "vendor-456",
    "product": "auto",
    "status": "active"
  }
}

🤝 Dependencies

Internal Dependencies

• DocumentCRUD Service: Source of document change events
• AuditHandler Service: Audit trail integration
• Event Hub Infrastructure: Event message delivery

External Dependencies

• SQL Server: Primary synchronization target database
• Azure Cosmos DB: Additional document data lookups
• Sequelize ORM: Database abstraction and query building

NPM Dependencies

• sequelize: ORM for SQL Server integration
• tedious: SQL Server driver
• @azure/cosmos: Cosmos DB client
• dateformat: Date formatting utilities
• async: Asynchronous processing utilities

---

This documentation was created through manual code analysis of the DocumentEventHandler microservice codebase.