Implement automated backup system (SQLite to GCS)

[fusion94]

Description

Build an automated daily backup system for the SQLite database with local and cloud storage retention.

Requirements

Backup Script

• Use SQLite .backup API (safe even while DB is in use with WAL mode)
• Pre-backup: PRAGMA wal_checkpoint(TRUNCATE) to flush WAL
• Post-backup: PRAGMA integrity_check on the backup file to verify
• Timestamp backup files: cellar-backup-YYYY-MM-DD-HHMMSS.db
• Compress backup with gzip

Backup Schedule

• Daily automated backup via cron job or systemd timer
• Run at a low-traffic time (e.g., 3:00 AM)

Local Retention

• Store backups in /var/backups/cellarsync/
• Retain 30 days of local backups
• Auto-delete backups older than 30 days

Cloud Backup (GCS)

• Upload compressed backup to a GCS bucket
• Retain 90 days in GCS
• GCS lifecycle policy for auto-deletion
• VM service account with minimal GCS permissions (write to backup bucket only)

Backup Verification

• Weekly integrity check: restore latest backup to a temp database and run PRAGMA integrity_check
• Log results for monitoring

Backup npm Script

• npm run db:backup — Trigger manual backup
• npm run db:backup:verify — Verify latest backup integrity

Implementation

• scripts/backup.sh — Main backup script
• scripts/backup-verify.sh — Verification script
• Cron entry or systemd timer for scheduling
• GCS upload using gsutil or GCP Node.js SDK

Acceptance Criteria

• Backup script creates valid SQLite backup
• WAL checkpoint runs before backup
• Integrity check passes on backup file
• Backups are compressed (gzip)
• Local retention: 30 days with auto-cleanup
• GCS upload works (if configured)
• Manual backup trigger works via npm script
• Backup verification script restores and checks integrity
• Cron/timer runs daily without issues

References

• PRD Section 6: Backup Strategy
• PRD Section 7: Infrastructure & Deployment
• PRD Section 3: US-15 (User Stories)
• PRD Section 12: Risks & Mitigations (Data loss)