fix: preserve literal 'NULL' strings for primary key columns

fix: convert NaN/NULL to None and restore record properties structure in tap.py
feat: ingest independent schools in Ofsted tap and dbt staging
2026-07-16 08:54:14 +01:00 · 2026-07-16 08:29:59 +01:00 · 2026-07-15 23:21:40 +01:00 · 2026-07-15 17:38:02 +01:00 · 2026-07-15 17:23:40 +01:00 · 2026-07-15 12:32:36 +01:00
34 changed files with 1147 additions and 464 deletions
@@ -5,6 +5,13 @@ on:
    branches:
      - main
 # Cancel superseded runs: pushing a new commit to a PR (or an empty
 # re-trigger) aborts the previous still-running checks instead of running
 # a second full matrix alongside them.
 concurrency:
  group: pr-checks-${{ gitea.event.pull_request.number }}
  cancel-in-progress: true
 env:
  REGISTRY: privaterepo.sitaru.org
  BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
@@ -23,12 +30,22 @@ jobs:
        uses: actions/setup-node@v4
        with:
          node-version: 22
-          cache: npm
+
-          cache-dependency-path: nextjs-app/package-lock.json
+      # Cache the resolved node_modules (452 MB / 460 packages) keyed on the
      # lockfile. On a hit — the common case, since deps change rarely — the
      # whole `npm ci` step is skipped, not just its download phase. The key
      # pins OS + node major so we never restore incompatible native binaries.
      - name: Cache node_modules
        id: node-modules-cache
        uses: actions/cache@v4
        with:
          path: nextjs-app/node_modules
          key: nextjs-node-modules-${{ runner.os }}-node22-${{ hashFiles('nextjs-app/package-lock.json') }}
      - name: Install dependencies
        if: steps.node-modules-cache.outputs.cache-hit != 'true'
        working-directory: nextjs-app
-        run: npm ci
+        run: npm ci --prefer-offline --no-audit --no-fund
      - name: Typecheck
        working-directory: nextjs-app
