Database Setup

Repolyze uses PostgreSQL with Prisma ORM for managing user data, authentication, and rate limiting. This guide walks you through setting up the database.

Database Overview

Repolyze’s database stores:

User Authentication

NextAuth.js user accounts, sessions, and OAuth connections

Subscriptions

Polar.sh subscription data and plan information

Rate Limiting

IP-based tracking for analysis request limits

Audit Logs

Repository analysis request history

Prerequisites

Before you begin, ensure you have:

PostgreSQL 12 or higher installed
Database user with CREATE DATABASE privileges
Environment variables configured (see Environment Variables)

PostgreSQL Installation

macOS
Ubuntu/Debian
Windows
Docker

Using Homebrew:

# Install PostgreSQL
brew install postgresql@16

# Start PostgreSQL service
brew services start postgresql@16

# Verify installation
psql --version

Or use Postgres.app for a GUI-based installation.

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
psql --version

Download the installer from postgresql.org/download/windows
Run the installer and follow the setup wizard
Remember the password you set for the postgres user
Verify installation by opening pgAdmin or running:

psql --version

# Run PostgreSQL in a Docker container
docker run --name repolyze-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=repolyze \
  -p 5432:5432 \
  -d postgres:16

# Verify container is running
docker ps

Create Database

Connect to PostgreSQL and create a database for Repolyze:

# Connect to PostgreSQL (default user: postgres)
psql -U postgres

Then run these SQL commands:

-- Create database
CREATE DATABASE repolyze;

-- Create user (optional - for better security)
CREATE USER repolyze_user WITH PASSWORD 'your_secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE repolyze TO repolyze_user;

-- Exit psql
\q

If using Docker, the database is created automatically with -e POSTGRES_DB=repolyze

Configure Database URL

Add the database connection string to your .env.local file:

DATABASE_URL=postgresql://repolyze_user:your_secure_password@localhost:5432/repolyze

URL Format:

postgresql://[username]:[password]@[host]:[port]/[database]

Common configurations:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/repolyze

Generate Prisma Client

Generate the Prisma client from the schema:

pnpm prisma generate

This creates TypeScript types and the Prisma client in lib/generated/prisma/

The Prisma client is automatically generated during pnpm install via the postinstall script.

Run Database Migrations

Apply the database schema to your PostgreSQL database:

pnpm prisma migrate dev

This will:

Create all tables defined in prisma/schema.prisma
Generate migration files in prisma/migrations/
Regenerate the Prisma client

When prompted for a migration name, enter something descriptive like:

initial_schema

Verify Database Schema

Check that all tables were created successfully:

# Connect to database
psql -U repolyze_user -d repolyze

# List all tables
\dt

You should see:

User
Account
Session
VerificationToken
AnalysisRequest
_prisma_migrations

Database Schema

Repolyze’s database schema is defined in prisma/schema.prisma:

User Model

Stores user account information and subscription data:

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  accounts      Account[]
  sessions      Session[]
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  // Polar.sh Subscription
  plan                String    @default("free") // "free" | "pro"
  polarCustomerId     String?   @unique
  polarSubscriptionId String?
  planExpiresAt       DateTime?
}

Account Model

Stores OAuth provider connections (GitHub, Google):

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

Session Model

Manages user session tokens:

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

AnalysisRequest Model

Tracks repository analysis requests for rate limiting:

model AnalysisRequest {
  id        String   @id @default(cuid())
  ip        String
  repoUrl   String
  userId    String?  // null = anonymous user
  createdAt DateTime @default(now())

  @@index([ip, createdAt])
  @@index([userId, createdAt])
}

Prisma Commands

Useful Prisma commands for database management:

# Generate Prisma client from schema
pnpm prisma generate

Prisma Studio

Prisma Studio is a visual database browser:

pnpm prisma studio

This opens a web interface at http://localhost:5555 where you can:

Browse and edit database records
Filter and search data
Create, update, and delete records
View relationships between models

Be careful when modifying data in Prisma Studio, especially in production. Changes are applied immediately.

