kayg/infraternal

Watch

Fork 0

J435: verify fast red on RUSTSEC-2026-0459 (libsqlite3-sys / SQLite) #473

New issue

Open

opened 2026-11-09 11:41:19 +00:00 by kayg · 10 comments

kayg commented

2026-11-09 11:41:19 +00:00

Owner

scripts/verify fast fails its cargo-deny row on unmodified origin/dev (cedeb4d26) with RUSTSEC-2026-0459 (libsqlite3-sys, CVE-2026-119375). Every branch's gate is red on this row.

Advisory. SQLite before 3.54.0 can return uninitialized page-cache memory when a process opens a database together with a sibling -wal file whose page size differs from the database's. Local vector, needs the victim to open attacker-written files. Patched: libsqlite3-sys >= 0.39.0 (SQLite 3.54.x, shipped with rusqlite 0.41).

Plan (est. 1 h). 1. Exposure analysis. 2. Check whether a patch upgrade exists within sqlx 0.8. 3. If not, a time-limited deny.toml ignore. 4. verify quick, verify fast on compute01. Branch fix-rustsec-2026-0459 from origin/dev cedeb4d26. Job J435.

`scripts/verify fast` fails its cargo-deny row on unmodified origin/dev (cedeb4d26) with RUSTSEC-2026-0459 (`libsqlite3-sys`, CVE-2026-119375). Every branch's gate is red on this row. **Advisory.** SQLite before 3.54.0 can return uninitialized page-cache memory when a process opens a database together with a sibling `-wal` file whose page size differs from the database's. Local vector, needs the victim to open attacker-written files. Patched: `libsqlite3-sys >= 0.39.0` (SQLite 3.54.x, shipped with rusqlite 0.41). **Plan (est. 1 h).** 1. Exposure analysis. 2. Check whether a patch upgrade exists within sqlx 0.8. 3. If not, a time-limited deny.toml ignore. 4. `verify quick`, `verify fast` on compute01. Branch `fix-rustsec-2026-0459` from origin/dev cedeb4d26. Job J435.

kayg commented

2026-11-09 11:42:06 +00:00

Author

Owner

Finding: no in-line upgrade exists (stop condition hit); interim ignore added.

Branch fix-rustsec-2026-0459, base origin/dev cedeb4d26.

Evidence (crates.io API / upstream Cargo.toml, 2026-11-09):

Patched: libsqlite3-sys >= 0.39.0 (bundles SQLite 3.54.x, from rusqlite 0.41).
sqlx 0.8.6 → sqlx-sqlite requires libsqlite3-sys ^0.30.1 (Cargo.lock: 0.30.1).
sqlx 0.9.0 (newest) → libsqlite3-sys >=0.30.1, <0.38.0. Excludes 0.39, so even a sqlx major bump does not fix it.
SQLite is bundled: workspace uses sqlx feature sqlite = sqlx-sqlite/bundled.

Exposure verdict: not exploitable in our use. Needs an attacker-written database plus a mismatched -wal sibling opened by us.

One production open site: open_database in crates/infraternal-server/src/runtime.rs:848, path <data>/infraternal.sqlite, directory created and forced private (0700) by the server.
crates/infraternal/src/release_cli.rs opens the same file only after checking owner, private mode, regular file, one link.
No import/restore of a foreign database; everything else is sqlite::memory: (tests/examples).
Anyone who can plant a -wal there already controls the server's data folder.

Options for the owner (foundational: driver/engine, not decided here):

rusqlite 0.41 (+ own pool/migrations replacing sqlx's): fixed, but replaces the DB layer including sqlx::migrate! and the 54 migrations' runner.
Wait for a sqlx release that allows libsqlite3-sys >= 0.39. No cost, stays exposed (low).
sqlx sqlite-unbundled with system SQLite >= 3.54 (needs buildtime_bindgen and per-target packaging; hurts the static musl builds).

Interim: deny.toml ignore for RUSTSEC-2026-0459 with reason and expiry 2026-12-09. Cargo.lock unchanged, so Nix vendoring and the migration runner are unchanged (no dependency moved; the 54-migration check is not affected and was not re-run beyond the gate's own tests). Gate runs: compute01 slot queue, log ~/.cache/codex-scratch/j435/gate.log.

**Finding: no in-line upgrade exists (stop condition hit); interim ignore added.** Branch `fix-rustsec-2026-0459`, base origin/dev cedeb4d26. Evidence (crates.io API / upstream Cargo.toml, 2026-11-09): - Patched: `libsqlite3-sys >= 0.39.0` (bundles SQLite 3.54.x, from rusqlite 0.41). - sqlx 0.8.6 → `sqlx-sqlite` requires `libsqlite3-sys ^0.30.1` (Cargo.lock: 0.30.1). - sqlx 0.9.0 (newest) → `libsqlite3-sys >=0.30.1, <0.38.0`. Excludes 0.39, so even a sqlx major bump does not fix it. - SQLite is **bundled**: workspace uses sqlx feature `sqlite` = `sqlx-sqlite/bundled`. **Exposure verdict: not exploitable in our use.** Needs an attacker-written database plus a mismatched `-wal` sibling opened by us. - One production open site: `open_database` in `crates/infraternal-server/src/runtime.rs:848`, path `<data>/infraternal.sqlite`, directory created and forced private (0700) by the server. - `crates/infraternal/src/release_cli.rs` opens the same file only after checking owner, private mode, regular file, one link. - No import/restore of a foreign database; everything else is `sqlite::memory:` (tests/examples). - Anyone who can plant a `-wal` there already controls the server's data folder. **Options for the owner (foundational: driver/engine, not decided here):** 1. rusqlite 0.41 (+ own pool/migrations replacing sqlx's): fixed, but replaces the DB layer including `sqlx::migrate!` and the 54 migrations' runner. 2. Wait for a sqlx release that allows libsqlite3-sys >= 0.39. No cost, stays exposed (low). 3. sqlx `sqlite-unbundled` with system SQLite >= 3.54 (needs buildtime_bindgen and per-target packaging; hurts the static musl builds). **Interim:** `deny.toml` ignore for RUSTSEC-2026-0459 with reason and expiry **2026-12-09**. Cargo.lock unchanged, so Nix vendoring and the migration runner are unchanged (no dependency moved; the 54-migration check is not affected and was not re-run beyond the gate's own tests). Gate runs: compute01 slot queue, log `~/.cache/codex-scratch/j435/gate.log`.

kayg commented

2026-11-09 13:00:46 +00:00

Author

Owner

J438 start: measuring sqlx vs rusqlite (measure only, no decision, no product change).

Base: origin/dev 6df0f5bd5 (read-only worktree). No product branch; the harness is a scratch crate on compute01 under ~/.cache/codex-scratch/j438/ (outside the repo).

Plan (est. ~4 h):

Facts with primary sources: release cadence + SQLite/security pickup, worker-thread vs direct-call architecture, compile-time checks, migration runner, licences, static musl. (~45 min)
Pick the hot queries from crates/infraternal-server (session lookup, auth, layout/look.set, inbox, host list); scratch harness applying all 54 migrations with realistic rows; (a) sqlx 0.8.6, (b) rusqlite 0.41 + spawn_blocking pool, (c) rusqlite 0.41 one writer thread + N WAL readers. p50/p99/throughput at 1 and 64 tasks, repeated runs + spread; memory per connection/pool, startup, musl x86_64 size delta, compile time. Through build-slot; timing on the perf VM via perf-run. (~2.5 h)
Migration cost: query-site counts, sqlx::migrate! sites, _sqlx_migrations compatibility plan. (~30 min)

