Database Setup

Overview

Bounty uses PostgreSQL as its primary database with Drizzle ORM for type-safe database operations and schema management. This guide covers setting up your database, running migrations, and managing your data.

Database Requirements

Version

PostgreSQL 14 or higher

Storage

Minimum 1GB for initial deployment

SSL/TLS

Required for production deployments

Extensions

No special extensions required

Choosing a Database Provider

Recommended: Neon

Neon provides serverless PostgreSQL with excellent features:

Serverless - Automatic scaling and pay-per-use pricing
Branching - Database branches for development and testing
Point-in-time Recovery - Restore to any point in time
Free Tier - Generous free tier for small projects

Quick Start:

# Get a temporary 72-hour database
open https://neon.new

Alternative Providers

Supabase

Supabase offers PostgreSQL with real-time capabilities:

Built-in authentication (if not using Better Auth)
Real-time subscriptions
Storage and edge functions
Good free tier

Connection String Format:

postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres

Railway

Railway provides simple PostgreSQL hosting:

One-click PostgreSQL deployment
Automatic backups
Simple pricing
Good for small to medium deployments

Setup:

Create new project
Add PostgreSQL service
Copy connection string from variables

Self-Hosted PostgreSQL

Run your own PostgreSQL instance:Docker:

docker run -d \
  --name bounty-postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=bounty \
  -p 5432:5432 \
  -v bounty-data:/var/lib/postgresql/data \
  postgres:16-alpine

Connection String:

postgresql://postgres:yourpassword@localhost:5432/bounty

For production, configure SSL/TLS and secure your PostgreSQL instance properly.

Database Configuration

Connection String Format

Bounty requires a PostgreSQL connection string in the DATABASE_URL environment variable:

DATABASE_URL="postgresql://username:password@host:port/database?sslmode=require"

Components:

username - Database user (e.g., postgres)
password - User password (URL-encode special characters)
host - Database server hostname
port - PostgreSQL port (default: 5432)
database - Database name
sslmode=require - Enforce SSL connection (required for production)

Special Characters in CredentialsURL-encode special characters in username or password:

@ becomes %40
# becomes %23
! becomes %21
(space) becomes %20

Example: p@ssw0rd! → p%40ssw0rd%21

Connection Pool Configuration

Bounty uses node-postgres (pg) with connection pooling. The configuration is in packages/db/src/index.ts:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl:
    process.env.NODE_ENV === 'production'
      ? { rejectUnauthorized: false }
      : false,
});

Production Settings:

SSL is automatically enabled in production
Connection pooling is handled by pg.Pool
Drizzle ORM manages query execution

Drizzle ORM Setup

Configuration

Drizzle configuration is defined in packages/db/drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./src/migrations",
  schema: "./src/schema/index.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL || "",
  },
  tablesFilter: ["!pg_stat_statements", "!pg_stat_statements_info"],
});

Configuration Details:

out - Directory for migration files
schema - Path to schema definitions
dialect - Database type (PostgreSQL)
tablesFilter - Exclude system tables from schema introspection

Database Schema

The database schema is organized into multiple files in packages/db/src/schema/:

Core Schemas

auth.ts - User authentication (Better Auth tables)
profiles.ts - User profiles and settings
bounties.ts - Bounty listings and submissions
organization.ts - Organizations and memberships
github-installation.ts - GitHub App installations

Feature Schemas

payments.ts - Payment tracking and transactions
notifications.ts - User notifications
invites.ts - Organization invites
feature-votes.ts - Feature request voting
passkey.ts - Passkey authentication
linear-account.ts - Linear integration
beta-applications.ts - Early access applications

Running Migrations

Initial Setup

Set DATABASE_URL

export DATABASE_URL="postgresql://username:password@host/database?sslmode=require"

Push Schema to Database
```
bun db:push
```
This command:
- Reads your schema definitions
- Generates and executes SQL to create tables
- Updates existing tables if schema changed
- Does NOT create migration files

bun db:push is great for development but shouldn’t be used in production. Use migrations instead.

Production Migrations

For production deployments, use proper migrations:

Generate Migration

bun db:generate

This creates a new migration file in packages/db/src/migrations/ based on schema changes.

Review Migration

Check the generated SQL in packages/db/src/migrations/XXXX_*.sql:

CREATE TABLE "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "email" text NOT NULL UNIQUE,
  "created_at" timestamp DEFAULT now()
);

Run Migration

bun db:migrate

Applies pending migrations to your database.

Always review generated migrations before running them in production. Drizzle generates safe migrations, but complex schema changes may need manual adjustment.

Migration Workflow

Development:

# Make schema changes in packages/db/src/schema/
# Push directly to dev database
bun db:push

Production:

# 1. Make schema changes
# 2. Generate migration
bun db:generate

# 3. Review migration SQL
cat packages/db/src/migrations/0001_*.sql

# 4. Test on staging database
DATABASE_URL="...staging..." bun db:migrate

# 5. Apply to production
DATABASE_URL="...production..." bun db:migrate

Database Management Commands

All database commands are defined in package.json and packages/db/package.json:

Available Commands

# Push schema changes (development only)
bun db:push

# Open Drizzle Studio (database GUI)
bun db:studio

