Root cause: the UNION ALL query in data_loader.py produced two rows per
all-through school per year (one KS2, one KS4), with drop_duplicates()
silently discarding the KS4 row. Fixes:
- New dbt mart `fact_performance`: FULL OUTER JOIN of fact_ks2_performance
and fact_ks4_performance on (urn, year). One row per school per year.
All-through schools have both KS2 and KS4 columns populated.
- data_loader.py: replace 175-line UNION ALL with a simple JOIN to
fact_performance. No more duplicate rows or drop_duplicates needed.
- sync_typesense.py: single LATERAL JOIN to fact_performance instead of
two separate KS2/KS4 joins.
- app.py: remove drop_duplicates (no longer needed); add PHASE_GROUPS
constant so all-through/middle schools appear in primary and secondary
filter results (were previously invisible to both); scope result_filters
gender/admissions_policies to secondary schools only.
- HomeView.tsx: isSecondaryView is now majority-based (not "any secondary")
and isMixedView shows both sort option sets for mixed result sets.
- school/[slug]/page.tsx: all-through schools route to SchoolDetailView
(renders both SATs + GCSE sections) instead of SecondarySchoolDetailView
(KS4-only). Dedicated SEO metadata for all-through schools.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Remove 10-mile radius option; cap backend radius max at 5 miles
- Raise backend page_size max to 500 so map can fetch all schools in one call
- HomeView: when map view is active, fetch all schools within radius
(page_size=500) instead of showing only the paginated first page;
falls back to initial SSR schools while loading
- SchoolMap/LeafletMapInner: accept referencePoint prop and render a
distinctive coral circle pin at the search postcode location
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Load-more requests read URL params (postcode, radius, etc.) but page_size
is never in the URL — it's hardcoded in page.tsx. Without it the backend
received page_size=None, hit a TypeError on (page-1)*None, returned 500,
and the silent catch left the user stuck on page 1.
In a dense area (e.g. Wimbledon SW19) 50 schools fit within ~1.8 miles,
so page 1 never shows anything beyond that regardless of selected radius.
Fix:
- Backend: give page_size a safe default of 25 instead of None
- Frontend: explicitly pass initialSchools.page_size in load-more params
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Backend builds sitemap.xml from school data at startup (in-memory)
- POST /api/admin/regenerate-sitemap refreshes it after data updates
- New Airflow DAG (sitemap_generate) runs Sundays 05:00 and calls the endpoint
- Next.js proxies /sitemap.xml to the backend; removes the slow dynamic sitemap.ts
- docker-compose passes BACKEND_URL + ADMIN_API_KEY to Airflow env
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
1. Simpler home page: only search box on landing, no filter dropdowns
2. Advanced filters: hidden behind toggle on results page, auto-open if active
3. Per-school phase rendering: each row renders based on its own data
4. Taller 4-line rows with context line (type, age range, denomination, gender)
5. Result-scoped filters: dropdown values reflect current search results
6. Fix blank filter values: exclude empty strings and "Not applicable"
7. Rankings: Primary/Secondary phase tabs with phase-specific metrics
8. Compare: Primary/Secondary tabs with school counts and phase metrics
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
- Backend: replace INNER JOIN ks2 with UNION ALL (ks2 + ks4) so primary
and secondary schools both appear in the main DataFrame
- Backend: add /api/national-averages endpoint computing means from live
data, replacing the hardcoded NATIONAL_AVG constant on the frontend
- Backend: add phase filter param to /api/schools; return phases from
/api/filters; fix hardcoded "phase": "Primary" in school detail endpoint
- Backend: add KS4 metric definitions (Attainment 8, Progress 8, EBacc,
English & Maths pass rates) to METRIC_DEFINITIONS and RANKING_COLUMNS
- Frontend: SchoolDetailView is now phase-aware — secondary schools show
a GCSE Results section (Att8, P8, E&M, EBacc) instead of SATs; phonics
tab hidden for secondary; admissions says Year 7 instead of Year 3;
history table shows KS4 columns; chart datasets switch for secondary
- Frontend: new MetricTooltip component (CSS-only ⓘ icon) backed by
METRIC_EXPLANATIONS — added to RWM, GPS, SEN, EAL, IDACI, progress
scores and all KS4 metrics throughout SchoolDetailView and SchoolCard
- Frontend: METRIC_EXPLANATIONS extended with KS4 terms (Attainment 8,
Progress 8, EBacc) and previously missing terms (SEN, EHCP, EAL, IDACI)
- Frontend: SchoolCard expands "RWM" to "Reading, Writing & Maths" and
shows Attainment 8 / English & Maths Grade 4+ for secondary schools
- Frontend: FilterBar adds Phase dropdown (Primary / Secondary / All-through)
- Frontend: HomeView hero copy updated; compact list shows phase-aware metric
- Global metadata updated to remove "primary only" framing
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
sync_typesense.py:
- Fix query string replacement: was matching 'ST_X(l.geom) as lng' but
QUERY_BASE uses 'l.longitude as lng' — KS2/KS4 lateral joins were
silently dropped on every sync run
backend:
- Add typesense_url/typesense_api_key settings to config.py
- Add search_schools_typesense() to data_loader.py — queries Typesense
'schools' alias, returns URNs in relevance order with typo tolerance;
falls back to empty list if Typesense is unavailable
- /api/schools: replace pandas str.contains with Typesense search;
results are filtered from the DataFrame and returned in relevance order;
graceful fallback to substring match if Typesense is down
requirements.txt: add typesense==0.21.0, numpy==1.26.4
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Pipeline:
- EES tap: split KS4 into performance + info streams, fix admissions filename
(SchoolLevel keyword match), fix census filename (yearly suffix), remove
phonics (no school-level data on EES), change endswith → in for matching
- stg_ees_ks4: rewrite to filter long-format data and extract Attainment 8,
Progress 8, EBacc, English/Maths metrics; join KS4 info for context
- stg_ees_admissions: map real CSV columns (total_number_places_offered, etc.)
- stg_ees_census: update source reference, stub with TODO for data columns
- Remove stg_ees_phonics, fact_phonics (no school-level EES data)
- Add ees_ks4_performance + ees_ks4_info sources, remove ees_ks4 + ees_phonics
- Update int_ks4_with_lineage + fact_ks4_performance with new KS4 columns
- Annual EES DAG: remove stg_ees_phonics+ from selector
Backend:
- models.py: replace all models to point at marts.* tables with schema='marts'
(DimSchool, DimLocation, KS2Performance, FactOfstedInspection, etc.)
- data_loader.py: rewrite load_school_data_as_dataframe() using raw SQL joining
dim_school + dim_location + fact_ks2_performance; update get_supplementary_data()
- database.py: remove migration machinery, keep only connection setup
- app.py: remove check_and_migrate_if_needed, remove /api/admin/reimport-ks2
endpoints (pipeline handles all imports)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
The geocoding pass over ~15k schools takes longer than any reasonable
HTTP timeout. New approach:
- POST /api/admin/reimport-ks2 starts migration in background thread,
returns {"status":"started"} immediately
- GET /api/admin/reimport-ks2/status returns {running, done}
- ks2.py polls status every 30s (max 2h) before returning
- Kestra flow timeout bumped to PT2H
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Add geocode query param to /api/admin/reimport-ks2 (defaults true).
ks2.py passes ?geocode=true so postcodes are resolved to lat/lng in
the same migration pass.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- backend: POST /api/admin/reimport-ks2 runs full CSV migration in a thread
- backend/docker-compose: ADMIN_API_KEY env var (default: changeme) so the
key is stable across restarts and the integrator can call the endpoint
- integrator: sources/ks2.py triggers the backend endpoint (900s timeout)
- integrator: flows/ks2.yml Kestra flow (manual trigger, no schedule)
To re-ingest after a DB wipe: trigger the ks2-reimport flow from the
Kestra UI at http://localhost:8080.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Adds a full data integration pipeline for enriching school profiles with
supplementary data from Ofsted, GIAS, EES, IDACI, and FBIT.
Backend:
- Bump SCHEMA_VERSION to 3; add 8 new DB tables (ofsted_inspections,
ofsted_parent_view, school_census, admissions, sen_detail, phonics,
school_deprivation, school_finance) plus GIAS columns on schools
- Expose all supplementary data via GET /api/schools/{urn}
- Enrich school list responses with ofsted_grade + ofsted_date
Integrator (new service):
- FastAPI HTTP microservice; Kestra calls POST /run/{source}
- 9 source modules: ofsted, gias, parent_view, census, admissions,
sen_detail, phonics, idaci, finance
- 9 Kestra flow YAMLs with scheduled triggers and 3× retry
Frontend:
- SchoolRow: colour-coded Ofsted badge (Outstanding/Good/RI/Inadequate)
- SchoolDetailView: 7 new sections — Ofsted sub-judgements, Parent View
survey bars, Admissions, Pupils & Inclusion / SEN, Phonics, Deprivation
Context, Finances
- types.ts: 8 new interfaces + extended School/SchoolDetailsResponse
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
The frontend expects location_info with coordinates array, but backend was
returning search_location with lat/lng keys. This fix enables the map toggle
to appear for location-based searches.
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
On startup, the app now checks if the database schema version matches
the code. If there's a mismatch or no version exists, it automatically
runs a full data migration before starting.
- Add backend/version.py with SCHEMA_VERSION constant
- Add backend/migration.py with extracted migration logic
- Add SchemaVersion model to track DB version
- Add version check functions to database.py
- Update app.py lifespan to use check_and_migrate_if_needed()
- Simplify migrate_csv_to_db.py to use shared logic
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
When searching by location, users can now toggle between list view
(school cards grid) and a split map view showing:
- Interactive map on left with all school markers
- Scrollable school list on right
- Blue marker for search location, default markers for schools
- Clicking a marker highlights and scrolls to the corresponding card
Mobile responsive with stacked layout on smaller screens.
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Replace footer note with a contact form that emails contact@schoolcompare.co.uk
via FormSubmit.co. Keep only the data source attribution. Update CSP to allow
form submissions to FormSubmit.co and add responsive styling for the form.
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
- Add GA4 measurement ID to config (default: G-J0PCVT14NY)
- Add /api/config endpoint to expose GA ID to frontend
- Update cookie consent with Analytics category (opt-in)
- Load GA4 only after user consents to analytics cookies
- Update CSP to allow Google Analytics domains
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Use direct bracket indexing instead of .get() for pandas Series
row access in calc_distance function to ensure scalar values
are returned for pd.isna() checks.
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
- Display RWM Higher % alongside RWM Expected % on school cards
- Add trend indicators (up/down/stable arrows) showing year-over-year change
- Backend calculates previous year's RWM for trend comparison
- Trend appears on cards and in school detail modal
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Creates a scalable favicon with the same design as the header logo:
dark background with coral-colored circle, grid lines, and center dot.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Implements Silktide Consent Manager via jsDelivr CDN for GDPR compliance.
The banner informs users the site only uses essential cookies and allows
them to manage preferences.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
