In many projects, a knowledge base starts out as a collection of Markdown files, FAQs, product descriptions, technical documents, or operations manuals.
If you only need to display pages, keeping this content in a Git repository, CMS, or object storage is fine. But once you want on-site search, related article recommendations, intelligent Q&A, or an LLM that answers user questions from this content, it can no longer stay at the "file" level.
A lightweight and practical approach is:
Use PostgreSQL as the structured storage layer of the knowledge base, managing documents, sections, tags, sources, vectors, and search state in one place, then add LLM-powered Q&A on top.
If you use Supabase, you are essentially using managed PostgreSQL, and it is straightforward to enable pgvector, access policies, and API features.
The Overall Idea
The core of a knowledge base is not throwing all your content at an LLM. It is first doing controlled content management and retrieval in the database.
A basic flow can be designed like this:
Markdown / documents / FAQs
│
▼
Parsing and cleaning
- Extract titles, summaries, tags, and sources
- Remove unnecessary formatting
- Split by paragraph or meaning
│
▼
PostgreSQL
- documents: document metadata
- document_chunks: knowledge chunks
- tsvector: full-text search
- vector: semantic search
│
▼
Retrieval layer
- Keyword search
- Vector retrieval
- Hybrid ranking
│
▼
LLM answer
- Assemble context
- Limit the answer scope
- Return source citations
PostgreSQL acts as the foundation of the knowledge base here: it stores content, maintains structure, provides query capabilities, and gives the LLM controlled context.
Table Design
Start by splitting the knowledge base into two layers: documents and chunks.
documents stores the metadata of a complete piece of content, such as the title, slug, source, language, status, and update time.
create table documents (
id uuid primary key default gen_random_uuid(),
slug text not null unique,
title text not null,
summary text,
source text,
locale text not null default 'zh',
tags text[] not null default '{}',
status text not null default 'published',
published_at timestamptz,
updated_at timestamptz not null default now()
);
document_chunks stores the split knowledge chunks. During LLM Q&A, you usually do not put a whole article into the context. Instead, you retrieve the few most relevant chunks.
create table document_chunks (
id uuid primary key default gen_random_uuid(),
document_id uuid not null references documents(id) on delete cascade,
chunk_index int not null,
heading text,
content text not null,
token_count int,
search_vector tsvector,
embedding vector(1536),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
In this table:
contentis the chunk bodysearch_vectoris used for PostgreSQL full-text searchembeddingis used for vector searchheadingkeeps the subheading the chunk belongs to, which makes it easy to generate citations in answers
If you are not doing vector search yet, you can leave out the embedding field and get started with full-text search and structured tags alone.
Document Chunking
The quality of knowledge base Q&A depends heavily on the chunking strategy.
If chunks are too large, retrieved content is redundant and wastes context. If they are too small, they lack meaning and the LLM may not get complete information.
A fairly safe approach is to:
- Split by Markdown headings first
- Keep the heading path each chunk belongs to
- Keep each chunk to around 300 to 800 Chinese characters, or a similar length in other languages
- Split overly long sections by paragraph
- Avoid cutting through tables, lists, and code blocks
Take this Markdown article as an example:
## Refund Policy
If your learning progress is below 20%, you can request a refund within 7 days of buying a course.
## Invoices
Business customers can request a standard invoice or a special invoice.
It can be split into two chunks:
[
{
"heading": "Refund Policy",
"content": "If your learning progress is below 20%, you can request a refund within 7 days of buying a course."
},
{
"heading": "Invoices",
"content": "Business customers can request a standard invoice or a special invoice."
}
]
The benefit is that search results return not only the body text but also which topic the answer came from.
Full-Text Search
PostgreSQL has built-in full-text search, which suits questions with clear keywords.
For example, if a user searches for "refund invoice", you can match with to_tsvector and plainto_tsquery.
update document_chunks
set search_vector = to_tsvector('simple', coalesce(heading, '') || ' ' || content);
Then create an index:
create index document_chunks_search_idx
on document_chunks
using gin(search_vector);
And query it:
select
dc.id,
dc.heading,
dc.content,
ts_rank(dc.search_vector, plainto_tsquery('simple', 'refund invoice')) as rank
from document_chunks dc
join documents d on d.id = dc.document_id
where
d.status = 'published'
and dc.search_vector @@ plainto_tsquery('simple', 'refund invoice')
order by rank desc
limit 5;
Full-text search is explainable, inexpensive, and fast. It works well when the user's question already contains clear keywords.
Vector Retrieval
When a user's question is a semantic expression rather than simple keywords, you need vector search.
For example:
What can I do if I'm not happy with a course I bought?
This sentence does not contain the word "refund", but it should still retrieve the refund policy.
With pgvector, you can index document_chunks.embedding:
create extension if not exists vector;
create index document_chunks_embedding_idx
on document_chunks
using ivfflat (embedding vector_cosine_ops)
with (lists = 100);
At query time, first convert the user's question into an embedding, then run a similarity search:
select
dc.id,
dc.heading,
dc.content,
1 - (dc.embedding <=> $1) as similarity
from document_chunks dc
join documents d on d.id = dc.document_id
where d.status = 'published'
order by dc.embedding <=> $1
limit 5;
Here, $1 is the vector of the user's question.
Vector retrieval suits natural language questions better, but it has limits: being semantically similar does not always mean the business answer is correct. That is why real projects rarely rely on vector retrieval alone.
Hybrid Retrieval
A safer approach is to combine full-text search with vector retrieval.
One simple method:
- Retrieve a batch of results with full-text search
- Retrieve a batch of results with vector search
- Merge and deduplicate them
- Rerank by keyword score, vector similarity, document weight, and update time
The pseudocode looks roughly like this:
const keywordResults = await searchByKeyword(question);
const vectorResults = await searchByEmbedding(questionEmbedding);
const merged = mergeAndRerank({
keywordResults,
vectorResults,
weights: {
keyword: 0.4,
vector: 0.5,
freshness: 0.1,
},
});
For small and medium knowledge bases, this kind of hybrid retrieval can already handle many real questions.
If content volume and query complexity grow much larger, you can consider a dedicated search service or vector database later.
LLM Q&A Endpoint
After retrieving relevant chunks, pass them to the LLM as context.
The key is not to let the model improvise, but to explicitly require it to answer only from the context.
const context = chunks
.map((chunk, index) => {
return `[${index + 1}] ${chunk.heading ?? 'Knowledge chunk'}\n${chunk.content}`;
})
.join('\n\n');
const prompt = `
You are a knowledge base Q&A assistant.
Answer the user's question using only the materials below.
If the materials do not contain the answer, say "No relevant information was found in the current materials."
List the citation numbers after your answer.
Materials:
${context}
User question:
${question}
`;
The answer can then include citations:
You can request a refund within 7 days of purchase if your learning progress is below 20%. References: [1]
This reduces hallucinations and makes it easy for users to check the original text.
Content Update Flow
A knowledge base does not end with a one-time import. More often, articles, FAQs, and product descriptions keep being updated.
You can reuse a Git + GitHub Actions flow:
Markdown update
│
▼
GitHub Actions
- Validate frontmatter
- Update documents
- Re-chunk content
- Generate embeddings
- Upsert to PostgreSQL
│
▼
Next.js / API
- Revalidate page cache
- Q&A endpoint reads the latest knowledge chunks
The update strategy can stay simple:
- Document content unchanged: do not regenerate embeddings
- Document content changed: delete old chunks, then re-chunk and write them
- Title or tags changed: update only
documents - Publish status changed: apply it to search conditions immediately
If content volume is modest, regenerating chunks and embeddings for a whole article is acceptable. Once data grows, you can add incremental diffs.
Permissions and Security
If a knowledge base serves only public content, the permission model is fairly simple.
If it involves internal documents, customer data, or paid content, permission filtering must happen in the retrieval layer, not after the LLM has answered.
For example:
select dc.*
from document_chunks dc
join documents d on d.id = dc.document_id
where
d.status = 'published'
and d.visibility in ('public', 'customer')
order by dc.embedding <=> $1
limit 5;
The principle is: content a user is not allowed to see should never enter the LLM context.
With Supabase, you can also use RLS to control which documents different users, organizations, or roles can access.
Where This Approach Fits
A PostgreSQL-based knowledge base suits:
- Company website FAQs and technical blog Q&A
- SaaS help centers
- Internal operations manuals
- Product documentation search
- Small and medium RAG Q&A systems
- Projects already using Supabase or PostgreSQL
Its advantage is a short engineering path: content, metadata, permissions, full-text search, and vector search can all live in the same database, which keeps development and maintenance costs manageable.
Of course, this is not the final stop for every knowledge base. If content volume becomes very large, or you need complex search ranking, log analysis, or large-scale multi-tenant vector search, you can split out the search layer later. But early on, using PostgreSQL to manage your content assets is usually enough.
Practical Scenario: StudyAtMy
Take an education information platform like StudyAtMy as an example. Its knowledge base is more than a few static articles. It may include:
- School profiles
- Tuition details
- Curricula
- Application processes
- Visa and guardian policies
- Common parent questions
- Study guides and case studies
This content is a natural fit for PostgreSQL: fields such as school, city, curriculum, tags, publish date, and language version can be queried as structured data, while the body text can be split into knowledge chunks and retrieved with full-text and vector search.
When a user asks "Which international schools in Kuala Lumpur suit younger children?" or "What materials are usually needed to apply to an international school in Malaysia?", the system can first select public, relevant school data, guides, and FAQ chunks from PostgreSQL, then pass them to the LLM to generate an answer.
The point is not to let the model answer from nothing, but to have it answer from content the platform has already organized and reviewed. PostgreSQL serves as the content fact store and retrieval layer, while the LLM turns retrieved results into more natural language.
Summary
The key to building a knowledge base with PostgreSQL is not treating the database as a simple text store, but using its structured capabilities:
- Use tables to manage documents, chunks, status, and sources
- Use full-text search for questions with clear keywords
- Use
pgvectorfor semantically similar questions - Use hybrid retrieval to make Q&A more reliable
- Use permission filtering to ensure the LLM can only read content the user can see
- Use GitHub Actions or backend processes to keep knowledge content in sync
With this in place, Markdown, documents, and FAQs are no longer just page content. They can also become the foundation for search, recommendations, and intelligent Q&A.