Database Setup

This guide covers database setup for both local PostgreSQL (development) and CockroachDB (production) environments.

Database Providers

Environment	Database	Purpose
Local Development	PostgreSQL 18	Fast iteration, offline work
Production	CockroachDB Serverless	Auto-scaling, automatic backups, PITR

CockroachDB is PostgreSQL-compatible, so the same Prisma schema works for both environments.

Local Development Setup

1. Install PostgreSQL 18

Ubuntu/Debian
macOS
Windows

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc

# Install PostgreSQL 18
sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Using Homebrew
brew install postgresql@18

# Start service
brew services start postgresql@18

# Or use Postgres.app (GUI)
# Download from https://postgresapp.com

Download PostgreSQL 18 installer from postgresql.org
Run the installer
Note the password you set for the postgres user
Ensure PostgreSQL service is running

2. Create Local Database

# Connect to PostgreSQL as superuser
psql -U postgres

# Create database
CREATE DATABASE millenium_local;

# Exit psql
\q

If you get a password prompt, use the password you set during PostgreSQL installation.

3. Configure Connection String

Update backend/.env.local:

DATABASE_URL="postgresql://postgres:YOUR_PASSWORD@localhost:5432/millenium_local?schema=public"
DIRECT_URL="postgresql://postgres:YOUR_PASSWORD@localhost:5432/millenium_local?schema=public"

Replace YOUR_PASSWORD with your PostgreSQL password.

4. Initialize Database Schema

Navigate to backend directory

cd backend

Generate Prisma Client

npx prisma generate

This creates the TypeScript client from your schema.

Push schema to database

npx prisma db push

db push is only for development. It synchronizes your schema without migrations.

Expected output:

🚀  Your database is now in sync with your Prisma schema.
✔ Generated Prisma Client

Verify with Prisma Studio (optional)

npx prisma studio

Opens a GUI at http://localhost:5555 to browse your database.

Production Setup (CockroachDB)

1. Create CockroachDB Cluster

Go to cockroachlabs.cloud and create an account.

Create a Serverless cluster

Click “Create Cluster”
Select Serverless tier (free tier available)
Choose a region close to your users
Name your cluster (e.g., millennium-potters-prod)

Create SQL user

Go to “SQL Users” tab
Click “Add User”
Set username and generate a strong password
Save the password - it won’t be shown again

Get connection string

Go to “Connect” tab
Select “General connection string”
Copy the connection string
It will look like:

postgresql://user:[email protected]:26257/defaultdb?sslmode=verify-full

2. Configure Production Environment

Create backend/.env.production:

# Connection string with pooling for application use
DATABASE_URL="postgresql://user:[email protected]:26257/defaultdb?sslmode=verify-full&connection_limit=20&pool_timeout=20"

# Direct connection for migrations (no pooling)
DIRECT_URL="postgresql://user:[email protected]:26257/defaultdb?sslmode=verify-full"

connection_limit

number

default:"20"

Maximum number of connections in the pool. Set to 20-30 for 50-100 concurrent users.

pool_timeout

number

default:"20"

Connection acquisition timeout in seconds.

3. Apply Schema to Production

NEVER use prisma db push in production. It can cause data loss. Always use migrations.

Generate migration plan

npx prisma migrate diff \
  --from-url "$DIRECT_URL" \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migration_plan.sql

This generates a SQL script showing exactly what will change.

Review migration script

Open migration_plan.sql and verify:

✅ CREATE TABLE statements (safe)
✅ ALTER TABLE ... ADD COLUMN (safe)
⚠️ ALTER TABLE ... DROP COLUMN (review carefully)
❌ DROP TABLE (investigate immediately)

Apply migration

npx prisma db execute \
  --url "$DIRECT_URL" \
  --file migration_plan.sql

Verify schema

npx prisma db pull --url "$DIRECT_URL"

Confirms your Prisma schema matches the database.

Prisma Commands Reference

Development Commands

# Generate Prisma Client (run after schema changes)
npx prisma generate

# Sync schema to database (dev only)
npx prisma db push

