bug(server): forward service serializes connections on the SQLite store (two commits per TCP connection, rollback-journal mode)

Start with crates/openshell-server/src/persistence/sqlite.rs and the file-backed cases in crates/openshell-server/src/persistence/tests.rs; review how SqliteStore::connect handles journal mode and existing database files. Run the persistence tests, then verify WAL sidecars, concurrent reader behavior, and the requested durability and backup documentation.

User Story

As an operator running a single-node OpenShell gateway on a small VM, with openshell forward service in front of HTTP services that run inside sandboxes, I want concurrent client connections through a forward to be served concurrently and cheaply, so that a client fetching a handful of records in parallel gets them in tens of milliseconds instead of seconds and does not see connections dropped.

Problem Statement

Every TCP connection accepted by openshell forward service costs about 50-100 ms before the first byte reaches the target, the wall-clock time for N simultaneous connections is linear in N, and beyond roughly 20 simultaneous connections the excess are closed by the forward instead of being queued.

Reading the code, the forward path does two gateway store writes per TCP connection and the gateway's on-disk SQLite store runs in SQLite's default rollback-journal mode:

• crates/openshell-cli/src/run.rs service_forward_tcp: each accepted connection calls CreateSshSession before the ForwardTcp stream and RevokeSshSession after it. That is one INSERT and one UPDATE of an ssh_session object per connection.
• crates/openshell-server/src/persistence/sqlite.rs SqliteStore::connect: no journal or synchronous pragma is set, and sqlx 0.8 does not set one either, so a freshly created database runs with journal_mode=delete and synchronous=FULL. Every commit pays several fsyncs and a writer blocks readers, so all the reads the forward path also does (fetch_and_authorize_sandbox, session validation) queue behind the writes. The existing comment in persistence/tests.rs ("sqlx 0.8 doesn't default to WAL ... actual production path today") and issue #2999 (observed journal_mode=delete) confirm this is the shipped state.
• crates/openshell-server/src/grpc/sandbox.rs acquire_ssh_connection_slots: hard limit of 20 concurrent forward connections per sandbox (and 3 per token), introduced by #182 for SSH sessions. Connections queued behind the store are released in batches, so a burst exceeds the cap; the excess get RESOURCE_EXHAUSTED ("sandbox SSH connection limit reached") and the CLI closes the client socket, which the client sees as an EOF or reset during its TLS handshake.

Impact / Why This Matters

Any client that opens several connections to a forwarded service at once (a browser page loading N records, a connection pool warming up) serializes on the gateway store, and a burst above the cap loses connections outright. A dropped connection is worse than a slow one: the client has to detect it and retry, usually after a timeout. Current workarounds are to limit client concurrency to well under 20 and to retry dropped connections; neither removes the per-connection floor, which is 50-100 ms on a VM against a network-backed disk, and both are things every consumer of a forward has to know about.

Measured on a 2 vCPU Fedora 44 VM (gateway/CLI 0.0.110, Podman driver, file-backed SQLite on btrfs over a virtio network block volume; the relevant code is unchanged on main), from a pod on the VM's network (TCP connect to the VM ~0.2 ms), against a sandboxed HTTPS service behind a forward:

Concurrency sweep, N simultaneous connections each doing TLS handshake + GET / + Connection: close, read 64 bytes:

N	wall (s)	completed	mean per connection (ms)	failures
1	0.05	1	54	0
6	0.49	6	264	0
16	1.52	16	772	0
32	3.29	32	1445	0
64	6.05	29	3159	35 (EOF/RST after ClientHello)

A second run 10 s later gave the same shape with 9 of 32 and 43 of 64 lost. Wall time is ~95-100 ms per connection at every N. Ten sequential single connections averaged 82 ms.

Control on the same VM, ten sequential bare TLS handshakes:

Path	TLS handshake mean (ms)
gateway port directly, no forward	1.5
same VM, through a forward	88 (min 47, max 283)

