migrate.yml

name: Migrate database

# The single owner of production schema migrations.
#
# Every production consumer of the database has to be behind this: the API
# Lambdas (deploy-api.yml calls this workflow, then deploys) and apps/web
# (deployed by Vercel's git integration — see the note at the bottom of this
# file, which this workflow cannot order and does not pretend to).
#
# It also runs on its own `push` trigger, through migrate-on-push.yml, so a red
# Test cannot stop it: deploy-api.yml reaches this workflow only behind a gate
# that requires the whole Test suite to be green, while apps/web ships on the
# same push whatever that verdict says.
#
# Kept as its own workflow rather than a step inside deploy-api.yml for three
# reasons:
#
#   1. It must not depend on anything unrelated. As a step it sat behind
#      `pnpm install`, `pnpm --filter "@wraps/api^..." build` and the AWS OIDC
#      handshake — an api build break or an expired role meant the migration
#      silently never ran, while Vercel shipped apps/web on the same commit
#      regardless. Nothing here can fail for a reason that isn't the migration.
#
#   2. Two migrations must never run at once. `concurrency` on the job below
#      serialises every caller repo-wide; the group name is what does the work,
#      so any future caller must reuse it rather than invent its own.
#
#   3. Applying a migration out of band needs a button. 49ddfeaa left production
#      missing 0073_dazzling_puma with no way to apply it except a laptop and a
#      production DATABASE_URL. `workflow_dispatch` is that button.

on:
  workflow_call:
    inputs:
      stage:
        description: 'Stage whose database to migrate'
        required: false
        default: 'production'
        type: string
  workflow_dispatch:
    inputs:
      stage:
        description: 'Stage whose database to migrate'
        required: true
        default: 'production'
        type: choice
        options:
          - production
          - staging

permissions:
  contents: read

jobs:
  migrate:
    # Only runs upstream — forks (self-hosted customers) have no production
    # DATABASE_URL, and self-host migrates through scripts/selfhost/upgrade.ts
    # instead.
    if: github.repository == 'wraps-team/wraps'
    # GitHub-hosted, like the deploy-api jobs: on the shared blacksmith-2vcpu
    # pool this job queued behind a runner shortage and held every deploy
    # behind it. It needs only Node and a database connection.
    runs-on: ubuntu-latest
    # Supplies DATABASE_URL, which is an environment secret on `Production`,
    # not a repository secret. A job without this line gets an empty URL.
    environment: ${{ inputs.stage || 'production' }}
    # A migration that hangs holds the concurrency lock and blocks every deploy
    # behind it, so bound it. Well above the ~2s a normal run takes.
    timeout-minutes: 10

    # Repo-wide serialisation. Concurrency groups are shared across workflows,
    # so this one string is what guarantees a second caller waits rather than
    # running a second `drizzle-kit migrate` against Neon alongside the first.
    #
    # cancel-in-progress: false because a half-applied migration is the worst
    # outcome available. GitHub still cancels an older *pending* run when a
    # newer one queues behind the same group — that is safe here and desirable:
    # the newer commit's migration set is a superset of the older one's.
    concurrency:
      group: db-migrate-${{ inputs.stage || 'production' }}
      cancel-in-progress: false

    steps:
      - name: Checkout code
        uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v4

      - name: Setup pnpm
        uses: pnpm/action-setup@ea17c68df8912ef543352723c149a84f56e3d413 # v6.1.0

      - name: Setup Node.js
        uses: actions/setup-node@49933ea5288caeca8642d1e84afbd3f7d6820020 # v4
        with:
          node-version: '24.x'
          cache: 'pnpm'

      - name: Install dependencies
        run: pnpm install --frozen-lockfile

      # drizzle-kit reads DATABASE_URL from the environment: drizzle.config.ts
      # points dotenv at apps/web/.env.local, which does not exist on a runner,
      # and dotenv does not override an already-set variable anyway.
      - name: Run database migrations
        run: pnpm --filter @wraps/db db:migrate
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      # Every schema-declared index that ships as a `-- NOTE:` pointer in a
      # migration (CONCURRENTLY, out of band) is built here. Without this step
      # the indexes 0086 and 0088 point at are never created: drizzle-kit cannot
      # run CREATE INDEX CONCURRENTLY inside its transaction, so the DDL lives
      # in packages/db/scripts/index-manifest.ts and db:migrate-indexes runs it
      # after the migrations that reference it. Safe on every run —
      # CONCURRENTLY + IF NOT EXISTS, no blocking locks, and the script verifies
      # pg_index.indisvalid so a partial build fails the deploy rather than
      # being skipped forever by IF NOT EXISTS.
      #
      # DATABASE_DIRECT_URL is preferred by resolveDirectDatabaseUrl and must
      # point at the direct (non-pooled) endpoint; a transaction-mode pooler
      # rejects CONCURRENTLY with "cannot run inside a transaction block".
      # Fall back to DATABASE_URL when the direct secret is not provisioned —
      # if that URL is pooled the step fails loudly with the reason, which is
      # the correct outcome: a silently missing index is what this step exists
      # to prevent. Add DATABASE_DIRECT_URL to the Production environment once
      # the direct endpoint is available.
      - name: Create out-of-band indexes
        if: ${{ inputs.stage == 'production' }}
        run: pnpm --filter @wraps/db db:migrate-indexes
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          DATABASE_DIRECT_URL: ${{ secrets.DATABASE_DIRECT_URL }}

# What this workflow does NOT cover, stated plainly so the next person does not
# assume it is closed:
#
# apps/web deploys through Vercel's git integration (evidence: every
# `Production – wraps-web` GitHub deployment is created by vercel[bot], and no
# web deploy workflow has ever existed in this repo). Vercel builds and promotes
# on push, on its own clock, with no dependency on GitHub Actions. Nothing in
# .github/workflows can make it wait.
#
# migrate-on-push.yml starts this workflow on every push to main, so the
# migration no longer waits on the Test verdict. In practice it lands well before
# Vercel promotes — measured on 1cd949d1 (2026-08-19): push 19:35:42Z, migration
# applied 19:37:02Z, web live 19:39:48Z — but that is a margin, not a guarantee.
# A web build that promotes faster than the migration can still read a column
# that is not there yet: on 2026-07-30 the race with no migration step at all
# produced `column "preference_center_theme" does not exist` on /topics.
#
# Closing the race entirely needs a Vercel-side decision (an Ignored Build Step
# that gates on the migration journal, or disabling git auto-deploy on main and
# deploying web from Actions behind this workflow). Both need access this repo
# does not have.