Signal Desktop — Messages

Overview

The messages table in the Signal Desktop SQLCipher database is the primary forensic artifact for message content recovery. It contains all sent and received messages, system events (key changes, group membership changes, profile updates), call history entries, and expired disappearing message records. Once the database is decrypted, the body column contains fully readable plaintext message text.

Unlike Apple Messages (chat.db), Signal does not maintain a separate deleted messages reference table. When a message is deleted, the row is removed from the messages table entirely. The presence of an isErased = 1 flag indicates the message body was cleared (expiration timer reached or manual erase) but the message row still exists.

Timestamp Format

All timestamps in the Signal Desktop messages table use Unix milliseconds — the number of milliseconds since January 1, 1970 00:00:00 UTC. This is the JavaScript/Electron standard epoch.

This differs from Apple's Core Data epoch (used in Messages, FaceTime, Contacts, and Calendar), which counts seconds or nanoseconds from January 1, 2001.

Conversion formula:

Unix seconds = sent_at / 1000

-- Convert sent_at to human-readable UTC
SELECT datetime(sent_at / 1000, 'unixepoch') AS sent_utc
FROM messages;

Key timestamp columns:

received_at is particularly valuable for timeline reconstruction as it reflects the local device clock at the moment of receipt, independent of the sender's clock accuracy.

Messages Table Schema

The json column is particularly rich — Signal stores the complete JavaScript message object as a JSON blob alongside the relational columns. This includes quoted message references, emoji reactions, body range mentions, and link preview metadata that do not appear in dedicated columns.

Message Types

The type column identifies the nature of each row:

System-type messages (anything other than incoming and outgoing) are valuable forensically because they are generated by the Signal application and cannot be edited by the user. They create a timestamped audit trail of security events, group membership changes, and profile updates.

View-Once Messages

Signal supports view-once media (sometimes called "disappearing media") where an image or video can only be viewed once by the recipient. After viewing:

• The body column is cleared
• The isErased column is set to 1
• The associated attachment file may be deleted from the attachments.noindex directory

The message row itself remains in the messages table, providing evidence that a view-once media exchange occurred, including the timestamp and participants.

Disappearing Messages

Disappearing messages are controlled by expireTimer (per conversation, inherited by messages) and the expirationStartTimestamp column:

• expireTimer = 0 means no disappearing timer is set for this message
• expireTimer > 0 contains the duration in seconds (e.g., 3600 = 1 hour, 86400 = 1 day)
• expirationStartTimestamp records when the timer started counting — typically when the recipient viewed the message
• When the timer expires, Signal clears the body column and sets isErased = 1
• The message row is retained even after erasure

Forensic implication: The presence of a message with isErased = 1 proves the message existed and establishes the approximate timeframe (via sent_at and expirationStartTimestamp). Investigators can calculate when the timer expired as expirationStartTimestamp + (expireTimer * 1000).

Reactions

In modern Signal Desktop versions, emoji reactions are stored in the json column of the target message rather than in a separate table. The JSON structure for reactions:

{
  "reactions": [
    {
      "emoji": "👍",
      "fromId": "11111111-2222-3333-4444-555555555555",
      "targetTimestamp": 1700000000000,
      "timestamp": 1700000001234
    }
  ]
}

Quoted Messages (Replies)

When a user replies to a specific message, the reply message contains a quote reference in the json column:

{
  "quote": {
    "id": 1700000000000,
    "authorUuid": "11111111-2222-3333-4444-555555555555",
    "text": "The original message text"
  }
}

The id field in a quote is the sent_at millisecond timestamp of the quoted message — not a UUID. This allows correlation back to the original message.

Key SQL Queries

All Messages with Conversation Context

SELECT
    datetime(m.sent_at / 1000, 'unixepoch') AS sent_utc,
    datetime(m.received_at / 1000, 'unixepoch') AS received_utc,
    m.type,
    m.body,
    c.name AS conversation_name,
    c.e164 AS phone_number,
    m.sourceUuid AS sender_uuid,
    m.hasAttachments,
    m.isErased
FROM messages m
JOIN conversations c ON c.id = m.conversationId
ORDER BY m.sent_at;

Outgoing Messages Only

SELECT
    datetime(sent_at / 1000, 'unixepoch') AS sent_utc,
    body,
    conversationId
FROM messages
WHERE type = 'outgoing'
ORDER BY sent_at;

Erased Messages (Disappearing Message Evidence)

SELECT
    id,
    conversationId,
    datetime(sent_at / 1000, 'unixepoch') AS sent_utc,
    datetime(expirationStartTimestamp / 1000, 'unixepoch') AS expiration_started_utc,
    expireTimer,
    datetime((expirationStartTimestamp + expireTimer * 1000) / 1000, 'unixepoch') AS expired_at_utc,
    hasAttachments,
    hasVisualMediaAttachments
FROM messages
WHERE isErased = 1
ORDER BY sent_at;

Security Events (Key Changes, Group Changes)

SELECT
    datetime(sent_at / 1000, 'unixepoch') AS event_utc,
    type,
    conversationId,
    sourceUuid,
    body
FROM messages
WHERE type IN ('keychange', 'group-v2-change', 'profile-change', 'verified-change', 'change-number-notification')
ORDER BY sent_at;

Messages with Attachments

SELECT
    m.id,
    datetime(m.sent_at / 1000, 'unixepoch') AS sent_utc,
    m.type,
    m.body,
    m.hasAttachments,
    m.hasFileAttachments,
    m.hasVisualMediaAttachments,
    c.name AS conversation
FROM messages m
JOIN conversations c ON c.id = m.conversationId
WHERE m.hasAttachments = 1
ORDER BY m.sent_at DESC;

Message Volume Timeline (Daily)

SELECT
    date(sent_at / 1000, 'unixepoch') AS day,
    sum(CASE WHEN type = 'outgoing' THEN 1 ELSE 0 END) AS sent,
    sum(CASE WHEN type = 'incoming' THEN 1 ELSE 0 END) AS received
FROM messages
WHERE type IN ('incoming', 'outgoing')
GROUP BY day
ORDER BY day;

Full-Text Search in Message Bodies

SELECT
    datetime(sent_at / 1000, 'unixepoch') AS sent_utc,
    type,
    body,
    conversationId
FROM messages
WHERE body LIKE '%keyword%'
ORDER BY sent_at;

Forensic Analysis Notes

• body column is plaintext after decryption: Once SQLCipher is opened, the body column contains fully readable message text. There is no per-message encryption layer.
• sourceUuid vs source: The sourceUuid field (Signal UUID) is the modern identifier. The source field (phone number) is a legacy field populated for older messages. For messages received from newer Signal clients, source may be empty while sourceUuid is populated.
• Call history: Calls are recorded as type = 'call-history' rows in the messages table, not in a separate table. These records include the call direction and outcome.
• System messages cannot be deleted by users: Rows with types such as keychange, group-v2-change, and verified-change are system-generated events that users cannot delete through the Signal Desktop interface, providing a reliable audit trail.
• Story messages: Signal Stories (introduced in Signal Desktop 6.0) appear in the messages table with type = 'story' and include a storyId column reference. Stories expire after 24 hours but the database row may persist.

References

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