test(e2e): never pick an ISI school for the not-yet-inspected journey (review)

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

backend/app.py

@@ -1048,6 +1048,11 @@ async def get_school_details(request: Request, urn: int):
             "age_range": latest.get("age_range", ""),
             "has_sixth_form": latest.get("has_sixth_form"),
             "nursery_provision": latest.get("nursery_provision"),
+            # What the Ofsted badge may say when there is no inspection record
+            # (audit H4): the inspectorate GIAS names, and whether Ofsted's own
+            # data lists the school at all.
+            "inspectorate": latest.get("inspectorate"),
+            "ofsted_listed": latest.get("ofsted_listed"),
             # The header's Selective flag reads it (lib/schoolFacts).
             "admissions_policy": latest.get("admissions_policy"),
             "status": latest.get("status"),
@@ -1210,6 +1215,9 @@ async def compare_schools(
                     # schools get entrance-test framing, never the distance template.
                     "admissions_policy": convert_to_native(latest.get("admissions_policy")),
                     "has_sixth_form": convert_to_native(latest.get("has_sixth_form")),
+                    # Audit H4: what compare may say when there is no inspection record.
+                    "inspectorate": convert_to_native(latest.get("inspectorate")),
+                    "ofsted_listed": convert_to_native(latest.get("ofsted_listed")),
                     "capacity": convert_to_native(latest.get("capacity")),
                     "gias_total_pupils": convert_to_native(latest.get("gias_total_pupils")),
                     "trust_name": convert_to_native(latest.get("trust_name")),

backend/data_loader.py

@@ -251,6 +251,8 @@ _MAIN_QUERY = text("""
         s.website,
         s.telephone,
         s.nursery_provision,
+        s.inspectorate,
+        s.ofsted_listed,
         foi.current_grade         AS ofsted_grade,
         foi.current_grade_date    AS ofsted_grade_date,
         foi.latest_visit_date     AS ofsted_date,
@@ -376,7 +378,8 @@ for _old, _new in _LEGACY_NAME_REPLACEMENTS:
 _MAIN_QUERY_LEGACY_NAMES = text(_MAIN_QUERY_LEGACY_NAMES)
 
 # Fallback used when marts predate the optional GIAS/location detail columns
-# (telephone, nursery provision, county, parliamentary constituency) — i.e. the
+# (telephone, nursery provision, county, parliamentary constituency, and the
+# inspectorate / Ofsted-listing columns added for audit H4) — i.e. the
 # nightly dbt pipeline hasn't rebuilt the mart yet on this DB. Keeps each column
 # present as NULL so the whole data load degrades gracefully instead of failing.
 _EXTRA_OPTIONAL_COLUMNS = (
@@ -384,6 +387,8 @@ _EXTRA_OPTIONAL_COLUMNS = (
     "nursery_provision",
     "county",
     "parliamentary_constituency",
+    "inspectorate",
+    "ofsted_listed",
 )
 _MAIN_QUERY_NO_EXTRA_COLS = str(_MAIN_QUERY)
 for _col, _tbl in (
@@ -391,6 +396,8 @@ for _col, _tbl in (
     ("nursery_provision", "s"),
     ("county", "l"),
     ("parliamentary_constituency", "l"),
+    ("inspectorate", "s"),
+    ("ofsted_listed", "s"),
 ):
     _src = f"{_tbl}.{_col},"
     assert _src in _MAIN_QUERY_NO_EXTRA_COLS, (

backend/ofsted_codes.py

@@ -39,6 +39,9 @@ def report_card_labels(ofsted: dict) -> dict:
 
 
 def ofsted_page_url(urn: int) -> str:
-    """The school's page on ofsted.gov.uk (all its reports live there —
+    """The school's page on ofsted.gov.uk (all its reports live there — we
-    we never deep-link an individual report)."""
+    never deep-link an individual report). Ofsted's provider pages sit under a
-    return f"https://reports.ofsted.gov.uk/provider/21/{urn}"
+    category (21 primary, 23 secondary, 25 special, 40 FE colleges, …); this
+    address redirects to the right one, where provider/21 404s for every
+    non-primary school."""
+    return f"https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/{urn}"

backend/schemas.py

@@ -578,6 +578,8 @@ SCHOOL_COLUMNS = [
     "ofsted_date",
     "ofsted_framework",
     "ofsted_rc_date",
+    "inspectorate",
+    "ofsted_listed",
     "latitude",
     "longitude",
 ]

backend/tests/test_inspection_status_payload.py

@@ -0,0 +1,63 @@
+"""Audit H4: every payload that feeds an Ofsted badge carries the inspectorate
+GIAS names and whether Ofsted's own data lists the school, so the site never
+says "Not yet inspected" on the strength of missing data."""
+
+import pandas as pd
+import pytest
+from fastapi.testclient import TestClient
+
+
+def _df():
+    base = dict(year=202425, local_authority='Cambridgeshire', local_authority_code=873,
+                postcode='CB1 1AA', latitude=52.2, longitude=0.12, address='1 High St',
+                status='Open', town='Cambridge')
+    return pd.DataFrame([
+        dict(base, urn=136083, school_name='Abbey College Cambridge', phase='Secondary',
+             school_type='Other independent school', inspectorate='ISI', ofsted_listed=False),
+        dict(base, urn=150031, school_name='Abbas and Templecombe Church School', phase='Primary',
+             school_type='Academy converter', inspectorate='Not applicable', ofsted_listed=True),
+    ])
+
+
+@pytest.fixture()
+def client(monkeypatch):
+    from backend import app as app_module
+    from backend import database as database_module
+
+    def _no_db():
+        raise RuntimeError('no database in this test')
+
+    monkeypatch.setattr(app_module, 'load_latest_school_data', _df)
+    monkeypatch.setattr(app_module, 'load_school_data', _df)
+    monkeypatch.setattr(app_module, 'get_supplementary_data', lambda db, urn: {})
+    monkeypatch.setattr(app_module, '_place_registry', None)
+    monkeypatch.setattr(database_module, 'SessionLocal', _no_db)
+    return TestClient(app_module.app, raise_server_exceptions=False)
+
+
+def test_the_list_carries_inspectorate_and_listing(client):
+    resp = client.get('/api/schools?page_size=50')
+    assert resp.status_code == 200, resp.text
+    by_urn = {s['urn']: s for s in resp.json()['schools']}
+    assert by_urn[136083]['inspectorate'] == 'ISI'
+    assert by_urn[150031]['ofsted_listed'] is True
+
+
+def test_the_school_page_carries_them(client):
+    info = client.get('/api/schools/136083').json()['school_info']
+    assert info['inspectorate'] == 'ISI'
+    assert info['ofsted_listed'] is False
+
+
+def test_compare_carries_them(client):
+    body = client.get('/api/compare?urns=136083,150031').json()
+    assert body['comparison']['150031']['school_info']['ofsted_listed'] is True
+    assert body['comparison']['136083']['school_info']['inspectorate'] == 'ISI'
+
+
+def test_a_mart_built_before_the_columns_still_loads():
+    # Missing columns make the main query retry with NULLs (data_loader).
+    from backend import data_loader
+    assert {'inspectorate', 'ofsted_listed'} <= set(data_loader._EXTRA_OPTIONAL_COLUMNS)
+    fallback = str(data_loader._MAIN_QUERY_NO_EXTRA_COLS)
+    assert 'NULL AS inspectorate,' in fallback and 'NULL AS ofsted_listed,' in fallback

backend/tests/test_ofsted_codes.py

@@ -42,4 +42,4 @@ def test_unknown_code_is_skipped_not_crashed():
 
 
 def test_provider_url():
-    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/provider/21/138690"
+    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/138690"

backend/tests/test_supplementary_enrichment.py

@@ -33,7 +33,7 @@ def test_report_card_block_and_provider_url():
     assert block["report_card"]["rc_achievement"]["label"] == "Strong standard"
     assert "rc_safeguarding_met" not in block["report_card"]
     assert block["rc_safeguarding_met"] is True
-    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"
+    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/100140"
 
 
 def test_no_grade_is_carried_past_a_newer_inspection():

docs/ARCHITECTURE.md

@@ -61,6 +61,12 @@ history from the full DataFrame and supplementary data from marts. Comparisons
 batch supplementary queries across selected URNs. Async routes still contain
 synchronous dependency calls; a fully asynchronous database layer is not present.
 
+DfE's official benchmarks live in marts: `fact_ks2_national_averages` and
+`fact_ks4_national_averages` for England, and `fact_ks4_la_averages` (all
+state-funded schools, per LA) for the search rows' "vs LA avg".
+`data_loader.compute_benchmarks` computes further state-school benchmarks from
+our own dataset; they are not DfE figures and are labelled as computed.
+
 ## Frontend boundaries
 
 `app/(frontend)` owns the public root layout and pages. `app/(payload)` owns the

docs/superpowers/specs/2026-10-06-ks4-2023-24-and-la-averages-design.md

@@ -0,0 +1,300 @@
+# 2023/24 Results and DfE LA Averages — Design
+
+**Date:** 2026-10-06
+**Status:** approved design, not yet implemented
+**Scope:** `tap_uk_ees` (KS4 results, KS4 information, new LA stream),
+`safe_numeric`, new `fact_ks4_la_averages` mart, annual EES DAG selector,
+`/api/la-averages`, secondary search rows and map cards
+**Fixes:** audit findings C2 and H2, from the 3 Oct 2026 accuracy audit
+
+## Goal
+
+Show every 2023/24 result and school-information figure DfE published, and
+compare each state-funded secondary school with DfE's own local-authority
+average.
+
+## The problem
+
+### C2: 2023/24 is empty
+
+Bishop Stopford School (137086) shows Attainment 8 60.7 for 2022/23, nothing
+for 2023/24 and 58.7 for 2024/25. DfE's 2023/24 figures are Attainment 8 64.1,
+Progress 8 +1.02 and English and maths grade 4+ 91.7%. 2023/24 is the last year
+DfE published Progress 8 (2024/25 has no KS2 baseline), so the site shows no
+recent Progress 8 for any school. Site-wide, 4,170 listed schools have a DfE
+2023/24 Attainment 8 and 3,384 a Progress 8.
+
+There are three separate causes.
+
+1. **KS4 results.** The 2024/25 release's
+   `202425_performance_tables_schools_final.csv` is a time series: it holds
+   2022/23, 2023/24 and 2024/25 under the current column names, with the right
+   values (Bishop Stopford 2023/24: 64.1, 1.02, 91.7). The 2023/24 release's own
+   `202324_performance_tables_schools_final.csv`, re-issued on 10 March 2026,
+   uses the older names (`t_pupils`, `avg_att8`, `avg_p8score`,
+   `pt_l2basics_94` …). `EESDatasetStream` reads releases in the API's order,
+   newest first, so the old file is read last. Its rows carry none of the
+   declared fields, and target-postgres upserts on the stream's primary key
+   (`append_only = not key_properties` in meltanolabs-target-postgres 0.8.0),
+   so they overwrite the good 2023/24 rows with nulls.
+
+   The two files share the keys of every "Total" row, but 34,254 of the 57,090
+   2023/24 sub-group rows use different labels ("Low prior" against "Low prior
+   attainment"). Those old-label rows sit in `raw.ees_ks4_performance` with
+   null measures. Nothing reads them.
+
+2. **KS4 school information.** 2023/24 information exists only in the 2023/24
+   release, in `202324_information_about_schools_final.csv`, with the older
+   names. `EESKS4InfoStream` declares the newer ones, so prior attainment,
+   SEN percentages, disadvantage gaps and Progress 8 banding are null for
+   2023/24.
+
+3. **KS2 school information.** The 2023/24 file
+   `ks2_school_information_data.csv` uses the declared names, and pupil counts
+   load (school 147411: 818 pupils, 112 eligible). Its percentages are written
+   with a sign (`ptfsm6cla1a = "34%"`). `safe_numeric` accepts only
+   `^-?[0-9]+(\.[0-9]+)?$`, so disadvantaged, EAL, SEN and mobility percentages
+   are null for every school in 2023/24.
+
+DfE published no school-level KS2 information file for 2022/23 (the 2023/24
+release's attainment file carries 2022/23 attainment rows only). Those nulls are
+a gap in the source, not a defect.
+
+### H2: "vs LA avg" uses the wrong average
+
+`/api/la-averages` (`backend/app.py`) takes an unweighted mean of every school
+in the LA with an Attainment 8 score, independent and special schools included.
+Independent schools score low because DfE measures exclude IGCSEs, and special
+schools score low for other reasons, so the average is too low almost
+everywhere. Kensington and Chelsea's "LA avg" is 35.2: the mean of 6 state
+schools (54.9) and 8 independent schools (20.4). DfE's figure is 54.5. Of the
+151 LAs the audit compared, ours was lower in 147, by 7.1 points on average and
+by up to 19.3, so most secondary schools look better than their area.
+
+DfE's LA averages are already in the file the pipeline downloads for the
+England averages: the "summary, all state-funded" data set
+(`data-catalogue/data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv`). Its
+`Local authority` / `All state-funded` / Total rows match DfE's published
+performance-table LA averages (RECTYPE 4) for all 152 LAs in 2024/25, with no
+difference. `EESKs4NationalStream` keeps the England row and discards them.
+"All state-funded" is the same population as the England benchmark the site
+already shows.
+
+Computing the average ourselves from state-funded schools, weighted by pupils,
+was tested and rejected: it differs from DfE's figure by 1.1 points on average
+and is never exact.
+
+## Non-goals
+
+- 2022/23 KS2 school information (DfE published none).
+- An "excludes IGCSEs" note wherever an independent school's Attainment 8
+  appears. This change only stops comparing independent schools with the LA.
+- Showing 2023/24 Progress 8 in the GCSE section's headline. The history section
+  shows it once the data loads; the 2024/25 banner stays true.
+- Updating the fixed EES data-set id when DfE publishes 2025/26. It already
+  feeds the England averages; the LA stream shares it.
+- LA comparisons for other measures or on other pages.
+- Deleting the leftover old-label raw rows automatically.
+
+## The rules
+
+### The newest release owns every year it contains (KS4 results only)
+
+`EESDatasetStream` gets an opt-in class attribute,
+`_newest_release_owns_period: bool = False`. `EESKS4PerformanceStream` sets it
+to `True`. When it is on:
+
+- releases are processed newest first by `time_period` (from the release slug),
+  not in the API's order;
+- the stream records each `time_period` it has emitted;
+- in each older release, rows whose `time_period` a newer release already
+  emitted are dropped, and the stream logs how many it skipped and for which
+  years;
+- years only an older release contains are emitted as before.
+
+The filter is a pure function, testable without a download. Other streams keep
+the current behaviour. A general rule would be wrong: the 2024/25 KS2 file holds
+98,448 of the 955,956 rows the 2023/24 release has for 2023/24.
+
+### KS4 information: old names
+
+`EESKS4InfoStream._column_renames` maps the 2023/24 names onto the declared
+fields:
+
+| 2023/24 column | Declared field |
+|---|---|
+| `t_allks_pupils` | `allks_pupil_count` |
+| `t_allks_boys` | `allks_boys_count` |
+| `t_allks_girls` | `allks_girls_count` |
+| `t_pupils` | `endks4_pupil_count` |
+| `avg_ks2_scaledscore` | `ks2_scaledscore_average` |
+| `pt_sen_with_ehcp` | `sen_with_ehcp_pupil_percent` |
+| `pt_sen` | `sen_pupil_percent` |
+| `pt_sen_no_ehcp` | `sen_no_ehcp_pupil_percent` |
+| `diffn_att8` | `attainment8_diffn` |
+| `diffn_p8mea` | `progress8_diffn` |
+| `p8_banding` | `progress8_banding` |
+
+Newer files contain none of the old names, so they are unaffected.
+
+### `safe_numeric` accepts a trailing `%`
+
+The pattern becomes `^-?[0-9]+(\.[0-9]+)?%?$` and the cast reads
+`rtrim(col, '%')`. A value such as `34%` can only mean 34. Suppression codes
+(`c`, `z`, `x` …) still become null.
+
+### LA averages
+
+A new stream, `ees_ks4_la`, reads the same CSV as `ees_ks4_national` and keeps
+rows where `geographic_level = 'Local authority'`,
+`establishment_type_group = 'All state-funded'`, `breakdown_topic = 'Total'` and
+`breakdown = 'Total'` (case-insensitive, as the national stream compares). It
+emits `time_period`, `old_la_code`, `new_la_code`, `la_name` and the 8 headline
+measures the national stream emits (`_KS4_NATIONAL_COL_MAP`). Primary key:
+(`time_period`, `old_la_code`). The two streams share one download-and-filter
+helper.
+
+`old_la_code` is the GIAS LA code (`local_authority_code`), so schools join on
+the code, not the name. Names match today for every LA DfE publishes; DfE
+publishes no figure for City of London.
+
+### Which schools get a gap
+
+The search row and the map card show "vs LA avg" only when the school has an
+Attainment 8 score, is neither special (`isSpecialSchool`) nor independent
+(`isIndependentSchool`: "independent" in the GIAS type, which covers "Other
+independent school" and "Other independent special school"), and its LA has a
+DfE figure for the year the endpoint serves.
+
+## Delivery
+
+Two PRs, as for C1.
+
+### PR 1: pipeline
+
+- `pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py`: release
+  precedence, KS4 information renames, `ees_ks4_la` stream registered in
+  `discover_streams`, shared national/LA helper.
+- `pipeline/transform/macros/safe_numeric.sql`: trailing `%`.
+- `pipeline/transform/models/staging/`: source `raw.ees_ks4_la` and
+  `stg_ees_ks4_la` (view): `cast(old_la_code as integer) as la_code`,
+  `cast(time_period as integer) as year`, `la_name`, measures via
+  `safe_numeric`, named as in `stg_ees_ks4_national`.
+- `pipeline/transform/models/marts/fact_ks4_la_averages.sql` (table): one row
+  per (`year`, `la_code`) with `la_name` and the columns of
+  `fact_ks4_national_averages`. Schema tests: unique (`year`, `la_code`);
+  `year`, `la_code` and `la_name` not null.
+- Data tests in `pipeline/transform/tests/`:
+  - `assert_ks4_years_have_results`: every year in `stg_ees_ks4` has a non-null
+    Attainment 8 for at least 50% of its rows. DfE's files reach 82% each year;
+    2023/24 loads at 0% today. Pre-2019 years come from the legacy model and are
+    not tested.
+  - `assert_ks2_info_percentages_loaded`: for every year in `stg_ees_ks2` where
+    at least 1,000 rows have `total_pupils`, at least 90% of those rows have
+    `disadvantaged_pct`. DfE's files reach 96–97%. 2022/23 has no pupil counts
+    and is skipped.
+  - `assert_ks4_la_averages_cover_las`: the latest year in
+    `fact_ks4_la_averages` has at least 145 LAs (DfE: 152).
+- `pipeline/dags/school_data_pipeline.py`: the annual EES build selects
+  `stg_ees_ks4_la+`.
+- `docs/ARCHITECTURE.md`: LA averages come from DfE's data set.
+
+### PR 2: backend and UI (after the EES DAG has run on PR 1)
+
+- `backend/models.py`: `Ks4LaAverage` for `marts.fact_ks4_la_averages`.
+- `backend/app.py` `/api/la-averages`: the year is the latest with any school
+  Attainment 8 (as now). It reads that year's rows from the mart and keys each
+  `attainment_8_score` by our LA name, through the `local_authority_code` →
+  `local_authority` pairs in the school data. The response shape is unchanged.
+  No rows for that year, a missing table or a query error give an empty map,
+  logged, never another year's figures and never a computed mean.
+- `nextjs-app/lib/utils.ts`: `isIndependentSchool(school)`.
+- `nextjs-app/components/SecondarySchoolRow.tsx` and
+  `nextjs-app/components/LeafletMapInner.tsx`: the rule in "Which schools get a
+  gap".
+- `e2e/tests/journeys.spec.ts`: the two journeys under Testing.
+
+## Testing
+
+**Extractor (pytest, `pipeline/tests/`, new):**
+
+- precedence: with the flag on, a year in a newer release is emitted once, from
+  the newer release; a year only an older release has is kept; with the flag
+  off, every row passes;
+- releases arriving oldest first are still processed newest first;
+- KS4 information: an old-format row yields `endks4_pupil_count`,
+  `ks2_scaledscore_average`, `progress8_banding` and the rest of the table;
+- LA filter: a small CSV with national, regional, LA and sub-group rows yields
+  one row per LA and year, with the declared fields.
+
+**dbt (local `pgserver`, as for C1):**
+
+- unit test on `stg_ees_ks2`: `34%` → 34, `34` → 34, `c` → null;
+- unit test on `stg_ees_ks4_la` or the mart: codes and years cast, measures
+  carried;
+- the three data tests and the schema tests above;
+- `pipeline/tests/test_dag_selectors.py` passes with the new selector.
+
+**Backend (pytest):**
+
+- the response gives the mart's figure where the fixture's plain mean differs;
+- matching works by code when the mart's `la_name` differs from ours;
+- a mart without the served year, and a missing table, give an empty map.
+
+**Front end (Jest):**
+
+- `isIndependentSchool` for "Other independent school", "Other independent
+  special school", "Academy converter" and null;
+- `SecondarySchoolRow` and the map card show no gap for an independent school
+  and show one for a state school with an LA figure.
+
+**E2E (`journeys.spec.ts`, PR 2):**
+
+- C2: Bishop Stopford (137086) history shows 2023/24 Attainment 8 64.1 and
+  Progress 8 +1.02. These are final figures, so the journey stays stable.
+- H2: a search returning an independent and a state secondary: the independent
+  row has no "vs LA avg", the state row has one. The exact gap is not asserted:
+  DfE's 2025/26 provisional KS4 data is due and would change it.
+
+## Rollout and verification
+
+1. PR 1 merges to staging. Tudor runs `school_data_annual_ees` on staging.
+2. Through the staging API: Bishop Stopford 2023/24 Attainment 8 64.1 and
+   Progress 8 1.02; school 147411 2023/24 disadvantaged 34; the LA mart has 152
+   LAs for 2024/25. If a data test fails, the build stops before search sync;
+   investigate before going further.
+3. PR 2 merges, so the post-merge E2E gate runs against loaded data.
+4. Production, Tudor's decision, in order: promote PR 1, run the EES DAG on
+   production, promote PR 2. If PR 2 arrives first, the endpoint returns an
+   empty map and rows show no gap, never a wrong one.
+5. Optional cleanup, in the PR 1 description for Tudor:
+   `delete from raw.ees_ks4_performance where time_period = '202324' and pupil_count is null`.
+   New-format rows always have `pupil_count` (suppressed values are `c` or `z`,
+   not null), so this removes exactly the old-label leftovers.
+6. Validation on production against DfE's files: every listed school's 2023/24
+   Attainment 8, Progress 8 and English and maths figures match; the 2023/24 KS4
+   and KS2 information fields match; `/api/la-averages` equals DfE for all 152
+   LAs; independent rows show no gap. Then mark C2 and H2 resolved in the audit
+   report.
+
+## Expected visible change
+
+- Secondary history charts and tables run unbroken from 2022/23 to 2024/25, and
+  2023/24 Progress 8 appears for about 3,400 schools.
+- 2023/24 school information (KS2 and KS4) fills in.
+- "vs LA avg" drops for most state secondaries. In Kensington and Chelsea, a
+  school with Attainment 8 60.0 moves from +24.8 to +5.5.
+- Independent schools and City of London schools show no LA gap.
+
+## Risks
+
+- **Data-test thresholds stop a good build.** They were set from DfE's own
+  files (82% and 96–97% against 50% and 90%). A failure means the data changed
+  shape, which is what they are for.
+- **`safe_numeric` is shared by 14 models.** Values written as `n%` were null
+  and become numbers. No DfE column uses `%` for anything but a percentage.
+  The staging EES run is the check.
+- **Precedence drops data an older release holds more completely.** It is
+  opt-in for KS4 results, where both files hold the same 57,090 2023/24 keys.
+- **The fixed EES data-set id goes stale** when 2025/26 is published. The
+  endpoint's year check then gives no gap rather than a mismatched one.

e2e/tests/journeys.spec.ts

@@ -554,6 +554,49 @@ test('a grade confirmed at an ungraded visit says so and is dated by it', async
   await expect(section.getByText('Confirmed at an ungraded inspection, 18 July 2024')).toBeVisible();
 });
 
+test('a further education college shows its Ofsted result, not "Not yet inspected"', async ({ page }) => {
+  // Queen Mary's College (145175) is inspected under Ofsted's further education
+  // framework, which the site did not load: its badge said "Not yet inspected"
+  // (audit H4). Its result may change; the label must never be absent or wrong.
+  const res = await page.request.get('/api/schools/145175');
+  expect(res.ok()).toBeTruthy();
+  await page.goto('/school/145175');
+  const ofsted = page.locator('#ofsted');
+  await expect(ofsted).toBeVisible();
+  await expect(ofsted.getByText('Further education and skills')).toBeVisible();
+  await expect(ofsted).not.toContainText('Not yet inspected');
+});
+
+test('an ISI-inspected school says so instead of "Not yet inspected"', async ({ page }) => {
+  const res = await page.request.get('/api/schools?school_type=independent&phase=secondary&page_size=100');
+  expect(res.ok()).toBeTruthy();
+  const school = ((await res.json()).schools ?? []).find(
+    (s: { inspectorate?: string | null; ofsted_date?: string | null; ofsted_rc_date?: string | null }) =>
+      s.inspectorate === 'ISI' && !s.ofsted_date && !s.ofsted_rc_date);
+  test.skip(!school, 'no ISI school without an Ofsted record here');
+  await page.goto(`/school/${school.urn}`);
+  await expect(page.getByText('Inspected by the Independent Schools Inspectorate (ISI)')).toBeVisible();
+  await expect(page.locator('#ofsted')).not.toContainText('Not yet inspected');
+});
+
+test('"Not yet inspected" appears only for a school Ofsted lists without an inspection', async ({ page }) => {
+  const res = await page.request.get('/api/schools?phase=primary&page_size=500');
+  expect(res.ok()).toBeTruthy();
+  type Row = { urn: number; inspectorate?: string | null; ofsted_listed?: boolean | null; ofsted_date?: string | null; ofsted_rc_date?: string | null; ofsted_grade?: number | null };
+  const schools: Row[] = (await res.json()).schools ?? [];
+  // No Ofsted record, and not an ISI school (whose badge names ISI instead).
+  const uninspected = (s: Row) => !s.ofsted_date && !s.ofsted_rc_date && !s.ofsted_grade && s.inspectorate !== 'ISI';
+  const unlisted = schools.find(s => !s.ofsted_listed && uninspected(s));
+  const listed = schools.find(s => s.ofsted_listed && uninspected(s));
+  test.skip(!listed, 'no listed, uninspected primary on this page');
+  await page.goto(`/school/${listed!.urn}`);
+  await expect(page.locator('#ofsted').getByText('Not yet inspected')).toBeVisible();
+  if (unlisted) {
+    await page.goto(`/school/${unlisted.urn}`);
+    await expect(page.getByText('Not yet inspected')).toHaveCount(0);
+  }
+});
+
 test('school detail page renders name and performance data', async ({ page }) => {
   await searchByName(page, 'primary');
   const firstSchool = schoolLinks(page).first();

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

@@ -69,6 +69,18 @@ const data: Record<string, ComparisonData> = {
 };
 
 describe('CompareOfsted', () => {
+  it('words a school without an Ofsted record from the evidence (audit H4)', () => {
+    const isi = { ...school(4, 'ISI School'), inspectorate: 'ISI' } as School;
+    const listed = { ...school(5, 'New Academy'), ofsted_listed: true } as School;
+    render(<CompareOfsted schools={[isi, listed]} data={{
+      '4': { school_info: isi, yearly_data: [] } as unknown as ComparisonData,
+      '5': { school_info: listed, yearly_data: [] } as unknown as ComparisonData,
+    }} />);
+    expect(screen.getAllByText('Inspected by ISI').length).toBeGreaterThan(0);
+    expect(screen.getAllByText('Not yet inspected').length).toBeGreaterThan(0);
+    expect(screen.queryByText(/No inspection outcome in our dataset/)).toBeNull();
+  });
+
   it('renders the three regimes without inventing an overall grade for report cards', () => {
     render(<CompareOfsted schools={schools} data={data} />);
 

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

@@ -0,0 +1,20 @@
+import { render, screen } from '@testing-library/react';
+import { InspectionNotice } from '@/components/school/InspectionNotice';
+
+it('names ISI and links its reports for an ISI school (audit H4)', () => {
+  render(<InspectionNotice school={{ urn: 136083, inspectorate: 'ISI', ofsted_listed: false }} />);
+  expect(screen.getByText('Inspected by the Independent Schools Inspectorate (ISI)')).toBeInTheDocument();
+  expect(screen.getByRole('link', { name: /ISI reports/ })).toHaveAttribute('href', 'https://www.isi.net/reports/');
+});
+
+it('says "Not yet inspected" for a school Ofsted lists without an inspection', () => {
+  render(<InspectionNotice school={{ urn: 150031, inspectorate: 'Not applicable', ofsted_listed: true }} />);
+  expect(screen.getByText('Not yet inspected')).toBeInTheDocument();
+  expect(screen.getByRole('link', { name: /Ofsted reports/ })).toHaveAttribute(
+    'href', 'https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/150031');
+});
+
+it('renders nothing without inspection data, including an unknown listing', () => {
+  const { container } = render(<InspectionNotice school={{ urn: 1, inspectorate: null, ofsted_listed: null }} />);
+  expect(container).toBeEmptyDOMElement();
+});

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

@@ -82,3 +82,14 @@ it('prints an ungraded outcome when there is no grade (Oakgrove)', () => {
   expect(screen.getByText('No overall grade')).toBeInTheDocument();
   expect(screen.getByText(/Ungraded inspection, 13 November 2024: Standards maintained/)).toBeInTheDocument();
 });
+
+it('labels a further education record and prints its short inspection (audit H4)', () => {
+  renderSection({
+    framework: 'Further education and skills',
+    current_grade: { grade: 2, date: '2019-03-05', basis: 'graded' },
+    latest_visit: { date: '2023-02-01', kind: 'short', outcome: null },
+  });
+  expect(screen.getByText('Further education and skills')).toBeInTheDocument();
+  expect(screen.getByText('Good')).toBeInTheDocument();
+  expect(screen.getByText('Latest visit: Short inspection, 1 February 2023')).toBeInTheDocument();
+});

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

@@ -52,3 +52,8 @@ describe('SchoolRow shares the school page flags', () => {
     expect(screen.getByText("Boys' school")).toBeInTheDocument();
   });
 });
+
+it('renders no Ofsted badge for a school with no inspection data (audit H4)', () => {
+  const { container } = render(<SchoolRow school={{ ...base, ofsted_grade: null, ofsted_date: null, ofsted_listed: false, inspectorate: null }} />);
+  expect(container.textContent).not.toMatch(/Not yet inspected|Inspected/);
+});

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

@@ -19,6 +19,7 @@ import {
   summariseAdmissions,
   summariseReportCard,
   verdict,
+  noInspectionLabel,
 } from '@/lib/compareLogic';
 import type { OfstedInspection, SchoolAdmissions } from '@/lib/types';
 
@@ -359,3 +360,14 @@ describe('basket limits per comparison group', () => {
     expect(kept).toEqual([0, 1, 2, 3, 4, 7, 8, 9]);
   });
 });
+
+it('shows an FE report card with only a safeguarding result as a report card (audit H4)', () => {
+  const display = ofstedDisplay(ofsted({ rc_inspection_date: '2026-05-12', rc_safeguarding_met: true, report_card: {} }));
+  expect(display.kind).toBe('report_card');
+});
+
+it('words the no-record compare cell from the evidence', () => {
+  expect(noInspectionLabel({ inspectorate: 'ISI' })).toBe('Inspected by ISI');
+  expect(noInspectionLabel({ ofsted_listed: true })).toBe('Not yet inspected');
+  expect(noInspectionLabel({ ofsted_listed: null })).toBe('No inspection in our dataset');
+});

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

@@ -1,4 +1,4 @@
-import { gradeSourceLine, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
+import { gradeSourceLine, hasReportCard, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
 import type { OfstedInspection } from '@/lib/types';
 
 const base = { report_card: {} } as unknown as OfstedInspection;
@@ -45,3 +45,12 @@ describe('showLatestVisitLine', () => {
       latest_visit: { date: '2025-06-17', kind: 'graded', outcome: null } })).toBe(false);
   });
 });
+
+it('names a short inspection as Ofsted does', () => {
+  expect(latestVisitLine({ date: '2023-02-01', kind: 'short', outcome: null })).toBe('Short inspection, 1 February 2023');
+});
+
+it('treats a report card with only a safeguarding result as a report card (FE)', () => {
+  expect(hasReportCard({ rc_inspection_date: '2026-05-12', report_card: {} })).toBe(true);
+  expect(hasReportCard({ rc_inspection_date: null, report_card: {} })).toBe(false);
+});

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

@@ -148,40 +148,40 @@ describe('ofstedLegacyAreas', () => {
 describe('buildOfstedListBadge', () => {
   it('returns grade word + year for OEIF Outstanding', () => {
     const badge = buildOfstedListBadge({ ofsted_grade: 1, ofsted_grade_date: '2023-11-15', ofsted_date: '2023-11-15', ofsted_framework: 'OEIF' });
-    expect(badge.label).toBe('Outstanding · 2023');
+    expect(badge!.label).toBe('Outstanding · 2023');
-    expect(badge.cssClass).toBe('ofsted1');
+    expect(badge!.cssClass).toBe('ofsted1');
   });
 
   it('returns grade word for each OEIF grade', () => {
-    expect(buildOfstedListBadge({ ofsted_grade: 2, ofsted_grade_date: '2022-05-01' }).label).toBe('Good · 2022');
+    expect(buildOfstedListBadge({ ofsted_grade: 2, ofsted_grade_date: '2022-05-01' })!.label).toBe('Good · 2022');
-    expect(buildOfstedListBadge({ ofsted_grade: 3, ofsted_grade_date: '2021-01-01' }).label).toBe('Req. Improvement · 2021');
+    expect(buildOfstedListBadge({ ofsted_grade: 3, ofsted_grade_date: '2021-01-01' })!.label).toBe('Req. Improvement · 2021');
-    expect(buildOfstedListBadge({ ofsted_grade: 4, ofsted_grade_date: '2020-03-01' }).label).toBe('Inadequate · 2020');
+    expect(buildOfstedListBadge({ ofsted_grade: 4, ofsted_grade_date: '2020-03-01' })!.label).toBe('Inadequate · 2020');
   });
 
   it('prints a grade without a year when its date is missing', () => {
     const badge = buildOfstedListBadge({ ofsted_grade: 2, ofsted_grade_date: null, ofsted_date: '2025-01-01' });
-    expect(badge.label).toBe('Good');
+    expect(badge!.label).toBe('Good');
-    expect(badge.cssClass).toBe('ofsted2');
+    expect(badge!.cssClass).toBe('ofsted2');
   });
 
   it('dates a grade by the inspection that awarded it, not the latest visit', () => {
     // Washwood Heath: Good from a March 2020 graded inspection; latest visit
     // an ungraded one in May 2025 (audit M1).
     const badge = buildOfstedListBadge({ ofsted_grade: 2, ofsted_grade_date: '2020-03-03', ofsted_date: '2025-05-21' });
-    expect(badge.label).toBe('Good · 2020');
+    expect(badge!.label).toBe('Good · 2020');
   });
 
   it('shows Inspected for a latest inspection that gave no grade (Rabbsfarm)', () => {
     // Audit C1: this used to read "Good · 2025", from a 2020 ungraded visit.
     const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_grade_date: null, ofsted_date: '2025-06-17' });
-    expect(badge.label).toBe('Inspected · 2025');
+    expect(badge!.label).toBe('Inspected · 2025');
-    expect(badge.cssClass).toBe('ofstedInspected');
+    expect(badge!.cssClass).toBe('ofstedInspected');
   });
 
   it('returns a Report Card badge when ofsted_rc_date is present', () => {
     const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_rc_date: '2026-02-03' });
-    expect(badge.label).toBe('Report Card · 2026');
+    expect(badge!.label).toBe('Report Card · 2026');
-    expect(badge.cssClass).toBe('ofstedRc');
+    expect(badge!.cssClass).toBe('ofstedRc');
   });
 
   it('a report card wins over a carried-forward legacy grade', () => {
@@ -194,34 +194,46 @@ describe('buildOfstedListBadge', () => {
       ofsted_framework: 'Schools - S5',
       ofsted_rc_date: '2026-02-03',
     });
-    expect(badge.label).toBe('Report Card · 2026');
+    expect(badge!.label).toBe('Report Card · 2026');
-    expect(badge.cssClass).toBe('ofstedRc');
+    expect(badge!.cssClass).toBe('ofstedRc');
   });
 
   it('returns an "Inspected" badge for an OEIF inspection with no overall grade (post-Sept-2024)', () => {
     // Inspected after Sept 2024: inspection on record (date + framework) but
     // Ofsted no longer issues an overall grade. Must NOT read as "Not yet inspected".
     const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_date: '2024-11-01', ofsted_framework: 'OEIF' });
-    expect(badge.label).toBe('Inspected · 2024');
+    expect(badge!.label).toBe('Inspected · 2024');
-    expect(badge.cssClass).toBe('ofstedInspected');
+    expect(badge!.cssClass).toBe('ofstedInspected');
   });
 
   it('returns an "Inspected" badge without a year when the grade is missing and date is absent but a record exists', () => {
     const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_date: null, ofsted_framework: 'OEIF' });
-    expect(badge.label).toBe('Inspected');
+    expect(badge!.label).toBe('Inspected');
-    expect(badge.cssClass).toBe('ofstedInspected');
+    expect(badge!.cssClass).toBe('ofstedInspected');
   });
 
-  it('returns pending badge when no grade and no inspection on record', () => {
+  it('says "Not yet inspected" only when Ofsted lists the school uninspected (audit H4)', () => {
-    const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_date: null, ofsted_framework: null });
+    const badge = buildOfstedListBadge({ ofsted_grade: null, ofsted_date: null, ofsted_listed: true });
-    expect(badge.label).toBe('Not yet inspected');
+    expect(badge).toEqual({ label: 'Not yet inspected', cssClass: 'ofstedPending' });
-    expect(badge.cssClass).toBe('ofstedPending');
   });
 
-  it('returns pending badge when all fields are undefined', () => {
+  it('names ISI for a school GIAS says ISI inspects', () => {
-    const badge = buildOfstedListBadge({});
+    expect(buildOfstedListBadge({ ofsted_date: null, inspectorate: 'ISI', ofsted_listed: false }))
-    expect(badge.label).toBe('Not yet inspected');
+      .toEqual({ label: 'Inspected by ISI', cssClass: 'ofstedPending' });
-    expect(badge.cssClass).toBe('ofstedPending');
+  });
+
+  it('an Ofsted record wins over ISI', () => {
+    expect(buildOfstedListBadge({ ofsted_grade: 2, ofsted_grade_date: '2022-03-01', inspectorate: 'ISI' })!.label)
+      .toBe('Good · 2022');
+  });
+
+  it('gives no badge without any inspection data', () => {
+    expect(buildOfstedListBadge({ ofsted_grade: null, ofsted_date: null, ofsted_framework: null })).toBeNull();
+    expect(buildOfstedListBadge({ ofsted_listed: null, inspectorate: 'Not applicable' })).toBeNull();
+  });
+
+  it('gives no badge when all fields are undefined (audit H4)', () => {
+    expect(buildOfstedListBadge({})).toBeNull();
   });
 });
 

nextjs-app/components/LeafletMapInner.tsx

@@ -104,7 +104,7 @@ function cardHtml(school: School, ctx: CardContext): string {
     ? `${metric ? ' · ' : ''}${school.total_pupils.toLocaleString('en-GB')} pupils` : '';
   return `<div class="sc-card">
   <div class="sc-card-head"><a class="sc-card-name" href="${href}">${escapeHtml(school.school_name)}</a>${distance}</div>
-  <div class="sc-card-tags"><span class="sc-ofsted sc-${badge.cssClass}">${escapeHtml(badge.label)}</span>${type}</div>
+  <div class="sc-card-tags">${badge ? `<span class="sc-ofsted sc-${badge.cssClass}">${escapeHtml(badge.label)}</span>` : ''}${type}</div>
   ${metric || pupils ? `<div class="sc-card-metric">${metric}${pupils}</div>` : ''}
   <div class="sc-card-actions">
     <a class="btn btn-tertiary btn-sm" href="${href}">View</a>

nextjs-app/components/SchoolRow.tsx

@@ -66,9 +66,11 @@ export function SchoolRow({
           <a href={schoolUrl(school.urn, school.school_name)} className={styles.schoolName}>
             {school.school_name}
           </a>
+          {ofstedBadge && (
             <span className={`${styles.ofstedBadge} ${styles[ofstedBadge.cssClass]}`}>
               {ofstedBadge.label}
             </span>
+          )}
         </div>
 
         {/* Line 2: Context tags */}

nextjs-app/components/SecondarySchoolRow.tsx

@@ -67,9 +67,11 @@ export function SecondarySchoolRow({
           <a href={schoolUrl(school.urn, school.school_name)} className={styles.schoolName}>
             {school.school_name}
           </a>
+          {ofstedBadge && (
             <span className={`${styles.ofstedBadge} ${styles[ofstedBadge.cssClass]}`}>
               {ofstedBadge.label}
             </span>
+          )}
         </div>
 
         {/* Line 2: Context tags */}

nextjs-app/components/compare/CompareAtAGlance.tsx

@@ -14,6 +14,7 @@ import {
   summariseAdmissions,
   verdict,
   type ReportCardSummary,
+  noInspectionLabel,
 } from '@/lib/compareLogic';
 import type { Benchmarks, ComparisonData, NationalAverages, School } from '@/lib/types';
 import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';
@@ -91,7 +92,7 @@ export function CompareAtAGlance({
                   No overall grade
                 </span>
               )}
-              {display.kind === 'none' && <span className={s.small}>No inspection in our dataset</span>}
+              {display.kind === 'none' && <span className={s.small}>{noInspectionLabel(data[String(school.urn)]?.school_info ?? school)}</span>}
             </Cell>
           );
         })}

nextjs-app/components/compare/CompareOfsted.tsx

@@ -12,6 +12,7 @@ import {
   ofstedDisplay,
   rcAreaLabel,
   type OfstedDisplay,
+  noInspectionLabel,
 } from '@/lib/compareLogic';
 import { gradeSourceLine } from '@/lib/ofstedStatus';
 import type { ComparisonData, OfstedCurrentGrade, OfstedInspection, School } from '@/lib/types';
@@ -41,9 +42,11 @@ function yearsSince(iso: string | null): number | null {
   return (Date.now() - d.getTime()) / (365.25 * 24 * 3600 * 1000);
 }
 
-function ResultCell({ display, current }: { display: OfstedDisplay; current: OfstedCurrentGrade | null }) {
+function ResultCell({ display, current, school }: {
+  display: OfstedDisplay; current: OfstedCurrentGrade | null; school: School;
+}) {
   if (display.kind === 'none') {
-    return <span className={s.small}>No inspection outcome in our dataset</span>;
+    return <span className={s.small}>{noInspectionLabel(school)}</span>;
   }
   if (display.kind === 'report_card') {
     return (
@@ -175,7 +178,7 @@ export function CompareOfsted({
         <Measure label="Result">
         {schools.map((school, i) => (
           <Cell key={school.urn} school={school} index={i}>
-            <ResultCell display={displays[i]} current={data[String(school.urn)]?.ofsted?.current_grade ?? null} />
+            <ResultCell display={displays[i]} current={data[String(school.urn)]?.ofsted?.current_grade ?? null} school={data[String(school.urn)]?.school_info ?? school} />
           </Cell>
         ))}
         </Measure>
@@ -213,7 +216,7 @@ export function CompareOfsted({
                   schoolName={school.school_name}
                 />
               ) : (
-                <span className={s.small}>No inspection in our dataset</span>
+                <span className={s.small}>{noInspectionLabel(data[String(school.urn)]?.school_info ?? school)}</span>
               )}
             </Cell>
           );
@@ -228,7 +231,7 @@ export function CompareOfsted({
         {schools.map((school, i) => {
           const url =
             data[String(school.urn)]?.ofsted?.ofsted_page_url ??
-            `https://reports.ofsted.gov.uk/provider/21/${school.urn}`;
+            `https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/${school.urn}`;
           return (
             <Cell key={school.urn} school={school} index={i}>
               {/* Short visible label to save space on mobile (the coloured

nextjs-app/components/school/InspectionNotice.tsx

@@ -0,0 +1,46 @@
+/**
+ * InspectionNotice — the school page's inspection section when there is no
+ * Ofsted inspection record (audit H4): the inspectorate GIAS names (ISI), or
+ * Ofsted's own "not yet inspected" when its data lists the school. Nothing
+ * otherwise: no claim the data cannot support. Server component.
+ */
+
+import type { School } from '@/lib/types';
+import { Section, sectionStyles as styles } from './sectionShared';
+
+const OFSTED_REPORTS = 'https://reports.ofsted.gov.uk/inspection-reports/find-inspection-report/provider/ELS/';
+const ISI_REPORTS = 'https://www.isi.net/reports/';
+
+export function InspectionNotice({ school }: { school: Pick<School, 'urn' | 'inspectorate' | 'ofsted_listed'> }) {
+  if (school.inspectorate === 'ISI') {
+    return (
+      <Section id="ofsted">
+        <h2 className={styles.sectionTitle}>
+          Inspection
+          <a href={ISI_REPORTS} target="_blank" rel="noopener noreferrer" className={styles.ofstedReportLink}
+             data-umami-event="external_link_clicked" data-umami-event-target="isi">
+            ISI reports ↗
+          </a>
+        </h2>
+        <p className={styles.ofstedDisclaimer}>Inspected by the Independent Schools Inspectorate (ISI)</p>
+      </Section>
+    );
+  }
+  if (school.ofsted_listed) {
+    return (
+      <Section id="ofsted">
+        <h2 className={styles.sectionTitle}>
+          Ofsted Rating
+          <a href={`${OFSTED_REPORTS}${school.urn}`} target="_blank" rel="noopener noreferrer"
+             className={styles.ofstedReportLink} data-umami-event="external_link_clicked" data-umami-event-target="ofsted">
+            Ofsted reports ↗
+          </a>
+        </h2>
+        <div className={styles.ofstedHeader}>
+          <span className={styles.ofstedGrade}>Not yet inspected</span>
+        </div>
+      </Section>
+    );
+  }
+  return null;
+}

nextjs-app/components/school/OfstedSection.tsx

@@ -12,7 +12,7 @@
  */
 
 import { ofstedDisplay } from '@/lib/compareLogic';
-import { formatOfstedDate, gradeSourceLine, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
+import { FE_FRAMEWORK, formatOfstedDate, gradeSourceLine, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
 import type { OfstedInspection } from '@/lib/types';
 import { Section, sectionStyles as styles } from './sectionShared';
 
@@ -71,6 +71,10 @@ export function OfstedSection({
         </a>
       </h2>
 
+      {ofsted.framework === FE_FRAMEWORK && (
+        <p className={styles.ofstedDisclaimer}>{FE_FRAMEWORK}</p>
+      )}
+
       {isReportCard ? (
         /* ── New Report Card layout ── */
         <>

nextjs-app/components/school/PrimarySchoolSections.tsx

@@ -19,6 +19,8 @@ import type {
 import { ofstedLegacyAreas } from '@/lib/utils';
 import type { SchoolFlags } from '@/lib/schoolSections';
 import { OfstedSection } from './OfstedSection';
+import { InspectionNotice } from './InspectionNotice';
+import { hasReportCard } from '@/lib/ofstedStatus';
 import { ResultsSection } from './ResultsSection';
 import { AdmissionsSection } from './AdmissionsSection';
 import { DistanceSection } from './DistanceSection';
@@ -54,7 +56,8 @@ export function PrimarySchoolSections({
   const primaryAvg = nationalAvg?.primary ?? {};
   const secondaryAvg = nationalAvg?.secondary ?? {};
 
-  const isReportCard = !!(ofsted?.report_card && Object.keys(ofsted.report_card).length > 0);
+  // By date as well as area labels: an FE report card has only a safeguarding result (audit H4).
+  const isReportCard = !!ofsted && hasReportCard(ofsted);
   // Report cards are dated by their own inspection (rc_inspection_date);
   // anything else by the school's latest visit, never the older inspection a
   // grade may come from.
@@ -71,7 +74,7 @@ export function PrimarySchoolSections({
 
   return (
     <>
-      {ofsted && (
+      {ofsted ? (
         <OfstedSection
           ofsted={ofsted}
           urn={schoolInfo.urn}
@@ -80,7 +83,7 @@ export function PrimarySchoolSections({
           oeifAllSameGrade={oeifAllSameGrade}
           oeifAreas={oeifAreas}
         />
-      )}
+      ) : <InspectionNotice school={schoolInfo} />}
 
       {flags.hasAnyResults && flags.latestResults && (
         <ResultsSection

nextjs-app/components/school/SecondarySchoolSections.tsx

@@ -19,6 +19,8 @@ import type {
 import { ofstedLegacyAreas } from '@/lib/utils';
 import type { SecondaryFlags } from '@/lib/schoolSections';
 import { OfstedSection } from './OfstedSection';
+import { InspectionNotice } from './InspectionNotice';
+import { hasReportCard } from '@/lib/ofstedStatus';
 import { GcseSection } from './GcseSection';
 import { DestinationsSection } from './DestinationsSection';
 import { Post16DestinationsSection } from './Post16DestinationsSection';
@@ -60,7 +62,8 @@ export function SecondarySchoolSections({
 }: SecondarySchoolSectionsProps) {
   const secondaryAvg = nationalAvg?.secondary ?? {};
 
-  const isReportCard = !!(ofsted?.report_card && Object.keys(ofsted.report_card).length > 0);
+  // By date as well as area labels: an FE report card has only a safeguarding result (audit H4).
+  const isReportCard = !!ofsted && hasReportCard(ofsted);
   const ofstedInspectedDate = isReportCard
     ? ofsted?.rc_inspection_date ?? null
     : ofsted?.latest_visit?.date ?? null;
@@ -74,7 +77,7 @@ export function SecondarySchoolSections({
 
   return (
     <div className={styles.secondaryScope}>
-      {ofsted && (
+      {ofsted ? (
         <OfstedSection
           ofsted={ofsted}
           urn={schoolInfo.urn}
@@ -83,7 +86,7 @@ export function SecondarySchoolSections({
           oeifAllSameGrade={oeifAllSameGrade}
           oeifAreas={oeifAreas}
         />
-      )}
+      ) : <InspectionNotice school={schoolInfo} />}
 
       {flags.hasResults && flags.latestResults && (
         <GcseSection

nextjs-app/lib/compareLogic.ts

@@ -10,7 +10,8 @@
  * methodology instead of thresholding point estimates.
  */
 
-import type { OfstedInspection, SchoolAdmissions } from './types';
+import { hasReportCard } from './ofstedStatus';
+import type { OfstedInspection, School, SchoolAdmissions } from './types';
 
 // ---------------------------------------------------------------------------
 // Verdicts against an anchor (England average or state-school benchmark)
@@ -104,6 +105,16 @@ export type OfstedDisplay =
   | { kind: 'no_overall_grade' }
   | { kind: 'report_card'; summary: ReportCardSummary };
 
+/**
+ * The compare cell when a school has no Ofsted record (audit H4): ISI per GIAS,
+ * Ofsted's own "not yet inspected", or plainly that we hold nothing.
+ */
+export function noInspectionLabel(school: Pick<School, 'inspectorate' | 'ofsted_listed'>): string {
+  if (school.inspectorate === 'ISI') return 'Inspected by ISI';
+  if (school.ofsted_listed) return 'Not yet inspected';
+  return 'No inspection in our dataset';
+}
+
 export function ofstedDisplay(
   ofsted: OfstedInspection | null | undefined,
 ): OfstedDisplay {
@@ -111,7 +122,7 @@ export function ofstedDisplay(
 
   // A report card is the newest inspection format; when present it wins —
   // never derive or prefer an overall grade alongside it.
-  if (ofsted.report_card && Object.keys(ofsted.report_card).length > 0) {
+  if (hasReportCard(ofsted)) {
     return { kind: 'report_card', summary: summariseReportCard(ofsted) };
   }
 

nextjs-app/lib/ofstedStatus.ts

@@ -17,8 +17,22 @@ const VISIT_KIND: Record<OfstedLatestVisit['kind'], string> = {
   report_card: 'Report card inspection',
   graded: 'Graded inspection',
   ungraded: 'Ungraded inspection',
+  short: 'Short inspection',
 };
 
+/** fact_ofsted_latest.framework for Ofsted's further education and skills records (audit H4). */
+export const FE_FRAMEWORK = 'Further education and skills';
+
+/**
+ * A report card is in force. Checked by its date as well as its area labels:
+ * an FE report card carries a safeguarding result and no school areas.
+ */
+export function hasReportCard(
+  ofsted: Pick<OfstedInspection, 'rc_inspection_date' | 'report_card'>,
+): boolean {
+  return !!ofsted.rc_inspection_date || Object.keys(ofsted.report_card ?? {}).length > 0;
+}
+
 /** "Graded inspection, 6 July 2016" or "Confirmed at an ungraded inspection, 14 March 2023". */
 export function gradeSourceLine(current: OfstedCurrentGrade): string {
   const when = formatOfstedDate(current.date);

nextjs-app/lib/types.ts

@@ -86,6 +86,10 @@ export interface School {
   /** The school's latest inspection of any kind. */
   ofsted_date?: string | null;
   ofsted_framework?: string | null;
+  /** The inspectorate GIAS names ("ISI", "Ofsted", "Not applicable"). Audit H4. */
+  inspectorate?: string | null;
+  /** Whether Ofsted's own data lists the school, inspected or not. Audit H4. */
+  ofsted_listed?: boolean | null;
 }
 
 // ============================================================================
@@ -93,7 +97,8 @@ export interface School {
 // ============================================================================
 
 export interface OfstedInspection {
-  framework: 'OEIF' | 'ReportCard' | null;
+  /** Ofsted's event grouping (e.g. "Schools - S5"), or "Further education and skills" for FE records (audit H4). */
+  framework: string | null;
   inspection_date: string | null;
   /** Start date of the report-card inspection itself (Nov 2025+); null otherwise. */
   rc_inspection_date?: string | null;
@@ -143,7 +148,7 @@ export interface OfstedCurrentGrade {
 
 export interface OfstedLatestVisit {
   date: string;
-  kind: 'report_card' | 'graded' | 'ungraded';
+  kind: 'report_card' | 'graded' | 'ungraded' | 'short';
   outcome: string | null;
 }
 

nextjs-app/lib/utils.ts

@@ -678,7 +678,9 @@ export interface OfstedListBadge {
  *     or an ungraded visit whose outcome names no grade): "Inspected · YYYY",
  *     dated by the latest visit, so a school never reads as both inspected and
  *     "Not yet inspected"
- *   - No inspection on record: "Not yet inspected" in grey
+ *   - No inspection on record (audit H4): "Inspected by ISI" when GIAS names
+ *     ISI; "Not yet inspected" only when Ofsted's own data lists the school;
+ *     otherwise no badge, never a claim the data cannot support.
  */
 export function buildOfstedListBadge(school: {
   ofsted_grade?: 1 | 2 | 3 | 4 | null;
@@ -686,7 +688,9 @@ export function buildOfstedListBadge(school: {
   ofsted_date?: string | null;
   ofsted_framework?: string | null;
   ofsted_rc_date?: string | null;
-}): OfstedListBadge {
+  inspectorate?: string | null;
+  ofsted_listed?: boolean | null;
+}): OfstedListBadge | null {
   // A report card wins over any carried-forward legacy grade — signalled by
   // ofsted_rc_date. ofsted_framework is the raw event grouping ("Schools -
   // S5"), never "ReportCard", so it can't detect report cards.
@@ -719,7 +723,9 @@ export function buildOfstedListBadge(school: {
     return { label: `Inspected${visitYear ? ` · ${visitYear}` : ''}`, cssClass: 'ofstedInspected' };
   }
 
-  return { label: 'Not yet inspected', cssClass: 'ofstedPending' };
+  if (school.inspectorate === 'ISI') return { label: 'Inspected by ISI', cssClass: 'ofstedPending' };
+  if (school.ofsted_listed) return { label: 'Not yet inspected', cssClass: 'ofstedPending' };
+  return null;
 }
 
 // ============================================================================

pipeline/dags/school_data_pipeline.py

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

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

@@ -0,0 +1,47 @@
+"""KS4 school information: the fields the stream declares, and the older
+names DfE used for them.
+
+2023/24 information exists only in the 2023/24 release, whose
+202324_information_about_schools_final.csv (re-issued 10 March 2026) uses the
+older names. Without the renames every 2023/24 field loaded as null (audit C2).
+Newer files contain none of the older names, so the renames leave them alone.
+
+Free of the Singer SDK so CI's pytest can load it.
+"""
+
+# Declared Singer fields besides the required time_period and school_urn.
+KS4_INFO_FIELDS = (
+    "school_laestab",
+    "school_name",
+    "establishment_type_group",
+    "reldenom",
+    "admpol_pt",
+    "egender",
+    "agerange",
+    "allks_pupil_count",
+    "allks_boys_count",
+    "allks_girls_count",
+    "endks4_pupil_count",
+    "ks2_scaledscore_average",
+    "sen_with_ehcp_pupil_percent",
+    "sen_pupil_percent",
+    "sen_no_ehcp_pupil_percent",
+    "attainment8_diffn",
+    "progress8_diffn",
+    "progress8_banding",
+)
+
+# 2023/24 column name → declared field.
+KS4_INFO_RENAMES = {
+    "t_allks_pupils": "allks_pupil_count",
+    "t_allks_boys": "allks_boys_count",
+    "t_allks_girls": "allks_girls_count",
+    "t_pupils": "endks4_pupil_count",
+    "avg_ks2_scaledscore": "ks2_scaledscore_average",
+    "pt_sen_with_ehcp": "sen_with_ehcp_pupil_percent",
+    "pt_sen": "sen_pupil_percent",
+    "pt_sen_no_ehcp": "sen_no_ehcp_pupil_percent",
+    "diffn_att8": "attainment8_diffn",
+    "diffn_p8mea": "progress8_diffn",
+    "p8_banding": "progress8_banding",
+}

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

@@ -0,0 +1,56 @@
+"""DfE's KS4 "summary, all state-funded" data set (Key stage 4 performance).
+
+One CSV holds England, regional and local-authority headline rows for every
+year since 2018/19. The England stream (ees_ks4_national) and the LA stream
+(ees_ks4_la) both read it. Its LA rows match DfE's published performance-table
+LA averages exactly (audit H2). Suppressed values ('z', 'x') become NULL in
+dbt; Progress 8 is 'z' in years with no KS2 baseline (2024/25): DfE policy,
+not missing data.
+
+Free of the Singer SDK so CI's pytest can load it.
+"""
+from __future__ import annotations
+
+import pandas as pd
+
+KS4_SUMMARY_CSV_URL = (
+    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
+    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
+)
+
+# CSV column → Singer field: the same 8 headline measures at every level.
+KS4_HEADLINE_COL_MAP = {
+    "attainment8_average":    "attainment_8_score",
+    "progress8_average":      "progress_8_score",
+    "engmath_94_percent":     "english_maths_standard_pass_pct",
+    "engmath_95_percent":     "english_maths_strong_pass_pct",
+    "ebacc_entering_percent": "ebacc_entry_pct",
+    "ebacc_94_percent":       "ebacc_standard_pass_pct",
+    "ebacc_95_percent":       "ebacc_strong_pass_pct",
+    "ebacc_aps_average":      "ebacc_avg_score",
+}
+
+
+def headline_rows(df: pd.DataFrame, geographic_level: str) -> pd.DataFrame:
+    """All-pupil rows for all state-funded schools at one geographic level
+    ("National" or "Local authority"). Column names are lower-cased first;
+    a filter column the file lacks is not applied."""
+    df = df.copy()
+    df.columns = [c.strip().lower() for c in df.columns]
+    for col, want in (
+        ("geographic_level", geographic_level),
+        ("establishment_type_group", "All state-funded"),
+        ("breakdown_topic", "Total"),
+        ("breakdown", "Total"),
+    ):
+        if col in df.columns:
+            df = df[df[col].str.strip().str.lower() == want.lower()]
+    return df
+
+
+def headline_record(row: pd.Series, keys: tuple[str, ...]) -> dict[str, str]:
+    """A Singer record: the identifying columns, then the headline measures."""
+    record = {key: str(row.get(key, "")).strip() for key in keys}
+    for csv_col, field in KS4_HEADLINE_COL_MAP.items():
+        record[field] = str(row.get(csv_col, "")).strip()
+    return record

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

@@ -0,0 +1,59 @@
+"""Which release a row comes from when DfE re-publishes a year.
+
+DfE re-publishes earlier years inside later releases: the 2024/25 KS4 results
+file holds 2022/23, 2023/24 and 2024/25. The 2023/24 release's own file,
+re-issued on 10 March 2026 under older column names, was read after it and
+overwrote every 2023/24 row with blanks (audit C2). A stream that opts in
+treats the newest release as the authority for every year it contains.
+
+Free of the Singer SDK so CI's pytest, which installs only the backend's
+requirements, can load it.
+"""
+from __future__ import annotations
+
+import re
+
+import pandas as pd
+
+_SLUG_YEAR = re.compile(r"^(\d{4})-(\d{2})(?:-|$)")
+
+
+def slug_to_time_period(slug: str) -> str | None:
+    """A release slug's academic year as a time_period: '2022-23' → '202223'.
+
+    Suffixed slugs ('2024-25-revised', '2025-26-provisional') give the same
+    year, so newest_first places them by year; read as unknown, a revised
+    release went last and lost its year to the first release.
+    """
+    match = _SLUG_YEAR.match(slug or "")
+    return match.group(1) + match.group(2) if match else None
+
+
+def newest_first(releases: list[dict]) -> list[dict]:
+    """Releases by time_period, newest first. A release whose time_period is
+    unknown goes last, in the order given."""
+    dated = [r for r in releases if r.get("time_period")]
+    undated = [r for r in releases if not r.get("time_period")]
+    return sorted(dated, key=lambda r: r["time_period"], reverse=True) + undated
+
+
+def periods_in(df: pd.DataFrame) -> set[str]:
+    """The years a release's rows cover."""
+    if "time_period" not in df.columns:
+        return set()
+    return set(df["time_period"].astype(str).str.strip()) - {""}
+
+
+def drop_owned_periods(
+    df: pd.DataFrame, owned: set[str]
+) -> tuple[pd.DataFrame, dict[str, int]]:
+    """Drop the rows for years a newer release already supplied.
+
+    Returns the rows kept and, for each year dropped, how many rows went.
+    """
+    if "time_period" not in df.columns or not owned:
+        return df, {}
+    periods = df["time_period"].astype(str).str.strip()
+    dropped = periods.isin(owned)
+    skipped = {str(k): int(v) for k, v in periods[dropped].value_counts().items()}
+    return df[~dropped], skipped

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

@@ -17,6 +17,20 @@ import requests
 from singer_sdk import Stream, Tap
 from singer_sdk import typing as th
 
+from tap_uk_ees.ks4_info import KS4_INFO_FIELDS, KS4_INFO_RENAMES
+from tap_uk_ees.ks4_summary import (
+    KS4_HEADLINE_COL_MAP,
+    KS4_SUMMARY_CSV_URL,
+    headline_record,
+    headline_rows,
+)
+from tap_uk_ees.release_precedence import (
+    drop_owned_periods,
+    newest_first,
+    periods_in,
+    slug_to_time_period,
+)
+
 CONTENT_API_BASE = (
     "https://content.explore-education-statistics.service.gov.uk/api"
 )
@@ -31,14 +45,6 @@ def get_content_release_id(publication_slug: str) -> str:
     return resp.json()["id"]
 
 
-def _slug_to_time_period(slug: str) -> str | None:
-    """Convert a release slug like '2022-23' to a time_period like '202223'."""
-    parts = slug.split("-")
-    if len(parts) == 2 and len(parts[0]) == 4 and len(parts[1]) == 2:
-        return parts[0] + parts[1]
-    return None
-
-
 def get_all_releases(publication_slug: str) -> list[dict]:
     """Return all releases for a publication as dicts with 'id' and 'time_period'.
 
@@ -63,7 +69,7 @@ def get_all_releases(publication_slug: str) -> list[dict]:
             total_pages = paging.get("totalPages", 1)
 
         for r in releases:
-            time_period = _slug_to_time_period(r.get("slug", ""))
+            time_period = slug_to_time_period(r.get("slug", ""))
             result.append({"id": r["id"], "time_period": time_period})
 
         if page >= total_pages:
@@ -88,6 +94,8 @@ class EESDatasetStream(Stream):
     target CSV path inside the ZIP (substring match, not exact).
     Subclasses may set _column_renames to map messy CSV column names to
     clean Singer field names before yielding records.
+    Subclasses may set _newest_release_owns_period when DfE re-publishes
+    earlier years in later releases and the newest copy is the authority.
     """
 
     replication_key = None
@@ -96,6 +104,7 @@ class EESDatasetStream(Stream):
     _urn_column: str = "school_urn"  # column name for URN in the CSV
     _encoding: str = "utf-8"  # CSV file encoding (some DfE files use latin-1)
     _column_renames: dict = {}  # CSV column name → Singer field name
+    _newest_release_owns_period: bool = False  # see release_precedence.py
 
     def get_records(self, context):
         import pandas as pd
@@ -110,6 +119,9 @@ class EESDatasetStream(Stream):
         self.logger.info(
             "Found %d release(s) for %s", len(releases), self._publication_slug
         )
+        if self._newest_release_owns_period:
+            releases = newest_first(releases)
+        owned_periods: set[str] = set()
 
         for release in releases:
             release_id = release["id"]
@@ -163,6 +175,15 @@ class EESDatasetStream(Stream):
             if urn_col in df.columns:
                 df = df[df[urn_col].notna() & (df[urn_col] != "")]
 
+            if self._newest_release_owns_period:
+                df, skipped = drop_owned_periods(df, owned_periods)
+                for period, count in sorted(skipped.items()):
+                    self.logger.info(
+                        "Skipping %d rows for %s from release %s: a newer release supplied that year",
+                        count, period, release_id,
+                    )
+                owned_periods |= periods_in(df)
+
             self.logger.info("Emitting %d school-level rows from release %s", len(df), release_id)
 
             for _, row in df.iterrows():
@@ -251,6 +272,9 @@ class EESKS4PerformanceStream(EESDatasetStream):
     primary_keys = ["school_urn", "time_period", "breakdown_topic", "breakdown", "sex"]
     _publication_slug = "key-stage-4-performance"
     _target_filename = "performance_tables_schools"
+    # DfE's 2024/25 file re-publishes 2022/23 and 2023/24 under current names;
+    # the 2023/24 release's own file uses older ones (audit C2).
+    _newest_release_owns_period = True
     schema = th.PropertiesList(
         th.Property("time_period", th.StringType, required=True),
         th.Property("school_urn", th.StringType, required=True),
@@ -310,34 +334,20 @@ class EESKS4PerformanceStream(EESDatasetStream):
 
 
 # ── KS4 Information (wide format: one row per school, context/demographics) ──
-# File: 202425_information_about_schools_provisional.csv (38 cols)
+# Files: 202425_information_about_schools_final.csv (38 cols, current names);
+# 202324_information_about_schools_final.csv (60 cols, older names — the only
+# source of 2023/24 information). Field list and renames: ks4_info.py.
 
 class EESKS4InfoStream(EESDatasetStream):
     name = "ees_ks4_info"
     primary_keys = ["school_urn", "time_period"]
     _publication_slug = "key-stage-4-performance"
     _target_filename = "information_about_schools"
+    _column_renames = KS4_INFO_RENAMES
     schema = th.PropertiesList(
         th.Property("time_period", th.StringType, required=True),
         th.Property("school_urn", th.StringType, required=True),
-        th.Property("school_laestab", th.StringType),
+        *[th.Property(field, th.StringType) for field in KS4_INFO_FIELDS],
-        th.Property("school_name", th.StringType),
-        th.Property("establishment_type_group", th.StringType),
-        th.Property("reldenom", th.StringType),
-        th.Property("admpol_pt", th.StringType),
-        th.Property("egender", th.StringType),
-        th.Property("agerange", th.StringType),
-        th.Property("allks_pupil_count", th.StringType),
-        th.Property("allks_boys_count", th.StringType),
-        th.Property("allks_girls_count", th.StringType),
-        th.Property("endks4_pupil_count", th.StringType),
-        th.Property("ks2_scaledscore_average", th.StringType),
-        th.Property("sen_with_ehcp_pupil_percent", th.StringType),
-        th.Property("sen_pupil_percent", th.StringType),
-        th.Property("sen_no_ehcp_pupil_percent", th.StringType),
-        th.Property("attainment8_diffn", th.StringType),
-        th.Property("progress8_diffn", th.StringType),
-        th.Property("progress8_banding", th.StringType),
     ).to_dict()
 
 
@@ -564,37 +574,23 @@ class EESKs2NationalStream(Stream):
             yield record
 
 
-# ── KS4 National Headlines (national level only — one row per year) ──────────
+# ── KS4 National and LA Headlines (one data set, two streams) ────────────────
-# Dataset: "National characteristics summary data" (Key stage 4 performance).
+# DfE's "summary, all state-funded" data set: England, regional and LA rows,
-# Official England state-funded headline measures, 2018/19 → latest.
+# 2018/19 → latest. URL, measures and filters: ks4_summary.py.
-# Suppressed values ('z', 'x') → NULL downstream. Progress 8 is legitimately
-# absent in years with no KS2 baseline (e.g. 2024/25) — that is DfE policy,
-# not missing data.
 
-_KS4_NATIONAL_CSV_URL = (
+def _read_ks4_summary(logger):
-    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
+    """Download DfE's KS4 summary data set."""
-    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
+    import pandas as pd
-)
 
-_KS4_NATIONAL_COL_MAP = {
+    logger.info("Downloading KS4 summary data set: %s", KS4_SUMMARY_CSV_URL)
-    "attainment8_average":    "attainment_8_score",
+    resp = requests.get(KS4_SUMMARY_CSV_URL, timeout=60)
-    "progress8_average":      "progress_8_score",
+    resp.raise_for_status()
-    "engmath_94_percent":     "english_maths_standard_pass_pct",
+    return pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)
-    "engmath_95_percent":     "english_maths_strong_pass_pct",
-    "ebacc_entering_percent": "ebacc_entry_pct",
-    "ebacc_94_percent":       "ebacc_standard_pass_pct",
-    "ebacc_95_percent":       "ebacc_strong_pass_pct",
-    "ebacc_aps_average":      "ebacc_avg_score",
-}
 
 
 class EESKs4NationalStream(Stream):
-    """National KS4 headline averages — one row per academic year.
+    """National KS4 headline averages — one row per academic year (England,
-
+    all state-funded schools, all pupils)."""
-    Filters to geographic_level == 'National', establishment_type_group ==
-    'All state-funded', breakdown_topic == 'Total', breakdown == 'Total'
-    so only the England-wide all-pupils row per year is emitted.
-    """
 
     name = "ees_ks4_national"
     primary_keys = ["time_period"]
@@ -602,34 +598,41 @@ class EESKs4NationalStream(Stream):
 
     schema = th.PropertiesList(
         th.Property("time_period", th.StringType, required=True),
-        *[th.Property(out, th.StringType) for out in _KS4_NATIONAL_COL_MAP.values()],
+        *[th.Property(out, th.StringType) for out in KS4_HEADLINE_COL_MAP.values()],
     ).to_dict()
 
     def get_records(self, context):
-        import pandas as pd
+        df = headline_rows(_read_ks4_summary(self.logger), "National")
-
-        self.logger.info("Downloading KS4 national headlines: %s", _KS4_NATIONAL_CSV_URL)
-        resp = requests.get(_KS4_NATIONAL_CSV_URL, timeout=60)
-        resp.raise_for_status()
-
-        df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)
-        df.columns = [c.strip().lower() for c in df.columns]
-
-        for col, want in [
-            ("geographic_level", "national"),
-            ("establishment_type_group", "all state-funded"),
-            ("breakdown_topic", "total"),
-            ("breakdown", "total"),
-        ]:
-            if col in df.columns:
-                df = df[df[col].str.strip().str.lower() == want]
-
         self.logger.info("Emitting %d national KS4 rows", len(df))
         for _, row in df.iterrows():
