# SIP Trunk Database Mapping Audit Report
**Generated:** 2026-06-02  
**Purpose:** Phase 1 - Database Mapping Audit for Enterprise SIP Trunk Management

---

## Executive Summary

This report documents the mapping between GUI fields, database columns, and PJSIP realtime objects for the SIP Trunk Management module.

**Status:** IN PROGRESS  
**Coverage:** Partial - Form fields reviewed, controller logic partially audited

---

## Database Schema Overview

### Management Layer Tables

#### `sip_trunks` (51 columns)
Primary management table for trunk configuration. Stores high-level trunk settings.

#### `sip_trunk_meta` (metadata table)
Stores trunk status (enabled/disabled) and audit information.

### PJSIP Realtime Tables

#### `ps_endpoints` (73 columns)
Core endpoint configuration for SIP trunks.

#### `ps_auths` (7 columns)
Authentication credentials.

#### `ps_aors` (12 columns)
Address of Record configuration.

#### `ps_registrations` (18 columns)
Outbound registration configuration.

---

## Field Mapping Report

### Overview Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Trunk Name | `description` | ps_endpoints | `$endpoint->description` | `$request->description` | ✓ OK |
| Trunk ID | `id` | ps_endpoints | `$endpoint->id` | `$request->id` | ✓ OK |
| Trunk ID | `name` | sip_trunks | `$trunk->name` | `$trunkId` | ✓ OK |
| Outbound Caller ID | `from_user` | ps_endpoints | `$endpoint->from_user` | `$request->from_user` | ✓ OK |
| Outbound Caller ID | `from_user` | sip_trunks | `$trunk->from_user` | `$request->from_user` | ✓ OK |
| Domain | `from_domain` | ps_endpoints | `$endpoint->from_domain` | `$request->from_domain` | ✓ OK |
| Domain | `from_domain` | sip_trunks | `$trunk->from_domain` | `$request->from_domain` | ✓ OK |
| Emergency Caller ID | `emergency_cid` | ps_endpoints | `$endpoint->emergency_cid` | `$request->emergency_cid` | ⚠️ NOT IN SCHEMA |
| Transport | `transport` | ps_endpoints | `$endpoint->transport` | `$transport` | ✓ OK |
| Transport | `transport` | sip_trunks | `$trunk->transport` | `strtoupper(str_replace('transport-', '', $transport))` | ✓ OK |
| Notes | `notes` | sip_trunks | `old('notes')` | `$request->notes` | ✓ OK |
| Force Outbound CLI | `force_outbound_cli` | ps_endpoints | `$endpoint->force_outbound_cli` | `$request->force_outbound_cli` | ✓ OK |
| Enable Trunk | `disable_trunk` | sip_trunks | `!$trunk->disable_trunk` | NOT MAPPED | ⚠️ NOT SAVED |

### Authentication Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Username | `username` | ps_auths | `$auth->username` | `$request->username` | ✓ OK |
| Username | `username` | sip_trunks | `$trunk->username` | `$request->username` | ✓ OK |
| Password | `password` | ps_auths | `$auth->password` | `$request->secret` | ✓ OK |
| Password | `password` | sip_trunks | `$trunk->password` | `$request->secret` | ✓ OK |
| Auth Type | `auth_type` | ps_auths | `$auth->auth_type` | `$request->auth_type` | ✓ OK |
| Auth Type | `auth_type` | sip_trunks | `$trunk->auth_type` | NOT MAPPED | ⚠️ NOT SAVED |
| Realm | `realm` | ps_auths | `$auth->realm` | `$request->realm` | ✓ OK |
| MD5 Credential | `md5_cred` | ps_auths | `$auth->md5_cred` | NOT MAPPED | ⚠️ NOT SAVED |
| Nonce Lifetime | `nonce_lifetime` | ps_auths | `$auth->nonce_lifetime` | NOT MAPPED | ⚠️ NOT SAVED |

