skill: backups

should_trigger:
  - prompt: "We don't really have backups beyond a cron job nobody has checked. Design something real."
    why: "Designing a backup-and-restore program from scratch is the core purpose."
  - prompt: "What RPO and RTO should we be targeting for our main database?"
    why: "Picking defensible RPO/RTO targets and deriving cadence/topology is section 1 of the skill."
  - prompt: "Set up point-in-time recovery for our self-hosted Postgres so we can roll back to before a bad delete."
    why: "PITR strategy (base + WAL replay to a target time) is owned here; engine internals route to postgresdb but the strategy is here."
  - prompt: "Our backups live on the same server as the database — is that actually fine?"
    why: "Non-obvious: sounds like infra trivia, but it is a direct 3-2-1 / blast-radius violation this skill exists to catch."
  - prompt: "Com provo que les còpies de seguretat funcionen de debò abans que passi un desastre?"
    why: "Catalan for 'how do I prove the backups really work before a disaster' — the tested-restore discipline, the load-bearing part of the skill."
  - prompt: "Are our backups any good? Audit what we have."
    why: "Auditing an existing setup against the 3-2-1-1-0 pillars and tested-restore gap is an explicit use case."

should_not_trigger:
  - prompt: "Write the archive_command and tune autovacuum for our Postgres instance."
    route_to: postgresdb
    why: "Engine-specific internals (archive_command, VACUUM tuning) are postgresdb's domain, not cross-engine backup strategy."
  - prompt: "Plan an expand-contract schema migration with a safe rollback path."
    route_to: db-migrations
    why: "Reversible DDL / expand-contract rollout is a schema-migration concern owned by db-migrations, not backup-and-restore."
  - prompt: "Page me when a backup job fails and build a dashboard of backup status."
    route_to: monitoring
    why: "The skill decides WHAT to alert on; wiring the actual alerting/dashboards/paging is monitoring's domain."
  - prompt: "Rotate the KMS key we use for encryption at rest and manage key access."
    route_to: secure-coding
    why: "Key management as a general security control belongs to secure-coding, not backup strategy."
  - prompt: "Set up cross-region read replicas to reduce read latency for European users."
    route_to: postgresdb
    why: "Replication for performance/availability topology is a database concern; a replica is explicitly not a backup."

capability:
  - scenario: "User says: 'We run a nightly mysqldump to /backups on the same box as the MySQL server, no offsite copy, we've never restored it, and we tell customers our RPO is 1 hour.' Audit it."
    must_include:
      - "Flags the single-location violation of 3-2-1 (backup on the same host/blast radius as the source)"
      - "Flags the absence of an offsite and immutable/Object-Lock copy (3-2-1-1-0 gaps)"
      - "Flags that the restore has never been tested — the backup is unverified"
      - "Flags the RPO mismatch: a nightly dump cannot deliver a 1-hour RPO (up to ~24h data loss); mysqldump alone has no PITR"
      - "Proposes a concrete fix: offsite immutable copy with retention >=90 days, separate credentials, and binlog/PITR to meet the RPO"
      - "Proposes a scheduled restore-test cadence (monthly file / quarterly app / annual failover) and recording the actual RTO"

# Evals for the `backups` skill

These cases are routing and capability checks, not an executable test suite — there is no runner to invoke. Read `cases.yaml` against the skill: for each `should_trigger` prompt, confirm the SKILL.md frontmatter `description` and body would plausibly fire the skill; for each `should_not_trigger` prompt, confirm the named `route_to` sibling is the better home and that the description's `NOT ... (that is postgresdb)` boundary holds. For the `capability` case, run the scenario mentally (or hand it to an agent loaded with SKILL.md) and check the response covers every line in `must_include` — the 3-2-1-1-0 gaps, the untested-restore flag, the RPO-vs-cadence mismatch, and the concrete fix with a scheduled restore-test cadence. Treat a missing rubric item as a failing eval and patch the body, not the rubric.

# Engine recipes

Copy-paste starting points per data store. Adapt paths, regions, and retention to your RPO/RTO. Each block ends with the one gotcha that bites in production.

## Self-hosted PostgreSQL — pgBackRest

Current standard: full weekly + differential daily, zstd compression, parallel by disk count, async archiving.

```ini
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-type=s3
repo1-s3-bucket=my-pgbackrest-backups
repo1-s3-region=eu-west-1
compress-type=zst
process-max=4          # tune to DISK count, not CPU — backup is I/O-bound
archive-async=y        # avoids the WAL storm under write load

[main]
pg1-path=/var/lib/postgresql/18/main
```

```bash
pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main --type=full backup       # weekly
pgbackrest --stanza=main --type=diff backup        # daily
pgbackrest --stanza=main check                     # archive + backup reachable
pgbackrest --stanza=main verify                    # re-checksum the repo (the "0")
```

