Radius Voice Records - Low Level Design

radiusVoiceCalls.php keeps one row per voice call in dbo.radiusVoiceCalls (database eirFinesseGadget_db), built up from two sources: FreeRADIUS accounting records and Cisco Finesse agent-desktop events.

* Interim-Update (FreeRADIUS) inserts the row when the call is first reported. * Stop (FreeRADIUS) closes the call: disconnect time, cause, duration, callActive = 0. * finesse-start (Cisco Finesse) adds the agent, dialog and queue details to the existing row.

Another system reads the table, filtering on callActive and customerPhoneNumber, to see active calls and call history for a customer.

Runtime: one PHP file (radiusVoiceCalls.php) plus include/sql_db.php (the PDO connection), on Apache httpd with PHP 8.x and the pdo_sqlsrv driver, writing to SQL Server.

Out of scope: FreeRADIUS Start records, any other Finesse event type, retries of failed requests, and purging or archiving old rows.

[embedded content: architecture and data flow · 6 components\]

The gateway's accounting records reach the script through FreeRADIUS, the Finesse server calls it directly, and the script is the only writer to the table.

How one call's row is built up

- The first Interim-Update for a call arrives from FreeRADIUS. The script inserts the row with callActive = 1. - While the call is active, a finesse-start from the Finesse server adds the agent, dialog and queue details to that row. - The Stop from FreeRADIUS closes the call: disconnect time, cause, duration, and callActive = 0.

The reading system can query the row at any point in this sequence.

The script accepts HTTP PUT with a JSON object body at one URL, from allowed source IPs only. Every response is JSON in the form {“Reply-Message”: “…”}.

Checks, in the order they run

Request types

The script reads Acct-Status-Type first and falls back to acctStatusType. The value is matched case-insensitively. Any other value, including the earlier Cisco-Finesse placeholder, returns 422.

All values are read as text. A field that is missing, empty or not a plain value (an object or list) is treated as absent.

Example Interim-Update body

{
  "Acct-Session-Id": "00002C27",
  "h323-setup-time": "13:51:51.950 IRL Tue Oct 20 2026",
  "h323-gw-id": "UCCE-SP-CUBE-1-TST.network.example.com",
  "h323-conf-id": "DB0B4B6B CBBB11F1 AE7699CA 3DE394D9",
  "h323-call-origin": "answer",
  "h323-connect-time": "13:51:52.011 IRL Tue Oct 20 2026",
  "gw-cdn": "+35312612800",
  "Called-Station-Id": "12800",
  "User-Name": "+35312399138",
  "NAS-IP-Address": "10.123.123.46",
  "Acct-Status-Type": "Interim-Update"
}

Example finesse-start body

{
  "acctStatusType": "finesse-start",
  "h323ConfId": "3C36243ACDFC11F18F0AB15DB7BDE126",
  "dialogId": "16814315",
  "agentId": "91262",
  "agentFirstName": "Gerry",
  "agentLastName": "O'Rourke",
  "agentExtension": "6191262",
  "callType": "PREROUTE_ACD_IN",
  "phoneNumber": "+35312399138",
  "queueName": "eircomSK01.SG"
}

[embedded content: request processing · 5 checks, 3 handlers\]

A failed check stops the request and returns the code shown in its box. Section 8 lists every code and message.

- Read the call ID (spaces removed), the gateway and the session ID. If any of the three is missing, return 422. - Work out the customer phone number from the call direction and the E.164 rules (section 7). - Open a transaction and look up the call ID (Q1). If a row exists, log the request at DEBUG and return 200 without writing. - Otherwise insert the row (Q2) and commit. A duplicate-key error from a simultaneous request is retried once.

- If h323-conf-id is missing, return 422. - Convert the times, work out callDuration, and convert the disconnect cause from hex (section 7). - Look up the call ID (Q3). If there is no row, log an ERROR with the request and return 404. - Update the row (Q4), which sets callActive = 0, and commit.

- Decide how to find the call: by a real h323ConfId, or by phone number when h323ConfId is not_set, empty or missing. With neither a real ID nor a phone number, return 422. - Prepare the agent fields, cutting each to its column length. - Find the row (Q5, or Q6 and Q7). - One row: update it (Q8) and return 200. No rows: return 404. More than one row left after the window: return 422 and update nothing.

