docs: name the benchmarks computed from our dataset (review)

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
test(dbt): warn when DfE's LA averages fall behind the school results (review)
2026-10-06 12:50:40 +01:00 · 2026-10-06 12:50:27 +01:00 · 2026-10-06 12:50:10 +01:00 · 2026-10-06 12:33:36 +01:00 · 2026-10-06 12:33:03 +01:00 · 2026-10-06 12:32:22 +01:00
61 changed files with 5653 additions and 626 deletions
@@ -97,8 +97,9 @@ STATIC_SITEMAP_PATHS = ("/", "/rankings", "/compare", "/admissions")

 # A page has something a search result could state if any of these is present
 # in any year. Shared by _has_publishable_data and the per-school check in
-# _school_sitemap_rows so the two can never drift.
-_PUBLISHABLE_FIELDS = ("rwm_expected_pct", "attainment_8_score", "ofsted_grade")
+# _school_sitemap_rows so the two can never drift. An inspection with no
+# overall grade (ofsted_date alone) still gives a page something to state.
+_PUBLISHABLE_FIELDS = ("rwm_expected_pct", "attainment_8_score", "ofsted_grade", "ofsted_date")


 def _has_publishable_data(row) -> bool:
@@ -18,7 +18,7 @@ from .config import settings
 from .database import SessionLocal, engine
 from .models import (
    DimSchool, DimLocation, KS2Performance,
-    FactOfstedInspection, FactAdmissions, FactAdmissionDistance,
+    FactOfstedLatest, FactAdmissions, FactAdmissionDistance,
    FactDeprivation, FactFinance, FactPupilCharacteristics,
    FactKs4Destinations, FactKs5Destinations,
 )
