Upgrade document search to multi-dimensional indexed retrieval (content, time, metadata)

[sun-970]

Summary

The document search APIs (GET /api/doc and GET /api/v1/documents) only match against doc.title using SQL LIKE/ILIKE. Host applications (e.g. RoleWeave's "Memory & Collaboration" panel) that embed doc as a read-only knowledge source cannot search document content, filter by time range, or sort by update/create date with any granularity beyond the default updatedAt desc. When a workspace accumulates 30+ documents and the target keyword only appears in the body, the search returns nothing.

This issue tracks upgrading the doc-side search infrastructure to support multi-dimensional indexed retrieval. The host-app UI layer is out of scope for this repo; this issue focuses on the API and database capabilities that host apps consume.

Current behavior (code evidence)

Internal API: GET /api/doc

• File: src/app/api/doc/route.ts (lines 231-234)
• Search is title-only: whereOpt.title = { contains: keyword } (case-sensitive LIKE)
• No pagination — returns all matching rows
• No content search, no time/metadata filters

API v1: GET /api/v1/documents

• File: src/lib/api-v1-documents.ts (lines 159-204)
• Search is title-only: { title: { contains: query, mode: 'insensitive' } } (case-insensitive ILIKE)
• Has cursor-based pagination by (updatedAt, id) — good foundation
• Query param query is the only search dimension; starred and trash are the only filters
• No content search, no time range, no metadata filters

Prisma schema: Doc model

• File: prisma/schema.prisma (lines 19-41)
• Has content: String (TipTap JSON) — available for indexing but not searched
• Has contentBinary: Bytes? (Yjs binary state)
• Has createdAt: DateTime and updatedAt: DateTime — available for range queries but not exposed as filters
• No file type / MIME type field
• No file size field
• No tsvector column or GIN index

Full-text search infrastructure

• None exists. No tsvector, no GIN index, no @@ operator, no search migration.

Read-only / shared access

• File: src/lib/document-access.ts, prisma/schema.prisma (ShareRelation model)
• ShareRelation supports READ / WRITE access types
• API v1 getApiDocument resolves access as owner / write / read
• Host apps consuming docs via PAT with documents:read scope already see shared documents — search just needs to respect the same userId scope

Expected behavior

Phase 1 — Content full-text search (core)

1. Add a contentSearch tsvector column (or equivalent) to the Doc table, populated from the plain-text extraction of content (TipTap JSON → text).
2. Create a GIN index on the tsvector column.
3. Extend GET /api/v1/documents query param query to search both title and document content:
   • query=keyword → matches title OR content containing "keyword"
   • Return a matchField indicator in results (title | content | both) so the host UI can highlight where the match was found
4. Extend internal GET /api/doc keyword param with the same title+content search.
5. Keep the existing contains fallback for environments where PostgreSQL full-text search is not configured (e.g. SQLite dev).

Phase 2 — Time range filters

6. Add query params to GET /api/v1/documents:
   • after=2026-09-01 — documents updated after this ISO date
   • before=2026-09-17 — documents updated before this ISO date
   • sort=updated_asc|updated_desc|created_asc|created_desc (default: updated_desc)
7. Same params on internal GET /api/doc.

Phase 3 — Metadata filters

8. Add a mimeType or docType field to the Doc model (or a derived classification):
   • TipTap documents → application/tiptap
   • Future file attachments → their actual MIME types
9. Add query param type=tiptap|markdown|json|... to filter by document type.
10. Add minSize / maxSize query params for content size filtering.

Phase 4 — Structured query syntax (optional, host-UI side)

11. The host app UI can parse simple query syntax like type:json after:2026-09-01 keyword and translate it into the API query params above. This is a host-app concern; doc just needs to provide the filter params.

Suggested technical approach

• PostgreSQL tsvector + GIN is the natural fit since the stack is already Prisma + PostgreSQL.
• Use a Prisma migration to add the column and index.
• Populate contentSearch via a trigger on INSERT/UPDATE of the content column, or via application-level extraction in the create/update path.
• For TipTap JSON → plain text extraction, walk the JSON tree and concatenate text nodes (a small utility function, ~30 lines).
• Use websearch_to_tsquery() for the query side to support natural search syntax.
• For the matchField indicator, run two queries (title match + content match) or use a single SQL query with CASE WHEN to determine which field matched.

Reproduction

1. Create 30+ documents in doc with different body content but similar titles.
2. From a host app (e.g. RoleWeave memory panel), search for a keyword that only appears in one document's body.
3. Observe: zero results, because only title is searched.

Scope note

The "Memory & Collaboration" panel UI (file list, search input, "copy reference" button, "知识资料/全部文件" tabs) lives in the host application (e.g. bytefolk/roleweave), not in this repo. This issue covers only the doc-side API and database capabilities. The host app will need a corresponding issue to consume the new query params and render the enhanced results.

Phasing suggestion

Phase	Effort	Impact
1. Content full-text search	Medium (migration + extraction utility + query change)	High — solves the core "can't find by content" problem
2. Time range filters	Low (just add where conditions on existing DateTime fields)	Medium — helps narrow down large result sets
3. Metadata filters	Medium (schema change for type/size)	Low-Medium — useful as document diversity grows
4. Structured query syntax	N/A (host-app side)	Convenience for power users

Phase 1 alone would resolve the primary user complaint. Phase 2 is a small follow-up. Phases 3-4 can be deferred.

[waterbro-8]

Tracking open PR #69 (feat/multi-dimensional-search) for the API/DB search upgrade. Host-app UI is out of scope here. I am not opening a duplicate. No merge or close from me.