ci: speed up Frontend Typecheck + Tests; cancel superseded PR runs

The job does ~3s of real work (typecheck 1.4s + jest 1.3s) but installs
452 MB / 460 packages every run. Two changes:

- Cache nextjs-app/node_modules keyed on the lockfile hash (OS + node
  major pinned) and skip npm ci entirely on a hit — deps change rarely,
  so most PR pushes now do zero install. On miss, npm ci runs with
  --prefer-offline --no-audit --no-fund.
- Workflow-level concurrency with cancel-in-progress: a new commit (or an
  empty re-trigger) aborts the previous run instead of stacking a second
  full matrix.

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

.gitea/workflows/pr-checks.yml

@@ -5,6 +5,13 @@ on:
     branches:
       - main
 
+# Cancel superseded runs: pushing a new commit to a PR (or an empty
+# re-trigger) aborts the previous still-running checks instead of running
+# a second full matrix alongside them.
+concurrency:
+  group: pr-checks-${{ gitea.event.pull_request.number }}
+  cancel-in-progress: true
+
 env:
   REGISTRY: privaterepo.sitaru.org
   BACKEND_IMAGE_NAME: ${{ gitea.repository }}-backend
@@ -23,12 +30,22 @@ jobs:
         uses: actions/setup-node@v4
         with:
           node-version: 22
-          cache: npm
-          cache-dependency-path: nextjs-app/package-lock.json
+
+      # Cache the resolved node_modules (452 MB / 460 packages) keyed on the
+      # lockfile. On a hit — the common case, since deps change rarely — the
+      # whole `npm ci` step is skipped, not just its download phase. The key
+      # pins OS + node major so we never restore incompatible native binaries.
+      - name: Cache node_modules
+        id: node-modules-cache
+        uses: actions/cache@v4
+        with:
+          path: nextjs-app/node_modules
+          key: nextjs-node-modules-${{ runner.os }}-node22-${{ hashFiles('nextjs-app/package-lock.json') }}
 
       - name: Install dependencies
+        if: steps.node-modules-cache.outputs.cache-hit != 'true'
         working-directory: nextjs-app
-        run: npm ci
+        run: npm ci --prefer-offline --no-audit --no-fund
 
       - name: Typecheck
         working-directory: nextjs-app

backend/app.py

@@ -772,93 +772,101 @@ async def get_la_averages(request: Request):
     return {"year": latest_year, "secondary": {"attainment_8_by_la": la_avg}}
 
 
+_KS2_NATIONAL_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",
+    "disadvantaged_gap", "disadvantaged_pct", "sen_support_pct", "eal_pct",
+]
+_KS4_NATIONAL_METRICS = [
+    "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", "gcse_grade_91_pct",
+]
+
+
 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)."""
+    /api/compare.
+
+    Both series are persisted marts computed at import time: official DfE
+    KS2 figures (fact_ks2_national_averages) and dataset-computed KS4
+    averages (fact_ks4_national_averages) — the API never aggregates the
+    performance dataframe per request. If the KS4 mart hasn't been built
+    yet (deploy lands before the next DAG run), fall back to computing the
+    latest year only — a single-year scan, never the historical loop.
+    """
     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",
-        "disadvantaged_gap", "disadvantaged_pct", "sen_support_pct", "eal_pct",
-    ]
-    ks4_metrics = [
-        "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", "gcse_grade_91_pct",
-    ]
+    latest_year = int(df["year"].max())
 
-    def _means(sub_df, metric_list):
+    from . import database
+    from .models import Ks2NationalAverage, Ks4NationalAverage
+
+    def _row_metrics(row, metric_list):
         out = {}
         for col in metric_list:
-            if col in sub_df.columns:
-                val = sub_df[col].dropna()
-                if len(val) > 0:
-                    out[col] = round(float(val.mean()), 2)
+            val = getattr(row, col, None)
+            if val is not None:
+                out[col] = val
         return out
 
