school_compare/docs/superpowers/plans/2026-07-16-compare-mustfix-final-review.md

Compare Screen Must-Fix (Final Expert Review) 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: Fix the five promotion-blocking findings from the expert's final staging review: (1) blank all-secondary compare view, (2) report cards dated with pre-Nov-2025 legacy inspection dates, (3) FSM chip benchmarked against the wrong measure, (4) KS4 "national averages" that are dataset means presented as official DfE figures, (5) factually wrong "DfE didn't publish 2021/22" footnote.

Architecture: One branch/PR touching all three layers. Pipeline: a new tap field carries the report-card inspection's own date; a new EES stream ingests official KS4 national headlines; a new census-benchmarks mart replaces junk KS2-derived context medians. Backend: serialize the new fields, stop mislabelling computed KS4 means as official. Frontend: fix the phase-detection effect that leaves all-secondary comparisons stuck on an empty "primary" tab, date report cards correctly, drop the FSM→disadvantaged fallback, fix the footnote copy.

Tech Stack: Meltano/Singer taps (Python), dbt-postgres, FastAPI/SQLAlchemy/pandas, Next.js app router + Jest, Playwright e2e.

Global Constraints

• Never push to main; work on branch fix/compare-final-review-mustfix, open a PR. Never trigger the "Promote to Production (manual)" workflow — promotion is exclusively the human's call.
• User-facing behaviour changes must extend the e2e/ journeys in the same PR (they gate staging fitness and promotability).
• All user-facing copy on the compare screen comes verbatim from docs/superpowers/specs/mockups/compare-desktop.html / compare-mobile.html — except where this plan explicitly changes copy to fix a factual error (Task 6); the spec/mockup gets the same wording in the same commit.
• Benchmark provenance house style: official figures = "England average"; computed figures = "state-school average (computed from our dataset)".
• A report card must NEVER be displayed with a pre-November-2025 date. Report cards exist only from November 2025.
• Backend tests: uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q (repo root; there is no local pytest).
• dbt: cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . (never bare dbt — the Fusion binary shadows dbt-postgres).
• Frontend: cd nextjs-app && npx tsc --noEmit && npm test (run tsc un-piped so exit codes are not masked).
• Do NOT start a local server to test the application (CLAUDE.md).
• Commits end with the Claude Code Co-Authored-By + Claude-Session trailers used on this branch's history.

Root-Cause Evidence (verified 2026-07-16, do not re-derive)

• Finding 1: nextjs-app/components/ComparisonView.tsx:164-176 — the auto-phase effect returns early when selectedSchools.length === 0 (basket hydrates a beat after mount) and its dep array is only [comparisonData], so it never re-fires; comparePhase stays 'primary', activeSchools is empty, the page renders "No primary schools in your comparison" (a11y snapshot confirmed). No console errors — not a crash.
• Finding 2: In the Ofsted MI CSV (Management_information_-_state-funded_schools_-_latest_inspections_as_at_31_May_2026.csv) the report-card grade columns (cols 38–55, "Safeguarding standards", "Inclusion", …) belong to the latest full inspection block whose date is col 30 "Inspection start date" (Barclay 138690: 03/02/2026). The tap's inspection_date COLUMN_PRIORITY matches col 60 "Inspection start date of latest OEIF graded inspection" first (the legacy date; NULL for Barclay, so stg coalesces to the 2021 ungraded date). The rc data is real Ofsted data, not fabricated — it is mis-dated. Also discover_csv_url() returns matches[0] = the oldest (2017) link on the GOV.UK page; staging works only because mi_url is set in the environment. Staging raw is stale for at least Watford Grammar 136276 (staging shows rc grades; the current MI file has all rc columns NULL for it) — a fresh extract fixes that via upsert on (urn, inspection_date).
• Finding 3: nextjs-app/components/compare/CompareCommunity.tsx:36 — bench?.fsm_pct ?? bench?.disadvantaged_pct falls back across definitions. benchmarks.primary.fsm_pct is null because compute_benchmarks (backend/data_loader.py:528) medians the performance df, which has no fsm_pct (school FSM comes from census.fsm_pct = fact_pupil_characteristics). disadvantaged_pct / eal_pct are KS2-only columns, so the "secondary" medians (50.0 / 10.0) are computed over the few all-through schools' KS2 rows — junk.
• Finding 4: fact_ks4_national_averages.sql computes unweighted school means (A8 38.94 vs official 46.0; national P8 −0.27, impossible). Official series exists on EES: data-set 1b649e16-01e8-435b-a814-56be2faf9054 ("National characteristics summary data", KS4 performance publication), CSV endpoint same pattern as the KS2 national stream, national level, 2018/19→2024/25, establishment_type_group = 'All state-funded', breakdown_topic = 'Total', breakdown = 'Total'. Verified values: 2024/25 A8 46.0, P8 z (not published — no KS2 baseline for that cohort), EM 9-5 45.4%, EBacc entry 40.5%. It has no gcse_91_percent column.
• Finding 5: ComparisonChart.tsx:245-246 claims "DfE didn't publish school-level figures for 2021/22". False — DfE published school-level KS2 for 2021/22 in Dec 2022; spec §8.1 itself lists loading it as a pipeline task. The honest claim is that the figures aren't in our dataset.

