feat(api): compare school_info carries GIAS facts for the community section

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB

.gitea/workflows/deploy.yml

@@ -1,4 +1,4 @@
-name: Deploy (staging -> E2E gate -> production)
+name: Stage (build -> staging -> E2E gate)
 
 on:
   push:
@@ -193,48 +193,5 @@ jobs:
         env:
           BASE_URL: ${{ secrets.STAGING_BASE_URL }}
 
-  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 }}
+# Production deployment is a second, manual approval: see promote.yml
+# ("Promote to Production (manual)") and docs/DEPLOY.md.

.gitea/workflows/promote.yml

@@ -0,0 +1,126 @@
+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/app.py

@@ -25,6 +25,7 @@ import asyncio
 from .config import settings
 from .data_loader import (
     clear_cache,
+    compute_benchmarks,
     load_school_data,
     load_latest_school_data,
     geocode_single_postcode,
@@ -662,6 +663,34 @@ async def compare_schools(
     if comparison_data.empty:
         raise HTTPException(status_code=404, detail="No schools found")
 
+    # One session for all schools' supplementary blocks; failures degrade
+    # to empty blocks rather than failing a working comparison (mirrors
+    # the detail endpoint's defensive pattern).
+    from . import database
+
+    _EMPTY_SUPPLEMENTARY = {
+        "ofsted": None,
+        "census": None,
+        "admissions": None,
+        "admissions_history": [],
+        "deprivation": None,
+    }
+    supplementary_by_urn: dict = {}
+    db = None
+    try:
+        db = database.SessionLocal()
+        for urn in urn_list:
+            supp = get_supplementary_data(db, urn)
+            supplementary_by_urn[urn] = {
+                key: supp.get(key, default)
+                for key, default in _EMPTY_SUPPLEMENTARY.items()
+            }
+    except Exception:
+        supplementary_by_urn = {}
+    finally:
+        if db is not None:
+            db.close()
+
     result = {}
     for urn in urn_list:
         school_data = comparison_data[comparison_data["urn"] == urn].sort_values("year")
@@ -677,11 +706,27 @@ async def compare_schools(
                     "phase": latest.get("phase", ""),
                     "attainment_8_score": float(latest["attainment_8_score"]) if pd.notna(latest.get("attainment_8_score")) else None,
                     "rwm_expected_pct": float(latest["rwm_expected_pct"]) if pd.notna(latest.get("rwm_expected_pct")) else None,
+                    # GIAS facts the compare "Who goes there" section needs
+                    # (same fields the detail endpoint exposes)
+                    "religious_denomination": convert_to_native(latest.get("religious_denomination")),
+                    "age_range": convert_to_native(latest.get("age_range")),
+                    "gender": convert_to_native(latest.get("gender")),
+                    "has_sixth_form": convert_to_native(latest.get("has_sixth_form")),
+                    "capacity": convert_to_native(latest.get("capacity")),
+                    "gias_total_pupils": convert_to_native(latest.get("gias_total_pupils")),
+                    "trust_name": convert_to_native(latest.get("trust_name")),
                 },
                 "yearly_data": clean_for_json(school_data),
+                **supplementary_by_urn.get(urn, dict(_EMPTY_SUPPLEMENTARY)),
             }
 
-    return {"comparison": result}
+    return {
+        "comparison": result,
+        # Official DfE anchors + computed state-school benchmarks so the
+        # compare UI can label provenance correctly (spec §8.6).
+        "national_averages": _national_averages_payload(df),
+        "benchmarks": compute_benchmarks(df),
+    }
 
 
 @app.get("/api/filters")
@@ -727,22 +772,17 @@ async def get_la_averages(request: Request):
     return {"year": latest_year, "secondary": {"attainment_8_by_la": la_avg}}
 
 
-@app.get("/api/national-averages")
-@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
-async def get_national_averages(request: Request):
-    """
-    Compute national average for each metric from the latest data year.
-    Returns separate averages for primary (KS2) and secondary (KS4) schools.
-    Values are derived from the loaded DataFrame so they automatically
-    stay current when new data is loaded.
-    """
-    df = load_school_data()
+def _national_averages_payload(df: pd.DataFrame) -> dict:
+    """National-averages payload shared by /api/national-averages and
+    /api/compare. Official DfE KS2 figures come from the mart table;
+    KS4 figures are computed from our dataset (no DfE dataset yet)."""
     if df.empty:
         return {"primary": {}, "secondary": {}}
 
     ks2_metrics = [
         "rwm_expected_pct", "rwm_high_pct",
         "reading_expected_pct", "writing_expected_pct", "maths_expected_pct",
+        "gps_expected_pct", "gps_high_pct", "science_expected_pct",
         "reading_avg_score", "maths_avg_score", "gps_avg_score",
         "reading_progress", "writing_progress", "maths_progress",
         "overall_absence_pct", "persistent_absence_pct",
@@ -777,12 +817,13 @@ async def get_national_averages(request: Request):
 
     # Per-year KS2 primary averages: use official DfE figures from the mart table.
     # Per-year KS4 secondary averages: computed from our dataset (no DfE dataset yet).
-    from .database import SessionLocal
+    from . import database
     from .models import Ks2NationalAverage
 
     by_year = []
+    db = None
     try:
-        db = SessionLocal()
+        db = database.SessionLocal()
         nat_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
         # Build a lookup of computed secondary averages per year as fallback
         secondary_by_year = {}
@@ -810,7 +851,8 @@ async def get_national_averages(request: Request):
                 "secondary": secondary_by_year.get(yr, {}),
             })
     finally:
-        db.close()
+        if db is not None:
+            db.close()
 
     # Update latest_primary with official DfE figure for the latest year if available
     if by_year:
@@ -826,6 +868,17 @@ async def get_national_averages(request: Request):
     }
 
 
+@app.get("/api/national-averages")
+@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
+async def get_national_averages(request: Request):
+    """
+    National averages: official DfE KS2 figures per year plus computed
+    KS4 averages, derived from the loaded DataFrame and the
+    fact_ks2_national_averages mart.
+    """
+    return _national_averages_payload(load_school_data())
+
+
 @app.get("/api/metrics")
 @limiter.limit(f"{settings.rate_limit_per_minute}/minute")
 async def get_available_metrics(request: Request):

backend/data_loader.py

@@ -21,6 +21,7 @@ from .models import (
     FactOfstedInspection, FactAdmissions,
     FactDeprivation, FactFinance, FactPupilCharacteristics,
 )
