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
feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks
2026-07-13 23:46:12 +01:00 · 2026-07-13 18:48:28 +01:00 · 2026-07-13 18:45:30 +01:00 · 2026-07-13 18:44:29 +01:00 · 2026-07-13 18:42:08 +01:00 · 2026-07-13 18:40:55 +01:00
15 changed files with 1441 additions and 127 deletions
@@ -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:
+# Production deployment is a second, manual approval: see promote.yml
-    name: Promote to Production
+# ("Promote to Production (manual)") and docs/DEPLOY.md.
    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 }}
@@ -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 }}
@@ -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")
+def _national_averages_payload(df: pd.DataFrame) -> dict:
-@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
+    """National-averages payload shared by /api/national-averages and
-async def get_national_averages(request: Request):
+    /api/compare. Official DfE KS2 figures come from the mart table;
-    """
+    KS4 figures are computed from our dataset (no DfE dataset yet)."""
    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()
    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,6 +851,7 @@ async def get_national_averages(request: Request):
                "secondary": secondary_by_year.get(yr, {}),
            })
    finally:
        if db is not None:
            db.close()
    # Update latest_primary with official DfE figure for the latest year if available
@@ -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):
@@ -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"] = (
+    result["ofsted"] = _ofsted_block(o, urn) if o else None
        {
            "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
    )
    # 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.).
@@ -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)
@@ -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}"
@@ -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()) == {}
@@ -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
@@ -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"
@@ -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
@@ -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
+- Merging to `main` deploys automatically **to staging only**: images are
-  the **staging** Portainer stack, verified by the Playwright journeys in
+  built once, deployed to the staging Portainer stack, and verified by the
-  `e2e/`, and only then retagged `:prod` and rolled out to production.
+  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
@@ -1,41 +1,61 @@
 # SDLC & Deployment Pipeline
-SchoolCompare uses a fully automated staging → production pipeline on Gitea
+SchoolCompare uses a two-stage deploy model on Gitea Actions with two human
-Actions. AI writes the code on feature branches; the pipeline verifies every
+approvals. AI writes the code on feature branches; the first approval merges
-change on a staging environment before promoting the exact same images to
+the PR, which deploys to staging and runs the E2E gate; the second approval —
-production. Human input is directional only: feature requests, PR review if
+after manual testing on staging — promotes the exact same images to
-desired, and intervention when a gate fails.
+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
+   3. Playwright E2E journeys against staging      ← gate before human testing
-   4. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
+   ▼
 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
+- Merging to `main` releases **to staging only**. Production moves only on
-  fails, production is untouched.
+  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`.
+Re-run "Promote to Production (manual)" with the SHA of the last good commit
-To roll back:
+(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
@@ -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).
@@ -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.
@@ -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,
Author	SHA1	Message	Date
TudorandClaude Fable 5	c44d54d17d	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	2026-07-13 23:46:12 +01:00
TudorandClaude Fable 5	c0f31a5941	feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m45s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 22s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 51s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 37s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 3m12s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:48:28 +01:00
TudorandClaude Fable 5	cec7941b44	feat(api): computed state-school benchmarks Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:45:30 +01:00
TudorandClaude Fable 5	dbaa15c099	feat(api): expose progress CIs, KS4 banding/gaps, admissions detail, report-card labels Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:44:29 +01:00
TudorandClaude Fable 5	b5b47ca135	feat(api): Ofsted report-card labels and provider-page URL Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:42:08 +01:00
TudorandClaude Fable 5	0c89b2c34e	feat(api): map compare-foundation mart columns Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:40:55 +01:00
TudorandClaude Fable 5	4bf90b5f09	feat(pipeline): thread compare-foundation columns through fact_performance Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 15:46:43 +01:00
TudorandClaude Fable 5	17b4498c80	docs: plan for compare API enrichment Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 15:46:04 +01:00
tudor	3754947fd6	Merge pull request 'feat(pipeline): compare-screen data foundation — raw→marts promotions, national averages, Ofsted report cards' (#32 ) from feat/compare-data-foundation into main Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 12s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 48s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m16s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 41s Details Reviewed-on: #32	2026-07-13 12:52:11 +00:00
tudor	9799ad9b43	Merge pull request 'ci: two-stage deploy — staging automatic, production behind a manual approval' (#33 ) from chore/staged-prod-promotion into main Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 13s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 56s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 13s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 39s Details Reviewed-on: #33	2026-07-13 12:30:19 +00:00
TudorandClaude Fable 5	6877abedeb	fix(ci): harden promote workflow — env-isolated untrusted input, main-ancestry check, concurrency guard PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m38s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 44s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 10s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 2m39s Details Addresses the AI review findings on PR #33: - severe: the workflow_dispatch sha input was interpolated directly into the run script (shell injection with REGISTRY_TOKEN + prod webhook in scope). It now reaches the shell only via env, is rejected if it starts with '-', and is resolved locally with git rev-parse. - minor: the resolved sha must be a 40-hex ancestor of origin/main — non-main refs are refused explicitly instead of implicitly. - minor: a prod-promotion concurrency group serialises promotions (cancel-in-progress: false). Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 13:14:28 +01:00
TudorandClaude Fable 5	2b563cc0bf	docs: two-stage deploy model (staging auto, production manual) PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m36s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 43s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 10s Details PR Checks / AI Code Review (Claude) (pull_request) Failing after 2m10s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:38:37 +01:00
TudorandClaude Fable 5	75e92dc7f5	ci: manual production promotion workflow with e2e-gate verification Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:36:25 +01:00
TudorandClaude Fable 5	7499e7f557	ci: stop deploy pipeline at staging; production promotion becomes manual Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:35:39 +01:00
TudorandClaude Fable 5	dd0ff7d0c2	docs: plan for staged production promotion Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:34:32 +01:00