Introduction

I have been experimenting with solar panels, Home Assistant and my electricity meter lately. This French meter from the utility is called Linky. It provides a small power supply (130 mW) and a serial link hidden via amplitude modulation.
To avoid using batteries, I built a prototype that harvests this power, demodulates the serial link, and feeds the data into an nRF52 development kit. It works well enough that I never bothered turning this messy setup into a proper PCB.

However, every now and then—despite CRC checks—I get an erroneous reading that messes up the Home Assistant Energy panel. Here’s how to fix incorrect statistics in the Home Assistant database.

Preparation

Before editing the database, it is strongly recommended to stop Home Assistant to avoid concurrent access that could corrupt the database.

1. Connect to the system running Home Assistant
2. Install SQLite3 if needed. For example, on Debian-based systems: apt install sqlite3
3. Locate the home-assistant_v2.db database file in Home-Assistant data folder and create a backup

Open the database for editing

Open the database using SQLite and set a more readable output mode:

$ sqlite3 home-assistant_v2.db`
# Then enter the commands
sqlite> .header on
sqlite> .mode column

Find the data id

Identify Home Assitant’s internal identifier, it is called metadata_id. Replace easf02 with Home-Assistant’s Entity ID you are looking for.
Using LIKE '%PARTIAL_ENTITY_STRING% allows for easier search by matching any string containing PARTIAL_ENTITY_STRING

sqlite> SELECT id, statistic_id FROM statistics_meta WHERE statistic_id LIKE '%easf01%';
id  statistic_id            
--  ------------------------
6   sensor.linky_easf01     
15  sensor.linky_easf01_cost

In this case id = 6 is the one we want to fix, and id = 15 is the associated cost.

Search for erroneous data

We let’s ask the database for any sudden jump in the data, e.g change greater than 10,000 Wh. Adjust metadata_id = 6 and the threshold (> 10000) as needed.

WITH ordered_stats AS (
  SELECT
    id,
    created_ts,
    sum,
    metadata_id,
    LAG(sum) OVER (PARTITION BY metadata_id ORDER BY created_ts) AS previous_sum,
    LAG(created_ts) OVER (PARTITION BY metadata_id ORDER BY created_ts) AS previous_created_ts
  FROM statistics
  WHERE metadata_id = 6
)
SELECT
  id,
  datetime(created_ts, 'unixepoch', 'localtime') as date,
  ABS(sum - previous_sum) AS difference
FROM ordered_stats
WHERE ABS(sum - previous_sum) > 10000 ORDER BY id DESC LIMIT 5;

In a nutshell, this query:

1. Selects statistics data for the entity and associates each value with the previous one.
2. Displays the five most recent entries where the difference between consecutive values exceeds the threshold.

Example result:

id      date                 difference
------  -------------------  ----------
229351  2025-08-18 14:00:10  20676793.0
38711   2025-01-28 12:00:10  20020401.0

Looks like we found the 20.6 MWh spike reported in the Energy panel :-)

Update the value

Since Home Assistant records cumulative sums, we need to subtract the erroneous difference from all entries starting from the anomaly. Use the following query:

UPDATE statistics SET sum = sum - 20676793.0 WHERE metadata_id = 6 and id >= 229351;

Replace 20676793.0 and 229351 with your actual values.

Once done, exit SQLite by pressing Ctrl + D. The database is now fixed, and the energy panel displays accurate values!