@@ -251,10 +251,11 @@ _MAIN_QUERY = text("""
        s.website,
        s.telephone,
        s.nursery_provision,
-        foi.ofsted_grade,
-        foi.ofsted_date,
-        foi.ofsted_framework,
-        foi.ofsted_rc_date,
+        foi.current_grade         AS ofsted_grade,
+        foi.current_grade_date    AS ofsted_grade_date,
+        foi.latest_visit_date     AS ofsted_date,
+        foi.framework             AS ofsted_framework,
+        foi.rc_inspection_date    AS ofsted_rc_date,
        l.local_authority_name AS local_authority,
        l.local_authority_code,
        l.address_line1        AS address1,
@@ -335,21 +336,10 @@ _MAIN_QUERY = text("""
    FROM marts.dim_school s
    JOIN marts.dim_location l ON s.urn = l.urn
    LEFT JOIN marts.fact_performance p ON s.urn = p.urn
-    LEFT JOIN (
-        SELECT DISTINCT ON (urn)
-            urn,
-            -- Fall back to the ungraded-inspection grade when no graded grade exists.
-            COALESCE(overall_effectiveness, ungraded_grade) AS ofsted_grade,
-            inspection_date       AS ofsted_date,
-            framework             AS ofsted_framework,
-            -- Report-card signal for list/map badges: non-null only when the
-            -- latest inspection carries report-card grades. framework is the
-            -- raw event grouping ("Schools - S5"), never "ReportCard", so it
-            -- can't be used to detect report cards.
-            rc_inspection_date    AS ofsted_rc_date
-        FROM marts.fact_ofsted_inspection
-        ORDER BY urn, inspection_date DESC NULLS LAST
-    ) foi ON s.urn = foi.urn
+    -- One current Ofsted status per school (pipeline: int_ofsted_latest): the
+    -- grade still in force, dated by the inspection that awarded or confirmed
+    -- it, and the latest visit of any kind.
+    LEFT JOIN marts.fact_ofsted_latest foi ON s.urn = foi.urn
    ORDER BY s.school_name, p.year
 """)

@@ -731,39 +721,36 @@ def compute_benchmarks(df: pd.DataFrame, census_benchmarks: dict | None = None)
    }


+def _iso(d):
+    return d.isoformat() if d else None
+
+
 def _ofsted_block(o, urn: int) -> dict:
-    """Serialize the latest Ofsted inspection row for API responses.
+    """Serialize a fact_ofsted_latest row for API responses.

-    `grade_source` records where the effective overall grade came from:
-    a graded (Section 5) inspection, or carried forward from an ungraded
-    (Section 8) outcome — materially different claims a UI must be able
-    to distinguish. `report_card` holds coded+labelled renewed-framework
-    (Nov 2025) area judgements; safeguarding is a separate boolean and
-    never appears among the graded areas.
+    `current_grade` is the overall grade still in force, dated by the
+    inspection that awarded or confirmed it; `latest_visit` is the school's
+    most recent inspection of any kind. The rule lives in int_ofsted_latest
+    (docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md).
+    `overall_effectiveness` and `inspection_date` describe the graded
+    inspection itself and label its area judgements. `report_card` holds the
+    renewed-framework (Nov 2025) area judgements; safeguarding is a separate
+    boolean and never appears among the graded areas.
    """
-    if o.overall_effectiveness is not None:
-        grade_source = "graded"
-        overall = o.overall_effectiveness
-    elif o.ungraded_grade is not None:
-        # Fall back to the grade parsed from an ungraded (Section 8) outcome
-        # (e.g. "School remains Good") so the detail page matches the list badge.
-        grade_source = "ungraded_carried_forward"
-        overall = o.ungraded_grade
-    else:
-        grade_source = None
-        overall = None
-
    block = {
        "framework": o.framework,
-        "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
-        "rc_inspection_date": (
-            o.rc_inspection_date.isoformat()
-            if getattr(o, "rc_inspection_date", None)
-            else None
-        ),
+        "inspection_date": _iso(o.graded_inspection_date),
+        "rc_inspection_date": _iso(o.rc_inspection_date),
        "inspection_type": o.inspection_type,
-        "overall_effectiveness": overall,
-        "grade_source": grade_source,
+        "overall_effectiveness": o.overall_effectiveness if o.overall_effectiveness in (1, 2, 3, 4) else None,
+        "current_grade": (
+            {"grade": o.current_grade, "date": _iso(o.current_grade_date), "basis": o.current_grade_basis}
+            if o.current_grade is not None else None
+        ),
+        "latest_visit": (
+            {"date": _iso(o.latest_visit_date), "kind": o.latest_visit_kind, "outcome": o.latest_visit_outcome}
+            if o.latest_visit_date else None
+        ),
        "quality_of_education": o.quality_of_education,
        "behaviour_attitudes": o.behaviour_attitudes,
        "personal_development": o.personal_development,
@@ -1097,20 +1084,14 @@ def get_supplementary_data_batch(db: Session, urns: list[int]) -> dict:
            logging.getLogger(__name__).error("batch supplementary query failed: %s", e)
            db.rollback()

-    # Ofsted — latest inspection per URN. Ordered so the first row seen per
-    # URN is the most recent.
+    # Ofsted — the mart already holds one current status per URN.
    def _ofsted():
        rows = (
-            db.query(FactOfstedInspection)
-            .filter(FactOfstedInspection.urn.in_(urns))
-            .order_by(FactOfstedInspection.urn, FactOfstedInspection.inspection_date.desc())
+            db.query(FactOfstedLatest)
+            .filter(FactOfstedLatest.urn.in_(urns))
            .all()
        )
-        seen = set()
        for o in rows:
-            if o.urn in seen:
-                continue
-            seen.add(o.urn)
            result[o.urn]["ofsted"] = _ofsted_block(o, o.urn)
    _safe(_ofsted)

@@ -162,6 +162,49 @@ class FactOfstedInspection(Base):
    report_url = Column(Text)


+class FactOfstedLatest(Base):
+    """Current Ofsted status — one row per URN (pipeline: int_ofsted_latest).
+
+    `current_grade` is the overall grade still in force, dated by the
+    inspection that awarded or confirmed it; `latest_visit_*` is the school's
+    most recent inspection of any kind.
+    """
+    __tablename__ = "fact_ofsted_latest"
+    __table_args__ = MARTS
+
+    urn = Column(Integer, primary_key=True)
+    latest_visit_date = Column(Date)
+    latest_visit_kind = Column(String(20))
+    latest_visit_outcome = Column(String(100))
+    current_grade = Column(Integer)
+    current_grade_date = Column(Date)
+    current_grade_basis = Column(String(20))
+    graded_inspection_date = Column(Date)
+    ungraded_inspection_date = Column(Date)
+    rc_inspection_date = Column(Date)
+    inspection_type = Column(String(100))
+    framework = Column(String(20))
+    overall_effectiveness = Column(Integer)
+    quality_of_education = Column(Integer)
+    behaviour_attitudes = Column(Integer)
+    personal_development = Column(Integer)
+    leadership_management = Column(Integer)
+    early_years_provision = Column(Integer)
+    sixth_form_provision = Column(Integer)
+    ungraded_outcome = Column(String(100))
+    ungraded_grade = Column(Integer)
+    rc_safeguarding_met = Column(Boolean)
+    rc_inclusion = Column(Integer)
+    rc_curriculum_teaching = Column(Integer)
+    rc_achievement = Column(Integer)
+    rc_attendance_behaviour = Column(Integer)
+    rc_personal_development = Column(Integer)
+    rc_leadership_governance = Column(Integer)
+    rc_early_years = Column(Integer)
+    rc_sixth_form = Column(Integer)
+    report_url = Column(Text)
+
+
 class FactAdmissions(Base):
    """School admissions — one row per URN per year."""
    __tablename__ = "fact_admissions"
@@ -574,6 +574,7 @@ SCHOOL_COLUMNS = [
    "gender",
    "admissions_policy",
    "ofsted_grade",
+    "ofsted_grade_date",
    "ofsted_date",
    "ofsted_framework",
    "ofsted_rc_date",
@@ -12,7 +12,8 @@ from fastapi.testclient import TestClient
 LATEST = 202425

 CANNED_SUPPLEMENTARY = {
-    "ofsted": {"overall_effectiveness": 2, "grade_source": "graded",
+    "ofsted": {"overall_effectiveness": 2,
+               "current_grade": {"grade": 2, "date": "2023-01-01", "basis": "graded"},
               "report_card": {}, "ofsted_page_url": "https://reports.ofsted.gov.uk/provider/21/100140"},
    "census": {"year": 202526, "fsm_pct": 29.8},
    "admissions": {"year": 202627, "second_preference_offers": 4},
@@ -86,7 +87,7 @@ def test_each_school_gains_supplementary_blocks(client):
    body = client.get("/api/compare?urns=100140,138690").json()
    for urn in ("100140", "138690"):
        school = body["comparison"][urn]
-        assert school["ofsted"]["grade_source"] == "graded"
+        assert school["ofsted"]["current_grade"]["basis"] == "graded"
        assert school["census"]["fsm_pct"] == 29.8
        assert school["admissions"]["second_preference_offers"] == 4
        assert school["admissions_history"][0]["year"] == 202627
@@ -0,0 +1,56 @@
+"""The search badge reads ofsted_grade, ofsted_grade_date and ofsted_date from
+list rows (nextjs-app/lib/utils.ts buildOfstedListBadge). A field the list
+never sends would leave the badge without its year, or worse, fall back to
+"Not yet inspected". The school page reads current_grade and latest_visit from
+the ofsted block (lib/ofstedStatus.ts)."""
+
+import numpy as np
+import pandas as pd
+import pytest
+from fastapi.testclient import TestClient
+
+from backend.schemas import SCHOOL_COLUMNS
+
+
+def test_list_columns_include_the_status_fields():
+    for field in ("ofsted_grade", "ofsted_grade_date", "ofsted_date", "ofsted_rc_date"):
+        assert field in SCHOOL_COLUMNS
+
+
+def _df() -> pd.DataFrame:
+    # Rabbsfarm (102408): latest inspection 17 June 2025 gave no overall grade.
+    return pd.DataFrame([{
+        "urn": 102408, "school_name": "Rabbsfarm Primary School", "phase": "Primary",
+        "school_type": "Community school", "local_authority": "Hillingdon",
+        "address": "Gordon Road, Yiewsley, UB7 8AH", "postcode": "UB7 8AH",
+        "latitude": 51.51, "longitude": -0.47, "year": 202425, "rwm_expected_pct": 58.0,
+        "total_pupils": 60, "gias_total_pupils": 616,
+        "ofsted_grade": np.nan, "ofsted_grade_date": None, "ofsted_date": "2025-06-17",
+        "ofsted_framework": "Schools - S5", "ofsted_rc_date": None,
+    }])
+
+
+@pytest.fixture()
+def client(monkeypatch):
+    from backend import app as app_module
+
+    monkeypatch.setattr(app_module, "load_school_data", _df)
+    monkeypatch.setattr(app_module, "load_latest_school_data", _df)
+    monkeypatch.setattr(app_module, "_place_registry", None)
+    return TestClient(app_module.app, raise_server_exceptions=False)
+
+
+def test_search_rows_carry_the_status_fields(client):
+    resp = client.get("/api/schools")
+    assert resp.status_code == 200, resp.text
+    row = resp.json()["schools"][0]
+    assert row["ofsted_grade"] is None
+    assert row["ofsted_date"] == "2025-06-17"
+    assert "ofsted_grade_date" in row
+
+
+def test_a_school_with_only_an_inspection_is_publishable():
+    from backend.app import _has_publishable_data
+
+    assert _has_publishable_data({"rwm_expected_pct": None, "attainment_8_score": None,
+                                  "ofsted_grade": None, "ofsted_date": "2025-06-17"})
@@ -85,12 +85,15 @@ def _ofsted_row(urn, date, oe):
        "framework", "inspection_type", "quality_of_education", "behaviour_attitudes",
        "personal_development", "leadership_management", "early_years_provision",
        "sixth_form_provision", "ungraded_outcome", "ungraded_grade",
+        "ungraded_inspection_date", "rc_inspection_date", "latest_visit_outcome",
        "rc_safeguarding_met", "rc_inclusion", "rc_curriculum_teaching", "rc_achievement",
        "rc_attendance_behaviour", "rc_personal_development", "rc_leadership_governance",
        "rc_early_years", "rc_sixth_form", "report_url",
    )}
-    base.update(urn=urn, inspection_date=types.SimpleNamespace(isoformat=lambda: date),
-                overall_effectiveness=oe, grade_source=None)
+    when = types.SimpleNamespace(isoformat=lambda: date)
+    base.update(urn=urn, graded_inspection_date=when, latest_visit_date=when,
+                latest_visit_kind="graded", overall_effectiveness=oe,
+                current_grade=oe, current_grade_date=when, current_grade_basis="graded")
    return types.SimpleNamespace(**base)


@@ -114,10 +117,9 @@ def _dist_row(urn, year, distance_m, route_count=1):

 def test_one_query_per_table_and_latest_row_per_urn():
    rows = {
-        # URN 1 has two Ofsted rows; the batch must keep the most recent (2023).
-        "FactOfstedInspection": [
+        # The mart holds one current Ofsted status per URN.
+        "FactOfstedLatest": [
            _ofsted_row(1, "2023-01-01", 2),
-            _ofsted_row(1, "2019-01-01", 3),
            _ofsted_row(2, "2021-06-01", 1),
        ],
        "FactAdmissions": [_adm_row(1, 202526), _adm_row(1, 202627), _adm_row(2, 202627)],
@@ -142,14 +144,14 @@ def test_one_query_per_table_and_latest_row_per_urn():
    assert sorted(session.queries) == [
        "FactAdmissionDistance", "FactAdmissions", "FactDeprivation",
        "FactFinance", "FactKs4Destinations", "FactKs5Destinations",
-        "FactOfstedInspection", "FactPupilCharacteristics",
+        "FactOfstedLatest", "FactPupilCharacteristics",
    ]

    # A school with no destination rows gets null, not an empty shell — the
    # frontend renders the section from the block's presence.
    assert out[1]["destinations"] is None

-    # Latest Ofsted kept per URN
+    # Each URN's current Ofsted status
    assert out[1]["ofsted"]["overall_effectiveness"] == 2
    assert out[2]["ofsted"]["overall_effectiveness"] == 1

@@ -175,7 +177,7 @@ def test_one_query_per_table_and_latest_row_per_urn():


 def test_single_wrapper_matches_batch(monkeypatch):
-    session = _FakeSession({"FactOfstedInspection": [_ofsted_row(5, "2022-01-01", 2)]})
+    session = _FakeSession({"FactOfstedLatest": [_ofsted_row(5, "2022-01-01", 2)]})
    single = data_loader.get_supplementary_data(session, 5)
    assert single["ofsted"]["overall_effectiveness"] == 2
    assert single["admissions_history"] == []
@@ -10,7 +10,10 @@ from backend.data_loader import _admissions_row_dict, _ofsted_block

 def _row(**kw):
    base = dict(
-        framework="RC", inspection_date=None, inspection_type=None,
+        framework="RC", inspection_type=None,
+        graded_inspection_date=None, ungraded_inspection_date=None, rc_inspection_date=None,
+        latest_visit_date=None, latest_visit_kind=None, latest_visit_outcome=None,
+        current_grade=None, current_grade_date=None, current_grade_basis=None,
        overall_effectiveness=None, quality_of_education=None,
        behaviour_attitudes=None, personal_development=None,
        leadership_management=None, early_years_provision=None,
@@ -33,20 +36,41 @@ def test_report_card_block_and_provider_url():
    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"


-def test_grade_source_graded_vs_carried_forward():
-    assert _ofsted_block(_row(overall_effectiveness=1), urn=1)["grade_source"] == "graded"
-    carried = _ofsted_block(_row(ungraded_grade=2), urn=1)
-    assert carried["grade_source"] == "ungraded_carried_forward"
-    assert carried["overall_effectiveness"] == 2
-    assert _ofsted_block(_row(), urn=1)["grade_source"] is None
+def test_no_grade_is_carried_past_a_newer_inspection():
+    # Rabbsfarm (102408): the 2025 inspection gave no overall grade.
+    block = _ofsted_block(_row(
+        graded_inspection_date=date(2025, 6, 17), ungraded_inspection_date=date(2020, 2, 6),
+        latest_visit_date=date(2025, 6, 17), latest_visit_kind="graded",
+        ungraded_grade=2, ungraded_outcome="School remains Good", quality_of_education=3,
+    ), urn=102408)
+    assert block["current_grade"] is None
+    assert block["overall_effectiveness"] is None
+    assert block["latest_visit"] == {"date": "2025-06-17", "kind": "graded", "outcome": None}
+    assert block["inspection_date"] == "2025-06-17"
+    assert "grade_source" not in block
+
+
+def test_confirmed_grade_is_dated_by_the_confirming_visit():
+    block = _ofsted_block(_row(
+        graded_inspection_date=date(2020, 1, 7), ungraded_inspection_date=date(2024, 7, 18),
+        latest_visit_date=date(2024, 7, 18), latest_visit_kind="ungraded",
+        latest_visit_outcome="School remains Good", overall_effectiveness=2,
+        current_grade=2, current_grade_date=date(2024, 7, 18), current_grade_basis="confirmed",
+    ), urn=104762)
+    assert block["current_grade"] == {"grade": 2, "date": "2024-07-18", "basis": "confirmed"}
+    assert block["overall_effectiveness"] == 2
+    assert block["inspection_date"] == "2020-01-07"
+
+
+def test_overall_sentinel_is_not_served_as_a_grade():
+    assert _ofsted_block(_row(overall_effectiveness=9), urn=1)["overall_effectiveness"] is None


 def test_ofsted_block_carries_rc_inspection_date():
    o = _row(
-        ungraded_grade=2,
        rc_achievement=1,
        rc_inspection_date=date(2026, 2, 3),
-        inspection_date=date(2021, 10, 7),
+        graded_inspection_date=date(2021, 10, 7),
    )
    block = _ofsted_block(o, urn=138690)
    assert block["rc_inspection_date"] == "2026-02-03"
@@ -55,7 +79,7 @@ def test_ofsted_block_carries_rc_inspection_date():


 def test_ofsted_block_rc_inspection_date_none_when_absent():
-    o = _row(overall_effectiveness=1, inspection_date=date(2021, 10, 13))
+    o = _row(overall_effectiveness=1, graded_inspection_date=date(2021, 10, 13))
    block = _ofsted_block(o, urn=136276)
    assert block["rc_inspection_date"] is None

@@ -63,6 +87,7 @@ def test_ofsted_block_rc_inspection_date_none_when_absent():
 def test_ofsted_block_keeps_existing_keys():
    block = _ofsted_block(_row(overall_effectiveness=2, quality_of_education=2), urn=1)
    for key in ("framework", "inspection_date", "overall_effectiveness",
+                "current_grade", "latest_visit",
                "quality_of_education", "rc_inclusion", "report_url"):
        assert key in block

@@ -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
@@ -0,0 +1,257 @@
+# Ofsted Current Status — Design
+
+**Date:** 2026-10-05
+**Status:** approved design, not yet implemented
+**Scope:** `pipeline/transform` Ofsted models, `backend/data_loader.py`, list and
+detail API Ofsted fields, search and map badges, school-page Ofsted section,
+compare Ofsted rows, Typesense rating, sitemap
+**Fixes:** audit findings C1, M1 and (as a side effect) M2 and part of H3,
+from the 3 Oct 2026 accuracy audit
+
+## Goal
+
+Never show an Ofsted grade under a date it was not awarded or confirmed on, and
+always date "Inspected" by the school's latest visit.
+
+## The problem
+
+Ofsted's management information gives each school at most three inspections:
+the latest graded inspection (date G, an overall grade or "Not judged", area
+grades), the latest ungraded inspection (date U, an outcome sentence) and the
+latest report card (date RC).
+
+The site derives a grade and a date from these with two independent rules:
+
+- grade = the graded inspection's overall grade, or else the grade parsed from
+  the ungraded outcome ("School remains Good" → 2);
+- date = G, or else U.
+
+The two rules can pick different inspections. Every inspection from
+September 2024 to November 2025 was graded with "Not judged" overall, so the
+grade falls back to an older ungraded visit while the date stays the new one.
+
+- **C1.** Rabbsfarm Primary School (102408) shows "Good · 2025". Ofsted's
+  17 June 2025 inspection gave no overall grade and rated quality of education,
+  behaviour and leadership Requires Improvement. The "Good" comes from an
+  ungraded visit on 6 February 2020. Site-wide, 932 badges pair a
+  carried-forward grade with a newer inspection's year, and 147 of them say Good
+  or Outstanding while that inspection rated an area Requires Improvement or
+  Inadequate. Acre Wood Academy (151783) reads "Good · 2024" though the
+  October 2024 inspection rated all four areas Inadequate.
+- **M1.** When a newer ungraded visit exists, the page shows the older graded
+  date. Washwood Heath Academy (139888) reads "Inspected 3 Mar 2020 · 4+ years
+  ago"; Ofsted visited on 21 May 2025. 667 schools.
+
+Ofsted's own provider page for Rabbsfarm leads with the 2025 area judgements and
+"From September 2024, Ofsted no longer makes an overall effectiveness
+judgement". It shows no overall grade.
+
+The rule is also implemented four times: `dim_school.ofsted_grade` (feeds
+Typesense), the list SQL in `data_loader.py`, `_ofsted_block`, and two separate
+"latest row" picks over `marts.fact_ofsted_inspection` by `inspection_date`,
+which tie arbitrarily on duplicate monthly rows.
+
+## Non-goals
+
+- Predecessor inspections (audit M10): a grade Ofsted attributes to a previous
+  URN stays unlabelled.
+- Post-16 and ISI-inspected schools (H4) and the "Not yet inspected" label.
+- The compare page's broken Ofsted link (M4).
+- Report-card display, which is unchanged.
+
+## The rule
+
+Computed once per URN in `int_ofsted_latest`.
+
+**Latest visit:** the newest of RC, G and U.
+
+- `latest_visit_date`
+- `latest_visit_kind`: `report_card`, `graded` or `ungraded`
+- `latest_visit_outcome`: the ungraded outcome text when the kind is `ungraded`,
+  otherwise null
+
+**Current grade:** the overall grade still in force, if any.
+
+| Situation | `current_grade` | `current_grade_date` | `current_grade_basis` |
+|---|---|---|---|
+| A report card exists | null | null | null |
+| Latest is graded, overall 1–4 | that grade | G | `graded` |
+| Latest is graded, "Not judged" | null | null | null |
+| Latest is ungraded, outcome "School remains X…" (any qualifier) | X | U | `confirmed` |
+| Latest is ungraded, any other outcome ("Standards maintained", "Improved significantly", "Some aspects not as strong") | the graded inspection's overall grade if it is 1–4, else null | G when a grade is kept | `graded` when a grade is kept |
+| No inspection | null | null | null |
+
+A report card replaced overall grades, so no legacy grade stays in force beside
+one. The latest visit is read from the dates, not assumed: report cards began in
+November 2025, after the last legacy graded and ungraded inspections, and in
+Ofsted's 31 Aug 2026 data no school has a legacy visit newer than its report
+card, but the rule does not depend on that. Ties between G, U and RC on the same
+date resolve in the order report card, graded, ungraded.
+
+Invariants: `current_grade` is null or 1–4; `current_grade_date <=
+latest_visit_date`; `current_grade_basis` is null exactly when `current_grade`
+is null.
+
+### Expected results (Ofsted MI as at 31 Aug 2026)
+
+| URN | School | Ofsted data | Latest visit | Current grade |
+|---|---|---|---|---|
+| 102408 | Rabbsfarm Primary School | G 17 Jun 2025 Not judged; U 6 Feb 2020 remains Good | graded, 17 Jun 2025 | none |
+| 151783 | Acre Wood Academy | G 1 Oct 2024 Not judged; U 14 Mar 2023 remains Good (Concerns) | graded, 1 Oct 2024 | none |
+| 139888 | Washwood Heath Academy | G 3 Mar 2020 Good; U 21 May 2025 Standards maintained | ungraded, 21 May 2025, "Standards maintained" | Good, 3 Mar 2020, graded |
+| 104762 | Robins Lane Community Primary | G 7 Jan 2020 Good; U 18 Jul 2024 School remains Good | ungraded, 18 Jul 2024 | Good, 18 Jul 2024, confirmed |
+| 100094 | Royal Free Hospital Children's School | G 9 Oct 2019 Outstanding; U 5 Feb 2025 Some aspects not as strong | ungraded, 5 Feb 2025 | Outstanding, 9 Oct 2019, graded |
+| 136454 | Oakgrove School | U 13 Nov 2024 Standards maintained only | ungraded, 13 Nov 2024 | none |
+| 137086 | Bishop Stopford School | G 1 Apr 2025 Not judged | graded, 1 Apr 2025 | none |
+| 110048 | The Willink School | U 5 Oct 2023 remains Good; RC 6 May 2026 | report card, 6 May 2026 | none (report card shown) |
+| 149612 | St Michael's Catholic School | RC 10 Feb 2026 only | report card, 10 Feb 2026 | none (report card shown) |
+
+## What each page shows
+
+**Search and map badge** (`buildOfstedListBadge`), first match wins:
+
+1. Report card: "Report Card · *RC year*" (unchanged)
+2. Current grade: "*Grade* · *year of `current_grade_date`*"
+3. Latest visit: "Inspected · *year of `latest_visit_date`*"
+4. "Not yet inspected" (unchanged)
+
+**School page** (`OfstedSection`, both phases):
+
+- Title date: "Inspected *latest visit date*".
+- Headline: the report card; or the current grade with a source line
+  ("Graded inspection, 6 July 2016" or "Confirmed at an ungraded inspection,
+  14 March 2023"); or "No overall grade" with "Ofsted stopped giving overall
+  grades in September 2024".
+- "Latest visit" line when the latest visit is not the grade's source, e.g.
+  "Ungraded inspection, 13 Nov 2024: Standards maintained".
+- The area grid shows the graded inspection's judgements through
+  `ofstedLegacyAreas()`, dated by that inspection when it is not the latest
+  visit. The primary and secondary no-grade branches merge into one; the
+  secondary branch's four hard-coded areas (audit M2) go with it.
+
+**Compare:** `ofstedDisplay` returns `report_card`, `graded`, `confirmed`,
+`no_overall_grade` or `none`. The "Latest Ofsted inspection", "Result" and
+"Inspected" rows use the same fields as the school page.
+
+## Delivery
+
+Two pull requests. The mart columns exist before anything reads them, so
+neither needs compatibility code.
+
+### PR 1: pipeline (additive)
+
+- `stg_ofsted_inspections`: keep `graded_inspection_date`,
+  `ungraded_inspection_date` and `rc_inspection_date` as separate typed
+  columns, with the report-card date's existing guard. Keep `inspection_date`
+  (graded, else ungraded) for the current backend. Keep a row when any of the
+  three dates is present, so report-card-only schools are no longer dropped
+  (part of H3: 123 schools).
+- `int_ofsted_latest`: pick one row per URN by `latest_visit_date` descending,
+  then `rc_inspection_date`, `ungraded_inspection_date` and
+  `graded_inspection_date` descending (nulls last). A duplicate monthly row that
+  carries a newer report card therefore always wins. Add the five status
+  columns.
+- New mart `marts.fact_ofsted_latest`: one row per URN from `int_ofsted_latest`
+  with every column the pages need (status, area grades, report-card grades,
+  ungraded outcome, report URL). It does not join `dim_school`, so only the
+  monthly Ofsted DAG builds it.
+- `dim_school` is not changed in PR 1: the daily DAG does not rebuild
+  `int_ofsted_latest`, and reading a column that the monthly DAG has not yet
+  built would fail the daily run.
+- Visible effect: report-card-only schools gain their report card, because the
+  backend's existing reads of `fact_ofsted_inspection` now see their rows.
+  Nothing else changes.
+
+### PR 2: backend and UI (after the Ofsted DAG has run on PR 1)
+
+- `data_loader.py`: the list query and the batch query read
+  `marts.fact_ofsted_latest` instead of picking the latest row of
+  `fact_ofsted_inspection`. `_ofsted_block` reads the status columns and loses
+  its fallback to `ungraded_grade`.
+- List rows: `ofsted_grade` becomes `current_grade`; `ofsted_date` becomes
+  `latest_visit_date`; new `ofsted_grade_date`. `ofsted_rc_date` stays.
+- `ofsted` block: `overall_effectiveness` and `inspection_date` are the graded
+  inspection's own result and date (they label the area grid); new
+  `current_grade` `{grade, date, basis}` (or null) and `latest_visit`
+  `{date, kind, outcome}`; `grade_source` is removed. Report-card fields are
+  unchanged.
+- `dim_school.ofsted_grade` becomes `current_grade` (Typesense's rating follows
+  at the next sync); `ofsted_date` becomes `latest_visit_date`.
+- Sitemap: `lastmod` from `latest_visit_date`; `_PUBLISHABLE_FIELDS` also counts
+  a latest visit, so schools that lose a carried grade keep their sitemap entry.
+- Front end: `lib/types.ts`, `buildOfstedListBadge`, `OfstedSection`,
+  `PrimarySchoolSections`, `SecondarySchoolSections`, `compareLogic.ofstedDisplay`,
+  `CompareAtAGlance`, `CompareOfsted`. Place-page counts need no change.
+- Delete `buildOfstedHeroChip` and `buildSchoolSummary` in their own commit:
+  nothing renders them and they encode the old rule.
+
+## Testing
+
+**PR 1**
+
+- dbt unit tests on `int_ofsted_latest`, one per table row above plus
+  "report-card only" and "duplicate rows, newer report card wins".
+- Schema tests on `fact_ofsted_latest`: unique, not-null `urn`; accepted values
+  for `latest_visit_kind` and `current_grade_basis`; `current_grade` null or
+  1–4; `current_grade_date <= latest_visit_date`.
+- Run locally against a throwaway Postgres from `pgserver` (no Docker here). If
+  that fails, they still run in the Ofsted DAG's `dbt build`, which fails on any
+  broken case.
+- `pipeline/tests/test_dag_selectors.py` (PR #181) keeps passing.
+
+**PR 2**
+
+- pytest: contract test for the list and `ofsted` fields (style of
+  `test_school_page_flag_fields.py`); `_ofsted_block` from a
+  `fact_ofsted_latest` row; sitemap publishability.
+- Jest: a badge case per table row; `OfstedSection` for graded, confirmed, no
+  grade, the latest-visit line and a sixth-form area; `ofstedDisplay` kinds.
+  Rewrite tests that assert `carried_forward`.
+- E2E (same PR): Rabbsfarm's search row says "Inspected · 2025" and its page
+  says "No overall grade" with quality of education Requires Improvement; a
+  confirmed school says "Confirmed at an ungraded inspection". The existing
+  report-card journey stays.
+
+## Rollout and verification
+
+1. Merge PR 1. On staging, run `school_data_monthly_ofsted`, then:
+
+   ```sql
+   -- one row per school
+   select count(*) = count(distinct urn) from marts.fact_ofsted_latest;
+   -- the examples above
+   select urn, latest_visit_date, latest_visit_kind, latest_visit_outcome,
+          current_grade, current_grade_date, current_grade_basis
+   from marts.fact_ofsted_latest
+   where urn in (102408, 151783, 139888, 104762, 100094, 136454, 137086, 110048, 149612);
+   -- C1: a grade in force although the latest inspection gave none (expect 0)
+   select count(*) from marts.fact_ofsted_latest
+   where current_grade is not null and latest_visit_kind = 'graded'
+     and overall_effectiveness is null;
+   -- invariant (expect 0)
+   select count(*) from marts.fact_ofsted_latest where current_grade_date > latest_visit_date;
+   ```
+
+   Check through the API that St Michael's Catholic School (149612) shows its
+   report card.
+2. Promote PR 1 to production; run the Ofsted DAG there; repeat the checks.
+3. Merge PR 2. Let the daily DAG run (or trigger it) so `dim_school` and
+   Typesense pick up the change; run the E2E journeys; re-run the audit's C1,
+   M1 and M2 checks against staging: expect 0.
+4. Promote PR 2; repeat the audit checks on production.
+
+## Expected visible change
+
+About 932 schools change from a grade badge dated by a no-grade inspection
+("Good · 2025") to "Inspected · 2025". Counts of Good and Outstanding schools
+on place pages fall by the same schools, and Typesense's rating changes for
+them. Dates beside a grade can move earlier (to the inspection that awarded
+it); "Inspected" dates move later (to the latest visit).
+
+## Risks
+
+- Ofsted changes its MI columns most months. Unknown grade text parses to null
+  (`safe_numeric`), which degrades to "Inspected · year", never to a wrong grade.
+- PR 2 depends on the Ofsted DAG having run on the target environment after PR
+  1. If PR 2 is promoted first, the backend reads a missing table: promote in
+  order.
@@ -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.
@@ -474,6 +474,37 @@ test('a report-card school shows a Report Card badge in search results, not its
  await expect(page.getByText(/Report Card ·/).first()).toBeVisible();
 });

+test('a school whose latest inspection gave no grade is not shown with an older grade', async ({ page }) => {
+  // Rabbsfarm (102408): Ofsted's 17 June 2025 inspection gave no overall
+  // grade and rated three areas Requires Improvement. The site used to carry
+  // a 2020 "remains Good" forward and print "Good · 2025" (audit C1).
+  const URN = 102408;
+  const res = await page.request.get(`/api/schools/${URN}`);
+  expect(res.ok()).toBeTruthy();
+  const ofsted = (await res.json()).ofsted;
+  expect(ofsted.current_grade).toBeNull();
+  expect(ofsted.latest_visit.date).toBe('2025-06-17');
+
+  await searchByName(page, 'Rabbsfarm Primary');
+  const row = page.locator(`a[href*="${URN}"]`).first();
+  await expect(row).toBeVisible({ timeout: 15_000 });
+  await expect(page.getByText('Inspected · 2025').first()).toBeVisible();
+  await expect(page.getByText(/Good · 2025/)).toHaveCount(0);
+
+  await page.goto(`/school/${URN}`);
+  const section = page.locator('#ofsted');
+  await expect(section.getByText('No overall grade')).toBeVisible();
+  await expect(section.getByText('Requires Improvement').first()).toBeVisible();
+});
+
+test('a grade confirmed at an ungraded visit says so and is dated by it', async ({ page }) => {
+  const URN = 104762; // Robins Lane: graded Good Jan 2020, "School remains Good" 18 July 2024
+  await page.goto(`/school/${URN}`);
+  const section = page.locator('#ofsted');
+  await expect(section).toBeVisible({ timeout: 15_000 });
+  await expect(section.getByText('Confirmed at an ungraded inspection, 18 July 2024')).toBeVisible();
+});
+
 test('school detail page renders name and performance data', async ({ page }) => {
  await searchByName(page, 'primary');
  const firstSchool = schoolLinks(page).first();
@@ -33,18 +33,26 @@ function ofsted(partial: Partial<OfstedInspection>): OfstedInspection {
  };
 }

-const schools = [school(1, 'Graded School'), school(2, 'Carried School'), school(3, 'Card School')];
+const schools = [school(1, 'Graded School'), school(2, 'Confirmed School'), school(3, 'Card School')];

 const data: Record<string, ComparisonData> = {
  '1': {
    school_info: schools[0],
    yearly_data: [],
-    ofsted: ofsted({ overall_effectiveness: 1, grade_source: 'graded' }),
+    ofsted: ofsted({
+      overall_effectiveness: 1,
+      current_grade: { grade: 1, date: '2021-10-07', basis: 'graded' },
+      latest_visit: { date: '2021-10-07', kind: 'graded', outcome: null },
+    }),
  },
  '2': {
    school_info: schools[1],
    yearly_data: [],
-    ofsted: ofsted({ overall_effectiveness: 2, grade_source: 'ungraded_carried_forward' }),
+    ofsted: ofsted({
+      overall_effectiveness: 2,
+      current_grade: { grade: 2, date: '2023-03-14', basis: 'confirmed' },
+      latest_visit: { date: '2023-03-14', kind: 'ungraded', outcome: 'School remains Good' },
+    }),
  },
  '3': {
    school_info: schools[2],
@@ -65,9 +73,9 @@ describe('CompareOfsted', () => {
    render(<CompareOfsted schools={schools} data={data} />);

    expect(screen.getByText('Outstanding')).toBeInTheDocument();
-    // Carried-forward grade is shown but marked as such
+    // A grade confirmed at an ungraded visit says so, with that visit's date
    expect(screen.getByText('Good')).toBeInTheDocument();
-    expect(screen.getByText(/carried forward/i)).toBeInTheDocument();
+    expect(screen.getByText('Confirmed at an ungraded inspection, 14 March 2023')).toBeInTheDocument();
    // Report card: label present, no overall-grade badge for that school
    expect(screen.getByText('Report card')).toBeInTheDocument();
    expect(screen.getByText(/no overall grade/i)).toBeInTheDocument();
@@ -104,7 +112,7 @@ describe('CompareOfsted', () => {
        yearly_data: [],
        ofsted: ofsted({
          overall_effectiveness: 2,
-          grade_source: 'graded',
+          current_grade: { grade: 2, date: '2021-10-07', basis: 'graded' },
          quality_of_education: 1,
          early_years_provision: 9,
          sixth_form_provision: 2,
@@ -167,4 +175,26 @@ describe('CompareOfsted', () => {
    expect(screen.getAllByText('Graded').length).toBe(4);
    expect(screen.getAllByText('Card').length).toBe(4);
  });
+
+  it('dates "Inspected" by the latest visit, not the graded inspection (Washwood Heath)', () => {
+    const s1 = school(7, 'Washwood Heath Academy');
+    render(<CompareOfsted schools={[s1]} data={{ '7': { school_info: s1, yearly_data: [], ofsted: ofsted({
+      inspection_date: '2020-03-03', overall_effectiveness: 2, quality_of_education: 2,
+      current_grade: { grade: 2, date: '2020-03-03', basis: 'graded' },
+      latest_visit: { date: '2025-05-21', kind: 'ungraded', outcome: 'Standards maintained' },
+    }) } }} />);
+    expect(screen.getByText(/21 May 2025/)).toBeInTheDocument();
+    expect(screen.queryByText('4+ years ago')).not.toBeInTheDocument();
+    expect(screen.getByText('Graded inspection, 3 March 2020')).toBeInTheDocument();
+  });
+
+  it('shows no overall grade after a no-grade inspection (Rabbsfarm)', () => {
+    const s1 = school(8, 'Rabbsfarm Primary School');
+    render(<CompareOfsted schools={[s1]} data={{ '8': { school_info: s1, yearly_data: [], ofsted: ofsted({
+      inspection_date: '2025-06-17', quality_of_education: 3,
+      current_grade: null, latest_visit: { date: '2025-06-17', kind: 'graded', outcome: null },
+    }) } }} />);
+    expect(screen.getAllByText('No overall grade').length).toBeGreaterThan(0);
+    expect(screen.queryByText('Good')).not.toBeInTheDocument();
+  });
 });
@@ -8,7 +8,6 @@
 import { render, screen } from '@testing-library/react';

 import {
-  nearbyNoun,
  NearbySchoolsSection,
  shouldRenderNearby,
 } from '@/components/school/NearbySchoolsSection';
@@ -43,13 +42,7 @@ function school(overrides: Partial<NearbySchool> = {}): NearbySchool {

 function renderSection(nearby: NearbySchool[]) {
  return render(
-    <NearbySchoolsSection
-      urn={100001}
-      schoolName="Meadowbrook Primary School"
-      phase="Primary"
-      thisMetricValue={72}
-      nearby={nearby}
-    />,
+    <NearbySchoolsSection urn={100001} thisMetricValue={72} nearby={nearby} />,
  );
 }

@@ -90,15 +83,10 @@ describe('what the section claims', () => {
  });

  it('shows no chips at all when nothing is shared, rather than inventing one', () => {
-    const { container } = render(
-      <NearbySchoolsSection
-        urn={100001}
-        schoolName="Meadowbrook Primary School"
-        phase="Primary"
-        thisMetricValue={72}
-        nearby={[school({ shared: [] }), school({ urn: 100003, shared: [] })]}
-      />,
-    );
+    const { container } = renderSection([
+      school({ shared: [] }),
+      school({ urn: 100003, shared: [] }),
+    ]);
    // The card still carries its distance, name, type and figure — just no
    // claim of likeness.
    expect(container.querySelectorAll('li ul').length).toBe(0);
@@ -106,37 +94,6 @@ describe('what the section claims', () => {
  });
 });

-describe('what the lede calls the set', () => {
-  it.each([
-    ['Primary', 'primary schools'],
-    ['Middle deemed primary', 'primary schools'],
-    ['Secondary', 'secondary schools'],
-    ['Middle deemed secondary', 'secondary schools'],
-    ['All-through', 'all-through schools'],
-    // GIAS phase 6. Its candidates span the whole secondary group, so no
-    // single noun fits and it takes the honest general one.
-    ['16 plus', 'schools and colleges'],
-    ['', 'schools'],
-    [null, 'schools'],
-  ])('calls a %s school\'s neighbours "%s"', (phase, expected) => {
-    expect(nearbyNoun(phase)).toBe(expected);
-  });
-
-  it('never calls a sixth form college\'s neighbours primary schools', () => {
-    render(
-      <NearbySchoolsSection
-        urn={100001}
-        schoolName="Barnet Sixth Form College"
-        phase="16 plus"
-        thisMetricValue={null}
-        nearby={[school(), school({ urn: 100003 })]}
-      />,
-    );
-    expect(screen.getByText(/Other schools and colleges near Barnet Sixth Form College/)).toBeInTheDocument();
-    expect(screen.queryByText(/primary schools/)).not.toBeInTheDocument();
-  });
-});
-
 describe('cards', () => {
  it('links each school to its canonical slug', () => {
    renderSection([school(), school({ urn: 100003, school_name: 'Oakfield Primary School' })]);
@@ -0,0 +1,84 @@
+import { render, screen } from '@testing-library/react';
+import { OfstedSection } from '@/components/school/OfstedSection';
+import { ofstedLegacyAreas } from '@/lib/utils';
+import type { OfstedInspection } from '@/lib/types';
+
+const empty = {
+  framework: null, inspection_date: null, inspection_type: null, overall_effectiveness: null,
+  quality_of_education: null, behaviour_attitudes: null, personal_development: null,
+  leadership_management: null, early_years_provision: null, sixth_form_provision: null,
+  previous_overall: null, rc_safeguarding_met: null, rc_inclusion: null, rc_curriculum_teaching: null,
+  rc_achievement: null, rc_attendance_behaviour: null, rc_personal_development: null,
+  rc_leadership_governance: null, rc_early_years: null, rc_sixth_form: null, report_url: null,
+  report_card: {},
+} as OfstedInspection;
+
+// `page` mimics how each school page used to mount the section; the secondary
+// page's branch hard-coded four areas and dropped the sixth form (audit M2).
+function renderSection(o: Partial<OfstedInspection>, page: Record<string, unknown> = {}) {
+  const ofsted = { ...empty, ...o } as OfstedInspection;
+  render(
+    <OfstedSection
+      {...page}
+      ofsted={ofsted}
+      urn={1}
+      isReportCard={false}
+      ofstedInspectedDate={ofsted.latest_visit?.date ?? null}
+      oeifAllSameGrade={false}
+      oeifAreas={ofstedLegacyAreas(ofsted)}
+    />,
+  );
+}
+
+it('shows no overall grade, not an older one, after a no-grade inspection (Rabbsfarm)', () => {
+  renderSection({
+    inspection_date: '2025-06-17', quality_of_education: 3, behaviour_attitudes: 3,
+    personal_development: 2, leadership_management: 3, early_years_provision: 2,
+    current_grade: null, latest_visit: { date: '2025-06-17', kind: 'graded', outcome: null },
+  });
+  expect(screen.getByText(/Inspected 17 June 2025/)).toBeInTheDocument();
+  expect(screen.getByText('No overall grade')).toBeInTheDocument();
+  expect(screen.queryByText('Good', { selector: 'span' })).not.toBeInTheDocument();
+  expect(screen.queryByText('Not rated')).not.toBeInTheDocument();
+  expect(screen.getAllByText('Requires Improvement')).toHaveLength(3);
+});
+
+it('keeps the sixth-form judgement when there is no overall grade (audit M2)', () => {
+  renderSection({
+    inspection_date: '2025-04-01', quality_of_education: 1, behaviour_attitudes: 1,
+    personal_development: 1, leadership_management: 1, sixth_form_provision: 2,
+    current_grade: null, latest_visit: { date: '2025-04-01', kind: 'graded', outcome: null },
+  }, { variant: 'secondary' });
+  expect(screen.getByText(/sixth form/i)).toBeInTheDocument();
+  expect(screen.getByText('Good')).toBeInTheDocument();
+});
+
+it('dates a grade by its source and prints a later visit separately (Washwood Heath)', () => {
+  renderSection({
+    inspection_date: '2020-03-03', overall_effectiveness: 2, quality_of_education: 2,
+    current_grade: { grade: 2, date: '2020-03-03', basis: 'graded' },
+    latest_visit: { date: '2025-05-21', kind: 'ungraded', outcome: 'Standards maintained' },
+  });
+  expect(screen.getByText(/Inspected 21 May 2025/)).toBeInTheDocument();
+  expect(screen.getByText('Graded inspection, 3 March 2020')).toBeInTheDocument();
+  expect(screen.getByText(/Latest visit: Ungraded inspection, 21 May 2025: Standards maintained/)).toBeInTheDocument();
+});
+
+it('says a grade was confirmed at an ungraded visit (Robins Lane)', () => {
+  renderSection({
+    inspection_date: '2020-01-07', overall_effectiveness: 2,
+    current_grade: { grade: 2, date: '2024-07-18', basis: 'confirmed' },
+    latest_visit: { date: '2024-07-18', kind: 'ungraded', outcome: 'School remains Good' },
+  });
+  expect(screen.getByText('Confirmed at an ungraded inspection, 18 July 2024')).toBeInTheDocument();
+  expect(screen.queryByText(/Latest visit/)).not.toBeInTheDocument();
+});
+
+it('prints an ungraded outcome when there is no grade (Oakgrove)', () => {
+  renderSection({
+    current_grade: null,
+    latest_visit: { date: '2024-11-13', kind: 'ungraded', outcome: 'Standards maintained' },
+  });
+  expect(screen.getByText('No overall grade')).toBeInTheDocument();
+  expect(screen.getByText(/Ungraded inspection, 13 November 2024: Standards maintained/)).toBeInTheDocument();
+});
@@ -114,16 +114,13 @@ describe('ofstedDisplay', () => {
    expect(d.kind).toBe('report_card');
  });

-  it('distinguishes graded from carried-forward grades', () => {
-    const graded = ofstedDisplay(
-      ofsted({ overall_effectiveness: 1, grade_source: 'graded' }),
-    );
-    expect(graded).toMatchObject({ kind: 'graded', gradeLabel: 'Outstanding', carriedForward: false });
-
-    const carried = ofstedDisplay(
-      ofsted({ overall_effectiveness: 2, grade_source: 'ungraded_carried_forward' }),
-    );
-    expect(carried).toMatchObject({ kind: 'carried_forward', gradeLabel: 'Good', carriedForward: true });
+  it('distinguishes a graded grade from a confirmed one', () => {
+    expect(ofstedDisplay(ofsted({ current_grade: { grade: 1, date: '2019-10-09', basis: 'graded' },
+      latest_visit: { date: '2025-02-05', kind: 'ungraded', outcome: 'Some aspects not as strong' } })))
+      .toMatchObject({ kind: 'graded', gradeLabel: 'Outstanding', gradeDate: '2019-10-09' });
+    expect(ofstedDisplay(ofsted({ current_grade: { grade: 2, date: '2024-07-18', basis: 'confirmed' },
+      latest_visit: { date: '2024-07-18', kind: 'ungraded', outcome: 'School remains Good' } })))
+      .toMatchObject({ kind: 'confirmed', gradeLabel: 'Good' });
  });

  it('handles missing data', () => {
@@ -131,11 +128,11 @@ describe('ofstedDisplay', () => {
    expect(ofstedDisplay(ofsted({})).kind).toBe('none');
  });

-  it('identifies transitional inspections without overall grades', () => {
-    const transitional = ofstedDisplay(
-      ofsted({ overall_effectiveness: null, inspection_date: '2024-11-05' }),
-    );
-    expect(transitional.kind).toBe('transitional');
+  it('has no overall grade when the latest inspection gave none', () => {
+    // Rabbsfarm: the graded inspection's own overall is null, so no grade is
+    // in force even though an older ungraded visit said "remains Good".
+    expect(ofstedDisplay(ofsted({ current_grade: null, overall_effectiveness: null,
+      latest_visit: { date: '2025-06-17', kind: 'graded', outcome: null } })).kind).toBe('no_overall_grade');
  });

  it('uses the four legacy grade words', () => {
@@ -0,0 +1,47 @@
+import { gradeSourceLine, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
+import type { OfstedInspection } from '@/lib/types';
+
+const base = { report_card: {} } as unknown as OfstedInspection;
+
+describe('gradeSourceLine', () => {
+  it('names the graded inspection and its date', () => {
+    expect(gradeSourceLine({ grade: 2, date: '2016-07-06', basis: 'graded' }))
+      .toBe('Graded inspection, 6 July 2016');
+  });
+  it('names the ungraded visit that confirmed it', () => {
+    expect(gradeSourceLine({ grade: 2, date: '2023-03-14', basis: 'confirmed' }))
+      .toBe('Confirmed at an ungraded inspection, 14 March 2023');
+  });
+});
+
+describe('latestVisitLine', () => {
+  it('prints the ungraded outcome', () => {
+    expect(latestVisitLine({ date: '2024-11-13', kind: 'ungraded', outcome: 'Standards maintained' }))
+      .toBe('Ungraded inspection, 13 November 2024: Standards maintained');
+  });
+  it('prints a graded visit without an outcome', () => {
+    expect(latestVisitLine({ date: '2025-06-17', kind: 'graded', outcome: null }))
+      .toBe('Graded inspection, 17 June 2025');
+  });
+});
+
+describe('showLatestVisitLine', () => {
+  it('shows a later visit than the one the grade came from', () => {
+    expect(showLatestVisitLine({ ...base,
+      current_grade: { grade: 2, date: '2020-03-03', basis: 'graded' },
+      latest_visit: { date: '2025-05-21', kind: 'ungraded', outcome: 'Standards maintained' } })).toBe(true);
+  });
+  it('hides it when the visit is the grade’s own source', () => {
+    expect(showLatestVisitLine({ ...base,
+      current_grade: { grade: 2, date: '2024-07-18', basis: 'confirmed' },
+      latest_visit: { date: '2024-07-18', kind: 'ungraded', outcome: 'School remains Good' } })).toBe(false);
+  });
+  it('shows an ungraded outcome when there is no grade', () => {
+    expect(showLatestVisitLine({ ...base, current_grade: null,
+      latest_visit: { date: '2024-11-13', kind: 'ungraded', outcome: 'Standards maintained' } })).toBe(true);
+  });
+  it('hides it for a graded visit with no grade (the title already dates it)', () => {
+    expect(showLatestVisitLine({ ...base, current_grade: null,
+      latest_visit: { date: '2025-06-17', kind: 'graded', outcome: null } })).toBe(false);
+  });
+});
@@ -147,23 +147,37 @@ describe('ofstedLegacyAreas', () => {

 describe('buildOfstedListBadge', () => {
  it('returns grade word + year for OEIF Outstanding', () => {
-    const badge = buildOfstedListBadge({ ofsted_grade: 1, ofsted_date: '2023-11-15', ofsted_framework: 'OEIF' });
+    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.cssClass).toBe('ofsted1');
  });

  it('returns grade word for each OEIF grade', () => {
-    expect(buildOfstedListBadge({ ofsted_grade: 2, ofsted_date: '2022-05-01' }).label).toBe('Good · 2022');
-    expect(buildOfstedListBadge({ ofsted_grade: 3, ofsted_date: '2021-01-01' }).label).toBe('Req. Improvement · 2021');
-    expect(buildOfstedListBadge({ ofsted_grade: 4, ofsted_date: '2020-03-01' }).label).toBe('Inadequate · 2020');
+    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: 4, ofsted_grade_date: '2020-03-01' }).label).toBe('Inadequate · 2020');
  });

-  it('returns grade word without year when date is missing', () => {
-    const badge = buildOfstedListBadge({ ofsted_grade: 2, ofsted_date: null });
+  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.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');
+  });
+
+  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.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');
@@ -191,7 +191,8 @@ export const primaryFixture = {
    personal_development: 2,
    leadership_management: 2,
    previous_overall: 3,
-    grade_source: 'graded',
+    current_grade: { grade: 2, date: '2023-05-17', basis: 'graded' },
+    latest_visit: { date: '2023-05-17', kind: 'graded', outcome: null },
  }),
  census,
  admissions: makeAdmissions({ year: 2024 }),
@@ -295,7 +296,8 @@ export const allThroughFixture = {
    behaviour_attitudes: 1,
    personal_development: 1,
    leadership_management: 1,
-    grade_source: 'graded',
+    current_grade: { grade: 1, date: '2022-10-04', basis: 'graded' },
+    latest_visit: { date: '2022-10-04', kind: 'graded', outcome: null },
  }),
  census,
  admissions: makeAdmissions({ year: 2024, school_phase: 'Secondary' }),
@@ -78,21 +78,18 @@ export function CompareAtAGlance({
          return (
            <Cell key={school.urn} school={school} index={i}>
              {display.kind === 'report_card' && <ReportCardChips summary={display.summary} />}
-              {(display.kind === 'graded' || display.kind === 'carried_forward') && (
+              {(display.kind === 'graded' || display.kind === 'confirmed') && (
                <>
                  <span className={`${s.badge} ${display.grade <= 2 ? s.badgeGood : display.grade === 3 ? s.badgeWarn : s.badgeBad}`}>
                    {display.gradeLabel}
                  </span>
-                  {display.carriedForward && <span className={s.small}>Grade carried forward</span>}
+                  {display.kind === 'confirmed' && <span className={s.small}>Confirmed at an ungraded inspection</span>}
                </>
              )}
-              {display.kind === 'transitional' && (
-                <>
-                  <span className={s.badge} style={{ backgroundColor: 'var(--bg-secondary)', color: 'var(--text-secondary)' }}>
-                    No overall grade
-                  </span>
-                  <span className={s.small}>Sub-judgements only</span>
-                </>
+              {display.kind === 'no_overall_grade' && (
+                <span className={s.badge} style={{ backgroundColor: 'var(--bg-secondary)', color: 'var(--text-secondary)' }}>
+                  No overall grade
+                </span>
              )}
              {display.kind === 'none' && <span className={s.small}>No inspection in our dataset</span>}
            </Cell>
@@ -1,7 +1,8 @@
 /**
 * Ofsted section — one visual grammar for inspection detail across all
- * three regimes (legacy graded, interim carried-forward, renewed-framework
- * report card). Copy comes verbatim from the reviewed mockups.
+ * three regimes (legacy graded, no overall grade, renewed-framework report
+ * card). The grade shown is the one still in force, with the inspection that
+ * awarded or confirmed it; "Inspected" is the latest visit.
 */

 'use client';
@@ -12,7 +13,8 @@ import {
  rcAreaLabel,
  type OfstedDisplay,
 } from '@/lib/compareLogic';
-import type { ComparisonData, OfstedInspection, School } from '@/lib/types';
+import { gradeSourceLine } from '@/lib/ofstedStatus';
+import type { ComparisonData, OfstedCurrentGrade, OfstedInspection, School } from '@/lib/types';
 import { Cell, Chip, Measure, Section, SectionGrid, sectionStyles as s } from './sectionShared';

 const GRADE_TONE: Record<number, 'good' | 'warn' | 'bad'> = {
@@ -39,7 +41,7 @@ function yearsSince(iso: string | null): number | null {
  return (Date.now() - d.getTime()) / (365.25 * 24 * 3600 * 1000);
 }

-function ResultCell({ display }: { display: OfstedDisplay }) {
+function ResultCell({ display, current }: { display: OfstedDisplay; current: OfstedCurrentGrade | null }) {
  if (display.kind === 'none') {
    return <span className={s.small}>No inspection outcome in our dataset</span>;
  }
@@ -51,15 +53,13 @@ function ResultCell({ display }: { display: OfstedDisplay }) {
      </>
    );
  }
-  if (display.kind === 'transitional') {
+  if (display.kind === 'no_overall_grade') {
    return (
      <>
        <span className={s.badge} style={{ backgroundColor: 'var(--bg-secondary)', color: 'var(--text-secondary)' }}>
          No overall grade
        </span>
-        <span className={s.small}>
-          Inspected under transitional framework (sub-judgements only)
-        </span>
+        <span className={s.small}>Ofsted stopped giving overall grades in September 2024</span>
      </>
    );
  }
@@ -68,11 +68,7 @@ function ResultCell({ display }: { display: OfstedDisplay }) {
      <span className={`${s.badge} ${display.grade <= 2 ? s.badgeGood : display.grade === 3 ? s.badgeWarn : s.badgeBad}`}>
        {display.gradeLabel}
      </span>
-      <span className={s.small}>
-        {display.carriedForward
-          ? 'Grade carried forward from an earlier inspection (ungraded visit since)'
-          : 'Overall grade (older-style inspection)'}
-      </span>
+      {current && <span className={s.small}>{gradeSourceLine(current)}</span>}
    </>
  );
 }
@@ -179,7 +175,7 @@ export function CompareOfsted({
        <Measure label="Result">
        {schools.map((school, i) => (
          <Cell key={school.urn} school={school} index={i}>
-            <ResultCell display={displays[i]} />
+            <ResultCell display={displays[i]} current={data[String(school.urn)]?.ofsted?.current_grade ?? null} />
          </Cell>
        ))}
        </Measure>
@@ -187,19 +183,15 @@ export function CompareOfsted({
        <Measure label="Inspected">
        {schools.map((school, i) => {
          const ofsted = data[String(school.urn)]?.ofsted;
-          // A report card is dated by its OWN inspection date. The legacy
-          // inspection_date belongs to an older inspection and must never
-          // be shown against a report card (report cards exist only from
-          // Nov 2025).
-          const dateIso =
-            displays[i].kind === 'report_card'
-              ? ofsted?.rc_inspection_date ?? null
-              : ofsted?.inspection_date ?? null;
+          // The school's latest visit of any kind (a report card's own date
+          // when there is one), never an older inspection a grade comes from.
+          const dateIso = ofsted?.latest_visit?.date ?? ofsted?.rc_inspection_date ?? null;
          const age = yearsSince(dateIso);
          return (
            <Cell key={school.urn} school={school} index={i}>
              {formatInspectionDate(dateIso)}{' '}
              {age != null && age > 4 && <Chip tone="neutral">4+ years ago</Chip>}
+              {ofsted?.latest_visit?.outcome && <span className={s.small}>{ofsted.latest_visit.outcome}</span>}
            </Cell>
          );
        })}
@@ -1,8 +1,7 @@
 .heading { font-family: var(--font-display); font-size: 1.4rem; letter-spacing: -0.4px; margin: 0; }
-.lede { margin: 0.5rem 0 1.25rem; color: var(--text-secondary); max-width: 64ch; }
 .caption { margin: 1rem 0 0; font-size: 0.72rem; color: var(--text-muted); }

-.top { display: flex; align-items: flex-start; justify-content: space-between; gap: 1rem; }
+.top { display: flex; align-items: center; justify-content: space-between; gap: 1rem; margin-bottom: 1.25rem; }
 .arrows { display: flex; gap: 0.5rem; flex: none; }
 .arrow { width: 44px; height: 44px; display: grid; place-items: center; cursor: pointer; border: 1px solid var(--border-strong); border-radius: 999px; background: var(--bg-card); color: var(--brand); }
 .arrow:hover:not(:disabled) { border-color: var(--brand); background: var(--brand-bg); }
@@ -15,10 +14,10 @@
 .scroller { display: grid; grid-auto-flow: column; grid-auto-columns: calc((100% - 1.8rem) / 3); gap: 0.9rem; overflow-x: auto; scroll-snap-type: x mandatory; padding: 2px; margin: -2px; list-style: none; scrollbar-width: none; -ms-overflow-style: none; }
 .scroller::-webkit-scrollbar { display: none; }
@media (max-width: 820px) { .scroller { grid-auto-columns: calc((100% - 0.9rem) / 2); } }
-/* Touch widths (MOBILE.md): the arrows would take 96px from a 328px card and
-   crush the lede into four lines, for a control swiping already provides. They
-   go, and the documented right-edge fade carries the affordance — lifting at
-   the end of the travel, where there is nothing more to hint at. */
+/* Touch widths (MOBILE.md): the arrows would take 96px from a 328px card, for
+   a control swiping already provides. They go, and the documented right-edge
+   fade carries the affordance — lifting at the end of the travel, where there
+   is nothing more to hint at. */
@media (max-width: 640px) {
  .top { display: block; }
  .arrows { display: none; }
@@ -4,7 +4,7 @@
 * The scroller and its arrows.
 *
 * `children` are the server-rendered cards and `header` the server-rendered
- * heading and lede: both stay server components, passed through, so this file
+ * heading: both stay server components, passed through, so this file
 * owns a DOM ref and nothing else. That is what keeps all six links in the
 * initial HTML — a carousel that mounted cards on click would put four of the
 * six beyond a crawler and beyond a reader with no JavaScript.
@@ -13,8 +13,10 @@
 * reached on their behalf.
 *
 * There is deliberately no "how these are chosen" panel: the method is already
- * visible in the lede, the chips and the distances. The single caption line is
- * not a method note — it is the one thing a card cannot self-correct.
+ * visible in the chips and the distances. The single caption line is not a
+ * method note — it is the one thing a card cannot self-correct.
+ *
+ * Nor is there a lede: "Other primary schools near X" only restated the heading.
 */

 import Link from 'next/link';
@@ -32,28 +34,6 @@ export function shouldRenderNearby(nearby?: NearbySchool[] | null): boolean {
  return (nearby?.length ?? 0) >= MINIMUM;
 }

-/**
- * What the lede calls the set of schools it is showing.
- *
- * Derived from the school's own GIAS phase rather than the template it renders
- * with, because those disagree for "16 plus" (GIAS phase 6): a sixth-form
- * college renders the primary template — computeSchoolFlags tests for the
- * substring "secondary" — while the backend correctly matches it against the
- * secondary group. Taking the noun from the template would print "Other primary
- * schools near <sixth form college>" above a row of secondaries.
- *
- * A 16-plus school's candidates span the whole secondary group, so no single
- * noun fits and it gets the honest general one.
- */
-export function nearbyNoun(phase: string | null | undefined): string {
-  const text = (phase ?? '').trim().toLowerCase();
-  if (text === 'all-through') return 'all-through schools';
-  if (text === '16 plus') return 'schools and colleges';
-  if (text.includes('secondary')) return 'secondary schools';
-  if (text.includes('primary')) return 'primary schools';
-  return 'schools';
-}
-
 function metricLabel(key: string): string {
  return key === 'attainment_8_score' ? 'Attainment 8' : 'Reading, writing & maths';
 }
@@ -65,25 +45,16 @@ function formatMetric(value: number | null, key: string): string {

 export function NearbySchoolsSection({
  urn,
-  schoolName,
-  phase,
  thisMetricValue,
  nearby,
 }: {
  urn: number;
-  schoolName: string;
-  /** The school's own GIAS phase, not the template it renders with. */
-  phase: string | null | undefined;
  thisMetricValue: number | null;
  nearby?: NearbySchool[] | null;
 }) {
  if (!shouldRenderNearby(nearby)) return null;
  const schools = nearby as NearbySchool[];
-
-  // One card matched on phase alone, so the section may not claim the set
-  // shares an intake with this school.
  const metricKey = schools[0].metric_key;
-  const noun = nearbyNoun(phase);

  return (
    <Section id="nearby">
@@ -91,12 +62,9 @@ export function NearbySchoolsSection({
        count={schools.length}
        labelledBy="nearby-schools-heading"
        header={
-          <div>
-            <h2 id="nearby-schools-heading" className={styles.heading}>
-              Other schools nearby
-            </h2>
-            <p className={styles.lede}>{`Other ${noun} near ${schoolName}.`}</p>
-          </div>
+          <h2 id="nearby-schools-heading" className={styles.heading}>
+            Other schools nearby
+          </h2>
        }
      >
        {schools.map((school) => (
@@ -1,16 +1,18 @@
 /**
 * OfstedSection — shared between the primary and secondary detail pages.
 *
- * The two versions were ~80% identical, but that figure masked a real fork in
- * the no-overall-grade case: the primary page shows a "Not rated" badge, while
- * the secondary page shows a four-area OEIF panel. The disclaimer copy also
- * differs slightly. Both are preserved exactly via the `variant` prop rather
- * than reconciled, because this refactor must not change either page. Merging
- * them is a follow-up decision for a human, not a side effect of a move.
+ * The headline is the school's current Ofsted status (backend:
+ * fact_ofsted_latest): the report card; or the overall grade still in force,
+ * with the inspection that awarded or confirmed it; or "No overall grade".
+ * A later visit that is not the grade's source gets its own line. Both pages
+ * render the same branches, so neither drops an area judgement (audit M2).
+ * Rule: docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md
 *
 * Server component.
 */

+import { ofstedDisplay } from '@/lib/compareLogic';
+import { formatOfstedDate, gradeSourceLine, latestVisitLine, showLatestVisitLine } from '@/lib/ofstedStatus';
 import type { OfstedInspection } from '@/lib/types';
 import { Section, sectionStyles as styles } from './sectionShared';

@@ -40,14 +42,13 @@ export interface OfstedSectionProps {
  ofstedInspectedDate: string | null;
  oeifAllSameGrade: boolean;
  oeifAreas: { label: string; value: number }[];
-  variant?: 'primary' | 'secondary';
 }

 export function OfstedSection({
  ofsted, urn, isReportCard, ofstedInspectedDate,
-  oeifAllSameGrade, oeifAreas, variant = 'primary',
+  oeifAllSameGrade, oeifAreas,
 }: OfstedSectionProps) {
-  const isSecondary = variant === 'secondary';
+  const display = ofstedDisplay(ofsted);

  return (
    <Section id="ofsted">
@@ -55,7 +56,7 @@ export function OfstedSection({
        {isReportCard ? 'Ofsted Report Card' : 'Ofsted Rating'}
        {ofstedInspectedDate && (
          <span className={styles.ofstedDate}>
-            {isSecondary && ' '}Inspected {new Date(ofstedInspectedDate).toLocaleDateString('en-GB', { day: 'numeric', month: 'long', year: 'numeric' })}
+            Inspected {new Date(ofstedInspectedDate).toLocaleDateString('en-GB', { day: 'numeric', month: 'long', year: 'numeric' })}
          </span>
        )}
        <a
@@ -98,65 +99,55 @@ export function OfstedSection({
            })}
          </div>
        </>
-      ) : (!isSecondary || ofsted.overall_effectiveness) ? (
-        /* ── Old OEIF layout ── */
+      ) : (
        <>
          <div className={styles.ofstedHeader}>
-            <span className={`${styles.ofstedGrade} ${styles[`ofstedGrade${ofsted.overall_effectiveness}`]}`}>
-              {ofsted.overall_effectiveness ? OFSTED_LABELS[ofsted.overall_effectiveness] : 'Not rated'}
-            </span>
-            {ofsted.previous_overall != null &&
-              ofsted.previous_overall !== ofsted.overall_effectiveness && (
-              <span className={styles.ofstedPrevious}>
-                Previously: {OFSTED_LABELS[ofsted.previous_overall]}
-              </span>
+            {display.kind === 'graded' || display.kind === 'confirmed' ? (
+              <>
+                <span className={`${styles.ofstedGrade} ${styles[`ofstedGrade${display.grade}`]}`}>
+                  {display.gradeLabel}
+                </span>
+                {ofsted.previous_overall != null && ofsted.previous_overall !== display.grade && (
+                  <span className={styles.ofstedPrevious}>
+                    Previously: {OFSTED_LABELS[ofsted.previous_overall]}
+                  </span>
+                )}
+              </>
+            ) : (
+              <span className={styles.ofstedGrade}>No overall grade</span>
            )}
          </div>
          <p className={styles.ofstedDisclaimer}>
-            {ofsted.grade_source === 'ungraded_carried_forward'
-              ? 'This overall grade is carried forward from an earlier inspection. Ofsted has since visited without issuing a new overall grade. From September 2024, Ofsted no longer makes an overall effectiveness judgement.'
-              : isSecondary
-                ? 'From September 2024, Ofsted no longer makes an overall effectiveness judgement in inspections.'
-                : 'From September 2024, Ofsted no longer makes an overall effectiveness judgement in inspections of state-funded schools.'}
+            {ofsted.current_grade
+              ? gradeSourceLine(ofsted.current_grade)
+              : 'Ofsted stopped giving overall grades in September 2024.'}
          </p>
-          {oeifAllSameGrade ? (
-            <p className={styles.ofstedAllSame}>
-              Rated <strong>{OFSTED_LABELS[ofsted.overall_effectiveness!]}</strong> across all inspected areas: Quality of Teaching, Behaviour, Pupils&apos; Development and Leadership.
-            </p>
-          ) : (
-            <div className={`${styles.metricsGrid} ${styles.gradeGrid}`}>
-              {oeifAreas.map(({ label, value }) => (
-                <div key={label} className={styles.metricCard}>
-                  <div className={styles.metricLabel}>{label}</div>
-                  <div className={`${styles.metricValue} ${styles[`ofstedGrade${value}`]}`}>
-                    {OFSTED_LABELS[value]}
-                  </div>
-                </div>
-              ))}
-            </div>
+          {showLatestVisitLine(ofsted) && ofsted.latest_visit && (
+            <p className={styles.ofstedDisclaimer}>Latest visit: {latestVisitLine(ofsted.latest_visit)}</p>
          )}
-        </>
-      ) : (
-        /* ── Secondary only: inspected since Sept 2024, no overall grade ── */
-        <>
-          <p className={styles.sectionSubtitle}>
-            From September 2024, Ofsted no longer gives a single overall grade.
-          </p>
-          <div className={`${styles.metricsGrid} ${styles.gradeGrid}`}>
-            {[
-              { label: 'Quality of Education', value: ofsted.quality_of_education },
-              { label: 'Behaviour & Attitudes', value: ofsted.behaviour_attitudes },
-              { label: 'Personal Development', value: ofsted.personal_development },
-              { label: 'Leadership & Management', value: ofsted.leadership_management },
-            ].filter(({ value }) => value != null).map(({ label, value }) => (
-              <div key={label} className={styles.metricCard}>
-                <div className={styles.metricLabel}>{label}</div>
-                <div className={`${styles.metricValue} ${styles[`ofstedGrade${value}`]}`}>
-                  {OFSTED_LABELS[value!]}
-                </div>
+          {oeifAllSameGrade && (display.kind === 'graded' || display.kind === 'confirmed') ? (
+            <p className={styles.ofstedAllSame}>
+              Rated <strong>{display.gradeLabel}</strong> across all inspected areas: Quality of Teaching, Behaviour, Pupils&apos; Development and Leadership.
+            </p>
+          ) : oeifAreas.length > 0 ? (
+            <>
+              {ofsted.inspection_date && ofsted.inspection_date !== ofsted.latest_visit?.date && (
+                <p className={styles.ofstedDisclaimer}>
+                  Area judgements from the graded inspection, {formatOfstedDate(ofsted.inspection_date)}.
+                </p>
+              )}
+              <div className={`${styles.metricsGrid} ${styles.gradeGrid}`}>
+                {oeifAreas.map(({ label, value }) => (
+                  <div key={label} className={styles.metricCard}>
+                    <div className={styles.metricLabel}>{label}</div>
+                    <div className={`${styles.metricValue} ${styles[`ofstedGrade${value}`]}`}>
+                      {OFSTED_LABELS[value]}
+                    </div>
+                  </div>
+                ))}
              </div>
-            ))}
-          </div>
+            </>
+          ) : null}
        </>
      )}
    </Section>
@@ -55,15 +55,17 @@ export function PrimarySchoolSections({
  const secondaryAvg = nationalAvg?.secondary ?? {};

  const isReportCard = !!(ofsted?.report_card && Object.keys(ofsted.report_card).length > 0);
-  // Report cards are dated by their own inspection (rc_inspection_date), never
-  // the legacy inspection_date (report cards exist only from Nov 2025).
+  // 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.
  const ofstedInspectedDate = isReportCard
    ? ofsted?.rc_inspection_date ?? null
-    : ofsted?.inspection_date ?? null;
+    : ofsted?.latest_visit?.date ?? null;
  const oeifAreas = ofsted ? ofstedLegacyAreas(ofsted) : [];
  const oeifAllSameGrade =
    !!ofsted &&
    !isReportCard &&
+    ofsted.overall_effectiveness != null &&
    oeifAreas.length >= 3 &&
    oeifAreas.every((a) => a.value === ofsted.overall_effectiveness);

@@ -77,7 +79,6 @@ export function PrimarySchoolSections({
          ofstedInspectedDate={ofstedInspectedDate}
          oeifAllSameGrade={oeifAllSameGrade}
          oeifAreas={oeifAreas}
-          variant="primary"
        />
      )}

@@ -154,8 +155,6 @@ export function PrimarySchoolSections({
      {/* Last: it is where the reader goes next, not part of this school. */}
      <NearbySchoolsSection
        urn={schoolInfo.urn}
-        schoolName={schoolInfo.school_name}
-        phase={schoolInfo.phase}
        thisMetricValue={flags.latestResults?.rwm_expected_pct ?? null}
        nearby={nearbySchools}
      />
@@ -63,11 +63,12 @@ export function SecondarySchoolSections({
  const isReportCard = !!(ofsted?.report_card && Object.keys(ofsted.report_card).length > 0);
  const ofstedInspectedDate = isReportCard
    ? ofsted?.rc_inspection_date ?? null
-    : ofsted?.inspection_date ?? null;
+    : ofsted?.latest_visit?.date ?? null;
  const oeifAreas = ofsted ? ofstedLegacyAreas(ofsted) : [];
  const oeifAllSameGrade =
    !!ofsted &&
    !isReportCard &&
+    ofsted.overall_effectiveness != null &&
    oeifAreas.length >= 3 &&
    oeifAreas.every((a) => a.value === ofsted.overall_effectiveness);

@@ -81,7 +82,6 @@ export function SecondarySchoolSections({
          ofstedInspectedDate={ofstedInspectedDate}
          oeifAllSameGrade={oeifAllSameGrade}
          oeifAreas={oeifAreas}
-          variant="secondary"
        />
      )}

@@ -148,8 +148,6 @@ export function SecondarySchoolSections({
      {/* Last: it is where the reader goes next, not part of this school. */}
      <NearbySchoolsSection
        urn={schoolInfo.urn}
-        schoolName={schoolInfo.school_name}
-        phase={schoolInfo.phase}
        thisMetricValue={flags.latestResults?.attainment_8_score ?? null}
        nearby={nearbySchools}
      />
@@ -100,9 +100,8 @@ export function summariseReportCard(ofsted: OfstedInspection): ReportCardSummary

 export type OfstedDisplay =
  | { kind: 'none' }
-  | { kind: 'graded'; grade: number; gradeLabel: string; carriedForward: false }
-  | { kind: 'carried_forward'; grade: number; gradeLabel: string; carriedForward: true }
-  | { kind: 'transitional' }
+  | { kind: 'graded' | 'confirmed'; grade: number; gradeLabel: string; gradeDate: string | null }
+  | { kind: 'no_overall_grade' }
  | { kind: 'report_card'; summary: ReportCardSummary };

 export function ofstedDisplay(
@@ -116,19 +115,19 @@ export function ofstedDisplay(
    return { kind: 'report_card', summary: summariseReportCard(ofsted) };
  }

-  const grade = ofsted.overall_effectiveness;
-  const gradeLabel = grade != null ? OFSTED_LEGACY_GRADES[grade] : undefined;
-  if (grade == null || gradeLabel === undefined) {
-    if (ofsted.inspection_date) {
-      return { kind: 'transitional' };
-    }
-    return { kind: 'none' };
+  // The grade still in force (backend: fact_ofsted_latest), never one carried
+  // past a later inspection that gave none.
+  const current = ofsted.current_grade;
+  const gradeLabel = current ? OFSTED_LEGACY_GRADES[current.grade] : undefined;
+  if (current && gradeLabel !== undefined) {
+    return {
+      kind: current.basis === 'confirmed' ? 'confirmed' : 'graded',
+      grade: current.grade,
+      gradeLabel,
+      gradeDate: current.date,
+    };
  }
-
-  if (ofsted.grade_source === 'ungraded_carried_forward') {
-    return { kind: 'carried_forward', grade, gradeLabel, carriedForward: true };
-  }
-  return { kind: 'graded', grade, gradeLabel, carriedForward: false };
+  return ofsted.latest_visit ? { kind: 'no_overall_grade' } : { kind: 'none' };
 }

 // ---------------------------------------------------------------------------
@@ -0,0 +1,48 @@
+/**
+ * The sentences the school page and the compare page print about where an
+ * Ofsted grade came from and what the latest visit was. One wording, two pages.
+ * Rule: docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md
+ */
+
+import type { OfstedCurrentGrade, OfstedInspection, OfstedLatestVisit } from './types';
+
+export function formatOfstedDate(iso: string | null | undefined): string {
+  if (!iso) return '';
+  const d = new Date(iso);
+  if (Number.isNaN(d.getTime())) return '';
+  return d.toLocaleDateString('en-GB', { day: 'numeric', month: 'long', year: 'numeric' });
+}
+
+const VISIT_KIND: Record<OfstedLatestVisit['kind'], string> = {
+  report_card: 'Report card inspection',
+  graded: 'Graded inspection',
+  ungraded: 'Ungraded inspection',
+};
+
+/** "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);
+  const what = current.basis === 'confirmed' ? 'Confirmed at an ungraded inspection' : 'Graded inspection';
+  return when ? `${what}, ${when}` : what;
+}
+
+/** "Ungraded inspection, 13 November 2024: Standards maintained". */
+export function latestVisitLine(visit: OfstedLatestVisit): string {
+  const head = `${VISIT_KIND[visit.kind]}, ${formatOfstedDate(visit.date)}`;
+  return visit.outcome ? `${head}: ${visit.outcome}` : head;
+}
+
+/**
+ * Whether the latest visit needs its own line: when it is not where the grade
+ * came from, or when there is no grade but an ungraded outcome to report. A
+ * graded visit without a grade is already dated by the section title.
+ */
+export function showLatestVisitLine(
+  ofsted: Pick<OfstedInspection, 'current_grade' | 'latest_visit'>,
+): boolean {
+  const visit = ofsted.latest_visit;
+  if (!visit || visit.kind === 'report_card') return false;
+  const current = ofsted.current_grade;
+  if (current) return current.date !== visit.date;
+  return visit.kind === 'ungraded';
+}
@@ -76,10 +76,14 @@ export interface School {
  parliamentary_constituency?: string | null;

  // Ofsted (for list view — summary only)
+  /** The overall grade still in force; null when the latest inspection gave none. */
  ofsted_grade?: 1 | 2 | 3 | 4 | null;
+  /** Date the grade was awarded or confirmed (null without a grade). */
+  ofsted_grade_date?: string | null;
  /** Report-card inspection date (Nov 2025+); non-null identifies a report
   * card in the list/map, where the full report_card object isn't available. */
  ofsted_rc_date?: string | null;
+  /** The school's latest inspection of any kind. */
  ofsted_date?: string | null;
  ofsted_framework?: string | null;
 }
@@ -95,6 +99,7 @@ export interface OfstedInspection {
  rc_inspection_date?: string | null;
  inspection_type: string | null;
  // OEIF fields (old framework, pre-Nov 2025)
+  /** The graded inspection's own overall grade; never carried forward. */
  overall_effectiveness: 1 | 2 | 3 | 4 | null;
  quality_of_education: number | null;
  behaviour_attitudes: number | null;
@@ -115,9 +120,12 @@ export interface OfstedInspection {
  rc_leadership_governance: number | null;
  rc_early_years: number | null;
  rc_sixth_form: number | null;
-  /** Where the effective overall grade came from: a graded (Section 5)
-   * inspection, or carried forward from an ungraded (Section 8) outcome. */
-  grade_source?: 'graded' | 'ungraded_carried_forward' | null;
+  /** The overall grade still in force, dated by the inspection that awarded
+   * ("graded") or confirmed ("confirmed", an ungraded visit) it. Null when the
+   * latest inspection gave no overall grade, or for a report card. */
+  current_grade?: OfstedCurrentGrade | null;
+  /** The school's most recent inspection of any kind. */
+  latest_visit?: OfstedLatestVisit | null;
  /** Renewed-framework (Nov 2025) area judgements, coded + labelled by the
   * backend from the live-sampled Ofsted vocabulary. Empty when the school
   * has no report-card inspection. Safeguarding is never included here. */
@@ -127,6 +135,18 @@ export interface OfstedInspection {
  report_url?: string | null;
 }

+export interface OfstedCurrentGrade {
+  grade: 1 | 2 | 3 | 4;
+  date: string | null;
+  basis: 'graded' | 'confirmed';
+}
+
+export interface OfstedLatestVisit {
+  date: string;
+  kind: 'report_card' | 'graded' | 'ungraded';
+  outcome: string | null;
+}
+
 export interface ReportCardEntry {
  code: number;
  label: string;
@@ -2,7 +2,7 @@
 * Utility functions for SchoolCompare
 */

-import type { School, MetricDefinition, OfstedInspection, SchoolAdmissions, SchoolResult } from './types';
+import type { School, MetricDefinition } from './types';

 // ============================================================================
 // String Utilities
@@ -619,172 +619,6 @@ export function getCurrentAcademicYear(): number {
  return month >= 8 ? year : year - 1;
 }

-// ============================================================================
-// School Detail Hero Helpers
-// ============================================================================
-
-const OFSTED_OEIF_WORDS: Record<number, string> = {
-  1: 'Outstanding', 2: 'Good', 3: 'Requires Improvement', 4: 'Inadequate',
-};
-
-/**
- * Format an Ofsted inspection date as "Month YYYY" (e.g. "November 2023").
- */
-function formatOfstedMonth(date: string | null | undefined): string {
-  if (!date) return '';
-  const d = new Date(date);
-  if (Number.isNaN(d.getTime())) return '';
-  return d.toLocaleDateString('en-GB', { month: 'long', year: 'numeric' });
-}
-
-export type HeroTone = 'teal' | 'green' | 'gold' | 'coral' | 'neutral';
-
-export interface OfstedHeroChip {
-  state: 'oeif' | 'reportCard' | 'none';
-  title: string;           // Main label (e.g. "Ofsted Outstanding", "Ofsted Report Card")
-  subtitle: string;        // Context line (e.g. "Inspected November 2023")
-  detail?: string;         // Optional extra line (e.g. "Safeguarding: Met")
-  tone: HeroTone;          // Maps to dedicated hero tone classes (not badge classes)
-}
-
-/**
- * Build the hero-strip Ofsted chip, branching on the inspection framework.
- * Never synthesises a single overall grade for ReportCard schools.
- *
- * Note: the API may return ``framework`` as a literal string ``"NULL"`` for
- * older inspections, so we explicitly only branch into the ReportCard layout
- * when the value is exactly ``"ReportCard"``. Anything else with an
- * ``overall_effectiveness`` score is treated as OEIF.
- */
-export function buildOfstedHeroChip(ofsted: OfstedInspection | null | undefined): OfstedHeroChip {
-  if (!ofsted) {
-    return {
-      state: 'none',
-      title: 'Ofsted pending',
-      subtitle: 'No inspection on record',
-      tone: 'neutral',
-    };
-  }
-
-  const when = formatOfstedMonth(ofsted.inspection_date);
-
-  // ReportCard branch — only if the API explicitly says so
-  if (ofsted.framework === 'ReportCard') {
-    const safeguarding = ofsted.rc_safeguarding_met;
-    return {
-      state: 'reportCard',
-      title: 'Ofsted Report Card',
-      subtitle: when ? `Inspected ${when}` : 'New framework inspection',
-      detail:
-        safeguarding == null
-          ? undefined
-          : safeguarding ? 'Safeguarding: Met' : 'Safeguarding: Not met',
-      tone: safeguarding === false ? 'coral' : 'green',
-    };
-  }
-
-  // Otherwise treat as OEIF (covers framework === 'OEIF', null, "NULL", etc.)
-  const grade = ofsted.overall_effectiveness;
-  if (grade && OFSTED_OEIF_WORDS[grade]) {
-    const oeifTone: HeroTone =
-      grade === 1 ? 'teal' :
-      grade === 2 ? 'green' :
-      grade === 3 ? 'gold' :
-      'coral';
-    return {
-      state: 'oeif',
-      title: `Ofsted ${OFSTED_OEIF_WORDS[grade]}`,
-      subtitle: when ? `Inspected ${when}` : 'Inspected',
-      tone: oeifTone,
-    };
-  }
-
-  return {
-    state: 'oeif',
-    title: 'Ofsted inspected',
-    subtitle: when ? `Inspected ${when}` : 'Inspection on record',
-    tone: 'neutral',
-  };
-}
-
-/**
- * Build a one-sentence editorial summary for the school detail hero.
- * Branches on Ofsted framework so Report Card schools are never described
- * with an overall grade they do not have.
- */
-export function buildSchoolSummary(
-  schoolInfo: School,
-  ofsted: OfstedInspection | null | undefined,
-  admissions: SchoolAdmissions | null | undefined,
-  latestResults: SchoolResult | null | undefined,
-): string {
-  const parts: string[] = [];
-
-  // Size descriptor
-  const pupils = latestResults?.total_pupils ?? schoolInfo.total_pupils ?? null;
-  const sizeWord =
-    pupils == null ? '' :
-    pupils < 200 ? 'Small' :
-    pupils < 500 ? 'Mid-sized' :
-    'Large';
-
-  // Phase descriptor — avoid the raw code
-  const phase = (schoolInfo.phase ?? '').toLowerCase();
-  const phaseWord =
-    phase.includes('secondary') ? 'secondary' :
-    phase === 'all-through' ? 'all-through' :
-    phase.includes('primary') ? 'primary' :
-    'school';
-
-  // Religious character
-  const religion = schoolInfo.religious_denomination;
-  const religionWord =
-    !religion || /none|does not apply/i.test(religion) ? '' :
-    /roman catholic|catholic/i.test(religion) ? 'Catholic ' :
-    /church of england|ce|anglican/i.test(religion) ? 'Church of England ' :
-    /jewish/i.test(religion) ? 'Jewish ' :
-    /muslim|islam/i.test(religion) ? 'Muslim ' :
-    /hindu/i.test(religion) ? 'Hindu ' :
-    /sikh/i.test(religion) ? 'Sikh ' :
-    '';
-
-  // Locality — prefer town from address parsing (fallback to LA)
-  const locality = schoolInfo.town || schoolInfo.local_authority || '';
-
-  const lead = [sizeWord, religionWord + phaseWord].filter(Boolean).join(' ');
-  let opening = lead || 'School';
-  if (locality) opening += ` in ${locality}`;
-  parts.push(opening);
-
-  // Ofsted clause (framework-aware)
-  if (ofsted?.framework === 'OEIF' && ofsted.overall_effectiveness) {
-    parts.push(`rated ${OFSTED_OEIF_WORDS[ofsted.overall_effectiveness]} by Ofsted`);
-  } else if (ofsted?.framework === 'ReportCard') {
-    const when = formatOfstedMonth(ofsted.inspection_date);
-    parts.push(
-      when
-        ? `most recently inspected under Ofsted's Report Card framework in ${when}`
-        : "recently inspected under Ofsted's new Report Card framework",
-    );
-  }
-
-  // Admissions clause
-  if (admissions?.oversubscribed) {
-    if (admissions.first_preference_offer_pct != null) {
-      const pct = Math.round(admissions.first_preference_offer_pct);
-      parts.push(
-        `oversubscribed (${pct}% of first-choice applicants are offered a place)`,
-      );
-    } else {
-      parts.push('oversubscribed');
-    }
-  } else if (admissions?.first_preference_offer_pct != null && admissions.first_preference_offer_pct >= 90) {
-    parts.push('most families get their first-choice offer');
-  }
-
-  return parts.join(', ') + '.';
-}
-
 // ─── Legacy (OEIF) sub-judgement areas ────────────────────────────────────────

 export interface OfstedLegacyArea {
@@ -837,15 +671,18 @@ export interface OfstedListBadge {
 *     Checked FIRST so it wins over any carried-forward legacy grade — the
 *     list has no full report_card object, and ofsted_framework is the raw
 *     event grouping ("Schools - S5"), never "ReportCard".
- *   - OEIF school (ofsted_grade set): grade word + year, colour-keyed
- *   - Inspected without an overall grade (OEIF post-Sept-2024, where Ofsted no
- *     longer issues an overall judgement): "Inspected · YYYY" — mirrors the
- *     detail page's hero chip so a school never reads as both inspected and
+ *   - Current grade (ofsted_grade set): grade word + the year it was awarded
+ *     or confirmed (ofsted_grade_date), colour-keyed. Never the year of a later
+ *     visit: that paired old grades with new inspections (audit C1).
+ *   - Inspected with no grade in force (every inspection Sept 2024 – Nov 2025,
+ *     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
 */
 export function buildOfstedListBadge(school: {
  ofsted_grade?: 1 | 2 | 3 | 4 | null;
+  ofsted_grade_date?: string | null;
  ofsted_date?: string | null;
  ofsted_framework?: string | null;
  ofsted_rc_date?: string | null;
@@ -858,10 +695,7 @@ export function buildOfstedListBadge(school: {
    return { label: `Report Card · ${rcYear}`, cssClass: 'ofstedRc' };
  }

-  const year = school.ofsted_date
-    ? new Date(school.ofsted_date).getFullYear()
-    : null;
-  const yearStr = year ? ` · ${year}` : '';
+  const yearOf = (iso?: string | null) => (iso ? new Date(iso).getFullYear() : null);

  if (school.ofsted_grade) {
    const labels: Record<number, string> = {
@@ -870,17 +704,19 @@ export function buildOfstedListBadge(school: {
      3: 'Req. Improvement',
      4: 'Inadequate',
    };
+    const gradeYear = yearOf(school.ofsted_grade_date);
    return {
-      label: `${labels[school.ofsted_grade]}${yearStr}`,
+      label: `${labels[school.ofsted_grade]}${gradeYear ? ` · ${gradeYear}` : ''}`,
      cssClass: `ofsted${school.ofsted_grade}`,
    };
  }

-  // An inspection is on record (date or framework present) but carries no
-  // overall grade — a post-Sept-2024 OEIF inspection. Distinct from a school
-  // that has genuinely never been inspected.
+  // An inspection is on record but no overall grade is in force: every
+  // inspection from Sept 2024 to Nov 2025, or an ungraded visit whose outcome
+  // names no grade. Dated by the latest visit.
  if (school.ofsted_date != null || school.ofsted_framework != null) {
-    return { label: `Inspected${yearStr}`, cssClass: 'ofstedInspected' };
+    const visitYear = yearOf(school.ofsted_date);
+    return { label: `Inspected${visitYear ? ` · ${visitYear}` : ''}`, cssClass: 'ofstedInspected' };
  }

  return { label: 'Not yet inspected', cssClass: 'ofstedPending' };
@@ -106,9 +106,12 @@ print(f'Validation passed: {{count}} GIAS rows')
        """,
    )

+    # Marts fed by annual EES staging models are rebuilt by the EES DAG, even
+    # when they join dim_school. Selecting them here fails in any database
+    # where that DAG hasn't run (pipeline/tests/test_dag_selectors.py).
    dbt_build = BashOperator(
        task_id="dbt_build",
-        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_gias_establishments+ stg_gias_links+ gias_code_names+ --exclude int_ks2_with_lineage+ int_ks4_with_lineage+",
+        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_gias_establishments+ stg_gias_links+ gias_code_names+ --exclude int_ks2_with_lineage+ int_ks4_with_lineage+ stg_ees_ks4_destinations+ stg_ees_ks5_destinations+",
    )

    sync_typesense = BashOperator(
@@ -143,7 +146,7 @@ with DAG(

    dbt_build_ofsted = BashOperator(
        task_id="dbt_build",
-        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ofsted_inspections+ int_ofsted_latest+ fact_ofsted_inspection+ dim_school+",
+        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_ofsted_inspections+ int_ofsted_latest+ fact_ofsted_inspection+ dim_school+ --exclude stg_ees_ks4_destinations+ stg_ees_ks5_destinations+",
    )

    sync_typesense_ofsted = BashOperator(
@@ -190,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(
@@ -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",
+}
@@ -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
@@ -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
@@ -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("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),
+        *[th.Property(field, th.StringType) for field in KS4_INFO_FIELDS],
    ).to_dict()


@@ -564,37 +574,23 @@ class EESKs2NationalStream(Stream):
            yield record


-# ── KS4 National Headlines (national level only — one row per year) ──────────
-# Dataset: "National characteristics summary data" (Key stage 4 performance).
-# Official England state-funded headline measures, 2018/19 → latest.
-# Suppressed values ('z', 'x') → NULL downstream. Progress 8 is legitimately
-# absent in years with no KS2 baseline (e.g. 2024/25) — that is DfE policy,
-# not missing data.
+# ── KS4 National and LA Headlines (one data set, two streams) ────────────────
+# DfE's "summary, all state-funded" data set: England, regional and LA rows,
+# 2018/19 → latest. URL, measures and filters: ks4_summary.py.

-_KS4_NATIONAL_CSV_URL = (
-    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
-    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
-)
+def _read_ks4_summary(logger):
+    """Download DfE's KS4 summary data set."""
+    import pandas as pd

-_KS4_NATIONAL_COL_MAP = {
-    "attainment8_average":    "attainment_8_score",
-    "progress8_average":      "progress_8_score",
-    "engmath_94_percent":     "english_maths_standard_pass_pct",
-    "engmath_95_percent":     "english_maths_strong_pass_pct",
-    "ebacc_entering_percent": "ebacc_entry_pct",
-    "ebacc_94_percent":       "ebacc_standard_pass_pct",
-    "ebacc_95_percent":       "ebacc_strong_pass_pct",
-    "ebacc_aps_average":      "ebacc_avg_score",
-}
+    logger.info("Downloading KS4 summary data set: %s", KS4_SUMMARY_CSV_URL)
+    resp = requests.get(KS4_SUMMARY_CSV_URL, timeout=60)
+    resp.raise_for_status()
+    return pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)


 class EESKs4NationalStream(Stream):
-    """National KS4 headline averages — one row per academic year.
-
-    Filters to geographic_level == 'National', establishment_type_group ==
-    'All state-funded', breakdown_topic == 'Total', breakdown == 'Total'
-    so only the England-wide all-pupils row per year is emitted.
-    """
+    """National KS4 headline averages — one row per academic year (England,
+    all state-funded schools, all pupils)."""

    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
-
-        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]
-
+        df = headline_rows(_read_ks4_summary(self.logger), "National")
        self.logger.info("Emitting %d national KS4 rows", len(df))
        for _, row in df.iterrows():
-            record = {"time_period": row.get("time_period", "").strip()}
-            for csv_col, field in _KS4_NATIONAL_COL_MAP.items():
-                record[field] = row.get(csv_col, "").strip()
-            yield record
+            yield headline_record(row, ("time_period",))
+
+
+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),
        ]


@@ -0,0 +1,98 @@
+"""Every scheduled dbt build must only build models whose parents exist.
+
+The daily GIAS build selects `stg_gias_establishments+`, so any mart that joins
+dim_school joins the daily build too. When such a mart also reads a staging
+model that only the manually triggered EES DAG builds, the daily build fails in
+any database where that DAG has not run since. Sync and cache invalidation then
+never run either. The destinations marts did this from late August 2026.
+
+The graph is read from the model SQL, because CI has no dbt.
+"""
+import re
+from collections import defaultdict
+from pathlib import Path
+
+import pytest
+
+PIPELINE = Path(__file__).resolve().parents[1]
+MODELS = PIPELINE / 'transform' / 'models'
+DAG_FILE = PIPELINE / 'dags' / 'school_data_pipeline.py'
+
+REF = re.compile(r"ref\(\s*'([a-z0-9_]+)'\s*\)")
+DBT_BUILD = re.compile(r'dbt_build\w*\s*=\s*BashOperator\(.*?build --profiles-dir \. --target production ([^"]+)"', re.S)
+DAG_ID = re.compile(r'dag_id="([a-z0-9_]+)"')
+
+DAILY = 'school_data_daily'
+
+# dim_school reads int_ofsted_latest only when the relation exists
+# (adapter.get_relation), so a missing table is not a failure.
+OPTIONAL_PARENTS = {'int_ofsted_latest'}
+
+
+def model_parents():
+    """{model: models it refs}. Seeds are left out: they are loaded once and always exist."""
+    sql = {p.stem: p.read_text() for p in MODELS.rglob('*.sql')}
+    return {name: set(REF.findall(text)) & set(sql) for name, text in sql.items()}
+
+
+def downstream(node, children):
+    seen, stack = {node}, [node]
+    while stack:
+        for child in children[stack.pop()]:
+            if child not in seen:
+                seen.add(child)
+                stack.append(child)
+    return seen
+
+
+def expand(tokens, children):
+    out = set()
+    for token in tokens:
+        out |= downstream(token[:-1], children) if token.endswith('+') else {token}
+    return out
+
+
+def scheduled_builds():
+    """{dag_id: dbt selection arguments} for every dbt build in the DAG file."""
+    text = DAG_FILE.read_text()
+    starts = [(m.start(), m.group(1)) for m in DAG_ID.finditer(text)]
+    builds = {}
+    for i, (start, dag_id) in enumerate(starts):
+        end = starts[i + 1][0] if i + 1 < len(starts) else len(text)
+        found = DBT_BUILD.search(text, start, end)
+        if found:
+            builds[dag_id] = found.group(1)
+    return builds
+
+
+def selected_models(args, parents):
+    children = defaultdict(set)
+    for model, ps in parents.items():
+        for p in ps:
+            children[p].add(model)
+    select = re.search(r'--select (.+?)(?= --exclude|$)', args).group(1).split()
+    excluded = re.search(r'--exclude (.+)$', args)
+    exclude = excluded.group(1).split() if excluded else []
+    return (expand(select, children) - expand(exclude, children)) & set(parents)
+
+
+PARENTS = model_parents()
+BUILDS = scheduled_builds()
+DAILY_MODELS = selected_models(BUILDS[DAILY], PARENTS)
+
+
+def test_every_dag_with_a_dbt_build_is_parsed():
+    assert set(BUILDS) == {
+        'school_data_daily', 'school_data_monthly_ofsted', 'school_data_annual_ees',
+        'school_data_annual_idaci', 'school_data_annual_distance',
+    }
+
+
+@pytest.mark.parametrize('dag_id', sorted(BUILDS))
+def test_selected_models_only_read_models_that_exist(dag_id):
+    selected = selected_models(BUILDS[dag_id], PARENTS)
+    # The daily build is the base layer: other DAGs may rely on what it builds.
+    available = selected | OPTIONAL_PARENTS | (DAILY_MODELS if dag_id != DAILY else set())
+    missing = {model: sorted(PARENTS[model] - available) for model in sorted(selected)
+               if PARENTS[model] - available}
+    assert missing == {}, f'{dag_id} builds models whose parents it never builds: {missing}'
@@ -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)
@@ -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
@@ -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']
@@ -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 %}
@@ -1,18 +1,91 @@
-- Intermediate model: Latest Ofsted inspection per URN
-- Picks the most recent inspection for each school
+-- Intermediate model: the current Ofsted status per URN
+-- One row per school: its latest visit (report card, graded or ungraded
+-- inspection) and the overall grade still in force, if any. A grade is dated
+-- by the inspection that awarded or confirmed it, never by a later visit.
+-- Rule and examples: docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md

-with ranked as (
+with inspections as (
    select
        *,
+        -- The newest of the three inspections, read from the dates. (Report
+        -- cards began in Nov 2025, after the last legacy inspections, so today
+        -- a report card is always the latest; nothing below relies on that.)
+        greatest(rc_inspection_date, graded_inspection_date, ungraded_inspection_date)
+            as latest_visit_date
+    from {{ ref('stg_ofsted_inspections') }}
+),
+
+ranked as (
+    select
+        *,
+        -- Monthly loads can leave several rows per school. The newest visit
+        -- wins; the tie-breaks keep the choice deterministic.
        row_number() over (
            partition by urn
-            order by inspection_date desc
+            order by latest_visit_date desc,
+                     rc_inspection_date desc nulls last,
+                     graded_inspection_date desc nulls last,
+                     ungraded_inspection_date desc nulls last
        ) as rn
-    from {{ ref('stg_ofsted_inspections') }}
+    from inspections
+),
+
+latest as (
+    select
+        *,
+        -- Same-day ties resolve report card, then graded, then ungraded.
+        case
+            when rc_inspection_date = latest_visit_date then 'report_card'
+            when graded_inspection_date = latest_visit_date then 'graded'
+            else 'ungraded'
+        end as latest_visit_kind
+    from ranked
+    where rn = 1
+),
+
+graded as (
+    select
+        *,
+        -- The grade still in force. A report card replaced overall grades, so
+        -- none survives it. Otherwise: a graded inspection's own overall grade
+        -- (1-4; "Not judged" and the sentinel 9 are no grade); else an
+        -- ungraded visit's "School remains X"; else, after an ungraded visit
+        -- that names no grade, the graded inspection's grade.
+        case
+            when rc_inspection_date is not null
+                then null
+            when latest_visit_kind = 'graded' and overall_effectiveness between 1 and 4
+                then 'graded_latest'
+            when latest_visit_kind = 'ungraded' and ungraded_grade is not null
+                then 'confirmed'
+            when latest_visit_kind = 'ungraded' and overall_effectiveness between 1 and 4
+                then 'graded_earlier'
+        end as grade_case
+    from latest
 )

 select
    urn,
+    latest_visit_date,
+    latest_visit_kind,
+    case when latest_visit_kind = 'ungraded' then ungraded_outcome end as latest_visit_outcome,
+    case grade_case
+        when 'confirmed' then ungraded_grade
+        when 'graded_latest' then overall_effectiveness
+        when 'graded_earlier' then overall_effectiveness
+    end as current_grade,
+    case grade_case
+        when 'confirmed' then ungraded_inspection_date
+        when 'graded_latest' then graded_inspection_date
+        when 'graded_earlier' then graded_inspection_date
+    end as current_grade_date,
+    case grade_case
+        when 'confirmed' then 'confirmed'
+        when 'graded_latest' then 'graded'
+        when 'graded_earlier' then 'graded'
+    end as current_grade_basis,
+    graded_inspection_date,
+    ungraded_inspection_date,
    inspection_date,
    inspection_type,
    framework,
@@ -36,5 +109,4 @@ select
    rc_sixth_form,
    rc_inspection_date,
    report_url
-from ranked
-where rn = 1
+from graded
@@ -0,0 +1,113 @@
+version: 2
+
+unit_tests:
+  - name: graded_not_judged_has_no_grade
+    description: Rabbsfarm (102408). The 2025 inspection gave no overall grade, so the 2020 "remains Good" is not carried forward (audit C1).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 102408, graded_inspection_date: '2025-06-17', ungraded_inspection_date: '2020-02-06', overall_effectiveness: null, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 102408, latest_visit_date: '2025-06-17', latest_visit_kind: graded, latest_visit_outcome: null, current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: graded_with_overall_grade
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 1, graded_inspection_date: '2019-06-01', overall_effectiveness: 2}
+    expect:
+      rows:
+        - {urn: 1, latest_visit_date: '2019-06-01', latest_visit_kind: graded, current_grade: 2, current_grade_date: '2019-06-01', current_grade_basis: graded}
+
+  - name: ungraded_remains_good_confirms_the_grade
+    description: Robins Lane (104762). Graded Good 2020, "School remains Good" July 2024 — Good, dated by the confirming visit.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 104762, graded_inspection_date: '2020-01-07', ungraded_inspection_date: '2024-07-18', overall_effectiveness: 2, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 104762, latest_visit_date: '2024-07-18', latest_visit_kind: ungraded, latest_visit_outcome: 'School remains Good', current_grade: 2, current_grade_date: '2024-07-18', current_grade_basis: confirmed}
+
+  - name: post_2024_ungraded_keeps_graded_grade_with_its_own_date
+    description: Washwood Heath (139888). Graded Good 2020, "Standards maintained" May 2025 — Good, dated 2020; latest visit May 2025 (audit M1).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 139888, graded_inspection_date: '2020-03-03', ungraded_inspection_date: '2025-05-21', overall_effectiveness: 2, ungraded_grade: null, ungraded_outcome: 'Standards maintained'}
+    expect:
+      rows:
+        - {urn: 139888, latest_visit_date: '2025-05-21', latest_visit_kind: ungraded, latest_visit_outcome: 'Standards maintained', current_grade: 2, current_grade_date: '2020-03-03', current_grade_basis: graded}
+
+  - name: ungraded_only_standards_maintained
+    description: Oakgrove (136454). Only an ungraded visit, outcome names no grade.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 136454, ungraded_inspection_date: '2024-11-13', ungraded_grade: null, ungraded_outcome: 'Standards maintained'}
+    expect:
+      rows:
+        - {urn: 136454, latest_visit_date: '2024-11-13', latest_visit_kind: ungraded, latest_visit_outcome: 'Standards maintained', current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: report_card_wins
+    description: The Willink School (110048). A report card is the latest visit and no legacy grade stays in force.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 110048, ungraded_inspection_date: '2023-10-05', ungraded_grade: 2, ungraded_outcome: 'School remains Good', rc_inspection_date: '2026-05-06', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 110048, latest_visit_date: '2026-05-06', latest_visit_kind: report_card, latest_visit_outcome: null, current_grade: null, current_grade_date: null, current_grade_basis: null}
+
+  - name: duplicate_rows_newer_report_card_wins
+    description: Monthly loads can leave an older row beside a newer one for the same graded date; the row with the report card must win (audit H3).
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 138186, graded_inspection_date: '2023-06-13', overall_effectiveness: 3}
+          - {urn: 138186, graded_inspection_date: '2023-06-13', overall_effectiveness: 3, rc_inspection_date: '2026-06-02', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 138186, latest_visit_date: '2026-06-02', latest_visit_kind: report_card, current_grade: null}
+
+  - name: same_day_graded_wins
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 2, graded_inspection_date: '2024-03-01', ungraded_inspection_date: '2024-03-01', overall_effectiveness: 1, ungraded_grade: 2, ungraded_outcome: 'School remains Good'}
+    expect:
+      rows:
+        - {urn: 2, latest_visit_kind: graded, current_grade: 1, current_grade_basis: graded}
+
+  - name: overall_sentinel_is_not_a_grade
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 3, graded_inspection_date: '2018-05-01', overall_effectiveness: 9}
+    expect:
+      rows:
+        - {urn: 3, latest_visit_kind: graded, current_grade: null}
+
+  - name: newer_legacy_visit_after_a_report_card
+    description: >
+      Not in Ofsted's data today (0 of 2,451 report-card schools in the 31 Aug
+      2026 MI), but the latest visit is read from the dates, not assumed. The
+      report card still leaves no legacy grade in force.
+    model: int_ofsted_latest
+    given:
+      - input: ref('stg_ofsted_inspections')
+        rows:
+          - {urn: 4, ungraded_inspection_date: '2026-03-02', ungraded_grade: 2, ungraded_outcome: 'School remains Good', rc_inspection_date: '2025-12-01', rc_inclusion: 3}
+    expect:
+      rows:
+        - {urn: 4, latest_visit_date: '2026-03-02', latest_visit_kind: ungraded, latest_visit_outcome: 'School remains Good', current_grade: null, current_grade_date: null, current_grade_basis: null}
@@ -125,6 +125,30 @@ models:
      - name: inspection_date
        tests: [not_null]

+  - name: fact_ofsted_latest
+    description: >
+      Current Ofsted status, one row per URN: the latest visit and the overall
+      grade still in force. See int_ofsted_latest for the rule.
+    columns:
+      - name: urn
+        tests: [not_null, unique]
+      - name: latest_visit_date
+        tests: [not_null]
+      - name: latest_visit_kind
+        tests:
+          - not_null
+          - accepted_values:
+              values: ['report_card', 'graded', 'ungraded']
+      - name: current_grade
+        tests:
+          - accepted_values:
+              values: [1, 2, 3, 4]
+              quote: false
+      - name: current_grade_basis
+        tests:
+          - accepted_values:
+              values: ['graded', 'confirmed']
+
  - name: fact_pupil_characteristics
    description: Pupil demographics — one row per URN per year
    columns:
@@ -255,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:
@@ -71,12 +71,12 @@ select
    s.nursery_provision,
    s.admissions_policy_code,

-    -- Latest Ofsted (populated after monthly Ofsted pipeline runs)
+    -- Latest Ofsted (populated after monthly Ofsted pipeline runs). The grade
+    -- still in force and the latest visit, from int_ofsted_latest — never a
+    -- grade carried past a newer inspection.
    {% if ofsted_relation is not none %}
-    -- Prefer the graded overall effectiveness; fall back to the grade parsed
-    -- from the latest ungraded (Section 8) outcome when no graded grade exists.
-    coalesce(o.overall_effectiveness, o.ungraded_grade) as ofsted_grade,
-    o.inspection_date as ofsted_date,
+    o.current_grade as ofsted_grade,
+    o.latest_visit_date as ofsted_date,
    o.framework as ofsted_framework
    {% else %}
    null::text as ofsted_grade,
@@ -0,0 +1,35 @@
+version: 2
+
+unit_tests:
+  - name: dim_school_ofsted_grade_is_the_one_still_in_force
+    description: >
+      Rabbsfarm (102408). Its 2025 inspection gave no overall grade, so the 2020
+      "School remains Good" must not reach dim_school (which feeds Typesense's
+      rating), and ofsted_date is the latest visit (audit C1, M1).
+    model: dim_school
+    given:
+      - input: ref('stg_gias_establishments')
+        rows:
+          - {urn: 102408, school_name: 'Rabbsfarm Primary School', status_code: 1, school_type_code: 1, local_authority_code: 312, phase_code: 2}
+      - input: ref('int_ofsted_latest')
+        rows:
+          - {urn: 102408, inspection_date: '2025-06-17', overall_effectiveness: null, ungraded_grade: 2, framework: 'Schools - S5', latest_visit_date: '2025-06-17', current_grade: null}
+    expect:
+      rows:
+        - {urn: 102408, ofsted_grade: null, ofsted_date: '2025-06-17', ofsted_framework: 'Schools - S5'}
+
+  - name: dim_school_ofsted_grade_keeps_its_own_date_out_of_ofsted_date
+    description: >
+      Washwood Heath (139888). Good from a 2020 graded inspection; latest visit
+      an ungraded one in May 2025. ofsted_date is the latest visit.
+    model: dim_school
+    given:
+      - input: ref('stg_gias_establishments')
+        rows:
+          - {urn: 139888, school_name: 'Washwood Heath Academy', status_code: 1, school_type_code: 28, local_authority_code: 330, phase_code: 7}
+      - input: ref('int_ofsted_latest')
+        rows:
+          - {urn: 139888, inspection_date: '2020-03-03', overall_effectiveness: 2, ungraded_grade: null, framework: 'Schools - S5', latest_visit_date: '2025-05-21', current_grade: 2}
+    expect:
+      rows:
+        - {urn: 139888, ofsted_grade: 2, ofsted_date: '2025-05-21'}
@@ -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
@@ -0,0 +1,37 @@
+-- Mart: current Ofsted status — one row per URN
+-- The backend reads this instead of choosing the latest row of
+-- fact_ofsted_inspection itself. The rule lives in int_ofsted_latest.
+
+select
+    urn,
+    latest_visit_date,
+    latest_visit_kind,
+    latest_visit_outcome,
+    current_grade,
+    current_grade_date,
+    current_grade_basis,
+    graded_inspection_date,
+    ungraded_inspection_date,
+    rc_inspection_date,
+    inspection_type,
+    framework,
+    overall_effectiveness,
+    quality_of_education,
+    behaviour_attitudes,
+    personal_development,
+    leadership_management,
+    early_years_provision,
+    sixth_form_provision,
+    ungraded_outcome,
+    ungraded_grade,
+    rc_safeguarding_met,
+    rc_inclusion,
+    rc_curriculum_teaching,
+    rc_achievement,
+    rc_attendance_behaviour,
+    rc_personal_development,
+    rc_leadership_governance,
+    rc_early_years,
+    rc_sixth_form,
+    report_url
+from {{ ref('int_ofsted_latest') }}
@@ -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
@@ -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}
@@ -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]+$'
@@ -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}
@@ -1,5 +1,10 @@
 -- Staging model: Ofsted inspection records
-- Handles both OEIF (pre-Nov 2025) and Report Card (post-Nov 2025) frameworks
+-- Handles both OEIF (pre-Nov 2025) and Report Card (post-Nov 2025) frameworks.
+--
+-- Ofsted's MI carries up to three inspections per school: the latest graded
+-- one, the latest ungraded one and the latest report card. Their dates stay
+-- separate here so int_ofsted_latest can tell which came last and which one a
+-- grade belongs to (docs/superpowers/specs/2026-10-05-ofsted-current-status-design.md).

 with source as (
    select * from {{ source('raw', 'ofsted_inspections') }}
@@ -8,17 +13,12 @@ with source as (
 renamed as (
    select
        cast(urn as integer)                                                    as urn,
-        -- Inspection event date: the graded inspection when present, otherwise the
-        -- ungraded (Section 8) inspection so schools with only an ungraded
-        -- inspection are still retained.
-        coalesce(
-            to_date(nullif(trim(inspection_date), 'NULL'), 'DD/MM/YYYY'),
-            to_date(nullif(trim(ungraded_inspection_date), 'NULL'), 'DD/MM/YYYY')
-        )                                                                       as inspection_date,
+        to_date(nullif(trim(inspection_date), 'NULL'), 'DD/MM/YYYY')            as graded_inspection_date,
+        to_date(nullif(trim(ungraded_inspection_date), 'NULL'), 'DD/MM/YYYY')   as ungraded_inspection_date,
        inspection_type,
        event_type_grouping                                                     as framework,

-        -- OEIF grades (1-4 scale)
+        -- OEIF grades (1-4 scale; 9 = not applicable)
        {{ safe_numeric('overall_effectiveness') }}::integer                    as overall_effectiveness,
        {{ safe_numeric('quality_of_education') }}::integer                     as quality_of_education,
        {{ safe_numeric('behaviour_and_attitudes') }}::integer                  as behaviour_attitudes,
@@ -27,9 +27,9 @@ renamed as (
        {{ safe_numeric('early_years_provision') }}::integer                    as early_years_provision,
        {{ safe_numeric('sixth_form_provision') }}::integer                     as sixth_form_provision,

-        -- Ungraded (Section 8) inspection outcome — free text, plus a grade
-        -- parsed from it (1/2/null) used as a last-resort fallback for schools
-        -- with no graded overall effectiveness.
+        -- Ungraded (Section 8) inspection outcome — free text, plus the grade
+        -- it confirms ("School remains Good" → 2); null for outcomes that
+        -- name no grade.
        nullif(trim(ungraded_outcome), 'NULL')                                  as ungraded_outcome,
        {{ parse_ungraded_outcome('ungraded_outcome') }}::integer               as ungraded_grade,

@@ -50,31 +50,36 @@ renamed as (
        {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,

        -- Start date of the latest FULL inspection (the report-card
-        -- inspection in the renewed framework). Guarded in the final select:
-        -- only kept when the row actually carries report-card grades, because
-        -- in legacy-format files this column is the legacy inspection date.
+        -- inspection in the renewed framework). Only kept when the row
+        -- carries report-card grades, because in legacy-format files this
+        -- column is the legacy inspection date.
        to_date(nullif(trim(rc_inspection_date), 'NULL'), 'DD/MM/YYYY')   as rc_inspection_date_raw,

        nullif(trim(report_url), 'NULL')                                  as report_url
    from source
    where urn is not null
-      and (
-          nullif(trim(inspection_date), 'NULL') is not null
-          or nullif(trim(ungraded_inspection_date), 'NULL') is not null
-      )
+),
+
+dated as (
+    select
+        *,
+        case
+            when rc_safeguarding_met is not null
+              or rc_inclusion is not null
+              or rc_curriculum_teaching is not null
+              or rc_achievement is not null
+              or rc_attendance_behaviour is not null
+              or rc_personal_development is not null
+              or rc_leadership_governance is not null
+            then rc_inspection_date_raw
+        end as rc_inspection_date
+    from renamed
 )

 select
    *,
-    case
-        when rc_safeguarding_met is not null
-          or rc_inclusion is not null
-          or rc_curriculum_teaching is not null
-          or rc_achievement is not null
-          or rc_attendance_behaviour is not null
-          or rc_personal_development is not null
-          or rc_leadership_governance is not null
-        then rc_inspection_date_raw
-    end as rc_inspection_date
-from renamed
-where inspection_date is not null
+    -- For readers that predate the separate dates (fact_ofsted_inspection and
+    -- the backend until it reads fact_ofsted_latest). Never null below.
+    coalesce(graded_inspection_date, ungraded_inspection_date, rc_inspection_date) as inspection_date
+from dated
+where coalesce(graded_inspection_date, ungraded_inspection_date, rc_inspection_date) is not null
@@ -0,0 +1,25 @@
+version: 2
+
+unit_tests:
+  - name: stg_ofsted_keeps_the_three_dates_apart
+    description: The graded and ungraded dates must stay separate so the latest visit can be found.
+    model: stg_ofsted_inspections
+    given:
+      - input: source('raw', 'ofsted_inspections')
+        rows:
+          - {urn: 102408, inspection_date: '17/06/2025', ungraded_inspection_date: '06/02/2020', overall_effectiveness: 'Not judged', ungraded_outcome: 'School remains Good', rc_inspection_date: '17/06/2025', rc_inclusion: 'NULL'}
+    expect:
+      rows:
+        - {urn: 102408, graded_inspection_date: '2025-06-17', ungraded_inspection_date: '2020-02-06', rc_inspection_date: null, inspection_date: '2025-06-17', ungraded_grade: 2}
+
+  - name: stg_ofsted_keeps_report_card_only_rows
+    description: A school whose only inspection is a report card used to be dropped (audit H3).
+    model: stg_ofsted_inspections
+    given:
+      - input: source('raw', 'ofsted_inspections')
+        rows:
+          - {urn: 149612, inspection_date: 'NULL', ungraded_inspection_date: 'NULL', rc_inspection_date: '10/02/2026', rc_inclusion: 'Expected standard', rc_safeguarding_met: 'Met'}
+          - {urn: 1, inspection_date: 'NULL', ungraded_inspection_date: 'NULL', rc_inspection_date: 'NULL'}
+    expect:
+      rows:
+        - {urn: 149612, graded_inspection_date: null, ungraded_inspection_date: null, rc_inspection_date: '2026-02-10', inspection_date: '2026-02-10', rc_inclusion: 3}
@@ -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)
@@ -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
@@ -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
@@ -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(*)
@@ -0,0 +1,10 @@
+-- A grade is dated by the inspection that awarded or confirmed it, never
+-- after the latest visit, and has a date and a basis exactly when it exists.
+-- A report card leaves no legacy grade in force.
+
+select urn
+from {{ ref('fact_ofsted_latest') }}
+where current_grade_date > latest_visit_date
+   or (current_grade is null) <> (current_grade_date is null)
+   or (current_grade is null) <> (current_grade_basis is null)
+   or (rc_inspection_date is not null and current_grade is not null)
Author	SHA1	Message	Date
TudorandClaude Opus 5.5	5f93a7ecd2	docs: name the benchmarks computed from our dataset (review) PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m19s Details PR Checks / Backend Smoke (pull_request) Successful in 11s Details PR Checks / Build Backend (no push) (pull_request) Successful in 35s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m36s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 1m17s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 35s Details Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:50:40 +01:00
TudorandClaude Opus 5.5	951c666f8a	test(dbt): warn when DfE's LA averages fall behind the school results (review) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:50:27 +01:00
TudorandClaude Opus 5.5	e09d7a202b	fix(ees): read the year from suffixed release slugs (review) A '2025-26-revised' release read as an unknown year went last, behind the '2025-26' release, which then owned the year and dropped every revised row. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:50:10 +01:00
TudorandClaude Opus 5.5	4ae3853277	fix(dbt): read percentages DfE writes as "34%" (C2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:33:36 +01:00
TudorandClaude Opus 5.5	242c603aec	test(dbt): fail the build when a published year loads empty (C2, H2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:33:03 +01:00
TudorandClaude Opus 5.5	59dd20ab00	feat(dbt): fact_ks4_la_averages from DfE's LA rows (H2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 12:32:22 +01:00
TudorandClaude Opus 5.5	dbb60acff3	feat(ees): keep DfE's KS4 LA averages from the summary data set (H2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 10:46:53 +01:00
TudorandClaude Opus 5.5	967b1f0eed	fix(ees): read 2023/24 KS4 school information under DfE's older names (C2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 10:45:57 +01:00
TudorandClaude Opus 5.5	4db1131d0f	fix(ees): newest KS4 release owns every year it contains (C2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 10:45:14 +01:00
TudorandClaude Opus 5.5	0e987ef06e	docs: plan for 2023/24 KS4/KS2 data and DfE LA averages (C2, H2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 10:34:00 +01:00
TudorandClaude Opus 5.5	ac5b7ccd7f	docs: design for 2023/24 KS4/KS2 data and DfE LA averages (C2, H2) Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-06 10:08:25 +01:00
tudor	c26b65246f	Merge pull request 'fix: show the Ofsted grade still in force and the latest visit (C1/M1/M2, part 2 of 2)' (#184 ) from fix/ofsted-current-status-site into main Stage (build -> staging -> E2E gate) / prepare (push) Successful in 0s Details Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 21s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 1m36s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m28s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 5s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 3m22s Details Reviewed-on: #184	2026-10-05 21:47:34 +00:00
tudor	3728a63275	Merge pull request 'feat(pipeline): one current Ofsted status per school (C1/M1, part 1 of 2)' (#183 ) from fix/ofsted-current-status-pipeline into main Stage (build -> staging -> E2E gate) / prepare (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 20s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 1m31s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m19s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 6s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 3m24s Details Reviewed-on: #183	2026-10-05 15:44:59 +00:00
Tudor	90b7d09086	Merge #183 's review fixes into the site branch PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m14s Details PR Checks / Backend Smoke (pull_request) Successful in 10s Details PR Checks / Build Backend (no push) (pull_request) Successful in 18s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m26s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 52s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 26s Details	2026-10-05 16:39:34 +01:00
TudorandClaude Opus 5.5	480ac4b9a9	docs: plan names the session's trailer; spec reads the latest visit from dates PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m14s Details PR Checks / Backend Smoke (pull_request) Successful in 12s Details PR Checks / Build Backend (no push) (pull_request) Successful in 18s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m27s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 53s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 27s Details The plan hard-coded one model's Co-Authored-By trailer; an executor should use the one its own session specifies. The spec's report-card rule now matches int_ofsted_latest. Review on #183. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:39:24 +01:00
TudorandClaude Opus 5.5	93f17211b1	fix(pipeline): read the latest Ofsted visit from the dates int_ofsted_latest treated any report card as the latest visit, relying on report cards postdating legacy inspections. That holds today (0 of 2,451 report-card schools in the 31 Aug 2026 MI) but is now not assumed: the latest visit is the newest of the three dates. A report card still leaves no legacy grade in force, which is the spec's rule rather than an inference. New unit test newer_legacy_visit_after_a_report_card failed RED on the old logic. Review on #183. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:39:24 +01:00
TudorandClaude Opus 5.5	f86d6f45d1	test(e2e): no grade is carried past a no-grade inspection PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m14s Details PR Checks / Backend Smoke (pull_request) Successful in 10s Details PR Checks / Build Backend (no push) (pull_request) Successful in 18s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m25s Details PR Checks / Build Pipeline (no push) (pull_request) Canceled after 45s Details PR Checks / AI Code Review (Claude) (pull_request) Canceled after 0s Details Rabbsfarm (102408) must read 'Inspected · 2025' with no overall grade, and Robins Lane (104762) must say its Good was confirmed at an ungraded inspection on 18 July 2024. Both fail against staging today (no current_grade field, no confirmation line) and pass once PR #183's pipeline run and this branch are deployed. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:33:53 +01:00
TudorandClaude Opus 5.5	5eed09dfa7	refactor(utils): drop the unused Ofsted hero chip and summary sentence buildOfstedHeroChip and buildSchoolSummary were rendered nowhere and encoded the old carried-forward rule (and a framework value, 'ReportCard', the API never sends). Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:32:53 +01:00
TudorandClaude Opus 5.5	d37baa572b	fix(compare): Ofsted rows show the grade still in force and the latest visit 'Inspected' is the school's latest visit, so Washwood Heath reads 21 May 2025 rather than '3 Mar 2020 · 4+ years ago' (audit M1). The Result row names the inspection a grade came from, or says 'No overall grade'; 'Grade carried forward' and 'transitional framework' go. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:32:19 +01:00
TudorandClaude Opus 5.5	9ef48da4b1	fix(school): the Ofsted section shows the grade still in force and the latest visit The section is dated by the latest visit and headlines the grade still in force with the inspection that awarded or confirmed it, or 'No overall grade'. A later visit that isn't the grade's source gets its own line, with an ungraded outcome such as 'Standards maintained'. The primary and secondary no-grade branches merge, so the secondary page no longer drops the sixth-form and early-years judgements (audit M2), and 'Not rated' goes. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:31:26 +01:00
TudorandClaude Opus 5.5	a42c586cc8	fix(ofsted): badge and compare read the grade still in force Search badges date a grade by the inspection that awarded or confirmed it (ofsted_grade_date) and say 'Inspected · year' when the latest inspection gave no grade. ofstedDisplay's kinds become graded / confirmed / no_overall_grade, read from current_grade rather than a carried-forward overall_effectiveness. lib/ofstedStatus.ts holds the two sentences the school and compare pages share. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:29:54 +01:00
TudorandClaude Opus 5.5	a9e3a6a700	fix(backend): serve the current Ofsted status from fact_ofsted_latest The list query and the batch Ofsted fetch each picked 'the latest row' of fact_ofsted_inspection themselves, and _ofsted_block carried an older ungraded grade forward when the latest graded inspection gave none. All three now read marts.fact_ofsted_latest. List rows: ofsted_grade is the grade still in force, ofsted_grade_date when it was awarded or confirmed, ofsted_date the latest visit. The ofsted block gains current_grade and latest_visit and loses grade_source; overall_effectiveness is the graded inspection's own result. A school with only an inspection stays publishable in the sitemap. Requires fact_ofsted_latest (PR #183's pipeline run) on the database. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:28:30 +01:00
TudorandClaude Opus 5.5	96732c56d1	fix(pipeline): dim_school's Ofsted grade is the one still in force dim_school.ofsted_grade (Typesense's rating) coalesced the graded grade with an older ungraded visit's, so Rabbsfarm's 2020 'remains Good' survived its 2025 no-grade inspection. It now reads int_ofsted_latest's current_grade, and ofsted_date is the latest visit. Unit tests pin both (audit C1, M1). Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:26:51 +01:00
Tudor	befaf950a7	Merge PR 1 (fix/ofsted-current-status-pipeline, #183 ) into the site branch PR 2 reads the columns PR 1 adds. Merge main back in once #183 lands.	2026-10-05 16:25:49 +01:00
TudorandClaude Opus 5.5	dfa4928641	feat(pipeline): add fact_ofsted_latest, one current Ofsted status per school PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m18s Details PR Checks / Backend Smoke (pull_request) Successful in 10s Details PR Checks / Build Backend (no push) (pull_request) Successful in 20s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m31s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 50s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 32s Details The backend will read this instead of picking the latest row of fact_ofsted_inspection itself (twice, with arbitrary ties). Schema tests and assert_ofsted_current_grade_consistent pin its invariants. Built by the monthly Ofsted DAG (int_ofsted_latest+), not the daily one. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:24:41 +01:00
TudorandClaude Opus 5.5	e6babe21f5	feat(pipeline): work out each school's current Ofsted status once int_ofsted_latest now picks each school's latest visit (report card, graded or ungraded inspection) and the overall grade still in force, dated by the inspection that awarded or confirmed it. A 'School remains Good' from an older ungraded visit is no longer carried past a newer inspection that gave no grade (audit C1). Duplicate monthly rows resolve to the newest visit, so a newer report card always wins. dim_school's columns are unchanged. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:23:38 +01:00
TudorandClaude Opus 5.5	358705bf39	feat(pipeline): keep Ofsted's graded, ungraded and report-card dates apart int_ofsted_latest needs to know which inspection came last. Report-card-only schools are no longer dropped (123 schools, audit H3); inspection_date stays for current readers and now falls back to the report-card date, so fact_ofsted_inspection's not_null test still holds. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:22:49 +01:00
TudorandClaude Opus 5.5	3be902e98f	docs(plan): implementation plan for the current Ofsted status Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:20:49 +01:00
TudorandClaude Opus 5.5	12c52244ee	docs(spec): one current Ofsted status per school Design for audit findings C1 and M1. A grade carried forward from an older ungraded visit is shown under a newer inspection's date ("Good · 2025" for Rabbsfarm, whose 2025 inspection gave no grade), and "Inspected" dates show the last graded inspection rather than the latest visit. The rule moves into int_ofsted_latest and a new fact_ofsted_latest mart, shipped as a pipeline PR and then a backend/UI PR. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-05 16:00:35 +01:00
tudor	b6e48c4930	Merge pull request 'fix(pipeline): decode GIAS extracts as Windows-1252' (#182 ) from fix/gias-encoding into main Stage (build -> staging -> E2E gate) / prepare (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 21s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 1m32s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m37s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 5s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 3m21s Details Reviewed-on: #182	2026-10-05 06:31:30 +00:00
tudor	9f4f2507cc	Merge pull request 'fix(pipeline): keep the destinations marts out of the scheduled builds' (#181 ) from fix/scheduled-dbt-selectors into main Stage (build -> staging -> E2E gate) / prepare (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 47s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 1m35s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m20s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 3s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 3m26s Details Reviewed-on: #181	2026-10-05 06:19:44 +00:00
tudor	e1373fb6df	Merge pull request 'fix(school): drop the nearby section's lede, which repeated its heading' (#180 ) from fix/nearby-drop-lede into main Stage (build -> staging -> E2E gate) / prepare (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 19s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 1m31s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 2m8s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 4s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Failing after 45s Details Reviewed-on: #180	2026-10-04 09:13:48 +00:00
TudorandClaude Opus 5.5	65a2619e1d	fix(pipeline): keep the destinations marts out of the scheduled builds PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m18s Details PR Checks / Backend Smoke (pull_request) Successful in 11s Details PR Checks / Build Backend (no push) (pull_request) Successful in 18s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m35s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 1m21s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 19s Details fact_ks4_destinations and fact_ks5_destinations join dim_school, so the daily build's stg_gias_establishments+ and the monthly Ofsted build's dim_school+ both selected them. They also read stg_ees_ks4/ks5_destinations, which only the manually triggered EES DAG builds. Where that DAG hasn't run since the destinations models landed, dbt_build fails with "relation staging.stg_ees_ks4_destinations does not exist", and sync_typesense and invalidate_cache never run. Production's register data has been stuck at about 25 Aug 2026. Both builds now exclude the descendants of the two EES staging models, as the daily build already does for the KS2/KS4 lineage models. The EES DAG still rebuilds the marts when their data changes. test_dag_selectors reads the model graph from the SQL (CI has no dbt) and checks that every scheduled build only reads models it or the daily build builds. It failed for the daily and monthly Ofsted builds before this change. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-03 22:21:27 +01:00
TudorandClaude Opus 5.5	ccfa44389e	fix(school): drop the nearby section's lede, which repeated its heading PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 1m19s Details PR Checks / Backend Smoke (pull_request) Successful in 10s Details PR Checks / Build Backend (no push) (pull_request) Successful in 19s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 1m32s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 1m18s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 14s Details "Other schools nearby" was followed by "Other primary schools near <school>.", which says the same thing again. The heading now stands alone. nearbyNoun() and the phase and schoolName props existed only to build that line, so they go with it. Its bottom margin was the only gap between the heading and the cards, so the header row carries that gap now, and centres the heading against the carousel arrows now that it is a single line. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>	2026-10-03 21:10:36 +01:00