SMS & Campaigns Full Schema

This page documents the complete table schemas for the SMS and campaigns system. The overview and gotchas pages cover behavior — this page covers structure.

campaigns

SELECT id, id_company, name, status, id_template,
       schedule_type, scheduled_at, created_at, deleted_at
FROM campaigns
WHERE id_company = :id_company AND deleted_at IS NULL;

Column	Type	Notes
id	int	Primary key
id_company	int	Tenant scope — always required
name	varchar	Campaign display name
status	varchar	See status enum below
id_template	int	FK → email_templates.id
schedule_type	varchar	immediate, scheduled
scheduled_at	timestamptz	Null if immediate
deleted_at	timestamptz	Soft delete

Status enum

Value	Meaning
draft	Not yet scheduled
scheduled	Queued for future send
active	Currently sending
paused	Manually paused
completed	All sends finished

Creating a campaign

INSERT INTO campaigns (id_company, name, status, id_template,
                       schedule_type, created_at)
VALUES (:id_company, :name, 'draft', :id_template,
        'immediate', NOW())
RETURNING id;

campaigns_items

One row per contact per campaign send.

SELECT id, id_campaign, customer_id, id_company,
       status, sent_at, opened_at, replied_at, deleted_at
FROM campaigns_items
WHERE id_company = :id_company
  AND id_campaign = :id_campaign
  AND deleted_at IS NULL;

Column	Type	Notes
id	int	Primary key
id_campaign	int	FK → campaigns.id
customer_id	int	FK → contacts table — NOT id_customer
id_company	int	Tenant scope
status	varchar	pending, sent, failed, unsubscribed
sent_at	timestamptz	Null until sent
opened_at	timestamptz	Null until opened
replied_at	timestamptz	Null until reply received
deleted_at	timestamptz	Soft delete

Adding contacts to a campaign

INSERT INTO campaigns_items (id_campaign, customer_id,
                             id_company, status)
VALUES (:id_campaign, :customer_id, :id_company, 'pending');

Reply rate calculation

SELECT
  COUNT(*) AS total_sent,
  COUNT(replied_at) AS total_replied,
  ROUND(
    COUNT(replied_at)::numeric / NULLIF(COUNT(*), 0) * 100,
    1
  ) AS reply_rate_pct
FROM campaigns_items
WHERE id_campaign = :id_campaign
  AND id_company = :id_company
  AND deleted_at IS NULL
  AND status = 'sent';

sms_templates

SELECT id, id_company, name, body, created_at
FROM sms_templates
WHERE id_company = :id_company;

Column	Type	Notes
body	text	Template content with {{variable}} syntax

Available variables: {{first_name}}, {{last_name}}, {{phone}}, {{email}}, {{company}}

Opt-out tracking

Opt-outs are stored as a tag on the contact record. When a contact replies with STOP, the inbound SMS router adds the tag unsubscribed.

Before sending any campaign SMS, check:

SELECT id FROM taxonomy
WHERE id_customer = :customer_id
  AND tag = 'unsubscribed'
  AND id_company = :id_company;
-- If rows returned, do not send

Available phone numbers

Outbound SMS sends from the number configured in pb_settings under the key telnyx_phone (or twilio_phone depending on provider). Do not hardcode phone numbers — always read from pb_settings.

SELECT value FROM pb_settings WHERE key = 'telnyx_phone';
-- Note: pb_settings has NO id_company column