Schema Overview

The Virsyn Voice AI database runs on PostgreSQL via Supabase. It contains 17 tables, 14 custom enum types, and 3 views, organized around agency-scoped multi-tenancy.

Entity Relationships

agencies (top-level tenant)
├── users (agency_owner, agency_admin, agency_member)
├── clients
│   └── campaigns (sdr, cs)
│       └── campaign_contacts
├── agent_mappings (links to Ultravox agents)
│   ├── agency_phone_numbers (assigned to agents)
│   └── call_batches (outbound batch operations)
├── agency_tools (synced from Ultravox)
├── calls (inbound/outbound call records)
│   └── enrichment_queue (transcript + analysis jobs)
├── webhook_queue (incoming Ultravox events)
├── webhook_logs (audit trail)
├── historical_sync_jobs (backfill operations)
├── call_stats_daily (aggregated analytics)
└── credentials (encrypted Ultravox + Telnyx keys)

ultravox_voices (global voice catalog, not agency-scoped)
app_config (global key-value settings)
cron_job_logs (cron execution audit)

Table Index

Table	Group	Purpose
agencies	Core	Top-level tenant with encrypted API credentials
users	Core	Platform users with role-based access
clients	Core	Client organizations within an agency
app_config	Core	Global application key-value config
agent_mappings	Agents	Maps Ultravox agents to local records with config
agency_tools	Agents	Tools available to agents during calls
ultravox_voices	Agents	Cached voice catalog from Ultravox
agency_phone_numbers	Phone Numbers	Purchased Telnyx phone numbers
calls	Calls	Individual call records with transcripts and analysis
call_stats_daily	Calls	Aggregated daily call statistics
call_batches	Calls	Outbound batch call operations
campaigns	Campaigns	SDR and CS campaign definitions
campaign_contacts	Campaigns	Contacts within campaigns
webhook_queue	Queues	Pending webhook events for processing
webhook_logs	Queues	Webhook event audit log
enrichment_queue	Queues	Transcript and analysis processing queue
historical_sync_jobs	Sync	Historical call data import jobs
cron_job_logs	Sync	Cron job execution logs

Custom Types

The schema defines 14 PostgreSQL enums for type safety across status fields, roles, and categories. Key types include user_role, call_direction, phone_number_status, and various processing status enums.

Enrichment Pipeline

Database triggers automate the call enrichment pipeline:

1. Call ends → call_ended_queue_transcript trigger inserts a transcript item into enrichment_queue
2. Transcript fetched → transcript_fetched_queue_analysis trigger inserts an analysis item into enrichment_queue
3. Cron functions (process-enrichment-queue) claim and process queue items in batches

Views

Three database views provide pre-joined data for common queries:

• v_agency_phone_numbers_with_agent -- phone numbers enriched with agent details
• v_call_batch_summary -- batch operations with progress and campaign info
• virsyn_cron_jobs -- filtered view of active cron jobs