Querying the SQLite Cache

Starting w/ build 34 the Cache database lives in:

${HOME}/Library/Group Containers/4GXF3JAMM4.de.zeezide.swift.see1.apps.soy/Library/Caches/ACCOUNT-GUID/conversations.db

Selecting Conversation IDs

Public Channels

SELECT C.name, V.string_id, V.conversation_id 
  FROM channel AS C
  LEFT JOIN message_table_schema_version AS V 
         ON C.channel_id = V.conversation_id;
 
    general|CCD4PUFCG|4865877322943799751
    support|CCDRW5X7C|4865878355548183491
    testnew|CG81FDRDZ|4868076240400327258

Private Channels ("groups")

SELECT C.name, V.string_id, V.conversation_id 
  FROM "group" AS C
  LEFT JOIN message_table_schema_version AS V 
         ON C.group_id = V.conversation_id;

IMs

SELECT C.im_id AS ID, C.is_open, V.string_id
  FROM "im" AS C
  LEFT JOIN message_table_schema_version AS V 
         ON C.im_id = V.conversation_id;

MPIMs

SELECT C.mpim_id AS ID, C.is_open, V.string_id
  FROM "mpim" AS C
  LEFT JOIN message_table_schema_version AS V 
         ON C.mpim_id = V.conversation_id;

Selecting messages

Each conversation has an own message table.

SELECT message_id, text, blocks_json FROM "m_CCD4PUFCG"
 ORDER BY message_id DESC
 LIMIT 10; ! to get the newest 10 messages

Columns

• blocks_json is the new style JSON-formatted blocks content for Slack messages. text is the fallback if that is not available.
• text fallback, if blocks_json is not set
• attachments_json has the content of Slack "attachments". Those are not attachments like files, but things like bot generated content with color sidebars, or website previews. (blocks_json will supersede them, but they are still quite common)
• cache_created is the cache import timestamp of the message
• message_id is the Slack message identifier, which is composed of the message timestamp in the upper bits (when it was posted), but has extra fields, it is a bitfield structure that fits Int (63 bits). The timeline is sorted by that column.

Tools

The SQLite cache can be opened in database tools, like SQLiteBrowser. Or accessed w/ tools like Direct2SwiftUI ;-)