Query

event_store.db — email_messages

SQL

CREATE TABLE email_messages  (
  message_id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  mailbox TEXT,
  thread_key TEXT NOT NULL,
  direction TEXT NOT NULL CHECK (direction IN ('inbound','outbound')),
  from_email TEXT,
  from_name TEXT,
  to_emails TEXT,
  cc_emails TEXT,
  subject TEXT,
  text_body TEXT,
  html_body TEXT,
  in_reply_to TEXT,
  references_json TEXT,
  labels_json TEXT,
  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')),
  latest_draft_id TEXT,
  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 (thread_key) REFERENCES email_threads(thread_key) 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
message_id TEXT ✓ ✓ Rename | Drop
provider TEXT Rename | Drop
mailbox TEXT ✓ Rename | Drop
thread_key TEXT Rename | Drop
direction TEXT Rename | Drop
from_email TEXT ✓ Rename | Drop
from_name TEXT ✓ Rename | Drop
to_emails TEXT ✓ Rename | Drop
cc_emails TEXT ✓ Rename | Drop
subject TEXT ✓ Rename | Drop
text_body TEXT ✓ Rename | Drop
html_body TEXT ✓ Rename | Drop
in_reply_to TEXT ✓ Rename | Drop
references_json TEXT ✓ Rename | Drop
labels_json TEXT ✓ Rename | Drop
source_event_id TEXT Rename | Drop
received_at TEXT Rename | Drop
answer_state TEXT Rename | Drop
latest_draft_id 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
thread_key email_threads.thread_key

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_email_messages_answer_state
  • answer_state
  • direction
  • received_at
 SQL 
CREATE INDEX idx_email_messages_answer_state
ON email_messages(answer_state, direction, received_at ASC)
Drop
idx_email_messages_from
  • from_email
  • received_at
 SQL 
CREATE INDEX idx_email_messages_from
ON email_messages(from_email, received_at DESC)
Drop
idx_email_messages_mailbox
  • mailbox
  • received_at
 SQL 
CREATE INDEX idx_email_messages_mailbox
ON email_messages(mailbox, received_at DESC)
Drop
idx_email_messages_thread
  • thread_key
  • received_at
 SQL 
CREATE INDEX idx_email_messages_thread
ON email_messages(thread_key, received_at DESC)
Drop
sqlite_autoindex_email_messages_1 message_id ✓ SQL
-- no sql found --
 Drop