Handoff — the capacity and utilization work

Orientation for a developer picking up the enrollment, capacity and utilization work for NYC and Philadelphia, which sits beside the Miami-Dade build it was modelled on. It covers what exists and where it lives, how to run and verify it, and the things in it that look like bugs and are not.

Read this, then capacity-utilization-gaps.md, which is the register of what is still open.

What exists

• NYC. The SCA's Enrollment, Capacity & Utilization Report (the Blue Book): twelve school years, 2013-14 through 2025-26, with 2020-21 absent because the SCA never published it. Seven years come from NYC Open Data (gkd7-3vk7); five (2014-15, 2019-20, 2022-23, 2023-24, 2024-25) are parsed from the SCA's PDFs under a two-parser gate and committed as CSVs with a gate report beside each. Loaded at school grain, as six keys in school_year_metrics; the building-grain rows are rolled up in memory and not stored, and the packet computes its building figures from the source. Utilization is Blue Book enrollment (3-K and Pre-K included) over SCA target capacity (Pre-K rooms included). Shown as rows in the NYC school page's Access section (utilization badged "derived", with a one-line basis note), and on /methodology/analysis/enrollment-capacity.
• Philadelphia. The SDP Facilities Master Plan dashboard, one vintage (SY2024-25), pulled from a Qlik app into two committed CSVs (sites and buildings) and loaded at site grain (philly_sites) and school grain (four keys in philly_school_year_metrics), with philly_site_programs joining schools to sites. The building CSV is read by the packet, not loaded. A school's figure is its enrollment counted once, plus the Pre-K seats allotted across its own sites, over capacity summed across them; a school sharing a site with another directory school gets no school figure. Shown in a Facilities section on each Philadelphia school page, and on /philly/methodology/analysis/enrollment-capacity.
• Miami-Dade. Built separately (docs/cities/miami/06_enrollment_capacity_closures.md, data/analysis/miami-enrollment.json). Its SY2024-25 utilization block is one of the three rows the Philadelphia packet compares.
• Across the three. The comparison is on SY2024-25, in the Philadelphia packet's cross_city block and Philadelphia doc §4.2. The cities differ in denominator, universe and Pre-K basis, and each row names all three. No common-basis figure is computed.
• Descriptive, not ranked. Every capacity key carries subdomain = 'Facilities' and direction NEUTRAL. The subdomain keeps it out of both percentile batches, so it carries no percentile and no outlier ranking. The NYC movers page, which sorts by year-over-year change rather than by percentile, does list it like any other metric.

Where things are

How to run and verify

The runbook — installs, dry runs, probes, the cold-database load order and the row counts a full load produces — is gaps §0, and make capacity-help prints the same order.

Export DIRECT_URL to the target database before any load. Both seeders and both loaders connect through their loader library (scripts/loaders/_lib.ts, scripts/loaders/philly/_lib.ts), which uses DIRECT_URL (or POSTGRES_URL_NON_POOLING) whenever either is set, exported or in .env/.env.local, and DATABASE_URL only otherwise. An exported DIRECT_URL wins over one in the files, because dotenv does not override a value already set, but an exported DATABASE_URL does not win over a DIRECT_URL in them; a load run without an exported DIRECT_URL writes to whatever those files name. The psql steps read $DIRECT_URL from the shell alone.

What a correct run prints:

• nyc-bluebook.ts --dry-run: twelve years, each with check (iv) at 100% of single-building organizations within 1pp, max 0pp.
• philly/facilities.ts --dry-run: check (iv) 245/245 (max |diff| 0.0000), check (v) 285/285 sites, check (vi) 156/156 over 157 schools with a figure, and 183 more set aside on a shared site.
• philly/facilities.ts against a database: check (vi) 220/220 over 223 directory schools with a figure, and 39 more set aside on a shared site, then wrote 328 sites, 305 site↔school links, 892 school facts.

Full verification before committing:

npx tsc --noEmit
npx vitest run
npx tsx scripts/analysis/nyc-capacity-packet.ts --check
npx tsx scripts/analysis/philly-capacity-packet.ts --check
npx tsx scripts/loaders/nyc-bluebook.ts --dry-run
npx tsx scripts/loaders/philly/facilities.ts --dry-run
npx next build

On a checkout whose node_modules is a symlink, Turbopack refuses the build; npx next build --webpack works there.

If a figure changes, the probe, the design doc, the packet and the page all carry it, and they are expected to agree. --check on either packet diffs the committed JSON against a fresh run and exits non-zero on drift. The Philadelphia packet reads the NYC and Miami-Dade packets for its cross_city block, so regenerating either means regenerating it, and its --check fails until you do. Metric descriptions live in the database: after editing either definitions file, rerun its seeder.

