Database Schema & Prisma Models
Relevant source files
The following files were used as context for generating this wiki page:
The database layer serves as the central state management system for the AI Code Reviewer, coordinating between the web server and background worker processes. It utilizes PostgreSQL with the pgvector extension to facilitate both traditional relational data storage and advanced semantic search capabilities required for AI-driven code analysis.
The schema is managed via Prisma, which acts as the single source of truth for all models, migrations, and the generated client. This architecture ensures transactional consistency for background jobs and provides a "compounding moat" through a multi-layered retrieval system that stores codebase context and review history.
Sources: README.md, packages/db/README.md, packages/db/prisma/schema.prisma:10-15
Core Architecture & Relationships
The system is designed around a hierarchical structure starting from GitHub Users and Installations, leading down to specific Repositories and their associated AI Review activities.
Entity Relationship Overview
The following diagram illustrates the primary relationships between the core management entities:
The schema establishes a clear ownership chain where an Installation (representing an organization or user account) acts as the container for Repository entities and billing via the Subscription and Plan models.
Sources: packages/db/prisma/schema.prisma:19-115
Core Management Models
| Model | Description | Key Fields |
|---|---|---|
| User | Represents a GitHub user who authenticated with the application. | githubId, githubLogin, email |
| Installation | Represents a GitHub App installation on an account or organization. | githubId, accountLogin, suspended |
| Repository | A specific GitHub repository enabled for AI review. | githubId, owner, name, enabled |
| Plan | Defines usage limits (repos, tokens, PRs) and billing tiers. | slug, maxPrsPerMonth, priceMonthlyCents |
Sources: packages/db/prisma/schema.prisma:19-158, packages/db/prisma/seed.ts:20-50
AI Review System
The AI Review system tracks the lifecycle of code analysis from the arrival of a webhook to the final posting of comments on GitHub.
Review Lifecycle and Statuses
Reviews progress through several states as they are processed by the worker:
- PENDING: Initial state when a webhook is received.
- RUNNING: The worker has picked up the job and is calling the LLM.
- COMPLETED: Review comments and summary have been posted to GitHub.
- SKIPPED: Occurs if the repository or specific author is on an ignore list.
Sources: packages/db/prisma/schema.prisma:418-426, README.md
Review Data Models
| Model | Purpose |
|---|---|
| AiReview | Tracks one review cycle per (repo, PR, commit SHA). Stores the githubReviewId and summary. |
| AiReviewComment | Stores individual inline comments with severity, filePath, and optional code suggestion. |
| ReviewConfiguration | Per-repo or per-installation settings such as ignorePaths, maxFilesPerPr, and severityThreshold. |
Sources: packages/db/prisma/schema.prisma:380-466
Retrieval Layer & Vector Search
The repository implements a sophisticated Retrieval-Augmented Generation (RAG) system using pgvector. This allows the LLM to access context beyond the immediate diff.
Multi-Layer Context Retrieval
The schema supports four distinct layers of knowledge, three of which are directly represented in the DB:
- Codebase Context (Layer 1): Indexed functions and classes stored as
Chunkentities. - Review Memory (Layer 2): Historical review decisions (Accepted/Dismissed) to prevent repeated flagging of the same patterns.
- Repo Knowledge (Layer 2 - Extracted): Plain-English statements about architecture or conventions learned from developer feedback.
Vector Models Detail
The ChunkEmbedding, ReviewMemoryEmbedding, and RepoKnowledgeEmbedding models store high-dimensional vectors (1536 dimensions) using the Unsupported("vector(1536)") type, which matches OpenAI's text-embedding-3-small output.
Sources: packages/db/prisma/schema.prisma:307-353, packages/db/prisma/migrations/20260602103609_review_memory/migration.sql:22-68, CLAUDE.md
Configuration & Feature Management
The system uses database-backed feature flags and provider credentials to manage application behavior and third-party integrations.
Feature Flags and Credentials
- FeatureFlag: Allows for enabling/disabling features like
billing.enforcementorretrieval.rerankwith support for percentage rollouts and installation-specific overrides. - ProviderCredential: Stores encrypted API keys for LLM and Embedding providers. It uses AES-256-GCM, storing only the
encryptedSecret.
Sources: packages/db/prisma/schema.prisma:186-235, packages/db/prisma/seed.ts:58-85
Usage and Workflows
Prisma acts as the single entry point for database interactions within both the web and worker processes.
// Standard accessor for the Prisma singleton
import { getPrismaClient } from '@repo/db';
const prisma = getPrismaClient();
Common Commands
db:migrate: Applies migrations and updates the generated client.db:seed: Populates canonical data for plans and feature flags.db:studio: Opens a GUI for ad-hoc data inspection.
Sources: packages/db/README.md, packages/db/package.json:12-18
Conclusion
The database schema of the ai-reviwer project is not merely a storage layer but a core engine for AI orchestration. By integrating relational data with vector embeddings, the system enables a "compounding moat" of knowledge where the reviewer becomes more accurate over time by learning from repository-specific patterns and developer feedback. Significant emphasis is placed on security via encrypted credentials and operational stability through a centralized Prisma client.