Updating the number of schools

.gitea/workflows/deploy.yml

@@ -1,4 +1,4 @@
-name: Stage (build -> staging -> E2E gate)
+name: Deploy (staging -> E2E gate -> production)
 
 on:
   push:
@@ -193,5 +193,48 @@ jobs:
         env:
           BASE_URL: ${{ secrets.STAGING_BASE_URL }}
 
-# Production deployment is a second, manual approval: see promote.yml
-# ("Promote to Production (manual)") and docs/DEPLOY.md.
+  promote-prod:
+    name: Promote to Production
+    runs-on: ubuntu-latest
+    needs: [e2e-staging]
+    steps:
+      - name: Set up Docker Buildx
+        uses: docker/setup-buildx-action@v3
+
+      - name: Log in to Gitea Container Registry
+        uses: docker/login-action@v3
+        with:
+          registry: ${{ env.REGISTRY }}
+          username: ${{ gitea.actor }}
+          password: ${{ secrets.REGISTRY_TOKEN }}
+
+      - name: Retag verified images as prod
+        run: |
+          SHORT_SHA="sha-$(echo "${{ gitea.sha }}" | cut -c1-7)"
+          for IMAGE in \
+            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
+            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
+            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
+            # Keep a rollback pointer before moving :prod
+            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
+            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
+            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
+          done
+
+      - name: Trigger production stack update
+        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
+
+      - name: Wait for production to become healthy
+        run: |
+          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
+          for i in $(seq 1 60); do
+            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
+              echo "Production is up (attempt $i)"
+              exit 0
+            fi
+            sleep 5
+          done
+          echo "Production did not become healthy in time" >&2
+          exit 1
+        env:
+          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}

.gitea/workflows/promote.yml

@@ -1,126 +0,0 @@
-name: Promote to Production (manual)
-
-# Second approval gate of the deploy model: run this workflow from the
-# Actions UI after testing the feature on staging. It refuses commits
-# whose staging E2E gate is not green. See docs/DEPLOY.md.
-
-on:
-  workflow_dispatch:
-    inputs:
-      sha:
-        description: >-
-          Commit SHA on main to promote (full or >=7 chars).
-          Leave empty to promote the latest main commit.
-        required: false
-        default: ""
-
-# Only one promotion at a time; never cancel an in-flight promotion.
-concurrency:
-  group: prod-promotion
-  cancel-in-progress: false
-
-env:
-  REGISTRY: privaterepo.sitaru.org
-  BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
-  FRONTEND_IMAGE_NAME: ${{ gitea.repository }}-frontend
-  PIPELINE_IMAGE_NAME: ${{ gitea.repository }}-pipeline
-
-jobs:
-  promote-prod:
-    name: Promote approved commit to Production
-    runs-on: ubuntu-latest
-    steps:
-      - name: Checkout repository (full history for ancestry check)
-        uses: actions/checkout@v4
-        with:
-          fetch-depth: 0
-
-      - name: Resolve and validate target SHA
-        id: resolve
-        # SECURITY: the dispatch input is untrusted — it reaches the shell
-        # only via env (never spliced into `run:` with ${{ }}) and is only
-        # used as a quoted argument. The resolved value is validated as a
-        # 40-hex sha and required to be an ancestor of main before any
-        # later step interpolates it.
-        env:
-          SHA_INPUT: ${{ gitea.event.inputs.sha }}
-        run: |
-          set -euo pipefail
-          case "$SHA_INPUT" in
-            -*) echo "REFUSED: SHA input may not start with '-'." >&2; exit 1 ;;
-          esac
-          if [ -z "$SHA_INPUT" ]; then
-            SHA_INPUT="$(git rev-parse origin/main)"
-          fi
-          FULL_SHA=$(git rev-parse --verify --quiet "${SHA_INPUT}^{commit}") || {
-            echo "REFUSED: not a commit in this repository." >&2
-            exit 1
-          }
-          echo "$FULL_SHA" | grep -Eq '^[0-9a-f]{40}$'
-          if ! git merge-base --is-ancestor "$FULL_SHA" origin/main; then
-            echo "REFUSED: $FULL_SHA is not on main — only main commits are promotable." >&2
-            exit 1
-          fi
-          SHORT_SHA="sha-$(echo "$FULL_SHA" | cut -c1-7)"
-          echo "full=$FULL_SHA" >> "$GITHUB_OUTPUT"
-          echo "short=$SHORT_SHA" >> "$GITHUB_OUTPUT"
-          echo "Promoting $FULL_SHA (images tagged $SHORT_SHA)"
-
-      - name: Verify the staging E2E gate passed for this commit
-        run: |
-          STATUS_JSON=$(curl -fsS \
-            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
-            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/commits/${{ steps.resolve.outputs.full }}/status")
-          echo "$STATUS_JSON" | python3 -c "
-          import json, sys
-          d = json.load(sys.stdin)
-          ok = [s for s in d.get('statuses', [])
-                if 'E2E Journeys against Staging' in s.get('context', '')
-                and s.get('status') == 'success']
-          if not ok:
-              print('REFUSED: no successful \"E2E Journeys against Staging\" status on this commit.')
-              print('Contexts found:', [s.get('context') for s in d.get('statuses', [])])
-              sys.exit(1)
-          print('E2E gate verified green for this commit.')
-          "
-
-      - name: Set up Docker Buildx
-        uses: docker/setup-buildx-action@v3
-
-      - name: Log in to Gitea Container Registry
-        uses: docker/login-action@v3
-        with:
-          registry: ${{ env.REGISTRY }}
-          username: ${{ gitea.actor }}
-          password: ${{ secrets.REGISTRY_TOKEN }}
-
-      - name: Retag approved images as prod (keeping rollback pointer)
-        run: |
-          SHORT_SHA="${{ steps.resolve.outputs.short }}"
-          for IMAGE in \
-            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
-            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
-            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
-            # Keep a rollback pointer before moving :prod
-            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
-            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
-            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
-          done
-
-      - name: Trigger production stack update
-        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
-
-      - name: Wait for production to become healthy
-        run: |
-          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
-          for i in $(seq 1 60); do
-            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
-              echo "Production is up (attempt $i)"
-              exit 0
-            fi
-            sleep 5
-          done
-          echo "Production did not become healthy in time" >&2
-          exit 1
-        env:
-          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}

