Database Schema

The RAG Starter Kit uses PostgreSQL with the pgvector extension. The schema is managed by Prisma and includes models for users, workspaces, documents, chat, and RBAC.

Core Models

User

Application users with authentication details

id	String (cuid)	Primary key
email	String (unique)	User email
name	String?	Display name
image	String?	Avatar URL
role	UserRole	USER, ADMIN

Workspace

Isolated tenant containers for multi-tenancy

id	String (cuid)	Primary key
name	String	Workspace display name
slug	String (unique)	URL-friendly identifier
ownerId	String	FK → User

WorkspaceMember

User-workspace membership with roles

id	String (cuid)	Primary key
userId	String	FK → User
workspaceId	String	FK → Workspace
role	WorkspaceRole	OWNER, ADMIN, EDITOR, VIEWER

Document & Vector Storage

Document

Uploaded documents in a workspace

id	String (cuid)	Primary key
name	String	Original filename
url	String	Storage URL
mimeType	String	File MIME type
size	Int	File size in bytes
workspaceId	String	FK → Workspace

DocumentChunk

Text chunks with vector embeddings for similarity search

id	String (cuid)	Primary key
content	String	Chunk text content
embedding	Unsupported("vector(768)")	pgvector embedding
chunkIndex	Int	Position in document
metadata	Json?	Page number, section, etc.
documentId	String	FK → Document

IngestionJob

Background job tracking for document processing

id	String (cuid)	Primary key
status	IngestionStatus	PENDING, PROCESSING, COMPLETED, FAILED
errorCategory	ErrorCategory?	PARSE_ERROR, EMBEDDING_ERROR, etc.
documentId	String	FK → Document

Chat

Conversation

Chat conversations tied to a workspace

id	String (cuid)	Primary key
title	String?	Auto-generated or user-set title
workspaceId	String	FK → Workspace
userId	String	FK → User

Message

Individual messages in a conversation

id	String (cuid)	Primary key
role	String	user, assistant, system
content	String	Message text
sources	Json?	Cited document chunks
conversationId	String	FK → Conversation

Vector Search Architecture

Document chunks are stored with vector(768) embeddings in PostgreSQL using the pgvector extension. Similarity search uses cosine distance with HNSW indexes for fast approximate nearest neighbor queries.

The hybrid retrieval pipeline combines vector similarity search with full-text search (PostgreSQL tsvector) using Reciprocal Rank Fusion (RRF) to merge results from both sources for optimal relevance.

← Environment Variables RBAC Permissions →