---

Task 1: All-secondary comparison renders (phase-detection fix)

Files:

• Modify: nextjs-app/components/ComparisonView.tsx:176
• Create: nextjs-app/__tests__/components/ComparisonView.phase.test.tsx
• Modify: e2e/tests/journeys.spec.ts (add helper + journey after the existing twoPrimaryUrns helper / primary compare journey)

Interfaces:

• Consumes: existing ComparisonView props (initialData, initialUrns, metrics, selectedMetric), ComparisonProvider.

• Produces: no API changes; the auto-phase effect re-runs when the basket hydrates.

• Step 1: Write the failing Jest test

Create nextjs-app/__tests__/components/ComparisonView.phase.test.tsx (mirrors the mock setup of ComparisonView.refresh.test.tsx):

/**
 * Regression: an all-secondary comparison must render the secondary sections.
 *
 * The basket hydrates from the URL a beat after mount, so the auto-phase
 * effect must re-run once selectedSchools arrives — with deps of only
 * [comparisonData] it fired once against an empty basket, bailed, and the
 * page stayed on an empty "primary" tab ("No primary schools in your
 * comparison") even though all schools were secondary.
 */

import { render, screen, waitFor } from '@testing-library/react';

import { ComparisonView } from '@/components/ComparisonView';
import { ComparisonProvider } from '@/context/ComparisonProvider';
import type { ComparisonData, School } from '@/lib/types';

const fetchComparison = jest.fn();
jest.mock('@/lib/api', () => ({
  fetchComparison: (...args: unknown[]) => fetchComparison(...args),
}));
jest.mock('@/lib/analytics', () => ({ track: jest.fn() }));

function secondarySchool(urn: number, name: string): School {
  return {
    urn,
    school_name: name,
    local_authority: 'Testshire',
    school_type: 'Academy converter',
    attainment_8_score: 55,
    phase: 'Secondary',
  } as School;
}

function data(urn: number, name: string): ComparisonData {
  return {
    school_info: secondarySchool(urn, name),
    yearly_data: [{ year: 202425, attainment_8_score: 55 }] as ComparisonData['yearly_data'],
    ofsted: null,
    census: null,
    admissions: null,
    admissions_history: [],
    deprivation: null,
  };
}

const INITIAL_DATA = {
  '300': data(300, 'Gamma High'),
  '400': data(400, 'Delta Academy'),
};

test('an all-secondary comparison renders the sections, not an empty primary tab', async () => {
  render(
    <ComparisonProvider>
      <ComparisonView
        initialData={INITIAL_DATA}
        initialNationalAverages={{
          year: 202425,
          primary: {},
          secondary: { attainment_8_score: 46 },
          by_year: [],
        }}
        initialBenchmarks={undefined}
        initialUrns={[300, 400]}
        metrics={[]}
        selectedMetric="attainment_8_score"
      />
    </ComparisonProvider>,
  );

  await waitFor(() => {
    expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
  });
  expect(screen.getAllByText('Gamma High').length).toBeGreaterThan(0);
  expect(screen.queryByText(/No primary schools in your comparison/)).toBeNull();
  expect(fetchComparison).not.toHaveBeenCalled();
});

• Step 2: Run it to verify it fails

Run: cd nextjs-app && npx jest __tests__/components/ComparisonView.phase.test.tsx Expected: FAIL — "No primary schools in your comparison" is rendered / "At a glance" never appears.

• Step 3: Fix the effect dependencies

In nextjs-app/components/ComparisonView.tsx, the auto-phase effect currently ends:

  }, [comparisonData]); // eslint-disable-line react-hooks/exhaustive-deps

Change to:

    // selectedSchools is a dep because the basket hydrates after mount: the
    // first run sees an empty basket and bails, so it must re-fire when the
    // schools arrive. primarySchools/secondarySchools/metrics/selectedMetric
    // are intentionally omitted (derived or would cause loops).
  }, [comparisonData, selectedSchools]); // eslint-disable-line react-hooks/exhaustive-deps

(phaseLockedByUser still suppresses re-detection after a manual tab click; re-running with unchanged inputs sets the same state, which React treats as a no-op.)

• Step 4: Run the new test and the existing suite

Run: cd nextjs-app && npx tsc --noEmit && npm test Expected: PASS, including ComparisonView.refresh.test.tsx (the refresh regression must stay green).

• Step 5: Add the e2e secondary journey

In e2e/tests/journeys.spec.ts, add below twoPrimaryUrns:

async function twoSecondaryUrns(page: Page): Promise<[string, string]> {
  const res = await page.request.get('/api/schools?search=school&per_page=100');
  expect(res.ok()).toBeTruthy();
  const body = await res.json();
  const urns: string[] = (body.schools ?? [])
    .filter((s: { phase?: string; attainment_8_score?: number | null }) =>
      s.phase === 'Secondary' && s.attainment_8_score != null,
    )
    .map((s: { urn: number }) => String(s.urn));
  expect(urns.length).toBeGreaterThanOrEqual(2);
  return [urns[0], urns[1]];
}

(If /api/schools list rows lack attainment_8_score, filter on s.phase === 'Secondary' only — check the response first.) Then add a journey test next to the primary compare journey:

test('comparing two secondary schools renders the secondary sections', async ({ page }) => {
  const [urn0, urn1] = await twoSecondaryUrns(page);

  await page.goto(`/compare?urns=${urn0},${urn1}`);
  await expect(page.locator(`a[href*="${urn0}"]`).first()).toBeVisible({ timeout: 15_000 });

  // The parent-first sections must render — this page was completely blank
  // for all-secondary baskets (expert review must-fix #1).
  await expect(page.getByRole('heading', { name: 'At a glance' })).toBeVisible();
  await expect(page.getByRole('heading', { name: 'Ofsted inspection' })).toBeVisible();
  // A KS4 measure proves the secondary academics variant rendered.
  await expect(page.getByText(/Attainment 8/i).first()).toBeVisible();
  await expect(page.getByText(/No primary schools in your comparison/)).toHaveCount(0);
});

• Step 6: Commit

git add nextjs-app/components/ComparisonView.tsx nextjs-app/__tests__/components/ComparisonView.phase.test.tsx e2e/tests/journeys.spec.ts
git commit -m "fix(compare): render all-secondary comparisons — re-run phase detection after basket hydration"

---

Task 2: Report-card inspection date through the pipeline

Files:

• Modify: pipeline/plugins/extractors/tap-uk-ofsted/tap_uk_ofsted/tap.py (COLUMN_PRIORITY, schema, discover_csv_url)
• Modify: pipeline/transform/models/staging/stg_ofsted_inspections.sql
• Modify: pipeline/transform/models/intermediate/int_ofsted_latest.sql
• Modify: pipeline/transform/models/marts/fact_ofsted_inspection.sql
• Modify: pipeline/transform/models/marts/_marts_schema.yml (add column doc if other fact_ofsted columns are documented there)

Interfaces:

• Consumes: MI CSV column Inspection start date (the latest full inspection = the report-card inspection in the renewed framework; NULL when a school's only inspections are legacy OEIF/ungraded — verified for Watford Grammar).

• Produces: marts.fact_ofsted_inspection.rc_inspection_date (DATE, null unless the row carries report-card grades). Task 3 depends on this exact column name.

• Step 1: Add the tap field

In tap.py COLUMN_PRIORITY, after the rc_sixth_form entry, add:

    # Date of the latest FULL inspection — in the renewed framework this is
    # the report-card inspection's own start date (col "Inspection start
    # date"), distinct from the legacy OEIF graded/ungraded dates above.
    "rc_inspection_date": ["Inspection start date"],

and in the stream schema, next to the other rc properties:

        th.Property("rc_inspection_date", th.StringType),

Note: inspection_date's own priority list also contains "Inspection start date" as a lower-priority candidate — that stays; in renewed-framework files the higher-priority OEIF column exists so they map to different columns, and in legacy files both map to the same column but rc grades are absent, and staging nulls rc_inspection_date in that case (Step 3).

• Step 2: Fix discover_csv_url to pick the newest file, not matches[0]

The GOV.UK page lists 2017 files first; matches[0] is a 2017 CSV. Replace the body of discover_csv_url() to date-sort the latest_inspections_as_at links, mirroring discover_independent_csv_url:

def discover_csv_url() -> str | None:
    """Scrape GOV.UK page to find the latest MI CSV download link.

    The page lists a decade of monthly files, oldest first — take the
    newest 'latest inspections as at <date>' link by parsing its date,
    never matches[0].
    """
    resp = requests.get(GOV_UK_PAGE, timeout=30)
    resp.raise_for_status()
    csv_links = re.findall(
        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.csv)"',
        resp.text,
    )

    months = {
        'january': 1, 'february': 2, 'march': 3, 'april': 4, 'may': 5, 'june': 6,
        'july': 7, 'august': 8, 'september': 9, 'october': 10, 'november': 11, 'december': 12,
        'jan': 1, 'feb': 2, 'mar': 3, 'apr': 4, 'jun': 6,
        'jul': 7, 'aug': 8, 'sep': 9, 'oct': 10, 'nov': 11, 'dec': 12,
    }
    parsed_links = []
    for link in csv_links:
        normalized = link.lower().replace('-', '_')
        if 'latest_inspections_as_at' not in normalized:
            continue
        match = re.search(r'as_at_(\d{1,2})_([a-z]+)_(\d{4})', normalized)
        if match:
            day, month_str, year = match.groups()
            month = months.get(month_str)
            if month:
                try:
                    parsed_links.append((datetime(int(year), month, int(day)), link))
                except ValueError:
                    continue
    parsed_links.sort(reverse=True)
    if parsed_links:
        return parsed_links[0][1]
    if csv_links:
        return csv_links[-1]
    matches = re.findall(
        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.ods)"',
        resp.text,
    )
    return matches[0] if matches else None

(mi_url config still wins when set — self.config.get("mi_url") or discover_csv_url() is unchanged.)

• Step 3: Parse and guard the date in staging

In stg_ofsted_inspections.sql, inside the renamed CTE after the rc_sixth_form line, add:

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

and replace the final select:

select
    *,
    case
        when rc_safeguarding_met is not null
          or rc_inclusion is not null
          or rc_curriculum_teaching is not null
          or rc_achievement is not null
          or rc_attendance_behaviour is not null
          or rc_personal_development is not null
          or rc_leadership_governance is not null
        then rc_inspection_date_raw
    end as rc_inspection_date
from renamed
where inspection_date is not null

• Step 4: Propagate through int + mart

Add rc_inspection_date, to the explicit column lists of int_ofsted_latest.sql and fact_ofsted_inspection.sql (after rc_sixth_form). Do NOT propagate rc_inspection_date_raw.

• Step 5: Parse-check dbt

Run: cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . Expected: parse OK, no compilation errors.

• Step 6: Commit

git add pipeline/plugins/extractors/tap-uk-ofsted pipeline/transform/models
git commit -m "feat(pipeline): carry the report-card inspection's own date; pick newest MI file in discovery"

---

Task 3: Report-card date in the API and UI

Files:

• Modify: backend/models.py (FactOfstedInspection)
• Modify: backend/data_loader.py (_ofsted_block)
• Test: backend/tests/test_supplementary_enrichment.py (extend the existing _ofsted_block tests)
• Modify: nextjs-app/lib/types.ts (OfstedInspection)
• Modify: nextjs-app/components/compare/CompareOfsted.tsx ("Inspected" measure)
• Test: nextjs-app/__tests__/components/CompareOfsted.test.tsx

Interfaces:

• Consumes: marts.fact_ofsted_inspection.rc_inspection_date (Task 2).

• Produces: API ofsted.rc_inspection_date: string | null (ISO date). UI rule: report-card displays are dated with rc_inspection_date only; when null, show "—" (never the legacy date).

• Step 1: Failing backend test

