System Design: Enterprise Knowledge Assistant with RAG & Role-Based Access
Prerequisites: The 4-Step Framework for AI System Design, Diagnosing RAG Failure Modes.
Key Takeaway: In enterprise B2B environments, building a RAG assistant is not just about semantic relevance; it is strictly about Access Control Security. If an intern asks "What are the company's Q4 financial forecasts?", the vector database must never retrieve executive board decks. Designing enterprise RAG requires Pre-Filtering RBAC, Multi-Tenant Partitions, and Verifiable Citations.
The Fatal Flaw of Post-Filtering in Enterprise RAG
Inexperienced engineers often propose Post-Generation Filtering:
$$\text{User Query} \longrightarrow \text{Vector Search (All Docs)} \longrightarrow \text{LLM Answers} \longrightarrow \text{Filter Output}$$
Why this is an enterprise disaster:
- The Model Already Saw the Secret: Once confidential executive salary data is injected into the LLM context, it influences subsequent reasoning. Even if you redact the exact number, the model will answer related questions using that context.
- Context Window Pollution: If the top 5 vector matches are confidential files the user cannot view, discarding them after retrieval leaves zero relevant documents for the user's actual question.
- Indirect Prompt Injection: An attacker can craft a query that instructs the model to ignore post-filters.
The Golden Security Rule: Security filtering must happen at the database query layer (Pre-Filtering) before vectors are retrieved.
graph TD
User["User (Org: Acme, Roles: ['sales', 'viewer'])"] --> Query["User Query: 'Enterprise deal discounts'"]
Query --> VectorDB["pgvector Database Query"]
subgraph "Pre-Filter Security Gate (Database Level)"
VectorDB --> Clause["WHERE tenant_id = 'acme' AND permissions && ARRAY['sales', 'viewer']"]
Clause --> HNSW["HNSW Vector Search on Filtered Subset Only"]
end
HNSW --> TopChunks["Top 5 Authorized Chunks Only"]
TopChunks --> Prompt["Prompt Assembly (<source id='N'>)"]
Prompt --> LLM["LLM Generation (Zero Access to Unauthorized Data)"]
LLM --> UI["Frontend Streaming + Interactive Citation Badges"]Database Schema for Multi-Tenant RBAC Vector Search
In PostgreSQL with pgvector, documents and chunks must be linked to organizational tenancy and permission arrays:
-- Schema for enterprise RAG with RBAC
CREATE TABLE document_chunks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES organizations(id),
document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
content TEXT NOT NULL,
embedding VECTOR(1536) NOT NULL,
-- Role-based access control permissions
allowed_roles TEXT[] NOT NULL DEFAULT '{"admin"}',
user_access_list UUID[] DEFAULT '{}', -- Optional specific user overrides
metadata JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Compound index optimizing tenancy + permissions + vector similarity
CREATE INDEX idx_chunks_tenant_roles_embedding ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WHERE tenant_id IS NOT NULL;The Secure Query:
SELECT id, document_id, content, 1 - (embedding <=> $1) AS similarity
FROM document_chunks
WHERE tenant_id = $2
AND (allowed_roles && $3 OR $4 = ANY(user_access_list))
ORDER BY embedding <=> $1
LIMIT 10;Handling Real-Time Permission Revocation
What happens when an employee is removed from the finance role?
- Because permissions are evaluated at query time via
allowed_roles && $userRoles, the revocation is immediate (0ms latency). - There is no need to rebuild or re-embed the vector database.
Verifiable Citations & Source Drawers
Enterprise users do not trust unverified claims. The frontend interface must connect every generated sentence to its underlying authorized passage:
- The prompt encloses sources in
<source id="1" title="Pricing Guide">...</source>. - The model outputs bracketed citations
[1]. - The React markdown renderer converts
[1]into an interactive button that opens an authorized Source Drawer:- Document title and department badge.
- Last updated timestamp.
- Direct link to the source Confluence / Google Drive file.
- Mathematical similarity score.
Common Pitfalls & Anti-Patterns
- Relying on System Prompts to Enforce Permissions: Writing "You must only answer if the user is an admin" is a prompt injection vulnerability. Always enforce at the database query layer.
- Missing Cross-Tenant Isolation Tests: In multi-tenant SaaS, always write automated integration tests verifying that Tenant A cannot retrieve Tenant B vectors under any adversarial prompt.
- Chunking Across Document Permission Boundaries: If a document has an executive summary for all staff, but an appendix for executives only, slicing chunks across the boundary leaks executive context into staff chunks. Split chunks strictly within permission sections.
Senior Engineer Interview Angle
Staff Engineer Question: "How do you handle enterprise search across 5,000,000 documents when 90% of documents have unique per-user Google Drive / Slack access control lists (ACLs) rather than simple global roles?"
Exemplary Answer:
*"When documents have complex per-user access control lists (ACLs) involving thousands of individual user IDs, storing large user arrays inside every vector chunk can slow down database index scans. We solve this with Two-Stage Authorization Retrieval:
- Stage 1 (Authorized Document ID Resolution): When a user queries, we query our relational database (or Elasticsearch index) where access lists are normalized:
SELECT document_id FROM user_doc_permissions WHERE user_id = $1. This returns an array of authorized document IDs in ~5ms.- Stage 2 (Constrained Vector Search): We pass the authorized
document_idarray into our pgvector query usingWHERE document_id = ANY($1).- Partitioning: For massive enterprise tenants with millions of documents, we use PostgreSQL table partitioning by
tenant_idso vector HNSW graphs are physically isolated per tenant, ensuring sub-15ms vector search times."*
Quick Self-Check
- Why is post-generation permission filtering an enterprise security disaster?
- How does pre-filtering at the PostgreSQL database query level guarantee zero data leakage?
- What is the difference between Document-Level and Chunk-Level access control?