Database

BitDrip stores all persistent state in PostgreSQL. This guide covers backup, restore, and disaster recovery for self-hosted deployments.

Schema Overview

Table	Content	Backup priority
organizations	Tenant records, subscription tier	Critical
users	Accounts, roles, bcrypt password hashes	Critical
user_sessions	Active JWT sessions	Low — regenerated on login
api_keys	Hashed API keys, scopes, rate limits	Critical
audit_logs	Immutable evaluation log + SHA-256 hash chain	Critical
policies	Custom policy rules per organization	Critical
compliance_profiles	Active framework per organization	Critical

Backups

Backup strategy is your responsibility as the infrastructure owner. BitDrip does not manage backups for you. The recommended approach is a nightly pg_dump exported to durable object storage.

Manual Export

pg_dump "$DATABASE_URL" \
  --no-owner \
  --no-privileges \
  --format=custom \
  --compress=9 \
  --file="bitdrip-$(date +%Y%m%d-%H%M%S).pgdump"

The --format=custom flag produces a binary format that supports selective table restore.

Scheduled Export to Object Storage

For 90-day retention (CIS 8 compliance), schedule a nightly export job that uploads the dump to your object storage provider. The script below uses rclone, which supports S3, GCS, Azure Blob, Cloudflare R2, and most other providers:

#!/bin/bash
# backup.sh — run nightly via cron
set -euo pipefail

TIMESTAMP=$(date +%Y%m%d-%H%M%S)
FILENAME="bitdrip-${TIMESTAMP}.pgdump"
TMPFILE="/tmp/${FILENAME}"

pg_dump "$DATABASE_URL" \
  --no-owner --no-privileges \
  --format=custom --compress=9 \
  --file="$TMPFILE"

# Upload using rclone (configure your remote and bucket name)
rclone copy "$TMPFILE" "your-remote:your-bucket/db/${FILENAME}"
rm "$TMPFILE"

echo "Backup completed: ${FILENAME}"

Configure rclone for your storage provider with rclone config. Set a lifecycle policy on your bucket to delete objects older than 90 days — all major object storage providers support this.

Add to crontab:

0 2 * * * /opt/bitdrip/backup.sh >> /var/log/bitdrip-backup.log 2>&1

Restoring

Restore from pg_dump

# Full restore (overwrites existing database — use with caution)
pg_restore \
  --dbname="$DATABASE_URL" \
  --no-owner \
  --no-privileges \
  --clean \
  --if-exists \
  bitdrip-20260524-020000.pgdump

# Restore a single table (e.g., audit_logs only)
pg_restore \
  --dbname="$DATABASE_URL" \
  --no-owner \
  --no-privileges \
  --table=audit_logs \
  bitdrip-20260524-020000.pgdump

WARNING

Restoring audit_logs from backup will re-import the hash chain. Run the chain verification script after restore to confirm integrity:

node scripts/verify-audit-chain.js

Disaster Recovery

RTO / RPO Targets

Scenario	Recovery Time Objective	Recovery Point Objective
Corrupted table	< 1 hour	Last nightly backup (24h max)
Service outage (infra)	< 5 minutes	No data loss (depends on your HA setup)
Full database loss	< 2 hours	Last nightly backup (24h max)
Region outage	< 4 hours	Last nightly backup (24h max)

Recovery Runbook

1. Declare the incident — note the time data was last known good.

2. Stop writes — scale the Policy Engine to 0 replicas to prevent further writes to the corrupted state. How you do this depends on your container orchestrator (Docker Compose, Kubernetes, etc.).

3. Choose the recovery snapshot — select the latest backup before the incident time.

4. Restore — use pg_restore as shown above.

5. Verify — after restore:

# Check row counts match expected state
psql "$DATABASE_URL" -c "SELECT COUNT(*) FROM audit_logs;"
psql "$DATABASE_URL" -c "SELECT COUNT(*) FROM organizations;"

# Verify audit chain integrity
node scripts/verify-audit-chain.js

6. Resume traffic — scale Policy Engine back to 1+ replicas and confirm the /health endpoint returns 200.

Connection Pool Sizing

The Policy Engine opens a pool of PostgreSQL connections. Default pool size is 10. Tune via environment variable:

DATABASE_POOL_SIZE=20   # max connections from this service instance

Ensure your PostgreSQL max_connections setting (default 100) can accommodate DATABASE_POOL_SIZE × replicas plus headroom for admin tools. Leave at least 10 connections free for direct psql access during maintenance.

Maintenance

Vacuuming

PostgreSQL autovacuum runs automatically. The audit_logs table is append-only and grows quickly — monitor its size:

SELECT
  pg_size_pretty(pg_total_relation_size('audit_logs')) AS total_size,
  COUNT(*) AS row_count
FROM audit_logs;

Index Health

-- Check for unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

-- Check for bloated tables
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

Applying Migrations

BitDrip runs migrations automatically on startup via the db:migrate npm script. To run manually inside the Policy Engine container:

npm run db:migrate

Always back up before applying migrations to a production database.