feat: Parquet history provider for Signal K

[mairas]

feat: Parquet history provider for Signal K

Overview

Build halos-org/signalk-parquet-history-provider: a Signal K history provider that records to a
SQLite hot store, rolls it into a Parquet tree, and serves both history API surfaces by
spawning DuckDB over the tree plus the hot store.

It is a peer alternative to signalk-questdb-history-provider (sqhp), chosen per device.
Nothing in sqhp changes. No shared package is extracted.

Requirements

From _local/brainstorms/2026-08-22-parquet-history-provider-requirements.md.

• R1. Full Signal K history provider (v1 and v2 surfaces).
• R2. An alternative to sqhp, selected per device; not run together.
• R3. Offering work upstream to signalk-parquet is optional, not a design constraint.
• R4. The Signal K process performs only path filtering, rate capping and buffered socket writes.
• R5. Flush on whichever comes first: a time interval or a batch-size threshold.
• R6. A separate writer process owns the hot store.
• R7. Crash tolerance is the flush window, no worse than sqhp.
• R8. Bounded buffer when the writer is unreachable, dropping oldest first.
• R9. SQLite for the hot store; DuckDB for roll and queries.
• R10. Hot store rolled into the Parquet tree and truncated.
• R11. The roll runs in a short-lived process that exits.
• R12. Queries read hot store and Parquet tree together.
• R13. Include/exclude glob path filter.
• R14. Configurable default sampling-rate cap.
• R15. Per-path sampling-rate overrides.
• R16. User-defined ordered retention ladder (resolution + duration per step).
• R17. No per-path retention overrides.

Success criteria

Measured on Pi-class hardware, 70–130 rows/second, with the three-window method
(300 s windows, 180 s settle, cgroup cpu.stat, /proc/<pid>/io, /proc/diskstats,
half-window means checked for steady state). Report dispersion, not point estimates.

• Steady-state added resident memory across plugin and writer stays under 150 MB.
  Measured writer is 87 MB; QuestDB's standing cost is ~366 MB. Steady state is the only
  figure that represents a continuous cost.
• The roll's transient peak is reported separately and must not exceed available
  memory with the full marine stack running. It is not added to the steady-state figure.
• Total CPU across plugin, writer and amortised roll stays under +2.3 percentage points
  of one core — the measured sqhp + QuestDB figure.
• Signal K process disk writes stay within noise of the 12 KB/s no-history control.
• ~~A single-path range or bucket-aggregate query returns within sqhp's range (~34 ms for
  an hour-long request) against a fresh tree.~~ Measured and not met, by a factor of
  15. Unit 4a's real reader answers a single-path hour in 526–684 ms on a HALPI2, of
  which ~345 ms is the floor every spawned query pays before reading a row — ~220 ms of
  that is mapping the engine's native addon. The spike's 16–37 ms was in-engine time in an
  already-running process. This is a property of spawn-per-query, not of the tree, so the
  open question below replaces the criterion rather than restating it.
• A long-range query against an aged tree holding a full retention window's file count
  returns within a stated bound. Measured in Unit 4a: per-file planning is about 1 ms, so a
  30-day whole-window query is dominated by its rows rather than by its files, and a
  date-scoped query answers in 360–444 ms whatever the tree's size.
• Recording broadly does not require enumerating paths by hand.

Scope boundaries

• No shared core package. If two implementations later make a seam obvious, extract then.
• No changes to sqhp.
• No cloud upload, analysis UI or GPX import.
• The provider does not manage a container lifecycle.
• Running alongside sqhp on one device is unsupported.

Key decisions

All backed by measurements in the comment thread.

• SQLite hot store, DuckDB roll and query. 87 MB steady state against 159 MB, 3.3× less
  ingest CPU. SQLite's larger store (135 MB per 763k rows vs 20 MB) is answered by roll
  frequency, and the Parquet output is identical either way. The roll reads the hot
  store directly while the writer still holds it, because SQLite in WAL mode takes
  concurrent readers.
