# NJ DOE Fall Enrollment Reports

Reporter's notebook for the founding dataset of njschooldata: where it comes
from, the three container formats it's shipped in since 1998, how it joins to
everything else, and every oddity worth knowing before you trust a number.
The public-facing explainer lives at
[`site/data/fall-enrollment/`](../../site/data/fall-enrollment/index.html)
(rendered at njschooldata.fyi/data/fall-enrollment/); the source ledger and
the district-figure reconciliation this page's Validation section cites live
in [`soma-student-population/SOURCES.md`](../../soma-student-population/SOURCES.md).

```toml ergo
[dataset]
ergo = "0.4"
slug = "fall-enrollment"
title = "NJ DOE Fall Enrollment Reports"
publisher = "NJ Dept. of Education"
subject = "https://www.nj.gov/education/doedata/enr/"
source_url = "https://www.nj.gov/education/doedata/enr/"
pitfall = "'Two or More Races' and 'Native Hawaiian/Pacific Islander' don't exist before 2006-07 — and the warehouse stores 0, not NULL, for them in every earlier year, so a naive trend line reads a definitional change as a demographic swing."
status = "live"
confidence = "A"
updated = "2026-07-21"
implementation = "https://github.com/lavallee/njschooldata"

[dataset.coverage]
years = "1998-99 → 2025-26 (28 yrs)"
grain = "school year × school; also rolled up to school year × district"
entities = "744 districts, ~3,038 schools, all 21 NJ counties (plus charter / state-operated groupings)"

[dataset.access]
keys = ["county_code", "district_code", "school_code", "school_year"]
```

## Where this data now comes from

njschooldata no longer downloads or parses this source. Aquifer owns the
acquisition, the schema-era parsers, and the immutable release; the issues below
that describe parsing or source format are recorded against the producer's own
registry and are monitored here rather than handled here. The consumer adapter
installs an exact offline pin, and the issues still marked as handled are the
ones this repo genuinely handles.

Rollback is no longer "re-run the local builder" — the local builder is gone.
Roll the pin back to a retained release and re-run the adapter.

## What it is

An annual fall headcount of every student in every NJ public school and
district, broken out by grade, race/ethnicity, and economic/program status.
It's the first warehouse this project built, and it's still the primary join
target for every other dataset here (School Performance Reports, budgets,
district source notes all key on the same `(district_code, school_code,
school_year)` triple). The state publishes **one ZIP per school year** at
the source URL above — 28 years, three completely different file formats,
and a set of race/ethnicity categories that isn't even constant within one
of those formats.

## Access

Our loader scrapes the index page at
`https://www.nj.gov/education/doedata/enr/` and regex-matches
`enr(\d{2})/[Ee]nrollment_(\d{4})\.zip` links (`list_years()`) — so a new
year shows up automatically once the state posts it, no hardcoded year list.
Each match is fetched as `https://www.nj.gov/education/doedata/enr/enr<EE>/<cap>_<SSEE>.zip`,
trying both `enrollment` and `Enrollment` capitalization (`fetch()`) — the
state isn't consistent about it. Raw zips are cached locally and are
re-fetchable at any time.

## Structure

### Three format eras

| Era | Years | Container | Shape |
|---|---|---|---|
| **A** | 1998-99 → 2008-09 (11 yrs) | One CSV, `STAT_ENR.CSV` | One row per school × program/grade. Race is sex-coded columns — `WHM`/`WHF`, `BLM`/`BLF`, `HIM`/`HIF`, `ASM`/`ASF`, `AMM`/`AMF` — summed male+female for a race total. |
| **B** | 2009-10 → 2018-19 (10 yrs) | Excel "long" workbook. 2009-10 and 2010-11 are legacy binary `.xls`; 2011-12 on are `.xlsx`. Filenames drift: `enr.xls`, `enr.xlsx`, `EnrollmentReport.xlsx`. | A long "SQL Results"-style sheet: one row per school × grade × program, race still race×gender columns, header spellings shift (`WhiteM` vs `WHM`). |
| **C** | 2019-20 → 2025-26 (7 yrs) | Modern `.xlsx` with separate **State / District / School** sheets. | One row per school, plainly-named columns (`White`, `Black`, `Hispanic`…), each count column immediately followed by its percent twin. |

The loader's three parsers — `parse_stat_csv` (A),
`parse_long_workbook` (B), `parse_modern` (C) — are dispatched by
`parse_year()` from the zip's actual contents, never the year label. See
the Issues section below for exactly how each era's quirks are handled.

