Plan: Fix Missing Statistics Metrics

Root Causes

1. WebSocket Connections = 0

The stats API at admin/api/stats.php:33 queries:

$ws_connections = intval($pdo->query("SELECT count(*) FROM pg_stat_activity
    WHERE state = 'active' AND pid != pg_backend_pid()")->fetchColumn());

This is wrong — WebSocket connections are tracked in-memory by the relay via g_connection_count in src/websockets.c:139, not in pg_stat_activity. The PHP code is counting PostgreSQL backend processes, not WebSocket clients.

Fix: Query the subscriptions table for distinct wsi_pointer values where event_type = 'created' and no corresponding 'closed'/'disconnected' event exists. Or simpler: count distinct wsi_pointer values from the most recent 'created' entries.

2. Active Subscriptions = 0

The stats API at admin/api/stats.php:39 queries:

$active_subscriptions = intval($pdo->query("SELECT count(*) FROM subscriptions
    WHERE active = true")->fetchColumn());

The subscriptions table has no active column (see src/pg_schema.sql:207). It has event_type with values 'created', 'closed', 'expired', 'disconnected'.

Fix: Count subscriptions where event_type = 'created' and no matching 'closed'/'disconnected' record exists for the same (subscription_id, wsi_pointer).

3. Memory Usage = '-'

The code at admin/api/stats.php:46-51 reads /proc/meminfo — this may fail if the PHP-FPM process is running under a restricted user (e.g., www-data with ProtectProc=invisible or similar systemd sandboxing).

Fix: Use file_get_contents('/proc/meminfo') with proper error handling (already there), or fall back to sys_getloadavg() and shell_exec('free -b') as alternatives.

4. Oldest/Newest Event = '-'

The query at admin/api/stats.php:63-64 uses to_timestamp(MIN(created_at)) — this should work on 3.3M events but may be slow. Could be timing out.

Fix: Use SELECT MIN(created_at), MAX(created_at) FROM events in a single query, then format in PHP. Add a query timeout safeguard.

Changes Required

File: `admin/api/stats.php`

Line	Current	Fix
33	`pg_stat_activity` query	Count distinct active WebSocket connections from `subscriptions` table
39	`WHERE active = true`	Count active subscriptions using `event_type` logic
46-51	`/proc/meminfo`	Add fallback using `shell_exec('free -b')`
63-64	Two separate `to_timestamp` queries	Single `SELECT MIN(created_at), MAX(created_at)` query

SQL for Active Connections

SELECT COUNT(DISTINCT wsi_pointer) FROM subscriptions
WHERE event_type = 'created'
  AND wsi_pointer NOT IN (
    SELECT wsi_pointer FROM subscriptions
    WHERE event_type IN ('closed', 'disconnected', 'expired')
  )

SQL for Active Subscriptions

SELECT COUNT(*) FROM (
  SELECT subscription_id, wsi_pointer FROM subscriptions
  WHERE event_type = 'created'
  EXCEPT
  SELECT subscription_id, wsi_pointer FROM subscriptions
  WHERE event_type IN ('closed', 'disconnected', 'expired')
) AS active_subs

SQL for Oldest/Newest

SELECT MIN(created_at) AS oldest, MAX(created_at) AS newest FROM events

3.3 KiB Raw Permalink Blame History