# Capacity & utilization — what is missing Companion to the three city documents. They say what the data holds; this says what it does **not**, why each gap is open, and what closing it would take. - NYC: [`design/10_nyc_enrollment_capacity_utilization.md`](design/10_nyc_enrollment_capacity_utilization.md) - Philadelphia: [`cities/philly/06_enrollment_capacity_utilization.md`](cities/philly/06_enrollment_capacity_utilization.md) - Miami-Dade (the model): [`cities/miami/06_enrollment_capacity_closures.md`](cities/miami/06_enrollment_capacity_closures.md) Gaps are graded by what it would take to close them: | grade | meaning | |---|---| | **A** | Blocked only by the build environment. Works on a machine with a database and an unproxied network. | | **B** | The data exists but needs real work — parsing, hand-curation, a request to the publisher. | | **C** | The data does not exist publicly. Closing it means a records request, an interview, or accepting the limit. | No open gap is grade A: the schema and both loaders run against Postgres (§0), and every Blue Book year the SCA published is in the NYC panel (§1). ### Picking this up cold Read [`capacity-handoff.md`](capacity-handoff.md) first. It is the orientation brief: what exists, where it lives, how to run and verify it, and the things in it that look like bugs and are not. **This file is the register of what is still open.** | | gap | grade | |---|---|---| | **§1** | NYC: no Blue Book for 2020-21 — the SCA never published one | C | | **§2** | NYC: no closure or decision record | C | | **§3** | NYC: no second publisher for capacity | C | | **§4** | Philadelphia: one vintage, no history | B | | **§5** | Philadelphia: a second, 2017-labelled capacity figure, and no documented method for either | B | | **§6** | Philadelphia: access is an undocumented API | B | | **§7** | `scripts/sql/enable-rls.sql` does not run to completion against this schema | B | | **§8** | Smaller open items, in the data and in the code | B / C | | **§10** | In the sources, not in the database: what the loaders leave out, and how to add it | — | §9 says which one would change the picture most, and it is a C. §10 is not a gap in the data: it lists what the sources carry that the database does not store. ## 0. What already works, so you know where the floor is Everything in the first block runs with no database and no credentials. Install first — the probes need `scipy` and the Qlik fetcher needs `websocket-client`, both of which are in the pinned file: ```bash npm install pip install -r pipeline_philly/requirements.txt ``` ```bash python -m pipeline_philly.fetch_facilities # Qlik → two committed CSVs + provenance npx tsx scripts/loaders/nyc-bluebook.ts --dry-run # live Socrata + 5 committed parses, 12 years, check (iv) npx tsx scripts/loaders/philly/facilities.ts --dry-run # committed CSVs, checks (iv), (v), (vi) npx vitest run tests/nyc-bluebook-transform.test.ts \ tests/philly-facilities-transform.test.ts # 90 tests, verbatim source rows python docs/design/probe/bluebook_util.py # every NYC figure in the doc python docs/design/probe/bluebook_verify.py # the NYC cross-source verification python docs/cities/philly/probe/facilities.py # the Philadelphia figures, live (§4.1-4.3 from the packet) npx tsx scripts/analysis/nyc-capacity-packet.ts --check # committed packet vs live sources npx tsx scripts/analysis/philly-capacity-packet.ts --check # committed packet vs a fresh run ``` The checks the comments name, (iv) to (vi), are defined under [Checks that stop a load](#checks-that-stop-a-load) below. Most of these have a `make` target too; `make capacity-help` lists them and prints the cold-database load order. Three read only committed files: the Philadelphia loader's dry run, the Philadelphia packet (the two CSVs plus the committed NYC and Miami-Dade packets, which it reads for its `cross_city` block) and the tests. Everything else fetches live (Socrata for NYC, the Qlik engine for Philadelphia, the federal CCD and an InfoHub xlsx for the verification), so a network failure is a network failure, not a data defect. The Philadelphia dry run prints, on the committed extract: ``` check (iv) utilization == (enrollment + PreK) / capacity: 245/245 within 0.005 (max |diff| 0.0000) check (v) enroll w/ allotment == enrollment + PreK allotment: 285/285 sites check (vi) school roll-up over every extract link (before the directory filter): 157 schools, 183 on a shared site, 0 conflicting; a school with one capacity site reproduces that site's published utilization 156/156 ``` **The five Blue Book years Socrata does not carry are parsed and committed** under `data/cities/nyc/reference/parsed/`, one `bluebook_.csv` and one `bluebook_.gate.json` each. The loader, the probe and the packet all read them and all three refuse a year whose gate did not pass, so nothing above needs the parser installed. To re-parse a year you need `pdfplumber` and `camelot` (and the PDF; §1 says how the files are obtained): ```bash pip install -r pipeline_nyc/requirements.txt python -m pipeline_nyc.parse.bluebook [...] \ --snapshot= --evidence= ``` ### Loading The schema step adds two Philadelphia tables, `philly_sites` and `philly_site_programs`. NYC's figures are rows in the existing `school_year_metrics`, so its load needs no schema step, only its seeded definitions. The Philadelphia loader no-ops with a clear message if its two tables are not in the database yet, so it is safe to run before the schema step. The schema step only adds tables the site renders without, and the RLS script is re-runnable, so both can run before or after this code is served; the seeders and loaders run once it is, because code that does not keep the `Facilities` keys out of ranking would rank the capacity rows. Export `DIRECT_URL` to the target database first. 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. dotenv does not override a value already exported, so an exported `DIRECT_URL` wins over one in the files, but an exported `DATABASE_URL` does not win over a `DIRECT_URL` in them. `-v ON_ERROR_STOP=1` makes psql exit non-zero when a transaction rolls back; a plain `psql -f` prints the error and exits 0. ```bash psql -v ON_ERROR_STOP=1 "$DIRECT_URL" -f scripts/sql/capacity-schema.sql # adds philly_sites, philly_site_programs psql -v ON_ERROR_STOP=1 "$DIRECT_URL" -f scripts/sql/enable-rls.sql # RLS + grants — rolls back if any table it names is missing, see §7 npx tsx scripts/loaders/metric-definitions-capacity.ts # BEFORE the loaders (FK on metric_key) npx tsx scripts/loaders/philly/metric_definitions_facilities.ts npx tsx scripts/loaders/nyc-bluebook.ts # 7 years from Socrata + the 5 committed parses npx tsx scripts/loaders/philly/facilities.ts ``` Metric descriptions live in the database, so a change to either definitions file needs its seeder rerun before the pages show it. ### What a full load writes ```sql SELECT year, count(*) FROM school_year_metrics WHERE metric_key IN ('target_capacity', 'utilization_pct', 'utilization_pct_published', 'prek_capacity', 'cluster_rooms_reported', 'cluster_rooms_needed') GROUP BY year ORDER BY year; -- 12 rows: 2013-14, 2014-15, 2015-16, 2016-17, 2017-18, 2018-19, 2019-20, -- 2021-22, 2022-23, 2023-24, 2024-25, 2025-26. 2020-21 is absent. A thirteenth -- year means SNAPSHOT_YEAR gained an entry that was not date-stamped by -- measurement (§1). Against production's 2,131-DBN `schools` table the -- per-year counts run 9,295–10,192 (9,721 in 2025-26); they follow the -- `schools` table the loader joins against (NYC doc §10). SELECT count(*) FROM philly_sites; -- 328 SELECT count(*) FROM philly_sites WHERE capacity IS NOT NULL; -- 254 SELECT count(*) FROM philly_site_programs; -- 305 = 385 − 80 SELECT count(DISTINCT school_ulcs) FROM philly_school_year_metrics WHERE metric_key = 'utilization_pct'; -- 223 ``` The NYC load prints `wrote school facts` for each year. The Philadelphia load prints `check (vi) school roll-up over directory schools: 223 schools, 39 on a shared site, 0 conflicting; … 220/220` and `wrote 328 sites, 305 site↔school links, 892 school facts`. - **`philly_site_programs` has no fixed expected count.** The extract carries 385 site↔school pairs, but the loader writes only those whose ULCS is already in `philly_schools` and prints how many it skipped. On the current extract all 80 skipped links are Pre-K programmes, which the Master School List does not carry. The invariant is `385 − skipped`, not 385. - **The NYC loader reports, per year, within these ranges:** | quantity | range across the twelve years | |---|---| | organizations | 1,776 – 1,844 | | split across buildings | 14.0% – 19.2% | | buildings | 1,383 – 1,466 | | holding >1 organization | **35.7% – 41.3%** | | check (iv) | 100%, max 0pp, every year | The co-location range is over every building in the extract. The design doc and the analysis page quote **38.5–42.1%** for the same phenomenon because the packet restricts to buildings carrying both an enrollment and a capacity, 1,311–1,334 of them rather than 1,383–1,466. Both are right; they are different denominators. - **`--dry-run`, `tsc` and the tests stop short of sending the loaders' writes**, so a defect in generated SQL shows only on a real load. The counts above are what a real load is checked against. ### Checks that stop a load Each loader refuses to write rather than write something its transform cannot vouch for. If one of these fails, the source changed shape or a parse is wrong; the check is not the thing to change. Check (iv) is the computed-value check of the (i)–(iv) validation taxonomy in [`design/05_validation_spec.md`](design/05_validation_spec.md); (v) and (vi) are Philadelphia additions. - **NYC.** Check (iv): utilization recomputed from enrollment and capacity against the publisher's own column, single-building organizations within 1pp (100% at max 0pp on all twelve years). An unrecognised snapshot vintage (`UnknownSnapshotError`, §1). A parsed year whose gate report is missing or failed, or whose rows carry no snapshot date (`_bluebook_recovered.ts`). - **Philadelphia.** Check (iv): published site utilization equals (enrollment + Pre-K allotment) ÷ capacity within 0.005. Check (v): on every site, enrollment with allotment equals enrollment plus the allotment. Check (vi): a school whose capacity sits on one of its sites reproduces that site's published utilization within 0.005. A school whose own sites carry different enrollments (the roll-up counts enrollment once because the dashboard repeats it on every site). The school-grain checks run before anything is written, so a refusal leaves the tables as they were. The loader only hashes the building CSV, for the extract pin; the Philadelphia packet, which parses it, stops on a repeated building whose rows disagree. --- ## 1. NYC — no Blue Book for 2020-21 · grade C **What's missing.** The panel covers **twelve school years, 2013-14 through 2025-26, with one gap: 2020-21.** Seven years come from NYC Open Data (`gkd7-3vk7`, 20,754 rows). Five — **2014-15, 2019-20, 2022-23, 2023-24, 2024-25**, 15,222 rows — are parsed from the SCA's own annual reports by [`pipeline_nyc/parse/bluebook.py`](../pipeline_nyc/parse/bluebook.py) and committed under `data/cities/nyc/reference/parsed/`. **Why it is a C: the SCA never published 2020-21.** Three independent lines of evidence: - No file of that name has ever been archived on the SCA's blob store or on its own domain. - The archived filename inventory runs 2019-2020 and then 2021-2022, with nothing between. - The SCA's own file-listing API, archived in September 2022, enumerates seven year-categories from 2013-14 to 2019-20 and has no 2020-21 slot at all. The live listing enumerates twelve, 2013-14 to 2025-26, and also has none. So the panel has one two-year segment (2019-20 → 2021-22) rather than a missing observation, and a time-series figure breaks the line there rather than interpolating. School-grain median utilization is 91–95 from 2013-14 through 2019-20, 83 in 2021-22, 82 / 82 / 80 in 2022-23 to 2024-25 and 78 in 2025-26 (NYC doc §4.1), so the largest step falls inside that segment. The enrollment-versus-capacity decomposition in NYC doc §4.3 runs along the same series, with the same one segment. **What closing it would take.** A records request to the SCA asking whether the audited-register file behind a 2020-21 report exists internally. There is nothing to parse. ### How the Blue Book files are obtained The "Enrollment, Capacity & Utilization" tab on `nycsca.org/Community/Capital-Plan-Reports-Data` is a client-rendered DNN file-listing module, but no browser is needed. - **The category id is numeric.** `catName` is `29`, rendered into a hidden input on the page itself, `dnn_ctr2108_View_hidNycscaCategory_FileList`. The endpoint takes the id; a string category name returns `"No Files Found"`. With the id, the listing is a plain JSON GET: ```bash curl -H 'ModuleId: 2108' -H 'TabId: 174' \ 'https://www.nycsca.org/DesktopModules/FileList/API/FileList/getlistings?catName=29&viewType=1&resTopicFilter=<rFilter=&editMode=false' ``` - **Each file's download URL** comes from a second call, `/DesktopModules/FileList/API/FileList/getfilebyid?ItemId=`, which returns an Azure blob URL carrying a SAS token. - **Most listed records are dangling.** On the live site `getfilebyid` returns `Path: null` for most years: the listing enumerates the record, the blob behind it is gone. Of the twelve year-categories, only 2013-14, 2014-15, 2024-25 and 2025-26 resolve to a live file, and not every file inside them does: 2025-26 resolves 6 of 6, 2014-15 5 of 6, 2024-25 4 of 6, 2013-14 2 of 7, and the other eight years 0. The listing's own `FileExtension` field predicts this exactly — every entry that resolves carries `"pdf"`, every dangling one `null` — so it can be read before spending a request. - **Orphan records.** `getfilebyid` answers for any live file record, not only for records a listing enumerates, so a record no listing references still yields a download URL. The **2014-15 Classic Edition**, whose listed record (id 4828) is dangling, is served this way at id 630 under `General/ToDelete/`. It is the edition loaded: its first column is the geographic district, where the City Council edition's is the council district. The five City Council borough files are kept as corroboration; parsed independently they give the same 2,717 rows and agree on every figure but one cell (NYC doc §2.1). - **The rest of the id space holds no other year.** Enumerating the modern file id space resolves 13 objects under `Capital_Plan/Utilization_Reports/`, and none of them is 2019-20, 2022-23, 2023-24 or 2020-21. The blob container gives nothing away: anonymous listing is refused, the SAS is bound per blob (a valid signature for one file returns 403 on any other), and a missing blob and an invented name both return the same 404, so there is no existence oracle. - **Rate limit.** `www.nycsca.org` sits behind a load balancer that trips a blanket 403 across the whole host within a few hundred sustained requests and clears about a minute after they stop. A 403 means back off and retry, not "no file"; a scan that reads it as "no file" under-reports. - **2019-20, 2022-23 and 2023-24 come from the Internet Archive.** Request the SAS-bearing blob URL through `web.archive.org/web/id_/`; the tokens are expired against live Azure, and the archive replays its own stored copy regardless. Each file was checked byte-identical against a second, independent capture, so a single corrupt snapshot cannot pass as the source. The capture URL, the DNN file id, the byte count and the `sha256` of every file are in `data/cities/nyc/sources/sca_bluebook_//provenance.json`. The PDFs themselves are gitignored. ### How a parsed year enters the panel The same procedure applies to any year that is only available as a PDF. 1. **Parse under the two-parser gate.** Following the Miami precedent (`pipeline_miami/parse/fish_los.py`), parser A places pdfplumber word boxes against each page's own header anchors (canonical), and parser B reads the same pages through camelot `stream`, which infers columns from whitespace and shares no extraction code with A. The gate requires agreement on the record count and on every identifier and every number, in order. `organization_name` is gated; `bldg_name` is compared and reported but not gated, because camelot truncates a name that wraps, which is a property of parser B rather than evidence about the data. A gate failure is fixed in the parser, never in the gate. All five years pass with zero gated-cell mismatches; per-year rows, organizations and buildings are in NYC doc §2.1. 2. **Date-stamp by measurement.** `bluebook_util.py` section B joins the parse to every year of the DOE Demographic Snapshot and takes the year minimising the median absolute per-school enrollment difference; a file's label and posting date are not used. All five score median 0 with runner-up margins of 18–23 students, against 20.5–24 for the seven Socrata vintages. NYC doc §2 gives the per-year medians and match rates; the per-year margins are `date_stamp.runner_up_margin_students` in each parsed year's gate report, and can be read for all twelve years off the snapshot-by-year matrix that section B prints. A minimum above 0, or a margin of a few students, would indicate a bad parse rather than an unusual year. 3. **Register the vintage.** `SNAPSHOT_YEAR` in `scripts/loaders/_bluebook.ts` has twelve entries. The five PDF keys are the audited-register dates the reports state (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 an identifier for the vintage; the school year beside it is the measured one. `nyc-bluebook.ts` throws `UnknownSnapshotError` on a vintage that is not in the map rather than inferring a year from the posting date: the observed lag runs from three to eighteen months, so inference would silently overwrite a real year. 4. **Load.** [`scripts/loaders/_bluebook_recovered.ts`](../scripts/loaders/_bluebook_recovered.ts) reads the committed parses and pushes them onto the Socrata rows before `groupBySnapshot`, in the Socrata field shape, so the roll-up, the join and check (iv) treat them the same way. It throws rather than load a year whose gate report is missing, whose gate did not pass, or whose rows carry no snapshot date (an empty `data_as_of` would otherwise be dropped silently). Nothing in the transform asks where a row came from; provenance lives in the gate report beside each CSV, in `meta.recovered_years` in the packet and in `provenance.json`. Check (iv) is 100%, max 0pp, on all twelve years. **The control.** 2025-26 is published on Socrata and available as a PDF, so the parser has a year whose answer is known: 3,132 rows against 3,132, no key only on either side, no duplicate keys, and **43,700 of 43,848 cells identical (0.338% divergence)**. Through the repo's own transform the parsed rows give 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 148 divergent cells, and one roll-up difference, are what a parsed year differs in: - **124 cells** (62 rows × 2 columns): the report prints a blank where the published extract carries a `0`, in the PK-capacity and cluster-rooms-needed columns, on split-site organizations. The parse reads them as **absent** rather than inventing a zero. The blank-vs-zero rule the parser 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 (§8). - **18 cells** (6 rows × 3 columns): the published extract blanks the building columns on a row where the parse repeats them. `rollUpBuildings` takes the first **complete** row, so both give the same building record. - **6 cells** of whitespace: five building-name cells covering three distinct names (one of them repeated on three rows) and one organization name. - After roll-up, two organizations (`QSST`, `Q177`) take a different `geo_dist`. All 3,132 rows agree on the field; these two sit in buildings across several districts and the two sources order their rows differently, so "the first row's district" differs. The join keys on the org code alone and never on `geo_dist` (NYC doc §3.1), so nothing downstream reads it. --- ## 2. NYC — no closure or decision list · grade C **What's missing.** There is no public, machine-readable record of NYC school closures, mergers, truncations or co-locations. This is the largest gap in the NYC track, and it is what stops any claim of the form *"the schools the city actually closed were the ones at X% utilization."* **What was checked.** The NYC Open Data catalog returns nothing for school closings, mergers, or Panel for Educational Policy actions. The only adjacent dataset, `q7ra-ebu4` "Underutilized Space Report", is a one-off 2015 list built on the 2013-14 Blue Book. **Also ruled out, with evidence.** The DOE Demographic Snapshot cannot be used as a proxy: each published file restates *every* year to one fixed school universe, so a school that closes is removed from the earlier years too. Within-file exits are 0 in nine of twelve year pairs across three publication vintages and never exceed 2 (measured in `probe/bluebook_util.py` section I). Exits are visible only *across* vintages, which is why the closure-prediction test in §4.6 of the NYC doc uses a 12-year cross-vintage difference and is labelled a proxy. **What closing it would take.** The source is the PEP's own record — significant-change proposals, Educational Impact Statements, and the monthly vote calendar, all PDF. Miami's `MiamiSchoolEvent` is the model: a hand-curated CSV where every row cites its document, loaded by `scripts/loaders/miami/school_events.ts` and replaced wholesale on each run. Budget it as weeks, not days; it is the prerequisite for any predictive framing. **Until then**, the NYC page stays descriptive: *this is how full each building is, and how that has changed* — not *these schools are at risk*. --- ## 3. NYC — no second publisher for capacity · grade C Enrollment is verified against an independent publisher: NCES CCD agrees with the Blue Book at r = 0.9998, and the control shows CCD differs from the DOE's *own* file by the same 3–4 students, so the residual is CCD-side. **Capacity has no such check.** CCD, NYSED and the city's other datasets carry enrollment, not seats. The strongest available test is within-publisher: target capacity is 92% explained by the DOE's physical room inventory (`wavz-fkw8`) at a stable ~32 sq ft per seat across three vintages eight years apart (33–34 with Pre-K rooms counted, NYC doc §5.2). That is a consistency check, not independent confirmation, and the doc says so. The parsed years do not change this. Their control (§1) tests the *parse* against the publisher's own extract for a year both exist for; it says nothing about whether the publisher's capacity figure is right. No recipe here: this is a limit of the public record, and the NYC doc (§5.5) and the NYC analysis page state it. --- ## 4. Philadelphia — one vintage, no history · grade B **What's missing.** Capacity exists for **SY2024-25 only**. No trend, no decomposition, no before/after. (The app also keeps an older per-building capacity it labels 2017, beside a SY2016-17 enrollment count; whether that pair is a usable second vintage is unmeasured — §5, and Philadelphia doc §6.5.) **A hazard in the dashboard.** It exposes a `School Year` field with two values (2016-2017, 2024-2025) that looks like a two-point panel. It is not: `School Year` lives on the student/catchment table and is cross-joined to the facilities table, so every site appears under both labels with identical numbers: of the 335 (ULCS, site) pairs carrying a capacity under both labels, 0 differ on any figure. Reading the dashboard without that check would show a flat trend that does not exist. `pipeline_philly/fetch_facilities.py` does not pull that field. **Candidates checked and ruled out.** The SDP Facility Condition Assessment archive (Google Drive, 2015/2016/2017/2020/2021/2022, ~62 reports per year) is **condition only**. A full-text scan of a 63-page 2022 report found **zero** mentions of capacity, utilization or enrollment. FCAs are Parsons condition assessments; capacity is not their purpose. **Candidates not yet checked.** - The app's own "OLD" pair: `BLDGCAPACITY` (labelled 2017) and a SY2016-17 enrollment count, which the app divides into its own "Site Utilization (OLD)" (§5). Check the enrollment count against CCD 2016-17 before relying on it. - The 2017 Comprehensive School Planning Review. - SDP's Longitudinal School List (`.../School_List/Longitudinal%20School%20List%20(20171128).xlsx`, 2001-02 → 2016-17) — directory, but worth a look for a capacity column. - PICA and City Controller reports on SDP facilities. - A direct request to SDP's Office of Operations. They computed a utilization score for every site; they likely have prior years. The NYC panel in §1 is a weak precedent for this one: it works because the SCA posts the same report annually in a stable layout. SDP publishes no equivalent annual facilities report, so there is no series of PDFs to parse. --- ## 5. Philadelphia — a second, 2017-labelled capacity figure, and no documented method for either · grade B **What's missing.** Documentation. The app carries `Site Capacity` — the figure the district publishes and plans on, and the one every served Philadelphia figure uses — and a second figure, `BLDGCAPACITY`, per building. Nothing in the app says how either was measured. Its labels are the only evidence about the second: the measure library names `sum(BLDGCAPACITY)` "Current Capacity (OLD)", divides a SY2016-17 enrollment count by it for "Site Utilization (OLD)", labels its OLD figures 2017, and keeps the field in a `BuildingsOld` table beside the older condition fields. That is consistent with an earlier capacity figure for the same buildings. No served figure uses it; Philadelphia doc §6.5 describes it. **How far apart the two are.** The extract repeats a building once per programme on its site, so summing its rows would count a building once per programme. Summed over each site's **distinct** buildings, the building figure equals the app's own `Sum([BLDGCAPACITY])` on all 236 sites carrying one. On the 207 district sites carrying both figures it runs a median 0.989× the site figure, p10 0.754, p90 1.301. Recomputing 2024-25 utilization on each, on the same 207: | denominator | median utilization | share under 50% | |---|---|---| | `Site Capacity` | 67.1% | 25.1% | | `BLDGCAPACITY`, distinct buildings summed | 61.8% | **31.4%** | (On the full 215 the published basis reads 24.7%, so the restriction does not create the gap.) **The choice of denominator moves the under-50% share from a quarter of sites to about a third — 6.3 points.** In aggregate the two nearly agree (building seats 2.7% below site seats on the 207 sites); site by site they do not. The comparison crosses vintages: it divides 2024-25 enrollment by a capacity the app labels 2017, a pairing the app itself never makes, so part of the site-by-site difference may be capacity that changed after 2017 rather than definition. NYC's two-formula ambiguity (target vs historical capacity, NYC doc §4.4) is measured within one year, 2013-14, and moves its under-50% share by 2.4. Floor area has the same repetition one step further: `gross_square_footage` sits in the app's per-programme table, so even the app's own `Sum` of it counts a two-programme building twice. Philadelphia doc §6.3 counts each building once. Three measured limits on the building figure: all 249 buildings carrying one are Active, so a status filter changes nothing; every seat on the 207 sites is in a classroom-use building; and 19 `BLDGCAPACITY` records (11,536 seats) are linked to no current building, so no site figure counts them. **What closing it would take.** Ask SDP whether `BLDGCAPACITY` is the 2017 capacity of the same buildings, and how the 2024-25 `Site Capacity` was measured. Until it is documented, `BLDGCAPACITY` is compared with site capacity in Philadelphia doc §6.5 and in the packet's `denominator_sensitivity`, which reads it from the committed building CSV. It is not stored in the database (§10) and is never served as "the" capacity. **A lead for §4, not measured.** The app pairs that 2017-labelled capacity with a SY2016-17 enrollment count in its own "Site Utilization (OLD)" measure. Whether that pair is a usable second vintage — whether the enrollment count includes Pre-K, and assigns students to sites the way `Site Enrollment` does — is unmeasured. Check it against CCD 2016-17 before relying on it. --- ## 6. Philadelphia — access is an undocumented API · grade B There is no CSV, no Socrata dataset, and no facilities category in SDP's open-data catalog. The only path is the Qlik Sense Engine JSON API behind the dashboard, using an app id (`2d47abaf-…`) read out of a JavaScript bundle. Two operational notes, both handled in `fetch_facilities.py`: - The load balancer returns **403 without a browser `User-Agent`**, before the engine sees the request. It looks like an auth failure and is not one; anonymous access is open (`mustAuthenticate: false`). - `GetHyperCubeData` caps a page at ~10,000 cells; `PAGE_ROWS = 250`. The fetcher refuses to write a short table (<400 site rows, <250 site rows with a capacity, <500 building rows) rather than let a silent truncation read as a real shrinkage. The thresholds are on rows, not on sites or buildings: 428 site rows are 328 sites and 502 building rows are 399 buildings, because both cubes repeat once per programme. The CSVs are committed because this path could vanish. The SCA's dangling file records in §1 — a live listing whose blobs have been deleted — are the same failure mode one publisher over. --- ## 7. `scripts/sql/enable-rls.sql` does not run to completion against this schema · grade B The script is one transaction that names every city's tables, and the first table the database lacks aborts it: the whole transaction rolls back, including the block for the two facilities tables, which sits after the other cities' blocks. Two sets of tables are outside the facilities work and can be missing: - The five `california_*` tables (`california_entities`, `california_comparison_groups`, `california_metric_definitions`, `california_entity_year_metrics`, `california_data_loads`), which `prisma/schema.prisma` does not define. A database built from this schema alone fails with `relation "california_entities" does not exist`. - The six `miami_*` tables (`miami_schools` and five more), which the Miami-Dade runbook's schema step creates ([`cities/miami/07_runbook.md`](cities/miami/07_runbook.md) §1). Production lacks them until that step has run there, so on production the script fails at `ALTER TABLE miami_schools`. With `-v ON_ERROR_STOP=1` (§0), psql exits non-zero on the rollback; a plain `psql -f` prints `ERROR` and `ROLLBACK` and exits 0. The facilities tables' own RLS block and the grant loop run cleanly on their own, and that is the fallback: run the `Facilities tables` block, then the grant loop and the two `ALTER DEFAULT PRIVILEGES` statements after it, without the other cities' blocks. Applied that way, `relrowsecurity` is true on `philly_sites` and `philly_site_programs`, and `anon` and `authenticated` hold `SELECT` and nothing else on them. NYC's capacity rows are in `school_year_metrics`, which the NYC block of the same script covers. **What closing it would take.** A decision by whoever owns that script: guard each city's block with `to_regclass`, or split the facilities block into a file of its own; for the `california_*` lines, dropping them or adding the tables to the schema also works. --- ## 8. Smaller open items ### In the data | item | grade | note | |---|---|---| | NYC charters in private space | C | Absent from the Blue Book entirely. Any citywide NYC utilization figure is a *DOE-buildings* figure and is labelled one. | | NYC 2020-21 | C | The SCA never published a Blue Book for it (§1). The panel carries a two-year segment across it. | | NYC ambiguous org codes | B | Four of 2,232 (`K594`, `K646`, `M505`, `M610`) map to two DBNs each in the directory-plus-Snapshot union, and the packet **drops** them rather than guessing. The loader skips a code only when it is ambiguous in the `schools` table it joins against; production's holds one DBN for each of the four, so the loader writes them to `13K594`, `17K646`, `03M505` and `04M610`, whose names match the Blue Book's (NYC doc §3.1). Resolving them in the union needs the building address; the Blue Book gives geographic district, which is the field that is wrong for exactly these cases. | | NYC middle/high room bands | B | Most MS/HS rooms carry the unbanded label "REGULAR CLASSROOM", so the per-band sq-ft-per-seat split resolves only the elementary case (n=165 vs n=11 and n=2). The banded figure is elementary-only evidence. | | NYC blank-vs-zero in the parsed years | B | On split-site organizations the report prints nothing where the published extract carries a 0 in the PK-capacity and cluster-rooms-needed columns, so those come out absent rather than 0 (§1). It affects 62 of 3,132 rows in the control year. No utilization figure moves — neither column enters the calculation — but `prek_capacity` and `cluster_rooms_needed` are null rather than 0 on those organizations in a parsed year, and a per-year total of Pre-K seats is a lower bound on the five PDF years. | | Capacity keys in the outlier and mover dropdowns | B | The NYC and Philadelphia capacity keys carry no percentile (both percentile batches skip `subdomain = 'Facilities'`), so the outlier pages show none for them. The outlier and mover dropdowns still list them, though, and the NYC movers page sorts them by year-over-year change like any other metric — for example a school whose utilization moved from 154% to 404% between 2024-25 and 2025-26. Philadelphia has one year, so it has no movers. Filtering the capacity keys out of those lists is a change to the ratings pages. | | Philadelphia school level is year-dependent | B | `School Level` varies across the app's `School Year` values for some schools — between the two labels (Joseph Brown, 8210: Elementary under 2016-17, Elementary-Middle under 2024-25; Beeber, S023: High, then Middle-High) or on rows with no year label (Carver, S037, reads Middle-High under both labels and High only on unlabelled rows) — and the fetcher keeps whichever variant the engine emits first. On the 215 district sites, 2 carry a level that is not their 2024-25 one: S023 and S037 read High where 2024-25 says Middle-High. The by-level table in Philadelphia doc §4.1 (the packet's `by_school_level`) counts them as High, and so does the packet's `ratio_by_school_level` for the 2017-labelled figure (§5), whose High ratio would read 0.948 on 43 sites rather than 0.955 on 45. A fix would pin the level to the 2024-25 label in the fetcher and re-derive both. | | Philadelphia `BLDGFCI` vintage | B | `BLDGFCI` sits in the app's `BuildingsOld` table with `BLDGCAPACITY` and `FCI Status (OLD)`, so it is probably the older condition figure, not the current one. No published figure here uses it; anything that does has to name its vintage. | | Philadelphia charter coverage | C | 22 sites. Charters in their own buildings are largely absent, the same limitation NYC has. | | Philadelphia shared sites | B | 37% of district sites host more than one programme, so the site's capacity is not attributable to one school. The loader writes site-grain facts for all of them but **school-grain facts only where attribution is unambiguous**, and reports the count it set aside: 39 schools share a site with another school in the directory. A site shared only with the school's own Pre-K programme, which the Master School List does not carry, is attributed to the host school, and its figure includes the Pre-K seats allotted there. Of the 331 directory schools, 223 carry a school figure, 39 are on a shared site, 10 have sites with no or zero published capacity, and 59 have no site in the plan. | | Philadelphia split campuses | B | Three district schools (2210, 7440, 8770) have classroom capacity on two sites each. SDP's per-site utilization divides the whole school by one site's capacity on each, and the site-grain distribution keeps that published basis, counting 1,871 students twice. Merged into one unit per school it reads n 212, median 66.6%, 25.0% under 50% and 11.8% over 100%, against 215, 67.1%, 24.7% and 13.5% as published (Philadelphia doc §4.1; the packet's `distribution.split_campus_sensitivity`). Kept as published; how students divide between the two buildings is not published. | | Philadelphia possibly short denominators | B | Olney Elementary (7400), The Workshop School (8560) and Walter Saul (6040) each have a linked site with no or zero published capacity that holds classroom buildings (Philadelphia doc §3.2). If those buildings hold seats, the school figures (99.4%, 75.4%, 50.7%) overstate utilization. Flagged in the doc; not adjusted, since the dashboard publishes no capacity for those sites. | | Cross-city comparability | B | Three denominators (SCA target capacity, SDP site capacity, permanent FISH capacity), three universes, and three Pre-K bases: every numerator includes Pre-K, but Philadelphia adds allotted seats while NYC (3-K and Pre-K) and Miami-Dade (PK) count enrolled children, and NYC's target capacity also gives Pre-K rooms seats. Philadelphia's site capacity is assigned to Pre-K space as well (its five Pre-K-only sites carry 60–221 seats); whether Miami-Dade's FISH capacity counts Pre-K stations is not established. No Pre-K-consistent figure is computed. Pre-K seats are 2.2% of Philadelphia's district-site numerator; without them its 215 sites read median 63.4%, 59 (27.4%) under 50% and 66.9% overall, against 67.1%, 24.7% and 68.4% (measured from the committed site extract; no packet field carries it). The three-way table in Philadelphia doc §4.2 (SY2024-25 for all three; the same figures are in the Philadelphia packet's `cross_city` block) names each row's numerator, denominator and universe; the figures do not travel without them. | ### In the code and the documents | item | note | |---|---| | NYC school profile view | The `codex/nyc-school-profile` branch adds a profile view (behind `SCHOOL_PROFILE_ENABLED` or `?profile=1`) that replaces the NYC school page and carries no capacity or utilization content. With it enabled, NYC school pages show no utilization, while Philadelphia's and Miami-Dade's do. | | Dropped spaces in built pages | The production build (Next 16.2.6) can drop the space between an inline element or expression and the text after it. The two capacity analysis pages write those spaces as `{" "}`; other pages still render the joined form, e.g. `/methodology/analysis/absenteeism` shows "0mismatches" and "80–141vs" (`app/methodology/analysis/absenteeism/page.tsx:996`, written `{p.meta.n_crosscheck_mismatches} mismatches`). Scanning the built HTML for `[A-Za-z]` finds new cases after an element; after an expression the joined form is `` followed by a letter, which also matches intended joins such as `5pp`. | | Miami-Dade companion doc | Its §2 utilization bullet carries the SY2024-25 packet figures (`data/analysis/miami-enrollment.json` → `utilization`); the rest of `docs/cities/miami/06_enrollment_capacity_closures.md` has not been checked against the Miami-Dade packet. | | Philadelphia roll-up comparisons are one-off | The `Site Utilization (MAP)` comparison (all 23 multi-site District schools) and the NCES 2024 correlation of the loaded school figures (r 0.9993, 222 schools) in Philadelphia doc §3.2 are printed by no committed script. The probe's own roll-up (its section C, used by sections E and H2) takes the largest enrollment across all of a school's sites, shared ones included, which is not the loader's rule. Closing it: a probe section or a packet field that runs `schoolFacts` and prints both comparisons. | --- ## 9. What would change the picture most If only one gap gets closed, close **§2 — the NYC decision record**. NYC has the best data of the three cities and the least ability to say anything about what the city does with it. Philadelphia, with a thinner dataset (one vintage, §4), supports a sharper analysis because the district published its own per-site recommendations alongside the utilization figures, in the same publication. The NYC panel runs twelve school years and places the largest step in its utilization series inside a single two-year segment, so it is the absence of a closure record, not the length of the utilization series, that limits any statement about what the city did in response. --- ## 10. In the sources, not in the database The database holds what the site reads: for NYC, six school-grain keys in `school_year_metrics` (NYC doc §7); for Philadelphia, `philly_sites`, `philly_site_programs` and four school-grain keys in `philly_school_year_metrics` (Philadelphia doc §7). The sources carry more, grouped below. None of it is stored because nothing on the site reads it from the database. Every figure the two analysis pages show from it is computed by the packets from the source files, so none of those figures depends on it. **Adding a group back.** Commit `339dbe5`, on the branch of PR #39, stores all four groups; `git show 339dbe5:` prints each file as it stood there. | piece | path in `339dbe5` | |---|---| | Models `Building`, `SchoolBuilding`, `BuildingYearMetric`, `PhillyBuilding`, and the six `PhillySite` fields, with the design notes in their comments | `prisma/schema.prisma` | | The same as SQL: tables, indexes, foreign keys | `scripts/sql/capacity-schema.sql` | | RLS and public-read policies for them | `scripts/sql/enable-rls.sql`, the `Facilities tables` block | | NYC writes: buildings (name and district from each building's latest panel year), building facts, school-building links | `scripts/loaders/nyc-bluebook.ts` | | NYC building-grain definitions | `scripts/loaders/metric-definitions-capacity.ts` | | Philadelphia writes: buildings, deduped and conflict-checked, and the six site fields | `scripts/loaders/philly/facilities.ts` | | Philadelphia building-grain definitions | `scripts/loaders/philly/metric_definitions_facilities.ts` | | Proof trees for the Philadelphia building keys (`buildingMetric`), and the proof page's note for a key with no school value (`SCHOOL_FACILITIES_KEYS`) | `lib/philly/lineage.ts`; `app/philly/proof/metric/[metricKey]/page.tsx`, `lib/philly/facilities-display.ts` | | `bulkUpsert` emitting `ON CONFLICT … DO NOTHING` for an empty update list, which `school_buildings` needs: it has no payload columns, and `DO UPDATE SET` with no assignment is a Postgres syntax error (42601) | `scripts/loaders/_bulk.ts` | Generate the SQL with `prisma migrate diff` and apply it as a file, not with `prisma db push` (`scripts/loaders/README.md` says why), and add each table to the RLS block. **NYC building panel.** The Blue Book publishes every row at building grain as well as organization grain (`bldg_id`, `bldg_name`, `geo_dist`, `bldg_enroll`, `target_bldg_cap`, `target_bldg_util`). Over the twelve years that is 1,596 building codes, 1,352–1,460 a year carrying a building figure; 16,488 building enrollments, 16,277 target capacities and 16,276 utilizations; 26,451 school-building links, 2,151–2,290 a year over the DBNs the loader resolves; and each building's name and geographic district. It is in Socrata `gkd7-3vk7` and `data/cities/nyc/reference/parsed/bluebook_.csv`. `nyc-capacity-packet.ts` reads both for the building distributions, the co-location share and the page's school-against-building figure (NYC doc §4.1, §9); the loader rolls building rows up in memory to sum a split school, for check (iv) and its split-site report, and for the co-location counts it prints. What an implementer needs: - 38.5–42.1% of buildings with an enrollment and a capacity hold more than one organization in every year (35.7–41.3% of all buildings; NYC doc §1), so a building figure is keyed on the SCA building code and never copied onto the DBNs inside; copied, it would be counted once per organization in the building, up to nine times, in any citywide total. - School and building are many-to-many and change by year: 14–19% of organizations occupy more than one building, and a building holds several schools. The link is a year-scoped table, `(school_dbn, building_id, year)`, not a column on either side. - The `9xx` codes are annexes and transportables, and annex rows often carry no capacity, so a school's capacity is summed over its buildings. - A building's geographic district is not always the administrative district of the schools in it: it differs for D75, D79 and charter schools (01M094 is 75M094; NYC doc §3.1). It is a building attribute, like the name, and belongs to the latest year the building appears in, so loading an older year must not overwrite it. - `target_bldg_util` is the published building utilization, not a recomputed one. **Philadelphia buildings.** `data/cities/philly/reference/parsed/sdp_facilities_buildings.csv` is 502 rows for 399 buildings: use type, status, gross square footage (343 buildings carry one), year built, floor count, lot size, `BLDGCAPACITY` (249), `BLDGFCI`, the facility condition index (307) and replacement value (343); 362 buildings sit on 285 sites, and the other 37 carry nothing but the id. `philly-capacity-packet.ts` reads the file for its building counts and the `BLDGCAPACITY` comparison (Philadelphia doc §3.3, §6.5), and the probe reads the same fields live for floor area (Philadelphia doc §6.3). The loader's extract pin (`EXTRACT_SHA256`) covers this file, and nothing else in the loader reads it. What an implementer needs: - The extract repeats a building once per programme on its site (94 ids, up to four times), identical on every field but `ulcs_code`. Dedupe on `building_id`, and stop if a repeat ever disagrees: `dedupeBuildings` and `buildingRepeatConflicts` in `scripts/loaders/philly/_facilities.ts`, which the packet uses. - 47 of the 285 sites with a building hold more than one, garages and fieldhouses among them, so a site's floor area is summed over its classroom-use buildings, each counted once (`classroomAreaBySite` in the same file; Philadelphia doc §6.3). - `BLDGCAPACITY` is the app's 2017-labelled ("OLD") capacity. It is never "the" capacity: summed over a site's distinct buildings it runs 0.754–1.301× site capacity across the middle 80% of 207 district sites (§5). - `BLDGFCI` sits in the app's `BuildingsOld` table and is probably the older condition figure; the facility condition index is the current one. A figure built on either names its vintage (§8). **Six site-grain fields.** `data/cities/philly/reference/parsed/sdp_facilities_sites.csv` carries per site `sector` (on 285 of 328 sites), `school_level`, K-12 `enrollment`, `utilization_band`, `program_change` (282 of 328) and `project_recommendation`. `philly_sites` stores the site's identity, capacity, Pre-K allotment, enrollment with allotment and utilization, which are what a school page lists, and none of these six. The packet reads them from the file: `sector`, with a capacity and an enrollment, picks the 215 district sites, `school_level` gives the by-level table, and `program_change` is the decision list of Philadelphia doc §4.3. The loader reads `sector` to keep a site's District row and count district sites, and `enrollment` for the school roll-up and check (v). In `339dbe5` the six are nullable columns on `philly_sites`, with indexes on `sector` and `program_change`. Stored, `school_level` would carry the §8 hazard: the fetcher keeps whichever year's variant the engine emits first. **Six building-grain metric definitions.** NYC `building_enrollment`, `building_target_capacity` and `building_utilization_pct` (from `bldg_enroll`, `target_bldg_cap`, `target_bldg_util`); Philadelphia `facility_condition_index`, `gross_square_footage` and `building_capacity_alt`. None carries a school value, and the site shows none of them. Their descriptions in `339dbe5` state each one's grain and measured figures (the 2025-26 building median 81% against the school median 78%; 122.0 gross sq ft per seat, R² 0.721). A definition added back needs `subdomain = 'Facilities'` to stay out of the percentiles, and a Philadelphia one needs the proof page's per-building note.