[Rewrite P3] Storage seam + LMDB backend (strfry-style) + PG→LMDB migration

Parent: #860
Milestone: Phase 3: LMDB storage
Host: both · Expected cost: $2,400

The storage seam and the LMDB backend. This is the largest single risk in the rewrite — strfry-style embedded storage replacing PostgreSQL.

nostream-storage traits first, ported from the measured repository interfaces (src/@types/repositories.ts): IEventRepository (create/upsert/findByFilters/countByFilters/deleteByPubkeyAndIds/deleteByPubkeyExceptKinds/vanish/retention), IUserRepository (balances, admission, vanished), IInvoiceRepository, plus invite-codes, dvm-jobs, nip05-verifications, reports, notification-outbox, delivery-log
Postgres backend kept during the rewrite (the parity oracle needs it): the current knex schema (37 migrations) becomes sqlx migrations; behaviour-identical
LMDB backend design (strfry's approach):
- event rows keyed by event id; secondary indexes: pubkey+kind+created_at (replaceable resolution), kind+created_at (subscription scans), tag indexes for #e/#p/#a/#d (strfry's per-tag-name indexes), full-text for NIP-50 (tantivy sidecar or LMDB prefix — decide in the design sub-issue)
- dup-id detection via id-keyed main DB; deletion/replaceability as index updates, not row mutation
- transactions per write batch; reader/writer concurrency model mapped to LMDB's MVCC (readers never block)
- the metadata tables (users, invoices, invites, outbox) — decide: same LMDB DBs (simple, portable, no postgres at all) vs a small embedded SQL (rebook/rusqlite) for the relational tail; record the decision with reasoning
NIP-50 search: NIP-50 FTS index measured in the TS tree (20260427_000000_add_nip50_fts_index.js migration) — port semantics to the chosen search store, parity-tested on the sampled corpus
Migration tool: nostream-cli migrate pg-to-lmdb — bulk copy with checksum manifest, resumable, measured throughput target ≥ 50k events/s on chappie hardware
Benchmark rig (chappie): the measured TS/PG baseline (events/s ingest, REQ latency p50/p95/p99 across 1M/10M event corpora) vs LMDB — numbers in docs/rewrite/benchmarks.md; the cutover gate reads these

Acceptance: the LMDB backend passes the full storage-parity suite (every repository test the TS suite carries, re-expressed against both backends); benchmark report exists with the PG baseline and LMDB numbers side by side; migration tool round-trips a 1M-event corpus with zero checksum mismatches.

Factory job 007-storage-lmdb opened: base rewrite/main @ b324ef5 (Phase 1 + nostream-core merged), branch factory/007-storage-lmdb, worktree projects/nostream-worktrees/007-storage-lmdb, factory 0.10.6, expected $2,400 (Phase 3 anchor — no measured job in this phase yet; it will be replaced, not scaled, from this job's measurement). Stage: recon.

Pulled ahead of #865 on measured evidence (recorded on #860): the storage traits are missing the methods REQ/EVENT need, and #865's differential harness needs a Postgres + Redis host this sandbox does not have. So this job runs first.

One scope question the spec must settle, since the issue bundles five large surfaces (traits, Postgres kept as oracle, LMDB backend, migration tool, benchmark rig): if that is more than one job, the spec says which slice this job delivers and what moves to named follow-ups — a plan that needs more than the anchor is a finding to report, not absorb.}

Recon complete (recon.md, 452 lines). Headline measurements:

Trait surface: TS declares 51 methods across nine interfaces; Rust declares 27 (Event 4/11, Invoice 3/5, User 3/7, Nip05 2/4, Invite 3/5, Dvm 3/5, Report 3/5, DeliveryLog 3/4, Outbox 3/5). Two structural gaps no count shows: IQueryResult.stream() (consumed by the REQ handler) has no Rust form, and DBEvent's storage-only fields (deleted_at, expires_at, first_seen, event_deduplication, remote_address) have no home in nostream_core::Event.
The oracle does run here. The 192 repository unit tests compare knex-generated SQL text, so they cannot judge an LMDB backend — but the Cucumber suite is backend-agnostic: with postgres:16-alpine + redis:7-alpine published on ports, all 37 migrations applied (Batch 1 run: 37 migrations) and 118 scenarios / 587 steps pass in 7.3 s. Containers removed afterwards.
Engine inputs: heed 0.20.5 / LMDB 0.9.70 (sync, MVCC, one writer, 511-byte key cap); seal() sorts so negentropy imposes no index order; tantivy 0.27 builds in 42 s, rusqlite 0.40 bundled in 43 s, rebook does not exist on crates.io; LMDB measured 48.8k events/s with scattered index keys (147.7k clustered), 3.86M point lookups/s; Postgres 17.9k events/s and a REQ-shape p50 of 11.17 ms over 200k rows.
Slice, adopted: this job delivers the traits (51 methods, with a streaming find_by_filters) + the Postgres backend as sqlx migrations + the differential parity harness; the LMDB store, NIP-50 search, pg-to-lmdb and the benchmark rig become four named follow-ups with their own acceptance. The $2,400 anchor prices all five surfaces, so the follow-ups carry the remainder — recorded as the cost finding rather than absorbed.
Constraints the spec must absorb: no Postgres service in CI, so sqlx compile-time macros are unusable there; nostream-cli has neither a migrate command nor a nostream-storage dependency yet; no 1M/10M corpus exists (seeds hold 463 events, no generator).

Spec stage now.}

