Lesson 7.2: Designing the RAG Pipeline
Schema Design
-- Documents
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
source TEXT NOT NULL, -- 'upload', 'web', 'api'
content TEXT NOT NULL,
category TEXT,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ -- Soft delete
);
CREATE INDEX documents_category_idx ON documents(category) WHERE deleted_at IS NULL;
CREATE INDEX documents_created_idx ON documents(created_at DESC) WHERE deleted_at IS NULL;
-- Chunks for embedding
CREATE TABLE document_chunks (
id BIGSERIAL PRIMARY KEY,
document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_index INT NOT NULL,
content TEXT NOT NULL,
token_count INT NOT NULL,
embedding VECTOR(1536) NOT NULL,
UNIQUE(document_id, chunk_index)
);
CREATE INDEX chunks_embedding_hnsw ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX chunks_document_idx ON document_chunks(document_id);
-- Permissions
CREATE TABLE document_permissions (
document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
user_id BIGINT NOT NULL,
permission_level TEXT NOT NULL DEFAULT 'read', -- 'read', 'write', 'admin'
granted_at TIMESTAMPTZ DEFAULT NOW(),
PRIMARY KEY (document_id, user_id)
);
CREATE INDEX document_permissions_user_idx ON document_permissions(user_id);
Query Patterns
-- Hybrid RAG query with permissions
SELECT
d.id AS document_id,
d.title,
c.content AS chunk_content,
c.embedding <=> $1::vector AS similarity_score
FROM document_chunks c
JOIN documents d ON c.document_id = d.id
JOIN document_permissions p ON d.id = p.document_id
WHERE
-- Permission check
p.user_id = $2
AND p.permission_level IN ('read', 'write', 'admin')
-- Metadata filters
AND d.category = $3
AND d.deleted_at IS NULL
-- Similarity threshold
AND c.embedding <=> $1::vector < 0.75
ORDER BY similarity_score
LIMIT 10;
-- Index strategy ensures:
-- 1. chunks_embedding_hnsw: Fast vector search
-- 2. document_permissions_user_idx: Fast permission check
-- 3. documents_category_idx: Fast category filter
Key Takeaways
- Three-table design: documents, document_chunks, document_permissions
- Soft deletes preserve data while hiding it from queries (deleted_at column)
- Chunking strategy splits documents for better embedding and retrieval
- HNSW index on embeddings enables fast vector similarity search
- Permission system ensures users only see authorized documents
- Partial indexes (WHERE deleted_at IS NULL) improve query performance
- CASCADE deletes automatically clean up chunks and permissions when documents are deleted
- Query combines vector search, permissions, and metadata filters in one SQL statement
التالي · الدرس 41 من 45
Building the Feature Store
قراءة 2 دقيقة
أنهيت 40 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