### Normalized schema

All 28 years land in one SQLite file, three tables (~69,300 `enrollment`
rows, ~18,400 `district_enrollment` rows):

```sql
CREATE TABLE enrollment (
  school_year TEXT, county_code TEXT, county_name TEXT,
  district_code TEXT, district_name TEXT, school_code TEXT, school_name TEXT,
  total REAL,
  white REAL, black REAL, hispanic REAL, asian REAL,
  native_american REAL, hawaiian_pi REAL, two_or_more REAL,
  pk REAL, k REAL, g1 REAL, g2 REAL, g3 REAL, g4 REAL, g5 REAL, g6 REAL,
  g7 REAL, g8 REAL, g9 REAL, g10 REAL, g11 REAL, g12 REAL,
  free_lunch REAL, reduced_lunch REAL, ml_learners REAL,
  migrant REAL, military REAL, homeless REAL,
  PRIMARY KEY (school_year, county_code, district_code, school_code)
);
-- district_enrollment mirrors this (no school_code/name; adds
-- synthesized INTEGER DEFAULT 0), keyed (school_year, county_code, district_code).
-- school_name_history: (county_code, district_code, school_code, school_name,
-- first_year, last_year, years) — one row per code+name pair ever seen.
```

Counts are `REAL`, not `INTEGER` — shared-time students are reported as
halves (see `fall-enrollment/fractional-shared-time-counts`). Gender-by-race
exists in the Era A/B source files but isn't loaded into any column here —
a scope choice, not a data defect.

## Joins

Practical join key: `(district_code, school_code, school_year)` for
schools, `(district_code, school_year)` for districts. `district_code` is
zero-padded to 4 digits and `school_code` to 3 (`.zfill(4)` / `.zfill(3)`)
in every era's parser and is normally stable across a school's history —
but it is not guaranteed globally unique at the school-year grain. The source
contains one known 1999-00 collision, `3530-050`, whose two rows describe New
Brunswick High and Rutgers-Douglas Center; downstream consumers must quarantine
both rather than guess or overwrite. See `fall-enrollment/school-identity-vs-name-drift`
for why `county_code` and `school_name` are also unsafe as universal keys. This is the same code shape the
School Performance Reports warehouse joins against
(`docs/data-sources/school-performance-reports.md`).

## Issues

### Three incompatible file containers since 1998

```toml ergo
[issue]
id = "era-format-drift"
title = "A parser must detect the era (CSV, long workbook, or School/District sheets) before it can read a row"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "wb.sheetnames / file extension differ across years: a bare .csv (Era A, 1998-99→2008-09), an Excel 'long' workbook (Era B, 2009-10→2018-19), or an .xlsx with separate School/District/State sheets (Era C, 2019-20→2025-26). parse_year() picks the reader by inspecting the zip contents and workbook shape, never the year label."

[issue.scope]
all = true
```

Any code written against one era's shape (a fixed CSV column order, say)
silently mis-reads or crashes on the other two. `parse_year()` is the single
place that has to get this right; every downstream table depends on it.

### Era B ships as legacy binary .xls with drifting filenames

```toml ergo
[issue]
id = "era-b-file-quirks"
title = "2009-10 and 2010-11 are binary .xls (openpyxl can't open them directly); the filename inside the zip also isn't fixed"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "openpyxl.load_workbook raises on the 2009-10/2010-11 files unless converted first; the zip's single data file is named enr.xls, enr.xlsx, or EnrollmentReport.xlsx depending on the year, never a fixed name."

[issue.scope]
years = "2009-10 → 2018-19 (binary .xls specifically 2009-10, 2010-11)"
```

`tools/xls_reader.py` reads the OLE2 container and the BIFF8 records directly,
so the two binary years need nothing outside the standard library. Filename
drift is handled by matching on extension (`*.csv` / `*.xlsx` / `*.xls` inside
the zip) rather than a fixed name, so a new filename doesn't need a code change
— only a new *extension* would.

Until 2026-07 this shelled out to `libreoffice --headless --convert-to xlsx`
instead, which was worse than it looked: on a host without LibreOffice the build
printed one `ERROR` line per affected year and carried on, producing a warehouse
missing 2009-10 and 2010-11 entirely — about 4,950 school-year rows — with a
normal-looking success summary at the end. A rebuild on a fresh machine
therefore *silently* shrank the series. Reading the format directly removes both
the external dependency and that failure mode.

