SMS & Campaigns Schema Gotchas

This page documents the non-obvious quirks in the SMS and campaigns tables. An AI agent or developer who skips this page will write broken queries.

campaigns_items uses customer_id, not id_customer

The foreign key linking a campaign item to a contact is named customer_id — not id_customer, not contact_id.

-- Wrong — column does not exist
SELECT * FROM campaigns_items WHERE id_customer = :contact_id;

-- Wrong — also does not exist
SELECT * FROM campaigns_items WHERE contact_id = :contact_id;

-- Correct
SELECT * FROM campaigns_items WHERE customer_id = :contact_id;

This is the single most common query error in the campaigns system.

smslog.text is a BLOB

The text column in smslog stores message content as a BLOB, not VARCHAR or TEXT. This has two consequences:

1. String comparisons require CONVERT

-- Wrong — comparison silently fails or returns unexpected results
SELECT * FROM smslog WHERE text LIKE '%follow up%';

-- Correct
SELECT * FROM smslog
WHERE CONVERT(text, CHAR) LIKE '%follow up%';

2. Output is binary unless cast

When selecting message content to display or process, always cast:

SELECT
  id,
  CONVERT(text, CHAR) AS message,
  direction,
  created_at
FROM smslog
WHERE id_company = :id_company
  AND deleted_at IS NULL
ORDER BY created_at DESC;

Forgetting the cast returns a binary buffer object in application code instead of a string. This will not throw an error — it will silently display as garbled output or [object Object].

campaigns_items soft deletes

campaigns_items follows the standard soft-delete pattern. Always include deleted_at IS NULL:

SELECT * FROM campaigns_items
WHERE id_company = :id_company
  AND customer_id = :contact_id
  AND deleted_at IS NULL;

Campaign status values

The status column on campaigns uses these exact strings:

Value	Meaning
draft	Not yet scheduled
scheduled	Queued for future send
sending	Currently in progress
sent	Completed
paused	Manually paused mid-send
failed	Send failed — check error_message

Filtering for active campaigns:

SELECT * FROM campaigns
WHERE id_company = :id_company
  AND status IN ('scheduled', 'sending')
  AND deleted_at IS NULL;

Template variable substitution

sms_templates.body uses double-curly syntax:

Hi {{first_name}}, your appointment is on {{date}}.

Substitution happens at send time. Available variables:

Variable	Source
{{first_name}}	contacts.first_name
{{last_name}}	contacts.last_name
{{phone}}	contacts.phone
{{email}}	contacts.email
{{company}}	companies.name linked to the contact

Unresolved variables (contact missing the field) render as an empty string — not as the raw {{variable}} placeholder.

Full contact SMS thread query

The correct pattern to fetch a complete SMS thread for a contact:

SELECT
  id,
  CONVERT(text, CHAR) AS message,
  direction,
  created_at
FROM smslog
WHERE id_company = :id_company
  AND contact_id = :contact_id
  AND deleted_at IS NULL
ORDER BY created_at ASC;