In backend/tests/test_supplementary_enrichment.py, alongside the existing _ofsted_block tests, add (reuse the file's existing fake-row helper/style):

def test_ofsted_block_carries_rc_inspection_date():
    o = _fake_ofsted_row(  # use this file's existing fake/stub construction
        overall_effectiveness=None,
        ungraded_grade=2,
        rc_achievement=1,
        rc_inspection_date=date(2026, 2, 3),
        inspection_date=date(2021, 10, 7),
    )
    block = _ofsted_block(o, 138690)
    assert block["rc_inspection_date"] == "2026-02-03"
    # The legacy inspection date is still present, unchanged.
    assert block["inspection_date"] == "2021-10-07"


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

Run: uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests/test_supplementary_enrichment.py -q Expected: FAIL (KeyError / AttributeError on rc_inspection_date).

• Step 2: Backend implementation

backend/models.py, in FactOfstedInspection after rc_sixth_form:

    # Start date of the report-card inspection itself (renewed framework,
    # Nov 2025+). Null for rows without report-card grades.
    rc_inspection_date = Column(Date)

backend/data_loader.py _ofsted_block, after the "inspection_date" entry:

        "rc_inspection_date": (
            o.rc_inspection_date.isoformat()
            if getattr(o, "rc_inspection_date", None)
            else None
        ),

(getattr default keeps old test stubs working.) Run the backend suite; expected: PASS.

• Step 3: Failing frontend test

nextjs-app/lib/types.ts, in OfstedInspection, after inspection_date:

  /** Start date of the report-card inspection itself (Nov 2025+); null otherwise. */
  rc_inspection_date?: string | null;

In nextjs-app/__tests__/components/CompareOfsted.test.tsx, add to the existing suite (reusing its fixture style):

it('dates a report card with the report-card inspection date, never the legacy date', () => {
  const ofsted = reportCardOfsted({
    inspection_date: '2021-10-07',
    rc_inspection_date: '2026-02-03',
  });
  render(<CompareOfsted schools={[schoolFixture]} data={{ [String(schoolFixture.urn)]: { ...dataFixture, ofsted } }} />);
  expect(screen.getByText(/3 Feb 2026/)).toBeInTheDocument();
  expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
  expect(screen.queryByText('4+ years ago')).toBeNull();
});

it('shows an em dash when a report card has no rc_inspection_date yet', () => {
  const ofsted = reportCardOfsted({ inspection_date: '2021-10-07', rc_inspection_date: null });
  render(<CompareOfsted schools={[schoolFixture]} data={{ [String(schoolFixture.urn)]: { ...dataFixture, ofsted } }} />);
  expect(screen.getByText('—')).toBeInTheDocument();
  expect(screen.queryByText(/7 Oct 2021/)).toBeNull();
});

(reportCardOfsted = the file's existing report-card fixture builder, or build inline matching its other tests.) Run just this file; expected: FAIL.

• Step 4: Frontend implementation

In CompareOfsted.tsx, replace the body of the "Inspected" measure's map:

        {schools.map((school, i) => {
          const ofsted = data[String(school.urn)]?.ofsted;
          // A report card is dated by its OWN inspection date. The legacy
          // inspection_date belongs to an older inspection and must never
          // be shown against a report card (report cards exist only from
          // Nov 2025).
          const dateIso =
            displays[i].kind === 'report_card'
              ? ofsted?.rc_inspection_date ?? null
              : ofsted?.inspection_date ?? null;
          const age = yearsSince(dateIso);
          return (
            <Cell key={school.urn} school={school} index={i}>
              {formatInspectionDate(dateIso)}{' '}
              {age != null && age > 4 && <Chip tone="neutral">4+ years ago</Chip>}
            </Cell>
          );
        })}

• Step 5: Run frontend checks

Run: cd nextjs-app && npx tsc --noEmit && npm test Expected: PASS.

• Step 6: Commit

git add backend/models.py backend/data_loader.py backend/tests nextjs-app/lib/types.ts nextjs-app/components/compare/CompareOfsted.tsx nextjs-app/__tests__/components/CompareOfsted.test.tsx
git commit -m "fix(compare): date report cards with their own inspection date, never the legacy one"

---

Task 4: Census-based context benchmarks; kill the FSM fallback

Files:

• Create: pipeline/transform/models/marts/fact_census_benchmarks.sql
• Modify: pipeline/transform/models/marts/_marts_schema.yml
• Modify: backend/models.py (new CensusBenchmark model)
• Modify: backend/data_loader.py (compute_benchmarks)
• Modify: backend/app.py (compare endpoint call site, only if the signature change requires it)
• Test: backend/tests/test_benchmarks.py
• Modify: nextjs-app/components/compare/CompareCommunity.tsx:36
• Test: nextjs-app/__tests__/lib/compareLogic.test.ts or the community section's existing test home (add a fallback-removal test where the FSM chip logic is tested today)

Interfaces:

• Consumes: marts.fact_pupil_characteristics (urn, year, phase_type_grouping, total_pupils, fsm_pct, eal_pct).

• Produces: marts.fact_census_benchmarks — one row per phase ('primary'/'secondary'), columns phase, year, fsm_pct, eal_pct, median_pupils. fsm_pct/eal_pct are pupil-weighted means (so they approximate the national pupil-level rate, answering the expert's objection to school-median anchors). API benchmarks.{primary,secondary} keeps its existing keys; fsm_pct/eal_pct/median_pupils now come from this mart; disadvantaged_pct becomes primary-only (the KS2-column median was junk for secondary).

• Step 1: dbt mart

Create pipeline/transform/models/marts/fact_census_benchmarks.sql:

{{ config(materialized='table') }}

-- Mart: state-school context benchmarks from the pupil census — one row per
-- phase, latest census year. Computed at import time (never per request).
-- fsm_pct / eal_pct are pupil-weighted means, i.e. "what % of pupils", not
-- "the median school" — this matches how DfE quotes national FSM/EAL rates.
-- Consumers must label these "state-school average (computed from our
-- dataset)" (spec §8.6), never "England average".

with latest as (
    select max(year) as year from {{ ref('fact_pupil_characteristics') }}
),

classified as (
    select
        case
            when p.phase_type_grouping ilike '%primary%'   then 'primary'
            when p.phase_type_grouping ilike '%secondary%' then 'secondary'
        end as phase,
        p.total_pupils,
        p.fsm_pct,
        p.eal_pct,
        l.year
    from {{ ref('fact_pupil_characteristics') }} p
    join latest l on p.year = l.year
    where p.total_pupils is not null and p.total_pupils > 0
)

select
    phase,
    max(year) as year,
    round((sum(fsm_pct * total_pupils) filter (where fsm_pct is not null)
        / nullif(sum(total_pupils) filter (where fsm_pct is not null), 0))::numeric, 1) as fsm_pct,
    round((sum(eal_pct * total_pupils) filter (where eal_pct is not null)
        / nullif(sum(total_pupils) filter (where eal_pct is not null), 0))::numeric, 1) as eal_pct,
    round(percentile_cont(0.5) within group (order by total_pupils))::integer as median_pupils
from classified
where phase is not null
group by phase

Add a fact_census_benchmarks entry to _marts_schema.yml in the file's existing style (name + description; column tests only if sibling marts have them).

Run: cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . — expected PASS.

• Step 2: Failing backend test

In backend/tests/test_benchmarks.py add:

def test_benchmarks_use_census_mart_for_context(monkeypatch):
    census = {
        "primary": {"year": 202425, "fsm_pct": 25.3, "eal_pct": 21.8, "median_pupils": 240},
        "secondary": {"year": 202425, "fsm_pct": 24.1, "eal_pct": 18.9, "median_pupils": 980},
    }
    result = compute_benchmarks(_sample_df(), census_benchmarks=census)
    assert result["primary"]["fsm_pct"] == 25.3
    assert result["secondary"]["eal_pct"] == 18.9
    assert result["secondary"]["median_pupils"] == 980
    # KS2-only columns must not produce a fake secondary disadvantaged anchor.
    assert result["secondary"]["disadvantaged_pct"] is None


def test_benchmarks_context_none_when_mart_missing():
    result = compute_benchmarks(_sample_df(), census_benchmarks=None)
    assert result["primary"]["fsm_pct"] is None   # never silently fall back

(_sample_df() = this file's existing dataframe fixture.) Run the file; expected: FAIL (unexpected keyword census_benchmarks).

• Step 3: Backend implementation

backend/models.py (next to the national-average models):

class CensusBenchmark(Base):
    """State-school context benchmarks from the pupil census — one row per phase."""
    __tablename__ = "fact_census_benchmarks"
    __table_args__ = MARTS

    phase = Column(String(20), primary_key=True)
    year = Column(Integer)
    fsm_pct = Column(Float)      # pupil-weighted mean
    eal_pct = Column(Float)      # pupil-weighted mean
    median_pupils = Column(Integer)

backend/data_loader.py — change the signature and _block:

def compute_benchmarks(df: pd.DataFrame, census_benchmarks: dict | None = None) -> dict:

Inside, keep _median and _weighted_disadvantaged as-is, and replace _block with:

    def _block(sub, phase, with_disadvantaged):
        census = (census_benchmarks or {}).get(phase) or {}
        block = {
            # Context measures come from the census mart (pupil-weighted):
            # the performance df has no fsm_pct, and its eal/disadvantaged
            # columns are KS2-only — medianing them for "secondary" produced
            # junk anchors from the handful of all-through schools.
            "eal_pct": census.get("eal_pct"),
            "sen_support_pct": _median(sub, "sen_support_pct"),
            "disadvantaged_pct": _median(sub, "disadvantaged_pct") if with_disadvantaged else None,
            "fsm_pct": census.get("fsm_pct"),
            "median_pupils": census.get("median_pupils"),
        }
        if with_disadvantaged:
            block["disadvantaged_rwm_expected_pct"] = _weighted_disadvantaged(sub)
        return block

and the return:

    return {
        "source": "state-school average (computed from our dataset)",
        "year": int(latest_year),
        "primary": _block(prim, "primary", with_disadvantaged=True),
        "secondary": _block(sec, "secondary", with_disadvantaged=False),
    }

In backend/app.py's compare endpoint, load the mart and pass it (same defensive style as the national-averages queries):

    census_benchmarks = None
    try:
        rows = db.query(CensusBenchmark).all()
        if rows:
            census_benchmarks = {
                r.phase: {
                    "year": r.year,
                    "fsm_pct": r.fsm_pct,
                    "eal_pct": r.eal_pct,
                    "median_pupils": r.median_pupils,
                }
                for r in rows
            }
    except Exception:
        db.rollback()
    ...
        "benchmarks": compute_benchmarks(df, census_benchmarks=census_benchmarks),

(Import CensusBenchmark; use the endpoint's existing db session pattern.) Run the backend suite; expected: PASS (update any existing benchmark tests that asserted the old median-sourced fsm/eal values).

• Step 4: Frontend — remove the cross-definition fallback

nextjs-app/components/compare/CompareCommunity.tsx:36:

    const anchor = bench?.fsm_pct ?? null;

If the FSM chip has unit coverage, update/add the case: anchor null ⇒ no verdict chip rendered (bare value only). Run cd nextjs-app && npx tsc --noEmit && npm test — expected PASS.

• Step 5: Commit

git add pipeline/transform/models/marts backend/models.py backend/data_loader.py backend/app.py backend/tests/test_benchmarks.py nextjs-app/components/compare/CompareCommunity.tsx nextjs-app/__tests__
git commit -m "fix(compare): census-sourced FSM/EAL benchmarks; never fall back across measure definitions"

---

Task 5: Official KS4 national averages

Files:

• Modify: pipeline/plugins/extractors/tap-uk-ees/tap_uk_ees/tap.py (new stream, registered in discover_streams)
• Create: pipeline/transform/models/staging/stg_ees_ks4_national.sql
• Modify: pipeline/transform/models/staging/_stg_sources.yml (add raw table ees_ks4_national)
• Modify: pipeline/transform/models/marts/fact_ks4_national_averages.sql (rewrite)
• Modify: backend/models.py (Ks4NationalAverage docstring), backend/app.py (_national_averages_payload — remove the computed fallback)
• Test: backend/tests/test_national_averages_marts.py

Interfaces:

• Consumes: EES data-catalogue CSV https://explore-education-statistics.service.gov.uk/data-catalogue/data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv (columns verified: time_period, geographic_level, establishment_type_group, breakdown_topic, breakdown, attainment8_average, progress8_average, engmath_95_percent, engmath_94_percent, ebacc_entering_percent, ebacc_95_percent, ebacc_94_percent, ebacc_aps_average, …).

• Produces: marts.fact_ks4_national_averages with the SAME columns as today (so Ks4NationalAverage needs no schema change), now holding official DfE figures; gcse_grade_91_pct is NULL (not in the official series — the England anchor for that measure disappears, which is correct: it was noise).

• Step 1: Tap stream

In tap.py, after the KS2 national stream, add:

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

_KS4_NATIONAL_CSV_URL = (
    "https://explore-education-statistics.service.gov.uk/data-catalogue/"
    "data-set/1b649e16-01e8-435b-a814-56be2faf9054/csv"
)

_KS4_NATIONAL_COL_MAP = {
    "attainment8_average":    "attainment_8_score",
    "progress8_average":      "progress_8_score",
    "engmath_94_percent":     "english_maths_standard_pass_pct",
    "engmath_95_percent":     "english_maths_strong_pass_pct",
    "ebacc_entering_percent": "ebacc_entry_pct",
    "ebacc_94_percent":       "ebacc_standard_pass_pct",
    "ebacc_95_percent":       "ebacc_strong_pass_pct",
    "ebacc_aps_average":      "ebacc_avg_score",
}


class EESKs4NationalStream(Stream):
    """National KS4 headline averages — one row per academic year.

    Filters to geographic_level == 'National', establishment_type_group ==
    'All state-funded', breakdown_topic == 'Total', breakdown == 'Total'
    so only the England-wide all-pupils row per year is emitted.
    """

    name = "ees_ks4_national"
    primary_keys = ["time_period"]
    replication_key = None

    schema = th.PropertiesList(
        th.Property("time_period", th.StringType, required=True),
        *[th.Property(out, th.StringType) for out in _KS4_NATIONAL_COL_MAP.values()],
    ).to_dict()

    def get_records(self, context):
        import pandas as pd

        self.logger.info("Downloading KS4 national headlines: %s", _KS4_NATIONAL_CSV_URL)
        resp = requests.get(_KS4_NATIONAL_CSV_URL, timeout=60)
        resp.raise_for_status()

        df = pd.read_csv(io.BytesIO(resp.content), dtype=str, keep_default_na=False)
        df.columns = [c.strip().lower() for c in df.columns]

        for col, want in [
            ("geographic_level", "national"),
            ("establishment_type_group", "all state-funded"),
            ("breakdown_topic", "total"),
            ("breakdown", "total"),
        ]:
            if col in df.columns:
                df = df[df[col].str.strip().str.lower() == want]

        self.logger.info("Emitting %d national KS4 rows", len(df))
        for _, row in df.iterrows():
            record = {"time_period": row.get("time_period", "").strip()}
            for src, out in _KS4_NATIONAL_COL_MAP.items():
                record[out] = row.get(src, "")
            yield record

Register EESKs4NationalStream(self) in discover_streams next to the KS2 national stream.

• Step 2: Raw source + staging model

Add to _stg_sources.yml under the raw source, matching the ees_ks2_national entry's style:

      - name: ees_ks4_national
        description: Official DfE KS4 national headline averages (EES data catalogue)

Create pipeline/transform/models/staging/stg_ees_ks4_national.sql:

{{ config(materialized='table') }}

-- Staging model: official DfE KS4 national headline averages — one row per
-- academic year (England, all state-funded, all pupils). Source: EES data
-- catalogue "National characteristics summary data". Suppressed values
-- ('z', 'x') are coerced to NULL by safe_numeric — Progress 8 is 'z' in
-- years with no KS2 baseline (e.g. 2024/25): legitimately unpublished.

select
    cast(trim(time_period) as integer)                        as year,
    {{ safe_numeric('attainment_8_score') }}                  as attainment_8_score,
    {{ safe_numeric('progress_8_score') }}                    as progress_8_score,
    {{ safe_numeric('english_maths_standard_pass_pct') }}     as english_maths_standard_pass_pct,
    {{ safe_numeric('english_maths_strong_pass_pct') }}       as english_maths_strong_pass_pct,
    {{ safe_numeric('ebacc_entry_pct') }}                     as ebacc_entry_pct,
    {{ safe_numeric('ebacc_standard_pass_pct') }}             as ebacc_standard_pass_pct,
    {{ safe_numeric('ebacc_strong_pass_pct') }}               as ebacc_strong_pass_pct,
    {{ safe_numeric('ebacc_avg_score') }}                     as ebacc_avg_score
from {{ source('raw', 'ees_ks4_national') }}
where time_period ~ '^[0-9]+$'

• Step 3: Rewrite the mart

Replace the entire body of fact_ks4_national_averages.sql:

{{ config(materialized='table') }}

-- Mart: OFFICIAL DfE KS4 national headline averages — one row per academic
-- year (England, state-funded, all pupils), from the EES national dataset.
-- Replaces the previous unweighted school-level means, which were 7–15
-- points off every headline measure and produced an arithmetically
-- impossible national Progress 8. gcse_grade_91_pct has no official
-- national series and is NULL (schema kept for the API model).

select
    year,
    attainment_8_score,
    progress_8_score,
    english_maths_standard_pass_pct,
    english_maths_strong_pass_pct,
    ebacc_entry_pct,
    ebacc_standard_pass_pct,
    ebacc_strong_pass_pct,
    ebacc_avg_score,
    cast(null as double precision) as gcse_grade_91_pct
from {{ ref('stg_ees_ks4_national') }}
order by year

Run: cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . — expected PASS.

• Step 4: Backend — official provenance, no computed fallback

backend/models.py: change the Ks4NationalAverage docstring to """Official DfE KS4 national headline averages — one row per academic year.""".

backend/app.py _national_averages_payload: delete the entire if not any(secondary_by_year.values()): fallback block (it computes dataset means that the UI footnote then labels official). Update the function docstring's KS4 sentence to: official DfE KS4 figures (fact_ks4_national_averages). If the KS4 mart hasn't been built yet, the secondary series is empty — never a computed stand-in, because the UI labels these figures as official.

Update backend/tests/test_national_averages_marts.py: the test that exercised the fallback now asserts the opposite —

def test_ks4_secondary_empty_when_mart_missing(...):
    # No computed stand-in: the UI labels national figures as official DfE
    # data, so an empty mart must yield an empty secondary series.
    payload = _national_averages_payload(df)
    assert all(not e["secondary"] for e in payload["by_year"])

(adapt to the file's existing fixtures/monkeypatching). Run the backend suite — expected PASS.

• Step 5: Commit

git add pipeline/plugins/extractors/tap-uk-ees pipeline/transform backend
git commit -m "fix(data): official DfE KS4 national headline averages; drop mislabelled computed means"

---

Task 6: Honest 2021/22 footnote

Files:

• Modify: nextjs-app/components/ComparisonChart.tsx:243-247
• Modify: nextjs-app/lib/compareChartData.ts (comment lines 7, 53–55 — comments only, no logic)
• Modify: nextjs-app/__tests__/lib/compareChartData.test.ts (test name/comment wording only)
• Modify: docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md §8.1

Interfaces: none — copy and docs only. This is the one place the plan changes reviewed copy, because the reviewed copy is factually wrong (Global Constraints exception).

• Step 1: Fix the user-facing copy

In ComparisonChart.tsx replace the note:

      {built.showUnpublished202122Note && (
        <p className={styles.chartNote}>
          No national tests were held in 2019/20 and 2020/21 (COVID), and our dataset doesn&apos;t
          yet include school-level figures for 2021/22 — the England average is shown for that
          year.
        </p>
      )}

• Step 2: Fix the lying comments

In compareChartData.ts, update the header comment (line 7) and the showUnpublished202122Note doc comment (lines 53–55) to say the 2021/22 school-level figures are absent from our dataset (DfE published them in Dec 2022; ingesting them is a backlog pipeline task), not "unpublished". Rename nothing (the flag name stays — pure rename churn). In compareChartData.test.ts, adjust the test description/comment wording the same way.

• Step 3: Correct spec §8.1

In the spec's §8.1, replace any wording that calls 2021/22 school-level KS2 a "permanent DfE gap" with: DfE published school-level KS2 results for 2021/22 in December 2022 (with comparability caveats); they are not yet ingested — loading them remains an open pipeline task, and the chart footnote says "our dataset doesn't yet include" accordingly.

• Step 4: Verify + commit

Run: cd nextjs-app && npx tsc --noEmit && npm test — expected PASS.

git add nextjs-app docs/superpowers/specs/2026-07-11-compare-screen-redesign-design.md
git commit -m "fix(compare): stop attributing the missing 2021/22 school-level year to DfE"

---

Task 7: Full verification, PR, and post-deploy checklist

Files: none new (verification + PR).

• Step 1: Run everything

uv run --with-requirements requirements.txt --with pytest --with "httpx==0.27.0" python -m pytest backend/tests -q
cd nextjs-app && npx tsc --noEmit && npm test && cd ..
cd pipeline/transform && uv run --with dbt-postgres python -m dbt.cli.main parse --profiles-dir . && cd ../..

Expected: all PASS.

• Step 2: Open the PR

Push fix/compare-final-review-mustfix; open a PR via the Gitea API using git credential fill basic auth (token-header auth 401s). PR body: summarize the five findings and fixes, link the expert review, end with the standard Claude Code attribution + session URL. Note in the body that findings 2 and 4 also need a DAG run after the staging deploy before the UI shows corrected data.

• Step 3: Post-merge staging verification (after the user merges and the daily DAG runs — record results, do not promote)

# Report card dated by its own inspection (Barclay): expect 2026-02-03
curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=138690" | python3 -c "import json,sys; o=json.load(sys.stdin)['comparison']['138690']['ofsted']; print(o['rc_inspection_date'], o['inspection_date'])"
# Stale Watford rc grades cleared by the fresh extract: expect report_card == {}
curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=136276" | python3 -c "import json,sys; print(json.load(sys.stdin)['comparison']['136276']['ofsted']['report_card'])"
# Official KS4 nationals: expect A8 46.0 for 202425, progress_8_score absent
curl -sk "https://stx.schoolcompare.co.uk/api/national-averages" | python3 -c "import json,sys; print(json.load(sys.stdin)['secondary'])"
# FSM benchmark real (~24-26), secondary disadvantaged_pct gone
curl -sk "https://stx.schoolcompare.co.uk/api/compare?urns=138690,136276" | python3 -c "import json,sys; print(json.load(sys.stdin)['benchmarks'])"

Then re-screenshot both phase views (desktop + mobile, "More measures" expanded, Watford Grammar in the secondary set) and hand them to the Ofsted expert agent for the sign-off pass it said it expects. Production promotion remains the human's manual call.

---

Out of Scope (expert should-fix/minor — separate follow-ups)

• 137086-style interim state (subgrades without an overall from an RI reinspection) rendering treatment (finding 6).
• Disadvantaged cohort sizes on the attainment row (finding 7, spec §8.5).
• SEN/EAL "typical school" labelling and secondary SEN benchmark (finding 8) — note Task 4 already upgrades EAL to a pupil-weighted census figure.
• Selective-school admissions copy variant (finding 9).
• Removing/relabelling gcse_grade_91_pct as a compare measure (finding 10) — Task 5 already removes its false England anchor.
• Palette deviation (11), trends picker label (12), "More measures" expanded-state verification (13).
• Actually ingesting the 2021/22 school-level KS2 release (the copy in Task 6 says "doesn't yet include").