Drizzle Studio

Drizzle Studio provides a visual interface to browse and edit your database:

bun db:studio

This opens a web interface at https://local.drizzle.studio where you can:

Browse all tables and data
Edit records directly
Run custom queries
Visualize relationships

Drizzle Studio uses your DATABASE_URL environment variable. Make sure it’s set before running the command.

Database Backups

Manual Backup

Create a database dump:

bun db:dump

This runs the script in scripts/dump-db.sh to export your database.

pg_dump (Direct)

For more control, use pg_dump directly:

# Full database dump
pg_dump $DATABASE_URL > backup-$(date +%Y%m%d).sql

# Schema only
pg_dump --schema-only $DATABASE_URL > schema-backup.sql

# Data only
pg_dump --data-only $DATABASE_URL > data-backup.sql

Automated Backups

Most providers offer automated backups:

Neon - Automatic point-in-time recovery
Supabase - Daily automated backups
Railway - Snapshot backups on demand

Set up automated backups before deploying to production. Test your restore process regularly.

Database Monitoring

Performance Monitoring

Monitor these key metrics:

Connection count - Track active connections
Query performance - Identify slow queries
Table sizes - Monitor growth over time
Index usage - Ensure indexes are being used

Query Active Connections

SELECT 
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query
FROM pg_stat_activity
WHERE datname = 'your_database_name';

Check Table Sizes

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Troubleshooting

Connection Refused

Error: ECONNREFUSED or connection refusedSolutions:

Verify database is running
Check host and port are correct
Ensure firewall allows connections
Verify network connectivity

SSL/TLS Errors

Error: SSL connection required or certificate verify failedSolutions:

Add ?sslmode=require to connection string
For self-signed certs, use ?sslmode=require with rejectUnauthorized: false
Ensure your provider supports SSL

Authentication Failed

Error: password authentication failedSolutions:

Verify username and password are correct
URL-encode special characters in credentials
Check user has database access permissions
Ensure user exists in the database

Migration Errors

Error: Migration fails or schema mismatchSolutions:

Check existing schema doesn’t conflict
Review migration SQL for errors
Ensure no manual schema changes were made
Drop and recreate database for clean slate (dev only)

Too Many Connections

Error: sorry, too many clients alreadySolutions:

Reduce connection pool size
Upgrade database tier for more connections
Check for connection leaks in application
Use connection pooler like PgBouncer

Advanced Topics

Database Migrations in CI/CD

Run migrations automatically during deployment:

# .github/workflows/deploy.yml
- name: Run Database Migrations
  run: bun db:migrate
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

Multiple Environments

Use different databases for each environment:

# .env.development
DATABASE_URL="postgresql://...dev-database..."

# .env.staging  
DATABASE_URL="postgresql://...staging-database..."

# .env.production
DATABASE_URL="postgresql://...production-database..."

Database Seeding

Populate your database with test data:

// scripts/seed-database.ts
import { db } from '@bounty/db';

await db.insert(users).values([
  { email: '[email protected]', name: 'Test User' },
  // ... more seed data
]);

bun run scripts/seed-database.ts

Best Practices

Use Migrations

Always use migrations in production. Never use db:push for production deployments.

Test Migrations

Test migrations on staging before applying to production. Review generated SQL.

Backup First

Always backup before running migrations or making schema changes.

Monitor Performance

Set up monitoring for connection count, query performance, and table sizes.

Next Steps

Deployment

Deploy your Bounty instance to production with database configured

Get Started

Core Features

Integrations

Self-Hosting

​Overview

​Database Requirements

Version

Storage

SSL/TLS

Extensions

​Choosing a Database Provider

​Recommended: Neon

​Alternative Providers

​Database Configuration

​Connection String Format

​Connection Pool Configuration

​Drizzle ORM Setup

​Configuration

​Database Schema

​Running Migrations

​Initial Setup

​Production Migrations

​Generate Migration

​Review Migration

​Run Migration

​Migration Workflow

​Database Management Commands

​Available Commands

​Drizzle Studio

​Database Backups

​Manual Backup

​pg_dump (Direct)

​Automated Backups

​Database Monitoring

​Performance Monitoring

​Query Active Connections

​Check Table Sizes

​Troubleshooting

​Advanced Topics

​Database Migrations in CI/CD

​Multiple Environments

​Database Seeding

​Best Practices

Use Migrations

Test Migrations

Backup First

Monitor Performance

​Next Steps

Deployment

Build docs developers (and LLMs) love

Overview

Database Requirements

Choosing a Database Provider

Recommended: Neon

Alternative Providers

Database Configuration

Connection String Format

Connection Pool Configuration

Drizzle ORM Setup

Configuration

Database Schema

Running Migrations

Initial Setup

Production Migrations

Generate Migration

Review Migration

Run Migration

Migration Workflow

Database Management Commands

Available Commands

Drizzle Studio

Database Backups

Manual Backup

pg_dump (Direct)

Automated Backups

Database Monitoring

Performance Monitoring

Query Active Connections

Check Table Sizes

Troubleshooting

Advanced Topics

Database Migrations in CI/CD

Multiple Environments

Database Seeding

Best Practices

Next Steps