Signal Desktop — Conversations & Contacts

Overview

The conversations table in the Signal Desktop SQLCipher database is the central registry for all 1:1 and group conversations. Each row represents either an individual contact or a group chat. The table serves as the primary source for contact identity information — phone numbers, Signal UUIDs, profile names, and group membership lists — all stored locally regardless of whether the contact exists in the macOS Contacts app.

For identity key forensics (safety number verification and key change history), the companion identityKeys table is equally important and is covered at the end of this article.

Conversations Table Schema

Columns Available via the json Column

Some frequently needed fields are stored inside the json column rather than as dedicated columns. The collector extracts these automatically:

Conversation Types

The type column distinguishes two conversation categories:

Private (type = 'private')

Each row represents a 1:1 conversation with a single contact. The contact is identified by uuid (primary) and e164 (phone number, secondary). Both identifiers may be present, or only the UUID if the contact has not registered a phone number.

Group (type = 'group')

Each row represents a group chat. The groupId field contains a base64-encoded group identifier. The groupVersion field indicates the group protocol version: version 1 is the legacy single-device group format; version 2 is the modern encrypted group format with server-side membership management.

Group member information is stored in the json column under the membersV2 key as an array of objects:

{
  "membersV2": [
    { "uuid": "11111111-2222-3333-4444-555555555555", "role": 2 },
    { "uuid": "66666666-7777-8888-9999-aaaaaaaaaaaa", "role": 1 }
  ]
}

Member roles:

• 1 = standard member
• 2 = administrator

Contact Identity Fields

Signal Desktop maintains its own contact registry independently of the macOS Contacts app. The following fields together constitute the full identity record for a contact:

The uuid field is the most reliable long-term identifier. Signal UUIDs do not change when a contact reinstalls Signal or changes their phone number (after the initial migration). The e164 phone number may change, and when it does, Signal records a change-number-notification event in the messages table.

Verification Status

The verified column in the identityKeys table (not the conversations table) records whether the local user has verified a contact's safety number. The conversations table does not directly expose verification status, but it can be joined to identityKeys via the uuid field.

Verification values:

• 0 = default (not explicitly verified or unverified)
• 1 = verified (user manually confirmed the safety number)
• 2 = unverified (user explicitly marked as unverified after a key change)

Manually verified contacts (verified = 1) indicate deliberate trust establishment. Contacts marked as unverified (verified = 2) indicate the local user saw a safety number mismatch and chose not to accept it.

Blocked Contacts

The blocked field in the json column indicates whether a contact has been blocked. Blocked contacts cannot send messages to the blocking user. The presence of a blocked contact in the conversations table confirms that the blocking user was previously in communication with that contact (blocking requires a conversation to have existed).

-- Extract blocked contacts from the json column
SELECT
    id,
    e164,
    uuid,
    profileName,
    profileFamilyName,
    json_extract(json, '$.blocked') AS is_blocked
FROM conversations
WHERE json_extract(json, '$.blocked') = 1;

Disappearing Message Timers

Each conversation may have a default disappearing message timer that applies to all new messages in that conversation. This is stored in the json column under expireTimer (in seconds):

SELECT
    name,
    e164,
    uuid,
    json_extract(json, '$.expireTimer') AS expire_timer_seconds
FROM conversations
WHERE json_extract(json, '$.expireTimer') > 0;

Timer values are set per conversation. A non-zero value does not mean all messages have been erased — it means new messages are subject to automatic deletion after the specified interval.

Key SQL Queries

All Private Contacts

SELECT
    id,
    e164,
    uuid,
    profileName,
    profileFamilyName,
    name,
    about,
    datetime(active_at / 1000, 'unixepoch') AS last_active_utc,
    lastMessage,
    unreadCount
FROM conversations
WHERE type = 'private'
ORDER BY active_at DESC;

All Group Conversations

SELECT
    id,
    name,
    groupId,
    groupVersion,
    datetime(active_at / 1000, 'unixepoch') AS last_active_utc,
    lastMessage,
    unreadCount
FROM conversations
WHERE type = 'group'
ORDER BY active_at DESC;

Group Members

