Campaigns, segments, lists, sends, and engagement events for email and multi-channel outreach.

Campaigns

Campaigns, audience segments, recipient lists, per-contact send tracking, and engagement events (opens, clicks, bounces). Supports email and other channels via the channel column.

Tables

campaigns

Column	Type	Description
id	uuid	Primary key, auto-generated.
org_id	uuid	References organizations. Cascade delete.
creator_id	uuid	References users. Set NULL on user delete.
name	text	Campaign name.
subject	text	Email subject line or message headline.
body	text	Campaign content body.
channel	text	Delivery channel: email, sms, push, etc. Defaults to email.
status	campaign_status	Current campaign state. Defaults to draft.
scheduled_at	timestamptz	When the campaign is scheduled to send.
sent_at	timestamptz	When the campaign was actually sent.
created_at	timestamptz	Row creation timestamp.
updated_at	timestamptz	Auto-updated via trigger.
metadata	jsonb	Arbitrary key-value data. Defaults to empty object.

campaign_segments

Column	Type	Description
id	uuid	Primary key, auto-generated.
org_id	uuid	References organizations. Cascade delete.
name	text	Segment name.
description	text	Optional description of the segment criteria.
filter_rules	jsonb	JSON filter criteria used to resolve matching contacts. Defaults to empty object.
contact_count	integer	Cached count of contacts matching the segment. Defaults to 0.
created_at	timestamptz	Row creation timestamp.
updated_at	timestamptz	Auto-updated via trigger.
metadata	jsonb	Arbitrary key-value data. Defaults to empty object.

campaign_lists

Column	Type	Description
id	uuid	Primary key, auto-generated.
org_id	uuid	References organizations. Cascade delete.
campaign_id	uuid	References campaigns. Cascade delete.
segment_id	uuid	References campaign_segments. Set NULL on segment delete.
contact_id	uuid	References contacts. Cascade delete.
created_at	timestamptz	Row creation timestamp.
updated_at	timestamptz	Auto-updated via trigger.
metadata	jsonb	Arbitrary key-value data. Defaults to empty object.

Unique constraint on (campaign_id, contact_id) -- a contact can appear in a campaign list only once.

campaign_sends

Column	Type	Description
id	uuid	Primary key, auto-generated.
org_id	uuid	References organizations. Cascade delete.
campaign_id	uuid	References campaigns. Cascade delete.
contact_id	uuid	References contacts. Cascade delete.
status	send_status	Delivery status. Defaults to queued.
sent_at	timestamptz	When the message was sent.
delivered_at	timestamptz	When delivery was confirmed.
error	text	Error message if delivery failed.
created_at	timestamptz	Row creation timestamp.
updated_at	timestamptz	Auto-updated via trigger.
metadata	jsonb	Arbitrary key-value data. Defaults to empty object.

campaign_events

Column	Type	Description
id	uuid	Primary key, auto-generated.
org_id	uuid	References organizations. Cascade delete.
send_id	uuid	References campaign_sends. Cascade delete.
event_type	campaign_event_type	Type of engagement event.
url	text	Clicked URL for click events. NULL for other event types.
occurred_at	timestamptz	When the event occurred. Defaults to now().
created_at	timestamptz	Row creation timestamp.
updated_at	timestamptz	Auto-updated via trigger.
metadata	jsonb	Arbitrary key-value data. Defaults to empty object.

Enums

campaign_status

Value	Description
draft	Campaign is being composed.
scheduled	Campaign is scheduled for future delivery.
sending	Campaign is currently being sent.
sent	Campaign has been fully sent.
paused	Campaign sending has been paused.
canceled	Campaign has been canceled.

send_status

Value	Description
queued	Message is queued for delivery.
sent	Message has been sent to the provider.
delivered	Delivery confirmed by the provider.
bounced	Message bounced.
failed	Delivery failed due to an error.

campaign_event_type

Value	Description
open	Recipient opened the message.
click	Recipient clicked a link.
unsubscribe	Recipient unsubscribed.
complaint	Recipient reported the message as spam.
bounce	Message bounced (hard or soft).

Row-Level Security

All five tables are scoped to the current user's organization via get_user_org_id(). Any org member can select, insert, and update rows. Only admins (checked via is_admin()) can delete rows.

