Keep Home Assistant history for years

A guide for the Home Assistant community. Every command and output below comes from a run against the service on 2026-10-04.

Home Assistant's recorder keeps states for 10 days by default (purge_keep_days), and long-term statistics keep only hourly mean, min and max for sensors with a state_class. This guide sends the state changes of your sensors, thermostats, lights, blinds and shutters, locks, doorbells and trackers to a table you own, once a minute, with nothing but rest_command and one automation in configuration.yaml: no add-on, no custom component, no relay. At the end you have a table with one row per entity per minute in which it changed (time, entity, state, the numbers that matter for its kind: temperature, setpoint, position, brightness, and every attribute as JSON) that you can query with SQL for as long as you keep it, including how much your solar panels made and your car used each day.

The table is an Apache Iceberg table in the open Parquet format, stored in Germany: DuckDB, Spark, Trino, Snowflake and the account page's SQL panel read it, and it never depends on Home Assistant's database.

What you need

• Home Assistant with access to configuration.yaml (the File editor or Studio Code Server add-on, or the config folder of a container install). The run behind this page used Home Assistant 2026.9.4 in the official container image, ghcr.io/home-assistant/home-assistant:stable, with the built-in demo integration (three thermostats, five covers, six lights, four locks, a button event, three trackers) and template sensors standing in for a solar inverter and a car charger. Entities from any integration (Nest, Hue, Somfy, Nuki, Tesla, an inverter) are the same domains with the same attributes, and are sent the same way.
• A LakehouseBox account and the lhbox command line (agent setup), on any computer: it is needed once, to create the table and the address Home Assistant posts to. Home Assistant itself needs nothing installed.
• DuckDB 1.5.5 or newer on that computer to query (lhbox duckdb opens it with your catalog attached).

1. Create a catalog and the table

lhbox login
lhbox catalog create home
lhbox table create home.ha.states --column event_time:timestamptz:required --column entity_id:string:required --column state:string --column value:double --column unit:string --column friendly_name:string --column current_temperature:double --column target_temperature:double --column current_humidity:double --column hvac_action:string --column position:double --column brightness:double --column attributes:string

One row is one state of one entity:

The six middle columns are null for the entities they do not apply to. They are columns, not only keys inside attributes, because they are what you plot and average: a column is typed (no casting), and Parquet keeps its min and max per file, so WHERE target_temperature > 22 skips files instead of parsing JSON in every row.

2. Create the address Home Assistant posts to

A sink with a device route is a private HTTPS address that accepts a JSON body with no headers to set and commits what it receives to the table every five minutes:

lhbox sink create --catalog home --table ha.states --name ha_states --device --save ha-sink.json

The address is shown once and written to ha-sink.json (mode 0600) instead of the terminal. Read it with jq -r .device_url ha-sink.json: it looks like https://in.lakehousebox.com/d/<token>. The address is the password: anyone who has it can add rows to this table (and nothing else). Put it in Home Assistant's secrets.yaml, not in configuration.yaml:

# secrets.yaml
lakehousebox_url: https://in.lakehousebox.com/d/<token>

If it leaks, lhbox sink device-token ha_states --save ha-sink.json replaces it and the old one stops working at once.

3. Add the rest_command and the automation

Add this to configuration.yaml exactly as it is (the run used it verbatim). Every minute the automation looks for the entities of the listed domains whose state or attributes changed in the minute that just ended, and the rest_command posts them as one JSON array. A minute with no change posts nothing.

