Database Backup Drill

name: db-backup-drill
desc: "Backup a SQLite database, verify integrity, rotate old backups, and prove restorability"

defaults:
  db_file: "./app.db"
  backup_dir: "./backups"
  keep: "3"

tasks:
  seed_demo_db:
    desc: "Create a demo database to protect (demo helper)"
    steps:
      - name: seed
        func: shell
        do: |
          rm -f {{ vars.db_file }}
          sqlite3 {{ vars.db_file }} "CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT); \
            INSERT INTO users(email) VALUES ('a@x.io'),('b@x.io'),('c@x.io'),('d@x.io');"
          echo "seeded $(sqlite3 {{ vars.db_file }} 'SELECT COUNT(*) FROM users;') users"

  backup:
    desc: "Take a consistent online backup and verify it end to end"
    steps:
      - name: snapshot
        desc: "Online backup via sqlite's backup API (safe under writes)"
        func: shell
        do: |
          mkdir -p {{ vars.backup_dir }}
          TS=$(date +%Y%m%d-%H%M%S)
          sqlite3 {{ vars.db_file }} ".backup {{ vars.backup_dir }}/app-$TS.db"
          echo "file={{ vars.backup_dir }}/app-$TS.db"
        outputs:
          file: '{{ result.output | regexFind "file=(.+)" }}'

      - name: integrity
        desc: "A backup you have not verified is not a backup"
        func: shell
        do: sqlite3 {{ steps.snapshot.file }} "PRAGMA integrity_check;"
        outputs:
          check: "{{ result.output }}"

      - name: source_rows
        func: shell
        do: sqlite3 {{ vars.db_file }} "SELECT COUNT(*) FROM users;"
        outputs:
          count: "{{ result.output }}"

      - name: restore_drill
        desc: "Restore into a scratch file and compare row counts"
        func: shell
        do: |
          cp {{ steps.snapshot.file }} ./scratch-restore.db
          sqlite3 ./scratch-restore.db "SELECT COUNT(*) FROM users;"
        outputs:
          count: "{{ result.output }}"
        finally:
          - name: drop_scratch
            func: shell
            do: rm -f ./scratch-restore.db

      - name: verify
        func: assert
        args:
          conditions:
            - condition: '{{ eq steps.integrity.check "ok" }}'
              desc: "backup passes PRAGMA integrity_check"
            - condition: '{{ eq steps.source_rows.count steps.restore_drill.count }}'
              desc: "restored row count matches the source"

      - name: rotate
        desc: "Keep only the newest N backups"
        func: shell
        do: |
          cd {{ vars.backup_dir }}
          DOOMED=$(ls -1t app-*.db | tail -n +$(( {{ vars.keep }} + 1 )))
          if [ -n "$DOOMED" ]; then echo "$DOOMED" | xargs rm -v; else echo "nothing to rotate"; fi
          echo "kept=$(ls -1 app-*.db | wc -l | tr -d ' ')"
        outputs:
          kept: '{{ result.output | regexFind "kept=([0-9]+)" }}'

      - name: summary
        func: shell
        do: 'echo "BACKUP_OK file={{ steps.snapshot.file }} rows={{ steps.source_rows.count }} kept={{ steps.rotate.kept }}"'

  verify_latest:
    desc: "Standalone check of the newest backup, with alerting if it is corrupt"
    steps:
      - name: check_latest
        task: _check_latest_inner
        catch:
          - name: alert
            func: shell
            do: 'echo "ALERT: latest backup failed verification - investigate before relying on it"'

  _check_latest_inner:
    steps:
      - name: find_latest
        func: shell
        do: ls -1t {{ vars.backup_dir }}/app-*.db | head -1
        outputs:
          file: "{{ result.output }}"
      - name: integrity
        func: shell
        do: |
          RES=$(sqlite3 {{ steps.find_latest.file }} "PRAGMA integrity_check;" 2>&1 || true)
          echo "result=$RES"
          test "$RES" = "ok"

orchstep run seed_demo_db          # demo database
orchstep run backup                # run it a few times to see rotation
orchstep run verify_latest         # re-check the newest backup any time

BACKUP_OK file=./backups/app-20260611-221856.db rows=4 kept=3
# after corrupting the newest backup file:
ALERT: latest backup failed verification - investigate before relying on it