Projections: request, minute and device fact rows #630

Part of #623.

Scope

After this PR, history.db holds three projections beside its events: one row per lease request, minute rows (per worker and platform, plus totals) written when a value changes, and one row per device fact. Each is written in the same transaction as the event that changed it (#629's projection step). They survive a restart, are kept for metrics.retention (90 days by default), and outlive the 7 days of events. A projection whose check fails at start is rebuilt from the kept events, in batches, and simlock status says so once. A failed migration, or a stored version newer than the code's, stops the daemon from starting. history.usage, the read side #631 calls on its read thread, answers from the rows. ADR 0032 §2, §3, §4 and §6, ADR 0016 §2, §3 and ADR 0021 §5 fix the rules.

In short

Each event that changes a request, a level or a device fact updates its row before the event's transaction commits. The rows are those of feature #623 Shared values: Request row, Minute row, Device fact row. This task adds their tables, the one function that fills them, and the reads #631 makes. Six device events and lease.granted gain the device's platform.

Projection: a table built only from event envelopes, by one function. Example: after lease.requested at 10:00:01 and lease.granted at 10:00:39 for req_8f2, its request row has requestedAt 10:00:01 and grantedAt 10:00:39, so its wait is 38 s.

Integral: a value times the milliseconds it held in the minute, summed. Example: 1 lease for the first 15 s of a minute and 3 for the other 45 s gives 1 × 15 000 + 3 × 45 000 = 150 000, a mean of 2.5.

Gap marker: a row that says "no data from here". Written at a daemon stop, a daemon start and, on a gateway, worker.disconnected. Example: 2 leases held from 10:04:30 and no lease event until 10:09 reads as 2 for 10:05 to 10:08; a daemon stopped at 10:06 reads as 2 until 10:06 and no data after.

flowchart LR
  B[Bus] --> H["#629 writer: BEGIN; INSERT event"]
  H --> P["Projector (new)<br/>request, minute, device fact rows"]
  P --> C[COMMIT]
  S["#629 start()"] --> M{"migrate + check (new)"}
  M -->|migration fails or newer version| X["Daemon stops (new)"]
  M -->|check ok| R[Ready]
  M -->|check fails| RB["Rebuild in batches (new)"]
  RB -->|ok| R
  RB -->|throws| E["Rows the events cover stay empty (new)"]
  Q["#631 read thread"] --> U["history.usage (new)"] --> D[(history.db, read-only)]

simlock status after a start that rebuilt, and after one whose rebuild failed:

History: figures were rebuilt from the event history at 2026-10-16 09:12.

History: figures could not be rebuilt; see daemon.log.

A start after a downgrade, in daemon.log, and the daemon exits 1:

{"level":"error","module":"daemon.history","message":"History projection usage is at version 3; this simlock knows up to 2. Upgrade simlock, or move history.db away.",…}

Quantities: every time in a row is epoch ms by the clock named in the rules below. metrics.retention is ms, counted back from the prune's Clock.now(); a request row goes by requestedAt, a minute row by its minute's start, a device fact row by at. An integral is value × ms, within its minute.

If this goes wrong, an operator sees #631's figures disagree with a count by hand from simlock events, or figures lost after an upgrade.

Technical spec

Facts

Modules touched

Searches: the event names in F1-F10 (git grep -n '"lease.requested"\|"lease.granted"\|"lease.rejected"\|"lease.declined"\|"request.granted"\|"capacity.changed"\|"queue.changed"', 34 production hits, and the eight device names, emitted in src/core and src/leasing); metrics in src/core/config.ts (0 hits); #629's HISTORY_PROJECTIONS and HistoryProjectionDefinition.

• src/history/projections/usage/ (new, the first projection): the pure projector, its SQL schema and migrations, check, rebuild, prune, and the read side history.usage. Registered in The event history is a SQLite file, history.db #629's HISTORY_PROJECTIONS.
• src/history/ (The event history is a SQLite file, history.db #629's host): the definition gains version, migrations, check, rebuild; start() runs migrate → check → (rebuild); status() gains rebuiltAt and rebuildFailed; a refusal makes start() reject.
• src/bus/index.ts: platform on lease.granted and the six device payloads (feature Operational metrics kept over hours and shown in the console #623 Shared values, Device event platform). Emit sites (F3, F11): src/leasing/lease-lifecycle.ts, src/core/registry.ts, src/core/quarantine-coordinator.ts, src/leasing/lease-health-monitor.ts. All but the lifecycle are strict test areas; so is src/leasing/.
• src/daemon/main.ts: passes config.mode and metrics.retention; a rejected history.start() stops the daemon with reason history-refused and exit 1.
• src/core/config.ts: metrics.retention, its default and check, and metrics in the gateway's keys.
• src/contract/operations.ts, schemas.ts: status.get history.rebuiltAt, rebuildFailed; config.get metrics.
• src/daemon/dispatcher.ts, src/gateway/dispatcher.ts: pass the two fields through.
• src/cli/index.ts: the third and fourth status lines.
• Docs: docs/CONFIGURATION.md (metrics.retention, the gateway key list at line 237), docs/CLI.md (simlock status), docs/HTTP-API.md (GET /v1/status), both EVENTS.md rows (F13), the history manual, docs/internal/ARCHITECTURE.md's history row.

flowchart LR
  hist["history (changed)"] --> proj["projections/usage (new)"]
  proj --> bus["bus (changed: platform fields)"]
  main["daemon/main.ts (changed)"] --> hist
  t631["#631 read thread"] --> reads["history.usage (new, in projections/usage)"]
  lease["leasing emitters (changed)"] --> bus
  core["core emitters (changed)"] --> bus
  config["core/config (changed)"]
  contract["contract (changed)"]
  cli["cli (changed)"]

No new process or network hop: the projector runs inside the daemon's event transaction; the reads run on #631's thread, in this process.

Surface and extension points

• The projection is the first implementation of The event history is a SQLite file, history.db #629's extension point. src/history/projections/usage/index.ts exports its definition and openUsageReads. The history host names no projection (architecture rule 21).
• The event history is a SQLite file, history.db #629's HistoryProjectionDefinition grows: version: number; migrations: readonly string[] (SQL, one per step from the previous version); check(db): boolean; and create(db, { fleet, metricsRetentionMs }) → { apply(envelope), prune(now), rebuild(cursor) }. The conformance suite gains: migrations from version 0 reach version; rebuild over the events apply saw gives the same rows.
• Version. Stored per projection, in a projection_versions(name, version) table. In start(), for each projection: stored version above the code's → refuse; else each migration from the stored version up, one transaction each; a migration that throws → refuse. Refuse: start() rejects naming the projection and both versions; nothing is dropped.
• check: every table and every column the code's version declares exists with its declared type, the stored version equals the code's, and no rebuild is marked as running. False → rebuild.
• Read side history.usage = openUsageReads(db), over the read-only connection usage.get and simlock stats from the projections #631 opens on its read thread (feature Operational metrics kept over hours and shown in the console #623 Shared values, history.usage). The four calls are synchronous and run inside the one read transaction usage.get and simlock stats from the projections #631 opens, so they see one commit:
  • oldestTs(): number | undefined: the earliest of request rows' requestedAt, minute rows' minute start and device fact rows' at; undefined when all three are empty.
  • requestRows({ from, to }): RequestRow[]: rows with from ≤ requestedAt < to, by requestedAt then requestId.
  • minuteBuckets({ from, to, bucketMs, workerId, platform }): MinuteBucket[]: for one key, one entry per bucket (start = from + k × bucketMs) that has a known value: { start, knownMs, <value>: { peak, integral } }, aggregated in SQL. peak is the largest minute peak, integral the sum of minute integrals, knownMs the sum of known ms. Values carry across minutes with no row, from the last row before from too, up to to, and stop at a gap marker (rules below). A value absent from every minute of the bucket is absent.
  • deviceFacts({ from, to }): DeviceFactRow[]: rows with from ≤ at < to, by at.

Contract and event changes

• status.get history gains rebuiltAt?: number (epoch ms) and rebuildFailed?: true (feature Operational metrics kept over hours and shown in the console #623 Shared values, status.get history).
• config.get gains metrics: { retention: number }, optional in the schema, as eventLog is (F18).
• This PR bumps the protocol by one.
• lease.granted, device.ready, device.recovered, device.quarantine-recovered, device.recovery-failed, device.quarantine-abandoned and device.quarantine-stranded gain platform: "ios" | "android", the device's own (feature Operational metrics kept over hours and shown in the console #623 Shared values, Device event platform). Additive (events rule 6). Their rows in both EVENTS.md files (F13) name the field. A quarantined device says what failed #635 adds it to device.quarantined. No event added.
• Wire input: every payload field the projector reads is parsed with bounds, because a relayed payload is unvalidated (F14). Strings are cut to 200 characters; numbers must be finite and ≥ 0; enums must be one of the known values. A field that fails is absent from the row; a payload missing a key field (requestId, leaseId, deviceId) changes no row. The event row is still written (safety rule 10). An unknown event name changes no row.

Projection rules

The fields, keys and kinds are feature #623 Shared values: Request row, Minute row, Device fact row. Below is only how events fill them.

Own and relayed, on a gateway: a request fact is the gateway's own when it has no payload.workerId. worker.disconnected is always the gateway's own (F14) and its workerId names the worker. request.dispatched is never read.

Request row:

• Created by lease.requested, or by lease.rejected with no row for its requestId (refusedAtAdmission). platform and model come from requestSpec (F2); a class request has no model.
• grantedAt, source, leaseId from lease.granted by requestId. rejectedAt, reason from lease.rejected by requestId. endedAt, endReason (explicit, killed, device-lost, or expired) and heldMs (endedAt − grantedAt) from lease.released or lease.expired by leaseId.
• A field already set is not set again by a later event.
• On a worker, a lease.requested with fleetRequestId makes a row with probe set. Its lease.declined sets rejectedAt and reason by requestId, with declined: true, and also writes a device fact row.

Fleet rules, on a gateway (ADR 0021 §5, from #347):

• Request rows come from the gateway's own lease.requested, lease.rejected and request.granted, by request id, by the gateway's clock.
• request.granted sets grantedAt, workerId (its worker) and workerLeaseId. A worker-failed rejection sets workerId from its worker.
• Held leases: each relayed lease.granted is kept in a held_leases table, keyed (workerId, leaseId), with its platform, source and time (worker's clock), and deleted at that lease's relayed lease.released or lease.expired. Rows older than metrics.retention are pruned. This bounds what waits for a join.
• source comes from the held lease matching the row's workerId and workerLeaseId, whichever of the two arrives first. With no match, source stays absent; a reader treats an absent source on a granted gateway row as unknown. unknown is never written.
• endedAt, endReason and heldMs on a gateway row come from the relayed end, by the worker's clock: heldMs = relayed end − relayed grant. They are used only for heldMs; turnaround is wait plus heldMs, never a gateway time minus a worker time.
• A request with no outcome when the gateway's own daemon.started arrives is rejected daemon-restarted at that event's time (F15). A relayed daemon.started closes nothing.
• Relayed lease.requested, lease.queued, queue.changed, lease.rejected and lease.declined never create or settle a request row.

Minute row:

• A step is an event that changes a value. On each step, every row the value belongs to is updated: the platform row, the worker's all row and, on a gateway, the * row, so the all and * peaks are true totals at one moment. A row is written only for a minute in which a value changed.
• slotsUsed = running + reserved, slotsMax = maxRunning, from capacity.changed (F8, F19): ios and android per platform, global for all. ramUsedBytes and ramLimitBytes from ramBudget, on all only.
• queueDepth: the sum of both queue lines' last depth (F9), on all only.
• leasesHeld: leases granted and not yet ended, by lease.granted's platform.
• waiting: requests whose row has no grant and no rejection. A probe is never counted on a worker.
• On a gateway, the * rows: slotsUsed, slotsMax, RAM and leasesHeld are the sums over connected workers' latest values; queueDepth and waiting are the gateway's own lines and requests only. A worker's rows hold its relayed capacity.changed, queue.changed and held leases.
• peak, last and integral per value per minute; a step splits the integral at its timestamp. knownMs is the ms of the minute from the first known value (the minute's start when carried in) to the minute's end or a gap marker.
• Clock skew: a step stamped before its key's last step (a worker clock behind the gateway's, or two workers' clocks on a * row) counts at the last step's time.
• Gap markers, in a gaps(at, workerId) table: the daemon's own daemon.stopping and daemon.started write one for every key (workerId null); a gateway's worker.disconnected writes one for that worker, and its values leave the * sums. A reader carries a value up to the next marker for its key. At a start marker with no stop marker before it (a crash), carrying ends at the end of the last minute with a row before it, so nothing is carried over the downtime.

Device fact row:

• One row per event listed in feature Operational metrics kept over hours and shown in the console #623 Shared values, Device fact row. kind is the event name. durationMs from device.provisioned.duration, device.ready.bootDuration and device.recovered.duration. A device.ready with bootDuration 0 (doctor, F12) is no boot fact and writes no row.
• platform from the event: device.provisioned's spec, lease.declined's requestSpec, and the new platform field on the others. An event without it gives no platform.
• workerId from payload.workerId on a relayed event; absent on a worker's own.

Restart (create() after a stop or crash):

• Restores from the tables: open requests (rows with no grant and no rejection) and held leases (held_leases, and on a worker the rows granted and not ended).
• Minute values are not carried over: the start's gap marker cuts them. At the own daemon.started, leasesHeld and waiting get a step from the restored state; slots, RAM and queue depth get theirs from the start's capacity.changed and queue.changed (F8, F9).
• Rows written before the stop are not changed.

Rebuild (feature #623 Shared values, Projection version):

• Runs in start() when check is false, after The event history is a SQLite file, history.db #629's start prune and before its timer is armed. It marks itself running in the database; a stop or crash mid-rebuild leaves the mark, so the next start rebuilds again.
• It recreates each table that fails the check (its rows are lost), deletes rows the kept events cover from the tables that pass (request rows with requestedAt, device facts with at, minute rows and gaps from the oldest event's minute on), and replays every kept event in write order (rowid) through apply, in batches of 1 000 events, one transaction each, with setImmediate between batches. Rows older than the oldest event are kept.
• Events emitted meanwhile are written by The event history is a SQLite file, history.db #629's writer with no projection step; the rebuild's cursor reaches them, and live projection resumes in the same tick as the last batch. Leases, status.get and other work run between batches. Bound: ⌈kept events / 1000⌉ batches.
• Done: clear the mark, set the version, set rebuiltAt. Throws: the deleted rows stay deleted; rebuildFailed: true, writing stays true, no rebuiltAt, one error; the projector writes no rows for the rest of this run.

Prune: in #629's prune, before the vacuum, delete rows older than now − metrics.retention: request rows by requestedAt, minute rows and gaps by minute start, device facts by at, held leases by their grant time.

• Replay order (decided by the maintainer, 2026-10-19): a replay applies events in the order they were written, never by timestamp. The start catch-up reads the bus ring in seq order (its own read; bus.replay() keeps time order for its other callers); the rebuild reads history.db by rowid. A seq that does not rise marks a new run.

Other code on the same state

Searches for the state: writers of the projection tables (only this module; git grep -n "INSERT INTO\|DELETE FROM" src after #629); readers of the events in F1-F10 (git grep -n 'subscribe("lease\.\|subscribe("capacity\|subscribe("queue', 5 production hits, none writes history); HISTORY_PROJECTIONS (#629 only).

Failure modes

State lines:

• A crash mid-transaction: the event and its rows are both there or both absent.
• A crash mid-rebuild: the mark stays, so the next start rebuilds again.
• Two callers at once: one write connection, one thread; usage.get and simlock stats from the projections #631's reads use their own connection and a read transaction.

Rules in play

• ADR 0032 §2, "each event and its projection changes in one transaction": the projector runs inside The event history is a SQLite file, history.db #629's transaction; minute rows only on change; gap markers.
• ADR 0032 §4: a failed migration or a newer version refuses; only a failed check rebuilds, in batches; rows older than the oldest event are kept.
• ADR 0032 §5: reads on their own read-only connection.
• ADR 0016 §2, "nothing is inferred from timing alone": every join is by id.
• ADR 0021 §5, "a fleet request's outcome is its gateway request.granted or lease.rejected": the fleet rules above.
• Architecture rule 1: the projector has no platform code; ios and android are payload values.
• Architecture rule 12: the rebuild is running, done or failed, and marked in the database.
• Architecture rules 19-21: the first registered projection; the host names none.
• Safety rule 10, "anything that arrives over the wire is a claim": bounded parse of every payload field.
• Testing rule 6: driven through the bus and read back through history.usage.

Tests

Seam: createEventHistory with HISTORY_PROJECTIONS over a real node:sqlite file in a temp directory; events go in through EventBus.emit and republish; rows are read back through openUsageReads on a second, read-only connection. Model #629's src/history/history.test.ts. Config and CLI lines through their existing suites.

• a request granted, held and released gives one row with requestedAt, grantedAt, source, leaseId, endedAt, heldMs and endReason explicit; an expired lease gives endReason expired
• a rejected request's row has rejectedAt and reason; an admission refusal gets its own row with refusedAtAdmission and requestedAt equal to rejectedAt; a class request has no model
• a field already set is not changed by a later event, an event already stored changes no row a second time, and an event for an unknown request or lease changes no row
• a relayed payload with an over-long string, a negative number, an unknown source or no requestId writes the event row, and the row field is absent or no row changes; an unknown event name changes no projection row
• on a worker, a probe's row has probe set, is not counted in waiting, and its lease.declined sets rejectedAt, reason and declined and writes a device fact row
• on a gateway, a row takes its grant, worker and workerLeaseId from the own request.granted, ignoring relayed lease.requested, lease.queued, lease.rejected, lease.declined and an earlier relayed lease.granted; a worker-failed rejection sets the worker and a timeout one none
• on a gateway, source and heldMs come from the held relayed lease matching workerId and workerLeaseId, by the worker's clock, whether the relayed grant arrives before or after request.granted; with no relayed grant source is absent; a relayed end deletes the held lease
• on a gateway, an open request is rejected daemon-restarted at the own next daemon.started; a relayed daemon.started closes nothing
• minute rows: slotsUsed is running plus reserved and slotsMax per platform and on all; RAM used and limit and queue depth (both lines summed) only on all; no row for a minute with no change
• peak, last and integral of a value that steps twice in a minute are the highest, the final and the time-weighted sum; an all row's peak is the highest total at one moment, not the sum of platform peaks
• leasesHeld by lease.granted's platform and waiting per platform rise and fall with grants, ends and rejections, across two minutes
• on a gateway, the * row sums the workers' slots, RAM and leases and holds only the gateway's own queue depth and waiting; after worker.disconnected that worker leaves the sums
• a step stamped before its key's last step counts at the last step's time, on a relayed row and on a * row
• daemon.stopping, daemon.started and, on a gateway, worker.disconnected each write a gap marker
• after a restart, open requests and held leases are restored, leasesHeld and waiting get a step at daemon.started, and every row written before the stop is unchanged
• one device fact row per listed device event and per lease.declined, with durationMs, platform from the event (absent when the event has none) and workerId on a relayed one; a device.ready with bootDuration 0 writes none
• lease.granted and the six device events carry the device's platform, for an iOS and an Android device, at every emit site (extends each emitter's existing event test)
• history.usage.oldestTs() is the earliest of the three tables' times, each table the earliest in one case, and undefined when all are empty
• history.usage.requestRows and deviceFacts return rows with from ≤ time < to (one at from in, one at to out), in their stated order
• history.usage.minuteBuckets returns, per bucket, the largest minute peak, the summed integral and summed knownMs for the asked key only; it carries a value across minutes with no row and from before from, stops at a gap marker, carries nothing over a crash's downtime, and leaves RAM absent where no minute had it
• the four reads in one read transaction see the same commit while the writer commits between them
• a stored version below the code's is migrated and keeps its rows; a migration that throws, or a stored version above the code's, makes start() reject naming the projection and versions, with every row kept
• a failed check rebuilds in batches of 1 000 with other work running between them: rows older than the oldest event are kept, the rest equal the live rows, an event emitted mid-rebuild is projected once, and status() gives rebuiltAt after; a start with nothing to fix gives neither field
• a stop mid-rebuild leaves the mark and the next start rebuilds; a rebuild that throws leaves no rows for the events' window, rebuildFailed true, writing true, no rebuiltAt, one error, and later events are still written
• prune deletes request, minute, gap, device fact and held lease rows older than metrics.retention and keeps one at the cutoff, at start and one hour later
• a config with metrics.retention shorter than eventLog.retention, or not a positive integer, fails the load naming the key; with none, it is the larger of 90 days and eventLog.retention; a gateway reads it with no warning; config.get reports it
• status.get carries history.rebuiltAt and rebuildFailed on a worker and a gateway, and simlock status prints the rebuilt line with the local time and the could-not-rebuild line only when each is set (extends The event history is a SQLite file, history.db #629's status lines test)
• (e2e) after a scripted run on the fake driver, simlock daemon stop and start, the request rows read through history.usage match the requests a test counts from simlock events --since 1h; with metrics.retention and eventLog.retention a few seconds, the rows of the first run are gone after the next start
• (e2e) a history.db whose projection table lost a column while the daemon was stopped is rebuilt at the next start, a lease is granted meanwhile, and simlock status prints the rebuilt line; one with a newer stored version makes simlock daemon start fail and leaves the file unchanged
• A relayed event whose timestamp is older than the event before it, within one run, is not read as a new run: the start catch-up gives the same fleet, key, worker and queue rows as the live writer.

Done when

• On the fake driver, after a scripted run with a grant, a --no-wait rejection and a timeout, history.db has one request row per request with the times from simlock events --since 1h, minute rows for the minutes that changed, and a device fact row for each provision and boot.
• After simlock daemon stop and start, the rows are the same.
• With eventLog.retention a few seconds, request rows older than that are still there after the next start; with metrics.retention also short, they are gone.
• A broken projection table is rebuilt at the next start and simlock status prints the rebuilt line; a newer stored version stops the daemon from starting.
• docs/CONFIGURATION.md describes metrics.retention, its default and that it may not be shorter than eventLog.retention.

Out of scope

• usage.get, simlock stats, HISTORY_NOT_KEPT, HISTORY_UNAVAILABLE, percentiles, buckets chosen, the read thread itself and one read per window (usage.get and simlock stats from the projections #631).
• The HTTP route, CSV, console (GET /v1/stats and the simlock stats --csv ledger #632-Console Usage page #634).

Depends on

• The event history is a SQLite file, history.db #629
• A quarantined device says what failed #635

Written by an agent.