@@ -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
@@ -679,8 +680,10 @@ async def compare_schools(
    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()
@@ -772,93 +775,101 @@ async def get_la_averages(request: Request):
    return {"year": latest_year, "secondary": {"attainment_8_by_la": la_avg}}
 _KS2_NATIONAL_METRICS = [
    "rwm_expected_pct", "rwm_high_pct",
    "reading_expected_pct", "writing_expected_pct", "maths_expected_pct",
    "gps_expected_pct", "gps_high_pct", "science_expected_pct",
    "reading_avg_score", "maths_avg_score", "gps_avg_score",
    "reading_progress", "writing_progress", "maths_progress",
    "overall_absence_pct", "persistent_absence_pct",
    "disadvantaged_gap", "disadvantaged_pct", "sen_support_pct", "eal_pct",
 ]
 _KS4_NATIONAL_METRICS = [
    "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", "gcse_grade_91_pct",
 ]
 def _national_averages_payload(df: pd.DataFrame) -> dict:
    """National-averages payload shared by /api/national-averages and
-    /api/compare. Official DfE KS2 figures come from the mart table;
+    /api/compare.
-    KS4 figures are computed from our dataset (no DfE dataset yet)."""
+
    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.
    """
    if df.empty:
        return {"primary": {}, "secondary": {}}
-    ks2_metrics = [
+    latest_year = int(df["year"].max())
        "rwm_expected_pct", "rwm_high_pct",
        "reading_expected_pct", "writing_expected_pct", "maths_expected_pct",
        "gps_expected_pct", "gps_high_pct", "science_expected_pct",
        "reading_avg_score", "maths_avg_score", "gps_avg_score",
        "reading_progress", "writing_progress", "maths_progress",
        "overall_absence_pct", "persistent_absence_pct",
        "disadvantaged_gap", "disadvantaged_pct", "sen_support_pct", "eal_pct",
    ]
    ks4_metrics = [
        "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", "gcse_grade_91_pct",
    ]
-    def _means(sub_df, metric_list):
+    from . import database
    from .models import Ks2NationalAverage, Ks4NationalAverage
    def _row_metrics(row, metric_list):
        out = {}
        for col in metric_list:
-            if col in sub_df.columns:
+            val = getattr(row, col, None)
-                val = sub_df[col].dropna()
+            if val is not None:
-                if len(val) > 0:
+                out[col] = val
                    out[col] = round(float(val.mean()), 2)
        return out
-    latest_year = int(df["year"].max())
+    ks2_rows: list = []
-    df_latest = df[df["year"] == latest_year]
+    ks4_rows: list = []
    # Primary: schools where KS2 data is non-null
    primary_df = df_latest[df_latest["rwm_expected_pct"].notna()]
    # Secondary: schools where KS4 data is non-null
    secondary_df = df_latest[df_latest["attainment_8_score"].notna()]
    latest_primary = _means(primary_df, ks2_metrics)
    latest_secondary = _means(secondary_df, ks4_metrics)
    # Per-year KS2 primary averages: use official DfE figures from the mart table.
    # Per-year KS4 secondary averages: computed from our dataset (no DfE dataset yet).
    from . import database
    from .models import Ks2NationalAverage
    by_year = []
    db = None
    try:
        db = database.SessionLocal()
-        nat_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
+        try:
-        # Build a lookup of computed secondary averages per year as fallback
+            ks2_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
-        secondary_by_year = {}
+        except Exception:
-        for yr in sorted(df["year"].dropna().unique()):
+            db.rollback()
-            yr = int(yr)
+        try:
-            df_yr = df[df["year"] == yr]
+            ks4_rows = db.query(Ks4NationalAverage).order_by(Ks4NationalAverage.year).all()
-            secondary_by_year[yr] = _means(
+        except Exception:
-                df_yr[df_yr["attainment_8_score"].notna()], ks4_metrics
+            db.rollback()
-            )
+    except Exception:
-        # Merge: official KS2 figures + computed KS4 figures per year
+        pass
        ks2_years = {r.year for r in nat_rows}
        all_years = sorted(ks2_years | set(secondary_by_year.keys()))
        nat_lookup = {r.year: r for r in nat_rows}
        for yr in all_years:
            primary_yr: dict = {}
            if yr in nat_lookup:
                r = nat_lookup[yr]
                for col in ks2_metrics:
                    val = getattr(r, col, None)
                    if val is not None:
                        primary_yr[col] = val
            by_year.append({
                "year": yr,
                "primary": primary_yr,
                "secondary": secondary_by_year.get(yr, {}),
            })
    finally:
        if db is not None:
            db.close()
-    # Update latest_primary with official DfE figure for the latest year if available
+    primary_by_year = {r.year: _row_metrics(r, _KS2_NATIONAL_METRICS) for r in ks2_rows}
-    if by_year:
+    secondary_by_year = {r.year: _row_metrics(r, _KS4_NATIONAL_METRICS) for r in ks4_rows}
-        latest_official = next((e["primary"] for e in reversed(by_year) if e["primary"]), None)
+
-        if latest_official:
+    if not any(secondary_by_year.values()):
-            latest_primary = latest_official
+        # 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 = [
        {
            "year": yr,
            "primary": primary_by_year.get(yr, {}),
            "secondary": secondary_by_year.get(yr, {}),
        }
        for yr in all_years
    ]
    latest_primary = next((e["primary"] for e in reversed(by_year) if e["primary"]), {})
    latest_secondary = next((e["secondary"] for e in reversed(by_year) if e["secondary"]), {})
    return {
        "year": latest_year,
@@ -577,6 +577,7 @@ def compute_benchmarks(df: pd.DataFrame) -> dict:
            "eal_pct": _median(sub, "eal_pct"),
            "sen_support_pct": _median(sub, "sen_support_pct"),
            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
            "fsm_pct": _median(sub, "fsm_pct"),
            "median_pupils": median_pupils,
        }
        if with_disadvantaged:
@@ -662,92 +663,150 @@ def _admissions_row_dict(a) -> dict:
    }
-def get_supplementary_data(db: Session, urn: int) -> dict:
+def _census_dict(pc) -> dict:
-    """Fetch all supplementary data for a single school URN."""
+    return {
-    result = {}
+        "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)
+            fn()
            if latest_field:
                q = q.order_by(getattr(model, latest_field).desc())
            return q.first()
        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
+    # Ofsted — latest inspection per URN. Ordered so the first row seen per
-    o = safe_query(FactOfstedInspection, "urn", "inspection_date")
+    # URN is the most recent.
-    result["ofsted"] = _ofsted_block(o, urn) if o else None
+    def _ofsted():
-
+        rows = (
-    # Census (latest year of fact_pupil_characteristics)
+            db.query(FactOfstedInspection)
-    pc = safe_query(FactPupilCharacteristics, "urn", "year")
+            .filter(FactOfstedInspection.urn.in_(urns))
-    result["census"] = (
+            .order_by(FactOfstedInspection.urn, FactOfstedInspection.inspection_date.desc())
        {
            "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())
            .all()
        )
-    except Exception as e:
+        seen = set()
-        import logging
+        for o in rows:
-        logging.getLogger(__name__).error("admissions history query failed: %s", e)
+            if o.urn in seen:
-        db.rollback()
+                continue
-        admissions_rows = []
+            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]
+    # Census — latest year per URN.
-    result["admissions_history"] = history
+    def _census():
-    # Keep the single latest-year object for backwards-compatible consumers
+        rows = (
-    # (hero chips, etc.).
+            db.query(FactPupilCharacteristics)
-    result["admissions"] = history[-1] if history else None
+            .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
+    # Admissions — all years per URN, oldest first (multi-year trend view).
-    result["sen_detail"] = None
+    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
+    # Deprivation — one row per URN.
-    result["phonics"] = None
+    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
+    # Finance — latest year per URN.
-    d = safe_query(FactDeprivation, "urn")
+    def _finance():
-    result["deprivation"] = (
+        rows = (
-        {
+            db.query(FactFinance)
-            "lsoa_code": d.lsoa_code,
+            .filter(FactFinance.urn.in_(urns))
-            "idaci_score": d.idaci_score,
+            .order_by(FactFinance.urn, FactFinance.year.desc())
-            "idaci_decile": d.idaci_decile,
+            .all()
-        }
+        )
-        if d
+        seen = set()
-        else None
+        for f in rows:
-    )
+            if f.urn in seen:
-
+                continue
-    # Finance (latest year)
+            seen.add(f.urn)
-    f = safe_query(FactFinance, "urn", "year")
+            result[f.urn]["finance"] = _finance_dict(f)
-    result["finance"] = (
+    _safe(_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
    )
    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)]
@@ -231,6 +231,23 @@ class FactFinance(Base):
    premises_cost_pct = Column(Float)
 class Ks4NationalAverage(Base):
    """Computed national KS4 averages (from our dataset) — one row per year."""
    __tablename__ = "fact_ks4_national_averages"
    __table_args__ = MARTS
    year = Column(Integer, primary_key=True)
    attainment_8_score = Column(Float)
    progress_8_score = Column(Float)
    english_maths_standard_pass_pct = Column(Float)
    english_maths_strong_pass_pct = Column(Float)
    ebacc_entry_pct = Column(Float)
    ebacc_standard_pass_pct = Column(Float)
    ebacc_strong_pass_pct = Column(Float)
    ebacc_avg_score = Column(Float)
    gcse_grade_91_pct = Column(Float)
 class Ks2NationalAverage(Base):
    """Official DfE KS2 national headline averages — one row per academic year."""
    __tablename__ = "fact_ks2_national_averages"
@@ -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)
@@ -59,6 +59,8 @@ def test_medians_ignore_nan_and_older_years():
    assert b["year"] == LATEST
    # eal medians over [10,20,30,40,50] = 30
    assert b["primary"]["eal_pct"] == 30.0
    # fsm medians over [15,17,19,21,23] = 19
    assert b["primary"]["fsm_pct"] == 19.0
    # median pupils over [200,280,300,350,400] = 300
    assert b["primary"]["median_pupils"] == 300
@@ -66,6 +68,7 @@ def test_medians_ignore_nan_and_older_years():
 def test_secondary_block_has_no_disadvantaged_rwm():
    b = compute_benchmarks(_df())
    assert "disadvantaged_rwm_expected_pct" not in b["secondary"]
    assert b["secondary"]["fsm_pct"] == 13.0
    assert b["secondary"]["median_pupils"] == 1100
@@ -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"]
@@ -0,0 +1,93 @@
 """_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."""
 import numpy as np
 import pandas as pd
 import pytest
 LATEST = 202425
 def _df():
    return pd.DataFrame(
        [
            dict(year=202324, attainment_8_score=40.0, rwm_expected_pct=np.nan),
            dict(year=LATEST, attainment_8_score=50.0, rwm_expected_pct=np.nan),
            dict(year=LATEST, attainment_8_score=30.0, rwm_expected_pct=np.nan),
            dict(year=LATEST, attainment_8_score=np.nan, rwm_expected_pct=80.0),
        ]
    )
 class _Ks2Row:
    year = LATEST
    rwm_expected_pct = 62.1
    gps_expected_pct = 72.0
 class _Ks4Row:
    year = LATEST
    attainment_8_score = 46.5
    progress_8_score = -0.02
 class _StubSession:
    """Returns KS2 rows for the first query and KS4 rows for the second —
    mirroring the payload's query order."""
    def __init__(self):
        self.calls = 0
    def query(self, model):
        self._model = model.__name__
        return self
    def order_by(self, *a):
        return self
    def all(self):
        return [_Ks2Row()] if self._model == "Ks2NationalAverage" else [_Ks4Row()]
    def close(self):
        pass
 class _Ks4MissingSession(_StubSession):
    def all(self):
        if self._model == "Ks4NationalAverage":
            raise RuntimeError("relation does not exist")
        return [_Ks2Row()]
    def rollback(self):
        pass
@pytest.fixture()
 def payload(monkeypatch):
    from backend import app as app_module
    from backend import database as database_module
    def _run(session_cls):
        monkeypatch.setattr(database_module, "SessionLocal", session_cls)
        return app_module._national_averages_payload(_df())
    return _run
 def test_ks4_averages_come_from_the_mart_not_the_dataframe(payload):
    body = payload(_StubSession)
    # Mart value (46.5), NOT the dataframe mean of (50+30)/2 = 40.0
    assert body["secondary"]["attainment_8_score"] == 46.5
    assert body["primary"]["rwm_expected_pct"] == 62.1
    assert body["by_year"][-1]["secondary"]["progress_8_score"] == -0.02
 def test_missing_ks4_mart_falls_back_to_latest_year_only(payload):
    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]
