Views

The schema includes 3 database views that provide pre-joined data for frequent query patterns.

v_agency_phone_numbers_with_agent

Joins agency_phone_numbers with agent_mappings to provide phone number records enriched with the assigned agent's details.

SELECT
  pn.*,
  am.ultravox_agent_id,
  am.ultravox_agent_name,
  am.voice,
  am.language_hint
FROM agency_phone_numbers pn
LEFT JOIN agent_mappings am ON am.id = pn.agent_mapping_id;

Use case: Phone number list views that need to show the assigned agent's name and voice without a separate query.

Columns added:

Column	Type	Description
ultravox_agent_id	varchar(255)	The Ultravox agent ID (null if unassigned)
ultravox_agent_name	varchar(255)	The agent's display name
voice	varchar(100)	The agent's voice setting
language_hint	varchar(10)	The agent's language hint

v_call_batch_summary

Joins call_batches with agent_mappings and campaigns to provide batch records with agent name, campaign name, and computed progress.

SELECT
  cb.*,
  am.ultravox_agent_name AS agent_name,
  c.name AS campaign_name,
  ROUND(
    (cb.completed + cb.failed + cb.skipped)::numeric
    / NULLIF(cb.total_contacts, 0)::numeric * 100, 1
  ) AS progress_percent,
  cb.total_contacts - cb.completed - cb.failed - cb.skipped AS remaining
FROM call_batches cb
LEFT JOIN agent_mappings am ON am.id = cb.agent_mapping_id
LEFT JOIN campaigns c ON c.id = cb.campaign_id;

Use case: Batch monitoring dashboards that need progress tracking and human-readable names.

Columns added:

Column	Type	Description
agent_name	varchar(255)	The batch's agent display name
campaign_name	varchar(255)	The batch's campaign name
progress_percent	numeric	Percentage of contacts processed (0-100)
remaining	integer	Number of contacts not yet processed

virsyn_cron_jobs

Filters the cron.job system table to show only Virsyn-specific scheduled jobs.

SELECT jobid, jobname, schedule, command, nodename, nodeport, database, username, active
FROM cron.job
WHERE jobname LIKE 'virsyn_%'
ORDER BY jobname;

Use case: Monitoring which cron jobs are active and their schedules. Only shows jobs with the virsyn_ prefix.