309 lines
13 KiB
YAML
309 lines
13 KiB
YAML
name: Den DB Migrate
|
|
|
|
# Applies Drizzle migrations to the production PlanetScale database.
|
|
#
|
|
# Required GitHub Actions secrets (Settings -> Secrets and variables -> Actions):
|
|
# DATABASE_HOST PlanetScale host (e.g. aws.connect.psdb.cloud)
|
|
# DATABASE_USERNAME PlanetScale branch password username
|
|
# DATABASE_PASSWORD PlanetScale branch password
|
|
# DATABASE_NAME PlanetScale database name (drizzle-kit migrate connects over the MySQL protocol and needs it)
|
|
# PLANETSCALE_SERVICE_TOKEN_ID PlanetScale service token id (used to toggle safe migrations)
|
|
# PLANETSCALE_SERVICE_TOKEN PlanetScale service token secret
|
|
#
|
|
# Safe migrations stays enabled on the production branch at all times. This
|
|
# workflow briefly disables it (direct DDL) while applying reviewed migration
|
|
# files, then re-enables it in an always() step — so ad-hoc manual DDL stays
|
|
# blocked outside CI runs.
|
|
#
|
|
# Runs automatically when migration files land on dev, and manually via
|
|
# workflow_dispatch. For a database previously managed with db:push (no
|
|
# __drizzle_migrations table yet), run once manually with baseline=true
|
|
# to record existing migrations as applied without executing them.
|
|
|
|
on:
|
|
push:
|
|
branches:
|
|
- dev
|
|
paths:
|
|
- "ee/packages/den-db/drizzle/**"
|
|
# Also run when this workflow itself changes: a broken migrate run fixed
|
|
# by a workflow-only PR must re-apply pending migrations on merge instead
|
|
# of waiting for a manual dispatch (migrate is idempotent via the journal).
|
|
- ".github/workflows/den-db-migrate.yml"
|
|
schedule:
|
|
# Self-heal: if a push-triggered run fails (workflow bug, transient infra),
|
|
# pending migrations get re-applied on the next tick instead of waiting for
|
|
# a human. The pending-check below makes idle ticks read-only no-ops.
|
|
- cron: "23,53 * * * *"
|
|
workflow_dispatch:
|
|
inputs:
|
|
baseline:
|
|
description: "One-time: mark existing migrations as applied without executing them (for a db previously managed via db:push)"
|
|
type: boolean
|
|
default: false
|
|
baseline_through:
|
|
description: "Baseline through this migration tag (default: latest, e.g. 0020_breezy_siren)"
|
|
type: string
|
|
required: false
|
|
dry_run:
|
|
description: "Print what would happen without applying migrations"
|
|
type: boolean
|
|
default: false
|
|
|
|
permissions:
|
|
contents: read
|
|
|
|
concurrency:
|
|
group: den-db-migrate
|
|
cancel-in-progress: false
|
|
|
|
jobs:
|
|
migrate:
|
|
name: Apply migrations
|
|
if: github.repository == 'different-ai/openwork'
|
|
runs-on: blacksmith-4vcpu-ubuntu-2204
|
|
|
|
env:
|
|
DATABASE_HOST: ${{ secrets.DATABASE_HOST }}
|
|
DATABASE_USERNAME: ${{ secrets.DATABASE_USERNAME }}
|
|
DATABASE_PASSWORD: ${{ secrets.DATABASE_PASSWORD }}
|
|
DATABASE_NAME: ${{ secrets.DATABASE_NAME }}
|
|
PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PLANETSCALE_SERVICE_TOKEN_ID }}
|
|
PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PLANETSCALE_SERVICE_TOKEN }}
|
|
PLANETSCALE_ORG: ben-prologe
|
|
PLANETSCALE_BRANCH: main
|
|
|
|
steps:
|
|
- name: Verify database secrets are configured
|
|
run: |
|
|
set -euo pipefail
|
|
missing=""
|
|
[ -z "$DATABASE_HOST" ] && missing="$missing DATABASE_HOST"
|
|
[ -z "$DATABASE_USERNAME" ] && missing="$missing DATABASE_USERNAME"
|
|
[ -z "$DATABASE_PASSWORD" ] && missing="$missing DATABASE_PASSWORD"
|
|
[ -z "$DATABASE_NAME" ] && missing="$missing DATABASE_NAME"
|
|
[ -z "$PLANETSCALE_SERVICE_TOKEN_ID" ] && missing="$missing PLANETSCALE_SERVICE_TOKEN_ID"
|
|
[ -z "$PLANETSCALE_SERVICE_TOKEN" ] && missing="$missing PLANETSCALE_SERVICE_TOKEN"
|
|
if [ -n "$missing" ]; then
|
|
echo "::error::Missing GitHub Actions secrets:$missing. Add them under Settings -> Secrets and variables -> Actions."
|
|
exit 1
|
|
fi
|
|
echo "Database secrets present."
|
|
|
|
- name: Checkout
|
|
uses: actions/checkout@v6
|
|
|
|
- name: Setup Node
|
|
uses: actions/setup-node@v6
|
|
with:
|
|
node-version: 24
|
|
|
|
- name: Setup pnpm
|
|
uses: pnpm/action-setup@v4
|
|
with:
|
|
version: 11.4.0
|
|
|
|
- name: Install dependencies
|
|
run: pnpm install --filter "@openwork-ee/den-db..."
|
|
|
|
# Read-only gate: compare the drizzle journal against __drizzle_migrations.
|
|
# Scheduled/push runs with nothing pending stop here, so safe migrations
|
|
# are only ever toggled when there is real DDL to apply. workflow_dispatch
|
|
# always proceeds (baseline mode needs to run before the table exists).
|
|
- name: Check for pending migrations
|
|
id: pending
|
|
run: |
|
|
set -euo pipefail
|
|
pending="$(cd ee/packages/den-db && node --input-type=module -e "
|
|
const { readFile } = await import('node:fs/promises');
|
|
const { Client } = await import('@planetscale/database');
|
|
const journal = JSON.parse(await readFile('drizzle/meta/_journal.json', 'utf8'));
|
|
const latest = Math.max(...journal.entries.map((entry) => entry.when));
|
|
const client = new Client({ host: process.env.DATABASE_HOST, username: process.env.DATABASE_USERNAME, password: process.env.DATABASE_PASSWORD });
|
|
let applied = 0;
|
|
try {
|
|
const result = await client.execute('SELECT MAX(created_at) AS latest FROM __drizzle_migrations');
|
|
applied = Number(result.rows[0]?.latest ?? 0);
|
|
} catch {
|
|
applied = 0;
|
|
}
|
|
console.log(latest > applied ? 'true' : 'false');
|
|
")"
|
|
echo "pending=$pending" >> "$GITHUB_OUTPUT"
|
|
echo "Pending migrations: $pending"
|
|
|
|
- name: Setup pscale CLI
|
|
if: steps.pending.outputs.pending == 'true' || github.event_name == 'workflow_dispatch'
|
|
id: setup-pscale
|
|
uses: planetscale/setup-pscale-action@v1
|
|
|
|
- name: Disable safe migrations (allow DDL for this run only)
|
|
if: steps.pending.outputs.pending == 'true' || github.event_name == 'workflow_dispatch'
|
|
run: |
|
|
set -euo pipefail
|
|
pscale_retry() {
|
|
local attempt err_file error_output output status
|
|
for attempt in $(seq 1 5); do
|
|
err_file="$(mktemp)"
|
|
if output="$("$@" 2>"$err_file")"; then
|
|
error_output="$(<"$err_file")"
|
|
rm -f "$err_file"
|
|
if [ -n "$error_output" ]; then
|
|
printf '%s\n' "$error_output" >&2
|
|
fi
|
|
printf '%s\n' "$output"
|
|
return 0
|
|
else
|
|
status=$?
|
|
error_output="$(<"$err_file")"
|
|
rm -f "$err_file"
|
|
if [ -n "$output" ]; then
|
|
printf '%s\n' "$output" >&2
|
|
fi
|
|
if [ -n "$error_output" ]; then
|
|
printf '%s\n' "$error_output" >&2
|
|
fi
|
|
if [ "$attempt" -lt 5 ]; then
|
|
echo "pscale command failed (attempt $attempt/5, exit $status); retrying in 10s..." >&2
|
|
sleep 10
|
|
fi
|
|
fi
|
|
done
|
|
return 1
|
|
}
|
|
# Read the resulting state from the toggle's own response instead of a
|
|
# separate `pscale branch show`: `disable` is idempotent, and it saves a
|
|
# call that needs different API permissions and a different output shape.
|
|
if ! state="$(pscale_retry pscale branch safe-migrations disable "$DATABASE_NAME" "$PLANETSCALE_BRANCH" --org "$PLANETSCALE_ORG" --format json)"; then
|
|
echo "::error::Could not disable safe migrations for $DATABASE_NAME/$PLANETSCALE_BRANCH."
|
|
exit 1
|
|
fi
|
|
if ! jq -e '.safe_migrations == false' >/dev/null <<<"$state"; then
|
|
echo "::error::PlanetScale did not confirm safe migrations were disabled. Response: $state"
|
|
exit 1
|
|
fi
|
|
echo "Safe migrations disabled for the duration of this run."
|
|
|
|
# The toggle propagates to the connection gateways asynchronously and
|
|
# per edge node, so require several consecutive successful DDL probes
|
|
# before proceeding (a single success can still hit a stale node).
|
|
consecutive=0
|
|
for attempt in $(seq 1 36); do
|
|
if (cd ee/packages/den-db && node --input-type=module -e "
|
|
const { Client } = await import('@planetscale/database');
|
|
const client = new Client({ host: process.env.DATABASE_HOST, username: process.env.DATABASE_USERNAME, password: process.env.DATABASE_PASSWORD });
|
|
await client.execute('CREATE TABLE IF NOT EXISTS __ddl_probe (id int)');
|
|
await client.execute('DROP TABLE IF EXISTS __ddl_probe');
|
|
" 2>/tmp/ddl-probe-err); then
|
|
consecutive=$((consecutive + 1))
|
|
echo "DDL probe ok (attempt $attempt, consecutive $consecutive)."
|
|
if [ "$consecutive" -ge 3 ]; then
|
|
break
|
|
fi
|
|
sleep 5
|
|
else
|
|
consecutive=0
|
|
echo "DDL still blocked (attempt $attempt): $(tail -1 /tmp/ddl-probe-err 2>/dev/null || true)"
|
|
sleep 10
|
|
fi
|
|
done
|
|
if [ "$consecutive" -lt 3 ]; then
|
|
echo "::error::DDL window did not open within the probe budget after disabling safe migrations."
|
|
exit 1
|
|
fi
|
|
|
|
- name: Baseline existing migrations (one-time)
|
|
if: github.event_name == 'workflow_dispatch' && inputs.baseline
|
|
run: |
|
|
set -euo pipefail
|
|
# Retry only when a stale gateway node still reports DDL as disabled.
|
|
run_with_ddl_retry() {
|
|
local attempt output
|
|
for attempt in $(seq 1 8); do
|
|
if output="$("$@" 2>&1)"; then
|
|
printf '%s\n' "$output"
|
|
return 0
|
|
fi
|
|
printf '%s\n' "$output"
|
|
if ! printf '%s' "$output" | grep -q "direct DDL is disabled"; then
|
|
return 1
|
|
fi
|
|
echo "DDL window not propagated everywhere yet; retrying (attempt $attempt)..."
|
|
sleep 15
|
|
done
|
|
return 1
|
|
}
|
|
extra=""
|
|
if [ -n "${{ inputs.baseline_through }}" ]; then
|
|
extra="--through ${{ inputs.baseline_through }}"
|
|
fi
|
|
if [ "${{ inputs.dry_run }}" = "true" ]; then
|
|
run_with_ddl_retry pnpm --filter @openwork-ee/den-db db:baseline -- $extra
|
|
else
|
|
run_with_ddl_retry pnpm --filter @openwork-ee/den-db db:baseline -- --yes $extra
|
|
fi
|
|
|
|
- name: Apply migrations
|
|
if: >-
|
|
(steps.pending.outputs.pending == 'true' || github.event_name == 'workflow_dispatch') &&
|
|
(github.event_name != 'workflow_dispatch' || !inputs.dry_run)
|
|
run: |
|
|
set -euo pipefail
|
|
run_with_ddl_retry() {
|
|
local attempt output
|
|
for attempt in $(seq 1 8); do
|
|
if output="$("$@" 2>&1)"; then
|
|
printf '%s\n' "$output"
|
|
return 0
|
|
fi
|
|
printf '%s\n' "$output"
|
|
if ! printf '%s' "$output" | grep -q "direct DDL is disabled"; then
|
|
return 1
|
|
fi
|
|
echo "DDL window not propagated everywhere yet; retrying (attempt $attempt)..."
|
|
sleep 15
|
|
done
|
|
return 1
|
|
}
|
|
run_with_ddl_retry pnpm --filter @openwork-ee/den-db db:migrate
|
|
|
|
- name: Re-enable safe migrations
|
|
if: always() && steps.setup-pscale.conclusion == 'success'
|
|
run: |
|
|
set -euo pipefail
|
|
confirmed="false"
|
|
for attempt in $(seq 1 5); do
|
|
echo "Enabling safe migrations (attempt $attempt/5)."
|
|
# Same as the disable step: trust the toggle's own JSON response, and
|
|
# always echo it when it does not confirm, so a future API/CLI change
|
|
# shows up in the log instead of being swallowed.
|
|
if output="$(pscale branch safe-migrations enable "$DATABASE_NAME" "$PLANETSCALE_BRANCH" --org "$PLANETSCALE_ORG" --format json 2>&1)"; then
|
|
if jq -e '.safe_migrations == true' >/dev/null <<<"$output"; then
|
|
confirmed="true"
|
|
echo "PlanetScale confirmed safe migrations are enabled."
|
|
break
|
|
fi
|
|
echo "PlanetScale enable response did not confirm safe migrations: $output" >&2
|
|
else
|
|
status=$?
|
|
if [ -n "$output" ]; then
|
|
printf '%s\n' "$output" >&2
|
|
fi
|
|
echo "pscale enable failed (attempt $attempt/5, exit $status)." >&2
|
|
fi
|
|
|
|
if [ "$attempt" -lt 5 ]; then
|
|
echo "Safe migrations not confirmed enabled yet; retrying in 10s..."
|
|
sleep 10
|
|
fi
|
|
done
|
|
|
|
if [ "$confirmed" != "true" ]; then
|
|
echo "::error::Safe migrations is NOT re-enabled on $DATABASE_NAME/$PLANETSCALE_BRANCH. Re-enable it manually: pscale branch safe-migrations enable $DATABASE_NAME $PLANETSCALE_BRANCH --org $PLANETSCALE_ORG"
|
|
exit 1
|
|
fi
|
|
|
|
- name: Dry run note
|
|
if: github.event_name == 'workflow_dispatch' && inputs.dry_run
|
|
run: echo "Dry run requested -- skipped 'db:migrate'. Baseline output above (if enabled) shows the plan."
|