Database Setup

Overview

Better Uptime uses PostgreSQL as its primary database and Prisma as the ORM. This guide covers database setup, schema overview, and running migrations.

Prerequisites

PostgreSQL 12 or higher installed
Database user with CREATE DATABASE privileges
Node.js or Bun installed for running Prisma commands

Database Configuration

PostgreSQL Setup

Install PostgreSQL

Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

macOS (Homebrew):

brew install postgresql@15
brew services start postgresql@15

Docker:

docker run --name better-uptime-postgres \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=better_uptime \
  -p 5432:5432 \
  -d postgres:15-alpine

Create Database

Connect to PostgreSQL:

psql -U postgres

Create database and user:

CREATE DATABASE better_uptime;
CREATE USER better_uptime_user WITH PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE better_uptime TO better_uptime_user;

Set Connection String

Add to your .env file:

DATABASE_URL=postgresql://better_uptime_user:your_secure_password@localhost:5432/better_uptime

Format: postgresql://[user]:[password]@[host]:[port]/[database]

Connection String Examples

DATABASE_URL=postgresql://postgres:password@localhost:5432/better_uptime

Prisma Schema Overview

Better Uptime uses Prisma for database management. The schema is located at packages/store/prisma/schema.prisma.

Core Models

User Model

Stores user account information:

model User {
  id            String       @id @default(cuid())
  email         String       @unique
  passwordHash  String?
  emailVerified Boolean      @default(false)
  name          String?
  avatarUrl     String?
  isActive      Boolean      @default(true)
  accounts      Account[]
  createdAt     DateTime     @default(now())
  updatedAt     DateTime     @updatedAt
  website       Website[]
  statusPages   StatusPage[]
}

Fields:

id: Unique user identifier (CUID)
email: Unique email address
passwordHash: Hashed password (optional for OAuth users)
emailVerified: Email verification status
name: Display name
avatarUrl: Profile picture URL
isActive: Account active status

Account Model

Stores OAuth provider accounts:

