# 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`](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 | what | where | |---|---| | The gap register — what is still open, and the grades | `docs/capacity-utilization-gaps.md` | | What the sources carry that the database does not store (the building panels, six Philadelphia site fields), and how to add it | `docs/capacity-utilization-gaps.md` §10 | | NYC findings, method, and the schema rationale | `docs/design/10_nyc_enrollment_capacity_utilization.md` | | Philadelphia findings | `docs/cities/philly/06_enrollment_capacity_utilization.md` | | Miami — the precedent both cities were modelled on | `docs/cities/miami/06_enrollment_capacity_closures.md` | | NYC transform (pure, no I/O) and its hazard notes | `scripts/loaders/_bluebook.ts` | | NYC loader (Socrata fetch, the parsed years, reporting, check (iv)) | `scripts/loaders/nyc-bluebook.ts` | | NYC metric keys | `scripts/loaders/metric-definitions-capacity.ts` | | NYC Blue Book PDF parser and its gate — parser A is pdfplumber word boxes against per-page column anchors and is canonical, parser B is camelot `stream`; they must agree on the record count and on every identifier and number, in order | `pipeline_nyc/parse/bluebook.py` | | The five committed parses and their gate reports | `data/cities/nyc/reference/parsed/` | | Where each PDF came from: capture URL, DNN file id, bytes, `sha256` (the PDFs are gitignored) | `data/cities/nyc/sources/sca_bluebook_//provenance.json` | | Gate-checked reader for those years, shared by the loader and the packet so a year in the warehouse cannot be missing from the page | `scripts/loaders/_bluebook_recovered.ts` | | Philadelphia transform (dedupe, site links, school roll-up, checks (iv)-(vi)) and loader | `scripts/loaders/philly/_facilities.ts`, `scripts/loaders/philly/facilities.ts` | | Philadelphia metric keys | `scripts/loaders/philly/metric_definitions_facilities.ts` (the capacity keys; the other Philadelphia keys are in `philly/metric_definitions.ts`) | | Qlik extract → committed CSVs | `pipeline_philly/fetch_facilities.py` | | Analysis packets → `data/analysis/{nyc,philly}-enrollment-capacity.json` | `scripts/analysis/nyc-capacity-packet.ts`, `scripts/analysis/philly-capacity-packet.ts` | | Packet types and charts | `components/analysis/{nyc,philly}-capacity-packet.ts`, `components/analysis/{nyc,philly}-capacity-charts.tsx` | | Analysis pages | `app/methodology/analysis/enrollment-capacity/page.tsx`, `app/philly/methodology/analysis/enrollment-capacity/page.tsx` | | Philadelphia school page: the Facilities section, its site query, and the pure function that picks which case a school shows (its own figure, 223 of 331 schools; a shared site's figures, 39; no published capacity, 10; no site in the plan, 59; site figures with no stored school figure, 0) | `app/philly/schools/[ulcs]/page.tsx`; `getPhillySchoolSites` and `phillyFacilitiesMissing` in `lib/queries/philly-schools.ts`; `lib/philly/facilities-display.ts` | | NYC school page (capacity rows and their basis note in the Access section) | `app/schools/[dbn]/page.tsx` | | Proof trees for the Philadelphia capacity keys (roll-up formula, assumptions, the checks that guard it) and the Facilities source entry | `lib/philly/lineage.ts` | | Keeping the capacity keys out of the percentiles | the `subdomain` filter in `scripts/loaders/compute-percentiles.ts` and `scripts/loaders/philly/compute_percentiles.ts` | | Probes that reproduce the published figures | `docs/design/probe/bluebook_util.py`, `bluebook_verify.py`, `docs/cities/philly/probe/facilities.py` | | Tests: transforms on verbatim source rows, and the school-page case choice | `tests/nyc-bluebook-transform.test.ts`, `tests/philly-facilities-transform.test.ts`, `tests/philly-school-facilities.test.ts` | | The two-parser precedent the NYC gate was modelled on — it runs **two pdfplumber strategies**, A reading page text with a regex and B reading word boxes by x-position. It does not use camelot; camelot is imported in no file in that pipeline | `pipeline_miami/parse/fish_los.py` | | Every command, the cold-database load order, and how to re-parse a year | `make capacity-help` | | House writing rules for anything reader-facing | `docs/writing-style.md` | --- ## 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: ```bash 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: ```bash python -m pipeline_nyc.parse.bluebook [...] \ --snapshot=YYYY-MM-DD --evidence=.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.