[Search] Ensure /search page works end-to-end with all content types

[Search] Ensure /search page works end-to-end with all content types

Task ID: eb06a191-3527-485f-a36b-76a204a1f616

Goal

Ensure the /search page works end-to-end: fast API responses, all content types return results, FTS indexes are healthy.

Acceptance Criteria

Root Cause Analysis

Issue 1: 10-second API response time

The /api/search endpoint was taking ~10 seconds to respond despite all individual DB queries completing in <25ms. Root cause: the search logging INSERT INTO search_queries at the end of the endpoint used get_db() which creates a connection with timeout=10. Under heavy write contention from other agents, this INSERT would wait up to 10 seconds for a write lock, blocking the entire API response.

Fix: Use a separate SQLite connection with timeout=0.5 for the logging INSERT, and close the read connection first. If the DB is locked, the log entry is silently skipped (best-effort logging).

Issue 2: Broken FTS5 indexes

analyses_fts and wiki_pages_fts had stale content ("missing row N from content table" errors), causing FTS queries to fail and fall back to slower LIKE queries.

Fix: Rebuilt both FTS indexes via INSERT INTO table(table) VALUES('rebuild'). Added rebuild_fts.py utility script for future maintenance.

Work Log

2026-04-03 02:45 PT — Slot 8

• Tested /search page: loads 200 in 25ms (static HTML with client-side JS)
• Tested /api/search: returns 200 but takes 10.3 seconds
• Profiled individual DB queries: all complete in <25ms total
• Identified root cause: search_queries INSERT uses get_db() with timeout=10, blocks on write lock contention
• Fixed: separate connection with 0.5s timeout for logging
• Rebuilt broken FTS indexes (analyses_fts, wiki_pages_fts)
• Added rebuild_fts.py utility script
• Committed and pushed

File: eb06a191-3527-485f-a36b-76a204a1f616_spec.md

Modified: 2026-05-01 20:13

Size: 2.2 KB