The problem

Point a generic “Copilot”-style agent at a repository of PDFs and Word docs and it will answer questions, but you get almost no control over how it ingests documents, how it ranks evidence, whether it actually cites the right page, or what happens to sensitive content on the way to the model. That’s fine for casual use and a real problem the moment an organization needs a dependable, auditable, secure way to query large document repositories — legal contracts, internal policies, compliance archives, technical manuals — where “the answer must be traceable to an exact passage” isn’t optional and “don’t leak PII to a third-party model” isn’t optional either. I built this design to answer a specific question I kept running into: what does a RAG system look like when you actually need to control ingestion quality, retrieval precision, and data exposure end to end, instead of trusting a black box?

The approach

This is a complete, implementable architecture spec for a Python + Postgres RAG system, written as a design document rather than shipped code, but detailed enough to build from directly. It lays out two pipelines. The ingestion pipeline discovers documents, hashes them with sha256 for incremental reindexing, parses PDF/DOCX (python-docx, pypdf), chunks text (starting at 800 tokens with 150-token overlap, splitting on headings where possible), embeds each chunk with a multilingual retrieval model, and upserts everything into Postgres. The query pipeline is the interesting part: it runs hybrid retrieval, meaning Postgres full-text search (lexical, tuned for Italian stemming and exact identifiers) and pgvector similarity search (semantic, for paraphrases) run in parallel, get fused with Reciprocal Rank Fusion, and only then hit a mandatory reranking stage — a GPU-backed cross-encoder microservice that rescoring the top ~100 candidates down to the real top-K evidence, because reranking directly optimizes relevance in a way embeddings alone don’t.

Before anything reaches an LLM, sensitive content gets deterministically obfuscated — regex and dictionary rules replace PII and confidential identifiers with typed placeholders like [[EMAIL_1]] — and only rehydrated back into the final answer locally, after generation, so raw sensitive data never has to leave the boundary and never gets logged. The generation prompt is constrained to answer only from supplied evidence, cite document/page/section for every claim, preserve placeholders, and refuse when evidence is insufficient. The doc also specifies the minimum data model (documents, chunks, chunk_meta, eval_cases tables), a proposed Streamlit chat UI with expandable “show evidence” panels and citation display, and a five-phase rollout plan (P0 prototype through P4 operations) with concrete, measurable exit criteria per phase — Recall@50 for the baseline, Hit@10 and latency targets for reranking, a passed security review before hardening is considered done.

What I learned

The clearest lesson: reranking is not a nice-to-have, it’s the single highest-leverage lever for answer precision, because embeddings are an approximate similarity measure while a cross-encoder reranker directly scores query/passage relevance — which is why I made it mandatory in the design rather than optional. The second lesson is that you don’t need a separate vector database to do this well: Postgres with FTS plus pgvector handles both lexical and semantic retrieval for small-to-medium document repositories, and keeping the whole stack to “Python services + Postgres” is a real simplification worth defending against the instinct to add a dedicated vector DB service. Third, obfuscation/rehydration only works if it’s deterministic and local — placeholders you can reverse in memory, never persisted, never logged in raw form — which is a very different (and much more auditable) posture than hoping the model provider handles PII correctly.

Where this could go

The problem this addresses isn’t personal-only — it’s exactly the shape enterprise IT and compliance teams hit whenever they want employees, or agents, to query internal document stores (contracts, policies, regulatory filings, support ticket archives) without either leaking PII to an external model or trusting an ungoverned copilot’s citations. Because the design specifies explicit, measurable exit criteria per phase, a team could pick this up and actually stage a real internal build off it rather than treating it as a whitepaper. The natural extension is agentic retrieval — adding a reflection loop that checks whether the retrieved evidence actually satisfies the question before generating, rather than a single-shot retrieve-then-generate pass, which is the direction more sophisticated retrieval platforms (and a related project of mine on agentic retrieval) are already heading. The reranker-as-microservice pattern also generalizes cleanly to multi-tenant setups: one shared GPU rerank service sitting behind several ingestion pipelines for different document domains or business units.