Project indexing can delete accepted-but-unmaterialized notes (production data loss)

[phernandez]

Summary

A project-wide index can delete a valid DB-first note while its Markdown file is still waiting for asynchronous materialization.

This is confirmed production data loss. The caller receives 202 / Created, but the initial project index can later classify the accepted database row as an externally deleted file, remove Entity (cascading NoteContent), and leave no durable note or file.

Production Logfire issue #2489 is one observable interleaving. Other interleavings return status=missing normally and do not create an exception issue.

Production evidence

Observed on 2026-08-13 with Cloud release bc2e7e889e16552052094e21a99a7b03cb71005c and Core 0.22.2.dev219+38a2ed67 (38a2ed675f7f33f23be129f3948ff52ec5f023b3).

Representative fresh-project sequence, with tenant and content identity redacted:

1. An MCP create_memory_project call created an empty project and immediately queued project-index job 9497726.
2. A following MCP write_note accepted entity 10, NoteContent version 1, returned Created, and queued materialization job 9497727.
3. PGQueuer picked both jobs at approximately 19:24:46Z.
4. The index listed the project prefix from S3 and found zero objects.
5. ChangeDetector.load_all_indexed_paths() nevertheless returned the new DB-only note, so delete planning reported one deleted file.
6. Project-index maintenance locked and deleted the entity; ON DELETE CASCADE removed note_content.
7. Materialization had already loaded both ORM rows. Its session.flush() then raised:
   sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'note_content' expected to update 1 row(s); 0 were matched.
8. Current tenant state contains neither entity 10 nor its note_content, and no later materialization job exists. This job failed before writing S3.

Traces:

• Accepted MCP write
• Concurrent project index
• Failed materialization / Logfire #2489

The exception is not the only production signature. Over the latest 14-day query window, materialization emitted nine normal status=missing results across five tenants. At least two on 2026-08-13 align within one second with a same-tenant, same-project index deleting exactly one row:

• 15:16 index deletion followed by materialization reporting that note state disappeared after file write.
• 18:20 index deletion followed by same-project materialization reporting missing state.

This gives one exception-confirmed and two independently correlated production occurrences today. The other six missing outcomes may be legitimate delete-after-enqueue races and are not claimed as data loss without further correlation.

Root cause

The consistency model is DB-first for accepted Markdown, but project delete detection currently treats every indexed entity path as if its storage projection must already exist:

• ChangeDetector.load_all_indexed_paths() delegates to EntityRepository.get_all_file_paths().
• plan_file_changes() computes deletes as all_db_paths - storage_paths - moved_old_paths.
• No NoteContent.file_write_status, db_version, or file_version state is carried into delete planning.
• RepositoryProjectIndexMaintenanceStore defaults to TrustPlannedProjectIndexDeleteVerifier. The S3 runtime therefore trusts the scan verdict at apply time.
• Project-index deletion correctly takes the shared NoteContent lock before deleting Entity, but locking cannot make the deletion semantically valid; it only decides which transaction wins.

A newly accepted note is a valid state even when its file is absent. Treating storage as authoritative during that window destroys the durable source of truth.

The StaleDataError is a secondary ORM check-then-flush race: preflight reads live models, project-index deletion removes them, and the generated UPDATE asserts one matched row. Converting only that exception to a missing/no-op result would hide the error but would not prevent data loss.

Required design

Keep asynchronous materialization. Do not make the request wait for S3 and do not use timing delays or queue ordering as the correctness boundary.

The fix should establish both invariants:

1. Project-index delete planning/application cannot delete accepted content whose current file projection is not proven synchronized.
2. Materialization preflight handles a legitimate concurrent explicit delete without leaking StaleDataError.

A likely shape is:

• load typed delete candidates rather than bare paths, carrying the minimum accepted/materialized lineage needed to distinguish storage-owned indexed files from DB-owned pending content;
• exclude pending, writing, failed, and otherwise unsynchronized current DB versions from external-delete planning;
• revalidate the same lineage with a guarded SQL predicate at delete application so an accepted write or materialization transition after the scan cannot be deleted from a stale plan;
• use a guarded lower-level UPDATE ... WHERE entity/project/version/checksum still match RETURNING ... (or equivalent row-locking CAS) for materialization preflight and return a detached value object; zero rows must be classified from current state rather than becoming an ORM row-count exception.

The exact predicate needs to preserve non-Markdown/storage-origin entities and true external deletion of previously synchronized Markdown.

Acceptance criteria