-    latest_year = int(df["year"].max())
-    df_latest = df[df["year"] == latest_year]
-
-    # Primary: schools where KS2 data is non-null
-    primary_df = df_latest[df_latest["rwm_expected_pct"].notna()]
-    # Secondary: schools where KS4 data is non-null
-    secondary_df = df_latest[df_latest["attainment_8_score"].notna()]
-
-    latest_primary = _means(primary_df, ks2_metrics)
-    latest_secondary = _means(secondary_df, ks4_metrics)
-
-    # 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 . import database
-    from .models import Ks2NationalAverage
-
-    by_year = []
+    ks2_rows: list = []
+    ks4_rows: list = []
     db = None
     try:
         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 = {}
-        for yr in sorted(df["year"].dropna().unique()):
-            yr = int(yr)
-            df_yr = df[df["year"] == yr]
-            secondary_by_year[yr] = _means(
-                df_yr[df_yr["attainment_8_score"].notna()], ks4_metrics
-            )
-        # Merge: official KS2 figures + computed KS4 figures per year
-        ks2_years = {r.year for r in nat_rows}
-        all_years = sorted(ks2_years | set(secondary_by_year.keys()))
-        nat_lookup = {r.year: r for r in nat_rows}
-        for yr in all_years:
-            primary_yr: dict = {}
-            if yr in nat_lookup:
-                r = nat_lookup[yr]
-                for col in ks2_metrics:
-                    val = getattr(r, col, None)
-                    if val is not None:
-                        primary_yr[col] = val
-            by_year.append({
-                "year": yr,
-                "primary": primary_yr,
-                "secondary": secondary_by_year.get(yr, {}),
-            })
+        try:
+            ks2_rows = db.query(Ks2NationalAverage).order_by(Ks2NationalAverage.year).all()
+        except Exception:
+            db.rollback()
+        try:
+            ks4_rows = db.query(Ks4NationalAverage).order_by(Ks4NationalAverage.year).all()
+        except Exception:
+            db.rollback()
+    except Exception:
+        pass
     finally:
         if db is not None:
             db.close()
 
