Memories: background consolidation can silently drop or duplicate memories when new ones are saved concurrently

Summary

Codex builds memory in two background steps:

1. Per-thread extraction (internally "stage 1") — after a conversation ends, a background job extracts candidate memories from that thread and writes them to the stage1_outputs table.
2. Global consolidation (internally "phase 2") — a second background job ranks those candidates across all threads, takes the top-N, and merges them into your global memory files (MEMORY.md / memory_summary.md).

The bug is in how step 2 selects the top-N. It pages through the ranked candidates with LIMIT/OFFSET across separate, non-transactional queries, while step 1 may still be writing new candidates. When a newly-extracted candidate ranks above the current page, every later OFFSET shifts by one — so consolidation can silently skip a candidate (memory lost) or read one twice (memory duplicated), and the saved baseline it diffs against on the next run gets corrupted too.

In one line: if Codex finishes saving a new memory at the same moment it's consolidating, it can drop or double-count memories with no error shown.

Impact

Silent corruption of what Codex remembers. The likely user-visible symptom is "a memory I saved earlier wasn't used later" — possibly related to #24172 ("Memories are forgotten"), though unconfirmed.

Environment

• Platform: all (this is a logic/SQL ordering issue, not OS-specific).
• Feature: Memories with generate_memories = true.
• Code: codex-rs/state/src/runtime/memories.rs — get_phase2_input_selection (the step-2 selection).
• Reviewed against main.

Reproduction (deterministic)

1. Seed N threads with completed extractions (stage1_outputs rows), spread across the ranking.
2. From a concurrent task, insert additional higher-ranked stage1_outputs rows mid-selection — even for never-selectable threads (no threads row), which only perturb the OFFSET without ever being eligible.
3. Run the selection with a small page size to widen the race window.
4. Observe: the returned set ≠ the seeded eligible set — entries are missing or duplicated.

I have a regression test that reproduces this reliably (12 eligible threads; a concurrent task inserts 60 high-ranked, never-selectable rows per iteration over 10 iterations; the result diverges from the seeded set without a fix). Happy to share the test.

Expected vs. actual

• Expected: consolidation always selects exactly the eligible top-N, with no duplicates or drops, regardless of concurrent writes.
• Actual: under concurrent writes the selection can drop or duplicate entries.

Root cause

LIMIT/OFFSET pagination is only stable over an unchanging result set. The ranked query and the per-candidate re-fetch run as separate autocommit statements, so a concurrent insert that ranks above the current page shifts the window — a classic non-repeatable read / phantom-row problem.

Suggested fix (high level)

Run the ranked selection and the per-candidate re-fetch inside a single transaction, so the whole selection observes one consistent snapshot. Under SQLite WAL this is a non-blocking read snapshot, so concurrent writers aren't stalled. I have a candidate fix + the failing test locally and can share a high-level outline on request.

Duplication check

Searched open issues/PRs and the memory-related branches: no existing fix. #24172 may be a downstream symptom (unconfirmed). #22008 and the empty-input housekeeping work touch the same consolidation pipeline but address a different concern.