persistence: transaction-scoped delete leaves search_index rows — short pages on the Postgres fast path, chains through deleted resources

[aacruzgon]

Summary

Deleting a resource inside a SQLite or PostgreSQL transaction soft-deletes it and writes history but leaves its search_index rows in place, whereas the direct delete clears them and MongoDB's in-transaction delete clears them too. The stale rows are mostly masked by is_deleted = 0 joins, but two paths are not masked.

Evidence

• crates/persistence/src/backends/sqlite/transaction.rs:423-486 SqliteTransaction::delete: SELECT … is_deleted = 0, UPDATE resources SET is_deleted = 1 (:463), INSERT INTO resource_history (:471). No DELETE FROM search_index.
• crates/persistence/src/backends/postgres/transaction.rs:700-767 PostgresTransaction::delete: same shape (:747, :757).
• Direct deletes clear the index: backends/sqlite/storage.rs:550-557 and backends/postgres/storage.rs:670-679 (DELETE FROM search_index WHERE tenant_id = … AND resource_type = … AND resource_id = …, guarded by !is_search_offloaded()).
• MongoDB's in-transaction delete calls delete_search_index_in_bundle_transaction (backends/mongodb/storage.rs:3234).
• Runtime check on SQLite: after a transaction delete, search and search_count by identifier both return 0, so ordinary search is safe.

Consequences that are not masked (code-evident)

1. PostgreSQL v18 fast path returns short pages. backends/postgres/search_impl.rs:297-312 applies LIMIT {count+1} inside the search_index subquery and only then joins resources … WHERE r.is_deleted = FALSE. Stale rows for transaction-deleted resources consume page slots, so a page can come back up to count rows short; that path has no cursor and has_previous is hardcoded false, so the dropped rows are unreachable, while search_count (:550, counts resources … is_deleted = FALSE) reports them.
2. Chained search matches through deleted resources. backends/sqlite/search/chain_builder.rs:374-424 builds every chain link from search_index alone with no resources join, so Observation?subject.name=Smith still matches Observations whose Patient was deleted inside a transaction. The returned resource is is_deleted-gated; the chained-through one is not.

find_resources_by_value (sqlite/search_impl.rs:1215-1284) is index-only and unguarded but has no callers.

Design reference

#28 specifies soft delete preserving history; #223 (E1) and #704 treat divergence between the search index and the primary as a defect.

Proposed fix

Clear the resource's search_index rows in both transaction delete implementations (mirroring the direct path and the existing IndexWrite::Replace on update), then add tests: Postgres fast-path page size after a transaction delete, and a chained search through a transaction-deleted target on SQLite.

Found while validating #511 (see PR #860).