Skip to main content

D1 Backup and Restore Runbook

This runbook covers the local parts of RFC-007 Phase 5.

Files​

  • Backup script: scripts/d1-backup.sh
  • Backup workflow: .github/workflows/d1-backup.yml
  • Default output path: backups/d1/*.sql

Automated Backups (GitHub Actions)​

The backup workflow runs automatically:

  • Scheduled: Daily at 00:00 UTC via .github/workflows/d1-backup.yml
  • Manual: Trigger via GitHub Actions workflow_dispatch

Setup​

  1. Add secrets to GitHub repository:

    • CLOUDFLARE_API_TOKEN: Cloudflare API token with D1 and R2 write access
    • CLOUDFLARE_ACCOUNT_ID: Cloudflare account ID
    • R2_BACKUP_BUCKET: R2 bucket name (optional, for cloud storage)

  2. The workflow will:

    • Export the remote D1 database
    • Upload to R2 (if configured)
    • Keep last 7 backups locally in repository

Manual Backup procedure​

  1. Local backup:
./scripts/d1-backup.sh local
  1. Remote backup:
./scripts/d1-backup.sh remote
  1. Remote backup + upload to R2:
R2_BACKUP_BUCKET=your-backup-bucket R2_OBJECT_PREFIX=photobooth/d1 ./scripts/d1-backup.sh remote

Restore procedure​

  1. Identify backup file to restore.
  2. Apply to local D1 for verification:
npx wrangler d1 execute photobooth-db --local --file backups/d1/<backup-file>.sql
  1. Validate critical tables:
npx wrangler d1 execute photobooth-db --local --command "SELECT COUNT(*) AS payments FROM payments;"
npx wrangler d1 execute photobooth-db --local --command "SELECT COUNT(*) AS licenses FROM licenses;"
  1. After validation, execute restore in remote environment during approved maintenance window:
npx wrangler d1 execute photobooth-db --remote --file backups/d1/<backup-file>.sql

Operational checks​

  1. Confirm export file is non-empty.
  2. Confirm restore SQL exits successfully.
  3. Confirm row counts and a sample payment lookup after restore.
  4. Record backup timestamp and checksum in the incident/change log.