chatops-migrate.yml

name: ChatOps Migration Deployment

on:
  issue_comment:
    types: [created]

env:
  BYTEBASE_URL: https://demo.bytebase.com
  BYTEBASE_SERVICE_ACCOUNT: api@service.bytebase.com # set service account via environment variable
  BYTEBASE_SERVICE_ACCOUNT_SECRET: ${{ secrets.BYTEBASE_SERVICE_ACCOUNT_SECRET }} # set service account secret via environment variable
  BYTEBASE_PROJECT: "projects/hr"
  FILE_PATTERN: "migrations-semver/*.sql"

jobs:
  parse-command:
    if: github.event.issue.pull_request && startsWith(github.event.comment.body, '/migrate')
    runs-on: ubuntu-latest
    outputs:
      environment: ${{ steps.parse.outputs.environment }}
      valid-command: ${{ steps.parse.outputs.valid-command }}
      stage: ${{ steps.parse.outputs.stage }}
      targets: ${{ steps.parse.outputs.targets }}
    steps:
      - name: Write command config
        # write a bytebase-action-config.yaml file to runner temp directory
        run: |
          cat <<EOF > ${{ runner.temp }}/bytebase-action-config.yaml
          test:
            stage: environments/test
            targets:
              - instances/test-sample-instance/databases/hr_test
          prod:
            stage: environments/prod
            targets:
              - instances/prod-sample-instance/databases/hr_prod
          EOF
      - name: Parse migrate command
        id: parse
        run: |
          COMMENT="${{ github.event.comment.body }}"
          echo "Comment: $COMMENT"
          
          CONFIG_FILE="${{ runner.temp }}/bytebase-action-config.yaml"
          
          # Get list of valid environments from config
          VALID_ENVS=$(yq eval 'keys | join(", ")' "$CONFIG_FILE")
          echo "valid-envs=$VALID_ENVS" >> $GITHUB_OUTPUT

          # Extract environment from "/migrate <environment>"
          if [[ $COMMENT =~ ^/migrate[[:space:]]+([a-zA-Z]+) ]]; then
            ENVIRONMENT="${BASH_REMATCH[1]}"
            echo "Parsed environment: $ENVIRONMENT"
            
            # Check if environment exists in config using yq
            if yq eval "has(\"$ENVIRONMENT\")" "$CONFIG_FILE" | grep -q true; then
              echo "environment=$ENVIRONMENT" >> $GITHUB_OUTPUT
              echo "valid-command=true" >> $GITHUB_OUTPUT
              
              # Extract stage and targets from config for the environment
              STAGE=$(yq eval ".$ENVIRONMENT.stage" "$CONFIG_FILE")
              TARGETS=$(yq eval ".$ENVIRONMENT.targets | join(\",\")" "$CONFIG_FILE")
              
              echo "stage=$STAGE" >> $GITHUB_OUTPUT
              echo "targets=$TARGETS" >> $GITHUB_OUTPUT
              echo "✅ Valid environment: $ENVIRONMENT"
              echo "   Stage: $STAGE"
              echo "   Targets: $TARGETS"
            else
              echo "valid-command=false" >> $GITHUB_OUTPUT
              echo "❌ Invalid environment: $ENVIRONMENT"
              echo "   Valid environments are: $VALID_ENVS"
            fi
          else
            echo "valid-command=false" >> $GITHUB_OUTPUT
            echo "❌ Invalid command format"
          fi

      - name: Add reaction to comment
        if: steps.parse.outputs.valid-command == 'true'
        uses: peter-evans/create-or-update-comment@v3
        with:
          comment-id: ${{ github.event.comment.id }}
          reactions: rocket

      - name: Comment on invalid command
        if: steps.parse.outputs.valid-command == 'false'
        uses: peter-evans/create-or-update-comment@v3
        with:
          issue-number: ${{ github.event.issue.number }}
          body: |
            ❌ Invalid migrate command. 

            **Usage:** `/migrate <environment>`  
            **Valid environments:** `${{ steps.parse.outputs.valid-envs }}`

            **Example:** `/migrate test`
          reactions: confused

  deploy:
    needs: parse-command
    if: needs.parse-command.outputs.valid-command == 'true'
    runs-on: ubuntu-latest
    environment: ${{ needs.parse-command.outputs.environment }}
    container:
      image: bytebase/bytebase-action:latest
    steps:
      - name: Checkout PR merge commit
        uses: actions/checkout@v4
        with:
          ref: refs/pull/${{ github.event.issue.number }}/merge

      - name: Comment deployment started
        id: deployment-comment
        uses: peter-evans/create-or-update-comment@v3
        with:
          issue-number: ${{ github.event.issue.number }}
          body: |
            🚀 **Migration deployment starting**

            **Environment:** `${{ needs.parse-command.outputs.environment }}`  
            **PR:** #${{ github.event.issue.number }}
            **Triggered by:** @${{ github.event.comment.user.login }}

            Creating rollout plan...

            Please check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.

      - name: Create rollout plan
        id: create-rollout
        env:
          BYTEBASE_TARGETS: ${{ needs.parse-command.outputs.targets }}
          BYTEBASE_OUTPUT: ${{ runner.temp }}/bytebase-metadata.json
        run: |
          echo "Creating rollout plan for ${{ needs.parse-command.outputs.environment }}..."

          bytebase-action rollout \
            --url=${{ env.BYTEBASE_URL }} \
            --project=${{ env.BYTEBASE_PROJECT }} \
            --file-pattern=${{ env.FILE_PATTERN }} \
            --targets=${{ env.BYTEBASE_TARGETS }} \
            --output=${{ env.BYTEBASE_OUTPUT }}

          PLAN=$(jq -r .plan ${{ runner.temp }}/bytebase-metadata.json)
          echo "plan=$PLAN" >> $GITHUB_OUTPUT

      - name: Extract rollout metadata
        id: extract-metadata
        run: |
          # Read the metadata file
          METADATA_FILE="${{ runner.temp }}/bytebase-metadata.json"
          
          # Extract release, plan, and rollout from metadata
          RELEASE=$(jq -r .release "$METADATA_FILE")
          PLAN=$(jq -r .plan "$METADATA_FILE")
          ROLLOUT=$(jq -r .rollout "$METADATA_FILE")
          
          # Trim trailing slash from BYTEBASE_URL if present
          BASE_URL="${{ env.BYTEBASE_URL }}"
          BASE_URL="${BASE_URL%/}"
          
          # Output for use in comment
          echo "release=$RELEASE" >> $GITHUB_OUTPUT
          echo "plan=$PLAN" >> $GITHUB_OUTPUT
          echo "rollout=$ROLLOUT" >> $GITHUB_OUTPUT
          echo "base-url=$BASE_URL" >> $GITHUB_OUTPUT

      - name: Update comment with rollout details
        uses: peter-evans/create-or-update-comment@v3
        with:
          comment-id: ${{ steps.deployment-comment.outputs.comment-id }}
          edit-mode: replace
          body: |
            🚀 **Migration deployment in progress**
            
            **Environment:** `${{ needs.parse-command.outputs.environment }}`
            **PR:** #${{ github.event.issue.number }}
            **Triggered by:** @${{ github.event.comment.user.login }}
            
            **Bytebase Resources:**
            - 📦 **Release:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.release }}
            - 📝 **Plan:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.plan }}
            - 🚀 **Rollout:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.rollout }}
            
            Executing database changes...

            Please check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.

      - name: Execute rollout
        env:
          BYTEBASE_TARGET_STAGE: ${{ needs.parse-command.outputs.stage }}
        run: |
          echo "Executing rollout to ${{ needs.parse-command.outputs.environment }}..."

          bytebase-action rollout \
            --url=${{ env.BYTEBASE_URL }} \
            --project=${{ env.BYTEBASE_PROJECT }} \
            --target-stage=${{ env.BYTEBASE_TARGET_STAGE }} \
            --plan=${{ steps.create-rollout.outputs.plan }}

      - name: Comment deployment success
        if: success()
        uses: peter-evans/create-or-update-comment@v3
        with:
          comment-id: ${{ steps.deployment-comment.outputs.comment-id }}
          edit-mode: replace
          body: |
            ✅ **Migration deployment completed successfully**
            
            **Environment:** `${{ needs.parse-command.outputs.environment }}`
            **PR:** #${{ github.event.issue.number }}
            **Triggered by:** @${{ github.event.comment.user.login }}
            
            **Bytebase Resources:**
            - 📦 **Release:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.release }}
            - 📝 **Plan:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.plan }}
            - 🚀 **Rollout:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.rollout }}
            
            Database schema has been successfully updated.

            Please check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.

      - name: Comment deployment failure
        if: failure()
        uses: peter-evans/create-or-update-comment@v3
        with:
          comment-id: ${{ steps.deployment-comment.outputs.comment-id }}
          edit-mode: replace
          body: |
            ❌ **Migration deployment failed**
            
            **Environment:** `${{ needs.parse-command.outputs.environment }}`
            **PR:** #${{ github.event.issue.number }}
            **Triggered by:** @${{ github.event.comment.user.login }}
            
            **Bytebase Resources:**
            - 📦 **Release:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.release }}
            - 📝 **Plan:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.plan }}
            - 🚀 **Rollout:** ${{ steps.extract-metadata.outputs.base-url }}/${{ steps.extract-metadata.outputs.rollout }}
            
            Please check the [workflow run](${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}) for details.