手動デプロイ + データベース自動管理の CI/CD ワークフロー設計

[issue-123 ブランチにプッシュ]
    │
    ▼
┌─────────────────────────────────────┐
│ GitHub Actions: preview-db-setup    │
│ - DB 作成（linto-issue-123）         │
│ - マイグレーション実行               │
│ - Vercel 環境変数設定                │
└─────────────────────────────────────┘
    │
    ▼
┌─────────────────────────────────────┐
│ 手動: Google Console 設定           │
│ - redirect URI の追加               │
│ - JavaScript 生成元の追加            │
└─────────────────────────────────────┘
    │
    ▼
[Vercel ダッシュボードから手動デプロイ]
    │
    ▼
[issue-123 → develop にマージ]
    │
    ├──▶ GitHub Actions: preview-db-delete
    │    - DB 削除（linto-issue-123）
    │    - Vercel 環境変数削除
    │
    └──▶ GitHub Actions: staging-migrate
         - staging DB にマイグレーション実行

[develop → main にマージ]
    │
    ▼
[何も自動実行されない]
    │
    ▼
[手動で workflow_dispatch からマイグレーション実行]
[手動で Vercel ダッシュボードからデプロイ]

{
  "$schema": "https://openapi.vercel.sh/vercel.json",
  "git": {
    "deploymentEnabled": false
  }
}

# 削除するファイル
rm .github/workflows/preview-deploy.yml

name: Preview DB Setup

on:
  push:
    branches:
      - 'issue-*'

# 環境変数（プロジェクト固有の値はここで定義）
env:
  TURSO_API_TOKEN: ${{ secrets.TURSO_API_TOKEN }}
  # プロジェクト固有の設定（ハードコード）
  VERCEL_PROJECT_NAME: linto-dev
  VERCEL_SCOPE_SLUG: naokiyazawas-projects
  TURSO_STAGING_DB: linto-staging
  TURSO_PREVIEW_GROUP: preview

