# Message History

Message History lets you save selected fields from MQTT messages to EMQX Tables. Create a mapping to choose an MQTT topic filter, a target table, and the fields to store.

EMQX Tables stores and queries data, but it does not automatically receive messages from an EMQX Broker deployment. Without Message History, you must manually configure a data integration connector, rule, and action. Message History sets up and manages this ingestion pipeline through a three-step mapping workflow.

With Message History, you can:

- Set up MQTT data storage without manually building a data integration pipeline.
- Preview recent records and check delivery metrics from the Broker deployment.
- Retain telemetry in EMQX Tables for SQL queries and trend analysis.
- Enable, disable, or delete individual mappings without deleting data already stored in EMQX Tables.

Message History is available only on the international site for Dedicated and Dedicated Flex deployments running EMQX v6.1.4 or later.

## How Message History Works

The mapping wizard has three steps:

- **Storage Location**: Select an EMQX Tables deployment in the same network as the Broker and a database. Select a Tables user and enter its password. Then set a retention period (TTL) and timestamp column name.
- **Message & Table Schema**: Enter an MQTT topic filter and target table, map message values to tag or field columns, and choose the timestamp source and precision.
- **Confirmation**: Review the storage location, topic, and table schema, then create the mapping.

After you select the storage location, Message History creates or reuses a connection to EMQX Tables. When you confirm the mapping, it creates a data integration rule and an EMQX Tables action. The rule selects values from messages that match the topic filter, and the action writes those values to the target table.

If the target table does not exist, the first successful write creates it with the selected TTL and timestamp column name. These settings do not change an existing table. Values in the first successful write determine the column types, so later messages must use compatible types. The message example only previews the schema; it does not create the table or write a record.

Only messages received while a mapping is enabled are stored. Message History does not backfill earlier MQTT traffic or replay stored messages to subscribers. For client connection and subscription events, use [Event History](./event_history.md) instead.

Each matching message consumes data integration TPS. Check your deployment's [data integration TPS](./metrics.md) capacity before creating a mapping.

## Before You Start

- Make sure that the Broker deployment is running.
- Prepare a running EMQX Tables deployment in the same network as the Broker. Tables deployments reached through a NAT Gateway or PrivateLink from another network are not eligible.
- Prepare an EMQX Tables database and a user that has access to it. The user must have the `SqlSelect` and `SqlInsert` privileges; the **Broker Integration** preset grants both. Have the user's password ready. See [Manage EMQX Tables Users](../emqx_tables/emqx_tables_user_management.md).

## Example: Store Sensor Readings

In this example, devices publish temperature and humidity readings to topics such as `sensors/sensor-001/telemetry`. The mapping stores readings from all sensors in the `sensor_history` table in the `public` database. It uses the Broker received time for the timestamp column, so the payload does not need a timestamp.

### Create the Mapping

1. In the Cloud Console, open the Broker deployment and select **Message History** in the left menu. Click **Map a Topic to a Table** if this is the first mapping, or **New Mapping** if mappings already exist.

2. Under **Storage Location**, select the EMQX Tables deployment, the `public` database, and the Tables user. Enter the user's password. Leave **Time-To-Live (TTL)** at `30 days` and the timestamp column name at `timestamp`. Click **Next** to create or reuse the connection.

   ![Configure the storage location](./_assets/message-history-storage-location.png)

3. Under **Message & Table Schema**, enter `sensors/+/telemetry` for **MQTT Topic** and `sensor_history` for **Table Name**. The `+` wildcard matches one topic level, so this filter includes readings from different sensor IDs.

4. Replace the **MQTT Message Example** with this payload:

   ```json
   {
     "device_id": "sensor-001",
     "temperature": 23.5,
     "humidity": 58.5
   }
   ```

5. In **Field Mapping**, set `payload.device_id` to the **Tag** role. Keep `payload.temperature` and `payload.humidity` as **Field** columns. Leave their target column names as `device_id`, `temperature`, and `humidity`.

   ::: tip

   After the first successful write, the table has a timestamp column named `timestamp`, a `device_id` tag for filtering by device, and `temperature` and `humidity` fields for the readings. At least one **Field** column is required, and target column names must be unique.

   :::