### Registration Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Server URI | `server_uri` | ps_registrations | `$reg->server_uri` | `$regServerUri` | ✓ OK |
| Server URI | `server_uri` | sip_trunks | `$trunk->server_uri` | `$request->reg_server_uri` | ✓ OK |
| Client URI | `client_uri` | ps_registrations | `$reg->client_uri` | `$regClientUri` | ✓ OK |
| Client URI | `client_uri` | sip_trunks | `$trunk->client_uri` | `$request->reg_client_uri` | ✓ OK |
| Contact User | `contact_user` | ps_registrations | `$reg->contact_user` | `$request->reg_contact_user` | ✓ OK |
| Contact User | `contact_user` | sip_trunks | `$trunk->contact_user` | `$request->reg_contact_user` | ✓ OK |
| Retry Interval | `retry_interval` | ps_registrations | `$reg->retry_interval` | `$request->retry_interval` | ✓ OK |
| Expiration | `expiration` | ps_registrations | `$reg->expiration` | `$request->expiration` | ✓ OK |
| Max Retries | `max_retries` | ps_registrations | `$reg->max_retries` | `$request->max_retries` | ✓ OK |
| Forbidden Retry Interval | `forbidden_retry_interval` | ps_registrations | `$reg->forbidden_retry_interval` | `$request->forbidden_retry_interval` | ✓ OK |
| Auth Rejection Permanent | `auth_rejection_permanent` | ps_registrations | `$reg->auth_rejection_permanent` | `$request->auth_rejection_permanent` | ✓ OK |
| Line Support | `line` | ps_registrations | `$reg->line` | `$request->line` | ✓ OK |
| Line Endpoint | `endpoint` | ps_registrations | `$reg->endpoint` | `$request->endpoint` | ✓ OK |
| Support Outbound | `support_outbound` | ps_registrations | `$reg->support_outbound` | `$request->support_outbound` | ✓ OK |
| Support Path | `support_path` | ps_registrations | `$reg->support_path` | `$request->support_path` | ✓ OK |
| Outbound Auth | `outbound_auth` | ps_registrations | `$reg->outbound_auth` | `$request->outbound_auth` | ✓ OK |
| Outbound Proxy | `outbound_proxy` | ps_registrations | `$reg->outbound_proxy` | `$request->outbound_proxy` | ✓ OK |
| Auth | `auth` | ps_registrations | `$reg->auth` | `$request->auth` | ✓ OK |

### Connectivity Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Transport | `transport` | ps_endpoints | `$endpoint->transport` | `$transport` | ✓ OK |
| Outbound Proxy | `outbound_proxy` | ps_endpoints | `$endpoint->outbound_proxy` | `$request->outbound_proxy` | ✓ OK |
| Contact URI | `contact` | ps_aors | `$aor->contact` | `$request->contact` | ✓ OK |
| Max Contacts | `max_contacts` | ps_aors | `$aor->max_contacts` | `$request->max_contacts` | ✓ OK |
| Max Contacts | `max_contacts` | sip_trunks | `$trunk->max_contacts` | NOT MAPPED | ⚠️ NOT SAVED |
| Qualify Frequency | `qualify_frequency` | ps_aors | `$aor->qualify_frequency` | `$request->qualify_frequency` | ✓ OK |
| Qualify Timeout | `qualify_timeout` | ps_aors | `$aor->qualify_timeout` | NOT MAPPED | ⚠️ NOT IN SCHEMA |
| Remove Existing | `remove_existing` | ps_aors | `$aor->remove_existing` | `$formatBoolean(true, ...)` | ✓ OK |
| Support Path | `support_path` | ps_aors | `$aor->support_path` | `$request->support_path` | ✓ OK |

