Optimize search and ingestion performance

[BigSimmo]

Problem

• search-scope.ts resolves filters by pulling pages of 1 000 documents until it hits a hard cap of 5 000. Each page is filtered in application code rather than in SQL. This O(N) scan grows linearly with corpus size and risks timeouts.
• The ingestion worker loops through every image in a document to compute hashes, run classification and upload captions. For large PDFs with hundreds of images, this leads to long processing times and memory pressure.
• The worker sequentially calls external APIs for image captioning and embedding, waiting on each response before proceeding. In high-throughput scenarios this drastically reduces throughput.
• Helper functions perform individual Supabase RPC calls (e.g. to update job progress or document metadata) for every minor state change, resulting in many network round-trips.

Proposed improvements

• Push search filtering and scope resolution into SQL using full-text indices or dynamic where clauses. This avoids scanning thousands of rows in application code.
• Offload heavy image processing to a separate micro-service or limit the number of images processed per worker to mitigate memory pressure.
• Batch embedding and caption requests and use concurrency primitives (e.g. Promise.all or worker pools) to increase throughput.
• Combine related updates into single SQL statements or stored procedures to reduce network latency and lower the risk of partial failures.