@@ -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"] == []
@@ -19,6 +19,27 @@ 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]];
 }
 test('home page loads with hero search', async ({ page }) => {
  await page.goto('/');
  await expect(page.locator('h1').first()).toBeVisible();
@@ -139,19 +160,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
+  // Two same-phase (pure primary) schools so both stay on one tab.
-  await searchByName(page, 'primary');
+  const [urn0, urn1] = await twoPrimaryUrns(page);
  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);
-  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*="${urn0}"]`).first()).toBeVisible({ timeout: 15_000 });
-  await expect(page.locator(`a[href*="${urns[1]}"]`).first()).toBeVisible();
+  await expect(page.locator(`a[href*="${urn1}"]`).first()).toBeVisible();
  // The parent-first sections render in order (data-invariant: headings only)
  for (const heading of [
@@ -212,8 +227,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 });
@@ -95,4 +95,12 @@ describe('CompareOfsted', () => {
    expect(links).toHaveLength(3);
    expect(links[0]).toHaveAttribute('href', 'https://reports.ofsted.gov.uk/provider/21/1');
  });
  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);
  });
 });
@@ -0,0 +1,82 @@
 /**
 * Regression: on refresh, the compare page must show the SSR-rendered data.
 *
 * The basket hydrates from the URL a beat after mount (selectedSchools is
 * empty for the first render), so the fetch effect must not blank the
 * SSR payload during that window — and must not refetch data the server
 * already provided.
 */
 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 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,
  };
 }
 const INITIAL_DATA = {
  '100': data(100, 'Alpha Primary'),
  '200': data(200, 'Beta Primary'),
 };
 beforeEach(() => {
  fetchComparison.mockReset();
 });
 test('renders SSR data on refresh without wiping it or refetching', async () => {
  render(
    <ComparisonProvider>
      <ComparisonView
        initialData={INITIAL_DATA}
        initialNationalAverages={{
          year: 202425,
          primary: { rwm_expected_pct: 62 },
          secondary: {},
          by_year: [],
        }}
        initialBenchmarks={undefined}
        initialUrns={[100, 200]}
        metrics={[]}
        selectedMetric="rwm_expected_pct"
      />
    </ComparisonProvider>,
  );
  // Both SSR-provided schools appear (data was not blanked during hydration)
  await waitFor(() => {
    expect(screen.getAllByText('Alpha Primary').length).toBeGreaterThan(0);
  });
  expect(screen.getAllByText('Beta Primary').length).toBeGreaterThan(0);
  expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
  // …and the client never refetched data the server already rendered.
  expect(fetchComparison).not.toHaveBeenCalled();
 });
@@ -126,6 +126,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',
@@ -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…');
  });
 });
@@ -32,26 +32,24 @@ export default async function ComparePage({ searchParams }: ComparePageProps) {
  const selectedMetric = metricParam || 'rwm_expected_pct';
  try {
-    // Fetch comparison data if URNs provided
+    // Fetch comparison + metrics in parallel — they are independent.
-    let comparisonData = null;
+    const [comparisonResponse, metricsResponse] = await Promise.all([
-    if (urns.length > 0) {
+      urns.length > 0
-      try {
+        ? fetchComparison(urnsParam!).catch((error) => {
-        const response = await fetchComparison(urnsParam!);
+            console.error('Failed to fetch comparison:', error);
-        comparisonData = response.comparison;
+            return null;
-      } catch (error) {
+          })
-        console.error('Failed to fetch comparison:', error);
+        : Promise.resolve(null),
-      }
+      fetchMetrics(),
-    }
+    ]);
    // Fetch available metrics
    const metricsResponse = await fetchMetrics();
    // Metrics is already an array
    const metricsArray = metricsResponse?.metrics || [];
    return (
      <ComparisonView
-        initialData={comparisonData}
+        initialData={comparisonResponse?.comparison ?? null}
        initialNationalAverages={comparisonResponse?.national_averages}
        initialBenchmarks={comparisonResponse?.benchmarks}
        initialUrns={urns}
        metrics={metricsArray}
        selectedMetric={selectedMetric}
@@ -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;
@@ -163,3 +170,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;
  }
 }
@@ -28,13 +28,15 @@ 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';
 interface ComparisonViewProps {
  initialData: Record<string, ComparisonData> | null;
  initialNationalAverages?: NationalAverages;
  initialBenchmarks?: Benchmarks;
  initialUrns: number[];
  metrics: MetricDefinition[];
  selectedMetric: string;
@@ -42,6 +44,8 @@ interface ComparisonViewProps {
 export function ComparisonView({
  initialData,
  initialNationalAverages,
  initialBenchmarks,
  initialUrns,
  metrics,
  selectedMetric: initialMetric,
@@ -54,8 +58,10 @@ export function ComparisonView({
  const [selectedMetric, setSelectedMetric] = useState(initialMetric);
  const [isModalOpen, setIsModalOpen] = useState(false);
  const [comparisonData, setComparisonData] = useState(initialData);
-  const [nationalAverages, setNationalAverages] = useState<NationalAverages | undefined>();
+  const [nationalAverages, setNationalAverages] = useState<NationalAverages | undefined>(
-  const [benchmarks, setBenchmarks] = useState<Benchmarks | undefined>();
+    initialNationalAverages,
  );
  const [benchmarks, setBenchmarks] = useState<Benchmarks | undefined>(initialBenchmarks);
  const [shareConfirm, setShareConfirm] = useState(false);
  const [comparePhase, setComparePhase] = useState<'primary' | 'secondary'>('primary');
  // Tracks whether the user has explicitly clicked a phase tab.
@@ -81,13 +87,16 @@ export function ComparisonView({
    }
  }, [isInitialized]); // eslint-disable-line react-hooks/exhaustive-deps
-  // Sync URL with selected schools + metric, and (re)fetch the comparison.
+  const urnKey = selectedSchools.map((s) => s.urn).join(',');
  // Sync the URL with the selection + metric. Pure navigation state — no
  // fetching here: metric changes are presentational (the data is already
  // client-side) and must not refire the comparison request.
  useEffect(() => {
    const urns = selectedSchools.map((s) => s.urn).join(',');
    const params = new URLSearchParams(searchParams);
-    if (urns) {
+    if (urnKey) {
-      params.set('urns', urns);
+      params.set('urns', urnKey);
    } else {
      params.delete('urns');
    }
@@ -96,40 +105,60 @@ export function ComparisonView({
    const newUrl = `${pathname}?${params.toString()}`;
    router.replace(newUrl, { scroll: false });
  }, [urnKey, selectedMetric, pathname, searchParams, router]);
-    if (selectedSchools.length > 0) {
+  // Fetch when the school set changes, but only for schools we don't already
-      fetchComparison(urns, { cache: 'no-store' })
+  // have data for. This skips the refetch of SSR-rendered data on load AND
-        .then((data) => {
+  // avoids a network call when a school is merely removed. A ref holds the
-          setComparisonData(data.comparison);
+  // latest data so the effect can read it without re-running on every fetch.
-          setNationalAverages(data.national_averages);
+  //
-          setBenchmarks(data.benchmarks);
+  // Correctness note: we must NOT null the data on a transient empty urnKey.
-        })
+  // On mount the basket is empty for a beat before it hydrates from the URL,
-        .catch((err) => {
+  // and blanking here (then skipping the refetch because SSR "covers" the set)
-          // Keep whatever we already have (SSR data or a previous fetch) rather
+  // was leaving the page empty on refresh. The render already shows the empty
-          // than blanking the page — a transient refetch failure shouldn't
+  // state whenever `selectedSchools` is empty, so stale data for deselected
-          // destroy a working comparison the user is looking at.
+  // schools is harmless — it's simply unused.
-          console.error('Failed to fetch comparison:', err);
+  const comparisonDataRef = useRef(comparisonData);
-        });
+  comparisonDataRef.current = comparisonData;
    } else {
      setComparisonData(null);
      setNationalAverages(undefined);
      setBenchmarks(undefined);
    }
  }, [selectedSchools, selectedMetric, pathname, searchParams, router]);
-  // Classify schools by phase using comparison data
+  useEffect(() => {
-  const classifySchool = (school: School): 'primary' | 'secondary' => {
+    if (!isInitialized || !urnKey) return;
    const have = comparisonDataRef.current ?? {};
    const covered = urnKey.split(',').every((urn) => have[urn] != null);
    if (covered) return;
    fetchComparison(urnKey, { cache: 'no-store' })
      .then((data) => {
        setComparisonData(data.comparison);
        setNationalAverages(data.national_averages);
        setBenchmarks(data.benchmarks);
      })
      .catch((err) => {
        // Keep whatever we already have (SSR data or a previous fetch) rather
        // than blanking the page — a transient refetch failure shouldn't
        // destroy a working comparison the user is looking at.
        console.error('Failed to fetch comparison:', err);
      });
  }, [urnKey, isInitialized]);
  const primarySchools = selectedSchools.filter((school) => {
    const info = comparisonData?.[school.urn]?.school_info;
-    if (info?.attainment_8_score != null) return 'secondary';
+    const hasPrimaryData =
-    if (info?.rwm_expected_pct != null) return 'primary';
+      info?.rwm_expected_pct != null ||
-    // Fallback: check yearly data
+      comparisonData?.[school.urn]?.yearly_data?.some((d) => d.rwm_expected_pct != null);
-    const yearlyData = comparisonData?.[school.urn]?.yearly_data;
+    if (hasPrimaryData) return true;
-    if (yearlyData?.some((d) => d.attainment_8_score != null)) return 'secondary';
+    return school.phase?.toLowerCase().includes('primary') || false;
-    return 'primary';
+  });
  };
-  const primarySchools = selectedSchools.filter((s) => classifySchool(s) === 'primary');
+  const secondarySchools = selectedSchools.filter((school) => {
-  const secondarySchools = selectedSchools.filter((s) => classifySchool(s) === 'secondary');
+    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(() => {
@@ -324,7 +353,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(' · ')}
@@ -349,6 +379,7 @@ export function ComparisonView({
                data={activeComparisonData}
                nationalAverages={nationalAverages}
                benchmarks={benchmarks}
                isSecondary={!isPrimary}
              />
              <CompareOfsted schools={activeSchools} data={activeComparisonData} />
              <CompareAcademics
@@ -356,12 +387,14 @@ export function ComparisonView({
                data={activeComparisonData}
                nationalAverages={nationalAverages}
                benchmarks={benchmarks}
                isSecondary={!isPrimary}
              />
              <CompareAdmissions schools={activeSchools} data={activeComparisonData} />
              <CompareCommunity
                schools={activeSchools}
                data={activeComparisonData}
                benchmarks={benchmarks}
                isSecondary={!isPrimary}
              />
              <TrendsExplorer
                schools={activeSchools}
@@ -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,
  );
@@ -10,7 +10,7 @@
 import { 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,
@@ -49,9 +49,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.">
+        <Measure
-          Interest in the school
+          tip="How many application forms named the school at any preference rank — not the number of families competing head-to-head for a place."
-        </RowLabel>
+          label="Interest in the school"
        >
        {schools.map((school, i) => {
          const a = rows[i];
          return (
@@ -68,7 +69,9 @@ export function CompareAdmissions({
          );
        })}
-        <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,7 +98,9 @@ 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);
@@ -118,6 +123,7 @@ export function CompareAdmissions({
            </Cell>
          );
        })}
        </Measure>
      </SectionGrid>
    </Section>
  );
@@ -15,7 +15,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 +47,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 +71,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 +85,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,8 +141,9 @@ 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);
          return (
@@ -148,8 +159,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 +186,7 @@ export function CompareAtAGlance({
            </Cell>
          );
        })}
        </Measure>
      </SectionGrid>
    </Section>
  );
@@ -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,27 @@ 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 anchor = bench?.fsm_pct ?? bench?.disadvantaged_pct ?? null;
-    const v = verdict(value, bench.disadvantaged_pct, 3);
+    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 === 'above' && `Above the state-school average (${Math.round(anchor)}%)`}
-        {v === 'close' && 'About the state-school average'}
+        {v === 'close' && `About the state-school average (${Math.round(anchor)}%)`}
-        {v === 'below' && 'Below the state-school average'}
+        {v === 'below' && `Below the state-school average (${Math.round(anchor)}%)`}
      </Chip>
    );
  };
@@ -48,7 +51,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 +77,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 +90,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.">
+        <Measure
-          Free school meals
+          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."
-        </RowLabel>
+          label="Free school meals"
        >
        {schools.map((school, i) => {
          const fsm = data[String(school.urn)]?.census?.fsm_pct ?? null;
          return (
@@ -104,10 +110,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.">
+        <Measure
-          English as an additional language
+          tip="% of pupils whose first language is known or believed to be other than English. State-school average computed from our dataset."
-        </RowLabel>
+          label="English as an additional language"
        >
        {schools.map((school, i) => {
          const eal = data[String(school.urn)]?.census?.eal_pct ?? null;
          return (
@@ -116,10 +124,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.">
+        <Measure
-          Extra learning support (SEN)
+          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."
-        </RowLabel>
+          label="Extra learning support (SEN)"
        >
        {schools.map((school, i) => {
          const rows = data[String(school.urn)]?.yearly_data ?? [];
          let sen: number | null = null;
@@ -143,8 +153,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 +166,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 +177,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 +190,7 @@ export function CompareCommunity({
            </Cell>
          );
        })}
        </Measure>
      </SectionGrid>
    </Section>
  );
@@ -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}`}>
@@ -157,14 +169,15 @@ 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);
@@ -176,9 +189,12 @@ export function CompareOfsted({
          );
        })}