• A Postgres concurrency regression runs the real fresh-project sequence: initial project index is delayed, write_note is accepted, and index plus materialization are released concurrently.
• After all work settles, the entity, note_content, search state, and S3 object all exist and agree on current path/checksum/version.
• The regression fails against the pre-fix implementation by deleting the accepted note.
• Both race orders are covered: index attempts deletion before materialization claims the row, and after preflight has begun.
• Project delete planning excludes accepted-but-unmaterialized current content without relying on age, sleep, FIFO job order, or process-local locks.
• Delete application revalidates its snapshot/CAS state so a concurrent accepted write cannot be destroyed.
• A genuinely externally deleted, previously synchronized file still removes its entity and derived search/vector rows.
• An explicit user delete racing a queued materialization remains idempotent: the materializer reports a typed missing/stale outcome and guarded cleanup cannot remove a newer file.
• Materialization preflight no longer leaks StaleDataError when its target disappears.
• Cloud integration coverage exercises the S3/PGQueuer adapter composition, not only an in-memory planner.
• Production acceptance queries show no same-tenant/project index deletion followed by materialization missing/error for the deleted generation.
• Identify affected production writes and define recovery before closing; do not assume exception issue count equals impact.

Related

• Background relation resolution reads notes before async materialization #1159 covers relation resolution reading pending materialization, not deletion of the accepted source of truth.
• Prevent Entity-NoteContent lock-order deadlocks during materialization #1187 fixed a different Entity/NoteContent lock-order deadlock. This issue is about an invalid external-delete decision; row-lock ordering alone cannot solve it.
• basicmachines-co/basic-memory-cloud#1661 tracks truthful production operation outcomes. The normal status=missing interleavings need causal diagnostics there, but the deletion invariant belongs in Core.

[claude]

Claude finished @phernandez's task in 39s —— View job

---

Triage