PITR restore to a target time (restore into an ISOLATED target, never over prod):

```bash
pgbackrest --stanza=main --type=time \
  --target="2026-06-02 13:59:00+00" \
  --target-action=promote restore
```

Gotcha: `repo1-retention-full` controls how much WAL is kept. Expire it too aggressively and the chain to your target time is gone — PITR silently fails. Keep WAL at least as long as the oldest full you might restore.

WAL-G is the cloud-native alternative (env-driven, smaller footprint):

```bash
export WALG_S3_PREFIX=s3://my-bucket/wal-g
export AWS_REGION=eu-west-1
wal-g backup-push /var/lib/postgresql/18/main
wal-g backup-fetch /var/lib/postgresql/18/restore LATEST
# then recovery_target_time in postgresql.conf + restore_command = 'wal-g wal-fetch %f %p'
```

## Amazon RDS — PITR via AWS CLI

```bash
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier prod-db \
  --target-db-instance-identifier prod-db-restore-test \
  --restore-time 2026-06-02T13:59:00Z \
  --db-subnet-group-name isolated-restore-subnet
```

Gotcha: this restores into the **same region**. AWS Backup can *copy* a backup cross-region but does **not** support a PITR restore in another region. Retention ceiling is 35 days; for longer, export a manual snapshot.

## MySQL / MariaDB — XtraBackup + binlog

```bash
# Physical hot backup
xtrabackup --backup --target-dir=/backup/base --user=backup --password=...
xtrabackup --prepare --target-dir=/backup/base

# PITR = restore base, then replay binlog to a stop point
mysqlbinlog --stop-datetime="2026-06-02 13:59:00" \
  /var/lib/mysql/binlog.000042 | mysql -u root -p
```

Gotcha: `mysqldump` alone gives no PITR. If you must use it, add `--single-transaction --source-data=2` and ship binlogs separately, or you can only restore to the dump instant. (`--master-data` is the deprecated alias — replaced by `--source-data` since 8.0.26.)

## Redis — RDB + AOF

```conf
# redis.conf — only if Redis is a system of record, not just a cache
save 900 1
appendonly yes
appendfsync everysec   # ~1s RPO; 'always' is durable but slow
```

Gotcha: if Redis is purely a cache, backing it up is usually wasted effort — rebuild from the source of truth instead. Decide which Redis you have first.

## Files / app data — restic to an Object-Lock bucket

```bash
export RESTIC_REPOSITORY=s3:s3.amazonaws.com/my-immutable-backups/app
export RESTIC_PASSWORD_FILE=/etc/restic/pass   # key lives OUTSIDE this bucket
restic init
restic backup /srv/app/data --exclude-file=/etc/restic/excludes
restic check --read-data-subset=10%            # integrity verify
restic restore latest --target /restore/test   # into isolated path
```

Borg equivalent (smaller repos, one exclusive lock per repo):

```bash
borg init --encryption=repokey-blake2 /backup/repo
borg create /backup/repo::'{hostname}-{now}' /srv/app/data
borg check /backup/repo
borg extract /backup/repo::archive-name        # restore
```

Gotcha: both encrypt with AES-256, but the key/password must live in a *different* blast radius than the data. An encrypted backup whose key died with the source is unrecoverable.

## S3 Object Lock (the immutability layer)

```bash
aws s3api create-bucket --bucket my-immutable-backups --object-lock-enabled-for-bucket \
  --region eu-west-1 --create-bucket-configuration LocationConstraint=eu-west-1
aws s3api put-object-lock-configuration --bucket my-immutable-backups \
  --object-lock-configuration '{"ObjectLockEnabled":"ENABLED","Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":90}}}'
```

`COMPLIANCE` mode cannot be shortened or bypassed even by root — that is the point. Use a separate IAM principal for writing backups vs. one (rarely used) for lifecycle administration.

# Restore runbook + scheduled-test discipline

A backup you can't restore under pressure is worthless. Fill this in *before* the disaster, and rehearse it on the cadence below.

## Restore runbook (fill-in template)

```text
SERVICE / DATA STORE: ____________________
RUNBOOK OWNER:        ____________________   LAST REHEARSED: __________

PRECONDITIONS
- [ ] Isolated restore target provisioned (NOT production, separate credentials)
- [ ] Backup source identified: repo / bucket = __________________
- [ ] Decryption key available from: __________ (must NOT be the lost system)
- [ ] Target recovery time / point: __________________

STEPS
1. Provision the isolated target ............... cmd: ______________
2. Pull the base/full backup ................... cmd: ______________
3. Replay change log to target time (PITR) ..... cmd: ______________
4. Bring the store up in isolation ............. cmd: ______________
5. VALIDATE (do not skip):
   - row counts vs. expected ................... query: ___________
   - latest known-good record present ......... query: ___________
   - app smoke test against restored data ..... ______________
6. Cut over (only if this is a real recovery) .. ______________

SIGN-OFF
- Restore succeeded: Y / N
- Data validated correct (not just present): Y / N
- Actual recovery time: __________  (start ____ → serving ____)
- Issues / follow-ups: ______________________________
```