Spec complete (spec.md, 1,021 lines). The issue is delivered as five jobs; this job is the first slice.

In this job: all nine repository traits with 51 methods (full Rust signatures in the spec), a streaming QueryStream<'a, T> for find_by_filters/find_reconciliation_items that works for a Postgres portal and an LMDB read transaction, a storage EventRecord wrapper holding DBEvent's five storage-only columns (core Event untouched), the Postgres backend as 37 sqlx migrations, and the differential parity harness.
Harness: scripts/storage-parity.sh → sqlx migrations + a catalogue-in-fixture check, the TS-recorded 51-method differential replay (empty diff), then cucumber-js against the sqlx-migrated database (118 scenarios / 587 steps). CI gains a postgres:16-alpine service and runs the two #[ignore]d targets.
Dependencies: workspace sqlx 0.9 (runtime-tokio, postgres, migrate, macros, chrono, json, tls-none), chrono 0.4, futures-core 0.3. Nothing added to the CLI.
Follow-ups, each with its own acceptance and preconditions: LMDB store (2.5–4k LOC), LMDB NIP-50 (300–600), nostream-cli migrate pg-to-lmdb (600–1,000), benchmark rig with 1M/10M corpora (no corpus exists today).

Cost, replaced on the board rather than absorbed: the $2,400 anchor priced all five surfaces; this slice is ≈49% of the measured code, so the Project item and job.json now read $1,170 for this job, with ≈$1,230 belonging to the four follow-ups. The figure is a scope split from the measured code share, not a calibration — Phase 3 still has no measured job, and this job's close will supply the first one.}

Build round 2: STATUS: READY at 8d1ec50 (three commits, not pushed), after a round-1 BLOCKED on ordinary remaining work. Independently verified here, not taken from build.md:

cargo test --workspace --all-features → 589 passed / 0 failed / 2 ignored (base 546); fmt and both clippy passes clean; release build green.
scripts/storage-parity.sh → exit 0, all three steps: sqlx migrations from empty + catalogue match, the 110-operation TS-recorded differential replay with an empty diff, and 118 Cucumber scenarios / 587 steps passing against the sqlx-migrated database. Containers created labelled, published on ports, and removed by the script's trap.

The harness found and fixed real backend defects before review: the upsert tuple appended its extra columns outside the parentheses, inet was read as text (…/32) instead of via host(), and the actionable-target sort key. Two catalogue drifts the migration test caught (a default spelling, a unique-constraint name) are now matched by the migrations.

