# Database Backup — Complete Reference

Cydekick uses **spatie/laravel-backup v9** to dump the MySQL database, compress it into an encrypted zip, and upload it to a dedicated Cloudflare R2 bucket. Settings (retention, R2 credentials, folder path) are managed through the admin UI at **Settings → Backups**.

---

## Architecture overview

```
Schedule (02:00)
  └─ backup:run --only-db
       └─ RunDatabaseBackupJob (queued, queue=high)
            ├─ Re-reads BackupSettings from DB (always fresh)
            ├─ mysqldump → .sql
            ├─ Zip + encrypt (BACKUP_ARCHIVE_PASSWORD)
            └─ Upload to r2-backups disk → {folder}/{AppName}/{datetime}.zip

Schedule (02:30)  backup:clean   — prune old files per retention policy
Schedule (03:00)  backup:monitor — health check (alerts if >48h old or >5 GB)
```

Manual trigger: **Run Backup Now** button on the Settings → Backups page.

---

## File map

```
app/
├── Filament/Pages/Settings/ManageBackups.php   — admin UI page (Livewire)
├── Jobs/RunDatabaseBackupJob.php               — queued job that calls backup:run
├── Settings/BackupSettings.php                 — spatie/laravel-settings class
└── Providers/AppServiceProvider.php            — applies BackupSettings to config at boot

config/
├── backup.php                                  — spatie backup config
└── database.php                               — mysql.dump.dump_binary_path (MYSQLDUMP_PATH)

database/migrations/
├── 2026_08_17_000001_seed_backup_settings.php
├── 2026_08_17_000002_add_r2_fields_to_backup_settings.php
└── 2026_08_17_000003_add_r2_path_to_backup_settings.php

resources/views/filament/pages/settings/backups.blade.php
bootstrap/app.php                               — schedule registration
```

---

## R2 storage

Backups go to a **dedicated private bucket** (`cydekick-backups`) — separate from the image storage bucket so database dumps are never publicly accessible.

- Public Development URL must be **disabled** on this bucket
- Disk name in Laravel: `r2-backups`
- Files land at: `{folder}/{AppName}/{Y-m-d-H-i-s}.zip`
  - e.g. `db-backups/production/Cydekick/2026-08-17-02-00-17.zip`

The disk is configured in two places (first one wins at boot, job always re-reads):

1. **`config/filesystems.php`** — base definition using env vars as fallback
2. **`AppServiceProvider::boot()`** — overrides with `BackupSettings` values from DB

---

## BackupSettings (spatie/laravel-settings)

Stored in the `settings` table under `group = 'backup'`. Managed via admin UI.

| Property | Default | Purpose |
|---|---|---|
| `keep_all_days` | 14 | Keep every backup for this many days |
| `keep_daily_days` | 90 | Keep one per day for this many days |
| `keep_weekly_weeks` | 52 | Keep one per week for this many weeks |
| `keep_monthly_months` | 24 | Keep one per month |
| `keep_yearly_years` | 5 | Keep one per year |
| `notify_email` | '' | Email for failure/health alerts |
| `r2_account_id` | null | Cloudflare Account ID |
| `r2_access_key_id` | null | R2 API token key |
| `r2_secret_access_key` | null | R2 API token secret |
| `r2_bucket` | null | Bucket name (`cydekick-backups`) |
| `r2_path` | `db-backups` | Folder path within bucket — use `db-backups/production` on prod, `db-backups/dev` locally |

Saving settings in the UI also calls `config:clear` and `queue:restart` automatically, so workers pick up changes without manual intervention (Supervisor restarts them on production).

---

## How RunDatabaseBackupJob works

The job always re-reads `BackupSettings` from the database at the start of `handle()` and calls `Storage::forgetDisk('r2-backups')` before reconfiguring it. This means even a long-running worker that was started before settings were changed will use the current config for the next backup.

```php
// Simplified — see app/Jobs/RunDatabaseBackupJob.php
$backup = app(BackupSettings::class);
config(['filesystems.disks.r2-backups' => [...]]);
Storage::forgetDisk('r2-backups');
$exitCode = Artisan::call('backup:run', ['--only-db' => true]);
if ($exitCode !== 0) throw new RuntimeException('...');
```

