FabledCurator/.forgejo/workflows/baseline.yml

# TEMPORARY — milestone 328 steps 1-2. Delete once the baseline is stamped.
#
# Squashing 87 alembic revisions into one baseline has exactly one dangerous
# failure: the generated baseline does not reproduce the schema the chain
# produced, `alembic stamp` writes a version string anyway (it validates
# NOTHING), and the divergence surfaces on the next real migration against the
# operator's live data.
#
# So this workflow does the comparison in CI, where a pgvector Postgres already
# gets built from the chain on every integration run, and nothing is at risk.
# It answers one question: does `upgrade head` on the collapsed chain produce a
# byte-identical schema to `upgrade head` on the 87-revision chain?
#
# The chain is read from git rather than from the working tree, so this keeps
# working AFTER the old revisions are deleted — `chain_ref` names a commit that
# still has them. That is what makes this the proof for step 1 and the
# pre-flight for step 2, rather than a one-shot script.
#
# While the chain is still present it also autogenerates a candidate baseline
# from the models and prints it. That is a starting point, NOT the answer:
# autogenerate reads SQLAlchemy metadata, and three things here do not live
# there —
#   * CREATE EXTENSION vector           (0001)
#   * CREATE EXTENSION tsm_system_rows  (0004)
#   * the HNSW index on image_record.siglip_embedding, which is raw SQL
#     because alembic's create_index cannot express `USING hnsw (...)`  (0036)
# plus any CHECK constraint or server_default that a migration added without
# the model declaring it. Those must be hand-added, and the diff below is what
# proves none were missed.
name: Alembic baseline

on:
  workflow_dispatch:
    inputs:
      chain_ref:
        description: 'Commit/tag that still carries the full 0001..0087 chain'
        type: string
        default: '0a5bbe8'
      mode:
        description: 'chain = compare against this tree''s migrations; models = compare against a schema built from the MODELS'
        type: string
        default: 'chain'