jobs:
  setup-preview-db:
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - name: Checkout
        uses: actions/checkout@v6

      - name: Setup Node.js
        uses: actions/setup-node@v6
        with:
          node-version: '24'
          cache: 'npm'

      - name: Install dependencies
        run: npm ci

      - name: Install Vercel CLI
        run: npm install -g vercel@latest

      - name: Install Turso CLI
        run: curl -sSfL https://get.tur.so/install.sh | bash

      # ========================================
      # Step 1: Vercel からプロジェクト情報と環境変数を取得
      # ========================================
      - name: Pull Vercel environment
        run: |
          vercel link --yes --token=${{ secrets.VERCEL_TOKEN }}
          vercel env pull .env.local --yes --environment=preview --token=${{ secrets.VERCEL_TOKEN }}
        env:
          VERCEL_ORG_ID: ${{ secrets.VERCEL_ORG_ID }}
          VERCEL_PROJECT_ID: ${{ secrets.VERCEL_PROJECT_ID }}

      # ========================================
      # Step 2: データベース名の生成
      # ========================================
      - name: Generate DB name from branch
        id: db_name
        run: |
          BRANCH="${GITHUB_REF_NAME}"
          # ブランチ名から DB 名を生成（小文字化、英数字とハイフンのみ、32文字以内）
          DB_NAME="linto-$(echo "$BRANCH" | tr '[:upper:]' '[:lower:]' | tr -cd '[:alnum:]-' | cut -c 1-32)"
          echo "name=$DB_NAME" >> $GITHUB_OUTPUT
          echo "Generated DB name: $DB_NAME"

      # ========================================
      # Step 3: データベースの存在確認
      # ========================================
      - name: Check if database exists
        id: db_check
        run: |
          if ~/.turso/turso db show ${{ steps.db_name.outputs.name }} > /dev/null 2>&1; then
            echo "exists=true" >> $GITHUB_OUTPUT
            echo "Database already exists"
          else
            echo "exists=false" >> $GITHUB_OUTPUT
            echo "Database does not exist"
          fi

      # ========================================
      # Step 4: データベースの作成（初回のみ）
      # ========================================
      - name: Create Preview Database
        if: steps.db_check.outputs.exists == 'false'
        run: |
          echo "Creating database from staging..."
          ~/.turso/turso db create ${{ steps.db_name.outputs.name }} \
            --from-db ${{ env.TURSO_STAGING_DB }} \
            --group ${{ env.TURSO_PREVIEW_GROUP }}

      # ========================================
      # Step 5: データベース URL の取得
      # ========================================
      - name: Get Database URL
        id: db_url
        run: |
          DB_URL=$(~/.turso/turso db show ${{ steps.db_name.outputs.name }} --url)
          echo "url=$DB_URL" >> $GITHUB_OUTPUT
          echo "Database URL: $DB_URL"

      # ========================================
      # Step 6: マイグレーションの実行
      # ========================================
      - name: Run migrations
        env:
          TURSO_DATABASE_URL: ${{ steps.db_url.outputs.url }}
          TURSO_AUTH_TOKEN: ${{ secrets.TURSO_GROUP_TOKEN }}
        run: |
          # Vercel から取得した環境変数を読み込み（マイグレーションに必要な値）
          set -a
          source .env.local
          set +a
          # DB URL は Preview 用に上書き
          export TURSO_DATABASE_URL="${{ steps.db_url.outputs.url }}"
          export TURSO_AUTH_TOKEN="${{ secrets.TURSO_GROUP_TOKEN }}"
          npm run db:migrate

      # ========================================
      # Step 7: Preview URL の生成
      # ========================================
      - name: Generate Preview URL
        id: preview_url
        run: |
          # ブランチ名を URL セーフな形式に変換
          BRANCH_SLUG=$(echo "${{ github.ref_name }}" | tr '[:upper:]' '[:lower:]' | tr -cd '[:alnum:]-')
          # Vercel の Preview URL 形式: {project}-git-{branch}-{scope}.vercel.app
          PREVIEW_URL="https://${{ env.VERCEL_PROJECT_NAME }}-git-${BRANCH_SLUG}-${{ env.VERCEL_SCOPE_SLUG }}.vercel.app"
          echo "url=$PREVIEW_URL" >> $GITHUB_OUTPUT
          echo "Preview URL: $PREVIEW_URL"

      # ========================================
      # Step 8: Vercel 環境変数の設定
      # ========================================
      - name: Set Vercel environment variables
        env:
          VERCEL_TOKEN: ${{ secrets.VERCEL_TOKEN }}
        run: |
          # プロジェクト ID を vercel.json から取得
          PROJECT_ID=$(cat .vercel/project.json | jq -r '.projectId')

          # TURSO_DATABASE_URL
          curl -X POST "https://api.vercel.com/v10/projects/$PROJECT_ID/env?upsert=true" \
            -H "Authorization: Bearer $VERCEL_TOKEN" \
            -H "Content-Type: application/json" \
            -d '{
              "key": "TURSO_DATABASE_URL",
              "value": "${{ steps.db_url.outputs.url }}",
              "type": "encrypted",
              "target": ["preview"],
              "gitBranch": "${{ github.ref_name }}"
            }'

          # BETTER_AUTH_URL
          curl -X POST "https://api.vercel.com/v10/projects/$PROJECT_ID/env?upsert=true" \
            -H "Authorization: Bearer $VERCEL_TOKEN" \
            -H "Content-Type: application/json" \
            -d '{
              "key": "BETTER_AUTH_URL",
              "value": "${{ steps.preview_url.outputs.url }}",
              "type": "plain",
              "target": ["preview"],
              "gitBranch": "${{ github.ref_name }}"
            }'

          # NEXT_PUBLIC_SITE_URL
          curl -X POST "https://api.vercel.com/v10/projects/$VERCEL_PROJECT_ID/env?upsert=true" \
            -H "Authorization: Bearer $VERCEL_TOKEN" \
            -H "Content-Type: application/json" \
            -d '{
              "key": "NEXT_PUBLIC_SITE_URL",
              "value": "${{ steps.preview_url.outputs.url }}",
              "type": "plain",
              "target": ["preview"],
              "gitBranch": "${{ github.ref_name }}"
            }'

          echo "Vercel environment variables set for branch: ${{ github.ref_name }}"

      # ========================================
      # Step 8: サマリーの出力
      # ========================================
      - name: Output summary
        run: |
          echo "## Preview Database Setup Complete" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "| Item | Value |" >> $GITHUB_STEP_SUMMARY
          echo "|------|-------|" >> $GITHUB_STEP_SUMMARY
          echo "| Database | \`${{ steps.db_name.outputs.name }}\` |" >> $GITHUB_STEP_SUMMARY
          echo "| Branch | \`${{ github.ref_name }}\` |" >> $GITHUB_STEP_SUMMARY
          echo "| Preview URL | \`${{ steps.preview_url.outputs.url }}\` |" >> $GITHUB_STEP_SUMMARY
          echo "| Created | ${{ steps.db_check.outputs.exists == 'false' && 'Yes' || 'No (already existed)' }} |" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "### Next Steps" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "1. **Google Console で OAuth 設定を追加**:" >> $GITHUB_STEP_SUMMARY
          echo "   - JavaScript 生成元: \`${{ steps.preview_url.outputs.url }}\`" >> $GITHUB_STEP_SUMMARY
          echo "   - リダイレクト URI: \`${{ steps.preview_url.outputs.url }}/api/auth/callback/google\`" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "2. **Vercel ダッシュボードから手動デプロイ**" >> $GITHUB_STEP_SUMMARY

