Call Tables

calls

Individual call records with transcripts, AI analysis, and metadata. Records are created from webhook events or historical sync jobs.

Column	Type	Default	Nullable	Description
id	uuid	uuid_generate_v4()	no	Primary key
agency_id	uuid	—	no	FK → agencies.id
client_id	uuid	—	yes	FK → clients.id
campaign_id	uuid	—	yes	FK → campaigns.id
ultravox_call_id	varchar(255)	—	no	Ultravox-side call identifier
ultravox_agent_id	varchar(255)	—	yes	Agent that handled the call
ultravox_agent_name	varchar(255)	—	yes	Agent display name at time of call
source	call_source	'webhook'	no	How the record was created
sync_job_id	uuid	—	yes	FK → historical_sync_jobs.id (if imported)
direction	call_direction	—	yes	Inbound or outbound
from_number	varchar(50)	—	yes	Caller phone number
to_number	varchar(50)	—	yes	Called phone number
created_at	timestamptz	—	no	When the call started (from Ultravox)
joined_at	timestamptz	—	yes	When the agent joined
ended_at	timestamptz	—	yes	When the call ended
duration_seconds	integer	—	yes	Call duration
end_reason	varchar(50)	—	yes	Why the call ended (e.g., hangup, timeout)
short_summary	text	—	yes	One-line call summary
summary	text	—	yes	Detailed call summary
metadata	jsonb	'{}'	yes	Additional call metadata
transcript	jsonb	—	yes	Full call transcript
transcript_status	transcript_status	'pending'	no	Transcript fetch state
transcript_error	text	—	yes	Transcript fetch error message
transcript_fetched_at	timestamptz	—	yes	When transcript was fetched
analysis	jsonb	—	yes	AI analysis results
analysis_status	analysis_status	'pending'	no	Analysis processing state
analysis_error	text	—	yes	Analysis error message
analysis_completed_at	timestamptz	—	yes	When analysis completed
raw_payload	jsonb	—	yes	Original webhook/API payload
has_recording	boolean	false	yes	Whether a recording exists
record_created_at	timestamptz	now()	no	When the DB record was created
record_updated_at	timestamptz	now()	no	Last DB record update (auto-updated)

Constraints:

Constraint	Type	Details
calls_pkey	Primary key	id
calls_ultravox_unique_per_agency	Unique	(agency_id, ultravox_call_id)
calls_agency_id_fkey	Foreign key	agency_id → agencies(id) ON DELETE CASCADE
calls_client_id_fkey	Foreign key	client_id → clients(id) ON DELETE SET NULL
calls_campaign_id_fkey	Foreign key	campaign_id → campaigns(id) ON DELETE SET NULL
calls_duration_positive	Check	duration_seconds IS NULL OR duration_seconds >= 0

Indexes:

Index	Columns	Notes
idx_calls_agency_id	agency_id
idx_calls_client_id	client_id
idx_calls_campaign_id	campaign_id
idx_calls_ultravox_call_id	ultravox_call_id	Lookup by Ultravox ID
idx_calls_ultravox_agent_id	ultravox_agent_id	Calls by agent
idx_calls_created_at	created_at DESC	Chronological listing
idx_calls_ended_at	ended_at DESC	Recent ended calls
idx_calls_direction	direction
idx_calls_end_reason	end_reason
idx_calls_agency_created	(agency_id, created_at DESC)	Agency call history
idx_calls_client_created	(client_id, created_at DESC)	Client call history
idx_calls_campaign_created	(campaign_id, created_at DESC)	Campaign call history
idx_calls_transcript_status	transcript_status	Filtered: WHERE status IN ('pending', 'fetching')
idx_calls_analysis_status	analysis_status	Filtered: WHERE status IN ('pending', 'processing')
idx_calls_sync_job	sync_job_id	Filtered: WHERE sync_job_id IS NOT NULL

Triggers:

Trigger	Event	Function	Description
call_ended_queue_transcript	AFTER INSERT or UPDATE	trigger_call_ended_queue_transcript()	Queues transcript fetch when a call ends
transcript_fetched_queue_analysis	AFTER UPDATE	trigger_transcript_fetched_queue_analysis()	Queues AI analysis when transcript is ready
update_calls_record_updated_at	BEFORE UPDATE	update_call_record_updated_at()	Keeps record_updated_at current

Related edge functions: process-webhook-queue, process-enrichment-queue, get-call-recording

---

call_stats_daily

Aggregated daily call statistics per agency, optionally broken down by client and campaign. Populated by the refresh-aggregations edge function.

