Skip to main content

​
Overview

Millennium Potters uses a multi-layered backup strategy combining CockroachDB’s native backups with application-level exports to Cloudinary.

​
Backup Types

​
1. CockroachDB Automatic Backups

Production database backups (managed by CockroachDB Serverless):
  • Frequency: Hourly
  • Retention: 30 days
  • Type: Full database snapshots
  • Location: CockroachDB managed storage
  • Recovery: Point-in-Time Recovery (PITR)
These backups are automatic and require no manual intervention. They protect against database-level failures.

​
2. Application-Level Backups

The backend API provides manual backup creation with flexible options: 
POST /api/backup/create
Content-Type: application/json
Authorization: Bearer YOUR_ADMIN_TOKEN

{
  "includeAuditLogs": false,
  "includeSessions": false,
  "location": "cloud"
}
Parameters:
  • includeAuditLogs: Include audit log entries (default: false)
  • includeSessions: Include active user sessions (default: false)
  • location: "local", "cloud", or "both" (default: "local")
Response: 
{
  "success": true,
  "data": {
    "backupId": "clx1234567890",
    "filename": "backup_2026-03-11T10-30-15-000Z.json",
    "downloadUrl": "https://res.cloudinary.com/..."
  },
  "message": "Backup created successfully"
}
Admin access required - All backup endpoints require authentication and the ADMIN role (backend/src/routes/backup.routes.ts:9-10).

​
Backup Operations

​
List All Backups

GET /api/backup/list
Response: 
{
  "success": true,
  "data": [
    {
      "id": "clx1234567890",
      "filename": "backup_2026-03-11T10-30-15-000Z.json",
      "fileSize": 2457600,
      "location": "cloud",
      "cloudinaryUrl": "https://res.cloudinary.com/...",
      "type": "manual",
      "status": "completed",
      "recordCounts": {
        "users": 25,
        "unions": 10,
        "loans": 150,
        "repayments": 450
      },
      "createdAt": "2026-03-11T10:30:15.000Z",
      "createdBy": "clxADMIN123"
    }
  ]
}

​
Download a Backup

GET /api/backup/download/:id
Behavior:
  • If stored in Cloudinary: Redirects to the cloud URL
  • If stored locally: Streams the file directly
Implementation: backend/src/controllers/backup.controller.ts:53-78

​
Delete a Backup

DELETE /api/backup/:id
This action is irreversible. Deleted backups cannot be recovered unless they exist in multiple locations.

​
Restore Operations

​
Restore from Backup File

POST /api/backup/restore
Content-Type: application/json

{
  "backupData": {
    "metadata": {
      "version": "1.0",
      "timestamp": "2026-03-11T10:30:15.000Z"
    },
    "data": {
      "users": [...],
      "unions": [...],
      "loans": [...]
    }
  }
}
Important Notes:
  • System must be in maintenance mode during restore
  • All users will be logged out after restore
  • The restore process validates backup format before proceeding
Implementation: backend/src/controllers/backup.controller.ts:108-132

​
System Reset (Emergency)

POST /api/backup/reset
Content-Type: application/json

{
  "confirmationToken": "DELETE"
}
DESTRUCTIVE OPERATION - This wipes all data and creates a fresh SuperAdmin account:
  • Username: SuperAdmin
  • Password: SecurePassword123
Requires exact confirmation token: "DELETE"

​
Point-in-Time Recovery (PITR)

​
CockroachDB PITR

Restore to any point within the last 30 days: 
-- Restore entire database to 2 hours ago
RESTORE DATABASE millenium_potters 
FROM LATEST IN 's3://your-backup-bucket/backups/' 
AS OF SYSTEM TIME '-2h';

-- Restore specific table to exact timestamp
RESTORE TABLE millenium_potters.loans 
FROM LATEST IN 's3://your-backup-bucket/backups/' 
AS OF SYSTEM TIME '2026-03-11 08:00:00';
When to use PITR:
  • Accidental data deletion
  • Incorrect bulk updates
  • Rollback after failed migration
  • Recovery from data corruption

​
pg_dump Method (Manual Backups)

Following the documented deployment procedures: 
# Create backup
pg_dump "[PROD_CONNECTION_STRING]" \
  --no-owner --no-acl \
  > prod_backup_$(date +%Y%m%d_%H%M%S).sql

# Restore full backup
psql "[PROD_CONNECTION_STRING]" < prod_backup_20260311_103015.sql

# Restore specific table
grep -A 1000 "COPY public.User" prod_backup.sql | head -n 500 > user_table_only.sql
psql "[PROD_CONNECTION_STRING]" < user_table_only.sql
No format conversion needed - pg_dump produces PostgreSQL-compatible SQL that works directly with CockroachDB.

​
Backup Schedule Settings

​
Get Schedule Configuration

GET /api/backup/schedule
Response: 
{
  "success": true,
  "data": {
    "frequency": "daily",
    "location": "cloud",
    "retentionDays": 30,
    "includeAuditLogs": false,
    "includeSessions": false,
    "lastBackupAt": "2026-03-11T02:00:00.000Z",
    "nextBackupAt": "2026-03-12T02:00:00.000Z"
  }
}

​
Update Schedule

PUT /api/backup/schedule
Content-Type: application/json

{
  "frequency": "daily",
  "location": "both",
  "retentionDays": 60,
  "includeAuditLogs": true,
  "includeSessions": false
}
Frequency options:
  • "disabled": No automatic backups
  • "daily": Once per day at 2 AM
  • "weekly": Every Sunday at 2 AM
  • "monthly": First day of month at 2 AM
Database schema: backend/prisma/schema.prisma:661-673

​
Cloudinary Storage

Application-level backups are exported to Cloudinary:
  • Format: JSON
  • Compression: Enabled for files > 1MB
  • Access: Authenticated URLs with expiration
  • Retention: Based on schedule settings (default 30 days)
Storage location pattern: 
res.cloudinary.com/<cloud_name>/raw/upload/backups/backup_<timestamp>.json

​
Backup Verification

Before relying on any backup, verify its integrity:
1

Check file size

Ensure the backup file is not empty or suspiciously small. A typical backup should be several MB.
2

Validate JSON structure

For application backups, verify the JSON parses correctly:
cat backup.json | jq '.metadata'
3

Check record counts

Compare record counts in backup metadata to expected values:
{
  "recordCounts": {
    "users": 25,
    "unions": 10,
    "loans": 150
  }
}
4

Test restore in staging

Always test restore procedures in a non-production environment first.

​
Dependency Checking

Before deleting entities, check for dependencies: 
GET /api/backup/dependencies/:entityType/:entityId
Example: 
GET /api/backup/dependencies/union/clx1234567890
Response: 
{
  "success": true,
  "data": {
    "canDelete": false,
    "dependencies": {
      "loans": 5,
      "unionMembers": 12
    },
    "message": "Cannot delete: 5 active loans and 12 members depend on this union"
  }
}
Supported entity types:
  • user
  • union
  • unionMember
  • loanType
Implementation: backend/src/controllers/backup.controller.ts:212-230

​
Backup Best Practices

Enable automatic backups - Set frequency to at least "daily" with cloud storage
Test restore procedures regularly - Schedule quarterly restore drills
Store backups off-site - Use Cloudinary ("cloud") or "both" locations
Document restore procedures - Keep runbooks updated with connection strings and commands
Monitor backup jobs - Check BackupRecord status field for failures
Verify before major changes - Always create a manual backup before schema migrations or data transformations

Build docs developers (and LLMs) love

Get started for freeTalk to us