GreatSchools API — school data integration
Plan v1 · Shattique · 7/19/26 · Status: awaiting Operator GO · Key:
GREATSCHOOLS_API_KEY set on Edge Function (7/19) — never client-side1 · What GreatSchools gives us
GreatSchools API (v2, key-based,
https://gs-api.greatschools.org/) returns per-school: GS universal ID, name, level (elementary/middle/high), district, address, lat/lon, phone, website, enrollment, student/teacher ratio, and the GS Summary Rating (1–10) + sub-ratings (test scores, progress, equity). Lookups: nearby-schools by lat/lon + radius, schools-by-district, and school-profile by GS id. Contract tier controls rate limits (typ. 3k–30k calls/day) — the bulk backfill is designed around caching so we stay far under it.
2 · What we already have to match on
Every listing row carries:
school_district (indexed column, e.g. "Frisco ISD"), plus raw->rets names — ElementarySchoolName, MiddleSchoolName, HighSchoolName (+ Intermediate/Junior/Primary/Senior tier variants) — and latitude/longitude. Distinct (district, school, tier) tuples are a few thousand across ~470k listings, so we resolve schools once and reuse; we never call GS per listing.
3 · Data model (new)
| Table | Purpose / key columns |
|---|---|
pure_schools | One row per physical school. id, gs_id (unique), name, level, district, address/city/zip, lat/lon, rating, sub_ratings jsonb, enrollment, student_teacher_ratio, website, phone, gs_url, fetched_at. RLS: public read, service-role write. |
pure_school_aliases | Feed-name school map: (district, tier, feed_name) unique school_id, match_method (exact/fuzzy/geo/manual), confidence. This is the cache that makes incremental sync nearly free. |
listing_schools | listing_id, tier (elem/middle/high/…), school_id. Rebuilt from aliases; what the property page joins on. |
school_sync_queue | Work queue: listing_id, status, attempts, error. Fed by trigger (§5). |
4 · Phase A — bulk backfill (one-time)
1
Extract tuples. SQL pulls distinct
(district, tier, feed_name) + a representative lat/lon (median of listings naming it). Est. 2–4k tuples.2
Resolve against GS. Runs on the existing GCP VM worker (same pattern as the evals worker): for each district, call schools-by-district once (or nearby-schools at the tuple's lat/lon), then match feed names — exact normalized (strip "Elementary/HS", punctuation) trigram fuzzy ≥.75 geo-nearest same-level within 8mi. Writes
pure_schools + pure_school_aliases with method+confidence. Unmatched match_method='unresolved' for the review screen.3
Fan out to listings. One SQL join rebuilds
listing_schools for all ~470k listings from the alias table. No API calls.4
Review. Admin page (§6) lists fuzzy/geo/unresolved matches for a human pass. API budget: ~1 call per district-level page ≈ well under 1k calls total.
4b · Dirty-name resolution (NTREIS free-text problem)
Agents type school names free-text, so the feed carries short forms, misspellings, and inconsistent tiers ("Mckinney" / "McKinney HS" / "McKinny High"). The matcher treats the feed name as a hint, never an identifier — the GS school list for the district is the ground truth we snap to. Matching ladder, in order, each step recording
match_method + confidence:
1
Normalize both sides: lowercase, strip punctuation/possessives, expand abbreviations (HS/JH/MS/Elem/El/Int/Sr/Jr full words), drop tier suffixes ("High School", "Elementary"...), collapse whitespace, drop honorific initials ("Jim and Betty Hughes" "Hughes").
2
Exact match on normalized name within (district, tier) confidence 1.0.
3
Token match: all feed tokens ⊂ GS name tokens or vice versa (handles "Rock Hill" "Rock Hill High School") 0.9.
4
Trigram fuzzy (pg_trgm similarity ≥ .72 within district+tier, best-of; catches misspellings "McKinny""McKinney") similarity as confidence.
5
Geo tie-break / fallback: nearest GS school of the same tier within 8 mi of the listing's median lat/lon — used to pick between fuzzy candidates, or alone ( 0.5) when the name matches nothing.
6
Guardrails: matches never cross districts (unless district itself is blank geo-only); tier mismatches are rejected (an "Elementary" GS record can't satisfy a HighSchoolName); every auto-match keeps the verbatim feed string in the alias row for audit; a wrong learned alias is fixable once in admin and heals all affected listings on the next fan-out.
Human loop: everything < 0.85 lands in
/pure-admin/school-matching. An admin fix writes a permanent alias row — the system learns: every future listing with that exact bad spelling auto-maps with confidence 1.0 (method=manual). Aliases are per (district, tier, verbatim feed string), so learned fixes never collide across districts.5 · Phase B — incremental (new/updated listings)
1
Trigger on
listings insert/update-of-school-fields enqueue to school_sync_queue (skips if the alias tuple already exists — the 99% case, zero API cost).2
Drain cron (VM worker or
pure-mcp scheduled function, every 15 min): for queued rows, resolve via alias cache first; only novel (district, name) tuples hit the GS API; write alias + listing_schools.3
Refresh cron (monthly, off-hours CT window like
design_system_autobuild): re-fetch pure_schools profiles older than 90 days so ratings stay current.6 · Surfaces
Property details page: "Schools" group rendered from
listing_schools name, level, GS rating badge (1–10, green ≥7 / gold 4–6 / red ≤3), distance, district — managed through Field Visibility like every other group. Search: school-name autocomplete gains real school entities (not just district text). Admin: /pure-admin/school-matching — match review queue (approve/fix unresolved + fuzzy), sync status KPIs, manual re-run. Registered in pages registry + admin menu.
7 · Rules & guardrails
· API key lives as Edge Function / VM secret only — never in client JS (site is static; all GS calls are server-side).
· GS attribution/branding requirements honored on any surface showing ratings (logo + link per license).
· Alias cache is the contract: no per-listing GS calls, ever.
· Checkpointed build (CP1 schema CP2 backfill CP3 trigger+cron CP4 UI), each reversible; changelog rows per CP as Shattique.
· Poppy memory +
· GS attribution/branding requirements honored on any surface showing ratings (logo + link per license).
· Alias cache is the contract: no per-listing GS calls, ever.
· Checkpointed build (CP1 schema CP2 backfill CP3 trigger+cron CP4 UI), each reversible; changelog rows per CP as Shattique.
· Poppy memory +
pure_memory.integration.greatschools written at CP1 so all lanes know the tables are canon.
8 · What I need from you
1. The GreatSchools API key (add as secret
2. Confirm the license tier (rate limit + whether Summary Rating redistribution is included).
3. Say "CP1 go" and I'll start with the schema + memory records.
GREATSCHOOLS_API_KEY on the pure-mcp Edge Function — Supabase dashboard Edge Functions Secrets — or hand it to me to set via workbench, and I'll also place it in the VM worker env).2. Confirm the license tier (rate limit + whether Summary Rating redistribution is included).
3. Say "CP1 go" and I'll start with the schema + memory records.