One deviation recorded rather than taken: AC13's grep prints 2 because one INSERT INTO event_tags is the trigger function's own, and the pinned catalogue fixes that text; the set-based backfill is present and equivalent. Fixtures are generated (tests/capture/gen-storage-parity.mjs, which refuses a rewrite checkout) and committed: the whole public catalogue and a 483-event / 51-method replay script including UNION, NIP-50, NIP-56, retention buckets, invoice scaling, lease and backoff paths, and two serializable transaction groups.

Review scoping now.}

Review round 1 closed: security, code, architecture and performance all returned CHANGES. The two that matter most:

Harness exposure (security, blocking). scripts/storage-parity.sh published the throwaway Postgres with the Docker default bind (0.0.0.0) and the literal superuser password postgres; the reviewer verified a login and a query from another host, and Redis was unauthenticated the same way. On every harness run that is a superuser shell (COPY … FROM PROGRAM) exposed on all interfaces.
NIP-50 lost its index (performance, blocking). Binding the text-search config through Arg::Text made sqlx declare TEXT, so the plan casts $1::text to regconfig and cannot use events_content_fts_idx: 444.8–671.3 ms per execution vs 0.088 ms on a 200k-event database, with the parameter typed regconfig (what node-postgres sent). Same SQL, full scan on every search.

Also real: with no replicas the code still dials a second pool (double the connection budget) and connect omits timezone=UTC; QueryStream's Send bound does not hold against heed's default !Send read transaction, so the ADR's LMDB claim is unproven; sqlx is not confined behind a feature, so every dependent links the Postgres driver; the old backend::PostgresBackend stub survives as dead code; FieldUpdate::Clear is a no-op for tos_accepted_at; delivery_log.find_successful_target_ids is unscripted (50 of 51 methods); the retention-whitelist scenario is vacuous; and ADR 0003 lacks consequences and revisit triggers.

What held up: every value reaches the driver bound (no SQL interpolation of ids, authors, kinds, tags, search text or geohash), transactions are SERIALIZABLE with server-side assertion, and the fixtures genuinely reproduce the capture — the code reviewer regenerated schema.json byte-identically from 242a3001. Builder round 2 dispatched (resume 1 of 5).}

Builder round 3 (round-2 of review) at dbf4e0f, four appended commits, 22 files, +704/−232. Verified here: workspace 592/0/2 ignored (was 589), tree clean, and the harness exits 0 with 118 scenarios / 587 steps when invoked as NODE_PATH=<primary>/node_modules PARITY_HOST=100.106.134.5 PARITY_BIND=100.106.134.5 ./scripts/storage-parity.sh.

Blockers closed with proof: ports publish as -p "$PARITY_BIND:…" with no wildcard (the builder's docker inspect shows the host address, and the repo has zero references to the private address); per-run openssl rand -hex 16 passwords reach -e, both URLs, the Cucumber DB_URI and Redis --requirepass; NIP-50's config is guarded ^[a-z_][a-z0-9_]*$ and inlined so the plan is a Bitmap Index Scan on events_content_fts_idx, 0.198 ms, asserted with enable_seqscan = off — a deliberate divergence from the capture, which binds ?::regconfig and misses its own index, recorded in ADR 0003.

Also fixed: one pool until a replica exists, timezone=UTC on every constructor, heed read-txn-no-tls with a revisit trigger, sqlx behind a postgres feature (--no-default-features links no driver), the dead stub deleted, tos_accepted_at Clear now nulls, find_successful_target_ids scripted (115 ops / 51 distinct methods), the retention-whitelist scenario reordered with real counts, and ADR 0003 gained consequences plus five triggers.

One open question the round-2 lenses are asked to judge: the script's new defaults (127.0.0.1) do not work here, because cargo tests run inside the builder container whose loopback is not the daemon host's — the two exports above are what make it green. Either the head or CLAUDE.md must say so, or the script must discover the right address.}

Review round 2 closed at 7349441: security PASS, performance PASS, code PASS, architecture CHANGES.

Closed with proof, not with prose:

