Fresh import admission and library rows use typed JSON batches instead
of one INSERT per item. Each batch targets at most 128 records or
approximately 512 KiB; one already-validated large record may exceed the
byte target. Bigint byte counts remain exact decimal strings.
All batches share the existing run lock and transaction. Append imports
retain their per-thread validation/locking path. This stack is based on
**dev c9b57c4274c2eaa753a54ca7cbb834856db7ed9d** and preserves
#400–#402. Project reconciliation, history-ID pagination, activity
indexing and mirror metadata listing are inherited from dev, with no
duplicate implementations here. The new index migration is **0100**
after dev's 0097–0099.
## Measurement
Before: read layer (#406). After: this stack's top. `bun
scripts/bench/db.ts --run --explain`, PostgreSQL 18.6, identical
synthetic 240-thread/2,048-turn/4,096-byte dataset, two warmups, 20
serial samples, nearest-rank p95. All stack layers were remeasured after
the incident stack merged; old pre-incident comparisons are superseded.
| Operation | Queries before → after | p95 ms before → after | Returned
rows before → after | Decoded JSON bytes before → after |
|---|---:|---:|---:|---:|
| Import admission (100 items) | 111 → 12 | 20.5 → 22.0 | 0 → 0 | 222 →
24 |
| Import reads | 2 → 2 | 9.8 → 35.2 | 2,323 → 2,323 | 704,170 → 704,170
|
Import admission p50 was 16.7/8.2 ms. The slower read sample is
explicitly retained: its SQL, rows, decoded bytes and shared-block
counts are unchanged. A separate `--operation imports` repeat gave
admission p50/p95 7.5/8.9 ms and read p50/p95 8.5/10.1 ms, with the same
query/row/byte counts. Read server execution was 7.71 ms before, 18.90
in the full after run, and 7.94 in the repeat. Latency remains noisy
under shared-host load; the repeat does not replace the table, and no
stable read-latency improvement is claimed. Query reduction is the
stronger evidence.
Counts include transaction controls. Bytes are decoded returned JSON,
not bytes written, wire traffic or disk I/O; empty arrays contribute two
bytes per query. This is a store-level benchmark, not a production
RPC/event-loop SLO.
Compared with current dev c9b57c42, the complete stack changes
title-search p95 from 1,069.9 to 91.2 ms and decoded bytes from 844,938
to 98,080; paged-open p95 is 29.8/22.5 ms with 107 rows on both sides;
repeated mark seen is 2/1 statements. Projects remain 8 statements and
mirror lists remain 546 metadata bytes: those improvements already
belong to the incident stack.
## Validation
- Import tests passed: 300 items and 300 library rows survive restart
with order, owner, JSON and bigint >2^53 intact; a late-batch conflict
rolls back the run and every earlier batch (2 tests, 904 assertions).
- Current-dev focused schema/index/upgrade, history, mentions,
read-marker and #401 read-cost suites passed sequentially. No
multi-gateway test files ran concurrently.
- All required current-dev top-branch checks passed: bun install, bun
run gen:check, bun run typecheck, bun run lint, bun run format:check,
foreground bun run check:tmp and the web build. The full run passed
1,914 tests, skipped 161 opt-in/platform tests and failed none across
342 files (22,862 assertions, 984 seconds). check:tmp reported nothing
left in its temp directory. After the checks, a single blank-line lint
correction was folded into the index layer and descendants were rebased;
no runtime code changed. Lint and formatting passed with that
correction, and the working tree is clean.
## Remaining database work
The 2,095-location static inventory is not a completed manual audit of
every SQL statement. Next work should finish cold/admin/query-generator
coverage, expand update/mirror/side-thread fixtures, measure concurrent
desktop/server load and pool/timeouts, and quantify JSONB/bytea/write
amplification. Prepared statements remain enabled (100 per connection;
pool default 10).
Concrete candidates are repeated progress aggregates in
`imports/store.ts`, unbounded pending-command decoding in
`store/outbox.ts`, and workspace preparation awaited while a schedule
transaction holds its row lock in `store/bus-schedules.ts`. Preserve
import totals, command ordering and schedule fencing when changing them.
Safe pruning needs durable watermarks/tombstones and restart tests; no
age-based deletion is introduced. Large index builds require a
maintenance window, and large legacy snapshots/restored windows remain a
limitation.
Polish docs describe harness usage, indexes, import batching and
retention constraints. No UI text changed; copy audit does not apply.
Keep all four PRs as drafts.