rest_command:
  lakehousebox:
    url: !secret lakehousebox_url
    method: post
    content_type: "application/json"
    timeout: 20
    payload: >-
      {%- set ns = namespace(rows=[]) -%}
      {%- for s in states | selectattr('domain', 'in', domains)
                          | selectattr('entity_id', 'search', include)
                          | rejectattr('entity_id', 'search', exclude) -%}
        {%- if since < s.last_updated.timestamp() <= until -%}
          {%- set a = s.attributes -%}
          {%- set ns.rows = ns.rows + [{
            "event_time": s.last_updated.isoformat(),
            "entity_id": s.entity_id,
            "state": s.state,
            "value": s.state | float(none),
            "unit": a.get('unit_of_measurement'),
            "friendly_name": a.get('friendly_name'),
            "current_temperature": a.get('current_temperature') | float(none),
            "target_temperature": a.get('temperature') | float(none)
                                  if s.domain in ['climate', 'water_heater'] else none,
            "current_humidity": a.get('current_humidity') | float(none),
            "hvac_action": a.get('hvac_action'),
            "position": a.get('current_position') | float(none),
            "brightness": ((a.get('brightness') or 0) / 2.55) | round(0)
                          if s.domain == 'light' and s.state in ['on', 'off'] else none,
            "attributes": a | to_json
          }] -%}
        {%- endif -%}
      {%- endfor -%}
      {{ ns.rows | to_json }}

automation:
  - id: lakehousebox_history
    alias: Send state changes to LakehouseBox
    mode: queued
    triggers:
      - trigger: time_pattern
        minutes: "/1"
    variables:
      domains: ["sensor", "binary_sensor", "climate", "cover", "light", "lock", "event", "device_tracker"]
      include: ""
      exclude: '^sensor\.(backup_|sun_)'
      until: "{{ trigger.now.timestamp() }}"
      since: "{{ until - 60 }}"
    conditions:
      - condition: template
        value_template: >
          {{ states | selectattr('domain', 'in', domains)
                    | selectattr('entity_id', 'search', include)
                    | rejectattr('entity_id', 'search', exclude)
                    | map(attribute='last_updated') | map('as_timestamp')
                    | select('>', since) | select('<=', until) | list | count > 0 }}
    actions:
      - action: rest_command.lakehousebox
        data:
          domains: "{{ domains }}"
          include: "{{ include }}"
          exclude: "{{ exclude }}"
          since: "{{ since }}"
          until: "{{ until }}"
        response_variable: sent
      - if: "{{ sent.status not in [200, 202] }}"
        then:
          - action: system_log.write
            data:
              level: warning
              message: "LakehouseBox answered {{ sent.status }}: {{ sent.content }}"

If your configuration.yaml already has an automation: line (the default automation: !include automations.yaml), paste the automation into the automations editor in YAML mode instead, without the automation: line. The warning step uses system_log.write; the run's minimal configuration had to load it with a system_log: line. Choose what to send explains domains, include and exclude.

Restart Home Assistant (a new rest_command needs a restart). Each minute with a change, the log shows the post and the answer when homeassistant.components.rest_command logs at debug; from the run (the address masked):

2026-10-04 09:21:00.249 DEBUG (MainThread) [homeassistant.components.rest_command] Calling post https://in.lakehousebox.com/d/<token> with headers: {'Content-Type': 'application/json'} and payload: b'[{"event_time":"2026-10-04T07:20:32.204847+00:00","entity_id":"device_tracker.demo_paulus",...
2026-10-04 09:21:00.492 DEBUG (MainThread) [homeassistant.components.rest_command] Success. Url: https://in.lakehousebox.com/d/<token>. Status code: 202. ...

