scripts/loaders/philly/metric_definitions_facilities.ts

// Seed the Philadelphia facilities metric keys — the capacity, enrollment and
// utilization facts written by scripts/loaders/philly/facilities.ts out of the
// SDP Facilities Data Dashboard. Idempotent — re-running upserts.
//
// Kept apart from philly/metric_definitions.ts (the outcome, survey and
// demographic keys), as NYC's facilities keys are kept apart in
// scripts/loaders/metric-definitions-capacity.ts.
//
// Run:  npx tsx scripts/loaders/philly/metric_definitions_facilities.ts
//
// Source is the SDP Facilities Data Dashboard behind philasd.org/fpp, pulled by
// pipeline_philly/fetch_facilities.py and loaded by
// scripts/loaders/philly/facilities.ts. Seed these BEFORE that loader:
// philly_school_year_metrics.metric_key carries a foreign key to this table.
//
// Four properties bear on every key below, and each is the opposite of what a
// first reading of the dashboard suggests:
//   * The grain is the SITE, not the school. 80 of 215 district sites with a
//     capacity host more than one programme, and every programme's row repeats
//     the site's figures, so counting rows double-weights those sites.
//   * School ↔ site is many-to-many: 32 schools sit on more than one site.
//     School-grain facts are written only where attribution is unambiguous.
//   * PreK allotment (seats, not children) is INSIDE the utilization numerator here.
//     NYC's Blue Book counts enrolled 3-K/Pre-K students and gives Pre-K rooms target
//     capacity, so the two cities' utilization figures are on different bases as published.
//   * One vintage exists, SY2024-25. The dashboard's School Year field is a
//     cross-join across the facilities table and carries no capacity
//     information — the figures under its 2016-17 label are the 2024-25
//     figures under a second label, so no trend can be read off it. (The app's
//     separate "OLD" measures — BLDGCAPACITY, labelled 2017 — are a different
//     thing and are unmeasured as a vintage; doc §6.5.)
//
// All four keys are school grain. The building figures the dashboard also
// publishes (condition index, floor area, a second capacity) are not stored
// (gap register §10).
//
// Every key is NEUTRAL: none of them is better in a direction. What keeps them
// out of the percentile batch, so that they carry no percentile, is subdomain =
// 'Facilities' (philly/compute_percentiles.ts), not the direction; a new
// capacity key stays out only if it carries that subdomain.
//
// Evidence: docs/cities/philly/06_enrollment_capacity_utilization.md.

import { prisma } from "./_lib";
import type {
  MetricDomain,
  MetricUnit,
  MetricDirection,
} from "@prisma/client";

type Def = {
  key: string;
  displayName: string;
  domain: MetricDomain;
  subdomain?: string;
  sourceName: string;
  sourceUrl: string;
  unit: MetricUnit;
  direction: MetricDirection;
  description: string;
  derived?: boolean;
};

const SDP_FACILITIES_URL = "https://www.philasd.org/fpp/";

