xamxam/docs/nextcloud-sync.md

# Nextcloud off-site backup sync

XAMXAM pushes its latest SQLite snapshot to a Nextcloud folder so that a
server/disk failure doesn't take the backup history with it. This is the
implementation of *Phase 5 — Remote Sync* from the archived
[`backup-plan.md`](archive/backup-plan.md).

## What it does

| Property | Value |
|----------|-------|
| **Destination** | `https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK` |
| **Protocol** | WebDAV (HTTP `PUT` / `PROPFIND` / `DELETE`) |
| **Credentials** | Reused from `smtp_settings` (`xamxam@erg.be`), decrypted via `Crypto` |
| **Transport** | PHP `curl` extension — no `rclone`, no shell `curl` |
| **What is uploaded** | The newest `db-*.db.gz` snapshot from `/var/backups/xamxam` |
| **Remote filename** | `xamxam-db-<timestamp>.db.gz` (timestamped, not overwritten) |
| **Remote retention** | Last **7** snapshots (configurable via `REMOTE_KEEP`) |
| **Cadence** | Daily, 20 min after the 02:00 snapshot |

The uploads are **SQLite snapshots only** — the CSV and files-ZIP exports
(see [export.md](export.md)) are *not* pushed. This keeps Nextcloud usage
minimal; the full export set remains available on-demand from the admin panel.

### Sizing

As of writing, the live DB is ~2.5 MB and compresses to ~475 KB. Seven daily
snapshots are therefore ~3.3 MB — a negligible fraction of the 10 GB Nextcloud
quota for `xamxam@erg.be`. If more history is desired, raise `REMOTE_KEEP` in
the cron line; there is ample headroom.

## Script: `scripts/nextcloud-sync.php`

CLI script (deployed to `/usr/local/bin/nextcloud-sync.php`), run by cron.

Flow:
1. Resolve `APP_ROOT` (`/var/www/xamxam` in prod, `app/` in dev) and load the
   composer autoloader.
2. No-op `DatabaseMigrations` (read-only — the sync must never mutate the DB).
3. Load SMTP credentials via `SmtpRelay::getSettings()` (decrypts the password).
4. Find the newest `db-*.db.gz` in `/var/backups/xamxam`.
5. `PUT` it to the WebDAV path (streaming upload, memory-safe).
6. `PROPFIND` the uploaded file and verify its size matches the local file
   (integrity check — a failed sync is detected, not assumed).
7. `PROPFIND` the folder, list `xamxam-db-*.db.gz`, and `DELETE` the oldest
   beyond `REMOTE_KEEP` (`7` by default).

Exit code `0` on success, `1` on any failure (logged to
`/var/log/xamxam-backup-YYYY-MM-DD.log`). A sync failure never blocks the
local backup — the two jobs are separate cron entries.

### Environment variables

| Variable | Default | Meaning |
|----------|---------|---------|
| `REMOTE_KEEP` | `7` | Number of remote snapshots to retain |

## Cron wiring

In `/etc/cron.d/xamxam-backup` (deployed from `deploy/xamxam-backup.cron`):

```
# Daily snapshot at 2am — kept 90 days
0  2  *  *  *  www-data  RETENTION_DAYS=90 /usr/local/bin/backup-sqlite.sh >> /var/log/xamxam-backup-$(date +\%Y-\%m-\%d).log 2>&1

# Push the latest snapshot to Nextcloud WebDAV (daily, after the 2am snapshot)
20 2  *  *  *  www-data  php /usr/local/bin/nextcloud-sync.php >> /var/log/xamxam-backup-$(date +\%Y-\%m-\%d).log 2>&1
```

The sync runs as `www-data`, so it shares the file permissions of the
backup script (`/var/backups/xamxam` is `www-data:www-data`). The `.env`
holding the crypto key is `640 www-data:xamxam` — readable by `www-data`, so
`Crypto::decrypt` works from the CLI.

## Monitoring

The backup watchdog ([`scripts/backup-watchdog.php`](../scripts/backup-watchdog.php))
now checks **both** the local snapshots and the Nextcloud copy:

- **Local** — newest `db-*.db.gz` older than 2 h → alert.
- **Remote** — newest `xamxam-db-*.db.gz` older than 48 h (or none found) → alert.

Both feed the same anti-flood email to `xamxam@erg.be` (or the configured
`notify_email`). A single alert can report either or both issues.

## Manual verification

```bash
# Read the backup folder (expect 207 Multi-Status)
curl -sS -u 'xamxam@erg.be:<password>' -X PROPFIND --head \
  'https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK' \
  -o /dev/null -w '%{http_code}\n'

# Upload a test file (expect 201), then delete it (expect 204)
curl -sS -u 'xamxam@erg.be:<password>' -X PUT --data-binary test \
  'https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK/_write_test.txt' \
  -o /dev/null -w '%{http_code}\n'
curl -sS -u 'xamxam@erg.be:<password>' -X DELETE \
  'https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK/_write_test.txt' \
  -o /dev/null -w '%{http_code}\n'

# Run the sync manually (server only)
sudo -u www-data php /usr/local/bin/nextcloud-sync.php
```

## Restoring from Nextcloud

The remote snapshot is the same `.db.gz` the local backup produces, so
restoring follows the standard procedure in [deployment.md](deployment.md):

```bash
# Download the snapshot
curl -sS -u 'xamxam@erg.be:<password>' -O \
  'https://cloud.erg.school/remote.php/dav/files/xamxam%40erg.be/XAMXAM-BCK/xamxam-db-<timestamp>.db.gz'

# Restore
sudo systemctl stop nginx
gunzip -c xamxam-db-<timestamp>.db.gz > /var/www/xamxam/storage/xamxam.db
chown www-data:www-data /var/www/xamxam/storage/xamxam.db
chmod 660 /var/www/xamxam/storage/xamxam.db
sudo systemctl start nginx
```

## Security notes

- Credentials are read from the DB (`smtp_settings`) and **never** logged or
  echoed; only the resulting success/failure messages are written to the log.
- The Nextcloud password is the same LDAP/SSO-backed `xamxam@erg.be`
  credential used for SMTP — the WebDAV Basic-auth endpoint accepts it directly
  (verified live: `PUT` → 201, `PROPFIND` → 207, `DELETE` → 204).
- `CURLOPT_SSL_VERIFYPEER` / `CURLOPT_SSL_VERIFYHOST` are enabled (certificate
  chain is validated).
- The sync is one-way and additive-then-pruned: it never deletes a snapshot
  until a newer one has been confirmed uploaded.