• No shared package, but most of the history surfaces are copied, not re-derived.
  Counted against the source, only
  54 of 866 lines carry SQL — 29 of 444 in history-v1.ts, 25 of 422 in
  history-v2.ts. The other ~94% is storage-agnostic contract behaviour: decodeValue,
  groupRowsIntoDeltas and its (ts, context, source) composite key, the ~160-line
  streamHistory chunk/resume machine, computeSMA/computeEMA, normalizeContext, the
  per-spec column assembly and timestamp union. That is copied like path-matcher.ts;
  only query construction is rewritten. The files still cannot be lifted whole — they call
  queryClient.exec and hard-code three table names — which is why no package is
  extracted. But Unit 4 is the largest work in the plan, not the smallest.
• The roll is a short-lived process. Peak is ~218 MB and is dominated by holding one
  writer per partition, not by data volume: 10× the rows costs 26% more memory. DuckDB's
  allocator does not return it in-process, so the process must exit. Chunking the roll
  inside one process makes the peak worse (218 → 325 → 473 MB for 1, 2, 4 chunks).
• Queries run in a query service that holds the engine. A process per query was
  built and measured first: starting an engine is ~345 ms on a HALPI2 and answering is
  96–246 ms, so it spent six times longer starting than working. The service is a
  separate process — never the writer and never the server — and it is kept for the
  life of the plugin. That buys a single-path hour at a median of 148 ms instead of
  526–684 ms, and costs 92 MB idle rising to the high-water mark of the largest answer
  it has served (164 MB after fifteen ordinary queries, 317 MB after one of 82,000
  rows). Recycling it against an idle timeout or an RSS ceiling is Recycle the query service instead of holding one for the life of the plugin #178.
• The tree is flat and the roll is hourly. Settled by measurement in Unit 3a: one
  Parquet file per roll under parquet/date=<YYYY-MM-DD>/, no path partitioning, no
  compaction pass, plus a cumulative last-value sidecar. A partitioned write's peak
  follows its row count and runs out of memory at 552 partitions; a streaming COPY
  does not, and stays under 250 MB at a day's rows. The interval is not a memory lever —
  it is the hot store's ceiling, and every recent query full-scans that store through
  sqlite_scanner at 63 ms per 13 MB, unchanged by an index on ts.
  Decision.

Open questions

• ~~Whether a ~0.5 s history query is acceptable.~~ Decided 2026-08-23: keep the
  engine. A process per query answered a single-path hour in 526–684 ms; the query
  service answers it in 135–175 ms. That is still four to five times sqhp's ~34 ms
  rather than fifteen, and the remainder is not startup — the same statement on an open
  engine runs in 39–45 ms, and the rest is the rows crossing a pipe as JSON. The
  standing cost this accepts is tracked in Recycle the query service instead of holding one for the life of the plugin #178.
• Vector averaging for units: rad paths needs live-server metadata handed to the roll.
• Non-numeric aggregation: strings, booleans and positions have no defined ladder
  behaviour. sqhp takes last() per bucket and refuses per-axis position aggregation.
• How an operator picks a provider. apps/signalk-server/prestart.sh already carries
  QuestDB-specific historyApi.defaultProvider logic.
• Grafana's datasource is provisioned per datastore; a Parquet tree has none.
• Switching providers strands existing history. No migration path is in scope.

Risks

Risk	Mitigation
sqlite_scanner and spatial are not bundled — they installed over the network	Bundle at build time in Unit 1; devices may be offline
Delta-supplied path and context strings become directory names	Closed by Unit 3a: only the roll's own UTC date is a directory, so no delta-supplied string reaches the filesystem
Writer socket is unauthenticated if it lands as TCP	Unix domain socket, mode 0600, owner-only (Unit 2)
DuckDB extension autoload fetches binaries at query time	Disable autoinstall/autoload, restrict file access, lock config after startup (Unit 4)
AIS-driven path and context cardinality inflates partition count, which sets the roll's peak	Closed by Unit 3a: there are no partitions, and a streaming roll's peak does not follow cardinality
Small-file Parquet output inflates size and roll memory	Closed by Unit 3a: one file per roll, 24 a day. The per-path alternative measured 10,337 files and 43 MB of disk for data one file holds in 3.2 MB
Benchmarks are from one device and one workload	Unit 7 reports dispersion and states the workload
History v1 has no provider registry — signalk-server/src/index.ts:316 is a single global slot, last registration wins, and unregisterHistoryProvider ignores its argument	Gate v1 registration on being the configured historyApi.defaultProvider; detect co-residency and refuse to guess (Unit 6)
Spawn-per-query turns a multi-series panel into many ~120 MB processes	One request compiles to one process; cap concurrent query processes (Unit 4a)
Nothing admission-controls a roll (~218 MB), a query (~120 MB) and a playback session at once	Unit 7 measures the summed transient; Unit 4a states whether they may overlap
Extension load, catalog setup and Parquet metadata reads are paid per spawned process	Unit 4a measures end-to-end wall time from spawn, not in-engine query time

