Production Deployment Guide

Guide for deploying SelfHostedDB in production environments

This guide covers production deployment best practices, scaling considerations, and operational requirements.

Prerequisites

System Requirements

Minimum:

CPU: 1 vCPU
RAM: 512 MB
Disk: 1 GB (for application)
Network: Port 3001 accessible

Recommended:

CPU: 2 vCPU
RAM: 2 GB
Disk: 5 GB (for application + logs)
Network: HTTPS enabled (port 443)

Software Requirements

Docker 20.10+ (or compatible container runtime)
PostgreSQL 12+ (can be on same or separate server)
Reverse proxy (nginx, Traefik, or cloud load balancer) for HTTPS

Environment Configuration

Required Environment Variables

Create a .env file or set these in your deployment platform:

# Database Connection (REQUIRED)
DATABASE_URL=postgres://username:password@host:5432/database?sslmode=require
 
# Server Configuration
PORT=3001
NODE_ENV=production
 
# Authentication (REQUIRED - Change these!)
AUTH_USER=your_admin_username
AUTH_PASS=your_strong_password_here
 
# License Server (REQUIRED)
LICENSE_SERVER_URL=https://license.yourdomain.com
 
# License Encryption (RECOMMENDED for production)
LICENSE_ENCRYPTION_KEY=your-generated-encryption-key-here

Security Checklist

AUTH_USER and AUTH_PASS are strong credentials (16+ characters)
DATABASE_URL uses SSL/TLS (?sslmode=require)
Database credentials are stored securely (secrets manager, not in code)
.env file is excluded from Docker image (use runtime environment variables)
Firewall rules restrict database access to app container only
LICENSE_SERVER_URL uses HTTPS in production
LICENSE_ENCRYPTION_KEY is set and stored securely (secrets manager)
License server is accessible from application server

Deployment Options

Option 1: Docker Compose (Single Server)

Best for: Small deployments, single server setups

# docker-compose.prod.yml
version: '3.8'
 
services:
  app:
    image: your-registry/selfhosteddb:latest
    container_name: selfhosteddb-prod
    restart: unless-stopped
    ports:
      - "3001:3001"
    environment:
      DATABASE_URL: ${DATABASE_URL}
      AUTH_USER: ${AUTH_USER}
      AUTH_PASS: ${AUTH_PASS}
      LICENSE_SERVER_URL: ${LICENSE_SERVER_URL}
      LICENSE_ENCRYPTION_KEY: ${LICENSE_ENCRYPTION_KEY}
      NODE_ENV: production
      PORT: 3001
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3001/api/health"]
      interval: 30s
      timeout: 10s
      retries: 3
      start_period: 40s
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

Deploy:

docker-compose -f docker-compose.prod.yml up -d

Option 2: Docker Run (Manual)

Best for: Quick deployments, custom configurations

docker run -d \
  --name selfhosteddb-prod \
  --restart unless-stopped \
  -p 3001:3001 \
  -e DATABASE_URL="postgres://user:pass@host:5432/db?sslmode=require" \
  -e AUTH_USER="admin" \
  -e AUTH_PASS="your-strong-password" \
  -e LICENSE_SERVER_URL="https://license.yourdomain.com" \
  -e LICENSE_ENCRYPTION_KEY="your-encryption-key" \
  -e NODE_ENV="production" \
  -e PORT="3001" \
  your-registry/selfhosteddb:latest

Option 3: Kubernetes

Best for: Enterprise deployments, high availability

See Kubernetes Deployment section below.

Option 4: Cloud Platforms

For platform-specific deployment guides, see:

AWS Deployment - EC2, ECS, EKS
Azure Deployment - Container Instances, App Service, AKS
Google Cloud Platform - Cloud Run, GKE
DigitalOcean - Droplets, App Platform
Oracle Cloud - Container Instances, OKE

Performance Optimization

Database Connection Pooling

The application uses connection pooling (default: 20 connections). Adjust if needed:

// In backend/server.js (if customizing)
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Maximum connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Timeout after 2s
});

Tuning Guidelines:

Small deployments (< 10 concurrent users): 10-20 connections
Medium deployments (10-50 users): 20-50 connections
Large deployments (50+ users): 50-100 connections (monitor database limits)

Query Timeouts

Default query timeout is 30 seconds. Adjust in server.js if needed:

await client.query('SET statement_timeout = 30000'); // 30 seconds

Frontend Caching

React Query caches API responses:

staleTime: 5 minutes (data considered fresh)
cacheTime: 10 minutes (data kept in cache)

No configuration needed, but be aware of cache behavior during updates.

Monitoring & Logging

Health Checks

Endpoint: GET /api/health

Response:

{
  "status": "ok",
  "timestamp": "2025-01-27T10:00:00.000Z"
}

Use for:

Container health checks (Docker, Kubernetes)
Load balancer health checks
Monitoring alerts

Logging

Log Levels:

ERROR: Database connection failures, authentication failures
WARN: Slow queries (>1 second), connection pool exhaustion
INFO: Application startup, successful connections, query execution times
DEBUG: Detailed query logs (development only)

Log Aggregation:

Docker:

# View logs
docker logs selfhosteddb-prod
 
# Follow logs
docker logs -f selfhosteddb-prod
 
# Log rotation (configured in docker-compose)
logging:
  driver: "json-file"
  options:
    max-size: "10m"
    max-file: "3"

Kubernetes:

# View logs
kubectl logs -f deployment/selfhosteddb
 
# Export logs
kubectl logs deployment/selfhosteddb > app.log

Cloud Platforms:

AWS: CloudWatch Logs
Azure: Application Insights
GCP: Cloud Logging

Monitoring Metrics

Key Metrics to Monitor:

