02 — Gap Analysis (Philly source layer vs. NYC)
How the Philly build differs from the NYC build, dimension by dimension. Same
format as docs/design/02_gap_analysis.md (KX's NYC gap analysis), so the
direct comparison is legible.
Each row scores what changes to port the corresponding NYC capability to Philly. "Same" = lift verbatim; "Adapt" = config or schema tweak; "New" = code that has no NYC analogue.
A. Source ingest
| # | Dimension | NYC posture | Philly posture | Change |
|---|---|---|---|---|
| A.1 | Source connector kind | xlsx via NYC DOE InfoHub CDN | Mix of Socrata API (OpenDataPhilly) + xlsx via PDE | Adapt. New Python connector for Socrata; keep xlsx connector. |
| A.2 | School-roster join key | DBN (e.g. 10X440) | PDE 9-digit Public School Code as primary; SDP School ID as join | New. Primary-key migration; carry both IDs. |
| A.3 | Year encoding | YYYY-YY (e.g. 2024-25) | PDE uses YYYY-YY; SDP uses both YYYY-YY and end-year-int | Same. Loader normalizer handles both already. |
| A.4 | Suppression rule | n < 5 (DOE), n < 10 (NYSED) | n < 11 (PDE) | Adapt. Threshold in outcomes.yaml. |
| A.5 | File-bytes provenance | sha256 + retrieved_at + bytes via verify/download.py | Same connector pattern works on PDE xlsx; Socrata pulls record response metadata | Same. KX's download.py extends to Socrata directly. |
| A.6 | Source-file refresh URL stability | InfoHub URLs are stable but unversioned | OpenDataPhilly Socrata URLs are stable; PDE URLs are stable per dataset | Same posture. Annual full re-pull. |
B. Schema (Postgres)
| # | Dimension | NYC | Philly | Change |
|---|---|---|---|---|
| B.1 | schools table | DBN as primary key; community-district + borough columns | Composite AUN + Schl as primary key (see findings.md §F.1); LEA-level joins via AUN; SDP's AUN = 126515001. Geography: learning_network + city_council_district + zip_code + lat/lng. | Adapt. Composite PK exposed as a single school_code = "<AUN>-<Schl>" for convenience; carry both columns. |
| B.2 | school_type enum | DOE_PUBLIC, CHARTER, D75_SPECIAL_ED, D79_TRANSFER, ALT_PROGRAM | PDE's actual enum (findings.md §F.5): regular, charter, compctc, cyber, specialed. Renaissance Charter status is not a PDE category — carry it as a derived is_renaissance boolean from the SDP charter office list. | Adapt. Adopt PDE's 5-value enum + is_renaissance derived column; do NOT invent SPECIAL_ADMISSION here (that's an admission category). |
| B.3 | admission_category enum | ZONED, OPEN, SCREENED, AUDITION, LIMITED_UNSCREENED, EDOPT, SPECIALIZED_HS, etc. | Simpler: NEIGHBORHOOD, CITYWIDE_LOTTERY, SPECIAL_ADMISSION, CHARTER_LOTTERY, MAGNET | Adapt. Smaller enum. |
| B.4 | subgroup enum | ALL, ASIAN, BLACK, HISPANIC, WHITE, MULTI_RACIAL, MALE, FEMALE, ELL, FORMER_ELL, NEVER_ELL, SWD, NON_SWD, ECON_DIS, NOT_ECON_DIS, TEMP_HOUSING | PDE actual (from findings.md §F.2): ALL, AMER_INDIAN_AK_NATIVE, ASIAN, HAWAIIAN_PAC_ISL, BLACK, HISPANIC, WHITE, TWO_OR_MORE_RACES, ECON_DISADV, ELL, SWD, COMBINED_ETHNICITY. No gender, no temp-housing, no former-ELL. | Adapt. Richer racial granularity than NYC (Amer Indian, Hawaiian/Pac Isl separate); narrower on gender + status subgroups. |
| B.5 | school_year_metrics long-format table | One row per (school, year, metric_key, subgroup, value, suppressed, n) | Same. Pattern works identically; loader unpivots Future Ready's wide-format <Measure>_<Subgroup> columns. | Same. |
| B.5a | First-class essa_designation column on schools | (NYC doesn't have it) | Per findings.md §F.3: 5-value enum DFLT/TSI/CSI/ATSI/ACSI; updated annually; analytically substantive (53 Philly schools are CSI, 9 are ACSI). | New column. Belongs in schools, not as a story tag. |
| B.5b | First-class PVAAS growth indicator | (NYC doesn't have it) | Future Ready exposes PVAAS growth per subject × subgroup (findings.md §F.2). Status-vs-growth decision (NYC's spec §9.3) is settled here by data availability. | New metric family. pvaas_growth_* keys; v1 includes at least Algebra/Lit/Bio. |
| B.6 | metric_definitions | One row per metric_key | Same structure; ~90 metric_keys for PSSA grades × subjects + Keystone + grad + absenteeism + PSES rollups | Same. Populated by an analog of metric-definitions.ts. |
| B.7 | Peer-group tables (peer_groups, school_peers) | KNN(K=40) on grade band + admission + demographics + topic | Same algorithm; smaller N (~300 vs ~1,800) means k might want to drop to 20–30 for stability | Same code; revisit k in config. |
| B.8 | Survey raw table | One row per (school, year, question_id, respondent, value, n_respondents) | Same. PSES Zip files unpack to the same shape. | Same. |
C. Analysis pipeline (Python, per KX's 03 §B)
| # | Capability | Status in NYC | Philly change |
|---|---|---|---|
| C.1 | pipeline/analysis/engine.py (WLS + standardize + flag) | Lifted from scaffold; per spec | Same. No PA-specific logic. |
| C.2 | pipeline/analysis/outliers.py, run_outcome.py | Lifted | Same. |
| C.3 | pipeline/analysis/trends.py + comparability regimes | Lifted; comparability.yaml configures regime breaks | Adapt. Three regime breaks for PSSA: 2014-15 (PA Core), 2022-23 (post-COVID recalibration). |
| C.4 | pipeline/assemble/build_analysis_table.py | DB query → parquet | Adapt. Same shape, queries Philly schema. |
| C.5 | Outcome configs (outcomes.yaml) | 8 outcomes (ela_g3, math_g3, ela_g7, math_g7, ela_all, math_all, grad_4yr, chronic_absent) | New file. ~12 outcomes: PSSA g3-8 ELA, PSSA g3-8 Math, Keystone Alg/Lit/Bio, grad_4yr, chronic_absent. |
| C.6 | Covariate configs (covariates.yaml) | Race composition, enrollment, admission | Same covariate types; values differ. |
| C.7 | Composites on standardized residuals (C5 in NYC plan) | Deferred (last) | Same sequencing. |
D. Validation harness (verify/ analog)
| # | Capability | NYC posture | Philly posture |
|---|---|---|---|
| D.1 | (i) Completeness | verify/reconcile.py reports source-only DBNs | Same. Reports source-only PDE codes. |
| D.2 | (ii) Base case | DB vs exact ingested xlsx (per-cell fidelity) | Same mechanism; PDE / OpenDataPhilly source files. |
| D.3 | (iii) Spot check | DB vs NYC School Snapshot | Use SPREE as the (iii) reference. SDP's SPREE composite is the Philly analogue. Per-metric mappings in snapshot.yaml-equivalent. |
| D.4 | (iv) Computed values | Not done in NYC build either | Same gap. Deferred. |
| D.5 | Independent-publisher reference | Deferred (NYSED) | Available day one — PDE and Future Ready PA Index. Use PDE bulk file as the (ii) source-of-truth and Future Ready PA Index as a third reference for cross-checking SPREE. |
E. Front-end & UX
| # | Surface | NYC change | Philly change |
|---|---|---|---|
| E.1 | Outliers page | Direction-adjusted percentile, peer/city pills, proclivity badge | Same components; copy needs Philly terminology (Learning Network not District 15, etc.). |
| E.2 | Movers page | Year-over-year delta with metric-aware direction | Same. |
| E.3 | School profile page | One outcome card per metric × subgroup × peer group | Same. |
| E.4 | Stories | 54 hand-written analyses keyed to NYC programs (Renewal, Small Schools, D15) | All new. ~25 Philly-specific story stubs at v1 launch (see §G). |
| E.5 | Methodology recipes | One recipe per story + Outliers + Movers | Same infrastructure; new content. |
| E.6 | Geography taxonomy | Borough + community district badges | Adapt. Learning Network + city council district. |
F. Stories — the new analytical surface area
Philly has its own program history that maps to interesting analytical questions. Drafted as story stubs, replacing the NYC equivalents:
| Philly story | NYC analogue |
|---|---|
| The Hite closures retrospective (2013) — 23 schools closed during Hite tenure; where did the kids land, what happened to outcomes? | NYC Renewal Schools postmortem |
| Mastery / Renaissance Charter conversion trajectory — schools converted 2010–2015 from district to Renaissance Charter; do their outcomes diverge from a matched-comparison set? | NYC Small Schools legacy |
| Special Admission high schools (Masterman, Central, etc.) — Philly's "specialized HS" analogue; selection effects + value-add | NYC SHSAT story |
| Cyber-charter outcome cliff — students leaving Philly district to cyber-charters; per-student outcome trajectory if cohort data permits | (no analogue; new) |
| Lottery-vs-screened middle schools — Philly's special-admission middle schools (Masterman, GAMP, etc.) compared with neighborhood schools | NYC D15 screen-vs-lottery |
| Climate-survey vs outcomes — same template, PSES instead of NYCSS | NYC Teacher Voice Attendance story |
| Charter sector outcome composition — district vs charter, adjusted for student-body | (NYC has it as a small piece; Philly is the main story) |
| Asian achievement variance — Philly's South Asian + East Asian populations have different outcome distributions | NYC equivalent |
| ... | ... |
The 25 stubs are populated in docs/cities/philly/stories/ after P3 ships
(see 03_integration_plan.md).
G. Bottom line
- Source layer: mostly new code (Socrata + PDE connectors), shaped by KX's
verify/sources.yamlpattern. - Schema: structurally the same; enums and PK change.
- Analysis pipeline: lifted verbatim from the scaffold.
- Verification harness: lifted; spot-check reference changes from NYC Snapshot to SDP SPREE.
- Front-end: lifted; copy + geography taxonomy adapt.
- Stories: all new.
Estimated relative effort vs. NYC build: ~40% of the NYC effort. Smaller universe, fewer source publishers, the methodology is solved, and the verification spine + UI components carry over.