fix(pipeline): build stg_ees_ks4_national in the annual EES DAG

The DAG's dbt --select list predates the official-KS4-nationals stream,
so the extract loaded raw.ees_ks4_national but the staging model and
fact_ks4_national_averages were never rebuilt — staging kept serving the
old computed means after the DAG run.

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

.gitignore

@@ -1,2 +1,7 @@
 venv
 __pycache__/
+
+# dbt local build artifacts (embed absolute paths + anonymous-usage UUID)
+pipeline/transform/target/
+pipeline/transform/logs/
+pipeline/transform/.user.yml

backend/app.py

@@ -30,6 +30,7 @@ from .data_loader import (
     load_latest_school_data,
     geocode_single_postcode,
     get_supplementary_data,
+    get_supplementary_data_batch,
     search_schools_typesense,
 )
 from .data_loader import get_data_info as get_db_info
@@ -676,15 +677,43 @@ async def compare_schools(
         "deprivation": None,
     }
     supplementary_by_urn: dict = {}
+    census_benchmarks = None
     db = None
     try:
         db = database.SessionLocal()
+        # One query per table for all schools, not ~5 queries per school.
+        batch = get_supplementary_data_batch(db, urn_list)
         for urn in urn_list:
-            supp = get_supplementary_data(db, urn)
+            supp = batch.get(urn, {})
             supplementary_by_urn[urn] = {
                 key: supp.get(key, default)
                 for key, default in _EMPTY_SUPPLEMENTARY.items()
             }
+        # Import-time census context benchmarks (fact_census_benchmarks);
+        # absent mart → None, and compute_benchmarks leaves those fields null.
+        try:
+            from .models import CensusBenchmark
+
+            rows = db.query(CensusBenchmark).all()
+            by_phase = {
+                r.phase: {
+                    "year": r.year,
+                    "fsm_pct": r.fsm_pct,
+                    "eal_pct": r.eal_pct,
+                    "median_pupils": r.median_pupils,
+                }
+                for r in rows
+                if getattr(r, "phase", None) in ("primary", "secondary")
+            }
+            if by_phase:
+                census_benchmarks = by_phase
+        except Exception:
+            # Missing mart (or a stubbed session in tests) must never break
+            # the compare payload — and not every session has rollback().
+            try:
+                db.rollback()
+            except Exception:
+                pass
     except Exception:
         supplementary_by_urn = {}
     finally:
@@ -711,6 +740,9 @@ async def compare_schools(
                     "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")),
+                    # Needed by the admissions "What this means" copy: selective
+                    # schools get entrance-test framing, never the distance template.
+                    "admissions_policy": convert_to_native(latest.get("admissions_policy")),
                     "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")),
@@ -725,7 +757,7 @@ async def compare_schools(
         # 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),
+        "benchmarks": compute_benchmarks(df, census_benchmarks=census_benchmarks),
     }
 
 
@@ -794,11 +826,11 @@ def _national_averages_payload(df: pd.DataFrame) -> dict:
     /api/compare.
 
     Both series are persisted marts computed at import time: official DfE
-    KS2 figures (fact_ks2_national_averages) and dataset-computed KS4
-    averages (fact_ks4_national_averages) — the API never aggregates the
-    performance dataframe per request. If the KS4 mart hasn't been built
-    yet (deploy lands before the next DAG run), fall back to computing the
-    latest year only — a single-year scan, never the historical loop.
+    KS2 figures (fact_ks2_national_averages) and official DfE KS4 figures
+    (fact_ks4_national_averages) — the API never aggregates the performance
+    dataframe per request. If the KS4 mart hasn't been built yet, the
+    secondary series is empty — never a computed stand-in, because the UI
+    labels these figures as official DfE data.
     """
     if df.empty:
         return {"primary": {}, "secondary": {}}
@@ -838,23 +870,6 @@ def _national_averages_payload(df: pd.DataFrame) -> dict:
     primary_by_year = {r.year: _row_metrics(r, _KS2_NATIONAL_METRICS) for r in ks2_rows}
     secondary_by_year = {r.year: _row_metrics(r, _KS4_NATIONAL_METRICS) for r in ks4_rows}
 
-    if not any(secondary_by_year.values()):
-        # KS4 mart missing/empty: compute the latest year only.
-        df_latest = df[df["year"] == latest_year]
-        sec = (
-            df_latest[df_latest["attainment_8_score"].notna()]
-            if "attainment_8_score" in df_latest.columns
-            else df_latest.iloc[0:0]
-        )
-        vals = {}
-        for col in _KS4_NATIONAL_METRICS:
-            if col in sec.columns:
-                v = sec[col].dropna()
-                if len(v) > 0:
-                    vals[col] = round(float(v.mean()), 2)
-        if vals:
-            secondary_by_year[latest_year] = vals
-
     all_years = sorted(set(primary_by_year) | set(secondary_by_year))
     by_year = [
         {

backend/data_loader.py

@@ -525,13 +525,20 @@ def get_data_info(db: Session = None) -> dict:
 # SUPPLEMENTARY DATA — per-school detail page
 # =============================================================================
 
-def compute_benchmarks(df: pd.DataFrame) -> dict:
+def compute_benchmarks(df: pd.DataFrame, census_benchmarks: dict | None = None) -> 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.
+    small schools don't dominate.
+
+    Context measures (FSM/EAL/pupil counts) come from `census_benchmarks`
+    (the fact_census_benchmarks mart, pupil-weighted, keyed by phase): the
+    performance df has no fsm_pct at all, and its eal/disadvantaged columns
+    are KS2-only — medianing them for "secondary" produced junk anchors
+    from the handful of all-through schools. When the mart is unavailable
+    these are None; never fall back across measure definitions.
     """
     if df.empty or "year" not in df.columns:
         return {}
@@ -567,17 +574,14 @@ def compute_benchmarks(df: pd.DataFrame) -> dict:
         )
         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)
+    def _block(sub, phase, with_disadvantaged):
+        census = (census_benchmarks or {}).get(phase) or {}
         block = {
-            "eal_pct": _median(sub, "eal_pct"),
+            "eal_pct": census.get("eal_pct"),
             "sen_support_pct": _median(sub, "sen_support_pct"),
-            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
-            "median_pupils": median_pupils,
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct") if with_disadvantaged else None,
+            "fsm_pct": census.get("fsm_pct"),
+            "median_pupils": census.get("median_pupils"),
         }
         if with_disadvantaged:
             block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
@@ -586,8 +590,8 @@ def compute_benchmarks(df: pd.DataFrame) -> dict:
     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),
+        "primary": _block(prim, "primary", with_disadvantaged=True),
+        "secondary": _block(sec, "secondary", with_disadvantaged=False),
     }
 
 
@@ -616,6 +620,11 @@ def _ofsted_block(o, urn: int) -> dict:
     block = {
         "framework": o.framework,
         "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
+        "rc_inspection_date": (
+            o.rc_inspection_date.isoformat()
+            if getattr(o, "rc_inspection_date", None)
+            else None
+        ),
         "inspection_type": o.inspection_type,
         "overall_effectiveness": overall,
         "grade_source": grade_source,
@@ -662,92 +671,150 @@ def _admissions_row_dict(a) -> dict:
     }
 
 
-def get_supplementary_data(db: Session, urn: int) -> dict:
-    """Fetch all supplementary data for a single school URN."""
-    result = {}
+def _census_dict(pc) -> dict:
+    return {
+        "year": pc.year,
+        "total_pupils": pc.total_pupils,
+        "female_pupils": pc.female_pupils,
+        "male_pupils": pc.male_pupils,
+        "fsm_pct": pc.fsm_pct,
+        "eal_pct": pc.eal_pct,
+    }
 
-    def safe_query(model, pk_field, latest_field=None):
+
+def _deprivation_dict(d) -> dict:
+    return {
+        "lsoa_code": d.lsoa_code,
+        "idaci_score": d.idaci_score,
+        "idaci_decile": d.idaci_decile,
+    }
+
+
+def _finance_dict(f) -> dict:
+    return {
+        "year": f.year,
+        "per_pupil_spend": f.per_pupil_spend,
+        "staff_cost_pct": f.staff_cost_pct,
+        "teacher_cost_pct": f.teacher_cost_pct,
+        "support_staff_cost_pct": f.support_staff_cost_pct,
+        "premises_cost_pct": f.premises_cost_pct,
+    }
+
+
+def _empty_supplementary() -> dict:
+    return {
+        "ofsted": None,
+        "census": None,
+        "admissions": None,
+        "admissions_history": [],
+        "sen_detail": None,
+        "phonics": None,
+        "deprivation": None,
+        "finance": None,
+    }
+
+
+def get_supplementary_data_batch(db: Session, urns: list[int]) -> dict:
+    """Fetch supplementary data for many URNs with one query per table
+    (WHERE urn IN (...)) instead of ~5 queries per school, collapsing the
+    per-request round-trips from 5*N to a constant 5. Returns {urn: block}
+    with the same shape get_supplementary_data produces per URN.
+
+    Each table is queried independently and failures degrade that table to
+    empty for every URN — a missing mart never blanks the others.
+    """
+    urns = [int(u) for u in urns]
+    result = {urn: _empty_supplementary() for urn in urns}
+    if not urns:
+        return result
+
+    def _safe(fn):
         try:
-            q = db.query(model).filter(getattr(model, pk_field) == urn)
-            if latest_field:
-                q = q.order_by(getattr(model, latest_field).desc())
-            return q.first()
+            fn()
         except Exception as e:
             import logging
-            logging.getLogger(__name__).error("safe_query failed for %s: %s", model.__name__, e)
+            logging.getLogger(__name__).error("batch supplementary query failed: %s", e)
             db.rollback()
-            return None
 
-    # Latest Ofsted inspection
-    o = safe_query(FactOfstedInspection, "urn", "inspection_date")
-    result["ofsted"] = _ofsted_block(o, urn) if o else None
-
-    # Census (latest year of fact_pupil_characteristics)
-    pc = safe_query(FactPupilCharacteristics, "urn", "year")
-    result["census"] = (
-        {
-            "year": pc.year,
-            "total_pupils": pc.total_pupils,
-            "female_pupils": pc.female_pupils,
-            "male_pupils": pc.male_pupils,
-            "fsm_pct": pc.fsm_pct,
-            "eal_pct": pc.eal_pct,
-        }
-        if pc
-        else None
-    )
-
-    # Admissions — all years, oldest first (for the multi-year trend view).
-    try:
-        admissions_rows = (
-            db.query(FactAdmissions)
-            .filter(FactAdmissions.urn == urn)
-            .order_by(FactAdmissions.year.asc())
+    # Ofsted — latest inspection per URN. Ordered so the first row seen per
+    # URN is the most recent.
+    def _ofsted():
+        rows = (
+            db.query(FactOfstedInspection)
+            .filter(FactOfstedInspection.urn.in_(urns))
+            .order_by(FactOfstedInspection.urn, FactOfstedInspection.inspection_date.desc())
             .all()
         )
-    except Exception as e:
-        import logging
-        logging.getLogger(__name__).error("admissions history query failed: %s", e)
-        db.rollback()
-        admissions_rows = []
+        seen = set()
+        for o in rows:
+            if o.urn in seen:
+                continue
+            seen.add(o.urn)
+            result[o.urn]["ofsted"] = _ofsted_block(o, o.urn)
+    _safe(_ofsted)
 
-    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.).
-    result["admissions"] = history[-1] if history else None
+    # Census — latest year per URN.
+    def _census():
+        rows = (
+            db.query(FactPupilCharacteristics)
+            .filter(FactPupilCharacteristics.urn.in_(urns))
+            .order_by(FactPupilCharacteristics.urn, FactPupilCharacteristics.year.desc())
+            .all()
+        )
+        seen = set()
+        for pc in rows:
+            if pc.urn in seen:
+                continue
+            seen.add(pc.urn)
+            result[pc.urn]["census"] = _census_dict(pc)
+    _safe(_census)
 
-    # SEN detail — not available in current marts
-    result["sen_detail"] = None
+    # Admissions — all years per URN, oldest first (multi-year trend view).
+    def _admissions():
+        rows = (
+            db.query(FactAdmissions)
+            .filter(FactAdmissions.urn.in_(urns))
+            .order_by(FactAdmissions.urn, FactAdmissions.year.asc())
+            .all()
+        )
+        history: dict = {urn: [] for urn in urns}
+        for a in rows:
+            history[a.urn].append(_admissions_row_dict(a))
+        for urn, rows_for_urn in history.items():
+            result[urn]["admissions_history"] = rows_for_urn
+            result[urn]["admissions"] = rows_for_urn[-1] if rows_for_urn else None
+    _safe(_admissions)
 
-    # Phonics — no school-level data on EES
-    result["phonics"] = None
+    # Deprivation — one row per URN.
+    def _deprivation():
+        rows = (
+            db.query(FactDeprivation)
+            .filter(FactDeprivation.urn.in_(urns))
+            .all()
+        )
+        for d in rows:
+            result[d.urn]["deprivation"] = _deprivation_dict(d)
+    _safe(_deprivation)
 
-    # Deprivation
-    d = safe_query(FactDeprivation, "urn")
-    result["deprivation"] = (
-        {
-            "lsoa_code": d.lsoa_code,
-            "idaci_score": d.idaci_score,
-            "idaci_decile": d.idaci_decile,
-        }
-        if d
-        else None
-    )
-
-    # Finance (latest year)
-    f = safe_query(FactFinance, "urn", "year")
-    result["finance"] = (
-        {
-            "year": f.year,
-            "per_pupil_spend": f.per_pupil_spend,
-            "staff_cost_pct": f.staff_cost_pct,
-            "teacher_cost_pct": f.teacher_cost_pct,
-            "support_staff_cost_pct": f.support_staff_cost_pct,
-            "premises_cost_pct": f.premises_cost_pct,
-        }
-        if f
-        else None
-    )
+    # Finance — latest year per URN.
+    def _finance():
+        rows = (
+            db.query(FactFinance)
+            .filter(FactFinance.urn.in_(urns))
+            .order_by(FactFinance.urn, FactFinance.year.desc())
+            .all()
+        )
+        seen = set()
+        for f in rows:
+            if f.urn in seen:
+                continue
+            seen.add(f.urn)
+            result[f.urn]["finance"] = _finance_dict(f)
+    _safe(_finance)
 
     return result
+
+
+def get_supplementary_data(db: Session, urn: int) -> dict:
+    """Supplementary data for a single URN (thin wrapper over the batch)."""
+    return get_supplementary_data_batch(db, [urn])[int(urn)]

backend/models.py

@@ -156,6 +156,9 @@ class FactOfstedInspection(Base):
     rc_leadership_governance = Column(Integer)
     rc_early_years = Column(Integer)
     rc_sixth_form = Column(Integer)
+    # Start date of the report-card inspection itself (renewed framework,
+    # Nov 2025+). Null for rows without report-card grades.
+    rc_inspection_date = Column(Date)
     report_url = Column(Text)
 
 
@@ -231,8 +234,27 @@ class FactFinance(Base):
     premises_cost_pct = Column(Float)
 
 
+class CensusBenchmark(Base):
+    """State-school context benchmarks from the pupil census — one row per phase.
+
+    fsm_pct / eal_pct are pupil-weighted means. Computed at import time;
+    consumers label them "state-school average (computed from our dataset)".
+    """
+    __tablename__ = "fact_census_benchmarks"
+    __table_args__ = MARTS
+
+    phase = Column(String(20), primary_key=True)
+    year = Column(Integer)
+    fsm_pct = Column(Float)
+    eal_pct = Column(Float)
+    median_pupils = Column(Integer)
+
+
 class Ks4NationalAverage(Base):
-    """Computed national KS4 averages (from our dataset) — one row per year."""
+    """Official DfE KS4 national headline averages — one row per academic year.
+
+    gcse_grade_91_pct has no official national series and is always NULL.
+    """
     __tablename__ = "fact_ks4_national_averages"
     __table_args__ = MARTS
 

backend/tests/test_benchmarks.py

@@ -17,33 +17,33 @@ def _df():
         # 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),
+             sen_support_pct=10.0, disadvantaged_pct=20.0, fsm_pct=15.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),
+             sen_support_pct=14.0, disadvantaged_pct=24.0, fsm_pct=17.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),
+             sen_support_pct=18.0, disadvantaged_pct=30.0, fsm_pct=19.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),
+             sen_support_pct=np.nan, disadvantaged_pct=np.nan, fsm_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),
+             sen_support_pct=20.0, disadvantaged_pct=40.0, fsm_pct=21.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),
+             sen_support_pct=22.0, disadvantaged_pct=44.0, fsm_pct=23.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),
+             sen_support_pct=12.0, disadvantaged_pct=22.0, fsm_pct=12.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),
+             sen_support_pct=16.0, disadvantaged_pct=26.0, fsm_pct=14.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),
+             sen_support_pct=99.0, disadvantaged_pct=99.0, fsm_pct=99.0, total_pupils=9999),
     ]
     return pd.DataFrame(rows)
 
@@ -57,16 +57,40 @@ def test_weighted_disadvantaged_average():
 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
+    # sen medians over [10,14,18,20,22] = 18 — the only context measure still
+    # sourced from the performance df (the rest come from the census mart).
+    assert b["primary"]["sen_support_pct"] == 18.0
+    # disadvantaged_pct medians over [20,24,30,40,44] = 30
+    assert b["primary"]["disadvantaged_pct"] == 30.0
+
+
+def test_benchmarks_use_census_mart_for_context():
+    census = {
+        "primary": {"year": LATEST, "fsm_pct": 25.3, "eal_pct": 21.8, "median_pupils": 240},
+        "secondary": {"year": LATEST, "fsm_pct": 24.1, "eal_pct": 18.9, "median_pupils": 980},
+    }
+    b = compute_benchmarks(_df(), census_benchmarks=census)
+    assert b["primary"]["fsm_pct"] == 25.3
+    assert b["primary"]["eal_pct"] == 21.8
+    assert b["secondary"]["eal_pct"] == 18.9
+    assert b["secondary"]["median_pupils"] == 980
+
+
+def test_benchmarks_context_none_when_mart_missing():
+    # The performance df has no fsm_pct and its eal/disadvantaged columns are
+    # KS2-only — never silently fall back to medianing them for context.
+    b = compute_benchmarks(_df(), census_benchmarks=None)
+    assert b["primary"]["fsm_pct"] is None
+    assert b["primary"]["eal_pct"] is None
+    assert b["primary"]["median_pupils"] is None
 
 
 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
+    # KS2-only columns must not produce a fake secondary disadvantaged anchor
+    # (the old median over all-through schools' KS2 rows produced 50%).
+    assert b["secondary"]["disadvantaged_pct"] is None
 
 
 def test_provenance_string():

backend/tests/test_compare_enrichment.py

@@ -67,7 +67,9 @@ def client(monkeypatch):
 
     monkeypatch.setattr(app_module, "load_school_data", _two_primary_schools_df)
     monkeypatch.setattr(
-        app_module, "get_supplementary_data", lambda db, urn: dict(CANNED_SUPPLEMENTARY)
+        app_module,
+        "get_supplementary_data_batch",
+        lambda db, urns: {int(u): dict(CANNED_SUPPLEMENTARY) for u in urns},
     )
     monkeypatch.setattr(database_module, "SessionLocal", _StubSession)
     return TestClient(app_module.app, raise_server_exceptions=False)
@@ -102,10 +104,10 @@ def test_top_level_national_averages_and_benchmarks(client):
 def test_supplementary_failure_degrades_not_500(client, monkeypatch):
     from backend import app as app_module
 
-    def _boom(db, urn):
+    def _boom(db, urns):
         raise RuntimeError("marts unavailable")
 
-    monkeypatch.setattr(app_module, "get_supplementary_data", _boom)
+    monkeypatch.setattr(app_module, "get_supplementary_data_batch", _boom)
     resp = client.get("/api/compare?urns=100140")
     assert resp.status_code == 200
     school = resp.json()["comparison"]["100140"]

backend/tests/test_national_averages_marts.py

