What is the proper way to put historical data in Sensor?

Development
1

For the past month, I have been attempting to fix this integration, which provides a synthetic sensor for water consumption in Home Assistant.

The problem is that Aiguas (the water provider) reports water consumption with random lag, which can be 2-4 hours, but it could also be 24-48 hours.

When writing to statistics, we put the same value into the state and sum columns. And I use SensorStateClass.TOTAL for the state class.
And everything works fine until the new hour.

At the beginning of every hour, HA itself inserts a record into the statistics table where state is the last state it found in the DB and sum is 0. Which completely breaks the statistic.
I have no idea why it is doing it. I read the documentation on the Sensor and concluded that the current solution is technically correct. But still, we are getting big negative values at the end of the hour.

I tried different approaches (almost all of them: no state class, another state class, filling in gaps, etc), none of them seems to be working.
Current solution fills in gaps in the data from Aiguas with the last known state of the Sensor, and it works fine, but at the beginning of every hour, I am getting this error:

Blocked attempt to insert duplicated statistic rows...
...
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: statistics.metadata_id, statistics.start_ts
[SQL: INSERT INTO statistics (created, created_ts, metadata_id, start, start_ts, mean, mean_weight, min, max, last_reset, last_reset_ts, state, sum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id]
[parameters: (None, 1763406010.22059, 367, None, 1763402400.0, None, None, None, None, None, None, 595.187, 0.19299999999998363)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

As you can see, it tries to insert zero value even though I always keep state == sum.

And this is where I need community help. What is the proper way of doing this integration, considering that data can have a significant lag?

Can I say somehow to HA: I manage the history myself, please don’t touch it?

2

If you’re writing statistics manually to the DB, I think you don’t want to create a sensor, you just want an external statistic with no associated entity, something like import:water_consumption.

I think if you try to create an entity, that entity will also be writing to the statistics database, which will clash with your imports. So keep the imports entirely separate from any real entity.

3

Thanks.
I’ll try.
Is this is a recommended way of doing things in such a case?