Models Messaging Opt Outs · Querying PostHog Data · PostHog/skills · Skills Docs

Models Messaging Opt Outs · Querying PostHog Data · PostHog/skills · Skills Docs

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.

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.

-- 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 = 'ally@example.com'