### The same field's column header spells differently across years

```toml ergo
[issue]
id = "header-naming-variants"
title = "Race/sex columns use different header spellings by era (WhiteM vs WHM, and others)"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "RACE_PATTERNS lists every observed header prefix per race×sex column, e.g. white: 'whm'/'whitem' (male), 'whf'/'whitef' (female); two_or_more: 'mum'/'2moreracesm'/'twoormorem'. A parser hard-coded to one spelling finds zero matching columns in the other era's file — a silent all-null column, not an error."

[issue.scope]
years = "1998-99 → 2018-19 (Era A/B; Era C uses plain names like White, Black, Hispanic)"
columns = ["white", "black", "hispanic", "asian", "native_american", "hawaiian_pi", "two_or_more"]
```

Matching is done by header prefix after normalizing to lowercase
alphanumerics (`nz()`), against every spelling `RACE_PATTERNS` has on
record — not a single canonical name.

### Some Era B years mislabel the grade column "Total" on every row

```toml ergo
[issue]
id = "grade-mislabeled-total-prgcode"
title = "Grade_Level reads 'Total' for every row in some Era B years; the real grade is in a separate PRGCODE column"
effect = "corrupts"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "In affected years, every row's grade-level column literally reads 'Total', with the real grade level carried in PRGCODE instead."
misuse = "Trusting Grade_Level at face value in an affected year either collapses every grade into one bucket, or — because the accumulator treats a 'Total'-labeled row specially — makes every per-grade row look like the school's authoritative Total row."

[issue.scope]
years = "some years within Era B (2009-10 → 2018-19); exact years not enumerated in the source or in the builder's comments"
columns = ["Grade_Level", "PRGCODE"]
```

`parse_long_workbook` builds a list of grade-label candidate columns
(`Grade_Level`, `PRGCODE`, `ProgramName`, …) and picks whichever one on a
given row actually resolves to a real grade via `grade_key()`, falling back
to a `Total`-labeled candidate only if none do.

### Header rows are not always row 1

```toml ergo
[issue]
id = "header-row-not-first"
title = "Some sheets bury the header a few rows down; row 1 can be a title or note"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "parse_long_workbook scans rows 1-6, and parse_modern's hdr() scans rows 1-9, for a row containing a recognizable district-code-style label ('distid'/'districtcode'/'dist', or 'districtname') before treating anything as data."

[issue.scope]
years = "Era B and C (2009-10 → 2025-26)"
```

Assuming row 1 is the header misreads a title/note row as data (or as an
empty/garbage header that matches nothing downstream). Both scanners give
up after a fixed number of rows rather than scanning the whole sheet.

### Era B and Era C are both .xlsx; only the sheet names tell them apart

```toml ergo
[issue]
id = "era-b-vs-c-ambiguous"
title = "File extension alone can't distinguish the long-workbook era from the School/District-sheet era — both are .xlsx"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "parse_year() checks wb.sheetnames for at least one sheet with 'school' in its name and one with 'district' in its name; only then does it call parse_modern (Era C). Otherwise the file is treated as an Era B long workbook."

[issue.scope]
years = "2009-10 → 2025-26 (era boundary)"
```

There's no format-version marker in the file itself — the sheet-name test
*is* the era boundary detector.

### Era C pairs every count column with a percent column right next to it

```toml ergo
[issue]
id = "count-percent-adjacency"
title = "Modern School/District sheets place a % column immediately after every count column"
effect = "corrupts"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "hdr() explicitly skips any header cell whose raw value contains '%' when building the column index, so the first (count) column of each count/percent pair wins the name."
misuse = "Reading the wrong member of a count/percent pair silently substitutes a 0-100 percentage for a headcount, or vice versa, with no type error to flag it."

[issue.scope]
years = "2019-20 → 2025-26 (Era C)"
columns = ["White", "Black", "Hispanic", "Asian", "*Percent*", "*%*"]
```

The rule, verbatim from the code comment: "count cols precede their %
twins" — take the first column matching a given name, skip the rest.

### Grade 8 header misspelled "Eight Grade" drops the column for three years

