Keep short-interval measurements for some time, then aggregate to daily min-max

[jackjansen]

Having short-interval measurements is very handy when you're debugging some issue (such as t the battery depleting below the cutoff level), but having it over long periods will make the esp32 run out of storage.

Asked claude.ai (after analysing the problems for a couple of days, with the current measurements), comments below., And mine below that.

[jackjansen]

Datalogger: tiered storage spec

Context

The accuklein and accugroot devices are ESP32-based battery voltage loggers running the iotsa/lissabon firmware. They wake from deep sleep, measure battery voltage, optionally sync via WiFi/NTP, and go back to sleep. Data is stored on the device and served as a CSV at http://accuklein.local/datalogger/data.csv (columns: t, ts, v — ISO timestamp, Unix timestamp, voltage in V).

The current implementation stores all readings at the same interval in a flat CSV that grows indefinitely. This spec describes a tiered storage scheme that keeps fine-grained recent data and compresses older data.

Goal

Keep enough resolution to diagnose events (undervoltage cutoffs, charging start/stop times) while preventing unbounded storage growth on the ESP32.

Proposed tiers

Tier 1 — raw, 10-minute interval, last 2 weeks

Keep all readings as-is for approximately the last 14 days. This is the working diagnostic window. At 10-minute intervals, 14 days = ~2016 readings, which is manageable.

Tier 2 — hourly summary, last 3 months

For data older than 2 weeks but less than ~3 months: compress to one record per hour. Each hourly record should store:

• date — date (YYYY-MM-DD)
• hour — hour of day (0–23)
• min_v — minimum voltage in that hour
• max_v — maximum voltage in that hour
• min_t — Unix timestamp of the minimum (to know when it occurred)
• max_t — Unix timestamp of the maximum (to know when it occurred)
• n — number of raw readings that were compressed into this record

Tier 3 — daily summary, older than 3 months

For data older than ~3 months: compress to one record per day. Each daily record should store:

• date — date (YYYY-MM-DD)
• min_v — minimum voltage that day
• max_v — maximum voltage that day
• min_t — Unix timestamp of the minimum
• max_t — Unix timestamp of the maximum
• n — number of readings compressed

The time-of-min and time-of-max are important: they encode when charging started (time of daily max, typically mid-morning to early afternoon) and when the evening load drop happened (time of daily min, typically late night or early morning). Without these, the daily record loses most of its diagnostic value.

Storage format

Two or three separate CSV files are probably simplest:

• data.csv — tier 1, raw readings, existing format (t,ts,v)
• data_hourly.csv — tier 2, hourly summaries
• data_daily.csv — tier 3, daily summaries

This avoids mixing formats in one file and makes it easy to fetch only the tier you need.

Compression trigger

Compression should run as a background task, triggered either:

• On each wake cycle, after the measurement is stored (cheap check: if oldest raw record is > 14 days old, compress it), or
• Once per day at a fixed time (e.g. just after midnight NTP sync).

The per-wake approach is simpler and avoids a separate scheduled task.

Compression algorithm (sketch)

On each wake:

1. Read the oldest raw record from data.csv.
2. If its timestamp is > 14 days ago:
   a. If it is > 3 months ago: append a daily summary record to data_daily.csv (accumulating across all readings for that calendar day before writing), then delete those raw records.
   b. If it is between 14 days and 3 months ago: append an hourly summary record to data_hourly.csv, then delete those raw records.
3. Repeat until the oldest remaining raw record is within the 14-day window.

In practice, on most wake cycles there will be nothing to compress (the oldest record is recent). The loop only does real work after a 14-day boundary is crossed.

Serving the data

The existing /datalogger/data.csv endpoint can continue serving tier 1. Add:

• /datalogger/data_hourly.csv — tier 2
• /datalogger/data_daily.csv — tier 3

Or alternatively, a single /datalogger/data.csv?tier=raw|hourly|daily query parameter if that fits the iotsa routing model better.

Notes

• The NTP timestamp (ts) is more reliable than the ISO string (t) for ordering and arithmetic, since the clock drifts between NTP syncs. Use ts for all compression boundaries.
• Duplicate/out-of-order records (caused by NTP corrections) already exist in the raw data. The compression step should sort by ts before summarising, and deduplicate on ts if exact duplicates exist.
• n (reading count) in compressed records is useful for detecting gaps — an hourly record with n=1 instead of the expected 6 indicates the device was asleep or offline for most of that hour.

[jackjansen]

• I'm not 100% sure the hourly format is needed.
• I think if you're requesting the daily data (and the hourly data, if we want it) the missing data should be aggregated from what we still have only in raw.