Re-parsing a Blue Book year is a separate, deliberate step — it is not part of that sequence, and the committed CSVs are what the loader and the packet read. Gaps §1 says how the PDFs are obtained and how a parsed year enters the panel:

python -m pipeline_nyc.parse.bluebook <year> <pdf> [<pdf>...] \
  --snapshot=YYYY-MM-DD --evidence=<section-B measurement>.json

Things that look like bugs and are not

A failed check stops the load. That is the design. Both loaders refuse to write rather than write something the transform cannot vouch for:

• NYC: check (iv), which recomputes utilization from enrollment and capacity and compares it with the publisher's own column (100% at max 0pp on all twelve years, the five parsed ones included, which is the main evidence those parses are right); an unrecognised snapshot vintage (UnknownSnapshotError); a parsed year whose gate report is missing or failed.
• Philadelphia: check (iv), published site utilization against (enrollment + Pre-K allotment) ÷ capacity; check (v), enrollment with allotment against enrollment plus allotment on every site; check (vi), a school whose capacity sits on one of its sites against that site's published utilization; a school whose own sites carry different enrollments. The Philadelphia packet, which reads the building CSV, stops on a repeated building whose rows disagree.

Each guards an assumption the transform relies on. If one fails, the source changed shape or a parse is wrong; do not relax the threshold, skip the year or widen the tolerance to get a load through. The Philadelphia school-grain checks run before anything is written, so a refusal leaves the tables as they were.

Split-site check (iv) is reported, not gated (NYC). A school across several buildings gets one row per building; annex rows often carry a null capacity while repeating the school-wide utilization. So the published per-row utilization is not the utilization of that row's enrollment and capacity, and the loader reports split-site agreement separately (51/209 within 1pp on 2025-26, max 75pp) without gating on it.

The same quantity is reported over two universes. Co-location reads 35.7–41.3% from the loader (every building in the extract, 1,383–1,466 of them) and 38.5–42.1% from the packet (buildings carrying both an enrollment and a capacity, 1,311–1,334). Both are correct across all twelve years. Check which denominator a figure uses before concluding two numbers disagree.

NYC's school count differs between the packet and the database. The packet joins organizations to DBNs against the directory-plus-Snapshot union, the loader against the schools table of the database it writes to, so the stored count depends on that table. Production's is built from the 2019-20 School Locations extract and lacks the schools opened since: loaded there, the two counts agree within one school through 2019-20 and diverge after it, to 1,702 in the packet and 1,671 in the database for 2024-25 (NYC doc §10). The packet's figure is the one the pages cite.

The Philadelphia dry run and the load report different check (vi) counts. The dry run never opens the database, so it cannot read the school directory and runs the roll-up over every link in the extract: 157 schools with a figure and 183 more set aside on a shared site, because a school's own Pre-K programme counts as a second school there. The load runs over directory schools only: 223 with a figure and 39 more set aside on a shared site.

A Philadelphia school figure can match no per-site figure SDP publishes. Three split campuses (2210, 7440, 8770) have capacity on two sites of their own. SDP's per-site utilization divides the whole school by one site's capacity (Hancock, 8770: 194.6% on S100, 256.4% on S130); the school figure divides by the sum (110.6%), the rule the dashboard's own per-school measure, Site Utilization (MAP), uses. The site-grain distribution in the packet keeps SDP's per-site basis (Philadelphia doc §4.1).

Snapshot years are measured, not read off the file. Each of the twelve vintages was matched against every year of the DOE Demographic Snapshot and assigned the year with the minimum median absolute enrollment difference — 0 students in all twelve, runner-up margins 18–24 students. The publication lag runs three to eighteen months, so the posting date gives wrong answers. UnknownSnapshotError aborts rather than inferring. The five parsed years are keyed on the audited-register date their report states (2014-10-31, 2019-10-31, 2022-10-31, 2023-10-31, 2024-10-31) because a PDF has no posting date; the key is only an identifier for the vintage, and the school year beside it in SNAPSHOT_YEAR was measured. Date-stamp anything new the same way (bluebook_util.py section B) before adding it.

philly_site_programs has no fixed expected count. The extract carries 385 site↔school pairs; the loader writes only those whose ULCS is already in philly_schools and prints how many it skipped (80 on the current extract, all Pre-K programmes). The invariant is 385 − skipped, not 385.

