fix(compare): sticky school bar hidden behind the site header

The Navigation header is position:sticky top:0 (z-index 1000). The school
bar was also sticky top:0 (z-index 10), so when scrolled it pinned at the
same top:0 *behind* the header — on mobile 57 of its 72px were covered,
leaving only a sliver, so you couldn't see which schools were selected.

Offset the bar's sticky top to the header height (65px desktop, 57px
mobile — the Navigation breakpoint is also 640px) so it pins just below.

e2e guard asserts the bar's sticky offset is at least the header height
(verified it fails against the pre-fix build).

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

.gitea/workflows/pr-checks.yml

@@ -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

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
@@ -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;
-    KS4 figures are computed from our dataset (no DfE dataset yet)."""
+    /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.
+    """
     if df.empty:
         return {"primary": {}, "secondary": {}}
 
-    ks2_metrics = [
-        "rwm_expected_pct", "rwm_high_pct",
-        "reading_expected_pct", "writing_expected_pct", "maths_expected_pct",
-        "gps_expected_pct", "gps_high_pct", "science_expected_pct",
-        "reading_avg_score", "maths_avg_score", "gps_avg_score",
-        "reading_progress", "writing_progress", "maths_progress",
-        "overall_absence_pct", "persistent_absence_pct",
-        "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",
-    ]
+    latest_year = int(df["year"].max())
 
-    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 = sub_df[col].dropna()
-                if len(val) > 0:
-                    out[col] = round(float(val.mean()), 2)
+            val = getattr(row, col, None)
+            if val is not None:
+                out[col] = val
         return out
 
-    latest_year = int(df["year"].max())
-    df_latest = df[df["year"] == latest_year]
-
-    # 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 = []
+    ks2_rows: list = []
+    ks4_rows: list = []
     db = None
     try:
         db = database.SessionLocal()
-        nat_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
-        # Build a lookup of computed secondary averages per year as fallback
-        secondary_by_year = {}
-        for yr in sorted(df["year"].dropna().unique()):
-            yr = int(yr)
-            df_yr = df[df["year"] == yr]
-            secondary_by_year[yr] = _means(
-                df_yr[df_yr["attainment_8_score"].notna()], ks4_metrics
-            )
-        # Merge: official KS2 figures + computed KS4 figures per year
-        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, {}),
-            })
+        try:
+            ks2_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
+        except Exception:
+            db.rollback()
+        try:
+            ks4_rows = db.query(Ks4NationalAverage).order_by(Ks4NationalAverage.year).all()
+        except Exception:
+            db.rollback()
+    except Exception:
+        pass
     finally:
         if db is not None:
             db.close()
 
-    # Update latest_primary with official DfE figure for the latest year if available
-    if by_year:
-        latest_official = next((e["primary"] for e in reversed(by_year) if e["primary"]), None)
-        if latest_official:
-            latest_primary = latest_official
+    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 = [
+        {
+            "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,

backend/data_loader.py

@@ -662,92 +662,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

@@ -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"

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

@@ -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]

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"] == []

e2e/tests/journeys.spec.ts

@@ -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
-  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 [
@@ -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 });

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

@@ -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);
+  });
 });

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

@@ -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();
+});

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/app/compare/page.tsx

@@ -32,26 +32,24 @@ export default async function ComparePage({ searchParams }: ComparePageProps) {
   const selectedMetric = metricParam || 'rwm_expected_pct';
 
   try {
-    // Fetch comparison data if URNs provided
-    let comparisonData = null;
-    if (urns.length > 0) {
-      try {
-        const response = await fetchComparison(urnsParam!);
-        comparisonData = response.comparison;
-      } catch (error) {
-        console.error('Failed to fetch comparison:', error);
-      }
-    }
+    // Fetch comparison + metrics in parallel — they are independent.
+    const [comparisonResponse, metricsResponse] = await Promise.all([
+      urns.length > 0
+        ? fetchComparison(urnsParam!).catch((error) => {
+            console.error('Failed to fetch comparison:', error);
+            return null;
+          })
+        : 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}

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;
@@ -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;
+  }
+}

nextjs-app/components/ComparisonView.tsx

@@ -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 [benchmarks, setBenchmarks] = useState<Benchmarks | undefined>();
+  const [nationalAverages, setNationalAverages] = useState<NationalAverages | 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) {
-      params.set('urns', urns);
+    if (urnKey) {
+      params.set('urns', urnKey);
     } else {
       params.delete('urns');
     }
@@ -96,26 +105,42 @@ export function ComparisonView({
 
     const newUrl = `${pathname}?${params.toString()}`;
     router.replace(newUrl, { scroll: false });
+  }, [urnKey, selectedMetric, pathname, searchParams, router]);
 
-    if (selectedSchools.length > 0) {
-      fetchComparison(urns, { 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);
-        });
-    } else {
-      setComparisonData(null);
-      setNationalAverages(undefined);
-      setBenchmarks(undefined);
-    }
-  }, [selectedSchools, selectedMetric, pathname, searchParams, router]);
+  // Fetch when the school set changes, but only for schools we don't already
+  // have data for. This skips the refetch of SSR-rendered data on load AND
+  // avoids a network call when a school is merely removed. A ref holds the
+  // latest data so the effect can read it without re-running on every fetch.
+  //
+  // 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,
+  // and blanking here (then skipping the refetch because SSR "covers" the set)
+  // was leaving the page empty on refresh. The render already shows the empty
+  // state whenever `selectedSchools` is empty, so stale data for deselected
+  // schools is harmless — it's simply unused.
+  const comparisonDataRef = useRef(comparisonData);
+  comparisonDataRef.current = comparisonData;
+
+  useEffect(() => {
+    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]);
 
   // Classify schools by phase using comparison data
   const classifySchool = (school: School): 'primary' | 'secondary' => {
@@ -324,7 +349,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(' · ')}

nextjs-app/components/compare/CompareAdmissions.tsx

@@ -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.">
-          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 (
@@ -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>
   );

nextjs-app/components/compare/CompareAtAGlance.tsx

@@ -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 (
@@ -69,7 +69,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 (
@@ -87,16 +87,16 @@ export function CompareAtAGlance({
             </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 +131,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 +149,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 +176,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;
@@ -48,7 +48,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 +74,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 +87,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 +107,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 +121,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 +150,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 +163,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 +174,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 +187,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',
@@ -157,14 +157,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 +177,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.">
-          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 +200,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 +218,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/hooks/useComparison.ts

@@ -1,50 +1,18 @@
 /**
- * Custom hook for managing school comparison state
- * Uses shared context for real-time updates across components
+ * Custom hook for managing school comparison state.
+ *
+ * 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 {
-    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,
-  };
+  return useComparisonContext();
 }

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/transform/models/marts/_marts_schema.yml

@@ -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:

pipeline/transform/models/marts/fact_ks4_national_averages.sql

@@ -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