SAT-KDL Integration

Error Handling & Troubleshooting

Comprehensive guide for error handling, troubleshooting, and recovery procedures for the SAT-KDL integration

Error Handling & Troubleshooting

This guide provides comprehensive information about error handling patterns, common issues, and troubleshooting procedures for the SAT-KDL integration system.

Error Handling Architecture

Centralized Logging System

All SAT-KDL integration points use the LogSatRequest model for consistent error tracking:

// LogSatRequest model structure
class LogSatRequest extends Model
{
    protected $fillable = [
        'type',        // Integration type (customers, products, etc.)
        'doc_num',     // Document/transaction reference
        'payload',     // Data sent to SAT
        'response',    // SAT response (if successful)
        'error',       // Error message (if failed)
        'success',     // Boolean success indicator
    ];
}

Error Categories

Integration errors are categorized into several types:

1. Authentication Errors

Cause: Invalid credentials, expired tokens, authentication service unavailable
Impact: All integration operations fail
Resolution: Verify credentials, refresh tokens, check SAT authentication service

2. Network Connectivity Errors

Cause: Network timeouts, DNS issues, firewall blocks
Impact: Intermittent failures, sync delays
Resolution: Check network connectivity, verify firewall rules, increase timeouts

3. Data Validation Errors

Cause: Invalid data formats, missing required fields, business rule violations
Impact: Specific records fail to sync
Resolution: Fix source data, update validation rules, implement data cleanup

4. Business Logic Errors

Cause: Duplicate records, invalid references, constraint violations
Impact: Logical failures requiring business review
Resolution: Review business rules, fix data relationships, implement deduplication

Common Error Scenarios

Authentication Failures

Symptoms

{
    "error": "Authentication failed: Invalid API token",
    "success": false,
    "type": "customers"
}

Diagnostic Steps

# Test SAT authentication
curl -X POST "https://sat-api.example.com/auth" \
  -H "Content-Type: application/json" \
  -d '{"username": "your_username", "password": "your_password"}'

# Check token expiry
php artisan sat:check-auth

# Verify configuration
php artisan config:show | grep SAT

Resolution

// Refresh authentication token
$auth = new AuthenticateSAT();
$newToken = $auth->refreshToken();

// Update configuration if needed
config(['sat.api_token' => $newToken]);

Data Validation Failures

Customer Data Issues

