feat(mcp): add mem_find_project tool for cross-project relevance routing

[carlosmoradev]

📋 Pre-flight Checks

• I have searched existing issues and this is not a duplicate
• I understand this issue needs status:approved before a PR can be opened

🔍 Problem Description

When searching for a memory without knowing which project it belongs to, the only option is to call mem_search repeatedly with different project values.

There's no way to ask: "which of my projects has memories relevant to X?"

This is a routing problem — before searching deep, you need to know where to search.

Real scenario: I'm working on project-A and need to find context about "auth middleware". I don't know if I logged that under project-B, project-C, or somewhere else. Today there's no tool to answer that question in one call.

💡 Proposed Solution

New tool mem_find_project(query) that searches across all projects and returns which ones have relevant memories, ordered by relevance score.

Example usage:

mem_find_project(query: "auth middleware")

Expected output:

Projects with relevant memories:

plt-gatekeeper  (3 matches)
  → "JWT auth middleware decision"

plt-nexxo  (1 match)
  → "Auth token refresh pattern"

This is a directory lookup, not a deep search. The agent uses the result to know where to call mem_search next.

📦 Affected Area

MCP Server (tools, transport) + Store (database, queries)

🔄 Alternatives Considered

Using mem_search with all_projects: true returns individual memories but doesn't answer "where should I look?" — the result set can be large and unfocused across many projects when the agent doesn't know which project to target.

📎 Additional Context

The store already has the building blocks:

• SearchResult already includes Project *string via embedded Observation (store.go:57)
• Search() uses FTS5 full-text search and returns ranked results (store.go:1462)
• ListProjectNames() lists all distinct projects (store.go:2287)
• sanitizeFTS() handles query sanitization and is reusable

What's missing is a SearchProjectsGrouped(query) function that runs FTS5 across all projects and groups results by project, ordered by max relevance score per group.

Implementation outline:

1. New store.SearchProjectsGrouped() — FTS5 query without project filter, group results by project in memory, order by max(rank) per group
2. New MCP tool mem_find_project — registers the tool, calls SearchProjectsGrouped, formats output as project → match count + top match preview

This pairs naturally with mem_search(all_projects: true) as a two-step workflow:

1. mem_find_project("topic") → discover which projects are relevant
2. mem_search("topic", project: "the-right-project") → retrieve the actual memories

Originally reported in #172 (opened from wrong GitHub account).

[Alan-TheGentleman]

Good idea, but this changes the MCP tool surface and overlaps with existing search semantics. Before a PR makes sense, we need a design note that compares this with enhancing mem_search, defines the ranking contract, and sets a performance budget. Please keep the discussion here until that contract is clear.

[carlosmoradev]

Thanks for the review. Short answers to the three points:

Why a separate tool instead of a flag on mem_search

They return different things: mem_search lists observations, mem_find_project lists projects with a count and a short preview. Folding it into mem_search(group_by_project: true) would change the tool's output shape based on a flag, which breaks the "one tool, one shape" contract the rest of the MCP surface follows.

The flow is two steps, and it pairs with #303:

mem_find_project("auth middleware")
  → plt-gatekeeper (3), plt-nexxo (1)
mem_search("auth middleware", project: "plt-gatekeeper")

That second mem_search is scoped, cheaper than all_projects: true, and produces a focused result set.

Ranking

Per project: MAX(fts.rank) as the primary score, ties broken by COUNT(matches) desc, then project name asc. MAX mirrors the routing question ("where is my best hit?"). SUM would reward many mediocre matches over a single excellent one, which is the opposite of what we want here.

Shape:

type ProjectMatch struct {
    Project    string
    MatchCount int
    TopRank    float64
    TopTitle   string
    TopPreview string  // ~120 chars
}

Performance

Budget: p95 < 50ms on 10k observations / 20 projects, local SQLite. Single FTS5 query, in-memory grouping bounded by an internal scan cap (~200 rows). The PR will include a BenchmarkSearchProjectsGrouped_10k_20projects as evidence.

If the corpus grows and the cap becomes the bottleneck, the upgrade path is ROW_NUMBER() OVER (PARTITION BY project ORDER BY fts.rank). Follow-up, not a v1 blocker.

If you'd prefer a tighter v1 (no preview, just project + count), let me know and I'll trim it before opening the PR.