nyc-edu-data — App Spec

App Name

nyc-edu-data

One-Line Description

NYC school data warehouse with three front-end flows on a shared DB: parent comparison, journalist outlier-finder, and researcher correlation explorer.

Why this shape

The same underlying data answers three different questions:

• Parents ask "is this school good for my kid?" → side-by-side comparison, plain-language framing.
• Journalists ask "which schools are unusual vs. similar schools?" → outlier detection against comparison groups, not citywide averages. The NYCENET comparison group explainer is the right peer-grouping definition to follow.
• Researchers ask "what predicts what?" → long-format data, bulk export, correlation tools.

Designing the schema for the researcher use case (long-format, fully attributed, year-versioned) covers the other two for free.

Pages & Routes

• / — landing, persona picker
• /parents
  • /parents/compare?dbns=X,Y,Z — side-by-side comparison
  • /parents/school/[dbn] — single school profile (parent framing)
• /journalists
  • /journalists/outliers?metric=...&grade_band=...&direction=... — outlier finder vs. comparison group
  • /journalists/movers — schools with biggest year-over-year changes
• /researchers
  • /researchers/explore — interactive cross-tab + scatter
  • /researchers/export — CSV / Parquet bulk download
• /schools/[dbn] — canonical school page (shared across personas)
• /methodology — sources, comparison-group explainer, outlier definitions, caveats
• /about

Data Models

Schema is long-format for metrics and survey responses so we can add new metrics, years, and questions without schema migrations.

School

• dbn — string PK (NYC Dept-Borough-Number, e.g., 01M015)
• name — string
• borough — enum (M, B, K, Q, R)
• district — int (1–32, plus D75/D79/D84)
• school_type — enum (DOE_PUBLIC, CHARTER, D75_SPECIAL_ED, D79_TRANSFER, ALT_PROGRAM)
• grade_band — enum (ES, MS, K8, HS, K12, TRANSFER, D75)
• grade_levels_served — string (e.g., "PK,K,1,2,3,4,5")
• address, lat, lng — for map view
• latest_enrollment — int
• comparison_group_id — FK to ComparisonGroup
• include_in_default_comparisons — bool (false for D75/D79/alt; surfaces only when user opts in)
• nysed_beds_code — string (for joining to NYSED state data)
• closed_at — date nullable (so historical years still resolve)
• has many: SchoolYearMetric, SchoolYearDemographics, SurveyResponse, IncidentReport

Universe: NYC DOE public schools + NYC public charters. Special-program schools (D75, D79 transfer, alt programs) are included but tagged via school_type and include_in_default_comparisons = false so they don't pollute outlier lists or default parent comparisons.

SchoolYearDemographics

Annual snapshot of student composition. Used for researcher equity analysis, journalist framing, and the fallback peer-group computation for charters / new schools.

• school_dbn — FK
• year — string (YYYY-YY)
• total_enrollment — int
• pct_black, pct_hispanic, pct_white, pct_asian, pct_multi_racial, pct_other — numeric
• pct_ell — numeric (English Language Learners)
• pct_swd — numeric (Students with Disabilities)
• pct_econ_dis — numeric (Economic Need Index / FRL proxy — document which)
• pct_temp_housing — numeric (NYC publishes this; key equity indicator)
• pct_male, pct_female — numeric
• source_load_id — FK
• Unique: (school_dbn, year)

ComparisonGroup

NYCENET groups each school with ~40 peer schools serving similar populations. Definitions can change year over year, so we version them.

• id — string PK
• year — string (school year, e.g., 2023-24)
• source — enum (NYCENET, DERIVED) — NYCENET for DOE schools where available, DERIVED for charters and schools NYCENET doesn't cover
• description — text
• methodology_notes — text (paste from NYCENET source for NYCENET groups; describe k-NN inputs for DERIVED groups)
• has many: Schools (for that year)

Sourcing strategy: scrape NYCENET portal for DOE schools (authoritative). For charters and brand-new schools NYCENET doesn't yet cover, compute a derived peer group using k-nearest neighbors on SchoolYearDemographics + grade band. Both kinds live in this table side-by-side, tagged via source.

MetricDefinition

The canonical catalog of every metric we track. Decoupled from values so we can add metrics, change definitions, or deprecate without touching value rows.

