Building a VoIP Monitoring PHP Dashboard

Multi-Tab Interface for Homer SIP, ViciDial Stats, Call Quality Analysis, and Server Health

What you will build: A production-grade, multi-tab PHP dashboard that unifies SIP call data from Homer, inbound/outbound call statistics from ViciDial (MySQL), call quality metrics from RTCP reports, audio recording analysis, and admin audit logs into a single, dark-themed management interface. The entire application runs in a Docker container with Apache and PHP 8.2, connecting to PostgreSQL (Homer), MySQL (ViciDial), and SQLite (local user management) simultaneously.

Who this is for: VoIP operations engineers, call center managers, PHP developers building monitoring tools, and anyone who needs a unified view of SIP infrastructure and call center performance without heavyweight frameworks.

Prerequisites: Docker and Docker Compose, PHP 8.2 knowledge, basic SQL (MySQL and PostgreSQL), a Homer/heplify deployment capturing SIP traffic, and one or more ViciDial servers with MySQL read-only access.

Time to build: 6-8 hours for the core dashboard with all tabs, another 3-4 hours for the audio analysis integration and AI features.

Table of Contents

1. The Problem: Fragmented VoIP Visibility
2. Architecture Overview
3. Project Structure
4. Docker Setup
5. Shared Configuration (config.php)
6. Authentication and Permission System
7. The Main Dashboard (index.php)
8. Tab: SIP Calls with Ladder Diagrams
9. Tab: Call Quality (RTCP Analysis)
10. Tab: SIP Registrations
11. Tab: Inbound Calls (ViciDial)
12. Tab: Manual/Zoiper Calls
13. Tab: Audit Log
14. Tab: Ring Group Monitoring
15. Recording Proxy (rec.php)
16. Audio Quality Analysis Integration
17. Call Investigation (AI-Powered)
18. Admin Panel (User Management)
19. CSV Export System
20. Frontend: Styling and JavaScript
21. Production Deployment Tips
22. Troubleshooting

