# 03 — Integration Plan (P0–P5) Sequenced capabilities to ship the Philly build, analogous to KX's C0–C5 in `docs/design/03_integration_plan.md`. Each P-item is one branch + one focused PR (see `04_contribution_plan.md`). Effort = S (<1 day) / M (1–3 days) / L (>3 days) / XL (>1 week). Risk = Low / Med / High. **Sequencing principle:** the Philly source layer is the only thing genuinely new. Everything else (analysis engine, verification harness, UI components, methodology) lifts. So the work plan front-loads the source layer (P0–P2), then re-uses NYC infrastructure to ride. --- ## P0. Repo setup + scaffolding — **Effort S · Risk Low** Decision O.1 (same repo vs new repo) settles first. Two execution paths: - **If same repo (multi-city namespace):** add `lib/cities//` module pattern; parameterize `lib/queries/schools.ts` by a `city` discriminator; add `cities` enum to `schools` table. Substantial refactor of the NYC code. - **If new repo (`philly-edu-data` as a copy-then-modify):** fork the repo, rename, delete NYC-specific data, keep the architecture. Far less code surface to change at the cost of duplicated front-end maintenance. This plan is **written to apply equally to either path** — branch names use `philly/` prefix in same-repo mode and `main`-direct in fork mode. Concrete items: - `pipeline_philly/` Python package (or `pipeline/` if fork), `requirements.txt` pinned identically to NYC's scaffold. - `verify-philly/` Python harness directory (or `verify/`), copying the NYC harness's source-registry pattern. - `data/cities/philly/` for analysis-table outputs, reference files, QA reports. - Adapt `.gitignore` to ignore the new `data/processed/philly/`. - Empty `outcomes.yaml` / `covariates.yaml` / `comparability.yaml` for Philly. *Gate:* `make assemble-philly` (or `make assemble` in fork mode) runs end-to-end with empty configs and emits a zero-row parquet. No site impact. --- ## P1. Source ingest (test scores + grad + absenteeism) — **Effort L · Risk Med** The first real loader work. Three connector types to build, all under `scripts/loaders/philly/` (or `scripts/loaders/` in fork mode): 1. **PDE bulk xlsx connector** — for the authoritative PSSA + Keystone results files. 2. **OpenDataPhilly Socrata connector** — for SDP-curated copies (faster iteration than PDE). 3. **Future Ready PA Index xlsx connector** — for grad + chronic absenteeism + a few SDP-not-published indicators. Concrete items: - `docs/cities/philly/sources.yaml` populated for all v1 outcomes (the source registry stub already exists; this fills it in with URLs after first fetch). - One `scripts/loaders/philly/.ts` per source (xlsx/Socrata pull → normalize → upsert into `school_year_metrics`). - `metric-definitions-philly.ts` — registers all metric_keys with their direction, unit, source, applicable grade band. - Source-file provenance (sha256, retrieved_at, bytes) recorded into `DataLoad` per KX's pattern. - A `philly_schools` (or `schools` in fork) directory loader, populated from PDE's school code list + SDP's directory + the charter list. *Outcomes covered in P1:* PSSA grades 3–8 ELA + Math (proficiency rate, mean scale score), Keystone Algebra/Lit/Bio (proficiency rate), 4-year cohort graduation rate, chronic absenteeism rate. *Out of scope for P1:* surveys (P2), composites (P5). *Gate:* DB populated, smoke-test query returns expected row counts; no analysis work yet. --- ## P2. PSES survey ingest — **Effort M · Risk Low** PSES Zip files unpack to one CSV per respondent group. Each CSV has the school × question × response-option grain, which is essentially the same as NYCSS's "Pos & Neg %" sheet — KX's `nyc-school-survey.ts` pattern applies verbatim. Concrete items: - PSES connector: download Zip → unzip → upsert per-respondent CSVs into a `survey_responses` table. - Survey metric rollups: a `compute-survey-metrics-philly.ts` analog producing `survey_teacher_recommend_school`, `survey_teacher_trust`, `survey_student_safety`, `survey_student_respect`, `survey_parent_satisfaction`, `survey_principal_*`, `survey_support_staff_*` (PSES has more respondent groups than NYCSS). - Survey comparability regime config — PSES has been stable since 2017-18, so likely one regime. *Gate:* survey rollups appear in `school_year_metrics` with `subgroup=ALL` and `metric_key` matching the NYC naming. --- ## P3. Peer-group derivation + percentile compute — **Effort S · Risk Low** Lifted verbatim from the NYC build. The only Philly-specific decision is k — with ~300 schools instead of ~1,800, k=40 may produce overly homogeneous peer groups. Plan: derive at k=20, 30, 40 and flip-diff to pick. Concrete items: - `scripts/derive/peer-groups.ts` runs with Philly config (admission categories, grade bands match Philly enums). - `scripts/derive/percentiles.ts` computes citywide percentile + comparison-group percentile, direction-adjusted. *Gate:* the Outliers route renders with Philly data and direction-adjusted percentiles look correct on spot-checked schools (e.g. the most-coveted Special Admission high schools surface at the top decile for academic metrics). --- ## P4. Validation harness — **Effort M · Risk Low-Med** Direct adaptation of KX's `verify/`. The three connectors KX defined (`served_db.py`, `source_file.py`, `source_socrata.py`) all apply unchanged; only the registry contents differ. Concrete items: - `verify-philly/sources.yaml` (or replace contents of `verify/sources.yaml` in fork mode) populated for all P1 sources. - `verify-philly/snapshot.yaml` mapping each metric_key to its SPREE varname (where SPREE publishes a comparable measure). For metrics SPREE doesn't cover (e.g. some Keystone subgroups), the (iii) spot check is skipped and noted in `verify-philly/METHODOLOGY.md`. - `verify-philly/METHODOLOGY.md` — the Philly analog of KX's per-metric definitions doc; cites SDP's SPREE Educator Guide instead of NYC's SQR Educator Guide. - (ii) base case run for every Philly metric, gating a "validation pass" badge per outcome. - (iii) spot check against SPREE for the metrics it covers, ±1.5pp tolerance per KX's convention. *Gate:* `docs/qa_reports/__validation.md` and `__snapshot.md` both written per metric; pass rates documented in `METHODOLOGY.md`. --- ## P5. Analysis pipeline + stories + site — **Effort XL · Risk Med** The big front-end + analytical surface buildout. Most of the work is content (story stubs, methodology recipes) rather than code, since the NYC components all apply. Concrete items: - Lift `pipeline/analysis/{engine,outliers,trends,run_outcome,trend_runner}.py` from `docs/design/reference-scaffold/`. Configure `outcomes.yaml` / `covariates.yaml` / `comparability.yaml` for Philly outcomes (per O.4, O.5). - Run the analysis pipeline on the pilot outcome (PSSA grade 3 ELA + Math composite) — flip-diff against simple decile method. - Adapt UI components: Outliers/Movers filters get Philly metric labels (`items` map per the dropdown-label fix), Learning Network badges replace Borough badges, Special Admission badges replace Specialized HS badges. - 25 v1 Philly story stubs in `data/cities/philly/stories/` (or `data/stories/` in fork) — see `02_gap_analysis.md §F` for the planned list. - Per-story recipes following the `data/stories/recipes.ts` pattern. - Methodology page + about page rewritten for Philly context. *Gate:* full site walkthrough — every primary route returns 200, sample stories render, Outliers + Movers filter to PSSA grade 3 ELA + Math, school profile renders for ≥10 Philly schools across sectors. --- ## P-extras (post-v1, deferred) - **Cyber-charter sector** — add `CYBER_CHARTER` to school_type; tag schools serving Philly students; build a "Philly cyber-charter cohort" view. - **Statewide reference cohorts** — ingest all PA schools (not just Philly) so we can compute "Philly schools vs. demographically similar PA schools." - **Council district + neighborhood geography** — point-in-polygon join from school lat/lng to OpenDataPhilly boundaries. - **Per-pupil funding** — same gap as NYC; addable when SDP publishes school-allocation memos in structured form (currently PDF). - **InsideSchools-style narratives** — partnership with Great Philly Schools or hand-curated. --- ## Suggested sequencing `P0 → P1 → P2 → P3 → P4 → P5`, with v1 launch after P5 ships. Total estimated effort: 6–10 weeks of focused work for one engineer who already knows the NYC codebase. Risk concentrates in P1 (new connectors with first-time data sources). Each P-item is independently shippable behind a `?philly=1` query flag (or, in fork mode, as a separate site). The NYC build is not modified at any step.