# Messages (datalake dataset) One row = one attempted communication (SMS, email, voice, web form, push). The platform writes it when a workflow action reaches execution — which is not the same as "a message went out". ``` dry run → row IS written, at status :pending, external_id null. The action pipeline creates the message row BEFORE it checks the mode; dry_run then skips only the tool call + status update. A rehearsal leaves :pending rows — see the dry-run note below and workflows.md §8. scheduled action → NO row yet — the row is written when the action FIRES, not when it's scheduled ("scheduled" lives in the logs; see action_logs.md §1). live dispatch → row at :sent, external_id from the provider. ``` **So row-presence ≠ "it sent".** To count real sends, filter by `status` (and, if you rehearse, exclude dry-run rows via the execution `mode` — a dry-run row sits at `:pending` with no `external_id`). A `:pending` row also *consumes its idempotency key* (the key is written even in dry_run, and `:pending` is inside the uniqueness constraint), so a rehearsal is not a free preview — see `workflows.md` §8 for the rehearse-then-send trap. This is a **dataset**, not a resource with its own SDK namespace. Read it like any dataset: `api.datasets.createUserSearch` + `api.datasets.search` with dataset type `'message'` — **singular**; the plural `messages` is the SQL *table* name (used in `executeSql` queries and as this page's title), and passing it as the dataset type 422s with `/resource_type is not valid`. Base SQL alias: `m`. ## 1. The status lifecycle — and who writes each hop ``` pending → accepted → queued → sending → sent → delivered └──→ failed (+ failure_reason) also: duplicate (provider id already held by another send — the send HAPPENED; terminal, never retried) invalidated (idempotency key collision — the fire was rejected, nothing went out) received (inbound message, direction: 'inbound') engagement, each with its own time: read read_at opened opened_at the link host's page opened clicked clicked_at a person first tapped a link form_submitted form_submitted_at the link host's form was submitted ``` `accepted` (the provider took the send request) and `sending` (dispatching to the carrier) are intermediate hops between `pending` and `sent`; the order above is the order the contract declares them. Treat any of them as "in flight, outcome unknown" — see **Status is eventually honest** below. Four different writers touch a message row. Know which is which: | Writer | Writes | When | |---|---|---| | the action executor | the row itself; `pending`→`sent`, `external_id`, `sent_at`; also `duplicate` | at execution (writes the row at `pending`; advances to `sent` + sets `external_id` only on a live tool call — a dry-run stops at `pending`). Lands `duplicate` when the provider id it got back is already held by another send through the same tool | | the action status updater | `delivered` / `failed`, `delivered_at`, `failure_reason` | later, when its poll runs (see `action_status_updaters.md` §7) | | `authenticatedLinks.exchange` | `clicked`, `clicked_at` (the first person's tap only) | when the host behind `link_prefix` forwards a tap; automated taps move nothing | | `authenticatedLinks.updateMessageTracking` | `opened` / `form_submitted`, `opened_at`, `form_submitted_at` | when the host reports its page opened or its form submitted; status only moves forward | Two consequences: - **"Sent" is not "delivered".** Provider acceptance and carrier delivery are different facts. If no status updater is configured for the sender tool, rows stay at `sent` forever and delivery rate is unknowable. Updaters are created manually — nothing is automatic. - **Status is eventually honest.** A just-dispatched row is neither delivered nor failed yet. A UI should show that in-between state, not guess. ### `failed` vs `duplicate` — never retry the second These are **not** interchangeable, and the difference decides whether a row is safe to re-send: | | `failed` | `duplicate` | |---|---|---| | provider id | none was ever returned | one came back, but another send through the same tool already holds it | | did anything go out? | no | **yes — the send happened** | | retryable? | yes | **no** — never retried, never re-sent | So a "retry everything that didn't reach `delivered`" sweep is wrong: it must select on `failed`, never on "not delivered". Re-sending a `duplicate` sends a second real message to a recipient who already got one. One consequence worth internalising: **the send path never records a message that has an `external_id` as `failed`.** Once a provider id comes back, the row stays `pending` until the delivery poller resolves it. `failed` therefore means "nothing left the building", not "we don't know yet". `invalidated` is a third, separate thing — an idempotency-key collision rejected the fire before it went out (see `idempotency_key` in §2). Do not read the word "duplicate" in that context as the `duplicate` status: `invalidated` means nothing was sent, `duplicate` means something was. ## 2. Fields Identity + routing: ``` id, direction ('outbound'|'inbound'), channel ('sms'|'email'| 'voice'|'web_form'|'push'), sender_type, legal_entity_id, thread_id, in_reply_to_id ``` Content (masked in the tokenized and redacted lakes): ``` subject, body, attachments, metadata, sender_id ``` Delivery truth: ``` status, external_id — provider's id (Twilio SID, SES id…); the join key for delivery write-back queued_at, sent_at, delivered_at, failure_reason, sms_carrier ``` Engagement: ``` opened_at, form_submitted_at, read_at ``` Provenance (which workflow produced this): ``` workflow_id, action_id, decision_key, context_key, idempotency_key — unique per (legal_entity_id, idempotency_key); a repeat fire on the same key lands as status 'invalidated' (nothing sent) — NOT status 'duplicate', which means a send did go out sender_tool_id ``` **There is NO `batch_id` on messages.** Campaign/batch attribution goes through `action_logs` (which has `batch_id` AND `external_id`) — join on `external_id`. See §4. ## 3. Reading messages ```typescript // (1) materialise a search — WHERE fragment against alias `m` // dataset type is SINGULAR 'message' (the table is plural) const { data: search } = await api.datasets.createUserSearch( tenantSlug, datalakeSlug, 'message', { search_query: `m.status = 'failed' AND m.channel = 'sms'` }, ) // (2) page through it const { data: page } = await api.datasets.search( tenantSlug, datalakeSlug, 'message', { userSearchId: search.id, dataAccessMode: 'tokenized' }, ) ``` Filterable columns (Flop, the full set): `id`, `inserted_at`, `direction`, `channel`, `status`, `sender_type`, `body`, `subject`, `legal_entity_id`, `sender_id`, `sent_at`, `queued_at`, `delivered_at`, `thread_id`, `external_id`, `global_search`. **`decision_key` is NOT in the Flop-filterable set** — you cannot `?filter[decision_key]=…` (nor can the workflow-run-log endpoints; see §4). But you CAN still scope a search by it two other ways, both cookbook-proven: put `m.decision_key = '…'` in the `createUserSearch` **`search_query`** SQL WHERE fragment (as the SMS-workflow cookbooks do), or `GROUP BY m.decision_key` in `executeSql` for analytics (§4). The Flop bracket-filter and the raw-SQL surfaces are different lanes — `decision_key` lives only on the SQL lane. `dataAccessMode` selects which lake answers: `'raw'` returns the real content, `'tokenized'` returns a stable `tok_` per masked column, and `'redacted'` returns an unjoinable placeholder. All three return **plain values of the column's own type** — a masked column is a string, not a wrapper object. ## 4. Campaign math (the dashboard recipe) "How did campaign X do?" — where X is one run's `batch_id`: ``` action_logs (al) — has batch_id + external_id │ join on external_id ▼ messages (m) — has status + engagement timestamps ``` ```sql SELECT count(*) AS sent, count(*) FILTER (WHERE m.status = 'delivered') AS delivered, count(*) FILTER (WHERE m.status = 'failed') AS undelivered, count(*) FILTER (WHERE m.opened_at IS NOT NULL) AS opened, count(*) FILTER (WHERE m.status = 'clicked') AS clicked FROM action_logs al JOIN messages m ON m.external_id = al.external_id WHERE al.batch_id = '' ``` Run it with `api.datalakes.executeSql` (read-only tier). Delivered rate = delivered / sent — honest ONLY if a status updater is reconciling those sender tools. Audience export ("who did it go to") = the same query selecting subject columns, or the batch's merged NDJSON artifact (see `action_logs.md` §5). ### The message log IS your per-variant analytics table Every message row, in all three lakes, carries the full provenance of the send: ``` decision_key which action fired — the A/B variant key channel sms | email | voice | web_form | push status delivery state delivered_at when the carrier confirmed opened_at engagement (from updateMessageTracking) workflow_id, action_id, sender_tool_id, legal_entity_id ``` So "how did variant A do vs variant B?" or "SMS vs email open rate?" is a plain `GROUP BY` over the datalake `messages` table via `executeSql` — **nothing to build, no analytics service**: ```sql SELECT m.decision_key, m.channel, count(*) AS sent, count(*) FILTER (WHERE m.status = 'delivered') AS delivered, count(*) FILTER (WHERE m.status = 'failed') AS failed, count(*) FILTER (WHERE m.opened_at IS NOT NULL) AS opened FROM messages m GROUP BY m.decision_key, m.channel ORDER BY m.decision_key, m.channel ``` Scope it to one campaign run by joining `action_logs` (which carries `batch_id`) on `external_id`, as in the recipe above — `decision_key` is present on both the message and the action-log rows. **The trap: the workflow-run-log REST endpoints do NOT filter on `decision_key`.** Their Flop-filterable sets are narrower than the message row's columns suggest: ``` batchLogs (workflow_run_logs) filter: batch_id, status, inserted_at workflowLogs(workflow_exec_logs) filter: status, mode, batch_id, subject_type, filter_result, inserted_at action-execution log — no Flop schema at all (not filterable) message dataset search filter: (see §3) — decision_key NOT in it ``` So `?filter[decision_key]=…` fails on every one of those. The logs are the *operational* surface (how is the run doing); the datalake `messages` table via `executeSql` is the *analytics* surface (how did each variant perform). Reach for `executeSql` for any per-variant or per-channel reporting. ## 5. Gotchas 1. **Row-presence ≠ "it sent"; row-absence ≠ "nothing happened".** A **dry run DOES write a message row** (at `:pending`, no `external_id`) — only a *scheduled, not-yet-fired* action has no row yet. "What did the workflow do" is answered by the execution logs (`workflows.md` §8); the messages table answers "what actually went out" only once you filter by `status` (and exclude dry-run `:pending`/`mode` rows). See the dry-run note at the top. 2. **No `batch_id` column.** Filter `action_logs` by `batch_id`, then join to messages on `external_id`. 3. **Engagement fields are not statuses.** `opened_at` / `form_submitted_at` arrive via `updateMessageTracking` and never move `status`. A failed message can still show an `opened_at` from an earlier attempt in the thread — read both. 4. **`status: 'invalidated'` is the idempotency guard firing** — the same `(legal_entity_id, idempotency_key)` already sent. Not an error; usually exactly what you want. 5. **Delivery events that don't match are dropped silently.** The status updater joins on exact `external_id`; a prefix mismatch means rows quietly stay `sent` (see `action_status_updaters.md` gotcha 3).