## Scheduled restore-test checklist

Run on the cadence from SKILL.md §6. Tick every box or the test does not count.

```text
[ ] Restore ran in an ISOLATED environment (no prod credentials, no shared bucket write access)
[ ] Restored to a specific point in time (not just "latest"), to exercise PITR
[ ] Ran integrity verify first (restic/borg check, pgbackrest verify)
[ ] Ran a validation query proving data is CORRECT, not merely present
[ ] Recorded the ACTUAL recovery time (see log below)
[ ] Compared actual time against the stated RTO; flagged if RTO is now fiction
[ ] Logged any step that was slower/harder than the runbook claims; updated the runbook
[ ] Torn down the isolated target afterwards (cost + stale-data hygiene)
```

Cadence reminder:
- Monthly: file/single-object/single-table restore.
- Quarterly: full application recovery (app stands up against restored data).
- Annually: full-environment failover in an isolated account/region.

## Actual-RTO log

Keep this table in the repo. The RTO you publish should be the worst recent *measured* number, not an estimate.

| Date | Test type | Target point | Restored OK? | Validated correct? | Actual RTO | Notes |
|------|-----------|--------------|--------------|--------------------|------------|-------|
| 2026-06-01 | file | latest-1d | Y | Y | 00:12 | clean |
| 2026-06-30 | app recovery | 2026-06-30 02:00 | Y | Y | 00:48 | DNS step slow |
| 2026-09-30 | full failover | 2026-09-29 23:00 | Y | Y | 03:41 | RTO target was 2h — fix |

If a row says "Restored OK? N" or "Validated correct? N", that is an incident, not a footnote. Treat it like a production outage you got to schedule.

#!/usr/bin/env bash
# verify.sh — completeness lint for a backup-policy / restore-runbook artifact.
#
# Read-only. It does NOT run, create, or touch any backup. It only greps a
# produced artifact for the five pillars this skill insists on:
#   1. an RPO value           4. immutability (Object Lock / WORM)
#   2. an RTO value           5. a scheduled restore-test cadence + a verification step
#   3. an offsite copy
#
# Usage:
#   scripts/verify.sh <path-to-policy-or-runbook.md>
#
# Exit codes:
#   0  all pillars present, OR no/empty target given (nothing to lint = no false failure)
#   1  the target exists with content but is missing one or more pillars
#   2  the target path was given but does not exist / is unreadable

set -euo pipefail

target="${1:-}"

# No target, or an empty target → nothing to lint. Clean exit, never a false failure.
if [[ -z "${target}" ]]; then
  echo "verify.sh: no artifact path given — nothing to lint (ok)"
  exit 0
fi

if [[ ! -e "${target}" ]]; then
  echo "verify.sh: '${target}' does not exist" >&2
  exit 2
fi

if [[ ! -r "${target}" ]]; then
  echo "verify.sh: '${target}' is not readable" >&2
  exit 2
fi

if [[ ! -s "${target}" ]]; then
  echo "verify.sh: '${target}' is empty — nothing to lint (ok)"
  exit 0
fi

# Case-insensitive presence check helper.
has() { grep -Eiq -- "$1" "${target}"; }

missing=()

# Pillar 1: RPO — the acronym, with a number somewhere near it is ideal but the
# label alone is the minimum signal.
has 'rpo' || missing+=("RPO target (recovery point objective)")

# Pillar 2: RTO.
has 'rto' || missing+=("RTO target (recovery time objective)")

# Pillar 3: an offsite copy.
has 'off[ -]?site|different (region|account)|cross[ -]region' \
  || missing+=("offsite copy (different region/account)")

# Pillar 4: immutability.
has 'immutab|object[ -]?lock|worm' \
  || missing+=("immutability (Object Lock / WORM)")

# Pillar 5a: a scheduled restore-test cadence.
has 'restore[ -](test|drill)|test(ed)? restore|monthly|quarterly|annual' \
  || missing+=("scheduled restore-test cadence")

# Pillar 5b: a verification / integrity step.
has 'verif|integrity|checksum|\bcheck\b|0 (verification )?errors' \
  || missing+=("verification / integrity step")

if [[ ${#missing[@]} -eq 0 ]]; then
  echo "verify.sh: '${target}' covers all five pillars — ok"
  exit 0
fi

echo "verify.sh: '${target}' is missing the following pillar(s):" >&2
for m in "${missing[@]}"; do
  echo "  - ${m}" >&2
done
exit 1