Facebook Messenger — Lightspeed Database

Overview

The Facebook Messenger Desktop forensic artifact is built on Project Lightspeed, announced by Meta in 2020 as a ground-up rewrite of Messenger's architecture. Lightspeed uses SQLite as the universal data layer — all UI state, message caching, contact data, sync queuing, and application logic operate through a single integrated SQLite database. The database is managed by MSYS (Meta's cross-platform C library), which executes stored procedures within SQLite and handles bidirectional server sync.

The result is a database with hundreds of tables that covers every aspect of the Messenger experience in a single file. Because MSYS is the same codebase across iOS, macOS, Android, and Windows, the schema is largely consistent across platforms — forensic research from iOS or Windows Messenger artifacts transfers directly to the macOS Catalyst variant.

Database File Naming

The primary database follows a consistent naming convention across all platforms:

lightspeed-<facebook_user_id>.db

Where <facebook_user_id> is the numeric Facebook User ID (FBID) of the logged-in account. Example:

lightspeed-100098765432101.db

The FBID is embedded in the filename and can be extracted without opening the database, enabling immediate account attribution.

Full Path on macOS Catalyst

~/Library/Containers/com.facebook.archon/Data/Library/Application Support/Messenger/Partitions/<partition_id>/lightspeed-<fbid>.db

The <partition_id> is a per-installation identifier (often default or a UUID). The collector uses a glob pattern to discover all databases across all partitions:

Data/Library/Application Support/Messenger/Partitions/*/lightspeed-*.db
Data/Library/Application Support/Messenger/lightspeed-*.db
Data/Library/Application Support/lightspeed-*.db

Companion Files

Always collect the WAL and SHM files alongside the main database:

Omitting the WAL file may result in missing messages written in the most recent sync cycle.

Supporting Databases

Depending on the Messenger version, additional databases may exist alongside the main Lightspeed database:

The macfor collector checks for both standalone databases and embedded tables within the main Lightspeed file.

Encryption Detection

Method

Read the first 16 bytes of the database file. A valid SQLite database always begins with the magic string SQLite format 3 followed by a null byte (0x00):

Offset 0, length 16: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00
                     S  Q  L  i  t  e     f  o  r  m  a  t     3  \0

An encrypted database shows random bytes at offset 0. There is no valid SQLite header.

Manual Verification

# Hex dump the first 16 bytes
xxd -l 16 lightspeed-100098765432101.db

# Or read as ASCII (plaintext shows "SQLite format 3")
head -c 15 lightspeed-100098765432101.db

Encryption Timeline

The transition is not a hard cutoff. The exact relationship between the E2EE transport rollout and local database encryption requires hands-on verification on post-2023 installations. Pre-2023 databases that were not updated after December 2023 may remain plaintext.

Key Storage (Encrypted Databases)

If the database is encrypted, the key is not stored in a plaintext configuration file alongside the database (unlike Signal Desktop). The encryption key may be:

• Derived from the user's Meta authentication token
• Stored in the macOS Keychain under a service name associated with com.facebook.archon
• Embedded in the application binary or loaded from a Meta-controlled key service

Check the macOS Keychain for entries with service name com.facebook.archon or labels containing "Facebook" or "Messenger". A Keychain entry containing binary data of 16, 32, or 64 bytes may be the database key.

Table Discovery

The MSYS schema uses hundreds of tables whose names may vary across Messenger versions. The collector discovers tables dynamically by querying sqlite_master:

-- List all user tables in the database
SELECT name, type
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

Table names follow several naming conventions:

• Bare names: messages, threads, contacts, calls
• MSYS-prefixed: msys_messages, msys_threads
• Collection index tables: collection_index_contacts, collection_index_calls
• Blob storage: __blob_storage, blob_storage

The collector searches for each table type using a priority-ordered candidate list and falls back to substring matching against sqlite_master if no exact match is found.

Inspect Available Columns

Before running SQL queries, inspect the actual columns available in each table:

-- List all columns in the messages table
PRAGMA table_info(messages);

-- List all columns in the thread_summaries table
PRAGMA table_info(thread_summaries);

Column names may vary. The collector maps logical fields to actual column names using a candidate list for each field (e.g., the message body column may be text, body, content, message_text, or snippet).

Table Schemas

The following schemas are based on cross-platform analysis (Windows and iOS Messenger). Exact column names on macOS Catalyst should be verified with PRAGMA table_info() before running queries.

messages

The core message table. Contains one row per message with content, sender identity, timestamps, and thread reference.

Additional columns may include attachment references, reaction counts, and message status flags.

threads / thread_summaries

Conversation thread metadata. May be present as threads, thread_summaries, or msys_threads.

folder_type Values

The folder_type field in thread records indicates the conversation state:

Pending threads (folder_type = 2) represent message requests from people the user does not follow. These may be forensically significant as evidence that a contact attempted to communicate.

contacts

Contact records extracted from the user's Messenger contact list.

search_tokens

Records user search activity within the Messenger search interface. Each row represents a name or token the user searched for, along with the associated FBID if the search resolved to a specific user.

This table provides evidence of deliberate user searches for specific people. It persists independently of message history and may be accessible even when messages are encrypted.

calls

Voice and video call history.

attachments

Media attachment metadata. Contains references to locally cached files and Facebook CDN URLs.

client_activity_log

Application activity timestamps showing when the Messenger client performed sync operations, refreshed data, or was actively in use.

This table provides evidence of application usage patterns independently of message content.

library_metadata

Sync state tracking. Records the last time each data category was checked against the server.

Timestamp Format

All timestamps in the Lightspeed/MSYS database use Unix epoch milliseconds (integer milliseconds since January 1, 1970, 00:00:00 UTC). This is consistent with the Windows and iOS variants, and follows the cross-platform MSYS convention.

Timestamp Conversion

-- Convert Unix milliseconds to ISO 8601 in SQLite
SELECT datetime(timestamp_ms / 1000, 'unixepoch') AS timestamp_utc
FROM messages;

-- With millisecond precision (using strftime)
SELECT strftime('%Y-%m-%dT%H:%M:%f', timestamp_ms / 1000.0, 'unixepoch') AS timestamp_utc
FROM messages;

Note: If you encounter floating-point timestamps (values in the range 978307200–2000000000), these may be Core Data epoch seconds rather than Unix milliseconds. Core Data epoch conversion: datetime(core_data_ts + 978307200, 'unixepoch').

Key SQL Queries

Verify Database is Readable

-- Should return a non-empty list of table names if database is plaintext
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

Full Message Timeline

SELECT
    datetime(timestamp_ms / 1000, 'unixepoch') AS sent_utc,
    sender_name,
    sender_id   AS sender_fbid,
    thread_id,
    text        AS message_body,
    msg_type
FROM messages
ORDER BY timestamp_ms DESC;

All Conversation Threads

SELECT
    thread_id,
    thread_name,
    CASE folder_type
        WHEN 1 THEN 'active'
        WHEN 2 THEN 'pending'
        WHEN 5 THEN 'empty'
        ELSE 'unknown'
    END AS folder_status,
    datetime(last_message_date / 1000, 'unixepoch') AS last_message_utc,
    last_message_preview,
    unread_count,
    participant_count
FROM thread_summaries
ORDER BY last_message_date DESC;

Messages in a Specific Thread

SELECT
    datetime(timestamp_ms / 1000, 'unixepoch') AS sent_utc,
    sender_name,
    sender_id AS sender_fbid,
    text       AS message_body
FROM messages
WHERE thread_id = '<thread_id>'
ORDER BY timestamp_ms;

Contact Network

SELECT
    fbid,
    name,
    friend_status,
    phone_number
FROM contacts
ORDER BY name;

Search Activity (Who Was Searched For)

SELECT
    token         AS search_query,
    target_fbid,
    datetime(last_searched / 1000, 'unixepoch') AS last_searched_utc
FROM search_tokens
ORDER BY last_searched DESC;

Call History

SELECT
    call_id,
    participants,
    call_type,
    datetime(timestamp_ms / 1000, 'unixepoch') AS call_start_utc,
    duration AS duration_seconds
FROM calls
ORDER BY timestamp_ms DESC;

Application Activity Log

SELECT
    activity_type,
    datetime(timestamp / 1000, 'unixepoch') AS activity_utc
FROM client_activity_log
ORDER BY timestamp DESC;

Identify Account FBID from Database Contents

-- The database filename contains the FBID. Within the database,
-- look for the account owner's FBID in the thread key references:
SELECT DISTINCT fbid_from_thread_key AS account_fbid
FROM thread_summaries
LIMIT 10;

Pending Message Requests

-- Identify conversation threads that are in pending/request state
SELECT
    thread_id,
    thread_name,
    datetime(last_message_date / 1000, 'unixepoch') AS last_message_utc
FROM thread_summaries
WHERE folder_type = 2;

Dynamic Schema Handling

Because the MSYS schema varies across Messenger versions, investigators should not assume fixed column names. The recommended approach for manual analysis:

1. List all tables: SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
2. Inspect columns for each table of interest: PRAGMA table_info(<table_name>);
3. Adjust query column names to match actual schema

The macfor collector handles this automatically using runtime column discovery before each query.

Forensic Significance

Thread enumeration proves communication network. Even if message content is not cached locally, the presence of threads proves the account communicated with specific contacts. The fbid_from_thread_key field links each thread directly to a Facebook User ID.

Pending threads reveal blocked communications. Threads with folder_type = 2 represent message requests that the user never accepted. These may document contact attempts from parties the user was attempting to avoid or ignore.

Unread count as engagement indicator. A thread with a high unread count indicates the user has not read recent messages in that conversation. Combined with the last_message_date, this helps establish whether the user was actively using a particular conversation.

CDN URLs in attachments are traceable. Attachment records referencing scontent.*.fbcdn.net CDN URLs may be usable with legal process to Meta to retrieve the original media even if the local cache has been cleared.

Sync timestamps bound investigation window. The library_metadata and client_activity_log tables record when data was last synchronized. If the last sync was one week before evidence collection, the local cache reflects data from up to one week before collection — earlier messages may not be present.

References

• Project LightSpeed: Rewriting the Messenger codebase — Meta Engineering
• Launching Default E2EE on Messenger — Meta
• ForensAFE — iOS Facebook Messenger Artifacts
• ForensAFE — Facebook Messenger Windows App
• Facebook Messenger Desktop Overview
• Messenger Pay