This is an old revision of the document!

---

# Radius Voice Records - Low Level Design

Oct 27, 2026 · @Gerard

## 1. Purpose and scope

`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.

## 2. Architecture and data flow

[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

1. The first Interim-Update for a call arrives from FreeRADIUS. The script inserts the row with `callActive = 1`. 2. While the call is active, a `finesse-start` from the Finesse server adds the agent, dialog and queue details to that row. 3. 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.

## 3. Request interface

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

```json {

"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

```json {

"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"

} ```

## 4. Processing flow

[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.

### Interim-Update

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

### Stop

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

### finesse-start

1. 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. 2. Prepare the agent fields, cutting each to its column length. 3. Find the row (Q5, or Q6 and Q7). 4. 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.

## 5. Data model

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

```sql CREATE NONCLUSTERED INDEX [IX_radiusVoiceCalls_phone_active]

  ON [dbo].[radiusVoiceCalls] ([customerPhoneNumber], [callActive])
  INCLUDE ([h323_connect_time], [DbDateTime]);

```

## 6. SQL queries

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.

### Interim-Update (`insertInterimIfNew`)

Q1. Does the call already have a row?

```sql 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)

```sql 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.

### Stop (`updateStop`)

Q3. Find the row

```sql 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

```sql 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.

### finesse-start (`findFinesseRow`, then update)

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

```sql 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)

```sql 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)

```sql 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

```sql 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.

## 7. Data conversions

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.

### Call duration

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.

1. Both times are read as time-zone-aware timestamps, so the clocks going back in autumn cannot distort the result. 2. `callDuration` is the difference in seconds, rounded to a whole second (8.945 seconds is stored as 9). 3. 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.

### Customer phone number

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.

## 8. Configuration, logging and error handling

### Configuration

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`.

### Logging

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.

### Response codes

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.

## 9. Operations, assumptions and known limitations

### Deployment checklist

- 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|`.

### Known behaviours

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.

### Test status

The script passes a PHP syntax check. The conversion functions, the Finesse row lookup and the request handling were exercised with a stand-in database that returned scripted rows and recorded each statement. It has not yet been run against a real SQL Server, so the first live Interim-Update, Stop and finesse-start should be checked against the table and the log.