Security: the wildcard bind is gone by construction (empty PARITY_BIND refuses at preflight and creates nothing; docker create -p 127.0.0.1:… reports HostIp: 127.0.0.1), all five credential sites carry per-run passwords, and a forced admin-connection failure logs zero occurrences of the password. NIP-50's new interpolation surface is one guarded site (^[a-z_][a-z0-9_]*$, error before SQL), pinned by an injection unit test.
Performance: reproduced cold — Bitmap Index Scan on events_content_fts_idx, 2 rows, 0.162 ms versus 371–399 ms Parallel Seq Scan for the old bound-config shape; the enable_seqscan=off assertion is meaningful (the wrong shape still plans a Seq Scan). One pool, 3 connections, not 6.
Code: all eight round-1 findings resolved and checked against the code; fixtures still regenerate from the capture (schema identical, parity identical modulo two random enqueue UUIDs); the two #[ignore]d DB targets pass against a labelled Postgres.

Architecture's four are record and build-config hygiene, not behaviour: the postgres feature gates the library but not the tests (so the config the ADR nominates for the LMDB follow-up cannot be built — cargo check --no-default-features --all-targets fails), ADR trigger 4 cannot fire because the plan test EXPLAINs a hardcoded 'simple' predicate rather than the composer's SQL, ADR:166 wrongly says the heed feature changes no LMDB behaviour (NO_TLS is OR-ed at env open), and the preflight misclassifies unix:///fd:///tcp://[::1] as remote. Round 3 dispatched with those plus four cheap notes (batch constant 11 vs 10, a rename-proof redaction step, ADR op count, explicit wildcard override).}

Builder round 4 (review round 3) at 281b3b3: three commits. Verified here: workspace 594/0/2 ignored (was 593), cargo check -p nostream-storage --no-default-features --all-targets Finished, tree clean.

The architecture verdict is addressed at its cause rather than annotated: the DB-backed test targets are now #![cfg(feature = "postgres")] and CI runs the feature-off build it claimed; the plan guard loads the fixture's settings and PREPAREs/EXPLAINs the composer's own statement, so nip50.language: english makes it fail — trigger 4 can fire; the ADR now says what the heed feature does (EnvFlags::NO_TLS OR-ed at env open, which is why RoTxn: Send is sound) and adds a trigger for EventRecord encoding; the preflight parses the daemon endpoint (only tcp:///ssh:// are remote, brackets stripped) and refuses wildcard binds, with unix:// and tcp://[::1] now proceeding. The four cheap notes are in: the batch constant matches the tuple's binds, the redaction step requires 1 passed so a rename cannot pass vacuously, and the ADR's counts (115 entries: 112 calls + 2 groups + 1 probe) match the fixture.}

Review round 3 at 281b3b3: security CHANGES, code CHANGES, architecture CHANGES — every round-2 finding confirmed resolved by measurement, and what remains is small and exact.

Confirmed by running, not reading: --no-default-features --all-targets builds and its feature-free test list is non-empty (3 tests incl. the trait dyn-compatibility check); the plan guard now fails when nip50.language: english is composed (Seq Scan, 215 ms) and passes with simple (Bitmap Index Scan); the ADR's heed claim matches heed 0.20.5 env.rs:295-302; the endpoint predicate matrix classifies unset/unix:///fd:///npipe:///tcp://127.0.0.1|localhost|[::1] local and tcp://host/ssh:///remote-IP remote; and the redaction guard flips to exit 1 when the test is renamed.

What still blocks:

Security: the wildcard refusal omits the bracketed IPv6 form — PARITY_BIND='[::]' passes the guard and Docker binds HostIp: "::" (measured), while the bare :: arm is dead because Docker rejects that spelling.
Code + architecture (same two sentences): ADR 0003:145 still says the upsert chunks at 5,957 rows with eleven binds (code: 6,553 rows, ten binds, deleted_at a literal NULL), and ADR:177-180/build.md:92-93 say 112 direct calls plus one probe (fixture: 111 calls plus two probes; the 115 total is right).
Architecture note: trigger 4 says "run the target with the deployed settings" but the target always reads defaults + fixture — add the switch or reword the trigger.

Round 4 dispatched (resume 4 of 5).}