name: Preview DB Delete

on:
  pull_request:
    types: [closed]
    branches: [develop]

jobs:
  delete-preview-db:
    # issue-* ブランチからの PR のみ対象
    if: startsWith(github.head_ref, 'issue-')
    runs-on: ubuntu-latest
    timeout-minutes: 5
    steps:
      - name: Install Turso CLI
        run: curl -sSfL https://get.tur.so/install.sh | bash

      # ========================================
      # Step 1: データベース名の生成
      # ========================================
      - name: Generate DB name from branch
        id: db_name
        run: |
          BRANCH="${{ github.head_ref }}"
          DB_NAME="linto-$(echo "$BRANCH" | tr '[:upper:]' '[:lower:]' | tr -cd '[:alnum:]-' | cut -c 1-32)"
          echo "name=$DB_NAME" >> $GITHUB_OUTPUT

      # ========================================
      # Step 2: データベースの削除
      # ========================================
      - name: Delete Preview Database
        env:
          TURSO_API_TOKEN: ${{ secrets.TURSO_API_TOKEN }}
        run: |
          ~/.turso/turso db destroy ${{ steps.db_name.outputs.name }} --yes || true
          echo "Database deleted: ${{ steps.db_name.outputs.name }}"

      # ========================================
      # Step 3: Vercel 環境変数の削除
      # ========================================
      # 注意: Vercel はブランチがマージ/削除されても環境変数を自動削除しない
      # そのため、手動で削除する必要がある
      - name: Get and delete Vercel environment variables
        env:
          VERCEL_TOKEN: ${{ secrets.VERCEL_TOKEN }}
          VERCEL_PROJECT_ID: ${{ secrets.VERCEL_PROJECT_ID }}
          BRANCH_NAME: ${{ github.head_ref }}
        run: |
          # ブランチに紐づく全ての環境変数 ID を取得
          ENV_IDS=$(curl -s "https://api.vercel.com/v9/projects/$VERCEL_PROJECT_ID/env" \
            -H "Authorization: Bearer $VERCEL_TOKEN" \
            | jq -r --arg branch "$BRANCH_NAME" '.envs[] | select(.gitBranch == $branch) | .id')

          if [ -z "$ENV_IDS" ]; then
            echo "No environment variables found for branch: $BRANCH_NAME"
            exit 0
          fi

          # 各環境変数を削除
          for ENV_ID in $ENV_IDS; do
            echo "Deleting environment variable: $ENV_ID"
            curl -X DELETE "https://api.vercel.com/v9/projects/$VERCEL_PROJECT_ID/env/$ENV_ID" \
              -H "Authorization: Bearer $VERCEL_TOKEN"
          done

          echo "All environment variables deleted for branch: $BRANCH_NAME"

      # ========================================
      # Step 4: サマリーの出力
      # ========================================
      - name: Output summary
        run: |
          echo "## Preview Database Deleted" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "| Item | Value |" >> $GITHUB_STEP_SUMMARY
          echo "|------|-------|" >> $GITHUB_STEP_SUMMARY
          echo "| Database | \`${{ steps.db_name.outputs.name }}\` |" >> $GITHUB_STEP_SUMMARY
          echo "| Branch | \`${{ github.head_ref }}\` |" >> $GITHUB_STEP_SUMMARY
          echo "| Env Variables | Deleted (TURSO_DATABASE_URL, BETTER_AUTH_URL, NEXT_PUBLIC_SITE_URL) |" >> $GITHUB_STEP_SUMMARY

