Search upgrade: Postgres full-text (tsvector) and pgvector semantic search

FeaturePlannedView on roadmap· by Elsama· 5 months ago

Context: Search is ILIKE-based today (src/lib/mcp/tools.ts). pgvector is enabled but unused; pg_trgm powers duplicate suggestions only.

Scope:

Add tsvector columns and GIN indexes for posts, docs, and changelog; rank results.
Add an embeddings pipeline and pgvector index for semantic search and better duplicate detection.
Blend lexical and semantic results in the search UI and the MCP search tool.

Done when: search returns ranked, relevant results across posts, docs, and changelog with semantic recall.

Comments (7)

Elsama· 9 days ago
Lexical slice shipped (migration 0098): search_tsv + GIN on posts/changelog/docs, lexical_rank_* RPCs, MCP + /api/search use FTS with ILIKE fallback. Embeddings / pgvector blend still open on this card.
Elsama· 8 days ago
Update: lexical full-text search (tsvector + GIN, ranked results with ILIKE fallback) is shipped in product and MCP search. pgvector semantic embeddings remain planned (needs embedding pipeline + model credentials).
Elsama· 8 days ago
Update (overnight grind): Lexical FTS remains shipped. pgvector foundation is in (migration 0117): content_embeddings table, semantic_rank_* RPCs, lexical/semantic blend helpers, and optional OpenAI-compatible query embed when HOLLAHOOP_EMBEDDINGS_ENABLED + API key are set. Index backfill / live ranking still needs model credentials + an indexer job - leaving this card Planned until that lands.
Elsama· 8 days ago
Also shipped: /api/cron/embeddings-backfill indexes recent posts into content_embeddings when HOLLAHOOP_EMBEDDINGS_ENABLED + API key are set (no-op otherwise). Lexical FTS remains the default path.
Elsama· 8 days ago
Overnight update: lexical FTS remains shipped; pgvector schema/blend/backfill cron already in 0117. Still blocked on HOLLAHOOP_EMBEDDINGS_ENABLED + API key on the VM for live semantic ranking. TipTap trailing-@ mentions, MFA recovery codes (0122), org soft require_mfa (0123), and Twilio SMS delivery log (0124) shipped in code tonight.
Elsama· 8 days ago
Overnight status: lexical FTS is shipped (search_tsv + GIN, /api/search, command palette, MCP search). pgvector semantic search remains planned — schema/blend/backfill cron exist but need HOLLAHOOP_EMBEDDINGS_ENABLED + model credentials on the VM. Leaving this card Planned until embeddings are live; do not treat the FTS half as still open.
Elsama· 8 days ago
Hygiene (overnight grind): Lexical FTS is shipped (tsvector + GIN, lexical_rank_*, public search + MCP search). pgvector semantic search remains planned — schema/blend/backfill cron ship in 0117 but live ranking still needs HOLLAHOOP_EMBEDDINGS_ENABLED + API key on the VM.

Admin suggestion: split this card or mark the FTS half Shipped in the body so the roadmap doesn’t imply semantic search is live.

Join the discussion