```toml ergo
[issue]
id = "grade8-misspelled-header"
title = "The grade-8 header reads 'Eight Grade' (missing the 'h') in 2020-21 through 2022-23"
effect = "corrupts"
type = "format"
status = "monitor"
discovered = "2026-06"
detection = "The School and District sheets header grade 8 as 'Eight Grade' in 2020-21, 2021-22, and 2022-23; every other year — including the correction in 2023-24 — spells it 'Eighth Grade.'"
misuse = "A column matcher looking only for the correct spelling reads every NJ school's grade-8 enrollment as blank for these three years — most visible at middle schools, where grade 8 is the terminal cohort."

[issue.scope]
years = ["2020-21", "2021-22", "2022-23"]
columns = ["Eighth Grade", "Eight Grade", "g8"]
```

`parse_modern`'s grade-column map matches both spellings for grade 8
(`("g8", ("eighthgrade", "eightgrade"))`) — a one-line fix, but one that
would otherwise zero out a real column for every school in the state.

### New Jersey's race/ethnicity categories are not the same set across all 28 years

```toml ergo
[issue]
id = "race-category-scope-change"
title = "'Two or More Races' and 'Native Hawaiian/Pacific Islander' didn't exist before 2006-07; Asian's meaning narrows that year"
effect = "misleads"
core = true
type = "coding"
status = "open"
discovered = "2026-06"
detection = "White, Black, Hispanic, and Native American run all 28 years unchanged. Native Hawaiian/Pacific Islander and Two or More Races both first appear in 2006-07; before that NJ used a 5-category scheme and Pacific Islander students were counted inside Asian."
misuse = "Comparing a pre-2006-07 Asian share to a post-2006-07 Asian share as the same universe — pre-2006-07 Asian includes Pacific Islander, post-2006-07 doesn't. Comparing raw 'Two or More' shares across the 2006-07 line compares a real population to a structural zero. The demographic-shift explorer therefore calculates only pre/pre five-category pairs or post/post seven-category pairs; it does not assign a shift or rank across the reporting boundary."

[issue.scope]
years = "all (break at 2006-07)"
columns = ["hawaiian_pi", "two_or_more", "asian"]
```

This is the single most consequential fact about this dataset. It sits
*inside* the CSV era (Era A runs 1998-99 → 2008-09, the category break is
2006-07) — a separate axis from the container-format eras above; a file can
be Era A *and* pre-2006-07, or Era A *and* post-2006-07. The
schema-bound comparison logic described in `misuse` above is implemented in the
demographic-shift explorer downstream, not in the producer
itself — the Aquifer parser faithfully carries the categories forward as the
source presents them (see the next issue for exactly how "forward" is
implemented for the missing years).

### The legacy producer stored absent pre-2006 race categories as zero

```toml ergo
[issue]
id = "pre-2006-race-fields-zero"
title = "The legacy producer stored 0 (not NULL) for hawaiian_pi/two_or_more before the categories existed"
effect = "corrupts"
core = true
type = "coding"
status = "monitor"
discovered = "2026-07"
detection = "The legacy producer emitted zero before the categories existed. The pinned Aquifer release and consumer adapter now emit NULL plus structural-absence measure states; the old builder remains rollback-only."
misuse = "AVG(two_or_more / total) computed across the full 28-year series reads the pre-2006-07 years as '0% multiracial' rather than 'category did not exist' — silently dragging any long-run multiracial-share trend toward zero. A NULL-aware filter (WHERE two_or_more IS NOT NULL) does not catch these rows, because they aren't NULL."

[issue.scope]
years = "1998-99 → 2005-06 (before Two or More / Pacific Islander existed in the source)"
columns = ["hawaiian_pi", "two_or_more"]
tables = ["enrollment", "district_enrollment"]
```

The pinned Aquifer v3 baseline repairs all 19,360 pre-boundary school rows and
5,112 district rows to SQL `NULL`, while its typed `measure_values` projection
records `structural-absence`. The retained rollback builder still has the old
accumulator behavior, so invoking that rollback intentionally restores the
known zero defect until the Aquifer adapter is run again.

### A school's Total row and its per-grade rows must never be summed together

```toml ergo
[issue]
id = "total-vs-grade-double-count"
title = "Era A/B files carry an authoritative Total row per school plus separate per-grade (and sometimes special-ed) rows"
effect = "corrupts"
type = "measurement"
status = "monitor"
discovered = "2026-06"
detection = "Era A/B files have one Total-labeled row per school plus per-grade rows, and sometimes special-ed-by-disability rows that are already folded into Total."
misuse = "Summing every row for a school (Total + grade rows + special-ed rows) roughly doubles or triples its reported enrollment."

[issue.scope]
years = "1998-99 → 2018-19 (Era A/B, row-per-grade files)"
tables = ["enrollment", "district_enrollment"]
```