+from .ofsted_codes import ofsted_page_url, report_card_labels
 from .schemas import SCHOOL_TYPE_MAP
 from .gias_codes import (
     ADMISSIONS_POLICY,
@@ -190,13 +191,20 @@ _MAIN_QUERY = text("""
         p.reading_high_pct,
         p.reading_avg_score,
         p.reading_progress,
+        p.reading_progress_lower_ci,
+        p.reading_progress_upper_ci,
         p.writing_expected_pct,
         p.writing_high_pct,
         p.writing_progress,
+        p.writing_progress_lower_ci,
+        p.writing_progress_upper_ci,
+        p.writing_working_towards_pct,
         p.maths_expected_pct,
         p.maths_high_pct,
         p.maths_avg_score,
         p.maths_progress,
+        p.maths_progress_lower_ci,
+        p.maths_progress_upper_ci,
         p.gps_expected_pct,
         p.gps_high_pct,
         p.gps_avg_score,
@@ -225,6 +233,9 @@ _MAIN_QUERY = text("""
         p.progress_8_maths,
         p.progress_8_ebacc,
         p.progress_8_open,
+        p.progress_8_banding,
+        p.attainment_8_disadvantage_gap,
+        p.progress_8_disadvantage_gap,
         p.english_maths_strong_pass_pct,
         p.english_maths_standard_pass_pct,
         p.ebacc_entry_pct,
@@ -514,6 +525,143 @@ def get_data_info(db: Session = None) -> dict:
 # SUPPLEMENTARY DATA — per-school detail page
 # =============================================================================
 
+def compute_benchmarks(df: pd.DataFrame) -> dict:
+    """State-school benchmarks computed from our dataset (spec §5/§8.6).
+
+    NOT official DfE figures — consumers must label them
+    "state-school average (computed from our dataset)". The disadvantaged
+    attainment average is weighted by cohort size (eligible_pupils) so
+    small schools don't dominate; context measures are medians.
+    """
+    if df.empty or "year" not in df.columns:
+        return {}
+    latest_year = df["year"].max()
+    if pd.isna(latest_year):
+        return {}
+    d = df[df["year"] == latest_year]
+    if d.empty:
+        return {}
+    is_secondary = (
+        d["attainment_8_score"].notna()
+        if "attainment_8_score" in d.columns
+        else pd.Series(False, index=d.index)
+    )
+    prim, sec = d[~is_secondary], d[is_secondary]
+
+    def _median(sub, col):
+        if col not in sub.columns:
+            return None
+        v = sub[col].median()
+        return round(float(v), 1) if pd.notna(v) else None
+
+    def _weighted_disadvantaged(sub):
+        needed = {"rwm_expected_disadvantaged_pct", "eligible_pupils"}
+        if not needed <= set(sub.columns):
+            return None
+        s = sub.dropna(subset=list(needed))
+        if s.empty or s["eligible_pupils"].sum() == 0:
+            return None
+        w = (
+            (s["rwm_expected_disadvantaged_pct"] * s["eligible_pupils"]).sum()
+            / s["eligible_pupils"].sum()
+        )
+        return round(float(w), 1)
+
+    def _block(sub, with_disadvantaged):
+        median_pupils = None
+        if "total_pupils" in sub.columns:
+            mp = sub["total_pupils"].median()
+            if pd.notna(mp):
+                median_pupils = int(mp)
+        block = {
+            "eal_pct": _median(sub, "eal_pct"),
+            "sen_support_pct": _median(sub, "sen_support_pct"),
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
+            "median_pupils": median_pupils,
+        }
+        if with_disadvantaged:
+            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
+        return block
+
+    return {
+        "source": "state-school average (computed from our dataset)",
+        "year": int(latest_year),
+        "primary": _block(prim, with_disadvantaged=True),
+        "secondary": _block(sec, with_disadvantaged=False),
+    }
+
+
+def _ofsted_block(o, urn: int) -> dict:
+    """Serialize the latest Ofsted inspection row for API responses.
+
+    `grade_source` records where the effective overall grade came from:
+    a graded (Section 5) inspection, or carried forward from an ungraded
+    (Section 8) outcome — materially different claims a UI must be able
+    to distinguish. `report_card` holds coded+labelled renewed-framework
+    (Nov 2025) area judgements; safeguarding is a separate boolean and
+    never appears among the graded areas.
+    """
+    if o.overall_effectiveness is not None:
+        grade_source = "graded"
+        overall = o.overall_effectiveness
+    elif o.ungraded_grade is not None:
+        # Fall back to the grade parsed from an ungraded (Section 8) outcome
+        # (e.g. "School remains Good") so the detail page matches the list badge.
+        grade_source = "ungraded_carried_forward"
+        overall = o.ungraded_grade
+    else:
+        grade_source = None
+        overall = None
+
+    block = {
+        "framework": o.framework,
+        "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
+        "inspection_type": o.inspection_type,
+        "overall_effectiveness": overall,
+        "grade_source": grade_source,
+        "quality_of_education": o.quality_of_education,
+        "behaviour_attitudes": o.behaviour_attitudes,
+        "personal_development": o.personal_development,
+        "leadership_management": o.leadership_management,
+        "early_years_provision": o.early_years_provision,
+        "sixth_form_provision": o.sixth_form_provision,
+        "previous_overall": None,  # Not available in new schema
+        "rc_safeguarding_met": o.rc_safeguarding_met,
+        "rc_inclusion": o.rc_inclusion,
+        "rc_curriculum_teaching": o.rc_curriculum_teaching,
+        "rc_achievement": o.rc_achievement,
+        "rc_attendance_behaviour": o.rc_attendance_behaviour,
+        "rc_personal_development": o.rc_personal_development,
+        "rc_leadership_governance": o.rc_leadership_governance,
+        "rc_early_years": o.rc_early_years,
+        "rc_sixth_form": o.rc_sixth_form,
+        "report_url": o.report_url,
+        "ofsted_page_url": ofsted_page_url(urn),
+    }
+    block["report_card"] = report_card_labels(block)
+    return block
+
+
+def _admissions_row_dict(a) -> dict:
+    """Serialize one fact_admissions row for API responses."""
+    return {
+        "year": a.year,
+        "school_phase": a.school_phase,
+        "places_offered": a.places_offered,
+        "total_applications": a.total_applications,
+        "first_preference_applications": a.first_preference_applications,
+        "first_preference_offers": a.first_preference_offers,
+        "first_preference_offer_pct": a.first_preference_offer_pct,
+        "oversubscription_ratio": a.oversubscription_ratio,
+        "oversubscribed": a.oversubscribed,
+        "total_offers": a.total_offers,
+        "second_preference_offers": a.second_preference_offers,
+        "third_preference_offers": a.third_preference_offers,
+        "cross_la_applications": a.cross_la_applications,
+        "cross_la_offers": a.cross_la_offers,
+    }
+
+
 def get_supplementary_data(db: Session, urn: int) -> dict:
     """Fetch all supplementary data for a single school URN."""
     result = {}
@@ -532,40 +680,7 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
 
     # Latest Ofsted inspection
     o = safe_query(FactOfstedInspection, "urn", "inspection_date")
-    result["ofsted"] = (
-        {
-            "framework": o.framework,
-            "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
-            "inspection_type": o.inspection_type,
-            # Fall back to the grade parsed from an ungraded (Section 8) outcome
-            # (e.g. "School remains Good") when there's no graded grade, so the
-            # detail page matches the list badge.
-            "overall_effectiveness": (
-                o.overall_effectiveness
-                if o.overall_effectiveness is not None
-                else o.ungraded_grade
-            ),
-            "quality_of_education": o.quality_of_education,
-            "behaviour_attitudes": o.behaviour_attitudes,
-            "personal_development": o.personal_development,
-            "leadership_management": o.leadership_management,
-            "early_years_provision": o.early_years_provision,
-            "sixth_form_provision": o.sixth_form_provision,
-            "previous_overall": None,  # Not available in new schema
-            "rc_safeguarding_met": o.rc_safeguarding_met,
-            "rc_inclusion": o.rc_inclusion,
-            "rc_curriculum_teaching": o.rc_curriculum_teaching,
-            "rc_achievement": o.rc_achievement,
-            "rc_attendance_behaviour": o.rc_attendance_behaviour,
-            "rc_personal_development": o.rc_personal_development,
-            "rc_leadership_governance": o.rc_leadership_governance,
-            "rc_early_years": o.rc_early_years,
-            "rc_sixth_form": o.rc_sixth_form,
-            "report_url": o.report_url,
-        }
-        if o
-        else None
-    )
+    result["ofsted"] = _ofsted_block(o, urn) if o else None
 
     # Census (latest year of fact_pupil_characteristics)
     pc = safe_query(FactPupilCharacteristics, "urn", "year")
@@ -583,19 +698,6 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
     )
 
     # Admissions — all years, oldest first (for the multi-year trend view).
-    def _admissions_row(a):
-        return {
-            "year": a.year,
-            "school_phase": a.school_phase,
-            "places_offered": a.places_offered,
-            "total_applications": a.total_applications,
-            "first_preference_applications": a.first_preference_applications,
-            "first_preference_offers": a.first_preference_offers,
-            "first_preference_offer_pct": a.first_preference_offer_pct,
-            "oversubscription_ratio": a.oversubscription_ratio,
-            "oversubscribed": a.oversubscribed,
-        }
-
     try:
         admissions_rows = (
             db.query(FactAdmissions)
@@ -609,7 +711,7 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
         db.rollback()
         admissions_rows = []
 
-    history = [_admissions_row(a) for a in admissions_rows]
+    history = [_admissions_row_dict(a) for a in admissions_rows]
     result["admissions_history"] = history
     # Keep the single latest-year object for backwards-compatible consumers
     # (hero chips, etc.).

backend/models.py

@@ -88,6 +88,15 @@ class KS2Performance(Base):
     maths_high_pct = Column(Float)
     maths_avg_score = Column(Float)
     maths_progress = Column(Float)
+    # Progress confidence intervals + writing working-towards (published
+    # for years with progress measures, i.e. up to 2022/23)
+    reading_progress_lower_ci = Column(Float)
+    reading_progress_upper_ci = Column(Float)
+    writing_progress_lower_ci = Column(Float)
+    writing_progress_upper_ci = Column(Float)
+    writing_working_towards_pct = Column(Float)
+    maths_progress_lower_ci = Column(Float)
+    maths_progress_upper_ci = Column(Float)
     gps_expected_pct = Column(Float)
     gps_high_pct = Column(Float)
     gps_avg_score = Column(Float)
@@ -165,6 +174,11 @@ class FactAdmissions(Base):
     total_applications = Column(Integer)
     first_preference_applications = Column(Integer)
     first_preference_offers = Column(Integer)
+    total_offers = Column(Integer)
+    second_preference_offers = Column(Integer)
+    third_preference_offers = Column(Integer)
+    cross_la_applications = Column(Integer)
+    cross_la_offers = Column(Integer)
     first_preference_offer_pct = Column(Float)
     oversubscription_ratio = Column(Float)
     oversubscribed = Column(Boolean)

backend/ofsted_codes.py

@@ -0,0 +1,44 @@
+"""Ofsted renewed-framework (Nov 2025) report-card code translation.
+
+Scale labels are the live-sampled vocabulary from the Ofsted MI file
+(see pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE) —
+verified against real data, not the consultation draft.
+"""
+
+REPORT_CARD_GRADE_NAMES = {
+    1: "Exceptional",
+    2: "Strong standard",
+    3: "Expected standard",
+    4: "Needs attention",
+    5: "Urgent improvement",
+}
+
+# Graded evaluation areas only — safeguarding is a separate boolean
+# judgement and must never appear in grade counts or label maps.
+_RC_AREA_KEYS = (
+    "rc_inclusion",
+    "rc_curriculum_teaching",
+    "rc_achievement",
+    "rc_attendance_behaviour",
+    "rc_personal_development",
+    "rc_leadership_governance",
+    "rc_early_years",
+    "rc_sixth_form",
+)
+
+
+def report_card_labels(ofsted: dict) -> dict:
+    """{area_key: {code, label}} for populated, known-valued rc_* areas."""
+    out = {}
+    for key in _RC_AREA_KEYS:
+        code = ofsted.get(key)
+        label = REPORT_CARD_GRADE_NAMES.get(code)
+        if code is not None and label is not None:
+            out[key] = {"code": code, "label": label}
+    return out
+
+
+def ofsted_page_url(urn: int) -> str:
+    """The school's page on ofsted.gov.uk (all its reports live there —
+    we never deep-link an individual report)."""
+    return f"https://reports.ofsted.gov.uk/provider/21/{urn}"

backend/tests/test_benchmarks.py

@@ -0,0 +1,78 @@
+"""compute_benchmarks: state-school benchmarks computed from our dataset
+(spec §5/§8.6). The disadvantaged average must be weighted by cohort size,
+medians must ignore NaN, and only the latest year counts."""
+
+import numpy as np
+import pandas as pd
+
+from backend.data_loader import compute_benchmarks
+
+LATEST = 202425
+
+
+def _df():
+    rows = [
+        # Six primary schools, latest year. Disadvantaged RWM chosen so the
+        # weighted average differs clearly from the unweighted mean:
+        # weighted = (40*100 + 60*300) / 400 = 55.0 ; unweighted mean = 50.0
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=100,
+             rwm_expected_disadvantaged_pct=40.0, eal_pct=10.0,
+             sen_support_pct=10.0, disadvantaged_pct=20.0, total_pupils=200),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=300,
+             rwm_expected_disadvantaged_pct=60.0, eal_pct=20.0,
+             sen_support_pct=14.0, disadvantaged_pct=24.0, total_pupils=280),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=np.nan,
+             rwm_expected_disadvantaged_pct=99.0, eal_pct=30.0,
+             sen_support_pct=18.0, disadvantaged_pct=30.0, total_pupils=300),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=50,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=np.nan,
+             sen_support_pct=np.nan, disadvantaged_pct=np.nan, total_pupils=np.nan),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=40,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=40.0,
+             sen_support_pct=20.0, disadvantaged_pct=40.0, total_pupils=350),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=60,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=50.0,
+             sen_support_pct=22.0, disadvantaged_pct=44.0, total_pupils=400),
+        # Two secondary schools (attainment_8 non-null)
+        dict(year=LATEST, attainment_8_score=45.0, eligible_pupils=180,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=15.0,
+             sen_support_pct=12.0, disadvantaged_pct=22.0, total_pupils=1000),
+        dict(year=LATEST, attainment_8_score=50.0, eligible_pupils=200,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=25.0,
+             sen_support_pct=16.0, disadvantaged_pct=26.0, total_pupils=1200),
+        # An older-year primary row that must NOT influence anything
+        dict(year=202324, attainment_8_score=np.nan, eligible_pupils=500,
+             rwm_expected_disadvantaged_pct=1.0, eal_pct=99.0,
+             sen_support_pct=99.0, disadvantaged_pct=99.0, total_pupils=9999),
+    ]
+    return pd.DataFrame(rows)
+
+
+def test_weighted_disadvantaged_average():
+    b = compute_benchmarks(_df())
+    # Row 3 has NaN eligible_pupils and must be excluded from the weighting.
+    assert b["primary"]["disadvantaged_rwm_expected_pct"] == 55.0
+
+
+def test_medians_ignore_nan_and_older_years():
+    b = compute_benchmarks(_df())
+    assert b["year"] == LATEST
+    # eal medians over [10,20,30,40,50] = 30
+    assert b["primary"]["eal_pct"] == 30.0
+    # median pupils over [200,280,300,350,400] = 300
+    assert b["primary"]["median_pupils"] == 300
+
+
+def test_secondary_block_has_no_disadvantaged_rwm():
+    b = compute_benchmarks(_df())
+    assert "disadvantaged_rwm_expected_pct" not in b["secondary"]
+    assert b["secondary"]["median_pupils"] == 1100
+
+
+def test_provenance_string():
+    b = compute_benchmarks(_df())
+    assert b["source"] == "state-school average (computed from our dataset)"
+
+
+def test_empty_df():
+    assert compute_benchmarks(pd.DataFrame()) == {}

