Sync Tables
historical_sync_jobs
Tracks historical call data import jobs. Each job fetches past calls from Ultravox for a specified date range and imports them into the calls table.
| Column | Type | Default | Nullable | Description |
|---|---|---|---|---|
id | uuid | uuid_generate_v4() | no | Primary key |
agency_id | uuid | — | no | FK → agencies.id |
date_from | timestamptz | — | yes | Start of date range (null = beginning of time) |
date_to | timestamptz | — | no | End of date range |
status | sync_job_status | 'pending' | no | Job status |
total_calls | integer | — | yes | Total calls found in Ultravox |
processed_calls | integer | 0 | no | Calls processed so far |
imported_calls | integer | 0 | no | Calls successfully imported |
skipped_calls | integer | 0 | no | Calls skipped (already exist) |
failed_calls | integer | 0 | no | Calls that failed to import |
cursor | text | — | yes | Pagination cursor for resuming |
error_message | text | — | yes | Last error message |
last_error_at | timestamptz | — | yes | When the last error occurred |
max_calls | integer | 100 | yes | Maximum calls to import per job |
created_at | timestamptz | now() | no | Record creation timestamp |
started_at | timestamptz | — | yes | When processing began |
completed_at | timestamptz | — | yes | When processing finished |
last_activity_at | timestamptz | — | yes | Last processing activity timestamp |
Constraints:
| Constraint | Type | Details |
|---|---|---|
historical_sync_jobs_pkey | Primary key | id |
historical_sync_jobs_agency_id_fkey | Foreign key | agency_id → agencies(id) ON DELETE CASCADE |
sync_jobs_counts_positive | Check | processed_calls >= 0 AND imported_calls >= 0 AND skipped_calls >= 0 AND failed_calls >= 0 |
sync_jobs_date_range | Check | date_from IS NULL OR date_from < date_to |
Indexes:
| Index | Columns | Notes |
|---|---|---|
idx_sync_jobs_agency | agency_id | Agency-scoped queries |
idx_sync_jobs_status | status | Filter by status |
idx_sync_jobs_active | (agency_id, status) | Filtered: WHERE status IN ('pending', 'in_progress') — active jobs |
idx_sync_jobs_created | created_at DESC | Chronological listing |
Related edge functions: start-historical-sync, process-historical-sync, cancel-historical-sync, get-sync-status
cron_job_logs
Audit log for cron job executions. Each row represents one execution of a scheduled job.
| Column | Type | Default | Nullable | Description |
|---|---|---|---|---|
id | uuid | gen_random_uuid() | no | Primary key |
job_name | text | — | no | Name of the cron job |
started_at | timestamptz | now() | yes | When execution started |
completed_at | timestamptz | — | yes | When execution finished |
status | text | 'running' | yes | Execution status (e.g., running, completed, failed) |
result | jsonb | — | yes | Execution result data |
error_message | text | — | yes | Error message if failed |
created_at | timestamptz | now() | yes | Record creation timestamp |
Constraints:
| Constraint | Type | Details |
|---|---|---|
cron_job_logs_pkey | Primary key | id |
Indexes:
| Index | Columns |
|---|---|
idx_cron_job_logs_job_name | job_name |
idx_cron_job_logs_started_at | started_at DESC |
Related views: virsyn_cron_jobs shows the currently active cron job definitions.