pal-e-docs

Concept: Building a Self-Hosted RAG (Act 2 Architecture)

concept-phase5-self-hosted-rag Doc

active concept
Project

Are We Building a RAG?

Yes — but not the typical one.
Act 2 of the Postgres plan (Phases 5-8) is building a fully self-hosted RAG system that runs entirely inside Postgres + MCP tools. No external vector database, no managed embedding API dependency, no LangChain orchestration. The whole thing lives where the data already lives.

How It Maps to RAG Components

RAG Component Typical Stack What We're Building
<strong>Document Store</strong> S3 + vector DB (Pinecone, Weaviate) Postgres (notes table, blocks table)
<strong>Chunking</strong> LangChain text splitters (arbitrary 512-token windows) Phase 7 blocks (natural semantic chunks — headings, paragraphs, tables, code)
<strong>Embedding</strong> OpenAI ada-002 API calls Phase 6 pgvector (embeddings stored in DB)
<strong>Keyword Index</strong> Elasticsearch Phase 5 tsvector (built into Postgres)
<strong>Retrieval</strong> Multi-step orchestration code Phase 8 compound MCP queries
<strong>Augmented Generation</strong> Prompt stuffing into LLM context Agent reads search results + snippets

What Makes This Different

1. Retrieval Is Invisible

We're not building a retrieval pipeline outside the database that feeds into an LLM. We're building retrieval inside the database and exposing it through the same MCP tools agents already use. The agent doesn't know it's doing RAG — it just calls search_notes("postgres restore") and gets a ranked, snippet-enriched answer. The retrieval is invisible.
This is the database-side intelligence pattern: Postgres does the heavy lifting, every other layer is thin.

2. The Content Model IS the Chunking Strategy

Most RAG systems have a chunking problem — how do you split documents into meaningful pieces? Arbitrary 512-token windows with 50-token overlap? Sentence splitting? Paragraph splitting? Every approach loses context at the boundaries.
Phase 7 (blocks) solves this naturally. Notes decompose into typed blocks (headings, paragraphs, tables, code). Each block is a semantic unit with a known type. You embed blocks, not whole documents. No arbitrary splitting, no overlap windows. The content model is the chunking strategy.
A table block stays a table. A code block stays a code block. A heading + its paragraphs stay together. The structure that humans created when writing the note is preserved in the retrieval.

3. Hybrid Search From Day One

Most RAG systems start with vector search and bolt on keyword search later when they realize embeddings miss exact terms. We're building both in the same database:
  • Phase 5 (tsvector) — deterministic keyword search. "Find notes containing 'kubectl port-forward'." Precise, fast, no false positives.
  • Phase 6 (pgvector) — semantic similarity search. "Find notes about recovering from infrastructure failures." Fuzzy, conceptual, handles synonyms.
  • Phase 8 — compound queries that combine both. "Find notes semantically related to 'disaster recovery' that also mention 'MinIO'." Best of both worlds in a single query.
Because both indexes live in the same database, hybrid search is a JOIN, not an orchestration problem.

4. Zero External Dependencies

The entire system runs on the k3s cluster:
  • Postgres (CNPG operator) — already deployed
  • pal-e-docs API — already deployed
  • MCP tools — already deployed
  • Embeddings — TBD, but can run locally (e.g., sentence-transformers, or a self-hosted model)
No Pinecone bills. No OpenAI embedding API rate limits. No data leaving the cluster. The knowledge stays where it lives.

The Progression

Each phase adds a capability. By Phase 8, the agent can ask a natural language question and get back the specific blocks from the specific notes that answer it — ranked by relevance, with both keyword and semantic matching, chunked at natural content boundaries. That's RAG. But the agent just thinks it's calling an MCP tool.
  • phase-postgres-5-fulltext-search — Phase 5 (keyword search layer)
  • phase-postgres-6-vector-search — Phase 6 (semantic search layer)
  • phase-postgres-7-block-content — Phase 7 (chunking layer)
  • phase-postgres-8-mcp-optimization — Phase 8 (compound retrieval)
  • concept-phase5-database-side-intelligence — the underlying architecture pattern
  • plan-2026-02-26-tf-modularize-postgres — the parent plan (Act 2 vision)