feat(api): compare school_info carries GIAS facts for the community section

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB
feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks
2026-07-13 23:46:12 +01:00 · 2026-07-13 18:48:28 +01:00 · 2026-07-13 18:45:30 +01:00 · 2026-07-13 18:44:29 +01:00 · 2026-07-13 18:42:08 +01:00 · 2026-07-13 18:40:55 +01:00
40 changed files with 3099 additions and 155 deletions
@@ -1,4 +1,4 @@
-name: Deploy (staging -> E2E gate -> production)
+name: Stage (build -> staging -> E2E gate)

 on:
  push:
@@ -193,48 +193,5 @@ jobs:
        env:
          BASE_URL: ${{ secrets.STAGING_BASE_URL }}

-  promote-prod:
-    name: Promote to Production
-    runs-on: ubuntu-latest
-    needs: [e2e-staging]
-    steps:
-      - name: Set up Docker Buildx
-        uses: docker/setup-buildx-action@v3
-
-      - name: Log in to Gitea Container Registry
-        uses: docker/login-action@v3
-        with:
-          registry: ${{ env.REGISTRY }}
-          username: ${{ gitea.actor }}
-          password: ${{ secrets.REGISTRY_TOKEN }}
-
-      - name: Retag verified images as prod
-        run: |
-          SHORT_SHA="sha-$(echo "${{ gitea.sha }}" | cut -c1-7)"
-          for IMAGE in \
-            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
-            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
-            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
-            # Keep a rollback pointer before moving :prod
-            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
-            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
-            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
-          done
-
-      - name: Trigger production stack update
-        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
-
-      - name: Wait for production to become healthy
-        run: |
-          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
-          for i in $(seq 1 60); do
-            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
-              echo "Production is up (attempt $i)"
-              exit 0
-            fi
-            sleep 5
-          done
-          echo "Production did not become healthy in time" >&2
-          exit 1
-        env:
-          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}
+# Production deployment is a second, manual approval: see promote.yml
+# ("Promote to Production (manual)") and docs/DEPLOY.md.
@@ -0,0 +1,126 @@
+name: Promote to Production (manual)
+
+# Second approval gate of the deploy model: run this workflow from the
+# Actions UI after testing the feature on staging. It refuses commits
+# whose staging E2E gate is not green. See docs/DEPLOY.md.
+
+on:
+  workflow_dispatch:
+    inputs:
+      sha:
+        description: >-
+          Commit SHA on main to promote (full or >=7 chars).
+          Leave empty to promote the latest main commit.
+        required: false
+        default: ""
+
+# Only one promotion at a time; never cancel an in-flight promotion.
+concurrency:
+  group: prod-promotion
+  cancel-in-progress: false
+
+env:
+  REGISTRY: privaterepo.sitaru.org
+  BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
+  FRONTEND_IMAGE_NAME: ${{ gitea.repository }}-frontend
+  PIPELINE_IMAGE_NAME: ${{ gitea.repository }}-pipeline
+
+jobs:
+  promote-prod:
+    name: Promote approved commit to Production
+    runs-on: ubuntu-latest
+    steps:
+      - name: Checkout repository (full history for ancestry check)
+        uses: actions/checkout@v4
+        with:
+          fetch-depth: 0
+
+      - name: Resolve and validate target SHA
+        id: resolve
+        # SECURITY: the dispatch input is untrusted — it reaches the shell
+        # only via env (never spliced into `run:` with ${{ }}) and is only
+        # used as a quoted argument. The resolved value is validated as a
+        # 40-hex sha and required to be an ancestor of main before any
+        # later step interpolates it.
+        env:
+          SHA_INPUT: ${{ gitea.event.inputs.sha }}
+        run: |
+          set -euo pipefail
+          case "$SHA_INPUT" in
+            -*) echo "REFUSED: SHA input may not start with '-'." >&2; exit 1 ;;
+          esac
+          if [ -z "$SHA_INPUT" ]; then
+            SHA_INPUT="$(git rev-parse origin/main)"
+          fi
+          FULL_SHA=$(git rev-parse --verify --quiet "${SHA_INPUT}^{commit}") || {
+            echo "REFUSED: not a commit in this repository." >&2
+            exit 1
+          }
+          echo "$FULL_SHA" | grep -Eq '^[0-9a-f]{40}$'
+          if ! git merge-base --is-ancestor "$FULL_SHA" origin/main; then
+            echo "REFUSED: $FULL_SHA is not on main — only main commits are promotable." >&2
+            exit 1
+          fi
+          SHORT_SHA="sha-$(echo "$FULL_SHA" | cut -c1-7)"
+          echo "full=$FULL_SHA" >> "$GITHUB_OUTPUT"
+          echo "short=$SHORT_SHA" >> "$GITHUB_OUTPUT"
+          echo "Promoting $FULL_SHA (images tagged $SHORT_SHA)"
+
+      - name: Verify the staging E2E gate passed for this commit
+        run: |
+          STATUS_JSON=$(curl -fsS \
+            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
+            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/commits/${{ steps.resolve.outputs.full }}/status")
+          echo "$STATUS_JSON" | python3 -c "
+          import json, sys
+          d = json.load(sys.stdin)
+          ok = [s for s in d.get('statuses', [])
+                if 'E2E Journeys against Staging' in s.get('context', '')
+                and s.get('status') == 'success']
+          if not ok:
+              print('REFUSED: no successful \"E2E Journeys against Staging\" status on this commit.')
+              print('Contexts found:', [s.get('context') for s in d.get('statuses', [])])
+              sys.exit(1)
+          print('E2E gate verified green for this commit.')
+          "
+
+      - name: Set up Docker Buildx
+        uses: docker/setup-buildx-action@v3
+
+      - name: Log in to Gitea Container Registry
+        uses: docker/login-action@v3
+        with:
+          registry: ${{ env.REGISTRY }}
+          username: ${{ gitea.actor }}
+          password: ${{ secrets.REGISTRY_TOKEN }}
+
+      - name: Retag approved images as prod (keeping rollback pointer)
+        run: |
+          SHORT_SHA="${{ steps.resolve.outputs.short }}"
+          for IMAGE in \
+            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
+            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
+            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
+            # Keep a rollback pointer before moving :prod
+            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
+            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
+            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
+          done
+
+      - name: Trigger production stack update
+        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
+
+      - name: Wait for production to become healthy
+        run: |
+          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
+          for i in $(seq 1 60); do
+            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
+              echo "Production is up (attempt $i)"
+              exit 0
+            fi
+            sleep 5
+          done
+          echo "Production did not become healthy in time" >&2
+          exit 1
+        env:
+          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}
@@ -25,6 +25,7 @@ import asyncio
 from .config import settings
 from .data_loader import (
    clear_cache,
+    compute_benchmarks,
    load_school_data,
    load_latest_school_data,
    geocode_single_postcode,
@@ -662,6 +663,34 @@ async def compare_schools(
    if comparison_data.empty:
        raise HTTPException(status_code=404, detail="No schools found")

+    # One session for all schools' supplementary blocks; failures degrade
+    # to empty blocks rather than failing a working comparison (mirrors
+    # the detail endpoint's defensive pattern).
+    from . import database
+
+    _EMPTY_SUPPLEMENTARY = {
+        "ofsted": None,
+        "census": None,
+        "admissions": None,
+        "admissions_history": [],
+        "deprivation": None,
+    }
+    supplementary_by_urn: dict = {}
+    db = None
+    try:
+        db = database.SessionLocal()
+        for urn in urn_list:
+            supp = get_supplementary_data(db, urn)
+            supplementary_by_urn[urn] = {
+                key: supp.get(key, default)
+                for key, default in _EMPTY_SUPPLEMENTARY.items()
+            }
+    except Exception:
+        supplementary_by_urn = {}
+    finally:
+        if db is not None:
+            db.close()
+
    result = {}
    for urn in urn_list:
        school_data = comparison_data[comparison_data["urn"] == urn].sort_values("year")
@@ -677,11 +706,27 @@ async def compare_schools(
                    "phase": latest.get("phase", ""),
                    "attainment_8_score": float(latest["attainment_8_score"]) if pd.notna(latest.get("attainment_8_score")) else None,
                    "rwm_expected_pct": float(latest["rwm_expected_pct"]) if pd.notna(latest.get("rwm_expected_pct")) else None,
+                    # GIAS facts the compare "Who goes there" section needs
+                    # (same fields the detail endpoint exposes)
+                    "religious_denomination": convert_to_native(latest.get("religious_denomination")),
+                    "age_range": convert_to_native(latest.get("age_range")),
+                    "gender": convert_to_native(latest.get("gender")),
+                    "has_sixth_form": convert_to_native(latest.get("has_sixth_form")),
+                    "capacity": convert_to_native(latest.get("capacity")),
+                    "gias_total_pupils": convert_to_native(latest.get("gias_total_pupils")),
+                    "trust_name": convert_to_native(latest.get("trust_name")),
                },
                "yearly_data": clean_for_json(school_data),
+                **supplementary_by_urn.get(urn, dict(_EMPTY_SUPPLEMENTARY)),
            }

-    return {"comparison": result}
+    return {
+        "comparison": result,
+        # Official DfE anchors + computed state-school benchmarks so the
+        # compare UI can label provenance correctly (spec §8.6).
+        "national_averages": _national_averages_payload(df),
+        "benchmarks": compute_benchmarks(df),
+    }


@app.get("/api/filters")
@@ -727,22 +772,17 @@ async def get_la_averages(request: Request):
    return {"year": latest_year, "secondary": {"attainment_8_by_la": la_avg}}


-@app.get("/api/national-averages")
-@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
-async def get_national_averages(request: Request):
-    """
-    Compute national average for each metric from the latest data year.
-    Returns separate averages for primary (KS2) and secondary (KS4) schools.
-    Values are derived from the loaded DataFrame so they automatically
-    stay current when new data is loaded.
-    """
-    df = load_school_data()
+def _national_averages_payload(df: pd.DataFrame) -> dict:
+    """National-averages payload shared by /api/national-averages and
+    /api/compare. Official DfE KS2 figures come from the mart table;
+    KS4 figures are computed from our dataset (no DfE dataset yet)."""
    if df.empty:
        return {"primary": {}, "secondary": {}}

    ks2_metrics = [
        "rwm_expected_pct", "rwm_high_pct",
        "reading_expected_pct", "writing_expected_pct", "maths_expected_pct",
+        "gps_expected_pct", "gps_high_pct", "science_expected_pct",
        "reading_avg_score", "maths_avg_score", "gps_avg_score",
        "reading_progress", "writing_progress", "maths_progress",
        "overall_absence_pct", "persistent_absence_pct",
@@ -777,12 +817,13 @@ async def get_national_averages(request: Request):

    # Per-year KS2 primary averages: use official DfE figures from the mart table.
    # Per-year KS4 secondary averages: computed from our dataset (no DfE dataset yet).
-    from .database import SessionLocal
+    from . import database
    from .models import Ks2NationalAverage

    by_year = []
+    db = None
    try:
-        db = SessionLocal()
+        db = database.SessionLocal()
        nat_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
        # Build a lookup of computed secondary averages per year as fallback
        secondary_by_year = {}
@@ -810,6 +851,7 @@ async def get_national_averages(request: Request):
                "secondary": secondary_by_year.get(yr, {}),
            })
    finally:
+        if db is not None:
            db.close()

    # Update latest_primary with official DfE figure for the latest year if available
@@ -826,6 +868,17 @@ async def get_national_averages(request: Request):
    }


+@app.get("/api/national-averages")
+@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
+async def get_national_averages(request: Request):
+    """
+    National averages: official DfE KS2 figures per year plus computed
+    KS4 averages, derived from the loaded DataFrame and the
+    fact_ks2_national_averages mart.
+    """
+    return _national_averages_payload(load_school_data())
+
+
@app.get("/api/metrics")
@limiter.limit(f"{settings.rate_limit_per_minute}/minute")
 async def get_available_metrics(request: Request):
@@ -21,6 +21,7 @@ from .models import (
    FactOfstedInspection, FactAdmissions,
    FactDeprivation, FactFinance, FactPupilCharacteristics,
 )
