Summary

A failed search refresh could remove a note from search. Fixes #1621.

Cause

SearchService.index_entity_data ran in two transactions:

1. repository.delete_by_entity_id, which committed on its own. On Postgres, search_index_fts_chunks rows go with it through the cascade.
2. index_entity_markdown or index_entity_file, which wrote the replacement rows (and on Postgres _replace_fts_chunks) in a later transaction.

If step 2 fails, as in the production _replace_fts_chunks timeouts, the note is left with no search rows at all.

Under the AGENTS.md consistency model this is a real finding, not temporary lag. The state doesn't converge by itself:

• The orphan sweep only removes rows.
• Readiness counts files by checksum, so the file still counts as indexed.
• The job retries ran out.

The note stays out of search until someone edits it or re-indexes the project.

Fix

• delete_by_entity_id, index_item and bulk_index_items take an optional caller-owned session. This applies to the Protocol, the base class, and the SQLite and Postgres implementations.
• Their explicit session.commit() calls are gone. When the method opens its own session, db.scoped_session already commits on success and rolls back on error.
• index_entity_data runs the delete and the replacement in one scoped_session.
• The Markdown content is still read before the transaction opens, so no storage I/O happens inside it.

Result:

• A failed refresh rolls back to the previous rows and FTS chunks.
• A failed first index leaves nothing behind and can be retried.
• A successful refresh replaces the rows once, including removing stale observation and relation rows.

This adds no FOR UPDATE and no cross-entity transaction. The only new lock is the delete's lock on this entity's own rows, held until the replacement commits. Postgres already ran the upsert and chunk replacement in a single transaction.

Not in this PR

• The production repair of the affected tenant's derived indexes, which the issue says to track separately.
• The load that causes the timeouts: 13 GB of search_index_fts_chunks bloat and autovacuum pressure.

Tests

New file tests/services/test_search_refresh_atomicity.py:

• Replacement rows are written, then the transaction fails. The previous projection stays unchanged and full-text search still finds it.
• A first index fails. There's no partial projection, and a retry indexes all 5 rows.
• A successful refresh after deleting an observation replaces the rows once: the stale observation row is gone and the entity snippet is updated.
• Postgres only: _replace_fts_chunks times out. The previous rows and chunk count are unchanged, and a retry keeps the chunk count stable.

The first two tests fail on main. tests passes on SQLite (7920 passed). tests/repository, tests/indexing and tests/services pass on Postgres (2389 passed). just fast-check is clean.

🤖 Generated with Claude Code

https://claude.ai/code/session_01BF4TkgAJpVrgwzrhemkdig