Skip to main content

Automated Database Backups to AWS S3 + Admin API

Context​

Need automated daily backups of hannibal production database to AWS S3 at midnight, with a paginated admin API to view backup history.

Changes​

1. NEW Prisma model: DatabaseBackup​

File: backend/prisma/schema.prisma

model DatabaseBackup {
  id          String   @id @default(uuid())
  database    String                          // "hannibal"
  fileName    String   @map("file_name")
  fileSize    BigInt?  @map("file_size")      // bytes
  status      String   @default("pending")    // pending, completed, failed
  s3Key       String?  @map("s3_key")         // S3 object key
  s3Bucket    String?  @map("s3_bucket")
  errorMsg    String?  @map("error_msg")
  durationMs  Int?     @map("duration_ms")    // backup duration
  startedAt   DateTime @default(now()) @map("started_at")
  completedAt DateTime? @map("completed_at")

  @@index([status])
  @@index([startedAt])
  @@map("database_backups")
}

Run npx prisma migrate dev --name add-database-backups.

2. NEW service: backend/src/services/backupService.ts​

  • runBackup() — executes pg_dump via child_process.execSync on the Postgres container, gzips, uploads to S3 via AWS SDK, records entry in DatabaseBackup table
  • listBackups(page, pageSize, status?) — paginated query on DatabaseBackup
  • Uses @aws-sdk/client-s3 for S3 upload (add to backend deps)
  • Env vars: S3_BACKUP_BUCKET, S3_BACKUP_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY

3. NEW cron job: backend/src/jobs/backupJob.ts​

  • Registers with existing job scheduler (node-cron)
  • Runs daily at 0 0 * * * (midnight UTC)
  • Calls backupService.runBackup()
  • Pattern: follow existing job files in backend/src/jobs/

4. EDIT admin route: backend/src/routes/admin.ts​

Add two endpoints following existing patterns:

List backups (paginated):

GET /api/admin/backups?limit=50&offset=0&status=completed

Response: { success: true, data: [...backups], meta: { total } }

Trigger manual backup:

POST /api/admin/backups/trigger

Kicks off backupService.runBackup() async, returns immediately with the created backup record (status=pending). The backup completes in the background. Response: { success: true, data: { id, status: "pending", fileName } }

Both protected by authenticateAdmin (already applied to all admin routes). POST restricted to authorize('admin', 'super_admin').

5. EDIT: infra/services/hannibal/crontab​

Change schedule from daily 03:00 to midnight:

0 0 * * * root cd /opt/hannibal && ...

Note: The infra cron is a fallback. Primary trigger is the in-app node-cron job. Both can coexist — the backup service is idempotent (unique fileName per timestamp).

6. NEW: infra/configs/.env.infra.hannibal​

Checked-in reference config for the server.

Files Summary​

FileAction
backend/prisma/schema.prismaEDIT — add DatabaseBackup model
backend/src/services/backupService.tsNEW — backup logic + S3 upload
backend/src/jobs/backupJob.tsNEW — midnight cron trigger
backend/src/routes/admin.tsEDIT — add GET /backups + POST /backups/trigger endpoints
backend/package.jsonEDIT — add @aws-sdk/client-s3
infra/services/hannibal/crontabEDIT — midnight schedule
infra/configs/.env.infra.hannibalNEW — server config reference

Server Setup (manual, after merge)​

  1. Create S3 bucket + IAM user with s3:PutObject/s3:GetObject/s3:ListBucket
  2. Set env vars on server: S3_BACKUP_BUCKET, S3_BACKUP_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY
  3. Install crontab (fallback): crontab /opt/hannibal/infra/services/hannibal/crontab

Verification​

  1. Trigger backup manually via backupService.runBackup() or hitting a test endpoint
  2. Check database_backups table for entry with status=completed
  3. Verify S3 upload: aws s3 ls s3://<bucket>/postgres/
  4. Hit GET /api/admin/backups — confirm paginated response
  5. Check node-cron logs at midnight for automatic trigger