48 lines
1.4 KiB
SQL
48 lines
1.4 KiB
SQL
CREATE TABLE campaigns (
|
|
id TEXT PRIMARY KEY,
|
|
code TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
audience_label TEXT NOT NULL,
|
|
audience_group TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
total_recipients INTEGER NOT NULL DEFAULT 0,
|
|
delivered_count INTEGER NOT NULL DEFAULT 0,
|
|
read_count INTEGER NOT NULL DEFAULT 0,
|
|
failed_count INTEGER NOT NULL DEFAULT 0,
|
|
delivery_rate DOUBLE PRECISION,
|
|
read_rate DOUBLE PRECISION,
|
|
sent_at TIMESTAMP(3),
|
|
scheduled_at TIMESTAMP(3),
|
|
template_name TEXT,
|
|
language TEXT,
|
|
message_title TEXT,
|
|
message_body TEXT,
|
|
primary_button TEXT,
|
|
secondary_button TEXT,
|
|
banner_image_url TEXT,
|
|
created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE campaign_recipients (
|
|
id TEXT PRIMARY KEY,
|
|
campaign_id TEXT NOT NULL,
|
|
phone_number TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
sent_at TIMESTAMP(3),
|
|
error_reason TEXT,
|
|
device_os TEXT,
|
|
created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT campaign_recipients_campaign_id_fkey
|
|
FOREIGN KEY (campaign_id) REFERENCES campaigns(id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE INDEX campaign_recipients_campaign_id_status_idx
|
|
ON campaign_recipients(campaign_id, status);
|
|
|
|
CREATE INDEX campaign_recipients_campaign_id_sent_at_idx
|
|
ON campaign_recipients(campaign_id, sent_at);
|