Database Schema (`msgstore.db`)
Relevant source files
The following files were used as context for generating this wiki page:- web/src/Pages/SearchSQLite/utils/queryBuilder.js
- web/src/Pages/SearchSQLite/services/databaseService.js
- chatsExporter/whatsapp_db_android_exporter.py
- web/src/Pages/SearchSQLite/hooks/useMessageSearch.js
- web/src/Pages/SearchSQLite/components/UploadStep.jsx
- web/src/analytics/processors/statisticsProcessor.js
The msgstore.db file is a SQLite database that contains the decrypted chat history from a WhatsApp Android backup. It serves as the central data source for all analysis, searching, and visualization features within the web application. The entire system is designed to operate locally on the user's machine, with the web front-end loading and querying this database file directly in the browser using sql.js.
The lifecycle of the database begins with its creation from an encrypted .crypt15 backup file using the chatsExporter Python script. Once decrypted, the user uploads the msgstore.db file into the web application. The application then initializes an in-memory database instance and provides an interface for users to filter and explore their message history. All queries are constructed and executed client-side, ensuring user data remains private.
Database Lifecycle
The msgstore.db file is not part of the repository but is generated and used by the application in a specific workflow. This process ensures that encrypted user data is handled securely on the user's own machine.
The diagram below illustrates the end-to-end flow from the encrypted backup to in-browser analysis.
Schema Overview
The application does not interact with the entire database schema but only a subset of tables and columns required for searching and analytics. The relationships between these tables are inferred from the LEFT JOIN statements in the main query builder function.
The following Entity-Relationship diagram shows the key tables and their connections as utilized by the application.
Key Tables
The following tables are explicitly referenced in the SQL queries executed by the web application.
message Table
This is the primary table, containing the core data for each message.
| Column | Inferred Type | Description |
|---|---|---|
_id | INTEGER | Primary key for the message. Used in joins with message_media, message_forwarded, and message_mentions. |
chat_row_id | INTEGER | Foreign key linking to the chat._id table to identify the conversation. |
sender_jid_row_id | INTEGER | Foreign key linking to the jid._id table to identify the message sender. |
from_me | INTEGER | A boolean flag (0 or 1) indicating if the message was sent by the user. 1 for sent, 0 for received. |
timestamp | INTEGER | The message timestamp, stored as a Unix epoch in milliseconds. |
message_type | INTEGER | An integer code representing the type of message (text, image, audio, etc.). |
starred | INTEGER | A boolean flag (0 or 1) indicating if the message is starred. |
Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:47-67, web/src/analytics/processors/statisticsProcessor.js:70
chat Table
This table links a message to a specific chat conversation.
| Column | Inferred Type | Description |
|---|---|---|
_id | INTEGER | Primary key for the chat. |
jid_row_id | INTEGER | Foreign key linking to the jid._id table to get the chat's identifier. |
Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:62
jid Table
The jid (Jabber ID) table stores unique identifiers for users and groups. It is joined twice in the main query: once to get the chat's ID and once to get the message sender's ID.
| Column | Inferred Type | Description |
|---|---|---|
_id | INTEGER | Primary key for the JID entry. |
user | TEXT | The user or group identifier string (e.g., a phone number). |
Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:18, 63, 64
message_media Table
This table contains metadata for messages that have media content.
| Column | Inferred Type | Description |
|---|---|---|
message_row_id | INTEGER | Foreign key linking to message._id. |
media_duration | INTEGER | The duration of the media in seconds, used specifically for audio messages. |
Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:59, 65, web/src/analytics/processors/statisticsProcessor.js:66
Data Interaction and Querying
The web application interacts with the msgstore.db file through a service-oriented architecture in the browser.
Database Service
The DatabaseService class is a wrapper around sql.js. It is responsible for:
- Initializing the database from an
ArrayBufferof the uploaded.dbfile. - Executing arbitrary SQL queries and returning results as an array of objects.
A key method in this service is getDateRange, which finds the earliest and latest message timestamps to populate the date range picker in the UI.
// web/src/Pages/SearchSQLite/services/databaseService.js:73-76
const query = `
SELECT min(Strftime('%Y-%m-%d', message.timestamp / 1000, 'unixepoch')) AS start,
max(Strftime('%Y-%m-%d', message.timestamp / 1000, 'unixepoch')) AS end
FROM main.message
WHERE message.message_type <> 7;
`;
Sources: web/src/Pages/SearchSQLite/services/databaseService.js:1-91
Dynamic Query Construction
The buildMessagesQuery function in queryBuilder.js is responsible for dynamically constructing the main SQL query based on user-selected filters. It assembles a WHERE clause based on:
- Start and end dates.
- A specific chat (
jid.user). - Message media type (
message.message_type). - Message direction (
message.from_me).
The query also uses a CASE statement to map the integer message_type to a human-readable string.
Message Type Mapping
The following table shows the mapping from the message_type integer to the string representation used in the application.
message_type | Type String |
|---|---|
| 0 | text |
| 1 | image |
| 2 | audio |
| 3 | video |
| 4 | contact |
| 5 | location |
| 9 | document |
| 20 | sticker |
| 66 | poll |
| other | unknown |
Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:51-60
Search Execution Flow
The following sequence diagram details the process from a user initiating a search to the results being displayed.
Summary
The msgstore.db schema is fundamental to the whatsapp-chat-analytics project. The application's architecture is cleverly designed to leverage a client-side SQL engine (sql.js) to query a user-provided, locally-decrypted database. This approach provides powerful data analysis capabilities while maintaining strict user privacy. The queries focus on a select group of tables—message, chat, jid, and message_media—to extract timestamps, sender information, and message content types, which are then processed to generate insightful statistics and visualizations.