backend/tests/test_compare_enrichment.py

@@ -0,0 +1,121 @@
+"""/api/compare enrichment for the compare redesign: per-school
+supplementary blocks, top-level national_averages (shared with the
+/api/national-averages endpoint) and computed benchmarks — all additive."""
+
+import types
+
+import numpy as np
+import pandas as pd
+import pytest
+from fastapi.testclient import TestClient
+
+LATEST = 202425
+
+CANNED_SUPPLEMENTARY = {
+    "ofsted": {"overall_effectiveness": 2, "grade_source": "graded",
+               "report_card": {}, "ofsted_page_url": "https://reports.ofsted.gov.uk/provider/21/100140"},
+    "census": {"year": 202526, "fsm_pct": 29.8},
+    "admissions": {"year": 202627, "second_preference_offers": 4},
+    "admissions_history": [{"year": 202627, "second_preference_offers": 4}],
+    "sen_detail": None,
+    "phonics": None,
+    "deprivation": {"idaci_decile": 4},
+    "finance": None,
+}
+
+
+def _two_primary_schools_df() -> pd.DataFrame:
+    rows = []
+    for urn, name, rwm, dis in ((100140, "Plumcroft Primary School", 79.0, 72.0),
+                                (138690, "Barclay Primary School", 87.0, 86.0)):
+        rows.append(dict(
+            urn=urn, school_name=name, local_authority="Greenwich",
+            school_type="Community school", address="1 Road", phase="Primary",
+            year=LATEST, rwm_expected_pct=rwm, attainment_8_score=np.nan,
+            eligible_pupils=60, rwm_expected_disadvantaged_pct=dis,
+            eal_pct=20.0, sen_support_pct=14.0, disadvantaged_pct=25.0,
+            total_pupils=1000.0,
+        ))
+    return pd.DataFrame(rows)
+
+
+class _StubNatRow:
+    year = 202425
+    rwm_expected_pct = 62.1
+    gps_expected_pct = 72.0
+    science_expected_pct = 81.0
+
+
+class _StubSession:
+    def query(self, *a, **k):
+        return self
+
+    def order_by(self, *a, **k):
+        return self
+
+    def all(self):
+        return [_StubNatRow()]
+
+    def close(self):
+        pass
+
+
+@pytest.fixture()
+def client(monkeypatch):
+    from backend import app as app_module
+    from backend import database as database_module
+
+    monkeypatch.setattr(app_module, "load_school_data", _two_primary_schools_df)
+    monkeypatch.setattr(
+        app_module, "get_supplementary_data", lambda db, urn: dict(CANNED_SUPPLEMENTARY)
+    )
+    monkeypatch.setattr(database_module, "SessionLocal", _StubSession)
+    return TestClient(app_module.app, raise_server_exceptions=False)
+
+
+def test_existing_shape_is_preserved(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    school = body["comparison"]["100140"]
+    assert school["school_info"]["rwm_expected_pct"] == 79.0
+    assert school["yearly_data"][0]["year"] == LATEST
+
+
+def test_each_school_gains_supplementary_blocks(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    for urn in ("100140", "138690"):
+        school = body["comparison"][urn]
+        assert school["ofsted"]["grade_source"] == "graded"
+        assert school["census"]["fsm_pct"] == 29.8
+        assert school["admissions"]["second_preference_offers"] == 4
+        assert school["admissions_history"][0]["year"] == 202627
+        assert school["deprivation"]["idaci_decile"] == 4
+
+
+def test_top_level_national_averages_and_benchmarks(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    assert body["national_averages"]["year"] == LATEST
+    assert body["benchmarks"]["source"] == "state-school average (computed from our dataset)"
+    # weighted over equal cohorts of 72 and 86 = 79.0
+    assert body["benchmarks"]["primary"]["disadvantaged_rwm_expected_pct"] == 79.0
+
+
+def test_supplementary_failure_degrades_not_500(client, monkeypatch):
+    from backend import app as app_module
+
+    def _boom(db, urn):
+        raise RuntimeError("marts unavailable")
+
+    monkeypatch.setattr(app_module, "get_supplementary_data", _boom)
+    resp = client.get("/api/compare?urns=100140")
+    assert resp.status_code == 200
+    school = resp.json()["comparison"]["100140"]
+    assert school["ofsted"] is None
+    assert school["admissions_history"] == []
+
+
+def test_national_averages_endpoint_exposes_gps_science(client):
+    body = client.get("/api/national-averages").json()
+    latest_primary_by_year = [e["primary"] for e in body["by_year"] if e["primary"]]
+    assert latest_primary_by_year, "expected official by_year rows from the stub"
+    assert latest_primary_by_year[-1]["gps_expected_pct"] == 72.0
+    assert latest_primary_by_year[-1]["science_expected_pct"] == 81.0

backend/tests/test_ofsted_codes.py

@@ -0,0 +1,45 @@
+"""Report-card code translation uses the live-sampled Ofsted vocabulary
+(pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE):
+Exceptional / Strong standard / Expected standard / Needs attention /
+Urgent improvement — never the consultation draft's 'Attention needed'."""
+
+from backend.ofsted_codes import (
+    REPORT_CARD_GRADE_NAMES,
+    ofsted_page_url,
+    report_card_labels,
+)
+
+
+def test_scale_is_sampled_vocabulary():
+    assert REPORT_CARD_GRADE_NAMES == {
+        1: "Exceptional",
+        2: "Strong standard",
+        3: "Expected standard",
+        4: "Needs attention",
+        5: "Urgent improvement",
+    }
+
+
+def test_labels_only_for_populated_areas_and_never_safeguarding():
+    ofsted = {
+        "rc_achievement": 2,
+        "rc_inclusion": 3,
+        "rc_attendance_behaviour": 4,
+        "rc_early_years": None,
+        "rc_safeguarding_met": True,
+        "overall_effectiveness": None,
+    }
+    labels = report_card_labels(ofsted)
+    assert labels == {
+        "rc_achievement": {"code": 2, "label": "Strong standard"},
+        "rc_inclusion": {"code": 3, "label": "Expected standard"},
+        "rc_attendance_behaviour": {"code": 4, "label": "Needs attention"},
+    }
+
+
+def test_unknown_code_is_skipped_not_crashed():
+    assert report_card_labels({"rc_achievement": 9}) == {}
+
+
+def test_provider_url():
+    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/provider/21/138690"

backend/tests/test_supplementary_enrichment.py

@@ -0,0 +1,65 @@
+"""Supplementary-block enrichment for the compare redesign: report-card
+labels, provider-page URL, graded-vs-carried-forward provenance, and the
+admissions preference/cross-LA detail promoted in the data-foundation PR."""
+
+import types
+
+from backend.data_loader import _admissions_row_dict, _ofsted_block
+
+
+def _row(**kw):
+    base = dict(
+        framework="RC", inspection_date=None, inspection_type=None,
+        overall_effectiveness=None, quality_of_education=None,
+        behaviour_attitudes=None, personal_development=None,
+        leadership_management=None, early_years_provision=None,
+        sixth_form_provision=None, ungraded_outcome=None, ungraded_grade=None,
+        rc_safeguarding_met=None, rc_inclusion=None, rc_curriculum_teaching=None,
+        rc_achievement=None, rc_attendance_behaviour=None,
+        rc_personal_development=None, rc_leadership_governance=None,
+        rc_early_years=None, rc_sixth_form=None, report_url=None,
+    )
+    base.update(kw)
+    return types.SimpleNamespace(**base)
+
+
+def test_report_card_block_and_provider_url():
+    o = _row(rc_achievement=2, rc_inclusion=3, rc_safeguarding_met=True)
+    block = _ofsted_block(o, urn=100140)
+    assert block["report_card"]["rc_achievement"]["label"] == "Strong standard"
+    assert "rc_safeguarding_met" not in block["report_card"]
+    assert block["rc_safeguarding_met"] is True
+    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"
+
+
+def test_grade_source_graded_vs_carried_forward():
+    assert _ofsted_block(_row(overall_effectiveness=1), urn=1)["grade_source"] == "graded"
+    carried = _ofsted_block(_row(ungraded_grade=2), urn=1)
+    assert carried["grade_source"] == "ungraded_carried_forward"
+    assert carried["overall_effectiveness"] == 2
+    assert _ofsted_block(_row(), urn=1)["grade_source"] is None
+
+
+def test_ofsted_block_keeps_existing_keys():
+    block = _ofsted_block(_row(overall_effectiveness=2, quality_of_education=2), urn=1)
+    for key in ("framework", "inspection_date", "overall_effectiveness",
+                "quality_of_education", "rc_inclusion", "report_url"):
+        assert key in block
+
+
+def test_admissions_row_new_fields():
+    a = types.SimpleNamespace(
+        year=202627, school_phase="Primary", places_offered=80,
+        total_applications=185, first_preference_applications=74,
+        first_preference_offers=74, first_preference_offer_pct=100.0,
+        oversubscription_ratio=0.925, oversubscribed=False,
+        total_offers=80, second_preference_offers=4, third_preference_offers=2,
+        cross_la_applications=12, cross_la_offers=3,
+    )
+    d = _admissions_row_dict(a)
+    for k in ("total_offers", "second_preference_offers", "third_preference_offers",
+              "cross_la_applications", "cross_la_offers"):
+        assert d[k] == getattr(a, k)
+    # Existing keys unchanged
+    assert d["first_preference_offer_pct"] == 100.0
+    assert d["oversubscribed"] is False

claude.md

@@ -112,11 +112,15 @@ 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: 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.
+- 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.
 - If you change user-facing behaviour, update or extend the `e2e/` journey
-  tests in the same PR — they are the promotion gate.
+  tests in the same PR — they gate whether staging is fit for human testing
+  and whether a commit is promotable.
 
 ## Recent Changes
 

docs/DEPLOY.md

@@ -1,41 +1,61 @@
 # SDLC & Deployment Pipeline
 
-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.
+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.
 
 ## The flow
 
 ```
 feature branch (AI-authored)
-   │  PR to main
+   │  PR to main                                    ← approval #1
    ▼
 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)
    ▼
-Deploy pipeline (.gitea/workflows/deploy.yml)
+Stage pipeline (.gitea/workflows/deploy.yml) — automatic
    1. build & push images        → tags sha-<sha>, staging
    2. staging Portainer webhook  → wait for staging health
-   3. Playwright E2E journeys against staging
-   4. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
+   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)
       previous :prod saved as :prod-previous
-   5. prod Portainer webhook     → wait for prod health
+   4. prod Portainer webhook     → wait for prod health
 ```
 
 Key principle: **build once, promote the exact image**. Production pins `:prod`,
-which only moves after the E2E gate passes on staging. Nothing tags `:latest`
+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`
 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` **is** the release action. If staging or the E2E gate
-  fails, production is untouched.
+- 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.
 
 ## Environments
 
@@ -92,8 +112,9 @@ fail the E2E gate. That's the point: staging absorbs the risk.
 
 ## Rollback
 
-Every promotion first re-points `:prod-previous` at the outgoing `:prod`.
-To roll back:
+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`:
 
 ```bash
 for img in backend frontend pipeline; do

docs/superpowers/plans/2026-07-13-compare-api-enrichment.md

@@ -0,0 +1,399 @@
+# Compare API Enrichment (Backend PR) 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:** Expose the PR #32 data through the API so the redesigned compare screen can be built: enrich `/api/compare` with supplementary blocks + national averages + computed benchmarks, translate Ofsted report-card codes to labels, and surface the new mart columns (spec §6, §8 of `docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md`).
+
+**Architecture:** All changes are additive API fields — existing consumers keep working. One small dbt change rides along: `fact_performance` (the combined KS2+KS4 mart the backend's `_MAIN_QUERY` reads) enumerates columns explicitly and was not extended in PR #32, so the new KS2 CI and KS4 banding columns must be threaded through it here. Everything else is backend Python: `models.py` mappings, `data_loader` query/supplementary additions, an Ofsted label dictionary (gias_codes pattern), and `/api/compare` composition.
+
+**Tech Stack:** FastAPI, SQLAlchemy, pandas; dbt (one model); pytest via `python -m pytest backend/tests -q` (CI installs `requirements.txt pytest "httpx<0.28"`; locally use `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q`).
+
+## Global Constraints
+
+- **Never push to `main`.** Branch: `feat/compare-api-enrichment`.
+- **Additive only** to API responses; never rename/remove existing fields (frontend + e2e depend on them).
+- **Report-card scale labels are the live-sampled vocabulary** (evidence in `pipeline/scripts/diagnose_compare_gaps.py`): `1=Exceptional, 2=Strong standard, 3=Expected standard, 4=Needs attention, 5=Urgent improvement`. Never "Attention needed". Safeguarding is boolean met/not-met, never counted as a graded area.
+- **Ofsted links** are always the provider page `https://reports.ofsted.gov.uk/provider/21/{urn}` (spec §5) labelled as the school's Ofsted page.
+- **Benchmark provenance** (spec §8.6): computed values are "state-school average (computed from our dataset)" — the API must expose them under a `benchmarks` key, clearly separate from official `national_averages`.
+- TDD: each behaviour lands with a failing test first, in `backend/tests/` following the `test_school_details.py` pattern (pandas fixture + monkeypatched `load_school_data` + `TestClient`).
+- Deploy note for the PR body: the new API fields return NULL/empty until prod's DAGs have run post-promotion.
+
+---
+
+### Task 0: Branch
+
+- [ ] `git checkout main && git pull && git checkout -b feat/compare-api-enrichment` (commit this plan file on the branch).
+
+---
+
+### Task 1: Thread PR #32 columns through `fact_performance`
+
+**Files:**
+- Modify: `pipeline/transform/models/marts/fact_performance.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (fact_performance block, if it has one — add the columns wherever the model's other columns are listed; if the model has no column list there, skip the yml)
+
+**Interfaces:**
+- Produces (for `_MAIN_QUERY` in Task 4): `ks2.*` CI columns and `ks4.progress_8_banding`, `ks4.attainment_8_disadvantage_gap`, `ks4.progress_8_disadvantage_gap` on `marts.fact_performance`.
+
+- [ ] **Step 1:** In `fact_performance.sql`, after `ks2.reading_progress,` add `ks2.reading_progress_lower_ci,` and `ks2.reading_progress_upper_ci,`; after `ks2.writing_progress,` add `ks2.writing_progress_lower_ci,`, `ks2.writing_progress_upper_ci,`, `ks2.writing_working_towards_pct,`; after `ks2.maths_progress,` add `ks2.maths_progress_lower_ci,`, `ks2.maths_progress_upper_ci,`. In the KS4 section, after the `ks4.progress_8_upper_ci`-equivalent line (locate the Progress 8 block) add:
+
+```sql
+    ks4.progress_8_banding,
+    ks4.attainment_8_disadvantage_gap,
+    ks4.progress_8_disadvantage_gap,
+```
+
+- [ ] **Step 2:** Parse gate: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .` → exit 0.
+
+- [ ] **Step 3:** Commit: `feat(pipeline): thread compare-foundation columns through fact_performance`
+
+---
+
+### Task 2: ORM mappings for the new mart columns
+
+**Files:**
+- Modify: `backend/models.py` (`KS2Performance` after `maths_progress`; `FactAdmissions` after `first_preference_offers`)
+- Test: none (declarative mappings; covered by Task 4's query tests)
+
+**Interfaces:**
+- Produces attributes used by Task 4: `KS2Performance.reading_progress_lower_ci` … `maths_progress_upper_ci`, `writing_working_towards_pct` (Float); `FactAdmissions.total_offers`, `.second_preference_offers`, `.third_preference_offers`, `.cross_la_applications`, `.cross_la_offers` (Integer).
+
+- [ ] **Step 1:** Add to `KS2Performance` (next to the existing progress columns):
+
+```python
+    reading_progress_lower_ci = Column(Float)
+    reading_progress_upper_ci = Column(Float)
+    writing_progress_lower_ci = Column(Float)
+    writing_progress_upper_ci = Column(Float)
+    writing_working_towards_pct = Column(Float)
+    maths_progress_lower_ci = Column(Float)
+    maths_progress_upper_ci = Column(Float)
+```
+
+Add to `FactAdmissions` (after `first_preference_offers`):
+
+```python
+    total_offers = Column(Integer)
+    second_preference_offers = Column(Integer)
+    third_preference_offers = Column(Integer)
+    cross_la_applications = Column(Integer)
+    cross_la_offers = Column(Integer)
+```
+
+(`FactOfstedInspection` already maps all `rc_*` columns with the right types — verify, don't change.)
+
+- [ ] **Step 2:** Commit: `feat(api): map compare-foundation mart columns`
+
+---
+
+### Task 3: Ofsted label dictionary + provider URL (TDD)
+
+**Files:**
+- Create: `backend/ofsted_codes.py`
+- Test: `backend/tests/test_ofsted_codes.py`
+
+**Interfaces:**
+- Produces for Task 4: `REPORT_CARD_GRADE_NAMES: dict[int, str]`, `report_card_labels(ofsted: dict) -> dict` (returns `{area_key: {"code": int, "label": str}}` for the non-null `rc_*` grade fields, excluding safeguarding), `ofsted_page_url(urn: int) -> str`.
+
+- [ ] **Step 1: Failing tests**
+
+```python
+"""Report-card code translation uses the live-sampled Ofsted vocabulary
+(pipeline/scripts/diagnose_compare_gaps.py TASK 7 VALUE SAMPLE):
+Exceptional / Strong standard / Expected standard / Needs attention /
+Urgent improvement — never the consultation draft's 'Attention needed'."""
+from backend.ofsted_codes import (
+    REPORT_CARD_GRADE_NAMES, report_card_labels, ofsted_page_url,
+)
+
+
+def test_scale_is_sampled_vocabulary():
+    assert REPORT_CARD_GRADE_NAMES == {
+        1: "Exceptional",
+        2: "Strong standard",
+        3: "Expected standard",
+        4: "Needs attention",
+        5: "Urgent improvement",
+    }
+
+
+def test_labels_only_for_populated_areas_and_never_safeguarding():
+    ofsted = {
+        "rc_achievement": 2,
+        "rc_inclusion": 3,
+        "rc_attendance_behaviour": 4,
+        "rc_early_years": None,
+        "rc_safeguarding_met": True,
+        "overall_effectiveness": None,
+    }
+    labels = report_card_labels(ofsted)
+    assert labels == {
+        "rc_achievement": {"code": 2, "label": "Strong standard"},
+        "rc_inclusion": {"code": 3, "label": "Expected standard"},
+        "rc_attendance_behaviour": {"code": 4, "label": "Needs attention"},
+    }
+
+
+def test_unknown_code_is_skipped_not_crashed():
+    assert report_card_labels({"rc_achievement": 9}) == {}
+
+
+def test_provider_url():
+    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/provider/21/138690"
+```
+
+- [ ] **Step 2:** Run `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests/test_ofsted_codes.py -q` → FAIL (module missing).
+
+- [ ] **Step 3: Implement `backend/ofsted_codes.py`**
+
+```python
+"""Ofsted renewed-framework (Nov 2025) report-card code translation.
+
+Scale labels are the live-sampled vocabulary from the Ofsted MI file
+(see pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE) —
+verified against real data, not the consultation draft.
+"""
+
+REPORT_CARD_GRADE_NAMES = {
+    1: "Exceptional",
+    2: "Strong standard",
+    3: "Expected standard",
+    4: "Needs attention",
+    5: "Urgent improvement",
+}
+
+# Graded evaluation areas only — safeguarding is a separate boolean
+# judgement and must never appear in grade counts or label maps.
+_RC_AREA_KEYS = (
+    "rc_inclusion",
+    "rc_curriculum_teaching",
+    "rc_achievement",
+    "rc_attendance_behaviour",
+    "rc_personal_development",
+    "rc_leadership_governance",
+    "rc_early_years",
+    "rc_sixth_form",
+)
+
+
+def report_card_labels(ofsted: dict) -> dict:
+    """{area_key: {code, label}} for populated, known-valued rc_* areas."""
+    out = {}
+    for key in _RC_AREA_KEYS:
+        code = ofsted.get(key)
+        label = REPORT_CARD_GRADE_NAMES.get(code)
+        if code is not None and label is not None:
+            out[key] = {"code": code, "label": label}
+    return out
+
+
+def ofsted_page_url(urn: int) -> str:
+    """The school's page on ofsted.gov.uk (all its reports live there —
+    we never deep-link an individual report; spec §5)."""
+    return f"https://reports.ofsted.gov.uk/provider/21/{urn}"
+```
+
+- [ ] **Step 4:** Re-run the test file → 4 passed. Run the full suite (same command, `backend/tests -q`) → all pass.
+
+- [ ] **Step 5:** Commit: `feat(api): Ofsted report-card labels and provider-page URL`
+
+---
+
+### Task 4: data_loader — query columns + richer supplementary blocks (TDD)
+
+**Files:**
+- Modify: `backend/data_loader.py` (`_MAIN_QUERY` ~line 153; `get_supplementary_data` ~line 460)
+- Test: `backend/tests/test_supplementary_enrichment.py`
+
+**Interfaces:**
+- `_MAIN_QUERY` additionally selects (KS2 block, after `p.maths_progress`): `p.reading_progress_lower_ci, p.reading_progress_upper_ci, p.writing_progress_lower_ci, p.writing_progress_upper_ci, p.writing_working_towards_pct, p.maths_progress_lower_ci, p.maths_progress_upper_ci`; (KS4 block, after the Progress 8 CI columns): `p.progress_8_banding, p.attainment_8_disadvantage_gap, p.progress_8_disadvantage_gap`. Note `_MAIN_QUERY_NO_SIXTH_FORM`/`_MAIN_QUERY_LEGACY_NAMES` are string-derived from `_MAIN_QUERY` (lines 259-270) and inherit automatically — verify the assertions there still hold.
+- `get_supplementary_data(db, urn)["admissions"]` rows additionally carry: `total_offers`, `second_preference_offers`, `third_preference_offers`, `cross_la_applications`, `cross_la_offers` (add to `_admissions_row`).
+- `get_supplementary_data(db, urn)["ofsted"]` additionally carries: `report_card` (the `report_card_labels(...)` dict, `{}` when no rc data), `ofsted_page_url`, and `grade_source`: `"graded"` when `overall_effectiveness` came from the graded column, `"ungraded_carried_forward"` when the fallback `ungraded_grade` supplied it, `None` when neither.
+
+- [ ] **Step 1: Failing tests** — construct a fake Ofsted row object (simple `types.SimpleNamespace` with the model's attributes) and call the block-building logic via `get_supplementary_data` with a stubbed session (follow how existing tests stub the db; if none do, factor the ofsted-dict construction into a pure helper `_ofsted_block(o, urn)` and test that directly — preferred):
+
+```python
+import types
+from backend.data_loader import _ofsted_block
+
+
+def _row(**kw):
+    base = dict(
+        framework="RC", inspection_date=None, inspection_type=None,
+        overall_effectiveness=None, quality_of_education=None,
+        behaviour_attitudes=None, personal_development=None,
+        leadership_management=None, early_years_provision=None,
+        sixth_form_provision=None, ungraded_outcome=None, ungraded_grade=None,
+        rc_safeguarding_met=None, rc_inclusion=None, rc_curriculum_teaching=None,
+        rc_achievement=None, rc_attendance_behaviour=None,
+        rc_personal_development=None, rc_leadership_governance=None,
+        rc_early_years=None, rc_sixth_form=None, report_url=None,
+    )
+    base.update(kw)
+    return types.SimpleNamespace(**base)
+
+
+def test_report_card_block_and_provider_url():
+    o = _row(rc_achievement=2, rc_inclusion=3, rc_safeguarding_met=True)
+    block = _ofsted_block(o, urn=100140)
+    assert block["report_card"]["rc_achievement"]["label"] == "Strong standard"
+    assert "rc_safeguarding_met" not in block["report_card"]
+    assert block["rc_safeguarding_met"] is True
+    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"
+
+
+def test_grade_source_graded_vs_carried_forward():
+    assert _ofsted_block(_row(overall_effectiveness=1), urn=1)["grade_source"] == "graded"
+    carried = _ofsted_block(_row(ungraded_grade=2), urn=1)
+    assert carried["grade_source"] == "ungraded_carried_forward"
+    assert carried["overall_effectiveness"] == 2
+    assert _ofsted_block(_row(), urn=1)["grade_source"] is None
+
+
+def test_admissions_row_new_fields():
+    from backend.data_loader import _admissions_row_dict
+    a = types.SimpleNamespace(
+        year=202627, school_phase="Primary", places_offered=80,
+        total_applications=185, first_preference_applications=74,
+        first_preference_offers=74, first_preference_offer_pct=100.0,
+        oversubscription_ratio=0.925, oversubscribed=False,
+        total_offers=80, second_preference_offers=4, third_preference_offers=2,
+        cross_la_applications=12, cross_la_offers=3,
+    )
+    d = _admissions_row_dict(a)
+    for k in ("total_offers", "second_preference_offers", "third_preference_offers",
+              "cross_la_applications", "cross_la_offers"):
+        assert d[k] == getattr(a, k)
+```
+
+- [ ] **Step 2:** Run → FAIL (helpers don't exist).
+
+- [ ] **Step 3: Implement.** Refactor the existing inline ofsted-dict construction in `get_supplementary_data` into a module-level `_ofsted_block(o, urn)` that produces the existing keys **unchanged** plus the three new ones (`report_card` via `report_card_labels(...)` from Task 3, `ofsted_page_url` via `ofsted_page_url(urn)`, `grade_source` per the interface rule — derived from which source supplied `overall_effectiveness`). Rename/extract the local `_admissions_row` into module-level `_admissions_row_dict(a)` and append the five new fields. Add the ten new columns to `_MAIN_QUERY` exactly as the interface lists them. `get_supplementary_data` calls both helpers; its external shape gains only additive keys.
+
+- [ ] **Step 4:** Full suite → all pass (existing `test_school_details.py` etc. must not break; if a fixture enumerates yearly-data columns, extend it with the new NaN columns as needed).
+
+- [ ] **Step 5:** Commit: `feat(api): expose progress CIs, KS4 banding/gaps, admissions detail, report-card labels`
+
+---
+
+### Task 5: Computed benchmarks helper (TDD)
+
+**Files:**
+- Modify: `backend/data_loader.py` (new function)
+- Test: `backend/tests/test_benchmarks.py`
+
+**Interfaces:**
+- Produces for Task 6: `compute_benchmarks(df) -> dict` — pure function over the main dataframe (latest year, state schools), shape:
+
+```python
+{
+  "source": "state-school average (computed from our dataset)",
+  "year": 202425,
+  "primary": {
+      "disadvantaged_rwm_expected_pct": 46.1,   # weighted by eligible_pupils
+      "eal_pct": 22.3,                           # median
+      "sen_support_pct": 14.0,                   # median
+      "disadvantaged_pct": 24.8,                 # median (FSM6 proxy)
+      "median_pupils": 281,                      # median school size
+  },
+  "secondary": { "median_pupils": 1024, "eal_pct": ..., "sen_support_pct": ..., "disadvantaged_pct": ... },
+}
+```
+
+- [ ] **Step 1: Failing tests** — build a small synthetic df (6 primary rows with known eligible_pupils/rwm_expected_disadvantaged_pct so the weighted average is hand-checkable; a couple of secondary rows flagged by non-null `attainment_8_score`), assert: weighted disadvantaged average matches hand computation (not the unweighted mean), medians ignore NaN, secondary block lacks the disadvantaged-RWM key, latest-year filtering (rows from an older year must not affect results), and empty df → `{}`.
+
+- [ ] **Step 2:** Run → FAIL.
+
+- [ ] **Step 3: Implement** in `data_loader.py`:
+
+```python
+def compute_benchmarks(df: pd.DataFrame) -> dict:
+    """State-school benchmarks computed from our dataset (spec §5/§8.6).
+    These are NOT official DfE figures — consumers must label them
+    'state-school average (computed from our dataset)'."""
+    if df.empty or "year" not in df.columns:
+        return {}
+    latest_year = df["year"].max()
+    d = df[df["year"] == latest_year]
+    if d.empty:
+        return {}
+    is_secondary = d["attainment_8_score"].notna() if "attainment_8_score" in d.columns else pd.Series(False, index=d.index)
+    prim, sec = d[~is_secondary], d[is_secondary]
+
+    def _median(sub, col):
+        if col not in sub.columns:
+            return None
+        v = sub[col].median()
+        return round(float(v), 1) if pd.notna(v) else None
+
+    def _weighted_disadvantaged(sub):
+        if not {"rwm_expected_disadvantaged_pct", "eligible_pupils"} <= set(sub.columns):
+            return None
+        s = sub.dropna(subset=["rwm_expected_disadvantaged_pct", "eligible_pupils"])
+        if s.empty or s["eligible_pupils"].sum() == 0:
+            return None
+        w = (s["rwm_expected_disadvantaged_pct"] * s["eligible_pupils"]).sum() / s["eligible_pupils"].sum()
+        return round(float(w), 1)
+
+    def _block(sub, with_disadvantaged):
+        block = {
+            "eal_pct": _median(sub, "eal_pct"),
+            "sen_support_pct": _median(sub, "sen_support_pct"),
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
+            "median_pupils": int(sub["total_pupils"].median()) if "total_pupils" in sub.columns and pd.notna(sub["total_pupils"].median()) else None,
+        }
+        if with_disadvantaged:
+            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
+        return block
+
+    return {
+        "source": "state-school average (computed from our dataset)",
+        "year": int(latest_year),
+        "primary": _block(prim, with_disadvantaged=True),
+        "secondary": _block(sec, with_disadvantaged=False),
+    }
+```
+
+(Adapt column presence to the real df — `sen_support_pct` reaches the df via `_MAIN_QUERY`; confirm and add it there if the KS2 block doesn't already select it, mirroring Task 4's additions.)
+
+- [ ] **Step 4:** Full suite → pass. **Step 5:** Commit: `feat(api): computed state-school benchmarks`
+
+---
+
+### Task 6: Enrich `/api/compare` + expose GPS/science national averages (TDD)
+
+**Files:**
+- Modify: `backend/app.py` (`compare_schools` ~line 636; `get_national_averages` ~line 730)
+- Test: `backend/tests/test_compare_enrichment.py`
+
+**Interfaces (response additions, all additive):**
+- `/api/compare` top level gains: `"national_averages"` (same payload the `/api/national-averages` endpoint returns — extract the endpoint body into a helper `_national_averages_payload(df)` and reuse; do not duplicate the logic) and `"benchmarks"` (Task 5's `compute_benchmarks(df)`).
+- Each `comparison[urn]` gains: `"ofsted"`, `"census"`, `"admissions"`, `"admissions_history"`, `"deprivation"` from `get_supplementary_data` (one `SessionLocal()` for the whole request, closed in `finally`; on exception the five keys are `None`/`[]` — mirror the detail endpoint's defensive pattern at app.py:583-590).
+- `get_national_averages`' KS2 metric list gains `"gps_expected_pct", "gps_high_pct", "science_expected_pct"` so the England ticks for GPS/science flow once the data exists.
+
+- [ ] **Step 1: Failing tests** — monkeypatch `load_school_data` with a two-school primary df (reuse/extend the fixture style of `test_school_details.py`) and monkeypatch `get_supplementary_data` to a canned dict; assert on `TestClient(app).get("/api/compare?urns=...")`:
+  - response keeps the existing shape (`comparison[urn]["school_info"]["rwm_expected_pct"]` etc.),
+  - each school gains the five supplementary keys (canned values round-tripped),
+  - top-level `national_averages` and `benchmarks` present; `benchmarks["source"]` is the exact provenance string,
+  - a supplementary-layer exception (monkeypatched to raise) degrades to `ofsted: None` etc. with HTTP 200,
+  - `/api/national-averages` includes `gps_expected_pct` in the primary block when the df/national table provides it (monkeypatch the national-averages source the endpoint reads).
+
+- [ ] **Step 2:** Run → FAIL. **Step 3:** Implement per the interfaces. **Step 4:** Full suite → pass.
+
+- [ ] **Step 5:** Commit: `feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks`
+
+---
+
+### Task 7: PR + verification
+
+- [ ] **Step 1:** Full suite one more time + `uv run --with pyyaml python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/deploy.yml'))"` sanity is NOT needed (no workflow changes) — instead run the dbt parse gate again (Task 1 file).
+- [ ] **Step 2:** Push, open PR via the Gitea API (credential-helper basic auth). PR body: the new response shapes (one JSON sketch), the reused-not-duplicated national-averages helper, the provenance rule for benchmarks, deploy note (fields NULL until prod DAGs run post-promotion), and that no e2e change is needed (no user-facing behaviour changes — the compare UI still reads the old fields; the frontend PR carries the journey updates).
+- [ ] **Step 3:** After merge + staging deploy: `curl -s https://stx.schoolcompare.co.uk/api/compare?urns=138690,100140 | python3 -m json.tool | head -80` — verify the new keys and that `benchmarks.primary.disadvantaged_rwm_expected_pct` is plausible (~45-47). Verify `/api/national-averages` now carries `gps_expected_pct`/`science_expected_pct` (values or honest nulls if DfE suppresses them at national level).
+
+---
+
+## Out of scope
+
+- Frontend rebuild + e2e journeys (next PR — consumes everything this PR exposes).
+- `schemas.py` METRIC_DEFINITIONS additions for the trends picker (frontend PR decides which of the new columns become picker metrics).
+- CI-based progress banding logic (frontend computes Above/Average/Below from the CI columns; historical years only).

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

@@ -0,0 +1,275 @@
+# 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.

pipeline/transform/models/marts/fact_performance.sql

@@ -25,13 +25,20 @@ select
     ks2.reading_high_pct,
     ks2.reading_avg_score,
     ks2.reading_progress,
+    ks2.reading_progress_lower_ci,
+    ks2.reading_progress_upper_ci,
     ks2.writing_expected_pct,
     ks2.writing_high_pct,
     ks2.writing_progress,
+    ks2.writing_progress_lower_ci,
+    ks2.writing_progress_upper_ci,
+    ks2.writing_working_towards_pct,
     ks2.maths_expected_pct,
     ks2.maths_high_pct,
     ks2.maths_avg_score,
     ks2.maths_progress,
+    ks2.maths_progress_lower_ci,
+    ks2.maths_progress_upper_ci,
     ks2.gps_expected_pct,
     ks2.gps_high_pct,
     ks2.gps_avg_score,
@@ -61,6 +68,9 @@ select
     ks4.progress_8_maths,
     ks4.progress_8_ebacc,
     ks4.progress_8_open,
+    ks4.progress_8_banding,
+    ks4.attainment_8_disadvantage_gap,
+    ks4.progress_8_disadvantage_gap,
     ks4.english_maths_strong_pass_pct,
     ks4.english_maths_standard_pass_pct,
     ks4.ebacc_entry_pct,