Implementation units

Foundations

• Unit 1: Scaffold, bundle sqlite_scanner, build the measurement harness #163 — Unit 1: Scaffold, bundle sqlite_scanner, build the measurement harness
• Unit 2: Writer process and SQLite hot store #164 — Unit 2: Writer process and SQLite hot store

Storage layout

• Unit 3a: Settle partitioning, roll interval and compaction by measurement #165 — Unit 3a: Settle partitioning, roll interval and compaction by measurement
• Unit 3b: Roll implementation #170 — Unit 3b: Roll implementation

Query

• Unit 4a: DuckDB execution layer and hot-store/tree reader #166 — Unit 4a: DuckDB execution layer and hot-store/tree reader
• Unit 4b: History v2 surface #171 — Unit 4b: History v2 surface
• Unit 4c: History v1 surface (all three methods) #172 — Unit 4c: History v1 surface (all three methods)

Retention and ship

• Unit 5a: Flat partition expiry (sqhp parity) #167 — Unit 5a: Flat partition expiry (sqhp parity)
• Unit 6: Publish, bake, provider selection #168 — Unit 6: Publish, bake, provider selection
• Unit 7: On-device verification with dispersion #169 — Unit 7: On-device verification with dispersion

After verification

• Unit 5b: Resolution ladder and aggregation #173 — Unit 5b: Resolution ladder and aggregation

4b and 4c are independent of each other; both depend on 4a. Unit 5b is sequenced after
Unit 7 because it serves no success criterion, carries the worst correctness risks, and
runs inside the roll process whose transient peak the design is judged on — a peak
measured without aggregation.

[mairas]

Document review: plan on hold, premise unpriced

Six reviewer personas read the assembled plan (tracking issue plus all nine units). 81
findings, 12 at P0. Five premise-level failures were verified against the repo or
measured on halpi.hurma, not taken on the reviewers' word. The plan is on hold; the unit
issues are being kept as a record but not worked.

Verified premise failures

1. The shared core rests on a false reading of the code.
The plan's justification is that history-v1.ts and history-v2.ts are the valuable
storage-agnostic part. They are QuestDB SQL builders — 444 and 422 lines, ~20 SQL
constructs each (SAMPLE BY … FILL(NULL), LATEST ON, first(lat), LIMIT 50000),
hard-coding the three table names, calling queryClient.exec(sql) directly. The
string-table fallback fires on all-null rows, which is only meaningful because
FILL(NULL) fabricates a row per bucket. Three reviewers reached this independently.
Genuinely portable code is roughly 900 of ~2,100 src lines. The plan's own Unit 7 then
lists those same two files as patterns to follow — re-deriving what Unit 1 claims to make
unnecessary.

2. Two DuckDB processes cannot share the hot store. Tested on the device:

PROBE read-write: FAILED -> IO Error: Could not set lock on file: Conflicting lock is held
PROBE read-only:  FAILED -> IO Error: Could not set lock on file: Conflicting lock is held

While the writer holds the file read-write, a second process cannot open it even
read-only. Unit 5's forked roll "reads the hot store" — impossible. Unit 7's "spawned
DuckDB reading both stores" is not a tradeoff, it is blocked. Hot-store handover moves
from one of three optional memory mitigations to a hard correctness requirement, with
different failure semantics: "truncate after the Parquet write is durable" becomes
"rotate, then delete the rotated file".

3. The safety net for the production refactor does not exist.
history-v1.test.ts (802 lines) and history-v2.test.ts (863 lines) assert on generated
SQL text — 13 and 47 matches for SQL-string assertions. Unit 1's "core suite passes with
no dependency on QuestDB" and Unit 2's "existing suite passes without modification to its
assertions" are mutually impossible. Characterization-first was the sole mitigation for
refactoring a plugin recording live on a boat, and it evaporates on the two files
carrying the most test weight.

