schema-diff.yml

---
name: schema-diff

on:
  pull_request:
    paths:
      - "schemata/**"

permissions:
  contents: read
  pull-requests: write

jobs:
  schema-diff:
    name: Schema Diff
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16.14-alpine
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Checkout
        timeout-minutes: 3
        uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1 # v7.0.1
        with:
          fetch-depth: 0

      - name: Get changed schema files
        id: changed
        run: |
          MERGE_BASE=$(git merge-base "${{ github.event.pull_request.base.sha }}" "${{ github.event.pull_request.head.sha }}")
          echo "merge_base=${MERGE_BASE}" >> "$GITHUB_OUTPUT"

          CHANGED_FILES=$(git diff --name-only "${MERGE_BASE}" HEAD -- 'schemata/*.sql')
          if [ -z "$CHANGED_FILES" ]; then
            echo "No schema files changed"
            echo "has_changes=false" >> "$GITHUB_OUTPUT"
            exit 0
          fi

          echo "has_changes=true" >> "$GITHUB_OUTPUT"
          echo "files<<EOF" >> "$GITHUB_OUTPUT"
          echo "$CHANGED_FILES" >> "$GITHUB_OUTPUT"
          echo "EOF" >> "$GITHUB_OUTPUT"

      - name: Install psqldef
        if: steps.changed.outputs.has_changes == 'true'
        uses: jdx/mise-action@dad1bfd3df957f44999b559dd69dc1671cb4e9ea  # v4.2.1
        with:
          install_args: psqldef

      - name: Create databases
        if: steps.changed.outputs.has_changes == 'true'
        env:
          PGPASSWORD: postgres
        run: |
          while IFS= read -r file; do
            [ -z "$file" ] && continue
            DB_NAME=$(basename "$file" .sql)
            echo "Creating database: ${DB_NAME}"
            createdb -h localhost -U postgres "$DB_NAME"
          done <<< "${{ steps.changed.outputs.files }}"

      - name: Apply base schema (merge base)
        if: steps.changed.outputs.has_changes == 'true'
        env:
          PGPASSWORD: postgres
        run: |
          git checkout "${{ steps.changed.outputs.merge_base }}"

          while IFS= read -r file; do
            [ -z "$file" ] && continue
            DB_NAME=$(basename "$file" .sql)
            # PR でファイルが新規追加された場合、merge base 時点には存在しない。
            # その場合はベーススキーマが「空」であるとみなし (createdb 直後のまま) スキップする。
            if [ ! -f "$file" ]; then
              echo "Skipping base schema for ${DB_NAME}: ${file} is newly added (no base schema)"
              continue
            fi
            echo "Applying base schema for ${DB_NAME}..."
            psqldef -h localhost -U postgres "$DB_NAME" < "$file"
          done <<< "${{ steps.changed.outputs.files }}"

      - name: Generate schema diff (dry-run)
        if: steps.changed.outputs.has_changes == 'true'
        env:
          PGPASSWORD: postgres
        run: |
          git checkout "${{ github.event.pull_request.head.sha }}"

          {
            echo "## Schema Diff (psqldef)"
            echo ""

            while IFS= read -r file; do
              [ -z "$file" ] && continue
              DB_NAME=$(basename "$file" .sql)
              echo "### \`${DB_NAME}\`"
              echo ""
              echo '```sql'
              # --enable-drop 付きで dry-run し、DROP を伴う変更も差分に表示させる
              # (使い捨て DB への dry-run なので適用リスクはない。付けないと DROP が差分から消え、
              #  削除のみの PR が「差分なし」に見えてレビューで見落とす)。
              psqldef -h localhost -U postgres "$DB_NAME" --enable-drop --dry-run < "$file" 2>&1
              echo '```'
              echo ""
            done <<< "${{ steps.changed.outputs.files }}"
          } > /tmp/schema-diff-comment.md

      - name: Comment on PR
        if: steps.changed.outputs.has_changes == 'true'
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            let body = fs.readFileSync('/tmp/schema-diff-comment.md', 'utf8');

            // DROP を伴う破壊的変更が含まれる場合は、マージ前の対応をコメント冒頭に警告する。
            // 実際の SQL 文 (DROP TABLE / INDEX / …) にのみマッチさせ、散文の "drop" を誤検出しない。
            const hasDrop = /\bDROP\s+(TABLE|INDEX|VIEW|MATERIALIZED\s+VIEW|SEQUENCE|SCHEMA|CONSTRAINT|COLUMN|TYPE|FUNCTION|TRIGGER)\b/i.test(body);
            if (hasDrop) {
              const warning =
                '> ⚠️ **この PR には DROP を伴う破壊的変更が含まれます。**\n'
                + '> 通常の migration (自動デプロイ / standalone の既定) は `--enable-drop` を付けないため **DROP は適用されません**。\n'
                + '> **マージ前に** `DB Migration (standalone)` を **`enable_drop` オン** で対象環境ごとに手動実行し、DROP を反映してください。\n\n';
              body = warning + body;
            }

            const { data: comments } = await github.rest.issues.listComments({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
            });

            const marker = 'Schema Diff (psqldef)';
            const botComment = comments.find(c => c.body.includes(marker));

            if (botComment) {
              await github.rest.issues.updateComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                comment_id: botComment.id,
                body,
              });
            } else {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: context.issue.number,
                body,
              });
            }