-- Extract member UUIDs and roles from group conversations
-- Then join back to conversations to get contact details
SELECT
    g.name AS group_name,
    g.id AS group_id,
    m.value AS member_json,
    json_extract(m.value, '$.uuid') AS member_uuid,
    json_extract(m.value, '$.role') AS member_role,
    c.profileName AS member_profile_name,
    c.profileFamilyName AS member_profile_family,
    c.e164 AS member_phone
FROM conversations g,
     json_each(json_extract(g.json, '$.membersV2')) m
LEFT JOIN conversations c ON c.uuid = json_extract(m.value, '$.uuid')
WHERE g.type = 'group'
ORDER BY g.name, member_role DESC;

Contacts with Disappearing Message Timers

SELECT
    name,
    e164,
    uuid,
    json_extract(json, '$.expireTimer') AS expire_timer_seconds
FROM conversations
WHERE json_extract(json, '$.expireTimer') > 0
ORDER BY expire_timer_seconds;

Contact Communication Summary

SELECT
    c.name,
    c.profileName,
    c.profileFamilyName,
    c.e164,
    c.uuid,
    count(m.id) AS total_messages,
    sum(CASE WHEN m.type = 'outgoing' THEN 1 ELSE 0 END) AS sent,
    sum(CASE WHEN m.type = 'incoming' THEN 1 ELSE 0 END) AS received,
    datetime(max(m.sent_at) / 1000, 'unixepoch') AS most_recent_utc
FROM conversations c
LEFT JOIN messages m ON m.conversationId = c.id
    AND m.type IN ('incoming', 'outgoing')
WHERE c.type = 'private'
GROUP BY c.id
ORDER BY most_recent_utc DESC;

identityKeys Table

The identityKeys table tracks the Curve25519 public identity key for each known contact. This table is the foundation for Signal's safety number feature — the safety number displayed in the Signal app is derived from the combination of the local user's identity key and the contact's identity key stored here.

Key Change Forensics

Every time a contact's identity key changes (new device, Signal reinstall, linked device removal), Signal records the event in two places:

1. identityKeys table: The row is updated with the new public key and timestamp
2. messages table: A system message with type = 'keychange' is inserted into the conversation

The keychange event in the messages table is particularly valuable because it persists even after the identity key has been overwritten in identityKeys. By combining both tables, investigators can reconstruct the full key change history.

-- Full identity key and change history
SELECT
    ik.id AS contact_identifier,
    c.profileName,
    c.e164,
    datetime(ik.firstUse / 1000, 'unixepoch') AS key_first_seen_utc,
    datetime(ik.timestamp / 1000, 'unixepoch') AS key_last_updated_utc,
    CASE ik.verified
        WHEN 0 THEN 'default'
        WHEN 1 THEN 'verified'
        WHEN 2 THEN 'unverified'
        ELSE 'unknown'
    END AS verification_status,
    count(kc.id) AS key_change_events
FROM identityKeys ik
LEFT JOIN conversations c ON c.uuid = ik.id
LEFT JOIN messages kc ON kc.sourceUuid = ik.id AND kc.type = 'keychange'
GROUP BY ik.id
ORDER BY ik.timestamp DESC;

Forensic Analysis Notes

• Profile data persists after contact deletion: Signal stores profile names, about text, and phone numbers locally in the conversations table. Even if a contact removes themselves from the local device's address book, their Signal profile data remains in the conversations table for as long as the conversation history exists.
• UUID is more stable than phone number: The uuid field is the authoritative Signal identifier. Phone numbers (e164) can be changed by the user and Signal will record a change-number-notification event in the messages table when this occurs.
• Group membership history: Signal does not maintain a historical record of group membership changes in the conversations table — only the current member list is stored in membersV2. Historical membership changes are recorded as group-v2-change events in the messages table.
• Blocked contacts reveal prior contact: A blocked contact entry confirms the blocking user was in prior communication with that contact at some point.
• lastMessage preview: The lastMessage column contains a plaintext preview of the most recent message. This preview may be populated even if the full message has been deleted from the messages table, potentially recovering content that would otherwise be unavailable.

References

• Signal Desktop Source Code
• Signal Protocol Technical Documentation
• SQLCipher Documentation