restore-sanitized-db.yml

name: Restore Sanitized DB

concurrency: stage

on:
  workflow_dispatch:
  workflow_call:
  schedule:
    - cron: '50 1 * * MON' # At 01:50 UTC every Monday

jobs:
  sanitize-db:
    runs-on: ubuntu-latest
    environment: ProductionRO

    env:
      MYSQL_ROOT_PASSWORD: root

    services:
      mysql:
        image: mysql:5.7
        env:
          MYSQL_ROOT_PASSWORD: root
        ports:
          - "3306:3306"
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=10s --health-retries=10

    steps:
      - name: Checkout
        uses: actions/checkout@v3

      - name: Download DB backup
        id: get-db
        uses: ./.github/actions/get-db
        with:
          workflow: backup-db.yml
          zip-password: ${{ secrets.BACKUP_PASSWORD }}

      - name: Load DB
        run: cat ${{ steps.get-db.outputs.db-dump-file }} | /usr/bin/mysql -h 127.0.0.1 -u root -p$MYSQL_ROOT_PASSWORD

      - uses: actions/checkout@v3

      - name: Remove PII
        run: |
          # execute SQL script with PII removal
          cat $GITHUB_WORKSPACE/mysql/sanitize-db.sql | \
             /usr/bin/mysql -h 127.0.0.1 -u root -p$MYSQL_ROOT_PASSWORD --database orbitar_db

      - name: Dump database
        run: |
          mysqldump --host 127.0.0.1 --column-statistics=0 --default-character-set=utf8mb4 --single-transaction \
            --add-drop-database --databases orbitar_db activity_db -u root -p$MYSQL_ROOT_PASSWORD > stage-backup.sql

      - name: Compress database
        env:
          STAGING_BACKUP_PASSWORD: ${{ secrets.STAGING_BACKUP_PASSWORD }}
        run: |
          pwd
          ls -la
          zip -m -P $STAGING_BACKUP_PASSWORD stage-backup.zip stage-backup.sql

      - name: Upload artifact
        uses: actions/upload-artifact@v3
        with:
          name: stage-backup
          path: stage-backup.zip
          if-no-files-found: error

  restore-db:
    needs: sanitize-db
    environment: Staging
    runs-on: [self-hosted, stage]
    steps:
      - uses: actions/checkout@v3

      - name: Download DB backup
        uses: ./.github/actions/get-db
        id: get-db
        with:
          zip-password: ${{ secrets.STAGING_BACKUP_PASSWORD }}

      - name: Stop backend
        working-directory: /orbitar
        run: docker compose -f docker-compose.ssl.local.yml stop backend

      - name: Restore backup
        working-directory: /orbitar
        env:
            MYSQL_ROOT_PASSWORD: ${{ secrets.MYSQL_ROOT_PASSWORD }}
        run: |
          docker compose -f docker-compose.ssl.local.yml exec -T mysql /usr/bin/mysql \
            -u root --password=$MYSQL_ROOT_PASSWORD --default-character-set=utf8mb4 \
            < ${{ steps.get-db.outputs.db-dump-file }}

      - name: Flush Redis cache
        working-directory: /orbitar
        env:
          REDIS_PASSWORD: orbitar
        run: docker compose -f docker-compose.ssl.local.yml exec -e REDISCLI_AUTH=$REDIS_PASSWORD redis redis-cli FLUSHALL

      - name: Restart backend
        working-directory: /orbitar
        run: docker compose -f docker-compose.ssl.local.yml start backend