+from .ofsted_codes import ofsted_page_url, report_card_labels
 from .schemas import SCHOOL_TYPE_MAP
 from .gias_codes import (
    ADMISSIONS_POLICY,
@@ -190,13 +191,20 @@ _MAIN_QUERY = text("""
        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,
@@ -225,6 +233,9 @@ _MAIN_QUERY = text("""
        p.progress_8_maths,
        p.progress_8_ebacc,
        p.progress_8_open,
+        p.progress_8_banding,
+        p.attainment_8_disadvantage_gap,
+        p.progress_8_disadvantage_gap,
        p.english_maths_strong_pass_pct,
        p.english_maths_standard_pass_pct,
        p.ebacc_entry_pct,
@@ -514,6 +525,143 @@ def get_data_info(db: Session = None) -> dict:
 # SUPPLEMENTARY DATA — per-school detail page
 # =============================================================================

+def compute_benchmarks(df: pd.DataFrame) -> dict:
+    """State-school benchmarks computed from our dataset (spec §5/§8.6).
+
+    NOT official DfE figures — consumers must label them
+    "state-school average (computed from our dataset)". The disadvantaged
+    attainment average is weighted by cohort size (eligible_pupils) so
+    small schools don't dominate; context measures are medians.
+    """
+    if df.empty or "year" not in df.columns:
+        return {}
+    latest_year = df["year"].max()
+    if pd.isna(latest_year):
+        return {}
+    d = df[df["year"] == latest_year]
+    if d.empty:
+        return {}
+    is_secondary = (
+        d["attainment_8_score"].notna()
+        if "attainment_8_score" in d.columns
+        else pd.Series(False, index=d.index)
+    )
+    prim, sec = d[~is_secondary], d[is_secondary]
+
+    def _median(sub, col):
+        if col not in sub.columns:
+            return None
+        v = sub[col].median()
+        return round(float(v), 1) if pd.notna(v) else None
+
+    def _weighted_disadvantaged(sub):
+        needed = {"rwm_expected_disadvantaged_pct", "eligible_pupils"}
+        if not needed <= set(sub.columns):
+            return None
+        s = sub.dropna(subset=list(needed))
+        if s.empty or s["eligible_pupils"].sum() == 0:
+            return None
+        w = (
+            (s["rwm_expected_disadvantaged_pct"] * s["eligible_pupils"]).sum()
+            / s["eligible_pupils"].sum()
+        )
+        return round(float(w), 1)
+
+    def _block(sub, with_disadvantaged):
+        median_pupils = None
+        if "total_pupils" in sub.columns:
+            mp = sub["total_pupils"].median()
+            if pd.notna(mp):
+                median_pupils = int(mp)
+        block = {
+            "eal_pct": _median(sub, "eal_pct"),
+            "sen_support_pct": _median(sub, "sen_support_pct"),
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
+            "median_pupils": median_pupils,
+        }
+        if with_disadvantaged:
+            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
+        return block
+
+    return {
+        "source": "state-school average (computed from our dataset)",
+        "year": int(latest_year),
+        "primary": _block(prim, with_disadvantaged=True),
+        "secondary": _block(sec, with_disadvantaged=False),
+    }
+
+
+def _ofsted_block(o, urn: int) -> dict:
+    """Serialize the latest Ofsted inspection row for API responses.
+
+    `grade_source` records where the effective overall grade came from:
+    a graded (Section 5) inspection, or carried forward from an ungraded
+    (Section 8) outcome — materially different claims a UI must be able
+    to distinguish. `report_card` holds coded+labelled renewed-framework
+    (Nov 2025) area judgements; safeguarding is a separate boolean and
+    never appears among the graded areas.
+    """
+    if o.overall_effectiveness is not None:
+        grade_source = "graded"
+        overall = o.overall_effectiveness
+    elif o.ungraded_grade is not None:
+        # Fall back to the grade parsed from an ungraded (Section 8) outcome
+        # (e.g. "School remains Good") so the detail page matches the list badge.
+        grade_source = "ungraded_carried_forward"
+        overall = o.ungraded_grade
+    else:
+        grade_source = None
+        overall = None
+
+    block = {
+        "framework": o.framework,
+        "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
+        "inspection_type": o.inspection_type,
+        "overall_effectiveness": overall,
+        "grade_source": grade_source,
+        "quality_of_education": o.quality_of_education,
+        "behaviour_attitudes": o.behaviour_attitudes,
+        "personal_development": o.personal_development,
+        "leadership_management": o.leadership_management,
+        "early_years_provision": o.early_years_provision,
+        "sixth_form_provision": o.sixth_form_provision,
+        "previous_overall": None,  # Not available in new schema
+        "rc_safeguarding_met": o.rc_safeguarding_met,
+        "rc_inclusion": o.rc_inclusion,
+        "rc_curriculum_teaching": o.rc_curriculum_teaching,
+        "rc_achievement": o.rc_achievement,
+        "rc_attendance_behaviour": o.rc_attendance_behaviour,
+        "rc_personal_development": o.rc_personal_development,
+        "rc_leadership_governance": o.rc_leadership_governance,
+        "rc_early_years": o.rc_early_years,
+        "rc_sixth_form": o.rc_sixth_form,
+        "report_url": o.report_url,
+        "ofsted_page_url": ofsted_page_url(urn),
+    }
+    block["report_card"] = report_card_labels(block)
+    return block
+
+
+def _admissions_row_dict(a) -> dict:
+    """Serialize one fact_admissions row for API responses."""
+    return {
+        "year": a.year,
+        "school_phase": a.school_phase,
+        "places_offered": a.places_offered,
+        "total_applications": a.total_applications,
+        "first_preference_applications": a.first_preference_applications,
+        "first_preference_offers": a.first_preference_offers,
+        "first_preference_offer_pct": a.first_preference_offer_pct,
+        "oversubscription_ratio": a.oversubscription_ratio,
+        "oversubscribed": a.oversubscribed,
+        "total_offers": a.total_offers,
+        "second_preference_offers": a.second_preference_offers,
+        "third_preference_offers": a.third_preference_offers,
+        "cross_la_applications": a.cross_la_applications,
+        "cross_la_offers": a.cross_la_offers,
+    }
+
+
 def get_supplementary_data(db: Session, urn: int) -> dict:
    """Fetch all supplementary data for a single school URN."""
    result = {}
@@ -532,40 +680,7 @@ def get_supplementary_data(db: Session, urn: int) -> dict:

    # Latest Ofsted inspection
    o = safe_query(FactOfstedInspection, "urn", "inspection_date")
-    result["ofsted"] = (
-        {
-            "framework": o.framework,
-            "inspection_date": o.inspection_date.isoformat() if o.inspection_date else None,
-            "inspection_type": o.inspection_type,
-            # Fall back to the grade parsed from an ungraded (Section 8) outcome
-            # (e.g. "School remains Good") when there's no graded grade, so the
-            # detail page matches the list badge.
-            "overall_effectiveness": (
-                o.overall_effectiveness
-                if o.overall_effectiveness is not None
-                else o.ungraded_grade
-            ),
-            "quality_of_education": o.quality_of_education,
-            "behaviour_attitudes": o.behaviour_attitudes,
-            "personal_development": o.personal_development,
-            "leadership_management": o.leadership_management,
-            "early_years_provision": o.early_years_provision,
-            "sixth_form_provision": o.sixth_form_provision,
-            "previous_overall": None,  # Not available in new schema
-            "rc_safeguarding_met": o.rc_safeguarding_met,
-            "rc_inclusion": o.rc_inclusion,
-            "rc_curriculum_teaching": o.rc_curriculum_teaching,
-            "rc_achievement": o.rc_achievement,
-            "rc_attendance_behaviour": o.rc_attendance_behaviour,
-            "rc_personal_development": o.rc_personal_development,
-            "rc_leadership_governance": o.rc_leadership_governance,
-            "rc_early_years": o.rc_early_years,
-            "rc_sixth_form": o.rc_sixth_form,
-            "report_url": o.report_url,
-        }
-        if o
-        else None
-    )
+    result["ofsted"] = _ofsted_block(o, urn) if o else None

    # Census (latest year of fact_pupil_characteristics)
    pc = safe_query(FactPupilCharacteristics, "urn", "year")
@@ -583,19 +698,6 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
    )

    # Admissions — all years, oldest first (for the multi-year trend view).
-    def _admissions_row(a):
-        return {
-            "year": a.year,
-            "school_phase": a.school_phase,
-            "places_offered": a.places_offered,
-            "total_applications": a.total_applications,
-            "first_preference_applications": a.first_preference_applications,
-            "first_preference_offers": a.first_preference_offers,
-            "first_preference_offer_pct": a.first_preference_offer_pct,
-            "oversubscription_ratio": a.oversubscription_ratio,
-            "oversubscribed": a.oversubscribed,
-        }
-
    try:
        admissions_rows = (
            db.query(FactAdmissions)
@@ -609,7 +711,7 @@ def get_supplementary_data(db: Session, urn: int) -> dict:
        db.rollback()
        admissions_rows = []

-    history = [_admissions_row(a) for a in admissions_rows]
+    history = [_admissions_row_dict(a) for a in admissions_rows]
    result["admissions_history"] = history
    # Keep the single latest-year object for backwards-compatible consumers
    # (hero chips, etc.).
@@ -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: "",
 }


@@ -88,6 +88,15 @@ class KS2Performance(Base):
    maths_high_pct = Column(Float)
    maths_avg_score = Column(Float)
    maths_progress = Column(Float)
+    # Progress confidence intervals + writing working-towards (published
+    # for years with progress measures, i.e. up to 2022/23)
+    reading_progress_lower_ci = Column(Float)
+    reading_progress_upper_ci = Column(Float)
+    writing_progress_lower_ci = Column(Float)
+    writing_progress_upper_ci = Column(Float)
+    writing_working_towards_pct = Column(Float)
+    maths_progress_lower_ci = Column(Float)
+    maths_progress_upper_ci = Column(Float)
    gps_expected_pct = Column(Float)
    gps_high_pct = Column(Float)
    gps_avg_score = Column(Float)
@@ -165,6 +174,11 @@ class FactAdmissions(Base):
    total_applications = Column(Integer)
    first_preference_applications = Column(Integer)
    first_preference_offers = Column(Integer)
+    total_offers = Column(Integer)
+    second_preference_offers = Column(Integer)
+    third_preference_offers = Column(Integer)
+    cross_la_applications = Column(Integer)
+    cross_la_offers = Column(Integer)
    first_preference_offer_pct = Column(Float)
    oversubscription_ratio = Column(Float)
    oversubscribed = Column(Boolean)
@@ -0,0 +1,44 @@
+"""Ofsted renewed-framework (Nov 2025) report-card code translation.
+
+Scale labels are the live-sampled vocabulary from the Ofsted MI file
+(see pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE) —
+verified against real data, not the consultation draft.
+"""
+
+REPORT_CARD_GRADE_NAMES = {
+    1: "Exceptional",
+    2: "Strong standard",
+    3: "Expected standard",
+    4: "Needs attention",
+    5: "Urgent improvement",
+}
+
+# Graded evaluation areas only — safeguarding is a separate boolean
+# judgement and must never appear in grade counts or label maps.
+_RC_AREA_KEYS = (
+    "rc_inclusion",
+    "rc_curriculum_teaching",
+    "rc_achievement",
+    "rc_attendance_behaviour",
+    "rc_personal_development",
+    "rc_leadership_governance",
+    "rc_early_years",
+    "rc_sixth_form",
+)
+
+
+def report_card_labels(ofsted: dict) -> dict:
+    """{area_key: {code, label}} for populated, known-valued rc_* areas."""
+    out = {}
+    for key in _RC_AREA_KEYS:
+        code = ofsted.get(key)
+        label = REPORT_CARD_GRADE_NAMES.get(code)
+        if code is not None and label is not None:
+            out[key] = {"code": code, "label": label}
+    return out
+
+
+def ofsted_page_url(urn: int) -> str:
+    """The school's page on ofsted.gov.uk (all its reports live there —
+    we never deep-link an individual report)."""
+    return f"https://reports.ofsted.gov.uk/provider/21/{urn}"
@@ -0,0 +1,78 @@
+"""compute_benchmarks: state-school benchmarks computed from our dataset
+(spec §5/§8.6). The disadvantaged average must be weighted by cohort size,
+medians must ignore NaN, and only the latest year counts."""
+
+import numpy as np
+import pandas as pd
+
+from backend.data_loader import compute_benchmarks
+
+LATEST = 202425
+
+
+def _df():
+    rows = [
+        # Six primary schools, latest year. Disadvantaged RWM chosen so the
+        # weighted average differs clearly from the unweighted mean:
+        # weighted = (40*100 + 60*300) / 400 = 55.0 ; unweighted mean = 50.0
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=100,
+             rwm_expected_disadvantaged_pct=40.0, eal_pct=10.0,
+             sen_support_pct=10.0, disadvantaged_pct=20.0, total_pupils=200),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=300,
+             rwm_expected_disadvantaged_pct=60.0, eal_pct=20.0,
+             sen_support_pct=14.0, disadvantaged_pct=24.0, total_pupils=280),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=np.nan,
+             rwm_expected_disadvantaged_pct=99.0, eal_pct=30.0,
+             sen_support_pct=18.0, disadvantaged_pct=30.0, total_pupils=300),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=50,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=np.nan,
+             sen_support_pct=np.nan, disadvantaged_pct=np.nan, total_pupils=np.nan),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=40,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=40.0,
+             sen_support_pct=20.0, disadvantaged_pct=40.0, total_pupils=350),
+        dict(year=LATEST, attainment_8_score=np.nan, eligible_pupils=60,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=50.0,
+             sen_support_pct=22.0, disadvantaged_pct=44.0, total_pupils=400),
+        # Two secondary schools (attainment_8 non-null)
+        dict(year=LATEST, attainment_8_score=45.0, eligible_pupils=180,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=15.0,
+             sen_support_pct=12.0, disadvantaged_pct=22.0, total_pupils=1000),
+        dict(year=LATEST, attainment_8_score=50.0, eligible_pupils=200,
+             rwm_expected_disadvantaged_pct=np.nan, eal_pct=25.0,
+             sen_support_pct=16.0, disadvantaged_pct=26.0, total_pupils=1200),
+        # An older-year primary row that must NOT influence anything
+        dict(year=202324, attainment_8_score=np.nan, eligible_pupils=500,
+             rwm_expected_disadvantaged_pct=1.0, eal_pct=99.0,
+             sen_support_pct=99.0, disadvantaged_pct=99.0, total_pupils=9999),
+    ]
+    return pd.DataFrame(rows)
+
+
+def test_weighted_disadvantaged_average():
+    b = compute_benchmarks(_df())
+    # Row 3 has NaN eligible_pupils and must be excluded from the weighting.
+    assert b["primary"]["disadvantaged_rwm_expected_pct"] == 55.0
+
+
+def test_medians_ignore_nan_and_older_years():
+    b = compute_benchmarks(_df())
+    assert b["year"] == LATEST
+    # eal medians over [10,20,30,40,50] = 30
+    assert b["primary"]["eal_pct"] == 30.0
+    # median pupils over [200,280,300,350,400] = 300
+    assert b["primary"]["median_pupils"] == 300
+
+
+def test_secondary_block_has_no_disadvantaged_rwm():
+    b = compute_benchmarks(_df())
+    assert "disadvantaged_rwm_expected_pct" not in b["secondary"]
+    assert b["secondary"]["median_pupils"] == 1100
+
+
+def test_provenance_string():
+    b = compute_benchmarks(_df())
+    assert b["source"] == "state-school average (computed from our dataset)"
+
+
+def test_empty_df():
+    assert compute_benchmarks(pd.DataFrame()) == {}
@@ -0,0 +1,121 @@
+"""/api/compare enrichment for the compare redesign: per-school
+supplementary blocks, top-level national_averages (shared with the
+/api/national-averages endpoint) and computed benchmarks — all additive."""
+
+import types
+
+import numpy as np
+import pandas as pd
+import pytest
+from fastapi.testclient import TestClient
+
+LATEST = 202425
+
+CANNED_SUPPLEMENTARY = {
+    "ofsted": {"overall_effectiveness": 2, "grade_source": "graded",
+               "report_card": {}, "ofsted_page_url": "https://reports.ofsted.gov.uk/provider/21/100140"},
+    "census": {"year": 202526, "fsm_pct": 29.8},
+    "admissions": {"year": 202627, "second_preference_offers": 4},
+    "admissions_history": [{"year": 202627, "second_preference_offers": 4}],
+    "sen_detail": None,
+    "phonics": None,
+    "deprivation": {"idaci_decile": 4},
+    "finance": None,
+}
+
+
+def _two_primary_schools_df() -> pd.DataFrame:
+    rows = []
+    for urn, name, rwm, dis in ((100140, "Plumcroft Primary School", 79.0, 72.0),
+                                (138690, "Barclay Primary School", 87.0, 86.0)):
+        rows.append(dict(
+            urn=urn, school_name=name, local_authority="Greenwich",
+            school_type="Community school", address="1 Road", phase="Primary",
+            year=LATEST, rwm_expected_pct=rwm, attainment_8_score=np.nan,
+            eligible_pupils=60, rwm_expected_disadvantaged_pct=dis,
+            eal_pct=20.0, sen_support_pct=14.0, disadvantaged_pct=25.0,
+            total_pupils=1000.0,
+        ))
+    return pd.DataFrame(rows)
+
+
+class _StubNatRow:
+    year = 202425
+    rwm_expected_pct = 62.1
+    gps_expected_pct = 72.0
+    science_expected_pct = 81.0
+
+
+class _StubSession:
+    def query(self, *a, **k):
+        return self
+
+    def order_by(self, *a, **k):
+        return self
+
+    def all(self):
+        return [_StubNatRow()]
+
+    def close(self):
+        pass
+
+
+@pytest.fixture()
+def client(monkeypatch):
+    from backend import app as app_module
+    from backend import database as database_module
+
+    monkeypatch.setattr(app_module, "load_school_data", _two_primary_schools_df)
+    monkeypatch.setattr(
+        app_module, "get_supplementary_data", lambda db, urn: dict(CANNED_SUPPLEMENTARY)
+    )
+    monkeypatch.setattr(database_module, "SessionLocal", _StubSession)
+    return TestClient(app_module.app, raise_server_exceptions=False)
+
+
+def test_existing_shape_is_preserved(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    school = body["comparison"]["100140"]
+    assert school["school_info"]["rwm_expected_pct"] == 79.0
+    assert school["yearly_data"][0]["year"] == LATEST
+
+
+def test_each_school_gains_supplementary_blocks(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    for urn in ("100140", "138690"):
+        school = body["comparison"][urn]
+        assert school["ofsted"]["grade_source"] == "graded"
+        assert school["census"]["fsm_pct"] == 29.8
+        assert school["admissions"]["second_preference_offers"] == 4
+        assert school["admissions_history"][0]["year"] == 202627
+        assert school["deprivation"]["idaci_decile"] == 4
+
+
+def test_top_level_national_averages_and_benchmarks(client):
+    body = client.get("/api/compare?urns=100140,138690").json()
+    assert body["national_averages"]["year"] == LATEST
+    assert body["benchmarks"]["source"] == "state-school average (computed from our dataset)"
+    # weighted over equal cohorts of 72 and 86 = 79.0
+    assert body["benchmarks"]["primary"]["disadvantaged_rwm_expected_pct"] == 79.0
+
+
+def test_supplementary_failure_degrades_not_500(client, monkeypatch):
+    from backend import app as app_module
+
+    def _boom(db, urn):
+        raise RuntimeError("marts unavailable")
+
+    monkeypatch.setattr(app_module, "get_supplementary_data", _boom)
+    resp = client.get("/api/compare?urns=100140")
+    assert resp.status_code == 200
+    school = resp.json()["comparison"]["100140"]
+    assert school["ofsted"] is None
+    assert school["admissions_history"] == []
+
+
+def test_national_averages_endpoint_exposes_gps_science(client):
+    body = client.get("/api/national-averages").json()
+    latest_primary_by_year = [e["primary"] for e in body["by_year"] if e["primary"]]
+    assert latest_primary_by_year, "expected official by_year rows from the stub"
+    assert latest_primary_by_year[-1]["gps_expected_pct"] == 72.0
+    assert latest_primary_by_year[-1]["science_expected_pct"] == 81.0
@@ -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) == ""
@@ -0,0 +1,45 @@
+"""Report-card code translation uses the live-sampled Ofsted vocabulary
+(pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE):
+Exceptional / Strong standard / Expected standard / Needs attention /
+Urgent improvement — never the consultation draft's 'Attention needed'."""
+
+from backend.ofsted_codes import (
+    REPORT_CARD_GRADE_NAMES,
+    ofsted_page_url,
+    report_card_labels,
+)
+
+
+def test_scale_is_sampled_vocabulary():
+    assert REPORT_CARD_GRADE_NAMES == {
+        1: "Exceptional",
+        2: "Strong standard",
+        3: "Expected standard",
+        4: "Needs attention",
+        5: "Urgent improvement",
+    }
+
+
+def test_labels_only_for_populated_areas_and_never_safeguarding():
+    ofsted = {
+        "rc_achievement": 2,
+        "rc_inclusion": 3,
+        "rc_attendance_behaviour": 4,
+        "rc_early_years": None,
+        "rc_safeguarding_met": True,
+        "overall_effectiveness": None,
+    }
+    labels = report_card_labels(ofsted)
+    assert labels == {
+        "rc_achievement": {"code": 2, "label": "Strong standard"},
+        "rc_inclusion": {"code": 3, "label": "Expected standard"},
+        "rc_attendance_behaviour": {"code": 4, "label": "Needs attention"},
+    }
+
+
+def test_unknown_code_is_skipped_not_crashed():
+    assert report_card_labels({"rc_achievement": 9}) == {}
+
+
+def test_provider_url():
+    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/provider/21/138690"
@@ -0,0 +1,65 @@
+"""Supplementary-block enrichment for the compare redesign: report-card
+labels, provider-page URL, graded-vs-carried-forward provenance, and the
+admissions preference/cross-LA detail promoted in the data-foundation PR."""
+
+import types
+
+from backend.data_loader import _admissions_row_dict, _ofsted_block
+
+
+def _row(**kw):
+    base = dict(
+        framework="RC", inspection_date=None, inspection_type=None,
+        overall_effectiveness=None, quality_of_education=None,
+        behaviour_attitudes=None, personal_development=None,
+        leadership_management=None, early_years_provision=None,
+        sixth_form_provision=None, ungraded_outcome=None, ungraded_grade=None,
+        rc_safeguarding_met=None, rc_inclusion=None, rc_curriculum_teaching=None,
+        rc_achievement=None, rc_attendance_behaviour=None,
+        rc_personal_development=None, rc_leadership_governance=None,
+        rc_early_years=None, rc_sixth_form=None, report_url=None,
+    )
+    base.update(kw)
+    return types.SimpleNamespace(**base)
+
+
+def test_report_card_block_and_provider_url():
+    o = _row(rc_achievement=2, rc_inclusion=3, rc_safeguarding_met=True)
+    block = _ofsted_block(o, urn=100140)
+    assert block["report_card"]["rc_achievement"]["label"] == "Strong standard"
+    assert "rc_safeguarding_met" not in block["report_card"]
+    assert block["rc_safeguarding_met"] is True
+    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"
+
+
+def test_grade_source_graded_vs_carried_forward():
+    assert _ofsted_block(_row(overall_effectiveness=1), urn=1)["grade_source"] == "graded"
+    carried = _ofsted_block(_row(ungraded_grade=2), urn=1)
+    assert carried["grade_source"] == "ungraded_carried_forward"
+    assert carried["overall_effectiveness"] == 2
+    assert _ofsted_block(_row(), urn=1)["grade_source"] is None
+
+
+def test_ofsted_block_keeps_existing_keys():
+    block = _ofsted_block(_row(overall_effectiveness=2, quality_of_education=2), urn=1)
+    for key in ("framework", "inspection_date", "overall_effectiveness",
+                "quality_of_education", "rc_inclusion", "report_url"):
+        assert key in block
+
+
+def test_admissions_row_new_fields():
+    a = types.SimpleNamespace(
+        year=202627, school_phase="Primary", places_offered=80,
+        total_applications=185, first_preference_applications=74,
+        first_preference_offers=74, first_preference_offer_pct=100.0,
+        oversubscription_ratio=0.925, oversubscribed=False,
+        total_offers=80, second_preference_offers=4, third_preference_offers=2,
+        cross_la_applications=12, cross_la_offers=3,
+    )
+    d = _admissions_row_dict(a)
+    for k in ("total_offers", "second_preference_offers", "third_preference_offers",
+              "cross_la_applications", "cross_la_offers"):
+        assert d[k] == getattr(a, k)
+    # Existing keys unchanged
+    assert d["first_preference_offer_pct"] == 100.0
+    assert d["oversubscribed"] is False
@@ -112,11 +112,15 @@ Full details in `docs/DEPLOY.md`. The short version:
 - **Never push to `main` directly.** Work on a feature branch and open a PR;
  branch protection requires the PR checks (typecheck, tests, builds, AI review)
  to pass before merge.
- Merging to `main` deploys automatically: images are built once, deployed to
-  the **staging** Portainer stack, verified by the Playwright journeys in
-  `e2e/`, and only then retagged `:prod` and rolled out to production.
+- Merging to `main` deploys automatically **to staging only**: images are
+  built once, deployed to the staging Portainer stack, and verified by the
+  Playwright journeys in `e2e/`. Production is a second, manual approval:
+  the "Promote to Production (manual)" workflow in Gitea Actions, run after
+  testing the feature on staging. It refuses commits whose staging E2E gate
+  isn't green. Never trigger it yourself — promotion is the human's call.
 - If you change user-facing behaviour, update or extend the `e2e/` journey
-  tests in the same PR — they are the promotion gate.
+  tests in the same PR — they gate whether staging is fit for human testing
+  and whether a commit is promotable.

 ## Recent Changes

@@ -1,41 +1,61 @@
 # SDLC & Deployment Pipeline

-SchoolCompare uses a fully automated staging → production pipeline on Gitea
-Actions. AI writes the code on feature branches; the pipeline verifies every
-change on a staging environment before promoting the exact same images to
-production. Human input is directional only: feature requests, PR review if
-desired, and intervention when a gate fails.
+SchoolCompare uses a two-stage deploy model on Gitea Actions with two human
+approvals. AI writes the code on feature branches; the first approval merges
+the PR, which deploys to staging and runs the E2E gate; the second approval —
+after manual testing on staging — promotes the exact same images to
+production via a manual workflow.

 ## The flow

 ```
 feature branch (AI-authored)
-   │  PR to main
+   │  PR to main                                    ← approval #1
   ▼
 PR checks (.gitea/workflows/pr-checks.yml)
   typecheck + unit tests + backend smoke + image builds (no push)
   + Claude code review posted as a PR comment (severe findings fail the check)
   │  merge (branch protection requires green checks)
   ▼
-Deploy pipeline (.gitea/workflows/deploy.yml)
+Stage pipeline (.gitea/workflows/deploy.yml) — automatic
   1. build & push images        → tags sha-<sha>, staging
   2. staging Portainer webhook  → wait for staging health
-   3. Playwright E2E journeys against staging
-   4. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
+   3. Playwright E2E journeys against staging      ← gate before human testing
+   ▼
+Manual testing on staging (stx.schoolcompare.co.uk)
+   │  Actions → "Promote to Production (manual)"   ← approval #2
+   ▼
+Promote pipeline (.gitea/workflows/promote.yml) — manual dispatch
+   1. resolve target sha (input, or latest main if empty)
+   2. REFUSE unless that commit's "E2E Journeys against Staging" status is green
+   3. retag sha-<sha> → :prod    (same bytes — build once, promote the image)
      previous :prod saved as :prod-previous
-   5. prod Portainer webhook     → wait for prod health
+   4. prod Portainer webhook     → wait for prod health
 ```

 Key principle: **build once, promote the exact image**. Production pins `:prod`,
-which only moves after the E2E gate passes on staging. Nothing tags `:latest`
+which only moves when a human runs the promote workflow — and the workflow
+only accepts commits that passed the staging E2E gate. Nothing tags `:latest`
 anymore.

 ## Branch & PR workflow

 - `main` is protected: no direct pushes, PRs require green status checks.
 - All work (human or AI) happens on feature branches → PR to `main`.
- Merging to `main` **is** the release action. If staging or the E2E gate
-  fails, production is untouched.
+- Merging to `main` releases **to staging only**. Production moves only on
+  the second approval. If staging or the E2E gate fails, fix forward —
+  production is untouched either way.
+
+## Promotion granularity
+
+Staging always runs the latest `main`. Promoting approves a *state of main*,
+not a single PR — if two PRs merged since the last promotion, they ship
+together. Test staging accordingly. To promote an older state, pass its
+commit SHA to the promote workflow (its images must still exist in the
+registry).
+
+Staging quirk for manual testing: external `/api` is broken at the staging
+proxy — exercise API endpoints from the host, not via the public staging URL.

 ## Environments

@@ -92,8 +112,9 @@ fail the E2E gate. That's the point: staging absorbs the risk.

 ## Rollback

-Every promotion first re-points `:prod-previous` at the outgoing `:prod`.
-To roll back:
+Re-run "Promote to Production (manual)" with the SHA of the last good commit
+(fastest, fully gated), or manually re-point the tags — every promotion first
+saves the outgoing `:prod` as `:prod-previous`:

 ```bash
 for img in backend frontend pipeline; do
@@ -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.
@@ -0,0 +1,399 @@
+# Compare API Enrichment (Backend 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:** Expose the PR #32 data through the API so the redesigned compare screen can be built: enrich `/api/compare` with supplementary blocks + national averages + computed benchmarks, translate Ofsted report-card codes to labels, and surface the new mart columns (spec §6, §8 of `docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md`).
+
+**Architecture:** All changes are additive API fields — existing consumers keep working. One small dbt change rides along: `fact_performance` (the combined KS2+KS4 mart the backend's `_MAIN_QUERY` reads) enumerates columns explicitly and was not extended in PR #32, so the new KS2 CI and KS4 banding columns must be threaded through it here. Everything else is backend Python: `models.py` mappings, `data_loader` query/supplementary additions, an Ofsted label dictionary (gias_codes pattern), and `/api/compare` composition.
+
+**Tech Stack:** FastAPI, SQLAlchemy, pandas; dbt (one model); pytest via `python -m pytest backend/tests -q` (CI installs `requirements.txt pytest "httpx<0.28"`; locally use `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q`).
+
+## Global Constraints
+
+- **Never push to `main`.** Branch: `feat/compare-api-enrichment`.
+- **Additive only** to API responses; never rename/remove existing fields (frontend + e2e depend on them).
+- **Report-card scale labels are the live-sampled vocabulary** (evidence in `pipeline/scripts/diagnose_compare_gaps.py`): `1=Exceptional, 2=Strong standard, 3=Expected standard, 4=Needs attention, 5=Urgent improvement`. Never "Attention needed". Safeguarding is boolean met/not-met, never counted as a graded area.
+- **Ofsted links** are always the provider page `https://reports.ofsted.gov.uk/provider/21/{urn}` (spec §5) labelled as the school's Ofsted page.
+- **Benchmark provenance** (spec §8.6): computed values are "state-school average (computed from our dataset)" — the API must expose them under a `benchmarks` key, clearly separate from official `national_averages`.
+- TDD: each behaviour lands with a failing test first, in `backend/tests/` following the `test_school_details.py` pattern (pandas fixture + monkeypatched `load_school_data` + `TestClient`).
+- Deploy note for the PR body: the new API fields return NULL/empty until prod's DAGs have run post-promotion.
+
+---
+
+### Task 0: Branch
+
+- [ ] `git checkout main && git pull && git checkout -b feat/compare-api-enrichment` (commit this plan file on the branch).
+
+---
+
+### Task 1: Thread PR #32 columns through `fact_performance`
+
+**Files:**
+- Modify: `pipeline/transform/models/marts/fact_performance.sql`
+- Modify: `pipeline/transform/models/marts/_marts_schema.yml` (fact_performance block, if it has one — add the columns wherever the model's other columns are listed; if the model has no column list there, skip the yml)
+
+**Interfaces:**
+- Produces (for `_MAIN_QUERY` in Task 4): `ks2.*` CI columns and `ks4.progress_8_banding`, `ks4.attainment_8_disadvantage_gap`, `ks4.progress_8_disadvantage_gap` on `marts.fact_performance`.
+
+- [ ] **Step 1:** In `fact_performance.sql`, after `ks2.reading_progress,` add `ks2.reading_progress_lower_ci,` and `ks2.reading_progress_upper_ci,`; after `ks2.writing_progress,` add `ks2.writing_progress_lower_ci,`, `ks2.writing_progress_upper_ci,`, `ks2.writing_working_towards_pct,`; after `ks2.maths_progress,` add `ks2.maths_progress_lower_ci,`, `ks2.maths_progress_upper_ci,`. In the KS4 section, after the `ks4.progress_8_upper_ci`-equivalent line (locate the Progress 8 block) add:
+
+```sql
+    ks4.progress_8_banding,
+    ks4.attainment_8_disadvantage_gap,
+    ks4.progress_8_disadvantage_gap,
+```
+
+- [ ] **Step 2:** Parse gate: `cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir .` → exit 0.
+
+- [ ] **Step 3:** Commit: `feat(pipeline): thread compare-foundation columns through fact_performance`
+
+---
+
+### Task 2: ORM mappings for the new mart columns
+
+**Files:**
+- Modify: `backend/models.py` (`KS2Performance` after `maths_progress`; `FactAdmissions` after `first_preference_offers`)
+- Test: none (declarative mappings; covered by Task 4's query tests)
+
+**Interfaces:**
+- Produces attributes used by Task 4: `KS2Performance.reading_progress_lower_ci` … `maths_progress_upper_ci`, `writing_working_towards_pct` (Float); `FactAdmissions.total_offers`, `.second_preference_offers`, `.third_preference_offers`, `.cross_la_applications`, `.cross_la_offers` (Integer).
+
+- [ ] **Step 1:** Add to `KS2Performance` (next to the existing progress columns):
+
+```python
+    reading_progress_lower_ci = Column(Float)
+    reading_progress_upper_ci = Column(Float)
+    writing_progress_lower_ci = Column(Float)
+    writing_progress_upper_ci = Column(Float)
+    writing_working_towards_pct = Column(Float)
+    maths_progress_lower_ci = Column(Float)
+    maths_progress_upper_ci = Column(Float)
+```
+
+Add to `FactAdmissions` (after `first_preference_offers`):
+
+```python
+    total_offers = Column(Integer)
+    second_preference_offers = Column(Integer)
+    third_preference_offers = Column(Integer)
+    cross_la_applications = Column(Integer)
+    cross_la_offers = Column(Integer)
+```
+
+(`FactOfstedInspection` already maps all `rc_*` columns with the right types — verify, don't change.)
+
+- [ ] **Step 2:** Commit: `feat(api): map compare-foundation mart columns`
+
+---
+
+### Task 3: Ofsted label dictionary + provider URL (TDD)
+
+**Files:**
+- Create: `backend/ofsted_codes.py`
+- Test: `backend/tests/test_ofsted_codes.py`
+
+**Interfaces:**
+- Produces for Task 4: `REPORT_CARD_GRADE_NAMES: dict[int, str]`, `report_card_labels(ofsted: dict) -> dict` (returns `{area_key: {"code": int, "label": str}}` for the non-null `rc_*` grade fields, excluding safeguarding), `ofsted_page_url(urn: int) -> str`.
+
+- [ ] **Step 1: Failing tests**
+
+```python
+"""Report-card code translation uses the live-sampled Ofsted vocabulary
+(pipeline/scripts/diagnose_compare_gaps.py TASK 7 VALUE SAMPLE):
+Exceptional / Strong standard / Expected standard / Needs attention /
+Urgent improvement — never the consultation draft's 'Attention needed'."""
+from backend.ofsted_codes import (
+    REPORT_CARD_GRADE_NAMES, report_card_labels, ofsted_page_url,
+)
+
+
+def test_scale_is_sampled_vocabulary():
+    assert REPORT_CARD_GRADE_NAMES == {
+        1: "Exceptional",
+        2: "Strong standard",
+        3: "Expected standard",
+        4: "Needs attention",
+        5: "Urgent improvement",
+    }
+
+
+def test_labels_only_for_populated_areas_and_never_safeguarding():
+    ofsted = {
+        "rc_achievement": 2,
+        "rc_inclusion": 3,
+        "rc_attendance_behaviour": 4,
+        "rc_early_years": None,
+        "rc_safeguarding_met": True,
+        "overall_effectiveness": None,
+    }
+    labels = report_card_labels(ofsted)
+    assert labels == {
+        "rc_achievement": {"code": 2, "label": "Strong standard"},
+        "rc_inclusion": {"code": 3, "label": "Expected standard"},
+        "rc_attendance_behaviour": {"code": 4, "label": "Needs attention"},
+    }
+
+
+def test_unknown_code_is_skipped_not_crashed():
+    assert report_card_labels({"rc_achievement": 9}) == {}
+
+
+def test_provider_url():
+    assert ofsted_page_url(138690) == "https://reports.ofsted.gov.uk/provider/21/138690"
+```
+
+- [ ] **Step 2:** Run `uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests/test_ofsted_codes.py -q` → FAIL (module missing).
+
+- [ ] **Step 3: Implement `backend/ofsted_codes.py`**
+
+```python
+"""Ofsted renewed-framework (Nov 2025) report-card code translation.
+
+Scale labels are the live-sampled vocabulary from the Ofsted MI file
+(see pipeline/scripts/diagnose_compare_gaps.py, TASK 7 VALUE SAMPLE) —
+verified against real data, not the consultation draft.
+"""
+
+REPORT_CARD_GRADE_NAMES = {
+    1: "Exceptional",
+    2: "Strong standard",
+    3: "Expected standard",
+    4: "Needs attention",
+    5: "Urgent improvement",
+}
+
+# Graded evaluation areas only — safeguarding is a separate boolean
+# judgement and must never appear in grade counts or label maps.
+_RC_AREA_KEYS = (
+    "rc_inclusion",
+    "rc_curriculum_teaching",
+    "rc_achievement",
+    "rc_attendance_behaviour",
+    "rc_personal_development",
+    "rc_leadership_governance",
+    "rc_early_years",
+    "rc_sixth_form",
+)
+
+
+def report_card_labels(ofsted: dict) -> dict:
+    """{area_key: {code, label}} for populated, known-valued rc_* areas."""
+    out = {}
+    for key in _RC_AREA_KEYS:
+        code = ofsted.get(key)
+        label = REPORT_CARD_GRADE_NAMES.get(code)
+        if code is not None and label is not None:
+            out[key] = {"code": code, "label": label}
+    return out
+
+
+def ofsted_page_url(urn: int) -> str:
+    """The school's page on ofsted.gov.uk (all its reports live there —
+    we never deep-link an individual report; spec §5)."""
+    return f"https://reports.ofsted.gov.uk/provider/21/{urn}"
+```
+
+- [ ] **Step 4:** Re-run the test file → 4 passed. Run the full suite (same command, `backend/tests -q`) → all pass.
+
+- [ ] **Step 5:** Commit: `feat(api): Ofsted report-card labels and provider-page URL`
+
+---
+
+### Task 4: data_loader — query columns + richer supplementary blocks (TDD)
+
+**Files:**
+- Modify: `backend/data_loader.py` (`_MAIN_QUERY` ~line 153; `get_supplementary_data` ~line 460)
+- Test: `backend/tests/test_supplementary_enrichment.py`
+
+**Interfaces:**
+- `_MAIN_QUERY` additionally selects (KS2 block, after `p.maths_progress`): `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`; (KS4 block, after the Progress 8 CI columns): `p.progress_8_banding, p.attainment_8_disadvantage_gap, p.progress_8_disadvantage_gap`. Note `_MAIN_QUERY_NO_SIXTH_FORM`/`_MAIN_QUERY_LEGACY_NAMES` are string-derived from `_MAIN_QUERY` (lines 259-270) and inherit automatically — verify the assertions there still hold.
+- `get_supplementary_data(db, urn)["admissions"]` rows additionally carry: `total_offers`, `second_preference_offers`, `third_preference_offers`, `cross_la_applications`, `cross_la_offers` (add to `_admissions_row`).
+- `get_supplementary_data(db, urn)["ofsted"]` additionally carries: `report_card` (the `report_card_labels(...)` dict, `{}` when no rc data), `ofsted_page_url`, and `grade_source`: `"graded"` when `overall_effectiveness` came from the graded column, `"ungraded_carried_forward"` when the fallback `ungraded_grade` supplied it, `None` when neither.
+
+- [ ] **Step 1: Failing tests** — construct a fake Ofsted row object (simple `types.SimpleNamespace` with the model's attributes) and call the block-building logic via `get_supplementary_data` with a stubbed session (follow how existing tests stub the db; if none do, factor the ofsted-dict construction into a pure helper `_ofsted_block(o, urn)` and test that directly — preferred):
+
+```python
+import types
+from backend.data_loader import _ofsted_block
+
+
+def _row(**kw):
+    base = dict(
+        framework="RC", inspection_date=None, inspection_type=None,
+        overall_effectiveness=None, quality_of_education=None,
+        behaviour_attitudes=None, personal_development=None,
+        leadership_management=None, early_years_provision=None,
+        sixth_form_provision=None, ungraded_outcome=None, ungraded_grade=None,
+        rc_safeguarding_met=None, rc_inclusion=None, rc_curriculum_teaching=None,
+        rc_achievement=None, rc_attendance_behaviour=None,
+        rc_personal_development=None, rc_leadership_governance=None,
+        rc_early_years=None, rc_sixth_form=None, report_url=None,
+    )
+    base.update(kw)
+    return types.SimpleNamespace(**base)
+
+
+def test_report_card_block_and_provider_url():
+    o = _row(rc_achievement=2, rc_inclusion=3, rc_safeguarding_met=True)
+    block = _ofsted_block(o, urn=100140)
+    assert block["report_card"]["rc_achievement"]["label"] == "Strong standard"
+    assert "rc_safeguarding_met" not in block["report_card"]
+    assert block["rc_safeguarding_met"] is True
+    assert block["ofsted_page_url"] == "https://reports.ofsted.gov.uk/provider/21/100140"
+
+
+def test_grade_source_graded_vs_carried_forward():
+    assert _ofsted_block(_row(overall_effectiveness=1), urn=1)["grade_source"] == "graded"
+    carried = _ofsted_block(_row(ungraded_grade=2), urn=1)
+    assert carried["grade_source"] == "ungraded_carried_forward"
+    assert carried["overall_effectiveness"] == 2
+    assert _ofsted_block(_row(), urn=1)["grade_source"] is None
+
+
+def test_admissions_row_new_fields():
+    from backend.data_loader import _admissions_row_dict
+    a = types.SimpleNamespace(
+        year=202627, school_phase="Primary", places_offered=80,
+        total_applications=185, first_preference_applications=74,
+        first_preference_offers=74, first_preference_offer_pct=100.0,
+        oversubscription_ratio=0.925, oversubscribed=False,
+        total_offers=80, second_preference_offers=4, third_preference_offers=2,
+        cross_la_applications=12, cross_la_offers=3,
+    )
+    d = _admissions_row_dict(a)
+    for k in ("total_offers", "second_preference_offers", "third_preference_offers",
+              "cross_la_applications", "cross_la_offers"):
+        assert d[k] == getattr(a, k)
+```
+
+- [ ] **Step 2:** Run → FAIL (helpers don't exist).
+
+- [ ] **Step 3: Implement.** Refactor the existing inline ofsted-dict construction in `get_supplementary_data` into a module-level `_ofsted_block(o, urn)` that produces the existing keys **unchanged** plus the three new ones (`report_card` via `report_card_labels(...)` from Task 3, `ofsted_page_url` via `ofsted_page_url(urn)`, `grade_source` per the interface rule — derived from which source supplied `overall_effectiveness`). Rename/extract the local `_admissions_row` into module-level `_admissions_row_dict(a)` and append the five new fields. Add the ten new columns to `_MAIN_QUERY` exactly as the interface lists them. `get_supplementary_data` calls both helpers; its external shape gains only additive keys.
+
+- [ ] **Step 4:** Full suite → all pass (existing `test_school_details.py` etc. must not break; if a fixture enumerates yearly-data columns, extend it with the new NaN columns as needed).
+
+- [ ] **Step 5:** Commit: `feat(api): expose progress CIs, KS4 banding/gaps, admissions detail, report-card labels`
+
+---
+
+### Task 5: Computed benchmarks helper (TDD)
+
+**Files:**
+- Modify: `backend/data_loader.py` (new function)
+- Test: `backend/tests/test_benchmarks.py`
+
+**Interfaces:**
+- Produces for Task 6: `compute_benchmarks(df) -> dict` — pure function over the main dataframe (latest year, state schools), shape:
+
+```python
+{
+  "source": "state-school average (computed from our dataset)",
+  "year": 202425,
+  "primary": {
+      "disadvantaged_rwm_expected_pct": 46.1,   # weighted by eligible_pupils
+      "eal_pct": 22.3,                           # median
+      "sen_support_pct": 14.0,                   # median
+      "disadvantaged_pct": 24.8,                 # median (FSM6 proxy)
+      "median_pupils": 281,                      # median school size
+  },
+  "secondary": { "median_pupils": 1024, "eal_pct": ..., "sen_support_pct": ..., "disadvantaged_pct": ... },
+}
+```
+
+- [ ] **Step 1: Failing tests** — build a small synthetic df (6 primary rows with known eligible_pupils/rwm_expected_disadvantaged_pct so the weighted average is hand-checkable; a couple of secondary rows flagged by non-null `attainment_8_score`), assert: weighted disadvantaged average matches hand computation (not the unweighted mean), medians ignore NaN, secondary block lacks the disadvantaged-RWM key, latest-year filtering (rows from an older year must not affect results), and empty df → `{}`.
+
+- [ ] **Step 2:** Run → FAIL.
+
+- [ ] **Step 3: Implement** in `data_loader.py`:
+
+```python
+def compute_benchmarks(df: pd.DataFrame) -> dict:
+    """State-school benchmarks computed from our dataset (spec §5/§8.6).
+    These are NOT official DfE figures — consumers must label them
+    'state-school average (computed from our dataset)'."""
+    if df.empty or "year" not in df.columns:
+        return {}
+    latest_year = df["year"].max()
+    d = df[df["year"] == latest_year]
+    if d.empty:
+        return {}
+    is_secondary = d["attainment_8_score"].notna() if "attainment_8_score" in d.columns else pd.Series(False, index=d.index)
+    prim, sec = d[~is_secondary], d[is_secondary]
+
+    def _median(sub, col):
+        if col not in sub.columns:
+            return None
+        v = sub[col].median()
+        return round(float(v), 1) if pd.notna(v) else None
+
+    def _weighted_disadvantaged(sub):
+        if not {"rwm_expected_disadvantaged_pct", "eligible_pupils"} <= set(sub.columns):
+            return None
+        s = sub.dropna(subset=["rwm_expected_disadvantaged_pct", "eligible_pupils"])
+        if s.empty or s["eligible_pupils"].sum() == 0:
+            return None
+        w = (s["rwm_expected_disadvantaged_pct"] * s["eligible_pupils"]).sum() / s["eligible_pupils"].sum()
+        return round(float(w), 1)
+
+    def _block(sub, with_disadvantaged):
+        block = {
+            "eal_pct": _median(sub, "eal_pct"),
+            "sen_support_pct": _median(sub, "sen_support_pct"),
+            "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
+            "median_pupils": int(sub["total_pupils"].median()) if "total_pupils" in sub.columns and pd.notna(sub["total_pupils"].median()) else None,
+        }
+        if with_disadvantaged:
+            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
+        return block
+
+    return {
+        "source": "state-school average (computed from our dataset)",
+        "year": int(latest_year),
+        "primary": _block(prim, with_disadvantaged=True),
+        "secondary": _block(sec, with_disadvantaged=False),
+    }
+```
+
+(Adapt column presence to the real df — `sen_support_pct` reaches the df via `_MAIN_QUERY`; confirm and add it there if the KS2 block doesn't already select it, mirroring Task 4's additions.)
+
+- [ ] **Step 4:** Full suite → pass. **Step 5:** Commit: `feat(api): computed state-school benchmarks`
+
+---
+
+### Task 6: Enrich `/api/compare` + expose GPS/science national averages (TDD)
+
+**Files:**
+- Modify: `backend/app.py` (`compare_schools` ~line 636; `get_national_averages` ~line 730)
+- Test: `backend/tests/test_compare_enrichment.py`
+
+**Interfaces (response additions, all additive):**
+- `/api/compare` top level gains: `"national_averages"` (same payload the `/api/national-averages` endpoint returns — extract the endpoint body into a helper `_national_averages_payload(df)` and reuse; do not duplicate the logic) and `"benchmarks"` (Task 5's `compute_benchmarks(df)`).
+- Each `comparison[urn]` gains: `"ofsted"`, `"census"`, `"admissions"`, `"admissions_history"`, `"deprivation"` from `get_supplementary_data` (one `SessionLocal()` for the whole request, closed in `finally`; on exception the five keys are `None`/`[]` — mirror the detail endpoint's defensive pattern at app.py:583-590).
+- `get_national_averages`' KS2 metric list gains `"gps_expected_pct", "gps_high_pct", "science_expected_pct"` so the England ticks for GPS/science flow once the data exists.
+
+- [ ] **Step 1: Failing tests** — monkeypatch `load_school_data` with a two-school primary df (reuse/extend the fixture style of `test_school_details.py`) and monkeypatch `get_supplementary_data` to a canned dict; assert on `TestClient(app).get("/api/compare?urns=...")`:
+  - response keeps the existing shape (`comparison[urn]["school_info"]["rwm_expected_pct"]` etc.),
+  - each school gains the five supplementary keys (canned values round-tripped),
+  - top-level `national_averages` and `benchmarks` present; `benchmarks["source"]` is the exact provenance string,
+  - a supplementary-layer exception (monkeypatched to raise) degrades to `ofsted: None` etc. with HTTP 200,
+  - `/api/national-averages` includes `gps_expected_pct` in the primary block when the df/national table provides it (monkeypatch the national-averages source the endpoint reads).
+
+- [ ] **Step 2:** Run → FAIL. **Step 3:** Implement per the interfaces. **Step 4:** Full suite → pass.
+
+- [ ] **Step 5:** Commit: `feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks`
+
+---
+
+### Task 7: PR + verification
+
+- [ ] **Step 1:** Full suite one more time + `uv run --with pyyaml python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/deploy.yml'))"` sanity is NOT needed (no workflow changes) — instead run the dbt parse gate again (Task 1 file).
+- [ ] **Step 2:** Push, open PR via the Gitea API (credential-helper basic auth). PR body: the new response shapes (one JSON sketch), the reused-not-duplicated national-averages helper, the provenance rule for benchmarks, deploy note (fields NULL until prod DAGs run post-promotion), and that no e2e change is needed (no user-facing behaviour changes — the compare UI still reads the old fields; the frontend PR carries the journey updates).
+- [ ] **Step 3:** After merge + staging deploy: `curl -s https://stx.schoolcompare.co.uk/api/compare?urns=138690,100140 | python3 -m json.tool | head -80` — verify the new keys and that `benchmarks.primary.disadvantaged_rwm_expected_pct` is plausible (~45-47). Verify `/api/national-averages` now carries `gps_expected_pct`/`science_expected_pct` (values or honest nulls if DfE suppresses them at national level).
+
+---
+
+## Out of scope
+
+- Frontend rebuild + e2e journeys (next PR — consumes everything this PR exposes).
+- `schemas.py` METRIC_DEFINITIONS additions for the trends picker (frontend PR decides which of the new columns become picker metrics).
+- CI-based progress banding logic (frontend computes Above/Average/Below from the CI columns; historical years only).
@@ -0,0 +1,275 @@
+# Staged Production Promotion (Manual Gate) 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:** Merging a PR deploys to staging only; production deployment requires a second, explicit human approval after manual testing on staging.
+
+**Architecture:** Split the existing single `deploy.yml` pipeline in two. The push-to-main workflow keeps build → staging deploy → e2e gate and **stops there**. A new `promote.yml` runs only on `workflow_dispatch` (the "Run workflow" button in Gitea's Actions UI, supported on this server — Gitea 1.26.4): it verifies the chosen commit passed the staging e2e gate, retags its `:sha-*` images to `:prod` (keeping `:prod-previous` for rollback), and triggers the Portainer prod webhook. Promotion granularity is a main-branch commit: staging always runs the latest main, so you approve a *state of main*, not an individual PR.
+
+**Tech Stack:** Gitea Actions (1.26.4), Docker buildx imagetools, Portainer webhooks, Gitea commit-status API.
+
+## Global Constraints
+
+- **Never push to `main` directly** — this change itself goes through a PR (`chore/staged-prod-promotion` branch).
+- Existing image tagging scheme is unchanged: `type=sha` (e.g. `sha-6f925ab`) + `:staging`; promotion still retags `:sha-*` → `:prod` with `:prod-previous` kept as the rollback pointer.
+- The e2e journeys remain a **hard gate before human testing** (a red staging never reaches the promote button) and the promote workflow must refuse to promote a commit whose staging e2e did not succeed.
+- Secrets already exist and are reused: `REGISTRY_TOKEN` (also a Gitea API token), `PORTAINER_STAGING_WEBHOOK`, `PORTAINER_PROD_WEBHOOK`, `STAGING_BASE_URL`, `PROD_BASE_URL`.
+- Staging quirk (memory): external `/api` is broken at the staging proxy — manual API testing happens from the host, not through stx.schoolcompare.co.uk; note it in the runbook, don't try to fix it in this plan.
+
+## Considered approaches (context for the reviewer)
+
+1. **Manual `workflow_dispatch` promote workflow (chosen).** Native on Gitea 1.26; the second approval is clicking "Run workflow" (or one API call) after testing staging. Least machinery, auditable via the Actions run history.
+2. *Tag-driven promotion* (`push: tags: promote-*`): works on any Gitea version; approval = pushing a tag. Slightly more scriptable, less discoverable; kept as documented fallback only.
+3. *GitOps `production` branch + promotion PR:* approval literally reuses the PR-review UI, but adds a second long-lived branch to keep in sync — too much ceremony for a solo project. Rejected.
+
+---
+
+### Task 0: Branch
+
+- [ ] `git checkout main && git pull && git checkout -b chore/staged-prod-promotion`
+
+---
+
+### Task 1: Stop the push-to-main workflow after the e2e gate
+
+**Files:**
+- Modify: `.gitea/workflows/deploy.yml`
+
+**Interfaces:**
+- Produces: images tagged `:sha-<short>` + `:staging` (unchanged), a green `E2E Journeys against Staging` commit status that Task 2's promote workflow checks by name. **Do not rename the `e2e-staging` job's `name:` without updating Task 2's status check.**
+
+- [ ] **Step 1: Remove the auto-promotion**
+
+In `.gitea/workflows/deploy.yml`:
+1. Change line 1 to: `name: Stage (build -> staging -> E2E gate)`
+2. Delete the entire `promote-prod` job (lines 196–240 in the current file: from `  promote-prod:` to the end of the file).
+3. Leave `build-*`, `deploy-staging`, and `e2e-staging` untouched.
+
+- [ ] **Step 2: Sanity-check the YAML**
+
+Run: `python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/deploy.yml')); print('yaml ok')"`
+Expected: `yaml ok`
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add .gitea/workflows/deploy.yml
+git commit -m "ci: stop deploy pipeline at staging; production promotion becomes manual"
+```
+
+---
+
+### Task 2: Manual promote workflow
+
+**Files:**
+- Create: `.gitea/workflows/promote.yml`
+
+**Interfaces:**
+- Consumes: `:sha-<short>` images built by deploy.yml; the `E2E Journeys against Staging` commit status.
+- Produces: `:prod` and `:prod-previous` tags; prod stack update.
+
+- [ ] **Step 1: Write the workflow**
+
+```yaml
+name: Promote to Production (manual)
+
+on:
+  workflow_dispatch:
+    inputs:
+      sha:
+        description: >-
+          Commit SHA on main to promote (full or >=7 chars).
+          Leave empty to promote the latest main commit.
+        required: false
+        default: ""
+
+env:
+  REGISTRY: privaterepo.sitaru.org
+  BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
+  FRONTEND_IMAGE_NAME: ${{ gitea.repository }}-frontend
+  PIPELINE_IMAGE_NAME: ${{ gitea.repository }}-pipeline
+
+jobs:
+  promote-prod:
+    name: Promote approved commit to Production
+    runs-on: ubuntu-latest
+    steps:
+      - name: Resolve target SHA
+        id: resolve
+        run: |
+          SHA_INPUT="${{ gitea.event.inputs.sha }}"
+          if [ -z "$SHA_INPUT" ]; then
+            SHA_INPUT="${{ gitea.sha }}"
+          fi
+          # Normalise to the full sha via the API so short inputs work
+          FULL_SHA=$(curl -fsS \
+            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
+            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/git/commits/${SHA_INPUT}" \
+            | python3 -c "import json,sys; print(json.load(sys.stdin)['sha'])")
+          SHORT_SHA="sha-$(echo "$FULL_SHA" | cut -c1-7)"
+          echo "full=$FULL_SHA" >> "$GITHUB_OUTPUT"
+          echo "short=$SHORT_SHA" >> "$GITHUB_OUTPUT"
+          echo "Promoting $FULL_SHA (images tagged $SHORT_SHA)"
+
+      - name: Verify the staging E2E gate passed for this commit
+        run: |
+          STATUS_JSON=$(curl -fsS \
+            -H "Authorization: token ${{ secrets.REGISTRY_TOKEN }}" \
+            "https://${REGISTRY}/api/v1/repos/${{ gitea.repository }}/commits/${{ steps.resolve.outputs.full }}/status")
+          echo "$STATUS_JSON" | python3 -c "
+          import json, sys
+          d = json.load(sys.stdin)
+          ok = [s for s in d.get('statuses', [])
+                if 'E2E Journeys against Staging' in s.get('context', '')
+                and s.get('status') == 'success']
+          if not ok:
+              print('REFUSED: no successful \"E2E Journeys against Staging\" status on this commit.')
+              print('Contexts found:', [s.get('context') for s in d.get('statuses', [])])
+              sys.exit(1)
+          print('E2E gate verified green for this commit.')
+          "
+
+      - name: Set up Docker Buildx
+        uses: docker/setup-buildx-action@v3
+
+      - name: Log in to Gitea Container Registry
+        uses: docker/login-action@v3
+        with:
+          registry: ${{ env.REGISTRY }}
+          username: ${{ gitea.actor }}
+          password: ${{ secrets.REGISTRY_TOKEN }}
+
+      - name: Retag approved images as prod (keeping rollback pointer)
+        run: |
+          SHORT_SHA="${{ steps.resolve.outputs.short }}"
+          for IMAGE in \
+            "${REGISTRY}/${BACKEND_IMAGE_NAME}" \
+            "${REGISTRY}/${FRONTEND_IMAGE_NAME}" \
+            "${REGISTRY}/${PIPELINE_IMAGE_NAME}"; do
+            docker buildx imagetools create -t "${IMAGE}:prod-previous" "${IMAGE}:prod" || true
+            docker buildx imagetools create -t "${IMAGE}:prod" "${IMAGE}:${SHORT_SHA}"
+            echo "Promoted ${IMAGE}:${SHORT_SHA} -> :prod"
+          done
+
+      - name: Trigger production stack update
+        run: curl -fsSk -X POST "${{ secrets.PORTAINER_PROD_WEBHOOK }}"
+
+      - name: Wait for production to become healthy
+        run: |
+          echo "Polling ${PROD_BASE_URL} for up to 5 minutes..."
+          for i in $(seq 1 60); do
+            if curl -fsS -o /dev/null --max-time 10 "${PROD_BASE_URL}/"; then
+              echo "Production is up (attempt $i)"
+              exit 0
+            fi
+            sleep 5
+          done
+          echo "Production did not become healthy in time" >&2
+          exit 1
+        env:
+          PROD_BASE_URL: ${{ secrets.PROD_BASE_URL }}
+```
+
+Implementation notes for the engineer:
+- Gitea Actions uses the GitHub-compatible `$GITHUB_OUTPUT` file for step outputs; if the runner image doesn't populate it, fall back to `$GITEA_OUTPUT` (check the runner's docs/output at first run).
+- The retag step is copied verbatim from the old `promote-prod` job except the SHA comes from the resolved input instead of `gitea.sha` — behaviour for the default (empty input on latest main) is identical to before.
+- If `docker buildx imagetools create` fails with "not found" for `${IMAGE}:${SHORT_SHA}`, the chosen commit predates the registry's retention or never built — the error message is the desired behaviour (refuse loudly).
+
+- [ ] **Step 2: YAML sanity check**
+
+Run: `python3 -c "import yaml; yaml.safe_load(open('.gitea/workflows/promote.yml')); print('yaml ok')"`
+Expected: `yaml ok`
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add .gitea/workflows/promote.yml
+git commit -m "ci: manual production promotion workflow with e2e-gate verification"
+```
+
+---
+
+### Task 3: Documentation — deploy model + runbook
+
+**Files:**
+- Modify: `docs/DEPLOY.md`
+- Modify: `claude.md` (the SDLC section)
+
+- [ ] **Step 1: Rewrite the flow description in `docs/DEPLOY.md`**
+
+Replace the staging→prod description with the new model (adapt to the file's existing structure; the substance to convey):
+
+```markdown
+## Deploy model
+
+1. **PR → main (first approval).** Branch-protected merge; PR checks
+   (typecheck, tests, builds, AI review) must pass.
+2. **Merge → staging (automatic).** Images are built once and tagged
+   `sha-<short>` + `staging`; the staging stack updates; Playwright
+   journeys in `e2e/` run against staging. A red e2e run means staging
+   is not fit for testing — fix forward before considering promotion.
+3. **Manual testing on staging.** stx.schoolcompare.co.uk. Note:
+   external `/api` is broken at the staging proxy — exercise API
+   endpoints from the host.
+4. **Promote → production (second approval).** Actions → "Promote to
+   Production (manual)" → Run workflow. Leave the SHA empty to promote
+   the latest main, or paste a specific commit SHA. The workflow
+   refuses commits whose staging e2e gate is not green, retags the
+   images `:prod` (keeping `:prod-previous`), and updates the prod
+   stack.
+
+### Promotion granularity
+
+Staging always runs the latest `main`. Promoting approves a *state of
+main*, not a single PR — if two PRs merged since the last promotion,
+they ship together. Test staging accordingly.
+
+### Rollback
+
+Re-run "Promote to Production (manual)" with the SHA of the last good
+commit (or retag manually: `docker buildx imagetools create -t
+<image>:prod <image>:prod-previous` for each of the three images, then
+POST the prod Portainer webhook).
+```
+
+- [ ] **Step 2: Update the SDLC bullet in `claude.md`**
+
+Replace the sentence "Merging to `main` deploys automatically: … retagged `:prod` and rolled out to production." with:
+
+```markdown
+- Merging to `main` deploys automatically **to staging only**: images
+  are built once, deployed to the staging Portainer stack, and verified
+  by the Playwright journeys in `e2e/`. Production is a second, manual
+  approval: the "Promote to Production (manual)" workflow in Gitea
+  Actions, run after testing the feature on staging. It refuses commits
+  whose staging e2e gate isn't green.
+```
+
+- [ ] **Step 3: Commit**
+
+```bash
+git add docs/DEPLOY.md claude.md
+git commit -m "docs: two-stage deploy model (staging auto, production manual)"
+```
+
+---
+
+### Task 4: PR + live validation
+
+- [ ] **Step 1: Push and open the PR** (Gitea API with credential-helper basic auth, as usual). PR body: the new model in three lines, the rollback recipe, and a warning that between merging this PR and its first promotion run, production receives no deployments (expected).
+
+- [ ] **Step 2: Validate after merge (human-in-the-loop):**
+1. Merge this PR → confirm the `Stage (build -> staging -> E2E gate)` run goes green and **no** production deployment happens (prod image digest unchanged: `docker buildx imagetools inspect <image>:prod` before/after, or check the Portainer prod stack's last-update time).
+2. Test something trivial on staging.
+3. Run "Promote to Production (manual)" with the SHA empty → confirm e2e verification passes, retag happens, prod becomes healthy.
+4. Negative test: run the promote workflow with a garbage SHA (e.g. `deadbeef1`) → confirm it fails at resolve/verify without touching `:prod`.
+
+- [ ] **Step 3: Update the ledger/memory** with the new deploy model so future sessions stop assuming auto-promotion.
+
+---
+
+## Out of scope / future options
+
+- Notifications when staging is ready for testing (Gitea can email on workflow completion; a webhook to ntfy/Matrix could be added later).
+- Restricting who can run the promote workflow: Gitea 1.26 runs `workflow_dispatch` with the permissions of the dispatching user; for a solo repo this is already effectively restricted.
+- The tag-driven fallback (`on: push: tags: promote-*`) if `workflow_dispatch` ever proves unreliable on the runner.
@@ -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.
@@ -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.)
@@ -38,6 +38,31 @@ default_args = {
    "retry_delay": timedelta(minutes=5),
 }

+# The backend caches the marts DataFrame at startup; after any rebuild the
+# cache must be invalidated or the API serves stale (or empty) data until the
+# container restarts.
+INVALIDATE_CACHE_CMD = """
+set -e
+BACKEND_URL="${BACKEND_URL:-http://backend:80}"
+ADMIN_KEY="${ADMIN_API_KEY:-changeme}"
+
+echo "Calling $BACKEND_URL/api/admin/reload ..."
+
+response=$(curl -s -o /tmp/reload_response.json -w "%{http_code}" \\
+    --connect-timeout 10 --max-time 120 \\
+    -X POST "$BACKEND_URL/api/admin/reload" \\
+    -H "X-API-Key: $ADMIN_KEY" \\
+    -H "Content-Type: application/json")
+
+echo "HTTP status: $response"
+cat /tmp/reload_response.json
+
+if [ "$response" != "200" ]; then
+    echo "ERROR: backend cache reload failed (HTTP $response)"
+    exit 1
+fi
+"""
+

 # ── Daily DAG (GIAS + downstream) ──────────────────────────────────────

@@ -91,7 +116,12 @@ print(f'Validation passed: {{count}} GIAS rows')
        bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
    )

-    extract_group >> validate_raw >> dbt_build >> sync_typesense
+    invalidate_cache = BashOperator(
+        task_id="invalidate_cache",
+        bash_command=INVALIDATE_CACHE_CMD,
+    )
+
+    extract_group >> validate_raw >> dbt_build >> sync_typesense >> invalidate_cache


 # ── Monthly DAG (Ofsted) ───────────────────────────────────────────────
@@ -121,7 +151,12 @@ with DAG(
        bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
    )

-    extract_ofsted >> dbt_build_ofsted >> sync_typesense_ofsted
+    invalidate_cache_ofsted = BashOperator(
+        task_id="invalidate_cache",
+        bash_command=INVALIDATE_CACHE_CMD,
+    )
+
+    extract_ofsted >> dbt_build_ofsted >> sync_typesense_ofsted >> invalidate_cache_ofsted


 # ── Annual DAG (EES: KS2, KS4, Census, Admissions) ───────────────────
@@ -153,7 +188,12 @@ with DAG(
        bash_command=f"cd {PIPELINE_DIR} && python scripts/sync_typesense.py",
    )

-    extract_ees_group >> dbt_build_ees >> sync_typesense_ees
+    invalidate_cache_ees = BashOperator(
+        task_id="invalidate_cache",
+        bash_command=INVALIDATE_CACHE_CMD,
+    )
+
+    extract_ees_group >> dbt_build_ees >> sync_typesense_ees >> invalidate_cache_ees


 # ── Annual DAG (IDACI Deprivation) ────────────────────────────────────
@@ -178,4 +218,9 @@ with DAG(
        bash_command=f"cd {PIPELINE_DIR}/transform && {DBT_BIN} build --profiles-dir . --target production --select stg_idaci+ fact_deprivation+",
    )

-    extract_idaci >> dbt_build_idaci
+    invalidate_cache_idaci = BashOperator(
+        task_id="invalidate_cache",
+        bash_command=INVALIDATE_CACHE_CMD,
+    )
+
+    extract_idaci >> dbt_build_idaci >> invalidate_cache_idaci
@@ -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()

@@ -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.
@@ -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))
-        dupes = len(mapping) - len({c for c, _ in mapping})
-        if dupes:
-            sys.exit(f"{code_col}: {dupes} codes map to multiple names — investigate before generating")
+        by_code: dict[int, set] = {}
+        for c, n in pairs.itertuples(index=False):
+            by_code.setdefault(int(c), set()).add(n)
+        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('"', '\\"')
@@ -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: "",
 }


@@ -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 %}
@@ -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,
@@ -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
@@ -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
@@ -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,
@@ -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,
@@ -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,
@@ -25,13 +25,20 @@ select
    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,
@@ -61,6 +68,9 @@ select
    ks4.progress_8_maths,
    ks4.progress_8_ebacc,
    ks4.progress_8_open,
+    ks4.progress_8_banding,
+    ks4.attainment_8_disadvantage_gap,
+    ks4.progress_8_disadvantage_gap,
    ks4.english_maths_strong_pass_pct,
    ks4.english_maths_standard_pass_pct,
    ks4.ebacc_entry_pct,
@@ -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.
@@ -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,
@@ -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
@@ -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
@@ -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,
@@ -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
@@ -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)
-        -- TODO: add rc_* columns to tap-uk-ofsted schema once CSV column names are confirmed
-        null::text as rc_safeguarding_met,
-        null::text as rc_inclusion,
-        null::text as rc_curriculum_teaching,
-        null::text as rc_achievement,
-        null::text as rc_attendance_behaviour,
-        null::text as rc_personal_development,
-        null::text as rc_leadership_governance,
-        null::text as rc_early_years,
-        null::text as rc_sixth_form,
+        -- Report Card fields (post-Nov 2025 framework), 5-point scale:
+        -- 1 Exceptional · 2 Strong standard · 3 Expected standard
+        -- · 4 Needs attention · 5 Urgent improvement
+        case lower(trim(nullif(rc_safeguarding_met, 'NULL')))
+            when 'met' then true
+            when 'not met' then false
+        end                                                               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,
+        -- 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
@@ -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,
Author	SHA1	Message	Date
TudorandClaude Fable 5	c44d54d17d	feat(api): compare school_info carries GIAS facts for the community section Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 23:46:12 +01:00
TudorandClaude Fable 5	c0f31a5941	feat(api): compare endpoint carries supplementary blocks, national averages and benchmarks PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m45s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 22s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 51s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 37s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 3m12s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:48:28 +01:00
TudorandClaude Fable 5	cec7941b44	feat(api): computed state-school benchmarks Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:45:30 +01:00
TudorandClaude Fable 5	dbaa15c099	feat(api): expose progress CIs, KS4 banding/gaps, admissions detail, report-card labels Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:44:29 +01:00
TudorandClaude Fable 5	b5b47ca135	feat(api): Ofsted report-card labels and provider-page URL Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:42:08 +01:00
TudorandClaude Fable 5	0c89b2c34e	feat(api): map compare-foundation mart columns Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 18:40:55 +01:00
TudorandClaude Fable 5	4bf90b5f09	feat(pipeline): thread compare-foundation columns through fact_performance Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 15:46:43 +01:00
TudorandClaude Fable 5	17b4498c80	docs: plan for compare API enrichment Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 15:46:04 +01:00
tudor	3754947fd6	Merge pull request 'feat(pipeline): compare-screen data foundation — raw→marts promotions, national averages, Ofsted report cards' (#32 ) from feat/compare-data-foundation into main Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 12s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 48s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m16s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 41s Details Reviewed-on: #32	2026-07-13 12:52:11 +00:00
tudor	9799ad9b43	Merge pull request 'ci: two-stage deploy — staging automatic, production behind a manual approval' (#33 ) from chore/staged-prod-promotion into main Stage (build -> staging -> E2E gate) / Build Backend (FastAPI) (push) Successful in 13s Details Stage (build -> staging -> E2E gate) / Build Frontend (Next.js) (push) Successful in 56s Details Stage (build -> staging -> E2E gate) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 13s Details Stage (build -> staging -> E2E gate) / Deploy to Staging (push) Successful in 1s Details Stage (build -> staging -> E2E gate) / E2E Journeys against Staging (push) Successful in 39s Details Reviewed-on: #33	2026-07-13 12:30:19 +00:00
TudorandClaude Fable 5	d5cd0abfee	ci: re-run PR checks (AI review job errored without posting findings) PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m38s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 45s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 46s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 5m50s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 13:15:52 +01:00
TudorandClaude Fable 5	6877abedeb	fix(ci): harden promote workflow — env-isolated untrusted input, main-ancestry check, concurrency guard PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m38s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 44s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 10s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 2m39s Details Addresses the AI review findings on PR #33: - severe: the workflow_dispatch sha input was interpolated directly into the run script (shell injection with REGISTRY_TOKEN + prod webhook in scope). It now reaches the shell only via env, is rejected if it starts with '-', and is resolved locally with git rev-parse. - minor: the resolved sha must be a 40-hex ancestor of origin/main — non-main refs are refused explicitly instead of implicitly. - minor: a prod-promotion concurrency group serialises promotions (cancel-in-progress: false). Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 13:14:28 +01:00
TudorandClaude Fable 5	436ec6151b	fix(pipeline): thread KS2 progress CI columns through the legacy union and lineage model PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m39s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 44s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 46s Details PR Checks / AI Code Review (Claude) (pull_request) Failing after 28s Details The AI review gate caught that stg_ees_ks2's 7 new columns broke the positional UNION ALL with stg_legacy_ks2 in int_ks2_with_lineage, and that the lineage CTEs never emitted them (same class of bug fixed for KS4 in `34a5de2`). Legacy gets typed null placeholders at matching positions; both lineage CTEs pass the columns through. 45/45 columns verified name-identical in order across both union branches. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:42:40 +01:00
TudorandClaude Fable 5	2b563cc0bf	docs: two-stage deploy model (staging auto, production manual) PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m36s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 10s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 43s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 10s Details PR Checks / AI Code Review (Claude) (pull_request) Failing after 2m10s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:38:37 +01:00
TudorandClaude Fable 5	75e92dc7f5	ci: manual production promotion workflow with e2e-gate verification Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:36:25 +01:00
TudorandClaude Fable 5	7499e7f557	ci: stop deploy pipeline at staging; production promotion becomes manual Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:35:39 +01:00
TudorandClaude Fable 5	dd0ff7d0c2	docs: plan for staged production promotion Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:34:32 +01:00
TudorandClaude Fable 5	6f925abf6b	fix(pipeline): harden banding against EES sentinels; diagnostic cleanups PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m40s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 12s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 41s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 46s Details PR Checks / AI Code Review (Claude) (pull_request) Failing after 3m17s Details Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-13 08:22:34 +01:00
TudorandClaude Fable 5	03518520f8	fix(pipeline): unknown safeguarding values parse to NULL, not "not met" Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:17:11 +01:00
TudorandClaude Fable 5	c2ed002118	docs(pipeline): record Task 7 report-card grade value sample + collision check Evidence trail for the rc_* mapping in the prior commit: real value_counts() over the 7 MI report-card columns, confirming the 5-value grade vocabulary and that 'Achievement'/'Safeguarding standards' match by exact string only (no legacy OEIF column accidentally consumed). Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:13:03 +01:00
TudorandClaude Fable 5	02084e427c	feat(pipeline): extract Ofsted report-card judgements (rc_* columns) Wires the tap TODO in stg_ofsted_inspections.sql: maps the 7 confirmed report-card MI columns (Safeguarding standards, Inclusion, Curriculum and teaching, Achievement, Attendance and behaviour, Personal development and wellbeing, Leadership and governance) into rc_* fields, parsed via the new parse_report_card_grade macro against real sampled grade values (Exceptional/Strong standard/Expected standard/Needs attention/Urgent improvement). rc_safeguarding_met becomes boolean from Met/Not met. rc_early_years/rc_sixth_form have no MI column yet and are intentionally omitted from COLUMN_PRIORITY, staying NULL. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:12:33 +01:00
TudorandClaude Fable 5	bee63a7836	docs(spec): 2021/22 school-level KS2 is a permanent DfE source gap, not a pipeline task Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:10:05 +01:00
TudorandClaude Fable 5	fc21783298	chore(pipeline): verify 2021/22 legacy KS2 archive compatibility DfE never published school-level KS2 2021/22 data publicly (confirmed via EES release notes and by walking the Compare School Performance download wizard, which has no ks2 checkbox for 2021-2022, same as the COVID-cancelled 2020-2021 year). No archive exists to verify column headers against or upload to the filebrowser; Task 6 is blocked at the source-data level. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:08:36 +01:00
TudorandClaude Fable 5	ccd8e73fe8	fix(pipeline): include 2015/16 national averages Widen the year filter in stg_ees_ks2_national.sql from >= 201617 to >= 201516 so the England national-averages line no longer starts a year late; the catalogue CSV has a real, comparable 201516 row (2015/16 was the first year of the current expected-standard tests, so it's the correct floor). GPS/science/scaled-score national columns confirmed present at source with correct mapping; prod NULLs are stale raw data, backfilled by the next extract run. No _KS2_NATIONAL_COL_MAP change accompanies this fix. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:02:28 +01:00
TudorandClaude Fable 5	5f1b6adb44	fix(pipeline): align SEN column order across KS4 union branches int_ks4_with_lineage.sql unions stg_ees_ks4 and stg_legacy_ks4 via `select *`, which PostgreSQL aligns positionally. stg_legacy_ks4 listed sen_support_pct before sen_ehcp_pct while stg_ees_ks4 lists sen_ehcp_pct before sen_support_pct, swapping the two values for legacy-sourced rows in marts.fact_ks4_performance. Reordered stg_legacy_ks4's final select to match. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 22:00:15 +01:00
TudorandClaude Fable 5	34a5de2687	feat(pipeline): Progress 8 banding and KS4 disadvantage gaps in marts Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 21:55:34 +01:00
Tudor	af43b291e7	feat(pipeline): KS2 progress confidence intervals and writing working-towards	2026-07-12 21:34:59 +01:00
Tudor	5a94f470e1	feat(pipeline): admissions preference breakdown and cross-LA demand in marts	2026-07-12 21:30:50 +01:00
Tudor	0fe1ea0d6a	chore(pipeline): diagnostic for compare-screen data gaps	2026-07-12 21:25:00 +01:00
TudorandClaude Fable 5	297bdbd12e	docs: compare-screen redesign spec, expert review, and data-foundation plan Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB	2026-07-12 21:19:53 +01:00
tudor	58e90fef61	Merge pull request 'fix(api): blank-name GIAS sentinel codes map to empty string, not Unknown(n)' (#27 ) from fix/gias-blank-name-codes into main Deploy (staging -> E2E gate -> production) / Build Backend (FastAPI) (push) Successful in 20s Details Deploy (staging -> E2E gate -> production) / Build Frontend (Next.js) (push) Successful in 54s Details Deploy (staging -> E2E gate -> production) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m5s Details Deploy (staging -> E2E gate -> production) / Deploy to Staging (push) Successful in 1s Details Deploy (staging -> E2E gate -> production) / E2E Journeys against Staging (push) Successful in 41s Details Deploy (staging -> E2E gate -> production) / Promote to Production (push) Successful in 9s Details Reviewed-on: #27	2026-07-09 21:21:08 +00:00
TudorandClaude Fable 5	3710529e49	fix(api): map blank-name GIAS sentinel codes to empty string, not Unknown PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m40s Details PR Checks / Backend Smoke (pull_request) Successful in 7s Details PR Checks / Build Backend (no push) (pull_request) Successful in 20s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 54s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 37s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 2m35s Details ReligiousCharacter 99 (~4k schools) and AdmissionsPolicy 9 (~5.6k) carry a code with a blank name in the GIAS CSV; the generator skipped them so they hit the Unknown(<code>) path — wrongly triggering the Faith-priority tag and polluting filters. Blank-only codes now map to "" (byte-identical to the old name pipeline); accepted_values lists extended to match the seed. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>	2026-07-09 22:04:53 +01:00
tudor	159207c6f5	Merge pull request 'fix(pipeline): add the missing backend cache-invalidation step to the data DAGs' (#26 ) from fix/daily-dag-cache-invalidation into main Deploy (staging -> E2E gate -> production) / Build Backend (FastAPI) (push) Successful in 13s Details Deploy (staging -> E2E gate -> production) / Build Frontend (Next.js) (push) Successful in 51s Details Deploy (staging -> E2E gate -> production) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 1m3s Details Deploy (staging -> E2E gate -> production) / Deploy to Staging (push) Successful in 1s Details Deploy (staging -> E2E gate -> production) / E2E Journeys against Staging (push) Successful in 43s Details Deploy (staging -> E2E gate -> production) / Promote to Production (push) Successful in 9s Details Reviewed-on: #26	2026-07-09 20:41:42 +00:00
TudorandClaude Fable 5	d677b54533	fix(pipeline): cache invalidation for IDACI DAG too; curl timeouts PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m37s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 18s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 48s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 35s Details PR Checks / AI Code Review (Claude) (pull_request) Successful in 1m40s Details Addresses AI-review findings: the annual IDACI DAG also rebuilds a mart (fact_deprivation) and needs the reload; curl gets connect/max timeouts so an unreachable backend fails fast instead of hanging the task. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>	2026-07-09 21:26:45 +01:00
TudorandClaude Fable 5	c353e36072	fix(pipeline): actually invalidate the backend cache after data rebuilds PR Checks / Frontend Typecheck + Tests (pull_request) Successful in 9m40s Details PR Checks / Backend Smoke (pull_request) Successful in 6s Details PR Checks / Build Backend (no push) (pull_request) Successful in 16s Details PR Checks / Build Frontend (no push) (pull_request) Successful in 51s Details PR Checks / Build Pipeline (no push) (pull_request) Successful in 36s Details PR Checks / AI Code Review (Claude) (pull_request) Failing after 1m20s Details The daily/monthly/annual DAG docstring promised an Invalidate Cache step that never existed — after a marts rebuild the backend kept serving its startup-cached (possibly empty) DataFrame until a container restart. Add a POST /api/admin/reload task at the end of each pipeline DAG, mirroring the sitemap DAG's admin-call pattern. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>	2026-07-09 21:12:01 +01:00
tudor	d9223a6d6e	Merge pull request 'fix(api): legacy name-column fallback when marts predate the GIAS code migration' (#25 ) from fix/gias-legacy-fallback into main Deploy (staging -> E2E gate -> production) / Build Backend (FastAPI) (push) Successful in 19s Details Deploy (staging -> E2E gate -> production) / Build Frontend (Next.js) (push) Successful in 54s Details Deploy (staging -> E2E gate -> production) / Build Pipeline (Meltano + dbt + Airflow) (push) Successful in 12s Details Deploy (staging -> E2E gate -> production) / Deploy to Staging (push) Successful in 1s Details Deploy (staging -> E2E gate -> production) / E2E Journeys against Staging (push) Failing after 4m45s Details Deploy (staging -> E2E gate -> production) / Promote to Production (push) Has been skipped Details Reviewed-on: #25	2026-07-09 19:13:55 +00:00