Files
Jyotisha/.gitea/workflows/migrate-production-database.yml
Jesse_Chen 455917a870
Staging Backend Quality Gate / validate (push) Successful in 10m16s
Staging Backend Quality Gate / publish (push) Successful in 2m8s
ops: keep production SSH sessions alive
2026-08-10 16:17:41 +08:00

364 lines
20 KiB
YAML

name: Migrate Production Database (manual only)
on:
workflow_dispatch:
inputs:
deploy_sha:
description: Full current production release SHA to migrate
required: true
type: string
recovery_reference:
description: Backup or PITR recovery reference; multiple migration files are not atomic as a set
required: true
type: string
recovery_created_at:
description: Recovery point creation time in UTC, exactly YYYY-MM-DDTHH:MM:SSZ and no more than 24 hours old
required: true
type: string
restore_verified:
description: Confirm that this recovery point has passed a restore verification
required: true
default: false
type: boolean
permissions:
contents: read
actions: read
concurrency:
group: production-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.PRODUCTION_HOST }}
DEPLOY_PORT: ${{ vars.PRODUCTION_PORT }}
DEPLOY_USER: ${{ vars.PRODUCTION_USER }}
DEPLOY_PATH: ${{ vars.PRODUCTION_PATH }}
STAGING_URL: ${{ vars.STAGING_URL }}
PRODUCTION_KNOWN_HOSTS: ${{ vars.PRODUCTION_KNOWN_HOSTS }}
steps:
- name: Validate current production revision and successful gates
id: revision
env:
DEPLOY_SHA: ${{ inputs.deploy_sha }}
RECOVERY_REFERENCE: ${{ inputs.recovery_reference }}
RECOVERY_CREATED_AT: ${{ inputs.recovery_created_at }}
RESTORE_VERIFIED: ${{ inputs.restore_verified }}
run: |
set -euo pipefail
[[ "$DEPLOY_SHA" =~ ^[0-9a-f]{40}$ ]] || { echo "deploy_sha must be a lowercase full commit SHA" >&2; exit 1; }
[[ "$STAGING_URL" == "https://staging.jyotisha.chat" ]] || { echo "unexpected staging acceptance URL" >&2; exit 1; }
[[ "$RECOVERY_REFERENCE" =~ ^[A-Za-z0-9][A-Za-z0-9._:/@+-]{0,199}$ ]] || {
echo "recovery_reference must be 1-200 safe reference characters" >&2
exit 1
}
[[ "$RECOVERY_CREATED_AT" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}Z$ ]] || {
echo "recovery_created_at must be UTC in YYYY-MM-DDTHH:MM:SSZ format" >&2
exit 1
}
[[ "$RESTORE_VERIFIED" == "true" ]] || {
echo "restore_verified=true is required for a production schema migration" >&2
exit 1
}
python3 - "$RECOVERY_CREATED_AT" <<'PY'
from datetime import datetime, timedelta, timezone
import sys
try:
created_at = datetime.strptime(sys.argv[1], "%Y-%m-%dT%H:%M:%SZ").replace(tzinfo=timezone.utc)
except ValueError as error:
raise SystemExit(f"invalid recovery_created_at: {error}")
age = datetime.now(timezone.utc) - created_at
if age < timedelta(0) or age > timedelta(hours=24):
raise SystemExit("recovery_created_at must be no more than 24 hours old and not in the future")
PY
echo "Recovery attested: reference=$RECOVERY_REFERENCE created_at=$RECOVERY_CREATED_AT restore_verified=true"
echo "WARNING: migration files run sequentially and are not atomic as a whole; recovery may be required after a partial migration." >&2
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)"
main_head="$(read_ref_sha main)"
[[ "$main_head" == "$DEPLOY_SHA" && "$staging_head" == "$DEPLOY_SHA" ]] || {
echo "production migration requires main and staging to equal deploy_sha" >&2
exit 1
}
[[ "$GITEA_SHA" == "$DEPLOY_SHA" ]] || {
echo "dispatch the production migration workflow from the exact main release SHA" >&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 "no successful exact-SHA staging backend quality gate found" >&2
exit 1
}
release_runs="$(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/actions/runs?head_sha=$DEPLOY_SHA&event=workflow_dispatch&status=success&limit=100")"
jq -e --arg sha "$DEPLOY_SHA" '
any(.workflow_runs[]?;
(.path | split("@")[0] | endswith("release-quality-gate.yml")) and
.head_sha == $sha and .event == "workflow_dispatch" and .conclusion == "success"
)
' <<<"$release_runs" >/dev/null || {
echo "no successful exact-SHA manual release quality gate found" >&2
exit 1
}
observed_staging_sha="$(curl --fail --silent --show-error --connect-timeout 15 --max-time 30 --retry 3 --retry-all-errors \
"$STAGING_URL/api/health" | jq -er '.deployment.gitCommit | select(test("^[0-9a-f]{40}$"))')"
[[ "$observed_staging_sha" == "$DEPLOY_SHA" ]] || {
echo "public staging has not accepted the requested SHA" >&2
exit 1
}
{
echo "sha=$DEPLOY_SHA"
echo "gate_run_id=$gate_run_id"
echo "recovery_reference=$RECOVERY_REFERENCE"
echo "recovery_created_at=$RECOVERY_CREATED_AT"
echo "restore_verified=true"
} >>"$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 production migration artifact bundle")
if sum(entry.file_size for entry in entries) > 3 * 1024 * 1024:
raise SystemExit("production migration 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 production migration artifact path")
if mode and not stat.S_ISREG(mode):
raise SystemExit("unsafe production migration 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-production-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 production migration controller bundle")
if sum(member.size for member in members) > 2 * 1024 * 1024:
raise SystemExit("production migration 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 production migration 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 production schema migration under pinned SSH identity
env:
SSH_PRIVATE_KEY_BASE64: ${{ secrets.PRODUCTION_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 }}
RECOVERY_REFERENCE: ${{ steps.revision.outputs.recovery_reference }}
RECOVERY_CREATED_AT: ${{ steps.revision.outputs.recovery_created_at }}
RESTORE_VERIFIED: ${{ steps.revision.outputs.restore_verified }}
run: |
set -euo pipefail
[[ "$DEPLOY_HOST" == "118.194.235.34" ]]
[[ "$DEPLOY_PORT" =~ ^[1-9][0-9]{0,4}$ ]] && (( DEPLOY_PORT <= 65535 ))
[[ "$DEPLOY_USER" == "deploy" ]]
[[ "$DEPLOY_PATH" == "/opt/jyotisha-production" ]]
test -n "$PRODUCTION_KNOWN_HOSTS"
ssh_root="${RUNNER_TEMP}/production-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' "$PRODUCTION_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 ServerAliveInterval=15 -o ServerAliveCountMax=4 -o StrictHostKeyChecking=yes -o "UserKnownHostsFile=$known_hosts_path")
remote="$DEPLOY_USER@$DEPLOY_HOST"
require_current_release_heads() {
current_staging="$(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_main="$(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/main" |
jq -er 'select(type == "array" and length == 1) | .[0] |
select(.ref == "refs/heads/main") | .object.sha |
select(test("^[0-9a-f]{40}$"))')"
[[ "$current_main" == "$DEPLOY_SHA" && "$current_staging" == "$DEPLOY_SHA" ]] || {
echo "main or staging advanced during production 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-production-migration.XXXXXXXXXX")"
[[ "$incoming" == /tmp/jyotisha-production-migration.* ]]
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'; id=\$(sudo -n docker ps -aq --filter 'label=com.docker.compose.project=jyotisha-production' --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; elif [ -e \"\$state\" ]; then printf state-present-without-container; else printf not-deployed; 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 "production migration rollback or divergence refused" >&2; exit 1; }
forward_verified=true
fi
require_current_release_heads
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' RECOVERY_REFERENCE='$RECOVERY_REFERENCE' RECOVERY_CREATED_AT='$RECOVERY_CREATED_AT' RESTORE_VERIFIED='$RESTORE_VERIFIED' DOCKER_CONFIG='$incoming/.docker' DOCKER_BIN='docker' bash '$incoming/deploy/run-production-migration.sh'"
require_current_release_heads
- name: Operator action
run: echo 'Schema migration complete. Production ETL and application deployment remain separate manual operations.'