Skip to main content

Database Schema & Migrations

Overview​

The Ominis Cluster Manager uses PostgreSQL as its single source of truth for all persistent state. This includes SIP extensions, IVR configurations, call center queues, active channels, CDR records, and access control lists.

Architecture Principle: Database-first design - All configuration lives in PostgreSQL, synchronized to FreeSWITCH instances via ODBC (for FreeSWITCH core) and XML-RPC (for runtime control).

Key Benefits​

  • ✅ Single Source of Truth: All state lives in PostgreSQL - no file synchronization required
  • ✅ Real-Time Queries: 10-50x faster than XML-RPC for channel/call lookups
  • ✅ Dynamic Configuration: Changes applied instantly via mod_xml_curl and ODBC
  • ✅ Audit Trail: Full history with timestamps on all configuration tables
  • ✅ High Availability: PostgreSQL clustering supports multi-region deployments

Database Architecture​

Schema Organization​

The database schema is organized into 6 logical domains:

DomainTablesPurpose
ExtensionsextensionsSIP user directory (authentication via mod_xml_curl)
IVR Systemivrs, ivr_menus, ivr_menu_options, ivr_tts_cacheXML-CURL IVR with OpenAI TTS
FreeSWITCH Corechannels, calls, registrations, recovery, complete, aliases, tasks, nat, interfacesReal-time state managed by FreeSWITCH ODBC
Call Centeragents, tiers, membersmod_callcenter state (ODBC-backed)
CDRcdrCall detail records for analytics
ACLacl_entriesIP-based access control lists

Complete Table Reference​

1. Extensions Table​

Purpose: SIP user directory for FreeSWITCH authentication via mod_xml_curl.