A non-zero exit code throws, so the console shows **ERR** and the job appears in the Errors tab.

---

## .env variables

| Variable | Required | Notes |
|---|---|---|
| `BACKUP_ARCHIVE_PASSWORD` | Yes | Password for the zip encryption — store safely |
| `BACKUP_NOTIFY_EMAIL` | Yes | Fallback if `notify_email` setting is blank |
| `MYSQLDUMP_PATH` | Windows only | Path to mysqldump directory, e.g. `C:\xampp\mysql\bin`. Blank on Linux. |

On Linux/production, `mysqldump` is in PATH so `MYSQLDUMP_PATH` is not needed. The `database.connections.mysql.dump.dump_binary_path` config key is set from this env var at config-load time (not in AppServiceProvider) so queue workers always see it.

---

## Compression

`database_dump_compressor` is set to `null` in `config/backup.php`. This means the SQL file is added to the zip uncompressed at the SQL level — the zip archive itself compresses it. This avoids a dependency on `gzip` being in PATH (which it isn't on Windows/XAMPP). File sizes are equivalent.

Do **not** re-enable `GzipCompressor` without confirming `gzip` is in PATH on all environments.

---

## Verifying a backup

**Check the dump is complete** (must end with `-- Dump completed on`):

```bash
# On the server — download latest, extract, check tail
php artisan tinker --execute="
\$disk = Storage::disk('r2-backups');
\$latest = collect(\$disk->allFiles('Cydekick'))->filter(fn(\$f) => str_ends_with(\$f, '.zip'))->sort()->last();
file_put_contents('/tmp/latest-backup.zip', \$disk->get(\$latest));
echo \$latest . PHP_EOL;
"
unzip -P "your-password" /tmp/latest-backup.zip -d /tmp/backup-check/
tail -5 /tmp/backup-check/*.sql
rm -rf /tmp/latest-backup.zip /tmp/backup-check/
```

**Trial restore** (Linux):

```bash
mysql -u root -p -e "CREATE DATABASE backup_test;"
mysql -u root -p backup_test < /tmp/backup-check/*.sql
mysql -u root -p -e "SHOW TABLES;" backup_test
mysql -u root -p -e "DROP DATABASE backup_test;"
```

**Import into MySQL Workbench** (Windows):

1. Download the zip from Cloudflare R2 dashboard (cydekick-backups bucket)
2. Extract with 7-Zip — right-click → 7-Zip → Extract Here → enter `BACKUP_ARCHIVE_PASSWORD`
3. Workbench → Server → Data Import → Import from Self-Contained File → select the `.sql` → Start Import

---

## Production deploy checklist

When deploying to a new server or after the initial backup setup:

```bash
# 1. Add to .env (before composer install — package:discover validates the email)
echo 'BACKUP_NOTIFY_EMAIL=email@jamesbanwell.co.uk' >> .env
echo 'BACKUP_ARCHIVE_PASSWORD=your-strong-password' >> .env

# 2. Standard deploy
git pull && composer install --no-dev --optimize-autoloader
php artisan migrate --force
php artisan view:clear && php artisan optimize:clear && php artisan optimize
sudo supervisorctl restart cydekick-worker:* cydekick-scheduler

# 3. In the admin UI (Settings → Backups):
#    - Fill in R2 credentials (Account ID, Bucket, Access Key, Secret)
#    - Set Folder path to: db-backups/production
#    - Save — cache clears and worker restarts automatically
```

> **Important:** `BACKUP_NOTIFY_EMAIL` must be in `.env` before `composer install` runs, because `package:discover` boots the app and spatie/laravel-backup validates the email address. A missing value causes the install to fail with `InvalidConfig::invalidEmail()`.

---

## Schedule

Registered in `bootstrap/app.php` via `$app->afterResolving(Schedule::class, ...)`.

| Time | Command | Purpose |
|---|---|---|
| 02:00 | `backup:run --only-db` | Create and upload the backup |
| 02:30 | `backup:clean` | Prune backups beyond retention policy |
| 03:00 | `backup:monitor` | Health check — alerts if newest backup >48h old |

The scheduler is run by `cydekick-scheduler` (Supervisor) which calls `php artisan schedule:run` every minute.