Connection Pooling

For production deployments, use connection pooling with Prisma Accelerate or pgBouncer:

Using Prisma with pg Adapter

Repolyze uses @prisma/adapter-pg for better connection handling:

import { PrismaClient } from '@prisma/client'
import { Pool } from 'pg'
import { PrismaPg } from '@prisma/adapter-pg'

const connectionString = process.env.DATABASE_URL
const pool = new Pool({ connectionString })
const adapter = new PrismaPg(pool)

const prisma = new PrismaClient({ adapter })

This is already configured in Repolyze’s codebase. The @prisma/adapter-pg and pg packages are installed.

Environment-Specific Configuration

Development

Use a local PostgreSQL instance:

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/repolyze_dev

Production

Use a managed PostgreSQL service with SSL:

DATABASE_URL=postgresql://user:[email protected]:5432/repolyze?sslmode=require&connection_limit=5

Troubleshooting

Connection refused error

If you see Error: connect ECONNREFUSED 127.0.0.1:5432:

Verify PostgreSQL is running:

# macOS (Homebrew)
brew services list

# Linux
sudo systemctl status postgresql

# Docker
docker ps

Check if PostgreSQL is listening on the correct port:
```
lsof -i :5432
```
Verify DATABASE_URL in .env.local is correct

Authentication failed error

If you see Error: password authentication failed:

Verify username and password in DATABASE_URL
Check PostgreSQL user permissions:
```
\du  -- List users and roles
```

Reset user password if needed:

ALTER USER repolyze_user WITH PASSWORD 'new_password';

Database does not exist

If you see Error: database "repolyze" does not exist:

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE repolyze;

Migration errors

If migrations fail:

Check the error message carefully
Verify database connection is working
Try resetting migrations (WARNING: deletes all data):
```
pnpm prisma migrate reset
```
Manually apply migrations:
```
pnpm prisma migrate deploy
```

Prisma client not found

If you see Cannot find module '@prisma/client':

# Regenerate Prisma client
pnpm prisma generate

# Or reinstall dependencies
rm -rf node_modules
pnpm install

SSL/TLS connection errors

For production databases requiring SSL:

# Add SSL parameters to DATABASE_URL
DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

# Or disable SSL verification (not recommended for production)
DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=disable

Best Practices

Use Separate Databases

Use different databases for development, staging, and production

Regular Backups

Set up automated backups for production databases

Monitor Performance

Track query performance and optimize slow queries

Connection Limits

Configure appropriate connection pool limits

Schema Changes

When modifying the Prisma schema:

Edit prisma/schema.prisma

Create a migration:

pnpm prisma migrate dev --name descriptive_name

Review the generated SQL in prisma/migrations/
Test the migration in development
Apply to production:
```
pnpm prisma migrate deploy
```

Always test schema changes in development before applying to production. Some migrations may cause data loss.

Next Steps

Installation

Complete the installation process

Environment Variables

Configure API keys and settings

API Reference

Learn about the API endpoints

Contributing

Contribute to Repolyze

Setup

Contributing

​Database Overview

User Authentication

Subscriptions

Rate Limiting

Audit Logs

​Prerequisites

​PostgreSQL Installation

​Database Setup

​Database Schema

​User Model

​Account Model

​Session Model

​AnalysisRequest Model

​Prisma Commands

​Prisma Studio

​Connection Pooling

​Using Prisma with pg Adapter

​Environment-Specific Configuration

​Development

​Production

​Troubleshooting

​Best Practices

Use Separate Databases

Regular Backups

Monitor Performance

Connection Limits

​Schema Changes

​Next Steps

Installation

Environment Variables

API Reference

Contributing

Build docs developers (and LLMs) love

Database Overview

Prerequisites

PostgreSQL Installation

Database Setup

Database Schema

User Model

Account Model

Session Model

AnalysisRequest Model

Prisma Commands

Prisma Studio

Connection Pooling

Using Prisma with pg Adapter

Environment-Specific Configuration

Development

Production

Troubleshooting

Best Practices

Schema Changes

Next Steps