# 03 — Integration Plan (evolve, not rebuild)

> Analysis & planning only — no prototype
> code is modified. Every change is **additive** and, where it touches existing
> logic, **behind a flag**, so the prototype keeps working at each commit.
> Effort = S (<1 day) / M (1–3 days) / L (>3 days). Risk = Low / Med / High.

Honors the locked decisions in the runbook: branch-per-capability + PR (no direct
`main` commits); residual-z runs **alongside** decile first, then the
replace-vs-supplement call is made from the flip diff; stack fit decided here;
status-vs-growth, 2022 treatment, and the reconcile reference source are
**configurable inputs to settle before the code phase**, not blockers for this review.

---

## A. Keep as-is (no change)

These match the spec's intent and are the foundation to build on (cf. spec §7.1,
`nyc-school-outcomes-spec.md:275-283`):

- **Peer-group infrastructure** — KNN(K=40) + hard filters + demographic/Jaccard distance (`scripts/loaders/nycenet-comparison-groups.ts`). Kept as a **complementary display + convergence cross-check** (spec §3.5, `nyc-school-outcomes-spec.md:150`), not as the primary flag.
- **Suppression + small-N visibility** (`_fetch.ts:111-115`; `lib/queries/schools.ts:207-214`).
- **School-universe tagging** — charters in; D75/D79/alt tagged out of defaults (`prisma/schema.prisma`, `SPECS.md:305`).
- **Survey rollups (dual NYCDOE + derived scores)**, directory/admission + topic parsing (`compute-survey-metrics.ts`, `derive-admission-topic.ts`).
- **Provenance rows** (`DataLoad` + `gitSha`, `_lib.ts:30-62`) — extend, don't replace.
- **The site itself** — Next.js IA (Ratings / Stories / Methodology / school profiles), persona flows, charts.

## B. Stack/architecture fit — the central decision

**The mismatch (from `01_inventory.md`):** the prototype's analysis is **TypeScript
writing computed columns into a live Postgres DB**, and the **site reads that DB
directly** via Prisma (`lib/queries/schools.ts`). Our scaffold is **Python**
(`numpy/pandas/scipy`) that emits **static JSON files** a site reads
(`pipeline/export/to_website_json.py:50-106`). The prototype has **no JSON-read
path** and **no Python runner/CI**.

Two integration shapes:

| | (a) Reimplement in TypeScript | (b) Python analysis sidecar (recommended) |
|---|---|---|
| What | Port `engine.py`/`trends.py` math to TS under `scripts/loaders/compute-residual-outliers.ts` etc.; write new columns to Postgres | Run the scaffold's Python engine as an offline step that reads the analysis inputs and writes **new additive columns/table** back to Postgres (and emits QA/provenance artifacts) |
| Toolchain | Single (TS) — matches house style, one runner, existing pre-push hook | **Second toolchain** (Python) — needs `requirements.txt` + a new test/CI lane |
| Reuses scaffold | No — reimplements & must re-validate WLS/SE/t-test by hand | **Yes** — `engine.py`, `trends.py`, `reconcile.py`, `references/*`, configs lifted ~verbatim; already has golden tests |
| Risk | Med-High: hand-porting statistics (WLS via `lstsq`, `√(s²+se²)`, t-test df) is error-prone; no numpy/scipy equivalents | Low-Med on correctness (tested code); Med on ops (cross-language wiring, two CI lanes) |
| Determinism/verify | Must build golden + reconcile from scratch in TS | Comes with the scaffold (`make golden`, `reconcile.py --strict`) |
| Spec alignment | OK | **Strong** — spec explicitly recommends "insert a Python analysis layer as the single source of truth that emits the site's data" (`nyc-school-outcomes-spec.md:294-298`) |

**Recommendation: (b), the Python analysis sidecar** — reusing the scaffold's tested
engine and verification spine rather than re-deriving statistics in TS. Rationale:

1. It reuses **validated** statistics + the verification spine instead of re-deriving them; avoids two copies of the math drifting.
2. It is **additive**: decile and residual-z render **side by side** during the transition — exactly what the "diff which schools flip" decision needs.
3. It matches the spec's recommended architecture and our scaffold, so the scaffold files below drop in.

Either way the **inputs** the Python step needs (a school-year analysis table with
covariates + denominators) come from one query against the existing DB →
`data/processed/analysis_table.parquet`, mirroring scaffold Stage 2
(`pipeline/assemble/build_analysis_table.py`).

### B.1 Write-back target — **hybrid (recommended)**

The output target is not either/or; use each store where it's strong:

- **JSON is the canonical analysis artifact.** The Python step emits the scaffold's deterministic, content-hashed, sorted-key JSON per outcome (`pipeline/export/to_website_json.py:14,44-73`). This is what golden tests diff and what `reconcile.py` treats as the published layer (`reconcile.py:35-44`) → reproducibility, versioned/git-reviewable artifacts (the **flip-diff shows up in the PR**), and DB-free CI.
- **Postgres is the serving copy.** A thin loader upserts that JSON into **additive nullable columns / a `school_trends` table** so the live site keeps reading Postgres unchanged (`lib/queries/schools.ts`), residual-z sits next to `comparison_group_percentile`, and the cross-cutting reads (school profile across 8 outcomes, researcher explore/export) keep their SQL joins.