`_accumulate()` captures a Total-labeled row into `S["total_row"]` and
returns immediately — it never also lands in the grade-sum accumulator.
`_finalize()` prefers the Total row's own total/race/FRL figures and only
falls back to summing grade rows when no Total row was seen at all.

### Per-grade counts can fall short of the school total — ungraded students are real

```toml ergo
[issue]
id = "ungraded-students-shortfall"
title = "A school's pk..g12 columns can sum to less than its total; the gap is ungraded (mostly special-ed) students, not a parsing error"
effect = "context"
type = "definitional"
status = "open"
discovered = "2026-06"
detection = "Roughly three-quarters to nine-tenths of schools have grades summing to the full total in a given year; special-services schools (regional day schools, schools for the deaf, county ed-services commissions) can sit near zero graded against a real total. Public explainer's examples: Vineland Senior High ~89% of total in graded rows, Newark's Abington Avenue School ~85%."
misuse = "Treating (total - sum of grades) as a data-quality defect, or silently redistributing the gap across grades to make the columns add up. The public explainer's own working rule: trust per-grade detail for a year only when grades sum to >=90% of total; otherwise fall back to the Total row."

[issue.scope]
years = "all"
columns = ["pk", "k", "g1", "g2", "g3", "g4", "g5", "g6", "g7", "g8", "g9", "g10", "g11", "g12", "total"]
entities = "special-services schools, and any school with a sizable special-ed program"
```

Real, not a bug: NJ's headcount counts ungraded students in the total but
in no grade. `build_enrollment_db.py` stores both `total` and the per-grade
columns as given, faithfully preserving the shortfall rather than papering
over it; the ≥90%-trust threshold mentioned in `misuse` is applied by
downstream consumers, not enforced in this file.

### Shared-time students are reported as fractional (half) counts

```toml ergo
[issue]
id = "fractional-shared-time-counts"
title = "Counts are not always integers — shared-time students split between schools are reported as halves"
effect = "corrupts"
type = "measurement"
status = "monitor"
discovered = "2026-06"
detection = "num() parses to float and only rounds to a whole number when the value already is one (int(f) if f == int(f) else round(f, 1)); roughly 4,400 fractional totals exist across the series."
misuse = "Casting a count column to int, or assuming COUNT-like integer semantics, truncates legitimate shared-time halves — a student split between two schools is reported as 0.5 at each."

[issue.scope]
years = "all"
columns = ["total", "white", "black", "hispanic", "asian", "native_american", "hawaiian_pi", "two_or_more", "pk", "k", "g1", "g2", "g3", "g4", "g5", "g6", "g7", "g8", "g9", "g10", "g11", "g12"]
```

All `NUMERIC` columns are declared `REAL` in the schema for exactly this
reason — an `INTEGER` column would force a silent rounding decision on
every import.

### Rollup rows are embedded among real schools and districts

```toml ergo
[issue]
id = "rollup-rows-as-schools"
title = "School code 999 or a 'TOTAL' name is the district's own aggregate row; district code 9999 or a county/state name is a rollup, not a real district"
effect = "corrupts"
type = "entry"
status = "monitor"
discovered = "2026-06"
detection = "A row with school_code '999', or a school_name containing 'TOTAL', is the district's own aggregate row, not a building. A row with district_code '9999', or a district_name of 'County Total'/'State Total'/'NJ Total', is a county/state rollup."
misuse = "Loading these rows as ordinary schools or districts double-counts every student in that district/county/state on top of the real building- and district-level rows."

[issue.scope]
years = "all"
columns = ["school_code", "school_name", "district_code", "district_name"]
```

`_split()` reroutes a school-code-999/`TOTAL`-named row into the district
list instead of the school list; `is_aggregate()` drops district-code-9999
and county/state-named rows from `district_enrollment` entirely in
`main()`, so neither ever reaches the warehouse as a school or a district.

### Missing and suppressed cells appear as ".", "*", "N", or "-", not a blank

```toml ergo
[issue]
id = "suppressed-blank-cells"
title = "Missing/suppressed values show up as sentinel strings ('.', '*', 'N', '-'), not empty cells"
effect = "corrupts"
type = "suppression"
status = "monitor"
discovered = "2026-06"
detection = "num() treats '.', '*', 'N', '-', empty string, and any string made only of dots/spaces as missing, returning None rather than parsing it as zero or raising ValueError."
misuse = "Coercing a suppressed cell to 0 (rather than NULL) understates a school's count in any SUM/AVG that doesn't explicitly filter nulls, and makes a genuinely-zero school indistinguishable from a suppressed one."

[issue.scope]
years = "all"
columns = ["*"]
```