On the PDF years, absent is not zero. The printed report leaves a blank where the Socrata extract carries a 0, in the PK-capacity and cluster-rooms-needed columns, on about 2% of rows — 62 of 3,132 on the control year, all of them split-site organizations. The parser reads those as absent rather than inventing a zero, because the ink is not on the page. The blank-vs-zero rule it does apply — if any of the three "+" columns carries a value, the blanks among them are zeros — is exact on all 1,660 rows where anything is printed. This is a floor of the source, not a parse defect: anything doing arithmetic on prek_capacity has to treat absent as unknown, not as zero, and a per-year total of Pre-K seats is a lower bound on the five PDF years in a way it is not on the seven Socrata ones.

The capacity keys carry no percentile. By design: they are descriptive. subdomain = 'Facilities' keeps them out of both percentile batches, and each batch clears any percentile stored on a Facilities row before it ranks. The outlier and mover dropdowns still list them. Selected on an outlier page, they show no results; the NYC movers page sorts them by year-over-year change like any other metric (Philadelphia has one year, so no movers). A new capacity key needs that subdomain. The SQL uses IS DISTINCT FROM so that keys with no subdomain stay in.

What is verified and what is not

Verified, and re-runnable:

• NYC enrollment against the federal CCD, r = 0.9998 / 0.9997, with the DOE's own Snapshot as a control.
• NYC capacity against the DOE room inventory, R² ≈ 0.92 over 28 pre-registered instructional functions. Same publisher, so a consistency check, not an independent one.
• The retired Blue Book extract: 1,382 buildings, 100% exact, r = 1.0000.
• Check (iv) in both cities — NYC at 100%, max 0pp, on all twelve years; Philadelphia 245/245 sites.
• Philadelphia's school roll-up against the district's own per-site figures: check (vi), 220/220 on every load. Two further comparisons are one-off measurements that no committed script prints (Philadelphia doc §3.2): the dashboard's per-school Site Utilization (MAP) matches the roll-up's numerator and capacity on all 23 multi-site District schools, and the loaded school figures correlate with the NCES 2024 enrollment count at r 0.9993 (median difference 2 students, 222 schools).
• The PDF parser against a control. 2025-26 is published on Socrata and available as a PDF. The parser run on that PDF gives 3,132 rows against 3,132, no key on one side only, no duplicate keys, and 43,700 of 43,848 cells identical — 0.338% divergence, in three understood groups (124 blank-vs-zero cells, 18 where the extract blanks building columns the parse repeats and rollUpBuildings reconciles, 6 cosmetic whitespace). Through the repo's own transform the parsed rows reproduce Socrata's answers: the same 1,776 organizations, the same 1,384 buildings, and an identical check (iv) — 1,496/1,496 single-building organizations within 1pp at max 0pp, split-site 51/209 at max 75pp.
• The row counts a full load writes (gaps §0), against a real database.

Not verified, and worth holding in mind:

• No second publisher states NYC capacity. Enrollment has one; capacity does not. The packet's qa.not_verified says so.
• The five parsed years have no second publisher either — Socrata never carried them, so the two-parser gate, the date-stamp measurement and check (iv) are the whole of the evidence for them. The 2025-26 control is what makes those three checks interpretable rather than self-referential.
• NYC has no public closure list, so nothing here supports a predictive claim. This is the gap that would change the picture most and it is a grade C.
• Philadelphia's dashboard carries a second, older capacity figure. On the 207 sites carrying both, the share under 50% reads 25.1% on site capacity and 31.4% on the building figure (each building counted once), a 6.3-point swing; in aggregate the two nearly agree. The app labels the building figure its 2017 ("OLD") capacity and documents the method for neither figure, and the comparison divides 2024-25 enrollment by that 2017 figure. No served figure uses it (gaps §5, Philadelphia doc §6.5).

How to work

The repo's own norms:

• Derive quantities from the process rather than quoting constants; state a decision rule as a lift over a base rate or a test, not a bare threshold.
• Validate against a reference constructed before looking at the output.
• Explain an effect by decomposition and ablation, not narrative.
• When a question is answerable from data in hand, measure it rather than choosing by taste.
• Report negative results next to positive ones. Several of the most useful findings here are negative — the SCA never published 2020-21, the Snapshot cannot supply a closure reference, the SDP report archive holds no capacity figures, and splitting the NYC decomposition by segment does not change the endpoint reading: the enrollment term is negative in every segment but one, and that one (2022-23 → 2023-24) is a +0.4 pp rise (NYC doc §4.3).
• Write reader-facing text as a description of what exists, per docs/writing-style.md: what each figure is and how it is computed, its hazards stated as hazards, and its limits.