One table, dbo.radiusVoiceCalls, holds one row per call. h323_conf_id is the only key used to find a call: Acct_Session_Id is stored but never searched, because different gateways can reuse the same value.

Columns and which request writes them

Keys and indexes

CREATE NONCLUSTERED INDEX [IX_radiusVoiceCalls_phone_active]
    ON [dbo].[radiusVoiceCalls] ([customerPhoneNumber], [callActive])
    INCLUDE ([h323_connect_time], [DbDateTime]);

Each request opens its own connection (getDbConnection()), runs all its statements inside one transaction, and commits or rolls back as a unit. Every statement is a prepared statement with bound parameters, so no request value is ever concatenated into SQL. Date and time values are bound as ISO 8601 text (2026-10-20T13:51:52.011), which SQL Server reads the same way whatever its language or DATEFORMAT setting.

Q1. Does the call already have a row?

SELECT id
  FROM dbo.radiusVoiceCalls WITH (UPDLOCK, HOLDLOCK)
 WHERE h323_conf_id = :conf

The lookup uses UQ_h323_conf_id. UPDLOCK, HOLDLOCK keeps the looked-up key range locked until the transaction ends, so a second request for the same call waits instead of inserting in the gap between this check and the insert. If a row comes back, nothing is written and the script logs the request at DEBUG and returns 200.

Q2. Insert the row (only when Q1 found nothing)

INSERT INTO dbo.radiusVoiceCalls
  (DbDateTime, Acct_Session_Id, h323_conf_id, h323_gw_id, GW_CalledNumber, Called_Station_Id,
   User_Name, NAS_IP_Address, h323_setup_time, h323_connect_time, inboundCall,
   customerPhoneNumber, callActive, contactCentreCall, recordUpdates)
VALUES
  (GETDATE(), :sid, :conf, :gw, :gwcdn, :csi,
   :usr, :nas, :setup, :conn, :inb,
   :cpn, 1, :cce, 1)

callActive and recordUpdates are set to 1 in the statement. Optional values that were absent from the request are bound as NULL.

Duplicate-key retry. If two requests for the same new call overlap and the insert fails with a duplicate-key error on UQ_h323_conf_id, the transaction is rolled back and the whole function runs once more. The second attempt finds the row in Q1 and returns without writing. Any other database error, or a second failure, returns 500.

Q3. Find the row

SELECT id, h323_disconnect_time
  FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
 WHERE h323_conf_id = :conf

No row means the Stop arrived without an Interim-Update (for example a call that never connected): the script logs an ERROR with the request and returns 404. A row that already has a disconnect time logs a WARNING and is still updated.

Q4. Close the call

UPDATE dbo.radiusVoiceCalls SET
    h323_disconnect_time  = COALESCE(:disc,   h323_disconnect_time),
    h323_disconnect_cause = COALESCE(:cause,  h323_disconnect_cause),
    callActive            = 0,
    h323_remote_address   = COALESCE(:remote, h323_remote_address),
    Acct_Session_Time     = COALESCE(:ast,    Acct_Session_Time),
    callDuration          = COALESCE(:dur,    callDuration),
    recordUpdates         = ISNULL(recordUpdates, 0) + 1
 WHERE id = :id

COALESCE means a value that could not be read from the Stop request leaves the stored value as it was.

Q5. Find by call ID (when h323ConfId is a real value)

SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK) WHERE h323_conf_id = :conf

No row returns 404. There is no fallback to the phone number.

Q6. Find by phone number (when h323ConfId is not_set, empty or missing)

SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
 WHERE customerPhoneNumber = :ph AND callActive = 1

No rows returns 404. One row is updated. More than one row goes to Q7.

Q7. Narrow by connect time (only when Q6 returned more than one row)

SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
 WHERE customerPhoneNumber = :ph AND callActive = 1 AND h323_connect_time >= :cutoff

:cutoff is the current time in Europe/Dublin, computed in PHP, minus $FINESSE_MATCH_SECONDS (default 5). It is computed in PHP because h323_connect_time holds Irish local time and the SQL Server clock may use another zone. One row is updated, no rows returns 404, and more than one row returns 422 without updating anything.