The body is a JSON array, one object per entity; that post carried 12 (6,144 bytes: two trackers, the four energy stand-ins, a thermostat, a cover, the button event, two lights and a lock). Three of them, with attributes in full to show what each kind carries (the setpoint the run's own demo automation had just changed):

[
 {"event_time": "2026-10-04T07:20:30.192805+00:00", "entity_id": "climate.hvac", "state": "cool", "value": null,
  "unit": null, "friendly_name": "Hvac", "current_temperature": 22.0, "target_temperature": 22.0,
  "current_humidity": 54.2, "hvac_action": "cooling", "position": null, "brightness": null,
  "attributes": "{\"hvac_modes\":[\"off\",\"heat\",\"cool\",\"auto\",\"dry\",\"fan_only\"],\"min_temp\":7,\"max_temp\":35,\"min_humidity\":30,\"max_humidity\":99,\"target_humidity_step\":5,\"fan_modes\":[\"on_low\",\"on_high\",\"auto_low\",\"auto_high\",\"off\"],\"swing_modes\":[\"auto\",\"1\",\"2\",\"3\",\"off\"],\"swing_horizontal_modes\":[\"auto\",\"rangefull\",\"off\"],\"current_temperature\":22,\"temperature\":22.0,\"target_temp_high\":null,\"target_temp_low\":null,\"current_humidity\":54.2,\"humidity\":67.4,\"fan_mode\":\"on_high\",\"hvac_action\":\"cooling\",\"swing_mode\":\"off\",\"swing_horizontal_mode\":\"auto\",\"friendly_name\":\"Hvac\",\"supported_features\":943}"},
 {"event_time": "2026-10-04T07:20:33.209055+00:00", "entity_id": "cover.hall_window", "state": "open", "value": null,
  "unit": null, "friendly_name": "Hall Window", "current_temperature": null, "target_temperature": null,
  "current_humidity": null, "hvac_action": null, "position": 30.0, "brightness": null,
  "attributes": "{\"is_closed\":false,\"current_position\":30,\"friendly_name\":\"Hall Window\",\"supported_features\":15}"},
 {"event_time": "2026-10-04T07:20:30.195991+00:00", "entity_id": "light.ceiling_lights", "state": "on", "value": null,
  "unit": null, "friendly_name": "Ceiling Lights", "current_temperature": null, "target_temperature": null,
  "current_humidity": null, "hvac_action": null, "position": null, "brightness": 20,
  "attributes": "{\"min_color_temp_kelvin\":2000,\"max_color_temp_kelvin\":6535,\"supported_color_modes\":[\"color_temp\",\"hs\"],\"color_mode\":\"color_temp\",\"brightness\":51,\"color_temp_kelvin\":2631,\"hs_color\":[28.55,67.974],\"rgb_color\":[255,164,82],\"xy_color\":[0.532,0.388],\"friendly_name\":\"Ceiling Lights\",\"supported_features\":0}"}
]

Home Assistant's brightness is 0 to 255 (51 here); the payload turns it into percent (20).

4. Check that rows arrive

The rows land in the table at the sink's next roll, within five minutes:

lhbox sink get ha_states

From the run, 29 minutes after the first post (an excerpt, two fields of the answer):

"received": {"batches_total": 30, "bytes_total": 207364, "rows_total": 421, "rows_rejected_total": 0},
"last_roll": {"at": "2026-10-04T07:32:03.724Z", "batches": 6, "rows": 70, "rejected_rows": 0, "seconds": 0.28},

rows_rejected_total above zero means a row did not fit the table (a key the table lacks, a value of the wrong type); lhbox sink get names where the rejected rows are kept, with the reason.

5. Query it

lhbox duckdb --catalog home

opens DuckDB with the catalog attached as home. All outputs below are from the run; the times are shown in the computer's time zone (Madrid, +02).

What arrived, per domain:

SELECT split_part(entity_id, '.', 1) AS domain, count(DISTINCT entity_id) AS entities, count(*) AS rows,
       min(event_time) AS first, max(event_time) AS last
FROM home.ha.states GROUP BY domain ORDER BY domain;

┌────────────────┬──────────┬───────┬───────────────────────────────┬───────────────────────────────┐
│     domain     │ entities │ rows  │             first             │             last              │
├────────────────┼──────────┼───────┼───────────────────────────────┼───────────────────────────────┤
│ binary_sensor  │        3 │     6 │ 2026-10-04 09:02:48.389571+02 │ 2026-10-04 09:03:33.635255+02 │
│ climate        │        3 │    30 │ 2026-10-04 09:02:48.391003+02 │ 2026-10-04 09:31:30.18749+02  │
│ cover          │        5 │    37 │ 2026-10-04 09:02:48.39323+02  │ 2026-10-04 09:31:36.22016+02  │
│ device_tracker │        3 │    62 │ 2026-10-04 09:02:48.381112+02 │ 2026-10-04 09:31:32.199813+02 │
│ event          │        2 │    32 │ 2026-10-04 09:02:48.359269+02 │ 2026-10-04 09:31:32.197903+02 │
│ light          │        6 │    58 │ 2026-10-04 09:02:48.39452+02  │ 2026-10-04 09:31:30.190397+02 │
│ lock           │        4 │    36 │ 2026-10-04 09:02:48.394971+02 │ 2026-10-04 09:31:32.195681+02 │
│ sensor         │       16 │   160 │ 2026-10-04 09:02:48.260073+02 │ 2026-10-04 09:32:00.176532+02 │
└────────────────┴──────────┴───────┴───────────────────────────────┴───────────────────────────────┘

Thermostats (climate): the room temperature against the setpoint, and what the thermostat was doing:

SELECT event_time, entity_id, state, hvac_action, current_temperature, target_temperature, current_humidity
FROM home.ha.states WHERE entity_id LIKE 'climate.%' ORDER BY event_time DESC, entity_id LIMIT 6;

┌───────────────────────────────┬──────────────┬─────────┬─────────────┬─────────────────────┬────────────────────┬──────────────────┐
│          event_time           │  entity_id   │  state  │ hvac_action │ current_temperature │ target_temperature │ current_humidity │
├───────────────────────────────┼──────────────┼─────────┼─────────────┼─────────────────────┼────────────────────┼──────────────────┤
│ 2026-10-04 09:31:30.18749+02  │ climate.hvac │ cool    │ cooling     │                22.0 │               21.0 │             54.2 │
│ 2026-10-04 09:29:30.199203+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               23.0 │             54.2 │
│ 2026-10-04 09:27:30.189651+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               18.0 │             54.2 │
│ 2026-10-04 09:26:30.184751+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               22.0 │             54.2 │
│ 2026-10-04 09:25:30.184194+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               18.0 │             54.2 │
│ 2026-10-04 09:24:30.189893+02 │ climate.hvac │ cool    │ cooling     │                22.0 │               21.0 │             54.2 │
└───────────────────────────────┴──────────────┴─────────┴─────────────┴─────────────────────┴────────────────────┴──────────────────┘

The run changed the setpoint of climate.hvac once a minute; the demo thermostats do not change their room temperature by themselves. A real one (Nest, tado, Netatmo) moves current_temperature and hvac_action on its own, and each change is a row. climate.ecobee is in heat_cool mode, where Home Assistant gives a range (target_temp_low, target_temp_high in attributes) instead of one setpoint, so target_temperature is null.

Covers (blinds, shutters, garage doors): the position over time:

SELECT event_time, entity_id, state, position
FROM home.ha.states WHERE entity_id = 'cover.hall_window' ORDER BY event_time DESC LIMIT 5;

┌───────────────────────────────┬───────────────────┬─────────┬──────────┐
│          event_time           │     entity_id     │  state  │ position │
├───────────────────────────────┼───────────────────┼─────────┼──────────┤
│ 2026-10-04 09:31:36.22016+02  │ cover.hall_window │ open    │     70.0 │
│ 2026-10-04 09:30:36.208295+02 │ cover.hall_window │ open    │     10.0 │
│ 2026-10-04 09:29:37.236123+02 │ cover.hall_window │ open    │     70.0 │
│ 2026-10-04 09:28:33.2207+02   │ cover.hall_window │ closed  │      0.0 │
│ 2026-10-04 09:27:32.198656+02 │ cover.hall_window │ open    │     30.0 │
└───────────────────────────────┴───────────────────┴─────────┴──────────┘

Lights: brightness in percent, 0 when off:

SELECT event_time, entity_id, state, brightness
FROM home.ha.states WHERE entity_id IN ('light.bed_light', 'light.ceiling_lights') ORDER BY event_time DESC, entity_id LIMIT 6;

┌───────────────────────────────┬──────────────────────┬─────────┬────────────┐
│          event_time           │      entity_id       │  state  │ brightness │
├───────────────────────────────┼──────────────────────┼─────────┼────────────┤
│ 2026-10-04 09:31:30.190397+02 │ light.ceiling_lights │ on      │       50.0 │
│ 2026-10-04 09:31:30.189496+02 │ light.bed_light      │ on      │       71.0 │
│ 2026-10-04 09:30:30.192124+02 │ light.ceiling_lights │ on      │       10.0 │
│ 2026-10-04 09:30:30.186702+02 │ light.bed_light      │ off     │        0.0 │
│ 2026-10-04 09:29:30.202582+02 │ light.ceiling_lights │ on      │       20.0 │
│ 2026-10-04 09:29:30.201591+02 │ light.bed_light      │ on      │       71.0 │
└───────────────────────────────┴──────────────────────┴─────────┴────────────┘

Locks: how often each one was unlocked, and when last:

WITH l AS (
  SELECT entity_id, event_time, state,
         lag(state) OVER (PARTITION BY entity_id ORDER BY event_time) AS before
  FROM home.ha.states WHERE entity_id LIKE 'lock.%'
)
SELECT entity_id, count(*) FILTER (state = 'unlocked' AND before IS DISTINCT FROM 'unlocked') AS times_unlocked,
       max(event_time) FILTER (state = 'unlocked') AS last_unlocked, arg_max(state, event_time) AS state_now
FROM l GROUP BY entity_id ORDER BY entity_id;

┌────────────────────────────┬────────────────┬───────────────────────────────┬───────────┐
│         entity_id          │ times_unlocked │         last_unlocked         │ state_now │
├────────────────────────────┼────────────────┼───────────────────────────────┼───────────┤
│ lock.front_door            │              9 │ 2026-10-04 09:31:32.195681+02 │ unlocked  │
│ lock.kitchen_door          │              1 │ 2026-10-04 09:03:33.64072+02  │ unlocked  │
│ lock.openable_lock         │              0 │ NULL                          │ locked    │
│ lock.poorly_installed_door │              1 │ 2026-10-04 09:03:33.640754+02 │ unlocked  │
└────────────────────────────┴────────────────┴───────────────────────────────┴───────────┘

Events (event: doorbells, buttons, camera motion and person events where the integration provides them): the state of an event entity is the time of its last event, and the kind of event is the event_type attribute:

SELECT event_time, entity_id, json_extract_string(attributes, '$.event_type') AS event_type
FROM home.ha.states WHERE entity_id LIKE 'event.%' AND state <> 'unknown' ORDER BY event_time DESC LIMIT 3;

┌───────────────────────────────┬─────────────────────────┬────────────┐
│          event_time           │        entity_id        │ event_type │
├───────────────────────────────┼─────────────────────────┼────────────┤
│ 2026-10-04 09:31:32.197903+02 │ event.push_button_press │ pressed    │
│ 2026-10-04 09:30:32.197349+02 │ event.push_button_press │ pressed    │
│ 2026-10-04 09:29:32.210712+02 │ event.push_button_press │ pressed    │
└───────────────────────────────┴─────────────────────────┴────────────┘

Trackers (device_tracker: phones, cars): the state is the zone (home, not_home, a zone name) and the position is in attributes:

SELECT event_time, entity_id, state,
       json_extract(attributes, '$.latitude')::double AS lat, json_extract(attributes, '$.longitude')::double AS lon
FROM home.ha.states WHERE entity_id LIKE 'device_tracker.%' ORDER BY event_time DESC, entity_id LIMIT 3;

┌───────────────────────────────┬──────────────────────────────────┬──────────┬──────────┬───────────┐
│          event_time           │            entity_id             │  state   │   lat    │    lon    │
├───────────────────────────────┼──────────────────────────────────┼──────────┼──────────┼───────────┤
│ 2026-10-04 09:31:32.199813+02 │ device_tracker.demo_anne_therese │ not_home │ 32.87041 │ 117.21973 │
│ 2026-10-04 09:31:32.199531+02 │ device_tracker.demo_paulus       │ not_home │ 32.86445 │ 117.22187 │
│ 2026-10-04 09:30:32.199241+02 │ device_tracker.demo_anne_therese │ not_home │ 32.86539 │ 117.22031 │
└───────────────────────────────┴──────────────────────────────────┴──────────┴──────────┴───────────┘

A tracker sends a row each time its position changes, so this is a location history: leave device_tracker out of domains if you do not want one.

Energy: solar, house and car per day

Power sensors (W) from an inverter, a smart meter and a car charger are plain sensor rows. Because a row is sent only when the value changes, each reading holds until the next one, and the energy of a day is the sum of each reading times the seconds until the next (W·s ÷ 3,600,000 = kWh). The run's three stand-in sensors were sensor.solar_power, sensor.house_consumption and sensor.car_charging_power; use your own entity ids:

WITH p AS (
  SELECT entity_id, event_time, value,
         lead(event_time) OVER (PARTITION BY entity_id ORDER BY event_time) AS next_time
  FROM home.ha.states
  WHERE entity_id IN ('sensor.solar_power', 'sensor.house_consumption', 'sensor.car_charging_power')
)
SELECT date_trunc('day', event_time)::date AS day,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.solar_power') / 3.6e6, 2) AS solar_kwh,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.house_consumption') / 3.6e6, 2) AS house_kwh,
       round(sum(value * epoch(next_time - event_time)) FILTER (entity_id = 'sensor.car_charging_power') / 3.6e6, 2) AS car_kwh,
       round(sum(epoch(next_time - event_time)) FILTER (entity_id = 'sensor.solar_power') / 3600, 2) AS hours_covered
