# 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](https://tools.nycenet.edu/resources/comp-group.html) 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 `MetricDefinition`s 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)

| Domain | Metric | Source | Grade band |
| --- | --- | --- | --- |
| Performance | 3rd grade ELA proficiency | [infohub test-results](https://infohub.nyced.org/reports/academics/test-results) | ES |
| Performance | 3rd grade math proficiency | infohub test-results | ES |
| Performance | 7th grade ELA proficiency | infohub test-results | MS |
| Performance | 7th grade math proficiency | infohub test-results | MS |
| Performance | 4-year graduation rate | [infohub graduation-results](https://infohub.nyced.org/reports/academics/graduation-results) | HS |
| Performance | College / career readiness rate | [infohub school-quality](https://infohub.nyced.org/reports/students-and-schools/school-quality/school-quality-reports-and-resources) | HS |
| Climate | Bullying — student-reported (survey) | [NYC School Survey](https://infohub.nyced.org/reports/students-and-schools/school-quality/nyc-school-survey/survey-archives) | All |
| Climate | Bullying — incidents | [NYSED SSEC](https://www.nysed.gov/information-reporting-services/ssec-school-safety-and-educational-climate) | All |
| Climate | Chronic absenteeism rate | [infohub attendance](https://infohub.nyced.org/reports/students-and-schools/school-quality/information-and-data-overview/end-of-year-attendance-and-chronic-absenteeism-data) | All |
| Climate | Teacher–principal trust (survey) | NYC School Survey | All |
| Climate | Teachers would recommend school (survey) | NYC School Survey | All |
| Access | HS course access index (derived) | [HS directory xlsx](https://infohub.nyced.org/docs/default-source/default-document-library/ose/fall-2025---hs-directory-datab85f64a0-05b9-439a-8e29-052ce60a5d86.xlsx) | HS |

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/`](./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/`](./data-sources/) for per-source detail (download URL, format, columns, gotchas, last refresh).

| Source | Format | Frequency | Notes |
| --- | --- | --- | --- |
| NYCDOE InfoHub — Test Results | xlsx | annual | ELA + math, grades 3–8 |
| NYCDOE InfoHub — Graduation Results | xlsx | annual | 4-yr + 6-yr cohort |
| NYCDOE InfoHub — School Quality Reports | xlsx | annual | CCR for HS |
| NYCDOE InfoHub — NYC School Survey | xlsx | annual | Student / Teacher / Parent — full survey |
| NYCDOE InfoHub — Attendance / Chronic Absenteeism | xlsx | annual |  |
| NYCDOE InfoHub — HS Directory | xlsx | annual | Source for course access metric |
| NYSED — SSEC Incident Reports | csv/xlsx | annual | BEDS code → DBN crosswalk needed |
| NYC Open Data — DOE School Locations / Directory | csv | rolling | Master school list + lat/lng |
| NYC Open Data — DBN ↔ BEDS Crosswalk | csv | rolling | Joining NYCDOE to NYSED |
| NYC Open Data — Charter School Directory | csv | annual | Charter universe (not in NYCDOE InfoHub) |
| NYCDOE InfoHub — Demographic Snapshot | xlsx | annual | Race / ELL / SWD / economic-need + subgroup breakouts |
| NYCENET — Comparison Groups | scrape | annual | tools.nycenet.edu/resources/comp-group.html |