Configuration options, database schema, and deployment guidelines for SAT Usage Reports

Configuration & Deployment

This document provides detailed configuration options, database schema, and deployment guidelines for both portal and client systems.

Database Schema

Table: sat_usage_hourly_reports

CREATE TABLE sat_usage_hourly_reports (
    id BIGINT UNSIGNED PRIMARY KEY,
    client_id BIGINT UNSIGNED,
    client_account_id BIGINT UNSIGNED,
    client_code VARCHAR(255),
    start_date DATETIME,
    end_date DATETIME,
    total_licences INT,
    assigned_licences INT,
    active_users_in_field INT,
    active_users_in_backend INT,
    total_users INT,
    users_on_leave INT DEFAULT 0,
    visits INT,
    offroute_visits INT,
    total_visits INT,
    number_of_orders INT,
    orders_value DECIMAL(10,2),
    number_of_sales INT,
    sales_value DECIMAL(10,2),
    no_deliveries INT,
    current_branch VARCHAR(255),
    request_json TEXT,
    response_json TEXT,
    callback_json TEXT,
    status VARCHAR(255) DEFAULT 'pending',
    message VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    
    INDEX idx_client_id (client_id),
    INDEX idx_client_account_id (client_account_id),
    INDEX idx_start_date (start_date),
    INDEX idx_end_date (end_date),
    INDEX idx_status (status)
);

Table: sat_usage_daily_reports

Similar structure to hourly reports but with date field instead of start_date/end_date.

CREATE TABLE sat_usage_daily_reports (
    id BIGINT UNSIGNED PRIMARY KEY,
    client_id BIGINT UNSIGNED,
    client_account_id BIGINT UNSIGNED,
    client_code VARCHAR(255),
    date DATE,
    total_licences INT,
    assigned_licences INT,
    active_users_in_field INT,
    active_users_in_backend INT,
    total_users INT,
    users_on_leave INT DEFAULT 0,
    visits INT,
    offroute_visits INT,
    total_visits INT,
    number_of_orders INT,
    orders_value DECIMAL(10,2),
    number_of_sales INT,
    sales_value DECIMAL(10,2),
    no_deliveries INT,
    current_branch VARCHAR(255),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    
    INDEX idx_client_id (client_id),
    INDEX idx_client_account_id (client_account_id),
    INDEX idx_date (date),
    UNIQUE KEY unique_client_date (client_id, client_account_id, date)
);

Portal System Configuration

Environment Variables

# Portal API endpoints
SAT_USAGE_REPORTS.SYNC_REPORT=v1/sat-usage-reports/retrieve
SAT_USAGE_REPORTS.SYNC_REPORT_CALLBACK=v1/sat-usage-reports/callback
SAT_USAGE_REPORTS.TRIGGER_CLIENT_CALLBACK_URL=v1/sat-usage-reports/request-client-send-sat-radar-report

# Sync configuration
SAT_USAGE_SYNC_TIMEOUT=30
SAT_USAGE_RETRY_ATTEMPTS=3
SAT_USAGE_RETRY_DELAY=10

# Queue configuration
SAT_USAGE_QUEUE_NAME=sat-usage-reports
SAT_USAGE_QUEUE_DELAY=0

Config File: config/reports/sat_usage_reports.php

<?php

return [
    /*
    |--------------------------------------------------------------------------
    | SAT Usage Reports Configuration
    |--------------------------------------------------------------------------
    |
    | Configuration options for SAT Usage Reports synchronization and processing
    |
    */

    // API Endpoints
    'sync_report' => env('SAT_USAGE_REPORTS.SYNC_REPORT', 'v1/sat-usage-reports/retrieve'),
    'sync_report_callback' => env('SAT_USAGE_REPORTS.SYNC_REPORT_CALLBACK', 'v1/sat-usage-reports/callback'),
    'trigger_client_callback_url' => env('SAT_USAGE_REPORTS.TRIGGER_CLIENT_CALLBACK_URL', 'v1/sat-usage-reports/request-client-send-sat-radar-report'),

    // Sync Settings
    'timeout' => env('SAT_USAGE_SYNC_TIMEOUT', 30),
    'retry_attempts' => env('SAT_USAGE_RETRY_ATTEMPTS', 3),
    'retry_delay' => env('SAT_USAGE_RETRY_DELAY', 10),

    // Queue Settings
    'queue_name' => env('SAT_USAGE_QUEUE_NAME', 'sat-usage-reports'),
    'queue_delay' => env('SAT_USAGE_QUEUE_DELAY', 0),

    // Processing Settings
    'batch_size' => env('SAT_USAGE_BATCH_SIZE', 50),
    'max_parallel_requests' => env('SAT_USAGE_MAX_PARALLEL', 10),

    // Logging
    'log_channel' => env('SAT_USAGE_LOG_CHANNEL', 'sat-usage'),
    'log_level' => env('SAT_USAGE_LOG_LEVEL', 'info'),
];

Logging Configuration: config/logging.php

'channels' => [
    // ... existing channels

    'sat-usage' => [
        'driver' => 'daily',
        'path' => storage_path('logs/sat-usage.log'),
        'level' => env('SAT_USAGE_LOG_LEVEL', 'info'),
        'days' => 14,
    ],
],