### Caller ID Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Caller ID Number | `callerid` | ps_endpoints | `$endpoint->callerid` | `$request->callerid` | ✓ OK |
| Caller ID Name | `callerid_name` | ps_endpoints | NOT MAPPED | NOT MAPPED | ⚠️ NOT IN SCHEMA |
| Force Outbound CLI | `force_outbound_cli` | ps_endpoints | `$endpoint->force_outbound_cli` | `$request->force_outbound_cli` | ✓ OK |
| Trust ID Outbound | `trust_id_outbound` | ps_endpoints | `$endpoint->trust_id_outbound` | `$request->trust_id_outbound` | ✓ OK |
| Trust ID Inbound | `trust_id_inbound` | ps_endpoints | `$endpoint->trust_id_inbound` | `$request->trust_id_inbound` | ✓ OK |
| Send PAI | `send_pai` | ps_endpoints | `$endpoint->send_pai` | `$request->send_pai` | ✓ OK |
| Send RPID | `send_rpid` | ps_endpoints | `$endpoint->send_rpid` | `$request->send_rpid` | ✓ OK |
| User EQ Phone | `user_eq_phone` | ps_endpoints | `$endpoint->user_eq_phone` | `$request->user_eq_phone` | ✓ OK |

### Media Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Codecs | `allow` | ps_endpoints | `$endpoint->allow` | `$allowStr` | ✓ OK |
| Codecs | `codecs` | sip_trunks | `$trunk->codecs` | NOT MAPPED | ⚠️ NOT SAVED |
| DTMF Mode | `dtmf_mode` | ps_endpoints | `$endpoint->dtmf_mode` | `$request->dtmf_mode` | ✓ OK |
| DTMF Mode | `dtmf_mode` | sip_trunks | `$trunk->dtmf_mode` | NOT MAPPED | ⚠️ NOT SAVED |
| Direct Media | `direct_media` | ps_endpoints | `$endpoint->direct_media` | `$request->direct_media` | ✓ OK |
| RTP Symmetric | `rtp_symmetric` | ps_endpoints | `$endpoint->rtp_symmetric` | `$request->rtp_symmetric` | ✓ OK |
| RTP Symmetric | `rtp_symmetric` | sip_trunks | `$trunk->rtp_symmetric` | NOT MAPPED | ⚠️ NOT SAVED |
| Rewrite Contact | `rewrite_contact` | ps_endpoints | `$endpoint->rewrite_contact` | `$request->rewrite_contact` | ✓ OK |
| Rewrite Contact | `rewrite_contact` | sip_trunks | `$trunk->rewrite_contact` | NOT MAPPED | ⚠️ NOT SAVED |
| Force RPort | `force_rport` | ps_endpoints | `$endpoint->force_rport` | `$request->force_rport` | ✓ OK |
| Preferred Codec Only | `preferred_codec_only` | ps_endpoints | `$endpoint->preferred_codec_only` | `$request->preferred_codec_only` | ✓ OK |
| RTCP Mux | `rtcp_mux` | ps_endpoints | `$endpoint->rtcp_mux` | `$request->rtcp_mux` | ✓ OK |
| RTP Timeout | `rtp_timeout` | ps_endpoints | `$endpoint->rtp_timeout` | `$request->rtp_timeout` | ✓ OK |
| RTP Timeout Hold | `rtp_timeout_hold` | ps_endpoints | `$endpoint->rtp_timeout_hold` | `$request->rtp_timeout_hold` | ✓ OK |
| RTP Keepalive | `rtp_keepalive` | ps_endpoints | `$endpoint->rtp_keepalive` | NOT MAPPED | ⚠️ NOT SAVED |
| Media Encryption | `media_encryption` | ps_endpoints | `$endpoint->media_encryption` | `$request->media_encryption` | ✓ OK |

### Signalling Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Timers | `timers` | ps_endpoints | `$endpoint->timers` | `$request->timers` | ✓ OK |
| Timers Min SE | `timers_min_se` | ps_endpoints | `$endpoint->timers_min_se` | `$request->timers_min_se` | ✓ OK |
| Timers Session Expires | `timers_sess_expires` | ps_endpoints | `$endpoint->timers_sess_expires` | `$request->timers_sess_expires` | ✓ OK |
| 100rel | `100rel` | ps_endpoints | `$endpoint->{'100rel'}` | NOT MAPPED | ⚠️ NOT SAVED |
| Connected Line Method | `connected_line_method` | ps_endpoints | `$endpoint->connected_line_method` | NOT MAPPED | ⚠️ NOT SAVED |

