scripts/loaders/philly/_facilities.ts
Served verbatim from the project repository. Internal working document conventions apply: documents may reference file paths, branch names, and findings-ledger anchors from the repo.
// Pure parsing for the SDP Facilities Master Plan extract. No Prisma, no IO —
// `facilities.ts` does that. Split out so the grain rules can be unit-tested
// without a database (tests/philly-facilities-transform.test.ts).
//
// Three grain rules, each the opposite of a first reading. All three are
// measured in docs/cities/philly/06_enrollment_capacity_utilization.md §3.
//
// 1. SITE is the grain capacity is published at, and a site hosting a
// district school plus its Pre-K programme (or a co-located charter)
// repeats the SAME capacity, enrollment and utilization on every
// programme's row. 91 of 328 sites. Counting rows double-weights them.
//
// 2. School ↔ site is MANY-TO-MANY. 32 schools sit on more than one site;
// 91 sites host more than one programme. A foreign key on either side
// would drop rows, so the relation needs a join table.
//
// 3. BUILDING is a third grain — floor area, year built, condition, use type
// and a second capacity figure. 47 of the 285 sites with a building hold
// more than one, and they are not all schoolhouses: John Bartram's site
// (S022) carries a Garage (16,000 sq ft) and its Main Classroom Building
// (270,000); its fieldhouse sits on a separate site. Any floor-area
// statistic has to sum the CLASSROOM-use buildings per site; reading one
// building's area as the site's silently substitutes a garage for a
// school. The extract also repeats a building once per programme on its
// site, so every per-site sum below dedupes on building_id first.
//
// NOTE the numerator: Philadelphia's published utilization INCLUDES the PreK
// allotment — seats allotted to the site's early-childhood programmes, not enrolled
// children. NYC's Blue Book counts enrolled 3-K/Pre-K students. The two cities'
// figures are on different bases, and no common-basis figure is computed;
// wherever they appear together the numerator, the denominator and the Pre-K
// basis are named (doc §4.2).
export type SiteRow = Record<string, string>;
export type BuildingRow = Record<string, string>;
/**
* Extract cells are strings; "" and "-" are both absent, and 0 is not.
* Thousands separators and a currency sign are dropped: replacement_value
* arrives as "$9,639,300". A cell of nothing but those is absent.
*/
export function int(v: string | undefined): number | null {
if (v === undefined) return null;
const s = v.trim();
if (s === "" || s === "-") return null;
const n = Number(s.replace(/[$,]/g, "") || NaN);
return Number.isFinite(n) ? Math.round(n) : null;
}
export function float(v: string | undefined): number | null {
if (v === undefined) return null;
const s = v.trim();
if (s === "" || s === "-") return null;
const n = Number(s.replace(/[$,]/g, "") || NaN);
return Number.isFinite(n) ? n : null;
}
/**
* Collapse (site × programme) rows to one record per site — rule 1.
*
* Every programme row on a site carries identical figures, so any row would do
* for capacity; the District-sector row is preferred because it also carries
* the school level and the district's recommendation.
*/
export function dedupeSites(rows: readonly SiteRow[]): Map<string, SiteRow> {
const bySite = new Map<string, SiteRow[]>();
for (const r of rows) {
const id = (r.site_id ?? "").trim();
if (!id) continue;
const bucket = bySite.get(id);
if (bucket) bucket.push(r);
else bySite.set(id, [r]);
}
const out = new Map<string, SiteRow>();
for (const [id, group] of bySite) {
out.set(id, group.find((r) => (r.sector ?? "").trim() === "District") ?? group[0]);
}
return out;
}
/**
* Collapse the building cube to one record per building — the same hazard as
* rule 1, one grain down.
*
* The building cube carries `ulcs_code`, so a building on a site hosting more
* than one programme is emitted once PER PROGRAMME: 502 extract rows are 399
* distinct buildings, and 94 building ids repeat (up to four times). The
* repeats are identical on every field except `ulcs_code`, which no building
* figure uses, so the first row of each group is a lossless choice.
*
* Counting rows instead of buildings overstates the stock by 26% and, worse,
* summing `building_capacity` over rows double-counts the seats of any
* building on a multi-programme site.
*/
export function dedupeBuildings(rows: readonly BuildingRow[]): Map<string, BuildingRow> {
const out = new Map<string, BuildingRow>();
for (const r of rows) {
const id = (r.building_id ?? "").trim();
if (!id || out.has(id)) continue;
out.set(id, r);
}
return out;
}
/**
* The building ids whose repeated rows differ on anything but `ulcs_code`.
*
* `dedupeBuildings` keeps the first row of each group, which is lossless only
* while the repeats are identical. On the 2024-25 extract they are — 94
* repeated ids, and only `ulcs_code` varies — so this returns []. A non-empty
* result means the source changed shape and the first-row choice would now
* drop information; callers throw rather than guess.
*/
export function buildingRepeatConflicts(
rows: readonly BuildingRow[],
ignore: readonly string[] = ["ulcs_code"],
): string[] {
const byId = new Map<string, BuildingRow[]>();
for (const r of rows) {
const id = (r.building_id ?? "").trim();
if (!id) continue;
const bucket = byId.get(id);
if (bucket) bucket.push(r);
else byId.set(id, [r]);
}
const conflicts: string[] = [];
for (const [id, group] of byId) {
if (group.length < 2) continue;
const keys = new Set(group.flatMap((r) => Object.keys(r)));
for (const k of ignore) keys.delete(k);
const first = group[0];
if (group.some((r) => [...keys].some((k) => (r[k] ?? "").trim() !== (first[k] ?? "").trim()))) {
conflicts.push(id);
}
}
return conflicts.sort();
}
/** Every distinct (site, school) pair — rule 2. Rows with no ULCS are dropped. */
export function siteProgramLinks(rows: readonly SiteRow[]): Array<{ siteId: string; ulcsCode: string }> {
const seen = new Set<string>();
const out: Array<{ siteId: string; ulcsCode: string }> = [];
for (const r of rows) {
const siteId = (r.site_id ?? "").trim();
const ulcsCode = (r.ulcs_code ?? "").trim();
if (!siteId || !ulcsCode) continue;
const key = `${siteId}\u0000${ulcsCode}`;
if (seen.has(key)) continue;
seen.add(key);
out.push({ siteId, ulcsCode });
}
return out;
}
/**
* Classroom-use floor area summed per site, each building counted once — rule 3.
*
* Dedupes on building_id itself, so raw extract rows and deduplicated records
* give the same answer. Summing the raw rows inflates 83 of the 253 sites
* carrying classroom area, because a building on a multi-programme site is
* emitted once per programme.
*/
export function classroomAreaBySite(buildings: readonly BuildingRow[]): Map<string, number> {
const out = new Map<string, number>();
for (const b of dedupeBuildings(buildings).values()) {
if (!(b.use_type ?? "").trim().startsWith("Classroom")) continue;
const siteId = (b.site_id ?? "").trim();
const area = int(b.gross_sqft);
if (!siteId || area === null || area <= 0) continue;
out.set(siteId, (out.get(siteId) ?? 0) + area);
}
return out;
}
/**
* The alternate capacity basis: `BLDGCAPACITY` summed per site, each building
* counted once.
*
* Dedupes on building_id itself, so raw extract rows and deduplicated records
* give the same answer. Summing the raw rows would inflate 79 of the 236 sites
* carrying a figure — 68 exactly doubled, 7 tripled, and 4 by a fraction where
* only one of a site's two capacity-bearing buildings is linked to both of its
* programmes. The deduplicated sum equals the dashboard's own
* `Sum([BLDGCAPACITY])` per site on all 236, because the field lives in a table
* keyed by building rather than in the per-programme Buildings table.
*
* Kept so the denominator sensitivity can be shown, never served as "the"
* capacity. The app labels it "Current Capacity (OLD)" (2017) and documents
* nothing more about how it was measured (doc §6.5).
*/
export function buildingCapacityBySite(buildings: readonly BuildingRow[]): Map<string, number> {
const out = new Map<string, number>();
for (const b of dedupeBuildings(buildings).values()) {
const siteId = (b.site_id ?? "").trim();
const cap = int(b.building_capacity);
if (!siteId || cap === null || cap <= 0) continue;
out.set(siteId, (out.get(siteId) ?? 0) + cap);
}
return out;
}
/**
* Validation check (iv): the published utilization must equal
* (enrollment + PreK allotment) / capacity. It does, exactly, for every site
* with all three parts — so any failure means the source's definition changed
* and the loader should refuse to load rather than serve a redefined metric.
*/
export function utilizationAgreement(sites: Iterable<SiteRow>): {
checked: number;
within: number;
maxDiff: number;
} {
let checked = 0;
let within = 0;
let maxDiff = 0;
for (const r of sites) {
const cap = int(r.capacity);
const enr = int(r.enroll_with_allotment);
const util = float(r.utilization);
if (!cap || enr === null || util === null) continue;
checked++;
const diff = Math.abs(util - enr / cap);
if (diff <= 0.005) within++;
if (diff > maxDiff) maxDiff = diff;
}
return { checked, within, maxDiff };
}
/**
* Validation check (v): on every site, the dashboard's Site Enroll w/ Allotment
* must equal its enrollment plus its PreK allotment (a blank allotment counts
* as 0). It does on all 285 sites carrying either figure. The school roll-up
* below reads `enrollment` and `prek_allotment` separately and adds them, so it
* leans on this identity; a failure means the extract changed shape.
*/
export function allotmentIdentity(sites: Iterable<SiteRow>): { checked: number; failing: string[] } {
let checked = 0;
const failing: string[] = [];
for (const r of sites) {
const enr = int(r.enrollment);
const withAllotment = int(r.enroll_with_allotment);
if (enr === null && withAllotment === null) continue;
checked++;
if (enr === null || withAllotment !== enr + (int(r.prek_allotment) ?? 0)) {
failing.push((r.site_id ?? "").trim());
}
}
return { checked, failing: failing.sort() };
}
/**
* Validation check (vi): the school roll-up against the district's own figure.
*
* Check (iv) is per site, so it cannot see a roll-up error. Where a school's
* capacity sits on exactly one of its linked sites, the dashboard's published
* utilization for that site already divides the whole school by the whole
* school's capacity, so the school-grain figure must reproduce it within 0.005.
* On the 2024-25 extract that is 220 of the 223 directory schools with a
* figure, and all 220 agree; a roll-up that summed enrollment across sites
* would agree for 200, so the check tells the two rules apart. A missing
* published figure on that site counts as a failure — it is a shape the
* extract does not contain. The other three schools (2210, 7440, 8770) have
* capacity on two sites, and each of those sites' published figure divides
* the whole school by one campus, so there is nothing to compare.
*/
export function schoolSiteAgreement(
sites: ReadonlyMap<string, SiteRow>,
links: ReadonlyArray<{ siteId: string; ulcsCode: string }>,
facts: ReadonlyArray<{ ulcsCode: string; utilizationPct: number }>,
): { checked: number; within: number; failing: string[] } {
const capacitySites = new Map<string, string[]>();
for (const { siteId, ulcsCode } of links) {
if ((int(sites.get(siteId)?.capacity) ?? 0) <= 0) continue;
if (!capacitySites.has(ulcsCode)) capacitySites.set(ulcsCode, []);
capacitySites.get(ulcsCode)!.push(siteId);
}
let checked = 0;
let within = 0;
const failing: string[] = [];
for (const f of facts) {
const one = capacitySites.get(f.ulcsCode);
if (!one || one.length !== 1) continue;
checked++;
const published = float(sites.get(one[0])?.utilization);
if (published !== null && Math.abs(f.utilizationPct / 100 - published) <= 0.005) within++;
else failing.push(f.ulcsCode);
}
return { checked, within, failing: failing.sort() };
}
/**
* School-grain capacity, ONLY where attribution is unambiguous.
*
* A school whose site is linked to another school in `links` cannot have that
* site's capacity attributed to it alone — the figure is the site's. Those
* schools are returned in `shared` and get no school-grain fact; the site
* record still carries the number. That test runs FIRST: a shared site's
* enrollment is the total of every programme on it (Strawberry Mansion's S109
* reads 542 = its own 307 plus two One Bright Ray programmes), so no roll-up
* can be read off it.
*
* "Another school" means another code in `links`. The loader passes only links
* to schools in the directory (the Master School List), so a site shared only
* with programmes the directory does not carry is attributed to the school it
* hosts. On the 2024-25 extract those programmes are all Pre-K: 71 of the 223
* directory schools with a figure sit on a site shared only with Pre-K
* programmes, and the Pre-K seats allotted there are in their numerator.
*
* A school on several sites of its own (23 of the 223 directory schools with a
* figure in SY2024-25) is rolled up the way the dashboard's own per-school
* measure does it (doc §3.2): capacity SUMMED over its sites; Pre-K allotment
* SUMMED over its sites (on the 2024-25 extract no school carries an
* allotment on more than one site, so the sum is that one site's); enrollment
* counted ONCE — the dashboard repeats the whole school's enrollment on every
* one of its site rows, so summing multiplies a school by its site count
* (Walter Saul: 4 × 416 = 1,664). A school whose own sites disagree on
* enrollment is returned in `conflicts`, and the loader then refuses the
* whole load, writing nothing. A blank capacity adds
* nothing and an explicit 0 counts; a school with no capacity, a total of 0,
* or no enrollment gets no fact rather than a zero.
*/
export function schoolFacts(
sites: ReadonlyMap<string, SiteRow>,
links: ReadonlyArray<{ siteId: string; ulcsCode: string }>,
): {
facts: Array<{ ulcsCode: string; capacity: number; enrollment: number; prek: number; utilizationPct: number }>;
shared: string[];
conflicts: string[];
} {
const sitesPerSchool = new Map<string, string[]>();
const schoolsPerSite = new Map<string, string[]>();
for (const { siteId, ulcsCode } of links) {
if (!sitesPerSchool.has(ulcsCode)) sitesPerSchool.set(ulcsCode, []);
sitesPerSchool.get(ulcsCode)!.push(siteId);
if (!schoolsPerSite.has(siteId)) schoolsPerSite.set(siteId, []);
schoolsPerSite.get(siteId)!.push(ulcsCode);
}
const facts: Array<{ ulcsCode: string; capacity: number; enrollment: number; prek: number; utilizationPct: number }> = [];
const shared: string[] = [];
const conflicts: string[] = [];
for (const [ulcsCode, siteIds] of sitesPerSchool) {
if (siteIds.some((s) => (schoolsPerSite.get(s)?.length ?? 0) > 1)) {
shared.push(ulcsCode);
continue;
}
let capacity = 0;
let prek = 0;
let sawCapacity = false;
const enrollments = new Set<number>();
for (const s of siteIds) {
const r = sites.get(s);
if (!r) continue;
const c = int(r.capacity);
if (c !== null) { capacity += c; sawCapacity = true; }
const e = int(r.enrollment);
if (e !== null) enrollments.add(e);
prek += int(r.prek_allotment) ?? 0;
}
if (enrollments.size > 1) {
conflicts.push(ulcsCode);
continue;
}
if (!sawCapacity || capacity === 0 || enrollments.size === 0) continue;
const enrollment = [...enrollments][0] + prek;
facts.push({ ulcsCode, capacity, enrollment, prek, utilizationPct: (100 * enrollment) / capacity });
}
return { facts, shared: shared.sort(), conflicts: conflicts.sort() };
}