CREATE TABLE IF NOT EXISTS extensions (
    number VARCHAR(20) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    email VARCHAR(255),
    department VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
    call_forwarding_json JSONB,
    voicemail_json JSONB,
    features_json JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_extensions_status ON extensions(status);
CREATE INDEX idx_extensions_department ON extensions(department);
CREATE INDEX idx_extensions_email ON extensions(email);

Key Features:

  • Used by GET /v1/freeswitch/directory endpoint (called by FreeSWITCH mod_xml_curl)
  • JSONB columns for flexible feature configuration (call forwarding, voicemail)
  • Automatic updated_at timestamp via trigger
  • Status field supports active, inactive, suspended

Example Row:

{
  "number": "1001",
  "password": "Winnipeg2025",
  "display_name": "John Doe",
  "email": "john@example.com",
  "department": "Sales",
  "status": "active",
  "call_forwarding_json": {"enabled": true, "destination": "1002"},
  "voicemail_json": {"enabled": true, "email_notify": true}
}

2. IVR Tables​

ivrs Table​

Purpose: IVR instances - one pod per IVR.

CREATE TABLE IF NOT EXISTS ivrs (
    name VARCHAR(64) PRIMARY KEY,
    description TEXT,
    openai_api_key TEXT NOT NULL, -- Encrypted at rest in production
    default_language VARCHAR(10) DEFAULT 'en',
    default_voice VARCHAR(50) DEFAULT 'alloy',
    tts_model VARCHAR(50) DEFAULT 'tts-1',
    status VARCHAR(20) DEFAULT 'pending', -- pending, running, stopped, error
    pod_name VARCHAR(255),
    service_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ivrs_status_check CHECK (status IN ('pending', 'running', 'stopped', 'error'))
);

Example:

{
  "name": "customer-support",
  "description": "Main customer support IVR",
  "openai_api_key": "sk-...",
  "default_language": "en",
  "default_voice": "alloy",
  "tts_model": "tts-1",
  "status": "running",
  "pod_name": "freeswitch-ivr-customer-support"
}

ivr_menus Table​

Purpose: IVR menu definitions with TTS prompts.

CREATE TABLE IF NOT EXISTS ivr_menus (
    id SERIAL PRIMARY KEY,
    ivr_name VARCHAR(64) NOT NULL REFERENCES ivrs(name) ON DELETE CASCADE,
    menu_id VARCHAR(100) NOT NULL, -- e.g., "main_menu", "sales_menu"
    prompt_text TEXT NOT NULL,
    tts_voice VARCHAR(50), -- Override default voice
    timeout_seconds INTEGER DEFAULT 5,
    max_retries INTEGER DEFAULT 3,
    invalid_prompt TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(ivr_name, menu_id)
);

ivr_menu_options Table​

Purpose: Menu digit mappings to actions.

CREATE TABLE IF NOT EXISTS ivr_menu_options (
    id SERIAL PRIMARY KEY,
    menu_id INTEGER NOT NULL REFERENCES ivr_menus(id) ON DELETE CASCADE,
    digit VARCHAR(10) NOT NULL, -- "1", "2", "*", "#", "0", "timeout"
    action_type VARCHAR(50) NOT NULL, -- transfer_queue, transfer_extension, sub_menu, http_api_call, hangup, play_audio, voicemail
    action_target TEXT NOT NULL, -- Queue name, extension, menu_id, URL, file path
    action_config JSONB, -- Additional action configuration
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(menu_id, digit)
);

Action Types:

  • transfer_queue - Transfer to callcenter queue
  • transfer_extension - Transfer to SIP extension
  • sub_menu - Navigate to sub-menu
  • http_api_call - Make HTTP request and speak response
  • hangup - Hang up call
  • play_audio - Play audio file
  • voicemail - Send to voicemail

ivr_tts_cache Table​

Purpose: OpenAI TTS audio file cache (SHA256-based deduplication).

CREATE TABLE IF NOT EXISTS ivr_tts_cache (
    id SERIAL PRIMARY KEY,
    text_hash VARCHAR(64) NOT NULL UNIQUE, -- SHA256(text + voice + language + model)
    text TEXT NOT NULL,
    language VARCHAR(10) NOT NULL,
    voice VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    file_path TEXT NOT NULL,
    file_size_bytes INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    access_count INTEGER DEFAULT 0
);

Caching Strategy:

  • Hash computed as SHA256(text + voice + language + model)
  • Cache hit = reuse existing audio file (saves OpenAI API cost)
  • access_count tracks popularity for cache eviction

3. FreeSWITCH Core Tables​

These tables are managed by FreeSWITCH core via ODBC. Do NOT write to these tables from the API - they are automatically maintained by FreeSWITCH.

channels Table​

Purpose: Active channel information (real-time).

CREATE TABLE IF NOT EXISTS channels (
   uuid VARCHAR(256),
   direction VARCHAR(32),
   created VARCHAR(128),
   created_epoch INTEGER,
   name VARCHAR(1024),
   state VARCHAR(64),
   cid_name VARCHAR(1024),
   cid_num VARCHAR(256),
   ip_addr VARCHAR(256),
   dest VARCHAR(1024),
   application VARCHAR(128),
   application_data VARCHAR(4096),
   dialplan VARCHAR(128),
   context VARCHAR(128),
   read_codec VARCHAR(128),
   read_rate VARCHAR(32),
   read_bit_rate VARCHAR(32),
   write_codec VARCHAR(128),
   write_rate VARCHAR(32),
   write_bit_rate VARCHAR(32),
   secure VARCHAR(64),
   hostname VARCHAR(256),
   presence_id VARCHAR(4096),
   presence_data VARCHAR(4096),
   accountcode VARCHAR(256),
   callstate VARCHAR(64),
   callee_name VARCHAR(1024),
   callee_num VARCHAR(256),
   callee_direction VARCHAR(5),
   call_uuid VARCHAR(256),
   sent_callee_name VARCHAR(1024),
   sent_callee_num VARCHAR(256),
   initial_cid_name VARCHAR(1024),
   initial_cid_num VARCHAR(256),
   initial_ip_addr VARCHAR(256),
   initial_dest VARCHAR(1024),
   initial_dialplan VARCHAR(128),
   initial_context VARCHAR(128)
);

Performance: 10-50x faster than XML-RPC for channel lookups.

Indexes:

CREATE INDEX chidx1 ON channels(hostname);
CREATE INDEX uuindex ON channels(uuid, hostname);
CREATE INDEX uuindex2 ON channels(call_uuid);

calls Table​

Purpose: Call information (bridges between channels).

CREATE TABLE IF NOT EXISTS calls (
   call_uuid VARCHAR(255),
   call_created VARCHAR(128),
   call_created_epoch INTEGER,
   caller_uuid VARCHAR(256),
   callee_uuid VARCHAR(256),
   hostname VARCHAR(256)
);

Indexes:

CREATE INDEX callsidx1 ON calls(hostname);
CREATE INDEX eruuindex ON calls(caller_uuid, hostname);
CREATE INDEX eeuuindex ON calls(callee_uuid);
CREATE INDEX eeuuindex2 ON calls(call_uuid);

registrations Table​

Purpose: SIP registrations (managed by freeswitch-registrar).

CREATE TABLE IF NOT EXISTS registrations (
   reg_user VARCHAR(256),
   realm VARCHAR(256),
   token VARCHAR(256),
   url TEXT,
   expires INTEGER,
   network_ip VARCHAR(256),
   network_port VARCHAR(256),
   network_proto VARCHAR(256),
   hostname VARCHAR(256),
   metadata VARCHAR(256)
);

Index:

CREATE INDEX regindex1 ON registrations(reg_user, realm, hostname);

FreeSWITCH Core Views​

detailed_calls View: Comprehensive call information with bridged channels (joins channels and calls tables).

basic_calls View: Simplified call information (lighter query).

4. Call Center Tables (mod_callcenter)​

These tables are managed by FreeSWITCH mod_callcenter via ODBC. The API can read and write (via XML-RPC).

agents Table​

Purpose: Call center agent definitions and state.

CREATE TABLE IF NOT EXISTS agents (
    name VARCHAR(255),
    instance_id VARCHAR(255),
    uuid VARCHAR(255) NOT NULL DEFAULT '',
    type VARCHAR(255) NOT NULL DEFAULT 'callback',
    contact VARCHAR(255) NOT NULL DEFAULT '',
    status VARCHAR(255) NOT NULL DEFAULT 'Available',
    state VARCHAR(255) NOT NULL DEFAULT 'Waiting',
    max_no_answer INTEGER NOT NULL DEFAULT 0,
    wrap_up_time INTEGER NOT NULL DEFAULT 0,
    reject_delay_time INTEGER NOT NULL DEFAULT 0,
    busy_delay_time INTEGER NOT NULL DEFAULT 0,
    no_answer_delay_time INTEGER NOT NULL DEFAULT 0,
    last_bridge_start INTEGER NOT NULL DEFAULT 0,
    last_bridge_end INTEGER NOT NULL DEFAULT 0,
    last_offered_call INTEGER NOT NULL DEFAULT 0,
    last_status_change INTEGER NOT NULL DEFAULT 0,
    no_answer_count INTEGER NOT NULL DEFAULT 0,
    calls_answered INTEGER NOT NULL DEFAULT 0,
    talk_time INTEGER NOT NULL DEFAULT 0,
    ready_time INTEGER NOT NULL DEFAULT 0
);

Key Fields:

  • type: Always callback (agents receive calls, don't login to queue)
  • contact: SIP URI (e.g., sofia/gateway/registrar/agent-10720)
  • status: Available, On Break, Logged Out, Available (On Demand)
  • state: Waiting, Receiving, In a queue call

Indexes:

CREATE INDEX idx_agents_name ON agents(name);
CREATE INDEX idx_agents_uuid ON agents(uuid);
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_state ON agents(state);

tiers Table​

Purpose: Queue-agent relationships and priorities.

CREATE TABLE IF NOT EXISTS tiers (
    queue VARCHAR(255),
    instance_id VARCHAR(255),
    agent VARCHAR(255),
    state VARCHAR(255) NOT NULL DEFAULT 'Ready',
    level INTEGER NOT NULL DEFAULT 1,
    position INTEGER NOT NULL DEFAULT 1
);

Key Fields:

  • queue: Queue name (e.g., queue-sales@default)
  • agent: Agent name (matches agents.name)
  • state: Ready, No Answer, Standby
  • level: Priority level (1 = highest)
  • position: Position within level

Indexes:

CREATE INDEX idx_tiers_queue ON tiers(queue);
CREATE INDEX idx_tiers_agent ON tiers(agent);
CREATE INDEX idx_tiers_state ON tiers(state);

members Table​

Purpose: Callers waiting in queues.

CREATE TABLE IF NOT EXISTS members (
    queue VARCHAR(255),
    instance_id VARCHAR(255),
    uuid VARCHAR(255) NOT NULL DEFAULT '',
    session_uuid VARCHAR(255) NOT NULL DEFAULT '',
    cid_number VARCHAR(255),
    cid_name VARCHAR(255),
    system_epoch INTEGER NOT NULL DEFAULT 0,
    joined_epoch INTEGER NOT NULL DEFAULT 0,
    rejoined_epoch INTEGER NOT NULL DEFAULT 0,
    bridge_epoch INTEGER NOT NULL DEFAULT 0,
    abandoned_epoch INTEGER NOT NULL DEFAULT 0,
    base_score INTEGER NOT NULL DEFAULT 0,
    skill_score INTEGER NOT NULL DEFAULT 0,
    serving_agent VARCHAR(255),
    serving_system VARCHAR(255),
    state VARCHAR(255)
);

Indexes:

CREATE INDEX idx_members_queue ON members(queue);
CREATE INDEX idx_members_uuid ON members(uuid);
CREATE INDEX idx_members_session_uuid ON members(session_uuid);
CREATE INDEX idx_members_state ON members(state);

5. CDR Table​

Purpose: Call detail records for analytics and billing.

CREATE TABLE IF NOT EXISTS cdr (
    uuid VARCHAR(255) PRIMARY KEY,
    caller_id_name VARCHAR(255),
    caller_id_number VARCHAR(255),
    destination_number VARCHAR(255),
    context VARCHAR(255),
    start_stamp TIMESTAMP,
    answer_stamp TIMESTAMP,
    end_stamp TIMESTAMP,
    duration INTEGER,
    billsec INTEGER,
    hangup_cause VARCHAR(255),
    json_cdr TEXT,
    -- Queue-specific fields
    queue_name VARCHAR(255),
    agent_name VARCHAR(255),
    member_session_uuid VARCHAR(255),
    member_state VARCHAR(50),
    -- Node identification
    switch_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

CREATE INDEX idx_cdr_queue ON cdr(queue_name);
CREATE INDEX idx_cdr_start ON cdr(start_stamp);
CREATE INDEX idx_cdr_hangup ON cdr(hangup_cause);
CREATE INDEX idx_cdr_switch ON cdr(switch_name);

Data Flow:

  1. FreeSWITCH generates JSON CDR on call hangup
  2. mod_odbc_cdr writes to PostgreSQL via ODBC
  3. API exposes CDR analytics via /v1/cdr endpoints

6. ACL Table​

Purpose: IP-based access control lists for FreeSWITCH.

CREATE TABLE IF NOT EXISTS acl_entries (
    id SERIAL PRIMARY KEY,
    list_name VARCHAR(64) NOT NULL DEFAULT 'trusted_users',
    cidr VARCHAR(128) NOT NULL,
    permission VARCHAR(10) NOT NULL DEFAULT 'allow',  -- 'allow' or 'deny'
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(list_name, cidr)
);

Index:

CREATE INDEX idx_acl_entries_list ON acl_entries(list_name);

Seed Data:

INSERT INTO acl_entries (list_name, cidr, permission, description) VALUES
('trusted_users', '10.42.0.0/16', 'allow', 'Kubernetes pod network'),
('trusted_users', '10.43.0.0/16', 'allow', 'Kubernetes service network'),
('trusted_users', '127.0.0.1/32', 'allow', 'Localhost');

Integration:

  • FreeSWITCH calls GET /v1/freeswitch/acl via mod_xml_curl
  • API queries acl_entries table and generates XML response
  • reloadacl command forces FreeSWITCH to reload ACL configuration

Entity-Relationship Diagram​

Connection Pooling Architecture​

The API uses asyncpg for high-performance PostgreSQL connections.

Connection Management​

Code Example:

import asyncpg
from config import get_settings

settings = get_settings()

async def get_db_connection():
    """
    Returns an async connection to PostgreSQL.
    DSN format: postgresql+asyncpg://user:pass@host:port/dbname
    """
    dsn = settings.DB_DSN.replace("postgresql+asyncpg://", "postgresql://")
    return await asyncpg.connect(dsn)

async def close_db_connection(db):
    """Closes the database connection."""
    if db:
        await db.close()

Usage Pattern:

async def list_extensions():
    conn = await get_db_connection()
    try:
        rows = await conn.fetch("SELECT * FROM extensions WHERE status = 'active'")
        return [dict(row) for row in rows]
    finally:
        await conn.close()

Configuration​

Environment Variables:

DB_DSN=postgresql+asyncpg://callcenter_user:password@postgres.client-demo-client.svc.cluster.local:5432/callcenter
DB_TYPE=postgresql  # or sqlite (legacy)
DB_HOST=postgres.client-demo-client.svc.cluster.local
DB_PORT=5432
DB_NAME=callcenter
DB_USER=callcenter_user
DB_PASS=your_secure_password

Connection Pooling (Future Enhancement):

# TODO: Implement connection pooling with asyncpg.create_pool()
pool = await asyncpg.create_pool(
    dsn=settings.DB_DSN,
    min_size=10,
    max_size=50,
    command_timeout=60
)

ODBC Integration​

FreeSWITCH uses ODBC (unixODBC + PostgreSQL driver) to access the database for:

  • mod_callcenter state (agents, tiers, members)
  • CDR logging (mod_odbc_cdr)
  • Core state (channels, calls, registrations)

ODBC Configuration​

File: /etc/odbc.ini

[callcenter]
Driver = PostgreSQL Unicode
Servername = postgres.demo-client.svc.cluster.local
Port = 5432
Database = callcenter
Username = callcenter_user
Password = callcenter_pass_demo-client
Protocol = 8.0
ReadOnly = no

FreeSWITCH Configuration:

<!-- autoload_configs/switch.conf.xml -->
<configuration name="switch.conf" description="Core Configuration">
  <settings>
    <param name="core-db-dsn" value="odbc://callcenter"/>
  </settings>
</configuration>

<!-- autoload_configs/callcenter.conf.xml -->
<configuration name="callcenter.conf">
  <settings>
    <param name="odbc-dsn" value="callcenter:callcenter_user:password"/>
  </settings>
</configuration>

<!-- autoload_configs/odbc_cdr.conf.xml -->
<configuration name="odbc_cdr.conf">
  <settings>
    <param name="odbc-dsn" value="callcenter"/>
    <param name="log-leg" value="a-leg"/>
  </settings>
  <tables>
    <table name="cdr" log-leg="a-leg">
      <field name="uuid" chan-var-name="uuid"/>
      <field name="caller_id_name" chan-var-name="caller_id_name"/>
      <field name="caller_id_number" chan-var-name="caller_id_number"/>
      <!-- ... -->
    </table>
  </tables>
</configuration>

ODBC Environment Variables​

Set in FreeSWITCH container:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

# Generated dynamically by custom-entrypoint.sh
CALLCENTER_ODBC_DSN=callcenter
POSTGRES_HOST=postgres.${NAMESPACE}.svc.cluster.local
POSTGRES_PORT=5432
POSTGRES_DB=callcenter
POSTGRES_USER=callcenter_user
POSTGRES_PASS=callcenter_pass_${NAMESPACE}

Testing ODBC Connectivity​

Script: freeswitch-queue/scripts/test-odbc.sh

#!/bin/bash
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

echo "=== ODBC Diagnostics ==="
echo "1. Environment:"
echo "   ODBCINI=${ODBCINI}"
echo "   ODBCSYSINI=${ODBCSYSINI}"

echo "2. ODBC Driver Manager:"
odbcinst -j

echo "3. Installed Drivers:"
odbcinst -q -d

echo "4. Configured DSNs:"
odbcinst -q -s

echo "5. Test Connection:"
isql -v callcenter

Migration Strategy​

Initial Schema Setup​

1. Helm Chart Initialization: The Helm chart includes an init-schema.sql file that runs on first deployment:

# charts/tenant-infra/templates/init-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: {{ .Release.Name }}-init-schema
spec:
  template:
    spec:
      containers:
      - name: init-schema
        image: postgres:15
        command:
        - /bin/sh
        - -c
        - |
          psql $DB_DSN < /init-schema.sql
        env:
        - name: DB_DSN
          value: postgresql://callcenter_user:password@postgres:5432/callcenter
        volumeMounts:
        - name: init-schema
          mountPath: /init-schema.sql
          subPath: init-schema.sql
      volumes:
      - name: init-schema
        configMap:
          name: {{ .Release.Name }}-init-schema

2. Python Migration Script:

python database/migrate.py

Output:

================================================================================
FreeSWITCH PostgreSQL Migration
================================================================================
✓ Connected to database
[1] Applying FreeSWITCH core schema...
✓ FreeSWITCH core tables created/verified
[2] Applying CDR schema...
✓ CDR table created/verified
[3] Applying mod_callcenter schema...
✓ mod_callcenter tables created/verified
[4] Applying IVR XML-CURL schema...
✓ IVR XML-CURL tables created/verified
[5] Verifying mod_callcenter tables...
✓ mod_callcenter tables exist: agents, tiers, members
[6] Database summary:
Total tables: 23
  - acl_entries: 16 kB
  - agents: 8 kB
  - calls: 8 kB
  - cdr: 8 kB
  - channels: 8 kB
  - extensions: 16 kB
  - ivr_menus: 8 kB
  - ivr_menu_options: 8 kB
  - ivr_tts_cache: 8 kB
  - ivrs: 8 kB
  - members: 8 kB
  - registrations: 8 kB
  - tiers: 8 kB
================================================================================
✓ Migration completed successfully!
================================================================================

Schema Versioning​

Approach: SQL files in database/schema/ with semantic naming:

database/schema/
├── extensions.sql           # SIP extensions
├── freeswitch_core.sql      # FreeSWITCH core tables
├── mod_callcenter.sql       # Call center tables
├── cdr.sql                  # CDR table
└── 006_ivr_xmlcurl.sql      # IVR system (versioned)

Migration Script:

# database/migrate.py
async def run_migration():
    schema_dir = Path(__file__).parent / "schema"
    
    # 1. FreeSWITCH core schema
    core_schema = (schema_dir / "freeswitch_core.sql").read_text()
    await conn.execute(core_schema)
    
    # 2. CDR schema
    cdr_schema = (schema_dir / "cdr.sql").read_text()
    await conn.execute(cdr_schema)
    
    # 3. mod_callcenter schema
    callcenter_schema = (schema_dir / "mod_callcenter.sql").read_text()
    await conn.execute(callcenter_schema)
    
    # 4. IVR XML-CURL schema
    ivr_schema = (schema_dir / "006_ivr_xmlcurl.sql").read_text()
    await conn.execute(ivr_schema)

Idempotent Migrations​

All SQL files use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS to ensure idempotency:

-- Safe to run multiple times
CREATE TABLE IF NOT EXISTS extensions (...);
CREATE INDEX IF NOT EXISTS idx_extensions_status ON extensions(status);

-- Upsert for seed data
INSERT INTO acl_entries (list_name, cidr, permission, description)
VALUES ('trusted_users', '10.42.0.0/16', 'allow', 'Kubernetes pod network')
ON CONFLICT (list_name, cidr) DO NOTHING;

Index Strategy​

Query Performance Optimization​

Principle: Index all foreign keys, status fields, and frequent query columns.

Index Categories:

Index TypePurposeExample
Primary KeyUnique row identificationextensions(number)
Foreign KeyJoin performanceivr_menus(ivr_name)
Status/StateFiltering active/pending recordsextensions(status), agents(state)
TimestampRange queries (CDR, logs)cdr(start_stamp)
Unique ConstraintData integrityivr_tts_cache(text_hash)
CompositeMulti-column queriesregistrations(reg_user, realm, hostname)

Index Analysis​

FreeSWITCH Core Tables:

-- channels: 3 indexes
CREATE INDEX chidx1 ON channels(hostname);              -- Filter by FreeSWITCH instance
CREATE INDEX uuindex ON channels(uuid, hostname);       -- Channel lookup (composite)
CREATE INDEX uuindex2 ON channels(call_uuid);           -- Call correlation

-- calls: 4 indexes
CREATE INDEX callsidx1 ON calls(hostname);              -- Filter by instance
CREATE INDEX eruuindex ON calls(caller_uuid, hostname); -- Caller lookup
CREATE INDEX eeuuindex ON calls(callee_uuid);           -- Callee lookup
CREATE INDEX eeuuindex2 ON calls(call_uuid);            -- Call UUID lookup

-- registrations: 1 composite index
CREATE INDEX regindex1 ON registrations(reg_user, realm, hostname);

Call Center Tables:

-- agents: 4 indexes
CREATE INDEX idx_agents_name ON agents(name);          -- Agent lookup
CREATE INDEX idx_agents_uuid ON agents(uuid);          -- UUID correlation
CREATE INDEX idx_agents_status ON agents(status);      -- Filter available agents
CREATE INDEX idx_agents_state ON agents(state);        -- Filter agent state

-- tiers: 3 indexes
CREATE INDEX idx_tiers_queue ON tiers(queue);          -- Queue lookup
CREATE INDEX idx_tiers_agent ON tiers(agent);          -- Agent lookup
CREATE INDEX idx_tiers_state ON tiers(state);          -- Filter ready tiers

-- members: 4 indexes
CREATE INDEX idx_members_queue ON members(queue);            -- Queue lookup
CREATE INDEX idx_members_uuid ON members(uuid);              -- Member lookup
CREATE INDEX idx_members_session_uuid ON members(session_uuid); -- Session correlation
CREATE INDEX idx_members_state ON members(state);            -- Filter member state

IVR Tables:

-- ivrs: 2 indexes
CREATE INDEX idx_ivrs_status ON ivrs(status);          -- Filter running/stopped IVRs

-- ivr_menus: 2 indexes
CREATE INDEX idx_ivr_menus_ivr_name ON ivr_menus(ivr_name);   -- Filter by IVR
CREATE INDEX idx_ivr_menus_menu_id ON ivr_menus(menu_id);     -- Menu lookup

-- ivr_menu_options: 1 index
CREATE INDEX idx_ivr_menu_options_menu_id ON ivr_menu_options(menu_id);

-- ivr_tts_cache: 1 index
CREATE INDEX idx_ivr_tts_cache_hash ON ivr_tts_cache(text_hash); -- Cache lookup

CDR Table:

CREATE INDEX idx_cdr_queue ON cdr(queue_name);         -- Queue analytics
CREATE INDEX idx_cdr_start ON cdr(start_stamp);        -- Time-based queries
CREATE INDEX idx_cdr_hangup ON cdr(hangup_cause);      -- Failure analysis
CREATE INDEX idx_cdr_switch ON cdr(switch_name);       -- Node filtering

Index Maintenance​

Check Index Usage:

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Unused Indexes:

SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public';

Performance Considerations​

Database vs XML-RPC Performance​

Benchmark Results:

OperationXML-RPCDatabaseSpeedup
Get all channels500-1000ms10-20ms50x faster
Get channel by UUID100-200ms8-10ms20x faster
Get call count150-300ms5-10ms30x faster
List registrations200-400ms15-25ms20x faster

Code Example:

# SLOW: XML-RPC (500-1000ms)
channels = await xml_rpc_client.call("show", "channels")

# FAST: Direct database query (10-20ms)
from adapters.database import get_channels
channels = await get_channels()

Query Optimization​

1. Use Indexes:

# ✓ GOOD: Uses idx_extensions_status
await conn.fetch("SELECT * FROM extensions WHERE status = 'active'")

# ✗ BAD: Full table scan
await conn.fetch("SELECT * FROM extensions WHERE LOWER(status) = 'active'")

2. Limit Result Sets:

# ✓ GOOD: Paginated query
await conn.fetch("SELECT * FROM cdr ORDER BY start_stamp DESC LIMIT 100 OFFSET 0")

# ✗ BAD: Unbounded query
await conn.fetch("SELECT * FROM cdr")

3. Use Prepared Statements:

# asyncpg automatically uses prepared statements with $1, $2 placeholders
await conn.fetchrow("SELECT * FROM channels WHERE uuid = $1", channel_uuid)

Connection Pooling (Future)​

Current: Single connection per request
Future: Connection pooling with asyncpg.create_pool()

# Future implementation
async def init_db_pool():
    global pool
    pool = await asyncpg.create_pool(
        dsn=settings.DB_DSN,
        min_size=10,
        max_size=50,
        command_timeout=60
    )

async def get_db_connection():
    async with pool.acquire() as conn:
        yield conn

Architecture Decision: PostgreSQL vs Redis​

Why PostgreSQL?​

RequirementPostgreSQLRedis
ACID Compliance✅ Full ACID⚠️ Eventual consistency
Complex Queries✅ SQL, JOINs, aggregations❌ Limited
Data Integrity✅ Foreign keys, constraints❌ Application-enforced
Audit Trail✅ Timestamps, triggers⚠️ Manual
FreeSWITCH Integration✅ Native ODBC support❌ Requires mod_redis
Backup/Recovery✅ pg_dump, WAL archiving⚠️ RDB/AOF
High Availability✅ Streaming replication✅ Sentinel/Cluster
Cost✅ Included in cluster💰 Additional service

Redis Use Cases (Future)​

Redis remains valuable for:

  • Session caching (temporary data, TTL expiration)
  • Rate limiting (high-frequency writes)
  • Pub/Sub (real-time notifications)
  • Hot path caching (frequent reads, infrequent writes)

Hybrid Architecture (Future):

PostgreSQL: Single source of truth (write-through)
Redis: Read cache (invalidation via PostgreSQL triggers)

Database Backup & Recovery​

Backup Strategy​

1. Continuous WAL Archiving:

# PostgreSQL configuration
archive_mode = on
archive_command = 'aws s3 cp %p s3://backups/wal/%f'

2. Daily Full Backups:

# Cron job (daily at 2 AM)
0 2 * * * pg_dump -U callcenter_user -d callcenter | gzip > /backups/callcenter_$(date +\%Y\%m\%d).sql.gz

3. Point-in-Time Recovery (PITR):

# Restore to specific timestamp
pg_restore --time '2025-10-14 12:30:00' /backups/callcenter_20251014.sql.gz

Kubernetes Backup (Helm Chart)​

CronJob Example:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-backup
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: pg-backup
            image: postgres:15
            command:
            - /bin/sh
            - -c
            - |
              pg_dump -U callcenter_user -d callcenter | gzip > /backups/callcenter_$(date +%Y%m%d).sql.gz
              aws s3 cp /backups/callcenter_$(date +%Y%m%d).sql.gz s3://backups/
            env:
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: password
            volumeMounts:
            - name: backups
              mountPath: /backups
          volumes:
          - name: backups
            emptyDir: {}

Security Considerations​

1. Password Storage​

Extensions Table:

  • SIP passwords stored in plaintext (required by FreeSWITCH)
  • Use strong passwords (20+ characters)
  • Rotate passwords via API (PUT /v1/extensions/{number})

IVR OpenAI Keys:

  • Encrypt at rest (PostgreSQL column encryption)
  • Use Kubernetes secrets for environment variables
  • Rotate keys regularly

2. Database Access Control​

User Permissions:

-- API user (full access)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO callcenter_user;

-- FreeSWITCH user (ODBC access)
GRANT SELECT, INSERT, UPDATE, DELETE ON agents, tiers, members TO freeswitch_user;
GRANT INSERT ON cdr TO freeswitch_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON channels, calls, registrations TO freeswitch_user;

-- Read-only analytics user
GRANT SELECT ON cdr, calls, channels TO analytics_user;

3. Network Security​

PostgreSQL Configuration:

# pg_hba.conf
host    callcenter    callcenter_user    10.42.0.0/16    md5  # Kubernetes pods
host    callcenter    callcenter_user    10.43.0.0/16    md5  # Kubernetes services
host    all           all                0.0.0.0/0       reject  # Deny all others

Kubernetes Network Policies:

apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: postgres-network-policy
spec:
  podSelector:
    matchLabels:
      app: postgres
  policyTypes:
  - Ingress
  ingress:
  - from:
    - podSelector:
        matchLabels:
          app: api
    - podSelector:
        matchLabels:
          app: freeswitch-queue
    ports:
    - protocol: TCP
      port: 5432

Monitoring & Debugging​

Database Metrics​

PostgreSQL pg_stat Views:

-- Connection count
SELECT count(*) FROM pg_stat_activity;

-- Active queries
SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'active';

-- Table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

ODBC Debugging​

Enable ODBC Tracing:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export ODBCDEBUG=1
export ODBCDEBUGFILE=/tmp/odbc.log

# Test connection
isql -v callcenter

# View logs
tail -f /tmp/odbc.log

FreeSWITCH ODBC Logs:

# Enable debug logging
fs_cli -x "console loglevel debug"
fs_cli -x "log 7"

# Test ODBC query
fs_cli -x "sql select * from agents limit 5"

API Query Logging​

Enable SQL Query Logging:

import logging

logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger("asyncpg")
logger.setLevel(logging.DEBUG)

Output:

DEBUG:asyncpg:SELECT * FROM extensions WHERE number = '1001'
DEBUG:asyncpg:Query executed in 8.2ms

Quick Reference​

Common Queries​

Extensions:

-- List active extensions
SELECT number, display_name, email FROM extensions WHERE status = 'active';

-- Create extension
INSERT INTO extensions (number, password, display_name, status)
VALUES ('1001', 'Winnipeg2025', 'John Doe', 'active');

-- Update extension
UPDATE extensions SET display_name = 'Jane Doe' WHERE number = '1001';

Agents:

-- List available agents
SELECT name, contact, status, state FROM agents WHERE status = 'Available';

-- Update agent status
UPDATE agents SET status = 'On Break' WHERE name = 'agent-10720';

Channels:

-- Count active channels
SELECT COUNT(*) FROM channels;

-- List channels by queue
SELECT uuid, cid_name, cid_num, state FROM channels WHERE context = 'queue-sales';

CDR:

-- Calls in last 24 hours
SELECT * FROM cdr WHERE start_stamp > NOW() - INTERVAL '24 hours' ORDER BY start_stamp DESC;

-- Average call duration by queue
SELECT queue_name, AVG(duration) as avg_duration FROM cdr GROUP BY queue_name;

Next Steps​

  • ✅ Schema deployed - All tables created via Helm chart
  • ✅ ODBC configured - FreeSWITCH connected to PostgreSQL
  • ✅ API integrated - asyncpg connections working
  • 🚧 Connection pooling - Implement asyncpg.create_pool()
  • 🚧 Monitoring - Add Prometheus metrics for query performance
  • 🚧 Backup automation - Scheduled WAL archiving and full backups