Database Configuration

Better Hub uses PostgreSQL with Prisma ORM for data persistence. This guide covers database setup, schema management, and migrations.

PostgreSQL Setup

Option 1: Docker Compose (Development)

The easiest way to run PostgreSQL locally:

Start PostgreSQL with Docker Compose

docker-compose up -d postgres

This starts PostgreSQL on port 54320 to avoid conflicts with system PostgreSQL.

Configure DATABASE_URL

Add to your .env file:

DATABASE_URL=postgresql://postgres:postgres@localhost:54320/better_hub

Run migrations

cd apps/web
npx prisma migrate dev

Option 2: Managed PostgreSQL (Production)

For production, use a managed PostgreSQL service:

# Get connection string from Neon dashboard
DATABASE_URL=postgresql://user:password@ep-xxx-xxx.us-east-2.aws.neon.tech/neondb?sslmode=require

Always use SSL connections (sslmode=require) for production databases.

Option 3: Local PostgreSQL Installation

If you have PostgreSQL installed locally:

Create database

createdb better_hub

Configure connection

DATABASE_URL=postgresql://localhost/better_hub

Database Schema

Better Hub’s database schema is defined in apps/web/prisma/schema.prisma.

Core Models

User

model

User accounts with GitHub authenticationKey fields:

id: Unique user identifier
email: User email address
githubPat: Optional GitHub Personal Access Token
onboardingDone: Onboarding completion status
stripeCustomerId: Stripe customer reference

Session

model

Active user sessionsKey fields:

token: Session token (unique)
expiresAt: Expiration timestamp
ipAddress: Client IP address
userAgent: Client user agent

Account

model

OAuth provider accounts (GitHub)Key fields:

providerId: OAuth provider (“github”)
accessToken: Encrypted OAuth access token
refreshToken: Encrypted refresh token
scope: Granted OAuth scopes

Feature Models

ChatConversation

model

Ghost AI chat conversationsRelations:

Has many ChatMessage records
Unique per userId + contextKey

GithubCacheEntry

model

Cached GitHub API responsesKey fields:

cacheKey: Cache identifier
cacheType: Type of cached data
dataJson: Serialized response data
etag: GitHub ETag for conditional requests

UsageLog

model

AI usage tracking for billingKey fields:

taskType: Type of AI operation
costUsd: Cost in USD
creditUsed: Credits consumed
stripeReported: Whether usage was reported to Stripe

SearchEmbedding

model

Vector embeddings for semantic searchKey fields:

contentType: Type of content (issue, PR, discussion)
embeddingJson: Vector embedding data
snippet: Text snippet for display

Full Schema Reference

View the complete schema at apps/web/prisma/schema.prisma.

Prisma Configuration

Client Generation

Prisma Client is generated to a custom location:

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

This is automatically generated on:

npm install (via postinstall script)
npm run build
Manual: npx prisma generate

Import Prisma Client

Use the centralized Prisma instance:

import { prisma } from "@/lib/db";

// Query example
const users = await prisma.user.findMany();

Always import from @/lib/db, not directly from generated Prisma client. This ensures proper connection pooling.

Connection Pooling

For serverless environments (Vercel, AWS Lambda), use Prisma’s connection pooling:

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

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

See implementation in apps/web/src/lib/db.ts.

Database Migrations

Development Workflow

Modify schema

Edit apps/web/prisma/schema.prisma to make schema changes.

Create migration

cd apps/web
npx prisma migrate dev --name describe_your_changes

This:

Creates a new migration file
Applies it to your database
Regenerates Prisma Client

Commit migration

git add prisma/migrations
git commit -m "Add migration: describe_your_changes"

Production Deployment

Apply migrations

Run migrations before deploying new code:

npx prisma migrate deploy

This applies all pending migrations without interactive prompts.

Deploy application

Deploy your application code after migrations succeed.

Never use prisma migrate dev in production. Always use prisma migrate deploy.

Migration Best Practices

Test migrations locally before deploying
Backup production database before applying migrations
Make backwards-compatible changes when possible:
- Add new optional fields instead of required ones
- Add new tables instead of modifying existing ones
- Use multi-step migrations for breaking changes
Review generated SQL in migration files before applying

Database Operations

Reset Database (Development)

Wipe database and reapply all migrations:

npx prisma migrate reset

This deletes all data. Only use in development.

View Data with Prisma Studio

Open a visual database browser:

npx prisma studio

Access at http://localhost:5555.

Seed Database

Create initial data for development:

npx prisma db seed

Configure seed script in package.json:

{
  "prisma": {
    "seed": "node --experimental-strip-types prisma/seed.ts"
  }
}

Indexing Strategy

