[Backend] Automated Database Backup & Disaster Recovery Verification Pipeline

[blurbeast]

Overview & Background

FlowFi stores indexed stream states, event histories, and user webhook configurations. To prevent data loss and ensure rapid disaster recovery, an automated encrypted backup pipeline with periodic restoration verification in CI is required.

---

Technical Specification & Architecture

1. Backup Script (scripts/db-backup.sh):
   • Perform atomic pg_dump with gzip compression.
   • Encrypt backup archive using AES-256-GCM.
   • Upload backup artifact to S3 / Cloud Storage with 30-day retention policy.
2. Disaster Recovery Validation Job (.github/workflows/dr-test.yml):
   • Scheduled weekly CI workflow that spins up a clean Postgres container.
   • Downloads the latest encrypted backup, decrypts, and restores it.
   • Runs Prisma schema verification and row count integrity assertions.

---

Target Files

• scripts/db-backup.sh
• scripts/db-restore.sh
• .github/workflows/dr-test.yml

---

Acceptance Criteria

• Encrypted automated backup script with cloud storage destination.
• Weekly disaster recovery CI workflow restores backup and validates DB integrity.
• Documentation added for disaster recovery procedures.

[obswrld]

I would like to work on this Issue

[Iker2522]

Hi! I'd like to claim this issue. Just finished BR-09 on zicket-backend (PR #181, merged) — comfortable building the encrypted backup script (pg_dump + AES-256-GCM + S3 upload), the restore script, and the weekly DR validation CI workflow with integrity checks. I can have this done in under 2 days.