Type: Bug (data-loss defect)
Priority: Critical — confirmed production data loss (Logfire #2489: entity + note_content deleted via ON DELETE CASCADE, no recovery path, no later materialization job).
Component: Cloud (manifests through the S3/PGQueuer materialization pipeline and RepositoryProjectIndexMaintenanceStore), with a root cause in Core's project-index delete planning (ChangeDetector.load_all_indexed_paths, plan_file_changes) and the DB-first consistency model. The MCP write path, entity/note-content locking, and cloud storage adapter are all implicated.
Complexity: Complex — this isn't a local bug fix. It requires:

• Carrying accepted/materialized lineage (file_write_status, db_version, file_version) into delete-candidate loading instead of bare paths.
• Excluding unsynchronized current DB versions (pending/writing/failed) from external-delete planning.
• A guarded CAS-style revalidation at delete-application time to close the check-then-flush race.
• Reworking materialization preflight to use a guarded UPDATE ... WHERE ... RETURNING (or equivalent) instead of an ORM load-then-flush that raises StaleDataError on a stale target.
• A new Postgres concurrency regression exercising the real S3/PGQueuer adapter composition (not just an in-memory planner), covering both race orderings described in the issue.

Reproduction is not missing here — the issue includes concrete production traces, root-cause analysis, and a detailed required design with acceptance criteria, so no reproduction steps need to be requested.

No duplicate open issues found. Related but distinct and already closed: #1159 (relation resolution reading pending materialization) and #1187 (a different Entity/NoteContent lock-order deadlock) — neither addresses this invalid external-delete decision. basicmachines-co/basic-memory-cloud#1661 (different repo) tracks the causal-diagnostics side of the same production signal.

Labels applied: bug, cloud (in addition to the existing production label).

[phernandez]

Recovery audit for the three 2026-08-13 correlated occurrences

Read-only production follow-up confirms that none of the three accepted generations recovered automatically. Customer identity and content paths remain redacted here.

Occurrence	Accepted generation	Current tenant DB	Storage outcome	Recovery source
15:15–15:18 UTC	project 2, entity 1, version 1	No entity by original ID or external ID; no note_content	Materialization wrote the file, then orphan cleanup job 9465909 reported deleted at 15:18:08Z	Tigris deleted-object/version history and Neon PITR should both be evaluated
18:19–18:20 UTC	project 13, entity 3701, version 1	No entity by original ID or external ID; no note_content	Project index deleted the DB state before preflight; materialization returned note state no longer exists without writing a file	Neon PITR is the server-side source
19:24 UTC / Logfire #2489	project 3, entity 10, version 1	No entity by original ID; no note_content; no later materialization job	Project index deleted during preflight; StaleDataError occurred before S3 write	Neon PITR is the server-side source

All three tenants remain active. The 18:19 and 19:24 cases are fresh projects whose automatically queued initial index later ran against notes accepted before that index was picked. The 15:15 case is the same initial-index shape for a getting-started project.

No production branch, restore, replay, file, or database row was created or changed during this audit.

Operational follow-up before closure:

• Bound the production search beyond the three correlated signatures, including project-index delete results and materialization missing outcomes that do not create Logfire issues.
• Capture affected rows from a read-only point-in-time source before the restore window expires.
• Define per-generation restoration that does not overwrite a newer customer recreation.
• Verify restored DB content is materialized and searchable, and that project indexing no longer removes it after the code fix.

[phernandez]

Production recovery audit (2026-08-13)

Read-only follow-up across the control-plane activity ledger, all three tenant databases, Tigris object history, and Neon project metadata confirms:

• No automatic recreation: there is no later activity event and no current tenant row matching any affected generation by original external ID, original path, or exact accepted-content checksum. A later MCP/tool call did not recreate any of the three notes, including under a new external ID at the same path or with the same content.
• Case A (materializer wrote before discovering the row was gone): Tigris retains one immutable object version (2,070 bytes, written 2026-08-13T15:16:37Z) followed by a latest delete marker (2026-08-13T15:18:07Z). This note can be recovered directly from that exact object version, then restored through the normal accepted-note/materialization path after a current-state conflict check.
• Cases B and C (row disappeared before storage write): no object version or delete marker exists for either exact project-prefixed key. Their authoritative recovery source is the tenant Neon history containing note_content.markdown_content immediately after acceptance and before the initial index deletion.
• PITR is time-sensitive: all three tenant Neon projects currently report history_retention_seconds=86400 (24 hours). Recovery branches should be created from just after each accepted note.created event and before the corresponding initial project-index deletion. Neon documents that a historical branch is isolated and can be queried/exported without changing current production state.
• Daily Tigris snapshots/B2 backup are not expected to contain these generations because each note was created and deleted within minutes between scheduled daily recovery points. Logfire also does not retain note bodies.

Safe restore procedure: create isolated historical Neon branches, export only the affected accepted Markdown plus stable identity/path/checksum, confirm production still has no row at the original external ID/path/checksum, restore through the canonical note-acceptance boundary (not blind production SQL), wait for materialization, and verify the current tenant row, Tigris object, checksum, activity event, and absence of a concurrent index deletion. Case A can use its immutable Tigris version as the content source and Neon PITR as an independent cross-check.

[phernandez]

Production recovery execution (2026-08-13)

Recovery is complete for the two affected generations whose projects still exist. The third generation is preserved but was intentionally not recreated because its project was explicitly deleted later.

Occurrence	Action	Verification
15:15 UTC (case A)	Restored the original external ID, path, metadata, and exact 2,070-byte accepted Markdown through the canonical accepted-note boundary; materialized the same accepted DB version through PGQueuer	Tenant DB is synced at version 1 with exact historical checksum 105a2407…decd75; current Tigris bytes match the PITR row and immutable historical object version; the row remained after webhook indexing settled
18:19 UTC (case B)	Exported the exact 1,076-byte historical generation from a read-only Neon PITR branch; no live mutation	The containing project has a later explicit project.deleted activity event at 19:21:48Z. Recreating the project/note would override later user intent, so this payload is held for a separate restore-or-deliver decision
19:24 UTC / Logfire #2489 (case C)	Restored the original external ID, path, metadata, and exact 2,102-byte accepted Markdown through the canonical accepted-note boundary; queued normal PGQueuer materialization	Tenant DB is synced at version 1 with exact historical checksum 41ffab27…a87118; current Tigris bytes match the PITR payload; the row remained after webhook indexing settled

Every live restore failed closed unless all of these remained true immediately before mutation:

• the original project still existed at the historical project path;
• no current row reused the original external ID or file path;
• no current note in that project had the historical content checksum; and
• the target Tigris key was absent.

Control-plane ORM rows were not carried into tenant work. Each restore loaded only the tenant storage fields into a detached value object, closed the control-plane session, then opened tenant-scoped work. Exact historical bytes were passed through the accepted-note mutation runner so identity/path conflicts, entity/search/graph persistence, and standard materialization semantics remained in force.

One operator-only case A enqueue attempt initially used an unsupported audit source and failed before queue insertion. The accepted DB row remained durable, was requeued unchanged with the supported api source, and the worker reported status=written. This did not alter the recovered content/version and did not create a new Logfire exception issue.

Temporary PITR branches for restored cases A and C have been deleted after verification. Case B's read-only recovery branch remains available until 2026-08-20T20:00:00Z, alongside the mode-600 local export, pending the explicit project-level decision.

The issue should remain open until the code fix and concurrency acceptance criteria land and production soak shows no recurrence. Customer recovery itself is no longer blocking cases A/C; case B requires only the later-deletion intent decision.