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; }
          read_ref_sha() {
            local branch="$1"
            curl --fail --silent --show-error --connect-timeout 15 --max-time 60 --retry 3 --retry-all-errors \
              --header "Authorization: token $GITEA_TOKEN" \
              "$GITEA_API_URL/repos/$GITEA_REPOSITORY/git/refs/heads/$branch" |
              jq -er --arg ref "refs/heads/$branch" '
                select(type == "array" and length == 1) |
                .[0] | select(.ref == $ref) | .object.sha |
                select(test("^[0-9a-f]{40}$"))
              '
          }
          staging_head="$(read_ref_sha staging)"
          [[ "$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_id" =~ ^[0-9]+$ ]]
          {
            echo "sha=$DEPLOY_SHA"
            echo "gate_run_id=$gate_run_id"
          } >>"$GITHUB_OUTPUT"

      - 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 }}
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
        run: |
          set -euo pipefail
          artifact_prefix="staging-image-manifest-$DEPLOY_SHA-"
          artifacts="$(curl --fail --silent --show-error \
            --header "Authorization: token $GITEA_TOKEN" \
            "$GITEA_API_URL/repos/$GITEA_REPOSITORY/actions/runs/$GATE_RUN_ID/artifacts?limit=100")"
          selected_artifact="$(jq -cer --arg prefix "$artifact_prefix" '
            [(.artifacts // [])[]
              | select(.expired == false and (.name | startswith($prefix)))
              | . + {attempt: ((.name | ltrimstr($prefix)) | tonumber?)}
              | select(.attempt != null and .attempt >= 1)
            ] | sort_by(.attempt, .id) | reverse | first
          ' <<<"$artifacts")"
          artifact_name="$(jq -er '.name' <<<"$selected_artifact")"
          artifact_id="$(jq -er '.id' <<<"$selected_artifact")"
          artifact_attempt="${artifact_name#"$artifact_prefix"}"
          [[ "$artifact_name" == "$artifact_prefix"* ]]
          [[ "$artifact_attempt" =~ ^[1-9][0-9]*$ ]]
          [[ "$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"
          python3 - "${RUNNER_TEMP}/staging-image-manifest.zip" artifacts/staging-image <<'PY'
          import pathlib, stat, sys, zipfile
          archive = pathlib.Path(sys.argv[1])
          destination = pathlib.Path(sys.argv[2])
          allowed = {"manifest.env", "controller.tar"}
          with zipfile.ZipFile(archive) as bundle:
              entries = bundle.infolist()
              names = [entry.filename for entry in entries]
              if len(names) != len(set(names)) or set(names) != allowed:
                  raise SystemExit("invalid staging artifact bundle")
              if sum(entry.file_size for entry in entries) > 3 * 1024 * 1024:
                  raise SystemExit("staging artifact bundle is too large")
              for entry in entries:
                  path = pathlib.PurePosixPath(entry.filename)
                  mode = entry.external_attr >> 16
                  if path.is_absolute() or ".." in path.parts or path.name != entry.filename:
                      raise SystemExit("unsafe staging artifact path")
                  if mode and not stat.S_ISREG(mode):
                      raise SystemExit("unsafe staging artifact type")
                  target = destination / entry.filename
                  with bundle.open(entry) as source, target.open("xb") as output:
                      output.write(source.read())
          PY
          [[ -f artifacts/staging-image/manifest.env ]]
          [[ -f artifacts/staging-image/controller.tar ]]

      - name: Validate gate-attested controller and digest-pinned migration image
        id: image
        env:
          DEPLOY_SHA: ${{ steps.revision.outputs.sha }}
        run: |
          set -euo pipefail
          manifest=artifacts/staging-image/manifest.env
          controller_tar=artifacts/staging-image/controller.tar
          [[ "$(wc -l < "$manifest" | tr -d ' ')" == 4 ]]
          manifest_sha="$(awk -F= '$1 == "git_sha" {print $2}' "$manifest")"
          expected_controller_digest="$(awk -F= '$1 == "controller_sha256" {print $2}' "$manifest")"
          [[ "$manifest_sha" == "$DEPLOY_SHA" && "$expected_controller_digest" =~ ^[0-9a-f]{64}$ ]]
          printf '%s  %s\n' "$expected_controller_digest" "$controller_tar" | sha256sum --check --status
          python3 - "$controller_tar" <<'PY'
          import pathlib, sys, tarfile
          archive = pathlib.Path(sys.argv[1])
          required = {"deploy/run-staging-migration.sh", "frontend/scripts/staging-image-manifest.mjs"}
          with tarfile.open(archive, "r:") as bundle:
              members = bundle.getmembers()
              names = [member.name for member in members]
              if len(names) != len(set(names)) or not required.issubset(names):
                  raise SystemExit("invalid staging controller bundle")
              if sum(member.size for member in members) > 2 * 1024 * 1024:
                  raise SystemExit("staging controller bundle is too large")
              for member in members:
                  path = pathlib.PurePosixPath(member.name)
                  if path.is_absolute() or ".." in path.parts or not (member.isdir() or member.isfile()):
                      raise SystemExit("unsafe staging controller bundle")
          PY
          install -d -m 700 artifacts/staging-image/extracted
          tar -xf "$controller_tar" -C artifacts/staging-image/extracted
          node artifacts/staging-image/extracted/frontend/scripts/staging-image-manifest.mjs \
            "$manifest" "$DEPLOY_SHA" "$IMAGE_REPOSITORY" >>"$GITHUB_OUTPUT"

      - name: Apply digest-pinned migration under host lock
        env:
          SSH_PRIVATE_KEY_BASE64: ${{ 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"
          test -n "$SSH_PRIVATE_KEY_BASE64"
          printf '%s' "$SSH_PRIVATE_KEY_BASE64" | base64 --decode > "$key_path"
          printf '%s\n' "$STAGING_KNOWN_HOSTS" | tr -d '\r' > "$known_hosts_path"
          chmod 600 "$key_path" "$known_hosts_path"
          ssh-keygen -y -f "$key_path" >/dev/null
          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="$(curl --fail --silent --show-error --connect-timeout 15 --max-time 60 --retry 3 --retry-all-errors \
              --header "Authorization: token $GITEA_TOKEN" \
              "$GITEA_API_URL/repos/$GITEA_REPOSITORY/git/refs/heads/staging" |
              jq -er 'select(type == "array" and length == 1) | .[0] |
                select(.ref == "refs/heads/staging") | .object.sha |
                select(test("^[0-9a-f]{40}$"))')"
            [[ "$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'"
          scp -i "$key_path" -P "$DEPLOY_PORT" -o BatchMode=yes -o IdentitiesOnly=yes -o StrictHostKeyChecking=yes -o "UserKnownHostsFile=$known_hosts_path" artifacts/staging-image/controller.tar "$remote:$incoming/controller.tar"
          ssh "${ssh_options[@]}" "$remote" "tar -xf '$incoming/controller.tar' -C '$incoming' && rm -f -- '$incoming/controller.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
            comparison="$(curl --fail --silent --show-error --connect-timeout 15 --max-time 60 --retry 3 --retry-all-errors \
              --header "Authorization: token $GITEA_TOKEN" \
              "$GITEA_API_URL/repos/$GITEA_REPOSITORY/compare/$previous_sha...$DEPLOY_SHA")"
            jq -e --arg base "$previous_sha" --arg head "$DEPLOY_SHA" '
              (.commits // []) as $commits |
              def parents($sha): [$commits[] | select(.sha == $sha) | (.parents // [])[] | .sha];
              def reaches($sha; $seen):
                if $sha == $base then true
                elif ($seen | index($sha)) != null then false
                else any(parents($sha)[]; . as $parent | reaches($parent; $seen + [$sha])) end;
              (.total_commits | type) == "number" and
              .total_commits == ($commits | length) and ($commits | length) > 0 and
              ([$commits[].sha] | length == (unique | length)) and reaches($head; [])
            ' <<<"$comparison" >/dev/null || { 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.'