### Security Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Identify By | `identify_by` | ps_endpoints | `$endpoint->identify_by` | `$request->identify_by` | ✓ OK |
| ACL | `acl` | ps_endpoints | `$endpoint->acl` | NOT MAPPED | ⚠️ NOT SAVED |
| Contact ACL | `contact_acl` | ps_endpoints | `$endpoint->contact_acl` | NOT MAPPED | ⚠️ NOT SAVED |
| Allow Unauthenticated Options | `allow_unauthenticated_options` | ps_endpoints | `$endpoint->allow_unauthenticated_options` | NOT MAPPED | ⚠️ NOT SAVED |

### Advanced Tab

| GUI Field | Database Column | Target Table | Read Logic | Write Logic | Status |
|-----------|-----------------|--------------|------------|-------------|--------|
| Context | `context` | ps_endpoints | `$endpoint->context` | `$request->context` | ✓ OK |
| Language | `language` | ps_endpoints | `$endpoint->language` | NOT MAPPED | ⚠️ NOT SAVED |
| Account Code | `accountcode` | ps_endpoints | `$endpoint->accountcode` | NOT MAPPED | ⚠️ NOT SAVED |
| Mailboxes | `mailboxes` | ps_endpoints | `$endpoint->mailboxes` | NOT MAPPED | ⚠️ NOT SAVED |
| Voicemail Extension | `voicemail_extension` | ps_endpoints | `$endpoint->voicemail_extension` | NOT MAPPED | ⚠️ NOT SAVED |
| TOS Audio | `tos_audio` | ps_endpoints | `$endpoint->tos_audio` | NOT MAPPED | ⚠️ NOT SAVED |
| TOS Video | `tos_video` | ps_endpoints | `$endpoint->tos_video` | NOT MAPPED | ⚠️ NOT SAVED |
| COS Audio | `cos_audio` | ps_endpoints | `$endpoint->cos_audio` | NOT MAPPED | ⚠️ NOT SAVED |
| COS Video | `cos_video` | ps_endpoints | `$endpoint->cos_video` | NOT MAPPED | ⚠️ NOT SAVED |

---

## Critical Issues Found

### 🔴 Critical - Data Loss Risk

1. **Enable Trunk toggle** - GUI field exists but not saved to database
   - Field: `enabled` checkbox
   - Expected: `sip_trunks.disable_trunk` or `sip_trunk_meta.status`
   - Current: NOT SAVED
   - Impact: Trunk enable/disable state not persisted

### 🟡 High Priority - Missing Mappings

2. **Emergency Caller ID** - Field exists but column not in schema
   - Field: `emergency_cid`
   - Expected: `ps_endpoints.emergency_cid`
   - Current: Column does not exist in ps_endpoints
   - Impact: Emergency CLI cannot be configured

3. **Caller ID Name** - Field exists but column not in schema
   - Field: `callerid_name`
   - Expected: `ps_endpoints.callerid_name` or separate column
   - Current: Column does not exist
   - Impact: Caller ID name cannot be set separately from number

4. **Qualify Timeout** - Field exists but column not in schema
   - Field: `qualify_timeout`
   - Expected: `ps_aors.qualify_timeout`
   - Current: Column does not exist in ps_aors
   - Impact: Cannot configure OPTIONS timeout

### 🟡 Medium Priority - sip_trunks Fields Not Saved

The following fields exist in `sip_trunks` table but are not saved from the GUI:

- `auth_type`
- `max_contacts`
- `codecs`
- `dtmf_mode`
- `rtp_symmetric`
- `rewrite_contact`
- `nat`
- `sip_debug`
- `insecure`
- `class_of_service`
- `ring_time`
- `dial_profile`
- `profile`
- `music_on_hold`
- `get_did_from`
- `get_cid_from`
- `trunk_cid_name`
- `trunk_cid_number`
- `overwrite_cid`
- `continue_on_busy`
- `disable_hangup_audios`
- `calls_recording`
- `contacts`
- `remote_username`
- `remote_secret`
- `contact_header`
- `permanent_auth_rejection`
- `max_retries`
- `forbidden_retry_interval`
- `dial_prefix`
- `dial_strip`
- `dial_prepend`