@@ -1,7 +1,7 @@
 """_national_averages_payload reads persisted marts (computed at import
-time) — it must never loop the dataframe per year. The only dataframe work
-allowed is the single-latest-year KS4 fallback for the window between a
-deploy and the next DAG run."""
+time) — it must never aggregate the dataframe. Both marts hold OFFICIAL
+DfE figures, so a missing KS4 mart yields an empty secondary series —
+never a computed stand-in the UI would mislabel as official."""
 
 import numpy as np
 import pandas as pd
@@ -84,10 +84,11 @@ def test_ks4_averages_come_from_the_mart_not_the_dataframe(payload):
     assert body["by_year"][-1]["secondary"]["progress_8_score"] == -0.02
 
 
-def test_missing_ks4_mart_falls_back_to_latest_year_only(payload):
+def test_ks4_secondary_empty_when_mart_missing(payload):
+    # No computed stand-in: the UI labels national figures as official DfE
+    # data, so an empty mart must yield an empty secondary series.
     body = payload(_Ks4MissingSession)
-    # Fallback computes the latest year from the df: mean(50, 30) = 40.0
-    assert body["secondary"]["attainment_8_score"] == 40.0
-    # ...and only the latest year — no historical KS4 loop
-    ks4_years = [e["year"] for e in body["by_year"] if e["secondary"]]
-    assert ks4_years == [LATEST]
+    assert body["secondary"] == {}
+    assert all(not e["secondary"] for e in body["by_year"])
+    # The KS2 series is unaffected.
+    assert body["primary"]["rwm_expected_pct"] == 62.1

backend/tests/test_supplementary_batch.py

@@ -0,0 +1,111 @@
+"""get_supplementary_data_batch fetches one query per table for all URNs
+(not ~5 per school) and returns the same per-URN block shape as the
+single-URN function, picking the latest row per URN where relevant."""
+
+import types
+
+from backend import data_loader
+from backend.data_loader import get_supplementary_data_batch
+
+
+class _FakeQuery:
+    """Records that a query ran and serves canned rows filtered by an in-list."""
+
+    def __init__(self, recorder, model_name, rows):
+        self._rec = recorder
+        self._model = model_name
+        self._rows = rows
+
+    def filter(self, *args, **kwargs):
+        return self
+
+    def order_by(self, *args, **kwargs):
+        return self
+
+    def all(self):
+        self._rec.append(self._model)
+        return self._rows
+
+    def first(self):
+        self._rec.append(self._model)
+        return self._rows[0] if self._rows else None
+
+
+class _FakeSession:
+    def __init__(self, rows_by_model):
+        self.rows_by_model = rows_by_model
+        self.queries: list[str] = []
+
+    def query(self, model):
+        name = model.__name__
+        return _FakeQuery(self.queries, name, self.rows_by_model.get(name, []))
+
+    def rollback(self):
+        pass
+
+
+def _ofsted_row(urn, date, oe):
+    base = {f: None for f in (
+        "framework", "inspection_type", "quality_of_education", "behaviour_attitudes",
+        "personal_development", "leadership_management", "early_years_provision",
+        "sixth_form_provision", "ungraded_outcome", "ungraded_grade",
+        "rc_safeguarding_met", "rc_inclusion", "rc_curriculum_teaching", "rc_achievement",
+        "rc_attendance_behaviour", "rc_personal_development", "rc_leadership_governance",
+        "rc_early_years", "rc_sixth_form", "report_url",
+    )}
+    base.update(urn=urn, inspection_date=types.SimpleNamespace(isoformat=lambda: date),
+                overall_effectiveness=oe, grade_source=None)
+    return types.SimpleNamespace(**base)
+
+
+def _adm_row(urn, year):
+    return types.SimpleNamespace(
+        urn=urn, year=year, school_phase="Primary", places_offered=100,
+        total_applications=200, first_preference_applications=150,
+        first_preference_offers=140, first_preference_offer_pct=93.3,
+        oversubscription_ratio=1.5, oversubscribed=True,
+        total_offers=100, second_preference_offers=5, third_preference_offers=2,
+        cross_la_applications=10, cross_la_offers=3,
+    )
+
+
+def test_one_query_per_table_and_latest_row_per_urn():
+    rows = {
+        # URN 1 has two Ofsted rows; the batch must keep the most recent (2023).
+        "FactOfstedInspection": [
+            _ofsted_row(1, "2023-01-01", 2),
+            _ofsted_row(1, "2019-01-01", 3),
+            _ofsted_row(2, "2021-06-01", 1),
+        ],
+        "FactAdmissions": [_adm_row(1, 202526), _adm_row(1, 202627), _adm_row(2, 202627)],
+        "FactPupilCharacteristics": [],
+        "FactDeprivation": [],
+        "FactFinance": [],
+    }
+    session = _FakeSession(rows)
+    out = get_supplementary_data_batch(session, [1, 2])
+
+    # Exactly one query per table — five total, regardless of two URNs.
+    assert sorted(session.queries) == [
+        "FactAdmissions", "FactDeprivation", "FactFinance",
+        "FactOfstedInspection", "FactPupilCharacteristics",
+    ]
+
+    # Latest Ofsted kept per URN
+    assert out[1]["ofsted"]["overall_effectiveness"] == 2
+    assert out[2]["ofsted"]["overall_effectiveness"] == 1
+
+    # Admissions history grouped per URN, latest exposed as `admissions`
+    assert [r["year"] for r in out[1]["admissions_history"]] == [202526, 202627]
+    assert out[1]["admissions"]["year"] == 202627
+    assert out[2]["admissions_history"] == [{**out[2]["admissions_history"][0]}]
+
+    # Empty tables degrade to the null block, not a crash
+    assert out[1]["census"] is None and out[1]["deprivation"] is None
+
+
+def test_single_wrapper_matches_batch(monkeypatch):
+    session = _FakeSession({"FactOfstedInspection": [_ofsted_row(5, "2022-01-01", 2)]})
+    single = data_loader.get_supplementary_data(session, 5)
+    assert single["ofsted"]["overall_effectiveness"] == 2
+    assert single["admissions_history"] == []

backend/tests/test_supplementary_enrichment.py

@@ -3,6 +3,7 @@ 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 datetime import date
 
 from backend.data_loader import _admissions_row_dict, _ofsted_block
 
@@ -40,6 +41,25 @@ def test_grade_source_graded_vs_carried_forward():
     assert _ofsted_block(_row(), urn=1)["grade_source"] is None
 
 
+def test_ofsted_block_carries_rc_inspection_date():
+    o = _row(
+        ungraded_grade=2,
+        rc_achievement=1,
+        rc_inspection_date=date(2026, 2, 3),
+        inspection_date=date(2021, 10, 7),
+    )
+    block = _ofsted_block(o, urn=138690)
+    assert block["rc_inspection_date"] == "2026-02-03"
+    # The legacy inspection date is still present, unchanged.
+    assert block["inspection_date"] == "2021-10-07"
+
+
+def test_ofsted_block_rc_inspection_date_none_when_absent():
+    o = _row(overall_effectiveness=1, inspection_date=date(2021, 10, 13))
+    block = _ofsted_block(o, urn=136276)
+    assert block["rc_inspection_date"] 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",

docs/superpowers/plans/2026-07-16-compare-mustfix-final-review.md

@@ -0,0 +1,931 @@
+# Compare Screen Must-Fix (Final Expert Review) 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:** Fix the five promotion-blocking findings from the expert's final staging review: (1) blank all-secondary compare view, (2) report cards dated with pre-Nov-2025 legacy inspection dates, (3) FSM chip benchmarked against the wrong measure, (4) KS4 "national averages" that are dataset means presented as official DfE figures, (5) factually wrong "DfE didn't publish 2021/22" footnote.
+
+**Architecture:** One branch/PR touching all three layers. Pipeline: a new tap field carries the report-card inspection's own date; a new EES stream ingests official KS4 national headlines; a new census-benchmarks mart replaces junk KS2-derived context medians. Backend: serialize the new fields, stop mislabelling computed KS4 means as official. Frontend: fix the phase-detection effect that leaves all-secondary comparisons stuck on an empty "primary" tab, date report cards correctly, drop the FSM→disadvantaged fallback, fix the footnote copy.
+
+**Tech Stack:** Meltano/Singer taps (Python), dbt-postgres, FastAPI/SQLAlchemy/pandas, Next.js app router + Jest, Playwright e2e.
+
+## Global Constraints
+
+- Never push to `main`; work on branch `fix/compare-final-review-mustfix`, open a PR. Never trigger the "Promote to Production (manual)" workflow — promotion is exclusively the human's call.
+- User-facing behaviour changes must extend the `e2e/` journeys in the same PR (they gate staging fitness and promotability).
+- All user-facing copy on the compare screen comes verbatim from `docs/superpowers/specs/mockups/compare-desktop.html` / `compare-mobile.html` — except where this plan explicitly changes copy to fix a factual error (Task 6); the spec/mockup gets the same wording in the same commit.
+- Benchmark provenance house style: official figures = "England average"; computed figures = "state-school average (computed from our dataset)".
+- A report card must NEVER be displayed with a pre-November-2025 date. Report cards exist only from November 2025.
+- Backend tests: `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q` (repo root; there is no local pytest).
+- dbt: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .` (never bare `dbt` — the Fusion binary shadows dbt-postgres).
+- Frontend: `cd nextjs-app && npx tsc --noEmit && npm test` (run tsc un-piped so exit codes are not masked).
+- Do NOT start a local server to test the application (CLAUDE.md).
+- Commits end with the Claude Code `Co-Authored-By` + `Claude-Session` trailers used on this branch's history.
+
+## Root-Cause Evidence (verified 2026-07-16, do not re-derive)
+
+- **Finding 1:** `nextjs-app/components/ComparisonView.tsx:164-176` — the auto-phase effect returns early when `selectedSchools.length === 0` (basket hydrates a beat after mount) and its dep array is only `[comparisonData]`, so it never re-fires; `comparePhase` stays `'primary'`, `activeSchools` is empty, the page renders "No primary schools in your comparison" (a11y snapshot confirmed). No console errors — not a crash.
+- **Finding 2:** In the Ofsted MI CSV (`Management_information_-_state-funded_schools_-_latest_inspections_as_at_31_May_2026.csv`) the report-card grade columns (cols 38–55, "Safeguarding standards", "Inclusion", …) belong to the **latest full inspection** block whose date is col 30 "Inspection start date" (Barclay 138690: `03/02/2026`). The tap's `inspection_date` COLUMN_PRIORITY matches col 60 "Inspection start date of latest OEIF graded inspection" first (the *legacy* date; NULL for Barclay, so stg coalesces to the 2021 *ungraded* date). The rc data is **real Ofsted data, not fabricated** — it is mis-dated. Also `discover_csv_url()` returns `matches[0]` = the oldest (2017) link on the GOV.UK page; staging works only because `mi_url` is set in the environment. Staging raw is stale for at least Watford Grammar 136276 (staging shows rc grades; the current MI file has all rc columns NULL for it) — a fresh extract fixes that via upsert on `(urn, inspection_date)`.
+- **Finding 3:** `nextjs-app/components/compare/CompareCommunity.tsx:36` — `bench?.fsm_pct ?? bench?.disadvantaged_pct` falls back across definitions. `benchmarks.primary.fsm_pct` is null because `compute_benchmarks` (backend/data_loader.py:528) medians the *performance* df, which has no `fsm_pct` (school FSM comes from `census.fsm_pct` = `fact_pupil_characteristics`). `disadvantaged_pct` / `eal_pct` are KS2-only columns, so the "secondary" medians (50.0 / 10.0) are computed over the few all-through schools' KS2 rows — junk.
+- **Finding 4:** `fact_ks4_national_averages.sql` computes unweighted school means (A8 38.94 vs official 46.0; national P8 −0.27, impossible). Official series exists on EES: data-set `1b649e16-01e8-435b-a814-56be2faf9054` ("National characteristics summary data", KS4 performance publication), CSV endpoint same pattern as the KS2 national stream, national level, 2018/19→2024/25, `establishment_type_group = 'All state-funded'`, `breakdown_topic = 'Total'`, `breakdown = 'Total'`. Verified values: 2024/25 A8 46.0, P8 `z` (not published — no KS2 baseline for that cohort), EM 9-5 45.4%, EBacc entry 40.5%. It has **no** `gcse_91_percent` column.
+- **Finding 5:** `ComparisonChart.tsx:245-246` claims "DfE didn't publish school-level figures for 2021/22". False — DfE published school-level KS2 for 2021/22 in Dec 2022; spec §8.1 itself lists loading it as a pipeline task. The honest claim is that the figures aren't in our dataset.
+
+---
+
+### Task 1: All-secondary comparison renders (phase-detection fix)
+
+**Files:**
+- Modify: `nextjs-app/components/ComparisonView.tsx:176`
+- Create: `nextjs-app/__tests__/components/ComparisonView.phase.test.tsx`
+- Modify: `e2e/tests/journeys.spec.ts` (add helper + journey after the existing `twoPrimaryUrns` helper / primary compare journey)
+
+**Interfaces:**
+- Consumes: existing `ComparisonView` props (`initialData`, `initialUrns`, `metrics`, `selectedMetric`), `ComparisonProvider`.
+- Produces: no API changes; the auto-phase effect re-runs when the basket hydrates.
+
+- [ ] **Step 1: Write the failing Jest test**
+
+Create `nextjs-app/__tests__/components/ComparisonView.phase.test.tsx` (mirrors the mock setup of `ComparisonView.refresh.test.tsx`):
+
+```tsx
+/**
+ * Regression: an all-secondary comparison must render the secondary sections.
+ *
+ * The basket hydrates from the URL a beat after mount, so the auto-phase
+ * effect must re-run once selectedSchools arrives — with deps of only
+ * [comparisonData] it fired once against an empty basket, bailed, and the
+ * page stayed on an empty "primary" tab ("No primary schools in your
+ * comparison") even though all schools were secondary.
+ */
+
+import { render, screen, waitFor } from '@testing-library/react';
+
+import { ComparisonView } from '@/components/ComparisonView';
+import { ComparisonProvider } from '@/context/ComparisonProvider';
+import type { ComparisonData, School } from '@/lib/types';
+
+const fetchComparison = jest.fn();
+jest.mock('@/lib/api', () => ({
+  fetchComparison: (...args: unknown[]) => fetchComparison(...args),
+}));
+jest.mock('@/lib/analytics', () => ({ track: jest.fn() }));
+
+function secondarySchool(urn: number, name: string): School {
+  return {
+    urn,
+    school_name: name,
+    local_authority: 'Testshire',
+    school_type: 'Academy converter',
+    attainment_8_score: 55,
+    phase: 'Secondary',
+  } as School;
+}
+
+function data(urn: number, name: string): ComparisonData {
+  return {
+    school_info: secondarySchool(urn, name),
+    yearly_data: [{ year: 202425, attainment_8_score: 55 }] as ComparisonData['yearly_data'],
+    ofsted: null,
+    census: null,
+    admissions: null,
+    admissions_history: [],
+    deprivation: null,
+  };
+}
+
+const INITIAL_DATA = {
+  '300': data(300, 'Gamma High'),
+  '400': data(400, 'Delta Academy'),
+};
+
+test('an all-secondary comparison renders the sections, not an empty primary tab', async () => {
+  render(
+    <ComparisonProvider>
+      <ComparisonView
+        initialData={INITIAL_DATA}
+        initialNationalAverages={{
+          year: 202425,
+          primary: {},
+          secondary: { attainment_8_score: 46 },
+          by_year: [],
+        }}
+        initialBenchmarks={undefined}
+        initialUrns={[300, 400]}
+        metrics={[]}
+        selectedMetric="attainment_8_score"
+      />
+    </ComparisonProvider>,
+  );
+
+  await waitFor(() => {
+    expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
+  });
+  expect(screen.getAllByText('Gamma High').length).toBeGreaterThan(0);
+  expect(screen.queryByText(/No primary schools in your comparison/)).toBeNull();
+  expect(fetchComparison).not.toHaveBeenCalled();
+});
+```
+
+- [ ] **Step 2: Run it to verify it fails**
+
+Run: `cd nextjs-app && npx jest __tests__/components/ComparisonView.phase.test.tsx`
+Expected: FAIL — "No primary schools in your comparison" is rendered / "At a glance" never appears.
+
+- [ ] **Step 3: Fix the effect dependencies**
+
+In `nextjs-app/components/ComparisonView.tsx`, the auto-phase effect currently ends:
+
+```tsx
+  }, [comparisonData]); // eslint-disable-line react-hooks/exhaustive-deps
+```
+
+Change to:
+
+```tsx
+    // selectedSchools is a dep because the basket hydrates after mount: the
+    // first run sees an empty basket and bails, so it must re-fire when the
+    // schools arrive. primarySchools/secondarySchools/metrics/selectedMetric
+    // are intentionally omitted (derived or would cause loops).
+  }, [comparisonData, selectedSchools]); // eslint-disable-line react-hooks/exhaustive-deps
+```
+
+(`phaseLockedByUser` still suppresses re-detection after a manual tab click; re-running with unchanged inputs sets the same state, which React treats as a no-op.)
+
+- [ ] **Step 4: Run the new test and the existing suite**
+
+Run: `cd nextjs-app && npx tsc --noEmit && npm test`
+Expected: PASS, including `ComparisonView.refresh.test.tsx` (the refresh regression must stay green).
+
+- [ ] **Step 5: Add the e2e secondary journey**
+
+In `e2e/tests/journeys.spec.ts`, add below `twoPrimaryUrns`:
+
+```ts
+async function twoSecondaryUrns(page: Page): Promise<[string, string]> {
+  const res = await page.request.get('/api/schools?search=school&per_page=100');
+  expect(res.ok()).toBeTruthy();
+  const body = await res.json();
+  const urns: string[] = (body.schools ?? [])
+    .filter((s: { phase?: string; attainment_8_score?: number | null }) =>
+      s.phase === 'Secondary' && s.attainment_8_score != null,
+    )
+    .map((s: { urn: number }) => String(s.urn));
+  expect(urns.length).toBeGreaterThanOrEqual(2);
+  return [urns[0], urns[1]];
+}
+```
+
+(If `/api/schools` list rows lack `attainment_8_score`, filter on `s.phase === 'Secondary'` only — check the response first.) Then add a journey test next to the primary compare journey:
+
+```ts
+test('comparing two secondary schools renders the secondary sections', async ({ page }) => {
+  const [urn0, urn1] = await twoSecondaryUrns(page);
+
+  await page.goto(`/compare?urns=${urn0},${urn1}`);
+  await expect(page.locator(`a[href*="${urn0}"]`).first()).toBeVisible({ timeout: 15_000 });
+
+  // The parent-first sections must render — this page was completely blank
+  // for all-secondary baskets (expert review must-fix #1).
+  await expect(page.getByRole('heading', { name: 'At a glance' })).toBeVisible();
+  await expect(page.getByRole('heading', { name: 'Ofsted inspection' })).toBeVisible();
+  // A KS4 measure proves the secondary academics variant rendered.
+  await expect(page.getByText(/Attainment 8/i).first()).toBeVisible();
+  await expect(page.getByText(/No primary schools in your comparison/)).toHaveCount(0);
+});
+```
+
+- [ ] **Step 6: Commit**
+
+```bash
+git add nextjs-app/components/ComparisonView.tsx nextjs-app/__tests__/components/ComparisonView.phase.test.tsx e2e/tests/journeys.spec.ts
+git commit -m "fix(compare): render all-secondary comparisons — re-run phase detection after basket hydration"
+```
+
+---
+
+### Task 2: Report-card inspection date through the pipeline
+
+**Files:**
+- Modify: `pipeline/plugins/extractors/tap-uk-ofsted/tap_uk_ofsted/tap.py` (COLUMN_PRIORITY, schema, `discover_csv_url`)
+- Modify: `pipeline/transform/models/staging/stg_ofsted_inspections.sql`
+- Modify: `pipeline/transform/models/intermediate/int_ofsted_latest.sql`
+- Modify: `pipeline/transform/models/marts/fact_ofsted_inspection.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (add column doc if other fact_ofsted columns are documented there)
+
+**Interfaces:**
+- Consumes: MI CSV column `Inspection start date` (the latest **full** inspection = the report-card inspection in the renewed framework; NULL when a school's only inspections are legacy OEIF/ungraded — verified for Watford Grammar).
+- Produces: `marts.fact_ofsted_inspection.rc_inspection_date` (DATE, null unless the row carries report-card grades). Task 3 depends on this exact column name.
+
+- [ ] **Step 1: Add the tap field**
+
+In `tap.py` COLUMN_PRIORITY, after the `rc_sixth_form` entry, add:
+
+```python
+    # Date of the latest FULL inspection — in the renewed framework this is
+    # the report-card inspection's own start date (col "Inspection start
+    # date"), distinct from the legacy OEIF graded/ungraded dates above.
+    "rc_inspection_date": ["Inspection start date"],
+```
+
+and in the stream schema, next to the other rc properties:
+
+```python
+        th.Property("rc_inspection_date", th.StringType),
+```
+
+Note: `inspection_date`'s own priority list also contains `"Inspection start date"` as a lower-priority candidate — that stays; in renewed-framework files the higher-priority OEIF column exists so they map to different columns, and in legacy files both map to the same column but rc grades are absent, and staging nulls `rc_inspection_date` in that case (Step 3).
+
+- [ ] **Step 2: Fix `discover_csv_url` to pick the newest file, not `matches[0]`**
+
+The GOV.UK page lists 2017 files first; `matches[0]` is a 2017 CSV. Replace the body of `discover_csv_url()` to date-sort the `latest_inspections_as_at` links, mirroring `discover_independent_csv_url`:
+
+```python
+def discover_csv_url() -> str | None:
+    """Scrape GOV.UK page to find the latest MI CSV download link.
+
+    The page lists a decade of monthly files, oldest first — take the
+    newest 'latest inspections as at <date>' link by parsing its date,
+    never matches[0].
+    """
+    resp = requests.get(GOV_UK_PAGE, timeout=30)
+    resp.raise_for_status()
+    csv_links = re.findall(
+        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.csv)"',
+        resp.text,
+    )
+
+    months = {
+        'january': 1, 'february': 2, 'march': 3, 'april': 4, 'may': 5, 'june': 6,
+        'july': 7, 'august': 8, 'september': 9, 'october': 10, 'november': 11, 'december': 12,
+        'jan': 1, 'feb': 2, 'mar': 3, 'apr': 4, 'jun': 6,
+        'jul': 7, 'aug': 8, 'sep': 9, 'oct': 10, 'nov': 11, 'dec': 12,
+    }
+    parsed_links = []
+    for link in csv_links:
+        normalized = link.lower().replace('-', '_')
+        if 'latest_inspections_as_at' not in normalized:
+            continue
+        match = re.search(r'as_at_(\d{1,2})_([a-z]+)_(\d{4})', normalized)
+        if match:
+            day, month_str, year = match.groups()
+            month = months.get(month_str)
+            if month:
+                try:
+                    parsed_links.append((datetime(int(year), month, int(day)), link))
+                except ValueError:
+                    continue
+    parsed_links.sort(reverse=True)
+    if parsed_links:
+        return parsed_links[0][1]
+    if csv_links:
+        return csv_links[-1]
+    matches = re.findall(
+        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.ods)"',
+        resp.text,
+    )
+    return matches[0] if matches else None
+```
+
+(`mi_url` config still wins when set — `self.config.get("mi_url") or discover_csv_url()` is unchanged.)
+
+- [ ] **Step 3: Parse and guard the date in staging**
+
+In `stg_ofsted_inspections.sql`, inside the `renamed` CTE after the `rc_sixth_form` line, add:
+
+```sql
+        -- Start date of the latest FULL inspection (the report-card
+        -- inspection in the renewed framework). Guarded below: only kept
+        -- when the row actually carries report-card grades, because in
+        -- legacy-format files this column is the legacy inspection date.
+        to_date(nullif(trim(rc_inspection_date), 'NULL'), 'DD/MM/YYYY') as rc_inspection_date_raw,
+```
+
+and replace the final select:
+
+```sql
+select
+    *,
+    case
+        when rc_safeguarding_met is not null
+          or rc_inclusion is not null
+          or rc_curriculum_teaching is not null
+          or rc_achievement is not null
+          or rc_attendance_behaviour is not null
+          or rc_personal_development is not null
+          or rc_leadership_governance is not null
+        then rc_inspection_date_raw
+    end as rc_inspection_date
+from renamed
+where inspection_date is not null
+```
+
+- [ ] **Step 4: Propagate through int + mart**
+
+Add `rc_inspection_date,` to the explicit column lists of `int_ofsted_latest.sql` and `fact_ofsted_inspection.sql` (after `rc_sixth_form`). Do NOT propagate `rc_inspection_date_raw`.
+
+- [ ] **Step 5: Parse-check dbt**
+
+Run: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .`
+Expected: parse OK, no compilation errors.
+
+- [ ] **Step 6: Commit**
+
+```bash
+git add pipeline/plugins/extractors/tap-uk-ofsted pipeline/transform/models
+git commit -m "feat(pipeline): carry the report-card inspection's own date; pick newest MI file in discovery"
+```
+
+---
+
+### Task 3: Report-card date in the API and UI
+
+**Files:**
+- Modify: `backend/models.py` (FactOfstedInspection)
+- Modify: `backend/data_loader.py` (`_ofsted_block`)
+- Test: `backend/tests/test_supplementary_enrichment.py` (extend the existing `_ofsted_block` tests)
+- Modify: `nextjs-app/lib/types.ts` (OfstedInspection)
+- Modify: `nextjs-app/components/compare/CompareOfsted.tsx` ("Inspected" measure)
+- Test: `nextjs-app/__tests__/components/CompareOfsted.test.tsx`
+
+**Interfaces:**
+- Consumes: `marts.fact_ofsted_inspection.rc_inspection_date` (Task 2).
+- Produces: API `ofsted.rc_inspection_date: string | null` (ISO date). UI rule: report-card displays are dated with `rc_inspection_date` only; when null, show "—" (never the legacy date).
+
+- [ ] **Step 1: Failing backend test**
+
+In `backend/tests/test_supplementary_enrichment.py`, alongside the existing `_ofsted_block` tests, add (reuse the file's existing fake-row helper/style):
+
+```python
+def test_ofsted_block_carries_rc_inspection_date():
+    o = _fake_ofsted_row(  # use this file's existing fake/stub construction
+        overall_effectiveness=None,
+        ungraded_grade=2,
+        rc_achievement=1,
+        rc_inspection_date=date(2026, 2, 3),
+        inspection_date=date(2021, 10, 7),
+    )
+    block = _ofsted_block(o, 138690)
+    assert block["rc_inspection_date"] == "2026-02-03"
+    # The legacy inspection date is still present, unchanged.
+    assert block["inspection_date"] == "2021-10-07"
+
+
+def test_ofsted_block_rc_inspection_date_none_when_absent():
+    o = _fake_ofsted_row(overall_effectiveness=1, inspection_date=date(2021, 10, 13))
+    block = _ofsted_block(o, 136276)
+    assert block["rc_inspection_date"] is None
+```
+
+Run: `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests/test_supplementary_enrichment.py -q`
+Expected: FAIL (KeyError / AttributeError on `rc_inspection_date`).
+
+- [ ] **Step 2: Backend implementation**
+
+`backend/models.py`, in `FactOfstedInspection` after `rc_sixth_form`:
+
+```python
+    # Start date of the report-card inspection itself (renewed framework,
+    # Nov 2025+). Null for rows without report-card grades.
+    rc_inspection_date = Column(Date)
+```
+
+`backend/data_loader.py` `_ofsted_block`, after the `"inspection_date"` entry:
+
+```python
+        "rc_inspection_date": (
+            o.rc_inspection_date.isoformat()
+            if getattr(o, "rc_inspection_date", None)
+            else None
+        ),
+```
+
+(`getattr` default keeps old test stubs working.) Run the backend suite; expected: PASS.
+
+- [ ] **Step 3: Failing frontend test**
+
+`nextjs-app/lib/types.ts`, in `OfstedInspection`, after `inspection_date`:
+
+```ts
+  /** Start date of the report-card inspection itself (Nov 2025+); null otherwise. */
+  rc_inspection_date?: string | null;
+```
+
+In `nextjs-app/__tests__/components/CompareOfsted.test.tsx`, add to the existing suite (reusing its fixture style):
+
+```tsx
+it('dates a report card with the report-card inspection date, never the legacy date', () => {
+  const ofsted = reportCardOfsted({
+    inspection_date: '2021-10-07',
+    rc_inspection_date: '2026-02-03',
+  });
+  render(<CompareOfsted schools={[schoolFixture]} data={{ [String(schoolFixture.urn)]: { ...dataFixture, ofsted } }} />);
+  expect(screen.getByText(/3 Feb 2026/)).toBeInTheDocument();
+  expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
+  expect(screen.queryByText('4+ years ago')).toBeNull();
+});
+
+it('shows an em dash when a report card has no rc_inspection_date yet', () => {
+  const ofsted = reportCardOfsted({ inspection_date: '2021-10-07', rc_inspection_date: null });
+  render(<CompareOfsted schools={[schoolFixture]} data={{ [String(schoolFixture.urn)]: { ...dataFixture, ofsted } }} />);
+  expect(screen.getByText('—')).toBeInTheDocument();
+  expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
+});
+```
+
+(`reportCardOfsted` = the file's existing report-card fixture builder, or build inline matching its other tests.) Run just this file; expected: FAIL.
+
+- [ ] **Step 4: Frontend implementation**
+
+In `CompareOfsted.tsx`, replace the body of the "Inspected" measure's map:
+
+```tsx
+        {schools.map((school, i) => {
+          const ofsted = data[String(school.urn)]?.ofsted;
+          // A report card is dated by its OWN inspection date. The legacy
+          // inspection_date belongs to an older inspection and must never
+          // be shown against a report card (report cards exist only from
+          // Nov 2025).
+          const dateIso =
+            displays[i].kind === 'report_card'
+              ? ofsted?.rc_inspection_date ?? null
+              : ofsted?.inspection_date ?? null;
+          const age = yearsSince(dateIso);
+          return (
+            <Cell key={school.urn} school={school} index={i}>
+              {formatInspectionDate(dateIso)}{' '}
+              {age != null && age > 4 && <Chip tone="neutral">4+ years ago</Chip>}
+            </Cell>
+          );
+        })}
+```
+
+- [ ] **Step 5: Run frontend checks**
+
+Run: `cd nextjs-app && npx tsc --noEmit && npm test`
+Expected: PASS.
+
+- [ ] **Step 6: Commit**
+
+```bash
+git add backend/models.py backend/data_loader.py backend/tests nextjs-app/lib/types.ts nextjs-app/components/compare/CompareOfsted.tsx nextjs-app/__tests__/components/CompareOfsted.test.tsx
+git commit -m "fix(compare): date report cards with their own inspection date, never the legacy one"
+```
+
+---
+
+### Task 4: Census-based context benchmarks; kill the FSM fallback
+
+**Files:**
+- Create: `pipeline/transform/models/marts/fact_census_benchmarks.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml`
+- Modify: `backend/models.py` (new `CensusBenchmark` model)
+- Modify: `backend/data_loader.py` (`compute_benchmarks`)
+- Modify: `backend/app.py` (compare endpoint call site, only if the signature change requires it)
+- Test: `backend/tests/test_benchmarks.py`
+- Modify: `nextjs-app/components/compare/CompareCommunity.tsx:36`
+- Test: `nextjs-app/__tests__/lib/compareLogic.test.ts` or the community section's existing test home (add a fallback-removal test where the FSM chip logic is tested today)
+
+**Interfaces:**
+- Consumes: `marts.fact_pupil_characteristics` (urn, year, phase_type_grouping, total_pupils, fsm_pct, eal_pct).
+- Produces: `marts.fact_census_benchmarks` — one row per phase (`'primary'`/`'secondary'`), columns `phase, year, fsm_pct, eal_pct, median_pupils`. `fsm_pct`/`eal_pct` are **pupil-weighted means** (so they approximate the national pupil-level rate, answering the expert's objection to school-median anchors). API `benchmarks.{primary,secondary}` keeps its existing keys; `fsm_pct`/`eal_pct`/`median_pupils` now come from this mart; `disadvantaged_pct` becomes primary-only (the KS2-column median was junk for secondary).
+
+- [ ] **Step 1: dbt mart**
+
+Create `pipeline/transform/models/marts/fact_census_benchmarks.sql`:
+
+```sql
+{{ config(materialized='table') }}
+
+-- Mart: state-school context benchmarks from the pupil census — one row per
+-- phase, latest census year. Computed at import time (never per request).
+-- fsm_pct / eal_pct are pupil-weighted means, i.e. "what % of pupils", not
+-- "the median school" — this matches how DfE quotes national FSM/EAL rates.
+-- Consumers must label these "state-school average (computed from our
+-- dataset)" (spec §8.6), never "England average".
+
+with latest as (
+    select max(year) as year from {{ ref('fact_pupil_characteristics') }}
+),
+
+classified as (
+    select
+        case
+            when p.phase_type_grouping ilike '%primary%'   then 'primary'
+            when p.phase_type_grouping ilike '%secondary%' then 'secondary'
+        end as phase,
+        p.total_pupils,
+        p.fsm_pct,
+        p.eal_pct,
+        l.year
+    from {{ ref('fact_pupil_characteristics') }} p
+    join latest l on p.year = l.year
+    where p.total_pupils is not null and p.total_pupils > 0
+)
+
+select
+    phase,
+    max(year) as year,
+    round((sum(fsm_pct * total_pupils) filter (where fsm_pct is not null)
+        / nullif(sum(total_pupils) filter (where fsm_pct is not null), 0))::numeric, 1) as fsm_pct,
+    round((sum(eal_pct * total_pupils) filter (where eal_pct is not null)
+        / nullif(sum(total_pupils) filter (where eal_pct is not null), 0))::numeric, 1) as eal_pct,
+    round(percentile_cont(0.5) within group (order by total_pupils))::integer as median_pupils
+from classified
+where phase is not null
+group by phase
+```
+
+Add a `fact_census_benchmarks` entry to `_marts_schema.yml` in the file's existing style (name + description; column tests only if sibling marts have them).
+
+Run: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .` — expected PASS.
+
+- [ ] **Step 2: Failing backend test**
+
+In `backend/tests/test_benchmarks.py` add:
+
+```python
+def test_benchmarks_use_census_mart_for_context(monkeypatch):
+    census = {
+        "primary": {"year": 202425, "fsm_pct": 25.3, "eal_pct": 21.8, "median_pupils": 240},
+        "secondary": {"year": 202425, "fsm_pct": 24.1, "eal_pct": 18.9, "median_pupils": 980},
+    }
+    result = compute_benchmarks(_sample_df(), census_benchmarks=census)
+    assert result["primary"]["fsm_pct"] == 25.3
+    assert result["secondary"]["eal_pct"] == 18.9
+    assert result["secondary"]["median_pupils"] == 980
+    # KS2-only columns must not produce a fake secondary disadvantaged anchor.
+    assert result["secondary"]["disadvantaged_pct"] is None
+
+
+def test_benchmarks_context_none_when_mart_missing():
+    result = compute_benchmarks(_sample_df(), census_benchmarks=None)
+    assert result["primary"]["fsm_pct"] is None   # never silently fall back
+```
+
+(`_sample_df()` = this file's existing dataframe fixture.) Run the file; expected: FAIL (unexpected keyword `census_benchmarks`).
+
+- [ ] **Step 3: Backend implementation**
+
+`backend/models.py` (next to the national-average models):
+
+```python
+class CensusBenchmark(Base):
+    """State-school context benchmarks from the pupil census — one row per phase."""
+    __tablename__ = "fact_census_benchmarks"
+    __table_args__ = MARTS
+
+    phase = Column(String(20), primary_key=True)
+    year = Column(Integer)
+    fsm_pct = Column(Float)      # pupil-weighted mean
+    eal_pct = Column(Float)      # pupil-weighted mean
+    median_pupils = Column(Integer)
+```
+
+`backend/data_loader.py` — change the signature and `_block`:
+
+```python
+def compute_benchmarks(df: pd.DataFrame, census_benchmarks: dict | None = None) -> dict:
+```
+
+Inside, keep `_median` and `_weighted_disadvantaged` as-is, and replace `_block` with:
+
+```python
+    def _block(sub, phase, with_disadvantaged):
+        census = (census_benchmarks or {}).get(phase) or {}
+        block = {
+            # Context measures come from the census mart (pupil-weighted):
+            # the performance df has no fsm_pct, and its eal/disadvantaged
+            # columns are KS2-only — medianing them for "secondary" produced
+            # junk anchors from the handful of all-through schools.
+            "eal_pct": census.get("eal_pct"),
+            "sen_support_pct": _median(sub, "sen_support_pct"),
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct") if with_disadvantaged else None,
+            "fsm_pct": census.get("fsm_pct"),
+            "median_pupils": census.get("median_pupils"),
+        }
+        if with_disadvantaged:
+            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
+        return block
+```
+
+and the return:
+
+```python
+    return {
+        "source": "state-school average (computed from our dataset)",
+        "year": int(latest_year),
+        "primary": _block(prim, "primary", with_disadvantaged=True),
+        "secondary": _block(sec, "secondary", with_disadvantaged=False),
+    }
+```
+
+In `backend/app.py`'s compare endpoint, load the mart and pass it (same defensive style as the national-averages queries):
+
+```python
+    census_benchmarks = None
+    try:
+        rows = db.query(CensusBenchmark).all()
+        if rows:
+            census_benchmarks = {
+                r.phase: {
+                    "year": r.year,
+                    "fsm_pct": r.fsm_pct,
+                    "eal_pct": r.eal_pct,
+                    "median_pupils": r.median_pupils,
+                }
+                for r in rows
+            }
+    except Exception:
+        db.rollback()
+    ...
+        "benchmarks": compute_benchmarks(df, census_benchmarks=census_benchmarks),
+```
+
+(Import `CensusBenchmark`; use the endpoint's existing db session pattern.) Run the backend suite; expected: PASS (update any existing benchmark tests that asserted the old median-sourced fsm/eal values).
+
+- [ ] **Step 4: Frontend — remove the cross-definition fallback**
+
+`nextjs-app/components/compare/CompareCommunity.tsx:36`:
+
+```tsx
+    const anchor = bench?.fsm_pct ?? null;
+```
+
+If the FSM chip has unit coverage, update/add the case: `anchor` null ⇒ no verdict chip rendered (bare value only). Run `cd nextjs-app && npx tsc --noEmit && npm test` — expected PASS.
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/transform/models/marts backend/models.py backend/data_loader.py backend/app.py backend/tests/test_benchmarks.py nextjs-app/components/compare/CompareCommunity.tsx nextjs-app/__tests__
+git commit -m "fix(compare): census-sourced FSM/EAL benchmarks; never fall back across measure definitions"
+```
+
+---
+
+### Task 5: Official KS4 national averages
+
+**Files:**
+- Modify: `pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py` (new stream, registered in `discover_streams`)
+- Create: `pipeline/transform/models/staging/stg_ees_ks4_national.sql`
+- Modify: `pipeline/transform/models/staging/_stg_sources.yml` (add raw table `ees_ks4_national`)
+- Modify: `pipeline/transform/models/marts/fact_ks4_national_averages.sql` (rewrite)
+- Modify: `backend/models.py` (Ks4NationalAverage docstring), `backend/app.py` (`_national_averages_payload` — remove the computed fallback)
+- Test: `backend/tests/test_national_averages_marts.py`
+
+**Interfaces:**
+- Consumes: EES data-catalogue CSV `https://explore-education-statistics.service.gov.uk/data-catalogue/data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv` (columns verified: `time_period, geographic_level, establishment_type_group, breakdown_topic, breakdown, attainment8_average, progress8_average, engmath_95_percent, engmath_94_percent, ebacc_entering_percent, ebacc_95_percent, ebacc_94_percent, ebacc_aps_average, …`).
+- Produces: `marts.fact_ks4_national_averages` with the SAME columns as today (so `Ks4NationalAverage` needs no schema change), now holding official DfE figures; `gcse_grade_91_pct` is NULL (not in the official series — the England anchor for that measure disappears, which is correct: it was noise).
+
+- [ ] **Step 1: Tap stream**
+
+In `tap.py`, after the KS2 national stream, add:
+
+```python
+# ── KS4 National Headlines (national level only — one row per year) ──────────
+# Dataset: "National characteristics summary data" (Key stage 4 performance).
+# Official England state-funded headline measures, 2018/19 → latest.
+# Suppressed values ('z', 'x') → NULL downstream. Progress 8 is legitimately
+# absent in years with no KS2 baseline (e.g. 2024/25) — that is DfE policy,
+# not missing data.
+
+_KS4_NATIONAL_CSV_URL = (
+    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
+    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
+)
+
+_KS4_NATIONAL_COL_MAP = {
+    "attainment8_average":    "attainment_8_score",
+    "progress8_average":      "progress_8_score",
+    "engmath_94_percent":     "english_maths_standard_pass_pct",
+    "engmath_95_percent":     "english_maths_strong_pass_pct",
+    "ebacc_entering_percent": "ebacc_entry_pct",
+    "ebacc_94_percent":       "ebacc_standard_pass_pct",
+    "ebacc_95_percent":       "ebacc_strong_pass_pct",
+    "ebacc_aps_average":      "ebacc_avg_score",
+}
+
+
+class EESKs4NationalStream(Stream):
+    """National KS4 headline averages — one row per academic year.
+
+    Filters to geographic_level == 'National', establishment_type_group ==
+    'All state-funded', breakdown_topic == 'Total', breakdown == 'Total'
+    so only the England-wide all-pupils row per year is emitted.
+    """
+
+    name = "ees_ks4_national"
+    primary_keys = ["time_period"]
+    replication_key = None
+
+    schema = th.PropertiesList(
+        th.Property("time_period", th.StringType, required=True),
+        *[th.Property(out, th.StringType) for out in _KS4_NATIONAL_COL_MAP.values()],
+    ).to_dict()
+
+    def get_records(self, context):
+        import pandas as pd
+
+        self.logger.info("Downloading KS4 national headlines: %s", _KS4_NATIONAL_CSV_URL)
+        resp = requests.get(_KS4_NATIONAL_CSV_URL, timeout=60)
+        resp.raise_for_status()
+
+        df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)
+        df.columns = [c.strip().lower() for c in df.columns]
+
+        for col, want in [
+            ("geographic_level", "national"),
+            ("establishment_type_group", "all state-funded"),
+            ("breakdown_topic", "total"),
+            ("breakdown", "total"),
+        ]:
+            if col in df.columns:
+                df = df[df[col].str.strip().str.lower() == want]
+
+        self.logger.info("Emitting %d national KS4 rows", len(df))
+        for _, row in df.iterrows():
+            record = {"time_period": row.get("time_period", "").strip()}
+            for src, out in _KS4_NATIONAL_COL_MAP.items():
+                record[out] = row.get(src, "")
+            yield record
+```
+
+Register `EESKs4NationalStream(self)` in `discover_streams` next to the KS2 national stream.
+
+- [ ] **Step 2: Raw source + staging model**
+
+Add to `_stg_sources.yml` under the raw source, matching the `ees_ks2_national` entry's style:
+
+```yaml
+      - name: ees_ks4_national
+        description: Official DfE KS4 national headline averages (EES data catalogue)
+```
+
+Create `pipeline/transform/models/staging/stg_ees_ks4_national.sql`:
+
+```sql
+{{ config(materialized='table') }}
+
+-- Staging model: official DfE KS4 national headline averages — one row per
+-- academic year (England, all state-funded, all pupils). Source: EES data
+-- catalogue "National characteristics summary data". Suppressed values
+-- ('z', 'x') are coerced to NULL by safe_numeric — Progress 8 is 'z' in
+-- years with no KS2 baseline (e.g. 2024/25): legitimately unpublished.
+
+select
+    cast(trim(time_period) as integer)                        as year,
+    {{ safe_numeric('attainment_8_score') }}                  as attainment_8_score,
+    {{ safe_numeric('progress_8_score') }}                    as progress_8_score,
+    {{ safe_numeric('english_maths_standard_pass_pct') }}     as english_maths_standard_pass_pct,
+    {{ safe_numeric('english_maths_strong_pass_pct') }}       as english_maths_strong_pass_pct,
+    {{ safe_numeric('ebacc_entry_pct') }}                     as ebacc_entry_pct,
+    {{ safe_numeric('ebacc_standard_pass_pct') }}             as ebacc_standard_pass_pct,
+    {{ safe_numeric('ebacc_strong_pass_pct') }}               as ebacc_strong_pass_pct,
+    {{ safe_numeric('ebacc_avg_score') }}                     as ebacc_avg_score
+from {{ source('raw', 'ees_ks4_national') }}
+where time_period ~ '^[0-9]+$'
+```
+
+- [ ] **Step 3: Rewrite the mart**
+
+Replace the entire body of `fact_ks4_national_averages.sql`:
+
+```sql
+{{ config(materialized='table') }}
+
+-- Mart: OFFICIAL DfE KS4 national headline averages — one row per academic
+-- year (England, state-funded, all pupils), from the EES national dataset.
+-- Replaces the previous unweighted school-level means, which were 7–15
+-- points off every headline measure and produced an arithmetically
+-- impossible national Progress 8. gcse_grade_91_pct has no official
+-- national series and is NULL (schema kept for the API model).
+
+select
+    year,
+    attainment_8_score,
+    progress_8_score,
+    english_maths_standard_pass_pct,
+    english_maths_strong_pass_pct,
+    ebacc_entry_pct,
+    ebacc_standard_pass_pct,
+    ebacc_strong_pass_pct,
+    ebacc_avg_score,
+    cast(null as double precision) as gcse_grade_91_pct
+from {{ ref('stg_ees_ks4_national') }}
+order by year
+```
+
+Run: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .` — expected PASS.
+
+- [ ] **Step 4: Backend — official provenance, no computed fallback**
+
+`backend/models.py`: change the `Ks4NationalAverage` docstring to `"""Official DfE KS4 national headline averages — one row per academic year."""`.
+
+`backend/app.py` `_national_averages_payload`: delete the entire `if not any(secondary_by_year.values()):` fallback block (it computes dataset means that the UI footnote then labels official). Update the function docstring's KS4 sentence to: `official DfE KS4 figures (fact_ks4_national_averages). If the KS4 mart hasn't been built yet, the secondary series is empty — never a computed stand-in, because the UI labels these figures as official.`
+
+Update `backend/tests/test_national_averages_marts.py`: the test that exercised the fallback now asserts the opposite —
+
+```python
+def test_ks4_secondary_empty_when_mart_missing(...):
+    # No computed stand-in: the UI labels national figures as official DfE
+    # data, so an empty mart must yield an empty secondary series.
+    payload = _national_averages_payload(df)
+    assert all(not e["secondary"] for e in payload["by_year"])
+```
+
+(adapt to the file's existing fixtures/monkeypatching). Run the backend suite — expected PASS.
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/plugins/extractors/tap-uk-ees pipeline/transform backend
+git commit -m "fix(data): official DfE KS4 national headline averages; drop mislabelled computed means"
+```
+
+---
+
+### Task 6: Honest 2021/22 footnote
+
+**Files:**
+- Modify: `nextjs-app/components/ComparisonChart.tsx:243-247`
+- Modify: `nextjs-app/lib/compareChartData.ts` (comment lines 7, 53–55 — comments only, no logic)
+- Modify: `nextjs-app/__tests__/lib/compareChartData.test.ts` (test name/comment wording only)
+- Modify: `docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md` §8.1
+
+**Interfaces:** none — copy and docs only. This is the one place the plan changes reviewed copy, because the reviewed copy is factually wrong (Global Constraints exception).
+
+- [ ] **Step 1: Fix the user-facing copy**
+
+In `ComparisonChart.tsx` replace the note:
+
+```tsx
+      {built.showUnpublished202122Note && (
+        <p className={styles.chartNote}>
+          No national tests were held in 2019/20 and 2020/21 (COVID), and our dataset doesn&apos;t
+          yet include school-level figures for 2021/22 — the England average is shown for that
+          year.
+        </p>
+      )}
+```
+
+- [ ] **Step 2: Fix the lying comments**
+
+In `compareChartData.ts`, update the header comment (line 7) and the `showUnpublished202122Note` doc comment (lines 53–55) to say the 2021/22 school-level figures are *absent from our dataset* (DfE published them in Dec 2022; ingesting them is a backlog pipeline task), not "unpublished". Rename nothing (the flag name stays — pure rename churn). In `compareChartData.test.ts`, adjust the test description/comment wording the same way.
+
+- [ ] **Step 3: Correct spec §8.1**
+
+In the spec's §8.1, replace any wording that calls 2021/22 school-level KS2 a "permanent DfE gap" with: DfE published school-level KS2 results for 2021/22 in December 2022 (with comparability caveats); they are not yet ingested — loading them remains an open pipeline task, and the chart footnote says "our dataset doesn't yet include" accordingly.
+
+- [ ] **Step 4: Verify + commit**
+
+Run: `cd nextjs-app && npx tsc --noEmit && npm test` — expected PASS.
+
+```bash
+git add nextjs-app docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md
+git commit -m "fix(compare): stop attributing the missing 2021/22 school-level year to DfE"
+```
+
+---
+
+### Task 7: Full verification, PR, and post-deploy checklist
+
+**Files:** none new (verification + PR).
+
+- [ ] **Step 1: Run everything**
+
+```bash
+uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q
+cd nextjs-app && npx tsc --noEmit && npm test && cd ..
+cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . && cd ../..
+```
+
+Expected: all PASS.
+
+- [ ] **Step 2: Open the PR**
+
+Push `fix/compare-final-review-mustfix`; open a PR via the Gitea API using `git credential fill` basic auth (token-header auth 401s). PR body: summarize the five findings and fixes, link the expert review, end with the standard Claude Code attribution + session URL. Note in the body that findings 2 and 4 also need a **DAG run after the staging deploy** before the UI shows corrected data.
+
+- [ ] **Step 3: Post-merge staging verification (after the user merges and the daily DAG runs — record results, do not promote)**
+
+```bash
+# Report card dated by its own inspection (Barclay): expect 2026-02-03
+curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=138690" | python3 -c "import json,sys; o=json.load(sys.stdin)['comparison']['138690']['ofsted']; print(o['rc_inspection_date'], o['inspection_date'])"
+# Stale Watford rc grades cleared by the fresh extract: expect report_card == {}
+curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=136276" | python3 -c "import json,sys; print(json.load(sys.stdin)['comparison']['136276']['ofsted']['report_card'])"
+# Official KS4 nationals: expect A8 46.0 for 202425, progress_8_score absent
+curl -sk "https://stx.schoolcompare.co.uk/api/national-averages" | python3 -c "import json,sys; print(json.load(sys.stdin)['secondary'])"
+# FSM benchmark real (~24-26), secondary disadvantaged_pct gone
+curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=138690,136276" | python3 -c "import json,sys; print(json.load(sys.stdin)['benchmarks'])"
+```
+
+Then re-screenshot both phase views (desktop + mobile, "More measures" expanded, Watford Grammar in the secondary set) and hand them to the Ofsted expert agent for the sign-off pass it said it expects. Production promotion remains the human's manual call.
+
+---
+
+## Out of Scope (expert should-fix/minor — separate follow-ups)
+
+- 137086-style interim state (subgrades without an overall from an RI reinspection) rendering treatment (finding 6).
+- Disadvantaged cohort sizes on the attainment row (finding 7, spec §8.5).
+- SEN/EAL "typical school" labelling and secondary SEN benchmark (finding 8) — note Task 4 already upgrades EAL to a pupil-weighted census figure.
+- Selective-school admissions copy variant (finding 9).
+- Removing/relabelling `gcse_grade_91_pct` as a compare measure (finding 10) — Task 5 already removes its false England anchor.
+- Palette deviation (11), trends picker label (12), "More measures" expanded-state verification (13).
+- Actually ingesting the 2021/22 school-level KS2 release (the copy in Task 6 says "doesn't *yet* include").

docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md

@@ -256,7 +256,12 @@ implementation, beyond what the mockups can show:
    DfE stated it would not publish KS2 2021/22 in performance tables
    (verified 2026-07-12 against EES, the CSP download service, and
    DfE release notes; see `# TASK 6 VERIFICATION` in
-   `pipeline/scripts/diagnose_compare_gaps.py`). The chart's England-
+   `pipeline/scripts/diagnose_compare_gaps.py`; re-verified 2026-07-16
+   after an expert-review challenge — the GOV.UK statistics announcement
+   "Primary school performance tables: 2022" is marked CANCELLED with
+   "will not be published in key stage 2 performance tables in academic
+   year 2021/22", so the footnote's "DfE didn't publish" claim stands
+   and must not be softened to "not in our dataset"). The chart's England-
    only 2021/22 point with broken school lines is therefore the
    correct permanent rendering; copy should say "DfE didn't publish
    school-level figures for 2021/22", not "not in our dataset yet".

e2e/tests/journeys.spec.ts

@@ -19,6 +19,40 @@ function schoolLinks(page: Page) {
   return page.locator('a[href^="/school/"]');
 }
 
+/**
+ * Two URNs guaranteed to be pure-primary (same phase). The compare page's
+ * phase tabs split all-through schools (which carry KS4 data) onto the
+ * secondary tab, so picking two arbitrary "primary" search hits can land
+ * them on different tabs where only the active one renders. Selecting via
+ * the API by exact phase keeps both on the same tab. Data-invariant: uses
+ * whatever primaries the environment holds.
+ */
+async function twoPrimaryUrns(page: Page): Promise<[string, string]> {
+  const res = await page.request.get('/api/schools?search=primary&per_page=50');
+  expect(res.ok()).toBeTruthy();
+  const body = await res.json();
+  const urns: string[] = (body.schools ?? [])
+    .filter((s: { phase?: string; rwm_expected_pct?: number | null }) =>
+      s.phase === 'Primary' && s.rwm_expected_pct != null,
+    )
+    .map((s: { urn: number }) => String(s.urn));
+  expect(urns.length).toBeGreaterThanOrEqual(2);
+  return [urns[0], urns[1]];
+}
+
+async function twoSecondaryUrns(page: Page): Promise<[string, string]> {
+  const res = await page.request.get('/api/schools?search=school&per_page=100');
+  expect(res.ok()).toBeTruthy();
+  const body = await res.json();
+  const urns: string[] = (body.schools ?? [])
+    .filter((s: { phase?: string; attainment_8_score?: number | null }) =>
+      s.phase === 'Secondary' && s.attainment_8_score != null,
+    )
+    .map((s: { urn: number }) => String(s.urn));
+  expect(urns.length).toBeGreaterThanOrEqual(2);
+  return [urns[0], urns[1]];
+}
+
 test('home page loads with hero search', async ({ page }) => {
   await page.goto('/');
   await expect(page.locator('h1').first()).toBeVisible();
@@ -139,19 +173,13 @@ test('results map fullscreen falls back to an overlay on iOS', async ({ page })
 });
 
 test('comparing two schools shows the parent-first sections side by side', async ({ page }) => {
-  // Collect two school URNs from search results, then load the share URL
-  await searchByName(page, 'primary');
-  await expect(schoolLinks(page).first()).toBeVisible({ timeout: 15_000 });
-  const hrefs = await schoolLinks(page).evaluateAll((links) =>
-    links.map((l) => (l as HTMLAnchorElement).getAttribute('href') || '')
-  );
-  const urns = [...new Set(hrefs.map((h) => h.match(/\/school\/(\d+)/)?.[1]).filter(Boolean))];
-  expect(urns.length).toBeGreaterThanOrEqual(2);
+  // Two same-phase (pure primary) schools so both stay on one tab.
+  const [urn0, urn1] = await twoPrimaryUrns(page);
 
-  await page.goto(`/compare?urns=${urns[0]},${urns[1]}`);
+  await page.goto(`/compare?urns=${urn0},${urn1}`);
   // Both schools' detail links should render in the comparison view
-  await expect(page.locator(`a[href*="${urns[0]}"]`).first()).toBeVisible({ timeout: 15_000 });
-  await expect(page.locator(`a[href*="${urns[1]}"]`).first()).toBeVisible();
+  await expect(page.locator(`a[href*="${urn0}"]`).first()).toBeVisible({ timeout: 15_000 });
+  await expect(page.locator(`a[href*="${urn1}"]`).first()).toBeVisible();
 
   // The parent-first sections render in order (data-invariant: headings only)
   for (const heading of [
@@ -169,6 +197,16 @@ test('comparing two schools shows the parent-first sections side by side', async
   // Every number gets an anchor: at least one England-average tick or label
   await expect(page.getByText(/England \d+/).first()).toBeVisible();
 
+  // Desktop: the sticky school bar shares the sections' grid template
+  // (200px label rail + one column per school) so chips align with the
+  // columns they label.
+  const barTemplate = await page
+    .locator('[aria-label="Schools in this comparison"]')
+    .evaluate((el) => getComputedStyle(el).gridTemplateColumns);
+  expect(barTemplate).toMatch(/^200px /);
+  // ...and its label rail carries the comparison caption.
+  await expect(page.getByText(/^\d+ (primary|secondary) schools?$/)).toBeVisible();
+
   // Ofsted linkout goes to the school's provider page, never a report deep-link
   const ofstedLink = page.getByRole('link', { name: /Ofsted page/i }).first();
   await expect(ofstedLink).toBeVisible();
@@ -184,6 +222,53 @@ test('comparing two schools shows the parent-first sections side by side', async
   }
 });
 
+test('comparing two secondary schools renders the secondary sections', async ({ page }) => {
+  const [urn0, urn1] = await twoSecondaryUrns(page);
+
+  await page.goto(`/compare?urns=${urn0},${urn1}`);
+  await expect(page.locator(`a[href*="${urn0}"]`).first()).toBeVisible({ timeout: 15_000 });
+
+  // The parent-first sections must render — this page was completely blank
+  // for all-secondary baskets (expert review must-fix #1).
+  await expect(page.getByRole('heading', { name: 'At a glance' }).first()).toBeVisible({
+    timeout: 15_000,
+  });
+  await expect(page.getByRole('heading', { name: 'Ofsted inspection' }).first()).toBeVisible();
+  // A KS4 measure proves the secondary academics variant rendered.
+  await expect(page.getByText(/Attainment 8/i).first()).toBeVisible();
+  await expect(page.getByText(/No primary schools in your comparison/)).toHaveCount(0);
+
+  // The admissions template must be phase-aware: the primaries' distance
+  // copy ("non-faith primaries") must never appear on a secondary comparison
+  // (expert sign-off must-fix M3).
+  await expect(page.getByText(/non-faith primaries/)).toHaveCount(0);
+});
+
+test('opening a different compare link after a previous comparison still renders', async ({ page }) => {
+  // Regression: the first visit stores a basket in localStorage; opening a
+  // link for a DIFFERENT school set then raced a stale fetch for the stored
+  // basket against the new SSR data, blanking every section (including the
+  // trends chart) until a hard refresh.
+  const [s0, s1] = await twoSecondaryUrns(page);
+  const [p0, p1] = await twoPrimaryUrns(page);
+
+  await page.goto(`/compare?urns=${s0},${s1}`);
+  await expect(page.getByRole('heading', { name: 'At a glance' }).first()).toBeVisible({
+    timeout: 15_000,
+  });
+
+  await page.goto(`/compare?urns=${p0},${p1}`);
+  await expect(page.getByRole('heading', { name: 'At a glance' }).first()).toBeVisible({
+    timeout: 15_000,
+  });
+  // Give any straggling stale response time to land, then confirm the new
+  // comparison is still on screen.
+  await page.waitForTimeout(1500);
+  await expect(page.getByRole('heading', { name: 'At a glance' }).first()).toBeVisible();
+  await expect(page.getByRole('heading', { name: 'Explore trends' }).first()).toBeVisible();
+  await expect(page.locator(`a[href*="${p0}"]`).first()).toBeVisible();
+});
+
 test('compare chart on mobile shows school chips with tap-to-focus', async ({ page }) => {
   await page.setViewportSize({ width: 390, height: 844 });
 
@@ -212,8 +297,26 @@ test('compare chart on mobile shows school chips with tap-to-focus', async ({ pa
   );
   expect(bodyOverflowsX).toBe(false);
 
+  // The sticky school bar must pin *below* the sticky site header, not at
+  // top:0 where the header covers it and the selected schools are hidden.
+  // Assert the sticky offset directly (robust — no scroll timing needed).
+  const barTop = await page
+    .locator('[class*="schoolBar"]')
+    .first()
+    .evaluate((el) => parseFloat(getComputedStyle(el).top));
+  const headerHeight = await page
+    .locator('[class*="header"]')
+    .first()
+    .evaluate((el) => el.getBoundingClientRect().height);
+  expect(barTop).toBeGreaterThanOrEqual(headerHeight - 1);
+
   // The trends chart still renders (inside the Explore trends section)…
-  await expect(page.locator('canvas:visible').first()).toBeVisible({ timeout: 15_000 });
+  const chartCanvas = page.locator('canvas:visible').first();
+  await expect(chartCanvas).toBeVisible({ timeout: 15_000 });
+  // …at a real height, not the squashed ~150px Chart.js fallback that
+  // appears when the container lacks a definite height.
+  const chartBox = await chartCanvas.boundingBox();
+  expect(chartBox && chartBox.height).toBeGreaterThan(220);
 
   // …with the mobile chart legend chips and tap-to-focus behaviour intact.
   const chipGroup = page.getByRole('group', { name: /highlight a school/i });

nextjs-app/__tests__/components/CompareAdmissions.test.tsx

@@ -0,0 +1,140 @@
+/**
+ * Getting a place — phase and school-type correctness (expert sign-off
+ * must-fixes M1/M3):
+ * - an all-through school's Year 7 round must never render on the primary
+ *   tab as if it were Reception odds;
+ * - selective schools get entrance-test framing, and the secondary tab
+ *   never shows the primaries' distance template.
+ */
+
+import { render, screen } from '@testing-library/react';
+
+import { CompareAdmissions } from '@/components/compare/CompareAdmissions';
+import type { ComparisonData, School, SchoolAdmissions } from '@/lib/types';
+
+function school(urn: number, name: string, extra: Partial<School> = {}): School {
+  return { urn, school_name: name, ...extra } as School;
+}
+
+function admissions(partial: Partial<SchoolAdmissions>): SchoolAdmissions {
+  return {
+    year: 202627,
+    school_phase: 'Secondary',
+    places_offered: 173,
+    total_applications: 433,
+    first_preference_offer_pct: 83,
+    oversubscribed: true,
+    ...partial,
+  } as SchoolAdmissions;
+}
+
+function entry(info: School, a: SchoolAdmissions | null): ComparisonData {
+  return {
+    school_info: info,
+    yearly_data: [],
+    ofsted: null,
+    census: null,
+    admissions: a,
+    admissions_history: a ? [a] : [],
+    deprivation: null,
+  };
+}
+
+describe('CompareAdmissions', () => {
+  it("does not show an all-through school's Year 7 round on the primary tab", () => {
+    // The real M1 scenario: an all-through school (Year 7 round only) beside
+    // a primary with a Reception round.
+    const allThrough = school(137306, 'Hessle High and Penshurst Primary');
+    const primary = school(138690, 'Barclay Primary School');
+    const data = {
+      '137306': entry(allThrough, admissions({ school_phase: 'Secondary' })),
+      '138690': entry(
+        primary,
+        admissions({
+          school_phase: 'Primary',
+          total_applications: 300,
+          places_offered: 120,
+          first_preference_offer_pct: 96,
+        }),
+      ),
+    };
+
+    render(<CompareAdmissions schools={[allThrough, primary]} data={data} isSecondary={false} />);
+
+    // Hessle's Year 7 figures must not appear…
+    expect(screen.queryByText('433')).toBeNull();
+    expect(
+      screen.getByText(/We don't hold Reception admissions data for this school/),
+    ).toBeInTheDocument();
+    // …while Barclay's Reception round renders normally.
+    expect(screen.getByText('300')).toBeInTheDocument();
+  });
+
+  it('phase-labels the section empty state when no matching round exists at all', () => {
+    const allThrough = school(137306, 'Hessle High and Penshurst Primary');
+    const data = { '137306': entry(allThrough, admissions({ school_phase: 'Secondary' })) };
+
+    render(<CompareAdmissions schools={[allThrough]} data={data} isSecondary={false} />);
+
+    expect(
+      screen.getByText(/No Reception admissions data is available for these schools yet/),
+    ).toBeInTheDocument();
+    expect(screen.queryByText('433')).toBeNull();
+  });
+
+  it('shows the Year 7 round on the secondary tab', () => {
+    const allThrough = school(137306, 'Hessle High and Penshurst Primary');
+    const data = { '137306': entry(allThrough, admissions({ school_phase: 'Secondary' })) };
+
+    render(<CompareAdmissions schools={[allThrough]} data={data} isSecondary={true} />);
+
+    expect(screen.getByText('433')).toBeInTheDocument();
+    expect(screen.getByText('173')).toBeInTheDocument();
+  });
+
+  it('gives selective schools entrance-test framing, never the distance template', () => {
+    const grammar = school(136276, 'Watford Grammar School for Boys', {
+      admissions_policy: 'Selective',
+      religious_denomination: 'Church of England',
+    });
+    const data = {
+      '136276': entry(grammar, admissions({ first_preference_offer_pct: 43.7 })),
+    };
+
+    render(<CompareAdmissions schools={[grammar]} data={data} isSecondary={true} />);
+
+    expect(
+      screen.getByText(/Entry is by entrance test — the school is selective/),
+    ).toBeInTheDocument();
+    expect(screen.queryByText(/non-faith primaries/)).toBeNull();
+  });
+
+  it('secondary faith school gets faith-aware copy, not the primaries template', () => {
+    const faithSchool = school(102052, "Bishop Stopford's School", {
+      admissions_policy: 'Non-selective',
+      religious_denomination: 'Church of England',
+    });
+    const data = {
+      '102052': entry(faithSchool, admissions({ first_preference_offer_pct: 68 })),
+    };
+
+    render(<CompareAdmissions schools={[faithSchool]} data={data} isSecondary={true} />);
+
+    expect(screen.getByText(/faith-based criteria may apply/)).toBeInTheDocument();
+    expect(screen.queryByText(/non-faith primaries/)).toBeNull();
+  });
+
+  it('keeps the reviewed distance copy for oversubscribed non-faith primaries', () => {
+    const primary = school(100140, 'Plumcroft Primary School');
+    const data = {
+      '100140': entry(
+        primary,
+        admissions({ school_phase: 'Primary', first_preference_offer_pct: 73.4 }),
+      ),
+    };
+
+    render(<CompareAdmissions schools={[primary]} data={data} isSecondary={false} />);
+
+    expect(screen.getByText(/for most non-faith primaries, distance decides/)).toBeInTheDocument();
+  });
+});

nextjs-app/__tests__/components/CompareOfsted.test.tsx

@@ -95,4 +95,76 @@ describe('CompareOfsted', () => {
     expect(links).toHaveLength(3);
     expect(links[0]).toHaveAttribute('href', 'https://reports.ofsted.gov.uk/provider/21/1');
   });
+
+  it('never renders Ofsted sentinel codes (9 = not applicable) as judgement chips', () => {
+    const sentinelSchool = school(6, 'Sentinel School');
+    const sentinelData: Record<string, ComparisonData> = {
+      '6': {
+        school_info: sentinelSchool,
+        yearly_data: [],
+        ofsted: ofsted({
+          overall_effectiveness: 2,
+          grade_source: 'graded',
+          quality_of_education: 1,
+          early_years_provision: 9,
+          sixth_form_provision: 2,
+        }),
+      },
+    };
+    render(<CompareOfsted schools={[sentinelSchool]} data={sentinelData} />);
+    // Real grades render…
+    expect(screen.getByText('Quality of education')).toBeInTheDocument();
+    // …the applicable sixth-form judgement renders (was previously dropped)…
+    expect(screen.getByText('Sixth form provision')).toBeInTheDocument();
+    // …and the not-applicable sentinel never appears, neither as area nor code.
+    expect(screen.queryByText('Early years provision')).toBeNull();
+    expect(screen.queryByText('9')).toBeNull();
+  });
+
+  it('dates a report card with the report-card inspection date, never the legacy date', () => {
+    const cardSchool = school(4, 'Dated Card School');
+    const cardData: Record<string, ComparisonData> = {
+      '4': {
+        school_info: cardSchool,
+        yearly_data: [],
+        ofsted: ofsted({
+          inspection_date: '2021-10-07',
+          rc_inspection_date: '2026-02-03',
+          rc_safeguarding_met: true,
+          report_card: { rc_achievement: { code: 1, label: 'Exceptional' } },
+        }),
+      },
+    };
+    render(<CompareOfsted schools={[cardSchool]} data={cardData} />);
+    expect(screen.getByText(/3 Feb 2026/)).toBeInTheDocument();
+    expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
+    expect(screen.queryByText('4+ years ago')).toBeNull();
+  });
+
+  it('shows an em dash when a report card has no rc_inspection_date yet', () => {
+    const cardSchool = school(5, 'Undated Card School');
+    const cardData: Record<string, ComparisonData> = {
+      '5': {
+        school_info: cardSchool,
+        yearly_data: [],
+        ofsted: ofsted({
+          inspection_date: '2021-10-07',
+          rc_inspection_date: null,
+          rc_safeguarding_met: true,
+          report_card: { rc_achievement: { code: 1, label: 'Exceptional' } },
+        }),
+      },
+    };
+    render(<CompareOfsted schools={[cardSchool]} data={cardData} />);
+    expect(screen.getByText('—')).toBeInTheDocument();
+    expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
+  });
+
+  it('renders a per-measure mobile tag with the short school name', () => {
+    render(<CompareOfsted schools={schools} data={data} />);
+    // Each measure repeats the schools, so the short name ("Graded" from
+    // "Graded School") appears once per measure (4) via the cell tag.
+    expect(screen.getAllByText('Graded').length).toBe(4);
+    expect(screen.getAllByText('Card').length).toBe(4);
+  });
 });

nextjs-app/__tests__/components/ComparisonView.phase.test.tsx

@@ -0,0 +1,78 @@
+/**
+ * Regression: an all-secondary comparison must render the secondary sections.
+ *
+ * The basket hydrates from the URL a beat after mount, so the auto-phase
+ * effect must re-run once selectedSchools arrives — with deps of only
+ * [comparisonData] it fired once against an empty basket, bailed, and the
+ * page stayed on an empty "primary" tab ("No primary schools in your
+ * comparison") even though all schools were secondary.
+ */
+
+import { render, screen, waitFor } from '@testing-library/react';
+
+import { ComparisonView } from '@/components/ComparisonView';
+import { ComparisonProvider } from '@/context/ComparisonProvider';
+import type { ComparisonData, School } from '@/lib/types';
+
+const fetchComparison = jest.fn();
+jest.mock('@/lib/api', () => ({
+  fetchComparison: (...args: unknown[]) => fetchComparison(...args),
+}));
+jest.mock('@/lib/analytics', () => ({ track: jest.fn() }));
+
+function secondarySchool(urn: number, name: string): School {
+  return {
+    urn,
+    school_name: name,
+    local_authority: 'Testshire',
+    school_type: 'Academy converter',
+    attainment_8_score: 55,
+    phase: 'Secondary',
+  } as School;
+}
+
+function data(urn: number, name: string): ComparisonData {
+  return {
+    school_info: secondarySchool(urn, name),
+    yearly_data: [{ year: 202425, attainment_8_score: 55 }] as ComparisonData['yearly_data'],
+    ofsted: null,
+    census: null,
+    admissions: null,
+    admissions_history: [],
+    deprivation: null,
+  };
+}
+
+const INITIAL_DATA = {
+  '300': data(300, 'Gamma High'),
+  '400': data(400, 'Delta Academy'),
+};
+
+test('an all-secondary comparison renders the sections, not an empty primary tab', async () => {
+  render(
+    <ComparisonProvider>
+      <ComparisonView
+        initialData={INITIAL_DATA}
+        initialNationalAverages={{
+          year: 202425,
+          primary: {},
+          secondary: { attainment_8_score: 46 },
+          by_year: [],
+        }}
+        initialBenchmarks={undefined}
+        initialUrns={[300, 400]}
+        metrics={[]}
+        selectedMetric="attainment_8_score"
+      />
+    </ComparisonProvider>,
+  );
+
+  await waitFor(() => {
+    expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
+  });
+  expect(screen.getAllByText('Gamma High').length).toBeGreaterThan(0);
+  expect(screen.queryByText(/No primary schools in your comparison/)).toBeNull();
+  // The sticky bar's rail caption reflects the active phase and count.
+  expect(screen.getByText('2 secondary schools')).toBeInTheDocument();
+  expect(fetchComparison).not.toHaveBeenCalled();
+});

nextjs-app/__tests__/components/ComparisonView.staleFetch.test.tsx

@@ -0,0 +1,107 @@
+/**
+ * Regression: opening a compare link while a DIFFERENT basket is stored must
+ * not blank the page.
+ *
+ * The basket hydrates from localStorage first, which can fire a fetch for the
+ * OLD school set; the URL-seed effect then replaces the basket with the URL's
+ * schools (already covered by SSR data, so no new fetch). When the stale
+ * response for the old set finally lands, it must not clobber the fresh SSR
+ * data — that left every section (including the trends chart) empty until a
+ * hard refresh.
+ */
+
+import { act, render, screen, waitFor } from '@testing-library/react';
+
+import { ComparisonView } from '@/components/ComparisonView';
+import { ComparisonProvider } from '@/context/ComparisonProvider';
+import type { ComparisonData, School } from '@/lib/types';
+
+const fetchComparison = jest.fn();
+jest.mock('@/lib/api', () => ({
+  fetchComparison: (...args: unknown[]) => fetchComparison(...args),
+}));
+jest.mock('@/lib/analytics', () => ({ track: jest.fn() }));
+
+function school(urn: number, name: string): School {
+  return {
+    urn,
+    school_name: name,
+    local_authority: 'Testshire',
+    school_type: 'Community school',
+    rwm_expected_pct: 80,
+    phase: 'Primary',
+  } as School;
+}
+
+function data(urn: number, name: string): ComparisonData {
+  return {
+    school_info: school(urn, name),
+    yearly_data: [{ year: 202425, rwm_expected_pct: 80 }] as ComparisonData['yearly_data'],
+    ofsted: null,
+    census: null,
+    admissions: null,
+    admissions_history: [],
+    deprivation: null,
+  };
+}
+
+// The visitor's previously stored basket (a different school entirely).
+const STORED_SCHOOL = school(900, 'Old Stored School');
+
+// The comparison the URL (and SSR) actually asked for.
+const URL_DATA = {
+  '100': data(100, 'Alpha Primary'),
+  '200': data(200, 'Beta Primary'),
+};
+
+beforeEach(() => {
+  fetchComparison.mockReset();
+  localStorage.clear();
+});
+
+test('a stale fetch for the previously stored basket does not clobber the URL comparison', async () => {
+  localStorage.setItem('selectedSchools', JSON.stringify([STORED_SCHOOL]));
+
+  const pending: Array<(v: unknown) => void> = [];
+  fetchComparison.mockImplementation(() => new Promise((resolve) => pending.push(resolve)));
+
+  render(
+    <ComparisonProvider>
+      <ComparisonView
+        initialData={URL_DATA}
+        initialNationalAverages={{
+          year: 202425,
+          primary: { rwm_expected_pct: 62 },
+          secondary: {},
+          by_year: [],
+        }}
+        initialBenchmarks={undefined}
+        initialUrns={[100, 200]}
+        metrics={[]}
+        selectedMetric="rwm_expected_pct"
+      />
+    </ComparisonProvider>,
+  );
+
+  // The URL's schools render from SSR data once the basket is reseeded.
+  await waitFor(() => {
+    expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
+  });
+  expect(screen.getAllByText('Alpha Primary').length).toBeGreaterThan(0);
+
+  // The transient stored-basket fetch (for school 900) resolves LATE, after
+  // the basket has moved on to the URL's schools.
+  await act(async () => {
+    for (const resolve of pending) {
+      resolve({
+        comparison: { '900': data(900, 'Old Stored School') },
+        national_averages: { year: 202425, primary: {}, secondary: {}, by_year: [] },
+        benchmarks: undefined,
+      });
+    }
+  });
+
+  // The page must still show the URL comparison — not go blank.
+  expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
+  expect(screen.getAllByText('Alpha Primary').length).toBeGreaterThan(0);
+});

nextjs-app/__tests__/lib/compareLogic.test.ts

@@ -7,6 +7,7 @@
 
 import {
   OFSTED_LEGACY_GRADES,
+  admissionsForPhase,
   ofstedDisplay,
   progressBand,
   rcAreaLabel,
@@ -126,6 +127,13 @@ describe('ofstedDisplay', () => {
     expect(ofstedDisplay(ofsted({})).kind).toBe('none');
   });
 
+  it('identifies transitional inspections without overall grades', () => {
+    const transitional = ofstedDisplay(
+      ofsted({ overall_effectiveness: null, inspection_date: '2024-11-05' }),
+    );
+    expect(transitional.kind).toBe('transitional');
+  });
+
   it('uses the four legacy grade words', () => {
     expect(OFSTED_LEGACY_GRADES).toEqual({
       1: 'Outstanding',
@@ -181,6 +189,44 @@ describe('summariseAdmissions', () => {
   });
 });
 
+describe('admissionsForPhase', () => {
+  const row = (year: number, school_phase: string | null): SchoolAdmissions =>
+    ({ year, school_phase, places_offered: 100, total_applications: 200, first_preference_offer_pct: 80 }) as SchoolAdmissions;
+
+  it('returns the latest round matching the active phase', () => {
+    const data = {
+      admissions: row(202627, 'Secondary'),
+      admissions_history: [row(202526, 'Secondary'), row(202526, 'Primary'), row(202425, 'Primary')],
+    };
+    expect(admissionsForPhase(data, true)?.year).toBe(202627);
+    expect(admissionsForPhase(data, false)?.year).toBe(202526);
+    expect(admissionsForPhase(data, false)?.school_phase).toBe('Primary');
+  });
+
+  it("never substitutes the other phase's round (all-through with Year 7 data only)", () => {
+    const data = {
+      admissions: row(202627, 'Secondary'),
+      admissions_history: [row(202526, 'Secondary')],
+    };
+    expect(admissionsForPhase(data, false)).toBeNull();
+    expect(admissionsForPhase(data, true)?.year).toBe(202627);
+  });
+
+  it('uses untagged legacy rows only when no row carries a phase', () => {
+    const untagged = { admissions: row(202627, null), admissions_history: [row(202526, null)] };
+    expect(admissionsForPhase(untagged, false)?.year).toBe(202627);
+    expect(admissionsForPhase(untagged, true)?.year).toBe(202627);
+
+    const mixed = { admissions: row(202627, 'Secondary'), admissions_history: [row(202526, null)] };
+    expect(admissionsForPhase(mixed, false)).toBeNull();
+  });
+
+  it('handles missing data', () => {
+    expect(admissionsForPhase(null, false)).toBeNull();
+    expect(admissionsForPhase({ admissions: null, admissions_history: [] }, true)).toBeNull();
+  });
+});
+
 describe('progressBand', () => {
   it('CI entirely above zero → above', () => {
     expect(progressBand(1.2, 0.4, 2.0)).toBe('above');

nextjs-app/__tests__/lib/utils.test.ts

@@ -10,6 +10,7 @@ import {
   debounce,
   buildOfstedListBadge,
   metricKind,
+  shortName,
   computeYBounds,
 } from '@/lib/utils';
 
@@ -223,3 +224,17 @@ describe('isProposedToClose', () => {
     expect(isProposedToClose({})).toBe(false);
   });
 });
+
+describe('shortName', () => {
+  it('drops the trailing establishment-type words', () => {
+    expect(shortName('Barclay Primary School')).toBe('Barclay');
+    expect(shortName('Elmhurst Primary School')).toBe('Elmhurst');
+    expect(shortName("St Mary's Catholic Primary School")).toBe("St Mary's");
+    expect(shortName('Riverside Community Junior School')).toBe('Riverside');
+  });
+
+  it('keeps a name that carries no type suffix, capping very long ones', () => {
+    expect(shortName('Beaver Road')).toBe('Beaver Road');
+    expect(shortName('A'.repeat(30), 10)).toBe('AAAAAAAAA…');
+  });
+});

nextjs-app/components/ComparisonView.module.css

@@ -80,10 +80,12 @@
 }
 
 /* Sticky school bar — column identity while scrolling; horizontal scroll on
-   narrow screens */
+   narrow screens. Offset by the sticky site header's height (Navigation is
+   position: sticky, top: 0) so this bar pins just below it instead of
+   sliding underneath and being hidden. Header ≈ 65px desktop / 57px mobile. */
 .schoolBar {
   position: sticky;
-  top: 0;
+  top: 65px;
   z-index: 10;
   background: var(--bg-primary, #faf7f2);
   display: flex;
@@ -132,6 +134,11 @@
   color: var(--accent-coral-dark, #b04a2e);
 }
 
+/* Full name on desktop, short name on the compact mobile pills. */
+.chipNameShort {
+  display: none;
+}
+
 .chipMeta {
   display: block;
   font-size: 0.78rem;
@@ -141,6 +148,54 @@
   text-overflow: ellipsis;
 }
 
+/* Caption filling the label rail on desktop ("Comparing / 3 primary
+   schools"). Hidden on mobile, where the bar is a row of compact pills. */
+.barCaption {
+  display: none;
+}
+
+/* Desktop (matches the sections' 761px breakpoint): the bar adopts the same
+   grid template as compareSections' .grid — a 200px row-label rail plus one
+   column per school — so each chip sits exactly over the column it labels.
+   The caption occupies the rail; chips flow into the school columns. */
+@media (min-width: 761px) {
+  .schoolBar {
+    display: grid;
+    grid-template-columns: 200px repeat(var(--school-count, 3), 1fr);
+    gap: 0 0.75rem;
+    overflow-x: visible;
+  }
+
+  .schoolChip {
+    min-width: 0;
+  }
+
+  .barCaption {
+    grid-column: 1;
+    display: flex;
+    flex-direction: column;
+    justify-content: center;
+    gap: 0.1rem;
+    padding-right: 0.5rem;
+    min-width: 0;
+  }
+
+  .barCaptionEyebrow {
+    font-size: 0.72rem;
+    font-weight: 600;
+    letter-spacing: 0.06em;
+    text-transform: uppercase;
+    color: var(--text-muted, #6d685f);
+  }
+
+  .barCaptionCount {
+    font-size: 0.95rem;
+    font-weight: 600;
+    line-height: 1.3;
+    color: var(--text-primary, #1a1612);
+  }
+}
+
 .chipRemove {
   margin-left: auto;
   border: none;
@@ -163,3 +218,45 @@
   padding-top: 1rem;
   max-width: 75ch;
 }
+
+/* Mobile: the sticky school bar becomes compact, horizontally-scrollable
+   pills with short names (matching the mobile mockup) instead of full-width
+   cards whose names wrap to several lines. */
+@media (max-width: 640px) {
+  /* The mobile Navigation header is shorter (≈57px). */
+  .schoolBar {
+    top: 57px;
+  }
+
+  .schoolChip {
+    flex: 0 0 auto;
+    min-width: 0;
+    border-top-width: 2px;
+    border-radius: 999px;
+    padding: 0.35rem 0.7rem;
+    box-shadow: none;
+  }
+
+  .chipName {
+    font-size: 0.85rem;
+    white-space: nowrap;
+  }
+
+  .chipNameFull {
+    display: none;
+  }
+
+  .chipNameShort {
+    display: inline;
+  }
+
+  .chipMeta {
+    display: none;
+  }
+
+  .chipRemove {
+    width: 18px;
+    height: 18px;
+    font-size: 0.75rem;
+  }
+}

nextjs-app/components/ComparisonView.tsx

@@ -9,7 +9,7 @@
 
 'use client';
 
-import { useEffect, useRef, useState } from 'react';
+import { useEffect, useRef, useState, type CSSProperties } from 'react';
 import { useRouter, usePathname, useSearchParams } from 'next/navigation';
 import { useComparison } from '@/hooks/useComparison';
 
@@ -28,7 +28,7 @@ import type {
   NationalAverages,
   School,
 } from '@/lib/types';
-import { CHART_COLORS, schoolUrl } from '@/lib/utils';
+import { CHART_COLORS, schoolUrl, shortName } from '@/lib/utils';
 import { fetchComparison } from '@/lib/api';
 import { track } from '@/lib/analytics';
 import styles from './ComparisonView.module.css';
@@ -85,7 +85,9 @@ export function ComparisonView({
         replaceSchools(urlSchools);
       }
     }
-  }, [isInitialized]); // eslint-disable-line react-hooks/exhaustive-deps
+    // Re-seed when a client-side navigation lands on a different ?urns= set
+    // (initialUrns/initialData are new props on the same component instance).
+  }, [isInitialized, initialUrns.join(',')]); // eslint-disable-line react-hooks/exhaustive-deps
 
   const urnKey = selectedSchools.map((s) => s.urn).join(',');
 
@@ -128,8 +130,18 @@ export function ComparisonView({
     const covered = urnKey.split(',').every((urn) => have[urn] != null);
     if (covered) return;
 
+    // Guard against out-of-order responses: while the basket hydrates from
+    // localStorage it can transiently hold a DIFFERENT school set than the
+    // URL, firing a fetch for schools the user is no longer comparing. That
+    // stale response must not replace data for the current set — it blanked
+    // every section until a hard refresh. Cleanup marks the run cancelled
+    // when urnKey moves on, so only the current selection's response is
+    // applied (replacing the map keeps it bounded and guarantees a re-added
+    // school is refetched fresh rather than served a lingering old entry).
+    let cancelled = false;
     fetchComparison(urnKey, { cache: 'no-store' })
       .then((data) => {
+        if (cancelled) return;
         setComparisonData(data.comparison);
         setNationalAverages(data.national_averages);
         setBenchmarks(data.benchmarks);
@@ -140,21 +152,28 @@ export function ComparisonView({
         // destroy a working comparison the user is looking at.
         console.error('Failed to fetch comparison:', err);
       });
+    return () => {
+      cancelled = true;
+    };
   }, [urnKey, isInitialized]);
 
-  // Classify schools by phase using comparison data
-  const classifySchool = (school: School): 'primary' | 'secondary' => {
+  const primarySchools = selectedSchools.filter((school) => {
     const info = comparisonData?.[school.urn]?.school_info;
-    if (info?.attainment_8_score != null) return 'secondary';
-    if (info?.rwm_expected_pct != null) return 'primary';
-    // Fallback: check yearly data
-    const yearlyData = comparisonData?.[school.urn]?.yearly_data;
-    if (yearlyData?.some((d) => d.attainment_8_score != null)) return 'secondary';
-    return 'primary';
-  };
+    const hasPrimaryData =
+      info?.rwm_expected_pct != null ||
+      comparisonData?.[school.urn]?.yearly_data?.some((d) => d.rwm_expected_pct != null);
+    if (hasPrimaryData) return true;
+    return school.phase?.toLowerCase().includes('primary') || false;
+  });
 
-  const primarySchools = selectedSchools.filter((s) => classifySchool(s) === 'primary');
-  const secondarySchools = selectedSchools.filter((s) => classifySchool(s) === 'secondary');
+  const secondarySchools = selectedSchools.filter((school) => {
+    const info = comparisonData?.[school.urn]?.school_info;
+    const hasSecondaryData =
+      info?.attainment_8_score != null ||
+      comparisonData?.[school.urn]?.yearly_data?.some((d) => d.attainment_8_score != null);
+    if (hasSecondaryData) return true;
+    return school.phase?.toLowerCase().includes('secondary') || false;
+  });
 
   // Auto-select tab with more schools and sync the metric to match the phase.
   useEffect(() => {
@@ -169,7 +188,11 @@ export function ComparisonView({
     if (!metricFitsPhase) {
       setSelectedMetric(newPhase === 'secondary' ? 'attainment_8_score' : 'rwm_expected_pct');
     }
-  }, [comparisonData]); // eslint-disable-line react-hooks/exhaustive-deps
+    // selectedSchools is a dep because the basket hydrates after mount: the
+    // first run sees an empty basket and bails, so it must re-fire when the
+    // schools arrive. primarySchools/secondarySchools/metrics/selectedMetric
+    // are intentionally omitted (derived or would cause loops).
+  }, [comparisonData, selectedSchools]); // eslint-disable-line react-hooks/exhaustive-deps
 
   const handlePhaseChange = (phase: 'primary' | 'secondary') => {
     phaseLockedByUser.current = true;
@@ -334,8 +357,22 @@ export function ComparisonView({
         />
       ) : (
         <>
-          {/* Sticky school bar — column identity while scrolling */}
-          <div className={styles.schoolBar} aria-label="Schools in this comparison">
+          {/* Sticky school bar — column identity while scrolling. On desktop
+              it shares the sections' grid template (via --school-count) so
+              each chip sits exactly over the column it labels. */}
+          <div
+            className={styles.schoolBar}
+            style={{ '--school-count': activeSchools.length } as CSSProperties}
+            aria-label="Schools in this comparison"
+          >
+            {/* Fills the 200px label rail on desktop (hidden on mobile). */}
+            <div className={styles.barCaption}>
+              <span className={styles.barCaptionEyebrow}>Comparing</span>
+              <span className={styles.barCaptionCount}>
+                {activeSchools.length} {comparePhase} school
+                {activeSchools.length === 1 ? '' : 's'}
+              </span>
+            </div>
             {activeSchools.map((school, index) => (
               <div
                 key={school.urn}
@@ -349,7 +386,8 @@ export function ComparisonView({
                 />
                 <span className={styles.chipText}>
                   <a className={styles.chipName} href={schoolUrl(school.urn, school.school_name)}>
-                    {school.school_name}
+                    <span className={styles.chipNameFull}>{school.school_name}</span>
+                    <span className={styles.chipNameShort}>{shortName(school.school_name)}</span>
                   </a>
                   <span className={styles.chipMeta}>
                     {[school.local_authority, school.school_type].filter(Boolean).join(' · ')}
@@ -374,6 +412,7 @@ export function ComparisonView({
                 data={activeComparisonData}
                 nationalAverages={nationalAverages}
                 benchmarks={benchmarks}
+                isSecondary={!isPrimary}
               />
               <CompareOfsted schools={activeSchools} data={activeComparisonData} />
               <CompareAcademics
@@ -381,12 +420,18 @@ export function ComparisonView({
                 data={activeComparisonData}
                 nationalAverages={nationalAverages}
                 benchmarks={benchmarks}
+                isSecondary={!isPrimary}
+              />
+              <CompareAdmissions
+                schools={activeSchools}
+                data={activeComparisonData}
+                isSecondary={!isPrimary}
               />
-              <CompareAdmissions schools={activeSchools} data={activeComparisonData} />
               <CompareCommunity
                 schools={activeSchools}
                 data={activeComparisonData}
                 benchmarks={benchmarks}
+                isSecondary={!isPrimary}
               />
               <TrendsExplorer
                 schools={activeSchools}

nextjs-app/components/compare/CompareAcademics.tsx

@@ -125,15 +125,17 @@ export function CompareAcademics({
   data,
   nationalAverages,
   benchmarks,
+  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   nationalAverages?: NationalAverages;
   benchmarks?: Benchmarks;
+  isSecondary?: boolean;
 }) {
   const urns = schools.map((school) => school.urn);
   const schoolNames = schools.map((school) => school.school_name);
-  const isSecondary = schools.some(
+  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
 

nextjs-app/components/compare/CompareAdmissions.tsx

@@ -7,19 +7,25 @@
 
 'use client';
 
-import { summariseAdmissions } from '@/lib/compareLogic';
+import { admissionsForPhase, summariseAdmissions } from '@/lib/compareLogic';
 import type { ComparisonData, School } from '@/lib/types';
 import { CHART_COLORS } from '@/lib/utils';
-import { Cell, Chip, RowLabel, Section, SectionGrid, sectionStyles as s } from './sectionShared';
+import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';
 
 export function CompareAdmissions({
   schools,
   data,
+  isSecondary = false,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
+  isSecondary?: boolean;
 }) {
-  const rows = schools.map((school) => data[String(school.urn)]?.admissions ?? null);
+  // Admissions rounds are phase-specific: an all-through school's Year 7
+  // round must never stand in for Reception on the primary tab (and vice
+  // versa) — beside pure primaries it reads as Reception odds.
+  const rows = schools.map((school) => admissionsForPhase(data[String(school.urn)], isSecondary));
+  const roundLabel = isSecondary ? 'Year 7' : 'Reception';
   const anyData = rows.some(Boolean);
   const entryYear = rows.find(Boolean)?.year;
   const entryLabel = entryYear
@@ -28,7 +34,10 @@ export function CompareAdmissions({
 
   if (!anyData) {
     return (
-      <Section title="Getting a place" how="No admissions data is available for these schools yet.">
+      <Section
+        title="Getting a place"
+        how={`No ${roundLabel} admissions data is available for these schools yet.`}
+      >
         <></>
       </Section>
     );
@@ -49,9 +58,10 @@ export function CompareAdmissions({
       }
     >
       <SectionGrid schools={schools}>
-        <RowLabel tip="How many application forms named the school at any preference rank — not the number of families competing head-to-head for a place.">
-          Interest in the school
-        </RowLabel>
+        <Measure
+          tip="How many application forms named the school at any preference rank — not the number of families competing head-to-head for a place."
+          label="Interest in the school"
+        >
         {schools.map((school, i) => {
           const a = rows[i];
           return (
@@ -62,13 +72,17 @@ export function CompareAdmissions({
                   <strong>{a.places_offered.toLocaleString('en-GB')}</strong> places
                 </>
               ) : (
-                <span className={s.small}>No data</span>
+                <span className={s.small}>
+                  We don&apos;t hold {roundLabel} admissions data for this school
+                </span>
               )}
             </Cell>
           );
         })}
 
-        <RowLabel>First-choice families offered a place</RowLabel>
+        </Measure>
+
+        <Measure label="First-choice families offered a place">
         {schools.map((school, i) => {
           const summary = summariseAdmissions(rows[i]);
           return (
@@ -95,19 +109,34 @@ export function CompareAdmissions({
           );
         })}
 
-        <RowLabel>What this means</RowLabel>
+        </Measure>
+
+        <Measure label="What this means">
         {schools.map((school, i) => {
           const a = rows[i];
           const summary = summariseAdmissions(a);
+          const info = data[String(school.urn)]?.school_info;
+          const selective = (info?.admissions_policy ?? '').toLowerCase() === 'selective';
+          const faith =
+            !!info?.religious_denomination &&
+            !/^(none|does not apply|not applicable)$/i.test(info.religious_denomination);
           let text: string | null = null;
           if (summary.firstPrefPct != null) {
-            if (summary.firstPrefPct >= 100) {
+            if (selective) {
+              // Selective schools: the entrance test decides, whatever the
+              // offer percentage looks like — never the distance template.
+              text =
+                'Entry is by entrance test — the school is selective; distance and preference rank don’t decide places.';
+            } else if (summary.firstPrefPct >= 100) {
               text = `Every family who put ${school.school_name} first got a place.`;
             } else if (summary.firstPrefPct >= 90) {
               text = `Nearly every family who put ${school.school_name} first got a place.`;
             } else if (a?.oversubscribed) {
-              text =
-                'More first-choice applications than places — check the school’s admission criteria (for most non-faith primaries, distance decides).';
+              text = isSecondary
+                ? faith
+                  ? 'More first-choice applications than places — check the school’s admission criteria (faith-based criteria may apply).'
+                  : 'More first-choice applications than places — check the school’s admission criteria (catchment or distance often decides, but criteria vary).'
+                : 'More first-choice applications than places — check the school’s admission criteria (for most non-faith primaries, distance decides).';
             } else {
               text = `${summary.firstPrefPct}% of first-choice families received an offer.`;
             }
@@ -118,6 +147,7 @@ export function CompareAdmissions({
             </Cell>
           );
         })}
+        </Measure>
       </SectionGrid>
     </Section>
   );

nextjs-app/components/compare/CompareAtAGlance.tsx

@@ -8,6 +8,7 @@
 'use client';
 
 import {
+  admissionsForPhase,
   latestValues,
   ofstedDisplay,
   summariseAdmissions,
@@ -15,7 +16,7 @@ import {
   type ReportCardSummary,
 } from '@/lib/compareLogic';
 import type { Benchmarks, ComparisonData, NationalAverages, School } from '@/lib/types';
-import { Cell, Chip, RowLabel, Section, SectionGrid, sectionStyles as s } from './sectionShared';
+import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';
 
 function ReportCardChips({ summary }: { summary: ReportCardSummary }) {
   return (
@@ -47,14 +48,16 @@ export function CompareAtAGlance({
   data,
   nationalAverages,
   benchmarks,
+  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   nationalAverages?: NationalAverages;
   benchmarks?: Benchmarks;
+  isSecondary?: boolean;
 }) {
   const urns = schools.map((school) => school.urn);
-  const isSecondary = schools.some(
+  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
   const headlineKey = isSecondary ? 'attainment_8_score' : 'rwm_expected_pct';
@@ -69,7 +72,7 @@ export function CompareAtAGlance({
   return (
     <Section title="At a glance" how="The short version — each row below is explained in its own section further down.">
       <SectionGrid schools={schools}>
-        <RowLabel>Latest Ofsted inspection</RowLabel>
+        <Measure label="Latest Ofsted inspection">
         {schools.map((school, i) => {
           const display = ofstedDisplay(data[String(school.urn)]?.ofsted);
           return (
@@ -83,20 +86,28 @@ export function CompareAtAGlance({
                   {display.carriedForward && <span className={s.small}>Grade carried forward</span>}
                 </>
               )}
+              {display.kind === 'transitional' && (
+                <>
+                  <span className={s.badge} style={{ backgroundColor: '#e2e8f0', color: '#475569' }}>
+                    No overall grade
+                  </span>
+                  <span className={s.small}>Sub-judgements only</span>
+                </>
+              )}
               {display.kind === 'none' && <span className={s.small}>No inspection in our dataset</span>}
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel
+        <Measure
           tip={
             isSecondary
               ? 'Average Attainment 8 score across GCSE subjects (latest year).'
               : '% of Year 6 pupils reaching the expected standard in reading, writing and maths (latest year).'
           }
+          label={isSecondary ? 'Attainment 8 score' : 'Children reaching the expected standard'}
         >
-          {isSecondary ? 'Attainment 8 score' : 'Children reaching the expected standard'}
-        </RowLabel>
         {schools.map((school, i) => {
           const value = headlineValues[i];
           return (
@@ -131,10 +142,15 @@ export function CompareAtAGlance({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Getting a place</RowLabel>
+        <Measure label="Getting a place">
         {schools.map((school, i) => {
-          const summary = summariseAdmissions(data[String(school.urn)]?.admissions);
+          // Phase-matched round only — an all-through school's Year 7 round
+          // must not masquerade as Reception odds on the primary tab.
+          const summary = summariseAdmissions(
+            admissionsForPhase(data[String(school.urn)], isSecondary),
+          );
           return (
             <Cell key={school.urn} school={school} index={i}>
               {summary.chip ? (
@@ -148,8 +164,9 @@ export function CompareAtAGlance({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Size</RowLabel>
+        <Measure label="Size">
         {schools.map((school, i) => {
           const census = data[String(school.urn)]?.census;
           const pupils = census?.total_pupils ?? school.total_pupils ?? null;
@@ -174,6 +191,7 @@ export function CompareAtAGlance({
             </Cell>
           );
         })}
+        </Measure>
       </SectionGrid>
     </Section>
   );

nextjs-app/components/compare/CompareCommunity.tsx

@@ -9,7 +9,7 @@
 
 import { verdict } from '@/lib/compareLogic';
 import type { Benchmarks, ComparisonData, School } from '@/lib/types';
-import { Cell, Chip, RowLabel, Section, SectionGrid, sectionStyles as s } from './sectionShared';
+import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';
 
 function pctSplit(part: number | null | undefined, total: number | null | undefined): string | null {
   if (part == null || total == null || total === 0) return null;
@@ -20,24 +20,30 @@ export function CompareCommunity({
   schools,
   data,
   benchmarks,
+  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   benchmarks?: Benchmarks;
+  isSecondary?: boolean;
 }) {
-  const isSecondary = schools.some(
+  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
   const bench = isSecondary ? benchmarks?.secondary : benchmarks?.primary;
 
   const fsmChip = (value: number | null) => {
-    if (value == null || bench?.disadvantaged_pct == null) return null;
-    const v = verdict(value, bench.disadvantaged_pct, 3);
+    // FSM is anchored only against a real FSM benchmark (census-sourced,
+    // pupil-weighted). disadvantaged_pct is a different measure (FSM6+CLA)
+    // — never fall back across definitions; no anchor means no chip.
+    const anchor = bench?.fsm_pct ?? null;
+    if (value == null || anchor == null) return null;
+    const v = verdict(value, anchor, 3);
     return (
       <Chip tone="neutral">
-        {v === 'above' && 'Above the state-school average'}
-        {v === 'close' && 'About the state-school average'}
-        {v === 'below' && 'Below the state-school average'}
+        {v === 'above' && `Above the state-school average (${Math.round(anchor)}%)`}
+        {v === 'close' && `About the state-school average (${Math.round(anchor)}%)`}
+        {v === 'below' && `Below the state-school average (${Math.round(anchor)}%)`}
       </Chip>
     );
   };
@@ -48,7 +54,7 @@ export function CompareCommunity({
       how="The school's community, from the latest school census. State-school averages are computed from our dataset and shown for context — there's no “right” number here."
     >
       <SectionGrid schools={schools}>
-        <RowLabel>Pupils on roll</RowLabel>
+        <Measure label="Pupils on roll">
         {schools.map((school, i) => {
           const info = data[String(school.urn)]?.school_info as (School & { gias_total_pupils?: number | null; capacity?: number | null }) | undefined;
           const census = data[String(school.urn)]?.census;
@@ -74,8 +80,9 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Girls / boys</RowLabel>
+        <Measure label="Girls / boys">
         {schools.map((school, i) => {
           const census = data[String(school.urn)]?.census;
           const girls = pctSplit(census?.female_pupils, census?.total_pupils);
@@ -86,10 +93,12 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel tip="% of pupils eligible for free school meals — a common measure of how many pupils come from lower-income families. Benchmark computed across state schools in our dataset.">
-          Free school meals
-        </RowLabel>
+        <Measure
+          tip="% of pupils eligible for free school meals — a common measure of how many pupils come from lower-income families. Benchmark computed across state schools in our dataset."
+          label="Free school meals"
+        >
         {schools.map((school, i) => {
           const fsm = data[String(school.urn)]?.census?.fsm_pct ?? null;
           return (
@@ -104,10 +113,12 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel tip="% of pupils whose first language is known or believed to be other than English. State-school average computed from our dataset.">
-          English as an additional language
-        </RowLabel>
+        <Measure
+          tip="% of pupils whose first language is known or believed to be other than English. State-school average computed from our dataset."
+          label="English as an additional language"
+        >
         {schools.map((school, i) => {
           const eal = data[String(school.urn)]?.census?.eal_pct ?? null;
           return (
@@ -116,10 +127,12 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel tip="% of pupils receiving SEN support (not including EHC plans). A high figure can mean the school hosts specialist provision — often a strength, not a warning sign. State-school average computed from our dataset.">
-          Extra learning support (SEN)
-        </RowLabel>
+        <Measure
+          tip="% of pupils receiving SEN support (not including EHC plans). A high figure can mean the school hosts specialist provision — often a strength, not a warning sign. State-school average computed from our dataset."
+          label="Extra learning support (SEN)"
+        >
         {schools.map((school, i) => {
           const rows = data[String(school.urn)]?.yearly_data ?? [];
           let sen: number | null = null;
@@ -143,8 +156,9 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Faith character</RowLabel>
+        <Measure label="Faith character">
         {schools.map((school, i) => {
           const info = data[String(school.urn)]?.school_info;
           const faith = info?.religious_denomination;
@@ -155,8 +169,9 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Ages</RowLabel>
+        <Measure label="Ages">
         {schools.map((school, i) => {
           const info = data[String(school.urn)]?.school_info;
           return (
@@ -165,8 +180,9 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
 
-        <RowLabel>Run by</RowLabel>
+        <Measure label="Run by">
         {schools.map((school, i) => {
           const info = data[String(school.urn)]?.school_info;
           const trust = info?.trust_name;
@@ -177,6 +193,7 @@ export function CompareCommunity({
             </Cell>
           );
         })}
+        </Measure>
       </SectionGrid>
     </Section>
   );

nextjs-app/components/compare/CompareOfsted.tsx

@@ -13,7 +13,7 @@ import {
   type OfstedDisplay,
 } from '@/lib/compareLogic';
 import type { ComparisonData, OfstedInspection, School } from '@/lib/types';
-import { Cell, Chip, RowLabel, Section, SectionGrid, sectionStyles as s } from './sectionShared';
+import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';
 
 const GRADE_TONE: Record<number, 'good' | 'warn' | 'bad'> = {
   1: 'good',
@@ -51,6 +51,18 @@ function ResultCell({ display }: { display: OfstedDisplay }) {
       </>
     );
   }
+  if (display.kind === 'transitional') {
+    return (
+      <>
+        <span className={s.badge} style={{ backgroundColor: '#e2e8f0', color: '#475569' }}>
+          No overall grade
+        </span>
+        <span className={s.small}>
+          Inspected under transitional framework (sub-judgements only)
+        </span>
+      </>
+    );
+  }
   return (
     <>
       <span className={`${s.badge} ${display.grade <= 2 ? s.badgeGood : display.grade === 3 ? s.badgeWarn : s.badgeBad}`}>
@@ -96,14 +108,21 @@ function JudgementDetailCell({
     );
   }
 
-  const legacyAreas: Array<[string, number | null]> = [
+  const legacyAreas: Array<[string, number | null | undefined]> = [
     ['Quality of education', ofsted.quality_of_education],
     ['Behaviour & attitudes', ofsted.behaviour_attitudes],
     ['Personal development', ofsted.personal_development],
     ['Leadership & management', ofsted.leadership_management],
     ['Early years provision', ofsted.early_years_provision],
+    ['Sixth form provision', ofsted.sixth_form_provision],
   ];
-  const published = legacyAreas.filter(([, grade]) => grade != null);
+  // Only real Ofsted grades (1–4) are judgements. The MI file uses sentinel
+  // codes for "not applicable / no judgement" (9, and 0/8 variants) — those
+  // must never render as a rating chip.
+  const published = legacyAreas.filter(
+    (entry): entry is [string, number] =>
+      entry[1] != null && entry[1] >= 1 && entry[1] <= 4,
+  );
 
   if (published.length === 0) {
     return (
@@ -157,28 +176,40 @@ export function CompareOfsted({
       }
     >
       <SectionGrid schools={schools}>
-        <RowLabel>Result</RowLabel>
+        <Measure label="Result">
         {schools.map((school, i) => (
           <Cell key={school.urn} school={school} index={i}>
             <ResultCell display={displays[i]} />
           </Cell>
         ))}
+        </Measure>
 
-        <RowLabel>Inspected</RowLabel>
+        <Measure label="Inspected">
         {schools.map((school, i) => {
           const ofsted = data[String(school.urn)]?.ofsted;
-          const age = yearsSince(ofsted?.inspection_date ?? null);
+          // A report card is dated by its OWN inspection date. The legacy
+          // inspection_date belongs to an older inspection and must never
+          // be shown against a report card (report cards exist only from
+          // Nov 2025).
+          const dateIso =
+            displays[i].kind === 'report_card'
+              ? ofsted?.rc_inspection_date ?? null
+              : ofsted?.inspection_date ?? null;
+          const age = yearsSince(dateIso);
           return (
             <Cell key={school.urn} school={school} index={i}>
-              {formatInspectionDate(ofsted?.inspection_date ?? null)}{' '}
+              {formatInspectionDate(dateIso)}{' '}
               {age != null && age > 4 && <Chip tone="neutral">4+ years ago</Chip>}
             </Cell>
           );
         })}
 
-        <RowLabel tip="Older-style inspections: one rating per judgement area, where published. New-style inspections: the full report card, one rating per area of school life.">
-          Judgement detail
-        </RowLabel>
+        </Measure>
+
+        <Measure
+          tip="Older-style inspections: one rating per judgement area, where published. New-style inspections: the full report card, one rating per area of school life."
+          label="Judgement detail"
+        >
         {schools.map((school, i) => {
           const ofsted = data[String(school.urn)]?.ofsted;
           return (
@@ -196,9 +227,12 @@ export function CompareOfsted({
           );
         })}
 
-        <RowLabel tip="Links to the school's page on ofsted.gov.uk, where all its inspection reports are listed.">
-          Ofsted page
-        </RowLabel>
+        </Measure>
+
+        <Measure
+          tip="Links to the school's page on ofsted.gov.uk, where all its inspection reports are listed."
+          label="Ofsted page"
+        >
         {schools.map((school, i) => {
           const url =
             data[String(school.urn)]?.ofsted?.ofsted_page_url ??
@@ -211,6 +245,7 @@ export function CompareOfsted({
             </Cell>
           );
         })}
+        </Measure>
       </SectionGrid>
     </Section>
   );

nextjs-app/components/compare/TrendsExplorer.module.css

@@ -60,37 +60,18 @@
   margin: 0 0 1rem;
 }
 
+/* ComparisonChart runs Chart.js with maintainAspectRatio:false, so it fills
+   its container's height — which must be *definite*. A min-height alone does
+   not resolve the chart wrapper's height:100%, leaving Chart.js to fall back
+   to its ~150px default (a squashed sliver). Give it a real height. */
 .chartBox {
-  min-height: 320px;
+  height: 420px;
 }
 
-.tableWrapper {
-  overflow-x: auto;
-  margin-top: 1.5rem;
-}
-
-.table {
-  width: 100%;
-  border-collapse: collapse;
-  font-size: 0.9rem;
-}
-
-.table th,
-.table td {
-  text-align: left;
-  padding: 0.6rem 0.75rem;
-  border-bottom: 1px solid var(--border-light);
-}
-
-.table th {
-  background: var(--bg-secondary);
-  font-size: 0.8rem;
-  text-transform: uppercase;
-  letter-spacing: 0.03em;
-  color: var(--text-secondary);
-}
-
-.yearCell {
-  font-weight: 600;
-  white-space: nowrap;
+@media (max-width: 640px) {
+  /* Taller on mobile: the mobile-only school chips sit above the canvas and
+     wrap to two rows for 3+ schools, so the plot keeps a usable height. */
+  .chartBox {
+    height: 360px;
+  }
 }

nextjs-app/components/compare/TrendsExplorer.tsx

@@ -1,19 +1,17 @@
 /**
  * Explore trends — the full grouped metric catalogue (nothing from the old
- * compare page is lost; spec §4's tier 3) driving the year-by-year chart
- * with its England reference line, plus the year-by-year table. Progress
- * metrics carry CI-based bands for the years DfE published them.
+ * compare page is lost; spec §4's tier 3) driving the year-by-year chart with
+ * its England reference line. Matches the mockup: a measure picker and the
+ * chart only (no data table).
  */
 
 'use client';
 
 import dynamic from 'next/dynamic';
 
-import { progressBand } from '@/lib/compareLogic';
 import type { ComparisonData, MetricDefinition, NationalAverages, School } from '@/lib/types';
-import { formatAcademicYear, formatMetricValue, metricKind } from '@/lib/utils';
 import { track } from '@/lib/analytics';
-import { Chip, Section, sectionStyles as s } from './sectionShared';
+import { Section } from './sectionShared';
 import styles from './TrendsExplorer.module.css';
 
 const ComparisonChart = dynamic(
@@ -40,14 +38,6 @@ const SECONDARY_OPTGROUPS: { label: string; category: string }[] = [
 export const PRIMARY_CATEGORIES = PRIMARY_OPTGROUPS.map((g) => g.category);
 export const SECONDARY_CATEGORIES = SECONDARY_OPTGROUPS.map((g) => g.category);
 
-const PROGRESS_CI: Record<string, [string, string]> = {
-  reading_progress: ['reading_progress_lower_ci', 'reading_progress_upper_ci'],
-  writing_progress: ['writing_progress_lower_ci', 'writing_progress_upper_ci'],
-  maths_progress: ['maths_progress_lower_ci', 'maths_progress_upper_ci'],
-};
-
-const BAND_LABEL = { above: 'Above average', average: 'Average', below: 'Below average' } as const;
-
 export function TrendsExplorer({
   schools,
   data,
@@ -78,21 +68,11 @@ export function TrendsExplorer({
     nationalByYear[entry.year] = block?.[metric] ?? null;
   }
 
-  const years = [
-    ...new Set(
-      schools.flatMap(
-        (school) => data[String(school.urn)]?.yearly_data.map((d) => Math.trunc(d.year)) ?? [],
-      ),
-    ),
-  ].sort((a, b) => a - b);
-
   const handleMetricChange = (next: string) => {
     track('compare_metric_changed', { metric: next, phase: isPrimaryPhase ? 'primary' : 'secondary' });
     onMetricChange(next);
   };
 
-  const ciKeys = PROGRESS_CI[metric];
-
   return (
     <Section
       title="Explore trends"
@@ -128,8 +108,7 @@ export function TrendsExplorer({
           {metric.includes('progress') && (
             <p className={styles.progressNote}>
               Progress scores measure pupils&apos; progress from KS1 to KS2. A score of 0 equals the
-              national average. DfE stopped publishing KS2 progress after 2022/23 (no KS1 baseline);
-              bands use DfE&apos;s confidence intervals, not the raw score alone.
+              national average. DfE stopped publishing KS2 progress after 2022/23 (no KS1 baseline).
             </p>
           )}
 
@@ -142,52 +121,6 @@ export function TrendsExplorer({
               nationalByYear={nationalByYear}
             />
           </div>
-
-          {years.length > 0 && (
-            <div className={styles.tableWrapper}>
-              <table className={styles.table}>
-                <thead>
-                  <tr>
-                    <th>Year</th>
-                    {schools.map((school) => (
-                      <th key={school.urn}>{school.school_name}</th>
-                    ))}
-                  </tr>
-                </thead>
-                <tbody>
-                  {years.map((year) => (
-                    <tr key={year}>
-                      <td className={styles.yearCell}>{formatAcademicYear(year)}</td>
-                      {schools.map((school) => {
-                        const row = data[String(school.urn)]?.yearly_data.find(
-                          (d) => Math.trunc(d.year) === year,
-                        ) as (Record<string, unknown> & { year: number }) | undefined;
-                        const value = row?.[metric];
-                        if (typeof value !== 'number') return <td key={school.urn}>–</td>;
-                        const band = ciKeys
-                          ? progressBand(
-                              value,
-                              (row?.[ciKeys[0]] as number | null) ?? null,
-                              (row?.[ciKeys[1]] as number | null) ?? null,
-                            )
-                          : null;
-                        return (
-                          <td key={school.urn}>
-                            {formatMetricValue(value, metricKind(metric))}{' '}
-                            {band && (
-                              <Chip tone={band === 'above' ? 'good' : band === 'below' ? 'warn' : 'neutral'}>
-                                {BAND_LABEL[band]}
-                              </Chip>
-                            )}
-                          </td>
-                        );
-                      })}
-                    </tr>
-                  ))}
-                </tbody>
-              </table>
-            </div>
-          )}
         </div>
       </details>
     </Section>

nextjs-app/components/compare/compareSections.module.css

@@ -31,43 +31,73 @@
   margin-top: 1.25rem;
 }
 
+/* Mobile base: each measure is a card; each cell is a school row led by a
+   colour dot + short name. `display: contents` at ≥761px dissolves the card
+   back into the shared grid. */
+.measure {
+  background: var(--bg-card);
+  border: 1px solid var(--border-light);
+  border-radius: 12px;
+  box-shadow: var(--shadow-soft);
+  padding: 0.75rem 0.85rem;
+  margin-bottom: 0.6rem;
+}
+
 .rowLabel {
   font-size: 0.85rem;
   font-weight: 600;
-  color: var(--text-secondary);
+  color: var(--text-primary);
   display: flex;
   align-items: center;
   gap: 0.35rem;
-  background: var(--bg-secondary);
-  border-radius: 6px;
-  padding: 0.4rem 0.6rem;
-  margin-top: 0.8rem;
+  padding: 0 0 0.1rem;
 }
 
 .cell {
-  padding: 0.4rem 0.6rem;
+  display: flex;
+  align-items: baseline;
+  gap: 0.35rem 0.5rem;
+  flex-wrap: wrap;
+  padding: 0.5rem 0;
+  border-top: 1px solid var(--border-light);
+  margin-top: 0.5rem;
   font-size: 0.95rem;
 }
 
-.cell::before {
-  content: attr(data-school);
-  display: block;
-  font-size: 0.72rem;
+/* The school name gets its own full-width line above the value — real
+   school names are long and varied, so a fixed-width name column truncated
+   them ("Our Lady Queen of H…") or crowded the value. */
+.cellTag {
+  display: inline-flex;
+  align-items: center;
+  gap: 0.4rem;
+  flex-basis: 100%;
+  font-size: 0.8rem;
   font-weight: 600;
-  color: var(--sc, var(--text-muted));
+  color: var(--sc, var(--text-secondary));
+  margin-bottom: 0.15rem;
+}
+
+.cellDot {
+  width: 9px;
+  height: 9px;
+  border-radius: 50%;
+  background: var(--dot, var(--text-muted));
+  flex: none;
 }
 
 .big {
-  font-size: 1.35rem;
+  font-size: 1.05rem;
   font-weight: 700;
   font-variant-numeric: tabular-nums;
 }
 
 .small {
   display: block;
+  flex-basis: 100%;
   font-size: 0.8rem;
   color: var(--text-muted);
-  margin-top: 0.1rem;
+  margin-top: 0;
 }
 
 .chip {
@@ -159,6 +189,7 @@
   display: flex;
   gap: 0.3rem;
   flex-wrap: wrap;
+  flex-basis: 100%;
   margin-top: 0.3rem;
 }
 
@@ -197,20 +228,37 @@
     gap: 0 0.75rem;
   }
 
+  /* Dissolve the per-measure card so its label + cells become grid items of
+     .grid, keeping columns aligned across every measure. */
+  .measure {
+    display: contents;
+  }
+
+  .cellTag {
+    display: none;
+  }
+
   .rowLabel {
-    background: none;
-    border-radius: 0;
-    margin-top: 0;
+    color: var(--text-secondary);
     padding: 0.85rem 0.5rem 0.85rem 0;
     border-bottom: 1px solid var(--border-light);
   }
 
   .cell {
+    display: block;
     padding: 0.85rem 0.25rem;
+    border-top: none;
     border-bottom: 1px solid var(--border-light);
+    margin-top: 0;
   }
 
-  .cell::before {
-    content: none;
+  .big {
+    font-size: 1.35rem;
+  }
+
+  .small {
+    flex-basis: auto;
+    padding-left: 0;
+    margin-top: 0.1rem;
   }
 }

nextjs-app/components/compare/sectionShared.tsx

@@ -10,7 +10,7 @@
 import type { CSSProperties, ReactNode } from 'react';
 
 import type { School } from '@/lib/types';
-import { CHART_TEXT_COLORS } from '@/lib/utils';
+import { CHART_COLORS, CHART_TEXT_COLORS, shortName } from '@/lib/utils';
 import styles from './compareSections.module.css';
 
 export function Section({
@@ -61,6 +61,29 @@ export function RowLabel({ children, tip }: { children: ReactNode; tip?: string
   );
 }
 
+/**
+ * One measure = its row label plus a cell per school. `display: contents` on
+ * desktop (see CSS) makes these flow into the section grid as if this wrapper
+ * weren't here, keeping columns aligned across measures; on mobile the wrapper
+ * becomes a card so each measure reads as its own block.
+ */
+export function Measure({
+  label,
+  tip,
+  children,
+}: {
+  label: ReactNode;
+  tip?: string;
+  children: ReactNode;
+}) {
+  return (
+    <div className={styles.measure}>
+      <RowLabel tip={tip}>{label}</RowLabel>
+      {children}
+    </div>
+  );
+}
+
 export function Cell({
   school,
   index,
@@ -73,9 +96,19 @@ export function Cell({
   return (
     <div
       className={styles.cell}
-      data-school={school.school_name}
-      style={{ '--sc': CHART_TEXT_COLORS[index % CHART_TEXT_COLORS.length] } as CSSProperties}
+      style={
+        {
+          '--sc': CHART_TEXT_COLORS[index % CHART_TEXT_COLORS.length],
+          '--dot': CHART_COLORS[index % CHART_COLORS.length],
+        } as CSSProperties
+      }
     >
+      {/* Mobile-only per-school tag (dot + short name); hidden on desktop,
+          where the column header identifies the school. */}
+      <span className={styles.cellTag}>
+        <span className={styles.cellDot} aria-hidden="true" />
+        {shortName(school.school_name)}
+      </span>
       {children}
     </div>
   );

nextjs-app/lib/compareLogic.ts

@@ -102,6 +102,7 @@ export type OfstedDisplay =
   | { kind: 'none' }
   | { kind: 'graded'; grade: number; gradeLabel: string; carriedForward: false }
   | { kind: 'carried_forward'; grade: number; gradeLabel: string; carriedForward: true }
+  | { kind: 'transitional' }
   | { kind: 'report_card'; summary: ReportCardSummary };
 
 export function ofstedDisplay(
@@ -117,7 +118,12 @@ export function ofstedDisplay(
 
   const grade = ofsted.overall_effectiveness;
   const gradeLabel = grade != null ? OFSTED_LEGACY_GRADES[grade] : undefined;
-  if (grade == null || gradeLabel === undefined) return { kind: 'none' };
+  if (grade == null || gradeLabel === undefined) {
+    if (ofsted.inspection_date) {
+      return { kind: 'transitional' };
+    }
+    return { kind: 'none' };
+  }
 
   if (ofsted.grade_source === 'ungraded_carried_forward') {
     return { kind: 'carried_forward', grade, gradeLabel, carriedForward: true };
@@ -137,6 +143,41 @@ export interface AdmissionsSummary {
   interest: string | null;
 }
 
+/**
+ * Pick the admissions round for the ACTIVE phase tab. An all-through school
+ * can carry only a Year 7 (Secondary) round — rendering that beside pure
+ * primaries' Reception rounds made 433-forms-for-173-places read as
+ * Reception odds. Rows matching the target phase win (latest year first);
+ * rows tagged with the OTHER phase are never substituted. Untagged rows
+ * (legacy data, no school_phase) are used only when no row carries a phase.
+ */
+export function admissionsForPhase(
+  data:
+    | { admissions?: SchoolAdmissions | null; admissions_history?: SchoolAdmissions[] }
+    | null
+    | undefined,
+  isSecondary: boolean,
+): SchoolAdmissions | null {
+  if (!data) return null;
+  const rows: SchoolAdmissions[] = [
+    ...(data.admissions_history ?? []),
+    ...(data.admissions ? [data.admissions] : []),
+  ];
+  if (rows.length === 0) return null;
+  const target = isSecondary ? 'secondary' : 'primary';
+  const byYearDesc = (a: SchoolAdmissions, b: SchoolAdmissions) => (b.year ?? 0) - (a.year ?? 0);
+
+  const matching = rows
+    .filter((r) => r.school_phase?.toLowerCase() === target)
+    .sort(byYearDesc);
+  if (matching.length > 0) return matching[0];
+
+  const tagged = rows.some((r) => r.school_phase != null);
+  if (!tagged) return [...rows].sort(byYearDesc)[0];
+
+  return null;
+}
+
 export function summariseAdmissions(
   a: SchoolAdmissions | null | undefined,
 ): AdmissionsSummary {

nextjs-app/lib/types.ts

@@ -79,12 +79,16 @@ export interface School {
 export interface OfstedInspection {
   framework: 'OEIF' | 'ReportCard' | null;
   inspection_date: string | null;
+  /** Start date of the report-card inspection itself (Nov 2025+); null otherwise. */
+  rc_inspection_date?: string | null;
   inspection_type: string | null;
   // OEIF fields (old framework, pre-Nov 2025)
   overall_effectiveness: 1 | 2 | 3 | 4 | null;
   quality_of_education: number | null;
   behaviour_attitudes: number | null;
   personal_development: number | null;
+  /** Sixth-form judgement where applicable; sentinel 9 = not applicable. */
+  sixth_form_provision?: number | null;
   leadership_management: number | null;
   early_years_provision: number | null;
   previous_overall: number | null;
@@ -357,6 +361,7 @@ export interface BenchmarkBlock {
   eal_pct: number | null;
   sen_support_pct: number | null;
   disadvantaged_pct: number | null;
+  fsm_pct?: number | null;
   median_pupils: number | null;
   /** Primary only — weighted by cohort size. */
   disadvantaged_rwm_expected_pct?: number | null;

nextjs-app/lib/utils.ts

@@ -59,6 +59,24 @@ export function truncate(text: string, maxLength: number): string {
   return text.slice(0, maxLength).trim() + '...';
 }
 
+/**
+ * A compact school label for tight spaces (mobile compare rows, chip bars):
+ * drop the trailing establishment-type words so "Barclay Primary School" →
+ * "Barclay", "St Mary's Catholic Primary School" → "St Mary's". Falls back to
+ * a length-capped truncation for names that don't carry a type suffix.
+ */
+export function shortName(name: string, maxLength = 32): string {
+  let s = name
+    .replace(
+      /\s+(primary|junior|infant|nursery|community|foundation|catholic|academy|school|college)\b.*$/i,
+      '',
+    )
+    .trim();
+  if (!s) s = name;
+  if (s.length > maxLength) s = s.slice(0, maxLength - 1).trim() + '…';
+  return s;
+}
+
 /**
  * Format a school's age range for display, e.g. "3-11" → "Ages 3–11".
  * Display-only — leaves the raw `age_range` field (used for sixth-form

pipeline/dags/school_data_pipeline.py

@@ -180,7 +180,7 @@ with DAG(
 
     dbt_build_ees = BashOperator(
         task_id="dbt_build",
-        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ees_ks2+ stg_legacy_ks2+ stg_ees_ks4+ stg_legacy_ks4+ stg_ees_census+ stg_ees_admissions+ stg_ees_ks2_national+",
+        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ees_ks2+ stg_legacy_ks2+ stg_ees_ks4+ stg_legacy_ks4+ stg_ees_census+ stg_ees_admissions+ stg_ees_ks2_national+ stg_ees_ks4_national+",
     )
 
     sync_typesense_ees = BashOperator(

pipeline/meltano.yml

@@ -49,6 +49,9 @@ plugins:
         - name: mi_url
           kind: string
           description: Ofsted Management Information download URL
+        - name: independent_mi_url
+          kind: string
+          description: Ofsted Independent Schools Management Information download URL
 
     - name: tap-uk-fbit
       namespace: uk_fbit

pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py

@@ -564,6 +564,74 @@ class EESKs2NationalStream(Stream):
             yield record
 
 
+# ── KS4 National Headlines (national level only — one row per year) ──────────
+# Dataset: "National characteristics summary data" (Key stage 4 performance).
+# Official England state-funded headline measures, 2018/19 → latest.
+# Suppressed values ('z', 'x') → NULL downstream. Progress 8 is legitimately
+# absent in years with no KS2 baseline (e.g. 2024/25) — that is DfE policy,
+# not missing data.
+
+_KS4_NATIONAL_CSV_URL = (
+    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
+    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
+)
+
+_KS4_NATIONAL_COL_MAP = {
+    "attainment8_average":    "attainment_8_score",
+    "progress8_average":      "progress_8_score",
+    "engmath_94_percent":     "english_maths_standard_pass_pct",
+    "engmath_95_percent":     "english_maths_strong_pass_pct",
+    "ebacc_entering_percent": "ebacc_entry_pct",
+    "ebacc_94_percent":       "ebacc_standard_pass_pct",
+    "ebacc_95_percent":       "ebacc_strong_pass_pct",
+    "ebacc_aps_average":      "ebacc_avg_score",
+}
+
+
+class EESKs4NationalStream(Stream):
+    """National KS4 headline averages — one row per academic year.
+
+    Filters to geographic_level == 'National', establishment_type_group ==
+    'All state-funded', breakdown_topic == 'Total', breakdown == 'Total'
+    so only the England-wide all-pupils row per year is emitted.
+    """
+
+    name = "ees_ks4_national"
+    primary_keys = ["time_period"]
+    replication_key = None
+
+    schema = th.PropertiesList(
+        th.Property("time_period", th.StringType, required=True),
+        *[th.Property(out, th.StringType) for out in _KS4_NATIONAL_COL_MAP.values()],
+    ).to_dict()
+
+    def get_records(self, context):
+        import pandas as pd
+
+        self.logger.info("Downloading KS4 national headlines: %s", _KS4_NATIONAL_CSV_URL)
+        resp = requests.get(_KS4_NATIONAL_CSV_URL, timeout=60)
+        resp.raise_for_status()
+
+        df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)
+        df.columns = [c.strip().lower() for c in df.columns]
+
+        for col, want in [
+            ("geographic_level", "national"),
+            ("establishment_type_group", "all state-funded"),
+            ("breakdown_topic", "total"),
+            ("breakdown", "total"),
+        ]:
+            if col in df.columns:
+                df = df[df[col].str.strip().str.lower() == want]
+
+        self.logger.info("Emitting %d national KS4 rows", len(df))
+        for _, row in df.iterrows():
+            record = {"time_period": row.get("time_period", "").strip()}
+            for csv_col, field in _KS4_NATIONAL_COL_MAP.items():
+                record[field] = row.get(csv_col, "").strip()
+            yield record
+
+
 # ── Legacy KS2 (pre-COVID wide format from DfE performance tables) ────────────
 # The DfE "Compare School Performance" site published school-level KS2 CSVs
 # in a wide format (one row per school, ~300 columns).  EES only has school-level
@@ -903,6 +971,7 @@ class TapUKEES(Tap):
             LegacyKS2Stream(self),
             LegacyKS4Stream(self),
             EESKs2NationalStream(self),
+            EESKs4NationalStream(self),
         ]
 
 

pipeline/plugins/extractors/tap-uk-ofsted/tap_uk_ofsted/tap.py

@@ -2,6 +2,7 @@
 
 from __future__ import annotations
 
+from datetime import datetime
 import io
 import re
 
@@ -14,20 +15,28 @@ GOV_UK_PAGE = (
     "monthly-management-information-ofsteds-school-inspections-outcomes"
 )
 
+INDEPENDENT_GOV_UK_PAGE = (
+    "https://www.gov.uk/government/statistical-data-sets/"
+    "non-association-independent-schools-inspections-and-outcomes-management-information"
+)
+
 # Column name → internal field, in priority order (first match wins).
 # Handles both current and older file formats.
 COLUMN_PRIORITY = {
     "urn": ["URN", "Urn", "urn"],
     "inspection_date": [
         "Inspection start date of latest OEIF graded inspection",
+        "Inspection start date of latest OEIF standard inspection",
         "Inspection start date",
         "Inspection date",
     ],
     "inspection_type": [
         "Inspection type of latest OEIF graded inspection",
+        "Inspection type of latest OEIF standard inspection",
         "Inspection type",
     ],
     "event_type_grouping": [
+        "Event type grouping of latest OEIF standard inspection",
         "Event type grouping",
         "Inspection type grouping",
     ],
@@ -52,10 +61,12 @@ COLUMN_PRIORITY = {
         "Effectiveness of leadership and management",
     ],
     "early_years_provision": [
+        "Latest OEIF early years provision (where applicable)",
         "Latest OEIF early years provision",
         "Early years provision (where applicable)",
     ],
     "sixth_form_provision": [
+        "Latest OEIF sixth form provision (where applicable)",
         "Latest OEIF sixth form provision",
         "Sixth form provision (where applicable)",
     ],
@@ -68,12 +79,7 @@ COLUMN_PRIORITY = {
     "ungraded_inspection_date": [
         "Date of latest ungraded inspection",
     ],
-    # Report Card fields (post-Nov 2025 framework). Confirmed verbatim MI
-    # headers per diagnose_compare_gaps.py's Task 1(c) findings. No MI column
-    # currently exists for early-years or sixth-form report-card grades, so
-    # those two fields are deliberately omitted here (see schema below) --
-    # they stay absent from every record, same as the existing `report_url`
-    # pattern for fields with no COLUMN_PRIORITY entry.
+    # Report Card fields (post-Nov 2025 framework).
     "rc_safeguarding_met": ["Safeguarding standards"],
     "rc_inclusion": ["Inclusion"],
     "rc_curriculum_teaching": ["Curriculum and teaching"],
@@ -81,20 +87,59 @@ COLUMN_PRIORITY = {
     "rc_attendance_behaviour": ["Attendance and behaviour"],
     "rc_personal_development": ["Personal development and wellbeing"],
     "rc_leadership_governance": ["Leadership and governance"],
+    "rc_early_years": ["Early years (where applicable)"],
+    "rc_sixth_form": ["Post-16 provision (where applicable)"],
+    # Date of the latest FULL inspection — in the renewed framework this is
+    # the report-card inspection's own start date (col "Inspection start
+    # date"), distinct from the legacy OEIF graded/ungraded dates above.
+    "rc_inspection_date": ["Inspection start date"],
+    "report_url": [
+        "Web Link (opens in new window)",
+        "Web link to Ofsted provider page",
+        "Web link",
+    ],
 }
 
 
 def discover_csv_url() -> str | None:
-    """Scrape GOV.UK page to find the latest MI CSV download link."""
+    """Scrape GOV.UK page to find the latest MI CSV download link.
+
+    The page lists a decade of monthly files, oldest first — take the
+    newest 'latest inspections as at <date>' link by parsing its date,
+    never matches[0] (that is a 2017 file).
+    """
     resp = requests.get(GOV_UK_PAGE, timeout=30)
     resp.raise_for_status()
-    # Look for CSV attachment links
-    matches = re.findall(
+    csv_links = re.findall(
         r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.csv)"',
         resp.text,
     )
-    if matches:
-        return matches[0]
+
+    months = {
+        'january': 1, 'february': 2, 'march': 3, 'april': 4, 'may': 5, 'june': 6,
+        'july': 7, 'august': 8, 'september': 9, 'october': 10, 'november': 11, 'december': 12,
+        'jan': 1, 'feb': 2, 'mar': 3, 'apr': 4, 'jun': 6,
+        'jul': 7, 'aug': 8, 'sep': 9, 'oct': 10, 'nov': 11, 'dec': 12,
+    }
+    parsed_links = []
+    for link in csv_links:
+        normalized = link.lower().replace('-', '_')
+        if 'latest_inspections_as_at' not in normalized:
+            continue
+        match = re.search(r'as_at_(\d{1,2})_([a-z]+)_(\d{4})', normalized)
+        if match:
+            day, month_str, year = match.groups()
+            month = months.get(month_str)
+            if month:
+                try:
+                    parsed_links.append((datetime(int(year), month, int(day)), link))
+                except ValueError:
+                    continue
+    parsed_links.sort(reverse=True)
+    if parsed_links:
+        return parsed_links[0][1]
+    if csv_links:
+        return csv_links[-1]
     # Fall back to ODS
     matches = re.findall(
         r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.ods)"',
@@ -103,6 +148,51 @@ def discover_csv_url() -> str | None:
     return matches[0] if matches else None
 
 
+def discover_independent_csv_url() -> str | None:
+    """Scrape GOV.UK page to find the latest independent schools MI CSV download link."""
+    resp = requests.get(INDEPENDENT_GOV_UK_PAGE, timeout=30)
+    resp.raise_for_status()
+    # Look for CSV attachment links
+    csv_links = re.findall(
+        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.csv)"',
+        resp.text,
+    )
+    if not csv_links:
+        # Fall back to ODS
+        csv_links = re.findall(
+            r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.ods)"',
+            resp.text,
+        )
+
+    months = {
+        'january': 1, 'february': 2, 'march': 3, 'april': 4, 'may': 5, 'june': 6,
+        'july': 7, 'august': 8, 'september': 9, 'october': 10, 'november': 11, 'december': 12
+    }
+
+    parsed_links = []
+    for link in csv_links:
+        normalized_link = link.lower().replace('-', '_')
+        if 'most_recent' not in normalized_link:
+            continue
+
+        match = re.search(r'as_at_(\d{1,2})_([a-z]+)_(\d{4})', normalized_link)
+        if match:
+            day, month_str, year = match.groups()
+            month = months.get(month_str)
+            if month:
+                try:
+                    dt = datetime(int(year), month, int(day))
+                    parsed_links.append((dt, link))
+                except ValueError:
+                    continue
+
+    parsed_links.sort(reverse=True)
+    if parsed_links:
+        return parsed_links[0][1]
+
+    return csv_links[0] if csv_links else None
+
+
 class OfstedInspectionsStream(Stream):
     """Stream: Ofsted inspection records."""
 
@@ -131,10 +221,9 @@ class OfstedInspectionsStream(Stream):
         th.Property("rc_attendance_behaviour", th.StringType),
         th.Property("rc_personal_development", th.StringType),
         th.Property("rc_leadership_governance", th.StringType),
-        # No MI column exists for these yet; declared for forward
-        # compatibility with the mart schema, always emitted as absent/NULL.
         th.Property("rc_early_years", th.StringType),
         th.Property("rc_sixth_form", th.StringType),
+        th.Property("rc_inspection_date", th.StringType),
         th.Property("report_url", th.StringType),
     ).to_dict()
 
@@ -148,15 +237,8 @@ class OfstedInspectionsStream(Stream):
                     break
         return mapping
 
-    def get_records(self, context):
-        import pandas as pd
-
-        url = self.config.get("mi_url") or discover_csv_url()
-        if not url:
-            self.logger.error("Could not discover Ofsted MI download URL")
-            return
-
-        self.logger.info("Downloading Ofsted MI: %s", url)
+    def _fetch_and_parse_url(self, url: str, pd) -> list[dict]:
+        """Download file and parse records."""
         resp = requests.get(url, timeout=120)
         resp.raise_for_status()
 
@@ -172,8 +254,6 @@ class OfstedInspectionsStream(Stream):
             lines = text.split("\n")
             header_idx = 0
             for i, line in enumerate(lines[:20]):
-                # Match lines where URN appears as a CSV field (start or after comma),
-                # not as a substring of words like "turn" or "return".
                 if re.search(r'(?:^|,)\s*URN\s*(?:,|$)', line):
                     header_idx = i
                     break
@@ -191,16 +271,38 @@ class OfstedInspectionsStream(Stream):
         for _, row in df.iterrows():
             record = {}
             for field, col in col_map.items():
-                record[field] = row.get(col, None)
+                val = row.get(col, None)
+                if pd.isna(val):
+                    val = None
+                record[field] = val
 
             # Cast URN
             try:
-                record["urn"] = int(record["urn"])
+                record["urn"] = int(record.get("urn"))
             except (ValueError, KeyError, TypeError):
                 continue
 
             yield record
 
+    def get_records(self, context):
+        import pandas as pd
+
+        # 1. State-funded schools
+        state_url = self.config.get("mi_url") or discover_csv_url()
+        if state_url:
+            self.logger.info("Downloading Ofsted state-funded MI: %s", state_url)
+            yield from self._fetch_and_parse_url(state_url, pd)
+        else:
+            self.logger.error("Could not discover Ofsted state-funded MI download URL")
+
+        # 2. Independent schools
+        ind_url = self.config.get("independent_mi_url") or discover_independent_csv_url()
+        if ind_url:
+            self.logger.info("Downloading Ofsted independent MI: %s", ind_url)
+            yield from self._fetch_and_parse_url(ind_url, pd)
+        else:
+            self.logger.error("Could not discover Ofsted independent MI download URL")
+
 
 class TapUKOfsted(Tap):
     """Singer tap for UK Ofsted Management Information."""
@@ -209,6 +311,7 @@ class TapUKOfsted(Tap):
 
     config_jsonschema = th.PropertiesList(
         th.Property("mi_url", th.StringType, description="Direct URL to Ofsted MI file"),
+        th.Property("independent_mi_url", th.StringType, description="Direct URL to Ofsted Independent Schools MI file"),
     ).to_dict()
 
     def discover_streams(self):

pipeline/transform/models/intermediate/int_ofsted_latest.sql

@@ -34,6 +34,7 @@ select
     rc_leadership_governance,
     rc_early_years,
     rc_sixth_form,
+    rc_inspection_date,
     report_url
 from ranked
 where rn = 1

pipeline/transform/models/marts/_marts_schema.yml

@@ -133,6 +133,16 @@ models:
       - name: year
         tests: [not_null]
 
+  - name: fact_census_benchmarks
+    description: >
+      State-school context benchmarks from the pupil census — one row per
+      phase (primary/secondary), latest census year. fsm_pct/eal_pct are
+      pupil-weighted means; consumers label them "state-school average
+      (computed from our dataset)", never "England average".
+    columns:
+      - name: phase
+        tests: [not_null, unique]
+
   - name: fact_admissions
     description: School admissions — one row per URN per year
     columns:

pipeline/transform/models/marts/fact_census_benchmarks.sql

@@ -0,0 +1,39 @@
+{{ config(materialized='table') }}
+
+-- Mart: state-school context benchmarks from the pupil census — one row per
+-- phase, latest census year. Computed at import time (never per request).
+-- fsm_pct / eal_pct are pupil-weighted means, i.e. "what % of pupils", not
+-- "the median school" — this matches how DfE quotes national FSM/EAL rates.
+-- Consumers must label these "state-school average (computed from our
+-- dataset)" (spec §8.6), never "England average".
+
+with latest as (
+    select max(year) as year from {{ ref('fact_pupil_characteristics') }}
+),
+
+classified as (
+    select
+        case
+            when p.phase_type_grouping ilike '%primary%'   then 'primary'
+            when p.phase_type_grouping ilike '%secondary%' then 'secondary'
+        end as phase,
+        p.total_pupils,
+        p.fsm_pct,
+        p.eal_pct,
+        l.year
+    from {{ ref('fact_pupil_characteristics') }} p
+    join latest l on p.year = l.year
+    where p.total_pupils is not null and p.total_pupils > 0
+)
+
+select
+    phase,
+    max(year) as year,
+    round((sum(fsm_pct * total_pupils) filter (where fsm_pct is not null)
+        / nullif(sum(total_pupils) filter (where fsm_pct is not null), 0))::numeric, 1) as fsm_pct,
+    round((sum(eal_pct * total_pupils) filter (where eal_pct is not null)
+        / nullif(sum(total_pupils) filter (where eal_pct is not null), 0))::numeric, 1) as eal_pct,
+    round(percentile_cont(0.5) within group (order by total_pupils))::integer as median_pupils
+from classified
+where phase is not null
+group by phase

pipeline/transform/models/marts/fact_ks4_national_averages.sql

@@ -1,25 +1,22 @@
 {{ config(materialized='table') }}
 
--- Mart: Computed national KS4 averages — one row per academic year.
--- Unlike fact_ks2_national_averages (official DfE figures), DfE publishes no
--- KS4 national-headline dataset we ingest yet, so these are means computed
--- across the state schools in our dataset. Computed once at build time so the
--- API never has to aggregate the full performance table per request.
--- Semantics match the API's previous per-request computation: rows where
--- attainment_8_score is non-null; per-column means ignore NULLs.
+-- Mart: OFFICIAL DfE KS4 national headline averages — one row per academic
+-- year (England, state-funded, all pupils), from the EES national dataset.
+-- Replaces the previous unweighted school-level means, which were 7–15
+-- points off every headline measure and produced an arithmetically
+-- impossible national Progress 8. gcse_grade_91_pct has no official
+-- national series and is NULL (schema kept for the API model).
 
 select
     year,
-    round(avg(attainment_8_score)::numeric, 2)               as attainment_8_score,
-    round(avg(progress_8_score)::numeric, 2)                 as progress_8_score,
-    round(avg(english_maths_standard_pass_pct)::numeric, 2)  as english_maths_standard_pass_pct,
-    round(avg(english_maths_strong_pass_pct)::numeric, 2)    as english_maths_strong_pass_pct,
-    round(avg(ebacc_entry_pct)::numeric, 2)                  as ebacc_entry_pct,
-    round(avg(ebacc_standard_pass_pct)::numeric, 2)          as ebacc_standard_pass_pct,
-    round(avg(ebacc_strong_pass_pct)::numeric, 2)            as ebacc_strong_pass_pct,
-    round(avg(ebacc_avg_score)::numeric, 2)                  as ebacc_avg_score,
-    round(avg(gcse_grade_91_pct)::numeric, 2)                as gcse_grade_91_pct
-from {{ ref('fact_ks4_performance') }}
-where attainment_8_score is not null
-group by year
+    attainment_8_score,
+    progress_8_score,
+    english_maths_standard_pass_pct,
+    english_maths_strong_pass_pct,
+    ebacc_entry_pct,
+    ebacc_standard_pass_pct,
+    ebacc_strong_pass_pct,
+    ebacc_avg_score,
+    cast(null as double precision) as gcse_grade_91_pct
+from {{ ref('stg_ees_ks4_national') }}
 order by year

pipeline/transform/models/marts/fact_ofsted_inspection.sql

@@ -23,5 +23,6 @@ select
     rc_leadership_governance,
     rc_early_years,
     rc_sixth_form,
+    rc_inspection_date,
     report_url
 from {{ ref('stg_ofsted_inspections') }}

pipeline/transform/models/staging/_stg_sources.yml

@@ -51,6 +51,9 @@ sources:
       - name: ees_ks2_national
         description: KS2 national headline averages from DfE EES data catalogue — one row per academic year
 
+      - name: ees_ks4_national
+        description: Official KS4 national headline averages from DfE EES data catalogue — one row per academic year
+
       # Phonics: no school-level data on EES (only national/LA level)
 
       - name: fbit_finance

pipeline/transform/models/staging/stg_ees_ks4_national.sql

@@ -0,0 +1,20 @@
+{{ config(materialized='table') }}
+
+-- Staging model: official DfE KS4 national headline averages — one row per
+-- academic year (England, all state-funded, all pupils). Source: EES data
+-- catalogue "National characteristics summary data". Suppressed values
+-- ('z', 'x') are coerced to NULL by safe_numeric — Progress 8 is 'z' in
+-- years with no KS2 baseline (e.g. 2024/25): legitimately unpublished.
+
+select
+    cast(trim(time_period) as integer)                        as year,
+    {{ safe_numeric('attainment_8_score') }}                  as attainment_8_score,
+    {{ safe_numeric('progress_8_score') }}                    as progress_8_score,
+    {{ safe_numeric('english_maths_standard_pass_pct') }}     as english_maths_standard_pass_pct,
+    {{ safe_numeric('english_maths_strong_pass_pct') }}       as english_maths_strong_pass_pct,
+    {{ safe_numeric('ebacc_entry_pct') }}                     as ebacc_entry_pct,
+    {{ safe_numeric('ebacc_standard_pass_pct') }}             as ebacc_standard_pass_pct,
+    {{ safe_numeric('ebacc_strong_pass_pct') }}               as ebacc_strong_pass_pct,
+    {{ safe_numeric('ebacc_avg_score') }}                     as ebacc_avg_score
+from {{ source('raw', 'ees_ks4_national') }}
+where time_period ~ '^[0-9]+$'

pipeline/transform/models/staging/stg_ofsted_inspections.sql

@@ -46,12 +46,16 @@ renamed as (
         {{ parse_report_card_grade('rc_attendance_behaviour') }}::integer as rc_attendance_behaviour,
         {{ parse_report_card_grade('rc_personal_development') }}::integer as rc_personal_development,
         {{ parse_report_card_grade('rc_leadership_governance') }}::integer as rc_leadership_governance,
-        -- No MI column exists for these yet (see tap.py); the tap never
-        -- emits rc_early_years/rc_sixth_form, so these stay NULL.
-        null::integer as rc_early_years,
-        null::integer as rc_sixth_form,
+        {{ parse_report_card_grade('rc_early_years') }}::integer          as rc_early_years,
+        {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,
 
-        report_url
+        -- Start date of the latest FULL inspection (the report-card
+        -- inspection in the renewed framework). Guarded in the final select:
+        -- only kept when the row actually carries report-card grades, because
+        -- in legacy-format files this column is the legacy inspection date.
+        to_date(nullif(trim(rc_inspection_date), 'NULL'), 'DD/MM/YYYY')   as rc_inspection_date_raw,
+
+        nullif(trim(report_url), 'NULL')                                  as report_url
     from source
     where urn is not null
       and (
@@ -60,5 +64,17 @@ renamed as (
       )
 )
 
-select * from renamed
+select
+    *,
+    case
+        when rc_safeguarding_met is not null
+          or rc_inclusion is not null
+          or rc_curriculum_teaching is not null
+          or rc_achievement is not null
+          or rc_attendance_behaviour is not null
+          or rc_personal_development is not null
+          or rc_leadership_governance is not null
+        then rc_inspection_date_raw
+    end as rc_inspection_date
+from renamed
 where inspection_date is not null