perf(state): batch energy-ledger SQL inside each control tick

[frahlg]

Problem and current behavior

FTW commits history, scalar samples, energy entries, and ledger cursors in one transaction each control tick. Keep that atomic boundary.

Within the ledger part, each observation now:

• upserts its asset, including a second identical upsert for another flow on the same asset;
• queries both power and counter cursors, even when CounterWh is nil;
• executes the same cursor and entry SQL again without explicit statement reuse.

A representative tick with 12 power-only observations executes about 61 SQL statements. This adds CPU time and allocations to FTW’s default two-second control loop.

Desired result

Reduce SQL calls, parsing, and allocations within the existing transaction while preserving the exact stored result.

A likely shape is:

• validate the full batch;
• preload only the cursor keys required by this batch;
• process observations in their current order;
• upsert each asset once, preserving the current last-observation-wins result;
• reuse prepared asset, cursor, and entry statements;
• avoid reading a counter cursor when the observation has no counter.

The implementation must remain bounded by the current observation batch.

Out of scope

• Changing energy math, sign rules, buckets, retention, rollup, provenance, quality, or sample_count.
• Buffering across control ticks or moving cursors into memory as the source of truth.
• Changing the SQLite driver, schema, indexes, history queries, or sample-write path.
• Changing the control interval.

Safety and failure behavior

History, scalar samples, energy assets, entries, and cursors must still commit or roll back together.

An invalid observation, cursor-read error, statement error, cancellation, or process stop before commit must not leave a partial tick. Restart must continue from the last SQLite-committed cursors. Do not add an in-memory write-back cache or a fallback path with different ledger rules.

Equal and out-of-order timestamps, counter reset/resume, power gaps, and simultaneous import/export or charge/discharge must keep their current results.

Prototype evidence

A temporary power-only prototype preloaded cursors, reused prepared statements, and removed duplicate asset writes.

Apple M5 arm64, 12 observations, five benchmark runs:

Path	Time	Bytes	Allocations
Current origin/master behavior	548–575 µs/op	44.4 KB/op	1,283/op
Batched prototype	164–180 µs/op	24.6 KB/op	728/op

Median speedup was about 3.3×, with 45% fewer bytes and 43% fewer allocations. This proves the opportunity; it does not replace Raspberry Pi evidence.

Acceptance tests

Exact parity

Run the current-master reference and candidate against separate fresh databases, then compare ordered contents of:

• energy_assets;
• energy_ledger_cursors;
• energy_ledger_entries;
• history_hot;
• ts_samples.

Values, timestamps, labels, energy_wh, source, quality, provenance, and sample_count must match exactly.

Fixtures must cover:

• 1, 6, 12, and 24 power-only observations;
• separate import/export and charge/discharge flows;
• counter baseline, valid delta, reset, and implausible jump;
• missing counter, power fallback, and counter resume without double count;
• a power gap over the current limit;
• equal and out-of-order timestamps;
• repeated observations for the same asset and flow;
• device rename and last-label behavior;
• invalid observation and an injected SQL failure after entry work but before cursor work.

Both failure cases must leave all five tables unchanged for that tick. Existing ledger, rollback, rollup, migration, and state tests must pass.

Raspberry Pi benchmark

Add a committed benchmark with power-only, counter-only, and mixed workloads at 1, 6, 12, and 24 observations.

Run the same baseline and candidate commits on one arm64 Pi with the same Go toolchain and DB image:

go test ./internal/state \
  -run '^$' \
  -bench '^BenchmarkRecordTickEnergyLedger' \
  -benchmem \
  -benchtime=3s \
  -count=10

Run once with the database on tmpfs and once on the deployment SD-card filesystem. Compare with benchstat.

Acceptance gate:

• 12-observation power workload is at least 2× faster on tmpfs;
• it is at least 20% faster on the SD filesystem;
• allocations fall by at least 35%;
• no workload regresses by more than 5%;
• kill/reopen testing on the SD filesystem finds no partial tick or cursor/entry mismatch.

Target files

Expected:

• go/internal/state/energy_ledger.go
• go/internal/state/energy_ledger_test.go or one focused benchmark test beside it
• one Changeset

Touch go/internal/state/store_ts.go only if transaction plumbing requires it. Do not expand into go/internal/state/store.go.

Open-PR overlap

Checked against live open PRs on 2026-08-10 and origin/master at c9c7919.

No open PR changes energy_ledger.go, energy_ledger_test.go, or store_ts.go. PRs #734, #735, and #736 change go/internal/state/store.go, so this work must stay out of that file. PR #825 changes go.mod and go.sum; this proposal needs no dependency.