Scheduled Commands: app/Console/Kernel.php

protected function schedule(Schedule $schedule)
{
    // SAT Usage Reports hourly sync
    $schedule->command('sync:sat-usage-reports')
        ->hourly()
        ->withoutOverlapping()
        ->runInBackground()
        ->appendOutputTo(storage_path('logs/scheduler-sat-usage.log'));
}

Client System Configuration (SAT Usage Radar)

Environment Variables

# Portal Integration
SAT_USAGE_BASE_URL=https://portal.example.com
SAT_USAGE_SYNC_ENDPOINT=v1/sat-usage-reports/sync-hourly-sat-usage-data
SAT_USAGE_CALLBACK_ENDPOINT=v1/sat-usage-reports/callback

# Authentication
SAT_USAGE_CLIENT_TOKEN=your_client_token_here
SAT_USAGE_CLIENT_ID=your_client_id_here

# System Behavior
SETTINGS_USE_SALES_ORDER=true  # Enables Orders V2 system
SAT_SYNC_ENABLED=true          # Enables SAT Usage Radar

# Sync Configuration
SAT_USAGE_SYNC_TIMEOUT=30
SAT_USAGE_RETRY_ATTEMPTS=3
SAT_USAGE_RETRY_DELAY=5

# Data Collection
SAT_USAGE_BATCH_SIZE=1000
SAT_USAGE_MEMORY_LIMIT=256M

System Settings Configuration

// Client-side settings that affect data collection
return [
    // Orders System Configuration
    'settings.use_sales_order' => env('SETTINGS_USE_SALES_ORDER', true), // Determines Orders V1 vs V2 usage
    
    // Portal Integration
    'sat-usage.base_url' => env('SAT_USAGE_BASE_URL', 'https://portal.example.com'),
    'sat-usage.sync-hourly-sat-usage-data' => env('SAT_USAGE_SYNC_ENDPOINT', 'v1/sat-usage-reports/sync-hourly-sat-usage-data'),
    'sat-usage.callback_endpoint' => env('SAT_USAGE_CALLBACK_ENDPOINT', 'v1/sat-usage-reports/callback'),
    
    // Authentication
    'sat-usage.client_token' => env('SAT_USAGE_CLIENT_TOKEN'),
    'sat-usage.client_id' => env('SAT_USAGE_CLIENT_ID'),
    
    // Sync Configuration
    'sat-usage.timeout' => env('SAT_USAGE_SYNC_TIMEOUT', 30),
    'sat-usage.retry_attempts' => env('SAT_USAGE_RETRY_ATTEMPTS', 3),
    'sat-usage.retry_delay' => env('SAT_USAGE_RETRY_DELAY', 5),
    
    // Performance
    'sat-usage.batch_size' => env('SAT_USAGE_BATCH_SIZE', 1000),
    'sat-usage.memory_limit' => env('SAT_USAGE_MEMORY_LIMIT', '256M'),
];

Filter Parameters Configuration

// Time-based filtering for data collection
$default_filters = [
    // Time Range (required)
    'start_date' => '2025-10-29 00:00:00',  // Beginning of reporting period
    'end_date' => '2025-10-29 23:59:59',    // End of reporting period
    
    // Optional Filters
    'user_types' => [1, 2, 3, 4],          // User types to include
    'include_on_leave' => false,           // Include users on leave
    'min_activity_threshold' => 1,         // Minimum activity for "active" users
    
    // Performance Tuning
    'chunk_size' => 1000,                  // Database query chunk size
    'memory_limit' => '256M',              // Memory limit for processing
];

Authentication Configuration

Portal Authentication Setup

Client Authentication Middleware

// app/Http/Middleware/ClientAuthenticationMiddleware.php
class ClientAuthenticationMiddleware
{
    public function handle($request, Closure $next)
    {
        $token = $request->bearerToken();
        
        if (!$token) {
            return response()->json(['error' => 'Token required'], 401);
        }
        
        $client = Client::where('api_token', $token)
            ->where('status', 'active')
            ->first();
            
        if (!$client) {
            return response()->json(['error' => 'Invalid token'], 401);
        }
        
        $request->attributes->set('client', $client);
        
        return $next($request);
    }
}

Route Configuration

// routes/api.php
Route::middleware(['client.auth'])->group(function () {
    Route::post('/process-client-provided-sat-usage-report', 
        [SatUsageReportsController::class, 'processClientData']);
});

Route::middleware(['auth:api'])->group(function () {
    Route::get('/sat-usage-reports', [SatUsageReportsController::class, 'index']);
    Route::get('/sat-usage-aggregates', [SatUsageReportsController::class, 'aggregates']);
    Route::get('/sat-usage-export', [SatUsageReportsController::class, 'export']);
});

Client Authentication Setup

HTTP Client Configuration

// Client-side HTTP client setup
class SatUsageHttpClient
{
    private $baseUrl;
    private $token;
    private $timeout;
    
    public function __construct()
    {
        $this->baseUrl = config('sat-usage.base_url');
        $this->token = config('sat-usage.client_token');
        $this->timeout = config('sat-usage.timeout', 30);
    }
    
