fix: use snake_case column names in raw SQL for CONTEXT test #260
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Build | |
| on: | |
| workflow_dispatch: | |
| pull_request: | |
| paths: | |
| - "app/**" | |
| - "components/**" | |
| - "lib/**" | |
| - "actions/**" | |
| - "hooks/**" | |
| - "prisma/**" | |
| - "scripts/**" | |
| - "content/**" | |
| - "e2e/**" | |
| - "src/__tests__/**" | |
| - "theme.config.tsx" | |
| - "mdx-components.tsx" | |
| - "playwright.config.ts" | |
| - "vitest.config.ts" | |
| - "vitest.workspace.ts" | |
| - "package.json" | |
| - "pnpm-lock.yaml" | |
| - "Dockerfile" | |
| - "types/**" | |
| - "next.config.*" | |
| - "tsconfig.json" | |
| - "biome.json" | |
| - ".github/workflows/**" | |
| push: | |
| branches: | |
| - main | |
| - staging | |
| paths: | |
| - "app/**" | |
| - "components/**" | |
| - "lib/**" | |
| - "actions/**" | |
| - "hooks/**" | |
| - "prisma/**" | |
| - "scripts/**" | |
| - "content/**" | |
| - "e2e/**" | |
| - "src/__tests__/**" | |
| - "theme.config.tsx" | |
| - "mdx-components.tsx" | |
| - "playwright.config.ts" | |
| - "vitest.config.ts" | |
| - "vitest.workspace.ts" | |
| - "package.json" | |
| - "pnpm-lock.yaml" | |
| - "Dockerfile" | |
| - "types/**" | |
| - "next.config.*" | |
| - "tsconfig.json" | |
| - "biome.json" | |
| - ".github/workflows/**" | |
| env: | |
| REGISTRY: ghcr.io | |
| # Docker registry requires lowercase. github.repository returns mixed case | |
| # (alan-turing-institute/AssurancePlatform), so we hardcode the lowercase form. | |
| IMAGE_NAME: alan-turing-institute/assuranceplatform/tea-app | |
| PROD_APP_URL: https://assuranceplatform.azurewebsites.net | |
| STAGING_APP_URL: https://staging-assuranceplatform.azurewebsites.net | |
| jobs: | |
| validate: | |
| name: Validate Code Quality | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Checkout repository | |
| uses: actions/checkout@v6 | |
| - name: Setup pnpm | |
| uses: pnpm/action-setup@v4 | |
| - name: Setup Node.js with cache | |
| uses: actions/setup-node@v6 | |
| with: | |
| node-version: '20' | |
| cache: 'pnpm' | |
| - name: Install dependencies | |
| run: pnpm install --frozen-lockfile | |
| - name: Cache Prisma Client | |
| uses: actions/cache@v5 | |
| id: prisma-cache-validate | |
| with: | |
| path: src/generated/prisma | |
| key: prisma-${{ runner.os }}-${{ hashFiles('prisma/schema.prisma', 'pnpm-lock.yaml') }} | |
| - name: Generate Prisma client | |
| if: steps.prisma-cache-validate.outputs.cache-hit != 'true' | |
| run: npx prisma generate --schema=prisma/schema.prisma | |
| env: | |
| DATABASE_URL: postgresql://build:build@localhost:5432/build | |
| - name: Run linter and type check (parallel) | |
| run: | | |
| pnpm exec ultracite check & | |
| LINT_PID=$! | |
| npx tsc --noEmit & | |
| TSC_PID=$! | |
| LINT_EXIT=0; wait $LINT_PID || LINT_EXIT=$? | |
| TSC_EXIT=0; wait $TSC_PID || TSC_EXIT=$? | |
| if [ $LINT_EXIT -ne 0 ] || [ $TSC_EXIT -ne 0 ]; then exit 1; fi | |
| - name: Security audit | |
| run: pnpm audit --audit-level=high | |
| continue-on-error: true | |
| test: | |
| name: Run Tests | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 20 | |
| services: | |
| postgres: | |
| image: postgres:16 | |
| env: | |
| POSTGRES_USER: tea_user | |
| POSTGRES_PASSWORD: tea_password | |
| POSTGRES_DB: tea_test | |
| ports: | |
| - 5432:5432 | |
| options: >- | |
| --health-cmd "pg_isready -U tea_user" | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| env: | |
| DATABASE_URL: postgresql://tea_user:tea_password@localhost:5432/tea_test | |
| NEXTAUTH_SECRET: test-secret-for-ci-only | |
| NEXTAUTH_URL: http://localhost:3000 | |
| SEED_USER_PASSWORD: ${{ secrets.SEED_USER_PASSWORD }} | |
| USE_LOCAL_STORAGE: "true" | |
| steps: | |
| - name: Checkout repository | |
| uses: actions/checkout@v6 | |
| - name: Setup pnpm | |
| uses: pnpm/action-setup@v4 | |
| - name: Setup Node.js with cache | |
| uses: actions/setup-node@v6 | |
| with: | |
| node-version: '20' | |
| cache: 'pnpm' | |
| - name: Install dependencies | |
| run: pnpm install --frozen-lockfile | |
| - name: Cache Prisma Client | |
| uses: actions/cache@v5 | |
| id: prisma-cache-test | |
| with: | |
| path: src/generated/prisma | |
| key: prisma-${{ runner.os }}-${{ hashFiles('prisma/schema.prisma', 'pnpm-lock.yaml') }} | |
| - name: Generate Prisma client | |
| if: steps.prisma-cache-test.outputs.cache-hit != 'true' | |
| run: npx prisma generate --schema=prisma/schema.prisma | |
| - name: Create tea_dev database | |
| run: | | |
| # Integration test globalSetup connects to tea_dev to create tea_test. | |
| # In CI, tea_test already exists (from service container), but tea_dev must exist too. | |
| PGPASSWORD=tea_password psql -h localhost -U tea_user -d tea_test -c "SELECT 1 FROM pg_database WHERE datname = 'tea_dev'" | grep -q 1 \ | |
| || PGPASSWORD=tea_password createdb -h localhost -U tea_user tea_dev | |
| - name: Run migrations | |
| run: npx prisma migrate deploy --schema=prisma/schema.prisma | |
| - name: Seed database | |
| run: npx tsx prisma/seed/dev-seed.ts | |
| - name: Run unit tests | |
| run: pnpm exec vitest run --config vitest.workspace.ts --project unit | |
| - name: Run integration tests | |
| run: pnpm exec vitest run --config vitest.workspace.ts --project integration | |
| - name: Re-seed database for E2E | |
| run: | | |
| PGPASSWORD=tea_password psql -h localhost -U tea_user -d tea_test -c " | |
| TRUNCATE TABLE | |
| case_study_images, case_study_published_cases, published_assurance_cases, | |
| case_studies, release_images, release_comments, release_snapshots, releases, | |
| comments, evidence_links, case_images, case_type_assignments, case_types, | |
| case_invites, case_team_permissions, case_permissions, assurance_elements, | |
| assurance_cases, pattern_permissions, pattern_team_permissions, | |
| pattern_elements, argument_patterns, team_members, teams, | |
| github_repositories, rate_limit_attempts, security_audit_logs, | |
| password_reset_attempts, users | |
| CASCADE; | |
| " | |
| npx tsx prisma/seed/dev-seed.ts | |
| - name: Build application for E2E | |
| run: | | |
| pnpm build | |
| cp -r public .next/standalone/public | |
| cp -r .next/static .next/standalone/.next/static | |
| - name: Cache Playwright browsers | |
| uses: actions/cache@v5 | |
| id: playwright-cache | |
| with: | |
| path: ~/.cache/ms-playwright | |
| key: playwright-${{ runner.os }}-${{ hashFiles('pnpm-lock.yaml') }} | |
| - name: Install Playwright Chromium | |
| if: steps.playwright-cache.outputs.cache-hit != 'true' | |
| run: pnpm exec playwright install --with-deps chromium | |
| - name: Install Playwright system deps (cache hit) | |
| if: steps.playwright-cache.outputs.cache-hit == 'true' | |
| run: pnpm exec playwright install-deps chromium | |
| - name: Run E2E tests | |
| run: pnpm exec playwright test | |
| - name: Upload Playwright report | |
| uses: actions/upload-artifact@v7 | |
| if: ${{ !cancelled() }} | |
| with: | |
| name: playwright-report | |
| path: playwright-report/ | |
| retention-days: 14 | |
| - name: Upload test results | |
| uses: actions/upload-artifact@v7 | |
| if: ${{ !cancelled() }} | |
| with: | |
| name: test-results | |
| path: test-results/ | |
| retention-days: 14 | |
| docker-build: | |
| name: Build and Push Docker Image | |
| needs: [validate, test] | |
| if: github.event_name == 'push' || github.event_name == 'workflow_dispatch' | |
| runs-on: ubuntu-latest | |
| permissions: | |
| contents: read | |
| packages: write | |
| steps: | |
| - name: Checkout repository | |
| uses: actions/checkout@v6 | |
| - name: Set up Docker Buildx | |
| uses: docker/setup-buildx-action@v4 | |
| - name: Log in to Container Registry | |
| uses: docker/login-action@v4 | |
| with: | |
| registry: ${{ env.REGISTRY }} | |
| username: ${{ github.actor }} | |
| password: ${{ secrets.GITHUB_TOKEN }} | |
| - name: Extract metadata | |
| id: meta | |
| uses: docker/metadata-action@v6 | |
| with: | |
| images: ${{ env.REGISTRY }}/${{ env.IMAGE_NAME }} | |
| tags: | | |
| # Branch name | |
| type=ref,event=branch | |
| # PR number | |
| type=ref,event=pr | |
| # SHA (short) - only for branch pushes, not PRs | |
| type=sha,prefix={{branch}}-,enable=${{ github.event_name != 'pull_request' }} | |
| # For main branch | |
| type=raw,value=latest,enable={{is_default_branch}} | |
| type=raw,value=main,enable=${{ github.ref == 'refs/heads/main' }} | |
| # For staging branch | |
| type=raw,value=staging,enable=${{ github.ref == 'refs/heads/staging' }} | |
| type=raw,value=staging-{{date 'YYYY-MM-DD'}}-{{sha}},enable=${{ github.ref == 'refs/heads/staging' }} | |
| # Semantic versions (for releases) | |
| type=semver,pattern={{version}} | |
| type=semver,pattern={{major}}.{{minor}} | |
| type=semver,pattern={{major}} | |
| - name: Build and push Docker image (Production - main branch) | |
| if: github.ref == 'refs/heads/main' | |
| uses: docker/build-push-action@v7 | |
| with: | |
| context: . | |
| file: ./Dockerfile | |
| push: true | |
| tags: ${{ steps.meta.outputs.tags }} | |
| labels: ${{ steps.meta.outputs.labels }} | |
| build-args: | | |
| GITHUB_APP_CLIENT_ID=${{ secrets.GH_APP_CLIENT_ID }} | |
| GITHUB_APP_CLIENT_SECRET=${{ secrets.GH_APP_CLIENT_SECRET }} | |
| GOOGLE_CLIENT_ID=${{ secrets.GOOGLE_CLIENT_ID }} | |
| GOOGLE_CLIENT_SECRET=${{ secrets.GOOGLE_CLIENT_SECRET }} | |
| NEXTAUTH_SECRET=${{ secrets.NEXTAUTH_SECRET }} | |
| NEXTAUTH_URL=${{ secrets.NEXTAUTH_URL_MAIN }} | |
| DATABASE_URL=postgresql://build:build@localhost:5432/build | |
| cache-from: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache | |
| cache-to: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache,mode=max | |
| platforms: linux/amd64 | |
| - name: Build and push Docker image (Staging branch) | |
| if: github.ref == 'refs/heads/staging' | |
| uses: docker/build-push-action@v7 | |
| with: | |
| context: . | |
| file: ./Dockerfile | |
| push: true | |
| tags: ${{ steps.meta.outputs.tags }} | |
| labels: ${{ steps.meta.outputs.labels }} | |
| build-args: | | |
| GITHUB_APP_CLIENT_ID=${{ secrets.GH_APP_CLIENT_ID_STAGING }} | |
| GITHUB_APP_CLIENT_SECRET=${{ secrets.GH_APP_CLIENT_SECRET_STAGING }} | |
| GOOGLE_CLIENT_ID=${{ secrets.GOOGLE_CLIENT_ID_STAGING }} | |
| GOOGLE_CLIENT_SECRET=${{ secrets.GOOGLE_CLIENT_SECRET_STAGING }} | |
| NEXTAUTH_SECRET=${{ secrets.NEXTAUTH_SECRET_STAGING }} | |
| NEXTAUTH_URL=${{ secrets.NEXTAUTH_URL_STAGING }} | |
| DATABASE_URL=postgresql://build:build@localhost:5432/build | |
| cache-from: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache | |
| cache-to: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache,mode=max | |
| platforms: linux/amd64 | |
| - name: Build and push Docker image (PR/other branches) | |
| if: github.ref != 'refs/heads/main' && github.ref != 'refs/heads/staging' | |
| uses: docker/build-push-action@v7 | |
| with: | |
| context: . | |
| file: ./Dockerfile | |
| push: true | |
| tags: ${{ steps.meta.outputs.tags }} | |
| labels: ${{ steps.meta.outputs.labels }} | |
| build-args: | | |
| GITHUB_APP_CLIENT_ID=${{ secrets.GH_APP_CLIENT_ID_STAGING }} | |
| GITHUB_APP_CLIENT_SECRET=${{ secrets.GH_APP_CLIENT_SECRET_STAGING }} | |
| GOOGLE_CLIENT_ID=${{ secrets.GOOGLE_CLIENT_ID_STAGING }} | |
| GOOGLE_CLIENT_SECRET=${{ secrets.GOOGLE_CLIENT_SECRET_STAGING }} | |
| NEXTAUTH_SECRET=${{ secrets.NEXTAUTH_SECRET_STAGING }} | |
| NEXTAUTH_URL=${{ secrets.NEXTAUTH_URL_STAGING }} | |
| DATABASE_URL=postgresql://build:build@localhost:5432/build | |
| cache-from: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache | |
| cache-to: type=registry,ref=ghcr.io/${{ env.IMAGE_NAME }}:cache,mode=max | |
| platforms: linux/amd64 | |
| deploy: | |
| name: Deploy to Azure | |
| runs-on: ubuntu-latest | |
| needs: [docker-build] | |
| if: github.ref == 'refs/heads/main' || github.ref == 'refs/heads/staging' | |
| steps: | |
| - name: Deploy to Azure (Production) | |
| if: github.ref == 'refs/heads/main' | |
| id: deploy-prod | |
| env: | |
| WEBHOOK_URL: ${{ secrets.AZURE_WEBAPP_WEBHOOK_PROD }} | |
| run: | | |
| echo "Triggering Azure deployment webhook..." | |
| # Webhook with retry logic | |
| MAX_RETRIES=3 | |
| DEPLOY_SUCCESS=false | |
| for i in $(seq 1 $MAX_RETRIES); do | |
| echo "Deployment attempt $i of $MAX_RETRIES..." | |
| HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" \ | |
| --max-time 30 \ | |
| -X POST "${WEBHOOK_URL}" \ | |
| -H "Content-Length: 0") | |
| if [ "$HTTP_STATUS" -ge 200 ] && [ "$HTTP_STATUS" -lt 300 ]; then | |
| echo "Webhook triggered successfully (HTTP $HTTP_STATUS)" | |
| DEPLOY_SUCCESS=true | |
| break | |
| fi | |
| echo "Webhook returned HTTP $HTTP_STATUS" | |
| [ $i -lt $MAX_RETRIES ] && sleep 10 | |
| done | |
| if [ "$DEPLOY_SUCCESS" = "false" ]; then | |
| echo "::error::Deployment webhook failed after $MAX_RETRIES attempts" | |
| exit 1 | |
| fi | |
| # Wait for deployment to start | |
| echo "Waiting 60 seconds for deployment to propagate..." | |
| sleep 60 | |
| # Health check with retries | |
| echo "Checking application health..." | |
| HEALTH_SUCCESS=false | |
| for i in $(seq 1 10); do | |
| echo "Health check attempt $i of 10..." | |
| HEALTH_STATUS=$(curl -sL -o /dev/null -w "%{http_code}" \ | |
| --max-time 10 \ | |
| "${{ env.PROD_APP_URL }}/api/health") | |
| if [ "$HEALTH_STATUS" = "200" ]; then | |
| echo "Application is healthy!" | |
| HEALTH_SUCCESS=true | |
| break | |
| fi | |
| echo "Health check returned HTTP $HEALTH_STATUS" | |
| [ $i -lt 10 ] && sleep 15 | |
| done | |
| if [ "$HEALTH_SUCCESS" = "false" ]; then | |
| echo "::error::Application health check did not pass after deployment" | |
| exit 1 | |
| fi | |
| - name: Deploy to Azure (Staging) | |
| if: github.ref == 'refs/heads/staging' | |
| id: deploy-staging | |
| env: | |
| WEBHOOK_URL: ${{ secrets.AZURE_WEBAPP_WEBHOOK_STAGING }} | |
| run: | | |
| echo "Triggering Azure deployment webhook..." | |
| # Webhook with retry logic | |
| MAX_RETRIES=3 | |
| DEPLOY_SUCCESS=false | |
| for i in $(seq 1 $MAX_RETRIES); do | |
| echo "Deployment attempt $i of $MAX_RETRIES..." | |
| HTTP_STATUS=$(curl -s -o /dev/null -w "%{http_code}" \ | |
| --max-time 30 \ | |
| -X POST "${WEBHOOK_URL}" \ | |
| -H "Content-Length: 0") | |
| if [ "$HTTP_STATUS" -ge 200 ] && [ "$HTTP_STATUS" -lt 300 ]; then | |
| echo "Webhook triggered successfully (HTTP $HTTP_STATUS)" | |
| DEPLOY_SUCCESS=true | |
| break | |
| fi | |
| echo "Webhook returned HTTP $HTTP_STATUS" | |
| [ $i -lt $MAX_RETRIES ] && sleep 10 | |
| done | |
| if [ "$DEPLOY_SUCCESS" = "false" ]; then | |
| echo "::error::Deployment webhook failed after $MAX_RETRIES attempts" | |
| exit 1 | |
| fi | |
| # Wait for deployment to start | |
| echo "Waiting 60 seconds for deployment to propagate..." | |
| sleep 60 | |
| # Health check with retries | |
| echo "Checking application health..." | |
| HEALTH_SUCCESS=false | |
| for i in $(seq 1 10); do | |
| echo "Health check attempt $i of 10..." | |
| HEALTH_STATUS=$(curl -sL -o /dev/null -w "%{http_code}" \ | |
| --max-time 10 \ | |
| "${{ env.STAGING_APP_URL }}/api/health") | |
| if [ "$HEALTH_STATUS" = "200" ]; then | |
| echo "Application is healthy!" | |
| HEALTH_SUCCESS=true | |
| break | |
| fi | |
| echo "Health check returned HTTP $HEALTH_STATUS" | |
| [ $i -lt 10 ] && sleep 15 | |
| done | |
| if [ "$HEALTH_SUCCESS" = "false" ]; then | |
| echo "::error::Application health check did not pass after deployment" | |
| exit 1 | |
| fi | |
| # GitHub Deployment Summary | |
| - name: Create Deployment Summary - Success | |
| if: success() | |
| run: | | |
| if [ "${{ github.ref }}" = "refs/heads/main" ]; then | |
| ENV="Production" | |
| APP_URL="${{ env.PROD_APP_URL }}" | |
| else | |
| ENV="Staging" | |
| APP_URL="${{ env.STAGING_APP_URL }}" | |
| fi | |
| echo "## Deployment Successful" >> $GITHUB_STEP_SUMMARY | |
| echo "" >> $GITHUB_STEP_SUMMARY | |
| echo "| Property | Value |" >> $GITHUB_STEP_SUMMARY | |
| echo "|----------|-------|" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Environment** | ${ENV} |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Health Check** | Passed |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Commit** | \`${{ github.sha }}\` |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Actor** | @${{ github.actor }} |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Application** | [${APP_URL}](${APP_URL}) |" >> $GITHUB_STEP_SUMMARY | |
| - name: Create Deployment Summary - Failure | |
| if: failure() | |
| run: | | |
| if [ "${{ github.ref }}" = "refs/heads/main" ]; then | |
| ENV="Production" | |
| else | |
| ENV="Staging" | |
| fi | |
| echo "## Deployment Failed" >> $GITHUB_STEP_SUMMARY | |
| echo "" >> $GITHUB_STEP_SUMMARY | |
| echo "| Property | Value |" >> $GITHUB_STEP_SUMMARY | |
| echo "|----------|-------|" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Environment** | ${ENV} |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Branch** | ${{ github.ref_name }} |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Commit** | \`${{ github.sha }}\` |" >> $GITHUB_STEP_SUMMARY | |
| echo "| **Actor** | @${{ github.actor }} |" >> $GITHUB_STEP_SUMMARY | |
| echo "" >> $GITHUB_STEP_SUMMARY | |
| echo "Please check the workflow logs for details." >> $GITHUB_STEP_SUMMARY |