Find What Works: Schools
← All source documentsRaw file
Source document

docs/cities/philly/03_integration_plan.md

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.

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/<city>/ 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/<source>.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/<metric>__validation.md and <metric>__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.