-        <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.">
+        </Measure>
-          Judgement detail
+
-        </RowLabel>
+        <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 +212,12 @@ export function CompareOfsted({
          );
        })}
-        <RowLabel tip="Links to the school's page on ofsted.gov.uk, where all its inspection reports are listed.">
+        </Measure>
-          Ofsted page
+
-        </RowLabel>
+        <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 +230,7 @@ export function CompareOfsted({
            </Cell>
          );
        })}
        </Measure>
      </SectionGrid>
    </Section>
  );
@@ -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 {
+@media (max-width: 640px) {
-  overflow-x: auto;
+  /* Taller on mobile: the mobile-only school chips sit above the canvas and
-  margin-top: 1.5rem;
+     wrap to two rows for 3+ schools, so the plot keeps a usable height. */
-}
+  .chartBox {
-
+    height: 360px;
-.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;
 }
@@ -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
+ * compare page is lost; spec §4's tier 3) driving the year-by-year chart with
- * with its England reference line, plus the year-by-year table. Progress
+ * its England reference line. Matches the mockup: a measure picker and the
- * metrics carry CI-based bands for the years DfE published them.
+ * 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);
+              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.
            </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>
@@ -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);
+  padding: 0 0 0.1rem;
  border-radius: 6px;
  padding: 0.4rem 0.6rem;
  margin-top: 0.8rem;
 }
 .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 {
+/* The school name gets its own full-width line above the value — real
-  content: attr(data-school);
+   school names are long and varied, so a fixed-width name column truncated
-  display: block;
+   them ("Our Lady Queen of H…") or crowded the value. */
-  font-size: 0.72rem;
+.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;
+    color: var(--text-secondary);
    border-radius: 0;
    margin-top: 0;
    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 {
+  .big {
-    content: none;
+    font-size: 1.35rem;
  }
  .small {
    flex-basis: auto;
    padding-left: 0;
    margin-top: 0.1rem;
  }
 }
@@ -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={
-      style={{ '--sc': CHART_TEXT_COLORS[index % CHART_TEXT_COLORS.length] } as CSSProperties}
+        {
          '--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>
  );
@@ -1,50 +1,18 @@
 /**
- * Custom hook for managing school comparison state
+ * Custom hook for managing school comparison state.
- * Uses shared context for real-time updates across components
+ *
 * This hook is mounted on every page via the global Navigation and
 * ComparisonToast, so it must stay cheap — it exposes basket state only.
 * The compare page fetches `/api/compare` itself (ComparisonView); nothing
 * ever read the comparison payload from here, so the previous per-page SWR
 * fetch (which fired on every page whenever the basket was non-empty) was
 * dead weight and has been removed.
 */
 'use client';
 import useSWR from 'swr';
 import { fetcher } from '@/lib/api';
 import { useComparisonContext } from '@/context/ComparisonContext';
 import type { ComparisonResponse } from '@/lib/types';
 export function useComparison() {
-  const {
+  return useComparisonContext();
    selectedSchools,
    addSchool,
    removeSchool,
    replaceSchools,
    clearAll,
    isSelected,
    canAddMore,
    isInitialized,
  } = useComparisonContext();
  // Fetch comparison data for selected schools
  const urns = selectedSchools.map((s) => s.urn).join(',');
  const { data, error, isLoading, mutate } = useSWR<ComparisonResponse>(
    selectedSchools.length > 0 ? `/compare?urns=${urns}` : null,
    fetcher,
    {
      revalidateOnFocus: false,
      dedupingInterval: 10000,
    }
  );
  return {
    selectedSchools,
    comparisonData: data?.comparison,
    isLoading,
    error,
    addSchool,
    removeSchool,
    replaceSchools,
    clearAll,
    isSelected,
    canAddMore,
    isInitialized,
    mutate,
  };
 }
@@ -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 };
@@ -357,6 +357,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;
@@ -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
@@ -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
@@ -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
+    # Report Card fields (post-Nov 2025 framework).
    # 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.
    "rc_safeguarding_met": ["Safeguarding standards"],
    "rc_inclusion": ["Inclusion"],
    "rc_curriculum_teaching": ["Curriculum and teaching"],
@@ -81,6 +87,13 @@ 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)"],
    "report_url": [
        "Web Link (opens in new window)",
        "Web link to Ofsted provider page",
        "Web link",
    ],
 }