-            record = {"time_period": row.get("time_period", "").strip()}
+            yield headline_record(row, ("time_period",))
-            for csv_col, field in _KS4_NATIONAL_COL_MAP.items():
+
-                record[field] = row.get(csv_col, "").strip()
+
-            yield record
+class EESKs4LaStream(Stream):
+    """DfE's KS4 local-authority averages — one row per academic year and LA
+    (all state-funded schools, all pupils), from the same data set as
+    ees_ks4_national. They match DfE's published performance-table LA averages
+    and replace a mean the API took over every school, independent and special
+    included (audit H2). old_la_code is the GIAS LA code: schools join on it,
+    not on the name."""
+
+    name = "ees_ks4_la"
+    primary_keys = ["time_period", "old_la_code"]
+    replication_key = None
+
+    schema = th.PropertiesList(
+        th.Property("time_period", th.StringType, required=True),
+        th.Property("old_la_code", th.StringType, required=True),
+        th.Property("new_la_code", th.StringType),
+        th.Property("la_name", th.StringType),
+        *[th.Property(out, th.StringType) for out in KS4_HEADLINE_COL_MAP.values()],
+    ).to_dict()
+
+    def get_records(self, context):
+        df = headline_rows(_read_ks4_summary(self.logger), "Local authority")
+        self.logger.info("Emitting %d LA KS4 rows", len(df))
+        for _, row in df.iterrows():
+            yield headline_record(row, ("time_period", "old_la_code", "new_la_code", "la_name"))
 
 
 # ── Legacy KS2 (pre-COVID wide format from DfE performance tables) ────────────