-- Find customers with missing required data
SELECT Customer, Name, Area, Email
FROM ArCustomer 
WHERE (Name IS NULL OR Name = '')
   OR (Area IS NULL OR Area = '')
   OR (Email IS NOT NULL AND Email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Product Data Issues

-- Find products with invalid UOM data
SELECT StockCode, Description, StockUom, AlternateUom, ConvFactAltUom
FROM InvMaster 
WHERE (StockUom IS NULL OR StockUom = '')
   OR (AlternateUom IS NOT NULL AND (ConvFactAltUom IS NULL OR ConvFactAltUom <= 0));

Price List Issues

-- Find customers without valid buying groups
SELECT Customer, Name, BuyingGroup1, BuyingGroup2, BuyingGroup3, BuyingGroup4, BuyingGroup5
FROM ArCustomer 
WHERE (BuyingGroup1 IS NULL OR BuyingGroup1 = '')
  AND (BuyingGroup2 IS NULL OR BuyingGroup2 = '')
  AND (BuyingGroup3 IS NULL OR BuyingGroup3 = '')
  AND (BuyingGroup4 IS NULL OR BuyingGroup4 = '')
  AND (BuyingGroup5 IS NULL OR BuyingGroup5 = '');

Network Connectivity Issues

Symptoms

{
    "error": "Connection timeout to SAT API",
    "success": false,
    "type": "products"
}

Diagnostic Commands

# Test basic connectivity
ping sat-api.example.com

# Test HTTPS connectivity
curl -I https://sat-api.example.com

# Test specific endpoint
curl -X GET "https://sat-api.example.com/api/health" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  --connect-timeout 10 \
  --max-time 30

# Check DNS resolution
nslookup sat-api.example.com

# Test from Laravel
php artisan sat:test-connectivity

Error Recovery Strategies

Automatic Recovery

Many integration points implement automatic recovery mechanisms:

Retry Logic

class SATIntegrationService
{
    protected $maxRetries = 3;
    protected $retryDelay = [30, 60, 120]; // seconds
    
    public function syncWithRetry($data, $endpoint)
    {
        $attempt = 0;
        
        while ($attempt < $this->maxRetries) {
            try {
                return $this->sendToSAT($data, $endpoint);
            } catch (\Exception $e) {
                $attempt++;
                
                if ($attempt >= $this->maxRetries) {
                    throw $e; // Final failure
                }
                
                sleep($this->retryDelay[$attempt - 1]);
            }
        }
    }
}

Exponential Backoff

public function calculateBackoffDelay($attempt)
{
    return min(300, (2 ** $attempt) + random_int(0, 10)); // Max 5 minutes
}

Manual Recovery Procedures

Reset Sync Flags

-- Reset specific integration type
UPDATE ArCustomer SET SAT_SYNC = NULL 
WHERE Customer IN (
    SELECT doc_num FROM LogSatRequest 
    WHERE type = 'customers' AND success = false
);

-- Reset by date range
UPDATE InvMaster SET SAT_SYNC = NULL 
WHERE ModifiedDate >= '2024-01-15'
AND (SAT_SYNC IS NULL OR SAT_SYNC != 'Y');

-- Reset all failed syncs from last 24 hours
UPDATE ArCustomer SET SAT_SYNC = NULL 
WHERE Customer IN (
    SELECT doc_num FROM LogSatRequest 
    WHERE success = false 
    AND created_at >= NOW() - INTERVAL 24 HOUR
);

Bulk Retry Operations

# Retry specific integration
php artisan sat-pull:customers --retry-failed

# Retry all failed syncs
php artisan sat:retry-all-failed

# Retry with specific date range
php artisan sat:retry-failed --from=2024-01-15 --to=2024-01-16

Monitoring & Alerting

Key Performance Indicators

Success Rates

-- Overall success rate by integration type
SELECT 
    type,
    COUNT(CASE WHEN success = true THEN 1 END) as successful,
    COUNT(CASE WHEN success = false THEN 1 END) as failed,
    ROUND(COUNT(CASE WHEN success = true THEN 1 END) * 100.0 / COUNT(*), 2) as success_rate
FROM LogSatRequest 
WHERE created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY type;

Error Patterns

-- Most common errors
SELECT 
    type,
    error,
    COUNT(*) as occurrence_count,
    COUNT(DISTINCT doc_num) as affected_records
FROM LogSatRequest 
WHERE success = false 
AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY type, error
ORDER BY occurrence_count DESC;

Performance Metrics

-- Average processing time by type
SELECT 
    type,
    COUNT(*) as total_operations,
    AVG(TIMESTAMPDIFF(SECOND, created_at, updated_at)) as avg_processing_time,
    MAX(TIMESTAMPDIFF(SECOND, created_at, updated_at)) as max_processing_time
FROM LogSatRequest 
WHERE success = true
AND created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY type;

Alert Configuration

Laravel Notifications

class IntegrationFailureNotification extends Notification
{
    public function via($notifiable)
    {
        return ['mail', 'slack'];
    }
    
    public function toMail($notifiable)
    {
        return (new MailMessage)
            ->subject('SAT Integration Failure Alert')
            ->line('Multiple integration failures detected')
            ->line("Type: {$this->type}")
            ->line("Error Count: {$this->errorCount}")
            ->action('View Logs', url('/admin/integration-logs'));
    }
}

Monitoring Triggers

// Alert when error rate exceeds threshold
class IntegrationMonitor
{
    public function checkErrorRates()
    {
        $errorRate = LogSatRequest::where('created_at', '>=', now()->subHour())
            ->where('success', false)
            ->count() / LogSatRequest::where('created_at', '>=', now()->subHour())->count();
        
        if ($errorRate > 0.1) { // 10% error rate threshold
            $this->sendAlert('High error rate detected: ' . ($errorRate * 100) . '%');
        }
    }
}

Debugging Tools

Laravel Artisan Commands

Integration Status Check

# Check overall integration health
php artisan sat:health-check

# Check specific integration
php artisan sat:status --type=customers

# Validate data before sync
php artisan sat:validate --type=products --limit=100

Log Analysis

# View recent errors
php artisan sat:errors --hours=24

# Analyze error patterns
php artisan sat:error-analysis --type=customers

# Export error report
php artisan sat:error-report --output=csv --date=2024-01-15

Database Queries

Find Stuck Records

-- Records that have been pending for too long
SELECT type, doc_num, created_at, error
FROM LogSatRequest 
WHERE success = false 
AND created_at < NOW() - INTERVAL 1 HOUR
ORDER BY created_at;

-- Records with multiple failures
SELECT doc_num, type, COUNT(*) as failure_count, MAX(created_at) as last_attempt
FROM LogSatRequest 
WHERE success = false
GROUP BY doc_num, type
HAVING COUNT(*) > 3
ORDER BY failure_count DESC;

Data Consistency Checks

-- Customers marked as synced but no log entry
SELECT c.Customer, c.Name, c.SAT_SYNC
FROM ArCustomer c
LEFT JOIN LogSatRequest l ON c.Customer = l.doc_num AND l.type = 'customers' AND l.success = true
WHERE c.SAT_SYNC = 'Y' AND l.id IS NULL;

-- Products with sync flag mismatch
SELECT p.StockCode, p.SAT_SYNC, COUNT(l.id) as log_entries
FROM InvMaster p
LEFT JOIN LogSatRequest l ON p.StockCode = l.doc_num AND l.type = 'products'
WHERE p.SAT_SYNC = 'Y'
GROUP BY p.StockCode, p.SAT_SYNC
HAVING COUNT(l.id) = 0;

Performance Troubleshooting

Database Performance

Query Optimization

-- Analyze slow queries
EXPLAIN SELECT * FROM ArCustomer 
WHERE (SAT_SYNC IS NULL OR SAT_SYNC != 'Y') 
AND Area IS NOT NULL AND Area != '';

-- Index recommendations
CREATE INDEX idx_arcustomer_sat_sync_area ON ArCustomer(SAT_SYNC, Area);
CREATE INDEX idx_invmaster_sat_sync_warehouse ON InvMaster(SAT_SYNC, WarehouseToUse);
CREATE INDEX idx_logsatrequest_type_success_created ON LogSatRequest(type, success, created_at);

Database Maintenance

# Optimize tables
mysql -e "OPTIMIZE TABLE LogSatRequest, ArCustomer, InvMaster;"

# Update statistics
mysql -e "ANALYZE TABLE LogSatRequest, ArCustomer, InvMaster;"

# Check table sizes
mysql -e "SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)' 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
ORDER BY (data_length + index_length) DESC;"

Memory and CPU Issues

Laravel Queue Optimization

# Monitor queue worker memory usage
ps aux | grep "queue:work"

# Restart workers if memory usage is high
php artisan queue:restart

# Use memory-efficient processing
php artisan queue:work --memory=256 --timeout=60

Batch Processing Optimization

// Process in smaller chunks
Customer::chunk(100, function ($customers) {
    foreach ($customers as $customer) {
        // Process individual customer
    }
    
    // Clear memory after each chunk
    gc_collect_cycles();
});

Data Recovery Procedures

Backup and Restore

Create Data Backups

# Backup integration logs
mysqldump your_database LogSatRequest > logsatrequest_backup.sql

# Backup sync flags
mysqldump your_database ArCustomer --where="SAT_SYNC='Y'" > customer_sync_backup.sql

Restore Sync States

-- Restore from backup if needed
SOURCE customer_sync_backup.sql;

-- Verify restoration
SELECT COUNT(*) FROM ArCustomer WHERE SAT_SYNC = 'Y';

Data Consistency Repair

Fix Inconsistent Sync Flags

-- Reset customers with successful log but wrong flag
UPDATE ArCustomer 
SET SAT_SYNC = 'Y' 
WHERE Customer IN (
    SELECT doc_num FROM LogSatRequest 
    WHERE type = 'customers' AND success = true
) AND (SAT_SYNC IS NULL OR SAT_SYNC != 'Y');

-- Reset products with wrong sync status
UPDATE InvMaster 
SET SAT_SYNC = 'Y' 
WHERE StockCode IN (
    SELECT doc_num FROM LogSatRequest 
    WHERE type = 'products' AND success = true
) AND (SAT_SYNC IS NULL OR SAT_SYNC != 'Y');

Best Practices for Error Prevention

Development Practices

Comprehensive Testing: Test all integration points thoroughly in development
Data Validation: Implement robust validation before sending data to SAT
Error Handling: Use try-catch blocks with specific exception handling
Logging: Log all operations with sufficient detail for debugging

Operational Practices

Regular Monitoring: Monitor integration health and error rates continuously
Proactive Maintenance: Fix data quality issues before they cause sync failures
Documentation: Keep detailed records of configuration changes and customizations
Backup Procedures: Maintain regular backups of integration logs and sync states

Configuration Management

Environment Separation: Use different SAT endpoints for development, staging, and production
Credential Management: Secure storage and rotation of API credentials
Timeout Configuration: Set appropriate timeouts for different operations
Rate Limiting: Implement rate limiting to avoid overwhelming SAT API

Emergency Procedures

Complete Integration Failure

Immediate Actions:
- Check SAT API status
- Verify network connectivity
- Review recent configuration changes
- Check Laravel scheduler status
Escalation Steps:
- Contact SAT support team
- Notify business stakeholders
- Implement manual workarounds if needed
- Document all actions taken
Recovery Process:
- Identify root cause
- Apply fixes
- Test thoroughly
- Resume normal operations
- Conduct post-incident review

Data Corruption Scenarios

Assessment:
- Identify scope of corruption
- Determine last known good state
- Assess business impact
Recovery:
- Stop all integration processes
- Restore from backups if necessary
- Re-sync affected data
- Validate data integrity
Prevention:
- Implement additional validation
- Improve backup procedures
- Add monitoring for data anomalies

Trip Management Integration

Documentation for the bi-directional trip management integration between KDL and SAT systems