Enterprise FreeSWITCH: Dynamic User Directories with PostgreSQL & JSONB
Introduction
FreeSWITCH fundamentally relies on XML for its configuration architecture—a robust design choice established during its inception by Anthony Minessale. While brilliant for static deployments, operating an enterprise-scale PBX or UCaaS (Unified Communications as a Service) platform demands a highly structured, scalable approach to daily operations. Managing thousands of evolving SIP extensions via flat XML files quickly becomes unsustainable.
To achieve robust agility, FreeSWITCH supports deep database and third-party system integrations for its core configuration and dialplans entirely through dynamic modules.
In this engineering guide, we will demonstrate how to serve the FreeSWITCH User Directory directly from a PostgreSQL cluster. We will utilize advanced highly-performant JSONB column types combined with rigorous JSON-schema database-level validation to guarantee infrastructure data integrity.
Architecture & Integration Flow
We will architect this integration utilizing three core components:
mod_luaBindings: A custom LUA script that intercepts FreeSWITCH directory requests and queries the database.- PostgreSQL
JSONB: SIP Users and their complex nested structures will be stored natively as JSON objects. - Database Schema Validation: We will deploy the third-party
postgres-json-schemaextension to strictly validate FreeSWITCH JSON configurations at the database tier before they are ever committed.
System Prerequisites
Ensure your environment meets the following baseline requirements:
- FreeSWITCH: Version 1.8+ installed with
mod_luaexplicitly loaded. - Database Connectivity: Core (
switch.conf.xml) and internal SIP profile (sip_profiles/internal.xml) databases are configured bounding to an ODBC DSN. - PostgreSQL: Version 12.0+ deployed and operational.
Stage 1: Extending PostgreSQL Capabilities
While PostgreSQL inherently validates JSON syntax, we require stringent schema validation to ensure that all FreeSWITCH attributes (passwords, voicemails, user contexts) conform perfectly to our telecom standards.
Step 1.1: Install PostgreSQL Development Libraries
sudo apt update && sudo apt install -y postgresql-server-dev-12
Step 1.2: Compile the JSON Schema Extension
Clone the robust postgres-json-schema repository and compile it directly into your Postgres installation path.
cd /usr/local/src/
git clone https://github.com/gavinwahl/postgres-json-schema.git
cd postgres-json-schema/
sudo make install
Step 1.3: Enable the Extension in PostgreSQL
Authenticate into your database and activate the extension to expose the validate_json_schema PL/pgSQL function.
sudo -u postgres psql
-- Inside the psql console
CREATE EXTENSION "postgres-json-schema";
\dx -- Verify installation list
Stage 2: Database Schema & Constraint Validation
We will now define the authoritative extensions table holding our SIP users.
Step 2.1: Construct the Extensions Table
CREATE TABLE extensions (
extension character varying(10) COLLATE pg_catalog."default" NOT NULL,
json_data jsonb,
CONSTRAINT extensions_pkey PRIMARY KEY (extension)
);
Step 2.2: Enforce Strict Schema Integrity via CHECK Constraints
This constraint guarantees that any json_data inserted completely conforms to our rigid FreeSWITCH structure, eliminating misconfigurations at the query layer.
ALTER TABLE extensions ADD CONSTRAINT data_is_valid CHECK (validate_json_schema('{
"$schema": "http://json-schema.org/draft-04/schema#",
"title": "FreeSWITCH JSON Schema for User Directory",
"description": "Validates complex nested SIP User jsonb structures.",
"type": "object",
"properties": {
"id": { "type": "integer" },
"params": {
"type": "object",
"properties": {
"password": { "type": "string" },
"a1-hash": { "type": "string" },
"dial-string": { "type": "string" },
"vm-password": { "type": "string" },
"vm-enabled": { "type": "string" },
"vm-mailto": { "type": "string" },
"vm-email-all-messages": { "type": "string" },
"vm-notify-all-messages": { "type": "string" },
"vm-attach-file": { "type": "string" },
"jsonrpc-allowed-methods": { "type": "string" },
"jsonrpc-allowed-event-channels": { "type": "string" }
},
"oneOf": [
{ "required": [ "password" ] },
{ "required": [ "a1-hash" ] }
],
"additionalProperties": false
},
"variables": {
"type": "object",
"properties": {
"user_context": { "type": "string" },
"callgroup": { "type": "string" },
"sched_hangup": { "type": "string" },
"toll_allow": { "type": "string" },
"accountcode": { "type": "string" },
"nibble_account": { "type": "string" },
"origination_caller_id_name": { "type": "string" },
"origination_caller_id_number": { "type": "string" },
"effective_caller_id_name": { "type": "string" },
"effective_caller_id_number": { "type": "string" },
"outbound_caller_id_name": { "type": "string" },
"outbound_caller_id_number": { "type": "string" }
},
"required": [ "user_context", "callgroup", "accountcode" ],
"additionalProperties": true
}
},
"required": [ "id", "params", "variables" ]
}', json_data));
Step 2.3: Insert a Valid SIP Endpoint
To test our rigid new constraint, we provision a sample SIP extension (7001).
INSERT INTO public.extensions(extension, json_data)
VALUES ('7001', '{
"id": 7001,
"params": {
"password": "SecurePassword123!",
"vm-mailto": "engineering@iqaai.com",
"dial-string": "the dial dialstring",
"vm-password": "123"
},
"variables": {
"callgroup": "engineering",
"accountcode": "33579",
"user_context": "default"
}
}');
Stage 3: FreeSWITCH Mod_Lua Integration
FreeSWITCH must now be told to query our LUA script whenever an endpoint attempts to REGISTER or is targeted for an INVITE.
Step 3.1: Bind LUA as the XML Handler
Edit the core config (autoload_configs/lua.conf.xml) to explicitly bind LUA to directory requests:
<configuration name="lua.conf" description="LUA Configuration">
<settings>
<param name="xml-handler-script" value="user_directory.lua"/>
<param name="xml-handler-bindings" value="directory"/>
</settings>
</configuration>
Note: A deep reloadxml is insufficient for initial binding assignments. A full FreeSWITCH application restart is mandatory.
Step 3.2: Engineer the LUA Database Handler
Create the primary interception script at /usr/local/freeswitch/scripts/user_directory.lua. This script connects to the ODBC backbone, fetches the JSONB block, deserializes it, and maps it strictly into the dynamic XML format FreeSWITCH internals expect.
-- ==========================================================
-- FreeSWITCH Dynamic User Directory via PostgreSQL JSONB
-- ==========================================================
package.path = '/usr/local/freeswitch/scripts/json.lua'
json = require("json")
package.path = '/usr/local/freeswitch/scripts/common.lua'
common = require("common")
-- Extract Request Metadata
user = params:getHeader("user")
domain_name = params:getHeader("domain")
event_calling_func = params:getHeader("Event-Calling-Function")
-- Initialize Database Connection Pool (via ODBC)
db = freeswitch.Dbh("odbc://freeswitch:freeswitch:freeswitch!")
assert(db:connected())
-- Intercept User Directory Events
if common.is_user_directory_function(event_calling_func) then
query = string.format("SELECT extension, json_data::text AS json_data FROM extensions WHERE extension = '%s'", user)
freeswitch.consoleLog("debug", "Executing User Query: " .. query)
function fetch_user_object(row)
json_data = row.json_data
extension = row.extension
end
db:query(query, fetch_user_object)
if (json_data == nil) then
XML_STRING = common.XML_STRING_NOT_FOUND
end
if (json_data) then
lua_value = json.parse(json_data)
-- Dynamically inject standard modern Dial-Strings
lua_value['params']['dial-string'] = "{^^:sip_invite_domain=${dialed_domain}:presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(*/${dialed_user}@${dialed_domain})},${verto_contact(${dialed_user}@${dialed_domain})}"
call_group = lua_value['variables']['call_group']
if call_group == nil then
call_group = user
end
-- Construct the XML Response Buffer
local xml = {}
table.insert(xml, [[<document type="freeswitch/xml">]])
table.insert(xml, [[ <section name="directory">]])
table.insert(xml, [[ <domain name="]] .. domain_name .. [[">]])
table.insert(xml, [[ <groups>]])
table.insert(xml, [[ <group name="]] .. call_group .. [[">]])
table.insert(xml, [[ <users>]])
table.insert(xml, [[ <user id="]] .. user .. [[">]])
for block, bvalue in pairs(lua_value) do
if type(bvalue) == 'table' then
table.insert(xml, [[ <]].. block ..[[>]])
for tag, tvalue in pairs(bvalue) do
table.insert(xml, [[ <]].. string.sub(block, 1, -2) ..[[ name="]] .. tag .. [[" value="]] .. tvalue .. [["/>]])
end
table.insert(xml, [[ </]].. block ..[[>]])
end
end
table.insert(xml, [[ </user>]])
table.insert(xml, [[ </users>]])
table.insert(xml, [[ </group>]])
table.insert(xml, [[ </groups>]])
table.insert(xml, [[ </domain>]])
table.insert(xml, [[ </section>]])
table.insert(xml, [[</document>]])
XML_STRING = table.concat(xml, "\n")
end
else
XML_STRING = common.XML_STRING_NOT_FOUND
end
freeswitch.consoleLog("debug", XML_STRING)
Post-Deployment Testing
Register an external SIP endpoint or WebRTC softphone targeting your FreeSWITCH fabric using the credentials provisioned strictly in the extensions SQL table.
Monitor the real-time execution via fs_cli. You should observe high-performance debug streams detailing the precise SQL lookup, JSON deserialization, and the massive corresponding XML string instantly generated directly from PostgreSQL and served to the dialplan.
You have successfully decoupled telecom control components from flat text, ushering in robust, programmatically configurable cloud infrastructure.