1. The Problem: Fragmented VoIP Visibility {#1-the-problem}

Running a multi-server VoIP call center means your data lives in at least three different places:

• Homer stores raw SIP signaling (INVITEs, BYEs, error responses) in PostgreSQL. It tells you what happened at the protocol level but not who the agent was or how long the customer waited in queue.
• ViciDial stores call center data in MySQL -- agent assignments, queue times, call dispositions, recordings. But it has no visibility into SIP-level failures or quality metrics.
• RTCP reports (captured by Homer) contain jitter, packet loss, and codec quality data. These are essential for diagnosing audio quality complaints but are buried in a separate PostgreSQL table with no call context.

The result: when a customer complains about a dropped call or poor audio, you are alt-tabbing between three different interfaces, correlating timestamps manually, and hoping the clocks are synchronized.

This dashboard solves that by:

• Querying Homer PostgreSQL and ViciDial MySQL in the same page load
• Showing SIP ladder diagrams with both A-leg (provider-to-server) and B-leg (server-to-agent) traces
• Enriching RTCP quality data with caller/agent information from the SIP INVITE
• Providing inline audio playback with waveform visualization and AI-powered quality analysis
• Supporting multi-server views with per-user permission controls

2. Architecture Overview {#2-architecture-overview}

┌─────────────────────────────────────────────────────────────────────────┐
│                          BROWSER                                         │
│                                                                         │
│   ┌──────────────────────────────────────────────────────────────┐      │
│   │                  VoIP Dashboard (index.php)                   │      │
│   │  ┌─────────┐ ┌──────────┐ ┌─────────┐ ┌────────┐ ┌───────┐ │      │
│   │  │SIP Calls│ │ Quality  │ │ Inbound │ │ Manual │ │ Audit │ │      │
│   │  │ (Homer) │ │ (RTCP)   │ │ (Vici)  │ │(Zoiper)│ │  Log  │ │      │
│   │  └────┬────┘ └────┬─────┘ └────┬────┘ └───┬────┘ └───┬───┘ │      │
│   └───────┼──────────┼──────────┼────────────┼──────────┼──────┘      │
│           └──────────┼──────────┼────────────┘          │              │
│                      │          │                        │              │
└──────────────────────┼──────────┼────────────────────────┼──────────────┘
                       │          │                        │
              ┌────────┴──────────┴────────────────────────┴────────┐
              │              Docker: monitoring-webapp               │
              │              PHP 8.2 + Apache                       │
              │                                                     │
              │  ┌──────────┐  ┌──────────┐  ┌──────────────────┐  │
              │  │config.php│  │ rec.php   │  │ analyze.php      │  │
              │  │(shared)  │  │(recording │  │ (audio quality   │  │
              │  │          │  │ proxy)    │  │  proxy to Python) │  │
              │  └──────────┘  └──────────┘  └──────────────────┘  │
              └──────────┬──────────┬──────────────┬────────────────┘
                         │          │              │
           ┌─────────────┤          │              │
           │             │          │              │
   ┌───────▼───────┐  ┌─▼──────────▼──┐  ┌───────▼────────┐
   │  PostgreSQL   │  │  MySQL x N     │  │  SQLite        │
   │  (Homer SIP)  │  │  (ViciDial     │  │  (Dashboard    │
   │               │  │   servers)     │  │   users/perms) │
   │ hep_proto_1_  │  │ vicidial_      │  │ dashboard_     │
   │   call        │  │   closer_log   │  │   users        │
   │ hep_proto_5_  │  │ call_log       │  │               │
   │   default     │  │ recording_log  │  │               │
   └───────────────┘  └───────────────┘  └───────────────┘

Key design decisions:

1. No JavaScript framework. The frontend is vanilla HTML + CSS + JS. Every tab is rendered server-side with PHP. Client-side JS is only used for interactive elements (waveforms, async analysis calls, toggle actions).

2. Three database engines in one app. PostgreSQL for Homer SIP data, MySQL for each ViciDial server (multiple connections), and SQLite for the local user/permission store. This avoids any dependency on a single data platform.

3. Multi-server support. The configuration defines N ViciDial servers. Every tab aggregates data across all servers (or filters to one), and each row shows which server the data came from with a color-coded badge.

4. Permission-gated access. Users authenticate with their ViciDial credentials (validated against MySQL). An SQLite table controls which servers and tabs each user can see.

3. Project Structure {#3-project-structure}

webapp/
├── Dockerfile              # PHP 8.2 + Apache with mysqli, pgsql, sqlite3
├── config.php              # Shared configuration, DB credentials, helpers
├── index.php               # Main dashboard (all tabs)
├── admin.php               # User/permission management panel
├── admin-ask.php           # AI assistant proxy (admin only)
├── analyze.php             # Audio quality analysis proxy
├── investigate.php         # Call investigation proxy (SIP trace + AI)
├── rec.php                 # Recording stream proxy
└── data/
    └── users.db            # SQLite database (auto-created)

The data/ directory is writable by Apache and stores the SQLite database. Everything else is read-only PHP files.

4. Docker Setup {#4-docker-setup}

Dockerfile

The webapp needs three PHP extensions: mysqli for ViciDial MySQL, pgsql for Homer PostgreSQL, and sqlite3 (bundled) for local user management.

FROM php:8.2-apache
RUN apt-get update && apt-get install -y libpq-dev libsqlite3-dev \
    && docker-php-ext-install mysqli pgsql \
    && apt-get clean && rm -rf /var/lib/apt/lists/*
RUN a2enmod rewrite

This is intentionally minimal. No Composer, no framework dependencies, no Node.js build step. The PHP extensions are the only system-level dependencies.

Docker Compose Service

The webapp is one service in a larger monitoring stack. Here is the relevant excerpt:

version: "3.8"

services:
  # PostgreSQL shared by Homer and the dashboard
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres-init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - monitoring

  # Homer HEP collector -- writes SIP data to PostgreSQL
  heplify-server:
    image: sipcapture/heplify-server:latest
    container_name: heplify-server
    restart: unless-stopped
    ports:
      - "9060:9060/udp"
      - "9060:9060/tcp"
    environment:
      HEPLIFYSERVER_HEPADDR: "0.0.0.0:9060"
      HEPLIFYSERVER_DBSHEMA: "homer7"
      HEPLIFYSERVER_DBDRIVER: "postgres"
      HEPLIFYSERVER_DBADDR: "postgres:5432"
      HEPLIFYSERVER_DBUSER: "homer"
      HEPLIFYSERVER_DBPASS: "${HOMER_DB_PASSWORD}"
      HEPLIFYSERVER_DBDATATABLE: "homer_data"
      HEPLIFYSERVER_DBCONFTABLE: "homer_config"
      HEPLIFYSERVER_DBDROPDAYS: 7
      HEPLIFYSERVER_LOGLVL: "info"
      HEPLIFYSERVER_LOGSTD: "true"
      HEPLIFYSERVER_PROMADDR: "0.0.0.0:9096"
      HEPLIFYSERVER_DEDUP: "false"
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - monitoring

  # The VoIP Monitoring Dashboard
  webapp:
    build: ./webapp
    container_name: monitoring-webapp
    restart: unless-stopped
    ports:
      - "${WEBAPP_PORT:-8082}:80"
    volumes:
      - ./webapp:/var/www/html
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - monitoring

volumes:
  postgres_data:

networks:
  monitoring:
    driver: bridge

Important details:

• The webapp volume-mounts the source directory, so you can edit PHP files without rebuilding the container.
• The webapp is on the same Docker network as PostgreSQL, so it connects to postgres:5432 (the container hostname).
• The ViciDial MySQL servers are external to Docker -- the webapp connects to them over the network using their real IP addresses.
• The webapp port defaults to 8082 but is configurable via .env.

Environment File (.env)

# Database Passwords
POSTGRES_PASSWORD=YOUR_POSTGRES_PASSWORD
HOMER_DB_PASSWORD=YOUR_HOMER_DB_PASSWORD

# Monitor VPS IP (for URL generation)
MONITOR_IP=YOUR_SERVER_IP

# Ports
WEBAPP_PORT=8082
HOMER_PORT=9080

# Homer Retention
HOMER_RETENTION_DAYS=7

Building and Starting

cd /opt/monitoring
docker compose build webapp
docker compose up -d webapp

Verify the container is running:

docker ps | grep monitoring-webapp
# Should show: monitoring-webapp ... Up ... 0.0.0.0:8082->80/tcp

5. Shared Configuration (config.php) {#5-shared-configuration}

Every PHP page includes config.php. It defines the server list, database credentials, helper functions, SQLite schema, and the authentication system.

Server Definitions

<?php
/**
 * VoIP Monitoring Dashboard - Shared Configuration
 */
ini_set('display_errors', 0);
error_reporting(E_ALL);

$SERVERS = array(
    'uk' => array(
        'name'     => 'UK',
        'host'     => 'YOUR_UK_SERVER_IP',
        'label'    => 'Alpha',
        'flag'     => "\xF0\x9F\x87\xAC\xF0\x9F\x87\xA7",  // British flag emoji
        'color'    => '#1e40af',
        'campaign' => 'ukcapm',
    ),
    'romania' => array(
        'name'     => 'Romania',
        'host'     => 'YOUR_ROMANIA_SERVER_IP',
        'label'    => 'Charlie',
        'flag'     => "\xF0\x9F\x87\xB7\xF0\x9F\x87\xB4",
        'color'    => '#0369a1',
        'campaign' => 'romania',
    ),
    'france' => array(
        'name'     => 'France',
        'host'     => 'YOUR_FRANCE_SERVER_IP',
        'label'    => 'Delta',
        'flag'     => "\xF0\x9F\x87\xAB\xF0\x9F\x87\xB7",
        'color'    => '#7c3aed',
        'campaign' => 'france',
    ),
    'italy' => array(
        'name'     => 'Italy',
        'host'     => 'YOUR_ITALY_SERVER_IP',
        'label'    => 'Echo',
        'flag'     => "\xF0\x9F\x87\xAE\xF0\x9F\x87\xB9",
        'color'    => '#059669',
        'campaign' => 'itacamp',
    ),
);

Each server entry includes:

• host: The IP address used for both MySQL connections and Homer IP filtering
• color: Used for the server badge background and the server selector button
• campaign: The ViciDial campaign ID (used for campaign-specific queries)

Tab Definitions

$ALL_TABS = array(
    'sip_calls'     => array('label' => 'SIP Calls',       'group' => 'homer'),
    'quality'       => array('label' => 'Call Quality',     'group' => 'homer'),
    'registrations' => array('label' => 'Registrations',    'group' => 'homer'),
    'inbound'       => array('label' => 'Inbound Calls',    'group' => 'vici'),
    'manual'        => array('label' => 'Manual (Zoiper)',  'group' => 'vici'),
    'ringgroup'     => array('label' => 'Ring Group',       'group' => 'vici'),
    'audit'         => array('label' => 'Audit Log',        'group' => 'vici'),
    'other'         => array('label' => 'Other (call_log)', 'group' => 'vici'),
);

Tabs are split into two groups: homer (SIP/protocol data from PostgreSQL) and vici (call center data from MySQL). The nav bar renders them with different color accents -- purple for Homer tabs, green for ViciDial tabs -- with a visual separator between groups.

Database Credentials

$EXCEED_LIMIT = 270;  // 4:30 = 270 seconds (compliance threshold)

// MySQL credentials (read-only user on all ViciDial servers)
$MYSQL_USER = 'YOUR_READONLY_USER';
$MYSQL_PASS = 'YOUR_READONLY_PASS';
$MYSQL_DB   = 'asterisk';

// PostgreSQL (Homer, inside Docker network)
$PG_HOST = 'postgres';  // Docker service name
$PG_PORT = 5432;
$PG_USER = 'homer';
$PG_PASS = 'YOUR_HOMER_DB_PASSWORD';
$PG_DB   = 'homer_data';

// SQLite (local user/permission store)
$SQLITE_PATH = __DIR__ . '/data/users.db';
$AUTH_REALM  = 'VoIP Monitoring';

The $EXCEED_LIMIT variable is a business rule: calls longer than 4 minutes 30 seconds are flagged as "exceeded" in the inbound tab. This is configurable per deployment.

Helper Functions

// IP-to-server reverse lookup
$ip_to_server = array();
foreach ($SERVERS as $key => $srv) {
    $ip_to_server[$srv['host']] = $key;
}

// XSS-safe output
function h($s) {
    return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8');
}

// Human-readable duration
function fmt_duration($s) {
    $s = (int)$s;
    if ($s <= 0) return '-';
    if ($s < 60) return $s . 's';
    if ($s < 3600) return floor($s/60) . 'm ' . ($s%60) . 's';
    return floor($s/3600) . 'h ' . floor(($s%3600)/60) . 'm';
}

// Relative timestamp (e.g., "5m ago", "2h ago")
function fmt_ago($datetime) {
    if (!$datetime) return '-';
    $ts = is_numeric($datetime) ? $datetime : strtotime($datetime);
    if (!$ts) return h($datetime);
    $ago = time() - $ts;
    if ($ago < 0) return h($datetime);
    if ($ago < 60) return $ago . 's ago';
    if ($ago < 3600) return floor($ago / 60) . 'm ago';
    if ($ago < 86400) return floor($ago / 3600) . 'h ago';
    return date('M j H:i', $ts);
}

// Color-coded server badge
function server_badge($server_key) {
    global $SERVERS;
    if (!isset($SERVERS[$server_key]))
        return '<span class="badge badge-gray">Unknown</span>';
    $s = $SERVERS[$server_key];
    return '<span class="badge" style="background:' . $s['color'] . '15;'
         . 'color:' . $s['color'] . ';'
         . 'border:1px solid ' . $s['color'] . '40;">'
         . $s['flag'] . ' ' . h($s['name']) . '</span>';
}

// Filter MySQL connections by selected server
function get_filtered_conns($mysql_conns, $server) {
    if ($server !== 'all' && isset($mysql_conns[$server]))
        return array($server => $mysql_conns[$server]);
    return $mysql_conns;
}

The server_badge() function is used throughout every tab to show which server a row came from. It uses the server's color with alpha transparency for the background, creating a subtle, readable badge.

6. Authentication and Permission System {#6-authentication-and-permissions}

ViciDial-Based Authentication

The dashboard uses HTTP Basic Auth, validating credentials against the ViciDial MySQL vicidial_users table. This means users log in with their existing ViciDial username and password -- no separate account system needed.

function request_auth($realm) {
    header('WWW-Authenticate: Basic realm="' . $realm . '"');
    header('HTTP/1.0 401 Unauthorized');
    die('ViciDial credentials required.');
}

function do_auth($mysql_conns, $realm) {
    $auth_user = isset($_SERVER['PHP_AUTH_USER']) ? $_SERVER['PHP_AUTH_USER'] : '';
    $auth_pass = isset($_SERVER['PHP_AUTH_PW'])   ? $_SERVER['PHP_AUTH_PW']   : '';
    if ($auth_user === '' || $auth_pass === '') request_auth($realm);

    $fullname = $auth_user;
    $ok = false;

    // Try each ViciDial server until we find a match
    foreach ($mysql_conns as $m) {
        $stmt = $m->prepare(
            "SELECT full_name, active, view_reports
             FROM vicidial_users
             WHERE user=? AND pass=? LIMIT 1"
        );
        if (!$stmt) continue;
        $stmt->bind_param('ss', $auth_user, $auth_pass);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows > 0) {
            $stmt->bind_result($fname, $active, $reports);
            $stmt->fetch();
            // Only allow active users with report access
            if ($active === 'Y' && $reports === '1') {
                $ok = true;
                $fullname = $fname ?: $auth_user;
            }
        }
        $stmt->free_result();
        $stmt->close();
        if ($ok) break;
    }

    if (!$ok) request_auth($realm);
    return array('user' => $auth_user, 'fullname' => $fullname);
}

Key points:

• The user is checked against every ViciDial server. A valid login on any server grants access.
• Only users with active='Y' and view_reports='1' can log in. This uses ViciDial's existing permission model.
• The full_name is displayed in the header bar.

SQLite Permission Layer

After authentication, a second layer controls what the user can see:

function get_sqlite() {
    global $SQLITE_PATH;
    $dir = dirname($SQLITE_PATH);
    if (!is_dir($dir)) mkdir($dir, 0755, true);

    $db = new SQLite3($SQLITE_PATH);
    $db->busyTimeout(5000);
    $db->exec("CREATE TABLE IF NOT EXISTS dashboard_users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vici_user TEXT UNIQUE NOT NULL,
        display_name TEXT DEFAULT '',
        is_admin INTEGER DEFAULT 0,
        allowed_servers TEXT DEFAULT 'all',
        allowed_tabs TEXT DEFAULT 'all',
        notes TEXT DEFAULT '',
        created_at TEXT DEFAULT (datetime('now')),
        updated_at TEXT DEFAULT (datetime('now'))
    )");
    return $db;
}

The schema stores:

• allowed_servers: Comma-separated list of server keys (e.g., uk,france) or all
• allowed_tabs: Comma-separated list of tab keys (e.g., inbound,manual) or all
• is_admin: Boolean flag for admin panel access

Auto-Creating the First Admin

The first user to log in is automatically made an admin:

if (!has_any_users()) auto_create_admin($auth_user, $AUTH_FULLNAME);

This bootstrap mechanism means you never get locked out of a fresh deployment.

Logout Handling

Browser HTTP Basic Auth cannot be easily cleared. The logout flow uses a cookie trick:

function do_logout($realm) {
    if (isset($_COOKIE['logout_done'])) {
        setcookie('logout_done', '', time() - 3600, '/');
        $path = strtok($_SERVER['REQUEST_URI'], '?');
        header('Location: ' . $path);
        exit;
    }
    setcookie('logout_done', '1', 0, '/');
    request_auth($realm);  // Send 401 to clear browser cache
}

7. The Main Dashboard (index.php) {#7-main-dashboard}

The main file is the largest component (~2000 lines). It handles all database connections, renders the header/nav/filters, and includes the logic for every tab.

Database Connection Strategy

On every page load, the dashboard opens connections to all configured databases:

// PostgreSQL (Homer) -- single connection
$pg = @pg_connect(
    "host=$PG_HOST port=$PG_PORT dbname=$PG_DB "
    . "user=$PG_USER password=$PG_PASS connect_timeout=5"
);

// MySQL (ViciDial) -- one connection per server
$mysql_conns = array();
foreach ($SERVERS as $key => $srv) {
    $m = @mysqli_init();
    $m->options(MYSQLI_OPT_CONNECT_TIMEOUT, 3);
    @$m->real_connect($srv['host'], $MYSQL_USER, $MYSQL_PASS, $MYSQL_DB, 3306);
    if (!$m->connect_error) {
        $m->set_charset("utf8");
        $mysql_conns[$key] = $m;
    }
}

The @ error suppression is intentional -- if a server is down, the dashboard still loads with the remaining servers. Connection status is shown as green/red dots in the header bar.

Permission Enforcement

After authentication, every query is filtered by the user's allowed servers:

$allowed_servers = ($perms['allowed_servers'] === 'all')
    ? array_keys($SERVERS)
    : array_filter(explode(',', $perms['allowed_servers']));

$allowed_tabs = ($perms['allowed_tabs'] === 'all')
    ? array_keys($ALL_TABS)
    : array_filter(explode(',', $perms['allowed_tabs']));

// Filter visible servers
$visible_servers = array();
foreach ($allowed_servers as $sk) {
    if (isset($SERVERS[trim($sk)]))
        $visible_servers[trim($sk)] = $SERVERS[trim($sk)];
}

Input Parameter Handling

All URL parameters are sanitized:

$tab     = isset($_GET['tab'])    ? $_GET['tab']    : 'sip_calls';
$server  = isset($_GET['server']) ? $_GET['server'] : 'all';
$quick   = isset($_GET['quick'])  ? $_GET['quick']  : 'today';
$number  = isset($_GET['number']) ? preg_replace('/[^0-9\+]/', '', $_GET['number']) : '';
$ext     = isset($_GET['ext'])    ? preg_replace('/[^0-9A-Za-z_\-]/', '', $_GET['ext']) : '';
$limit   = isset($_GET['limit']) ? min(max((int)$_GET['limit'], 50), 5000) : 200;

Note the limit clamping (50-5000) and the regex sanitization of phone numbers and extensions.

Time Range System

The dashboard supports four time range modes:

$startSQL = "NOW() - INTERVAL 7 DAY";
$endSQL   = "NOW()";

if ($quick === 'today')      $startSQL = "CURDATE()";
elseif ($quick === 'yday')   $startSQL = "DATE(NOW() - INTERVAL 1 DAY)";
elseif ($quick === '7d')     $startSQL = "NOW() - INTERVAL 7 DAY";
elseif ($quick === 'custom') {
    if ($date_from !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) {
        $startSQL = "'" . $date_from . " 00:00:00'";
        if ($date_to !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to))
            $endSQL = "'" . $date_to . " 23:59:59'";
    } else {
        $startSQL = "NOW() - INTERVAL $days DAY";
    }
}

// PostgreSQL uses different interval syntax
$pgInterval = '1 day';
if ($quick === 'today')      $pgInterval = '1 day';
elseif ($quick === '7d')     $pgInterval = '7 days';
elseif ($quick === 'custom') $pgInterval = "$days days";

This dual-syntax approach handles the MySQL vs. PostgreSQL interval difference transparently.

Header and Navigation Rendering

<!-- Server Selector -->
<div class="server-bar">
    <span class="label">Server:</span>
    <?php if (count($visible_servers) > 1): ?>
    <a href="<?=build_url(array('server'=>'all'))?>"
       class="server-btn all <?=($server==='all')?'active':''?>">All Servers</a>
    <?php endif; ?>
    <?php foreach ($visible_servers as $key => $srv): ?>
    <a href="<?=build_url(array('server'=>$key))?>"
       class="server-btn <?=($server===$key)?'active':''?>"
       style="<?=($server===$key)?'background:'.$srv['color'].';color:#fff;':''?>">
       <?=$srv['flag']?> <?=h($srv['name'])?>
    </a>
    <?php endforeach; ?>

    <!-- Connection status dots -->
    <div class="db-dots">
        <span><span class="dot <?=$pg?'ok':'err'?>"></span>Homer</span>
        <?php foreach ($visible_servers as $key => $srv): ?>
        <span><span class="dot <?=isset($mysql_conns[$key])?'ok':'err'?>"></span>
              <?=h($srv['name'])?></span>
        <?php endforeach; ?>
    </div>
</div>

<!-- Tab Navigation -->
<div class="nav">
<?php foreach ($ALL_TABS as $tk => $tv):
    if (!in_array($tk, $allowed_tabs)) continue;
    $cls = $tv['group'] . (($tab === $tk) ? ' active' : '');
?>
    <a href="<?=build_url(array('tab'=>$tk))?>" class="<?=$cls?>">
        <?=h($tv['label'])?>
    </a>
<?php endforeach; ?>
</div>

The build_url() helper preserves all current filter parameters when switching tabs or servers:

function build_url($params = array()) {
    global $tab, $server, $quick, $days, $number, $ext, $limit,
           $date_from, $date_to, $min_sec, $max_sec, $exceeded;
    $d = array(
        'tab' => $tab, 'server' => $server,
        'quick' => $quick, 'days' => $days, 'limit' => $limit
    );
    if ($number !== '') $d['number'] = $number;
    if ($ext !== '')    $d['ext']    = $ext;
    // ... more params
    return '?' . http_build_query(array_merge($d, $params));
}

8. Tab: SIP Calls with Ladder Diagrams {#8-sip-calls-tab}

The SIP Calls tab queries Homer's hep_proto_1_call table, which stores every SIP message captured by heplify.

Call List View

$ip_filter = homer_ip_filter($pg, $server, $allowed_servers);

$q = "SELECT sid as call_id,
             MIN(create_date) as start_time,
             MAX(create_date) as end_time,
             MAX(CASE WHEN data_header->>'method'='INVITE'
                 THEN data_header->>'from_user' END) as from_user,
             MAX(CASE WHEN data_header->>'method'='INVITE'
                 THEN data_header->>'to_user' END) as to_user,
             MAX(CASE WHEN data_header->>'method'='INVITE'
                 THEN protocol_header->>'srcIp' END) as src_ip,
             COUNT(*) as msgs,
             bool_or(data_header->>'method' = '200') as ok200,
             bool_or(data_header->>'method' = 'BYE') as has_bye,
             bool_or(data_header->>'method' = 'CANCEL') as has_cancel,
             bool_or(data_header->>'method' IN ('486','487','480','503'))
                 as has_err,
             EXTRACT(EPOCH FROM MAX(create_date) - MIN(create_date))::int
                 as duration_sec
      FROM hep_proto_1_call
      WHERE create_date >= NOW() - INTERVAL '$pgInterval'
        AND sid IN (
            SELECT DISTINCT sid FROM hep_proto_1_call
            WHERE data_header->>'method' = 'INVITE'
              AND create_date >= NOW() - INTERVAL '$pgInterval'
              $ip_filter
            ORDER BY sid DESC LIMIT $limit
        )
      GROUP BY sid ORDER BY start_time DESC";

The homer_ip_filter() function restricts results to the user's allowed servers by filtering on source/destination IP:

function homer_ip_filter($pg_conn, $server, $allowed_servers) {
    global $SERVERS;
    if ($server !== 'all' && isset($SERVERS[$server])) {
        $sip = pg_escape_string($pg_conn, $SERVERS[$server]['host']);
        return "AND (protocol_header->>'srcIp' = '$sip'
                 OR protocol_header->>'dstIp' = '$sip')";
    }
    // Multi-server: build IN clause
    if (count($allowed_servers) < count($SERVERS)) {
        $ips = array();
        foreach ($allowed_servers as $sk) {
            if (isset($SERVERS[$sk]))
                $ips[] = "'" . pg_escape_string($pg_conn, $SERVERS[$sk]['host']) . "'";
        }
        if (!empty($ips)) {
            $in = implode(',', $ips);
            return "AND (protocol_header->>'srcIp' IN ($in)
                     OR protocol_header->>'dstIp' IN ($in))";
        }
    }
    return '';
}

SIP Ladder Diagram (Trace View)

When you click "Trace" on a call, the dashboard renders a visual SIP ladder diagram showing every message exchanged between Provider, Server, and Agent.

The implementation works in three steps:

Step 1: Fetch A-leg messages

$q = "SELECT create_date, data_header->>'method' as method,
             protocol_header->>'srcIp' as src_ip,
             protocol_header->>'dstIp' as dst_ip,
             data_header->>'from_user' as from_user,
             data_header->>'to_user' as to_user,
             data_header->>'cseq' as cseq, raw
      FROM hep_proto_1_call
      WHERE sid = $1
      ORDER BY create_date ASC LIMIT 200";

Step 2: Find and fetch B-leg messages

The B-leg (server-to-agent or server-to-carrier) is found by searching for SIP sessions that:

• Overlap in time with the A-leg
• Involve the same server IP
• Contain matching phone numbers in the From/To headers

// Determine A-leg direction
$server_is_dst = ($a_invite && $a_invite['dst_ip'] === $server_ip);

// Search for B-leg in opposite direction
$b_src_filter = $server_is_dst
    ? "AND protocol_header->>'srcIp' = $3"
    : "AND protocol_header->>'dstIp' = $3";

$q2 = "SELECT DISTINCT sid,
              MAX(CASE WHEN data_header->>'method'='INVITE'
                  THEN data_header->>'from_user' END) as fu,
              MAX(CASE WHEN data_header->>'method'='INVITE'
                  THEN data_header->>'to_user' END) as tu,
              MIN(create_date) as ft
       FROM hep_proto_1_call
       WHERE create_date BETWEEN $1 - interval '5 seconds'
                            AND $2 + interval '120 seconds'
         $b_src_filter
         AND sid != $4
         AND data_header->>'method' = 'INVITE'
       GROUP BY sid ORDER BY ft ASC LIMIT 20";

Step 3: Render the ladder

The ladder diagram is pure CSS/HTML with positioned arrows:

<?php foreach ($all_msgs as $i => $msg):
    $src_idx = $entity_map[$msg['src_ip']] ?? 0;
    $dst_idx = $entity_map[$msg['dst_ip']] ?? (count($entities)-1);

    // Calculate arrow position as percentages
    $src_pct = ($src_idx + 0.5) / $num_ents * 100;
    $dst_pct = ($dst_idx + 0.5) / $num_ents * 100;
    $left_pct = min($src_pct, $dst_pct);
    $width_pct = abs($dst_pct - $src_pct);
    $dir = ($dst_idx > $src_idx) ? 'arr-r' : 'arr-l';

    // Color by SIP method/response code
    $color = '#3b82f6';  // blue default
    if ($is_resp && (int)$m_name >= 200 && (int)$m_name < 300)
        $color = '#22c55e';  // green for 2xx
    elseif ($is_resp && (int)$m_name >= 400)
        $color = '#ef4444';  // red for 4xx+
    elseif (strtoupper($m_name) === 'BYE')
        $color = '#f59e0b';  // amber for BYE/CANCEL
?>
    <div class="l-row" onclick="toggleRaw(<?=$i?>)">
        <div class="l-time"><?=h(substr($msg['create_date'], 11, 12))?></div>
        <div class="l-ents">
            <div class="l-arrow <?=$dir?>"
                 style="color:<?=$color?>;left:<?=$left_pct?>%;width:<?=$width_pct?>%;">
                <span class="l-label"><?=h($m_name)?></span>
            </div>
            <span class="l-leg <?=strtolower($leg)?>"><?=$leg?></span>
        </div>
    </div>
    <div class="l-raw" id="raw-<?=$i?>"><?=h($msg['raw'] ?? '')?></div>
<?php endforeach; ?>

Clicking any row toggles the raw SIP message display, rendered in a dark monospace panel.

9. Tab: Call Quality (RTCP Analysis) {#9-call-quality-tab}

The Quality tab queries Homer's hep_proto_5_default table, which stores RTCP (Real-Time Control Protocol) reports. These contain jitter, packet loss, and other quality metrics for active calls.

$q = "SELECT sid as call_id, create_date,
             protocol_header->>'srcIp' as src_ip,
             protocol_header->>'dstIp' as dst_ip, raw
      FROM hep_proto_5_default
      WHERE create_date >= NOW() - INTERVAL '$pgInterval' $ip_filter
      ORDER BY create_date DESC LIMIT $limit";

The raw RTCP data is JSON-encoded. We parse it to extract the quality metrics:

while ($r = pg_fetch_assoc($result)) {
    $rtcp = @json_decode($r['raw'], true);
    $rb = isset($rtcp['report_blocks'][0]) ? $rtcp['report_blocks'][0] : null;
    $r['jitter']   = $rb ? round(($rb['ia_jitter'] ?? 0) / 8, 1) : null;
    $r['loss_pct'] = $rb ? round(($rb['fraction_lost'] ?? 0) / 256 * 100, 2) : null;
    $r['pkts_lost'] = $rb ? ($rb['packets_lost'] ?? null) : null;
    // ... assign server badge
    $qdata[] = $r;
}

Enriching RTCP with Call Context

RTCP reports only contain IPs and Call-IDs, not caller/agent names. We enrich them by batch-fetching the INVITE data for each Call-ID:

// Collect unique Call-IDs from RTCP data
$sid_list = array_keys($q_sids);

// Batch fetch in chunks of 100
foreach (array_chunk($sid_list, 100) as $batch) {
    $q_info = "SELECT DISTINCT ON (sid) sid,
                      data_header->>'from_user' as from_user,
                      data_header->>'to_user' as to_user
               FROM hep_proto_1_call
               WHERE sid IN ($placeholders)
                 AND data_header->>'method' = 'INVITE'
               ORDER BY sid, create_date ASC";
    // ... execute and build lookup map
}

This lets us show "44712345678 -> 1001" instead of just IP addresses in the quality table.

Summary Cards

$hj = 0; $hl = 0; $js = 0; $jc = 0;
foreach ($qdata as $q2) {
    if ($q2['jitter'] !== null) { $js += $q2['jitter']; $jc++; }
    if ($q2['jitter'] > 30) $hj++;   // High jitter threshold: 30ms
    if ($q2['loss_pct'] > 1) $hl++;  // High loss threshold: 1%
}
$avg_j = $jc > 0 ? round($js/$jc, 1) : 0;

10. Tab: SIP Registrations {#10-registrations-tab}

This tab shows SIP REGISTER requests and responses from Homer's hep_proto_1_registration table. It is useful for diagnosing phone connectivity issues -- if an extension stopped registering, you will see it here.

$q = "SELECT create_date, data_header->>'method' as method,
             data_header->>'from_user' as from_user,
             protocol_header->>'srcIp' as src_ip,
             protocol_header->>'dstIp' as dst_ip,
             data_header->>'user_agent' as ua
      FROM hep_proto_1_registration
      WHERE create_date >= NOW() - INTERVAL '$pgInterval'
        $ip_filter $ext_filter
      ORDER BY create_date DESC LIMIT $limit";

Registration methods are color-coded: 200 responses get a green badge, 4xx/5xx get red, and REGISTER requests get blue.

11. Tab: Inbound Calls (ViciDial) {#11-inbound-calls-tab}

The Inbound tab is the most feature-rich. It queries ViciDial's vicidial_closer_log table (which tracks all inbound calls handled by agents) and joins it with recording_log for inline audio playback and call_log for the DID that was called.

Multi-Server Query Pattern

$conns = get_filtered_conns($mysql_conns, $server);
$all_rows = array();

foreach ($conns as $sk => $m) {
    if (!in_array($sk, $allowed_servers)) continue;

    $where = "WHERE vcl.call_date >= ($startSQL)
              AND vcl.call_date <= ($endSQL)";

    // Apply filters
    if ($number !== '')
        $where .= " AND vcl.phone_number LIKE '%"
                 . $m->real_escape_string($number) . "%'";
    if ($exceeded)
        $where .= " AND vcl.length_in_sec > $EXCEED_LIMIT";

    $q = "SELECT vcl.call_date, vcl.length_in_sec, vcl.phone_number,
                 vcl.queue_seconds, vcl.user, vu.full_name,
                 vcl.status, vcl.term_reason, vcl.uniqueid,
                 rl.location AS rec_url, rl.filename AS rec_filename,
                 cl.number_dialed AS called_number
          FROM vicidial_closer_log vcl
          LEFT JOIN vicidial_users vu ON vu.user = vcl.user
          LEFT JOIN recording_log rl ON rl.vicidial_id = vcl.uniqueid
          LEFT JOIN call_log cl ON cl.uniqueid = vcl.uniqueid
          $where
          AND (cl.channel IS NULL
               OR cl.channel NOT LIKE 'IAX2/ASTloop%')
          ORDER BY vcl.call_date DESC LIMIT $limit";

    $rs = $m->query($q);
    if ($rs) while ($row = $rs->fetch_assoc()) {
        $row['_server'] = $sk;  // Tag each row with its server
        $all_rows[] = $row;
    }
}

// Merge and sort across servers
usort($all_rows, function($a, $b) {
    return strcmp($b['call_date'], $a['call_date']);
});
$all_rows = array_slice($all_rows, 0, $limit);

This pattern -- query each server, tag rows, merge, sort, limit -- is used in every ViciDial tab. The IAX2/ASTloop filter removes internal loop channels that would create duplicate entries.

Summary Statistics Cards

$total = count($all_rows);
$answered = 0; $dropped = 0;
$total_sec = 0; $total_queue = 0; $exceeded_cnt = 0;

foreach ($all_rows as $r) {
    $total_sec += (int)$r['length_in_sec'];
    $total_queue += (int)$r['queue_seconds'];
    if ((int)$r['length_in_sec'] > 0) $answered++;
    if ($r['term_reason'] === 'CALLER') $dropped++;
    if ((int)$r['length_in_sec'] > $EXCEED_LIMIT) $exceeded_cnt++;
}

The cards show: Total Calls, Answered (with answer rate %), Caller Hangups, Average Queue Time, and Exceeded (>4:30) count.

Duration and Queue Color Coding

Each call row includes visual indicators:

// Duration bar (percentage of 4:30 threshold)
$dur_pct = $dur > 0 ? min(100, round($dur / $EXCEED_LIMIT * 100)) : 0;
$dur_bar_color = '#16a34a';  // green
if ($dur > $EXCEED_LIMIT) $dur_bar_color = '#dc2626';      // red
elseif ($dur > $EXCEED_LIMIT * 0.85) $dur_bar_color = '#ca8a04'; // yellow warning

// Queue time color
$q_cls = 'queue-fast';        // green
if ($qs > 120) $q_cls = 'queue-critical';  // red
elseif ($qs > 60) $q_cls = 'queue-slow';   // orange
elseif ($qs > 30) $q_cls = 'queue-ok';     // yellow

Inline Audio Playback

Each row with a recording shows an HTML5 audio player:

<?php if ($rec): ?>
    <div class="rec-player">
        <audio controls controlsList="nodownload" preload="none">
            <source src="<?=h($rec)?>" type="audio/wav">
        </audio>
        <button class="analyze-btn"
                onclick="analyzeRecording(this,'<?=h($row['_server'])?>',
                         '<?=h($rec_file_raw)?>','aq-<?=$aq_id?>')"
                title="Analyze audio quality">
            <span class="icon">&#128269;</span>
            <span class="spinner"></span>
        </button>
    </div>
<?php endif; ?>

The controlsList="nodownload" prevents casual downloading. The analyze button triggers the AI-powered audio quality analysis (covered in section 16).

Analytics Section

Below the call table, a collapsible analytics section shows aggregated data:

<details>
<summary>Analytics (By Phone & By Agent)</summary>

This section runs separate GROUP BY queries to show:

• By Phone Number: Top 50 callers with call count, total duration, and average duration
• By Agent: Top 50 agents with the same metrics

12. Tab: Manual/Zoiper Calls {#12-manual-calls-tab}

The Manual tab shows calls made from softphones (Zoiper, Linphone, etc.) that bypass the ViciDial dialer. These are tracked in the call_log table with channel_group='PHONE_DIAL'.

A key difference: call_log has an extension field (the SIP extension, e.g., "1032") instead of a ViciDial user. We join with vicidial_users.phone_login to map extensions back to agent names:

$q = "(SELECT cl.start_time, cl.length_in_sec, cl.channel,
             cl.extension AS phone_ext, cl.number_dialed,
             cl.uniqueid,
             rl.location AS rec_url,
             vu.user AS mapped_user, vu.full_name AS mapped_name
      FROM call_log cl
      LEFT JOIN recording_log rl ON rl.vicidial_id = cl.uniqueid
      LEFT JOIN vicidial_users vu ON vu.phone_login = cl.extension
      $where)
     UNION ALL
     (SELECT ... FROM call_log_archive cl ...)
     ORDER BY start_time DESC LIMIT $limit";

The UNION ALL with call_log_archive ensures we find calls that have been archived by ViciDial's daily maintenance. A deduplication step removes any calls that appear in both tables.

13. Tab: Audit Log {#13-audit-log-tab}

The Audit tab queries ViciDial's vicidial_admin_log table, showing admin actions like user creation, inbound group changes, and campaign modifications.

$where = "WHERE al.event_date >= ($startSQL)";
if (!$audit_all)
    $where .= " AND al.event_section IN ('USERS','INGRP')";
if ($audit_admin !== '')
    $where .= " AND al.user='" . $m->real_escape_string($audit_admin) . "'";
if ($audit_q !== '')
    $where .= " AND (al.event_notes LIKE '%"
             . $m->real_escape_string($audit_q) . "%')";

$q = "SELECT al.event_date, al.user, al.ip_address,
             al.event_section, al.event_type,
             al.record_id, al.event_code, al.event_notes
      FROM vicidial_admin_log al
      $where ORDER BY al.event_date DESC LIMIT $limit";

By default, it filters to USERS and INGRP sections (the most operationally relevant). Checking "All sections" removes this filter to show every admin action.

14. Tab: Ring Group Monitoring {#14-ring-group-tab}

The Ring Group tab tracks how incoming calls are distributed across softphone extensions in a ring-all or ring-sequential group. This uses custom tables (ringgroup_call_status and ringgroup_call_trace) that are populated by an Asterisk AGI script.

The tab has three sub-views:

Incoming Calls: Shows each ring group call with counts of extensions dialed, ringing, answered, offline, and busy.

Phone Performance: Aggregated per-extension statistics showing answer rate, average ring time, and offline count. This is essential for finding phones that are consistently offline or not answering.

Call Detail: When you click into a specific call, shows every extension that was attempted, its dial status, SIP response, ring duration, and the User-Agent string of the phone.

15. Recording Proxy (rec.php) {#15-recording-proxy}

ViciDial stores recordings on each server's web directory (/RECORDINGS/). Rather than exposing each server's recording directory to the internet, the dashboard proxies recording requests through rec.php.

How It Works

// Map server keys to IPs
$REC_SERVERS = array(
    'uk'      => 'YOUR_UK_SERVER_IP',
    'romania' => 'YOUR_ROMANIA_SERVER_IP',
    'france'  => 'YOUR_FRANCE_SERVER_IP',
    'italy'   => 'YOUR_ITALY_SERVER_IP',
);

// Build candidate URLs (recordings may be WAV or MP3)
$urls = array();
if ($leg !== '') {
    // Separate leg request -- look in ORIG/ directory
    $base = preg_replace('/-(all|in|out)\.(wav|mp3)$/i', '', $file);
    $urls[] = "http://$host/RECORDINGS/ORIG/{$base}-{$leg}.wav";
} elseif ($ext === 'mp3') {
    $urls[] = "http://$host/RECORDINGS/MP3/$file";
    $urls[] = "http://$host/RECORDINGS/$file";
} elseif ($ext === 'wav') {
    $urls[] = "http://$host/RECORDINGS/$file";
    $mp3 = preg_replace('/\.wav$/i', '.mp3', $file);
    $urls[] = "http://$host/RECORDINGS/MP3/$mp3";
}

// Try each URL until we find one that exists
foreach ($urls as $url) {
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_exec($ch);
    if (curl_getinfo($ch, CURLINFO_HTTP_CODE) === 200) {
        $found_url = $url;
        break;
    }
}

Security Features

1. Filename sanitization: Only alphanumeric, underscore, hyphen, and dot characters are allowed
2. Server access check: Users can only access recordings from their allowed servers
3. Download prevention: Non-admin users get Content-Disposition: inline without a filename, and Cache-Control: no-store to prevent browser caching
4. Leg support: The ?leg=in or ?leg=out parameter fetches individual call legs from the ORIG/ subdirectory for separate caller/agent audio analysis

16. Audio Quality Analysis Integration {#16-audio-analysis}

The dashboard includes a button on each recording row that triggers an AI-powered audio quality analysis. This is a two-phase process:

Phase 1: Signal Analysis (analyze.php)

When you click the magnifying glass button, the dashboard calls analyze.php, which proxies the request to a Python FastAPI service running on port 8084:

$api_host = file_exists('/.dockerenv') ? '172.18.0.1' : '127.0.0.1';
$mode = isset($_GET['ai']) && $_GET['ai'] === '1' ? 'ai-analyze' : 'analyze';
$api_url = "http://{$api_host}:8084/{$mode}?"
         . http_build_query(['server' => $server, 'file' => $file]);

Note the Docker networking detail: inside a container, the host machine is reachable at 172.18.0.1 (the Docker bridge gateway), not 127.0.0.1.

Phase 2: Frontend Rendering

The JavaScript renderAnalysis() function builds a rich analysis panel with:

• MOS score verdict (Excellent/Good/Fair/Poor/Bad) with color coding
• Speaker timelines: Visual bars showing when the caller and agent were speaking
• Waveform visualization: Uses WaveSurfer.js to render the audio waveform
• Findings panel: Talk ratio, volume levels, silence gaps, per-leg MOS comparison
• AI summary: Auto-fetched after the signal analysis completes

function analyzeRecording(btn, server, file, panelId) {
    var panel = document.getElementById(panelId);
    var row = btn.closest('tr').nextElementSibling;

    // Toggle if already loaded
    if (panel.classList.contains('show')) {
        panel.classList.remove('show');
        row.style.display = 'none';
        return;
    }

    btn.classList.add('loading');
    row.style.display = '';
    panel.classList.add('show');

    fetch('analyze.php?server=' + encodeURIComponent(server)
        + '&file=' + encodeURIComponent(file))
        .then(function(r) { return r.json(); })
        .then(function(data) {
            btn.classList.remove('loading');
            panel.dataset.loaded = '1';
            renderAnalysis(panel, data);

            // Auto-fetch AI analysis in background
            fetch('analyze.php?server=...&ai=1')
                .then(function(r2) { return r2.json(); })
                .then(function(ai) {
                    // Render AI summary into the panel
                });
        });
}

One-Way Audio Detection

The analysis includes automatic one-way audio detection:

if (data.one_way_audio) {
    var side = data.one_way_direction === 'outbound_silent'
        ? 'agent' : 'caller';
    return '<strong>One-way audio failure -- the ' + side
         + '\'s side transmitted no sound.</strong>';
}

17. Call Investigation (AI-Powered) {#17-call-investigation}

Each call row has a "?" button that triggers an AI-powered investigation. When clicked, investigate.php collects data from multiple sources and sends it to an AI model for explanation.

Data Collection

The investigation gathers:

1. ViciDial closer log: Queue wait time, position, termination reason
2. Carrier log: SIP hangup cause, dial timing, channel information
3. DID routing: Which DID was called, its routing rules, caller ID
4. SIP timing: INVITE-to-ring time, ring-to-answer time, final SIP code
5. Ring group data: Per-extension dial status, offline/busy/answered results
6. Homer SIP trace: Full A-leg and B-leg SIP message history

// Build comprehensive call context
$call_info = "Server: {$srv['name']} ({$srv['label']})\n";
$call_info .= "Phone: $phone\n";
$call_info .= "Date/Time: $calldate\n";
$call_info .= "Status: $status\n";
$call_info .= "Duration: {$dur}s\n";

// Query carrier log for SIP-level detail
$cq = "SELECT dialstatus, hangup_cause, sip_hangup_cause,
              sip_hangup_reason, dial_time, answered_time
       FROM vicidial_carrier_log WHERE uniqueid='$uid_esc'";

// Query Homer for SIP trace (with timezone conversion)
$q_find = "SELECT DISTINCT sid
           FROM hep_proto_1_call
           WHERE create_date BETWEEN
               ($1::timestamp AT TIME ZONE 'Europe/Rome') - interval '30 seconds'
               AND ($1::timestamp AT TIME ZONE 'Europe/Rome') + interval '...'
             AND (protocol_header->>'srcIp' = $2
                  OR protocol_header->>'dstIp' = $2)
             AND (data_header->>'from_user' LIKE '%' || $3 || '%'
                  OR data_header->>'to_user' LIKE '%' || $3 || '%')";

Note the timezone conversion: ViciDial stores timestamps in server-local time (e.g., Europe/Rome), while Homer stores in UTC. The AT TIME ZONE conversion handles this mismatch.

All collected data is sent to a Python AI service that returns a human-readable explanation of what happened and why.

18. Admin Panel (User Management) {#18-admin-panel}

The admin panel (admin.php) provides CRUD operations for dashboard users. Only users with is_admin=1 can access it.

User Management Operations

if ($action === 'add') {
    $vu = trim($_POST['vici_user'] ?? '');
    $ia = isset($_POST['is_admin']) ? 1 : 0;
    $sv = isset($_POST['srv']) ? implode(',', $_POST['srv']) : '';
    $tb = isset($_POST['tab']) ? implode(',', $_POST['tab']) : '';

    // "all" checkbox overrides individual selections
    if (in_array('all', $_POST['srv'] ?? array())) $sv = 'all';
    if (in_array('all', $_POST['tab'] ?? array())) $tb = 'all';

    $stmt = $db->prepare(
        "INSERT INTO dashboard_users
         (vici_user, display_name, is_admin, allowed_servers, allowed_tabs, notes)
         VALUES(:u, :d, :a, :s, :t, :n)"
    );
    // ... bind and execute
}

The admin panel also includes:

• An AI assistant chatbox (proxied through admin-ask.php to a Python AI service)
• Edit and delete operations for existing users
• Self-deletion prevention (admins cannot delete their own account)

19. CSV Export System {#19-csv-export}

The Inbound, Manual, and Other tabs include CSV export buttons. The export runs before any HTML output (critical for proper headers):

if ($download || $download_exceeded) {
    $csv_rows = array();
    $csv_headers = array();

    if ($tab === 'inbound') {
        $csv_headers = array(
            'Date', 'Server', 'Caller', 'DID Called', 'Agent',
            'Agent Name', 'Duration (s)', 'Exceeded', 'Queue (s)',
            'Status', 'Term Reason'
        );
        $csv_filename = 'inbound_calls_' . date('Ymd_His') . '.csv';

        // Run the same queries as the display, but output CSV
        foreach ($conns_csv as $sk => $m) {
            // ... query and collect rows
        }
    }

    // Sort and output
    usort($csv_rows, function($a, $b) {
        return strcmp($b[0], $a[0]);
    });
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $csv_filename . '"');
    $fp = fopen('php://output', 'w');
    fputcsv($fp, $csv_headers);
    foreach ($csv_rows as $row) fputcsv($fp, $row);
    fclose($fp);
    exit();
}

A separate "CSV Exceeded" button exports only calls that exceeded the compliance threshold.

20. Frontend: Styling and JavaScript {#20-frontend}

CSS Architecture

The dashboard uses CSS custom properties for theming:

:root {
    --bg: #f8fafc;
    --card: #fff;
    --border: #e2e8f0;
    --text: #1e293b;
    --muted: #64748b;
    --accent: #1e40af;
}

Key styling patterns:

Server badges use the server's color with alpha transparency:

.badge {
    display: inline-block;
    padding: 2px 7px;
    border-radius: 10px;
    font-size: 10px;
    font-weight: 600;
}

Duration bars are inline progress indicators:

.dur-bar {
    display: block;
    height: 3px;
    border-radius: 2px;
    margin-top: 3px;
    max-width: 80px;
    transition: width .3s;
}

Queue time color coding provides instant visual feedback:

.queue-fast { color: #16a34a; font-weight: 600; }     /* < 30s */
.queue-ok { color: #ca8a04; font-weight: 600; }       /* 30-60s */
.queue-slow { color: #ea580c; font-weight: 600; }     /* 60-120s */
.queue-critical { color: #dc2626; font-weight: 700; } /* > 120s */

Ladder diagram arrows are CSS-only with positioned pseudo-elements:

.l-arrow {
    position: absolute;
    top: 50%;
    transform: translateY(-50%);
    height: 2px;
    z-index: 2;
}
.l-arrow::before {
    content: '';
    position: absolute;
    left: 0; right: 0; top: 0;
    height: 2px;
    background: currentColor;
}
.l-arrow.arr-r::after {
    content: '';
    position: absolute;
    right: -1px; top: -4px;
    border: 5px solid transparent;
    border-left-color: currentColor;
    border-right-width: 0;
}

Row Highlighting

tr.row-missed td { background: #fef2f2; }    /* Light red for missed calls */
tr.row-answered td { background: #f0fdf4; }  /* Light green for answered */
tr.row-exceeded td { background: #fff7ed; }  /* Light orange for exceeded */
tr.row-zero td { background: #fef2f2; }      /* Light red for 0-duration */

/* Left border accent for exceeded/missed rows */
table.qa-table tr.row-exceeded td:first-child {
    box-shadow: inset 3px 0 0 #ea580c;
}
table.qa-table tr.row-missed td:first-child {
    box-shadow: inset 3px 0 0 #dc2626;
}

Waveform Integration

The dashboard loads WaveSurfer.js for audio waveform rendering:

<script src="https://unpkg.com/wavesurfer.js@7/dist/wavesurfer.min.js"></script>

Waveforms are rendered inside the analysis panel:

WaveSurfer.create({
    container: wfContainer,
    height: 70,
    waveColor: '#64748b',
    progressColor: '#3b82f6',
    cursorColor: '#ef4444',
    barWidth: 2,
    barGap: 1,
    barRadius: 1,
    responsive: true,
    normalize: true,
    backend: 'MediaElement',
    url: audioSrc.src
});

21. Production Deployment Tips {#21-production-tips}

1. Use a Read-Only MySQL User

Never connect the dashboard with the ViciDial admin credentials. Create a dedicated read-only user on each ViciDial server:

CREATE USER 'dashboard_ro'@'YOUR_DASHBOARD_IP' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT SELECT ON asterisk.vicidial_closer_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.vicidial_users TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.call_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.call_log_archive TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.recording_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.vicidial_carrier_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.vicidial_admin_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.vicidial_inbound_dids TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
GRANT SELECT ON asterisk.vicidial_did_log TO 'dashboard_ro'@'YOUR_DASHBOARD_IP';
FLUSH PRIVILEGES;

2. Set MySQL Query Timeouts

To prevent a slow query from hanging the dashboard:

SET GLOBAL max_statement_time = 300;

Also add a query timeout in your read-only user's session:

SET @MAX_STATEMENT_TIME = 30;

3. Ensure the SQLite Data Directory is Writable

docker exec monitoring-webapp chmod 777 /var/www/html/data

Or better, set the correct ownership:

docker exec monitoring-webapp chown -R www-data:www-data /var/www/html/data

4. Add Indexes on ViciDial Tables

For large installations, add these indexes to improve query performance:

ALTER TABLE vicidial_closer_log
    ADD INDEX idx_campaign_calldate_status (campaign_id, call_date, status);

ALTER TABLE vicidial_agent_log
    ADD INDEX idx_uniqueid (uniqueid);

ALTER TABLE call_log
    ADD INDEX idx_changrp_starttime (channel_group, start_time);

5. Homer Data Retention

Keep Homer's data retention reasonable (7 days default) to prevent PostgreSQL from growing too large:

HEPLIFYSERVER_DBDROPDAYS: 7

6. Docker Networking

The webapp reaches external MySQL servers over the Docker bridge network. Ensure your firewall allows port 3306 from the Docker container's IP range (typically 172.18.0.0/16).

For the Python analysis service running on the host, the container reaches it via 172.18.0.1 (the Docker bridge gateway).

7. HTTPS

In production, place a reverse proxy (Nginx or Traefik) in front of the webapp with TLS termination. The dashboard uses HTTP Basic Auth, which transmits credentials in base64 -- without HTTPS, these are visible on the network.

22. Troubleshooting {#22-troubleshooting}

"Homer not connected" Error

The PostgreSQL connection uses the Docker service name postgres. If you see this error:

1. Check that the PostgreSQL container is running: docker ps | grep postgres
2. Verify the Homer database exists: docker exec postgres psql -U postgres -l | grep homer
3. Check credentials in config.php match your .env file
4. Ensure the monitoring Docker network exists: docker network ls

MySQL Connection Failures (Red Dots)

If a ViciDial server shows a red dot in the header:

1. Test connectivity from inside the container:

   docker exec monitoring-webapp php -r "
       \$m = new mysqli('SERVER_IP', 'USER', 'PASS', 'asterisk', 3306);
       echo \$m->connect_error ?: 'Connected OK';
   "
   

2. Check that the MySQL user is allowed from the Docker IP range
3. Verify port 3306 is open on the ViciDial server's firewall
4. Ensure max_connections on MySQL has not been exhausted

Recordings Not Playing

If the audio player shows but produces no sound:

1. Check that the ViciDial server's Apache serves /RECORDINGS/:

   curl -I http://SERVER_IP/RECORDINGS/
   

2. Verify the recording file exists on the server
3. Check browser console for CORS or mixed-content errors
4. Ensure rec.php can reach the ViciDial server from inside Docker:

   docker exec monitoring-webapp curl -s -o /dev/null -w "%{http_code}" \
       http://SERVER_IP/RECORDINGS/test-file.wav
   

Analysis Button Stuck on "Loading"

The analysis proxy calls a Python service on port 8084. If it hangs:

1. Check the Python service is running: systemctl status audio-analysis
2. Test the proxy path from inside Docker:

   docker exec monitoring-webapp curl -s http://172.18.0.1:8084/
   

3. Check for timeout issues -- AI analysis can take 30-180 seconds for long recordings

SQLite "Database is Locked" Errors

This happens when multiple requests try to write simultaneously:

1. Ensure busyTimeout(5000) is set (already in the code)
2. Check file permissions on data/users.db
3. Consider moving to WAL mode for better concurrent access:

   $db->exec("PRAGMA journal_mode=WAL");
   

SIP Ladder Diagram Shows No B-Leg

The B-leg detection relies on matching phone numbers between the A-leg INVITE and potential B-leg INVITEs. If it fails:

1. Check that heplify is capturing traffic from both the provider-facing and agent-facing network interfaces
2. Verify the time window: B-leg search uses a 5-second-before to 120-second-after window
3. Look for NAT issues: if the server uses different IPs for inbound vs. outbound SIP, the $server_ip detection may not match

Timezone Mismatches Between Homer and ViciDial

Homer stores timestamps in UTC. ViciDial typically stores in server-local time. The investigation proxy handles this with AT TIME ZONE conversion:

WHERE create_date BETWEEN
    ($1::timestamp AT TIME ZONE 'Europe/Rome') - interval '30 seconds'
    AND ...

If your ViciDial servers use a different timezone, update the $vici_tz variable in investigate.php.

High Memory Usage

Each page load opens connections to all configured ViciDial servers. With 4+ servers, this means 4 MySQL connections plus 1 PostgreSQL connection per request. For high-traffic deployments:

1. Reduce $limit default from 200 to 100
2. Use the server selector to view one server at a time
3. Consider adding persistent connections with mysqli_pconnect (use with caution)

Summary

This dashboard demonstrates that a complex, multi-database monitoring interface does not require a JavaScript framework, an ORM, or a build pipeline. With ~2000 lines of PHP spread across 8 files, you get:

• Unified visibility across SIP signaling (Homer), call center operations (ViciDial), and audio quality analysis
• Multi-server aggregation with per-row server identification
• Role-based access control using existing ViciDial credentials plus a lightweight SQLite permission layer
• Protocol-level debugging with SIP ladder diagrams showing both call legs
• AI-powered investigation that correlates data from 5+ sources to explain why a call failed
• CSV export for compliance reporting and external analysis

The key architectural patterns -- server-tagged rows, dual-syntax time ranges, graceful degradation on connection failure, and the recording proxy -- are reusable in any multi-server PHP monitoring tool.