Response Time: Average API response time (< 200ms for table queries)
Error Rate: 4xx/5xx errors (< 1% of requests)
Database Connections: Active connections in pool
Memory Usage: Container memory consumption
CPU Usage: Container CPU utilization

Tools:

Prometheus + Grafana: Self-hosted monitoring
Datadog: Cloud monitoring (paid)
New Relic: Application performance monitoring (paid)

Backup & Recovery

Database Backups

The application does NOT handle database backups. You must configure backups at the database level.

PostgreSQL Backup Options:

pg_dump (Manual):

# Full backup
pg_dump -U postgres -d database_name > backup.sql
 
# Automated daily backup (cron)
0 2 * * * pg_dump -U postgres -d database_name > /backups/db_$(date +\%Y\%m\%d).sql

pg_basebackup (Physical backup):

pg_basebackup -D /backups/basebackup -Ft -z -P

Cloud Managed Backups:

AWS RDS: Automated backups enabled by default
Azure Database: Automated backups configured in portal
DigitalOcean Managed Database: Daily backups included

Application Data

No persistent data stored in application container. All data is in PostgreSQL.

Backup Application Configuration:

Environment variables (store in secrets manager)
Docker image (push to registry)
Deployment configuration (version control)

Recovery Procedures

Database Recovery:

# Restore from SQL dump
psql -U postgres -d database_name < backup.sql
 
# Restore from physical backup
pg_ctl stop
rm -rf /var/lib/postgresql/data/*
tar -xzf basebackup.tar.gz -C /var/lib/postgresql/data
pg_ctl start

Application Recovery:

Pull latest Docker image
Restore environment variables
Deploy container
Verify health check: curl http://localhost:3001/api/health

Scaling Considerations

Horizontal Scaling

Multiple Instances:

The application is stateless (except for in-memory connection pools). You can run multiple instances behind a load balancer.

Load Balancer Configuration:

Health Check: /api/health
Sticky Sessions: Not required (stateless)
Connection Pooling: Each instance maintains its own pool

Example (nginx):

upstream selfhosteddb {
    least_conn;
    server app1:3001;
    server app2:3001;
    server app3:3001;
}
 
server {
    listen 80;
    server_name your-domain.com;
    
    location / {
        proxy_pass http://selfhosteddb;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection 'upgrade';
        proxy_set_header Host $host;
        proxy_cache_bypass $http_upgrade;
    }
}

Vertical Scaling

Increase Resources:

CPU: More vCPUs for concurrent query processing
RAM: More memory for connection pools and caching
Database: Scale PostgreSQL (read replicas, connection limits)

Database Scaling

Read Replicas:

Configure read replicas for read-heavy workloads
Application does NOT support read/write splitting (all queries go to primary)

Connection Limits:

Monitor PostgreSQL max_connections setting
Ensure application pool size doesn't exceed database limits

Kubernetes Deployment

Deployment YAML:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: selfhosteddb
spec:
  replicas: 2
  selector:
    matchLabels:
      app: selfhosteddb
  template:
    metadata:
      labels:
        app: selfhosteddb
    spec:
      containers:
      - name: selfhosteddb
        image: your-registry/selfhosteddb:latest
        ports:
        - containerPort: 3001
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: selfhosteddb-secrets
              key: database-url
        - name: AUTH_USER
          valueFrom:
            secretKeyRef:
              name: selfhosteddb-secrets
              key: auth-user
        - name: AUTH_PASS
          valueFrom:
            secretKeyRef:
              name: selfhosteddb-secrets
              key: auth-pass
        - name: LICENSE_SERVER_URL
          valueFrom:
            secretKeyRef:
              name: selfhosteddb-secrets
              key: license-server-url
        - name: LICENSE_ENCRYPTION_KEY
          valueFrom:
            secretKeyRef:
              name: selfhosteddb-secrets
              key: license-encryption-key
        - name: NODE_ENV
          value: "production"
        - name: PORT
          value: "3001"
        livenessProbe:
          httpGet:
            path: /api/health
            port: 3001
          initialDelaySeconds: 30
          periodSeconds: 10
        readinessProbe:
          httpGet:
            path: /api/health
            port: 3001
          initialDelaySeconds: 10
          periodSeconds: 5
---
apiVersion: v1
kind: Service
metadata:
  name: selfhosteddb-service
spec:
  selector:
    app: selfhosteddb
  ports:
  - protocol: TCP
    port: 80
    targetPort: 3001
  type: LoadBalancer

Create Secrets:

kubectl create secret generic selfhosteddb-secrets \
  --from-literal=database-url='postgres://...' \
  --from-literal=auth-user='admin' \
  --from-literal=auth-pass='your-password' \
  --from-literal=license-server-url='https://license.yourdomain.com' \
  --from-literal=license-encryption-key='your-encryption-key'

Security in Production

See Security Guide for comprehensive security best practices.

Quick Checklist:

HTTPS enabled (reverse proxy or load balancer)
Strong authentication credentials
Database SSL/TLS enabled
Firewall rules configured
Regular security updates
Secrets stored in secrets manager (not in code)

Troubleshooting

See Troubleshooting Guide for common issues and solutions.

Related Documentation:

Installation Guide - Initial setup
Security Guide - Security best practices
API Documentation - API usage

Platform-Specific Guides

For detailed platform-specific deployment instructions:

AWS Deployment - EC2, ECS, EKS deployment
Azure Deployment - Container Instances, App Service, AKS
Google Cloud Platform - Cloud Run, GKE
DigitalOcean - Droplets, App Platform
Oracle Cloud - Container Instances, OKE

Support

For production deployment support:

Documentation: See Installation Guide
Issues: Check Troubleshooting Guide
API Reference: See API Documentation

Last Updated: 2025-01-27

Installation AWS Deployment