Support database-wide search with optional project filtering

[phernandez]

Part 1 of 2: database-wide search with optional project filtering

Enable one Core search across multiple projects in the same database. A project filter should be optional rather than requiring callers to invoke the project search endpoint repeatedly. Cloud orchestration is tracked in the companion issue linked below.

Problem

Core's search API/services and vector adapter scopes currently bind to a single project. PostgreSQL full-text and vector tables already carry project IDs in shared tables, but queries explicitly filter to one project. Cloud therefore fans a 17-project query out into 17 search pipelines, collects per-project pages and sorts them globally.

Production evidence (2026-09-15): with reranking disabled, a fresh all-project query still took 17.852s and emitted 17 FTS, 17 embedding and 17 vector-query spans. FTS alone reached 10.020s on one project. This motivates reducing repeated work; it does not guarantee a database-wide query will meet a particular latency target.

Requested behavior

• Add a database-scoped search entry point for text, vector and hybrid search. An omitted project criterion means all projects within the already-selected database and trusted access scope; a supplied project or project set narrows it.
• Keep tenant/database selection separate from project selection. Never search another database because the project filter is absent.
• Keep authorization scope separate from optional user criteria. Hosted callers supply an explicit authorized-project set; intersect requested projects with that set before retrieval. An empty authorized set must return no results, never mean unrestricted search. Caller-supplied criteria cannot expand trusted scope.
• Retrieve and rank across the selected set, then paginate once. Hybrid search still has FTS and vector operations; “one search” means one pipeline per database, not necessarily one SQL statement.
• Embed the query once per compatible embedding model/dimension group. Preserve provider/model compatibility and stale-vector checks rather than mixing incompatible embeddings.
• Carry project identity through vector matches, fusion, hydration, result schemas and stable pagination. Preserve distinct notes with matching relative paths/permalinks across projects.
• Preserve existing explicit single-project API/MCP/CLI behavior. Expose the new scope without silently changing existing default-project resolution; document how callers explicitly request database-wide search.
• Make caching include effective scope, search configuration and relevant project generations. Project/content/visibility changes must not expose stale unauthorized results.

Implementation surfaces

src/basic_memory/api/v2/routers/search_router.py, search service and repository composition, repository/search_repository_base.py, repository/postgres_search_repository.py, repository/pgvector_index.py, repository/semantic_vector_index.py, result schemas and read-cache keys/invalidation. Assess SQLite and external vector adapters explicitly; do not silently replace one scoped pipeline with hidden per-project fan-out.

Acceptance

• Real SQLite/Postgres coverage for one project, multiple projects and omitted project criteria; empty allowed scope; mixed result types; colliding paths; filtering and pagination.
• End-to-end coverage through the API and exposed MCP/CLI contract, including backward compatibility.
• Unauthorized projects never enter candidate selection, ranking, totals, snippets or caches; test warm-cache access-scope changes.
• Compare retrieval call counts, result relevance, latency and payload/memory costs on the same multi-project dataset, including cold and cached searches. Record deliberate relevance changes from globally ranking candidates instead of merging independently ranked project pages.
• Preserve bounded content previews and actual vector chunks.

Related work and boundaries

• Core #1557 addresses duplicate retrieval for stable reranker pagination; it is related but separate. Avoid carrying that duplication into the new scope.

• No admission-limit or cross-worker semaphore system. Do not re-enable production reranking as part of this issue. Rollout and production verification remain separately authorized.

• Companion Cloud orchestration: Cloud #2080.

[claude]

Claude finished @phernandez's task in 43s —— View job

---

Triage: Support database-wide search with optional project filtering

Type: Enhancement — new database-wide search capability layered on existing single-project search; not a defect.

Component: Core search (API/services/repository — api/v2/routers/search_router.py, search service, repository/search_repository_base.py, repository/postgres_search_repository.py, repository/pgvector_index.py, repository/semantic_vector_index.py). Not labeling cloud: every listed implementation surface lives in Core, and the issue explicitly separates itself from cloud orchestration, which is tracked in the companion issue (#1557 addresses the related-but-separate reranker pagination duplication; the cloud fan-out/orchestration work is tracked separately as noted in the issue body). Cloud production data is cited only as motivating evidence for the Core change.

Priority: Medium-High (prose only) — no user-facing breakage today, but it's blocking a meaningful latency/cost problem in production (17.852s / 17x fan-out for a multi-project query per the 2026-09-15 evidence cited). Worth prioritizing, but it's an optimization/capability issue, not an outage.

Complexity: Complex — this spans search repository/service composition, vector index scoping, authorization-vs-selection semantics, caching key design, and both SQLite and Postgres/pgvector adapters, with explicit requirements around authorization scope intersection, embedding-compatibility grouping, cross-project pagination stability, and backward compatibility for existing single-project callers. The acceptance criteria call for substantial new test coverage (SQLite + Postgres, empty authorized scope, colliding paths, warm-cache access-scope changes, relevance/latency/payload comparisons).

No duplicate issues found in the repo. This is well-specified with clear boundaries (explicitly excludes cloud orchestration fan-out and the #1557 reranker-pagination fix), which will help scope a PR.

Labels applied: enhancement (component: none — Core, not Cloud-specific).