# Findings from first-fetch (2026-06-03)

Real surprises and validations from fetching the first two sources. Every
finding triggers either a `sources.yaml` update or a planning-doc edit.

## ✓ Plan validated

- **Philly school count: 302** (212 district + 81 charter + 6 CTC + 2 cyber + 1 special-ed by `OrganizationTypeCode`). The plan estimated ~300 with ~210 district + ~85 charter. **On the money.**
- **Future Ready PA Index is a single bundle covering every PA school** with school-level rows. The plan assumed this.
- **Subgroup breakdowns are present** for every metric × 12 subgroups: All / AmericanIndianAlaskaNative / Asian / HawaiianPacificIslander / Black / Hispanic / White / 2orMoreRaces / EconomicallyDisadvantaged / EnglishLearner / StudentsWithDisabilities / CombinedEthnicity. **Richer than NYC**, which doesn't carry HawaiianPacificIslander or AmericanIndianAlaskaNative subgroups separately.

## ⚠ Plan surprises (substantive)

### F.1 Join key is **composite** (AUN + Schl), not single 9-digit code

The plan said:
> PDE 9-digit Public School Code as primary key (covers both sectors)

The reality: every PA school file uses **`AUN`** (9-digit LEA Administrative
Unit Number) + **`Schl`** (4-digit within-LEA school number). The 9-digit
"school code" doesn't exist as a single column — what we want is the
composite key `(AUN, Schl)`.

Implication for `02_gap_analysis.md §B.1` and the future schema:
- `schools` PK should be a derived `school_code` = `concat(AUN, '-', Schl)` (13 chars). Carry both `AUN` and `Schl` as columns alongside.
- LEA-level joins (district info, SDP-only filtering) use `AUN`.
- **SDP's AUN is `126515001`.** All SDP schools have AUN starting `1265…`.

### F.2 Future Ready is **richer than the spec assumed**

The Performance file has **three sheets totalling 514 columns** of measures —
not just the indicators we estimated. Specifically:

| Sheet | Cols | Contains |
|---|---|---|
| State Assessment Measures | 228 | PSSA + Keystone (Algebra/Lit/Bio) × subgroup × {%Proficient+Advanced, %Advanced, PVAAS growth, ESSA goal, ESSA-progress flag} |
| School On Track Measures | 93 | English-language-proficiency growth, chronic absenteeism, suspensions, regular attendance |
| College Career Measures | 193 | Grade 5/8/11 career standards, AP, dual enrollment, industry credentials, post-secondary enrollment |

Two consequences:

1. **PVAAS growth metric is available alongside proficiency.** This is a
   *value-added* indicator that NYC doesn't have. It enables a real
   distinction between status-of-students and growth-of-students that NYC
   couldn't make. (Settles `00_overview.md §3 — O.1 in the methodology spec,
   not the repo decision: status vs. growth controls.)
2. **`outcomes.yaml` should be expanded** to include the College/Career
   indicators as future-v2 outcomes. Not in scope for v1 but worth tagging
   as available.

### F.3 ESSA designations are a substantive analytical dimension

Every Philly school has an `ESSASchoolDesignation`:

| Designation | Meaning | Philly count |
|---|---|---|
| DFLT | Default — meeting goals | 120 |
| TSI | Targeted Support and Improvement | 85 |
| CSI | Comprehensive Support and Improvement | 53 |
| ATSI | Additional Targeted Support and Improvement | 35 |
| ACSI | Additional Comprehensive Support and Improvement | 9 |

**53 Philly schools are CSI (the federally-designated bottom 5%)**, and
9 are ACSI (chronically lowest). This is a *live* analogue to NYC's Renewal
Schools — except it's still in effect, federally-mandated, and updated
annually. **This belongs in the `schools` table as a first-class column**,
not as a story tag. The "CSI/ACSI school retrospective" deserves its own
story stub.

### F.4 Cyber-charter footprint in Philly is **smaller than I framed**

The plan called cyber-charters "out of scope for v1 because they serve
students statewide; attribution to Philly is conceptually fuzzy." That
framing still stands for the **statewide cyber-charter universe**, but the
Future Ready file shows only **2 cyber-charter schools with a Philadelphia
physical address** — both very small footprints. The big cyber-charter
question isn't "which Philly cyber-charters are good?" but "how many Philly
students attend cyber-charters that are physically located elsewhere?" —
which requires student-enrollment-origin data not in this file.

Net: keep v1 scope. The statewide-cyber-charter story is its own larger
project; the 2 Philly-located cyber-charters can be tagged but won't move
analytical narrative weight.

### F.5 The `regular` / `charter` / `compctc` / `cyber` / `specialed`
       OrganizationTypeCode enum, not what the plan wrote