Every `NUMERIC` field routes through `num()`, so this handling is uniform
across totals, race counts, grades, and the economic/program columns
alike.

### School names drift; one code also collides across institutions in one year

```toml ergo
[issue]
id = "school-identity-vs-name-drift"
title = "School names change, and even (district_code, school_code) has one known school-year collision that must be quarantined"
effect = "corrupts"
type = "linkage"
status = "monitor"
discovered = "2026-06"
detection = "district_code (zfill 4) and school_code (zfill 3) are normalized in every parser and are normally stable across a school's history. SOMSD school 090 carries four names across one code. But 1999-00 has two semantically conflicting 3530-050 rows under different county codes: New Brunswick High and Rutgers-Douglas Center."
misuse = "Joining by school_name reads a renamed school as several entities; treating (district_code, school_code, school_year) as infallibly unique silently overwrites one side of the 1999-00 3530-050 collision; including county_code in a general join can still drop matches where that field is unreliable. Quarantine the known collision and fail closed on any new one."

[issue.scope]
years = "all"
tables = ["enrollment", "district_enrollment", "school_name_history"]
columns = ["school_name", "district_code", "school_code", "county_code"]
```

`school_name_history` records every `(county_code, district_code,
school_code, school_name)` combination ever seen, with first/last year and
a count of years — built once at the end of `main()` from a `GROUP BY`
over the finished `enrollment` table, so a rename reads as one code with
two name-rows in that table, not as a school disappearing and a new one
appearing.

### Some district-year rows are our own sums of member schools, not the state's

```toml ergo
[issue]
id = "synthesized-district-rows"
title = "district_enrollment mixes source-published district totals with rows we synthesized by summing member schools, distinguished by a synthesized flag"
effect = "context"
type = "measurement"
status = "monitor"
discovered = "2026-06"
detection = "synth_missing_districts() sums NUMERIC columns across a district's member schools for any (county_code, district_code) with no district-level row already present that year, and writes synthesized=1; source-published district rows carry synthesized=0."
misuse = "Treating every district_enrollment row as the state's own published district total. A synthesized row is our sum of that year's member schools and can disagree slightly with a source total that used its own rounding or suppression, in a year the source happens to also publish one."

[issue.scope]
years = "all"
tables = ["district_enrollment"]
columns = ["synthesized"]
```

Not a defect — a deliberate fill for years/districts where the source
gives school-level rows only, transparently flagged rather than silently
merged with the rest of the table.

### Free/reduced lunch, ML/migrant, and military/homeless columns each start (or gap) in different years

```toml ergo
[issue]
id = "non-race-dimension-coverage-phase-in"
title = "Non-race dimensions don't share one coverage window: military starts 2019-20, homeless 2009-10 then 2019-20 onward"
effect = "context"
type = "coverage"
status = "open"
discovered = "2026-06"
detection = "free_lunch/reduced_lunch and ml_learners/migrant now run continuously from 1998-99; military appears only from 2019-20; homeless appears in 2009-10 and then continuously only from 2019-20."
misuse = "Charting a district's military-connected or homeless counts back before their coverage began reads a source reporting gap as a demographic collapse to zero — though because these come out NULL rather than 0 (fall-enrollment/suppressed-blank-cells), a chart that doesn't skip nulls will show a gap, not a false zero, if it's built correctly."

[issue.scope]
years = "varies by column — see detection"
columns = ["ml_learners", "migrant", "military", "homeless"]
```