On the VM: the gateway database reports PRAGMA journal_mode = delete, no -wal/-shm sidecars exist, and the forward units' journals contain 84 occurrences of service forward connection failed ... code: 'Some resource has been exhausted', message: "sandbox SSH connection limit reached" over 30 days, arriving in bursts (three within 7 ms).

A benchmark on a copy of that database (100 x {INSERT; UPDATE} as autocommit statements, then a writer thread looping the same pair while a second connection times 100 SELECT count(*); note this copy ran on tmpfs, so the absolute commit costs are lower bounds without disk fsync):

config	ms per commit	reader p95 (ms) with concurrent writer	reader max (ms)	writer iterations in 3 s
delete / FULL (current default)	0.08	18.4	53.5	18,815
wal / FULL	0.04	0.20	3.0	71,957
wal / NORMAL	0.02	0.17	0.4	71,581

Even with no disk in the path, delete mode makes readers wait behind the writer and cuts write throughput 4x; with real fsync latency the per-commit cost grows to tens of milliseconds, which matches the ~95 ms per connection (two commits) seen through the forward.

Acceptance Criteria

• Connection setup time through openshell forward service no longer scales linearly with the number of simultaneous connections on a file-backed SQLite store; 32 simultaneous connections to a loopback HTTP server in a sandbox complete in well under a second on a small VM.
• Reads on the gateway store are not blocked by concurrent writes (on-disk SQLite runs in WAL mode).
• The behaviour is covered by a file-backed store test (journal mode after connect, existing rollback-journal files switched on connect, concurrent readers under a burst of insert-then-update writes).
• Documentation states the SQLite durability setting, the -wal/-shm sidecars, and the backup implication.

Out of scope for this issue but worth separate discussion: minting one session token per forwarded TCP connection (rather than per forward process), and making the per-sandbox connection cap configurable or turning a refusal into a queue.

Reproduction Steps

1. Run a gateway with the default SQLite store on a VM or host whose disk has non-trivial fsync latency (a cloud block volume is enough).
2. Create a sandbox running a loopback HTTP server, for example:
   openshell sandbox create --name forward-probe -- sh -lc 'exec python3 -m http.server 63152 --bind 127.0.0.1'
3. openshell forward service forward-probe --target-host 127.0.0.1 --target-port 63152 --local 127.0.0.1:43152
4. From the same host, open N simultaneous TCP connections to 127.0.0.1:43152, each sending GET / HTTP/1.1 with Connection: close and reading the first bytes, for N in 1, 6, 16, 32, 64, and record wall time and how many connections complete. Repeat with ten sequential connections.
5. Compare with N connections to the HTTP server without the forward (from inside the sandbox) and with sqlite3 <gateway-db> 'pragma journal_mode' (expect delete).
6. Optional confirmation: stop the gateway, run pragma journal_mode=wal on the database file once, start it again and repeat step 4; the per-connection time drops and readers stop queueing.

Environment

• OpenShell: measured on 0.0.110 gateway and CLI; the store, forward and connection-cap code is the same on main.
• OS: Fedora Linux 44 (Cloud), kernel 6.19, 2 vCPU / 4 GiB VM.
• Runtime/deployment: Podman compute driver, single gateway, default file-backed SQLite store on btrfs over a virtio network block volume.

Logs

WARN service forward connection failed peer=<ip>:<port> error=code: 'Some resource has been exhausted', message: "sandbox SSH connection limit reached"

(84 occurrences in 30 days across the forward units on one VM, in bursts of several per 10 ms.)

Proposed change

Set journal_mode=WAL and synchronous=NORMAL for on-disk SQLite stores in SqliteStore::connect, switching the file to WAL on a single connection before the pool opens (the mode change needs an exclusive lock that busy_timeout cannot wait for), with tests and docs. A branch with this change, passing the persistence tests, is at https://github.com/n1hility/OpenShell/tree/sqlite-wal/jg; I will open it as a PR once this issue is triaged and I am vouched.