FROM p WHERE next_time IS NOT NULL GROUP BY day ORDER BY day;

┌────────────┬───────────┬───────────┬─────────┬───────────────┐
│    day     │ solar_kwh │ house_kwh │ car_kwh │ hours_covered │
├────────────┼───────────┼───────────┼─────────┼───────────────┤
│ 2026-10-04 │      1.36 │      0.68 │    1.56 │          0.49 │
└────────────┴───────────┴───────────┴─────────┴───────────────┘

hours_covered is how much of the day the readings span: the run lasted 29 minutes (0.49 hours), so these are the kWh of those minutes, not of a day (the stand-ins drew random values, which is why the car outdid the house). Days are cut in the time zone of the computer running DuckDB; SET TimeZone = 'Europe/Madrid'; first fixes it to yours. Two things make this an estimate: within a minute only the last reading is kept (a load that switches on and off inside one minute is missed), and if Home Assistant is down for an hour the last reading before the outage counts for the whole hour.

Where the integration has an energy counter (state_class: total_increasing, in kWh: an inverter's daily or total yield, a meter's import, a car's energy added), the counter is exact: the day's energy is its largest value minus its smallest (or its largest alone, for a counter that resets at midnight). From the run's stand-in counter, sensor.solar_energy:

SELECT date_trunc('day', event_time)::date AS day, round(max(value) - min(value), 3) AS solar_kwh
FROM home.ha.states WHERE entity_id = 'sensor.solar_energy' GROUP BY day ORDER BY day;

┌────────────┬───────────┐
│    day     │ solar_kwh │
├────────────┼───────────┤
│ 2026-10-04 │     1.288 │
└────────────┴───────────┘

The stand-in counter and the stand-in power sensor drew their values independently, so the two solar figures of the run do not agree; on a real installation both describe the same panels.

Choose what to send

A home with hundreds of entities sends only what changed, but some entities change all the time and are of no use later (the sun's position, signal strengths, uptime counters, update entities). Three variables of the automation choose:

• domains: the entity domains to send. The run used sensor, binary_sensor, climate, cover, light, lock, event and device_tracker; add switch, fan, valve, water_heater, media_player or any other domain the same way. A domain not listed sends nothing.
• exclude: a regular expression; entities whose entity_id it finds are left out. The run's '^sensor\.(backup_|sun_)' dropped Home Assistant's backup sensors and the sun sensors. To exclude nothing, use '^$' (it matches no entity_id). For several patterns, join them with |: '^sensor\.(sun_|.*_rssi$|.*_uptime$)|^update\.'.
• include: a regular expression an entity_id must match to be sent; "" (empty) matches every entity. To keep only a list, name them: '^(climate\.|lock\.|sensor\.(solar_power|house_consumption|car_charging_power)$)'. With this include (and exclude: '^$'), the run's posts carried only the three thermostats, the four locks and the three power sensors: 10 entities in the minute after a restart, then 5 a minute.

Keep the three in single quotes in YAML (a backslash inside double quotes is an escape). An entity is sent when its domain is in domains, include finds it, and exclude does not.

Limits and gotchas

• At most one row per entity per minute. The automation sends the current state of each entity that changed in the last minute, so a sensor that updates every 10 seconds gives one row a minute (its last value), not six. In the run the stand-in sensors updated every 20 seconds and each kept one row a minute. Triggering more often (for example seconds: "/30" with since: "{{ until - 30 }}") gives finer history; the run used one minute only. A sink accepts 6 posts a minute sustained (bursts of 30), and beyond that answers 429 rate_limited.
• 256 KiB per post. In the run a row averaged about 500 bytes with its attributes (6,006 bytes for 12 rows a minute); the largest was a thermostat at 980 bytes, because attributes carries its lists of modes. A post of 256 KiB therefore holds roughly 500 changed entities. If you have more, narrow what is sent (Choose what to send), or replace a | to_json with none in the payload: the column stays and is null, and a row shrinks to about 300 bytes.
• A restart sends every entity. When Home Assistant starts, every entity gets a new last_updated, so the first minute after a restart posts all the entities of the listed domains at once (in the run, 42 entities and 19,906 bytes in one post). That is the minute that comes closest to 256 KiB; if it is answered 413, narrow domains or exclude (the restart minute is lost, the minutes after it are not affected).
• Keep the template in the rest_command payload. Building the rows in the automation and passing them as a variable does not work: Home Assistant re-reads a rendered variable as a Python value, and the body sent that way was not JSON (400 unreadable_body). Passing only domains, include, exclude, since and until and rendering the JSON in the payload with to_json is what works.
• A failed post is not retried. rest_command does not queue: when the internet or the service is down, those minutes are not sent. Home Assistant's own recorder still has them for its retention period.
• Every key in the payload must be a column. A key the table does not have rejects the row (unknown_field) rather than being dropped silently. To add one, add the column to the table first. A table created with the seven columns of this page's first version (before the thermostat, cover and light columns) rejects the rows of this payload: create the table with all thirteen columns and a sink for it.
• Five minutes to appear. Rows are committed every 5 minutes by default (--roll-seconds on lhbox sink create, at least 60); what has been posted but not yet committed shows as lag in lhbox sink get.

Next

• Ingest: sinks and the device route for the roll, rejects and the device route's rules.
• DuckDB for connecting outside lhbox duckdb, and the other engines that read the table.
• Scheduled SQL to build daily summaries (energy per day, thermostat hours heating) from this table on a schedule.