The free/reduced-lunch hole this issue originally described — 2010-11, 2011-12,
and 2019-20 through 2021-22 — was never a source gap. All five years carry the
data; three separate parser defects hid it. See
[`percent-only-composition-years`](#the-2019-20-through-2021-22-files-publish-composition-as-percentages-only),
[`prgcode-55-is-the-total-row`](#era-b-before-2012-13-marks-the-total-row-only-with-prgcode-55),
and [`era-b-extra-column-spellings`](#era-b-abbreviates-three-composition-column-names).
`free_lunch` and `reduced_lunch` are now populated for every year from 1998-99
to 2025-26.

Gender-by-race also exists in the Era A/B source files (1998-2018) but
isn't loaded into any warehouse column at all — a scope decision (see
Structure), not a coverage gap in an existing column, so it isn't scoped
here.

### The rollback producer shares a warehouse with eight other families

```toml ergo
[issue]
id = "rollback-producer-shares-the-warehouse"
title = "tools/build_enrollment_db.py used to unlink the whole warehouse, destroying every other family"
effect = "breaks"
type = "availability"
status = "monitor"
discovered = "2026-07"
detection = "The builder opened with `if DB.exists(): DB.unlink()` and then created three tables. data/njdoe_enrollment.sqlite holds 49 tables across nine families."
misuse = "Running the documented rollback command on a populated warehouse silently discards budgets, School Performance Reports, state aid, accountability, IDEA, staffing, and the chronic-absence snapshot, then prints a normal success summary."

[issue.scope]
all = true
```

Every sibling builder in this repo dropped only the tables it owned. This one
recreated the file. The warehouse is git-ignored, so the loss was silent until
a generator failed or a page rendered empty — and the builder's own summary line
reported success either way.

The producer itself is now retired, and the shape that made the bug possible is
gone with it. Each family owns a file under `data/families/`, written by its own
adapter and nothing else, and `data/njdoe_enrollment.sqlite` is composed from
them by `tools/compose_warehouse.py`. No adapter writes the composed warehouse,
so no adapter can destroy another family; and because every Aquifer family is
reproducible from its pinned release, losing the composed file costs one compose
run rather than any data.

### The 2019-20 through 2021-22 files publish composition as percentages only

```toml ergo
[issue]
id = "percent-only-composition-years"
title = "2019-20 through 2021-22 publish free/reduced lunch and the other composition dimensions as percentages with no count column"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-07"
detection = "The School and District sheets for those three years carry %Free Lunch, %Reduced Lunch, %English Learners, %Migrant, %Military and %Homeless and no matching count column; from 2022-23 both a count and a percent column appear."
misuse = "Reading the resulting NULLs as a reporting gap — the state never stopped collecting these — or treating a recovered count as an exact reported figure."

[issue.scope]
years = "2019-20 → 2021-22"
columns = ["free_lunch", "reduced_lunch", "ml_learners", "migrant", "military", "homeless"]
```

The parser deliberately skips any column whose header contains `%` so that a
count column wins its name over the percent twin beside it
([`count-percent-adjacency`](#count-and-percent-columns-sit-adjacent-with-near-identical-headers)).
For three years there was no count column to win, so every one of these six
dimensions came out NULL for every school.

The header scan now keeps the percent columns in a second map and recovers the
count as `percent x total enrollment`, rounded. Percentages are printed to one
decimal, so a recovered count sits within about ±0.05% of enrollment of the
true figure — under one student for a school of 2,000. **These three years hold
derived counts, not reported ones.** Twenty-seven schools in 2019-20 still come
out NULL because the source leaves their percentage blank; that is real
missingness and stays NULL.

### Era B before 2012-13 marks the Total row only with PRGCODE 55

```toml ergo
[issue]
id = "prgcode-55-is-the-total-row"
title = "2010-11 and 2011-12 ship no GRADE_LEVEL column, so nothing spells 'Total' and the authoritative row is marked only by PRGCODE 55"
effect = "breaks"
type = "coding"
status = "monitor"
discovered = "2026-07"
detection = "The 2010-11 and 2011-12 workbooks have PRGCODE but no GRADE_LEVEL column; from 2012-13 both are present and GRADE_LEVEL carries the literal 'Total'."
misuse = "Treating the resulting NULL free/reduced-lunch columns as a source gap for those two years."

[issue.scope]
years = "2010-11 → 2011-12"
columns = ["free_lunch", "reduced_lunch", "ml_learners", "migrant"]
```

Free and reduced-price lunch are reported only on each school's Total row, never
on its grade rows. The Total row is found by looking for a label that starts
with "total", which those two years supply nowhere — so the row fell through to
the "special-program / ungraded, already inside the Total" branch and was
discarded, taking the year's whole FRL column with it. PRGCODE `55` is the
marker. Verified for 2011-12: every one of the 3,174 code-55 row totals equals
the sum of that school's other rows, so the code is the total, not another
program.

### Era B abbreviates three composition column names

```toml ergo
[issue]
id = "era-b-extra-column-spellings"
title = "2009-10 abbreviates reduced-price lunch to RED_LUNCH and migrant to MIG, and ships a HOMELESS column the parser never looked for"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-07"
detection = "The 2009-10 header row reads FREE_LUNCH, RED_LUNCH, LEP, MIG, HOMELESS where 2011-12 onward reads FREE_LUNCH, REDUCED_LUNCH / REDUCED_PRICE_LUNCH, LEP, MIGRANT."
misuse = "Comparing a 2009-10 free/reduced-lunch rate against later years when only the free half was ever loaded — the 2009-10 rate reads about six points too low."

[issue.scope]
years = "2009-10"
columns = ["reduced_lunch", "homeless"]
```

This one distorts rather than blanks: `free_lunch` loaded fine, `reduced_lunch`
did not, so 2009-10's combined free-and-reduced rate read 25.9% against a true
32.2% — a six-point dip in the middle of the recession that looked like a real
finding. The column-name alternates now cover every observed spelling.

### The multilingual-learner column was renamed mid-series

```toml ergo
[issue]
id = "multilingual-learner-renamed"
title = "The column reads 'English Learners' through 2022-23 and 'Multilingual Learners' from 2023-24"
effect = "breaks"
type = "format"
status = "monitor"
discovered = "2026-07"
detection = "The 2022-23 School sheet header reads 'English Learners'; the 2023-24 sheet reads 'Multilingual Learners'. The same population, renamed."
misuse = "Reading the NULL as a year in which no district enrolled a multilingual learner."

[issue.scope]
years = "2018-19, 2022-23"
columns = ["ml_learners"]
```

The rename is a terminology change, not a definitional one — the same students
are counted before and after. Missing the earlier spelling nulled `ml_learners`
for whole years while every neighbouring column loaded normally.

## Validation

```toml ergo
[validation]
date = "2026-06-14"
method = "reconciled extract against district-published figures"
result = "7/7 spot figures match within rounding (SOMSD White % 1998-99: ours 43.8, published 44)"
```

The full reconciliation, from `soma-student-population/SOURCES.md`, against
South Orange-Maplewood's own Alves/NJDOE figures (forum deck, Nov 2025):

| Figure | Our extract | District-published |
|---|---|---|
| SOMSD White %, 1998-99 | 43.8% | 44% |
| SOMSD %FRL, 1998-99 | 17.0% | 16.9% |
| South Mountain White %, 1998-99 | 56.1% | 56% |
| South Mountain %FRL, 1998-99 | 5.6% | 5.57% |
| South Mountain White %, 2018-19 | 64.0% | 64.3% ("2019") |
| SOMSD White %, 2019-20 | 55.4% | 55.4% |
| SOMSD %FRL, 2024-25 | 14.1% | 14.1% |

```toml ergo
[validation]
date = "2026-07-10"
method = "traced build_enrollment_db.py's race-field accumulation with a synthetic pre-2006-07 CSV header (WHM/WHF...AMM/AMF, no PIM/HAWNTVM/MUM/2MORERACESM columns), run locally against the actual parse_stat_csv function"
result = "confirms fall-enrollment/pre-2006-race-fields-zero: hawaiian_pi and two_or_more compute as literal 0 for a school with no such source columns, not None/NULL — race_index() returns empty column lists, _race_vals() sums them to 0, and _finalize()'s 'v is not None' check accepts that 0 as a present value"
```

## Provenance

Latest published: 2025-26 (per the public explainer, "updated June 2026").
The pipeline re-derives its own year list from the live NJDOE index every
run (`list_years()`), so it picks up new years without a code change — only
a genuinely new container shape would require one. Working copy last
rebuilt ~2026-06; record any re-pull and its date here. The whole build is
reproducible from the public ZIPs alone: given the raw files, the era
detection, header scanning, column-variant matching, and row-classification
rules registered as issues above are everything an implementation needs
to rebuild the same schema from scratch (ours is linked from the
manifest's `implementation` field).

## Changelog

```toml ergo
[change]
date = "2026-07-10"
note = "Page created in ergo format: 18 issues registered from the public explainer, the builder, and the SOMA sources ledger — including the new discovery that pre-2006-07 rows store 0 (not NULL) for hawaiian_pi/two_or_more, verified against the builder."
issues = ["pre-2006-race-fields-zero"]
```

```toml ergo
[change]
date = "2026-07-16"
note = "Aligned the demographic-shift contract with the 2006-07 reporting break: pre/pre pairs use five categories, post/post pairs use seven, and cross-boundary pairs receive no shift or rank. Recorded the 1999-00 3530-050 school-ID collision and the requirement to quarantine both conflicting rows rather than guess or overwrite."
issues = ["race-category-scope-change", "school-identity-vs-name-drift"]
```