[Proposal] Multi-Tenant RAG, pgvector Hybrid Search & LLM Streaming Integration

[VimalN2005]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

from app.services.rag import hybrid_search

# Native multi-tenant pgvector hybrid search (HNSW cosine + full-text RRF)
results = hybrid_search(
    session=session,
    user_id=current_user.id,
    query="system architecture",
    top_k=5,
    rerank=True,
)

Description

Proposing to add native multi-tenant RAG, pgvector hybrid search, and real-time LLM token streaming to the official full-stack template.

I have implemented and verified this entire pipeline with 90/90 tests passing:
https://github.com/VimalN2005/full-stack-fastapi-template

Key features added:

1. Native pgvector hybrid search (HNSW cosine distance + PostgreSQL full-text search with Reciprocal Rank Fusion).
2. Two-stage retrieval with cross-encoder re-ranking.
3. Abort-aware SSE token streaming with client disconnect detection (request.is_disconnected()).
4. Real-time monthly AI token metering & HTTP 429 budget quota guardrails.
5. Persistent multi-turn conversation memory with chat sessions.
6. Async background document ingestion via FastAPI BackgroundTasks with live status polling.
7. React 19 + Tailwind + shadcn Knowledge Base and streaming AI Chat UI.

Operating System

Windows

Operating System Details

Windows 11

Project Version

latest (master)

Python Version

3.10+

Additional Context

All 90/90 pytest tests pass cleanly with zero external API key requirements (deterministic offline embedding fallback included).

Full working fork & architecture documentation:
https://github.com/VimalN2005/full-stack-fastapi-template

[brians930617]

I'm particularly interested in how the multi-tenant isolation is enforced at the database level.

For the hybrid search, is user_id always applied as a mandatory filter to both the pgvector similarity query and the PostgreSQL full-text search query before the Reciprocal Rank Fusion step?

For example, if two users have documents with very similar content, could a result from another tenant ever enter either candidate set before RRF/re-ranking?

It would also be interesting to see an integration test that explicitly verifies cross-tenant isolation—for example, user A searches for a document that exists only for user B and must receive zero results.

That seems especially important for a production RAG implementation because filtering after retrieval/reranking could potentially expose another tenant's document metadata or content.

[VimalN2005]

Hi @brians930617, thank you for the great question!

Yes, multi-tenant isolation is enforced strictly at the database query level prior to candidate retrieval, Reciprocal Rank Fusion (RRF), and cross-encoder re-ranking:

1. Database-Level Query Isolation

Both search pipelines apply owner_id == user_id directly in their SQL WHERE clauses with an indexed foreign key:

• Dense Vector Search (pgvector):

  SELECT documentchunk.*, embedding <=> :query_vector AS distance
  FROM documentchunk
  WHERE documentchunk.owner_id = :user_id
    AND documentchunk.embedding IS NOT NULL
  ORDER BY distance
  LIMIT :top_k * 2;