backend/data_loader.py

@@ -4,7 +4,6 @@ Provides efficient queries with caching.
 """
 
 import logging
-import re
 
 import pandas as pd
 import numpy as np
@@ -263,81 +262,25 @@ assert "NULL AS has_sixth_form" in str(_MAIN_QUERY_NO_SIXTH_FORM), (
     "expected replacement of 's.has_sixth_form,' to have taken effect"
 )
 
-# Fallback used when marts.dim_school predates the GIAS code-dictionary
-# migration (i.e. the nightly dbt pipeline hasn't rebuilt the mart yet on
-# this DB, so it still has the old name columns instead of *_code columns).
-_MAIN_QUERY_LEGACY_NAMES = str(_MAIN_QUERY)
-_LEGACY_NAME_REPLACEMENTS = [
-    ("s.phase_code,", "s.phase,"),
-    ("s.school_type_code,", "s.school_type,"),
-    (
-        "s.religious_character_code,",
-        "s.religious_character AS religious_denomination,",
-    ),
-    ("s.status_code,", "s.status,"),
-    ("s.admissions_policy_code,", "s.admissions_policy,"),
-]
-for _old, _new in _LEGACY_NAME_REPLACEMENTS:
-    assert _old in _MAIN_QUERY_LEGACY_NAMES, (
-        f"expected {_old!r} to be present in _MAIN_QUERY before replacement"
-    )
-    _MAIN_QUERY_LEGACY_NAMES = _MAIN_QUERY_LEGACY_NAMES.replace(_old, _new)
-_MAIN_QUERY_LEGACY_NAMES = text(_MAIN_QUERY_LEGACY_NAMES)
-
-_GIAS_CODE_COLUMN_NAMES = (
-    "phase_code",
-    "school_type_code",
-    "religious_character_code",
-    "status_code",
-    "admissions_policy_code",
-)
-
-_MISSING_COLUMN_RE = re.compile(r'column "?(?:s\.)?(\w+)"? does not exist')
-
-
-def _missing_column_name(exc: Exception) -> Optional[str]:
-    """Name of the missing column from a psycopg2 UndefinedColumn error.
-
-    Inspects exc.orig (the DBAPI error), whose message names only the
-    offending column — str(exc) also embeds the full SQL statement, which
-    contains every column name and therefore must not be matched against.
-    """
-    orig = getattr(exc, "orig", None)
-    match = _MISSING_COLUMN_RE.search(str(orig) if orig is not None else str(exc))
-    return match.group(1) if match else None
-
 
 def load_school_data_as_dataframe() -> pd.DataFrame:
     """Load all school + KS2 data as a pandas DataFrame."""
     try:
         df = pd.read_sql(_MAIN_QUERY, engine)
     except sqlalchemy.exc.ProgrammingError as exc:
-        missing = _missing_column_name(exc)
-        if missing in _GIAS_CODE_COLUMN_NAMES:
-            logging.getLogger(__name__).warning(
-                "marts predate the GIAS code migration — falling back to "
-                "legacy name-column query: %s",
-                exc,
-            )
-            try:
-                df = pd.read_sql(_MAIN_QUERY_LEGACY_NAMES, engine)
-            except Exception as exc2:
-                print(f"Warning: Could not load school data from marts: {exc2}")
-                return pd.DataFrame()
-        elif missing == "has_sixth_form":
-            logging.getLogger(__name__).warning(
-                "marts.dim_school is missing has_sixth_form (pipeline hasn't "
-                "rebuilt the mart yet on this DB) — retrying without it: %s",
-                exc,
-            )
-            try:
-                df = pd.read_sql(_MAIN_QUERY_NO_SIXTH_FORM, engine)
-            except Exception as exc2:
-                print(f"Warning: Could not load school data from marts: {exc2}")
-                return pd.DataFrame()
-        else:
+        if "has_sixth_form" not in str(exc):
             print(f"Warning: Could not load school data from marts: {exc}")
             return pd.DataFrame()
+        logging.getLogger(__name__).warning(
+            "marts.dim_school is missing has_sixth_form (pipeline hasn't "
+            "rebuilt the mart yet on this DB) — retrying without it: %s",
+            exc,
+        )
+        try:
+            df = pd.read_sql(_MAIN_QUERY_NO_SIXTH_FORM, engine)
+        except Exception as exc2:
+            print(f"Warning: Could not load school data from marts: {exc2}")
+            return pd.DataFrame()
     except Exception as exc:
         print(f"Warning: Could not load school data from marts: {exc}")
         return pd.DataFrame()

backend/tests/test_gias_translation.py

@@ -4,7 +4,7 @@ rest of the backend sees must carry today's name strings."""
 import numpy as np
 import pandas as pd
 
