Signal Desktop — SQLCipher Database

Overview

Signal Desktop stores all message data in a SQLCipher-encrypted SQLite database at ~/Library/Application Support/Signal/sql/db.sqlite. The database is fully accessible once the decryption key is extracted from the adjacent config.json file. After decryption, the database is standard SQLite with a rich relational schema spanning messages, conversations, contacts, cryptographic session state, and application configuration.

SQLCipher 4 Encryption Parameters

Signal Desktop uses SQLCipher 4 with the following parameters:

Key Extraction from config.json

The decryption key is stored in ~/Library/Application Support/Signal/config.json as a 64-character hexadecimal string in the key field:

{
  "key": "a3f8c2d14e5b9f07d823a1c4b6e9f012345678901234567890abcdef12345678"
}

The key may optionally appear with a 0x prefix. When passing the key to SQLCipher, strip the prefix if present and format it as x'<64_hex_chars>'.

Key length validation: The raw key must be exactly 64 hexadecimal characters (representing 32 bytes / 256 bits). A key of any other length indicates a corrupted or non-standard configuration.

Opening the Database

With the sqlcipher CLI

-- Launch sqlcipher against the database file
-- (copy the file first to avoid modifying the original)
sqlcipher db.sqlite

-- Apply the decryption key
PRAGMA key = "x'a3f8c2d14e5b9f07d823a1c4b6e9f012345678901234567890abcdef12345678'";
PRAGMA cipher_page_size = 4096;

-- Verify decryption succeeded (should return a non-empty result)
SELECT count(*) FROM sqlite_master WHERE type = 'table';

With DB Browser for SQLite

1. Open DB Browser for SQLite
2. Select "Open Database" and choose the db.sqlite file
3. In the password dialog, select "Raw key" mode and enter 0x followed by the 64 hex chars from config.json

WAL Mode Consideration

Signal operates with SQLite WAL (Write-Ahead Logging) enabled. When copying the database for analysis, always copy all three files together:

• db.sqlite — main database file
• db.sqlite-wal — write-ahead log (may contain uncommitted or recently committed data)
• db.sqlite-shm — shared memory file (required for WAL recovery)

Failure to include the WAL file may result in missing recently written messages.

Database Tables

conversations

The central registry for all 1:1 and group conversations. See Conversations for the full column reference and analysis guidance.

messages

The primary message table. Contains all sent, received, and system messages. Key columns include id, conversationId, body, type, sent_at, received_at, sourceUuid, and isErased. See Messages for the full column reference.

sessions

The session table tracks active Signal Protocol sessions. Each session represents a Double Ratchet state with a specific contact or group. macfor collects session metadata (id, conversationId, timestamp) but does not extract the private ratchet state blob.

identityKeys

The identity key table is critical for safety number forensics. Each row represents the known public key for a contact. Key changes — recorded in the messages table as type = 'keychange' — indicate when a contact's identity changed, which can signal a new device installation, Signal reinstall, or potential account compromise.

preKeys

signedPreKeys

senderKeys

Sender keys are used for group messaging. Each member of a group distributes a sender key, enabling efficient group message decryption.

items

The items table is a key-value configuration store. Forensically relevant keys include registration state, linked device information, and application version.

Notable items keys:

Key SQL Queries

List All Tables

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

Count Messages by Conversation

SELECT
    c.name,
    c.e164,
    c.profileName,
    count(m.id) AS message_count
FROM conversations c
LEFT JOIN messages m ON m.conversationId = c.id
GROUP BY c.id
ORDER BY message_count DESC;

Full Message Timeline

SELECT
    datetime(m.sent_at / 1000, 'unixepoch') AS sent_utc,
    m.type,
    m.body,
    c.name AS conversation,
    m.sourceUuid AS sender_uuid
FROM messages m
JOIN conversations c ON c.id = m.conversationId
ORDER BY m.sent_at;

Identity Key Change History

SELECT
    datetime(sent_at / 1000, 'unixepoch') AS event_time,
    conversationId,
    sourceUuid,
    source
FROM messages
WHERE type = 'keychange'
ORDER BY sent_at;

Registration Information

SELECT id, json FROM items
WHERE id IN ('number_id', 'uuid_id', 'device_name', 'version')
ORDER BY id;

Contacts with Verification Status

SELECT
    id,
    publicKey,
    datetime(firstUse / 1000, 'unixepoch') AS first_use_utc,
    datetime(timestamp / 1000, 'unixepoch') AS last_updated_utc,
    CASE verified
        WHEN 0 THEN 'default'
        WHEN 1 THEN 'verified'
        WHEN 2 THEN 'unverified'
        ELSE 'unknown'
    END AS verification_status
FROM identityKeys
ORDER BY timestamp DESC;

Schema Stability

Signal Desktop is an Electron application with no guaranteed schema stability between releases. Signal's engineering team may add, rename, or remove columns in any version update. The macfor collector performs runtime column discovery using PRAGMA table_info() before querying each table, enabling it to handle both older and newer schema versions gracefully.

When running manual SQL queries, use PRAGMA table_info(<table_name>) to verify column availability before constructing queries that depend on specific columns.

Forensic Significance of Session and Identity Data

The session and identity key tables provide evidence that is not available from the message content alone:

• Session existence: An active session in the sessions table proves that the device successfully established an encrypted session with a contact, even if messages were deleted.
• Key change events: keychange records in the messages table are system-generated entries that cannot be deleted by the user through the normal Signal interface. They provide a timestamped record of when a contact's identity key changed.
• Verification history: The verified column in identityKeys records whether the user manually verified a contact's safety number — a deliberate act indicating deliberate trust establishment.
• Pre-key exhaustion: An empty preKeys table may indicate the account was re-registered or the Signal Desktop data directory was partially cleared.

References

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