Merge pull request 'fix(compare): sticky school bar hidden behind the site header' (#44) from fix/schoolbar-sticky-offset into main

Reviewed-on: #44

backend/data_loader.py

@@ -577,7 +577,6 @@ def compute_benchmarks(df: pd.DataFrame) -> dict:
             "eal_pct": _median(sub, "eal_pct"),
             "sen_support_pct": _median(sub, "sen_support_pct"),
             "disadvantaged_pct": _median(sub, "disadvantaged_pct"),
-            "fsm_pct": _median(sub, "fsm_pct"),
             "median_pupils": median_pupils,
         }
         if with_disadvantaged:

backend/tests/test_benchmarks.py

@@ -17,33 +17,33 @@ def _df():
         # 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, fsm_pct=15.0, total_pupils=200),
+             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, fsm_pct=17.0, total_pupils=280),
+             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, fsm_pct=19.0, total_pupils=300),
+             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, fsm_pct=np.nan, total_pupils=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, fsm_pct=21.0, total_pupils=350),
+             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, fsm_pct=23.0, total_pupils=400),
+             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, fsm_pct=12.0, total_pupils=1000),
+             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, fsm_pct=14.0, total_pupils=1200),
+             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, fsm_pct=99.0, total_pupils=9999),
+             sen_support_pct=99.0, disadvantaged_pct=99.0, total_pupils=9999),
     ]
     return pd.DataFrame(rows)
 
@@ -59,8 +59,6 @@ def test_medians_ignore_nan_and_older_years():
     assert b["year"] == LATEST
     # eal medians over [10,20,30,40,50] = 30
     assert b["primary"]["eal_pct"] == 30.0
-    # fsm medians over [15,17,19,21,23] = 19
-    assert b["primary"]["fsm_pct"] == 19.0
     # median pupils over [200,280,300,350,400] = 300
     assert b["primary"]["median_pupils"] == 300
 
@@ -68,7 +66,6 @@ def test_medians_ignore_nan_and_older_years():
 def test_secondary_block_has_no_disadvantaged_rwm():
     b = compute_benchmarks(_df())
     assert "disadvantaged_rwm_expected_pct" not in b["secondary"]
-    assert b["secondary"]["fsm_pct"] == 13.0
     assert b["secondary"]["median_pupils"] == 1100
 
 

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

@@ -126,13 +126,6 @@ describe('ofstedDisplay', () => {
     expect(ofstedDisplay(ofsted({})).kind).toBe('none');
   });
 