@@ -972,6 +975,7 @@ class TapUKEES(Tap):
             LegacyKS4Stream(self),
             EESKs2NationalStream(self),
             EESKs4NationalStream(self),
+            EESKs4LaStream(self),
         ]
 
 

pipeline/tests/test_ees_ks4_info_names.py

@@ -0,0 +1,67 @@
+"""KS4 school information for 2023/24 exists only in the 2023/24 release,
+whose file uses DfE's older column names. The stream declared only the newer
+ones, so every 2023/24 field loaded as null (audit C2). The headers below are
+DfE's, copied from 202324_information_about_schools_final.csv and
+202425_information_about_schools_final.csv.
+"""
+import importlib.util
+from pathlib import Path
+
+import pytest
+
+MODULE = (Path(__file__).resolve().parents[1] / 'plugins' / 'extractors' / 'tap-uk-ees'
+          / 'tap_uk_ees' / 'ks4_info.py')
+
+HEADER_2023_24 = (
+    'time_period', 'time_identifier', 'geographic_level', 'country_code', 'country_name',
+    'school_laestab', 'school_urn', 'school_name', 'old_la_code', 'new_la_code', 'la_name',
+    'version', 'establishment_type_group', 'full_address', 'telnum', 'pcon_code', 'pcon_name',
+    'contflag', 'iclose', 'reldenom', 'admpol_pt', 'egender', 'feeder', 'agerange',
+    't_allks_pupils', 't_allks_boys', 't_allks_girls', 't_pupils', 't_boys', 'pt_boys',
+    't_girls', 'pt_girls', 'avg_ks2_scaledscore', 't_prior_lo', 'pt_prior_lo', 't_prior_av',
+    'pt_prior_av', 't_prior_hi', 'pt_prior_hi', 't_disadvantaged', 'pt_disadvantaged',
+    't_not_disadvantaged', 'pt_not_disadvantaged', 't_language_not_english',
+    'pt_language_not_english', 't_language_english', 'pt_language_english',
+    't_language_unknown', 'pt_language_unknown', 't_not_mobile', 'pt_not_mobile',
+    't_sen_with_ehcp', 'pt_sen_with_ehcp', 't_sen', 'pt_sen', 't_sen_no_ehcp',
+    'pt_sen_no_ehcp', 'diffn_att8', 'diffn_p8mea', 'p8_banding',
+)
+
+HEADER_2024_25 = (
+    'time_period', 'time_identifier', 'geographic_level', 'country_code', 'country_name',
+    'school_laestab', 'school_urn', 'school_name', 'old_la_code', 'new_la_code', 'la_name',
+    'version', 'establishment_type_group', 'full_address', 'telnum', 'pcon_code', 'pcon_name',
+    'contflag', 'iclose', 'reldenom', 'admpol_pt', 'egender', 'feeder', 'agerange',
+    'allks_pupil_count', 'allks_boys_count', 'allks_girls_count', 'endks4_pupil_count',
+    'ks2_scaledscore_average', 'sen_with_ehcp_pupil_count', 'sen_with_ehcp_pupil_percent',
+    'sen_pupil_count', 'sen_pupil_percent', 'sen_no_ehcp_pupil_count',
+    'sen_no_ehcp_pupil_percent', 'attainment8_diffn', 'progress8_diffn', 'progress8_banding',
+)
+
+
+@pytest.fixture
+def ks4_info():
+    spec = importlib.util.spec_from_file_location('ks4_info', MODULE)
+    module = importlib.util.module_from_spec(spec)
+    spec.loader.exec_module(module)
+    return module
+
+
+def test_every_declared_field_is_in_the_2023_24_file_once_renamed(ks4_info):
+    renamed = {ks4_info.KS4_INFO_RENAMES.get(c, c) for c in HEADER_2023_24}
+    assert set(ks4_info.KS4_INFO_FIELDS) - renamed == set()
+
+
+def test_every_declared_field_is_in_the_2024_25_file(ks4_info):
+    assert set(ks4_info.KS4_INFO_FIELDS) - set(HEADER_2024_25) == set()
+
+
+def test_the_renames_cannot_collide_with_either_file(ks4_info):
+    # An old name in the current file, or a new name already in the old file,
+    # would let a rename overwrite a real column.
+    assert set(ks4_info.KS4_INFO_RENAMES) & set(HEADER_2024_25) == set()
+    assert set(ks4_info.KS4_INFO_RENAMES.values()) & set(HEADER_2023_24) == set()
+
+
+def test_every_rename_names_a_declared_field(ks4_info):
+    assert set(ks4_info.KS4_INFO_RENAMES.values()) <= set(ks4_info.KS4_INFO_FIELDS)

