# 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.yaml` pattern.
- **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.