CREATE TABLE sms_messages (
id TEXT PRIMARY KEY,
-- internal id
phone_number TEXT NOT NULL,
direction TEXT NOT NULL CHECK (direction IN ('inbound','outbound')),
role TEXT CHECK (role IN ('user','assistant')),
-- optional but helpful
text_body TEXT NOT NULL,
textbelt_text_id TEXT,
-- if provided
webhook_data TEXT,
-- correlation data
source_event_id TEXT NOT NULL,
received_at TEXT NOT NULL,
answer_state TEXT NOT NULL CHECK (
answer_state IN ('none','pending','drafted','suppressed','queued','sent')
),
suppression_reason TEXT,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
FOREIGN KEY (phone_number)
REFERENCES sms_conversations(phone_number)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (source_event_id)
REFERENCES events(id)
ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
)