Automated database backup flow for flower (scheduled mysqldump + rotation + restore path)

You are being dispatched from flower Brief #141: Automated database backup flow for flower (scheduled mysqldump + rotation + restore path)

Recall pointer:
- Use recall_brief with id 141 for the full folder if you need provenance.

Target:
- project: flower (/Users/mikeferrara/Documents/code/flower)
- branch: flower/141-db-backup
- worktree: not specified
- kind: fresh

Current brief spec:
## Goal
Scheduled, automated, rotated MySQL backup of the flower DB with a documented restore path — a safety net. (Context: a manual 326MB dump was taken 2026-07-03 at `~/flower-db-backups/flower-20260703-083107.sql.gz`.)

## Requirements
- **Command `php artisan flower:backup-db`** — `mysqldump` over TCP (`-h 127.0.0.1 -P 3306 --single-transaction --quick --no-tablespaces`, per CLAUDE.md's Herd-TCP note; the default unix socket `/tmp/mysql.sock` does NOT exist under Herd, so socket-default dumps fail). Pipe to gzip → a timestamped file.
- **Location:** configurable durable dir OUTSIDE the repo (default `~/flower-db-backups`), via `config/flower.php`, env-overridable.
- **Schedule:** register in the Laravel scheduler (already runs as the Solo `schedule:work` command on MAIN) at a configurable cadence (default daily).
- **Retention/rotation:** keep N most-recent backups (configurable, default 7); prune older.
- **Restore:** document the restore path (`gunzip | mysql` over TCP); optionally a guarded `flower:restore-db --file=` helper (must be confirm-guarded — it overwrites the live DB).
- **Config-as-data:** knobs in `config/flower.php` (dir, cadence, retention, mysqldump flags), all env-overridable.
- **Observability (optional):** last-run timestamp + size into a storage metric / `recall_health` so a stale or failed backup is visible.

## Verify / acceptance
- Command produces a valid, restorable gzipped dump; retention prunes correctly; scheduler entry registered.
- Feature tests: mock the dump; assert file creation + rotation behavior. `php artisan test` green + pint. `Brief: #141` trailer.

## Suggested PR split
P1 command + config + manual run → P2 schedule + retention → P3 restore helper + observability.

## Provenance
Operator request captured by flower-orchestrator (2026-07-03 overnight run). Formalized to canonical spec by flower-refine (2026-07-03) — the orchestrator note was already well-grounded (cites the Herd-TCP mysqldump gotcha); no open questions, sensible defaults → ready to plan.

Recent/key trace events:
[1] participant_joined flower-orchestrator: (no body)
[2] note_added flower-orchestrator: **Operator request (2026-07-03, heading to sleep):** build an automated DB backup flow for flower as a safety net. A manual 326MB dump was taken today at `~/flower-db-backups/flower-20260703-083107.sql.gz`.

## Goal
Scheduled, automated, rotated MySQL backup of the flower DB with a documented restore path.

## Requirements
- **Command** `php artisan flower:backup-db` — mysqldump over TCP (`-h 127.0.0.1 -P 3306 --single-transaction --quick --no-tablespaces`, per CLAUDE.md's Herd-TCP note), gzip to a timestamped file.
- **Location:** configurable durable dir OUTSIDE the repo (default `~/flower-db-backups`), via `config/flower.php`, env-overridable.
- **Schedule:** register in the Laravel scheduler (already runs as a Solo `schedule:work` command) at a configurable cadence (default daily).
- **Retention/rotation:** keep N most-recent backups (configurable, default 7), prune older.
- **Restore:** document the restore path (`gunzip | mysql`); optionally a guarded `flower:restore-db --file=` helper.
- **Config-as-data:** knobs in `config/flower.php` (dir, cadence, retention, mysqldump flags), env-overridable.
- **Observability (optional):** last-run timestamp + size into a storage metric / `recall_health` so a stale/failed backup is visible.

## Verify
Command produces a valid, restorable gzipped dump; retention prunes correctly; scheduler entry registered; feature tests (mock the dump; assert file + rotation behavior); pint clean; `php artisan test` green.

## Suggested PR split
P1 command + config + manual run → P2 schedule + retention → P3 restore helper + observability.

Created by flower-orchestrator during the autonomous overnight run. Recommend a quick refine/plan pass before dispatch.
[3] status_change flower-orchestrator: (no body)
[4] participant_joined flower-refine: (no body)
[5] plan_proposed flower-refine: ## Goal
Scheduled, automated, rotated MySQL backup of the flower DB with a documented restore path — a safety net. (Context: a manual 326MB dump was taken 2026-07-03 at `~/flower-db-backups/flower-20260703-083107.sql.gz`.)

## Requirements
- **Command `php artisan flower:backup-db`** — `mysqldump` over TCP (`-h 127.0.0.1 -P 3306 --single-transaction --quick --no-tablespaces`, per CLAUDE.md's Herd-TCP note; the default unix socket `/tmp/mysql.sock` does NOT exist under Herd, so socket-default dumps fail). Pipe to gzip → a timestamped file.
- **Location:** configurable durable dir OUTSIDE the repo (default `~/flower-db-backups`), via `config/flower.php`, env-overridable.
- **Schedule:** register in the Laravel scheduler (already runs as the Solo `schedule:work` command on MAIN) at a configurable cadence (default daily).
- **Retention/rotation:** keep N most-recent backups (configurable, default 7); prune older.
- **Restore:** document the restore path (`gunzip | mysql` over TCP); optionally a guarded `flower:restore-db --file=` helper (must be confirm-guarded — it overwrites the live DB).
- **Config-as-data:** knobs in `config/flower.php` (dir, cadence, retention, mysqldump flags), all env-overridable.
- **Observability (optional):** last-run timestamp + size into a storage metric / `recall_health` so a stale or failed backup is visible.

## Verify / acceptance
- Command produces a valid, restorable gzipped dump; retention prunes correctly; scheduler entry registered.
- Feature tests: mock the dump; assert file creation + rotation behavior. `php artisan test` green + pint. `Brief: #141` trailer.

## Suggested PR split
P1 command + config + manual run → P2 schedule + retention → P3 restore helper + observability.

## Provenance
Operator request captured by flower-orchestrator (2026-07-03 overnight run). Formalized to canonical spec by flower-refine (2026-07-03) — the orchestrator note was already well-grounded (cites the Herd-TCP mysqldump gotcha); no open questions, sensible defaults → ready to plan.
[6] status_change flower-refine: (no body)
[7] link_added flower-refine: (no body)

Recommended linked context:
{
    "todos": [],
    "scratchpads": [
        {
            "id": 375,
            "solo_scratchpad_id": "1067",
            "name": "flower-refine — reset handoff (2026-07-03 #2)",
            "archived": false,
            "revision": 2
        }
    ]
}

Execution notes:
- Treat the brief as the source of truth.
- Keep work scoped to this dispatch request.
- Use brief_append / brief_update_status when reporting material progress; as your final dispatched-worker step, call brief_dispatch_complete with dispatch_request_id (or brief_id) and actor_ref.
- Codex workers should verify mutating Flower tools with tool_search query `brief_append brief_dispatch_complete flower_feedback` (limit 20) when tool availability is in doubt; report raw SEE/LOAD vs NOT visible instead of silently using local fallbacks.
- Add a git commit trailer `Brief: #141` to every commit for this brief so flower can exact-link commits back to the brief.