ASTPP CC/CPS Reporting System — Technical Documentation

ASTPP (the billing/switch control panel) had no way to answer "how many calls per second / concurrent calls does this customer or carrier have right now." This system adds that, end to end, as a set of small, independent components bolted onto the existing platform rather than a rewrite of anything:

• A TimescaleDB database (astpp_reports) holds all reporting data, completely separate from ASTPP's own MySQL database.

• A single Report API (FastAPI service) is the only process that ever touches TimescaleDB.

• A handful of small collector components — two Python daemons, several cron-driven poller/flush scripts, a few added lines of Lua inside FreeSWITCH, and a small config addition to OpenSIPS — feed data in from every point calls actually pass through.

• A CC / CPS Report module inside ASTPP's own PHP/CodeIgniter admin panel renders it.

Reading this diagram: solid arrows are the call-signaling path (what actually carries a phone call); dotted arrows are the reporting path (what carries data about the call). The two paths only ever touch inside FreeSWITCH's own dialplan (a few lines of Lua) and OpenSIPS's own routing script (a few lines of config) — nothing on the reporting side can ever block or slow down a real call, because every reporting write is either fire-and-forget to a local file, or happens on a completely separate connection/process from call handling.

What it is: the database of record for all reporting data. Runs on the same box as MySQL (37.27.251.165) but as a fully separate service, deliberately configured not to auto-tune itself against the box's total RAM (which would ignore MySQL's existing 10GB buffer pool reservation).

Install:

Configure (deliberately conservative — do NOT accept timescaledb-tune's auto-detected memory settings on a box already running MySQL):

Create the database, extension, and least-privilege app role:

Then create the hypertables, plain table, and continuous aggregates listed in Section 6 — each raw table is created as an ordinary CREATE TABLE, then converted with SELECT create_hypertable('table_name', 'time').

What it is: the only process that ever reads or writes TimescaleDB. Everything else in this system — the PHP portal, every collector — talks to this service, never to Postgres directly. This isolation specifically avoids repeating a real earlier incident on this platform where a synchronous external DB call from PHP-FPM (via mod_json_cdr) took the whole platform down.

Location: /opt/astpp-report-api/main.py, host 37.27.251.165.

Install:

Configure (/opt/astpp-report-api/.env, referenced by path only — never hardcoded in the script):

Run as a systemd service (see full unit in Section 7) bound only to the WireGuard address 10.100.0.1:8090 — never a public interface.

Two separate API keys are the core access-control decision here: a leaked read key (held by the PHP portal, which is a bigger attack surface) can never be used to write fake CC/CPS data, because it isn't accepted on any /internal/ingest/* route.

What it is: the only network path the Report API is reachable on. No private network existed between these Hetzner boxes before this project — only public IPs — so this tunnel was purpose-built to let collectors on media-1/media-2/web1 reach the Report API without exposing Postgres or the API to the public internet at all.

Install (repeat per peer, adjusting the address):

What it is: a persistent Python daemon, running only on media-1, that asks FreeSWITCH directly (via ESL — the Event Socket Library) "what's true right now" once a second, for both per-account and platform-wide concurrency, and once every 5 seconds for per-trunk concurrency. It talks to media-2 the same way, just over the WireGuard tunnel instead of locally.

Why a persistent daemon and not a cron job: a fresh fs_cli process every second would mean a process spawn + TCP connect + auth handshake every second, forever, across two servers — wasteful and adds latency. One long-lived ESL connection per concern is far cheaper.

Why three separate ESL connections, not one: the minimal ESL client here has no request/reply correlation ID. A single shared connection means a slow reply to one query can be silently consumed as the reply to the next, unrelated query — this exact bug was found and fixed in production (see Section 11). So each concern gets its own connection: self.conn (per-account limits), self.total_conn (platform total), self.trunk_conn (per-trunk, heavier query, longer timeout).

Install:

What it is: a persistent daemon, media-1 only (same local/remote dual-server pattern as the CC sampler), that subscribes to FreeSWITCH's ESL event bus (CHANNEL_EXECUTE, CHANNEL_ANSWER, CHANNEL_HANGUP, CHANNEL_DESTROY) to derive per-trunk attempted/accepted call rates. Event-bus subscription is acceptable here because short-window rate counts tolerate some event loss; it was found unsuitable for concurrency tracking specifically (see Section 11), which is why concurrency uses the sampler's direct polling instead.

Install: identical pattern to 3.4 — script at /usr/local/bin/trunk_attempt_watcher.py, unit trunk-attempt-watcher.service.

What they are: small, single-purpose scripts that move data from a local source (a queue file written by Lua, or a MySQL table) into the Report API. All follow the same shape — read, POST, mark as sent — and none of them ever write to Postgres directly.

Install pattern (same for all of them):

Each flush script does an atomic mv of the queue file to a per-run work file before reading it, so a write from Lua landing mid-flush is never lost or double-counted.

What it is: small additions to FreeSWITCH's existing dialplan/rate-limiting Lua scripts (not new call-handling logic — these scripts already run on every call; this just adds a few lines that log an event to a local file). Files live at /opt/ASTPP/freeswitch/scripts_deb9/astpp/scripts/ on both media-1 and media-2.

"Install" here means editing already-deployed, business-critical files — always with a timestamped backup first (cp astpp.xml.lua astpp.xml.lua.bak-cc-fix-<epoch>), and always verified on the next real call rather than by restarting FreeSWITCH (Lua files are re-read per call, no restart needed/wanted).

What it is: a one-line config addition turning on OpenSIPS's built-in statistics module, plus a counter increment in the routing script, so the true entry-point call rate can be measured — before any FreeSWITCH-side accept/reject decision happens at all.

Install/configure (/usr/local/etc/opensips/opensips.cfg):

In route{}:

Read via opensips-cli -x mi get_statistics script: (polled by opensips_invite_stat_poll.py, Section 3.6).

What it is: the cc_cps_report module inside ASTPP's existing admin panel — a controller, a view, no new database of its own. It never talks to TimescaleDB; every data call goes through the Report API over HTTPS with a hard 3-second timeout.

Location: application/modules/cc_cps_report/ on the web host (95.217.8.48) — dropped into ASTPP's existing HMVC module directory, picked up automatically by the framework's module autoloader (no separate install step beyond placing the files and clearing any object cache).

Key files:

• controllers/Cc_cps_report.php — account-scoping, proxying to the Report API, the superadmin-only top_customers()/top_carriers() actions.

• views/view_cc_cps_report.php — the live panel, drill-down charts, and the two statistics tables.

Why there are three independent CPS sources feeding different parts of the report, not one:

1. call_attempts/call_accepted (from rate_limit.lua) — the live number shown on the dashboard's top panel. Fires the instant FreeSWITCH first sees a call, for every account, metered or not.

2. call_admissions (from MySQL cdrs, via admissions_poller.py) — feeds the 24-hour/historical drill-down charts. cdrs only gets a row at hangup, with its timestamp backdated to when the call started — fine for a chart you look at after the fact, useless as a live number.

3. entry_point_cps_samples (from OpenSIPS) — the true call-attempt rate at the actual SIP entry point, before FreeSWITCH ever sees anything. Needed because roughly half of genuine attempts were found to never reach FreeSWITCH at all (auth/routing failures at the SIP proxy).

Why CC uses direct polling instead of counting start/end events: an event-log approach (count a "start" on call setup, an "end" on hangup, concurrency = starts with no matching end yet) was tried first and abandoned — under real load, a meaningful fraction of "start" events never got a matching "end" (channel UUID reassignment during transfers, and separately, raw ESL event loss measured at ~73% under load). Both failure modes make an event-counted concurrency figure drift upward forever without ever correcting itself. Directly asking FreeSWITCH "what's live right now" every few seconds has no such drift — it's always a fresh, self-correcting snapshot. Full story in Section 11.

Why per-account CC is null/dash for almost every account: FreeSWITCH's own rl_cc counter (read via limit_usage) only exists for accounts with a configured CC/CPS limit — about 13 of ~120,000. For everyone else, there is no concurrency counter anywhere in FreeSWITCH to poll; the dashboard shows "—" rather than fabricate a "0" that would look like real data.

Database: astpp_reports, PostgreSQL 16 + TimescaleDB, host 37.27.251.165. All hypertables partition on time.

Systemd services:

All three: Restart=always, RestartSec=5, CPU/memory caps so a runaway collector can never meaningfully compete with real call-routing threads.

Cron — media-1 and media-2 (identical):

Cron — 37.27.251.165 (report host):

Cron — 95.217.244.97 (OpenSIPS host):

• Read vs. ingest keys are separate secrets. The portal holds only the read key; a leak of it can never be used to write fake data.

• resolve_account_id() in the PHP controller is the single choke point for account scoping, re-validated against the session on every request — never cached, never trusts a client-supplied account_id directly.

• Superadmin-only aggregate views. "All Customers" and the top-5 statistics tables have no account-override path at all.

• The Report API is the only process that ever touches TimescaleDB, and binds only to the WireGuard interface — never a public IP.

1. Per-account live CC exists for only ~13 of ~120,000 accounts — FreeSWITCH itself has no concurrency counter for unmetered accounts, so this isn't fixable at the reporting layer alone; it would need the same "ask FreeSWITCH directly" polling pattern used for trunks, extended to resolve an account_id per live channel (not yet built — see Section 10).

2. Per-carrier CC will never sum close to the platform total, by design — only outbound legs routed to an external carrier ever carry a trunk_id at all; a live sample found only ~18% of channels do. Expected, not a bug.

3. Historical/drill-down CPS is hangup-time data — a call still in progress has no call_admissions row yet.

4. trunk_channel_events and its whole write path are dead code from the Report API's perspective — nothing reads it anymore, but the write path is still running. Harmless, but see Section 10.

Ideas for follow-up work, not yet built or scheduled — in rough order of value-to-effort:

1. Remove the dead trunk_channel_events path. Delete the hangup-hook lines from astpp.xml.lua, delete trunk_hangup_hook.lua, stop the flush cron entry, and eventually drop the table. Zero risk since nothing reads this data — but touches a live routing script, so should be confirmed with the user first.

2. Extend direct-polling CC coverage to all accounts, not just the ~13 metered ones. The same show channels-based technique that drives per-trunk CC could resolve an account_id per live channel and give every account a real live CC number instead of "—". Highest-value remaining gap in the system.

3. Historical backfill. No cdrs data before the system went live was ever backfilled — worth doing once if long-term trend charts become a priority.

4. Alerting on collector health. Nothing currently pages anyone if cc-sampler.service or trunk-attempt-watcher.service silently stalls (this already happened once for ~39 hours, see Section 11). A simple staleness check would catch this class of bug far sooner.

5. Redis-backed cache instead of in-process TTL cache for the Report API, if it's ever scaled beyond a single worker process pair.

6. Formal retention/compression policy on the raw hypertables — nothing currently compresses or drops old raw rows.

Kept here deliberately, not just in commit messages — these explain why the current design looks the way it does, and re-attempting a "simpler" version of an already-tried approach is a real risk without this context.

• cc-sampler.service silently stale for ~39 hours. A per-account exception handler caught a dead-connection error in the same except clause as "this one account's reading was garbage," so it looped past a dead connection forever instead of reconnecting. Fixed by splitting connection-level errors from per-value parse errors. (Direct motivation for the alerting idea in Section 10.)

• ESL response desync. Sharing one ESL connection across queries meant a slow reply under load left a stale reply in the buffer, silently consumed by the next unrelated command. Fixed with isolated connections per concern.

• Admissions poller pointed at 127.0.0.1 after the Report API was locked to the WireGuard-only address — failed silently for hours; fixed URL, added batch chunking.

• Continuous aggregate refresh order. Refreshing cps_hourly directly did nothing when the intermediate cps_1s was itself stale — must refresh bottom-up.

• Exact-match disposition filter never matched real values (NORMAL_CLEARING vs. NORMAL_CLEARING [16]) — fixed to a LIKE match.

• Browser caching of the /live poll — fixed with cache: no-store client-side and explicit no-cache headers server-side.

• CPS window anchored to wall-clock now() almost-always read 0 once ingestion had any lag. Anchored to the freshest ingested timestamp instead.

• Only 13 of ~120,056 accounts are covered by the rate-limiter cache at all — every other account's calls pass through completely untracked by design. Platform-wide totals had to be sourced from FreeSWITCH's own show calls count instead.

• OpenSIPS entry-point gap. True entry-point CPS read roughly double what was reaching FreeSWITCH — about half of genuine attempts were absorbed (auth/routing failures) before FreeSWITCH ever saw them.

• Trunk CC via ESL event subscription — tried, measured, and abandoned. The first implementation derived live per-trunk concurrency from an event log. Two failures were found, in order: (1) ESL event loss — a direct measurement using FreeSWITCH's own Event-Sequence numbers proved ~73% of relevant events never reached the watcher under real load. (2) Fixing reliability (in-process file write + native api_hangup_hook, not event-bus-based) still wasn't enough — the majority of "start" events never got a matching "end" in further testing, most likely from FreeSWITCH reassigning a channel's UUID mid-call during transfer/park/diversion flows. Resolution: abandoned event-correlation for this metric entirely — cc_sampler_daemon.py now directly polls FreeSWITCH's live channel list every 5 seconds instead.