This mirrors the reconcile model (`analysis_table` → `website_json` → serving) and adds just one JSON→DB load step that fits the existing loader pattern. **Pilot sequencing:** emit JSON first (zero site change; gets golden + reconcile + flip-diff in git), *then* add the JSON→DB upsert so the ratings UI can show both methods. If the hybrid is too much for the pilot, fall back to **Postgres-only write-back** (site untouched) and add JSON-as-artifact when the reproducibility gates (C4) land.

**Does JSON file size threaten in-memory reads? No, at the per-outcome grain.** The
ratings UI renders one outcome at a time, so the relevant unit is a single outcome's
`published_values.json`: ~800–1,000 band schools × a few comparable years ≈ 3k–8k
rows ≈ **~1–3 MB pretty-printed** (≈half minified), parsed server-side in tens of ms
and memoizable like today's outlier reads (`schools.ts:236-269`). In the hybrid the
request path reads Postgres anyway, so JSON size only touches the loader + CI, never
a user request. Two real caveats: **bulk researcher export** (all metrics × years ×
~15 subgroups — stays DB-backed, streams CSV/Parquet, never an in-memory JSON read)
and a future **all-subgroup outlier expansion** (pilot is `subgroup = ALL` only,
`SPECS.md:116`; publishing every subgroup multiplies a single file ~15× toward tens
of MB — revisit the read strategy then).

> If the team rejects a second toolchain outright, fall back to (a) and port only
> `engine.py`/`trends.py` to TS — but you still must rebuild golden + reconcile, so
> the cost lands anyway. Flagging this as the one decision that most changes the work.

## C. Change / add — sequenced, lowest-risk first

Each item is one branch + one PR (see `04_contribution_plan.md`), feature-flagged
where it touches existing reads.

### C0. Pipeline scaffold + housekeeping — **Effort S · Risk Low**
- Add a `pipeline/` Python package + `requirements.txt` (pin: `numpy/pandas/scipy/pyarrow/PyYAML/pytest` per scaffold) and a `Makefile`/npm script wrapper; add a `build_analysis_table` step that exports the school-year inputs (covariates, denominators, admission bucket) from Postgres to `data/processed/analysis_table.parquet`. Lift scaffold `pipeline/common.py`, `assemble/build_analysis_table.py` (adapt the source from fixtures → a DB query).
- Add `config/{outcomes,covariates,comparability}.yaml` (lift scaffold copies).
- Fix the stale `README.md:5` ("No code yet"); add `.gitignore` rules for `data/processed`, `__pycache__`.
- *Gate:* `make assemble` produces a parquet; no site impact.