pipeline/tests/test_ees_ks4_summary.py

@@ -0,0 +1,72 @@
+"""DfE's KS4 "summary, all state-funded" data set holds England, regional and
+LA rows. The England stream kept only the England row; its LA rows match
+DfE's published LA averages exactly, where the API's own mean was 7 points
+low (audit H2). Values below are DfE's for Kensington and Chelsea (207) and
+Wandsworth (212).
+"""
+import importlib.util
+import io
+from pathlib import Path
+
+import pandas as pd
+import pytest
+
+MODULE = (Path(__file__).resolve().parents[1] / 'plugins' / 'extractors' / 'tap-uk-ees'
+          / 'tap_uk_ees' / 'ks4_summary.py')
+
+CSV = """time_period,geographic_level,old_la_code,new_la_code,la_name,establishment_type_group,breakdown_topic,breakdown,attainment8_average,progress8_average,engmath_94_percent,engmath_95_percent,ebacc_entering_percent,ebacc_94_percent,ebacc_95_percent,ebacc_aps_average
+202425,National,,,,All state-funded,Total,Total,46.1,z,64.5,45.7,40.5,26.9,17.7,4.1
+202425,National,,,,All state-funded,Sex,Boys,44.1,z,62.0,43.0,38.0,24.0,16.0,3.9
+202425,Regional,,,,All state-funded,Total,Total,47.2,z,66.0,47.0,41.0,28.0,18.0,4.2
+202425,Local authority,207,E09000020,Kensington and Chelsea,All state-funded,Total,Total,54.5,z,77,61.4,45.6,32,26.6,4.89
+202425,Local authority,207,E09000020,Kensington and Chelsea,All state-funded,Sex,Girls,57.0,z,80,64.0,48.0,35,28.0,5.1
+202324,Local authority,207,E09000020,Kensington and Chelsea,All state-funded,Total,Total,54.5,0.29,76,60.0,44.0,31,25.0,4.8
+202425,Local authority,212,E09000032,Wandsworth,All state-funded,Total,Total,51.8,z,72,55.0,50.0,33,24.0,4.6
+202425,Local authority,212,E09000032,Wandsworth,Academies and free schools,Total,Total,52.0,z,73,56.0,51.0,34,25.0,4.7
+"""
+
+LA_KEYS = ('time_period', 'old_la_code', 'new_la_code', 'la_name')
+
+
+def _df():
+    return pd.read_csv(io.StringIO(CSV), dtype=str, keep_default_na=False)
+
+
+@pytest.fixture
+def summary():
+    spec = importlib.util.spec_from_file_location('ks4_summary', MODULE)
+    module = importlib.util.module_from_spec(spec)
+    spec.loader.exec_module(module)
+    return module
+
+
+def test_la_rows_are_one_per_la_and_year(summary):
+    rows = summary.headline_rows(_df(), 'Local authority')
+    assert sorted(zip(rows['time_period'], rows['old_la_code'])) == [
+        ('202324', '207'), ('202425', '207'), ('202425', '212')]
+
+
+def test_an_la_record_carries_codes_name_and_the_headline_measures(summary):
+    rows = summary.headline_rows(_df(), 'Local authority')
+    row = rows[(rows['time_period'] == '202425') & (rows['old_la_code'] == '207')].iloc[0]
+    assert summary.headline_record(row, LA_KEYS) == {
+        'time_period': '202425', 'old_la_code': '207', 'new_la_code': 'E09000020',
+        'la_name': 'Kensington and Chelsea',
+        'attainment_8_score': '54.5', 'progress_8_score': 'z',
+        'english_maths_standard_pass_pct': '77', 'english_maths_strong_pass_pct': '61.4',
+        'ebacc_entry_pct': '45.6', 'ebacc_standard_pass_pct': '32',
+        'ebacc_strong_pass_pct': '26.6', 'ebacc_avg_score': '4.89',
+    }
+
+
+def test_the_england_rows_are_one_per_year(summary):
+    rows = summary.headline_rows(_df(), 'National')
+    assert list(rows['time_period']) == ['202425']
+    assert summary.headline_record(rows.iloc[0], ('time_period',))['attainment_8_score'] == '46.1'
+
+
+def test_column_names_and_labels_match_whatever_their_case(summary):
+    df = _df()
+    df.columns = [c.upper() for c in df.columns]
+    df['GEOGRAPHIC_LEVEL'] = df['GEOGRAPHIC_LEVEL'].str.upper()
+    assert len(summary.headline_rows(df, 'Local authority')) == 3

