[Bug]: Conversation-content search scans all canonical assistant turns on large histories

Before submitting

• I searched existing issues and did not find a duplicate of this performance problem.
• I included enough detail to reproduce or investigate the problem.

Area

apps/server

Steps to reproduce

1. Use the isolated 1,000,000-message fixture and reproduction script in the benchmark evidence.
2. Run the existing conversation-content search query with its global canonical-assistant-message IN subquery for the fixture's rare and common searches.
3. Record timings and inspect EXPLAIN QUERY PLAN. The evidence includes 12 rounds in the same process/database, raw samples, query plans, checksums, and resource limits.

This is a database-query benchmark; the timings below are not end-to-end UI measurements.

Expected behavior

Checking whether an assistant message is canonical should use an indexed lookup instead of materializing every canonical assistant message ID for each content search.

Actual behavior

The assistant-message membership filter builds a global list from projection_turns. SQLite reports LIST SUBQUERY and SCAN turns, adding work across the turn history even though the search RPC returns at most 50 matches.

Original fixture measurements:

Query	p50	p95
Rare search	7,387 ms	12,830 ms
Common search	7,256 ms	12,034 ms

Impact

Major search-latency degradation on the large benchmark fixture. The slowdown makes conversation-content retrieval increasingly costly as history grows; it does not imply that all users experience these absolute timings.

Version or commit

The pre-fix query documented in #8969. The same global IN membership query is present on main at 39802c06117fae0b3da43624b0d54309c5437c72.

Environment

Isolated SQLite fixture with exactly 1,000,000 messages; database size 405.92 MiB. The original benchmark used a low-priority process restricted to one logical CPU. Full reproduction details are in the linked evidence.

Supporting evidence

Exact script, raw samples, query plans, checksums, and resource limits.

Workaround

No application-level workaround verified. A proposed targeted fix is available in #8969. The leading-wildcard message scan remains a separate limitation.

Start with the existing conversation-content search query and its projection_turns membership filter, then run the linked 1,000,000-message benchmark and inspect SQLite EXPLAIN QUERY PLAN. Compare the behavior with the targeted fix proposed in #8969; done means canonical-message membership uses an indexed lookup rather than materializing and scanning all canonical assistant IDs, while search results and checksums remain correct.