v1.0.11 - Real schema, minute table SQL, lag fix

The documented schema no longer matched reality

Current Scribe writes states_raw keyed on metadata_id, with entities as a lookup table and states as a view over the two. The old SQL assumed a states table carrying entity_id directly. A continuous aggregate may only read a single hypertable, so it now groups by metadata_id and a thin view adds entity_id back.

New: SQL/scribe/

The two scripts the setup actually needs, neither of which was in the repository before.

01_sensor_minute_aggregate.sql builds the 1-minute continuous aggregate sensor_minute_aggregate plus the sensor_minute_aggregate_entity lookup view, with retention, compression and a refresh policy.

02_sensor_minute_table.sql builds the prefilled sensor_minute hypertable, its indexes and policies, the refresh procedure and the job that runs every minute. This is the table charts should query — the older LOCF views (sensor_minute_scribe, sensor_minute_ltss) build a grid of every minute since the oldest bucket times every entity and run a correlated subquery per cell, which times out on any real dataset.

Fixed: the minute table lagged reality permanently

sensor_minute_refresh() only appended rows after max(minute). The continuous aggregate trails real time by a minute or two, so rows written for the most recent minutes still carried the previous value — and because they were never revisited, that lag became permanent.

It shows up on counters. A utility_meter with cycle: hourly resets exactly at :00, but in the minute table the reset appeared two minutes later, so a chart bucketing on the clock hour sampled every hour too early. Bars under-reported and the tail of each period landed in the next bar.

The procedure now reprocesses a short trailing window (5 minutes by default, configurable) and upserts on (minute, entity_id).

Both scripts are re-runnable

• add_job is guarded, so running the file again cannot create a second minute job competing for the same rows
• the parameterless procedure is dropped before the new signature is created, so it replaces rather than overloads — CREATE OR REPLACE with a different argument list would have left the old body in place

README rewritten

• Requirements, including a TimescaleDB 2.13 minimum (by_range)
• An explicit install order, and why the connection test passing does not mean the table exists
• Database roles and grants, with the grant blocks the SQL needs when the reader connects as a different role than the one that created the objects
• The state versus value split: for a numeric entity only value carries the reading, while state holds a placeholder. Reading state first flattens every numeric series to zero without raising an error
• Which table to query, and why not the LOCF views
• A sizing warning on the initial backfill: it writes entities × days × 1440 rows, easily tens of millions in one transaction. A day-at-a-time loop is included

Verified

Both scripts were run end to end on a clean database: 14 of 14 and 15 of 15 statements succeed, the aggregate backfills, the seed works, the scheduled job takes over and keeps the table growing, and a reader-style downsampled query returns correct data.