### C1. Residual-z outlier engine **alongside** decile — **Effort M · Risk Med**
- Lift scaffold `pipeline/analysis/engine.py`, `outliers.py`, `run_outcome.py`. Run pilot outcome = **3rd-grade ELA+Math composite** (spec's recommended pilot, `nyc-school-outcomes-spec.md:308-309`).
- Write results **additively** to Postgres: new nullable columns on `school_year_metrics` (`residual`, `z`, `outlier_flag`, `fitted`, `se_i`, `persistent_dir`) — never touch `comparison_group_percentile`. New Prisma migration (additive only).
- Surface in `lib/queries/schools.ts` behind a flag: `getOutliers({ method: "residual" | "decile" })`, default `decile` until the flip diff is reviewed. UI gains an opt-in toggle on `app/ratings/outliers/page.tsx`.
- **Deliverable that drives the locked decision:** a **flip-diff report** — schools flagged by decile but not residual-z and vice-versa, with `N`, `z`, proclivity decile — so replace-vs-supplement is decided from data (spec convergence check §3.5, `nyc-school-outcomes-spec.md:150`).
- *Covariate set:* use the spec's broader set (race composition + enrollment + admission), subsuming proclivity's 3 features (`covariates.yaml`; gap analysis #3).
- *Gate:* decile path unchanged; residual path additive + behind flag; flip-diff reviewed before any default change.

### C2. Trends view (within-year z + comparability regimes) — **Effort M · Risk Med**
- Lift scaffold `pipeline/analysis/trends.py`, `trend_runner.py`, `config/comparability.yaml`.
- Compute per-school slope of within-year z **within a regime**; write to a new `school_trends` table (additive). Add a Trends view or augment `app/ratings/movers/` — keep the existing raw-delta movers as a labeled "raw change" view; add the regime-aware trend as the default "trend" view, with **regime-boundary markers** (spec §6.3, `nyc-school-outcomes-spec.md:253-256`).
- This directly closes gaps #4 + #5; encodes the comparability handling the **stories already do by hand** (`data/stories/answers.ts:1770`).
- *Gate:* movers query untouched; trend is a new query + new view.

### C3. Verification spine — **Effort M · Risk Low-Med**
- **Internal gates:** lift `pipeline/verify/checks.py` — coverage matrix, finite-z, plausible flag counts; emit `docs/qa_reports/<outcome>__checks.md`.
- **Correctness vs reality (the largest gap, #9):** lift `pipeline/verify/reconcile.py` + `references/{base,manual_csv}.py`. Start with the **`manual_csv` adapter**: a reviewer records official NYC School Quality Snapshot / NYSED per-school figures for a stratified sample (always including every flagged outlier) into `data/reference/manual/<outcome>.csv`; `reconcile.py` diffs published-vs-reference and writes a QA report; `--strict` can gate a release.
- Later: add `socrata` / `cached_file` adapters (`references/socrata.py`, `cached_file.py`) once the reference source is settled (deferred decision).
- **Status — started in PR B (`verify/`):** the correctness part of this gap is built — **(ii) base case** (DB vs the exact ingested file) + **(iii) spot check** (DB vs the NYC School Snapshot) — as a standalone read-only Python harness rather than the scaffold's `reconcile.py`/`manual_csv`. NYC Snapshot was chosen as the (iii) reference (settles part of decision E.4); a different-publisher NYSED check is deferred; internal `checks.py` gates and **(iv)** computed-metric checks are still pending. Authoritative as-built doc: `verify/METHODOLOGY.md`.
- *Gate:* read-only; produces reports, changes nothing the site reads.

### C4. Provenance manifests + golden/determinism tests — **Effort S-M · Risk Low**
- Extend ingest to record **source-file `sha256`/`bytes`/`retrieved_at`** (today only `git_sha` + URL exist — gap #8; `_fetch.ts:9-29`, `DataLoad`); write a per-file `provenance.json`.
- Add golden tests on the pilot's analysis outputs (lift scaffold `tests/test_golden.py` + `make golden` pattern) so a re-run from fixed inputs reproduces identical results. Run under the new Python test lane; optionally also assert the TS read layer is stable via vitest.
- *Gate:* test-only + manifest writes.

### C5. Composites on standardized residuals — **Effort S · Risk Low (do last)**
- Per spec §7.2 item 5 (`nyc-school-outcomes-spec.md:291`), composites should combine standardized residuals, not raw rates (`compute-composites.ts:136` currently means raw). Revisit only after C1 lands and the residual columns exist.

## D. Concrete scaffold files to lift

| Scaffold file | Lands as | Used by |
|---|---|---|
| `pipeline/analysis/engine.py` | core WLS + standardize + flag | C1 |
| `pipeline/analysis/outliers.py`, `run_outcome.py` | View 1 orchestration / one-outcome driver | C1 |
| `pipeline/analysis/trends.py`, `trend_runner.py` | View 2 (z-slope within regime) | C2 |
| `config/outcomes.yaml`, `covariates.yaml`, `comparability.yaml` | per-outcome + covariate + regime config | C1, C2 |
| `pipeline/verify/checks.py` | internal stage gates | C3 |
| `pipeline/verify/reconcile.py` + `references/{base,manual_csv,socrata,cached_file}.py` | correctness vs official data | C3 |
| `pipeline/assemble/build_analysis_table.py`, `common.py` | inputs assembly (adapt source: fixtures → DB query) | C0 |
| `pipeline/export/to_website_json.py` | canonical JSON artifact (hybrid, §B.1); a thin JSON→DB loader upserts it for serving | C1, C2 |
| `tests/test_golden.py` + `Makefile` golden target | determinism tests | C4 |

## E. Open decisions this implicates (surface, don't resolve now)

Per the runbook these are **configurable inputs to settle before the code phase**,
not blockers for this review (map to spec §9, `nyc-school-outcomes-spec.md:327-341`):

1. **Status vs. growth controls** — status-only for v1 (shared across outcomes) vs. add a prior-achievement covariate where available (changes interpretation). Spec §9.3. Lives in `outcomes.yaml: prior_achievement`.
2. **2022 treatment** — old-standards recovery year: include, exclude, or report standardized-only. Spec §9.5; `comparability.yaml: ambiguous_years: [2022]`.
3. **Survey comparability regimes** — must verify NYC School Survey item wording/scale continuity before any cross-year survey trend (gap #7); `survey_regimes` is a deliberate placeholder.
4. **Reconcile reference source** — which official per-school source is authoritative (School Quality Snapshot vs. NYSED report card vs. Socrata); start with `manual_csv`, settle the automatable adapter later. Spec §7.2 item 4. (PR B settled the (iii) spot-check reference = NYC School Snapshot; a different-publisher NYSED check remains deferred.)
5. **Outlier statistic default** — decile vs. residual-z as the *shipped* default — decided from C1's flip-diff, per the locked decision.
6. **Admission handling** — covariate (default) vs. stratify for large screened/specialized HS sets. Spec §9.2; `outcomes.yaml: admission_handling`.

## F. Suggested sequencing

`C0 → C1 (+ flip-diff review → decide default) → C2 → C3 → C4 → C5`, piloting on
3rd-grade ELA+Math end-to-end before templatizing to the other 7 outcomes (spec
build sequence §8, `nyc-school-outcomes-spec.md:303-324`). Each step is independently shippable and
leaves the prototype working.