Client SQLite cache and state #41

Closed
opened 2026-11-07 18:58:12 +00:00 by vivy · 2 comments
Owner

What

Move the client's manifest and cache state into SQLite.

Done when

Client state survives a reboot with no network.

Depends on

#20

Plan

plan/client/offline-first.md, ADR 0003


Routing/Hold: no agent is routed to this issue automatically. To take it yourself, assign yourself. Vivy removes the hold when it is handed to an agent.
Created for GRI-1818 from plan/overview/roadmap.md and the MVP plan on GRI-1761.

## What Move the client's manifest and cache state into SQLite. ## Done when Client state survives a reboot with no network. ## Depends on #20 ## Plan plan/client/offline-first.md, ADR 0003 --- `Routing/Hold`: no agent is routed to this issue automatically. To take it yourself, assign yourself. Vivy removes the hold when it is handed to an agent. Created for GRI-1818 from `plan/overview/roadmap.md` and the MVP plan on GRI-1761.
Author
Owner

Taken over by Vivy from Voidgazer (board reassignment, 2026-11-13).

Finding. Voidgazer's uncommitted draft (crates/client/src/store.rs) was written on a dev from 2026-10-20. Since then GRI-1854 landed a client state store as a JSON file (state.json) plus the hash-named media cache, so the draft no longer fits. Not used; it is still in the shared project workspace, uncommitted.

What I did — branch feature/GRI-1899-client-sqlite-state, commit 99a2f32:

  • client::state::Store keeps its API (open/get/update, now async) and is backed by client.db: SQLite through sqlx (ADR 0003), WAL, synchronous = FULL, one transaction per change, mode 0600 (it holds the token).
  • Tables: identity, playlist, manifest_entry. Schema in crates/client/migrations/0001_device_state.sql, run with sqlx::migrate!.
  • A device on an older build: state.json is imported once into the empty database, then renamed state.json.imported. A corrupt state.json stops the start and is left alone (it holds the identity).
  • The media files stay files named by sha256. Moving the index into the database would only add a second index that can disagree with the directory. Written down in plan/client/offline-first.md.
  • Workspace sqlx now holds only shared features; the server adds postgres, the client adds sqlite. The client no longer links the Postgres driver.
  • Not in scope, and still to do: the schedule (the protocol does not carry one) and queued reports (the client sends no reports yet).

"Done when: state survives a reboot with no network" is covered by state::tests::every_field_survives_a_restart and the existing tests/sync_survives_restart.rs.

Taken over by Vivy from Voidgazer (board reassignment, 2026-11-13). **Finding.** Voidgazer's uncommitted draft (`crates/client/src/store.rs`) was written on a `dev` from 2026-10-20. Since then GRI-1854 landed a client state store as a JSON file (`state.json`) plus the hash-named media cache, so the draft no longer fits. Not used; it is still in the shared project workspace, uncommitted. **What I did** — branch `feature/GRI-1899-client-sqlite-state`, commit `99a2f32`: - `client::state::Store` keeps its API (`open`/`get`/`update`, now `async`) and is backed by `client.db`: SQLite through `sqlx` (ADR 0003), WAL, `synchronous = FULL`, one transaction per change, mode `0600` (it holds the token). - Tables: `identity`, `playlist`, `manifest_entry`. Schema in `crates/client/migrations/0001_device_state.sql`, run with `sqlx::migrate!`. - A device on an older build: `state.json` is imported once into the empty database, then renamed `state.json.imported`. A corrupt `state.json` stops the start and is left alone (it holds the identity). - The media files stay files named by sha256. Moving the index into the database would only add a second index that can disagree with the directory. Written down in `plan/client/offline-first.md`. - Workspace `sqlx` now holds only shared features; the server adds `postgres`, the client adds `sqlite`. The client no longer links the Postgres driver. - Not in scope, and still to do: the schedule (the protocol does not carry one) and queued reports (the client sends no reports yet). "Done when: state survives a reboot with no network" is covered by `state::tests::every_field_survives_a_restart` and the existing `tests/sync_survives_restart.rs`.
vivy closed this issue 2026-11-13 15:51:18 +00:00
Author
Owner

Merged: PR #164, 99a2f32 (merge c4e72cd) is in dev. Required CI green (clippy, deny, fmt, test, frontend-web, repository boundary).

One finding, not fixed here: RUSTSEC-2026-0459 / CVE-2026-119375 in the bundled SQLite.
cargo deny advisories (not a required check, and already red on dev for glib, paste and proc-macro-error) flags libsqlite3-sys 0.30.1. Before this change it was only in the lock file. Now it is linked into the client.

  • The bug: SQLite < 3.54.0 can return uninitialised page-cache memory when it opens a database together with a crafted -wal file whose page size differs.
  • The fix is libsqlite3-sys >= 0.39. No sqlx release accepts that today: 0.8.x pins ^0.30.1, and 0.9.0 has >=0.30.1, <0.38.0.
  • Exposure on a display: the client opens only its own client.db and -wal. Both are mode 0600 in its data directory. To use the bug, an attacker must already be able to write as the client user, and that user can already read the token. So the risk is low.
  • Options when a fix is wanted sooner: (a) the sqlite-unbundled feature, which links the system libsqlite3 and takes the distro's security updates; (b) wait for an sqlx release that accepts 0.39. Recommendation: (b), and bump when it ships. (a) adds a runtime system dependency on every display, and Ubuntu 24.04's 3.45 is affected too until the distro backports the fix.
Merged: PR #164, `99a2f32` (merge `c4e72cd`) is in `dev`. Required CI green (clippy, deny, fmt, test, frontend-web, repository boundary). **One finding, not fixed here: RUSTSEC-2026-0459 / CVE-2026-119375 in the bundled SQLite.** `cargo deny advisories` (not a required check, and already red on `dev` for glib, paste and proc-macro-error) flags `libsqlite3-sys 0.30.1`. Before this change it was only in the lock file. Now it is linked into the client. - The bug: SQLite < 3.54.0 can return uninitialised page-cache memory when it opens a database together with a crafted `-wal` file whose page size differs. - The fix is `libsqlite3-sys >= 0.39`. No `sqlx` release accepts that today: 0.8.x pins `^0.30.1`, and 0.9.0 has `>=0.30.1, <0.38.0`. - Exposure on a display: the client opens only its own `client.db` and `-wal`. Both are mode `0600` in its data directory. To use the bug, an attacker must already be able to write as the client user, and that user can already read the token. So the risk is low. - Options when a fix is wanted sooner: (a) the `sqlite-unbundled` feature, which links the system `libsqlite3` and takes the distro's security updates; (b) wait for an `sqlx` release that accepts 0.39. Recommendation: (b), and bump when it ships. (a) adds a runtime system dependency on every display, and Ubuntu 24.04's 3.45 is affected too until the distro backports the fix.
Sign in to join this conversation.
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
Grimoirix/grimoire-of-digital-signage#41
No description provided.