pipeline/tests/test_ees_release_precedence.py

@@ -0,0 +1,100 @@
+"""DfE re-publishes earlier years inside later KS4 releases.
+
+The 2024/25 results file holds 2022/23, 2023/24 and 2024/25 under current
+column names. The 2023/24 release's own file, re-issued in March 2026 under
+older names, was read after it and overwrote every 2023/24 row with blanks
+(audit C2). For the KS4 results stream the newest release owns every year it
+contains.
+"""
+import importlib.util
+import re
+from pathlib import Path
+
+import pandas as pd
+import pytest
+
+TAP_DIR = (Path(__file__).resolve().parents[1] / 'plugins' / 'extractors' / 'tap-uk-ees'
+           / 'tap_uk_ees')
+
+
+@pytest.fixture
+def precedence():
+    spec = importlib.util.spec_from_file_location(
+        'release_precedence', TAP_DIR / 'release_precedence.py')
+    module = importlib.util.module_from_spec(spec)
+    spec.loader.exec_module(module)
+    return module
+
+
+def _release(period):
+    return {'id': f'release-{period}', 'time_period': period}
+
+
+def test_releases_are_taken_newest_first_whatever_order_the_api_gives(precedence):
+    releases = [_release('202223'), _release('202425'), _release(None), _release('202324')]
+    ordered = precedence.newest_first(releases)
+    assert [r['time_period'] for r in ordered] == ['202425', '202324', '202223', None]
+
+
+def test_a_year_a_newer_release_supplied_is_dropped_from_an_older_one(precedence):
+    newer = pd.DataFrame({'time_period': ['202425', '202324', '202223'], 'school_urn': ['1'] * 3})
+    older = pd.DataFrame({'time_period': ['202324', '202324', '201920'], 'school_urn': ['1', '2', '1']})
+
+    kept, skipped = precedence.drop_owned_periods(older, precedence.periods_in(newer))
+
+    assert list(kept['time_period']) == ['201920']
+    assert skipped == {'202324': 2}
+
+
+def test_periods_match_despite_surrounding_spaces(precedence):
+    owned = precedence.periods_in(pd.DataFrame({'time_period': [' 202324 ']}))
+    kept, skipped = precedence.drop_owned_periods(pd.DataFrame({'time_period': ['202324']}), owned)
+    assert owned == {'202324'}
+    assert kept.empty
+    assert skipped == {'202324': 1}
+
+
+def test_nothing_is_dropped_before_any_year_is_owned(precedence):
+    df = pd.DataFrame({'time_period': ['202324'], 'school_urn': ['1']})
+    kept, skipped = precedence.drop_owned_periods(df, set())
+    assert kept.equals(df)
+    assert skipped == {}
+
+
+def test_a_file_without_time_period_is_left_alone(precedence):
+    df = pd.DataFrame({'school_urn': ['1']})
+    kept, skipped = precedence.drop_owned_periods(df, {'202324'})
+    assert kept.equals(df)
+    assert skipped == {}
+    assert precedence.periods_in(df) == set()
+
+
+def test_only_the_ks4_results_stream_opts_in():
+    # A general rule would wipe KS2: the 2024/25 KS2 file holds 98,448 of the
+    # 955,956 rows the 2023/24 release has for 2023/24.
+    source = (TAP_DIR / 'tap.py').read_text()
+    opted_in = [chunk.split('(')[0] for chunk in source.split('\nclass ')[1:]
+                if re.search(r'_newest_release_owns_period\s*=\s*True', chunk)]
+    assert opted_in == ['EESKS4PerformanceStream']
+
+
+@pytest.mark.parametrize('slug, period', [
+    ('2024-25', '202425'),
+    ('2024-25-revised', '202425'),
+    ('2025-26-provisional', '202526'),
+    ('latest', None),
+    ('', None),
+])
+def test_a_release_slug_gives_its_year_whatever_its_suffix(precedence, slug, period):
+    # KS2 already publishes "2024-25-revised" and "2025-26-provisional". An
+    # unread suffix sent the release last, behind the provisional one for the
+    # same year, which then owned the year and dropped every revised row.
+    assert precedence.slug_to_time_period(slug) == period
+
+
+def test_within_a_year_the_api_order_is_kept(precedence):
+    # The API lists a year's revised release before its first release.
+    releases = [{'id': 'revised', 'time_period': '202526'},
+                {'id': 'first', 'time_period': '202526'},
+                {'id': 'older', 'time_period': '202425'}]
+    assert [r['id'] for r in precedence.newest_first(releases)] == ['revised', 'first', 'older']

