The communications log
crm_communications is the unified email, call and message history — one org-scoped row per message, threaded into conversations and gated by an approval step before anything sends. crm_communication_events is the append-only engagement layer beside it: opens, clicks, replies, bounces and unsubscribes, deduped by idempotency_key. This page traces them top-down: where they are served from, the outbound lifecycle of a message, then the full schema, relationships and rules.
System design
interactive Start from the top. The inbox never touches storage directly — it talks to the communications subdomain, the only thing that reads or writes crm_communications, generates AI drafts, and hands an approved message to Nylas. Engagement flows the other way, from provider webhooks into crm_communication_events. Click a node to trace what it connects to.
Data flow — outbound email lifecycle
interactiveHow a message goes from a generated draft to a delivered, engaged-with conversation, as a closed loop. The AI drafts, a reviewer approves, Nylas sends, replies thread back, webhooks write the engagement events that roll up into the row status, and the insert trigger bumps account last-activity — then the loop closes back to the start. Click a stage to follow it.
Schema
interactive The message row is wide, so the columns are grouped — the first four groups are column groups of crm_communications, the last is the sibling crm_communication_events table. Colour marks each column's kind, and clicking a column expands its category and any table it references. Everything is org-scoped; soft-delete columns sit on the message row.
What the message is and the tenant scope every crm_communications row carries.
- primary key
Primary key.
- enum
email · call · meeting · sms · social · other.
- text
inbound · outbound.
- text
Subject line for email.
- text
Plain-text body.
- text
Rich body (ENG-412).
- text
AI-generated summary.
Relationships
interactiveA message points at the account, person and deal it belongs to, carries its sender, and fans out into per-message engagement events. Hover or click a table to trace its link and see its key columns.
crm_communications threads against the account/contact/deal and fans out to per-message engagement events.
RLS, triggers & invariants
modelThe database is storage only — no business logic. What it does enforce: tenant isolation, the soft-delete and uniqueness invariants, and the trigger that keeps account last-activity honest. The send gate and the status machine live in the API.
Drafts must pass awaiting_approval before the Nylas send fires — there is no silent outbound. A rejected draft never leaves the building.
A long enum tracks the message from draft through queued/sending/sent/delivered to opened/clicked/replied or bounced/failed. The dashboard reads it as the convenient roll-up.
crm_communication_events.idempotency_key is unique, so a re-delivered webhook never double-counts an open or a click. Engagement truth is append-only.
deleted_at + deleted_by mark a message removed; list queries filter deleted_at IS NULL rather than hard-deleting history.
is_member_of_organization(organization_id) gates every read. Users insert and update their own communications; superadmins bypass.
An insert bumps the linked company and person via update_crm_last_activity_dates(), so the account list reflects the latest touch.
crm_communications holds no rules of its own — tables, indexes, RLS and triggers only. Engagement truth lives in the append-only crm_communication_events, deduped by idempotency_key, so an open or a click is counted exactly once. The message row's status is a roll-up of those events, kept honest by the ingest path — convenient to read, but never the source of truth.