Reconcile file lineage when an older materialization publishes after its successor

[phernandez]

Problem

An older successful materialization can publish its file lineage after a newer materialization has already written and published. The older stale_db_version plan can replace file_version=4, synced with file_version=3, pending, although storage still contains revision 4.

This was reproduced with real PostgreSQL sessions and the Cloud runtime while validating https://github.com/basicmachines-co/basic-memory-cloud/pull/2012. It is a separate existing Core publication issue, not evidence that conditional storage writes failed. No exact historical production occurrence of this ordering has been established.

Reproduction

Core pin: bccf09c

Use Cloud's apps/cloud/tests/pgq/test_materialization_overlap.py at PR2012 as the fixture:

1. Start accepted revision 3 with no published file version and no stored object.
2. Let worker A prepare, then accept revision 4 and let worker B prepare.
3. Let A's conditional PUT land, but pause its get_file_metadata return before Core publication.
4. Let B observe accepted revision 3, write revision 4, and finish DB publication.
5. Release A's metadata response and let its older publication finish.

Observed: storage remains revision 4, but NoteContent.file_version is 3 and file_write_status is pending, with db_version=4. The regression's assert row.file_version == row.db_version == 4 fails with 3 == 4.

In the fixture, use the unpublished-first-write ordering but wait for second_finished in A's metadata callback and do not hold B's PUT until first_finished. The checked-in ordering isolates the separate first-publication NULL-version guard; this alternate ordering isolates publication regression.

Investigation and constraints

Inspect RepositoryNoteMaterializationPublisher.publish_written_file_state and plan_written_note_materialization_publish. The CAS protects the current DB version read by the publisher, but that DB version can already be 4 when the older publisher plans a file-version-3 update. The successful CAS then applies a stale lineage plan.

Preserve the documented derived-state convergence policy and canonical lock ordering. Do not add SELECT-time locks or hold a transaction across storage. Account for local filesystem writers as well as Cloud conditional writes before assuming successful writes always arrive in accepted-version order.

Acceptance

• Add a real-session regression for both storage and publication completion orders.
• After the newer file has been written and published, an older completion must not leave synchronized current content falsely pending without durable convergence work.
• Preserve legitimate stale writes, moved-path cleanup, external conflicts, and current accepted content.
• Demonstrate the regression fails on the prior implementation.
• Follow through with the Cloud Core-pin update and hosted runtime regression once Core is fixed.

[claude]

Claude finished @phernandez's task in 1m 8s —— View job

---

Triage

Type: Bug — a real code defect in Core's file-materialization publication path, not a Cloud-specific issue (the report itself notes local filesystem writers are equally in scope).

Component: Database/sync — specifically plan_written_note_materialization_publish / RepositoryNoteMaterializationPublisher.publish_written_file_state in src/basic_memory/index/note_content_materialization.py. This is the same publish path touched by the now-closed #1224 (which removed SELECT FOR UPDATE in favor of the db_version CAS); this issue is a follow-on regression surfaced while validating that change against real concurrent writers, not a duplicate of it.

Priority: Medium-High. The failure mode is a genuine convergence gap, not ordinary derived-state staleness: an older materialization's completion can overwrite a synced/file_version=4 row with a stale file_version=3, pending plan even though storage already holds revision 4 and DB db_version is already 4. Per this repo's consistency-model review policy, that qualifies as a real finding — it doesn't self-heal until some other write or reindex touches the row, and until then the row's write status is wrong. The window is narrow (requires two overlapping materializations to complete out of order) and is confirmed only in a reproduction harness (Cloud's test_materialization_overlap.py) rather than in production, which keeps it out of "critical."

Complexity: Medium. The reproduction, root cause, and constraints (CAS is correct but is compared against the wrong reference point; no SELECT-time locks; preserve legitimate stale-write/moved-path/external-conflict handling) are already spelled out in detail. The fix likely means comparing the publishing plan's target file version against the current row's file_version/write status (not just gating on db_version) before applying an older plan — but that still needs to go through the same real-session regression harness described here to be validated safely.

Duplicate check: No duplicate found. Related but distinct: #1224 (closed) removed SELECT FOR UPDATE from this same publish path — this issue is a newly discovered gap in that CAS-based design, not a repeat of it.

Labels applied: bug, component none (Core issue; not Cloud-specific despite being reproduced via the Cloud test harness).

Per the acceptance criteria, this needs a Core fix plus a regression test that fails on the current implementation before any Cloud pin update — no implementation is included in this triage pass.