[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

# Multi-tenant pgvector RAG endpoint in app/api/routes/rag.py
from app.services.rag import hybrid_search

results = hybrid_search(
    session=session,
    user_id=current_user.id,
    query="system architecture",
    top_k=5,
)

Description

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

I have implemented and verified this in a complete branch with 90/90 tests passing:
https://github.com/VimalN2005/full-stack-fastapi-template

Key features:

1. Native pgvector multi-tenant hybrid search (HNSW cosine + full-text search with RRF).
2. Abort-aware SSE token streaming with client disconnect detection (request.is_disconnected()).
3. Monthly AI token metering & HTTP 429 budget quota guardrails.
4. Multi-turn conversation memory with persistent chat sessions.
5. Async background document ingestion with live status tracking.
6. React + Vite Knowledge Base and streaming AI Chat UI.

Operating System

Windows

Operating System Details

No response

Project Version

latest (master)

Python Version

3.10+

Additional Context

Complete working fork & documentation available here:
https://github.com/VimalN2005/full-stack-fastapi-template

[artur282]

Solid proposal, and the branch has real working code behind it — here is a review of the parts that need a decision before this could land in the template, plus the process note.

Process first: this repository asks for a Discussion and only opens PRs when a maintainer requests one, so the productive outcome here is agreement on the design. Worth putting the branch's diff and the migration in this thread so it can be evaluated without a PR.

1. Where the code lives. backend/app today is models.py, crud.py, api/deps.py, api/routes/*, core/*, alembic/* — there is no service layer. Introducing app/services/rag.py is reasonable (the annotated aliases in deps.py — SessionDep, CurrentUser — make a RAGServiceDep fit right in), but say it explicitly, because the alternative is the template's existing split: SQL in crud.py, orchestration in the route.

2. Tenant isolation is the part that must be airtight. Item is the precedent: owner_id on every row, every query filtered by current_user.id. For Document/DocumentChunk the same applies, with two additions:

• a chunk query that filters only by vector distance leaks another tenant's text — the owner filter must be in the same statement (JOIN document ON ... WHERE document.owner_id = :uid), never applied afterwards in Python;
• consider PostgreSQL RLS with a per-request SET LOCAL app.current_user_id on top of that. The app already runs on Postgres; RLS is the only mechanism that still protects the data if a future route forgets the filter. Composite index (owner_id, embedding) keeps the filter cheap either way.

3. pgvector specifics. CREATE EXTENSION IF NOT EXISTS vector; belongs in an Alembic revision (not in init_db()) so alembic upgrade head reproduces the schema; the HNSW index must declare the operator class matching the distance metric you actually query with (vector_cosine_ops for <=>, vector_l2_ops for <->) or the planner silently ignores it.

4. Hybrid search / RRF. websearch_to_tsquery on user input plus ts_rank_cd is the right full-text side; GIN on to_tsvector('english', content). Two details that bite: RRF needs a deterministic tie-break (ORDER BY rrf_score DESC, chunk_id) or pagination is unstable on tied scores, and each branch should be limited before fusion (LIMIT :k in each CTE) rather than after.

5. Streaming. StreamingResponse plus request.is_disconnected() is correct. Two constraints: once the response has started you cannot change the status code, so a provider error has to be emitted as a final SSE frame instead of raised; and per-token DB round-trips should not hold a transaction open for the length of the generation — resolve retrieval in a short session, close it, then stream.

6. Quota guardrails. A Depends that checks the monthly budget is simpler and more testable than middleware (middleware would have to decode the token a second time to know the user), and it allows a proper 429 with Retry-After.

7. Ingestion. BackgroundTasks is fine at template scale, but the job status has to be a column (document.status) instead of in-process memory, otherwise a restart loses it. If it needs to survive multiple workers, that is the point where a queue is the honest answer.

8. Tests worth having. The suite can assert all of this without network: A's query never returns B's chunk, RRF ordering on a fixture corpus, 429 when the budget is exhausted, and that the SSE stream ends with a terminal event. Embeddings can be stubbed with a deterministic fixed-dimension function; only the vector column's dimension then needs to match.