# Open database GUI
npx prisma studio

# Pull schema from database (reverse engineering)
npx prisma db pull

Production Commands

# Generate migration SQL (safe preview)
npx prisma migrate diff \
  --from-url "$DATABASE_URL" \
  --to-schema-datamodel prisma/schema.prisma \
  --script

# Apply migration from file
npx prisma db execute --url "$DATABASE_URL" --file migration.sql

# Deploy pending migrations (if using migrate dev)
npx prisma migrate deploy

All production commands should use DIRECT_URL (without connection pooling) to avoid migration issues.

Database Schema Overview

The Millennium Potters schema includes:

Core Models

User - Staff accounts (Admin, Supervisor, Credit Officer)
Union - Groups of members managed by a Credit Officer
UnionMember - Individual borrowers
LoanType - Loan product definitions
Loan - Loan applications and records

Financial Models

RepaymentScheduleItem - Payment due dates
Repayment - Actual payments received
RepaymentAllocation - Links payments to schedule items

Supporting Models

DocumentType - Document categories
UnionMemberDocument - Member identity documents
LoanDocument - Loan collateral documents
BackupRecord - Backup metadata
AuditLog - Activity tracking
CompanySetting - System configuration

Key Schema Features

Soft Deletes: All models use deletedAt instead of hard deletes
Audit Trail: Full history via AuditLog and reassignment tables
Connection Pooling: Configured in datasource block
Enums: Strongly typed status fields

Connection String Format

PostgreSQL (Local)

postgresql://username:password@host:port/database?schema=public

Example:

postgresql://postgres:mypassword@localhost:5432/millenium_local?schema=public

CockroachDB (Production)

postgresql://username:password@host:port/database?sslmode=verify-full&connection_limit=20&pool_timeout=20

Example:

postgresql://millennium:[email protected]:26257/defaultdb?sslmode=verify-full&connection_limit=25&pool_timeout=20

sslmode

string

required

Always use verify-full for production CockroachDB connections.

Schema Migration Process

For New Features

Update Prisma schema

Edit backend/prisma/schema.prisma to add new models or fields.

Test locally

npx prisma db push

Test your changes in development.

Generate migration SQL

npx prisma migrate diff \
  --from-url "$PRODUCTION_URL" \
  --to-schema-datamodel prisma/schema.prisma \
  --script > migration_$(date +%Y%m%d).sql

Review and apply

Review the migration SQL
Test on a staging database if available
Apply to production during maintenance window
Verify with health checks

Backup and Recovery

Automated Backups (CockroachDB)

CockroachDB Serverless includes:

Automatic hourly backups (last 30 days)
Point-in-Time Recovery (PITR) to any point in the last 30 days
No configuration required

Manual Backups

PostgreSQL (Local)
CockroachDB (Production)

# Backup entire database
pg_dump -U postgres millenium_local > backup_$(date +%Y%m%d).sql

# Restore from backup
psql -U postgres millenium_local < backup_20260311.sql

# Export using pg_dump (PostgreSQL-compatible)
pg_dump "$DATABASE_URL" --no-owner --no-acl > prod_backup_$(date +%Y%m%d_%H%M%S).sql

# Restore
psql "$DATABASE_URL" < prod_backup_20260311_120000.sql

The backup file is PostgreSQL-compatible and works directly with CockroachDB.

Application-Level Backups

The system includes a built-in backup feature:

Location: Cloudinary (cloud storage)
Format: JSON export of all tables
Trigger: Manual via Settings → Backup or scheduled
Retention: Configurable (default 30 days)

See Production Deployment for details.

Troubleshooting

Connection refused errors

Symptoms: Error: connect ECONNREFUSED 127.0.0.1:5432Solutions:

Check PostgreSQL is running: sudo systemctl status postgresql
Start PostgreSQL: sudo systemctl start postgresql
Verify port 5432 is open: sudo netstat -plnt | grep 5432

Authentication failed

Symptoms: password authentication failed for user "postgres"Solutions:

Verify password in connection string

Reset PostgreSQL password:

sudo -u postgres psql
ALTER USER postgres PASSWORD 'newpassword';

