migrate.yml

# Applies the Alembic revisions in this checkout to a hosted database.
#
# Until this existed, nothing in the repository could reach Supabase at all.
# The schema was applied by a maintainer running `alembic upgrade head` from a
# laptop, which RELEASING.md's gate table admits in its own words: gate 4
# ("migrations up, down and `alembic check`, against real Postgres") is `hard`,
# enforced by tests/test_migrations.py, while gate 5 ("migration applied to UA
# before production") is `procedure` - a person is trusted to remember. This
# workflow is gate 5 becoming hard.
#
# WHAT STAYS A DECISION. Not the typing - the timing. RELEASING.md 8c requires
# expand-and-contract across two releases because the previous release is still
# serving traffic during a rollout, so a migration that drops a column breaks
# it. No workflow can know when that is safe. Production is therefore dispatch
# only and gated on the `production` environment's reviewers; what has been
# removed is the hand-typed connection string, not the judgement.
#
# UA IS NOT OPTIONAL ON THE WAY TO PRODUCTION. Dispatching production runs the
# UA leg first, in the same run, and the production job refuses to start unless
# UA really applied - not "was configured", not "was skipped politely".
# RELEASING.md 3 says "applied to UA first, always"; this is that sentence with
# teeth. Re-applying to UA is free when it is already there: `upgrade head` on
# a database at head is a no-op.
#
# WHICH CONNECTION STRING: SESSION MODE, PORT 5432. Not the transaction
# pooler, and - from a hosted runner - not the direct endpoint either. Three
# options that all look interchangeable in the dashboard:
#
#   db.<ref>.supabase.co:5432          direct. The best target for a
#                                      migration, and IPv6-only unless the
#                                      project buys the IPv4 add-on. GitHub's
#                                      hosted runners are IPv4-only, so this
#                                      works from a laptop and times out here.
#   aws-<region>.pooler...:5432        Supavisor session mode. IPv4, and one
#                                      backend per connection for its whole
#                                      life. THIS ONE.
#   aws-<region>.pooler...:6543        Supavisor transaction mode. IPv4, and
#                                      a different backend per transaction -
#                                      so `CREATE TABLE`, `ALTER TABLE` and
#                                      Alembic's advisory lock stop sharing a
#                                      session. Right for the running app
#                                      (backend/tests/test_pooler.py), wrong
#                                      here.
#
# The property a migration needs is one session that stays put, which session
# mode has and transaction mode does not; IPv4 is what makes it reachable from
# here at all. That is why these settings are *_MIGRATION_DATABASE_URL rather
# than the DATABASE_URL Fly holds - two different values, both correct for
# their own job, the same reasoning that gave the report-photo credentials
# their R2_PHOTO_ prefix (LAUNCH_CHECKLIST.md 1.7).
#
# check_schema_drift.py --url-only runs before every upgrade and refuses the
# two wrong ones by name, because both fail far from their cause: port 6543
# fails intermittently once an endpoint is warm, and a URL without `+psycopg`
# fails as an import error about a driver nobody chose.
#
# REPOSITORY SECRETS, HELD BY A JOB IN A PROTECTED ENVIRONMENT - not
# environment secrets. Environment protection rules gate the job either way,
# so the reviewer requirement is unaffected, and settings-configured.yml resolves
# the contexts with no `environment:` set, so an environment-scoped secret
# would be invisible to the one job whose purpose is noticing it had gone.
# publish-vector-data.yml already holds the R2 credentials this way.
#
# WHAT THIS IS NOT: the Supabase management API, and not the `apply_migration`
# tool an agent session has sitting in front of it. Those write Supabase's own
# `supabase_migrations.schema_migrations` ledger rather than Alembic's
# `alembic_version`, which would leave two ledgers disagreeing about what has
# been applied - strictly worse than the person this replaces. #371 rejected
# that route for a second reason as well: it produces the right tables while
# skipping the path UA exists to rehearse.

name: Migrate

on:
  push:
    branches: [main]
    # UA follows main automatically, and only when there is something to
    # apply. A merge that touches no revision needs no run: `upgrade head`
    # would be a no-op, and a workflow that fires constantly to do nothing is
    # one nobody reads the log of.
    paths:
      - "backend/alembic/versions/**"
      - ".github/workflows/migrate.yml"
  workflow_dispatch:
    inputs:
      target:
        description: "Which database. Production runs UA first and then waits for a reviewer."
        type: choice
        options: [ua, production]
        default: ua

# One migration at a time, across both targets. Two concurrent runs would
# contend for the same advisory lock and interleave in the log, and
# cancel-in-progress is wrong here in a way it is not for a preview build:
# cancelling mid-upgrade leaves a partially-applied chain.
concurrency:
  group: migrate
  cancel-in-progress: false

permissions:
  contents: read