pipeline/transform/macros/safe_numeric.sql

@@ -3,7 +3,9 @@
   Casts a string column to numeric, treating any non-numeric value as NULL.
   Handles all EES suppression codes (z, c, x, q, u, etc.) without needing
   an explicit list — any string that doesn't look like a number becomes NULL.
+  A trailing percent sign is accepted: DfE's 2023/24 KS2 information file
+  writes percentages as "34%" (audit C2).
 #}
 {% macro safe_numeric(col) -%}
-    CASE WHEN {{ col }} ~ '^-?[0-9]+(\.[0-9]+)?$' THEN {{ col }}::numeric ELSE NULL END
+    CASE WHEN {{ col }} ~ '^-?[0-9]+(\.[0-9]+)?%?$' THEN rtrim({{ col }}, '%')::numeric ELSE NULL END
 {%- endmacro %}

pipeline/transform/models/marts/_marts_schema.yml

@@ -279,11 +279,24 @@ models:
         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
+    description: Official DfE KS4 national headline averages (England, all state-funded schools, all pupils) — one row per academic year
     columns:
       - name: year
         tests: [not_null, unique]
 
+  - name: fact_ks4_la_averages
+    description: Official DfE KS4 local-authority averages (all state-funded schools, all pupils) — one row per academic year and LA; la_code is the GIAS LA code
+    columns:
+      - name: year
+        tests: [not_null]
+      - name: la_code
+        tests: [not_null]
+      - name: la_name
+        tests: [not_null]
+    tests:
+      - unique:
+          column_name: "year || '-' || la_code"
+
   - name: fact_deprivation
     description: IDACI deprivation index — one row per URN
     columns:

