Jyotisha/.gitea/workflows/migrate-staging-database.yml

name: Migrate Staging Database (manual only)

on:
  workflow_dispatch:
    inputs:
      deploy_sha:
        description: Full current staging SHA to migrate
        required: true
        type: string

permissions:
  contents: read
  actions: read

concurrency:
  group: staging-mutation
  cancel-in-progress: false
  queue: max

jobs:
  migrate:
    runs-on: manman-linux
    timeout-minutes: 20
    env:
      GITEA_SHA: ${{ gitea.sha }}
      GITEA_API_URL: ${{ gitea.api_url }}
      GITEA_REPOSITORY: ${{ gitea.repository }}
      GITEA_TOKEN: ${{ secrets.GITEA_TOKEN }}
      REGISTRY_HOST: crpi-d1feco6itet73spp.cn-hongkong.personal.cr.aliyuncs.com
      IMAGE_REPOSITORY: crpi-d1feco6itet73spp.cn-hongkong.personal.cr.aliyuncs.com/copse/jyotisha
      DEPLOY_HOST: ${{ vars.STAGING_HOST }}
      DEPLOY_PORT: ${{ vars.STAGING_PORT }}
      DEPLOY_USER: ${{ vars.STAGING_USER }}
      DEPLOY_PATH: ${{ vars.STAGING_PATH }}
      STAGING_KNOWN_HOSTS: ${{ vars.STAGING_KNOWN_HOSTS }}
    steps:
      - name: Validate current staging revision and successful gate
        id: revision
        env:
          DEPLOY_SHA: ${{ inputs.deploy_sha }}
        run: |
          set -euo pipefail
          [[ "$DEPLOY_SHA" =~ ^[0-9a-f]{40}$ ]] || { echo "deploy_sha must be a lowercase full commit SHA" >&2; exit 1; }
          staging_head="$(git ls-remote https://git.copse.top/root/Jyotisha.git refs/heads/staging | awk '{print $1}')"
          [[ "$staging_head" == "$DEPLOY_SHA" ]] || { echo "migration requires current staging head" >&2; exit 1; }
          runs="$(curl --fail --silent --show-error \
            --header "Authorization: token $GITEA_TOKEN" \
            "$GITEA_API_URL/repos/$GITEA_REPOSITORY/actions/runs?head_sha=$DEPLOY_SHA&branch=staging&event=push&status=success&limit=100")"
          selected_run="$(jq -cer --arg sha "$DEPLOY_SHA" '
            [.workflow_runs[] | select(
              (.path | split("@")[0] | endswith("backend-quality-gate.yml")) and
              .head_sha == $sha and .head_branch == "staging" and
              .event == "push" and .conclusion == "success"
            )] | sort_by(.id) | reverse | first
          ' <<<"$runs")"
          gate_run_id="$(jq -er '.id' <<<"$selected_run")"
          gate_run_attempt="$(jq -er '.run_attempt // 0' <<<"$selected_run")"
          [[ "$gate_run_id" =~ ^[0-9]+$ ]]
          [[ "$gate_run_attempt" =~ ^[0-9]+$ ]]
          {
            echo "sha=$DEPLOY_SHA"
            echo "gate_run_id=$gate_run_id"
            echo "gate_run_attempt=$gate_run_attempt"
          } >>"$GITHUB_OUTPUT"

      - name: Checkout trusted main controller
        env:
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
        run: |
          set -euo pipefail
          git init .
          git remote remove origin 2>/dev/null || true
          git remote add origin https://git.copse.top/root/Jyotisha.git
          git fetch --no-tags origin main "$DEPLOY_SHA"
          git checkout --detach --force origin/main
          git merge-base --is-ancestor "$DEPLOY_SHA" HEAD || { echo "staging revision is not in trusted main history" >&2; exit 1; }

      - name: Prepare pinned Node tooling
        env:
          NODE_TOOL_SOURCE_IMAGE: swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/library/node:22-bookworm-slim@sha256:ef343465b6a14bbdf2ab52f6e100ec0659a792464fcf72c462370d88b3df909c
          NODE_TOOL_IMAGE: node:22-bookworm-slim
        run: |
          set -euo pipefail
          if ! docker image inspect "$NODE_TOOL_SOURCE_IMAGE" >/dev/null 2>&1; then
            for attempt in 1 2 3; do
              if timeout 180 docker pull "$NODE_TOOL_SOURCE_IMAGE"; then
                break
              fi
              if [ "$attempt" -eq 3 ]; then
                echo "Failed to preload $NODE_TOOL_IMAGE after $attempt attempts" >&2
                exit 1
              fi
              sleep $((attempt * 15))
            done
          fi
          docker tag "$NODE_TOOL_SOURCE_IMAGE" "$NODE_TOOL_IMAGE"
          docker image inspect "$NODE_TOOL_IMAGE" >/dev/null
          tool_dir="$(mktemp -d "${RUNNER_TEMP:-/tmp}/jyotisha-node-tools.XXXXXX")"
          cat > "$tool_dir/node" <<'EOF'
          #!/usr/bin/env bash
          set -euo pipefail
          workdir="$(pwd -P)"
          exec docker run --rm \
            --user "$(id -u):$(id -g)" \
            --volume "$workdir:$workdir" \
            --workdir "$workdir" \
            --env HOME=/tmp \
            node:22-bookworm-slim "${0##*/}" "$@"
          EOF
          chmod 0755 "$tool_dir/node"
          ln -s node "$tool_dir/npm"
          test -n "${GITHUB_PATH:-}"
          printf '%s\n' "$tool_dir" >> "$GITHUB_PATH"
          export PATH="$tool_dir:$PATH"
          node --version
          npm --version

      - name: Download gate-produced migration manifest
        env:
          GATE_RUN_ID: ${{ steps.revision.outputs.gate_run_id }}
          GATE_RUN_ATTEMPT: ${{ steps.revision.outputs.gate_run_attempt }}
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
        run: |
          set -euo pipefail
          artifact_name="staging-image-manifest-$DEPLOY_SHA-$GATE_RUN_ATTEMPT"
          artifacts="$(curl --fail --silent --show-error \
            --header "Authorization: token $GITEA_TOKEN" \
            "$GITEA_API_URL/repos/$GITEA_REPOSITORY/actions/runs/$GATE_RUN_ID/artifacts?name=$artifact_name")"
          artifact_id="$(jq -er --arg name "$artifact_name" '[.artifacts[] | select(.name == $name and .expired == false)] | first | .id' <<<"$artifacts")"
          [[ "$artifact_id" =~ ^[0-9]+$ ]]
          install -d -m 700 artifacts/staging-image
          curl --fail --silent --show-error --location \
            --header "Authorization: token $GITEA_TOKEN" \
            "$GITEA_API_URL/repos/$GITEA_REPOSITORY/actions/artifacts/$artifact_id/zip" \
            --output "${RUNNER_TEMP}/staging-image-manifest.zip"
          unzip -q "${RUNNER_TEMP}/staging-image-manifest.zip" -d artifacts/staging-image
          [[ -f artifacts/staging-image/manifest.env ]]

      - name: Validate digest-pinned migration image
        id: image
        env:
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
        run: |
          set -euo pipefail
          node frontend/scripts/staging-image-manifest.mjs \
            artifacts/staging-image/manifest.env "$DEPLOY_SHA" "$IMAGE_REPOSITORY" >>"$GITHUB_OUTPUT"

      - name: Apply digest-pinned migration under host lock
        env:
          SSH_PRIVATE_KEY: ${{ secrets.STAGING_SSH_PRIVATE_KEY }}
          REGISTRY_USERNAME: ${{ secrets.REGISTRY_USERNAME }}
          REGISTRY_PASSWORD: ${{ secrets.REGISTRY_PASSWORD }}
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
          WEB_IMAGE: ${{ steps.image.outputs.web_image }}
        run: |
          set -euo pipefail
          ssh_root="${RUNNER_TEMP}/staging-migration-ssh"
          key_path="$ssh_root/id_ed25519"
          known_hosts_path="$ssh_root/known_hosts"
          incoming=""
          install -m 700 -d "$ssh_root"
          printf '%s\n' "$SSH_PRIVATE_KEY" | tr -d '\r' > "$key_path"
          printf '%s\n' "$STAGING_KNOWN_HOSTS" | tr -d '\r' > "$known_hosts_path"
          chmod 600 "$key_path" "$known_hosts_path"
          ssh_options=(-i "$key_path" -p "$DEPLOY_PORT" -o BatchMode=yes -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o "UserKnownHostsFile=$known_hosts_path")
          remote="$DEPLOY_USER@$DEPLOY_HOST"
          require_current_staging_head() {
            current_head="$(git ls-remote https://git.copse.top/root/Jyotisha.git refs/heads/staging | awk '{print $1}')"
            [[ "$current_head" == "$DEPLOY_SHA" ]] || { echo "staging advanced during migration; refusing stale mutation" >&2; exit 1; }
          }
          cleanup() {
            if [[ -n "$incoming" ]]; then
              ssh "${ssh_options[@]}" "$remote" "sudo -n docker --config '$incoming/.docker' logout '$REGISTRY_HOST' >/dev/null 2>&1 || true; sudo -n rm -rf -- '$incoming'" >/dev/null 2>&1 || true
            fi
            rm -rf -- "$ssh_root"
          }
          trap cleanup EXIT
          incoming="$(ssh "${ssh_options[@]}" "$remote" "mktemp -d /tmp/jyotisha-staging.XXXXXXXXXX")"
          [[ "$incoming" == /tmp/jyotisha-staging.* ]]
          ssh "${ssh_options[@]}" "$remote" "install -d -m 700 '$incoming/.docker'"
          tar -cf "${RUNNER_TEMP}/deploy.tar" deploy
          scp -i "$key_path" -P "$DEPLOY_PORT" -o BatchMode=yes -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o "UserKnownHostsFile=$known_hosts_path" "${RUNNER_TEMP}/deploy.tar" "$remote:$incoming/deploy.tar"
          ssh "${ssh_options[@]}" "$remote" "tar -xf '$incoming/deploy.tar' -C '$incoming' && rm -f -- '$incoming/deploy.tar'"
          previous_sha="$(ssh "${ssh_options[@]}" "$remote" "state='$DEPLOY_PATH/.state/deployed-revision'; if [ -f \"\$state\" ]; then cat \"\$state\"; else id=\$(sudo -n docker ps -aq --filter 'label=com.docker.compose.project=jyotisha-staging' --filter 'label=com.docker.compose.service=web' | head -n 1); if [ -n \"\$id\" ]; then sudo -n docker inspect --format '{{range .Config.Env}}{{println .}}{{end}}' \"\$id\" | sed -n 's/^GITHUB_SHA=//p' | head -n 1; else printf not-deployed; fi; fi")"
          [[ "$previous_sha" == not-deployed || "$previous_sha" =~ ^[0-9a-f]{40}$ ]] || exit 1
          forward_verified=false
          if [[ "$previous_sha" != not-deployed && "$previous_sha" != "$DEPLOY_SHA" ]]; then
            git cat-file -e "${previous_sha}^{commit}" 2>/dev/null || git fetch origin "$previous_sha"
            git merge-base --is-ancestor "$previous_sha" "$DEPLOY_SHA" || { echo "migration rollback or divergence refused" >&2; exit 1; }
            forward_verified=true
          fi
          require_current_staging_head
          printf '%s' "$REGISTRY_PASSWORD" | ssh "${ssh_options[@]}" "$remote" "sudo -n docker --config '$incoming/.docker' login '$REGISTRY_HOST' --username '$REGISTRY_USERNAME' --password-stdin"
          ssh "${ssh_options[@]}" "$remote" "sudo -n env INCOMING_PATH='$incoming' DEPLOY_PATH='$DEPLOY_PATH' WEB_IMAGE='$WEB_IMAGE' DEPLOY_SHA='$DEPLOY_SHA' EXPECTED_PREVIOUS_SHA='$previous_sha' FORWARD_REVISION_VERIFIED='$forward_verified' DOCKER_CONFIG='$incoming/.docker' DOCKER_BIN='docker' bash '$incoming/deploy/run-staging-migration.sh'"
          require_current_staging_head

      - name: Operator action
        run: echo 'Migration complete. Start Deploy staging manually with this exact SHA.'