-from backend.data_loader import _missing_column_name, translate_gias_code_columns
+from backend.data_loader import translate_gias_code_columns
 from backend.gias_codes import ESTABLISHMENT_STATUS, PHASE_OF_EDUCATION
 
 
@@ -42,91 +42,3 @@ def test_missing_code_columns_are_a_noop():
     out = translate_gias_code_columns(df)
     assert out.iloc[0]["phase"] == "Primary"
     assert out.iloc[0]["status"] == "Open"
-
-
-def _fake_exc(orig_message):
-    """A stand-in for sqlalchemy.exc.ProgrammingError: str(exc) embeds the
-    full SQL statement (deliberately containing every column name below, to
-    prove the matcher doesn't fall back to it), while .orig carries the real
-    DBAPI error message naming only the offending column."""
-    exc = Exception(
-        "SELECT s.phase_code, s.school_type_code, s.religious_character_code, "
-        "s.status_code, s.admissions_policy_code, s.has_sixth_form FROM ... "
-        f"[SQL: ...] (Background on this error at: https://...)"
-    )
-    exc.orig = Exception(orig_message) if orig_message is not None else None
-    return exc
-
-
-def test_missing_column_name_quoted():
-    assert _missing_column_name(_fake_exc('column "phase_code" does not exist')) == "phase_code"
-
-
-def test_missing_column_name_unquoted():
-    assert _missing_column_name(_fake_exc("column phase_code does not exist")) == "phase_code"
-
-
-def test_missing_column_name_table_prefixed():
-    assert (
-        _missing_column_name(_fake_exc("column s.has_sixth_form does not exist"))
-        == "has_sixth_form"
-    )
-
-
-def test_missing_column_name_no_match_returns_none():
-    assert _missing_column_name(_fake_exc("relation \"marts.dim_school\" does not exist")) is None
-
-
-def test_load_school_data_survives_premigration_marts(monkeypatch):
-    """Real prod state until the nightly pipeline first rebuilds the mart with
-    the GIAS code columns: marts.dim_school still has the old name columns
-    (phase, school_type, religious_character, status, admissions_policy)
-    instead of the new *_code columns. The first query raises UndefinedColumn
-    on s.phase_code; load_school_data_as_dataframe must retry with the
-    legacy name-column query rather than swallow the error and return (and
-    then have load_school_data cache) an empty DataFrame."""
-    import sqlalchemy.exc
-    from backend import data_loader
-
-    data_loader._df_cache = None
-    data_loader._df_latest_cache = None
-
-    good_df = pd.DataFrame(
-        [
-            {
-                "urn": 1,
-                "school_name": "Legacy School",
-                "phase": "Primary",
-                "school_type": "Academy",
-                "status": "Open",
-            }
-        ]
-    )
-    calls = []
-
-    def fake_read_sql(query, con):
-        calls.append(query)
-        if len(calls) == 1:
-            raise sqlalchemy.exc.ProgrammingError(
-                statement=str(data_loader._MAIN_QUERY),
-                params=None,
-                orig=Exception(
-                    "(psycopg2.errors.UndefinedColumn) column s.phase_code "
-                    "does not exist\nLINE 5:         s.phase_code,"
-                ),
-            )
-        return good_df.copy()
-
-    monkeypatch.setattr(data_loader.pd, "read_sql", fake_read_sql)
-
-    try:
-        df = data_loader.load_school_data_as_dataframe()
-    finally:
-        data_loader._df_cache = None
-        data_loader._df_latest_cache = None
-
-    assert len(calls) == 2, "must retry with the legacy name-column query variant"
-    assert calls[1] is data_loader._MAIN_QUERY_LEGACY_NAMES
-    assert not df.empty
-    assert df["phase"].iloc[0] == "Primary"
-    assert df["status"].iloc[0] == "Open"