    public function sendData($endpoint, $data)
    {
        $headers = [
            'Authorization' => 'Bearer ' . $this->token,
            'Content-Type' => 'application/json',
            'Accept' => 'application/json',
            'User-Agent' => 'SAT-Usage-Radar/1.0'
        ];
        
        $client = new GuzzleHttp\Client([
            'timeout' => $this->timeout,
            'verify' => true, // SSL verification
        ]);
        
        return $client->post($this->baseUrl . '/' . $endpoint, [
            'headers' => $headers,
            'json' => $data
        ]);
    }
}

Network Configuration

Firewall and Security

Portal-Side Security

# Allow HTTPS traffic from client systems
iptables -A INPUT -p tcp --dport 443 -s client_ip_range -j ACCEPT

# Rate limiting for API endpoints
iptables -A INPUT -p tcp --dport 443 -m limit --limit 25/minute --limit-burst 100 -j ACCEPT

Client-Side Security

# Allow outbound HTTPS to portal
iptables -A OUTPUT -p tcp --dport 443 -d portal_ip -j ACCEPT

# DNS resolution
iptables -A OUTPUT -p udp --dport 53 -j ACCEPT

Load Balancing Configuration

Nginx Configuration (Portal)

upstream sat_usage_backend {
    server 127.0.0.1:8000 weight=3;
    server 127.0.0.1:8001 weight=2;
    server 127.0.0.1:8002 weight=1;
}

server {
    listen 443 ssl;
    server_name portal.example.com;
    
    # SSL configuration
    ssl_certificate /path/to/certificate.crt;
    ssl_certificate_key /path/to/private.key;
    
    # API endpoints
    location /api/sat-usage {
        proxy_pass http://sat_usage_backend;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_connect_timeout 30s;
        proxy_send_timeout 30s;
        proxy_read_timeout 60s;
    }
}

Deployment Guidelines

Portal System Deployment

Prerequisites

# System requirements
- PHP 8.1+
- MySQL 8.0+
- Redis 6.0+
- Nginx 1.18+
- Supervisor for queue workers

Deployment Steps

# 1. Clone repository and install dependencies
git clone repository_url
cd sat-usage-portal
composer install --no-dev --optimize-autoloader

# 2. Environment configuration
cp .env.example .env
# Configure database, Redis, and SAT Usage settings

# 3. Database setup
php artisan migrate
php artisan db:seed --class=SatUsageSeeder

# 4. Cache optimization
php artisan config:cache
php artisan route:cache
php artisan view:cache

# 5. Queue workers setup
sudo supervisorctl reread
sudo supervisorctl update
sudo supervisorctl start sat-usage-worker:*

# 6. Scheduled jobs
php artisan schedule:work # or setup cron job

Supervisor Configuration

[program:sat-usage-worker]
process_name=%(program_name)s_%(process_num)02d
command=php /path/to/portal/artisan queue:work redis --queue=sat-usage-reports --sleep=3 --tries=3 --max-time=3600
autostart=true
autorestart=true
stopasgroup=true
killasgroup=true
user=www-data
numprocs=3
redirect_stderr=true
stdout_logfile=/path/to/portal/storage/logs/worker.log
stopwaitsecs=3600

Client System Deployment

Prerequisites

# System requirements
- PHP 8.0+
- MySQL 5.7+
- Existing SAT application
- Cron job access

Deployment Steps

# 1. Deploy SAT Usage Radar service
cp SatUsageReports/ app/Services/Reports/
composer dump-autoload

# 2. Environment configuration
# Add SAT Usage variables to .env file

# 3. Database migration (if needed)
php artisan migrate

# 4. Configuration verification
php artisan config:cache

# 5. Test connectivity
php artisan sat-usage:test-connection

# 6. Schedule setup (optional - for automatic triggers)
# Add to crontab if client-side triggering is needed

Cron Job Configuration (Optional)

# Optional: Client-side scheduled data collection
# Add to crontab for redundancy
0 * * * * cd /path/to/client && php artisan sat-usage:collect-and-send

Health Checks and Monitoring

Portal Health Checks

// Health check endpoint
Route::get('/health/sat-usage', function () {
    $checks = [
        'database' => DB::connection()->getPdo() ? 'ok' : 'failed',
        'redis' => Redis::ping() ? 'ok' : 'failed',
        'queue' => Queue::size('sat-usage-reports') >= 0 ? 'ok' : 'failed',
        'last_sync' => SatUsageHourlyReport::latest()->first()?->created_at,
    ];
    
    return response()->json($checks);
});

Client Health Checks

// Client connectivity test command
class TestSatUsageConnection extends Command
{
    public function handle()
    {
        try {
            $client = new SatUsageHttpClient();
            $response = $client->get('/health');
            
            $this->info('Connection successful: ' . $response->getStatusCode());
            return 0;
        } catch (Exception $e) {
            $this->error('Connection failed: ' . $e->getMessage());
            return 1;
        }
    }
}

Architecture & Components: System design and components overview
API & Synchronization: API endpoints and sync processes

Configuration & Deployment

On this page