Better Hub uses indexes for query performance:

User Queries

@@index([githubPat, email])

Optimizes looking up users by GitHub PAT or email.

Session Queries

@@index([userId, expiresAt])

Fast session validation and cleanup of expired sessions.

GitHub Cache

@@index([userId, cacheType])

Quick retrieval of cached GitHub data by type.

Usage Tracking

@@index([userId, createdAt])
@@index([stripeReported, createdAt])

Efficient billing queries and Stripe sync. See full index definitions in apps/web/prisma/schema.prisma.

Performance Optimization

Query Optimization

Use select to fetch only needed fields:

const user = await prisma.user.findUnique({
  where: { id: userId },
  select: { id: true, name: true, email: true }
});

Use include for relations:

const conversation = await prisma.chatConversation.findUnique({
  where: { id: conversationId },
  include: { messages: true }
});

Connection Pooling

For serverless environments, limit connection pool size:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10, // Maximum connections
});

Pagination

Use cursor-based pagination for large datasets:

const messages = await prisma.chatMessage.findMany({
  where: { conversationId },
  take: 50,
  cursor: { id: lastMessageId },
  orderBy: { createdAt: 'desc' }
});

Backup and Recovery

Automated Backups

Most managed PostgreSQL services provide automated backups:

Neon: Automatic point-in-time recovery
Supabase: Daily automated backups
Railway: Automatic snapshots

Manual Backup

Create a manual backup:

pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

Restore from Backup

psql $DATABASE_URL < backup_20260303.sql

Test your backup and recovery process regularly.

Troubleshooting

Connection Refused

Error: Can't reach database server Solutions:

Verify database is running: docker ps (Docker) or check service status
Check connection string format
Verify firewall rules allow connections

Migration Failed

Error: Migration fails midway Solutions:

Check migration SQL for syntax errors
Verify database permissions

Manually fix database state and mark migration as applied:

npx prisma migrate resolve --applied migration_name

Schema Out of Sync

Error: Prisma schema is out of sync with database Solutions:

Development: npx prisma migrate dev
Production: npx prisma migrate deploy
Force sync (development only): npx prisma db push

Too Many Connections

Error: too many connections Solutions:

Reduce connection pool size
Use connection pooler (PgBouncer)
Check for connection leaks in code

Security

Connection Security

Use SSL/TLS

Always use encrypted connections in production:

DATABASE_URL=postgresql://...?sslmode=require

Rotate credentials

Regularly rotate database passwords and connection strings.

Limit database access

Restrict database access to application servers only via firewall rules.

Data Encryption

Sensitive fields are encrypted at the application level:

OAuth tokens (via Better Auth)
GitHub Personal Access Tokens
API keys

Encryption uses BETTER_AUTH_SECRET as the key.

Get Started

Core Features

Configuration

Browser Extension

Development

Documentation Index

​PostgreSQL Setup

​Option 1: Docker Compose (Development)

​Option 2: Managed PostgreSQL (Production)

​Option 3: Local PostgreSQL Installation

​Database Schema

​Core Models

​Feature Models

​Full Schema Reference

​Prisma Configuration

​Client Generation

​Import Prisma Client

​Connection Pooling

​Database Migrations

​Development Workflow

​Production Deployment

​Migration Best Practices

​Database Operations

​Reset Database (Development)

​View Data with Prisma Studio

​Seed Database

​Indexing Strategy

​User Queries

​Session Queries

​GitHub Cache

​Usage Tracking

​Performance Optimization

​Query Optimization

​Connection Pooling

​Pagination

​Backup and Recovery

​Automated Backups

​Manual Backup

​Restore from Backup

​Troubleshooting

​Connection Refused

​Migration Failed

​Schema Out of Sync

​Too Many Connections

​Security

​Connection Security

​Data Encryption

Build docs developers (and LLMs) love

PostgreSQL Setup

Option 1: Docker Compose (Development)

Option 2: Managed PostgreSQL (Production)

Option 3: Local PostgreSQL Installation

Database Schema

Core Models

Feature Models

Full Schema Reference

Prisma Configuration

Client Generation

Import Prisma Client

Connection Pooling

Database Migrations

Development Workflow

Production Deployment

Migration Best Practices

Database Operations

Reset Database (Development)

View Data with Prisma Studio

Seed Database

Indexing Strategy

User Queries

Session Queries

GitHub Cache

Usage Tracking

Performance Optimization

Query Optimization

Connection Pooling

Pagination

Backup and Recovery

Automated Backups

Manual Backup

Restore from Backup

Troubleshooting

Connection Refused

Migration Failed

Schema Out of Sync

Too Many Connections

Security

Connection Security

Data Encryption