model Account {
  id                String  @id @default(cuid())
  userId            String
  provider          String
  providerAccountId String
  accessToken       String?
  refreshToken      String?
  expiresAt         Int?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

Purpose: Links users to OAuth providers (GitHub)

Website Model

Stores monitored websites:

model Website {
  id                 String              @id @default(cuid())
  url                String
  name               String?
  isActive           Boolean             @default(true)
  userId             String
  user               User                @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt          DateTime            @default(now())
  updatedAt          DateTime            @updatedAt
  statusPageMonitors StatusPageMonitor[]
}

Fields:

url: Website URL to monitor
name: Optional friendly name
isActive: Monitoring enabled/disabled
userId: Owner of the monitor

StatusPage Model

Stores public status pages:

model StatusPage {
  id          String              @id @default(cuid())
  name        String
  slug        String              @unique
  isPublished Boolean             @default(true)
  userId      String
  user        User                @relation(fields: [userId], references: [id], onDelete: Cascade)
  monitors    StatusPageMonitor[]
  domain      StatusPageDomain?
  createdAt   DateTime            @default(now())
  updatedAt   DateTime            @updatedAt
}

Fields:

name: Status page name
slug: Unique URL slug
isPublished: Public visibility
domain: Custom domain configuration

StatusPageMonitor Model

Links websites to status pages:

model StatusPageMonitor {
  statusPageId String
  websiteId    String
  createdAt    DateTime   @default(now())
  statusPage   StatusPage @relation(fields: [statusPageId], references: [id], onDelete: Cascade)
  website      Website    @relation(fields: [websiteId], references: [id], onDelete: Cascade)

  @@id([statusPageId, websiteId])
}

Purpose: Many-to-many relationship between status pages and monitors

StatusPageDomain Model

Stores custom domain configuration:

model StatusPageDomain {
  id                 String                         @id @default(cuid())
  statusPageId       String                         @unique
  hostname           String                         @unique
  verificationToken  String
  verificationStatus StatusDomainVerificationStatus @default(PENDING)
  verifiedAt         DateTime?
  createdAt          DateTime                       @default(now())
  updatedAt          DateTime                       @updatedAt
  statusPage         StatusPage                     @relation(fields: [statusPageId], references: [id], onDelete: Cascade)
}

Purpose: Custom domain verification for status pages

EmailVerificationToken Model

Stores email verification tokens:

model EmailVerificationToken {
  id        String   @id @default(cuid())
  token     String   @unique
  email     String
  expiresAt DateTime
  createdAt DateTime @default(now())
}

Purpose: Email verification during user registration

Enums

UptimeStatus

enum

Status of uptime checksValues:

UP: Website is responding
DOWN: Website is not responding

StatusDomainVerificationStatus

enum

Custom domain verification statusValues:

PENDING: Verification in progress
VERIFIED: Domain verified successfully
FAILED: Verification failed

Running Migrations

Initial Setup

Navigate to store package

cd packages/store

Generate Prisma Client

npx prisma generate

Or with Bun:

bun prisma generate

This creates the Prisma Client in packages/store/generated/prisma/.

Run migrations

Development (create and apply):

npx prisma migrate dev

Production (apply existing):

npx prisma migrate deploy

Verify database

npx prisma studio

Opens Prisma Studio at http://localhost:5555 to view and edit data.

Common Prisma Commands

# Generate Prisma Client
npx prisma generate

# Create a new migration
npx prisma migrate dev --name description_of_changes

# Apply migrations to production
npx prisma migrate deploy

# Reset database (⚠️ deletes all data)
npx prisma migrate reset

# View database in browser
npx prisma studio

# Validate schema
npx prisma validate

# Format schema file
npx prisma format

# View migration status
npx prisma migrate status

Database Indexes

The schema includes optimized indexes for common queries:

@@index([email])

Indexes improve query performance but use additional disk space. These indexes are optimized for Better Uptime’s query patterns.

Database Relationships

Key Relationships:

Users can have multiple OAuth accounts
Users can create multiple websites and status pages
Status pages can contain multiple monitors
Monitors link websites to status pages (many-to-many)
Status pages can have one custom domain

Backup and Restore

Backup Database

# Backup to file
pg_dump -U postgres -d better_uptime -F c -f backup_$(date +%Y%m%d).dump

# Or plain SQL format
pg_dump -U postgres -d better_uptime > backup_$(date +%Y%m%d).sql

Restore Database

# Restore from custom format
pg_restore -U postgres -d better_uptime -c backup.dump

# Restore from SQL format
psql -U postgres -d better_uptime < backup.sql

Automated Backups

Create a cron job for daily backups:

# Edit crontab
crontab -e

# Add daily backup at 2 AM
0 2 * * * pg_dump -U postgres -d better_uptime -F c -f /backups/better_uptime_$(date +\%Y\%m\%d).dump

Troubleshooting

Connection Refused

Error: Error: connect ECONNREFUSED 127.0.0.1:5432Solutions:

Verify PostgreSQL is running:
```
sudo systemctl status postgresql
```
Check PostgreSQL is listening:
```
sudo netstat -tulpn | grep 5432
```
Verify connection string in .env

Authentication Failed

Error: Error: password authentication failed for userSolutions:

Verify username and password in DATABASE_URL
Check PostgreSQL authentication method in pg_hba.conf:
```
sudo nano /etc/postgresql/15/main/pg_hba.conf
```
Change authentication to md5 or scram-sha-256
Restart PostgreSQL:
```
sudo systemctl restart postgresql
```

Migration Failures

Error: Migration failed to applySolutions:

Check migration status:
```
npx prisma migrate status
```

Resolve failed migration:

npx prisma migrate resolve --applied migration_name

If all else fails, reset (⚠️ deletes all data):
```
npx prisma migrate reset
```

SSL Required

Error: no pg_hba.conf entry for host, SSL offSolution: Add ?sslmode=require to connection string:

DATABASE_URL=postgresql://user:password@host:5432/database?sslmode=require

Production Best Practices

Connection Pooling

Use connection pooling for better performance:

# PgBouncer connection string
DATABASE_URL=postgresql://user:password@localhost:6432/better_uptime?pgbouncer=true

Or use Prisma’s connection pool settings:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  connection_limit = 10
}

Regular Backups

Automate daily backups
Store backups off-site
Test restore procedures regularly
Keep at least 30 days of backups

Monitoring

Monitor database health:

Query performance
Connection count
Disk usage
Replication lag (if using replicas)

Security

Use strong passwords
Enable SSL/TLS connections
Restrict network access
Keep PostgreSQL updated
Use read-only replicas for reporting

Next Steps

Development Setup

Set up local development environment

Production Deployment

Deploy to production

Environment Variables

Configure all environment variables

Get Started

Core Features

Deployment

Architecture

Overview

Prerequisites

Database Configuration

PostgreSQL Setup

Connection String Examples

Prisma Schema Overview

Core Models

Enums

Running Migrations

Initial Setup

Common Prisma Commands

Database Indexes

Database Relationships

Backup and Restore

Backup Database

Restore Database

Automated Backups

Troubleshooting

Production Best Practices

Next Steps

Development Setup

Production Deployment

Environment Variables

Build docs developers (and LLMs) love

Get Started

Core Features

Deployment

Architecture

​Overview

​Prerequisites

​Database Configuration

​PostgreSQL Setup

​Connection String Examples

​Prisma Schema Overview

​Core Models

​Enums

​Running Migrations

​Initial Setup

​Common Prisma Commands

​Database Indexes

​Database Relationships

​Backup and Restore

​Backup Database

​Restore Database

​Automated Backups

​Troubleshooting

​Production Best Practices

​Next Steps

Development Setup

Production Deployment

Environment Variables

Build docs developers (and LLMs) love

Overview

Prerequisites

Database Configuration

PostgreSQL Setup

Connection String Examples

Prisma Schema Overview

Core Models

Enums

Running Migrations

Initial Setup

Common Prisma Commands

Database Indexes

Database Relationships

Backup and Restore

Backup Database

Restore Database

Automated Backups

Troubleshooting

Production Best Practices

Next Steps