Enterprise RAG Architecture: Permission-Aware Knowledge Base with PostgreSQL and pgvector

Retrieval-Augmented Generation (RAG) is the process where a language model retrieves relevant parts from a company's own documents and uses them as context while generating a response. It provides up-to-date and organization-specific knowledge without retraining the model. However, the real challenge in an enterprise environment is not "finding the relevant piece," but "finding only the piece that the user is authorized to see." In this article, we outline a permission-aware RAG infrastructure using PostgreSQL and the pgvector extension.

Why PostgreSQL?

Instead of setting up a separate vector database, the pgvector extension adds vector search to PostgreSQL, which you are already using. This brings two major advantages from an enterprise perspective: document permissions and vectors live within the same transaction and the same authorization model (Row Level Security); and no new system needs to be learned for backup and monitoring.

Step 1: Chunking documents

Documents (contracts, procedures, technical documentation) are split into chunks that fit into the model context. The chunking strategy directly impacts result quality: an approach that respects heading boundaries and leaves a short overlap with neighboring chunks generally works better than splitting by a fixed character count. Each chunk must be linked to the source document and the document's access scope (department, company, confidentiality class).

create extension if not exists vector;

create table doc_chunks (
  id          bigserial primary key,
  document_id uuid not null references documents (id) on delete cascade,
  company_id  uuid not null,
  scope       text not null,          -- e.g. 'hr', 'finance', 'public'
  content     text not null,
  embedding   vector(768) not null,   -- dimension varies based on the embedding model used
  created_at  timestamptz not null default now()
);

create index on doc_chunks using hnsw (embedding vector_cosine_ops);

Step 2: Generating embeddings

An embedding model generates numerical vectors for each chunk. Open-weight embedding models can be used in local setups; the choice of model depends on language support (quality is important in Turkish texts), vector dimension, and hardware. Important rule: the embedding model used at query time must be the same model used when indexing the chunks; if the model changes, all chunks must be re-indexed.

Step 3: Permission-filtered search

This is the most critical step. The vector similarity query is executed along with the user's access scope. In a table with RLS enabled, the policy condition is automatically added to the query, so even if application code forgets the filter, the database will not return unauthorized chunks.

alter table doc_chunks enable row level security;

create policy chunks_read on doc_chunks for select using (
  company_id = current_company_id()          -- company in session
  and scope = any (current_user_scopes())    -- access scopes of the user
);

-- Query (policy applied automatically)
select id, document_id, content
from doc_chunks
order by embedding <=> $1    -- $1: embedding vector of the question
limit 8;

The most dangerous error in RAG systems is the assistant "summarizing" and presenting a document to a user who is not allowed to open it. The filter must be inside the data, not in front of the model.

Step 4: Context creation and source citation

The found chunks are provided to the model along with the question. When a response is generated, the user is shown which chunks were used as sources: document name, section, and link. Source citation serves two purposes: the user can verify the response, and it becomes easy to spot when the model "hallucinates."

Step 5: Audit log

For each query, the user, timestamp, question, returned chunk IDs, and model response are written to an audit table. These records are used for both security reviews and quality improvement. Since the logs themselves may contain personal data, retention periods and access permissions must be defined.

Quality: No improvement without an evaluation set

Before production, prepare a small evaluation set consisting of real user questions: the question, the expected source document, and an acceptable response. This set is re-run whenever the chunking strategy, chunk count, or model changes. We do not provide a numerical target; every organization's documents and expectations are different. The important thing is that changes are made measurably.

Local execution notes

• The model and embedding service should run in separate containers and be accessible only from the internal network.
• While indexing documents, content should be tagged according to its confidentiality class; "public" should not be the default.
• Model output should not directly trigger an action (such as record deletion); human approval must be in the loop.

Hybrid search: Vector alone is not enough

Vector similarity captures semantic proximity but can be weak on queries requiring exact matches (a contract number, a product code, a person's name). Combining PostgreSQL's full-text search feature (tsvector) with vector search and blending the results of both methods yields more accurate results in most enterprise scenarios. An appropriate dictionary configuration for Turkish (stemming and stop words) determines the quality of the full-text side.

-- Example: full-text + vector blend (conceptual)
with lexical as (
  select id, ts_rank(search_vector, plainto_tsquery('turkish', $q)) as score
  from doc_chunks where search_vector @@ plainto_tsquery('turkish', $q)
  order by score desc limit 20
), semantic as (
  select id, 1 - (embedding <=> $qvec) as score
  from doc_chunks order by embedding <=> $qvec limit 20
)
select coalesce(l.id, s.id) as id,
       coalesce(l.score, 0) * 0.4 + coalesce(s.score, 0) * 0.6 as combined
from lexical l full outer join semantic s on s.id = l.id
order by combined desc limit 8;

The weights (0.4 / 0.6) are examples and should be calibrated with the evaluation set.

What happens when a document is updated?

Enterprise documents change: procedures are updated, contracts are renewed. The index must track this change; otherwise, the assistant presents the old version as "current." Practical method: a hash value is kept for each document version, chunks of the changed document are deleted and regenerated, the old version remains in the archive but is excluded from searches. Chunks of a deleted document must also be instantly removed from the index; a "document no longer accessible" and a "document no longer existing" are treated the same way.

Model selection and hardware

In local deployments, model size is a balance between response quality and hardware cost. Small and medium-sized open-weight models can be sufficient for most enterprise needs in source-citing summarization and Q&A tasks; scenarios requiring complex multi-step reasoning demand a larger model. The decision is made using real questions in the evaluation set, not general benchmark tables. On the hardware side, GPU memory is decisive, and queue management is required as the number of concurrent users increases.

Enterprise rules in prompt design

• The model must respond only from the provided context; if it is not in the context, it should state "I do not have this information."
• Every response must return the used chunk IDs; source citation is generated from these IDs on the application side.
• Context containing personal data should be presented only to authorized users and only to the extent required by the question.
• User input must be added to the context as "data" and not as an "instruction"; text inside documents should not be considered instructions either (prompt injection).

User experience: Trust is built in the interface

Source links should be clickable and open the relevant section of the document; it should be visibly indicated that the response is a "model interpretation"; the user should be able to rate the response, and this feedback should enter the evaluation set. RAG systems are not systems that are set up once and left alone, but systems continuously tuned through feedback.

In Cy Logic's AI and automation services, RAG setups are implemented with these principles: authorization in the data, source in the response, audit in the log.