6. Keep **Timestamp Column** set to **Broker Received Time** and **Time Precision** set to **Milliseconds (ms)**. Click **Next**.

   ![Configure the message and table schema](./_assets/message-history-message-schema.png)

7. Review the destination, topic, and schema preview, then click **Confirm**. The mapping appears in the **Message History** list.

   ![Confirm the mapping settings](./_assets/message-history-confirmation.png)

### Publish and Query a Message

1. In the Broker deployment, open **Diagnostics** -> **MQTT Client**, connect, and publish the example payload to `sensors/sensor-001/telemetry`.
2. Return to **Message History** and expand the mapping row to preview recent records. Click the mapping **ID** to open its details and check the delivery metrics.
3. Open **Data Explorer** in the linked EMQX Tables deployment, select the `public` database, and run:

   ```sql
   SELECT * FROM sensor_history ORDER BY "timestamp" DESC LIMIT 10;
   ```

The query should return a row for `sensor-001` with the published temperature and humidity. See [Data Explorer](../emqx_tables/emqx_tables_data_explorer.md) for more query options.

![View mapping details and metrics](./_assets/message-history-mapping-details.png)

## Configure Other Message Formats

- **Topic filters**: Use MQTT `+` and `#` wildcards to capture multiple topics. A mapping stores only messages received while it is enabled; it does not copy earlier messages.
- **Message examples**: The Console detects fields and nested paths in a JSON object. A plain-text payload produces one `payload` field. Arrays are not supported. The example defines the columns; it does not sample messages received by the Broker.
- **Additional fields**: Use **Add Field** to map MQTT metadata such as `clientid`, `topic`, `qos`, `username`, or `peername`. Choose **Tag** for values you expect to filter or group by, and **Field** for measured values. Keep at least one Field column.
- **Message time**: To store when a device produced a reading, add a Unix timestamp or a date-time string such as `2026-09-23T08:00:00Z` to the example payload, then select that field under **Timestamp Column**. For a Unix timestamp, set **Time Precision** to match its unit. The Console converts a recognized RFC 3339 string to the selected precision. Otherwise, use **Broker Received Time**.
- **Existing connections**: If a Message History connection already uses the selected Tables deployment, database, and user, the Console reuses it. Its TTL and timestamp column name are read-only in the wizard. Those settings affect only tables first created through that connection; check the settings of any existing target table separately.

## Troubleshoot Setup and Writes

- If clicking **Next** does not create a connection, make sure that the Tables deployment is running in the same network as the Broker. Also check the Tables credentials and database access. Message History connections share the Broker's connector limit with Data Integration connectors.
- If no records appear, make sure that the mapping is enabled and that the published topic matches its MQTT topic filter. Messages published before the mapping was created are not copied into the table.
- Open the mapping details and check the **Success**, **Failed**, and **Dropped** metrics. A write can fail if the Tables deployment is unavailable, database access is insufficient, the timestamp is invalid, or a value does not match the table's column type.
- If the mapping was created for an existing table, inspect that table's timestamp column, TTL, and schema in EMQX Tables. The mapping's connection settings do not change an existing table.

## View and Manage Mappings

The **Message History** list shows each mapping's ID, MQTT topic, database, table, and enabled state.

- Expand a mapping row to preview up to 10 recent records.
- Click the mapping **ID** to view its destination, TTL, delivery metrics, and up to 100 recent records.
- Refresh the preview to load the latest records.
- Use **Data Explorer** for broader queries.

Message History queries EMQX Tables for each preview and orders the results by the timestamp column.

![Preview recent messages](./_assets/message-history-recent-messages.png)

Mappings cannot be edited after creation. You can enable, disable, or delete them:

- Use the switch in the **Enable** column to disable or enable a mapping. Disabling a mapping stops new writes but keeps data already stored in EMQX Tables.
- To delete a mapping, click its delete icon and enter the mapping **ID** in the confirmation dialog. Deleting a mapping does not delete the target table or stored data. If no other mapping uses its dedicated connection, that connection is also removed.