Column	Type	Default	Nullable	Description
id	uuid	uuid_generate_v4()	no	Primary key
agency_id	uuid	—	no	FK → agencies.id
client_id	uuid	—	yes	FK → clients.id
campaign_id	uuid	—	yes	FK → campaigns.id
stat_date	date	—	no	The date these stats cover
total_calls	integer	0	no	Total calls on this date
completed_calls	integer	0	no	Successfully completed calls
failed_calls	integer	0	no	Failed calls
total_duration_seconds	bigint	0	no	Sum of all call durations
avg_duration_seconds	numeric(10,2)	—	yes	Average call duration
min_duration_seconds	integer	—	yes	Shortest call
max_duration_seconds	integer	—	yes	Longest call
inbound_calls	integer	0	no	Inbound call count
outbound_calls	integer	0	no	Outbound call count
positive_sentiment_calls	integer	0	no	Calls with positive sentiment
neutral_sentiment_calls	integer	0	no	Calls with neutral sentiment
negative_sentiment_calls	integer	0	no	Calls with negative sentiment
meetings_booked	integer	0	no	Meetings booked (SDR campaigns)
qualified_leads	integer	0	no	Qualified leads (SDR campaigns)
objections_encountered	integer	0	no	Objections encountered (SDR campaigns)
issues_resolved	integer	0	no	Issues resolved (CS campaigns)
escalations	integer	0	no	Escalations (CS campaigns)
created_at	timestamptz	now()	no	Record creation timestamp
updated_at	timestamptz	now()	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
call_stats_daily_pkey	Primary key	id
call_stats_daily_unique	Unique	(agency_id, client_id, campaign_id, stat_date)
call_stats_daily_agency_id_fkey	Foreign key	agency_id → agencies(id) ON DELETE CASCADE
call_stats_daily_client_id_fkey	Foreign key	client_id → clients(id) ON DELETE CASCADE
call_stats_daily_campaign_id_fkey	Foreign key	campaign_id → campaigns(id) ON DELETE CASCADE
call_stats_daily_counts_positive	Check	All count and duration fields ≥ 0

Indexes:

Index	Columns	Notes
idx_call_stats_daily_agency	(agency_id, stat_date DESC)	Agency dashboard queries
idx_call_stats_daily_client	(client_id, stat_date DESC)	Client-filtered queries
idx_call_stats_daily_campaign	(campaign_id, stat_date DESC)	Campaign-filtered queries
idx_call_stats_daily_date	stat_date DESC	Global date queries
idx_call_stats_daily_agency_date_range	(agency_id, stat_date)	Date range queries

Triggers:

Trigger	Event	Function
update_call_stats_daily_updated_at	BEFORE UPDATE	update_updated_at_column()

Related edge functions: refresh-aggregations

---

call_batches

Outbound batch call operations. Tracks progress of batch calls against a set of campaign contacts.

Column	Type	Default	Nullable	Description
id	uuid	uuid_generate_v4()	no	Primary key
agency_id	uuid	—	no	FK → agencies.id
campaign_id	uuid	—	yes	FK → campaigns.id
agent_mapping_id	uuid	—	no	FK → agent_mappings.id
from_number	varchar(20)	—	no	Originating phone number (E.164)
status	batch_status	'pending'	no	Current batch status
mode	batch_mode	'immediate'	no	Execution mode
max_concurrent	integer	10	no	Max simultaneous calls (1–100)
calls_per_minute	integer	30	no	Rate limit (1–60)
total_contacts	integer	—	no	Total contacts in batch
queued	integer	0	no	Contacts queued for calling
in_progress	integer	0	no	Contacts currently being called
completed	integer	0	no	Contacts completed
failed	integer	0	no	Contacts failed
skipped	integer	0	no	Contacts skipped
scheduled_for	timestamptz	—	yes	When to start (for scheduled mode)
started_at	timestamptz	—	yes	When processing began
completed_at	timestamptz	—	yes	When processing finished
paused_at	timestamptz	—	yes	When paused
telephony_batch_id	varchar(255)	—	yes	External telephony batch ID
error_message	text	—	yes	Last error message
last_error_at	timestamptz	—	yes	When the last error occurred
created_at	timestamptz	now()	no	Record creation timestamp
updated_at	timestamptz	now()	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
call_batches_pkey	Primary key	id
call_batches_agency_id_fkey	Foreign key	agency_id → agencies(id) ON DELETE CASCADE
call_batches_agent_mapping_id_fkey	Foreign key	agent_mapping_id → agent_mappings(id) ON DELETE RESTRICT
call_batches_campaign_id_fkey	Foreign key	campaign_id → campaigns(id) ON DELETE SET NULL
call_batches_from_number_format	Check	from_number ~ '^\+[1-9][0-9]{6,14}$'
call_batches_max_concurrent_range	Check	max_concurrent >= 1 AND max_concurrent <= 100
call_batches_calls_per_minute_range	Check	calls_per_minute >= 1 AND calls_per_minute <= 60
call_batches_counts_positive	Check	All count fields ≥ 0

Indexes:

Index	Columns	Notes
idx_call_batches_agency_id	agency_id
idx_call_batches_campaign_id	campaign_id	Filtered: WHERE campaign_id IS NOT NULL
idx_call_batches_status	status
idx_call_batches_active	(agency_id, status)	Filtered: WHERE status IN ('pending', 'scheduled', 'processing', 'paused')
idx_call_batches_scheduled	scheduled_for	Filtered: WHERE status = 'scheduled' AND scheduled_for IS NOT NULL
idx_call_batches_telephony_id	telephony_batch_id	Filtered: WHERE telephony_batch_id IS NOT NULL

Triggers:

Trigger	Event	Function
update_call_batches_updated_at	BEFORE UPDATE	update_updated_at_column()

Views: v_call_batch_summary joins this table with agent_mappings and campaigns to include agent name, campaign name, and computed progress.