name: Staging Migration

on:
  pull_request:
    types: [closed]
    branches: [develop]

# プロジェクト固有の設定
env:
  VERCEL_ORG_ID: ${{ secrets.VERCEL_ORG_ID }}
  VERCEL_PROJECT_ID: ${{ secrets.VERCEL_PROJECT_ID }}

jobs:
  migrate-staging:
    # マージされた場合のみ実行（クローズのみは除外）
    if: github.event.pull_request.merged == true
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - name: Checkout
        uses: actions/checkout@v6
        with:
          ref: develop  # マージ後の develop を取得

      - name: Setup Node.js
        uses: actions/setup-node@v6
        with:
          node-version: '24'
          cache: 'npm'

      - name: Install dependencies
        run: npm ci

      - name: Install Vercel CLI
        run: npm install -g vercel@latest

      # ========================================
      # Vercel から環境変数を取得してマイグレーション実行
      # ========================================
      - name: Pull Vercel environment and run migrations
        run: |
          # Vercel プロジェクトをリンク
          vercel link --yes --token=${{ secrets.VERCEL_TOKEN }}

          # Preview 環境の環境変数を取得（Staging は Preview のデフォルト）
          vercel env pull .env.local --yes --environment=preview --token=${{ secrets.VERCEL_TOKEN }}

          # 環境変数を読み込んでマイグレーション実行
          set -a
          source .env.local
          set +a
          npm run db:migrate

      - name: Output summary
        run: |
          echo "## Staging Migration Complete" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "Migration applied to staging database." >> $GITHUB_STEP_SUMMARY

name: Production Migration

on:
  workflow_dispatch:
    inputs:
      confirm:
        description: 'Type "migrate" to confirm'
        required: true
        type: string

# プロジェクト固有の設定
env:
  VERCEL_ORG_ID: ${{ secrets.VERCEL_ORG_ID }}
  VERCEL_PROJECT_ID: ${{ secrets.VERCEL_PROJECT_ID }}

jobs:
  migrate-production:
    # 確認入力が正しい場合のみ実行
    if: github.event.inputs.confirm == 'migrate'
    runs-on: ubuntu-latest
    timeout-minutes: 10
    steps:
      - name: Checkout
        uses: actions/checkout@v6
        with:
          ref: main  # main ブランチを明示的に指定

      - name: Setup Node.js
        uses: actions/setup-node@v6
        with:
          node-version: '24'
          cache: 'npm'

      - name: Install dependencies
        run: npm ci

      - name: Install Vercel CLI
        run: npm install -g vercel@latest

      # ========================================
      # Vercel から環境変数を取得してマイグレーション実行
      # ========================================
      - name: Pull Vercel environment and run migrations
        run: |
          # Vercel プロジェクトをリンク
          vercel link --yes --token=${{ secrets.VERCEL_TOKEN }}

          # Production 環境の環境変数を取得
          vercel env pull .env.local --yes --environment=production --token=${{ secrets.VERCEL_TOKEN }}

          # 環境変数を読み込んでマイグレーション実行
          set -a
          source .env.local
          set +a
          npm run db:migrate

      - name: Output summary
        run: |
          echo "## Production Migration Complete" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "Migration applied to production database." >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "**Next Step**: Deploy manually from Vercel dashboard" >> $GITHUB_STEP_SUMMARY