jobs:
  compare:
    runs-on: python-ci
    container:
      image: git.fabledsword.com/bvandeusen/ci-python:3.14
    env:
      DB_USER: fabledcurator
      DB_PASSWORD: ci_integration
      DB_PORT: "5432"
      DB_NAME: fabledcurator_test
      SECRET_KEY: ci_integration_placeholder
    services:
      postgres:
        image: pgvector/pgvector:pg16
        env:
          POSTGRES_USER: fabledcurator
          POSTGRES_PASSWORD: ci_integration
          POSTGRES_DB: fabledcurator_test
        options: >-
          --health-cmd "pg_isready -U fabledcurator"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@v4
        with:
          # Full history is the point: `chain_ref` is read out of git, so a
          # shallow clone would not have the revisions to compare against.
          fetch-depth: 0

      - name: Resolve the Postgres service and install deps
        run: |
          set -eux
          # Same service-IP dance as ci.yml's integration job; see the long
          # comment there for why the job name must stay separator-free.
          PG=$(docker ps --filter "name=compare" --filter "ancestor=pgvector/pgvector:pg16" -q | head -n1)
          test -n "$PG"
          PG_IP=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' "$PG")
          test -n "$PG_IP"
          echo "PG_CONTAINER=$PG" >> "$GITHUB_ENV"
          echo "DB_HOST=$PG_IP" >> "$GITHUB_ENV"
          for i in $(seq 1 60); do
            (echo > "/dev/tcp/$PG_IP/5432") >/dev/null 2>&1 && break
            sleep 2
          done
          if command -v uv >/dev/null 2>&1; then
            uv pip install --system -r requirements.txt
          else
            pip install -r requirements.txt
          fi

      # DB 1: the 87-revision chain, read out of git at `chain_ref`.
      #
      # A git worktree rather than a checkout, so the current tree — which is
      # what we are testing — is left completely alone.
      - name: Build the schema the OLD chain produces
        env:
          CHAIN_REF: ${{ github.event.inputs.chain_ref }}
        run: |
          set -eux
          docker exec "$PG_CONTAINER" createdb -U fabledcurator fc_chain
          git worktree add /tmp/chain "$CHAIN_REF"
          ls /tmp/chain/alembic/versions/*.py | wc -l
          cd /tmp/chain
          DB_NAME=fc_chain alembic upgrade head
          cd -
          docker exec "$PG_CONTAINER" pg_dump -U fabledcurator --schema-only \
            --no-owner --no-privileges -d fc_chain > chain.sql
          wc -l chain.sql
          # Emit the dump itself, checksummed, for local analysis. Reconciling
          # the models against the deployed schema (#3275) needs the ACTUAL
          # schema, not an inference from a diff — parsing table context out of
          # unified-diff hunks drops every table whose CREATE TABLE line falls
          # outside a hunk, which silently under-reports.
          #
          # base64 + sha256 for the same reason as the candidate: a plain cat
          # of a file this size was truncated mid-line by the runner with the
          # step still green (run 4964).
          set +x
          B64=$(base64 -w 120 chain.sql)
          echo "===== BEGIN CHAIN SCHEMA (base64) ====="
          echo "$B64"
          echo "===== END CHAIN SCHEMA ====="
          echo "chain-sha256: $(sha256sum chain.sql | cut -d' ' -f1)"
          echo "chain-bytes:  $(wc -c < chain.sql)"
          set -x

      # A candidate baseline, autogenerated from the models against an EMPTY
      # database so every table shows up as a create. Printed for a human to
      # finish — it will be missing the three raw-SQL items named at the top.
      #
      # Gated on the TREE, not on a workflow input. A `type: boolean` input
      # read back as `github.event.inputs.generate == 'true'` silently
      # evaluated false on this runner (run 4960 skipped this step entirely
      # with no diagnostic) — the same `github.event.inputs` typing quirk
      # build.yml already works around. The file count is the real question
      # anyway: there is nothing to generate once the chain is collapsed.
      - name: Autogenerate a candidate baseline
        run: |
          set -eux
          if [ "$(ls alembic/versions/*.py | wc -l)" -le 1 ]; then
            echo "already collapsed — nothing to generate"
            exit 0
          fi
          docker exec "$PG_CONTAINER" createdb -U fabledcurator fc_gen
          # Hide the existing revisions so alembic sees an empty history and
          # emits the whole schema rather than a delta.
          mkdir -p /tmp/versions_held
          mv alembic/versions/*.py /tmp/versions_held/ 2>/dev/null || true
          DB_NAME=fc_gen alembic revision --autogenerate -m "baseline" || true
          # Printed rather than uploaded: ci-requirements.md records that this
          # runner cannot do actions/upload-artifact@v4+, and the repo dropped
          # the action entirely in 2026-05, so the job log is the retrieval
          # channel actually proven here.
          #
          # base64, not the raw file. A plain `cat` of the ~33KB candidate was
          # TRUNCATED MID-LINE by the runner on run 4964 — it stopped inside
          # `sa.Column('mime', sa.String(length=128)` and carried straight on
          # to the next traced command, with the step still green. A silent
          # cut in the middle of a schema definition is the worst possible
          # failure here, because the truncated text still looks like a
          # plausible file.
          #
          # base64 at a fixed narrow width gives many short lines instead of
          # few long ones, and — the actual point — a checksum and a line
          # count that make truncation DETECTABLE rather than invisible.
          set +x
          F=$(ls alembic/versions/*.py | head -1)
          B64=$(base64 -w 120 "$F")
          echo "===== BEGIN CANDIDATE BASELINE (base64) ====="
          echo "$B64"
          echo "===== END CANDIDATE BASELINE ====="
          echo "candidate-sha256: $(sha256sum "$F" | cut -d' ' -f1)"
          echo "candidate-bytes:  $(wc -c < "$F")"
          echo "candidate-b64-lines: $(echo "$B64" | wc -l)"
          set -x
          mkdir -p /tmp/candidate
          cp alembic/versions/*.py /tmp/candidate/
          # Put the tree back exactly as it was; this job never mutates state.
          rm -f alembic/versions/*.py
          mv /tmp/versions_held/*.py alembic/versions/ 2>/dev/null || true

      # DB 2: what the CURRENT tree produces.
      #
      # `mode: models` applies the candidate autogenerated from the MODELS
      # instead, which is what answers "do the models describe the schema?" —
      # the question #3275 exists because nobody had ever asked it. Under that
      # mode a clean diff means autogenerate is trustworthy again.
      #
      # The two extensions are created by hand first. They are database
      # objects, not table metadata, so no model can carry them and their
      # absence is not a model defect — it is simply outside what this
      # comparison is asking about.
      - name: Build the schema the CURRENT tree produces
        env:
          MODE: ${{ github.event.inputs.mode }}
        run: |
          set -eux
          docker exec "$PG_CONTAINER" createdb -U fabledcurator fc_base
          if [ "${MODE:-chain}" = "models" ]; then
            docker exec "$PG_CONTAINER" psql -U fabledcurator -d fc_base \
              -c "CREATE EXTENSION IF NOT EXISTS vector" \
              -c "CREATE EXTENSION IF NOT EXISTS tsm_system_rows"
            mkdir -p /tmp/held
            mv alembic/versions/*.py /tmp/held/
            cp /tmp/candidate/*.py alembic/versions/
            # Autogenerate EMITS pgvector.sqlalchemy.vector.VECTOR(...) without
            # importing it, so the file it writes cannot run:
            #   NameError: name 'pgvector' is not defined
            # Observed on run 4988, which is the proof rather than the theory.
            # This is a defect in the GENERATOR, not in the models, so it is
            # repaired here rather than counted as a schema difference — the
            # comparison is about whether the models describe the schema.
            sed -i '0,/^import sqlalchemy as sa$/s//import sqlalchemy as sa\nimport pgvector.sqlalchemy.vector/' alembic/versions/*.py
            grep -n 'import pgvector' alembic/versions/*.py
            # Second generator defect, same class as the missing import.
            #
            # base.py's naming convention includes %(constraint_name)s for ck,
            # which — unlike uq/fk/ix — means the convention is applied even to
            # a CheckConstraint that HAS a name. So a model declaring
            # name="singleton" correctly becomes ck_ml_settings_singleton in
            # the metadata. Autogenerate then writes that RENDERED name into
            # the migration, and running the migration applies the convention a
            # SECOND time: ck_ml_settings_ck_ml_settings_singleton.
            #
            # That is round-tripping damage done by the generator, not a claim
            # the models make, so it is repaired here rather than counted as a
            # schema difference. Undone by removing the ck_<table>_ prefix the
            # convention will re-add — the exact inverse, and it only fires on
            # a name that actually carries its own table's prefix.
            python3 - alembic/versions/*.py <<'PYEOF'
          import re, sys

          table = None
          for path in sys.argv[1:]:
              out = []
              for line in open(path):
                  m = re.search(r"op\.create_table\(\s*[\"']([A-Za-z0-9_]+)[\"']", line)
                  if m:
                      table = m.group(1)
                  if table and "CheckConstraint" in line:
                      prefix = f"ck_{table}_"
                      line = re.sub(
                          r"(name=[\"'])" + re.escape(prefix),
                          r"\1",
                          line,
                      )
                  out.append(line)
              open(path, "w").writelines(out)
          PYEOF
            grep -n 'CheckConstraint' alembic/versions/*.py || true
            ls alembic/versions/*.py
            DB_NAME=fc_base alembic upgrade head
            rm -f alembic/versions/*.py
            mv /tmp/held/*.py alembic/versions/
          else
            ls alembic/versions/*.py | wc -l
            DB_NAME=fc_base alembic upgrade head
          fi
          docker exec "$PG_CONTAINER" pg_dump -U fabledcurator --schema-only \
            --no-owner --no-privileges -d fc_base > baseline.sql
          wc -l baseline.sql

      # The verdict.
      #
      # pg_dump orders dumpable objects by name within type, not by creation
      # order, so two schemas built by different routes are directly
      # comparable. Normalisation is deliberately minimal, because a filter
      # that hides a real difference is the one way this check passes when it
      # should fail — blank lines, SQL comments, trailing whitespace, and:
      #
      #   \restrict / \unrestrict — a per-invocation RANDOM NONCE that newer
      #   pg_dump emits to fence the dump against injection during restore. It
      #   differs on every run by construction, so it is noise by definition,
      #   not a schema difference. Measured on run 4960, the control: two dumps
      #   of the SAME schema came back 1123 lines each and differed on exactly
      #   these two lines and nothing else. That control is what licenses this
      #   filter — it was observed to be the only false positive, rather than
      #   assumed to be one.
      # Column ORDER inside a CREATE TABLE is compared separately from column
      # CONTENT, and only content is fatal.
      #
      # A table built by 87 migrations has its columns in ADD COLUMN order; the
      # same table built in one shot has them in declaration order. That is a
      # real and permanent difference which no baseline can erase — the
      # operator's existing database keeps chain order forever, a fresh install
      # gets model order — so a check that fails on it would never pass and
      # would teach nothing. FC reaches every column through the ORM by name,
      # and `SELECT *` ordering is not depended on anywhere.
      #
      # So the second pass SORTS the column lines within each CREATE TABLE
      # rather than DELETING them. That distinction is the whole point: sorting
      # cannot hide a column that exists on one side only, or one whose type,
      # nullability or default differs — those still land in the diff. A filter
      # could have hidden all three.
      #
      # Both diffs are reported. The ordered one is informational; the
      # order-insensitive one is the verdict.
      - name: Diff
        run: |
          set -eu
          norm() {
            grep -vE '^\s*(--|$)' "$1" \
              | grep -vE '^\\(un)?restrict ' \
              | sed 's/[[:space:]]*$//'
          }
          norm chain.sql > a.txt
          norm baseline.sql > b.txt
          echo "normalised: chain=$(wc -l < a.txt) lines, current=$(wc -l < b.txt) lines"

          sort_table_columns() {
            python3 - "$1" <<'PYEOF'
          import re, sys

          lines = open(sys.argv[1]).read().splitlines()
          out, block = [], None
          for line in lines:
              if block is not None:
                  # ');' on its own closes the CREATE TABLE body.
                  if line.strip() == ");":
                      out.extend(sorted(block))
                      out.append(line)
                      block = None
                  else:
                      # Drop the list comma before sorting. Only the LAST
                      # column lacks one, so keeping it would make every
                      # reordering look like a content change as well — the
                      # comma is punctuation, and carries no schema meaning.
                      block.append(line.rstrip().rstrip(","))
                  continue
              out.append(line)
              if re.match(r"CREATE TABLE .*\($", line):
                  block = []
          if block is not None:  # unterminated body: emit it rather than drop it
              out.extend(block)
          print("\n".join(out))
          PYEOF
          }
          sort_table_columns a.txt > a.sorted.txt
          sort_table_columns b.txt > b.sorted.txt
          test "$(wc -l < a.sorted.txt)" = "$(wc -l < a.txt)"
          test "$(wc -l < b.sorted.txt)" = "$(wc -l < b.txt)"

          if diff -u a.txt b.txt > schema.diff; then
            echo "ORDERED DIFF: identical, column order included."
          else
            echo "ORDERED DIFF: $(grep -cE '^[+-]' schema.diff) changed lines (informational):"
            cat schema.diff
          fi
          echo
          echo "================================================================"
          echo
          if diff -u a.sorted.txt b.sorted.txt > sorted.diff; then
            echo "SCHEMAS MATCH — every difference above is column ORDER alone."
          else
            echo "SCHEMAS DIFFER — $(grep -cE '^[+-]' sorted.diff) changed lines that are NOT ordering:"
            cat sorted.diff
            echo
            echo "The baseline is wrong, not the database. Do not stamp."
            exit 1
          fi