# Story 2.4: Automated Backup Scheduling

## Story

**As a** system administrator,
**I want** to configure, trigger, and monitor automated PostgreSQL database backups from the admin UI,
**So that** I can meet the daily backup requirement without manual intervention and verify backups are completing.

## Status

done

## Acceptance Criteria

**AC1:** Given an authenticated admin navigates to `/admin/backups`, when the page loads, then the page shows: the configured destination path, the configured schedule, the status and timestamp of the last backup, and a "Run Backup Now" button. A table of the last 10 backup attempts is displayed with columns: timestamp, status (Success/Failed), filename, file size, and duration.

**AC2:** Given an admin configures the destination path and schedule and saves, when the form is POST'd to `/admin/backups/settings` with a valid CSRF token, then the destination path is validated (absolute, existing, writable directory). If valid, path and schedule are saved to `system_settings` and "Backup settings saved." is shown. If not writable, "The specified path is not writable. Please check server permissions." is shown.

**AC3:** Given an admin clicks "Run Backup Now", when the POST is sent to `/admin/backups/run` with a valid CSRF token, then `BackupService::run()` executes `pg_dump`, saves the file as `backup_YYYY-MM-DD_HHMMSS.sql`, records the attempt in `backup_log`, and redirects with a success or failure flash message.

**AC4:** Given the `backup_log` migration is run, then the table exists with: `id` (serial PK), `started_at`, `completed_at`, `status`, `filename`, `file_size_bytes`, `duration_seconds`, `error_message`.

**AC5:** Given `scripts/backup.php` is executed via cron, then it runs identically to a "Run Backup Now" trigger, records in `backup_log`, and its header documents the required cron entry.

**AC6:** Given backup_log has more than 30 entries, when `BackupService::run()` completes, then entries older than 30 days are pruned.

## Tasks / Subtasks

- [x] **Task 1: Migration** (AC: 4)
  - [x] 1.1: Create `db/migrations/20260522000001_create_backup_log_table.php`. Columns: `started_at` (timestamp not null), `completed_at` (timestamp nullable), `status` (varchar 10 not null), `filename` (varchar 255 nullable), `file_size_bytes` (integer nullable), `duration_seconds` (integer nullable), `error_message` (text nullable). Index on `started_at`.
  - [x] 1.2: Run `vendor/bin/phinx migrate` — migrated successfully.

- [x] **Task 2: Create `BackupService`** (AC: 3, 5, 6)
  - [x] 2.1: `run()` — validates configured path, builds filename `backup_YYYY-MM-DD_His.sql`, inserts pessimistic log row (status `'failed'`), calls `executePgDump()`, updates log row with final status / file size / duration, prunes old log entries, writes `Logger::info` or `Logger::error`.
  - [x] 2.2: `executePgDump()` — builds `pg_dump` command via `escapeshellarg()` for all arguments, executes via `proc_open` with inherited env + `PGPASSWORD` if `DB_PASSWORD` is set. Returns `[bool $success, ?string $error]`.
  - [x] 2.3: `pruneOldLogs()` — `DELETE FROM backup_log WHERE started_at < NOW() - INTERVAL '30 days'`, wrapped in try/catch (non-fatal).

- [x] **Task 3: Create `BackupController`** (AC: 1, 2, 3)
  - [x] 3.1: `showBackups()` — reads settings, fetches last 10 log rows (`getRecentBackupLogs()`), passes to view. `Cache-Control: no-store`.
  - [x] 3.2: `saveSettings()` — CSRF check, validates path via `validateDestinationPath()`, constrains schedule to `SCHEDULE_OPTIONS` allowlist, saves to `system_settings`, PRG redirect.
  - [x] 3.3: `runBackup()` — CSRF check, calls `BackupService::run()`, sets flash success/error, PRG redirect.
  - [x] 3.4: `validateDestinationPath(string $path): ?string` — static; returns error string or null. Checks: non-empty, starts with `/`, `is_dir()`, `is_writable()`.
  - [x] 3.5: `SCHEDULE_OPTIONS` constant — three cron expressions with human labels (Daily 2 AM, Weekly Sunday 2 AM, Monthly 1st 2 AM).

- [x] **Task 4: Create `backups.php` view** (AC: 1, 2, 3)
  - [x] 4.1: Admin tabs partial with `$activeTab = 'backups'`.
  - [x] 4.2: Status summary card: last backup status badge + timestamp; "Run Backup Now" button (disabled if no destination path configured).
  - [x] 4.3: Settings form: destination path text input + schedule select using `SCHEDULE_OPTIONS`. Cron expression shown beneath select as a `<code>` hint.
  - [x] 4.4: Backup log table: started_at, status badge (approved/rejected), filename, formatted file size (B/KB/MB), duration in seconds. Failed rows show error in `title` attribute. Empty state: "No backup attempts yet."

- [x] **Task 5: Create `scripts/backup.php`** (AC: 5)
  - [x] 5.1: PHP CLI script (SAPI guard). Bootstraps autoloader + dotenv. Instantiates `SystemSettingsService` + `BackupService`, calls `run()`. Exits 0 on success, 1 on failure. Writes to stdout/stderr with timestamp prefix.
  - [x] 5.2: Header comment documents three cron examples (daily, weekly, monthly).

- [x] **Task 6: Update tabs and routes** (AC: 1)
  - [x] 6.1: `_admin_tabs.php` — add "Backups" tab (fourth position).
  - [x] 6.2: `public/index.php` — instantiate `BackupController`; wire `GET /admin/backups`, `POST /admin/backups/settings`, `POST /admin/backups/run`, all behind `AuthMiddleware::requireRole('admin')`.

- [x] **Task 7: Tests** (AC: 2)
  - [x] 7.1: Create `tests/Unit/BackupControllerValidationTest.php` — tests for `validateDestinationPath`: empty string invalid, relative path invalid, non-existent path invalid, `sys_get_temp_dir()` valid, return type contract, `SCHEDULE_OPTIONS` format.
  - [x] 7.2: Run full suite — 76 tests, 3 skipped (pending Story 3.1 tables), 0 failures.

## Dev Notes

### pg_dump security

All `pg_dump` arguments (host, port, user, dbname, file path) go through `escapeshellarg()` — no shell injection is possible from config values. `PGPASSWORD` is passed via the child process environment, not the command line, so it does not appear in `ps aux` output.

### Pessimistic log write pattern

`insertLog()` inserts `status = 'failed'` before `pg_dump` runs. If the PHP process crashes mid-backup, the log row correctly reflects failure rather than leaving no record. `updateLog()` overwrites the status on completion.

### Socket vs password auth

The dev environment uses `DB_HOST=/var/run/postgresql` (Unix socket, peer auth). `BackupService` inherits the current process environment via `proc_open`, which already has peer-auth context. The `PGPASSWORD` injection is a no-op in this environment and required for password-auth production deployments.

### Run Backup Now is synchronous

The web-triggered backup runs synchronously in the HTTP request. For the small databases in scope this is acceptable. If backup duration approaches PHP's `max_execution_time`, raise the limit or switch to async (queue + polling) in a future story.

### File list

**New files:**
- `db/migrations/20260522000001_create_backup_log_table.php`
- `src/Services/BackupService.php`
- `src/Controllers/BackupController.php`
- `src/Views/admin/backups.php`
- `scripts/backup.php`
- `tests/Unit/BackupControllerValidationTest.php`

**Modified files:**
- `src/Views/admin/_admin_tabs.php` (Backups tab added)
- `public/index.php` (BackupController + 3 new routes)