2026-moving-helper/scripts/backup_db.sh

#!/usr/bin/env sh
set -eu

APP_DIR="__APP_DIR__"
DEFAULT_BACKUP_DIR="__BACKUP_DIR__"
ENV_FILE="$APP_DIR/.env"

require_command() {
  if ! command -v "$1" >/dev/null 2>&1; then
    echo "Missing required command: $1" >&2
    exit 1
  fi
}

resolve_path() {
  case "$1" in
    /*) printf '%s\n' "$1" ;;
    *) printf '%s/%s\n' "$APP_DIR" "$1" ;;
  esac
}

if [ ! -f "$ENV_FILE" ]; then
  echo "Deployed .env file not found: $ENV_FILE" >&2
  exit 1
fi

set -a
. "$ENV_FILE"
set +a

require_command sqlite3

if [ -n "${BACKUP_REMOTE:-}" ]; then
  require_command rclone
fi

BACKUP_DIR=${BACKUP_DIR:-$DEFAULT_BACKUP_DIR}
DATA_DIR=${DATA_DIR:-./data}
DB_PATH="$(resolve_path "$DATA_DIR")/app.db"

if [ ! -f "$DB_PATH" ]; then
  echo "Database file not found: $DB_PATH" >&2
  exit 1
fi

mkdir -p "$BACKUP_DIR"
TIMESTAMP=$(date +"%Y%m%d-%H%M%S")
TMP_BACKUP="$BACKUP_DIR/.app-$TIMESTAMP.db.tmp"
FINAL_BACKUP="$BACKUP_DIR/app-$TIMESTAMP.db"

cleanup() {
  rm -f "$TMP_BACKUP"
}

trap cleanup EXIT INT TERM

# Prefer sqlite3 .backup so the snapshot stays transactionally consistent without
# stopping the running container or racing with SQLite writes.
sqlite3 "$DB_PATH" <<EOF
.timeout 5000
.backup $TMP_BACKUP
EOF

mv "$TMP_BACKUP" "$FINAL_BACKUP"
trap - EXIT INT TERM

count=0
for backup_file in $(find "$BACKUP_DIR" -maxdepth 1 -type f -name 'app-*.db' | sort -r); do
  count=$((count + 1))
  if [ "$count" -gt 5 ]; then
    rm -f "$backup_file"
  fi
done

if [ -n "${BACKUP_REMOTE:-}" ]; then
  remote_target=${BACKUP_REMOTE%/}/$(basename "$FINAL_BACKUP")
  rclone copyto "$FINAL_BACKUP" "$remote_target"
  echo "Backup uploaded to remote: $remote_target"
else
  echo "BACKUP_REMOTE is empty; skipping remote upload"
fi

echo "Backup created: $FINAL_BACKUP"