How Can I Determine Whether a Conversation Tag Is Active?

Question

The CONVERSATION_TAG_HISTORY table contains the tags added to each conversation. However, it doesn't include a field that indicates whether a tag is active. How can I determine whether a tag is active?

Environment

Connector: Help Scout

Answer

To determine whether a tag is active, compare the conversation_updated_at timestamp in the CONVERSATION_TAG_HISTORY table with the updated_at timestamp in the CONVERSATION_HISTORY table. If these timestamps match, the tag is active. If they don't match, the conversation has been updated since the tag was added, indicating that the tag was removed.

You can use the following SQL query to check whether a tag is still active for a specific conversation:

SELECT *  
FROM conversation_tag_history t  
JOIN conversation_history c  
ON t.conversation_id = c.id  
WHERE c.id = '<conversationid>'  
AND c.updated_at = t.conversation_updated_at;