Campaign Tables

campaigns

SDR and CS campaign definitions. Each campaign belongs to a client and determines how calls are analyzed.

Column	Type	Default	Nullable	Description
`id`	`uuid`	`uuid_generate_v4()`	no	Primary key
`agency_id`	`uuid`	—	yes	FK → `agencies.id`
`client_id`	`uuid`	—	no	FK → `clients.id`
`name`	`varchar(255)`	—	no	Campaign display name
`type`	`campaign_type`	—	no	`sdr` or `cs` — determines analysis behavior
`settings`	`jsonb`	`'{}'`	yes	Campaign-specific configuration
`is_active`	`boolean`	`true`	no	Whether the campaign is active
`created_at`	`timestamptz`	`now()`	no	Record creation timestamp
`updated_at`	`timestamptz`	`now()`	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
`campaigns_pkey`	Primary key	`id`
`campaigns_agency_id_fkey`	Foreign key	`agency_id` → `agencies(id)` ON DELETE CASCADE
`campaigns_client_id_fkey`	Foreign key	`client_id` → `clients(id)` ON DELETE CASCADE
`campaigns_name_not_empty`	Check	`length(trim(name)) > 0`

Indexes:

Triggers:

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

Contacts within a campaign. Tracks call attempts, outcomes, and do-not-call status.

Column	Type	Default	Nullable	Description
`id`	`uuid`	`uuid_generate_v4()`	no	Primary key
`campaign_id`	`uuid`	—	no	FK → `campaigns.id`
`phone_number`	`varchar(20)`	—	no	Contact phone number
`first_name`	`varchar(100)`	—	yes	Contact first name
`last_name`	`varchar(100)`	—	yes	Contact last name
`email`	`varchar(255)`	—	yes	Contact email address
`company`	`varchar(255)`	—	yes	Company name
`title`	`varchar(255)`	—	yes	Job title
`timezone`	`varchar(50)`	—	yes	Contact timezone
`custom_data`	`jsonb`	`'{}'`	no	Additional contact fields
`status`	`contact_status`	`'pending'`	no	Processing status
`call_id`	`uuid`	—	yes	FK → `calls.id` (last call)
`batch_id`	`uuid`	—	yes	FK → `call_batches.id` (current batch)
`attempts`	`integer`	`0`	no	Number of call attempts made
`max_attempts`	`integer`	`3`	no	Maximum attempts before failing
`last_attempt_at`	`timestamptz`	—	yes	When the last call was attempted
`next_attempt_at`	`timestamptz`	—	yes	When to try next
`completed_at`	`timestamptz`	—	yes	When processing completed
`outcome`	`contact_outcome`	—	yes	Result of the call attempt
`duration_seconds`	`integer`	—	yes	Duration of the last call
`outcome_notes`	`text`	—	yes	Notes about the outcome
`do_not_call`	`boolean`	`false`	no	Whether the contact is on the DNC list
`dnc_reason`	`text`	—	yes	Why the contact was marked DNC
`created_at`	`timestamptz`	`now()`	no	Record creation timestamp
`updated_at`	`timestamptz`	`now()`	no	Last update timestamp (auto-updated)

Constraints:

Constraint	Type	Details
`campaign_contacts_pkey`	Primary key	`id`
`campaign_contacts_campaign_id_fkey`	Foreign key	`campaign_id` → `campaigns(id)` ON DELETE CASCADE
`campaign_contacts_call_id_fkey`	Foreign key	`call_id` → `calls(id)` ON DELETE SET NULL
`campaign_contacts_batch_id_fkey`	Foreign key	`batch_id` → `call_batches(id)` ON DELETE SET NULL
`campaign_contacts_phone_format`	Check	`phone_number ~ '^\+?[1-9][0-9]{6,14}$'`
`campaign_contacts_attempts_positive`	Check	`attempts >= 0`
`campaign_contacts_max_attempts_positive`	Check	`max_attempts > 0`

Indexes:

Index	Columns	Notes
`idx_campaign_contacts_campaign_id`	`campaign_id`	Campaign-scoped queries
`idx_campaign_contacts_status`	`status`	Filter by processing status
`idx_campaign_contacts_phone`	`phone_number`	Lookup by phone number
`idx_campaign_contacts_call_id`	`call_id`	Filtered: `WHERE call_id IS NOT NULL`
`idx_campaign_contacts_batch_id`	`batch_id`	Filtered: `WHERE batch_id IS NOT NULL`
`idx_campaign_contacts_pending`	`(campaign_id, status, next_attempt_at)`	Filtered: `WHERE status IN ('pending', 'scheduled')` — for picking next contacts to call
`idx_campaign_contacts_dnc`	`phone_number`	Filtered: `WHERE do_not_call = true` — DNC lookups

Triggers:

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