Q8. Write the agent details

UPDATE dbo.radiusVoiceCalls SET
    dialogId = :dlg, agentId = :aid, agentFirstName = :fn, agentLastName = :ln,
    agentExtension = :ext, cceCallType = :ctype, queueName = :q,
    contactCentreAgentConnected = 1,
    agentUpdates  = ISNULL(agentUpdates, 0) + 1,
    recordUpdates = ISNULL(recordUpdates, 0) + 1
 WHERE id = :id

Values are written exactly as sent, including not_set. A field missing from the request is written as NULL.

Request values are cleaned before they reach SQL. Anything that cannot be converted is stored as NULL and logged at WARNING, and the rest of the request still goes ahead.

Duration is only worked out when the Stop request carries h323-connect-time, which a call that never connected does not have. In that case callDuration stays NULL.

- Both times are read as time-zone-aware timestamps, so the clocks going back in autumn cannot distort the result. - callDuration is the difference in seconds, rounded to a whole second (8.945 seconds is stored as 9). - If either time is unreadable, or the disconnect is before the connect, the script uses Acct-Session-Time instead and logs the reason. The repeated hour at the autumn clock change (01:00 to 02:00) is ambiguous, and a call spanning it can hit this fallback.

The value stored in customerPhoneNumber depends on the call direction, taken from h323-call-origin (case-insensitive).

Outbound numbers arrive with an extra leading 0, the outside-line prefix. The rules are tried in this order, and the first match wins.

All settings are variables and constants at the top of radiusVoiceCalls.php.

The database host, name, user and password live in include/sql_db.php, which the script loads with require_once DIR . “/include/sql_db.php”. The database name there must be eirFinesseGadget_db.

Lines go to the PHP error log in the form |radiusVoiceCalls.php|LEVEL|{json}. The JSON always has a msg, plus client_ip, and acct_session_id, h323_conf_id and dialog_id once the request has supplied them, so one call can be followed across lines. Error lines include the request body (cut to 2,000 characters when the JSON itself is invalid). The api-key value is never logged.

Required fields. An Interim-Update needs Acct-Session-Id, h323-conf-id and h323-gw-id, because those columns cannot be NULL. A Stop needs h323-conf-id. A finesse-start needs either a real h323ConfId or a phoneNumber. Every other field is optional.

* Apache must pass PUT requests to PHP. Check that no Limit or LimitExcept rule blocks the method. * PHP needs the pdo_sqlsrv extension and the Microsoft ODBC driver for SQL Server. * The database login needs SELECT, INSERT and UPDATE on dbo.radiusVoiceCalls. * include/sql_db.php must point at eirFinesseGadget_db. If the password contains $, use single quotes around it in that file. * Fill in $allowed_ips, $api_key and $CCE_CUBES. Add the FreeRADIUS server and the Finesse server to $allowed_ips. * Serve the script directly from Apache. Behind a local reverse proxy every request looks like 127.0.0.1 and would skip the api-key check. * Use HTTPS at Apache, because the api-key travels in a header. * Create the index in section 5. * Read the PHP error log for lines starting |radiusVoiceCalls.php|.

Recommended housekeeping (not built). A scheduled SQL job that sets callActive = 0 on rows older than a chosen age, for example 12 hours, would clear stuck calls.

SELECT * FROM radiusVoiceCalls
WHERE h323_connect_time < DATEADD(HOUR,-3,GETDATE())
AND callActive = 1
ORDER BY id

We can find these calls later - by checking how many rows have a disconnect cause of 128

UPDATE radiusVoiceCalls
SET 
callActive = 0,
h323_disconnect_cause = 128,
h323_disconnect_time = GetDate()
 
WHERE h323_connect_time < DATEADD(HOUR,-3,GETDATE())
AND callActive = 1

We can find these calls later - by checking how many rows have a disconnect cause of 128

UPDATE radiusVoiceCalls
SET 
callDuration = DATEDIFF(SECOND, h323_connect_time, h323_disconnect_time),
Acct_Session_Time = DATEDIFF(SECOND, h323_connect_time, h323_disconnect_time)
WHERE 
h323_disconnect_cause = 128
AND callDuration IS NULL