Dependencies

crm -- contacts
identity (transitive via crm) -- organizations, users, get_user_org_id(), is_admin(), set_updated_at()

Example Queries

Calculate open rate and click rate for a campaign:

SELECT
  c.name AS campaign,
  count(DISTINCT cs.id) AS total_sends,
  count(DISTINCT cs.id) FILTER (WHERE cs.status = 'delivered') AS delivered,
  count(DISTINCT ce.id) FILTER (WHERE ce.event_type = 'open') AS opens,
  count(DISTINCT ce.id) FILTER (WHERE ce.event_type = 'click') AS clicks,
  round(
    100.0 * count(DISTINCT ce.send_id) FILTER (WHERE ce.event_type = 'open')
    / nullif(count(DISTINCT cs.id) FILTER (WHERE cs.status = 'delivered'), 0),
    1
  ) AS open_rate_pct,
  round(
    100.0 * count(DISTINCT ce.send_id) FILTER (WHERE ce.event_type = 'click')
    / nullif(count(DISTINCT cs.id) FILTER (WHERE cs.status = 'delivered'), 0),
    1
  ) AS click_rate_pct
FROM campaigns c
JOIN campaign_sends cs ON cs.campaign_id = c.id
LEFT JOIN campaign_events ce ON ce.send_id = cs.id
WHERE c.id = '<campaign_id>'
GROUP BY c.id, c.name;

Show bounce rate by campaign:

SELECT
  c.name AS campaign,
  count(cs.id) AS total_sends,
  count(cs.id) FILTER (WHERE cs.status = 'bounced') AS bounced,
  round(
    100.0 * count(cs.id) FILTER (WHERE cs.status = 'bounced')
    / nullif(count(cs.id), 0),
    1
  ) AS bounce_rate_pct
FROM campaigns c
JOIN campaign_sends cs ON cs.campaign_id = c.id
WHERE c.status = 'sent'
GROUP BY c.id, c.name
ORDER BY bounce_rate_pct DESC;

List engagement events for a specific contact across all campaigns:

SELECT
  c.name AS campaign,
  ce.event_type,
  ce.url,
  ce.occurred_at
FROM campaign_events ce
JOIN campaign_sends cs ON cs.id = ce.send_id
JOIN campaigns c ON c.id = cs.campaign_id
WHERE cs.contact_id = '<contact_id>'
ORDER BY ce.occurred_at DESC;

Show segment sizes and how many campaigns each segment has been used in:

SELECT
  seg.name AS segment,
  seg.contact_count,
  count(DISTINCT cl.campaign_id) AS campaigns_used_in
FROM campaign_segments seg
LEFT JOIN campaign_lists cl ON cl.segment_id = seg.id
GROUP BY seg.id, seg.name, seg.contact_count
ORDER BY seg.contact_count DESC;

List campaigns with delivery summary (queued, sent, delivered, bounced, failed):

SELECT
  c.name AS campaign,
  c.status,
  c.sent_at,
  count(cs.id) FILTER (WHERE cs.status = 'queued') AS queued,
  count(cs.id) FILTER (WHERE cs.status = 'sent') AS sent,
  count(cs.id) FILTER (WHERE cs.status = 'delivered') AS delivered,
  count(cs.id) FILTER (WHERE cs.status = 'bounced') AS bounced,
  count(cs.id) FILTER (WHERE cs.status = 'failed') AS failed
FROM campaigns c
LEFT JOIN campaign_sends cs ON cs.campaign_id = c.id
GROUP BY c.id, c.name, c.status, c.sent_at
ORDER BY c.sent_at DESC NULLS LAST;

Find contacts who clicked a link in any campaign but never unsubscribed:

SELECT DISTINCT
  con.id,
  con.first_name,
  con.last_name,
  con.email
FROM contacts con
JOIN campaign_sends cs ON cs.contact_id = con.id
JOIN campaign_events ce ON ce.send_id = cs.id AND ce.event_type = 'click'
WHERE con.id NOT IN (
  SELECT cs2.contact_id
  FROM campaign_sends cs2
  JOIN campaign_events ce2 ON ce2.send_id = cs2.id AND ce2.event_type = 'unsubscribe'
)
ORDER BY con.last_name, con.first_name;