storage-sqlite: `public_directory_users_capped` runs its follows query and users query as two un-transactioned statements — cross-process write between them can return wrong/empty follows

[erskingardner]

Severity: LOW (cross-process consistency; niche)
Component: crates/storage-sqlite (src/shared.rs, SqliteSharedStorage)

Summary

public_directory_users_capped issues two separate statements — the follows query (... WHERE account_id_hex IN (SELECT ... ORDER BY account_id_hex LIMIT ?1)) and the main users query (... ORDER BY account_id_hex LIMIT ?1) — outside any transaction. Within one process the connection mutex (self.lock()) serializes them, but SqliteSharedStorage is explicitly the cross-identity / cross-process database. If a second process commits a put_public_directory_user with a new lowest-ordered account_id_hex between the two statements, the "first N by account_id_hex" window differs between them, so a returned user can come back with another user's follows or an empty follows set.

Location

crates/storage-sqlite/src/shared.rs:318-369 (public_directory_users_capped) — two independent ORDER BY account_id_hex LIMIT ?1 reads with no enclosing read transaction.

Concrete failure scenario

Process A calls public_directory_users_capped(N). Between A's follows query and A's users query, process B inserts a new directory user whose account_id_hex sorts before the current N-th entry. A's users query now returns a shifted "first N" window; a user in that window whose follows were not captured by the earlier follows query comes back with empty (or mismatched) follows.

Impact

Occasional wrong/empty follow sets in a capped directory listing under concurrent cross-process writes. Niche given typical single-app-process deployments; correctness only (no crash, no corruption).

Suggested fix

Wrap both reads in a single read transaction (BEGIN … COMMIT) so they observe one consistent snapshot.

Dedup

Adjacent to but distinct from #348 (SqliteSharedStorage omits durability/privacy PRAGMAs + second-layer busy retry). No existing issue covers the two-statement snapshot skew in public_directory_users_capped.

---

Filed from a meticulous automated code-review pass; verified against source and deduplicated against the existing open/closed issue set.