perf(storage): batch long-term memory search results to eliminate N+1 reads

[liuxiaocs7]

Problem

Long-term memory key searches perform one query for the ranked item IDs, then three additional queries per result to load the item, its keys, and its sources. Returning the maximum 100 results therefore executes 301 reads (303 SQL statements including BEGIN and COMMIT); the default 20-result search executes 61 reads.

This adds repeated statement preparation, execution, and result-conversion overhead as the result count grows. The result data is already protected by a read transaction, so loading each item separately is unnecessary.

Code at baseline d2e6c1f273b7daa23ff75b84c8a30dca3f316713:

• searchByKeys loops over the matched IDs.
• #readItemRecord executes the three detail reads.

This is the storage performance improvement tracked as C141 in the local community-opportunities audit.

Desired outcome

• Use a fixed, small number of reads for up to 100 results by batching detail loading within the existing snapshot.
• Preserve exact/prefix matching, scope and archived filtering, ranking, result limits, and the ordering of each item's keys and sources.
• Preserve field validation and each item's child limits, including bounded reads that detect overflow without loading an entire corrupt child set.
• Add regression coverage for query counts, ordering, snapshot consistency, and data/error equivalence; compare the complete searchByKeys latency before and after, including single-result and maximum-child cases.

Alternatives or workarounds

Reducing the requested result limit avoids some queries but also returns fewer memories. Reusing prepared statements would reduce preparation overhead while retaining per-result database calls. A single join across items, keys, and sources can multiply rows, so bounded batch reads of each table are preferable.

Submitted with Codex assistance on behalf of @liuxiaocs7.

[liuxiaocs7]

take