const FACILITIES: Def[] = [
  {
    key: "site_capacity",
    displayName: "Capacity",
    domain: "ACCESS",
    subdomain: "Facilities",
    sourceName: "SDP Facilities Master Plan — Facilities Data Dashboard",
    sourceUrl: SDP_FACILITIES_URL,
    unit: "COUNT",
    direction: "NEUTRAL",
    description:
      "Seats the district's Facilities Master Plan assigns the site, summed across a school's sites. This is the denominator of utilization_pct and the basis every published Philadelphia capacity figure in this repo uses. It is not the only capacity number the dashboard carries: an older, 2017-labelled per-building figure (BLDGCAPACITY, not stored) runs 0.754-1.301x it across the middle 80% of 207 district sites (method doc §6.5). A school sharing a site with another school in the directory (the Master School List) gets no school-grain value, because the site's capacity is the site's and not that school's alone; the site record still carries the number. A site shared only with programmes the directory does not carry is attributed to the school it hosts: in SY2024-25 those programmes are all Pre-K, 71 of the 223 schools with a value sit on such a site, and the Pre-K seats allotted there are in their numerator. SY2024-25 only.",
  },
  {
    key: "site_enrollment",
    displayName: "Enrollment (incl. Pre-K allotment)",
    domain: "ACCESS",
    subdomain: "Facilities",
    sourceName: "SDP Facilities Master Plan — Facilities Data Dashboard",
    sourceUrl: SDP_FACILITIES_URL,
    unit: "COUNT",
    direction: "NEUTRAL",
    description:
      "The October 1 enrollment snapshot INCLUDING the Pre-K seats allotted to the site (the dashboard's Site Enroll w/ Allotment), which is the numerator of the published utilization score. A school on several sites of its own carries its enrollment ONCE plus the Pre-K seats allotted across its sites: the dashboard repeats the whole school's enrollment on every one of its site rows while splitting capacity per site, so summing multiplies a school by its site count — Walter Saul's four rows of 416 sum to 1,664 (NCES counts 417). Counted once, the stored figures correlate with the NCES 2024 enrollment count at r 0.999 (222 schools joined through the Master School List). A school sharing a site with another school in the directory gets no school-grain value; a site shared only with Pre-K programmes, which the directory does not carry, is attributed to the school it hosts.",
  },
  {
    key: "prek_allotment",
    displayName: "Pre-K allotment",
    domain: "ACCESS",
    subdomain: "Facilities",
    sourceName: "SDP Facilities Master Plan — Facilities Data Dashboard",
    sourceUrl: SDP_FACILITIES_URL,
    unit: "COUNT",
    direction: "NEUTRAL",
    description:
      "Pre-K seats allotted to the site — seats, not enrolled children. Carried separately so the numerator can be decomposed, but it is already inside site_enrollment and inside utilization_pct: the published score matches enrollment-plus-allotment on all 337 (site, programme) rows, and enrollment alone only on the 192 rows whose allotment is zero. A school on several sites sums it across them (it is published on one site only). NYC's Blue Book counts enrolled 3-K and Pre-K students rather than allotted seats, so a cross-city comparison names its Pre-K basis before the figures are shown together.",
  },
  {
    key: "utilization_pct",
    displayName: "Utilization",
    domain: "ACCESS",
    subdomain: "Facilities",
    sourceName: "SDP Facilities Master Plan — Facilities Data Dashboard",
    sourceUrl: SDP_FACILITIES_URL,
    unit: "PERCENT",
    direction: "NEUTRAL",
    description:
      "(Enrollment + Pre-K allotment) ÷ site capacity, as the district publishes it per site — the dashboard's own definition, and it reconciles exactly (max |diff| 0.0000 across 337 (site, programme) rows). A school on several sites of its own reads its enrollment once, plus the Pre-K seats allotted across its sites, over its capacity summed across those sites — the rule the dashboard's per-school figure uses; each of those sites' own published figure divides the whole school by that one site's capacity. Pre-K enters this numerator as allotted seats; NYC's Blue Book utilization_pct counts enrolled 3-K and Pre-K students over a target capacity that includes Pre-K rooms. The two keys share a name across the two cities and do not share a definition. On 215 distinct district sites in SY2024-25 the median is 67.1%, 24.7% sit under 50% and 13.5% over 100%, concentrated in the middle grades (standalone middle schools: n=12, median 45.8, half under 50%). Against the district's own published Program Change recommendations (base rate 7.4% Close), sites under 66% utilization carry a 7.47x lift (n=104, p=0.0012), under 75% an 8.04x lift (n=140, p=0.013), under 50% 5.09x (n=53, p=0.00093) and under 33% 5.29x (n=17); both sides of that test come from the same publication, so it measures how closely the district's decisions track its own stated criterion rather than out-of-sample prediction. School size separates more strongly than utilization: enrollment (with allotted Pre-K seats) under 300 carries a 10.72x lift (n=47).",
  },
];

async function main() {
  console.log(`[seed] upserting ${FACILITIES.length} Philly facilities metric definitions`);
  for (const def of FACILITIES) {
    await prisma.phillyMetricDefinition.upsert({
      where: { key: def.key },
      update: def,
      create: def,
    });
  }
  console.log(`[seed] done — ${FACILITIES.length} facilities metrics in philly_metric_definitions`);
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());