# プロジェクトをリンク
vercel link

# 生成されたファイルを確認
cat .vercel/project.json

{
  "orgId": "team_xxxxxxxxxxxxxxxx",
  "projectId": "prj_yyyyyyyyyyyyyyyy"
}

https://{project-name}-git-{branch-name}-{scope-slug}.vercel.app

https://{project-name}-git-{branch-name}-{scope-slug}.vercel.app

https://linto-dev-git-issue-123-naokiyazawas-projects.vercel.app

https://{project-name}-git-{branch-name}-{scope-slug}.vercel.app/api/auth/callback/google

https://linto-dev-git-issue-123-naokiyazawas-projects.vercel.app/api/auth/callback/google

### Next Steps

1. **Google Console で OAuth 設定を追加**:
   - JavaScript 生成元: `https://linto-dev-git-issue-123-naokiyazawas-projects.vercel.app`
   - リダイレクト URI: `https://linto-dev-git-issue-123-naokiyazawas-projects.vercel.app/api/auth/callback/google`

2. **Vercel ダッシュボードから手動デプロイ**

# Preview デプロイ
vercel --prod=false

# Production デプロイ
vercel --prod

.github/
└── workflows/
    ├── preview-db-setup.yml      # issue-* プッシュ時: DB 作成 + マイグレーション
    ├── preview-db-delete.yml     # PR クローズ時: DB 削除
    ├── staging-migrate.yml       # develop マージ時: staging マイグレーション
    └── production-migrate.yml    # 手動実行: production マイグレーション

vercel.json                       # 自動デプロイ無効化設定

# 1. issue ブランチを作成
git checkout -b issue-123

# 2. コードを変更してプッシュ
git push origin issue-123
# → GitHub Actions が自動で:
#   - DB 作成（linto-issue-123）
#   - マイグレーション実行
#   - Vercel 環境変数設定（TURSO_DATABASE_URL, BETTER_AUTH_URL, NEXT_PUBLIC_SITE_URL）

# 3. GitHub Actions の Summary を確認
# → 表示された Preview URL と callback URL をコピー

# 4. Google Console で OAuth 設定を追加（手動）
# → 承認済みの JavaScript 生成元: https://linto-dev-git-issue-123-xxx.vercel.app
# → 承認済みのリダイレクト URI: https://linto-dev-git-issue-123-xxx.vercel.app/api/auth/callback/google

# 5. Vercel ダッシュボードから手動でデプロイ
# → Preview 環境で動作確認

# 6. PR を作成して develop にマージ
# → GitHub Actions が自動で:
#   - Preview DB 削除
#   - Vercel 環境変数削除
#   - Staging DB にマイグレーション実行

# 7. Google Console から OAuth 設定を削除（手動・任意）
# → 不要になった Preview URL を削除

# 1. develop → main の PR を作成してマージ
# → 何も自動実行されない

# 2. GitHub Actions から Production Migration を手動実行
# → Actions タブ → Production Migration → Run workflow

# 3. Vercel ダッシュボードから手動で Production デプロイ

# Turso CLI で手動確認
turso db list
turso db show linto-issue-123

# ローカルで同じ環境変数を設定してテスト
export TURSO_DATABASE_URL=<url>
export TURSO_AUTH_TOKEN=<token>
npm run db:migrate

# Vercel CLI で確認
vercel env ls --environment=preview --git-branch=issue-123

# GitHub Secrets の設定を確認
# Settings → Secrets and variables → Actions