First counts (production code, excluding tests/examples): 61 files; 362 query(, 42 query_as(, 184 query_scalar(, 57 .begin(); 0 compile-time-checked macros (query!/query_as!/query_scalar!). sqlx::migrate! appears in 74 places, almost all in tests.

**J438 start: measuring sqlx vs rusqlite (measure only, no decision, no product change).** Base: origin/dev 6df0f5bd5 (read-only worktree). No product branch; the harness is a scratch crate on compute01 under `~/.cache/codex-scratch/j438/` (outside the repo). Plan (est. ~4 h): 1. Facts with primary sources: release cadence + SQLite/security pickup, worker-thread vs direct-call architecture, compile-time checks, migration runner, licences, static musl. (~45 min) 2. Pick the hot queries from crates/infraternal-server (session lookup, auth, layout/look.set, inbox, host list); scratch harness applying all 54 migrations with realistic rows; (a) sqlx 0.8.6, (b) rusqlite 0.41 + spawn_blocking pool, (c) rusqlite 0.41 one writer thread + N WAL readers. p50/p99/throughput at 1 and 64 tasks, repeated runs + spread; memory per connection/pool, startup, musl x86_64 size delta, compile time. Through build-slot; timing on the perf VM via perf-run. (~2.5 h) 3. Migration cost: query-site counts, `sqlx::migrate!` sites, `_sqlx_migrations` compatibility plan. (~30 min) First counts (production code, excluding tests/examples): 61 files; 362 `query(`, 42 `query_as(`, 184 `query_scalar(`, 57 `.begin()`; **0** compile-time-checked macros (`query!`/`query_as!`/`query_scalar!`). `sqlx::migrate!` appears in 74 places, almost all in tests.

kayg commented

2026-11-09 13:13:48 +00:00

Author

Owner

Progress (J435). Branch fix-rustsec-2026-0459 pushed, head 5acf28773 (one commit, deny.toml only).

Direct check on compute01 in the branch worktree: cargo deny check advisories licenses bans sources → advisories ok, bans ok, licenses ok, sources ok (exit 0).
verify quick + verify fast are queued behind 16 jobs in compute01's build-slot queue (started 12:41 CET, position 14 at last report). Log: ~/.cache/codex-scratch/j435/gate.log on compute01. I'm polling in the foreground and will post the verbatim gate output when it runs.

**Progress (J435).** Branch `fix-rustsec-2026-0459` pushed, head 5acf28773 (one commit, deny.toml only). - Direct check on compute01 in the branch worktree: `cargo deny check advisories licenses bans sources` → `advisories ok, bans ok, licenses ok, sources ok` (exit 0). - `verify quick` + `verify fast` are queued behind 16 jobs in compute01's build-slot queue (started 12:41 CET, position 14 at last report). Log: `~/.cache/codex-scratch/j435/gate.log` on compute01. I'm polling in the foreground and will post the verbatim gate output when it runs.

kayg commented

2026-11-09 13:32:35 +00:00

Author

Owner

Close-out (J435). The deny.toml exception merged to dev as ba79e6746 (orchestrator). On the orchestrator's instruction I killed my queued verify quick/verify fast waiter (build-slot PID 1978858) before it ran, so no verify quick or verify fast output exists from this job. Evidence I do have: cargo deny check advisories licenses bans sources on the branch → advisories ok, bans ok, licenses ok, sources ok; the orchestrator reports the compile check passed.

Branch fix-rustsec-2026-0459, head 5acf28773. Exposure verdict: not exploitable in our use (see the earlier finding). Exception expires 2026-12-09.

Open for the owner: the lasting fix is a database-driver decision, since no sqlx release accepts libsqlite3-sys >= 0.39. J438 is measuring sqlx against rusqlite. VM worktree and scratch removed.

**Close-out (J435).** The deny.toml exception merged to dev as ba79e6746 (orchestrator). On the orchestrator's instruction I killed my queued `verify quick`/`verify fast` waiter (build-slot PID 1978858) before it ran, so **no `verify quick` or `verify fast` output exists from this job**. Evidence I do have: `cargo deny check advisories licenses bans sources` on the branch → `advisories ok, bans ok, licenses ok, sources ok`; the orchestrator reports the compile check passed. Branch `fix-rustsec-2026-0459`, head 5acf28773. Exposure verdict: not exploitable in our use (see the earlier finding). Exception expires **2026-12-09**. Open for the owner: the lasting fix is a database-driver decision, since no sqlx release accepts `libsqlite3-sys >= 0.39`. J438 is measuring sqlx against rusqlite. VM worktree and scratch removed.

kayg commented

2026-11-09 13:56:13 +00:00

Author

Owner

J438 progress (no product change).

The harness is built and smoke-tested. Scratch: ~/.cache/codex-scratch/j438/ on code and on dev-02. It has crates bench-sqlx (a: pool like today; a2: sqlx with 1 writer + 7 readers), bench-rusqlite (0.41: b spawn_blocking pool; c 1 writer thread + 7 reader threads) and bench-rusqlite-032. The last is the same code on rusqlite 0.32 / SQLite 3.46, the version sqlx bundles, as an engine-version control.
Workloads are the real SQL from origin/dev 6df0f5bd5: session slide auth/sessions.rs:72 (a write on every request), host list remote_inventory.rs:170, layout resume canvas_store.rs:66, layout apply (the 8-statement BEGIN IMMEDIATE transaction in canvas_store.rs), and a mix of them. The DB has all 54 migrations and D170 rows: 50 users, 8k sessions, 1k hosts + certs, 200 terminals, 100k receipts.
Smoke check on dev-02 (noisy host, numbers only indicative): our own _sqlx_migrations-compatible runner validated all 54 sqlx-written rows. A fresh DB migrated by it has a schema identical to sqlx's.
Next: the x86_64 static musl build (first in dev-02's queue; compute01 has 16 queued), then 5 rotated repetitions on the perf VM through perf-run.

**J438 progress (no product change).** - The harness is built and smoke-tested. Scratch: `~/.cache/codex-scratch/j438/` on code and on dev-02. It has crates `bench-sqlx` (a: pool like today; a2: sqlx with 1 writer + 7 readers), `bench-rusqlite` (0.41: b spawn_blocking pool; c 1 writer thread + 7 reader threads) and `bench-rusqlite-032`. The last is the same code on rusqlite 0.32 / SQLite 3.46, the version sqlx bundles, as an engine-version control. - Workloads are the real SQL from origin/dev 6df0f5bd5: session slide `auth/sessions.rs:72` (a write on every request), host list `remote_inventory.rs:170`, layout resume `canvas_store.rs:66`, layout apply (the 8-statement `BEGIN IMMEDIATE` transaction in `canvas_store.rs`), and a mix of them. The DB has all 54 migrations and D170 rows: 50 users, 8k sessions, 1k hosts + certs, 200 terminals, 100k receipts. - Smoke check on dev-02 (noisy host, numbers only indicative): our own `_sqlx_migrations`-compatible runner validated all 54 sqlx-written rows. A fresh DB migrated by it has a schema identical to sqlx's. - Next: the x86_64 static musl build (first in dev-02's queue; compute01 has 16 queued), then 5 rotated repetitions on the perf VM through `perf-run`.

kayg referenced this issue

2026-11-09 14:33:14 +00:00

J400: enable predictive local echo automatically from measured RTT (D130) #441

kayg referenced this issue

2026-11-09 15:01:09 +00:00

D368: confirm and close a finished install’s own terminals during Take over (#404) #433

kayg commented

2026-11-09 15:17:41 +00:00

Author

Owner

J438: sqlx vs rusqlite, measured on infraternal's own hot queries (measure only, no decision)

I changed no product code and merged nothing. No repository code changed, so the gate does not apply. Scratch: code:~/.cache/codex-scratch/j438/; raw receipts are attached (j438-results.tar.gz).

Short answer to "are sqlx and rusqlite both mature and in rust? what's the difference? which one is better performing and updated often?"

Mature: both are mature, pure-Rust crates around the same C SQLite, and both are AGPL-compatible.
The difference:
- sqlx is async. Each connection lives on its own thread, and every statement is a message round trip to it.
- rusqlite is a plain synchronous call. We would add a small async front end ourselves, but a whole transaction then runs in one call.
Updates: rusqlite updates more often and ships SQLite fixes in 1–30 days. It shipped this fix 15 days after SQLite did. sqlx has historically taken 3–12 months, has no release with this fix yet, and won't backport it to our 0.8 line.
Performance: on the same SQLite engine, rusqlite is faster on every hot path we measured, from 25% to 78% at p50.

Owner table

The speed row is from the perf VM: median of 5 rotated runs, perf-run cpus 1–3, 0 steal. Spreads are in the detail table below.

	(a) sqlx 0.8.6, today	(a′) sqlx `main` + SQLite 3.54.1, the "wait for a release" path	(b) rusqlite 0.41, spawn_blocking pool	(c) rusqlite 0.41, 1 writer thread + 7 readers
Fixes RUSTSEC-2026-0459	no	only once released; no date	yes, today	yes, today
Speed, 1 caller, p50: session / host list / layout resume / layout write	65 / 542 / 149 / 490 µs	91 / n.m. / 160 / 446 µs	81 / 322 / 92 / 134 µs	79 / 328 / 92 / 146 µs
Speed, 64 tasks: session ops/s (p99)	12.3k (13.1 ms)	8.5k (24.9 ms)	11.5k (7.4 ms)	17.4k (3.9 ms)
layout write ops/s (p99)	1.23k (92 ms)	1.41k (78 ms)	2.05k (85 ms)	2.29k (64 ms)
mixed ops/s (p99)	7.4k (158 ms)	5.8k (200 ms)	7.7k (154 ms)	7.1k (64 ms)
Same engine, driver only (SQLite 3.46, rusqlite 0.32 vs sqlx 0.8.6), 1 caller p50	65 / 542 / 149 / 490 µs	–	49 / 281 / 60 / 106 µs (−25…−78%)	46 / 284 / 71 / 118 µs
Memory: warm 8-connection pool	6.2 MiB (≈310 KiB/conn), +8 threads	n.m.	6.4 MiB (≈385 KiB/conn), +9 threads	7.8 MiB, +8 threads
× expected sessions (D170: 200 terminals, 50 users)	the pool is shared, so a constant 6.2 MiB, ≈31 KiB/session	n.m.	6.4 MiB, ≈32 KiB/session	7.8 MiB, ≈39 KiB/session
Startup (open + validate 54 migrations) / fresh migrate of 54	1.5 ms / 86 ms	n.m.	7.4 ms (opens all 8 eagerly; lazy ≈1 ms) / 66 ms	same as (b)
Static x86_64 musl size, over a tokio-only baseline	+2,148 KiB	n.m.	+1,785 KiB (−363 KiB vs sqlx)	same as (b)
Cold compile, x86 musl, `-j4`, 3 runs	30–33 s	38 s (1 run)	19–22 s	same as (b)
Correctness	`begin_with` is not cancellation-safe, so we carry a spawn workaround (`plugin-api/src/storage.rs`). 8 pooled writers contend on SQLite's lock, which causes the busy-sleep tails above	same as (a)	a transaction in one closure always runs to completion; we own about 60 lines of pool code	same as (b), plus no write-lock contention by construction; we own about 80 lines
Consistency with the stack	async API in 62 production files and in the plugin API. Compile-time macros: we use none	0.8→0.9 major bump, about 1–3 days	sync calls behind our own `db` module. Same tokio, same zig static build (D213). Pool and migration crates still sit on rusqlite 0.40, so we write our own (about 60 lines each, both working in the scratch crate)	same as (b)
Migration cost	0	about 1–3 days, once released	about 2–3 weeks of job time (see §3)	same as (b)

n.m. = not measured.

Recommendation (mine, for the owner to decide): move to rusqlite 0.41 with layout (c): one writer thread and N reader threads, with our own _sqlx_migrations-compatible runner.

It is the only option that fixes the advisory now.

It is faster on every hot path, and it removes the write-lock tail: session p99 drops from 13.1 to 3.9 ms, and mixed p99 from 158 to 64 ms, at 64 tasks.

The binary is smaller, it compiles faster, and production databases keep their history unchanged.

Until the port lands, keep J435's deny.toml ignore. It expires 2026-12-09, so extend it once with the port's issue number. "Wait for sqlx 0.9.x" is cheap, but measured slower than today: it brings 3.54's cost without rusqlite's gains, and its release date is unknown.

Finding that affects every option: SQLite 3.54.1 costs about 30 µs per transaction on this workload

session slide, 1 caller p50	sqlx	rusqlite
SQLite 3.46.0	64 µs (sqlx 0.8.6)	50 µs (0.32)
SQLite 3.53.2	–	47 µs (0.40)
SQLite 3.54.1	91 µs (sqlx main)	76–81 µs (0.41)

The cost is the same in both drivers and absent on 3.53.2.
Read and write syscalls per op are unchanged (/proc/self/io), so it is CPU inside the engine, not I/O.
The only build-flag difference between libsqlite3-sys 0.38.2 and 0.39.1 is the dropped SQLITE_ENABLE_MEMORY_MANAGEMENT.
Any fix for RUSTSEC-2026-0459 pays this cost.
The session slide is a write on every authenticated request (auth/sessions.rs:72), so it is where the cost shows most. This is an observation for a later perf job, not a proposal.

Receipts: attr.jsonl, attr2.jsonl.

2. Method

Queries: the real SQL from origin/dev 6df0f5bd5:
- session slide UPDATE … RETURNING (auth/sessions.rs:72);
- host list (remote_inventory.rs:170);
- layout resume (canvas_store.rs:66);
- the 8-statement layout write under BEGIN IMMEDIATE, with the app writer mutex and the spawned BEGIN/COMMIT exactly as in canvas_store.rs;
- a mix of 80% session, 8% hosts, 8% resume and 4% write.
There is no inbox table on the server today, so the inbox was not measured.
Data: all 54 migrations applied through sqlx, D170 rows: 50 users, 4k live + 4k expired sessions, 1k hosts with certificates and bootstrap rows, 200 terminals, 6 KB layout documents with 64 journalled ops, 100k receipts. Every run starts from a fresh on-disk copy, in WAL, synchronous=NORMAL, with the same pragmas as open_database.
Pools: (a) copies open_database: 8 connections, test_before_acquire(false). (a2) is sqlx with a 1-writer pool plus a 7-reader pool. It shows that the tail comes from the pool shape, but that the per-statement round trip remains:

(a2) sqlx split value

session at 64 tasks, p99 5.1 ms

layout write, 1 caller, p50 474 µs
Controls: rusqlite 0.32 (SQLite 3.46, the same engine as sqlx 0.8.6) and rusqlite 0.40 (SQLite 3.53.2) separate the driver from the engine.
Caveats:
- The bench measures only the database layer (no HTTP, no JSON reducer).
- 64 tasks on 3 CPUs is above D170's load. Single-caller latency is what a user feels at the design point.
- The bench reads the revision column instead of parsing it out of the document JSON.

(a2) sqlx split	value
session at 64 tasks, p99	5.1 ms
layout write, 1 caller, p50	474 µs

Full per-cell table, with min–max spread over the 5 runs: table.md in the attachment. A few rows:

workload	tasks	variant	p50 µs (min–max)	p99 µs (min–max)	ops/s (min–max)
session	64	a-sqlx-pool	4,624 (4,600–4,678)	13,084 (13,045–19,823)	12,305 (12,158–12,353)
session	64	c-rusqlite041-split	3,669 (2,894–3,707)	3,887 (3,214–3,953)	17,412 (17,195–22,106)
apply	1	a-sqlx-pool	490 (487–493)	10,338 (7,129–12,106)	1,209 (1,163–1,347)
apply	1	b-rusqlite041-pool	134 (132–138)	10,754 (7,193–15,124)	2,009 (1,493–2,412)
mixed	64	a-sqlx-pool	1,853 (1,804–2,027)	157,806 (156,080–160,497)	7,411 (7,157–7,451)
mixed	64	c-rusqlite041-split	5,341 (5,278–5,448)	64,108 (62,887–79,236)	7,059 (6,886–7,346)

Syscalls per op:

operation	sqlx	rusqlite
session	14 reads	0.01 reads
layout write	25.7 reads + 25.2 writes	2.4 reads + 17.5 writes

The extra reads are sqlx's per-statement worker wake-ups.

1. Facts (primary sources, checked 2026-11-09)

Maintenance and security pickup

	sqlx (+ sqlx-sqlite)	rusqlite (+ libsqlite3-sys)
Releases 2023 / 2024 / 2025 / 2026	8 / 5 / 5 / 1	2 / 3 / 6 / 6
Latest	0.9.0, 2026-05-21; no stable release between 0.8.6 (2025-05-19) and 0.9.0	0.41.1, 2026-10-28 (libsqlite3-sys 0.39.1, 2026-10-25)
SQLite 3.54.0 (2026-10-09), the RUSTSEC-2026-0459 fix	not allowed by any release. sqlx 0.9.0 caps libsqlite3-sys `>=0.30.1, <0.38.0`. PR #4502 raising the cap to `<0.40` merged to `main` 2026-11-08, unreleased	bundled in libsqlite3-sys 0.39.0, 15 days after SQLite
Past lag, SQLite release → usable	3.45.0: 190 d; 3.46.0: 93 d; 3.51.1: 174 d; RUSTSEC-2022-0090: 346 d	1–30 d (3.39.2: 1 d; 3.45.0: 12 d; 3.46.0: 30 d; 3.54.0: 15 d)
Backports to our line (0.8)	declined by the maintainer (#4241, 2026-05-21). 0.8.x will never get the fix; we need 0.9 + an unreleased patch	n/a (we would track the latest)

Sources: crates.io versions/dependencies API for each crate; RUSTSEC-2026-0459; sqlite.org/changes; libsqlite3-sys/sqlite3/sqlite3.h at each rusqlite tag. The repo moved from launchbadge/sqlx to transact-rs/sqlx.

Architecture

sqlx-sqlite: each connection owns an OS thread (ConnectionWorker, worker.rs @ v0.8.6) fed by a flume channel. Every statement is one channel round trip and one wake-up, including each statement inside a transaction. The statement cache holds 100 by default.
rusqlite: Connection is Send, not Sync, and calls are plain synchronous FFI (lib.rs @ v0.41.1). Async callers need spawn_blocking or an owned thread. A whole transaction runs in one closure, so it is one hop. prepare_cached holds 16 by default and can be set higher.

Compile-time checked queries: sqlx has query!/query_as!, with an offline .sqlx cache and imperfect SQLite nullability inference (e.g. #4322). rusqlite has none. infraternal uses none: 0 macro sites. Every query is the runtime sqlx::query(...) form, so we lose nothing here.

Migration runner: sqlx _sqlx_migrations (version, description, installed_on, success, checksum = SHA-384 of the file, execution_time; migrate.rs). The server doesn't call sqlx::migrate! at runtime. build_support/migrations.rs already generates the 54-entry table at build time, with original SHA-384 checksums and compacted SQL, and auth/migration_compat.rs does the version-20→22 retag. rusqlite_migration 2.6.0 (Apache-2.0, PRAGMA user_version) and refinery 0.10 (MIT, its own refinery_schema_history) both still require rusqlite ≤ 0.40, and neither reads _sqlx_migrations.

Licences (all AGPL-compatible): sqlx MIT OR Apache-2.0; rusqlite MIT; libsqlite3-sys MIT; rusqlite_migration Apache-2.0; refinery MIT; deadpool-sqlite MIT OR Apache-2.0; r2d2_sqlite MIT; tokio-rusqlite MIT; async-sqlite MIT; SQLite public domain.

Static musl: both use the same libsqlite3-sys bundled build: cc with pre-generated bindings and no bindgen. Our zig cc (D213) builds both; measured below. rusqlite 0.41 needs SQLite ≥ 3.45.3 and MSRV 1.88/1.89, both fine on our 1.99.1.

Async pool crates: only async-sqlite 0.7.0 supports rusqlite 0.41 today. deadpool-sqlite, r2d2_sqlite and tokio-rusqlite are on 0.40. links = "sqlite3" forbids two libsqlite3-sys in one tree, so any of these would pin us to 0.40, which is still vulnerable. Variants (b) and (c) below use about 60 lines of our own code and no pool crate.

3. Cost of replacing sqlx (origin/dev `6df0f5bd5`)

	production code	tests / examples
files that use sqlx	62 (49 server, 6 mesh, 3 `infraternal`, 2 plugin-api, 1 release, 1 build_support)	133
`sqlx::query(` / `query_as(` / `query_scalar(` / `raw_sql(`	360 / 43 / 183 / 3 → 589 statements	605 / 58 / 259 / 48 → 970
`.execute/.fetch_*` calls	637	1,061
transactions (`begin`, `begin_with`, `begin_immediate`)	90 sites, 50 `Transaction<…>` signatures	48
`SqlitePool` in signatures/fields	150	234
`sqlx::Error` in error types	45	4
compile-time macros (`query!` and friends)	0	0
`sqlx::migrate!`	0 at runtime: `build_support/migrations.rs` generates a `Migrator` literal	81 (test fixtures that run or truncate the migrator)
public plugin boundary	`infraternal-plugin-api` (`socket-host`) exposes `sqlx::SqlitePool` and `storage::begin_immediate -> sqlx::Transaction` to the mesh and release plugins

Runner replacement. Use our own runner, about 60 lines; bench-rusqlite::migrate in the scratch crate is a working version. Neither rusqlite_migration nor refinery fits:

both pin rusqlite ≤ 0.40, which is still vulnerable;
user_version and refinery_schema_history would each need a one-time history conversion on production.

The own runner keeps the existing _sqlx_migrations table exactly as it is:

same columns;
SHA-384 of the original file, already computed by build_support/migrations.rs;
dirty-row refusal, ignore_missing=false, checksum validation;
one transaction per migration (none of the 54 uses -- no-transaction).

The version-20→22 retag in auth/migration_compat.rs ports as-is. Production databases need no conversion step.

Measured:

On a DB written by sqlx 0.8.6, the runner validated all 54/54 history rows and applied 0.
On an empty DB it applied all 54, and the resulting sqlite_master is identical to the sqlx-built schema.
The migration SQL files are unchanged, so D347 holds (never edit an applied migration).

Receipts: results/misc.jsonl (kinds compat and fresh).

Risks:

SQLite engine 3.46 → 3.54. Query plans and behaviour can change. The hot queries are measured below, and the full test suite would run on 3.54. Note that sqlx 0.9 plus a future patch would make the same jump.
The async boundary. Each async fn that awaits statements one by one inside a transaction becomes one synchronous closure. A transaction that awaits non-DB work (network, crypto) between statements has to be split. That is a design review per site, not a search-and-replace.
Plugin API change. The mesh and release plugins move with it.
Tests. 970 statements, mostly fixtures. A thin db::query(sql).bind().fetch_*() shim keeps them mechanical.

Estimate:

Production: 589 statements in 62 files, about 4–6 job-days. Transaction code (90 sites) is the slow part.
Tests: about 3–5 job-days with a shim.
Runner, compatibility test and pool: about 1 day.
Gate, perf and real-server proof: about 1–2 days.
Total: about 2–3 weeks of job time, with review.

A shim that keeps the query().bind() shape but sends each statement over a channel would port faster, but it gives back most of the latency win shown below. The win comes from running a whole transaction in one closure.

Cheaper alternatives, for comparison:

(i) Keep sqlx and move to 0.9.x once a release includes PR #4502. That is a 0.8→0.9 major bump of about 1–3 days, the date is unknown, and the lag history above is 3–12 months.
(ii) Keep sqlx 0.8 with the deny.toml ignore until 2026-12-09. Exposure is low per J435.
(iii) Keep sqlx and change only the pool shape: one writer connection plus readers (variant a2 below). It is small, and it does not fix the advisory.

Receipts

code ~/.cache/codex-scratch/j438/: bench/ (harness: common/common.rs, bench-sqlx, bench-sqlx-main, bench-rusqlite{,-032,-040}, baseline), perf/ (run scripts), results/results/{rep-1..5,misc,attr,attr2}.jsonl, table.md, attr2-table.md, sizes.txt.
perf VM ~/j438/: results/, pr-*/perf-run-*.{before,after}.txt (quiet checks), drive*.log. The binary SHA-256 values are in sizes.txt, and bin2/bin3 are listed in binaries.sha256.
dev-02 ~/.cache/codex-scratch/j438/logs/build-times.jsonl: cold build times. The fresh-zig-cache runs are the ones quoted; build-times-zigcached.jsonl holds the cached ones, which are not used.
Not measured: host list on sqlx main; aarch64 timings; the real infraternal binary's size delta. The probes measure the database library's own addition, so the product delta can be smaller where sqlx shares dependencies.

## J438: sqlx vs rusqlite, measured on infraternal's own hot queries (measure only, no decision) I changed no product code and merged nothing. **No repository code changed, so the gate does not apply.** Scratch: `code:~/.cache/codex-scratch/j438/`; raw receipts are attached (`j438-results.tar.gz`). **Short answer to "are sqlx and rusqlite both mature and in rust? what's the difference? which one is better performing and updated often?"** - **Mature:** both are mature, pure-Rust crates around the same C SQLite, and both are AGPL-compatible. - **The difference:** - sqlx is async. Each connection lives on its own thread, and every statement is a message round trip to it. - rusqlite is a plain synchronous call. We would add a small async front end ourselves, but a whole transaction then runs in one call. - **Updates:** rusqlite updates more often and ships SQLite fixes in 1–30 days. It shipped this fix 15 days after SQLite did. sqlx has historically taken 3–12 months, has **no release with this fix yet**, and won't backport it to our 0.8 line. - **Performance:** on the same SQLite engine, rusqlite is faster on every hot path we measured, from 25% to 78% at p50. ### Owner table The speed row is from the perf VM: median of 5 rotated runs, perf-run cpus 1–3, 0 steal. Spreads are in the detail table below. | | (a) sqlx 0.8.6, today | (a′) sqlx `main` + SQLite 3.54.1, the "wait for a release" path | (b) rusqlite 0.41, spawn_blocking pool | (c) rusqlite 0.41, 1 writer thread + 7 readers | |---|---|---|---|---| | **Fixes RUSTSEC-2026-0459** | no | only once released; no date | **yes, today** | **yes, today** | | **Speed, 1 caller, p50**: session / host list / layout resume / layout write | 65 / 542 / 149 / 490 µs | 91 / n.m. / 160 / 446 µs | 81 / 322 / 92 / 134 µs | 79 / 328 / 92 / 146 µs | | **Speed, 64 tasks**: session ops/s (p99) | 12.3k (13.1 ms) | 8.5k (24.9 ms) | 11.5k (7.4 ms) | **17.4k (3.9 ms)** | | layout write ops/s (p99) | 1.23k (92 ms) | 1.41k (78 ms) | 2.05k (85 ms) | **2.29k (64 ms)** | | mixed ops/s (p99) | 7.4k (158 ms) | 5.8k (200 ms) | 7.7k (154 ms) | 7.1k (**64 ms**) | | **Same engine, driver only** (SQLite 3.46, rusqlite 0.32 vs sqlx 0.8.6), 1 caller p50 | 65 / 542 / 149 / 490 µs | – | 49 / 281 / 60 / 106 µs (−25…−78%) | 46 / 284 / 71 / 118 µs | | **Memory**: warm 8-connection pool | 6.2 MiB (≈310 KiB/conn), +8 threads | n.m. | 6.4 MiB (≈385 KiB/conn), +9 threads | 7.8 MiB, +8 threads | | **× expected sessions** (D170: 200 terminals, 50 users) | the pool is shared, so a constant 6.2 MiB, ≈31 KiB/session | n.m. | 6.4 MiB, ≈32 KiB/session | 7.8 MiB, ≈39 KiB/session | | **Startup** (open + validate 54 migrations) / fresh migrate of 54 | 1.5 ms / 86 ms | n.m. | 7.4 ms (opens all 8 eagerly; lazy ≈1 ms) / 66 ms | same as (b) | | **Static x86_64 musl size**, over a tokio-only baseline | +2,148 KiB | n.m. | **+1,785 KiB (−363 KiB vs sqlx)** | same as (b) | | **Cold compile**, x86 musl, `-j4`, 3 runs | 30–33 s | 38 s (1 run) | **19–22 s** | same as (b) | | **Correctness** | `begin_with` is not cancellation-safe, so we carry a spawn workaround (`plugin-api/src/storage.rs`). 8 pooled writers contend on SQLite's lock, which causes the busy-sleep tails above | same as (a) | a transaction in one closure always runs to completion; we own about 60 lines of pool code | same as (b), plus no write-lock contention by construction; we own about 80 lines | | **Consistency with the stack** | async API in 62 production files and in the plugin API. Compile-time macros: we use none | 0.8→0.9 major bump, about 1–3 days | sync calls behind our own `db` module. Same tokio, same zig static build (D213). Pool and migration crates still sit on rusqlite 0.40, so we write our own (about 60 lines each, both working in the scratch crate) | same as (b) | | **Migration cost** | 0 | about 1–3 days, once released | about 2–3 weeks of job time (see §3) | same as (b) | n.m. = not measured. > **Recommendation (mine, for the owner to decide):** move to **rusqlite 0.41 with layout (c)**: one writer thread and N reader threads, with our own `_sqlx_migrations`-compatible runner. > - It is the only option that fixes the advisory now. > - It is faster on every hot path, and it removes the write-lock tail: session p99 drops from 13.1 to 3.9 ms, and mixed p99 from 158 to 64 ms, at 64 tasks. > - The binary is smaller, it compiles faster, and production databases keep their history unchanged. > > Until the port lands, keep J435's `deny.toml` ignore. It expires 2026-12-09, so extend it once with the port's issue number. "Wait for sqlx 0.9.x" is cheap, but measured *slower* than today: it brings 3.54's cost without rusqlite's gains, and its release date is unknown. ### Finding that affects every option: SQLite 3.54.1 costs about 30 µs per transaction on this workload | session slide, 1 caller p50 | sqlx | rusqlite | |---|---|---| | SQLite 3.46.0 | 64 µs (sqlx 0.8.6) | 50 µs (0.32) | | SQLite 3.53.2 | – | 47 µs (0.40) | | SQLite 3.54.1 | **91 µs** (sqlx main) | **76–81 µs** (0.41) | - The cost is the same in both drivers and absent on 3.53.2. - Read and write syscalls per op are unchanged (`/proc/self/io`), so it is CPU inside the engine, not I/O. - The only build-flag difference between libsqlite3-sys 0.38.2 and 0.39.1 is the dropped `SQLITE_ENABLE_MEMORY_MANAGEMENT`. - Any fix for RUSTSEC-2026-0459 pays this cost. - The session slide is a write on every authenticated request (`auth/sessions.rs:72`), so it is where the cost shows most. This is an observation for a later perf job, not a proposal. Receipts: `attr.jsonl`, `attr2.jsonl`. ### 2. Method - **Queries:** the real SQL from origin/dev 6df0f5bd5: - session slide `UPDATE … RETURNING` (`auth/sessions.rs:72`); - host list (`remote_inventory.rs:170`); - layout resume (`canvas_store.rs:66`); - the 8-statement layout write under `BEGIN IMMEDIATE`, with the app writer mutex and the spawned BEGIN/COMMIT exactly as in `canvas_store.rs`; - a mix of 80% session, 8% hosts, 8% resume and 4% write. - **There is no inbox table on the server today**, so the inbox was not measured. - **Data:** all 54 migrations applied through sqlx, D170 rows: 50 users, 4k live + 4k expired sessions, 1k hosts with certificates and bootstrap rows, 200 terminals, 6 KB layout documents with 64 journalled ops, 100k receipts. Every run starts from a fresh on-disk copy, in WAL, `synchronous=NORMAL`, with the same pragmas as `open_database`. - **Pools:** (a) copies `open_database`: 8 connections, `test_before_acquire(false)`. (a2) is sqlx with a 1-writer pool plus a 7-reader pool. It shows that the tail comes from the pool shape, but that the per-statement round trip remains: | (a2) sqlx split | value | |---|---| | session at 64 tasks, p99 | 5.1 ms | | layout write, 1 caller, p50 | 474 µs | - **Controls:** rusqlite 0.32 (SQLite 3.46, the same engine as sqlx 0.8.6) and rusqlite 0.40 (SQLite 3.53.2) separate the driver from the engine. - **Caveats:** - The bench measures only the database layer (no HTTP, no JSON reducer). - 64 tasks on 3 CPUs is above D170's load. Single-caller latency is what a user feels at the design point. - The bench reads the `revision` column instead of parsing it out of the document JSON. Full per-cell table, with min–max spread over the 5 runs: `table.md` in the attachment. A few rows: | workload | tasks | variant | p50 µs (min–max) | p99 µs (min–max) | ops/s (min–max) | |---|---|---|---|---|---| | session | 64 | a-sqlx-pool | 4,624 (4,600–4,678) | 13,084 (13,045–19,823) | 12,305 (12,158–12,353) | | session | 64 | c-rusqlite041-split | 3,669 (2,894–3,707) | 3,887 (3,214–3,953) | 17,412 (17,195–22,106) | | apply | 1 | a-sqlx-pool | 490 (487–493) | 10,338 (7,129–12,106) | 1,209 (1,163–1,347) | | apply | 1 | b-rusqlite041-pool | 134 (132–138) | 10,754 (7,193–15,124) | 2,009 (1,493–2,412) | | mixed | 64 | a-sqlx-pool | 1,853 (1,804–2,027) | 157,806 (156,080–160,497) | 7,411 (7,157–7,451) | | mixed | 64 | c-rusqlite041-split | 5,341 (5,278–5,448) | 64,108 (62,887–79,236) | 7,059 (6,886–7,346) | **Syscalls per op:** | operation | sqlx | rusqlite | |---|---|---| | session | 14 reads | 0.01 reads | | layout write | 25.7 reads + 25.2 writes | 2.4 reads + 17.5 writes | The extra reads are sqlx's per-statement worker wake-ups. ### 1. Facts (primary sources, checked 2026-11-09) **Maintenance and security pickup** | | sqlx (+ sqlx-sqlite) | rusqlite (+ libsqlite3-sys) | |---|---|---| | Releases 2023 / 2024 / 2025 / 2026 | 8 / 5 / 5 / 1 | 2 / 3 / 6 / 6 | | Latest | 0.9.0, 2026-05-21; no stable release between 0.8.6 (2025-05-19) and 0.9.0 | 0.41.1, 2026-10-28 (libsqlite3-sys 0.39.1, 2026-10-25) | | SQLite 3.54.0 (2026-10-09), the RUSTSEC-2026-0459 fix | not allowed by any release. sqlx 0.9.0 caps libsqlite3-sys `>=0.30.1, <0.38.0`. [PR #4502](https://github.com/transact-rs/sqlx/pull/4502) raising the cap to `<0.40` merged to `main` 2026-11-08, unreleased | bundled in libsqlite3-sys 0.39.0, 15 days after SQLite | | Past lag, SQLite release → usable | 3.45.0: 190 d; 3.46.0: 93 d; 3.51.1: 174 d; RUSTSEC-2022-0090: 346 d | 1–30 d (3.39.2: 1 d; 3.45.0: 12 d; 3.46.0: 30 d; 3.54.0: 15 d) | | Backports to our line (0.8) | declined by the maintainer ([#4241](https://github.com/transact-rs/sqlx/issues/4241), 2026-05-21). **0.8.x will never get the fix; we need 0.9 + an unreleased patch** | n/a (we would track the latest) | Sources: crates.io versions/dependencies API for each crate; [RUSTSEC-2026-0459](https://github.com/rustsec/advisory-db/blob/main/crates/libsqlite3-sys/RUSTSEC-2026-0459.md); [sqlite.org/changes](https://sqlite.org/changes.html); `libsqlite3-sys/sqlite3/sqlite3.h` at each rusqlite tag. The repo moved from launchbadge/sqlx to transact-rs/sqlx. **Architecture** - sqlx-sqlite: each connection owns an OS thread (`ConnectionWorker`, [worker.rs @ v0.8.6](https://github.com/transact-rs/sqlx/blob/v0.8.6/sqlx-sqlite/src/connection/worker.rs)) fed by a `flume` channel. **Every statement is one channel round trip and one wake-up**, including each statement inside a transaction. The statement cache holds 100 by default. - rusqlite: `Connection` is `Send`, not `Sync`, and calls are plain synchronous FFI ([lib.rs @ v0.41.1](https://github.com/rusqlite/rusqlite/blob/v0.41.1/src/lib.rs)). Async callers need `spawn_blocking` or an owned thread. **A whole transaction runs in one closure, so it is one hop.** `prepare_cached` holds 16 by default and can be set higher. **Compile-time checked queries:** sqlx has `query!`/`query_as!`, with an offline `.sqlx` cache and imperfect SQLite nullability inference (e.g. [#4322](https://github.com/transact-rs/sqlx/issues/4322)). rusqlite has none. **infraternal uses none: 0 macro sites. Every query is the runtime `sqlx::query(...)` form**, so we lose nothing here. **Migration runner:** sqlx `_sqlx_migrations` (version, description, installed_on, success, checksum = SHA-384 of the file, execution_time; [migrate.rs](https://github.com/transact-rs/sqlx/blob/v0.8.6/sqlx-sqlite/src/migrate.rs)). The server doesn't call `sqlx::migrate!` at runtime. `build_support/migrations.rs` already generates the 54-entry table at build time, with original SHA-384 checksums and compacted SQL, and `auth/migration_compat.rs` does the version-20→22 retag. rusqlite_migration 2.6.0 (Apache-2.0, `PRAGMA user_version`) and refinery 0.10 (MIT, its own `refinery_schema_history`) **both still require rusqlite ≤ 0.40**, and neither reads `_sqlx_migrations`. **Licences (all AGPL-compatible):** sqlx MIT OR Apache-2.0; rusqlite MIT; libsqlite3-sys MIT; rusqlite_migration Apache-2.0; refinery MIT; deadpool-sqlite MIT OR Apache-2.0; r2d2_sqlite MIT; tokio-rusqlite MIT; async-sqlite MIT; SQLite public domain. **Static musl:** both use the same `libsqlite3-sys` `bundled` build: `cc` with pre-generated bindings and no bindgen. Our `zig cc` (D213) builds both; measured below. rusqlite 0.41 needs SQLite ≥ 3.45.3 and MSRV 1.88/1.89, both fine on our 1.99.1. **Async pool crates:** only async-sqlite 0.7.0 supports rusqlite 0.41 today. deadpool-sqlite, r2d2_sqlite and tokio-rusqlite are on 0.40. `links = "sqlite3"` forbids two libsqlite3-sys in one tree, so any of these would pin us to 0.40, which is still vulnerable. Variants (b) and (c) below use about 60 lines of our own code and no pool crate. ### 3. Cost of replacing sqlx (origin/dev 6df0f5bd5) | | production code | tests / examples | |---|---|---| | files that use sqlx | 62 (49 server, 6 mesh, 3 `infraternal`, 2 plugin-api, 1 release, 1 build_support) | 133 | | `sqlx::query(` / `query_as(` / `query_scalar(` / `raw_sql(` | 360 / 43 / 183 / 3 → **589 statements** | 605 / 58 / 259 / 48 → 970 | | `.execute/.fetch_*` calls | 637 | 1,061 | | transactions (`begin`, `begin_with`, `begin_immediate`) | 90 sites, 50 `Transaction<…>` signatures | 48 | | `SqlitePool` in signatures/fields | 150 | 234 | | `sqlx::Error` in error types | 45 | 4 | | compile-time macros (`query!` and friends) | **0** | 0 | | `sqlx::migrate!` | **0 at runtime**: `build_support/migrations.rs` generates a `Migrator` literal | 81 (test fixtures that run or truncate the migrator) | | public plugin boundary | `infraternal-plugin-api` (`socket-host`) exposes `sqlx::SqlitePool` and `storage::begin_immediate -> sqlx::Transaction` to the mesh and release plugins | | **Runner replacement.** Use our own runner, about 60 lines; `bench-rusqlite::migrate` in the scratch crate is a working version. Neither rusqlite_migration nor refinery fits: - both pin rusqlite ≤ 0.40, which is still vulnerable; - `user_version` and `refinery_schema_history` would each need a one-time history conversion on production. The own runner keeps the existing `_sqlx_migrations` table exactly as it is: - same columns; - SHA-384 of the original file, already computed by `build_support/migrations.rs`; - dirty-row refusal, `ignore_missing=false`, checksum validation; - one transaction per migration (none of the 54 uses `-- no-transaction`). The version-20→22 retag in `auth/migration_compat.rs` ports as-is. Production databases need no conversion step. **Measured:** - On a DB written by sqlx 0.8.6, the runner validated all **54/54** history rows and applied 0. - On an empty DB it applied all 54, and the resulting `sqlite_master` is **identical** to the sqlx-built schema. - The migration SQL files are unchanged, so D347 holds (never edit an applied migration). Receipts: `results/misc.jsonl` (kinds `compat` and `fresh`). **Risks:** 1. **SQLite engine 3.46 → 3.54.** Query plans and behaviour can change. The hot queries are measured below, and the full test suite would run on 3.54. Note that sqlx 0.9 plus a future patch would make the same jump. 2. **The async boundary.** Each `async fn` that awaits statements one by one inside a transaction becomes one synchronous closure. A transaction that awaits non-DB work (network, crypto) between statements has to be split. That is a design review per site, not a search-and-replace. 3. **Plugin API change.** The mesh and release plugins move with it. 4. **Tests.** 970 statements, mostly fixtures. A thin `db::query(sql).bind().fetch_*()` shim keeps them mechanical. **Estimate:** - Production: 589 statements in 62 files, about 4–6 job-days. Transaction code (90 sites) is the slow part. - Tests: about 3–5 job-days with a shim. - Runner, compatibility test and pool: about 1 day. - Gate, perf and real-server proof: about 1–2 days. - **Total: about 2–3 weeks of job time, with review.** A shim that keeps the `query().bind()` shape but sends each statement over a channel would port faster, but it gives back most of the latency win shown below. The win comes from running a whole transaction in one closure. **Cheaper alternatives, for comparison:** - (i) Keep sqlx and move to 0.9.x once a release includes PR #4502. That is a 0.8→0.9 major bump of about 1–3 days, the date is unknown, and the lag history above is 3–12 months. - (ii) Keep sqlx 0.8 with the deny.toml ignore until 2026-12-09. Exposure is low per J435. - (iii) Keep sqlx and change only the pool shape: one writer connection plus readers (variant a2 below). It is small, and it does not fix the advisory. ### Receipts - **code** `~/.cache/codex-scratch/j438/`: `bench/` (harness: `common/common.rs`, `bench-sqlx`, `bench-sqlx-main`, `bench-rusqlite{,-032,-040}`, `baseline`), `perf/` (run scripts), `results/results/{rep-1..5,misc,attr,attr2}.jsonl`, `table.md`, `attr2-table.md`, `sizes.txt`. - **perf VM** `~/j438/`: `results/`, `pr-*/perf-run-*.{before,after}.txt` (quiet checks), `drive*.log`. The binary SHA-256 values are in `sizes.txt`, and `bin2`/`bin3` are listed in `binaries.sha256`. - **dev-02** `~/.cache/codex-scratch/j438/logs/build-times.jsonl`: cold build times. The fresh-zig-cache runs are the ones quoted; `build-times-zigcached.jsonl` holds the cached ones, which are not used. - **Not measured:** host list on sqlx `main`; aarch64 timings; the real `infraternal` binary's size delta. The probes measure the database library's own addition, so the product delta can be smaller where sqlx shares dependencies.

j438-results.tar.gz

76 KiB

kayg commented

2026-11-09 17:05:47 +00:00

Author

Owner

J449 start: D379 slice 1 (pool + migration runner, no query sites moved).

Branch rusqlite-slice1, base origin/dev bd551e549. VM worktree: dev-02 ~/worktrees/j449 (gates go where the queue is shorter).

Finding (blocks "rusqlite alongside sqlx" as briefed): Cargo cannot resolve sqlx 0.8.6 and rusqlite 0.41 in one graph. Both pull libsqlite3-sys (^0.30.1 vs ^0.39), which declares links = "sqlite3", and only one package per links value may exist in a resolve. That covers optional and dev dependencies too, so a feature flag doesn't help. Receipt (dev-02, ~/.cache/codex-scratch/j449/links-probe, cargo generate-lockfile):

package `libsqlite3-sys` links to the native library `sqlite3`, but it conflicts with a previous package which links to `sqlite3` as well:
package `libsqlite3-sys v0.30.1`
    ... which satisfies dependency `libsqlite3-sys = "^0.30.1"` of package `sqlx-sqlite v0.8.6`

This also hits D379's later slices: every half-ported binary links both drivers.

Options:

Vendor sqlx-sqlite 0.8.6 with only its libsqlite3-sys requirement raised to 0.39, as a [patch.crates-io] entry next to the existing vendor/russh. One SQLite (3.54.1) serves both drivers, so slices 1 to 3 build and ship incrementally. Side effects:
- sqlx paths move to SQLite 3.54 now, not at the end of the port;
- RUSTSEC-2026-0459 is fixed from slice 1, and the deny.toml ignore can go;
- J438 measured the ~30 µs/transaction 3.54 cost, which every fix pays.
- Feasibility probe (does sqlx-sqlite 0.8.6 build and run unmodified on 0.39?) is queued on dev-02, ~/.cache/codex-scratch/j449/patch-probe/run.log.
A separate-workspace crate (excluded, own Cargo.lock) until sqlx is gone. Production stays untouched. It needs a second Nix vendoring lock, new verify rows for that crate, and a two-binary compat test (a sqlx example writes the DB, the rusqlite test reads it). Slices 2 and 3 still can't ship incrementally: the port becomes one big-bang switch.

Recommendation: option 1, if the probe and the full server test suite pass on 3.54. It is less infrastructure and it is the only way D379's slice plan builds. The pool and runner code are the same either way, so I'm writing them now. I'll proceed with option 1 if the probe is clean and say so here. The orchestrator can redirect to option 2 before I push the packaging commit.

Plan (≈6 h):

New crate crates/infraternal-db: writer thread with a bounded queue, transaction closures, N WAL readers, tokio async API, explicit pragmas, and cancellation and panic semantics (2 h).
_sqlx_migrations runner fed by the same build_support generator: checksums, dirty, missing and mismatch refusal, plus the version-20→22 retag left to the server (1 h).
Red-first tests: queue ordering, cancellation, panic, full queue, reader isolation, and compat/fresh schema diff against today's sqlx path on a copy-shaped DB (1.5 h).
Port J438's harness as an opt-in bench, then perf VM runs against sqlx (holds ≤20 min, announced here) (1 h).
Gates: verify quick, then verify fast; plus cargo deny and Nix vendoring (0.5 h + queue).

**J449 start: D379 slice 1 (pool + migration runner, no query sites moved).** Branch `rusqlite-slice1`, base origin/dev bd551e549. VM worktree: dev-02 `~/worktrees/j449` (gates go where the queue is shorter). **Finding (blocks "rusqlite alongside sqlx" as briefed):** Cargo cannot resolve sqlx 0.8.6 and rusqlite 0.41 in one graph. Both pull `libsqlite3-sys` (`^0.30.1` vs `^0.39`), which declares `links = "sqlite3"`, and only one package per `links` value may exist in a resolve. That covers optional and dev dependencies too, so a feature flag doesn't help. Receipt (dev-02, `~/.cache/codex-scratch/j449/links-probe`, `cargo generate-lockfile`): ``` package `libsqlite3-sys` links to the native library `sqlite3`, but it conflicts with a previous package which links to `sqlite3` as well: package `libsqlite3-sys v0.30.1` ... which satisfies dependency `libsqlite3-sys = "^0.30.1"` of package `sqlx-sqlite v0.8.6` ``` This also hits D379's later slices: every half-ported binary links both drivers. **Options:** 1. **Vendor `sqlx-sqlite` 0.8.6 with only its `libsqlite3-sys` requirement raised to 0.39**, as a `[patch.crates-io]` entry next to the existing `vendor/russh`. One SQLite (3.54.1) serves both drivers, so slices 1 to 3 build and ship incrementally. Side effects: - sqlx paths move to SQLite 3.54 now, not at the end of the port; - RUSTSEC-2026-0459 is fixed from slice 1, and the deny.toml ignore can go; - J438 measured the ~30 µs/transaction 3.54 cost, which every fix pays. - Feasibility probe (does sqlx-sqlite 0.8.6 build and run unmodified on 0.39?) is queued on dev-02, `~/.cache/codex-scratch/j449/patch-probe/run.log`. 2. **A separate-workspace crate** (`exclude`d, own Cargo.lock) until sqlx is gone. Production stays untouched. It needs a second Nix vendoring lock, new verify rows for that crate, and a two-binary compat test (a sqlx example writes the DB, the rusqlite test reads it). Slices 2 and 3 still can't ship incrementally: the port becomes one big-bang switch. **Recommendation:** option 1, if the probe and the full server test suite pass on 3.54. It is less infrastructure and it is the only way D379's slice plan builds. The pool and runner code are the same either way, so I'm writing them now. I'll proceed with option 1 if the probe is clean and say so here. The orchestrator can redirect to option 2 before I push the packaging commit. **Plan (≈6 h):** 1. New crate `crates/infraternal-db`: writer thread with a bounded queue, transaction closures, N WAL readers, tokio async API, explicit pragmas, and cancellation and panic semantics (2 h). 2. `_sqlx_migrations` runner fed by the same build_support generator: checksums, dirty, missing and mismatch refusal, plus the version-20→22 retag left to the server (1 h). 3. Red-first tests: queue ordering, cancellation, panic, full queue, reader isolation, and compat/fresh schema diff against today's sqlx path on a copy-shaped DB (1.5 h). 4. Port J438's harness as an opt-in bench, then perf VM runs against sqlx (holds ≤20 min, announced here) (1 h). 5. Gates: verify quick, then verify fast; plus cargo deny and Nix vendoring (0.5 h + queue).

kayg commented

2026-11-09 17:53:49 +00:00

Author

Owner

J449 progress: option 1 taken, 4 commits pushed, head e21f75dc0 (branch rusqlite-slice1, base bd551e549).

Probe for option 1, clean (dev-02, ~/.cache/codex-scratch/j449/patch-probe/run.log). sqlx-sqlite 0.8.6, unmodified apart from its libsqlite3-sys requirement, builds and runs on 0.39.1 in one binary with rusqlite 0.41: rusqlite=3.54.1 sqlx=3.54.1, exit=0. So I went ahead with option 1, as announced in 89039.

Commits:

ae8705367 build: vendor sqlx-sqlite 0.8.6 on libsqlite3-sys 0.39. The registry checksum matches our lock, and INFRATERNAL.md records provenance.
- Effect: sqlx paths run on SQLite 3.54.1 from this commit on.
- cargo deny check advisories licenses bans sources: advisories ok, bans ok, licenses ok, sources ok. It now warns advisory-not-detected for the RUSTSEC-2026-0459 ignore. I left deny.toml as D379 says, and the orchestrator decides whether to drop the ignore.
5430a786e crates/infraternal-db: a one-writer/N-reader pool plus the _sqlx_migrations runner, with 24 unit tests and a doctest.
fefd36474 The build script also emits the runner's migration list, plus the examples/db_pool_bench port of J438's harness.
e21f75dc0 Compat tests in the server: today's open_database + Auth::new, checked against the runner.

Results so far (dev-02, logs in ~/.cache/codex-scratch/j449/logs/):

infraternal-db: test result: ok. 24 passed; 0 failed plus the doctest (run3.log).
Server compat: test result: ok. 4 passed; 0 failed; ... 582 filtered out (compat-green.log). This covers four things:
- a copy-shaped DB built by sqlx (2-stage history + D170 rows) passes with 54/54 rows validated, 0 applied, and is byte-identical before and after;
- a fresh runner-built DB is sqlite_schema-identical to a sqlx-built one, row by row, and sqlx accepts it back;
- checksum, future-version and dirty refusals are identical in both runners;
- the runner list equals the sqlx list.
Red by mutation (red-*.log). Each of these turned its test red: no cancel skip, no panic catch, unbounded queue, no query_only, no read snapshot, no checksum pre-check, gap skipped. Re-checks of first-step-only and CREATE-text are in run 4.
Finding: dropping the foreign_keys pragma stays green, because the bundled libsqlite3-sys compiles with SQLITE_DEFAULT_FOREIGN_KEYS=1. The pool sets the pragma anyway, so enforcement doesn't depend on that build flag.
Finding: rusqlite's execute_batch steps each statement only once, while sqlx runs each statement to completion. None of the 54 migrations has a top-level SELECT, so nothing differs today. The runner still uses Batch and steps every statement to its last row, to keep parity for future migrations.
New crates and licences: rusqlite 0.41.1 (MIT), libsqlite3-sys 0.39.1 (MIT), hashlink 0.12.2 (MIT/Apache-2.0), fallible-iterator and fallible-streaming-iterator (MIT/Apache-2.0), foldhash (Zlib), and the wasm-only sqlite-wasm-rs and rsqlite-vfs (MIT). All are AGPL-compatible.

Next:

Run 4 on dev-02: two mutation re-checks, then verify quick. It is queued.
Cross-build the bench for x86_64 and run the perf VM, announced here first, with holds of 20 minutes or less.
Merge origin/dev, then verify fast.

**J449 progress: option 1 taken, 4 commits pushed, head `e21f75dc0`** (branch `rusqlite-slice1`, base bd551e549). **Probe for option 1, clean (dev-02, `~/.cache/codex-scratch/j449/patch-probe/run.log`).** sqlx-sqlite 0.8.6, unmodified apart from its `libsqlite3-sys` requirement, builds and runs on 0.39.1 in one binary with rusqlite 0.41: `rusqlite=3.54.1 sqlx=3.54.1`, `exit=0`. So I went ahead with option 1, as announced in 89039. **Commits:** 1. `ae8705367` build: vendor sqlx-sqlite 0.8.6 on libsqlite3-sys 0.39. The registry checksum matches our lock, and `INFRATERNAL.md` records provenance. - **Effect:** sqlx paths run on SQLite 3.54.1 from this commit on. - **`cargo deny check advisories licenses bans sources`:** `advisories ok, bans ok, licenses ok, sources ok`. It now warns `advisory-not-detected` for the RUSTSEC-2026-0459 ignore. I left deny.toml as D379 says, and the orchestrator decides whether to drop the ignore. 2. `5430a786e` `crates/infraternal-db`: a one-writer/N-reader pool plus the `_sqlx_migrations` runner, with 24 unit tests and a doctest. 3. `fefd36474` The build script also emits the runner's migration list, plus the `examples/db_pool_bench` port of J438's harness. 4. `e21f75dc0` Compat tests in the server: today's `open_database` + `Auth::new`, checked against the runner. **Results so far (dev-02, logs in `~/.cache/codex-scratch/j449/logs/`):** - `infraternal-db`: `test result: ok. 24 passed; 0 failed` plus the doctest (`run3.log`). - Server compat: `test result: ok. 4 passed; 0 failed; ... 582 filtered out` (`compat-green.log`). This covers four things: - a copy-shaped DB built by sqlx (2-stage history + D170 rows) passes with 54/54 rows validated, 0 applied, and is byte-identical before and after; - a fresh runner-built DB is `sqlite_schema`-identical to a sqlx-built one, row by row, and sqlx accepts it back; - checksum, future-version and dirty refusals are identical in both runners; - the runner list equals the sqlx list. - Red by mutation (`red-*.log`). Each of these turned its test red: no cancel skip, no panic catch, unbounded queue, no `query_only`, no read snapshot, no checksum pre-check, gap skipped. Re-checks of first-step-only and CREATE-text are in run 4. - **Finding:** dropping the `foreign_keys` pragma stays green, because the bundled libsqlite3-sys compiles with `SQLITE_DEFAULT_FOREIGN_KEYS=1`. The pool sets the pragma anyway, so enforcement doesn't depend on that build flag. - **Finding:** rusqlite's `execute_batch` steps each statement only once, while sqlx runs each statement to completion. None of the 54 migrations has a top-level SELECT, so nothing differs today. The runner still uses `Batch` and steps every statement to its last row, to keep parity for future migrations. - **New crates and licences:** rusqlite 0.41.1 (MIT), libsqlite3-sys 0.39.1 (MIT), hashlink 0.12.2 (MIT/Apache-2.0), fallible-iterator and fallible-streaming-iterator (MIT/Apache-2.0), foldhash (Zlib), and the wasm-only sqlite-wasm-rs and rsqlite-vfs (MIT). All are AGPL-compatible. **Next:** 1. Run 4 on dev-02: two mutation re-checks, then `verify quick`. It is queued. 2. Cross-build the bench for x86_64 and run the perf VM, announced here first, with holds of 20 minutes or less. 3. Merge origin/dev, then `verify fast`.

kayg commented

2026-11-09 18:04:47 +00:00

Author

Owner

J449 perf VM holds (announced before taking the lock). I'll run five separate perf-run holds on backup01, each 10 minutes or less (target 20), one repetition per hold, queued behind the current holder. Hold 1 also builds the template (sqlx path + D170 seed) and runs startup and memory.

Binary: db_pool_bench, x86_64 musl release, sha256 c76bc1d4d330c0e5…. Built on dev-02 from rusqlite-slice1; its non-test sources equal head e9a74ba0a.
Variants, in one session, on the same template:
- a-sqlx-pool: J438's bench-sqlx, sqlx 0.8.6 on SQLite 3.46.0, i.e. origin/dev today.
- sqlx-pool: the same pool shape on this branch's SQLite 3.54.1.
- infraternal-db: the new pool on 3.54.1.
Cells: session, hosts, events, apply and mixed, each at 1 and 64 tasks. The variant order rotates per repetition, and every run starts from a fresh on-disk copy.
Driver: ~/j449/rep.sh. Results go to ~/j449/results/rep-N.jsonl, and the perf-run state files to ~/j449/pr-N/.

**J449 perf VM holds (announced before taking the lock).** I'll run five separate `perf-run` holds on backup01, each 10 minutes or less (target 20), one repetition per hold, queued behind the current holder. Hold 1 also builds the template (sqlx path + D170 seed) and runs startup and memory. - **Binary:** `db_pool_bench`, x86_64 musl release, sha256 `c76bc1d4d330c0e5…`. Built on dev-02 from `rusqlite-slice1`; its non-test sources equal head `e9a74ba0a`. - **Variants, in one session, on the same template:** - `a-sqlx-pool`: J438's `bench-sqlx`, sqlx 0.8.6 on SQLite 3.46.0, i.e. origin/dev today. - `sqlx-pool`: the same pool shape on this branch's SQLite 3.54.1. - `infraternal-db`: the new pool on 3.54.1. - **Cells:** session, hosts, events, apply and mixed, each at 1 and 64 tasks. The variant order rotates per repetition, and every run starts from a fresh on-disk copy. - **Driver:** `~/j449/rep.sh`. Results go to `~/j449/results/rep-N.jsonl`, and the perf-run state files to `~/j449/pr-N/`.

kayg referenced this issue

2026-11-09 18:40:58 +00:00

Mesh: Linux→Mac throughput collapses to <1 Mbit/s when the underlay MTU is below mesh MTU+overhead (fixed MTU, no PMTU/MSS clamp) #381

kayg commented

2026-11-09 18:53:59 +00:00

Author

Owner

J449 finding: the new pool's writer path stalls on the single-statement session slide; one extra perf hold of ≤5 min announced.

These are repetitions 1 and 2 on the perf VM. Rep 2 adds J438's own prototype (c-split) in the same session. Files: ~/j449/results/rep-{1,2}.jsonl.

session slide	infraternal-db	c-split (J438 prototype)	sqlx today (3.46)	sqlx on 3.54
1 task: p50 / p99 / p999 / mean	91 / 121 / 25,540 / 137 µs	78 / 103 / 157 / 79 µs	62 / 83 / 96 / 63 µs	92 / 121 / 156 / 93 µs
64 tasks: ops/s (p99)	9.2k (50 ms)	23.4k (2.9 ms)	12.4k (13 ms)	8.6k (25 ms)

Where the gap is. The layout write is a BEGIN IMMEDIATE transaction in both pools, and there they are equal: 1 task p50 148 vs 146 µs, 64 tasks 2.5k vs 2.5k ops/s. Reads are equal too: host list p999 403 vs 412 µs. So the stall only hits single-statement writes, which my pool wraps in BEGIN IMMEDIATE … COMMIT.
Experiment, to isolate the cause. A minimal writer with two knobs, run as a 2×2: queue (tokio bounded behind a mutex, or std unbounded) × transaction (IMMEDIATE, or autocommit), at 1 and 64 tasks, 3 rotated rounds. It is a scratch binary, not in the repo, sha256 05074b46…. It runs as one perf-run hold right after my reps 4–5. I'll fix the pool according to the result before the gate result is quoted.

**J449 finding: the new pool's writer path stalls on the single-statement session slide; one extra perf hold of ≤5 min announced.** These are repetitions 1 and 2 on the perf VM. Rep 2 adds J438's own prototype (`c-split`) in the same session. Files: `~/j449/results/rep-{1,2}.jsonl`. | session slide | infraternal-db | c-split (J438 prototype) | sqlx today (3.46) | sqlx on 3.54 | |---|---|---|---|---| | 1 task: p50 / p99 / **p999** / mean | 91 / 121 / **25,540** / 137 µs | 78 / 103 / **157** / 79 µs | 62 / 83 / 96 / 63 µs | 92 / 121 / 156 / 93 µs | | 64 tasks: ops/s (p99) | 9.2k (50 ms) | 23.4k (2.9 ms) | 12.4k (13 ms) | 8.6k (25 ms) | - **Where the gap is.** The layout write is a `BEGIN IMMEDIATE` transaction in both pools, and there they are equal: 1 task p50 148 vs 146 µs, 64 tasks 2.5k vs 2.5k ops/s. Reads are equal too: host list p999 403 vs 412 µs. So the stall only hits single-statement writes, which my pool wraps in `BEGIN IMMEDIATE … COMMIT`. - **Experiment, to isolate the cause.** A minimal writer with two knobs, run as a 2×2: queue (tokio bounded behind a mutex, or std unbounded) × transaction (IMMEDIATE, or autocommit), at 1 and 64 tasks, 3 rotated rounds. It is a scratch binary, not in the repo, sha256 `05074b46…`. It runs as one `perf-run` hold right after my reps 4–5. I'll fix the pool according to the result before the gate result is quoted.

kayg referenced this issue

2026-11-09 18:57:56 +00:00

App windows from a Mac as canvas windows (D207, D208) #146

kayg referenced this issue

2026-11-09 23:53:26 +00:00

Mesh: Linux→Mac throughput collapses to <1 Mbit/s when the underlay MTU is below mesh MTU+overhead (fixed MTU, no PMTU/MSS clamp) #381

kayg referenced this issue

2026-11-10 04:43:03 +00:00

Trust host key: a repeated click must not end in an error (J419, follows #439) #449