Broadcast Campaigns

Overview

This domain has two tables. campaign holds the campaign definition (recipients, content, send time, status and result counters). campaign_delivery_batch is created by manual SQL and tracks chunked delivery of multicast campaigns, with one row per delivery batch.

Table campaign

Column	Type	Nullable	Default	Description
id	SERIAL (INTEGER)	NO	nextval(...)	Primary key of the campaign
name	TEXT	NO	-	Campaign name
broadcast_now	BOOLEAN	NO	-	Send immediately (false means schedule at start_date)
start_date	TIMESTAMPTZ(3)	NO	-	Scheduled send time
cast_type	"CastType"	YES	broadcast	Delivery mode: broadcast, multicast, narrowcast
audience_id	INTEGER	YES	-	Target audience (FK to audience.id; NULL for a full broadcast)
rich_message_id	INTEGER	NO	-	Message to send (FK to rich_message.id)
line_oa_id	INTEGER	NO	-	Sending LINE OA (FK to line_oa.id)
organization_id	INTEGER	YES	0	Owning organization (FK to organization.id)
status	"CampaignStatus"	NO	-	Campaign status: sent, scheduled, cancel, draft, failed, sending
created_date	TIMESTAMPTZ(3)	NO	now()	Creation timestamp
created_by	INTEGER	NO	0	User who created the record
updated_date	TIMESTAMPTZ(3)	YES	-	Last update timestamp (maintained automatically)
updated_by	INTEGER	YES	0	User who last updated the record
deleted_date	TIMESTAMPTZ(3)	YES	-	Soft-delete timestamp
reason	TEXT	YES	''	Reason accompanying the status, e.g. why it was cancelled or failed
rich_message_content	JSONB	YES	-	Snapshot of the message content at send time (guards against later edits to the source)
line_message_object	JSONB	YES	-	Actual payload submitted to the LINE Messaging API
template_tracking	JSONB	YES	-	Mapping used to track clicks on each part of the message
end_tracking_date	TIMESTAMPTZ(3)	YES	-	When click-statistics collection for the campaign stops
has_merge_tags	BOOLEAN	NO	false	Whether the message uses merge tags (personalization variables)
skip_merge_tag_missing	BOOLEAN	NO	false	Skip recipients missing a merge tag value instead of sending with a blank
total_activity	INTEGER	NO	0	Total activities generated by this campaign
total_recipient	INTEGER	NO	0	Number of intended recipients
total_reach	INTEGER	NO	0	Number of recipients actually reached
total_unique_click	INTEGER	NO	0	Number of unique clickers
total_first_click	INTEGER	NO	0	Number of first clicks per recipient
claimed_at	TIMESTAMPTZ	YES	-	When a worker claimed the campaign for sending; drives the lease on the sending status

Table campaign_delivery_batch

This table is not in the Prisma schema — it is created by manual-sql/8.campaign_delivery_batch.sql. It stores one row per delivery batch of a multicast campaign: the worker planner inserts the rows, the delivery worker claims and updates them, and the last batch to finish finalizes the campaign.

Column	Type	Nullable	Default	Description
id	BIGSERIAL (BIGINT)	NO	nextval(...)	Primary key of the batch
campaign_id	INTEGER	NO	-	Campaign the batch belongs to
batch_no	INTEGER	NO	-	Batch sequence number within the campaign
user_count	INTEGER	NO	-	Number of recipients in this batch
status	VARCHAR	NO	'pending'	Batch status: pending, sending, done, failed
sent_count	INTEGER	NO	0	Successfully sent messages in this batch
error_count	INTEGER	NO	0	Failed sends
skipped_count	INTEGER	NO	0	Skipped recipients (e.g. missing merge tag data)
created_date	TIMESTAMPTZ	NO	now()	Batch creation timestamp
updated_date	TIMESTAMPTZ	NO	now()	Batch last update timestamp

Notes

• FKs on campaign: audience_id to audience.id, rich_message_id to rich_message.id, line_oa_id to line_oa.id, organization_id to organization.id. It is referenced back by tracking_log.campaign_id.
• Indexes on campaign: line_oa_id, deleted_date.
• claimed_at was added later by manual-sql/8.campaign_claim.sql, together with the sending value on the CampaignStatus enum. sending means a worker has claimed the campaign, and claimed_at lets the reaper detect rows stuck past their lease and revert them to scheduled.
• campaign_delivery_batch has the unique constraint campaign_delivery_batch_campaign_batch_uq on (campaign_id, batch_no), which prevents double claims and bounds a redelivery to re-sending at most one batch.
• campaign_delivery_batch has the index campaign_delivery_batch_campaign_status_idx on (campaign_id, status) so the finalize check over in-flight batches stays cheap.
• campaign_delivery_batch.campaign_id is not declared as an FK constraint in the script (it logically refers to campaign.id).