4. The 366 MB bar is not comparable to what replaces it.
halos-marine-containers/apps/questdb/config.yml states it: the JVM heap is capped at
192m via JVM_PREPEND=-Xmx192m, and "Most of QuestDB's memory is off-heap". The compose
file adds that QuestDB "memory-maps every partition column file and every WAL segment".
So 365.6 MB RSS is a 192 MB heap ceiling plus file-backed mapped pages the kernel
reclaims under pressure. DuckDB's 146 MB is anonymous heap. The success criteria compare
the two directly.

5. The cheapest alternative has never been priced.
halpi.hurma's kernel cmdline carries cgroup_disable=memory, and
/sys/fs/cgroup/cgroup.controllers lists only cpuset cpu io pids. QuestDB's
mem_limit: 768m is accepted by Docker and silently ignored. QuestDB has therefore never
run under an enforced memory ceiling on this device, and HaLOS builds its own kernel
cmdline with pi-gen. Nobody knows what a capped QuestDB costs.

The plan's own arithmetic argues against its engine choice

combined writer + roll peak	vs 366 MB bar
SQLite: 86 + 253 = 339 MB	under
DuckDB: 146 + 231 = 377 MB	over

The risk the plan calls "the tight constraint" is created by choosing DuckDB and
disappears under the rejected alternative. Two of the three mitigations listed for it are
also ruled out by the plan's own finding that DuckDB's allocator does not return memory
in-process — lowering memory_limit at runtime cannot free what is already allocated,
and reopening a database in a long-lived process keeps its arenas.

Other findings worth carrying forward

• The hot-store benchmark used repeated strings and a smooth sin() curve, which
  flatters exactly the columnar compression that decided the engine. Store-size ratios
  are upper bounds. No position or string channels were exercised.
• apps/signalk-server/prestart.sh already carries QuestDB-specific
  historyApi.defaultProvider logic. A third registered provider walks into a
  selection mechanism with known hazards.
• apps/grafana/prestart.sh provisions a datasource per installed datastore. Switching
  providers keeps Grafana and loses every dashboard built on QuestDB.
• Switching strands all recorded history. No migration path exists and the plan never
  says so.