pipeline/transform/models/marts/fact_ks4_la_averages.sql

@@ -0,0 +1,22 @@
+{{ config(materialized='table') }}
+
+-- Mart: OFFICIAL DfE KS4 local-authority averages — one row per academic year
+-- and LA (all state-funded schools, all pupils), from the same EES data set
+-- as fact_ks4_national_averages. Feeds "vs LA avg" on search rows and map
+-- cards, replacing a mean the API took over every school in the LA,
+-- independent and special included (audit H2). la_code is the GIAS LA code.
+
+select
+    year,
+    la_code,
+    la_name,
+    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
+from {{ ref('stg_ees_ks4_la') }}
+order by year, la_code

pipeline/transform/models/staging/_stg_sources.yml

@@ -79,6 +79,9 @@ sources:
       - name: ees_ks4_national
         description: Official KS4 national headline averages from DfE EES data catalogue — one row per academic year
 
+      - name: ees_ks4_la
+        description: Official KS4 local-authority averages (all state-funded schools) from the same DfE EES data set as ees_ks4_national — one row per academic year and LA
+
       # Phonics: no school-level data on EES (only national/LA level)
 
       - name: fbit_finance

pipeline/transform/models/staging/stg_ees_ks2.yml

@@ -0,0 +1,22 @@
+version: 2
+
+unit_tests:
+  - name: stg_ees_ks2_reads_percentages_written_with_a_sign
+    description: >
+      DfE's 2023/24 KS2 information file writes percentages as "34%", and every
+      2023/24 percentage loaded as null (audit C2). Plain numbers still load;
+      suppression codes stay null. School 147411's 2023/24 figures are DfE's.
+    model: stg_ees_ks2
+    given:
+      - input: source('raw', 'ees_ks2_attainment')
+        rows:
+          - {school_urn: '147411', time_period: '202324', subject: 'Reading, writing and maths', breakdown_topic: 'All pupils', breakdown: 'Total', expected_standard_pupil_percent: '70'}
+          - {school_urn: '100000', time_period: '202425', subject: 'Reading, writing and maths', breakdown_topic: 'All pupils', breakdown: 'Total', expected_standard_pupil_percent: '79'}
+      - input: source('raw', 'ees_ks2_info')
+        rows:
+          - {school_urn: '147411', time_period: '202324', totpups: '818', telig: '112', ptfsm6cla1a: '34%', ptealgrp2: '56%', psenelk: '20%', psenele: 'c', ptmobn: '87.5%'}
+          - {school_urn: '100000', time_period: '202425', totpups: '792', telig: '105', ptfsm6cla1a: '32', ptealgrp2: 'x', psenelk: '14', psenele: '2', ptmobn: '87'}
+    expect:
+      rows:
+        - {urn: 147411, year: 202324, total_pupils: 818, rwm_expected_pct: 70, disadvantaged_pct: 34, eal_pct: 56, sen_support_pct: 20, sen_ehcp_pct: null, stability_pct: 87.5}
+        - {urn: 100000, year: 202425, total_pupils: 792, rwm_expected_pct: 79, disadvantaged_pct: 32, eal_pct: null, sen_support_pct: 14, sen_ehcp_pct: 2, stability_pct: 87}