Builder round 5 (review round 4) at edfd19d, two commits. Verified here: workspace 594/0/2 ignored, tree clean, and the wildcard guard through the real script with NODE_PATH set — [::], [::0], 0::, ::0000, 0.0.0.0 and [0:0:0:0:0:0:0:0] each exit 1 with one line (is a wildcard: publish on one address), while [::1] exits 1 with the remote-daemon line.

The four items are closed at their cause: one unbracket pass feeds both the loopback and wildcard checks, with the IPv6 wildcard recognised structurally (all characters 0 or :) rather than by literal; probe_dial dials the unbracketed host while PARITY_ADMIN_URL/DB_URI keep brackets (Node's net.connect returns ENOTFOUND for [::1]); ADR 0003 now says 7,281 rows at nine binds and 6,553 at ten (deleted_at a literal NULL); the fixture breakdown reads 115 = 111 calls + 2 transaction groups (7 nested) + 2 probes, 52 distinct ops = 51 + probe; and settings_from_fixture honours PARITY_NIP50_LANGUAGE, with english making the migrations target fail on the indexed-expression check.

Round 4 is the last resume in the budget (4 of 5).}

Review round 4 at edfd19d: security, code and architecture all CHANGES — but rounds 1–3 are confirmed closed, and what remains is one classification fix plus one paragraph.

Real finding (security and code, same root): the wildcard guard matches characters, so Docker's dotted and mapped spellings slip through. Measured through the real script and docker inspect: ::0.0.0.0/0::0.0.0.0/[::0.0.0.0] publish HostIp:"::", and ::ffff:0:0/::ffff:0.0.0.0 publish HostIp:"::ffff:0.0.0.0" — proved reachable off-loopback (18793 OPEN from another container vs 18794 CLOSED for a loopback publish). Separately, 0:0:0:0:0:0:0:1 reaches the preflight though Docker normalises it to ::1, the same remote-daemon evasion round 3 named for [::1].

Architecture's is the ADR overclaim: the target hard-codes the shipped simple expression, so PARITY_NIP50_LANGUAGE=english fails before the EXPLAIN even if the index were rebuilt — the trigger must say what the run does and what a deployment must rebuild, and show PARITY_ADMIN_URL in the printed command (verbatim the command panics on a missing URL).

Round 5 is the last resume in the budget (5 of 5): the fix is canonical address parsing (node is already required by the probe) plus a reworded paragraph, and a CHANGES after it means rounds-exhausted.}

Final build round at 641fa42 (one commit). Verified here: workspace 594/0/2 ignored, tree clean, and through the real script — ::0.0.0.0-family spellings, [::ffff:0.0.0.0], 0:0:0:0:0:ffff:0:0 and 0x0.0.0.0 each exit 1 with the wildcard line and create nothing, while ::ffff:7f0:1 and ::ffff:7ff:1 correctly classify as explicit (they then fail at container start here only because those addresses are not local).

The classifier now parses through WHATWG URL — bracketing IPv6 first, which the parser requires — and classifies the canonical text: wildcard ::/::ffff:0:0/0.0.0.0; loopback ::1, 127/8, localhost, and mapped loopback decided numerically on the first mapped group (0x7f00..=0x7fff, so the ::ffff:7f… prefix false positives the operator measured are rejected); everything else explicit. PARITY_HOST is canonicalised with it. Trigger 4 now states what the run does, shows a command that runs, and records that a deployment rebuilding the index for another language must re-run the guard against its own database — which this target cannot certify.

Round 5 is the final review round in the granted budget (5 of 5 resumes): a CHANGES here means rounds-exhausted and the job escalates with the named finding.}

Final review round at 641fa42 — the last in the granted budget: security PASS, code PASS, architecture PASS, all three confirmed by measurement.

