ci: re-run PR checks (AI review job errored without posting findings)

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

backend/data_loader.py

@@ -4,6 +4,7 @@ Provides efficient queries with caching.
 """
 
 import logging
+import re
 
 import pandas as pd
 import numpy as np
@@ -262,15 +263,68 @@ assert "NULL AS has_sixth_form" in str(_MAIN_QUERY_NO_SIXTH_FORM), (
     "expected replacement of 's.has_sixth_form,' to have taken effect"
 )
 
+# Fallback used when marts.dim_school predates the GIAS code-dictionary
+# migration (i.e. the nightly dbt pipeline hasn't rebuilt the mart yet on
+# this DB, so it still has the old name columns instead of *_code columns).
+_MAIN_QUERY_LEGACY_NAMES = str(_MAIN_QUERY)
+_LEGACY_NAME_REPLACEMENTS = [
+    ("s.phase_code,", "s.phase,"),
+    ("s.school_type_code,", "s.school_type,"),
+    (
+        "s.religious_character_code,",
+        "s.religious_character AS religious_denomination,",
+    ),
+    ("s.status_code,", "s.status,"),
+    ("s.admissions_policy_code,", "s.admissions_policy,"),
+]
+for _old, _new in _LEGACY_NAME_REPLACEMENTS:
+    assert _old in _MAIN_QUERY_LEGACY_NAMES, (
+        f"expected {_old!r} to be present in _MAIN_QUERY before replacement"
+    )
+    _MAIN_QUERY_LEGACY_NAMES = _MAIN_QUERY_LEGACY_NAMES.replace(_old, _new)
+_MAIN_QUERY_LEGACY_NAMES = text(_MAIN_QUERY_LEGACY_NAMES)
+
+_GIAS_CODE_COLUMN_NAMES = (
+    "phase_code",
+    "school_type_code",
+    "religious_character_code",
+    "status_code",
+    "admissions_policy_code",
+)
+
+_MISSING_COLUMN_RE = re.compile(r'column "?(?:s\.)?(\w+)"? does not exist')
+
+
+def _missing_column_name(exc: Exception) -> Optional[str]:
+    """Name of the missing column from a psycopg2 UndefinedColumn error.
+
+    Inspects exc.orig (the DBAPI error), whose message names only the
+    offending column — str(exc) also embeds the full SQL statement, which
+    contains every column name and therefore must not be matched against.
+    """
+    orig = getattr(exc, "orig", None)
+    match = _MISSING_COLUMN_RE.search(str(orig) if orig is not None else str(exc))
+    return match.group(1) if match else None
+
 
 def load_school_data_as_dataframe() -> pd.DataFrame:
     """Load all school + KS2 data as a pandas DataFrame."""
     try:
         df = pd.read_sql(_MAIN_QUERY, engine)
     except sqlalchemy.exc.ProgrammingError as exc:
-        if "has_sixth_form" not in str(exc):
+        missing = _missing_column_name(exc)
-            print(f"Warning: Could not load school data from marts: {exc}")
+        if missing in _GIAS_CODE_COLUMN_NAMES:
+            logging.getLogger(__name__).warning(
+                "marts predate the GIAS code migration — falling back to "
+                "legacy name-column query: %s",
+                exc,
+            )
+            try:
+                df = pd.read_sql(_MAIN_QUERY_LEGACY_NAMES, engine)
+            except Exception as exc2:
+                print(f"Warning: Could not load school data from marts: {exc2}")
                 return pd.DataFrame()
+        elif missing == "has_sixth_form":
             logging.getLogger(__name__).warning(
                 "marts.dim_school is missing has_sixth_form (pipeline hasn't "
                 "rebuilt the mart yet on this DB) — retrying without it: %s",
@@ -281,6 +335,9 @@ def load_school_data_as_dataframe() -> pd.DataFrame:
             except Exception as exc2:
                 print(f"Warning: Could not load school data from marts: {exc2}")
                 return pd.DataFrame()
+        else:
+            print(f"Warning: Could not load school data from marts: {exc}")
+            return pd.DataFrame()
     except Exception as exc:
         print(f"Warning: Could not load school data from marts: {exc}")
         return pd.DataFrame()

backend/gias_codes.py

@@ -78,6 +78,7 @@ OFFICIAL_SIXTH_FORM: dict[int, str] = {
     0: "Not applicable",
     1: "Has a sixth form",
     2: "Does not have a sixth form",
+    9: "",
 }
 
 RELIGIOUS_CHARACTER: dict[int, str] = {
@@ -128,12 +129,14 @@ RELIGIOUS_CHARACTER: dict[int, str] = {
     47: "Reformed Baptist",
     48: "Roman Catholic/Anglican",
     49: "Sunni Deobandi",
+    99: "",
 }
 
 ADMISSIONS_POLICY: dict[int, str] = {
     0: "Not applicable",
     2: "Selective",
     4: "Non-selective",
+    9: "",
 }
 
 

backend/tests/test_gias_codes.py

@@ -81,3 +81,15 @@ def test_seed_matches_dictionaries():
         for row in csv.DictReader(fh):
             seed[row["field"]][int(row["code"])] = row["name"]
     assert seed == fields
+
+
+def test_blank_name_sentinel_codes_map_to_empty_string():
+    """GIAS carries codes whose (name) column is blank — e.g. ReligiousCharacter
+    99 (~4k schools) and AdmissionsPolicy 9 (~5.6k schools). The old name
+    pipeline served these as empty strings; the dictionaries must reproduce
+    that ("" is falsy, so UI tag heuristics stay silent) rather than letting
+    them hit the "Unknown (<code>)" path meant for genuinely new codes."""
+    assert RELIGIOUS_CHARACTER[99] == ""
+    assert ADMISSIONS_POLICY[9] == ""
+    assert translate(99, RELIGIOUS_CHARACTER) == ""
+    assert translate(9, ADMISSIONS_POLICY) == ""

backend/tests/test_gias_translation.py

@@ -4,7 +4,7 @@ rest of the backend sees must carry today's name strings."""
 import numpy as np
 import pandas as pd
 
-from backend.data_loader import translate_gias_code_columns
+from backend.data_loader import _missing_column_name, translate_gias_code_columns
 from backend.gias_codes import ESTABLISHMENT_STATUS, PHASE_OF_EDUCATION
 
 
@@ -42,3 +42,91 @@ def test_missing_code_columns_are_a_noop():
     out = translate_gias_code_columns(df)
     assert out.iloc[0]["phase"] == "Primary"
     assert out.iloc[0]["status"] == "Open"
+
+
+def _fake_exc(orig_message):
+    """A stand-in for sqlalchemy.exc.ProgrammingError: str(exc) embeds the
+    full SQL statement (deliberately containing every column name below, to
+    prove the matcher doesn't fall back to it), while .orig carries the real
+    DBAPI error message naming only the offending column."""
+    exc = Exception(
+        "SELECT s.phase_code, s.school_type_code, s.religious_character_code, "
+        "s.status_code, s.admissions_policy_code, s.has_sixth_form FROM ... "
+        f"[SQL: ...] (Background on this error at: https://...)"
+    )
+    exc.orig = Exception(orig_message) if orig_message is not None else None
+    return exc
+
+
+def test_missing_column_name_quoted():
+    assert _missing_column_name(_fake_exc('column "phase_code" does not exist')) == "phase_code"
+
+
+def test_missing_column_name_unquoted():
+    assert _missing_column_name(_fake_exc("column phase_code does not exist")) == "phase_code"
+
+
+def test_missing_column_name_table_prefixed():
+    assert (
+        _missing_column_name(_fake_exc("column s.has_sixth_form does not exist"))
+        == "has_sixth_form"
+    )
+
+
+def test_missing_column_name_no_match_returns_none():
+    assert _missing_column_name(_fake_exc("relation \"marts.dim_school\" does not exist")) is None
+
+
+def test_load_school_data_survives_premigration_marts(monkeypatch):
+    """Real prod state until the nightly pipeline first rebuilds the mart with
+    the GIAS code columns: marts.dim_school still has the old name columns
+    (phase, school_type, religious_character, status, admissions_policy)
+    instead of the new *_code columns. The first query raises UndefinedColumn
+    on s.phase_code; load_school_data_as_dataframe must retry with the
+    legacy name-column query rather than swallow the error and return (and
+    then have load_school_data cache) an empty DataFrame."""
+    import sqlalchemy.exc
+    from backend import data_loader
+
+    data_loader._df_cache = None
+    data_loader._df_latest_cache = None
+
+    good_df = pd.DataFrame(
+        [
+            {
+                "urn": 1,
+                "school_name": "Legacy School",
+                "phase": "Primary",
+                "school_type": "Academy",
+                "status": "Open",
+            }
+        ]
+    )
+    calls = []
+
+    def fake_read_sql(query, con):
+        calls.append(query)
+        if len(calls) == 1:
+            raise sqlalchemy.exc.ProgrammingError(
+                statement=str(data_loader._MAIN_QUERY),
+                params=None,
+                orig=Exception(
+                    "(psycopg2.errors.UndefinedColumn) column s.phase_code "
+                    "does not exist\nLINE 5:         s.phase_code,"
+                ),
+            )
+        return good_df.copy()
+
+    monkeypatch.setattr(data_loader.pd, "read_sql", fake_read_sql)
+
+    try:
+        df = data_loader.load_school_data_as_dataframe()
+    finally:
+        data_loader._df_cache = None
+        data_loader._df_latest_cache = None
+
+    assert len(calls) == 2, "must retry with the legacy name-column query variant"
+    assert calls[1] is data_loader._MAIN_QUERY_LEGACY_NAMES
+    assert not df.empty
+    assert df["phase"].iloc[0] == "Primary"
+    assert df["status"].iloc[0] == "Open"

backend/tests/test_sixth_form_flag.py

@@ -148,10 +148,14 @@ def test_load_school_data_survives_missing_has_sixth_form_column(monkeypatch):
     def fake_read_sql(query, con):
         calls.append(query)
         if len(calls) == 1:
