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-15
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: Create Dependency (common.lua)
The main handler will rely on generic utility logic for validations and returning static "Not Found" XML documents. Create this file at /usr/local/freeswitch/scripts/common.lua.
local common = {};
common.title = "Welcome to common functions"
common.user_directory_funcs = {'user_outgoing_channel', 'sofia_reg_parse_auth', 'voicemail_leave_main', 'user_data_function'}
common.sofia_funcs = {'config_sofia'}
function common.is_user_directory_function (val)
for index, value in ipairs(common.user_directory_funcs) do
if value == val then
return true
end
end
return false
end
common.XML_STRING_NOT_FOUND = [[
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
<section name="result">
<result status="not found" />
</section>
</document>
]];
return common
Step 3.3: Create Dependency (json.lua)
FreeSWITCH LUA does not natively parse JSON. We need a robust implementation to parse our JSONB responses. Create this file at /usr/local/freeswitch/scripts/json.lua.
--[[ json.lua ]]
local json = {}
-- Internal functions.
local function kind_of(obj)
if type(obj) ~= 'table' then return type(obj) end
local i = 1
for _ in pairs(obj) do
if obj[i] ~= nil then i = i + 1 else return 'table' end
end
if i == 1 then return 'table' else return 'array' end
end
local function escape_str(s)
local in_char = {'\\', '"', '/', '\b', '\f', '\n', '\r', '\t'}
local out_char = {'\\', '"', '/', 'b', 'f', 'n', 'r', 't'}
for i, c in ipairs(in_char) do
s = s:gsub(c, '\\' .. out_char[i])
end
return s
end
-- Returns pos, did_find
local function skip_delim(str, pos, delim, err_if_missing)
pos = pos + #str:match('^%s*', pos)
if str:sub(pos, pos) ~= delim then
if err_if_missing then
error('Expected ' .. delim .. ' near position ' .. pos)
end
return pos, false
end
return pos + 1, true
end
-- Parse string value
local function parse_str_val(str, pos, val)
val = val or ''
local early_end_error = 'End of input found while parsing string.'
if pos > #str then error(early_end_error) end
local c = str:sub(pos, pos)
if c == '"' then return val, pos + 1 end
if c ~= '\\' then return parse_str_val(str, pos + 1, val .. c) end
local esc_map = {b = '\b', f = '\f', n = '\n', r = '\r', t = '\t'}
local nextc = str:sub(pos + 1, pos + 1)
if not nextc then error(early_end_error) end
return parse_str_val(str, pos + 2, val .. (esc_map[nextc] or nextc))
end
-- Parse number value
local function parse_num_val(str, pos)
local num_str = str:match('^-?%d+%.?%d*[eE]?[+-]?%d*', pos)
local val = tonumber(num_str)
if not val then error('Error parsing number at position ' .. pos .. '.') end
return val, pos + #num_str
end
-- Public values and functions.
function json.stringify(obj, as_key)
local s = {}
local kind = kind_of(obj)
if kind == 'array' then
if as_key then error('Can\'t encode array as key.') end
s[#s + 1] = '['
for i, val in ipairs(obj) do
if i > 1 then s[#s + 1] = ', ' end
s[#s + 1] = json.stringify(val)
end
s[#s + 1] = ']'
elseif kind == 'table' then
if as_key then error('Can\'t encode table as key.') end
s[#s + 1] = '{'
for k, v in pairs(obj) do
if #s > 1 then s[#s + 1] = ', ' end
s[#s + 1] = json.stringify(k, true)
s[#s + 1] = ':'
s[#s + 1] = json.stringify(v)
end
s[#s + 1] = '}'
elseif kind == 'string' then
return '"' .. escape_str(obj) .. '"'
elseif kind == 'number' then
if as_key then return '"' .. tostring(obj) .. '"' end
return tostring(obj)
elseif kind == 'boolean' then
return tostring(obj)
elseif kind == 'nil' then
return 'null'
else
error('Unjsonifiable type: ' .. kind .. '.')
end
return table.concat(s)
end
json.null = {}
function json.parse(str, pos, end_delim)
pos = pos or 1
if pos > #str then error('Reached unexpected end of input.') end
local pos = pos + #str:match('^%s*', pos)
local first = str:sub(pos, pos)
if first == '{' then
local obj, key, delim_found = {}, true, true
pos = pos + 1
while true do
key, pos = json.parse(str, pos, '}')
if key == nil then return obj, pos end
if not delim_found then error('Comma missing between object items.') end
pos = skip_delim(str, pos, ':', true)
obj[key], pos = json.parse(str, pos)
pos, delim_found = skip_delim(str, pos, ',')
end
elseif first == '[' then
local arr, val, delim_found = {}, true, true
pos = pos + 1
while true do
val, pos = json.parse(str, pos, ']')
if val == nil then return arr, pos end
if not delim_found then error('Comma missing between array items.') end
arr[#arr + 1] = val
pos, delim_found = skip_delim(str, pos, ',')
end
elseif first == '"' then
return parse_str_val(str, pos + 1)
elseif first == '-' or first:match('%d') then
return parse_num_val(str, pos)
elseif first == end_delim then
return nil, pos + 1
else
local literals = {['true'] = true, ['false'] = false, ['null'] = json.null}
for lit_str, lit_val in pairs(literals) do
local lit_end = pos + #lit_str - 1
if str:sub(pos, lit_end) == lit_str then return lit_val, lit_end + 1 end
end
local pos_info_str = 'position ' .. pos .. ': ' .. str:sub(pos, pos + 10)
error('Invalid json syntax starting at ' .. pos_info_str)
end
end
return json
Step 3.4: Engineer the LUA Database Handler (user_directory.lua)
Create the primary interception script at /usr/local/freeswitch/scripts/user_directory.lua. This script explicitly links the two dependencies above, 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.
Discussion0
Join the conversation. Sign in to leave a comment.