-    # Update latest_primary with official DfE figure for the latest year if available
-    if by_year:
-        latest_official = next((e["primary"] for e in reversed(by_year) if e["primary"]), None)
-        if latest_official:
-            latest_primary = latest_official
+    primary_by_year = {r.year: _row_metrics(r, _KS2_NATIONAL_METRICS) for r in ks2_rows}
+    secondary_by_year = {r.year: _row_metrics(r, _KS4_NATIONAL_METRICS) for r in ks4_rows}
+
+    if not any(secondary_by_year.values()):
+        # KS4 mart missing/empty: compute the latest year only.
+        df_latest = df[df["year"] == latest_year]
+        sec = (
+            df_latest[df_latest["attainment_8_score"].notna()]
+            if "attainment_8_score" in df_latest.columns
+            else df_latest.iloc[0:0]
+        )
+        vals = {}
+        for col in _KS4_NATIONAL_METRICS:
+            if col in sec.columns:
+                v = sec[col].dropna()
+                if len(v) > 0:
+                    vals[col] = round(float(v.mean()), 2)
+        if vals:
+            secondary_by_year[latest_year] = vals
+
+    all_years = sorted(set(primary_by_year) | set(secondary_by_year))
+    by_year = [
+        {
+            "year": yr,
+            "primary": primary_by_year.get(yr, {}),
+            "secondary": secondary_by_year.get(yr, {}),
+        }
+        for yr in all_years
+    ]
+
+    latest_primary = next((e["primary"] for e in reversed(by_year) if e["primary"]), {})
+    latest_secondary = next((e["secondary"] for e in reversed(by_year) if e["secondary"]), {})
 
     return {
         "year": latest_year,

backend/models.py

@@ -231,6 +231,23 @@ class FactFinance(Base):
     premises_cost_pct = Column(Float)
 
 
+class Ks4NationalAverage(Base):
+    """Computed national KS4 averages (from our dataset) — one row per year."""
+    __tablename__ = "fact_ks4_national_averages"
+    __table_args__ = MARTS
+
+    year = Column(Integer, primary_key=True)
+    attainment_8_score = Column(Float)
+    progress_8_score = Column(Float)
+    english_maths_standard_pass_pct = Column(Float)
+    english_maths_strong_pass_pct = Column(Float)
+    ebacc_entry_pct = Column(Float)
+    ebacc_standard_pass_pct = Column(Float)
+    ebacc_strong_pass_pct = Column(Float)
+    ebacc_avg_score = Column(Float)
+    gcse_grade_91_pct = Column(Float)
+
+
 class Ks2NationalAverage(Base):
     """Official DfE KS2 national headline averages — one row per academic year."""
     __tablename__ = "fact_ks2_national_averages"

backend/tests/test_national_averages_marts.py

@@ -0,0 +1,93 @@
+"""_national_averages_payload reads persisted marts (computed at import
+time) — it must never loop the dataframe per year. The only dataframe work
+allowed is the single-latest-year KS4 fallback for the window between a
+deploy and the next DAG run."""
+
+import numpy as np
+import pandas as pd
+import pytest
+
+LATEST = 202425
+
+
+def _df():
+    return pd.DataFrame(
+        [
+            dict(year=202324, attainment_8_score=40.0, rwm_expected_pct=np.nan),
+            dict(year=LATEST, attainment_8_score=50.0, rwm_expected_pct=np.nan),
+            dict(year=LATEST, attainment_8_score=30.0, rwm_expected_pct=np.nan),
+            dict(year=LATEST, attainment_8_score=np.nan, rwm_expected_pct=80.0),
+        ]
+    )
+
+
+class _Ks2Row:
+    year = LATEST
+    rwm_expected_pct = 62.1
+    gps_expected_pct = 72.0
+
+
+class _Ks4Row:
+    year = LATEST
+    attainment_8_score = 46.5
+    progress_8_score = -0.02
+
+
+class _StubSession:
+    """Returns KS2 rows for the first query and KS4 rows for the second —
+    mirroring the payload's query order."""
+
+    def __init__(self):
+        self.calls = 0
+
+    def query(self, model):
+        self._model = model.__name__
+        return self
+
+    def order_by(self, *a):
+        return self
+
+    def all(self):
+        return [_Ks2Row()] if self._model == "Ks2NationalAverage" else [_Ks4Row()]
+
+    def close(self):
+        pass
+
+
+class _Ks4MissingSession(_StubSession):
+    def all(self):
+        if self._model == "Ks4NationalAverage":
+            raise RuntimeError("relation does not exist")
+        return [_Ks2Row()]
+
+    def rollback(self):
+        pass
+
+
+@pytest.fixture()
+def payload(monkeypatch):
+    from backend import app as app_module
+    from backend import database as database_module
+
+    def _run(session_cls):
+        monkeypatch.setattr(database_module, "SessionLocal", session_cls)
+        return app_module._national_averages_payload(_df())
+
+    return _run
+
+
+def test_ks4_averages_come_from_the_mart_not_the_dataframe(payload):
+    body = payload(_StubSession)
+    # Mart value (46.5), NOT the dataframe mean of (50+30)/2 = 40.0
+    assert body["secondary"]["attainment_8_score"] == 46.5
+    assert body["primary"]["rwm_expected_pct"] == 62.1
+    assert body["by_year"][-1]["secondary"]["progress_8_score"] == -0.02
+
+
+def test_missing_ks4_mart_falls_back_to_latest_year_only(payload):
+    body = payload(_Ks4MissingSession)
+    # Fallback computes the latest year from the df: mean(50, 30) = 40.0
+    assert body["secondary"]["attainment_8_score"] == 40.0
+    # ...and only the latest year — no historical KS4 loop
+    ks4_years = [e["year"] for e in body["by_year"] if e["secondary"]]
+    assert ks4_years == [LATEST]

nextjs-app/__tests__/components/ComparisonView.refresh.test.tsx

@@ -0,0 +1,82 @@
+/**
+ * Regression: on refresh, the compare page must show the SSR-rendered data.
+ *
+ * The basket hydrates from the URL a beat after mount (selectedSchools is
+ * empty for the first render), so the fetch effect must not blank the
+ * SSR payload during that window — and must not refetch data the server
+ * already provided.
+ */
+
+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 school(urn: number, name: string): School {
+  return {
+    urn,
+    school_name: name,
+    local_authority: 'Testshire',
+    school_type: 'Community school',
+    rwm_expected_pct: 80,
+    phase: 'Primary',
+  } as School;
+}
+
+function data(urn: number, name: string): ComparisonData {
+  return {
+    school_info: school(urn, name),
+    yearly_data: [{ year: 202425, rwm_expected_pct: 80 }] as ComparisonData['yearly_data'],
+    ofsted: null,
+    census: null,
+    admissions: null,
+    admissions_history: [],
+    deprivation: null,
+  };
+}
+
+const INITIAL_DATA = {
+  '100': data(100, 'Alpha Primary'),
+  '200': data(200, 'Beta Primary'),
+};
+
+beforeEach(() => {
+  fetchComparison.mockReset();
+});
+
+test('renders SSR data on refresh without wiping it or refetching', async () => {
+  render(
+    <ComparisonProvider>
+      <ComparisonView
+        initialData={INITIAL_DATA}
+        initialNationalAverages={{
+          year: 202425,
+          primary: { rwm_expected_pct: 62 },
+          secondary: {},
+          by_year: [],
+        }}
+        initialBenchmarks={undefined}
+        initialUrns={[100, 200]}
+        metrics={[]}
+        selectedMetric="rwm_expected_pct"
+      />
+    </ComparisonProvider>,
+  );
+
+  // Both SSR-provided schools appear (data was not blanked during hydration)
+  await waitFor(() => {
+    expect(screen.getAllByText('Alpha Primary').length).toBeGreaterThan(0);
+  });
+  expect(screen.getAllByText('Beta Primary').length).toBeGreaterThan(0);
+  expect(screen.getByRole('heading', { name: 'At a glance' })).toBeInTheDocument();
+
+  // …and the client never refetched data the server already rendered.
+  expect(fetchComparison).not.toHaveBeenCalled();
+});

nextjs-app/app/compare/page.tsx

@@ -32,26 +32,24 @@ export default async function ComparePage({ searchParams }: ComparePageProps) {
   const selectedMetric = metricParam || 'rwm_expected_pct';
 
   try {
-    // Fetch comparison data if URNs provided
-    let comparisonData = null;
-    if (urns.length > 0) {
-      try {
-        const response = await fetchComparison(urnsParam!);
-        comparisonData = response.comparison;
-      } catch (error) {
-        console.error('Failed to fetch comparison:', error);
-      }
-    }
+    // Fetch comparison + metrics in parallel — they are independent.
+    const [comparisonResponse, metricsResponse] = await Promise.all([
+      urns.length > 0
+        ? fetchComparison(urnsParam!).catch((error) => {
+            console.error('Failed to fetch comparison:', error);
+            return null;
+          })
+        : Promise.resolve(null),
+      fetchMetrics(),
+    ]);
 
-    // Fetch available metrics
-    const metricsResponse = await fetchMetrics();
-
-    // Metrics is already an array
     const metricsArray = metricsResponse?.metrics || [];
 
     return (
       <ComparisonView
-        initialData={comparisonData}
+        initialData={comparisonResponse?.comparison ?? null}
+        initialNationalAverages={comparisonResponse?.national_averages}
+        initialBenchmarks={comparisonResponse?.benchmarks}
         initialUrns={urns}
         metrics={metricsArray}
         selectedMetric={selectedMetric}

nextjs-app/components/ComparisonView.tsx

@@ -35,6 +35,8 @@ import styles from './ComparisonView.module.css';
 
 interface ComparisonViewProps {
   initialData: Record<string, ComparisonData> | null;
+  initialNationalAverages?: NationalAverages;
+  initialBenchmarks?: Benchmarks;
   initialUrns: number[];
   metrics: MetricDefinition[];
   selectedMetric: string;
@@ -42,6 +44,8 @@ interface ComparisonViewProps {
 
 export function ComparisonView({
   initialData,
+  initialNationalAverages,
+  initialBenchmarks,
   initialUrns,
   metrics,
   selectedMetric: initialMetric,
@@ -54,8 +58,10 @@ export function ComparisonView({
   const [selectedMetric, setSelectedMetric] = useState(initialMetric);
   const [isModalOpen, setIsModalOpen] = useState(false);
   const [comparisonData, setComparisonData] = useState(initialData);
-  const [nationalAverages, setNationalAverages] = useState<NationalAverages | undefined>();
-  const [benchmarks, setBenchmarks] = useState<Benchmarks | undefined>();
+  const [nationalAverages, setNationalAverages] = useState<NationalAverages | undefined>(
+    initialNationalAverages,
+  );
+  const [benchmarks, setBenchmarks] = useState<Benchmarks | undefined>(initialBenchmarks);
   const [shareConfirm, setShareConfirm] = useState(false);
   const [comparePhase, setComparePhase] = useState<'primary' | 'secondary'>('primary');
   // Tracks whether the user has explicitly clicked a phase tab.
@@ -81,13 +87,16 @@ export function ComparisonView({
     }
   }, [isInitialized]); // eslint-disable-line react-hooks/exhaustive-deps
 
-  // Sync URL with selected schools + metric, and (re)fetch the comparison.
+  const urnKey = selectedSchools.map((s) => s.urn).join(',');
+
+  // Sync the URL with the selection + metric. Pure navigation state — no
+  // fetching here: metric changes are presentational (the data is already
+  // client-side) and must not refire the comparison request.
   useEffect(() => {
-    const urns = selectedSchools.map((s) => s.urn).join(',');
     const params = new URLSearchParams(searchParams);
 
-    if (urns) {
-      params.set('urns', urns);
+    if (urnKey) {
+      params.set('urns', urnKey);
     } else {
       params.delete('urns');
     }
@@ -96,26 +105,42 @@ export function ComparisonView({
 
     const newUrl = `${pathname}?${params.toString()}`;
     router.replace(newUrl, { scroll: false });
+  }, [urnKey, selectedMetric, pathname, searchParams, router]);
 
-    if (selectedSchools.length > 0) {
-      fetchComparison(urns, { cache: 'no-store' })
-        .then((data) => {
-          setComparisonData(data.comparison);
-          setNationalAverages(data.national_averages);
-          setBenchmarks(data.benchmarks);
-        })
-        .catch((err) => {
-          // Keep whatever we already have (SSR data or a previous fetch) rather
-          // than blanking the page — a transient refetch failure shouldn't
-          // destroy a working comparison the user is looking at.
-          console.error('Failed to fetch comparison:', err);
-        });
-    } else {
-      setComparisonData(null);
-      setNationalAverages(undefined);
-      setBenchmarks(undefined);
-    }
-  }, [selectedSchools, selectedMetric, pathname, searchParams, router]);
+  // Fetch when the school set changes, but only for schools we don't already
+  // have data for. This skips the refetch of SSR-rendered data on load AND
+  // avoids a network call when a school is merely removed. A ref holds the
+  // latest data so the effect can read it without re-running on every fetch.
+  //
+  // Correctness note: we must NOT null the data on a transient empty urnKey.
+  // On mount the basket is empty for a beat before it hydrates from the URL,
+  // and blanking here (then skipping the refetch because SSR "covers" the set)
+  // was leaving the page empty on refresh. The render already shows the empty
+  // state whenever `selectedSchools` is empty, so stale data for deselected
+  // schools is harmless — it's simply unused.
+  const comparisonDataRef = useRef(comparisonData);
+  comparisonDataRef.current = comparisonData;
+
+  useEffect(() => {
+    if (!isInitialized || !urnKey) return;
+
+    const have = comparisonDataRef.current ?? {};
+    const covered = urnKey.split(',').every((urn) => have[urn] != null);
+    if (covered) return;
+
+    fetchComparison(urnKey, { cache: 'no-store' })
+      .then((data) => {
+        setComparisonData(data.comparison);
+        setNationalAverages(data.national_averages);
+        setBenchmarks(data.benchmarks);
+      })
+      .catch((err) => {
+        // Keep whatever we already have (SSR data or a previous fetch) rather
+        // than blanking the page — a transient refetch failure shouldn't
+        // destroy a working comparison the user is looking at.
+        console.error('Failed to fetch comparison:', err);
+      });
+  }, [urnKey, isInitialized]);
 
   // Classify schools by phase using comparison data
   const classifySchool = (school: School): 'primary' | 'secondary' => {

nextjs-app/hooks/useComparison.ts

@@ -1,50 +1,18 @@
 /**
- * Custom hook for managing school comparison state
- * Uses shared context for real-time updates across components
+ * Custom hook for managing school comparison state.
+ *
+ * This hook is mounted on every page via the global Navigation and
+ * ComparisonToast, so it must stay cheap — it exposes basket state only.
+ * The compare page fetches `/api/compare` itself (ComparisonView); nothing
+ * ever read the comparison payload from here, so the previous per-page SWR
+ * fetch (which fired on every page whenever the basket was non-empty) was
+ * dead weight and has been removed.
  */
 
 'use client';
 
-import useSWR from 'swr';
-import { fetcher } from '@/lib/api';
 import { useComparisonContext } from '@/context/ComparisonContext';
-import type { ComparisonResponse } from '@/lib/types';
 
 export function useComparison() {
-  const {
-    selectedSchools,
-    addSchool,
-    removeSchool,
-    replaceSchools,
-    clearAll,
-    isSelected,
-    canAddMore,
-    isInitialized,
-  } = useComparisonContext();
-
-  // Fetch comparison data for selected schools
-  const urns = selectedSchools.map((s) => s.urn).join(',');
-  const { data, error, isLoading, mutate } = useSWR<ComparisonResponse>(
-    selectedSchools.length > 0 ? `/compare?urns=${urns}` : null,
-    fetcher,
-    {
-      revalidateOnFocus: false,
-      dedupingInterval: 10000,
-    }
-  );
-
-  return {
-    selectedSchools,
-    comparisonData: data?.comparison,
-    isLoading,
-    error,
-    addSchool,
-    removeSchool,
-    replaceSchools,
-    clearAll,
-    isSelected,
-    canAddMore,
-    isInitialized,
-    mutate,
-  };
+  return useComparisonContext();
 }

pipeline/transform/models/marts/_marts_schema.yml

@@ -160,6 +160,12 @@ models:
       - name: year
         tests: [not_null, unique]
 
+  - name: fact_ks4_national_averages
+    description: Computed national KS4 averages (means across state schools in our dataset — not official DfE figures) — one row per academic year
+    columns:
+      - name: year
+        tests: [not_null, unique]
+
   - name: fact_deprivation
     description: IDACI deprivation index — one row per URN
     columns:

pipeline/transform/models/marts/fact_ks4_national_averages.sql

@@ -0,0 +1,25 @@
+{{ config(materialized='table') }}
+
+-- Mart: Computed national KS4 averages — one row per academic year.
+-- Unlike fact_ks2_national_averages (official DfE figures), DfE publishes no
+-- KS4 national-headline dataset we ingest yet, so these are means computed
+-- across the state schools in our dataset. Computed once at build time so the
+-- API never has to aggregate the full performance table per request.
+-- Semantics match the API's previous per-request computation: rows where
+-- attainment_8_score is non-null; per-column means ignore NULLs.
+
+select
+    year,
+    round(avg(attainment_8_score)::numeric, 2)               as attainment_8_score,
+    round(avg(progress_8_score)::numeric, 2)                 as progress_8_score,
+    round(avg(english_maths_standard_pass_pct)::numeric, 2)  as english_maths_standard_pass_pct,
+    round(avg(english_maths_strong_pass_pct)::numeric, 2)    as english_maths_strong_pass_pct,
+    round(avg(ebacc_entry_pct)::numeric, 2)                  as ebacc_entry_pct,
+    round(avg(ebacc_standard_pass_pct)::numeric, 2)          as ebacc_standard_pass_pct,
+    round(avg(ebacc_strong_pass_pct)::numeric, 2)            as ebacc_strong_pass_pct,
+    round(avg(ebacc_avg_score)::numeric, 2)                  as ebacc_avg_score,
+    round(avg(gcse_grade_91_pct)::numeric, 2)                as gcse_grade_91_pct
+from {{ ref('fact_ks4_performance') }}
+where attainment_8_score is not null
+group by year
+order by year