jobs:
  ua:
    name: Apply to UA
    runs-on: ubuntu-latest
    timeout-minutes: 15
    outputs:
      applied: ${{ steps.apply.outputs.applied }}
    # A migration touches neither of these, and cannot run without them.
    # app/config.py builds Settings() at import time and gives the Supabase
    # fields no default on purpose, so a job carrying only DATABASE_URL cannot
    # import the module that holds DATABASE_URL - alembic/env.py included.
    # That is what failed on the merge commit of #411, and it failed with the
    # connection string half-printed in a pydantic traceback.
    #
    # Passed here rather than defaulted in app/config.py, because a default
    # there would also apply to the app, where refusing to start without a
    # Supabase project is the documented intent.
    #
    # PLACEHOLDERS RATHER THAN vars.SUPABASE_URL, for two reasons. Wiring real
    # config into a job that never reads it invents a dependency that can then
    # break it - rotate or unset that variable and migrations fail for a
    # reason with nothing to do with migrations. And a workflow reading
    # vars.SUPABASE_* is how .github/tests/test_supabase_keepalive_workflow.py
    # recognises something talking to the Supabase project, which on a
    # schedule would read as a second keepalive. These jobs reach Postgres
    # directly, through DATABASE_URL, and never the project.
    env:
      SUPABASE_URL: "https://migrations-never-call-this.invalid"
      SUPABASE_ANON_KEY: "unused-by-migrations"
    defaults:
      run:
        working-directory: backend

    steps:
      # Skips rather than fails when UA has no database yet, the same way
      # ua.yml skips when no Pages project is configured: an unconfigured UA
      # must not turn every merge to main red. The production job below is
      # what makes sure that politeness cannot become a way past the gate.
      - name: Is there a UA database to migrate
        id: can
        # Runs before the checkout, so it cannot inherit the job's
        # working-directory: backend - that path does not exist yet, and bash
        # fails to start rather than the step failing, which reads as an
        # infrastructure fault rather than a workflow bug. This was the first
        # run's failure (#407 merged red).
        working-directory: .
        run: |
          if [ -z "${{ secrets.UA_MIGRATION_DATABASE_URL }}" ]; then
            echo "::warning::No UA database is configured, so nothing was migrated. See LAUNCH_CHECKLIST.md 5 and #371."
            echo "go=false" >> "$GITHUB_OUTPUT"
          else
            echo "go=true" >> "$GITHUB_OUTPUT"
          fi

      - uses: actions/checkout@v4
        if: steps.can.outputs.go == 'true'

      - uses: actions/setup-python@v5
        if: steps.can.outputs.go == 'true'
        with:
          python-version: "3.14"
          cache: pip
          cache-dependency-path: backend/requirements.txt

      - name: Install dependencies
        if: steps.can.outputs.go == 'true'
        run: pip install -r requirements.txt

      # Before `upgrade head`, never after: a connection string a migration
      # cannot use should stop the run while nothing has been applied.
      - name: Check the connection string is one a migration can use
        if: steps.can.outputs.go == 'true'
        env:
          DATABASE_URL: ${{ secrets.UA_MIGRATION_DATABASE_URL }}
        run: python check_schema_drift.py --label UA --url-only

      # Printed before and after, because "it ran" and "it moved" are
      # different facts and only the second one is evidence.
      - name: Apply
        id: apply
        if: steps.can.outputs.go == 'true'
        env:
          DATABASE_URL: ${{ secrets.UA_MIGRATION_DATABASE_URL }}
        run: |
          echo "Before: $(alembic current 2>&1 | tail -1)"
          alembic upgrade head
          echo "After:  $(alembic current 2>&1 | tail -1)"
          echo "applied=true" >> "$GITHUB_OUTPUT"

      # The same check schema-drift.yml runs on a schedule, run here against
      # the database that was just migrated. Catching a hand-edit at the
      # moment of the next deploy is the whole reason UA is applied to first.
      - name: Confirm UA matches this checkout
        if: steps.can.outputs.go == 'true'
        env:
          DATABASE_URL: ${{ secrets.UA_MIGRATION_DATABASE_URL }}
        run: python check_schema_drift.py --label UA

  production:
    name: Apply to production
    needs: ua
    if: github.event_name == 'workflow_dispatch' && inputs.target == 'production'
    runs-on: ubuntu-latest
    timeout-minutes: 15
    # The reviewer gate. Everything above this line can run unattended; this
    # cannot, and the environment is what makes that structural rather than
    # a convention someone follows.
    environment: production
    # Unused by a migration, required to import anything - see the UA job.
    env:
      SUPABASE_URL: "https://migrations-never-call-this.invalid"
      SUPABASE_ANON_KEY: "unused-by-migrations"
    defaults:
      run:
        working-directory: backend

    steps:
      # Two refusals, both cheap, both guarding a mistake that is expensive
      # and quiet. A dispatch can name any ref, and a migration applied to
      # production from a feature branch is a schema no merged commit
      # describes.
      - name: Refuse anything this should not do
        # Before the checkout, so not in backend/ - see the UA job's gate.
        working-directory: .
        run: |
          if [ "${{ github.ref }}" != "refs/heads/main" ]; then
            echo "::error::Production migrations run from main only; this dispatch names ${{ github.ref }}."
            exit 1
          fi
          if [ "${{ needs.ua.outputs.applied }}" != "true" ]; then
            echo "::error::UA was not migrated in this run, so this would be the first database to see these revisions. RELEASING.md 3: applied to UA first, always. Configure UA_MIGRATION_DATABASE_URL (#371) and dispatch again."
            exit 1
          fi
          if [ -z "${{ secrets.PRODUCTION_MIGRATION_DATABASE_URL }}" ]; then
            echo "::error::No production database is configured. See LAUNCH_CHECKLIST.md 5."
            exit 1
          fi

      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: "3.14"
          cache: pip
          cache-dependency-path: backend/requirements.txt

      - name: Install dependencies
        run: pip install -r requirements.txt

      - name: Check the connection string is one a migration can use
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_MIGRATION_DATABASE_URL }}
        run: python check_schema_drift.py --label production --url-only

      - name: Apply
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_MIGRATION_DATABASE_URL }}
        run: |
          echo "Before: $(alembic current 2>&1 | tail -1)"
          alembic upgrade head
          echo "After:  $(alembic current 2>&1 | tail -1)"

      - name: Confirm production matches this checkout
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_MIGRATION_DATABASE_URL }}
        run: python check_schema_drift.py --label production