+            # The statement text still contains phase_code, school_type_code,
+            # etc. (it's the full _MAIN_QUERY SELECT list) — that's exactly
+            # the collision this test guards against: matching must be done
+            # against exc.orig (the DBAPI error), not str(exc)/the statement.
             raise sqlalchemy.exc.ProgrammingError(
-                "SELECT ...",
+                statement=str(data_loader._MAIN_QUERY),
-                None,
+                params=None,
-                Exception(
+                orig=Exception(
                     "(psycopg2.errors.UndefinedColumn) column s.has_sixth_form "
                     "does not exist"
                 ),

docs/superpowers/plans/2026-07-12-compare-data-foundation.md

@@ -0,0 +1,591 @@
+# Compare-Screen Data Foundation (Pipeline PR) Implementation Plan
+
+> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
+
+**Goal:** Land every pipeline/dbt change the compare-screen redesign needs (spec §5 + §8 of `docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md`): promote raw-but-unstored fields to marts, close the national-averages gaps, and wire the Ofsted report-card columns.
+
+**Architecture:** Meltano Singer taps load `raw.*` tables; dbt builds `staging` → `marts` (read-only for the backend). All changes here are additive columns/rows — no breaking changes to existing marts. The full `dbt build` runs on the server via the Airflow DAGs; locally we gate with `dbt parse` (no DB needed) plus network-only diagnostic scripts.
+
+**Tech Stack:** Python (Singer SDK taps), dbt-postgres ~1.10 (invoked as `python -m dbt.cli.main`), Meltano, PostgreSQL.
+
+## Global Constraints
+
+- **No new external sources** (spec §5): only fields already in the `raw` schema or in files the taps already download. The one sanctioned tap change is the Ofsted MI report-card columns (spec §5, §8.4) and the legacy-KS2 year addition (same DfE performance-tables source).
+- **Additive only:** never rename or drop existing mart columns; the backend maps them 1:1 in `backend/models.py`.
+- **Never push to `main`.** Branch: `feat/compare-data-foundation`; PR checks must pass.
+- Backend `models.py` changes belong to the follow-up backend PR, not this one.
+- dbt invocation is always `python -m dbt.cli.main` (a bare `dbt` resolves to the wrong binary — see `pipeline/dags/school_data_pipeline.py:27`).
+- EES suppression codes `z`/`c`/`x` must go through the `safe_numeric` macro.
+- Computed benchmarks (FSM/EAL/SEN medians, disadvantaged national average) are **backend work** (spec §5) — explicitly out of scope here.
+
+---
+
+### Task 0: Create the branch
+
+**Files:** none
+
+- [ ] **Step 1:** `git checkout main && git pull && git checkout -b feat/compare-data-foundation`
+
+---
+
+### Task 1: Diagnostics — pin the three unknowns
+
+The spec flags three facts we must confirm from the actual files before wiring code: (a) why `gps_expected_pct`/`science_expected_pct` are NULL in `marts.fact_ks2_national_averages` despite being mapped end-to-end; (b) what the KS2 attainment long file calls its subjects/years for 2021/22 and 2022/23 (subject-level 2022/23 is NULL in prod; school-level 2021/22 is absent); (c) the exact report-card column headers in the current Ofsted MI CSV.
+
+**Files:**
+- Create: `pipeline/scripts/diagnose_compare_gaps.py`
+
+**Interfaces:**
+- Produces: a printed findings report; Tasks 5, 6, 7 consume the confirmed column/label names. Precedent: `pipeline/scripts/diagnose_ees_ks4.py`.
+
+- [ ] **Step 1: Write the diagnostic script**
+
+```python
+"""Diagnose the three data gaps blocking the compare-screen redesign.
+
+Run from repo root (network access required, no DB needed):
+    python pipeline/scripts/diagnose_compare_gaps.py
+"""
+import io
+import re
+import sys
+import zipfile
+
+import pandas as pd
+import requests
+
+sys.path.insert(0, "pipeline/plugins/extractors/tap-uk-ees")
+sys.path.insert(0, "pipeline/plugins/extractors/tap-uk-ofsted")
+from tap_uk_ees.tap import (  # noqa: E402
+    _KS2_NATIONAL_COL_MAP,
+    _KS2_NATIONAL_CSV_URL,
+    download_release_zip,
+    get_all_releases,
+)
+from tap_uk_ofsted.tap import discover_csv_url  # noqa: E402
+
+TIMEOUT = 120
+
+
+def check_national_gps_science():
+    print("\n=== (a) National catalogue CSV: GPS/science columns ===")
+    resp = requests.get(_KS2_NATIONAL_CSV_URL, timeout=TIMEOUT)
+    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 csv_col in ("pt_gps_exp", "pt_scita_exp", "avg_readscore", "avg_matscore", "avg_gpsscore"):
+        status = "PRESENT" if csv_col in df.columns else "MISSING"
+        print(f"  {csv_col}: {status}")
+    gps_like = [c for c in df.columns if "gps" in c or "scita" in c or "sci" in c]
+    print(f"  all gps/science-ish columns: {gps_like}")
+    nat = df[df.get("geographic_level", "").str.strip().str.lower() == "national"]
+    print(f"  national rows time_periods: {sorted(nat['time_period'].unique())}")
+    # Sample the values our map would read for the latest year
+    latest = nat[nat["time_period"] == nat["time_period"].max()]
+    for csv_col, field in _KS2_NATIONAL_COL_MAP.items():
+        val = latest.iloc[0].get(csv_col, "<col missing>") if len(latest) else "<no row>"
+        print(f"  {field} <- {csv_col} = {val!r}")
+
+
+def check_ks2_attainment_years_subjects():
+    print("\n=== (b) EES KS2 attainment: years & subject labels ===")
+    releases = get_all_releases("key-stage-2-attainment")
+    print(f"  releases found: {[r['time_period'] for r in releases]}")
+    for release in releases:
+        zf = download_release_zip(release["id"])
+        name = next((n for n in zf.namelist()
+                     if "ks2_school_attainment_data" in n and n.endswith(".csv")), None)
+        if not name:
+            print(f"  {release['time_period']}: NO school attainment CSV in ZIP")
+            continue
+        with zf.open(name) as f:
+            df = pd.read_csv(f, dtype=str, keep_default_na=False, nrows=200000)
+        years = sorted(df["time_period"].unique())
+        subjects = sorted(df["subject"].unique())
+        print(f"  release {release['time_period']}: time_periods={years}")
+        print(f"    subjects={subjects}")
+
+
+def check_ofsted_report_card_columns():
+    print("\n=== (c) Ofsted MI CSV: report-card columns ===")
+    url = discover_csv_url()
+    print(f"  MI file: {url}")
+    resp = requests.get(url, timeout=TIMEOUT)
+    resp.raise_for_status()
+    df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False, nrows=5)
+    rc_like = [c for c in df.columns
+               if re.search(r"report card|inclusion|curriculum|achievement|safeguard|well.?being|governance", c, re.I)]
+    print(f"  candidate report-card columns ({len(rc_like)}):")
+    for c in rc_like:
+        print(f"    - {c!r}")
+
+
+if __name__ == "__main__":
+    check_national_gps_science()
+    check_ks2_attainment_years_subjects()
+    check_ofsted_report_card_columns()
+```
+
+Note: if `_KS2_NATIONAL_CSV_URL` is named differently in `tap_uk_ees/tap.py` (it is defined near the `_KS2_NATIONAL_COL_MAP` around line ~490), import whatever constant holds the catalogue CSV URL.
+
+- [ ] **Step 2: Run it and record findings**
+
+Run: `python pipeline/scripts/diagnose_compare_gaps.py 2>&1 | tee /tmp/compare-gaps-findings.txt`
+Expected: three sections printed. Paste the findings as a comment block at the bottom of the script (so they're committed evidence), e.g. `# FINDINGS 2026-07-12: pt_gps_exp MISSING (actual col: ...), 202122 present in release X, rc columns: [...]`.
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add pipeline/scripts/diagnose_compare_gaps.py
+git commit -m "chore(pipeline): diagnostic for compare-screen data gaps"
+```
+
+---
+
+### Task 2: Admissions preference detail → mart
+
+Staging already extracts `second_preference_offers`, `third_preference_offers`, `total_offers` (`stg_ees_admissions.sql:26-29`) — the mart drops them. The cross-LA fields are declared in the tap (`all_applications_from_another_LA`, `offers_to_applicants_from_another_LA`) but not selected in staging.
+
+**Files:**
+- Modify: `pipeline/transform/models/staging/stg_ees_admissions.sql` (after line 33, in `renamed`)
+- Modify: `pipeline/transform/models/marts/fact_admissions.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (fact_admissions block, ~line 120)
+
+**Interfaces:**
+- Produces mart columns: `total_offers int`, `second_preference_offers int`, `third_preference_offers int`, `cross_la_applications int`, `cross_la_offers int`. The backend PR will map these in `FactAdmissions`.
+
+- [ ] **Step 1: Add cross-LA columns to staging**
+
+In `stg_ees_admissions.sql`, after the `first_preference_applications` line (line 33):
+
+```sql
+        -- Cross-borough demand: applications naming this school from families
+        -- living in another local authority, and offers made to them.
+        {{ safe_numeric('"all_applications_from_another_LA"') }}::integer as cross_la_applications,
+        {{ safe_numeric('"offers_to_applicants_from_another_LA"') }}::integer as cross_la_offers,
+```
+
+(Quote the identifiers — the tap emits them with mixed case, same trap as `FSM_eligible_percent`, see the header comment in that file. If `dbt parse` or the DAG run later shows the raw columns are lower-cased in Postgres, drop the double quotes.)
+
+- [ ] **Step 2: Pass everything through the mart**
+
+Replace the full select list in `fact_admissions.sql`:
+
+```sql
+-- Mart: School admissions — one row per URN per year
+
+select
+    urn,
+    year,
+    school_phase,
+    places_offered,
+    total_offers,
+    total_applications,
+    first_preference_applications,
+    first_preference_offers,
+    second_preference_offers,
+    third_preference_offers,
+    cross_la_applications,
+    cross_la_offers,
+    first_preference_offer_pct,
+    oversubscription_ratio,
+    oversubscribed,
+    admissions_policy
+from {{ ref('stg_ees_admissions') }}
+```
+
+- [ ] **Step 3: Add schema tests**
+
+In `_marts_schema.yml` under `fact_admissions.columns`, append:
+
+```yaml
+      - name: second_preference_offers
+      - name: third_preference_offers
+      - name: cross_la_applications
+      - name: cross_la_offers
+      - name: total_offers
+```
+
+- [ ] **Step 4: Parse gate**
+
+Run: `cd pipeline/transform && python -m dbt.cli.main parse --profiles-dir .`
+Expected: `Done.` with no compilation errors.
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/transform/models/staging/stg_ees_admissions.sql pipeline/transform/models/marts/fact_admissions.sql pipeline/transform/models/marts/_marts_schema.yml
+git commit -m "feat(pipeline): admissions preference breakdown and cross-LA demand in marts"
+```
+
+---
+
+### Task 3: KS2 progress confidence intervals + writing working-towards
+
+The tap already emits `progress_measure_lower_conf_interval`, `progress_measure_upper_conf_interval`, `working_towards_expected_standard_pupil_percent` (tap.py:203-206). The staging pivot drops them. These power the CI-based Above/Average/Below progress chips (spec §8, first-review item on statistical honesty).
+
+**Files:**
+- Modify: `pipeline/transform/models/staging/stg_ees_ks2.sql` (inside the `pivoted` CTE, next to each subject's `progress_measure_score` case, lines ~41/55/72, and in the final select ~lines 145-152)
+- Modify: `pipeline/transform/models/marts/fact_ks2_performance.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (fact_ks2_performance block, ~line 82)
+
+**Interfaces:**
+- Produces mart columns: `reading_progress_lower_ci`, `reading_progress_upper_ci`, `writing_progress_lower_ci`, `writing_progress_upper_ci`, `maths_progress_lower_ci`, `maths_progress_upper_ci` (float), `writing_working_towards_pct` (float).
+
+- [ ] **Step 1: Add pivot cases in staging**
+
+After the `reading_progress` case (line ~41), add:
+
+```sql
+        max(case when subject = 'Reading'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as reading_progress_lower_ci,
+        max(case when subject = 'Reading'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as reading_progress_upper_ci,
+```
+
+After the `writing_progress` case (line ~55), add:
+
+```sql
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as writing_progress_lower_ci,
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as writing_progress_upper_ci,
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('working_towards_expected_standard_pupil_percent') }} end) as writing_working_towards_pct,
+```
+
+After the `maths_progress` case (line ~72), add:
+
+```sql
+        max(case when subject = 'Maths'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as maths_progress_lower_ci,
+        max(case when subject = 'Maths'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as maths_progress_upper_ci,
+```
+
+Then add the seven new columns to the model's final select (next to the existing `p.reading_progress` / `p.writing_progress` / `p.maths_progress` lines ~145-152):
+
+```sql
+    p.reading_progress_lower_ci,
+    p.reading_progress_upper_ci,
+    p.writing_progress_lower_ci,
+    p.writing_progress_upper_ci,
+    p.writing_working_towards_pct,
+    p.maths_progress_lower_ci,
+    p.maths_progress_upper_ci,
+```
+
+- [ ] **Step 2: Pass through the mart**
+
+In `fact_ks2_performance.sql`, add the same seven column names to the select list immediately after the existing `maths_progress` line (this mart selects staging columns by name; match the file's existing alias style — if columns are selected bare, add them bare).
+
+- [ ] **Step 3: Schema tests**
+
+In `_marts_schema.yml` under `fact_ks2_performance.columns`, append the seven names (no tests beyond presence — values are legitimately NULL for 2023/24+ since progress measures ended with 2022/23, spec §4.3):
+
+```yaml
+      - name: reading_progress_lower_ci
+      - name: reading_progress_upper_ci
+      - name: writing_progress_lower_ci
+      - name: writing_progress_upper_ci
+      - name: writing_working_towards_pct
+      - name: maths_progress_lower_ci
+      - name: maths_progress_upper_ci
+```
+
+- [ ] **Step 4: Parse gate**
+
+Run: `cd pipeline/transform && python -m dbt.cli.main parse --profiles-dir .`
+Expected: `Done.`
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/transform/models/staging/stg_ees_ks2.sql pipeline/transform/models/marts/fact_ks2_performance.sql pipeline/transform/models/marts/_marts_schema.yml
+git commit -m "feat(pipeline): KS2 progress confidence intervals and writing working-towards"
+```
+
+---
+
+### Task 4: KS4 — Progress 8 banding and disadvantage gaps
+
+The tap's `ees_ks4_info` stream already declares `progress8_banding` (DfE's own "well above average … well below average" label — the ready-made secondary chip), `attainment8_diffn` and `progress8_diffn` (tap.py:338-340). Wire them through staging into the mart.
+
+**Files:**
+- Modify: `pipeline/transform/models/staging/stg_ees_ks4.sql` (the CTE that reads `ees_ks4_info` — the same one that already surfaces `sen_pct`; add three columns to its select and to the final joined select)
+- Modify: `pipeline/transform/models/marts/fact_ks4_performance.sql` (add after `progress_8_upper_ci`)
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (fact_ks4_performance block, ~line 93)
+
+**Interfaces:**
+- Produces mart columns: `progress_8_banding text`, `attainment_8_disadvantage_gap float`, `progress_8_disadvantage_gap float`.
+
+- [ ] **Step 1: Staging — select from the info source**
+
+In the info CTE of `stg_ees_ks4.sql` add:
+
+```sql
+        nullif(trim(progress8_banding), '') as progress_8_banding,
+        {{ safe_numeric('attainment8_diffn') }} as attainment_8_disadvantage_gap,
+        {{ safe_numeric('progress8_diffn') }} as progress_8_disadvantage_gap,
+```
+
+and add the three names to the model's final select (aliased the same way the CTE's other columns are).
+
+- [ ] **Step 2: Mart passthrough**
+
+In `fact_ks4_performance.sql`, after the `progress_8_upper_ci,` line:
+
+```sql
+    progress_8_banding,
+    attainment_8_disadvantage_gap,
+    progress_8_disadvantage_gap,
+```
+
+- [ ] **Step 3: Schema tests** — append the three names under `fact_ks4_performance.columns`, plus an accepted-values guard that tolerates NULL:
+
+```yaml
+      - name: progress_8_banding
+        tests:
+          - accepted_values:
+              values: ['Well above average', 'Above average', 'Average', 'Below average', 'Well below average']
+              config:
+                where: "progress_8_banding is not null"
+      - name: attainment_8_disadvantage_gap
+      - name: progress_8_disadvantage_gap
+```
+
+(If the DAG run later shows different capitalisation in the data, fix the accepted values to match the data, not vice versa.)
+
+- [ ] **Step 4: Parse gate** — `cd pipeline/transform && python -m dbt.cli.main parse --profiles-dir .` → `Done.`
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/transform/models/staging/stg_ees_ks4.sql pipeline/transform/models/marts/fact_ks4_performance.sql pipeline/transform/models/marts/_marts_schema.yml
+git commit -m "feat(pipeline): Progress 8 banding and KS4 disadvantage gaps in marts"
+```
+
+---
+
+### Task 5: National averages — 2015/16 row and GPS/science/scaled-score fix
+
+Two changes. (1) `stg_ees_ks2_national.sql:34` filters `>= 201617`, which is exactly why the England line starts a year late (2015/16 RWM = 53% exists in the catalogue). (2) GPS/science expected are NULL in prod despite full end-to-end mapping — Task 1's findings say whether the catalogue CSV column names differ from `_KS2_NATIONAL_COL_MAP` (`pt_gps_exp`, `pt_scita_exp`) or whether values are suppressed at source.
+
+**Files:**
+- Modify: `pipeline/transform/models/staging/stg_ees_ks2_national.sql:34`
+- Modify (conditional on Task 1 findings): `pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py` (`_KS2_NATIONAL_COL_MAP`)
+
+**Interfaces:**
+- Produces: a 201516 row in `marts.fact_ks2_national_averages`; non-NULL `gps_expected_pct`, `science_expected_pct`, `reading_avg_score`, `maths_avg_score`, `gps_avg_score` for years the DfE publishes them. Backend/frontend consume via `/api/national-averages` unchanged (additive year + newly non-NULL fields).
+
+- [ ] **Step 1: Widen the year filter**
+
+In `stg_ees_ks2_national.sql`, change line 34:
+
+```sql
+  and cast(trim(time_period) as integer) >= 201516
+```
+
+(2015/16 was the first year of the current expected-standard tests; nothing earlier is comparable, so keep a floor.)
+
+- [ ] **Step 2: Fix the column map per Task 1 findings**
+
+If Task 1 reported the actual CSV column names for GPS/science/scaled scores differ, update `_KS2_NATIONAL_COL_MAP` in `tap.py` accordingly, e.g. (illustrative — use the diagnosed names):
+
+```python
+_KS2_NATIONAL_COL_MAP = {
+    # ... existing entries ...
+    "pt_gps_exp": "gps_expected_pct",      # replace key with diagnosed name
+    "pt_scita_exp": "science_expected_pct",  # replace key with diagnosed name
+}
+```
+
+If Task 1 showed the columns are present but suppressed (`x`) at national level for all years, instead delete the two entries from the map, delete the corresponding lines from `stg_ees_ks2_national.sql` and `fact_ks2_national_averages.sql`, and record in the PR description that GPS/science England ticks stay "not in dataset" (the mockups already carry that caveat).
+
+- [ ] **Step 3: Parse gate** — `cd pipeline/transform && python -m dbt.cli.main parse --profiles-dir .` → `Done.`
+
+- [ ] **Step 4: Commit**
+
+```bash
+git add pipeline/transform/models/staging/stg_ees_ks2_national.sql pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py
+git commit -m "fix(pipeline): include 2015/16 national averages; fix GPS/science national mapping"
+```
+
+---
+
+### Task 6: Legacy KS2 — load the 2021/22 school-level year
+
+School-level 2021/22 exists in DfE performance-tables archives (same source as the four legacy years already loaded) but in neither our legacy config (stops at 201819, `pipeline/meltano.yml:33-37`) nor EES (starts 2022/23) — unless Task 1's finding (b) showed an EES release carrying 202122, in which case skip this task and note why in the PR.
+
+The legacy URLs point at the self-hosted filebrowser (`10.0.1.224:8081`) — **the 2021/22 DfE archive must be uploaded there first; this is the one human dependency in this plan.**
+
+**Files:**
+- Modify: `pipeline/meltano.yml` (legacy_ks2_urls block, line ~33)
+
+**Interfaces:**
+- Produces: `raw.legacy_ks2` rows with `year = '202122'`, flowing through `stg_legacy_ks2` → `fact_ks2_performance` unchanged (the stream maps old column names already; 2021/22 CSVs use the same `PTRWM_EXP`-style headers as 2018/19).
+
+- [ ] **Step 1: Verify the 2021/22 CSV headers match `_LEGACY_KS2_COLUMN_MAP`**
+
+Download the DfE 2021/22 KS2 revised archive (gov.uk "Compare School Performance data download": 2021-2022 all-schools ZIP), then:
+
+Run: `python -c "import zipfile,io,pandas as pd; zf=zipfile.ZipFile('/path/to/2021-2022.zip'); n=[x for x in zf.namelist() if 'ks2final' in x.lower() and x.endswith('.csv')][0]; df=pd.read_csv(zf.open(n), dtype=str, nrows=5); import sys; sys.path.insert(0,'pipeline/plugins/extractors/tap-uk-ees'); from tap_uk_ees.tap import _LEGACY_KS2_COLUMN_MAP as m; missing=[c for c in m if c not in df.columns]; print('missing legacy columns:', missing)"`
+Expected: `missing legacy columns: []` (progress columns `READPROG` etc. may legitimately be missing/blank in 2021/22 — acceptable, they load as NULL).
+
+- [ ] **Step 2: Upload the archive to the filebrowser and add the config entry**
+
+In `pipeline/meltano.yml` under `legacy_ks2_urls`, add (with the real share URL from the filebrowser upload):
+
+```yaml
+          "202122": "http://10.0.1.224:8081/filebrowser/api/public/dl/<SHARE_ID>?inline=true"
+```
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add pipeline/meltano.yml
+git commit -m "feat(pipeline): load 2021/22 school-level KS2 from legacy performance tables"
+```
+
+- [ ] **Step 4 (only if Task 1(b) showed 2022/23 subject labels differ):** widen the subject matchers in `stg_ees_ks2.sql` the same way GPS already is (`subject ilike '%grammar%' or subject = 'GPS'`), e.g. `subject in ('Reading', 'reading')` → use the diagnosed labels. Parse-gate and commit as `fix(pipeline): match 2022/23 KS2 subject labels`.
+
+---
+
+### Task 7: Ofsted report-card columns (rc_*)
+
+Resolves the tap TODO (`stg_ofsted_inspections.sql:37`). The marts/backed columns already exist as stubs; this wires real values. Uses Task 1(c)'s confirmed MI column names — the candidates below follow the MI file's existing naming style and must be corrected against the diagnostic output.
+
+**Files:**
+- Modify: `pipeline/plugins/extractors/tap-uk-ofsted/tap_uk_ofsted/tap.py` (COLUMN_PRIORITY ~line 19-72, schema ~line 100-114)
+- Create: `pipeline/transform/macros/parse_report_card_grade.sql`
+- Modify: `pipeline/transform/models/staging/stg_ofsted_inspections.sql:36-46`
+
+**Interfaces:**
+- Produces mart columns (already declared in `fact_ofsted_inspection`): `rc_safeguarding_met boolean`, and `rc_inclusion` … `rc_sixth_form` as integers on the 5-point scale `1=Exceptional, 2=Strong standard, 3=Expected standard, 4=Needs attention/Attention needed, 5=Urgent improvement`. The backend translates codes to labels (same pattern as `gias_codes.py`), verifying wording against Ofsted's published toolkit (spec §8.4).
+
+- [ ] **Step 1: Add tap column mappings**
+
+In `COLUMN_PRIORITY` add (replace candidate strings with Task 1(c)'s exact headers — keep them as priority lists so older files degrade to blank):
+
+```python
+    "rc_safeguarding_met": ["Report card safeguarding", "Safeguarding"],
+    "rc_inclusion": ["Report card inclusion", "Inclusion"],
+    "rc_curriculum_teaching": ["Report card curriculum and teaching", "Curriculum and teaching"],
+    "rc_achievement": ["Report card achievement", "Achievement"],
+    "rc_attendance_behaviour": ["Report card attendance and behaviour", "Attendance and behaviour"],
+    "rc_personal_development": ["Report card personal development and well-being", "Personal development and well-being"],
+    "rc_leadership_governance": ["Report card leadership and governance", "Leadership and governance"],
+    "rc_early_years": ["Report card early years", "Early years"],
+    "rc_sixth_form": ["Report card sixth form", "Sixth form"],
+```
+
+And in the stream schema (next to `report_url`, ~line 114):
+
+```python
+        th.Property("rc_safeguarding_met", th.StringType),
+        th.Property("rc_inclusion", th.StringType),
+        th.Property("rc_curriculum_teaching", th.StringType),
+        th.Property("rc_achievement", th.StringType),
+        th.Property("rc_attendance_behaviour", th.StringType),
+        th.Property("rc_personal_development", th.StringType),
+        th.Property("rc_leadership_governance", th.StringType),
+        th.Property("rc_early_years", th.StringType),
+        th.Property("rc_sixth_form", th.StringType),
+```
+
+- [ ] **Step 2: Write the grade-parsing macro**
+
+`pipeline/transform/macros/parse_report_card_grade.sql`:
+
+```sql
+{% macro parse_report_card_grade(column_name) %}
+    case lower(trim(nullif({{ column_name }}, 'NULL')))
+        when 'exceptional' then 1
+        when 'strong standard' then 2
+        when 'expected standard' then 3
+        when 'needs attention' then 4
+        when 'attention needed' then 4
+        when 'urgent improvement' then 5
+    end
+{% endmacro %}
+```
+
+- [ ] **Step 3: Wire staging**
+
+Replace `stg_ofsted_inspections.sql` lines 36-46 (the NULL stubs) with:
+
+```sql
+        -- Report Card fields (post-Nov 2025 framework), 5-point scale:
+        -- 1 Exceptional · 2 Strong standard · 3 Expected standard
+        -- · 4 Needs attention · 5 Urgent improvement
+        (lower(trim(nullif(rc_safeguarding_met, 'NULL'))) = 'met')        as rc_safeguarding_met,
+        {{ parse_report_card_grade('rc_inclusion') }}::integer            as rc_inclusion,
+        {{ parse_report_card_grade('rc_curriculum_teaching') }}::integer  as rc_curriculum_teaching,
+        {{ parse_report_card_grade('rc_achievement') }}::integer          as rc_achievement,
+        {{ parse_report_card_grade('rc_attendance_behaviour') }}::integer as rc_attendance_behaviour,
+        {{ parse_report_card_grade('rc_personal_development') }}::integer as rc_personal_development,
+        {{ parse_report_card_grade('rc_leadership_governance') }}::integer as rc_leadership_governance,
+        {{ parse_report_card_grade('rc_early_years') }}::integer          as rc_early_years,
+        {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,
+```
+
+Note `rc_safeguarding_met` becomes boolean (NULL when blank) — matching `fact_ofsted_inspection`'s `rc_safeguarding_met` Boolean column. If `fact_ofsted_inspection.sql` casts these columns, align its casts too (inspect that model; it currently passes the text stubs through).
+
+- [ ] **Step 4: Parse gate + tap smoke test**
+
+Run: `cd pipeline/transform && python -m dbt.cli.main parse --profiles-dir .` → `Done.`
+Run: `python -c "import sys; sys.path.insert(0,'pipeline/plugins/extractors/tap-uk-ofsted'); from tap_uk_ofsted.tap import COLUMN_PRIORITY; assert 'rc_inclusion' in COLUMN_PRIORITY; print('ok')"` → `ok`
+
+- [ ] **Step 5: Commit**
+
+```bash
+git add pipeline/plugins/extractors/tap-uk-ofsted/tap_uk_ofsted/tap.py pipeline/transform/macros/parse_report_card_grade.sql pipeline/transform/models/staging/stg_ofsted_inspections.sql
+git commit -m "feat(pipeline): extract Ofsted report-card judgements (rc_* columns)"
+```
+
+---
+
+### Task 8: PR + post-merge verification
+
+**Files:** none new
+
+- [ ] **Step 1: Push and open the PR** (Gitea — use the git credential helper + basic-auth API pattern; token-header auth 401s):
+
+```bash
+git push -u origin feat/compare-data-foundation
+# then create the PR via the Gitea API with basic auth from `git credential fill`
+```
+
+PR body: link spec §5/§8, list the new mart columns, note the Task 6 human dependency (filebrowser upload) and the Task 1 findings file.
+
+- [ ] **Step 2: After merge, verify the DAG run picked everything up**
+
+The daily/monthly DAGs rebuild the affected models (`pipeline/dags/school_data_pipeline.py`). Spot-check via the public API (production after promotion, staging first at stx.schoolcompare.co.uk — note external /api is broken at the staging proxy, so check staging from the host):
+
+```bash
+# 2015/16 national row exists
+curl -sL "https://www.schoolcompare.co.uk/api/national-averages" | python3 -c "import json,sys; d=json.load(sys.stdin); assert any(r['year']==201516 and r['primary'] for r in d['by_year']), '2015/16 missing'; print('201516 ok')"
+# 2021/22 school rows exist (Barclay)
+curl -sL "https://www.schoolcompare.co.uk/api/schools/138690" | python3 -c "import json,sys; d=json.load(sys.stdin); ys=[r['year'] for r in d['yearly_data']]; assert 202122 in [int(y) for y in ys], ys; print('202122 ok')"
+```
+
+(The admissions/CI/KS4/rc_* columns aren't API-visible until the backend PR maps them — verify those directly in Postgres from the pipeline host: `select count(*) from marts.fact_admissions where second_preference_offers is not null;` etc.)
+
+- [ ] **Step 3: Update the spec** — tick off the §5 promotions this PR delivered (edit the spec's promotion list to note "landed in PR #NN") and commit to main via a docs PR or alongside the backend PR.
+
+---
+
+## Out of scope (next plans)
+
+1. **Backend PR:** map new columns in `backend/models.py`, extend `/api/compare` with supplementary blocks + `national_averages`, computed benchmarks (FSM/EAL/SEN/size medians, disadvantaged national average), CI-based progress banding, report-card label translation (verify against Ofsted toolkit), Ofsted provider-page URLs, graded-vs-ungraded surfacing.
+2. **Frontend PR:** rebuild `/compare` per the mockups + e2e journeys (promotion gate).
+3. **Separate bug fix:** third school's series not rendering on the current production chart.
+4. **Post-v1 (spec):** census ethnicity/young-carer promotion, IDACI display, attendance section, gender-split/absence tier-2 measures.
+5. **Already in marts, no work needed:** KS4 EBacc entry/APS, grade 5+ English & maths, Progress 8 CIs — `fact_ks4_performance` carries them today; only the backend needs to expose them.

docs/superpowers/specs/2026-07-11-compare-screen-expert-review.md

@@ -0,0 +1,177 @@
+# Compare Screen Redesign — Expert Data Review
+
+**Date:** 2026-07-11
+**Reviewer:** subagent briefed as an English education-standards / DfE-Ofsted data expert
+**Subject:** desktop + mobile compare mockups and the redesign spec
+(`2026-07-11-compare-screen-redesign-design.md`)
+**Status:** first-pass must-fixes applied 2026-07-12; second-pass
+findings (below) applied 2026-07-12 — mockups + spec §4/§8 updated
+
+## Must-fix
+
+1. **COVID gap is wrong and drops a real results year.** KS2 tests were
+   cancelled 2019/20 and 2020/21 only; they resumed in 2021/22 with
+   published school-level results (England RWM ≈ 59%). The mockup charts
+   omit 2021/22 entirely and the tooltip claims no tests were held
+   2019/20–2021/22. Fix: add 2021/22 to axis and all series; shrink the
+   gap band; optionally annotate 2021/22 with DfE's post-pandemic
+   comparability caution.
+2. **Report-card at-a-glance summary miscounts areas.** Detail list has
+   4 Strong / 2 Expected / 1 Attention needed + Safeguarding met, but
+   the summary says "3 areas Expected standard" — it counts safeguarding
+   as a graded area. Safeguarding is a separate binary judgement and
+   must be excluded from rating counts.
+3. **"Where the offers went" derivation is unsound.** Places − 1st-pref
+   offers ≠ "second or third choices": the residual can include 4th–6th
+   preference offers (pan-London scheme) and LA-allocated children who
+   didn't choose the school; and offers don't necessarily equal PAN.
+   Use the real 2nd/3rd-preference fields being promoted from
+   `raw.ees_admissions`; until then drop the row.
+4. **Ofsted timeline in the copy is wrong.** Overall grades were
+   abolished September 2024, not November 2025; Sept 2024–Nov 2025
+   inspections kept the four key judgements without an overall grade
+   (ungraded inspections carried grades forward). Neither mockup shows
+   the interim regime, which will dominate real comparisons. Fix copy
+   and add an interim example.
+5. **Barclay's "published an overall grade only — no area-by-area
+   detail" misdescribes inspections.** No inspection type does that; a
+   2021 graded inspection necessarily had subgrades — the gap is in our
+   dataset. If it was an ungraded (s8) inspection, "Outstanding" is a
+   carried-forward grade and should say so. Fix: "We don't hold
+   area-by-area detail for this inspection", and distinguish graded vs
+   ungraded in the data model.
+
+## Should-fix
+
+6. Writing is teacher assessment, not a test — "national tests and
+   teacher assessments"; note TA caveat on the Writing strip.
+7. Verify renewed-framework wording against Ofsted's final toolkit:
+   likely "Needs attention" (not "Attention needed") and "Personal
+   development and well-being" (which otherwise collides with the
+   identically-named legacy judgement). Pin every label to the
+   published toolkit.
+8. "Expected standard" now means two things on one page (Ofsted area
+   rating vs KS2 measure) — disambiguate in tooltips.
+9. Disadvantaged row: DfE definition includes looked-after / previously
+   looked-after children, not just FSM6; benchmark labels inconsistent
+   across desktop/mobile; subgroup percentages need cohort sizes or a
+   volatility threshold before chips are attached.
+10. "Trend, last 7 years" spans ten years; sparklines render the COVID
+    gap as equal spacing (the exact defect the audit criticises) and
+    "Improved: 52% → 87%" endpoint-cherry-picks a volatile series.
+11. At-a-glance "Getting a place" uses different metrics per school
+    (Barclay is also oversubscribed on total preferences but shows a
+    green chip). Standardise on first-preference success %. Explain the
+    equal-preference rule; condition "living close by matters" on the
+    school's actual oversubscription criteria.
+12. "457 applications for 180 places" = total preferences at any rank,
+    not head-to-head applicants; lead with first preferences vs places.
+    Add offers-vs-final-intake (waiting lists/appeals) caveat.
+13. Elmhurst's subgrade list is likely missing Early years provision
+    (school has a nursery) — possible pipeline gap.
+14. "Ofsted rating" label is obsolete post-Sept-2024 — use "Latest
+    Ofsted inspection"; check whether Oct 2021 is the latest inspection
+    or merely the latest graded one.
+15. SEN: "EHCP plans" is redundant; 28% SEN support often indicates
+    resourced provision — add a note; England SEN-support ≈ 14%, not 13%.
+
+## Nice-to-have
+
+16. Consistent labelling of official DfE vs dataset-computed benchmarks
+    (and medians shouldn't be called averages inconsistently).
+17. England 2015/16 RWM (53%) exists in DfE publications — the null is
+    a dataset gap; source it or the England line looks broken.
+18. "1 in 4 first choices missed out" — actually more than 1 in 4.
+19. "1,273 of 1,260 places (full)" is over capacity; capacity figures
+    are often stale — say "at or above capacity".
+20. State the actual suppression rule (DfE: ≤5 pupils suppressed,
+    small numbers rounded) instead of "a handful".
+21. Spec §4.3 progress chips can't exist for displayed years: KS2
+    progress ended with 2022/23 (no KS1 baseline) and returns
+    ~2027/28 with the reception baseline. Make explicit in the spec.
+    IDACI (spec §4.5) is absent from mockups; if shipped, caveat it
+    describes pupils' neighbourhoods, not the school.
+22. Tooltips should give the official term "first preference" alongside
+    the plain-English "first choice".
+
+## Overall assessment (verbatim gist)
+
+The bones are genuinely good by education-data standards —
+England-average anchoring, explicit non-comparability messaging across
+Ofsted regimes, refusal to synthesise an overall grade, time-true
+x-axis, neutral FSM/EAL framing — better than most commercial
+school-comparison sites. But items 1–5 are outright factual errors or
+misdescriptions that a well-informed parent or Ofsted would catch;
+the admissions section needs the most conceptual work (equal
+preference, preferences-vs-applicants, offers-vs-intake). Fix 1–5
+before user testing; the rest fold into the planned PRs.
+
+---
+
+# Second-pass review (2026-07-12)
+
+Same reviewer, after the must-fixes and the new three-tier metric
+exposure model were applied.
+
+## Verification of first-pass must-fixes
+
+- **1 (COVID/2021/22): resolved.** Time-true axis, band covers only the
+  cancelled years, England 58.7% consistent with official figures,
+  dataset gaps break lines honestly; reading/maths England series all
+  match published figures; RWM ≤ min(subject) checks pass.
+- **2 (report-card count): resolved** — safeguarding excluded, spec §8.2.
+- **3 (offers derivation): resolved** — row removed, spec §8.3 bans it.
+- **4 (Ofsted timeline): resolved on desktop; mobile omits the interim
+  regime clause** (see finding 6).
+- **5 (Barclay explanation): resolved.**
+
+## New findings
+
+1. **Should-fix — scaled-score strip domain contradicts caption.**
+   Caption says "scaled scores run 80–120", strips render 100–120;
+   truncated domain exaggerates small gaps and below-100 averages
+   would fall off the edge. Render 80–120, or caption the 100–120
+   window honestly and define below-100 behaviour.
+2. **Should-fix — scaled-score England ticks (106/105/105) unsourced.**
+   Plausible but hand-entered; verify against DfE 2024/25 tables and
+   add loading official England scaled scores to the pipeline list
+   (absent from §8.1/§8.6).
+3. **Should-fix — "Writing" listed under "Higher standard" in the
+   picker.** Writing TA outcome is "greater depth" (GDS), never
+   "higher standard". Label "Writing — greater depth (teacher
+   assessment)"; tooltip the combined higher-standard composition.
+4. Nice — "grammar & punctuation" summary line drops "spelling" (GPS).
+5. Nice — science is teacher-assessed (no KS2 test since 2009) and
+   coarse; tooltip it like writing; reconsider its tier-2 slot.
+6. **Should-fix — mobile Ofsted copy skips the interim regime**
+   (Sept 2024–Nov 2025) that desktop explains. One clause fixes it.
+7. **Should-fix — benchmark provenance still inconsistent** (EAL
+   tooltip unsourced; FSM/disadvantaged chips vs tooltips use three
+   vocabularies; header note says all England averages are official).
+   Adopt one house style: official = "England average", computed =
+   "benchmark / typical state school (our dataset)". Also tighten EAL
+   definition to census wording ("first language known or believed to
+   be other than English").
+8. Nice — "community primaries" distance note attached to an academy
+   (Elmhurst); say "non-faith primaries" or condition on policy field.
+9. Nice — "Improving since 2022" → "since 2022/23".
+10. Nice — England chart tooltips show decimals; §7 mandates whole
+    percents.
+
+## Residual gaps not covered by spec §8
+
+11. Spec promises IDACI-in-words, Attendance section, and tier-2
+    gender/absence that the mockups never show — mark post-v1 or
+    demonstrate, so implementation scope is unambiguous.
+12. Add official England scaled-score averages to the pipeline task
+    list.
+13. Add the writing/greater-depth terminology rule to §8.7.
+
+## Verdict
+
+All must-fixes genuinely resolved; the tier model is conceptually
+sound ("no measure is lost", honest dataset-gap breaks, grouped
+picker). Remaining issues are contained: one internal contradiction
+(80–120 vs 100–120), one provenance inconsistency, one terminology
+error (writing/GDS). With findings 1–3 and 6–7 addressed, the data
+framing is fit to put in front of parents.

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

@@ -0,0 +1,324 @@
+# Compare Screen Redesign — Audit & Design
+
+**Date:** 2026-07-11
+**Status:** Draft — awaiting review
+**Scope:** `/compare` page (nextjs-app), `/api/compare` endpoint (backend)
+
+## 1. Audit of the current screen
+
+The current compare page (`nextjs-app/components/ComparisonView.tsx`) is a
+single-metric analyst tool: a `<select>` with ~40 KS2/GCSE metrics, one
+line chart over time, and a year-by-year table — all for the one selected
+metric. Observed on production with 3 primary schools:
+
+**What works**
+
+- URL-shareable state (`?urns=…&metric=…`), native share sheet.
+- Phase tabs (primary/secondary) with sensible auto-detection.
+- Colour-coded school cards tied to chart series.
+- Metric descriptions from `/api/metrics` (single source of truth).
+
+**What doesn't**
+
+1. **Performance-only.** The database already holds Ofsted inspections,
+   admissions/oversubscription history, pupil characteristics (FSM/EAL),
+   SEN, deprivation (IDACI), finance, capacity, faith, gender, trust —
+   none of it reaches the compare screen. `/api/compare` returns only
+   `yearly_data` + minimal `school_info`, while `/api/schools/{urn}`
+   already returns all supplementary blocks.
+2. **One metric at a time.** A parent must know which of ~40 metrics
+   matters, select each in turn, and hold results in their head. There is
+   no side-by-side overview and no way to see two dimensions at once.
+3. **No benchmarks.** Numbers float without anchors: is 79% RWM good?
+   The DB has official national averages (`fact_ks2_national_averages`)
+   but the page never shows them.
+4. **Domain jargon untranslated.** "GPS Expected %", "Progress scores",
+   "RWM Combined" assume DfE literacy. The only plain-English help is one
+   note for progress scores.
+5. **Raw numbers, no judgement support.** 87.0% vs 92.0% vs 79.0% — the
+   page never says "all three are well above the England average of 62%",
+   which is the fact a parent actually needs.
+6. **Bugs/paper cuts observed:** the third school's series did not render
+   on the production chart despite table data (worth a separate fix);
+   the COVID gap (2018/19 → 2022/23) renders as equal spacing with no
+   annotation; table shows "87.0%" precision that implies false accuracy.
+
+## 2. Data inventory (available vs shown)
+
+| Domain | Source table | On detail page | On compare |
+|---|---|---|---|
+| KS2 attainment/progress | fact_ks2_performance | yes | **yes** (only thing shown) |
+| National averages | fact_ks2_national_averages | partial | no |
+| Ofsted (latest + subgrades + report-card fields) | fact_ofsted_inspection, dim_school | yes | no |
+| Admissions & oversubscription (multi-year) | fact_admissions | yes | no |
+| Pupil characteristics (FSM, EAL, gender split) | fact_pupil_characteristics | yes | no |
+| Context (SEN, disadvantaged, stability, absence) | fact_ks2_performance | via metric picker | buried in picker |
+| Deprivation (IDACI) | fact_deprivation | yes | no |
+| Finance (per-pupil spend) | fact_finance | yes | no |
+| School facts (capacity, faith, ages, trust, nursery, gender) | dim_school | yes | no |
+| Location/distance | dim_location | map | no |
+
+## 3. Design goals
+
+1. **Answer parent questions, in order:** Is it a good school (Ofsted)?
+   Do children do well there (academics vs England)? Will my child get a
+   place (admissions)? What is the school like (size, community, faith)?
+2. **Every number gets an anchor** — the England average, rendered as a
+   consistent visual tick, plus a plain-English chip
+   (Above / Close to / Below England average).
+3. **Plain English first, jargon on demand.** Labels are questions or
+   sentences ("Children reaching the expected standard in reading,
+   writing and maths"), codes/acronyms live in tooltips.
+4. **Scan whole-picture first, drill down second.** The single-metric
+   trend explorer survives, demoted to an "Explore trends" section at the
+   bottom rather than being the entire page.
+
+## 4. Proposed structure
+
+Columns = schools (max 4 visible on desktop, horizontal scroll beyond),
+rows = dimensions. Sticky compact school header keeps column identity
+while scrolling. Sections, in order:
+
+1. **At a glance** — verdict row per school: Ofsted badge, headline
+   attainment vs England (dot strip + chip), oversubscription chip,
+   size, distance (when a location is set).
+2. **Ofsted inspection** — must handle all three inspection regimes,
+   which will coexist in comparisons for years:
+   - **Legacy graded (pre-Sept 2024):** overall grade badge
+     (Outstanding/Good/Requires improvement/Inadequate). Subgrades,
+     where published, are rendered in the **same area-by-rating chip
+     list UX as report cards** (one row per judgement area, rating as
+     a chip) — one visual grammar for inspection detail across both
+     regimes. Where our dataset has no subgrades for an inspection,
+     say so honestly ("We don't hold area-by-area detail for this
+     inspection") and point to the school's Ofsted page — never claim
+     the inspection itself published no detail (graded inspections
+     always have subgrades; if it was ungraded, the grade is
+     carried forward and must be labelled as such).
+   - **Interim ungraded (Sept 2024 – Nov 2025):** parsed outcome
+     ("remains Good") shown as the effective grade, marked as such.
+   - **Renewed framework report card (from Nov 2025):** no overall
+     grade exists. Render the report card as an area-by-rating list
+     using Ofsted's 5-point scale (Exceptional / Strong standard /
+     Expected standard / Attention needed / Urgent improvement) across
+     the evaluation areas we model (`rc_inclusion`,
+     `rc_curriculum_teaching`, `rc_achievement`,
+     `rc_attendance_behaviour`, `rc_personal_development`,
+     `rc_leadership_governance`, `rc_early_years`, `rc_sixth_form`)
+     plus the separate safeguarding met/not-met flag. **At-a-glance
+     summary rule:** never an unlabelled colour strip — summarise by
+     counting areas per rating, best first ("5 areas Strong standard ·
+     3 areas Expected standard"), and always name any area rated
+     Attention needed or Urgent improvement explicitly (never fold
+     problems into a count), plus "Safeguarding not met" whenever that
+     flag is false. When everything is Expected standard or better,
+     add the reassurance line "No areas need attention".
+   When a comparison mixes regimes, show a one-line comparability note
+   ("Ofsted changed how it reports in Nov 2025 — a report card and an
+   older overall grade aren't directly comparable"). Never derive a
+   fake overall grade from report-card areas.
+3. **Academics (KS2)** — one dot-strip row per headline measure (RWM
+   expected, RWM higher, reading/writing/maths expected), each with the
+   England-average tick and per-school dots; copy must say "tests and
+   teacher assessments" (writing is TA, not a test). Progress scores
+   translated to Above/Average/Below chips (CI-based) — **but note KS2
+   progress measures ended with 2022/23** (no KS1 baseline afterwards)
+   and return only when the reception-baseline cohort reaches Y6
+   (~2027/28), so progress chips apply to historical years in the
+   trends explorer, not the headline view. Sparkline per school over
+   the full published period, with an honest gap for the cancelled
+   test years (2019/20–2020/21). Disadvantaged-pupils row under an
+   "Equity" subheading, always with cohort size shown and DfE's full
+   definition (FSM6 **or** looked-after/previously looked-after).
+4. **Getting a place** — oversubscription ratio as plain sentence
+   ("184 applications for 80 places"), first-preference success %, trend
+   vs last year, admissions policy.
+5. **Who goes there** — pupils on roll (vs capacity), boys/girls, FSM %,
+   EAL %, SEN support %, faith, ages, nursery, trust. *Post-v1:* IDACI
+   decile in words (needs a coverage check of `fact_deprivation` and
+   the neighbourhood-not-school caveat, §8.7).
+6. **Attendance** — *post-v1.* The KS2 test-day absence fields are the
+   only per-school absence data we hold; they're near-zero for most
+   schools and easy to misread as general attendance. Ship only if a
+   general-absence source lands.
+7. **Explore trends** (existing feature, collapsed) — metric picker +
+   multi-year line chart + table, with an added England-average
+   reference line and a COVID-gap annotation.
+
+**Metric exposure model (three tiers).** No measure from the current
+page is lost; they surface at three levels of prominence:
+- **Tier 1 — headline strips (always visible):** RWM expected,
+  reading/writing/maths expected, RWM higher standard.
+- **Tier 2 — "More measures" expansion inside Academics:** GPS and
+  science expected % (science labelled teacher-assessed), average
+  scaled scores (reading/maths/GPS, same dot-strip grammar showing
+  the 100–120 window of the 80–120 scale, widening below 100, with
+  the England tick) — one tap/click away, same visual language.
+  *Post-v1:* gender split and absence (see §4.6).
+- **Tier 3 — Explore trends:** the full grouped catalogue (the
+  current page's ~40 metrics, including equity and school-context
+  measures, and the GCSE set for secondary phase) drives the
+  year-by-year chart and table via the grouped metric picker.
+The tier assignment is a content decision per phase (secondary:
+Attainment 8, Progress 8 banding, grade 5+ English & maths as tier 1;
+EBacc and subject entries as tier 2).
+
+Finance (per-pupil spend) is deliberately deferred: low parent value,
+risk of misreading. Revisit later.
+
+**Mobile (design target — mobile first):** the desktop grid is the
+adaptation, not the other way round. On mobile the layout goes
+*measure-first*: each row is one measure with all schools listed under
+it (colour dot + short name + value + chip), so comparison never
+requires horizontal swiping between school cards. A sticky horizontal
+school-chip bar keeps identity and add/remove available while
+scrolling. Dot strips already read measure-first and carry over
+unchanged. The trend chart scrolls horizontally inside its container.
+
+## 5. Data strategy — existing dataset only
+
+Constraint (agreed 2026-07-11): use only data already in marts plus
+fields already present in the `raw` schema extracts we pull today.
+No new external sources.
+
+**Gaps in the mockup, resolved within this constraint:**
+
+| Mockup element | Resolution |
+|---|---|
+| England average for disadvantaged pupils | Compute from our own data: `stg_ees_ks2` already pivots the Disadvantaged breakdown per school; aggregate it (weighted by eligible pupils) into `fact_ks2_national_averages` or compute in the API. Label it "England average (state schools)". |
+| England context for FSM / EAL / SEN chips | Compute dataset-wide medians per phase, same pattern as `/api/national-averages` does for KS4. |
+| "Much larger than average" size label | Dataset median pupils-on-roll per phase. |
+| Ofsted link | We don't have deep links to the latest report, so always link to the school's Ofsted provider page, `https://reports.ofsted.gov.uk/provider/21/{urn}`, derived from URN (label it "the school's Ofsted page", not "the report"). |
+
+**Raw fields we already pull but don't store — promote to marts (one
+dbt/pipeline PR, no tap changes):**
+
+- `raw.ees_admissions`: 2nd/3rd preference applications and offers,
+  total-preference counts, cross-LA applications and offers → richer
+  "Getting a place" (e.g. "offers reached 2nd-choice families",
+  competition from outside the borough).
+- `raw.ees_ks2_attainment`: progress-measure confidence intervals and
+  "working towards" % → lets the Above/Average/Below progress chips be
+  statistically honest (band by CI overlap with 0, mirroring DfE
+  methodology) instead of thresholding the point estimate.
+- `raw.ees_ks4_performance` / `ees_ks4_info`: `progress8_banding`
+  (DfE's own plain-English "well above average … well below average"
+  label — exactly the chip we want for secondary), EBacc entry/APS,
+  grade-5+ English & maths, `attainment8_diffn`/`progress8_diffn`
+  (disadvantage gaps) → the secondary-phase version of the Academics
+  section.
+- `raw.ees_census`: young-carer % and the ethnicity breakdown →
+  optional "Who goes there" enrichment; hold for a later iteration
+  (presentation needs care), but the data requires no new extract.
+- `raw.ofsted_inspections` / tap-uk-ofsted: the `rc_*` report-card
+  columns exist in staging/marts but are stubbed `null` — the tap has a
+  TODO to map the report-card column names from the Ofsted MI file
+  (same monthly extract we already download; inspections from Nov 2025
+  onward carry them). This is the one promotion that needs a small tap
+  schema addition, and it's a prerequisite for the new-framework Ofsted
+  display above.
+
+Explicitly out (not in any current extract): school-level phonics,
+workforce/teacher data, per-school attendance beyond the KS2 test-day
+absence fields, Ofsted report-card documents themselves.
+
+## 6. API changes
+
+Extend `GET /api/compare` response per URN with the same supplementary
+blocks the detail endpoint already builds (`get_supplementary_data`):
+`ofsted`, `census`, `admissions` (+ `admissions_history`), `deprivation`,
+plus a top-level `national_averages` block for the latest year. Reuse the
+existing function; no new tables. Response stays backward-compatible
+(additive fields only). Add derived helper fields server-side or compute
+chips client-side from `national_averages` (client-side preferred — no
+schema churn).
+
+## 7. Accessibility & comprehension devices
+
+- Verdict chips are text + colour + position (never colour alone).
+- Every acronym has a tooltip using existing `MetricTooltip`.
+- "How to read this" one-liner at the top of each section.
+- Chart palette: coral `#e07256`, teal `#00949b`, purple `#8664c9`
+  (validated: lightness band, chroma, CVD separation, contrast — the
+  current `--chart-2/-4` tokens fail chroma/contrast checks and should
+  be nudged to these).
+- Numbers rounded to whole percents; England tick labelled on first use.
+
+## 8. Expert-review requirements
+
+An adversarial review by an education-data expert (full findings in
+`2026-07-11-compare-screen-expert-review.md`) was applied to the
+mockups on 2026-07-12. The following are binding requirements for
+implementation, beyond what the mockups can show:
+
+1. **Chart truthfulness:** KS2 tests were cancelled 2019/20–2020/21
+   only. **2021/22 school-level figures are a permanent source gap** —
+   DfE stated it would not publish KS2 2021/22 in performance tables
+   (verified 2026-07-12 against EES, the CSP download service, and
+   DfE release notes; see `# TASK 6 VERIFICATION` in
+   `pipeline/scripts/diagnose_compare_gaps.py`). The chart's England-
+   only 2021/22 point with broken school lines is therefore the
+   correct permanent rendering; copy should say "DfE didn't publish
+   school-level figures for 2021/22", not "not in our dataset yet".
+   The 2015/16 national figure and the GPS/science/scaled-score
+   England averages ARE loadable (mapping already correct; refreshed
+   raw extract backfills them). Never render missing years as if time
+   were continuous.
+2. **Report-card summaries** count graded areas only — safeguarding is
+   a separate binary flag, never included in rating counts.
+3. **Admissions:** use the real preference-breakdown fields from
+   `raw.ees_admissions`; never derive "lower-preference offers" as
+   places − first-preference offers. Frame total applications as
+   "named on N forms" (any rank), lead with first-preference success,
+   and standardise at-a-glance chips on that one metric. Explain the
+   equal-preference rule; caveat offers vs final intake (waiting
+   lists/appeals); condition "distance decides" on the school's actual
+   oversubscription criteria where we have the admissions-policy field.
+4. **Ofsted:** overall grades ended September 2024 (report cards from
+   November 2025); the interim regime must be renderable. Distinguish
+   graded (s5) vs ungraded (s8) inspections and surface carried-forward
+   grades as such; "we don't hold the detail" is a statement about our
+   dataset, never about the inspection. Verify every scale/area label
+   against Ofsted's final published toolkit before launch (e.g. "Needs
+   attention" vs "Attention needed"; "Personal development and
+   well-being" vs the identically-named legacy judgement). Check
+   whether a school's latest inspection is merely its latest *graded*
+   one. Confirm Early years provision subgrades flow through the
+   pipeline for schools with nurseries.
+5. **Subgroup honesty:** disadvantaged-pupil percentages carry cohort
+   sizes and follow the DfE suppression rule (≤5 pupils suppressed);
+   state the rule verbatim in the footer.
+6. **Benchmark provenance:** official DfE figures and
+   dataset-computed benchmarks must be labelled distinctly and
+   consistently everywhere (a computed median is a "benchmark",
+   not an "England average").
+7. **Copy details:** "Latest Ofsted inspection" (not "Ofsted rating");
+   "EHC plans"; SEN-support benchmark ≈14%; high SEN share may
+   indicate resourced provision (say so neutrally); "at or above
+   capacity" rather than "full" (capacity data is often stale);
+   disambiguate Ofsted's "Expected standard" from the KS2 measure;
+   give official terms ("first preference") alongside plain English.
+   Writing has no "higher standard" — its TA outcome is "greater
+   depth (GDS)"; never list writing under a higher-standard group.
+   Science and writing are teacher-assessed and must be labelled as
+   such (no KS2 science test since 2009). House style for benchmark
+   provenance: official DfE figures say "England average"; computed
+   figures say "state-school average (computed from our dataset)" —
+   applied to every chip, tooltip, header note and section intro.
+   EAL uses the census wording: first language known or believed to
+   be other than English. If IDACI ships, caveat that it describes
+   pupils' home neighbourhoods, not the school.
+
+## 9. Rollout
+
+1. **PR 1 (backend):** extend `/api/compare` + tests.
+2. **PR 2 (frontend):** new compare layout behind the existing route;
+   e2e journey updated in the same PR (promotion gate).
+3. **Fix separately:** missing third series on the current chart.
+
+## 10. Open questions for review
+
+- Max schools: keep 10 in API but cap visible columns at 4 with scroll?
+- Should distance-from-home appear when the user searched by postcode
+  (data exists via `dim_location`)?
+- Keep finance out of v1? (Recommended: yes, out.)

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

@@ -68,6 +68,19 @@ COLUMN_PRIORITY = {
     "ungraded_inspection_date": [
         "Date of latest ungraded inspection",
     ],
+    # Report Card fields (post-Nov 2025 framework). Confirmed verbatim MI
+    # headers per diagnose_compare_gaps.py's Task 1(c) findings. No MI column
+    # currently exists for early-years or sixth-form report-card grades, so
+    # those two fields are deliberately omitted here (see schema below) --
+    # they stay absent from every record, same as the existing `report_url`
+    # pattern for fields with no COLUMN_PRIORITY entry.
+    "rc_safeguarding_met": ["Safeguarding standards"],
+    "rc_inclusion": ["Inclusion"],
+    "rc_curriculum_teaching": ["Curriculum and teaching"],
+    "rc_achievement": ["Achievement"],
+    "rc_attendance_behaviour": ["Attendance and behaviour"],
+    "rc_personal_development": ["Personal development and wellbeing"],
+    "rc_leadership_governance": ["Leadership and governance"],
 }
 
 
@@ -111,6 +124,17 @@ class OfstedInspectionsStream(Stream):
         th.Property("sixth_form_provision", th.StringType),
         th.Property("ungraded_outcome", th.StringType),
         th.Property("ungraded_inspection_date", th.StringType),
+        th.Property("rc_safeguarding_met", th.StringType),
+        th.Property("rc_inclusion", th.StringType),
+        th.Property("rc_curriculum_teaching", th.StringType),
+        th.Property("rc_achievement", th.StringType),
+        th.Property("rc_attendance_behaviour", th.StringType),
+        th.Property("rc_personal_development", th.StringType),
+        th.Property("rc_leadership_governance", th.StringType),
+        # No MI column exists for these yet; declared for forward
+        # compatibility with the mart schema, always emitted as absent/NULL.
+        th.Property("rc_early_years", th.StringType),
+        th.Property("rc_sixth_form", th.StringType),
         th.Property("report_url", th.StringType),
     ).to_dict()
 

pipeline/scripts/diagnose_compare_gaps.py

@@ -0,0 +1,305 @@
+"""Diagnose the three data gaps blocking the compare-screen redesign.
+
+Run from repo root (network access required, no DB needed):
+    uv run --with singer-sdk --with pandas --with requests \
+        python pipeline/scripts/diagnose_compare_gaps.py
+
+(singer_sdk is a transitive import of tap_uk_ees.tap / tap_uk_ofsted.tap and
+is not part of the repo's default environment, hence the `uv run --with`.)
+"""
+import io
+import re
+import sys
+
+import pandas as pd
+import requests
+
+sys.path.insert(0, "pipeline/plugins/extractors/tap-uk-ees")
+sys.path.insert(0, "pipeline/plugins/extractors/tap-uk-ofsted")
+from tap_uk_ees.tap import (  # noqa: E402
+    _KS2_NATIONAL_COL_MAP,
+    _KS2_NATIONAL_CSV_URL,
+    download_release_zip,
+    get_all_releases,
+)
+from tap_uk_ofsted.tap import discover_csv_url  # noqa: E402
+
+TIMEOUT = 120
+
+
+def check_national_gps_science():
+    print("\n=== (a) National catalogue CSV: GPS/science columns ===")
+    resp = requests.get(_KS2_NATIONAL_CSV_URL, timeout=TIMEOUT)
+    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 csv_col in ("pt_gps_exp", "pt_scita_exp", "avg_readscore", "avg_matscore", "avg_gpsscore"):
+        status = "PRESENT" if csv_col in df.columns else "MISSING"
+        print(f"  {csv_col}: {status}")
+    gps_like = [c for c in df.columns if "gps" in c or "scita" in c or "sci" in c]
+    print(f"  all gps/science-ish columns: {gps_like}")
+    if "geographic_level" in df.columns:
+        nat = df[df["geographic_level"].str.strip().str.lower() == "national"]
+    else:
+        print("  geographic_level column missing — cannot isolate national rows")
+        return
+    print(f"  national rows time_periods: {sorted(nat['time_period'].unique())}")
+    # Sample the values our map would read for the latest year
+    latest = nat[nat["time_period"] == nat["time_period"].max()]
+    for csv_col, field in _KS2_NATIONAL_COL_MAP.items():
+        val = latest.iloc[0].get(csv_col, "<col missing>") if len(latest) else "<no row>"
+        print(f"  {field} <- {csv_col} = {val!r}")
+
+
+def check_ks2_attainment_years_subjects():
+    print("\n=== (b) EES KS2 attainment: years & subject labels ===")
+    releases = get_all_releases("key-stage-2-attainment")
+    print(f"  releases found: {[r['time_period'] for r in releases]}")
+    for release in releases:
+        try:
+            zf = download_release_zip(release["id"])
+        except Exception as e:
+            print(f"  {release['time_period']}: DOWNLOAD FAILED: {e}")
+            continue
+        name = next((n for n in zf.namelist()
+                     if "ks2_school_attainment_data" in n and n.endswith(".csv")), None)
+        if not name:
+            print(f"  {release['time_period']}: NO school attainment CSV in ZIP")
+            print(f"    all CSVs in zip: {[n for n in zf.namelist() if n.endswith('.csv')]}")
+            continue
+        with zf.open(name) as f:
+            df = pd.read_csv(f, dtype=str, keep_default_na=False, nrows=200000)
+        years = sorted(df["time_period"].unique())
+        subjects = sorted(df["subject"].unique())
+        print(f"  release {release['time_period']}: time_periods={years}")
+        print(f"    subjects={subjects}")
+
+
+def check_ofsted_report_card_columns():
+    print("\n=== (c) Ofsted MI CSV: report-card columns ===")
+    url = discover_csv_url()
+    print(f"  MI file: {url}")
+    if url is None or not url.lower().endswith(".csv"):
+        print(f"  URL is not a CSV (likely ODS) — stopping this section. url={url!r}")
+        return
+    resp = requests.get(url, timeout=TIMEOUT)
+    resp.raise_for_status()
+    df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False, nrows=5)
+    rc_like = [c for c in df.columns
+               if re.search(r"report card|inclusion|curriculum|achievement|safeguard|well.?being|governance", c, re.I)]
+    print(f"  candidate report-card columns ({len(rc_like)}):")
+    for c in rc_like:
+        print(f"    - {c!r}")
+    print(f"  all columns ({len(df.columns)}):")
+    for c in df.columns:
+        print(f"    - {c!r}")
+
+
+if __name__ == "__main__":
+    check_national_gps_science()
+    check_ks2_attainment_years_subjects()
+    check_ofsted_report_card_columns()
+
+
+# FINDINGS 2026-07-12: run via
+#   uv run --with singer-sdk --with pandas --with requests \
+#       python pipeline/scripts/diagnose_compare_gaps.py
+#
+# (a) National catalogue CSV (GPS/science) — NOT a source-data problem.
+#     pt_gps_exp, pt_scita_exp, avg_readscore, avg_matscore, avg_gpsscore are
+#     all PRESENT in the catalogue CSV and hold real numeric values for the
+#     latest national row (time_period 202425: pt_gps_exp='72.6' ->
+#     gps_expected_pct; pt_scita_exp='81.6' -> science_expected_pct).
+#     national time_periods present: 201516, 201617, 201718, 201819, 201920,
+#     202021, 202122, 202223, 202324, 202425 (COVID years 201920/202021 are
+#     present as rows but suppressed with 'x' per the module docstring, not
+#     absent). So _KS2_NATIONAL_COL_MAP is correct and the extractor's own
+#     read of the source is fine end-to-end -- the NULLs in
+#     marts.fact_ks2_national_averages are NOT caused by a missing/renamed
+#     source column. The gap must be introduced downstream of the tap
+#     (staging/mart SQL, a stale/incomplete load, or a dbt model not
+#     selecting these two columns) -- Task 5/6 should look at the dbt
+#     staging model for ees_ks2_national and the mart definition, not the
+#     tap/column-map.
+#
+# (b) EES KS2 attainment (school-level, "key-stage-2-attainment" publication)
+#     releases found (via get_all_releases): [None, '202425', '202324',
+#     '202223', '202122']. The `None` entry is the *current/latest* release
+#     (its slug doesn't parse to a 6-digit time_period by _slug_to_time_period,
+#     but the CSV inside carries time_period='202425' -- same data as the
+#     202425-labelled release).
+#
+#     Only two of the four releases contain a school-level attainment CSV
+#     matching "ks2_school_attainment_data*.csv":
+#       - release None (latest):  HAS IT -> time_periods=['202425']
+#           subjects=['Grammar, punctuation and spelling', 'Maths', 'Reading',
+#                      'Reading, writing and maths', 'Science', 'Writing']
+#       - release 202324:         HAS IT -> time_periods=['202324']
+#           subjects= same 6 labels as above
+#       - release 202223:         NO school attainment CSV in ZIP. This
+#           release's ZIP instead contains only LA/regional/national/MAT-level
+#           files (e.g. ks2_regional_and_local_authority_*, ks2_multi_academy
+#           _trusts_*, ks2_national_*); no data/*school*attainment*.csv file
+#           exists at all in this release's package. This CONFIRMS the
+#           "subject-level 2022/23 is NULL in prod" symptom: the source
+#           release literally does not publish a school-level attainment file
+#           for 202223 under this filename pattern -- it's not a tap bug.
+#       - release 202122:         NO school attainment CSV in ZIP. Same
+#           situation: ZIP has only LA/regional/national-level files (e.g.
+#           ks2_regional_and_local_authority_2016_to_2022_revised.csv,
+#           ks2_national_school_characteristics_2016_to_2022_revised.csv);
+#           no school-level attainment CSV present. This CONFIRMS "school-level
+#           2021/22 is absent" -- again a genuine source-data absence, not an
+#           extractor bug.
+#     Implication for Tasks 5/6/7: 202122 and 202223 school-level attainment
+#     cannot be backfilled from the "key-stage-2-attainment" EES publication
+#     via this filename pattern -- those two years must either be sourced
+#     from a different EES dataset/file (e.g. one of the *_school_location_
+#     and_pupil_characteristics or *_school_type_and_pupil_characteristics
+#     files present in those ZIPs, which may carry school-level rows under a
+#     different filename), left NULL with an explicit "source unavailable"
+#     note, or backfilled from the legacy DfE "Compare School Performance"
+#     wide-format CSVs referenced elsewhere in tap.py. Subject labels to use
+#     when a source *is* found for 202324/202425:
+#       'Grammar, punctuation and spelling', 'Maths', 'Reading',
+#       'Reading, writing and maths', 'Science', 'Writing'
+#     (Reading, writing and maths spans reading+writing+maths combined --
+#     this is the RWM row.)
+#
+# (c) Ofsted MI CSV (report-card columns) — confirmed PRESENT.
+#     discover_csv_url() resolved to (as at run time, latest inspections
+#     31 May 2026):
+#       https://assets.publishing.service.gov.uk/media/6a27c45be13080622db38815/
+#       Management_information_-_state-funded_schools_-_latest_inspections_as_at_31_May_2026.csv
+#     This is a real .csv (not .ods) so section (c) ran to completion.
+#     Exact report-card column headers (7 grade columns + their paired date
+#     columns, all present verbatim, case/spacing exactly as below):
+#       'Safeguarding standards'                 / 'Safeguarding standards - date of grade'
+#       'Inclusion'                               / 'Inclusion - date of grade'
+#       'Curriculum and teaching'                 / 'Curriculum and teaching - date of grade'
+#       'Achievement'                             / 'Achievement - date of grade'
+#       'Attendance and behaviour'                / 'Attendance and behaviour - date of grade'
+#       'Personal development and wellbeing'      / 'Personal development and wellbeing - date of grade'
+#       'Leadership and governance'               / 'Leadership and governance - date of grade'
+#     Plus a related pass/fail-style field:
+#       'Latest OEIF  safeguarding is effective?' (note: double space in the
+#       header, verbatim from source -- preserve exactly when mapping)
+#     These are the new-style "report card" single-word-area grades
+#     (introduced alongside the "Attendance and behaviour" split from
+#     "Personal development"); they coexist in the same CSV with the legacy
+#     5-judgement OEIF columns ('Latest OEIF overall effectiveness',
+#     'Latest OEIF quality of education', 'Latest OEIF behaviour and
+#     attitudes', 'Latest OEIF personal development', 'Latest OEIF
+#     effectiveness of leadership and management'). Task 7 should map the 7
+#     report-card columns above (grade + date pairs, 6 of them, plus the
+#     safeguarding-effective flag) rather than inventing new column names.
+
+# TASK 6 VERIFICATION 2026-07-12: 2021/22 legacy KS2 school-level archive
+#
+# RESULT: BLOCKED at the source-data level. School-level KS2 attainment for
+# academic year 2021/22 was never published anywhere publicly by DfE -- not
+# in EES (confirmed by Task 1's finding (b) above), not in the legacy
+# "Compare School Performance" download wizard, and not as a standalone
+# performance-tables archive/ODS on assets.publishing.service.gov.uk. This
+# is a deliberate DfE decision, not a gap in our extraction logic.
+#
+# Confirming quote (Key stage 2 attainment 2021/22 release notes, via
+# https://explore-education-statistics.service.gov.uk/find-statistics/
+# key-stage-2-attainment/2021-22):
+#   "We will not publish key stage 2 data for academic year 2021/22 in
+#   performance tables (also known as Compare School and College
+#   Performance)." ... "The Department will, however, still produce the
+#   normal suite of key stage 2 accountability measures at school and
+#   multi-academy trust level and share these securely with primary
+#   schools, academy trusts and local authorities to inform school
+#   improvement discussions."
+# (i.e. school-level 202122 KS2 results exist internally at DfE but were
+# withheld from every public channel: performance tables/CSCP, EES, and by
+# extension the legacy DfE archives the current legacy_ks2_urls entries in
+# meltano.yml were sourced from.)
+#
+# What was tried:
+#  1. Direct download URL pattern from the task brief:
+#       https://www.compare-school-performance.service.gov.uk/download-data?download=true&regions=0&filters=KS2&fileformat=csv&year=2021-2022&meta=false
+#     -> HTTP 404, HTML error page (not a CSV/ZIP). Saved response inspected;
+#     confirmed 404 via response headers (`content-type: text/html`).
+#  2. Walked the actual multi-step download wizard at
+#     https://www.compare-school-performance.service.gov.uk/download-data
+#     with a browser User-Agent and a cookie jar, replicating the GET-based
+#     form steps: currentstep=year (downloadYear=2021-2022) -> currentstep=
+#     region (regiontype=all&la=0) -> currentstep=datatypes. On the final
+#     "datatypes" step, the checkbox list for 2021-2022 has NO "ks2" (or
+#     "ks2mats") option at all -- only ks4/ks4prov/ks4underlying/ks5* /
+#     pupil-destination/absence/census/mats checkboxes are present.
+#     Control check: repeating the same wizard walk for downloadYear=
+#     2018-2019, 2022-2023 and 2023-2024 shows a "ks2" (and "ks2mats")
+#     checkbox present in all three; downloadYear=2020-2021 (COVID-cancelled
+#     KS2 SATs year) also has NO ks2 checkbox, matching the pattern for a
+#     year where school-level KS2 genuinely isn't published. 2021-2022
+#     behaves identically to the cancelled 2020-2021 year, not like the
+#     normal 2018-2019/2022-2023/2023-2024 years.
+#  3. Web search for a standalone KS2 2022 performance-tables archive
+#     (e.g. "england_ks2final" for 2022) on assets.publishing.service.gov.uk
+#     found no such file; only unrelated 2022/2023-dated documents.
+#
+#  No ZIP was ever obtained -- /tmp/dfe-2021-2022-ks2.zip contains the 404
+#  HTML error page from attempt (1) above, not a real archive. It contains
+#  no england_ks2final.csv (there is no ZIP to look inside).
+#
+# Column-map check (brief's Step 1): NOT RUN -- there is no 2021/22
+# england_ks2final.csv to check headers against. This is moot until/unless
+# a non-public source (e.g. a manual/internal DfE extract) becomes
+# available; _LEGACY_KS2_COLUMN_MAP itself is unchanged and untested here.
+#
+# Recommendation: mark 202122 school-level KS2 as a genuine, permanent
+# source-data gap (not a backfill candidate) unless the project can obtain
+# the internal DfE extract DfE says it shared "securely with primary
+# schools, academy trusts and local authorities" -- that is not a route
+# available to this pipeline. Task 6's meltano.yml change (Step 2) and the
+# filebrowser upload should NOT proceed for 202122; there is nothing to
+# upload.
+
+# TASK 7 VALUE SAMPLE 2026-07-12: live value_counts() over the 7 report-card
+# columns (plus the related safeguarding-effective flag) in the same MI CSV
+# resolved by discover_csv_url() as at run time (31 May 2026 inspections
+# file). Blank cells read as the literal string 'NULL' (matches
+# keep_default_na=False in tap.py). Observed non-blank values, verbatim:
+#
+#   'Safeguarding standards':                 'Met' (1319), 'Not met' (10)
+#   'Inclusion':                               'Expected standard' (710),
+#       'Strong standard' (447), 'Needs attention' (130), 'Exceptional' (23),
+#       'Urgent improvement' (19)
+#   'Curriculum and teaching':                 'Expected standard' (797),
+#       'Needs attention' (287), 'Strong standard' (206),
+#       'Urgent improvement' (28), 'Exceptional' (11)
+#   'Achievement':                             'Expected standard' (701),
+#       'Needs attention' (364), 'Strong standard' (207),
+#       'Urgent improvement' (39), 'Exceptional' (18)
+#   'Attendance and behaviour':                'Expected standard' (699),
+#       'Strong standard' (405), 'Needs attention' (188),
+#       'Urgent improvement' (21), 'Exceptional' (16)
+#   'Personal development and wellbeing':      'Expected standard' (728),
+#       'Strong standard' (504), 'Needs attention' (66), 'Exceptional' (23),
+#       'Urgent improvement' (8)
+#   'Leadership and governance':               'Expected standard' (813),
+#       'Strong standard' (292), 'Needs attention' (172),
+#       'Urgent improvement' (34), 'Exceptional' (18)
+#   'Latest OEIF  safeguarding is effective?' (note double space, not used by
+#   Task 7 -- kept for completeness): 'Yes' (12970), 'No' (96)
+#
+# So the 6 graded report-card columns share exactly one 5-value vocabulary:
+# {'Exceptional', 'Strong standard', 'Expected standard', 'Needs attention',
+# 'Urgent improvement'} -- no 'Attention needed' variant was observed
+# anywhere, so parse_report_card_grade.sql does NOT need that speculative
+# branch from the task brief. 'Safeguarding standards' is a separate
+# two-value vocabulary {'Met', 'Not met'}.
+#
+# Collision check: 'Achievement' matches by EXACT list-membership
+# (`candidate in df_columns`, a Python list containment check against the
+# full column-name list, not a substring/regex match) against only
+# ['Achievement', 'Achievement - date of grade'] -- the date-paired column
+# has a different exact string and is never selected. Same check for
+# 'Safeguarding standards' found only itself, its own date-of-grade column,
+# and the unrelated 'Latest OEIF  safeguarding is effective?' column (not
+# mapped to any rc_* field). No legacy OEIF column is accidentally consumed
+# by an rc_ mapping.

pipeline/scripts/generate_gias_codes.py

@@ -94,13 +94,23 @@ def main() -> None:
     for code_col, name_col, dict_name, field_key in FIELDS:
         pairs = (
             df[[code_col, name_col]]
-            .loc[lambda d: (d[code_col] != "") & (d[name_col] != "")]
+            .loc[lambda d: d[code_col] != ""]
             .drop_duplicates()
         )
-        mapping = sorted((int(c), n) for c, n in pairs.itertuples(index=False))
+        by_code: dict[int, set] = {}
-        dupes = len(mapping) - len({c for c, _ in mapping})
+        for c, n in pairs.itertuples(index=False):
-        if dupes:
+            by_code.setdefault(int(c), set()).add(n)
-            sys.exit(f"{code_col}: {dupes} codes map to multiple names — investigate before generating")
+        mapping = []
+        for code, names in sorted(by_code.items()):
+            named = sorted(n for n in names if n != "")
+            if len(named) > 1:
+                sys.exit(f"{code_col}: code {code} maps to multiple names {named} — investigate before generating")
+            # Codes that only ever appear with a blank (name) are GIAS
+            # "not recorded" sentinels (e.g. ReligiousCharacter 99,
+            # AdmissionsPolicy 9). Map them to "" so the API serves the same
+            # empty string the old name pipeline did — the "Unknown (<code>)"
+            # path is reserved for genuinely new codes.
+            mapping.append((code, named[0] if named else ""))
         lines = [f"{dict_name}: dict[int, str] = {{"]
         for code, name in mapping:
             escaped = name.replace('"', '\\"')

pipeline/scripts/gias_codes.py

@@ -78,6 +78,7 @@ OFFICIAL_SIXTH_FORM: dict[int, str] = {
     0: "Not applicable",
     1: "Has a sixth form",
     2: "Does not have a sixth form",
+    9: "",
 }
 
 RELIGIOUS_CHARACTER: dict[int, str] = {
@@ -128,12 +129,14 @@ RELIGIOUS_CHARACTER: dict[int, str] = {
     47: "Reformed Baptist",
     48: "Roman Catholic/Anglican",
     49: "Sunni Deobandi",
+    99: "",
 }
 
 ADMISSIONS_POLICY: dict[int, str] = {
     0: "Not applicable",
     2: "Selective",
     4: "Non-selective",
+    9: "",
 }
 
 

pipeline/transform/macros/parse_report_card_grade.sql

@@ -0,0 +1,17 @@
+-- Macro: Parse Ofsted Report Card grade (post-Nov 2025 framework) from text
+-- into the 5-point scale. Real values confirmed via a live sample of the MI
+-- CSV (see pipeline/scripts/diagnose_compare_gaps.py's
+-- "TASK 7 VALUE SAMPLE 2026-07-12" note) -- unrecognised text (including the
+-- 'NULL' sentinel used by the source CSV for blanks) parses to NULL, never
+-- errors.
+
+{% macro parse_report_card_grade(column_name) %}
+    case lower(trim(nullif({{ column_name }}, 'NULL')))
+        when 'exceptional' then 1
+        when 'strong standard' then 2
+        when 'expected standard' then 3
+        when 'needs attention' then 4
+        when 'urgent improvement' then 5
+        else null
+    end
+{% endmacro %}

pipeline/transform/models/intermediate/int_ks2_with_lineage.sql

@@ -15,8 +15,11 @@ current_ks2 as (
         year, total_pupils, eligible_pupils,
         rwm_expected_pct, rwm_high_pct,
         reading_expected_pct, reading_high_pct, reading_avg_score, reading_progress,
+        reading_progress_lower_ci, reading_progress_upper_ci,
         writing_expected_pct, writing_high_pct, writing_progress,
+        writing_progress_lower_ci, writing_progress_upper_ci, writing_working_towards_pct,
         maths_expected_pct, maths_high_pct, maths_avg_score, maths_progress,
+        maths_progress_lower_ci, maths_progress_upper_ci,
         gps_expected_pct, gps_high_pct, gps_avg_score, science_expected_pct,
         reading_absence_pct, writing_absence_pct, maths_absence_pct, gps_absence_pct, science_absence_pct,
         rwm_expected_boys_pct, rwm_high_boys_pct, rwm_expected_girls_pct, rwm_high_girls_pct,
@@ -33,8 +36,11 @@ predecessor_ks2 as (
         ks2.year, ks2.total_pupils, ks2.eligible_pupils,
         ks2.rwm_expected_pct, ks2.rwm_high_pct,
         ks2.reading_expected_pct, ks2.reading_high_pct, ks2.reading_avg_score, ks2.reading_progress,
+        ks2.reading_progress_lower_ci, ks2.reading_progress_upper_ci,
         ks2.writing_expected_pct, ks2.writing_high_pct, ks2.writing_progress,
+        ks2.writing_progress_lower_ci, ks2.writing_progress_upper_ci, ks2.writing_working_towards_pct,
         ks2.maths_expected_pct, ks2.maths_high_pct, ks2.maths_avg_score, ks2.maths_progress,
+        ks2.maths_progress_lower_ci, ks2.maths_progress_upper_ci,
         ks2.gps_expected_pct, ks2.gps_high_pct, ks2.gps_avg_score, ks2.science_expected_pct,
         ks2.reading_absence_pct, ks2.writing_absence_pct, ks2.maths_absence_pct, ks2.gps_absence_pct, ks2.science_absence_pct,
         ks2.rwm_expected_boys_pct, ks2.rwm_high_boys_pct, ks2.rwm_expected_girls_pct, ks2.rwm_high_girls_pct,

pipeline/transform/models/intermediate/int_ks4_with_lineage.sql

@@ -18,7 +18,8 @@ current_ks4 as (
         english_maths_strong_pass_pct, english_maths_standard_pass_pct,
         ebacc_entry_pct, ebacc_strong_pass_pct, ebacc_standard_pass_pct, ebacc_avg_score,
         gcse_grade_91_pct,
-        sen_pct, sen_support_pct, sen_ehcp_pct
+        sen_pct, sen_support_pct, sen_ehcp_pct,
+        progress_8_banding, attainment_8_disadvantage_gap, progress_8_disadvantage_gap
     from all_ks4
 ),
 
@@ -34,7 +35,8 @@ predecessor_ks4 as (
         ks4.english_maths_strong_pass_pct, ks4.english_maths_standard_pass_pct,
         ks4.ebacc_entry_pct, ks4.ebacc_strong_pass_pct, ks4.ebacc_standard_pass_pct, ks4.ebacc_avg_score,
         ks4.gcse_grade_91_pct,
-        ks4.sen_pct, ks4.sen_support_pct, ks4.sen_ehcp_pct
+        ks4.sen_pct, ks4.sen_support_pct, ks4.sen_ehcp_pct,
+        ks4.progress_8_banding, ks4.attainment_8_disadvantage_gap, ks4.progress_8_disadvantage_gap
     from all_ks4 ks4
     inner join {{ ref('int_school_lineage') }} lin
         on ks4.urn = lin.predecessor_urn

pipeline/transform/models/marts/_marts_schema.yml

@@ -42,12 +42,12 @@ models:
         tests:
           - accepted_values:
               severity: warn
-              values: [0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49]
+              values: [0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 26, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 99]
       - name: admissions_policy_code
         tests:
           - accepted_values:
               severity: warn
-              values: [0, 2, 4]
+              values: [0, 2, 4, 9]
 
   - name: dim_location
     description: School location dimension with PostGIS geometry
@@ -86,6 +86,13 @@ models:
         tests: [not_null]
       - name: year
         tests: [not_null]
+      - name: reading_progress_lower_ci
+      - name: reading_progress_upper_ci
+      - name: writing_progress_lower_ci
+      - name: writing_progress_upper_ci
+      - name: writing_working_towards_pct
+      - name: maths_progress_lower_ci
+      - name: maths_progress_upper_ci
     tests:
       - unique:
           column_name: "urn || '-' || year"
@@ -97,6 +104,15 @@ models:
         tests: [not_null]
       - name: year
         tests: [not_null]
+      - name: progress_8_banding
+        tests:
+          - accepted_values:
+              values: ['Well above average', 'Above average', 'Average', 'Below average', 'Well below average']
+              config:
+                where: "progress_8_banding is not null"
+                severity: warn
+      - name: attainment_8_disadvantage_gap
+      - name: progress_8_disadvantage_gap
     tests:
       - unique:
           column_name: "urn || '-' || year"
@@ -124,6 +140,11 @@ models:
         tests: [not_null]
       - name: year
         tests: [not_null]
+      - name: second_preference_offers
+      - name: third_preference_offers
+      - name: cross_la_applications
+      - name: cross_la_offers
+      - name: total_offers
 
   - name: fact_finance
     description: School financial data — one row per URN per year

pipeline/transform/models/marts/fact_admissions.sql

@@ -5,9 +5,14 @@ select
     year,
     school_phase,
     places_offered,
+    total_offers,
     total_applications,
     first_preference_applications,
     first_preference_offers,
+    second_preference_offers,
+    third_preference_offers,
+    cross_la_applications,
+    cross_la_offers,
     first_preference_offer_pct,
     oversubscription_ratio,
     oversubscribed,

pipeline/transform/models/marts/fact_ks2_performance.sql

@@ -15,13 +15,20 @@ select
     reading_high_pct,
     reading_avg_score,
     reading_progress,
+    reading_progress_lower_ci,
+    reading_progress_upper_ci,
     writing_expected_pct,
     writing_high_pct,
     writing_progress,
+    writing_progress_lower_ci,
+    writing_progress_upper_ci,
+    writing_working_towards_pct,
     maths_expected_pct,
     maths_high_pct,
     maths_avg_score,
     maths_progress,
+    maths_progress_lower_ci,
+    maths_progress_upper_ci,
     gps_expected_pct,
     gps_high_pct,
     gps_avg_score,

pipeline/transform/models/marts/fact_ks4_performance.sql

@@ -16,6 +16,9 @@ select
     progress_8_score,
     progress_8_lower_ci,
     progress_8_upper_ci,
+    progress_8_banding,
+    attainment_8_disadvantage_gap,
+    progress_8_disadvantage_gap,
     progress_8_english,
     progress_8_maths,
     progress_8_ebacc,

pipeline/transform/models/staging/stg_ees_admissions.sql

@@ -32,6 +32,11 @@ renamed as (
         {{ safe_numeric('times_put_as_any_preferred_school') }}::integer as total_applications,
         {{ safe_numeric('times_put_as_1st_preference') }}::integer as first_preference_applications,
 
+        -- Cross-borough demand: applications naming this school from families
+        -- living in another local authority, and offers made to them.
+        {{ safe_numeric('"all_applications_from_another_LA"') }}::integer as cross_la_applications,
+        {{ safe_numeric('"offers_to_applicants_from_another_LA"') }}::integer as cross_la_offers,
+
         -- Proportions
         -- first_preference_offer_pct: of families who listed this school FIRST,
         -- the percentage that received an offer. 0–100 scale.

pipeline/transform/models/staging/stg_ees_ks2.sql

@@ -39,6 +39,12 @@ pivoted as (
         max(case when subject = 'Reading'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('progress_measure_score') }} end) as reading_progress,
+        max(case when subject = 'Reading'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as reading_progress_lower_ci,
+        max(case when subject = 'Reading'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as reading_progress_upper_ci,
         max(case when subject = 'Reading'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('absent_or_not_able_to_access_percent') }} end) as reading_absence_pct,
@@ -53,6 +59,15 @@ pivoted as (
         max(case when subject = 'Writing'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('progress_measure_score') }} end) as writing_progress,
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as writing_progress_lower_ci,
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as writing_progress_upper_ci,
+        max(case when subject = 'Writing'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('working_towards_expected_standard_pupil_percent') }} end) as writing_working_towards_pct,
         max(case when subject = 'Writing'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('absent_or_not_able_to_access_percent') }} end) as writing_absence_pct,
@@ -70,6 +85,12 @@ pivoted as (
         max(case when subject = 'Maths'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('progress_measure_score') }} end) as maths_progress,