@@ -103,6 +116,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,8 +189,6 @@ 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("report_url", th.StringType),
@@ -148,15 +204,8 @@ class OfstedInspectionsStream(Stream):
                    break
        return mapping
-    def get_records(self, context):
+    def _fetch_and_parse_url(self, url: str, pd) -> list[dict]:
-        import pandas as pd
+        """Download file and parse records."""
        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)
        resp = requests.get(url, timeout=120)
        resp.raise_for_status()
@@ -172,8 +221,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 +238,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 +278,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):
@@ -160,6 +160,12 @@ models:
      - name: year
        tests: [not_null, unique]
  - name: fact_ks4_national_averages
    description: Computed national KS4 averages (means across state schools in our dataset — not official DfE figures) — one row per academic year
    columns:
      - name: year
        tests: [not_null, unique]
  - name: fact_deprivation
    description: IDACI deprivation index — one row per URN
    columns:
@@ -0,0 +1,25 @@
 {{ 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.
 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
 order by year
@@ -46,12 +46,10 @@ 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
+        {{ parse_report_card_grade('rc_early_years') }}::integer          as rc_early_years,
-        -- emits rc_early_years/rc_sixth_form, so these stay NULL.
+        {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,
        null::integer as rc_early_years,
        null::integer as rc_sixth_form,
-        report_url
+        nullif(trim(report_url), 'NULL')                                  as report_url
    from source
    where urn is not null
      and (