Query

event_store.db — sms_messages

SQL

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
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id TEXT ✓ ✓ Rename | Drop
phone_number TEXT Rename | Drop
direction TEXT Rename | Drop
role TEXT ✓ Rename | Drop
text_body TEXT Rename | Drop
textbelt_text_id TEXT ✓ Rename | Drop
webhook_data TEXT ✓ Rename | Drop
source_event_id TEXT Rename | Drop
received_at TEXT Rename | Drop
answer_state TEXT Rename | Drop
suppression_reason TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
source_event_id events.id
phone_number sms_conversations.phone_number

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_sms_messages_answer_state
  • answer_state
  • direction
  • received_at
 SQL 
CREATE INDEX idx_sms_messages_answer_state
ON sms_messages(answer_state, direction, received_at ASC)
Drop
idx_sms_messages_phone
  • phone_number
  • received_at
 SQL 
CREATE INDEX idx_sms_messages_phone
ON sms_messages(phone_number, received_at DESC)
Drop
sqlite_autoindex_sms_messages_1 id ✓ SQL
-- no sql found --
 Drop