• Security: the writer socket transport is unspecified (a TCP port would be unauthenticated
  write access to the vessel's history); DuckDB extension autoload and file access are left
  at defaults on a device that is often offline; delta-supplied path and context strings
  become directory names without validation; and AIS-driven path/context cardinality is a
  remote lever on the partition count that sets the roll's memory peak.
• R3 was silently dropped from the requirements trace. It exists in the origin document:
  offering the work upstream to signalk-parquet is optional, not a design constraint.
• sqhp recorded 111.7 rows/s against parquet's 84.4 under configurations declared
  identical — a 32% divergence that is unexplained and undermines per-row comparisons.
• The +0.15 CPU point figure for sqhp's plugin side is a 0.6% change on a 23.2 base with
  no dispersion reported, and the same table shows the sqhp condition writing less than
  the no-history control (11.7 vs 12 KB/s), which is physically impossible and bounds the
  noise floor above the signal.

Decision

Price the premise before building anything. See the follow-up issue for the method.

[mairas]

Correction: premise failure 4 does not stand

The earlier synthesis claimed QuestDB's 365.6 MB RSS is dominated by reclaimable
file-backed mapped pages, making it non-comparable to DuckDB's anonymous heap. That is
wrong, and a measurement recorded on 2026-08-21 (already in memory, not re-run) says so:

QuestDB RSS after 5-minute warm-up:  420,320 kB
  anonymous:                          340,288 kB   (81%)
  file-backed:                         80,032 kB   (19%)

The error was reading config.yml's "Most of QuestDB's memory is off-heap" as "most of
it is file-backed". Off-heap native allocation — Cairo column buffers, ILP buffers — is
anonymous memory, not mapped files. The comparison against DuckDB's 146 MB anonymous
heap is therefore roughly like-for-like, and the memory driver is sound.

Context for the two figures: the NMT capture from the same session shows Java heap
1,065 MB reserved / 327 MB committed. A 1,065 MB reservation is a quarter of 4 GB — the
JVM default when it cannot read a cgroup limit — because JVM_PREPEND had been
overwritten with the NMT flags, so that run had no -Xmx. The 420 MB is the uncapped
figure; today's 365.6 MB was measured with -Xmx192m in place. The ~55 MB gap matches
config.yml's note that 192m measured 43 MB lighter.

What this changes

• Premise failure 4 is retracted. QuestDB holds roughly 330 MB of anonymous memory,
  and DuckDB's 146 MB would be a real recovery.
• Premise failure 5 narrows. The heap is capped at 192m; what is uncapped is the
  total, because cgroup_disable=memory makes mem_limit: 768m inert. Whether an
  enforced total ceiling changes QuestDB's footprint is still unmeasured, and remains
  the open question.
• Premise failures 1, 2 and 3 are unaffected. history-v1/v2 are QuestDB SQL
  builders; a second process cannot open the writer's DuckDB file even read-only; the
  v1/v2 suites assert on SQL text and cannot be the refactor's characterization net.
  Each was verified independently of the memory reading.

The plan's structural problems stand. Its driver is in better shape than the synthesis
implied.

[mairas]

Spike results: the three measurements the plan rewrite depends on

Throwaway spike on halpi.hurma, no packaging, code discarded. It answers the questions the
review left open, using real recorded data rather than synthetic rows.

1. Hot-store handover works, and costs 12 ms

The review established that a second process cannot open a DuckDB file the writer holds —
not even read-only. That made the roll unimplementable as planned. ATTACH/DETACH
resolves it:

[0.0s] ATTACHed hot1, ingesting
[6.1s] ingested 5060 rows into hot1
[6.1s] HANDOVER complete in 12 ms — now on hot2
[15.1s] ingest continued, total 12650 rows

Probing from a second process, during and after:

during ingest:    FAILED -> Conflicting lock is held
after handover:   opened detached hot1 OK, rows = 5060

Rows split cleanly — 5,060 in the detached file, 7,590 in the fresh one. So the writer
keeps an in-memory main database, ATTACHes the hot store, and at roll time DETACHes
and ATTACHes a new file; the roll process then opens the detached one. A 12 ms gap is
negligible against a 2–5 s flush window. The architecture is viable.

2. On real data, SQLite clears the memory bar and DuckDB does not

763,244 rows exported from the live buffer — real values, real string cardinality, real
position objects, 187 paths — replayed through both engines at the same 253-row batch.

763k real rows	SQLite	DuckDB
ingest CPU	3.9 s	12.9 s
bytes written	317 MB	198 MB
bytes per row	435	272
writer peak RSS	87 MB	159 MB
store	135 MB	20 MB

The synthetic benchmark did flatter DuckDB, as the review suspected, but only on disk: the
store-size ratio falls from 11.5× to 6.75×. CPU (3.3× vs 3.7×) and memory (1.8× vs
1.7×) are unchanged.

Rolling the same real data to Parquet:

	writer RSS	roll peak	combined	vs 366 MB
SQLite	87 MB	236 MB	323 MB	clears
DuckDB	159 MB	213 MB	372 MB	breaches

The two roll peaks are within 10% of each other — that cost belongs to
COPY … PARTITION_BY, not to the source engine. What separates them is the writer's
always-on footprint. Parquet output is identical either way: 13 MB, 186 files.

This reverses the engine decision. "DuckDB throughout" was chosen when memory looked
like a non-differentiator. On real data it is the difference between clearing the binding
constraint and breaching it. The evidence now supports a SQLite hot store with DuckDB for
the roll and queries — and ATTACH … TYPE SQLITE is the handoff, already measured cheap.

3. Query cost is modest; a spawned query process costs ~120 MB

Against the rolled tree plus the hot store, single-threaded, at a 32 MB memory_limit:

query	wall	CPU	peak RSS
raw range, 1 path (9,568 rows)	16–24 ms	10–30 ms	84–100 MB
60 s bucket average, 1 path	24–36 ms	40 ms	108 MB
union hot + tree, 60 s average	33–37 ms	30 ms	117 MB
all-paths latest value (v1 snapshot shape)	155–198 ms	150–170 ms	122 MB

For comparison, sqhp's history API answered an hour-long request in 34 ms. Single-path
queries here are in the same range. The all-paths snapshot is the expensive shape, since
it touches every partition.

Peak RSS reaches 122 MB even at a 32 MB limit, so a spawned query process costs about
120 MB transient — which is the number the "where do queries execute" decision needs.
Holding a query engine resident in the writer would add that to the always-on figure and
put either engine back over the bar.

Caveats

• All figures are from 763k rows, roughly 10.5% of a day at the measured rate. Full-day
  and multi-day history will be larger — the all-paths snapshot especially, which scales
  with partition count and depth.
• The exported path column carries the buffer's table name rather than the Signal K
  path, so path strings are slightly longer than production. Cardinality (187) is right.
• The roll produced 186 files here against 1,331 in the full-day synthetic run, because a
  tenth of the data fits one file per partition. The small-file problem returns at full
  scale and the partitioning question stays open.

[mairas]

Roll frequency: hourly does not help memory, and chunking backfires

Measured on halpi.hurma against a real 830k-row slice (187 paths), rolling the same store
at four time windows so partition count stays fixed at 186.

window	rows	peak RSS	parquet
30 min	83,172	173 MB	1.8 MB
1 h	191,713	201 MB	3.6 MB
2 h	469,142	212 MB	8.3 MB
4 h	830,022	218 MB	14.3 MB

Ten times the rows costs 26% more memory. The roll's peak is the fixed cost of holding
186 partition writers open, not a function of data volume, and it asymptotes near 220 MB.

Consequences for roll frequency:

• Memory: hourly saves ~17 MB over four-hourly. DuckDB as hot store would land at
  159 + 201 = 360 MB against the 366 MB bar — inside noise. SQLite at 87 + 201 = 288 MB
  has real margin. Roll frequency does not rescue the DuckDB-as-hot-store option.
• Durability: hourly is a 24× improvement in crash exposure — an hour of unrolled data
  at risk instead of a day. On a device whose normal shutdown is a power cut, this is the
  strongest argument for it.
• File count: 186 files/hour × 24 = 4,464 files/day against 186 for a daily roll, and
  row groups fall to ~1,030 rows per partition per hour against ~26,900 for a day. Both
  are below Parquet's efficient range; hourly is much further below. This compounds the
  small-file problem already open in the partitioning question.
• CPU and disk: a wash. 24 × 0.5 s = 12 s/day hourly against 6 × 1.7 s = 10 s/day at
  four-hourly; ~86 MB of Parquet per day either way.

So roll frequency is a durability-versus-file-count trade, not a memory lever.

Chunking the roll makes memory worse, not better

Since the peak is driven by partition count, the obvious move is to roll fewer partitions
at a time. Measured on the same 4 h slice, splitting one COPY … PARTITION_BY into
sequential path-group chunks within one process:

chunks	paths each	peak RSS
1	186	218 MB
2	93	325 MB
4	47	473 MB

Peak grows with the number of sequential COPY statements, because DuckDB's allocator
does not return memory between them — the same ratcheting that forces the roll out of the
long-lived process. Chunking only reduces peak if each chunk is a separate process that
exits, paying a process spawn per chunk.

[mairas]

Correction: the "combined peak vs 366 MB bar" is not a valid metric

Two errors in the spike write-up and the roll-frequency comment above, both mine.

1. Steady-state memory and a transient peak were added together

Those comments summed the writer's always-on RSS with the roll's ~2-second peak, called
the result a "combined peak", and compared it against QuestDB's steady-state 365.6 MB.
That is not a quantity. It adds memory held 24/7 to memory held for about two seconds.

The honest comparison:

	steady state (24/7)	transient (~2 s per roll)
QuestDB	~366 MB	—
DuckDB writer	159 MB	~218 MB
SQLite writer	87 MB	~218 MB

halpi.hurma has 1,328 MB available and boots with cgroup_disable=memory, so there is no
cgroup limit for a transient to trip; it would have to exhaust the system to matter, and a
two-second 218 MB allocation is far from that. The roll transient is close to free.

Consequences. The 366 MB "bar" was manufactured, and the claim that it "reverses the
engine decision" is withdrawn. Both engines cut QuestDB's standing footprint by 200+ MB,
which is the only figure that represents a continuous cost. The real trade is DuckDB's
6.75× smaller store, 1.6× fewer bytes written and single-engine simplicity against
SQLite's 3.3× lower ingest CPU and 72 MB lower steady state. On that comparison the
earlier decision — DuckDB throughout — stands, and the engine question is not settled by
memory at all.

The measurements themselves are unaffected. Only the metric built on top of them was wrong.

2. "A device whose normal shutdown is a power cut" was never verified

That phrase came from a reviewer persona and was repeated here as established fact. It is
probably backwards: HALPI2 carries a supercapacitor (its voltage is published by
signalk-halpi as electrical.halpi.supercapVoltage) and halpid.service — "HALPI2
Power Monitoring and Watchdog Daemon" — runs on the device. That pairing exists to ride
out input loss long enough for a clean shutdown. Verified: the supercap is monitored and
the daemon is active. Not verified: whether halpid initiates the shutdown, which needs a
read of its source.

Consequence. The durability argument for hourly rolling rested on this. If HALPI2
shuts down cleanly on power loss, unrolled-data exposure is much smaller than assumed and
the case for hourly rolling weakens correspondingly — leaving its 24× file-count cost with
less to buy. Roll frequency should be decided after someone confirms what halpid does.

[mairas]

Decision: SQLite hot store, DuckDB for roll and query

Why

Capability difference is narrow for this role. As a pure append-and-read hot store SQLite
gives up nothing, because every analytic query runs in DuckDB over Parquet plus the
attached hot store. Probed in the shipped image:

• SQLite 3.53.3 (node:sqlite): math functions incl. atan2/radians (vector-mean
  headings work), JSON1, FTS5, R*Tree, GEOPOLY compiled in; extension loading available
  via allowExtension: true. No geometry types, projections or ST_ library. SpatiaLite is
  not in the image.
• DuckDB 1.5.3: json, parquet, icu bundled. time_bucket, ASOF joins,
  quantile_cont, LIST/STRUCT. spatial is not bundled but installs and works
  (ST_Distance, ST_Area verified).

Spatial capability does not depend on the hot-store choice, since DuckDB is the query
engine either way.

What this changes in the design

1. The single-writer lock problem disappears. A second process cannot open a DuckDB
   file the writer holds, which is what broke the roll as originally planned. SQLite in WAL
   mode takes concurrent readers — demonstrated repeatedly today by reading the live
   buffer.db from a separate process while signalk-parquet was writing to it. The roll
   opens the hot store directly and the DETACH/ATTACH handover mechanism is no longer
   required.
2. Hourly rolling now pays. SQLite's cost is store size — 135 MB per 763k rows against
   DuckDB's 20 MB. Rolling daily that is ~890 MB peak for a 5M-row day; hourly it is
   ~37 MB. That removes SQLite's main disadvantage, and the Parquet output is identical
   either way. Roll frequency becomes a live design lever rather than a wash.
3. Steady-state memory improves. 87 MB writer against DuckDB's 159 MB, and 3.3× less
   ingest CPU.

New dependency to resolve

DuckDB's sqlite_scanner is not bundled — it installed over the network when the roll used
ATTACH … TYPE SQLITE. It must be bundled at build time, since devices may be offline.
Same applies to spatial if spatial queries are wanted.

[mairas]

Unit 3a closed the layout question, and three risks with it

Full decision and measurements: issue 165 comment; the document is in halos-org/signalk-parquet-history-provider#12.

The tree is flat: one Parquet file per roll under parquet/date=<YYYY-MM-DD>/, hourly and UTC-aligned, context and path as columns, no compaction pass, plus a cumulative last-value sidecar.

Edits to this issue:

• The open question "partitioning scheme, and whether a compaction pass is required" is removed — answered.
• The key decision on roll frequency is replaced. Its premise was wrong in two ways: it treated hourly as a hot-store-size trade against file count, when the flat layout makes file count a non-issue (24 a day), and it assumed the durability argument that a later correction had already withdrawn. What actually decides the interval is that every query touching recent time full-scans the unrolled hot store through sqlite_scanner, and an index on ts does not change that.
• Three risk rows are struck through as closed by construction rather than mitigated: delta-supplied strings as directory names, AIS cardinality inflating partition count, and small-file proliferation. None of them has a mechanism left in the design.

The earlier spike's "the roll's peak is fixed-cost dominated by partition count, asymptoting near 220 MB" does not survive this dataset. That measurement held partition count fixed at 186 and varied rows; at 552 partitions the write fails outright at a 256 MB limit, and the peak follows the row count rather than asymptoting. Both are consistent with a partitioned write collecting its result set before emitting it — which is the behaviour that rules the shape out, not the number.

[mairas]

Unit 4a measured the query path, and one success criterion did not survive it

The reader is in signalk-parquet-history-provider#14. Two things in this plan changed, both from measurements on a HALPI2 through the shipped code rather than through a scratch statement.

A spawned query's floor is ~345 ms before it reads a row. Three runs each: node starts in 26–29 ms, importing @duckdb/node-api takes that to 218–255 ms, creating and connecting an instance to 265–269 ms, and LOAD sqlite_scanner to 336–375 ms. Mapping the engine's native addon is ~220 ms of it and nothing in this design can avoid it — the addon is why a query runs in its own process, and the process is why the memory is transient.

So a single-path hour answers in 526–684 ms and a date-scoped range in 360–444 ms, against the criterion's ~34 ms. Unit 3a's 16–37 ms was in-engine time inside an already-running process, which is exactly what #166 suspected. I have struck the criterion in the body and put the decision in Open questions instead of quietly relaxing the number: accept ~0.5 s history queries, or reopen spawn-per-query. Nothing in 4b or 4c changes this arithmetic, so it is worth deciding before either is written.

For what it is worth, the trade is unchanged on the axis the design was chosen for: 0 MB standing against QuestDB's ~366 MB, and ~120 MB only while a query runs. A resident engine would buy back the latency and cost about that much permanently.

Unit 3a's layout decision survives its own reopening test. It said compaction reopens "if Unit 4a's real reader shows a multi-day query dominated by per-file cost rather than by startup". Measured against one day of real data hard-linked into 30 dated directories:

tree	files	one path, 30 days	paths, 30 days	one path, one day
roll files	240	2,195–2,244 ms	661–727 ms	360–435 ms
compacted to one per day	30	1,916–2,062 ms	601–631 ms	375–444 ms

Per-file planning is about 1 ms. Collapsing 240 files into 30 saves 230 ms of a 2,200 ms query that is dominated by its 82,590 rows, and compaction costs a second process at 344–484 MB. A date-scoped query answers in 360–444 ms whether the tree holds 1 file, 8 or 240, which is the property the flat layout was chosen for.

Two smaller facts for later units. An answer of ~82,000 rows peaks at 219 MB, so two queries and a roll at once is a ~600 MB summed transient — a sum of peaks measured apart, nothing coordinates them. And getPaths is a scan of the range in both sources (448–486 ms over a day, 601–727 ms over thirty); the cumulative sidecar cannot answer it, because it holds the last value per path rather than what reported inside a window.

docs/query-layer.md in the PR carries the full tables and the caveats.

[mairas]

Decided: keep the engine, and bound it later

The open question above is closed. Matti's call: an always-on query service now, and the recycled middle written up as #178 rather than built.

Measured on a HALPI2 through the shipped client, in signalk-parquet-history-provider#15:

query	process per query	first request	later requests
one path, one day	610–612 ms	652 ms	215–246 ms
one path, last hour	526–684 ms	529–558 ms	135–175 ms
paths in the day	448–486 ms	450 ms	96–117 ms
every path, 10 min (82k rows)	1,548–1,665 ms	1,576 ms	1,158–1,244 ms

Forty consecutive hour-long queries settle at a median of 148 ms. So the criterion is missed by four to five times rather than fifteen, and the remainder is no longer startup: the same statement on an already-open engine runs in 39–45 ms, and the rest is serialising the rows to JSON, the pipe, and parsing them back. That is a transport question, not an engine one.

What it costs. The service reports its own resident size with every answer: 92 MB idle, 117 MB after one query, 164 MB after fifteen, 170 MB after forty, and 317 MB after a single 82,000-row answer. It converges rather than leaking, and it never comes back down — DuckDB does not return what a query allocated, which is the same property that makes the roll a process that exits. With the writer at 87 MB, the plugin's standing cost is near 260 MB against the 150 MB in the success criteria above, and against QuestDB's ~366 MB.

That number is knowingly over budget for the MVP. #178 is where it gets bounded, with the signals to recycle on and what each would cost.

Two things were measured before choosing this shape, because both would have been quiet failures on a device. A held SQLite attachment does not go stale — cross-process, a reader that attached at 2,000 rows went on to see 3,500 and 203,500 — and it does not pin the writer's WAL: wal_checkpoint(TRUNCATE) returned busy: 0 and truncated to 0 kB with a reader attached. The service still attaches per request, because that costs 0.2–1.2 ms and picks up a store created after it started, but neither was a workaround for a problem.