Database does not exist

Symptoms: database "millenium_local" does not existSolutions:

psql -U postgres -c "CREATE DATABASE millenium_local;"

Prisma schema out of sync

Symptoms: The schema is not in sync with the databaseSolutions:

In development: npx prisma db push
In production: Generate and apply migration
Emergency: npx prisma db pull (overwrites schema with database state)

CockroachDB connection timeout

Symptoms: Connection hangs or times outSolutions:

Check SSL mode: must be verify-full
Verify cluster is running in CockroachDB dashboard
Check firewall allows outbound connections on port 26257
Test connection: psql "$DATABASE_URL" -c "SELECT 1;"

Too many connections error

Symptoms: remaining connection slots are reservedSolutions:

Reduce connection_limit in DATABASE_URL
Check for connection leaks in application code
Restart application to close stale connections
Monitor connection count in CockroachDB dashboard

Performance Tuning

Connection Pool Sizing

# For 50-100 concurrent users
DATABASE_URL="...?connection_limit=20&pool_timeout=20"

# For 100-200 concurrent users
DATABASE_URL="...?connection_limit=30&pool_timeout=30"

Formula: connection_limit = (expected_concurrent_users * 0.2) + 10

Index Optimization

The schema includes indexes on:

Primary keys (automatic)
Foreign keys (automatic)
Frequently queried fields (email, loanNumber, deletedAt)
Composite indexes for complex queries

No additional indexing needed for typical workloads.

Next Steps

Production Deployment

Deploy the application to production

Environment Variables

Review environment configuration

Setup

Operations

Database Providers

Local Development Setup

1. Install PostgreSQL 18

2. Create Local Database

3. Configure Connection String

4. Initialize Database Schema

Production Setup (CockroachDB)

1. Create CockroachDB Cluster

2. Configure Production Environment

3. Apply Schema to Production

Prisma Commands Reference

Development Commands

Production Commands

Database Schema Overview

Core Models

Financial Models

Supporting Models

Key Schema Features

Connection String Format

PostgreSQL (Local)

CockroachDB (Production)

Schema Migration Process

For New Features

Backup and Recovery

Automated Backups (CockroachDB)

Manual Backups

Application-Level Backups

Troubleshooting

Performance Tuning

Connection Pool Sizing

Index Optimization

Next Steps

Production Deployment

Environment Variables

Build docs developers (and LLMs) love

Setup

Operations

​Database Providers

​Local Development Setup

​1. Install PostgreSQL 18

​2. Create Local Database

​3. Configure Connection String

​4. Initialize Database Schema

​Production Setup (CockroachDB)

​1. Create CockroachDB Cluster

​2. Configure Production Environment

​3. Apply Schema to Production

​Prisma Commands Reference

​Development Commands

​Production Commands

​Database Schema Overview

​Core Models

​Financial Models

​Supporting Models

​Key Schema Features

​Connection String Format

​PostgreSQL (Local)

​CockroachDB (Production)

​Schema Migration Process

​For New Features

​Backup and Recovery

​Automated Backups (CockroachDB)

​Manual Backups

​Application-Level Backups

​Troubleshooting

​Performance Tuning

​Connection Pool Sizing

​Index Optimization

​Next Steps

Production Deployment

Environment Variables

Build docs developers (and LLMs) love

Database Providers

Local Development Setup

1. Install PostgreSQL 18

2. Create Local Database

3. Configure Connection String

4. Initialize Database Schema

Production Setup (CockroachDB)

1. Create CockroachDB Cluster

2. Configure Production Environment

3. Apply Schema to Production

Prisma Commands Reference

Development Commands

Production Commands

Database Schema Overview

Core Models

Financial Models

Supporting Models

Key Schema Features

Connection String Format

PostgreSQL (Local)

CockroachDB (Production)

Schema Migration Process

For New Features

Backup and Recovery

Automated Backups (CockroachDB)

Manual Backups

Application-Level Backups

Troubleshooting

Performance Tuning

Connection Pool Sizing

Index Optimization

Next Steps