+        max(case when subject = 'Maths'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_lower_conf_interval') }} end) as maths_progress_lower_ci,
+        max(case when subject = 'Maths'
+                  and breakdown_topic = 'All pupils' and breakdown = 'Total'
+            then {{ safe_numeric('progress_measure_upper_conf_interval') }} end) as maths_progress_upper_ci,
         max(case when subject = 'Maths'
                   and breakdown_topic = 'All pupils' and breakdown = 'Total'
             then {{ safe_numeric('absent_or_not_able_to_access_percent') }} end) as maths_absence_pct,
@@ -143,13 +164,20 @@ select
     p.reading_high_pct,
     p.reading_avg_score,
     p.reading_progress,
+    p.reading_progress_lower_ci,
+    p.reading_progress_upper_ci,
     p.writing_expected_pct,
     p.writing_high_pct,
     p.writing_progress,
+    p.writing_progress_lower_ci,
+    p.writing_progress_upper_ci,
+    p.writing_working_towards_pct,
     p.maths_expected_pct,
     p.maths_high_pct,
     p.maths_avg_score,
     p.maths_progress,
+    p.maths_progress_lower_ci,
+    p.maths_progress_upper_ci,
     p.gps_expected_pct,
     p.gps_high_pct,
     p.gps_avg_score,

pipeline/transform/models/staging/stg_ees_ks2_national.sql

@@ -31,4 +31,10 @@ select
 
 from {{ source('raw', 'ees_ks2_national') }}
 where time_period ~ '^[0-9]+$'
-  and cast(trim(time_period) as integer) >= 201617
+  -- 2015/16 was the first year of the current expected-standard tests, so it's
+  -- the correct floor (not 2016/17 -- that excluded a real, comparable national
+  -- row). GPS/science/scaled-score columns are already mapped correctly end to
+  -- end (tap.py's _KS2_NATIONAL_COL_MAP + this model select them fine); the
+  -- prod NULLs for those fields are stale raw.ees_ks2_national data from before
+  -- the map covered them, not a mapping bug -- no map change accompanies this fix.
+  and cast(trim(time_period) as integer) >= 201516

pipeline/transform/models/staging/stg_ees_ks4.sql

@@ -62,7 +62,16 @@ info as (
         {{ safe_numeric('ks2_scaledscore_average') }} as prior_attainment_avg,
         {{ safe_numeric('sen_pupil_percent') }} as sen_pct,
         {{ safe_numeric('sen_with_ehcp_pupil_percent') }} as sen_ehcp_pct,
-        {{ safe_numeric('sen_no_ehcp_pupil_percent') }} as sen_support_pct
+        {{ safe_numeric('sen_no_ehcp_pupil_percent') }} as sen_support_pct,
+        -- EES suppression sentinels (z/c/x/q/u) and blanks must not reach the
+        -- mart as banding labels
+        case
+            when lower(trim(progress8_banding)) in ('', 'z', 'c', 'x', 'q', 'u', 'null')
+                then null
+            else trim(progress8_banding)
+        end as progress_8_banding,
+        {{ safe_numeric('attainment8_diffn') }} as attainment_8_disadvantage_gap,
+        {{ safe_numeric('progress8_diffn') }} as progress_8_disadvantage_gap
     from {{ source('raw', 'ees_ks4_info') }}
     where school_urn is not null
 )
@@ -102,7 +111,10 @@ select
     -- Context
     i.sen_pct,
     i.sen_ehcp_pct,
-    i.sen_support_pct
+    i.sen_support_pct,
+    i.progress_8_banding,
+    i.attainment_8_disadvantage_gap,
+    i.progress_8_disadvantage_gap
 
 from all_pupils p
 left join info i on p.urn = i.urn and p.year = i.year

pipeline/transform/models/staging/stg_legacy_ks2.sql

@@ -17,13 +17,23 @@ select
     {{ safe_numeric('reading_high_pct') }} as reading_high_pct,
     {{ safe_numeric('reading_avg_score') }} as reading_avg_score,
     {{ safe_numeric('reading_progress') }} as reading_progress,
+    -- Progress CIs / working-towards: not published in the legacy CSVs.
+    -- Typed placeholders keep positional alignment with stg_ees_ks2 in
+    -- int_ks2_with_lineage's UNION ALL.
+    null::numeric as reading_progress_lower_ci,
+    null::numeric as reading_progress_upper_ci,
     {{ safe_numeric('writing_expected_pct') }} as writing_expected_pct,
     {{ safe_numeric('writing_high_pct') }} as writing_high_pct,
     {{ safe_numeric('writing_progress') }} as writing_progress,
+    null::numeric as writing_progress_lower_ci,
+    null::numeric as writing_progress_upper_ci,
+    null::numeric as writing_working_towards_pct,
     {{ safe_numeric('maths_expected_pct') }} as maths_expected_pct,
     {{ safe_numeric('maths_high_pct') }} as maths_high_pct,
     {{ safe_numeric('maths_avg_score') }} as maths_avg_score,
     {{ safe_numeric('maths_progress') }} as maths_progress,
+    null::numeric as maths_progress_lower_ci,
+    null::numeric as maths_progress_upper_ci,
     {{ safe_numeric('gps_expected_pct') }} as gps_expected_pct,
     {{ safe_numeric('gps_high_pct') }} as gps_high_pct,
     {{ safe_numeric('gps_avg_score') }} as gps_avg_score,

pipeline/transform/models/staging/stg_legacy_ks4.sql

@@ -41,8 +41,13 @@ select
 
     -- SEN
     null::numeric                           as sen_pct,
+    {{ safe_numeric('sen_ehcp_pct') }}      as sen_ehcp_pct,
     {{ safe_numeric('sen_support_pct') }}   as sen_support_pct,
-    {{ safe_numeric('sen_ehcp_pct') }}      as sen_ehcp_pct
+
+    -- Progress 8 banding & disadvantage gaps (not published in legacy format)
+    null::text    as progress_8_banding,
+    null::numeric as attainment_8_disadvantage_gap,
+    null::numeric as progress_8_disadvantage_gap
 
 from {{ source('raw', 'legacy_ks4') }}
 where urn is not null

pipeline/transform/models/staging/stg_ofsted_inspections.sql

@@ -33,17 +33,23 @@ renamed as (
         nullif(trim(ungraded_outcome), 'NULL')                                  as ungraded_outcome,
         {{ parse_ungraded_outcome('ungraded_outcome') }}::integer               as ungraded_grade,
 
-        -- Report Card fields (post-Nov 2025 framework)
+        -- Report Card fields (post-Nov 2025 framework), 5-point scale:
-        -- TODO: add rc_* columns to tap-uk-ofsted schema once CSV column names are confirmed
+        -- 1 Exceptional · 2 Strong standard · 3 Expected standard
-        null::text as rc_safeguarding_met,
+        -- · 4 Needs attention · 5 Urgent improvement
-        null::text as rc_inclusion,
+        case lower(trim(nullif(rc_safeguarding_met, 'NULL')))
-        null::text as rc_curriculum_teaching,
+            when 'met' then true
-        null::text as rc_achievement,
+            when 'not met' then false
-        null::text as rc_attendance_behaviour,
+        end                                                               as rc_safeguarding_met,
-        null::text as rc_personal_development,
+        {{ parse_report_card_grade('rc_inclusion') }}::integer            as rc_inclusion,
-        null::text as rc_leadership_governance,
+        {{ parse_report_card_grade('rc_curriculum_teaching') }}::integer  as rc_curriculum_teaching,
-        null::text as rc_early_years,
+        {{ parse_report_card_grade('rc_achievement') }}::integer          as rc_achievement,
-        null::text as rc_sixth_form,
+        {{ parse_report_card_grade('rc_attendance_behaviour') }}::integer as rc_attendance_behaviour,
+        {{ parse_report_card_grade('rc_personal_development') }}::integer as rc_personal_development,
+        {{ parse_report_card_grade('rc_leadership_governance') }}::integer as rc_leadership_governance,
+        -- No MI column exists for these yet (see tap.py); the tap never
+        -- emits rc_early_years/rc_sixth_form, so these stay NULL.
+        null::integer as rc_early_years,
+        null::integer as rc_sixth_form,
 
         report_url
     from source

pipeline/transform/seeds/gias_code_names.csv

@@ -53,6 +53,7 @@ phase_of_education,7,All-through
 official_sixth_form,0,Not applicable
 official_sixth_form,1,Has a sixth form
 official_sixth_form,2,Does not have a sixth form
+official_sixth_form,9,
 religious_character,0,Does not apply
 religious_character,2,Church of England
 religious_character,3,Roman Catholic
@@ -100,6 +101,8 @@ religious_character,46,Protestant/Evangelical
 religious_character,47,Reformed Baptist
 religious_character,48,Roman Catholic/Anglican
 religious_character,49,Sunni Deobandi
+religious_character,99,
 admissions_policy,0,Not applicable
 admissions_policy,2,Selective
 admissions_policy,4,Non-selective
+admissions_policy,9,