-  it('identifies transitional inspections without overall grades', () => {
-    const transitional = ofstedDisplay(
-      ofsted({ overall_effectiveness: null, inspection_date: '2024-11-05' }),
-    );
-    expect(transitional.kind).toBe('transitional');
-  });
-
   it('uses the four legacy grade words', () => {
     expect(OFSTED_LEGACY_GRADES).toEqual({
       1: 'Outstanding',

nextjs-app/components/ComparisonView.tsx

@@ -142,23 +142,19 @@ export function ComparisonView({
       });
   }, [urnKey, isInitialized]);
 
-  const primarySchools = selectedSchools.filter((school) => {
+  // Classify schools by phase using comparison data
+  const classifySchool = (school: School): 'primary' | 'secondary' => {
     const info = comparisonData?.[school.urn]?.school_info;
-    const hasPrimaryData =
+    if (info?.attainment_8_score != null) return 'secondary';
-      info?.rwm_expected_pct != null ||
+    if (info?.rwm_expected_pct != null) return 'primary';
-      comparisonData?.[school.urn]?.yearly_data?.some((d) => d.rwm_expected_pct != null);
+    // Fallback: check yearly data
-    if (hasPrimaryData) return true;
+    const yearlyData = comparisonData?.[school.urn]?.yearly_data;
-    return school.phase?.toLowerCase().includes('primary') || false;
+    if (yearlyData?.some((d) => d.attainment_8_score != null)) return 'secondary';
-  });
+    return 'primary';
+  };
 
-  const secondarySchools = selectedSchools.filter((school) => {
+  const primarySchools = selectedSchools.filter((s) => classifySchool(s) === 'primary');
-    const info = comparisonData?.[school.urn]?.school_info;
+  const secondarySchools = selectedSchools.filter((s) => classifySchool(s) === 'secondary');
-    const hasSecondaryData =
-      info?.attainment_8_score != null ||
-      comparisonData?.[school.urn]?.yearly_data?.some((d) => d.attainment_8_score != null);
-    if (hasSecondaryData) return true;
-    return school.phase?.toLowerCase().includes('secondary') || false;
-  });
 
   // Auto-select tab with more schools and sync the metric to match the phase.
   useEffect(() => {
@@ -379,7 +375,6 @@ export function ComparisonView({
                 data={activeComparisonData}
                 nationalAverages={nationalAverages}
                 benchmarks={benchmarks}
-                isSecondary={!isPrimary}
               />
               <CompareOfsted schools={activeSchools} data={activeComparisonData} />
               <CompareAcademics
@@ -387,14 +382,12 @@ export function ComparisonView({
                 data={activeComparisonData}
                 nationalAverages={nationalAverages}
                 benchmarks={benchmarks}
-                isSecondary={!isPrimary}
               />
               <CompareAdmissions schools={activeSchools} data={activeComparisonData} />
               <CompareCommunity
                 schools={activeSchools}
                 data={activeComparisonData}
                 benchmarks={benchmarks}
-                isSecondary={!isPrimary}
               />
               <TrendsExplorer
                 schools={activeSchools}

nextjs-app/components/compare/CompareAcademics.tsx

@@ -125,17 +125,15 @@ export function CompareAcademics({
   data,
   nationalAverages,
   benchmarks,
-  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   nationalAverages?: NationalAverages;
   benchmarks?: Benchmarks;
-  isSecondary?: boolean;
 }) {
   const urns = schools.map((school) => school.urn);
   const schoolNames = schools.map((school) => school.school_name);
-  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
+  const isSecondary = schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
 

nextjs-app/components/compare/CompareAtAGlance.tsx

@@ -47,16 +47,14 @@ export function CompareAtAGlance({
   data,
   nationalAverages,
   benchmarks,
-  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   nationalAverages?: NationalAverages;
   benchmarks?: Benchmarks;
-  isSecondary?: boolean;
 }) {
   const urns = schools.map((school) => school.urn);
-  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
+  const isSecondary = schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
   const headlineKey = isSecondary ? 'attainment_8_score' : 'rwm_expected_pct';
@@ -85,14 +83,6 @@ export function CompareAtAGlance({
                   {display.carriedForward && <span className={s.small}>Grade carried forward</span>}
                 </>
               )}
-              {display.kind === 'transitional' && (
-                <>
-                  <span className={s.badge} style={{ backgroundColor: '#e2e8f0', color: '#475569' }}>
-                    No overall grade
-                  </span>
-                  <span className={s.small}>Sub-judgements only</span>
-                </>
-              )}
               {display.kind === 'none' && <span className={s.small}>No inspection in our dataset</span>}
             </Cell>
           );

nextjs-app/components/compare/CompareCommunity.tsx

@@ -20,27 +20,24 @@ export function CompareCommunity({
   schools,
   data,
   benchmarks,
-  isSecondary: propIsSecondary,
 }: {
   schools: School[];
   data: Record<string, ComparisonData>;
   benchmarks?: Benchmarks;
-  isSecondary?: boolean;
 }) {
-  const isSecondary = propIsSecondary !== undefined ? propIsSecondary : schools.some(
+  const isSecondary = schools.some(
     (school) => data[String(school.urn)]?.school_info?.attainment_8_score != null,
   );
   const bench = isSecondary ? benchmarks?.secondary : benchmarks?.primary;
 
   const fsmChip = (value: number | null) => {
-    const anchor = bench?.fsm_pct ?? bench?.disadvantaged_pct ?? null;
+    if (value == null || bench?.disadvantaged_pct == null) return null;
-    if (value == null || anchor == null) return null;
+    const v = verdict(value, bench.disadvantaged_pct, 3);
-    const v = verdict(value, anchor, 3);
     return (
       <Chip tone="neutral">
-        {v === 'above' && `Above the state-school average (${Math.round(anchor)}%)`}
+        {v === 'above' && 'Above the state-school average'}
-        {v === 'close' && `About the state-school average (${Math.round(anchor)}%)`}
+        {v === 'close' && 'About the state-school average'}
-        {v === 'below' && `Below the state-school average (${Math.round(anchor)}%)`}
+        {v === 'below' && 'Below the state-school average'}
       </Chip>
     );
   };

nextjs-app/components/compare/CompareOfsted.tsx

@@ -51,18 +51,6 @@ function ResultCell({ display }: { display: OfstedDisplay }) {
       </>
     );
   }
-  if (display.kind === 'transitional') {
-    return (
-      <>
-        <span className={s.badge} style={{ backgroundColor: '#e2e8f0', color: '#475569' }}>
-          No overall grade
-        </span>
-        <span className={s.small}>
-          Inspected under transitional framework (sub-judgements only)
-        </span>
-      </>
-    );
-  }
   return (
     <>
       <span className={`${s.badge} ${display.grade <= 2 ? s.badgeGood : display.grade === 3 ? s.badgeWarn : s.badgeBad}`}>

nextjs-app/lib/compareLogic.ts

@@ -102,7 +102,6 @@ export type OfstedDisplay =
   | { kind: 'none' }
   | { kind: 'graded'; grade: number; gradeLabel: string; carriedForward: false }
   | { kind: 'carried_forward'; grade: number; gradeLabel: string; carriedForward: true }
-  | { kind: 'transitional' }
   | { kind: 'report_card'; summary: ReportCardSummary };
 
 export function ofstedDisplay(
@@ -118,12 +117,7 @@ export function ofstedDisplay(
 
   const grade = ofsted.overall_effectiveness;
   const gradeLabel = grade != null ? OFSTED_LEGACY_GRADES[grade] : undefined;
-  if (grade == null || gradeLabel === undefined) {
+  if (grade == null || gradeLabel === undefined) return { kind: 'none' };
-    if (ofsted.inspection_date) {
-      return { kind: 'transitional' };
-    }
-    return { kind: 'none' };
-  }
 
   if (ofsted.grade_source === 'ungraded_carried_forward') {
     return { kind: 'carried_forward', grade, gradeLabel, carriedForward: true };

nextjs-app/lib/types.ts

@@ -357,7 +357,6 @@ export interface BenchmarkBlock {
   eal_pct: number | null;
   sen_support_pct: number | null;
   disadvantaged_pct: number | null;
-  fsm_pct?: number | null;
   median_pupils: number | null;
   /** Primary only — weighted by cohort size. */
   disadvantaged_rwm_expected_pct?: number | null;

pipeline/meltano.yml

@@ -49,9 +49,6 @@ plugins:
         - name: mi_url
           kind: string
           description: Ofsted Management Information download URL
-        - name: independent_mi_url
-          kind: string
-          description: Ofsted Independent Schools Management Information download URL
 
     - name: tap-uk-fbit
       namespace: uk_fbit

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

@@ -2,7 +2,6 @@
 
 from __future__ import annotations
 
-from datetime import datetime
 import io
 import re
 
@@ -15,28 +14,20 @@ GOV_UK_PAGE = (
     "monthly-management-information-ofsteds-school-inspections-outcomes"
 )
 
-INDEPENDENT_GOV_UK_PAGE = (
-    "https://www.gov.uk/government/statistical-data-sets/"
-    "non-association-independent-schools-inspections-and-outcomes-management-information"
-)
-
 # Column name → internal field, in priority order (first match wins).
 # Handles both current and older file formats.
 COLUMN_PRIORITY = {
     "urn": ["URN", "Urn", "urn"],
     "inspection_date": [
         "Inspection start date of latest OEIF graded inspection",
-        "Inspection start date of latest OEIF standard inspection",
         "Inspection start date",
         "Inspection date",
     ],
     "inspection_type": [
         "Inspection type of latest OEIF graded inspection",
-        "Inspection type of latest OEIF standard inspection",
         "Inspection type",
     ],
     "event_type_grouping": [
-        "Event type grouping of latest OEIF standard inspection",
         "Event type grouping",
         "Inspection type grouping",
     ],
@@ -61,12 +52,10 @@ COLUMN_PRIORITY = {
         "Effectiveness of leadership and management",
     ],
     "early_years_provision": [
-        "Latest OEIF early years provision (where applicable)",
         "Latest OEIF early years provision",
         "Early years provision (where applicable)",
     ],
     "sixth_form_provision": [
-        "Latest OEIF sixth form provision (where applicable)",
         "Latest OEIF sixth form provision",
         "Sixth form provision (where applicable)",
     ],
@@ -79,7 +68,12 @@ COLUMN_PRIORITY = {
     "ungraded_inspection_date": [
         "Date of latest ungraded inspection",
     ],
-    # Report Card fields (post-Nov 2025 framework).
+    # 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"],
@@ -87,13 +81,6 @@ COLUMN_PRIORITY = {
     "rc_attendance_behaviour": ["Attendance and behaviour"],
     "rc_personal_development": ["Personal development and wellbeing"],
     "rc_leadership_governance": ["Leadership and governance"],
-    "rc_early_years": ["Early years (where applicable)"],
-    "rc_sixth_form": ["Post-16 provision (where applicable)"],
-    "report_url": [
-        "Web Link (opens in new window)",
-        "Web link to Ofsted provider page",
-        "Web link",
-    ],
 }
 
 
@@ -116,51 +103,6 @@ def discover_csv_url() -> str | None:
     return matches[0] if matches else None
 
 
-def discover_independent_csv_url() -> str | None:
-    """Scrape GOV.UK page to find the latest independent schools MI CSV download link."""
-    resp = requests.get(INDEPENDENT_GOV_UK_PAGE, timeout=30)
-    resp.raise_for_status()
-    # Look for CSV attachment links
-    csv_links = re.findall(
-        r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.csv)"',
-        resp.text,
-    )
-    if not csv_links:
-        # Fall back to ODS
-        csv_links = re.findall(
-            r'href="(https://assets\.publishing\.service\.gov\.uk/[^"]+\.ods)"',
-            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
-    }
-
-    parsed_links = []
-    for link in csv_links:
-        normalized_link = link.lower().replace('-', '_')
-        if 'most_recent' not in normalized_link:
-            continue
-
-        match = re.search(r'as_at_(\d{1,2})_([a-z]+)_(\d{4})', normalized_link)
-        if match:
-            day, month_str, year = match.groups()
-            month = months.get(month_str)
-            if month:
-                try:
-                    dt = datetime(int(year), month, int(day))
-                    parsed_links.append((dt, link))
-                except ValueError:
-                    continue
-
-    parsed_links.sort(reverse=True)
-    if parsed_links:
-        return parsed_links[0][1]
-
-    return csv_links[0] if csv_links else None
-
-
 class OfstedInspectionsStream(Stream):
     """Stream: Ofsted inspection records."""
 
@@ -189,6 +131,8 @@ class OfstedInspectionsStream(Stream):
         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),
@@ -204,8 +148,15 @@ class OfstedInspectionsStream(Stream):
                     break
         return mapping
 
-    def _fetch_and_parse_url(self, url: str, pd) -> list[dict]:
+    def get_records(self, context):
-        """Download file and parse records."""
+        import pandas as pd
+
+        url = self.config.get("mi_url") or discover_csv_url()
+        if not url:
+            self.logger.error("Could not discover Ofsted MI download URL")
+            return
+
+        self.logger.info("Downloading Ofsted MI: %s", url)
         resp = requests.get(url, timeout=120)
         resp.raise_for_status()
 
@@ -221,6 +172,8 @@ class OfstedInspectionsStream(Stream):
             lines = text.split("\n")
             header_idx = 0
             for i, line in enumerate(lines[:20]):
+                # Match lines where URN appears as a CSV field (start or after comma),
+                # not as a substring of words like "turn" or "return".
                 if re.search(r'(?:^|,)\s*URN\s*(?:,|$)', line):
                     header_idx = i
                     break
@@ -238,38 +191,16 @@ class OfstedInspectionsStream(Stream):
         for _, row in df.iterrows():
             record = {}
             for field, col in col_map.items():
-                val = row.get(col, None)
+                record[field] = row.get(col, None)
-                if pd.isna(val):
-                    val = None
-                record[field] = val
 
             # Cast URN
             try:
-                record["urn"] = int(record.get("urn"))
+                record["urn"] = int(record["urn"])
             except (ValueError, KeyError, TypeError):
                 continue
 
             yield record
 
-    def get_records(self, context):
-        import pandas as pd
-
-        # 1. State-funded schools
-        state_url = self.config.get("mi_url") or discover_csv_url()
-        if state_url:
-            self.logger.info("Downloading Ofsted state-funded MI: %s", state_url)
-            yield from self._fetch_and_parse_url(state_url, pd)
-        else:
-            self.logger.error("Could not discover Ofsted state-funded MI download URL")
-
-        # 2. Independent schools
-        ind_url = self.config.get("independent_mi_url") or discover_independent_csv_url()
-        if ind_url:
-            self.logger.info("Downloading Ofsted independent MI: %s", ind_url)
-            yield from self._fetch_and_parse_url(ind_url, pd)
-        else:
-            self.logger.error("Could not discover Ofsted independent MI download URL")
-
 
 class TapUKOfsted(Tap):
     """Singer tap for UK Ofsted Management Information."""
@@ -278,7 +209,6 @@ class TapUKOfsted(Tap):
 
     config_jsonschema = th.PropertiesList(
         th.Property("mi_url", th.StringType, description="Direct URL to Ofsted MI file"),
-        th.Property("independent_mi_url", th.StringType, description="Direct URL to Ofsted Independent Schools MI file"),
     ).to_dict()
 
     def discover_streams(self):

pipeline/transform/models/staging/stg_ofsted_inspections.sql

@@ -46,10 +46,12 @@ renamed as (
         {{ 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,
+        -- No MI column exists for these yet (see tap.py); the tap never
-        {{ parse_report_card_grade('rc_sixth_form') }}::integer           as rc_sixth_form,
+        -- emits rc_early_years/rc_sixth_form, so these stay NULL.
+        null::integer as rc_early_years,
+        null::integer as rc_sixth_form,
 
-        nullif(trim(report_url), 'NULL')                                  as report_url
+        report_url
     from source
     where urn is not null
       and (