backend/tests/test_sixth_form_flag.py

@@ -148,14 +148,10 @@ def test_load_school_data_survives_missing_has_sixth_form_column(monkeypatch):
     def fake_read_sql(query, con):
         calls.append(query)
         if len(calls) == 1:
-            # The statement text still contains phase_code, school_type_code,
-            # etc. (it's the full _MAIN_QUERY SELECT list) — that's exactly
-            # the collision this test guards against: matching must be done
-            # against exc.orig (the DBAPI error), not str(exc)/the statement.
             raise sqlalchemy.exc.ProgrammingError(
-                statement=str(data_loader._MAIN_QUERY),
-                params=None,
-                orig=Exception(
+                "SELECT ...",
+                None,
+                Exception(
                     "(psycopg2.errors.UndefinedColumn) column s.has_sixth_form "
                     "does not exist"
                 ),

claude.md

@@ -112,15 +112,11 @@ Full details in `docs/DEPLOY.md`. The short version:
 - **Never push to `main` directly.** Work on a feature branch and open a PR;
   branch protection requires the PR checks (typecheck, tests, builds, AI review)
   to pass before merge.
-- Merging to `main` deploys automatically **to staging only**: images are
-  built once, deployed to the staging Portainer stack, and verified by the
-  Playwright journeys in `e2e/`. Production is a second, manual approval:
-  the "Promote to Production (manual)" workflow in Gitea Actions, run after
-  testing the feature on staging. It refuses commits whose staging E2E gate
-  isn't green. Never trigger it yourself — promotion is the human's call.
+- Merging to `main` deploys automatically: images are built once, deployed to
+  the **staging** Portainer stack, verified by the Playwright journeys in
+  `e2e/`, and only then retagged `:prod` and rolled out to production.
 - If you change user-facing behaviour, update or extend the `e2e/` journey
-  tests in the same PR — they gate whether staging is fit for human testing
-  and whether a commit is promotable.
+  tests in the same PR — they are the promotion gate.
 
 ## Recent Changes
 

docs/DEPLOY.md

@@ -1,61 +1,41 @@
 # SDLC & Deployment Pipeline
 
-SchoolCompare uses a two-stage deploy model on Gitea Actions with two human
-approvals. AI writes the code on feature branches; the first approval merges
-the PR, which deploys to staging and runs the E2E gate; the second approval —
-after manual testing on staging — promotes the exact same images to
-production via a manual workflow.
+SchoolCompare uses a fully automated staging → production pipeline on Gitea
+Actions. AI writes the code on feature branches; the pipeline verifies every
+change on a staging environment before promoting the exact same images to
+production. Human input is directional only: feature requests, PR review if
+desired, and intervention when a gate fails.
 
 ## The flow
 
 ```
 feature branch (AI-authored)
-   │  PR to main                                    ← approval #1
+   │  PR to main
    ▼
 PR checks (.gitea/workflows/pr-checks.yml)
    typecheck + unit tests + backend smoke + image builds (no push)
    + Claude code review posted as a PR comment (severe findings fail the check)
    │  merge (branch protection requires green checks)
    ▼
-Stage pipeline (.gitea/workflows/deploy.yml) — automatic
+Deploy pipeline (.gitea/workflows/deploy.yml)
    1. build & push images        → tags sha-<sha>, staging
    2. staging Portainer webhook  → wait for staging health
-   3. Playwright E2E journeys against staging      ← gate before human testing
-   ▼
-Manual testing on staging (stx.schoolcompare.co.uk)
-   │  Actions → "Promote to Production (manual)"   ← approval #2
-   ▼
-Promote pipeline (.gitea/workflows/promote.yml) — manual dispatch
-   1. resolve target sha (input, or latest main if empty)
-   2. REFUSE unless that commit's "E2E Journeys against Staging" status is green
-   3. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
+   3. Playwright E2E journeys against staging
+   4. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
       previous :prod saved as :prod-previous
-   4. prod Portainer webhook     → wait for prod health
+   5. prod Portainer webhook     → wait for prod health
 ```
 
 Key principle: **build once, promote the exact image**. Production pins `:prod`,
-which only moves when a human runs the promote workflow — and the workflow
-only accepts commits that passed the staging E2E gate. Nothing tags `:latest`
+which only moves after the E2E gate passes on staging. Nothing tags `:latest`
 anymore.
 
 ## Branch & PR workflow
 
 - `main` is protected: no direct pushes, PRs require green status checks.
 - All work (human or AI) happens on feature branches → PR to `main`.
-- Merging to `main` releases **to staging only**. Production moves only on
-  the second approval. If staging or the E2E gate fails, fix forward —
-  production is untouched either way.
-
-## Promotion granularity
-
-Staging always runs the latest `main`. Promoting approves a *state of main*,
-not a single PR — if two PRs merged since the last promotion, they ship
-together. Test staging accordingly. To promote an older state, pass its
-commit SHA to the promote workflow (its images must still exist in the
-registry).
-
-Staging quirk for manual testing: external `/api` is broken at the staging
-proxy — exercise API endpoints from the host, not via the public staging URL.
+- Merging to `main` **is** the release action. If staging or the E2E gate
+  fails, production is untouched.
 
 ## Environments
 
@@ -112,9 +92,8 @@ fail the E2E gate. That's the point: staging absorbs the risk.
 
 ## Rollback
 
-Re-run "Promote to Production (manual)" with the SHA of the last good commit
-(fastest, fully gated), or manually re-point the tags — every promotion first
-saves the outgoing `:prod` as `:prod-previous`:
+Every promotion first re-points `:prod-previous` at the outgoing `:prod`.
+To roll back:
 
 ```bash
 for img in backend frontend pipeline; do

docs/superpowers/plans/2026-07-13-staged-prod-promotion.md

@@ -1,275 +0,0 @@
-# Staged Production Promotion (Manual Gate) Implementation Plan
-
-> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
-
-**Goal:** Merging a PR deploys to staging only; production deployment requires a second, explicit human approval after manual testing on staging.
-
-**Architecture:** Split the existing single `deploy.yml` pipeline in two. The push-to-main workflow keeps build → staging deploy → e2e gate and **stops there**. A new `promote.yml` runs only on `workflow_dispatch` (the "Run workflow" button in Gitea's Actions UI, supported on this server — Gitea 1.26.4): it verifies the chosen commit passed the staging e2e gate, retags its `:sha-*` images to `:prod` (keeping `:prod-previous` for rollback), and triggers the Portainer prod webhook. Promotion granularity is a main-branch commit: staging always runs the latest main, so you approve a *state of main*, not an individual PR.
-
-**Tech Stack:** Gitea Actions (1.26.4), Docker buildx imagetools, Portainer webhooks, Gitea commit-status API.
-
-## Global Constraints
-
-- **Never push to `main` directly** — this change itself goes through a PR (`chore/staged-prod-promotion` branch).
-- Existing image tagging scheme is unchanged: `type=sha` (e.g. `sha-6f925ab`) + `:staging`; promotion still retags `:sha-*` → `:prod` with `:prod-previous` kept as the rollback pointer.
-- The e2e journeys remain a **hard gate before human testing** (a red staging never reaches the promote button) and the promote workflow must refuse to promote a commit whose staging e2e did not succeed.
-- Secrets already exist and are reused: `REGISTRY_TOKEN` (also a Gitea API token), `PORTAINER_STAGING_WEBHOOK`, `PORTAINER_PROD_WEBHOOK`, `STAGING_BASE_URL`, `PROD_BASE_URL`.
-- Staging quirk (memory): external `/api` is broken at the staging proxy — manual API testing happens from the host, not through stx.schoolcompare.co.uk; note it in the runbook, don't try to fix it in this plan.
-
-## Considered approaches (context for the reviewer)
-
-1. **Manual `workflow_dispatch` promote workflow (chosen).** Native on Gitea 1.26; the second approval is clicking "Run workflow" (or one API call) after testing staging. Least machinery, auditable via the Actions run history.
-2. *Tag-driven promotion* (`push: tags: promote-*`): works on any Gitea version; approval = pushing a tag. Slightly more scriptable, less discoverable; kept as documented fallback only.
-3. *GitOps `production` branch + promotion PR:* approval literally reuses the PR-review UI, but adds a second long-lived branch to keep in sync — too much ceremony for a solo project. Rejected.
-
----
-
-### Task 0: Branch
-
-- [ ] `git checkout main && git pull && git checkout -b chore/staged-prod-promotion`
-
----
-
-### Task 1: Stop the push-to-main workflow after the e2e gate
-
-**Files:**
-- Modify: `.gitea/workflows/deploy.yml`
-
-**Interfaces:**
-- Produces: images tagged `:sha-<short>` + `:staging` (unchanged), a green `E2E Journeys against Staging` commit status that Task 2's promote workflow checks by name. **Do not rename the `e2e-staging` job's `name:` without updating Task 2's status check.**
-
-- [ ] **Step 1: Remove the auto-promotion**
-
-In `.gitea/workflows/deploy.yml`:
-1. Change line 1 to: `name: Stage (build -> staging -> E2E gate)`
-2. Delete the entire `promote-prod` job (lines 196–240 in the current file: from `  promote-prod:` to the end of the file).
-3. Leave `build-*`, `deploy-staging`, and `e2e-staging` untouched.
-
-- [ ] **Step 2: Sanity-check the YAML**
-
-Run: `python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/deploy.yml')); print('yaml ok')"`
-Expected: `yaml ok`
-
-- [ ] **Step 3: Commit**
-
-```bash
-git add .gitea/workflows/deploy.yml
-git commit -m "ci: stop deploy pipeline at staging; production promotion becomes manual"
-```
-
----
-
-### Task 2: Manual promote workflow
-
-**Files:**
-- Create: `.gitea/workflows/promote.yml`
-
-**Interfaces:**
-- Consumes: `:sha-<short>` images built by deploy.yml; the `E2E Journeys against Staging` commit status.
-- Produces: `:prod` and `:prod-previous` tags; prod stack update.
-
-- [ ] **Step 1: Write the workflow**
-
-```yaml
-name: Promote to Production (manual)
-
-on:
-  workflow_dispatch:
-    inputs:
-      sha:
-        description: >-
-          Commit SHA on main to promote (full or >=7 chars).
-          Leave empty to promote the latest main commit.
-        required: false
-        default: ""
-
-env:
-  REGISTRY: privaterepo.sitaru.org
-  BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
-  FRONTEND_IMAGE_NAME: ${{ gitea.repository }}-frontend
-  PIPELINE_IMAGE_NAME: ${{ gitea.repository }}-pipeline
-
-jobs:
-  promote-prod:
-    name: Promote approved commit to Production
-    runs-on: ubuntu-latest
-    steps:
-      - name: Resolve target SHA
-        id: resolve
-        run: |
-          SHA_INPUT="${{ gitea.event.inputs.sha }}"
-          if [ -z "$SHA_INPUT" ]; then
-            SHA_INPUT="${{ gitea.sha }}"
-          fi
-          # Normalise to the full sha via the API so short inputs work
-          FULL_SHA=$(curl -fsS \
-            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
-            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/git/commits/${SHA_INPUT}" \
-            | python3 -c "import json,sys; print(json.load(sys.stdin)['sha'])")
-          SHORT_SHA="sha-$(echo "$FULL_SHA" | cut -c1-7)"
-          echo "full=$FULL_SHA" >> "$GITHUB_OUTPUT"
-          echo "short=$SHORT_SHA" >> "$GITHUB_OUTPUT"
-          echo "Promoting $FULL_SHA (images tagged $SHORT_SHA)"
-
-      - name: Verify the staging E2E gate passed for this commit
-        run: |
-          STATUS_JSON=$(curl -fsS \
-            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
-            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/commits/${{ steps.resolve.outputs.full }}/status")
-          echo "$STATUS_JSON" | python3 -c "
-          import json, sys
-          d = json.load(sys.stdin)
-          ok = [s for s in d.get('statuses', [])
-                if 'E2E Journeys against Staging' in s.get('context', '')
-                and s.get('status') == 'success']
-          if not ok:
-              print('REFUSED: no successful \"E2E Journeys against Staging\" status on this commit.')
-              print('Contexts found:', [s.get('context') for s in d.get('statuses', [])])
-              sys.exit(1)
-          print('E2E gate verified green for this commit.')
-          "
-
-      - name: Set up Docker Buildx
-        uses: docker/setup-buildx-action@v3
-
-      - name: Log in to Gitea Container Registry
-        uses: docker/login-action@v3
-        with:
-          registry: ${{ env.REGISTRY }}
-          username: ${{ gitea.actor }}
-          password: ${{ secrets.REGISTRY_TOKEN }}
-
-      - name: Retag approved images as prod (keeping rollback pointer)
-        run: |
-          SHORT_SHA="${{ steps.resolve.outputs.short }}"
-          for IMAGE in \
-            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
-            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
-            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
-            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
-            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
-            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
-          done
-
-      - name: Trigger production stack update
-        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
-
-      - name: Wait for production to become healthy
-        run: |
-          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
-          for i in $(seq 1 60); do
-            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
-              echo "Production is up (attempt $i)"
-              exit 0
-            fi
-            sleep 5
-          done
-          echo "Production did not become healthy in time" >&2
-          exit 1
-        env:
-          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}
-```
-
-Implementation notes for the engineer:
-- Gitea Actions uses the GitHub-compatible `$GITHUB_OUTPUT` file for step outputs; if the runner image doesn't populate it, fall back to `$GITEA_OUTPUT` (check the runner's docs/output at first run).
-- The retag step is copied verbatim from the old `promote-prod` job except the SHA comes from the resolved input instead of `gitea.sha` — behaviour for the default (empty input on latest main) is identical to before.
-- If `docker buildx imagetools create` fails with "not found" for `${IMAGE}:${SHORT_SHA}`, the chosen commit predates the registry's retention or never built — the error message is the desired behaviour (refuse loudly).
-
-- [ ] **Step 2: YAML sanity check**
-
-Run: `python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/promote.yml')); print('yaml ok')"`
-Expected: `yaml ok`
-
-- [ ] **Step 3: Commit**
-
-```bash
-git add .gitea/workflows/promote.yml
-git commit -m "ci: manual production promotion workflow with e2e-gate verification"
-```
-
----
-
-### Task 3: Documentation — deploy model + runbook
-
-**Files:**
-- Modify: `docs/DEPLOY.md`
-- Modify: `claude.md` (the SDLC section)
-
-- [ ] **Step 1: Rewrite the flow description in `docs/DEPLOY.md`**
-
-Replace the staging→prod description with the new model (adapt to the file's existing structure; the substance to convey):
-
-```markdown
-## Deploy model
-
-1. **PR → main (first approval).** Branch-protected merge; PR checks
-   (typecheck, tests, builds, AI review) must pass.
-2. **Merge → staging (automatic).** Images are built once and tagged
-   `sha-<short>` + `staging`; the staging stack updates; Playwright
-   journeys in `e2e/` run against staging. A red e2e run means staging
-   is not fit for testing — fix forward before considering promotion.
-3. **Manual testing on staging.** stx.schoolcompare.co.uk. Note:
-   external `/api` is broken at the staging proxy — exercise API
-   endpoints from the host.
-4. **Promote → production (second approval).** Actions → "Promote to
-   Production (manual)" → Run workflow. Leave the SHA empty to promote
-   the latest main, or paste a specific commit SHA. The workflow
-   refuses commits whose staging e2e gate is not green, retags the
-   images `:prod` (keeping `:prod-previous`), and updates the prod
-   stack.
-
-### Promotion granularity
-
-Staging always runs the latest `main`. Promoting approves a *state of
-main*, not a single PR — if two PRs merged since the last promotion,
-they ship together. Test staging accordingly.
-
-### Rollback
-
-Re-run "Promote to Production (manual)" with the SHA of the last good
-commit (or retag manually: `docker buildx imagetools create -t
-<image>:prod <image>:prod-previous` for each of the three images, then
-POST the prod Portainer webhook).
-```
-
-- [ ] **Step 2: Update the SDLC bullet in `claude.md`**
-
-Replace the sentence "Merging to `main` deploys automatically: … retagged `:prod` and rolled out to production." with:
-
-```markdown
-- Merging to `main` deploys automatically **to staging only**: images
-  are built once, deployed to the staging Portainer stack, and verified
-  by the Playwright journeys in `e2e/`. Production is a second, manual
-  approval: the "Promote to Production (manual)" workflow in Gitea
-  Actions, run after testing the feature on staging. It refuses commits
-  whose staging e2e gate isn't green.
-```
-
-- [ ] **Step 3: Commit**
-
-```bash
-git add docs/DEPLOY.md claude.md
-git commit -m "docs: two-stage deploy model (staging auto, production manual)"
-```
-
----
-
-### Task 4: PR + live validation
-
-- [ ] **Step 1: Push and open the PR** (Gitea API with credential-helper basic auth, as usual). PR body: the new model in three lines, the rollback recipe, and a warning that between merging this PR and its first promotion run, production receives no deployments (expected).
-
-- [ ] **Step 2: Validate after merge (human-in-the-loop):**
-1. Merge this PR → confirm the `Stage (build -> staging -> E2E gate)` run goes green and **no** production deployment happens (prod image digest unchanged: `docker buildx imagetools inspect <image>:prod` before/after, or check the Portainer prod stack's last-update time).
-2. Test something trivial on staging.
-3. Run "Promote to Production (manual)" with the SHA empty → confirm e2e verification passes, retag happens, prod becomes healthy.
-4. Negative test: run the promote workflow with a garbage SHA (e.g. `deadbeef1`) → confirm it fails at resolve/verify without touching `:prod`.
-
-- [ ] **Step 3: Update the ledger/memory** with the new deploy model so future sessions stop assuming auto-promotion.
-
----
-
-## Out of scope / future options
-
-- Notifications when staging is ready for testing (Gitea can email on workflow completion; a webhook to ntfy/Matrix could be added later).
-- Restricting who can run the promote workflow: Gitea 1.26 runs `workflow_dispatch` with the permissions of the dispatching user; for a solo repo this is already effectively restricted.
-- The tag-driven fallback (`on: push: tags: promote-*`) if `workflow_dispatch` ever proves unreliable on the runner.

docs/superpowers/specs/2026-07-02-ux-audit-report.md

@@ -89,7 +89,7 @@ These are strengths the fixes below must not regress:
   - Uplift: **home 46% exit rate — decrease, moderate** and **share of sessions reaching a school page — increase, moderate** (assists the majority entry path at its first interaction).
 
 - **P1.7 — The mobile hero omits the value proposition entirely** *(J1-F6)*
-  - Evidence: desktop shows the "UPDATED WITH 2026/2027 ADMISSIONS RESULTS" trust badge and the "24,000+ schools… side by side, in one place" subheading; mobile renders only the poetic H1 ("Every school in England, *compared.*") and a bare search box (`j1-home-desktop-fold.png` vs `j1-home-mobile-fold.png`).
+  - Evidence: desktop shows the "UPDATED WITH 2026/2027 ADMISSIONS RESULTS" trust badge and the "27,000+ schools… side by side, in one place" subheading; mobile renders only the poetic H1 ("Every school in England, *compared.*") and a bare search box (`j1-home-desktop-fold.png` vs `j1-home-mobile-fold.png`).
   - Criterion: mobile content parity; Nielsen #1 — first-visit orientation ("what is this, why trust it") absent on the primary viewport.
   - Argument: 63% of entries land here and 56% of traffic is mobile; a first-time visitor gets no statement of coverage, data source, or freshness above the fold. Weak value proposition at first glance is a classic bounce driver and plausibly a material slice of the 46% exit rate.
   - Recommendation: restore a compact version of the badge + one-line value prop under the mobile H1 (one text block; the fold has room above the deadline rail).

nextjs-app/components/HomeView.tsx

@@ -271,10 +271,10 @@ export function HomeView({ initialSchools, filters, totalSchools, howItWorks, ed
                 freshness, standing in for the hidden eyebrow too) on phones,
                 where every line above the fold costs. */}
             <span className={styles.heroDescriptionFull}>
-              <strong>24,000+ primary and secondary schools</strong> with Key Stage 2 SATs, GCSE results, Ofsted grades, progress scores and admissions data — side by side, in one place.
+              <strong>27,000+ primary and secondary schools</strong> with Key Stage 2 SATs, GCSE results, Ofsted grades, progress scores and admissions data — side by side, in one place.
             </span>
             <span className={styles.heroDescriptionCompact}>
-              <strong>24,000+ English schools</strong> — SATs, GCSEs, Ofsted &amp; admissions, side by side. Updated for 2026/27.
+              <strong>27,000+ English schools</strong> — SATs, GCSEs, Ofsted &amp; admissions, side by side. Updated for 2026/27.
             </span>
           </p>
         </div>

pipeline/dags/school_data_pipeline.py

@@ -38,31 +38,6 @@ default_args = {
     "retry_delay": timedelta(minutes=5),
 }
 
-# The backend caches the marts DataFrame at startup; after any rebuild the
-# cache must be invalidated or the API serves stale (or empty) data until the
-# container restarts.
-INVALIDATE_CACHE_CMD = """
-set -e
-BACKEND_URL="${BACKEND_URL:-http://backend:80}"
-ADMIN_KEY="${ADMIN_API_KEY:-changeme}"
-
-echo "Calling $BACKEND_URL/api/admin/reload ..."
-
-response=$(curl -s -o /tmp/reload_response.json -w "%{http_code}" \\
-    --connect-timeout 10 --max-time 120 \\
-    -X POST "$BACKEND_URL/api/admin/reload" \\
-    -H "X-API-Key: $ADMIN_KEY" \\
-    -H "Content-Type: application/json")
-
-echo "HTTP status: $response"
-cat /tmp/reload_response.json
-
-if [ "$response" != "200" ]; then
-    echo "ERROR: backend cache reload failed (HTTP $response)"
-    exit 1
-fi
-"""
-
 
 # ── Daily DAG (GIAS + downstream) ──────────────────────────────────────
 
@@ -116,12 +91,7 @@ print(f'Validation passed: {{count}} GIAS rows')
         bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
     )
 
-    invalidate_cache = BashOperator(
-        task_id="invalidate_cache",
-        bash_command=INVALIDATE_CACHE_CMD,
-    )
-
-    extract_group >> validate_raw >> dbt_build >> sync_typesense >> invalidate_cache
+    extract_group >> validate_raw >> dbt_build >> sync_typesense
 
 
 # ── Monthly DAG (Ofsted) ───────────────────────────────────────────────
@@ -151,12 +121,7 @@ with DAG(
         bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
     )
 
-    invalidate_cache_ofsted = BashOperator(
-        task_id="invalidate_cache",
-        bash_command=INVALIDATE_CACHE_CMD,
-    )
-
-    extract_ofsted >> dbt_build_ofsted >> sync_typesense_ofsted >> invalidate_cache_ofsted
+    extract_ofsted >> dbt_build_ofsted >> sync_typesense_ofsted
 
 
 # ── Annual DAG (EES: KS2, KS4, Census, Admissions) ───────────────────
@@ -188,12 +153,7 @@ with DAG(
         bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
     )
 
-    invalidate_cache_ees = BashOperator(
-        task_id="invalidate_cache",
-        bash_command=INVALIDATE_CACHE_CMD,
-    )
-
-    extract_ees_group >> dbt_build_ees >> sync_typesense_ees >> invalidate_cache_ees
+    extract_ees_group >> dbt_build_ees >> sync_typesense_ees
 
 
 # ── Annual DAG (IDACI Deprivation) ────────────────────────────────────
@@ -218,9 +178,4 @@ with DAG(
         bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_idaci+ fact_deprivation+",
     )
 
-    invalidate_cache_idaci = BashOperator(
-        task_id="invalidate_cache",
-        bash_command=INVALIDATE_CACHE_CMD,
-    )
-
-    extract_idaci >> dbt_build_idaci >> invalidate_cache_idaci
+    extract_idaci >> dbt_build_idaci