Phone Number Tables

agency_phone_numbers

Purchased Telnyx phone numbers assigned to agencies. Each number can optionally be assigned to an agent mapping.

Column	Type	Default	Nullable	Description
`id`	`uuid`	`uuid_generate_v4()`	no	Primary key
`agency_id`	`uuid`	—	no	FK → `agencies.id`
`phone_number`	`varchar(20)`	—	no	E.164 phone number (unique)
`phone_number_id`	`varchar(255)`	—	yes	Telnyx phone number resource ID
`friendly_name`	`varchar(255)`	—	yes	Human-readable label
`number_type`	`varchar(50)`	—	yes	Type of number (e.g., `local`, `toll-free`)
`country_code`	`varchar(5)`	—	yes	ISO country code
`voice_enabled`	`boolean`	`true`	no	Whether voice calling is enabled
`sms_enabled`	`boolean`	`false`	no	Whether SMS is enabled
`mms_enabled`	`boolean`	`false`	no	Whether MMS is enabled
`agent_mapping_id`	`uuid`	—	yes	FK → `agent_mappings.id`
`direction`	`call_direction`	`'inbound'`	yes	Default call direction
`monthly_cost`	`numeric(10,4)`	—	yes	Monthly recurring cost
`setup_cost`	`numeric(10,4)`	—	yes	One-time purchase cost
`currency`	`varchar(3)`	`'USD'`	no	Cost currency code
`status`	`phone_number_status`	`'pending'`	no	Lifecycle status
`is_active`	`boolean`	`true`	no	Soft-delete flag
`purchased_at`	`timestamptz`	—	yes	When the number was purchased
`released_at`	`timestamptz`	—	yes	When the number was released
`created_at`	`timestamptz`	`now()`	no	Record creation timestamp
`updated_at`	`timestamptz`	`now()`	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
`agency_phone_numbers_pkey`	Primary key	`id`
`agency_phone_numbers_phone_unique`	Unique	`phone_number`
`agency_phone_numbers_agency_id_fkey`	Foreign key	`agency_id` → `agencies(id)` ON DELETE CASCADE
`agency_phone_numbers_agent_mapping_id_fkey`	Foreign key	`agent_mapping_id` → `agent_mappings(id)` ON DELETE SET NULL
`agency_phone_numbers_phone_format`	Check	`phone_number ~ '^\+[1-9][0-9]{6,14}$'` (E.164 format)

Indexes:

Index	Columns	Notes
`idx_agency_phones_agency_id`	`agency_id`	Agency-scoped queries
`idx_agency_phones_phone_number`	`phone_number`	Lookup by number
`idx_agency_phones_agent_mapping`	`agent_mapping_id`	Filtered: `WHERE agent_mapping_id IS NOT NULL`
`idx_agency_phones_status`	`status`	Filter by lifecycle status
`idx_agency_phones_active`	`(agency_id, is_active)`	Filtered: `WHERE is_active = true`

Triggers:

Trigger	Event	Function
`update_agency_phone_numbers_updated_at`	BEFORE UPDATE	`update_updated_at_column()`

Views: v_agency_phone_numbers_with_agent joins this table with agent_mappings to include agent name, voice, and language hint.