pipeline/transform/models/staging/stg_ees_ks4_la.sql

@@ -0,0 +1,21 @@
+-- Staging model: official DfE KS4 local-authority averages — one row per
+-- academic year and LA (all state-funded schools, all pupils). Same EES data
+-- set as stg_ees_ks4_national. la_code is the GIAS LA code
+-- (dim_location.local_authority_code). Suppressed values ('z', 'x') are
+-- coerced to NULL by safe_numeric.
+
+select
+    cast(trim(time_period) as integer)                        as year,
+    cast(trim(old_la_code) as integer)                        as la_code,
+    trim(la_name)                                             as la_name,
+    {{ safe_numeric('attainment_8_score') }}                  as attainment_8_score,
+    {{ safe_numeric('progress_8_score') }}                    as progress_8_score,
+    {{ safe_numeric('english_maths_standard_pass_pct') }}     as english_maths_standard_pass_pct,
+    {{ safe_numeric('english_maths_strong_pass_pct') }}       as english_maths_strong_pass_pct,
+    {{ safe_numeric('ebacc_entry_pct') }}                     as ebacc_entry_pct,
+    {{ safe_numeric('ebacc_standard_pass_pct') }}             as ebacc_standard_pass_pct,
+    {{ safe_numeric('ebacc_strong_pass_pct') }}               as ebacc_strong_pass_pct,
+    {{ safe_numeric('ebacc_avg_score') }}                     as ebacc_avg_score
+from {{ source('raw', 'ees_ks4_la') }}
+where time_period ~ '^[0-9]+$'
+  and old_la_code ~ '^[0-9]+$'

pipeline/transform/models/staging/stg_ees_ks4_la.yml

@@ -0,0 +1,15 @@
+version: 2
+
+unit_tests:
+  - name: stg_ees_ks4_la_casts_codes_years_and_measures
+    description: DfE's LA rows, keyed by the GIAS LA code. Suppressed values ('z') become null.
+    model: stg_ees_ks4_la
+    given:
+      - input: source('raw', 'ees_ks4_la')
+        rows:
+          - {time_period: '202425', old_la_code: '207', new_la_code: 'E09000020', la_name: 'Kensington and Chelsea', attainment_8_score: '54.5', progress_8_score: 'z', english_maths_standard_pass_pct: '77'}
+          - {time_period: '202324', old_la_code: '207', new_la_code: 'E09000020', la_name: 'Kensington and Chelsea', attainment_8_score: '54.5', progress_8_score: '0.29', english_maths_standard_pass_pct: '76'}
+    expect:
+      rows:
+        - {year: 202425, la_code: 207, la_name: 'Kensington and Chelsea', attainment_8_score: 54.5, progress_8_score: null, english_maths_standard_pass_pct: 77}
+        - {year: 202324, la_code: 207, la_name: 'Kensington and Chelsea', attainment_8_score: 54.5, progress_8_score: 0.29, english_maths_standard_pass_pct: 76}

pipeline/transform/tests/assert_ks2_info_percentages_loaded.sql

@@ -0,0 +1,16 @@
+-- Where a year's KS2 school information loaded (pupil counts present), its
+-- percentages loaded too. DfE's files give a disadvantaged % for 96–97% of
+-- those schools; 2023/24 loaded none because the file writes "34%" (audit
+-- C2). A year without an information file (2022/23) has no pupil counts and
+-- is skipped.
+
+select
+    year,
+    count(total_pupils) as with_pupils,
+    count(*) filter (where total_pupils is not null and disadvantaged_pct is not null)
+        as with_disadvantaged
+from {{ ref('stg_ees_ks2') }}
+group by year
+having count(total_pupils) >= 1000
+   and count(*) filter (where total_pupils is not null and disadvantaged_pct is not null)
+       < 0.9 * count(total_pupils)

pipeline/transform/tests/assert_ks4_la_averages_cover_las.sql

@@ -0,0 +1,13 @@
+-- DfE publishes an average for about 152 LAs a year. Fewer than 145 in the
+-- latest year (or none at all) means the LA filter in tap_uk_ees
+-- (ks4_summary.headline_rows) stopped matching, e.g. after DfE renamed a label.
+
+with latest as (
+    select max(year) as year from {{ ref('fact_ks4_la_averages') }}
+)
+
+select l.year, count(f.la_code) as las
+from latest l
+left join {{ ref('fact_ks4_la_averages') }} f on f.year = l.year
+group by l.year
+having count(f.la_code) < 145

pipeline/transform/tests/assert_ks4_la_averages_current.sql

@@ -0,0 +1,20 @@
+{{ config(severity='warn') }}
+
+-- DfE's LA averages cover the latest year with school results. They come from
+-- a fixed EES data set (ks4_summary.KS4_SUMMARY_CSV_URL); when DfE publishes a
+-- new year under a new data set, the school results move on and this mart does
+-- not, so /api/la-averages serves an empty map and every "vs LA avg" goes.
+-- A warning, not an error: it must not hold back a new year's school results.
+
+with schools as (
+    select max(year) as year from {{ ref('stg_ees_ks4') }} where attainment_8_score is not null
+),
+
+las as (
+    select max(year) as year from {{ ref('fact_ks4_la_averages') }}
+)
+
+select s.year as latest_school_year, l.year as latest_la_year
+from schools s
+cross join las l
+where l.year is null or l.year < s.year

pipeline/transform/tests/assert_ks4_years_have_results.sql

@@ -0,0 +1,12 @@
+-- Every year loaded from EES has an Attainment 8 for at least half its
+-- schools. DfE's files reach 82% each year; 2023/24 loaded at 0% after DfE
+-- re-issued its file under older column names (audit C2). Pre-2019 years come
+-- from stg_legacy_ks4 and are not checked here.
+
+select
+    year,
+    count(*) as schools,
+    count(attainment_8_score) as with_attainment_8
+from {{ ref('stg_ees_ks4') }}
+group by year
+having count(attainment_8_score) < 0.5 * count(*)