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
• Philadelphia: cities/philly/06_enrollment_capacity_utilization.md
• Miami-Dade (the model): cities/miami/06_enrollment_capacity_closures.md

Gaps are graded by what it would take to close them:

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 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.

§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:

npm install
pip install -r pipeline_philly/requirements.txt

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 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_<year>.csv and one bluebook_<year>.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):

pip install -r pipeline_nyc/requirements.txt
python -m pipeline_nyc.parse.bluebook <year> <pdf> [<pdf>...] \
    --snapshot=<audited-register date> --evidence=<date-stamp json>

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.

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

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 <n> 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; (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 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:

  curl -H 'ModuleId: 2108' -H 'TabId: 174' \
    'https://www.nycsca.org/DesktopModules/FileList/API/FileList/getlistings?catName=29&viewType=1&resTopicFilter=&ltrFilter=&editMode=false'
  

• Each file's download URL comes from a second call, /DesktopModules/FileList/API/FileList/getfilebyid?ItemId=<DNNFileId>, 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/<timestamp>id_/<url>; 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_<year>/<date>/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 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:

(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 §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

In the code and the documents

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:<path> prints each file as it stood there.

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_<year>.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.