• key — string PK (snake_case, e.g., ela_grade3_proficiency, chronic_absenteeism_rate)
• display_name — string
• domain — enum (PERFORMANCE, CLIMATE, ACCESS, DEMOGRAPHICS)
• subdomain — string (e.g., Test Scores, Bullying, Attendance)
• applicable_grade_bands — enum array
• source_name — string
• source_url — string
• unit — enum (PERCENT, RATE_PER_100, COUNT, SCORE_0_100, INDEX)
• direction — enum (HIGHER_BETTER, LOWER_BETTER, NEUTRAL)
• description — text (how it's calculated, caveats)
• derived — bool (true if computed by us, not a raw source field)

SchoolYearMetric

The long-format fact table. One row per (school, year, metric, subgroup).

• school_dbn — FK
• year — string (YYYY-YY)
• metric_key — FK
• subgroup — enum (default ALL; also BLACK, HISPANIC, WHITE, ASIAN, MULTI_RACIAL, ELL, FORMER_ELL, NEVER_ELL, SWD, NON_SWD, ECON_DIS, NOT_ECON_DIS, MALE, FEMALE, TEMP_HOUSING)
• value — numeric
• denominator — numeric nullable (raw N where applicable — keeps proportions honest)
• comparison_group_percentile — numeric nullable (precomputed for outlier flow; ALL subgroup only at v1)
• citywide_percentile — numeric nullable
• suppressed — bool (cell suppressed for student privacy)
• source_load_id — FK to DataLoad
• Unique: (school_dbn, year, metric_key, subgroup)
• Indexes on (metric_key, year, subgroup) for outlier queries, (school_dbn, year) for school pages.

Subgroups are ingested wherever the source publishes them. Most NYCDOE files publish race/ethnicity, ELL, SWD, and economic-need breakouts — not ingesting them now means re-ingesting later, and the equity analysis is the most-asked researcher question.

Survey + SurveyQuestion + SurveyResponse

Full NYC School Survey ingestion — all respondent groups, all questions, not just the ones called out in the email. The narrow metrics (bullying, teacher trust, recommend school) are exposed as named MetricDefinition entries that read from SurveyResponse under the hood.

Respondents: STUDENT, TEACHER, PARENT (the three survey populations).

Survey

• id — string PK (year, e.g., 2023-24)
• administration_window_start, _end — date
• response_rate_student, _teacher, _parent — numeric (citywide)

SurveyQuestion

• question_id — string (stable across years where possible; NYCDOE-assigned)
• year — string (versioned because wording changes)
• text — string
• domain — enum (one of the NYCDOE framework: RIGOROUS_INSTRUCTION, COLLABORATIVE_TEACHERS, SUPPORTIVE_ENVIRONMENT, EFFECTIVE_LEADERSHIP, FAMILY_TIES, TRUST)
• subdomain — string (e.g., Bullying, Teacher Trust, Recommend School)
• scale — enum (LIKERT_4, LIKERT_5, BINARY, FREQUENCY)
• respondent_types — enum array (subset of STUDENT, TEACHER, PARENT — some questions only go to one group)
• direction — enum (HIGHER_BETTER, LOWER_BETTER)
• tagged_metric_keys — string array (links back to named MetricDefinitions that use this question)
• Unique: (question_id, year)

SurveyResponse

The full distribution per school × year × question × respondent.

• school_dbn — FK
• year — FK to Survey
• question_id — FK
• respondent_type — enum (STUDENT, TEACHER, PARENT)
• n_respondents — int
• response_distribution — jsonb (e.g., {"strongly_agree": 0.45, "agree": 0.30, "disagree": 0.15, "strongly_disagree": 0.10})
• mean_score — numeric nullable (where the scale is meaningfully numeric)
• positive_pct — numeric nullable (top-2-box %, the most-cited summary)
• suppressed — bool (low N)
• source_load_id — FK
• Unique: (school_dbn, year, question_id, respondent_type)

SurveyDomainScore

NYCDOE publishes rolled-up domain scores too — we store them as authoritative, and also compute our own from the raw responses for transparency.

• school_dbn, year, respondent_type, domain
• nycdoe_score — numeric nullable (their published score)
• derived_score — numeric nullable (ours)
• citywide_avg_score — numeric
• Unique: (school_dbn, year, respondent_type, domain)

IncidentReport (NYSED SSEC)

State-reported safety/discipline incidents. Joins to School via the nysed_beds_code crosswalk.

• school_dbn — FK nullable (null if no crosswalk match — log + investigate)
• nysed_beds_code — string
• year — string
• incident_category — enum (BULLYING, WEAPONS, DRUGS_ALCOHOL, PHYSICAL_VIOLENCE, OTHER) — mapping documented in methodology
• subtype — string (NYSED's finer-grained label)
• count — int
• per_100_students — numeric (derived)
• source_load_id — FK
• Unique: (nysed_beds_code, year, incident_category, subtype)

DataLoad

Audit row per pipeline run. Crucial for journalist trust and researcher reproducibility.

• id — uuid PK
• source_name — string
• source_url — string
• year_covered — string
• loaded_at — timestamp
• rows_inserted, rows_updated, rows_suppressed — int
• script_path — string (which loader produced this)
• git_sha — string (code version)
• notes — text

SavedQuery (researcher use)

• id — uuid PK
• user_id — FK (auth.users)
• name — string
• query_json — jsonb (filter + axis selection)
• created_at, updated_at

Metric Coverage (from email)

Beyond the email asks, the full NYC School Survey (all questions, all three respondent groups) is ingested — the named metrics above are surfaced via MetricDefinition entries that point into SurveyResponse.

Auth

Default (Google OAuth via Supabase). Most pages public — school data is public.

Auth gates:

• Researcher saved queries
• Future: comments / annotations

Bulk export is public (no sign-in required) but rate-limited per IP. Researchers shouldn't need an account to download data; sign-in is for saved state, not access.

API Integrations

None at v1. All ingestion is bulk download + ETL.

AI Features

v1 ships without AI. Phase 2 additions, all Anthropic:

• Journalists: NL outlier search ("schools that improved most on absenteeism")
• Parents: narrative comparison summary across selected schools
• Researchers: correlation-explanation assist

Cron Jobs

Most sources release annually. Pipeline runs manually for v1. Phase 2:

• Annual re-pull per source
• Weekly check for new survey/test releases

Key UI / UX Notes

• Three clearly-distinct landing entries by persona; shared school page underneath.
• Always show the comparison group context when displaying a metric. Citywide averages mislead in NYC because schools serve very different populations. The journalist flow especially needs this — outliers vs. peers, not vs. all schools.
• Suppression visible, not hidden. Show "suppressed (small N)" rather than omitting. Trust requires showing what we don't show.
• Every metric tile has a methodology popover: source URL, year, calculation, caveats.
• Mobile-friendly for parents (browse on phones).
• Map view for parents (filter by neighborhood / commute distance).

Launch Sequence

Ship one persona at a time, in this order:

1. Journalists first. Most differentiated; outlier-finder + comparison-group framing is the unique value. Easiest to demo / earn press.
2. Researchers second. Mostly already-built once the data is in place — explore page + bulk export on top of the DB.
3. Parents last. Most polish-intensive UI; benefits from waiting until data + comparison framing are settled.

No AI features in v1. Get the data right first; AI on top of wrong data is worse than no AI. Revisit after the journalist flow ships.

Seed Data

• Full DBN list from NYC Open Data school directory (~1,900 schools incl. charters)
• Latest year of each named metric on first load (so the app is useful immediately)
• Then backfill all available historical years per source — most NYCDOE sources go back 10+ years. Five-year minimum for v1; full backfill in pass-2.

Environment Variables (additional)

None beyond app-factory defaults.

Overrides

• Repo visibility: private (default)
• Stack: app-factory default

Methodology Decisions

Resolved in spec round 1. Each gets its own file in methodology/ once we have data to back the choice up.

• Outlier definition (v1). Universal rule: top/bottom decile within the school's comparison group. Same rule for every metric in v1. Per-metric tuning (z-score for bounded proportions vs. Poisson tail for counts) is a v2 problem once we've seen distributions.
• Suppressed cells in outlier lists. Hidden by default. A "show all" toggle exposes them with a "low N" badge. Listing a school as an outlier on the basis of suppressed data is unfair.
• Survey rollup. Store both NYCDOE-published domain scores (nycdoe_score) and our own (derived_score). Surface NYCDOE by default; ours is a transparency toggle.
• Year normalization. Canonical form YYYY-YY (e.g., 2023-24). All loaders normalize at the source boundary.
• Unit of analysis. DBN, not building. When a source reports at the building level (occasionally true for HS course offerings), we map back to DBNs and accept some duplication rather than introducing a building-level entity.
• School universe. DOE public + NYC public charters. D75 / D79 / alt programs included but tagged via school_type and excluded from default comparisons.
• Historical depth. All available years per source. Five-year minimum for v1; full backfill in pass-2.
• Comparison group sourcing. NYCENET portal for DOE schools (authoritative); k-NN on demographics + grade band for charters and schools NYCENET doesn't cover. Tagged via ComparisonGroup.source.

Open Methodology Questions

Deferred until we look at the data.

1. HS course access metric. The HS directory xlsx has raw fields (AP courses, languages, dual-enrollment, advanced STEM). NYCDOE publishes no access score. Schema slot is in place (MetricDefinition with derived = true); derivation formula deferred until EDA on the directory data. Likely a tercile composite, but the weights are guesses without seeing the distributions.

2. School identifier crosswalk maintenance. NYC Open Data publishes a DBN ↔ BEDS crosswalk; we'll use it. Open question is how often to re-verify and how to handle schools that fall off the crosswalk (closures, mergers). Document in methodology/dbn-beds-crosswalk.md once we run the first load and see the join hit rate.

3. Branding / public name. Repo name nyc-edu-data is internal. A user-facing name is deferred until closer to launch.

Data Source Catalog

See data-sources/ for per-source detail (download URL, format, columns, gotchas, last refresh).