### 🟢 Low Priority - PJSIP Fields Not Exposed

The following PJSIP fields exist in schemas but have no GUI exposure:

**ps_endpoints:**
- `direct_media_method`
- `direct_media_glare_mitigation`
- `disable_direct_media_on_nat`
- `external_media_address`
- `ice_support`
- `mailboxes`
- `media_address`
- `rtp_ipv6`
- `send_diversion`
- `transport_method`
- `use_ptime`
- `named_call_group`
- `named_pickup_group`
- `allow_subscribe`
- `allow_transfer`
- `allow_overlap`
- `message_context`
- `aggregate_mwi`
- `mwi_from_user`
- `sdp_owner`
- `sdp_session`
- `record_on_feature`
- `record_off_feature`
- `fax_detect`
- `fax_detect_timeout`
- `g726_non_standard`
- `supress_moh_on_sendonly`
- `suppress_q850_reason_headers`
- `sub_min_expiry`
- `moh_suggest`
- `tone_zone`
- `device_state_busy_at`
- `follow_early_media_fork`
- `ignore_183_without_sdp`
- `incoming_call_offer_pref`
- `outgoing_call_offer_pref`
- `overlap_context`
- `redirect_method`
- `refer_blind_progress`
- `send_history_info`
- `send_connected_line`
- `trust_connected_line`
- `rpid_immediate`
- `max_audio_streams`
- `max_video_streams`
- `bundle`
- `webrtc`
- `stir_shaken`
- `stir_shaken_profile`
- `dtls_auto_generate_cert`
- `dtls_setup`
- `dtls_verify`
- `dtls_rekey`
- `dtls_fingerprint`
- `dtls_cert_file`
- `dtls_private_key`
- `dtls_ca_file`
- `dtls_ca_path`
- `dtls_cipher`
- `srtp_tag_32`
- `media_use_received_transport`
- `contact_acl`
- `allow_unauthenticated_options`
- `callerid_privacy`
- `callerid_tag`
- `call_group`
- `pickup_group`
- `department_id`
- `disallow`
- `max_audio_streams`
- `max_video_streams`
- `preferred_codec_only`
- `rtcp_mux`
- `webrtc`
- `dtls_auto_generate_cert`
- `avpf`
- `missed_call_email`
- `missed_call_notify`
- `require_pin`

**ps_aors:**
- `minimum_expiration`
- `maximum_expiration`
- `default_expiration`
- `outbound_proxy`
- `authenticate_qualify`
- `mailboxes`

**ps_registrations:**
- `transport`
- `authentication_realm`
- `security_negotiation`
- `contact_header_params`
- `fatal_retry_interval`

**ps_auths:**
- `nonce_lifetime`
- `md5_cred`

---

## Recommendations

### Immediate Actions (Phase 1)

1. **Fix Enable Trunk toggle** - Map to `sip_trunk_meta.status` or `sip_trunks.disable_trunk`
2. **Add missing columns** - Consider adding `emergency_cid`, `callerid_name`, `qualify_timeout` to schemas
3. **Save sip_trunks fields** - Add all sip_trunks columns to controller store/update methods

### Phase 2 Actions

1. **Implement password protection** - Ensure password never displays, only updates when new value entered
2. **Test save/load consistency** - Verify all fields persist and reload correctly

### Phase 3 Actions

1. **Expose critical PJSIP fields** - Add GUI for commonly used carrier settings
2. **Add digit manipulation** - Implement `dial_prefix`, `dial_strip`, `dial_prepend` in GUI

---

## Summary Statistics

- **Total GUI Fields Mapped:** ~80
- **Fields with Issues:** 45+
- **Critical Issues:** 1
- **High Priority Issues:** 3
- **Medium Priority Issues:** 26
- **Low Priority Issues:** 80+

**Overall Status:** ⚠️ REQUIRES ATTENTION - Multiple mapping gaps and missing save logic

---

*End of Phase 1 Report*