The plan proposed a 6-value `school_type` enum:
> SDP_DISTRICT, RENAISSANCE_CHARTER, CHARTER, CYBER_CHARTER, ALTERNATIVE_ED, SPECIAL_ADMISSION

The Future Ready actual: 5 values (`regular`, `charter`, `compctc`, `cyber`,
`specialed`). **Renaissance charter is not distinguished from regular
charter in PDE's classification.** We'd have to derive Renaissance status
from a different source (SDP's charter office list).

Net change to `02_gap_analysis.md §B.2`:
- Adopt PDE's 5-value enum as the source-of-truth.
- Carry a derived `is_renaissance` boolean (from SDP's charter office list,
  not yet ingested).
- Drop `SPECIAL_ADMISSION` from `school_type` (it's an admission category,
  not a school type — fits cleanly under `admission_category`).

## What this changes downstream

| Plan doc | Section | Edit needed |
|---|---|---|
| `02_gap_analysis.md` | B.1 (PK) | composite AUN + Schl, not single 9-digit code |
| `02_gap_analysis.md` | B.2 (school_type enum) | adopt PDE's 5-value enum + `is_renaissance` derived column |
| `02_gap_analysis.md` | B.4 (subgroup enum) | add `AMER_INDIAN`, `HAWAIIAN_PACIFIC_ISL`, `COMBINED_ETHNICITY`, drop `TEMP_HOUSING` (PA doesn't carry it) |
| `03_integration_plan.md` | P0 | add a column-name normalization step to handle Future Ready's `<MeasureName>_<Subgroup>` wide format (NYC ingested long-format files; Future Ready is wide) |
| `05_validation_spec.md` | Per-metric table | replace TBD source columns with the now-known `AUN`, `Schl`, `<MeasureName>_<Subgroup>` |
| `docs/cities/philly/00_overview.md` | §3 (open decisions) | add O.7: "Add PVAAS growth as a first-class indicator alongside proficiency, or defer to v2?" |
| `sources.yaml` | All `futurereadypa_*` entries | column names now known |

## What's still TBD

- `pde_pssa` and `pde_keystone` URLs — first-fetch needed to confirm sheet shape (or use Future Ready as the substitute and skip these for v1).
- All `pses_*` (5 PSES respondent files) — URL pattern not known until fetch.
- `sdp_spree` — SPREE files at `philasd.org/performance/programsservices/spree/` need URL discovery.
- `sdp_school_profiles` Socrata resource ID — discovery via OpenDataPhilly catalog.
- `opendataphilly_pssa` Socrata resource ID — same.

Each becomes its own first-fetch run.

## Bottom line

The Future Ready bundle alone is **enough to populate v1** for the 4 core
outcomes (PSSA ELA, PSSA Math, Keystone, grad rate) plus chronic
absenteeism. The PDE PSSA/Keystone bulk files can be skipped for v1; we
add them in v2 if we need grade-by-grade breakdowns Future Ready doesn't
expose at the school × grade × subgroup grain. The OpenDataPhilly + SDP
files are still important for **school directory enrichment** (SDP Learning
Network, City Council district, school-profile demographics) and for the
**PSES survey** ingest.

---

# Batch 2 findings (2026-06-03) — SDP CDN discovery

Updated after fetching 5 more sources: SDP PSSA+Keystone Zip, PSES Zip,
SDP graduation CSV, SDP 90%-attendance CSV, SDP suspensions CSV.

## ✓ Plan validated

- **PSES is one Zip per year, not five** (as the plan originally enumerated). One source_id per year is sufficient; the Zip contains a codebook plus xlsx per respondent group.
- **Stable CDN URLs at `cdn.philasd.org`**, going back to 2009-10 for PSSA, 2014-15 for PSES, 2013-14 for graduation. Discovery problem solved for SDP data.
- **PSSA + Keystone subgroup richness exceeds NYC's**: each school × subject × grade × subgroup row has `bel/bas/prof/adv/profadv` counts AND percentages (5 levels × 2 measures). NYC publishes only "% Level 3+4." Philly gives full distribution.

## ⚠ Batch 2 surprises

### F.6 SDP publishes **two population cuts per metric**, side by side

The SDP PSSA Zip contains **two flavors of the same data**:

| Variant | File suffix | Population |
|---|---|---|
| **Actual** | `*_Schools_Actual_Suppressed.csv` | All tested students at the school |
| **Acct** | `*_Schools_Acct_Suppressed.csv` | Accountability cohort (Oct-31-register attribution; SDP's official figure) |

This is exactly the population-cut distinction KX warned about in
`verify/METHODOLOGY.md` for NYC — where the NYC InfoHub file is **only**
the "Actual" cut and the accountability cohort had to be reverse-engineered.
**Philly gives us both.** Implication:

- **Canonical for SDP-district schools**: `Acct` (matches SDP's own published reports + SPREE composite).
- **(ii) base case** validates against the file we ingested ("Acct" specifically).
- **(iii) cross-cohort sanity check**: load both, compare per-school differences, surface where Acct vs Actual diverges meaningfully — gives us a NYC-style population-cut report **at ingest time**, not at validation time.

### F.7 Three distinct school-ID schemes need a crosswalk

The plan said composite PK = `AUN + Schl`. Reality:

| ID scheme | Used in | Sample |
|---|---|---|
| `id_eos` (SDP "End of School") | SDP PSSA + Keystone CSV (`id_eos` column) | `1130` (William T. Tilden MS) |
| `ULCS Code` (SDP Universal Location Code) | SDP attendance + suspensions + graduation CSVs | (need crosswalk) |
| `(AUN, Schl)` (PDE composite) | Future Ready + statewide PA files | `(126515001, 1010)` |

Plan implication: the `schools` table needs **three identifier columns** plus a derived `school_code` (the canonical join key for site URLs / queries). A separate `school_identifiers` mapping table or a one-time discovery loader builds the crosswalk from SDP's directory + PDE's directory. **This is a real infrastructure task that the plan didn't anticipate.**

Operationally: most SDP CSVs use `ULCS Code` (the `schoolid_ulcs` field in graduation), while their PSSA CSV uses `id_eos`. These two SDP codes may or may not be 1:1 — the SDP school directory will clarify (next fetch).

### F.8 Some SDP sources cover **only district schools**, not charters

The graduation CSV's `sector` column has two values: `District` and `Alternative`. **No charter rows.** Same pattern likely for attendance, suspensions, PSSA Zip (district + alternative only).

This is the inverse of Future Ready, which covers everyone but at a coarser grain. So the architecture is:

| Universe | Source for outcomes |
|---|---|
| SDP district + alternative schools | **SDP CSVs** (granular: grade × subgroup × full distribution) |
| SDP charters + Renaissance charters + cyber + CTC | **Future Ready** (school-wide subgroup, less granular) |

Loader does a **per-school-type source routing** rather than a single source per metric. Add this to the integration plan as a clarification to P1.

### F.9 Subgroup taxonomy is richer than the plan said

The graduation CSV's `subgroup` distinct values include **`Non-Binary`** as a gender — NYC doesn't carry it. Also `Multi Racial/Other`, `Native Hawaiian/Pacific Islander`, `Does Not Have IEP`, `Non-EL`, etc. Subgroup enum needs to be wider than NYC's. Edit B.4 again.

### F.10 SDP suspensions data is a **distribution**, not a rate

The OSS CSV doesn't publish a single "suspension rate" — it publishes the share of students with 0 / 1 / 2 / 3 / 4+ suspensions per year. That's richer than NYC's stub-only suspension data, and gives us a "high-tail suspensions" analytical dimension that NYC doesn't have. v1 should compute and store both:
- `oss_pct_zero` (% with 0 suspensions)
- `oss_pct_multiple` (% with 2 or more suspensions — the heavy-user share)
- `oss_pct_chronic` (% with 4+ suspensions)

These are derived metrics that ship cleanly via `metric_definitions` rather than as raw counts.

## What this changes downstream

| Plan doc | Section | Edit needed |
|---|---|---|
| `02_gap_analysis.md` | B.1 (PK) | Three ID schemes (id_eos / ULCS / AUN+Schl); add `school_identifiers` crosswalk table to schema |
| `02_gap_analysis.md` | B.4 (subgroup enum) | Add `NON_BINARY`, `NOT_IEP`, `NOT_ELL` |
| `03_integration_plan.md` | P1 | Add a per-school-type source routing step; both SDP "Actual" and "Acct" variants ingested; ID crosswalk loader prerequisite |
| `05_validation_spec.md` | New section | "Actual vs Acct" per-school diff report at ingest time as an additional check, distinct from (i)-(iv) |
| `sources.yaml` | sdp_pssa_keystone_2024-25 | Document both Schools_Actual and Schools_Acct files inside the zip |
| `00_overview.md` | §3 | Add O.8: "Actual vs Acct as canonical for SDP-district PSSA data?" — recommend Acct (matches SDP's public reports) |

## What's still TBD

- `sdp_charter_office_list` URL — needed to flag `is_renaissance`.

---

# Batch 3 findings (2026-06-03) — SDP Master School List + Enrollment/Demographics

Fetched + empirically tested the school-identifier crosswalk. **The "three
ID schemes" of F.7 collapse to two**, and the SDP Master School List
contains the full crosswalk in one file.

## ✓ Major plan simplification: the ID space is smaller than F.7 said

Verified relationships (333-school Master List + PSSA 2024-25 sample + Future Ready Philly subset):

| Relationship | Match rate | What this means |
|---|---|---|
| `ULCS Code` ≡ `SRC School ID × 10` | **239/239 (100%)** | SDP's two internal codes are the same identifier at different scales. Drop "three IDs"; carry one canonical SDP code. |
| `id_eos` (PSSA file) ≡ `ULCS Code` | **33/33 (100%)** | The SDP PSSA file's `id_eos` is just the ULCS code under a different column name. No separate ID family. |
| `(AUN, PA Code)` from PDE ≡ `(AUN Code, PA Code)` in Master List | **299/302 (99%)** | Future Ready's PDE-side composite key joins cleanly to the Master List on `AUN Code` + `PA Code`. The 3 unmatched are likely cyber-charters in PDE but not in SDP's directory. |

**Updated schema (replaces F.7):**

```
schools (primary key: school_code = "<ulcs_code>"):
  ulcs_code       (SDP canonical; ≡ id_eos in PSSA; SRC*10)
  src_school_id   (= ulcs_code / 10; carried for convenience)
  pa_code         (PDE Schl)
  aun_code        (PDE LEA)
  nces_code       (federal cross-state)
  ...
```

For SDP-only metrics, query by `ulcs_code`. For PDE-side joins, use
`(aun_code, pa_code)`. The Master School List is the single crosswalk
source — no separate `school_identifiers` table needed.

## ✓ Master School List is a rich directory beyond crosswalk

56 columns. Carries everything we need to seed the `schools` table:

| Field | Use |
|---|---|
| `Learning Network` | Geography taxonomy |
| `City Council District` | Geography taxonomy |
| `GPS Location` (lat,lng pair) | Geocoding |
| `Admission Type` | Admission category enum (Catchment / lottery / Special Admission / etc.) |
| `Governance` | District / Charter / Contracted |
| `Federal Accountability Designation` | ESSA status (cross-check with Future Ready's ESSA designation) |
| `School Reporting Category` | Carries Renaissance distinction directly (`SDP K12 Schools` vs `Renaissance Charter` etc.) |
| `Major Intervention`, `Major Intervention Year` | Direct analogue of NYC's "Renewal Schools" designation — at the school level, with year |
| `Community School Cohort` | Direct analogue of NYC's Community Schools designation — by cohort |
| `Title I Designation` | Federal Title I flag |
| `CTE Status` | Career & Technical Education flag |
| `School Leader Name` | Current principal (the principal-tenure data NYC couldn't get easily) |
| `Year Opened` | School age — useful for "post-2002 small schools" analogues |
| `Grade K..12` flags (0/1 per grade) | Grades served — derive grade band |

**F.11 — Renaissance Charter distinction is published right here.** The plan
said "carry `is_renaissance` as a derived boolean from the SDP charter office
list." Reality: `School Reporting Category` column already distinguishes
`Renaissance Charter` from `Charter` and from district categories. No
separate charter-office-list fetch required.

**F.12 — Major Intervention column = NYC Renewal analogue.** SDP records
schools that were placed on an Academic Improvement Plan or similar, with
the year. Bartram HS has `Major Intervention: Academic Improvement Plan`
since `2017-2018`. This is an ANALYTICAL DIMENSION the plan didn't
anticipate — it lets us tell the Philly version of the "schools the
district designated for turnaround" story directly from the directory.

**F.13 — Community School Cohort column = NYC Community Schools analogue.**
Same pattern. SDP tracks which schools have joined the Community Schools
program and in which cohort. Direct first-class data.

## ✓ Demographics file uses ULCS — clean join

The school-level enrollment+demographics CSV (35 cols, 2,629 rows for
2024-25) is **grade × school grain** (not just school). One row per
(`schoolyear`, `sector`, `ulcscode`, `gradelevel`). For school-level
demographic shares (the v1 use case), aggregate across grades on ULCS.

`subsector` column likely carries Renaissance distinction within
charters (TBD, verify on next batch).

## What this closes

Combined with the earlier finds, the v1 data layer is **discovered**:
- All v1 outcome metrics are in fetched files
- All universe-defining metadata (sector, type, admission, geography) is in the Master School List
- The two ID-space families (SDP's ULCS, PDE's AUN+PA Code) have a 99%+ join
- Renaissance Charter status is published, not derived
- ESSA designation is cross-published in two sources for validation

## What's still genuinely TBD

- **PDE PSSA + Keystone bulk files** — only needed if we want grade-by-grade
  breakdowns for *charter* schools. Future Ready gives charters school-wide
  only. Decision: defer to v2 unless a story specifically needs it.
- **The 3 Future Ready Philly schools missing from Master List** — cyber-charters
  with Philly mailing addresses but not in SDP's directory. Tag as
  "out-of-network" and proceed.
- **`Major Intervention` historical detail** — Master List has current designation.
  For the "Philly Renewal-style retrospective" story, we'd need historical
  Master Lists (which the longitudinal Zip provides).