Step 3: Decrypting the Database

Introduction

This document details the final stage of the WhatsApp Chat Analytics workflow, referred to as "Step 3: Analyze Your Chats". This phase occurs entirely within a local web application and allows users to upload, search, filter, and visualize their chat history. It's important to note that the database decryption itself is a prerequisite, performed in Step 1: Unlock Your Encrypted Backups. This analysis step consumes the resulting decrypted database (msgstore.db) and a chat list JSON file to provide a private, browser-based analytics experience.

The entire analysis process is self-contained on the user's machine, ensuring that private messages are never uploaded to any external server. The system uses a React frontend, leveraging sql.js to run a full SQLite engine in the browser, enabling complex queries directly on the user's data without a backend.

Sources: README.md:80-92, web/src/Pages/SearchSQLite/index.jsx, web/src/Pages/SearchSQLite/services/databaseService.js:12-16

Prerequisites

Before beginning the analysis, two key files must be generated from the preceding steps. These files are the primary inputs for the web application.

Sources: README.md:86-92, web/src/Pages/SearchSQLite/components/UploadStep.jsx:30-51, chatsExporter/whatsapp_db_android_exporter.py:73-75, chatsExporter/dumbWAChats.js:25-26

Web Application Workflow

The analysis is guided by a three-step user interface within the web application. The main component SearchSQLite manages the state and navigation between these steps.

Sources: web/src/Pages/SearchSQLite/index.jsx:13-17

This diagram illustrates the user flow through the web application's analysis steps.

Step 1: Upload Files

The user begins by uploading the two prerequisite files. The UploadStep component provides drag-and-drop areas for msgstore.db and model-storage-chat.json. Once both files are selected, the user can proceed to the next step.

• msgstore.db: Handled by handleMessagesFileUpload.
• model-storage-chat.json: Handled by handleChatFileUpload.

The useFileUpload hook manages the state for each file. Upon successful upload of the database file, it is processed by the DatabaseService.

Sources: web/src/Pages/SearchSQLite/components/UploadStep.jsx, web/src/Pages/SearchSQLite/index.jsx:19-30

Step 2: Search & Filter

In this step, the user defines the search criteria. The UI provides several filters:

• Date Range: A DateRangePicker allows selecting a start and end date. The available range is dynamically determined by querying the database for the earliest and latest message timestamps.
• Message Type: A CheckPicker to filter by types like 'image', 'video', 'audio', etc.
• Message Direction: A SelectPicker to choose between 'Sent', 'Received', or 'All'.
• Chat: A SelectPicker populated with the chat names from the uploaded JSON file, allowing the user to focus on a specific conversation.

These selections are managed by the useMessageSearch hook and are used to construct a dynamic SQL query.

Sources: web/src/Pages/SearchSQLite/components/SearchStep.jsx, web/src/Pages/SearchSQLite/index.jsx:33-46

Step 3: View Results

After the search is executed, the ResultsStep component displays the analytics. This includes:

• A summary of the chat.
• An ActivityChart and MessagesOverTimeChart for visualizing message patterns.
• A StatisticsDisplay component showing detailed metrics like total messages, message type breakdown, and audio duration statistics.

The data for these components is prepared by the MessageDataProcessor class.

Sources: web/src/Pages/SearchSQLite/components/ResultsStep.jsx:13-33

Data Loading and Processing

The core of the application's client-side processing involves loading the SQLite database into memory and parsing the chat list.

The following sequence diagram shows how a user-uploaded .db file is loaded and initialized.

Database Initialization

The databaseService.js is responsible for all SQLite operations.

1. initializeFromFile(arrayBuffer): This static method is the entry point. It takes an ArrayBuffer of the .db file.
2. initSqlJs: It initializes the sql.js library, loading the required WebAssembly file (sql-wasm.wasm).
3. new SQL.Database(uint8Array): It creates a new in-memory database instance from the file's byte array.
4. new DatabaseService(database): It returns a new DatabaseService instance containing the initialized database object.

Sources: web/src/Pages/SearchSQLite/services/databaseService.js:12-18

Chat List Parsing

The fileService.js handles the model-storage-chat.json file. The parseChatFile method reads the JSON string, parses it, and transforms the raw data into a clean list of chat objects, each containing an id, name, and type. This list is then used to populate the chat selection dropdown in the search step.

Sources: web/src/Pages/SearchSQLite/services/fileService.js:40-55

Querying the Database

The queryBuilder.js module is central to the search functionality. It dynamically constructs a SQL query string based on the user's filter selections from the "Search & Filter" step.

Query Construction

The buildMessagesQuery function assembles the query. The logic is as follows:

This flowchart shows how filters are combined into a final SQL query.

The final query selects various fields from the message table and joins with other tables like chat and jid to get all necessary information.

A snippet of the query construction:

// web/src/Pages/SearchSQLite/utils/queryBuilder.js:8-14
export function buildMessagesQuery({
  startDate,
  endDate,
  selectedChat,
  selectedMediaType,
  selectedMessagesDirection,
  chatName
}) {
  const whereConditions = [];

  // Date range condition
  whereConditions.push(
    `Strftime('%Y-%m-%d', mssg.timestamp / 1000, 'unixepoch') BETWEEN '${startDate}' AND '${endDate}'`
  );

Sources: web/src/Pages/SearchSQLite/utils/queryBuilder.js:8-14

Data Analysis and Visualization

Once the SQL query returns the raw message data, the MessageDataProcessor class takes over. This class acts as a facade for various specialized processors that calculate statistics and format data for the charts.

This class diagram shows the structure of the MessageDataProcessor and its relationship with sub-processors.

The MessageDataProcessor orchestrates calls to these processors and caches the results to avoid redundant computations. For example, getMessagesStatistics() internally calls getMessagesPerDay() and then uses a StatisticsProcessor instance to compute detailed metrics like message streaks, total counts, and audio durations. The final processed data is then passed as props to React components like StatisticsDisplay and ActivityChart for rendering.

Sources: web/src/analytics/MessageDataProcessor.js:108-116, web/src/Pages/SearchSQLite/components/ResultsStep.jsx:13-16

Conclusion

The analysis step is the culmination of the project, providing a user-friendly interface to explore WhatsApp chat data in a completely private and secure manner. By leveraging sql.js for in-browser database querying and a structured set of React components and data processors, it transforms the raw, decrypted database into meaningful insights and visualizations. The modular design, separating UI, data services, query logic, and analytics processing, creates a robust and extensible system for local chat analysis.

Sources: README.md:10-15, web/public/index.html:10-13