references/models-messaging-opt-outs.md
Messaging opt-outs
Message recipient preferences (system.message_recipient_preferences)
Messaging preferences per recipient, one row per recipient. The preferences map records opt-outs and opt-ins per message category.
Columns
Column | Type | Nullable | Description
--- | --- | --- | ---
id | String | NOT NULL | Preference row UUID.
team_id | Integer | NOT NULL |
identifier | String | NOT NULL | Recipient identifier, usually an email address.
preferences | JSON | NOT NULL | JSON map of message category ID to 'OPTED_OUT' or 'OPTED_IN'. The key '$all' covers all marketing messages; other keys are message_categories ids.
deleted | Integer | NOT NULL | 1 if the row has been deleted, 0 otherwise.
created_at | DateTime | NOT NULL | When the recipient was first recorded.
updated_at | DateTime | NOT NULL | When the recipient's preferences last changed.
Message categories (system.message_categories)
Message categories recipients can opt out of, one row per category. Category IDs are the keys in message_recipient_preferences.preferences.
Columns
Column | Type | Nullable | Description
--- | --- | --- | ---
id | String | NOT NULL | Category UUID, used as the key in recipient preferences.
team_id | Integer | NOT NULL |
key | String | NOT NULL | Stable category key used in the API, e.g. 'newsletter'.
name | String | NOT NULL | Display name of the category.
description | String | NOT NULL | Internal description of the category.
public_description | String | NOT NULL | Description shown to recipients on the preferences page.
category_type | String | NOT NULL | 'marketing' (opt-out applies) or 'transactional'.
deleted | Integer | NOT NULL | 1 if the category has been deleted, 0 otherwise.
created_at | DateTime | NOT NULL | When the category was created.
updated_at | DateTime | NOT NULL | When the category was last updated.
Query Examples
-- Recipients opted out of all marketing messages
SELECT identifier, updated_at
FROM system.message_recipient_preferences
WHERE deleted = 0 AND JSONExtractString(preferences, '$all') = 'OPTED_OUT'
ORDER BY updated_at DESC
-- Opt-out counts per category
SELECT c.key, c.name, count() AS opted_out
FROM system.message_recipient_preferences AS p
JOIN system.message_categories AS c ON JSONExtractString(p.preferences, toString(c.id)) = 'OPTED_OUT'
WHERE p.deleted = 0 AND c.deleted = 0
GROUP BY c.key, c.name
ORDER BY opted_out DESC
-- Is a specific recipient opted out of a category (falling back to the all-marketing flag)?
SELECT identifier,
JSONExtractString(preferences, (SELECT toString(id) FROM system.message_categories WHERE key = 'newsletter')) AS category_status,
JSONExtractString(preferences, '$all') AS all_marketing_status
FROM system.message_recipient_preferences
WHERE deleted = 0 AND identifier = '[email protected]'