Security: every wildcard spelling Docker accepts maps to exactly ::/::ffff:0:0/0.0.0.0 and refuses pre-create — ::0.0.0.0, [::0.0.0.0], 0::0.0.0.0, [::ffff:0.0.0.0], 0:0:0:0:0:ffff:0:0, ::0:ffff:0:0, 0.0.0.0., 0, 0x0, ::0000 each exit 1 with zero docker create calls. The mapped-loopback arm is numeric: ::ffff:7f0:1 and ::ffff:7ff:1 are explicit (7.240.0.1, 7.255.0.1), ::ffff:7f00:1…::ffff:7fff:ffff are loopback. PARITY_HOST canonicalisation keeps exactly one bracket pair in the URLs while the probe dials the plain form; userinfo/path/query inputs reduce to the host.
Code: corrected classifier verified end to end (bracket-first parse, throw → invalid, dial forms for v4/v6/mapped); the reworded trigger matches the code.
Architecture: trigger 4 now names the shipped simple expression, the assertion-before-EXPLAIN order, the scratch-database limit and the rejection of deriving the assertion from settings, with a command that runs.

Approve stage now: the factory approver is reading the full six-round job.}

Approve gate: ESCALATE — condition money-access-identity. Not a correctness refusal: the approver verified the branch itself and everything holds — oracle exit 0 (redaction guard, 37-row migrations idempotent and catalogue-identical, 115-entry parity with an empty diff, Cucumber 118/587, containers removed), fixture provenance reproduced from a fresh capture (schema byte-identical, parity identical modulo two DB-generated UUIDs it never compares), the plan guard fails under english for the right reason, AC1–19 met or assigned to the manager, merge-tree clean, and no test, guard or migration weakened.

It escalates because the change implements the payment and access storage path (confirm_invoice, charge_user, invoice/balance rows, admit_user/revoke_admission/is_admitted, replica routing) and adds 37 schema migrations, three workspace dependencies and a CI service — the money-access-identity, schema-or-dependency and deployment-config bullets all fire, and it must not land unattended.

Clears on: Cameri's word to merge, plus CI green on the pushed branch. The branch is held at 641fa42 in factory/007-storage-lmdb (unpushed), worktree intact, job at needs-human/escalated. Nothing else is outstanding on my side.}

Storage slice merged

PR #910 squash-merged into rewrite/main as 827558a655c2a6e181f5e128e44e089361368e69 (head 12539d4).

What landed — surfaces 1–2 only, per the job's spec decision S1:

nostream-storage: the nine *Repository traits (51 methods) and the record/stream types.
The PostgreSQL backend: the knex schema as 37 sqlx migrations, one module per repository, handwritten SQL.
The differential parity harness: capture generator, migrations + parity test targets, scripts/storage-parity.sh, fixtures, ADR 0001–0003.

Verification at merge:

check	result
required checks (8)	all green — Rust Checks, Unit Tests And Coverage, Integration Tests and Coverage, Build check, Changeset Required, Lint commits, Lint code, Lint PR title
`cargo test --workspace`	594 passed, 0 failed, 2 ignored
`scripts/storage-parity.sh`	118 scenarios / 587 steps, all passed
fmt + clippy (both feature sets)	clean

Coverage note: coverage/coveralls reported −3.1% at 70.9% on the PR. .nycrc.json instruments only src/**/*.ts and this branch changes zero files there (0 under src/, 60 under crates/), so the number is not about this diff; the two coverage rows also disagreed with each other at the same 70.9%. The Rust suite is outside that denominator.

Not landed — still open on this issue:

LMDB store (strfry-style).
LMDB NIP-50 search.
pg-to-lmdb migration tool.
Benchmark rig.

Follow-ups raised by the post-merge Greptile review (it posted after the merge; neither was a required check):

P1 — TLS connections cannot work. Cargo.toml selects sqlx with default features off and tls-none, so an sslmode=require or verify-full URL has no TLS provider. A TLS-required deployment cannot connect today.
P2 — transaction test harness loses message ids. run_transaction passes a fresh refs map to every nested op, so outbox.enqueue followed by mark_delivered/mark_failed in one group cannot resolve the id. One map per group, keeping committed references and discarding rolled-back ones.

Navigation Menu

Repository navigation

[Rewrite P3] Storage seam + LMDB backend (strfry-style) + PG→LMDB migration #864

Storage slice merged

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

[Rewrite P3] Storage seam + LMDB backend (strfry-style) + PG→LMDB migration #864

Description

Activity

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 19, 2026

chappie-daemon commented on Oct 20, 2026

Storage slice merged

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions