# 07 — Runbook: build, load, validate, publish

Everything needed to reproduce the Miami-Dade closure track from a clean
checkout, and to load it into the production database. Same conventions as
the Philly build; the Miami-specific parts are the origin-host rewrite, the
PDF parsers, and the hand-curated events table.

## 0. Prerequisites

- Node 22 + `npm ci`; Python 3.11 + `make miami-setup` (creates `pipeline_miami/.venv` with pandas, openpyxl, xlrd, pdfplumber, scipy, PyYAML, requests, psycopg).
- `unzip` on the PATH (the CCD loaders stream 200 MB zips through it).
- `.env.local` with `DIRECT_URL` / `DATABASE_URL` (a writable connection for loaders) and `VALIDATE_READONLY_DATABASE_URL` (a **read-only** role for the harness; the harness also sets the session read-only).
- Outbound HTTPS to `origin.fldoe.org`, `eds.fldoe.org`, `api.dadeschools.net`, `pdfs.dadeschools.net`, `nces.ed.gov`, `services.arcgis.com`. `www.fldoe.org` refuses cloud egress (Akamai 403); `discover.py` rewrites it.

## 1. Schema

```bash
npx prisma generate && npx prisma db push      # additive: miami_* tables + MetricUnit.USD
psql "$DIRECT_URL" -f scripts/sql/enable-rls.sql   # RLS + public-read policies on the miami_* tables and least-privilege REST grants (re-runnable; run after every db push)
```

## 2. Fetch (provenance + findings)

```bash
make miami-discover            # list registry state
make miami-discover-all        # fetch every resolved source_id in docs/cities/miami/sources.yaml
```

Writes `data/cities/miami/sources/<source_id>/<date>/{file,provenance.json}` (files git-ignored, provenance committed) and `pipeline_miami/findings/<source_id>.md`.

The per-grade assessment files (State Report of Schools, one per subject × grade × administration) are too many to register by hand:

```bash
make miami-discover-srs        # scrape the FLDOE results pages for spring 2023–2025, append the fldoe_srs_* entries to sources.yaml, fetch them
```

The climate-survey workbooks are listed on the Research Services page, which is a JavaScript shell; the file list comes from its CMS API (`https://api.dadeschools.net/api/wms/getPageData/3603`). All 20 are registered in `sources.yaml` (`mdcps_climate_survey_{students|parents|staff}_{year}`) and fetch with `--source`.

## 3. Parse the PDF tables (two-parser gate)

```bash
for y in 2022-23 2023-24 2024-25; do pipeline_miami/.venv/bin/python -m pipeline_miami.parse.fish_los fldoe_fish_los_dade_$y; done
pipeline_miami/.venv/bin/python -m pipeline_miami.parse.abc_book mdcps_abc_book_2026-27
```

Outputs `data/cities/miami/reference/parsed/<source_id>.csv` + `.gate.json` (committed). A loader refuses a table whose gate did not pass.

The climate-survey workbooks (17 long-format sheets with different column names, 3 pivot-cache-only) are normalised once:

```bash
make miami-parse-survey        # = python -m pipeline_miami.parse.climate_survey
```

Writes `data/cities/miami/reference/parsed/survey/<source_id>.csv` (+ `.school.csv`) and the item crosswalk `data/cities/miami/reference/survey_items.csv` (committed). Review the crosswalk when a new year is added: items are matched on stemmed wording (lead-ins such as "I feel" dropped), one item per year per canonical wording, with `data/cities/miami/reference/survey_item_aliases.csv` forcing known rewordings together; the parser prints any disjoint-year pairs it did not merge so the alias table can be extended. Domain, `reverse` (negatively worded) and `core` (asked every year) are canonical-level flags. Percent agreeing is re-expressed over agree + don't-know + disagree where the three sum further from 100 than the file's own rounding allows (0.5 pp per share column published as whole percents, plus 0.05 pp for decimal columns; the parser prints the tolerance it derived per file); the Q00/Q99 summary lines are dropped before anything is aggregated; the respondent count comes from the item rows, else the Q00 line (2018-19); an item row with its own count under 10 is suppressed whatever the school's median. A file whose values sit on the wrong row gets a `label_offset` in `SPECS` (the 2021-22 staff pivot cache stores item Q-(n+1)'s shares on the row labelled Q-n; the first item's values are then not in the file and the item is listed in `survey_item_exclusions.csv` so it still counts as asked). A file that cannot be made comparable is retired instead: `RETIRED_SPECS` in the parser names it with the measurement (the 2018-19 staff workbook: no don't-know column, agreement + disagreement summing to a median of 83%, so the staff series starts in 2019-20), the parser removes its parsed tables, and `RETIRED_SOURCES` in `climate_survey.ts` deletes any cells still served from it on the next load (the load history and the fetched file stay; `sources.yaml` marks the entry `role: retired`). Adding a spec key is the place to record any such publisher quirk; the harness re-reads the 2021-22 pivot caches with its own reader (`_survey_pivot_cache`) and its own copy of the offset, so the base case for those years verifies the implementation, not the diagnosis (that rests on the adjacent-year comparison in `10_review_2026-09-16b.md`).

Survey scoring uses favorable responses: agreement for positive items and disagreement for negative items. Unknown answers stay in the denominator and receive no favorable credit in either direction. A published three-share distribution totalling zero is missing, not zero agreement. The reviewed `staff_overload` alias joins the workload item across all seven years and retains the older publisher's Teaching and Learning category. Rebuild all survey tables and the crosswalk together before loading; the loader rejects a crosswalk without this complete core item.

After parsing, `python -m pipeline_miami.verify.survey_source_check` compares the four composites with the harness's independent workbook reader for its ten supported source files and checks their provenance hashes. This is a source-artifact check, not a served-database verdict. After reloading, rerun percentiles, `pipeline_miami.analysis.survey_enrollment` and the full validation harness. The loader records `favorable-response-v2` in its load notes; the survey analysis refuses old scoring loads, and the climate analysis page withholds earlier results until its packet carries that version. The two regression windows reuse the same endpoint and have different durations; year-to-year predictor correlations describe stability and do not identify measurement noise.

## 4. Load (order matters)

```bash
npx tsx scripts/loaders/miami/metric_definitions.ts
npx tsx scripts/loaders/miami/directory.ts          # MSID + CCD + GIS + School Grades → miami_schools
npx tsx scripts/loaders/miami/membership.ts         # every fetched fldoe_membership_by_school_grade_* year
npx tsx scripts/loaders/miami/ccd_membership.ts     # 2018-19, 2022-23, 2023-24 (cross-publisher reference only)
npx tsx scripts/loaders/miami/race_membership.ts    # pct_black … pct_two_or_more from the state race file, every fetched year (allocation rule: findings F.15)
npx tsx scripts/loaders/miami/ccd_staff.ts
npx tsx scripts/loaders/miami/gis_schoolsite.ts
npx tsx scripts/loaders/miami/fish_los.ts
npx tsx scripts/loaders/miami/abc_book.ts
npx tsx scripts/loaders/miami/essa_ppe.ts
npx tsx scripts/loaders/miami/school_events.ts      # data/cities/miami/reference/school_events.csv
npx tsx scripts/loaders/miami/derived.ts            # utilization, 5-yr change, K÷G5, students per teacher

# Outcomes (P1)
npx tsx scripts/loaders/miami/school_grades.ts      # every fetched fldoe_school_grades_* year (all sheets; 2022-23 has one per school type)
npx tsx scripts/loaders/miami/absenteeism.ts        # absent 21+ and absent ≥10% files
npx tsx scripts/loaders/miami/ada_adm.ts
npx tsx scripts/loaders/miami/graduation.ts         # total + race/category subgroups; virtual-provider rows skipped
npx tsx scripts/loaders/miami/ell.ts                # every fldoe_ell_membership_* year
npx tsx scripts/loaders/miami/srs_assessments.ts    # every fldoe_srs_* (and the three first-fetch ids) present under data/cities/miami/sources
npx tsx scripts/loaders/miami/sesir.ts              # excludes the file's SCHOOL TOTAL row from the sum and cross-checks against it
npx tsx scripts/loaders/miami/climate_survey.ts     # from the parsed survey tables + crosswalk (P2); students' response rate against grades 4-12 membership

# Peer groups and percentiles (P3) — after race_membership, ell, school_grades and membership
npx tsx scripts/loaders/miami/derive_peer_groups.ts # k=20/30/40; k=40 linked; k sensitivity not evaluated
npx tsx scripts/loaders/miami/compute_percentiles.ts

# Residual-z (additive columns) — after everything above
make miami-analyze                                  # = residual_z.py → residual_z.ts → survey_enrollment.py
pipeline_miami/.venv/bin/python -m pipeline_miami.export_metric_index   # metric index the methodology pages read (commit it)
```

Re-run `compute_percentiles.ts` and `make miami-analyze` after any outcome loader adds cells; percentiles and residuals are stored on the cell and are not recomputed on read.

Source loaders stage facts and school-record updates in memory. `finishDataLoad` publishes the load record, facts, metadata changes and pruning of prior source-owned facts in one database transaction. An invalid metric, missing school, failed write or unexpectedly empty replacement rolls the whole transaction back. A per-source advisory lock and generation check reject a candidate superseded by another completed load. No partially completed candidate is exposed. Derived peer/percentile scripts still run afterward, as separate steps; a complete refresh is not one global transaction. The load records include source SHA256, script path and git SHA. `school_grades.ts` and `fish_los.ts` refresh the school record (current grade, permanent capacity) only when the latest available year is in the run, so re-running an older year alone leaves it untouched. `race_membership.ts` reads every fetched year in one pass because 2022-23's allocation rule uses the adjacent years as a prior; `directory.ts` no longer touches `fish_capacity_permanent` on an existing record (that column belongs to `fish_los.ts`).

The events table accepts an optional `source_school_name`. The loader checks it against the directory before replacing events atomically. Carol City Middle (6051) has a checked source name; blank names on other rows mean that this independent identity check has not yet been recorded.

## 5. Validate

```bash
make miami-verify     # = python -m pipeline_miami.verify.run_all
```

Writes `docs/qa_reports/miami/*` including `__enrollment_full_qa.json`, whose `verdict.overall` gates the analysis page (PASS → indexable and green strip; otherwise noindex and a warning), and `__outcomes_validation.{json,md}` for the outcomes track: base-case components, the EDFacts absenteeism and CCD race cross-publisher checks, the residual-z checks (`__residual_checks.md`: independent N and eligibility, independent refit, served = committed, sanity gates) and the served-value invariants (`__served_invariants.md`: ranges by unit, identities between cells, ownership by the latest load, and year-over-year shift alarms; an alarm counts as explained only when `pipeline_miami/verify/served_invariants_allowlist.yaml` records its cause). The residual-z and climate-survey analysis pages take their status strip from these. Commit the reports.

## 6. Packet and site

```bash
npx tsx scripts/analysis/miami-enrollment-descriptives.ts   # → data/analysis/miami-enrollment.json (commit)
npx vitest run tests/unit/ && npx tsc --noEmit && npx next build
```

The page cites the packet and the QA report by content hash (`lib/miami/evidence-version.ts`); `/source-docs` refuses a stale hash.

Ratings pages (`/miami/ratings`, `/miami/ratings/outliers`, `/miami/ratings/movers`) read percentiles and residual-z columns straight from the database, so they need step 4's peer-group, percentile and residual scripts to have run; they render empty tables otherwise. The per-metric methodology pages, the residual-z and climate-survey analysis pages and the story pages are static and read only committed files (`data/analysis/*.json`, `data/cities/miami/reference/metric_index.json`, `docs/qa_reports/miami/*.json`).

## 7. Refreshing next year

1. Add the new year's entries to `sources.yaml` (membership, FISH LOS, ESSA PPE, ABC book, Statistical Highlights) — the URL patterns are in the existing entries.
2. `make miami-discover-all` → parse → load the new sources → `derived.ts` → `make miami-verify` → packet → commit.
3. Update `data/cities/miami/reference/school_events.csv` from the new board book (every row cites its PDF and page) and re-run `school_events.ts`.
4. Outcomes: add the new School Grades, graduation, absenteeism, ADA/ADM, ELL, race-membership and SESIR entries; `make miami-discover-srs` picks up the new spring assessment files by itself. Then the P1 loaders → `derive_peer_groups.ts` → `compute_percentiles.ts` → `make miami-analyze` → `make miami-verify` (add the new years to `pipeline_miami/verify/components.yaml`).
5. Survey: register the three new workbooks (the CMS API above lists them), add a `SPECS` entry per file in `pipeline_miami/parse/climate_survey.py` (sheet and column names change every year; say whether the shares are published or must come from counts), `make miami-parse-survey`, review `survey_items.csv` and the parser's list of unmerged rewordings (extend `survey_item_aliases.csv`), add the new files to the harness's own column map (`_SURVEY_COLUMNS` in `pipeline_miami/verify/connectors/source_file.py`) if they are to be base-case components, then `climate_survey.ts` → `compute_percentiles.ts` → `make miami-analyze`.
6. After any reload, read `__served_invariants.md`: a new shift alarm is either a real break to record in the allowlist with its cause (keyed by metric, year and kind — `median` or `count`), or a loader bug. The rule compares each year's served median and school count with the previous served year's, against three times the MAD of the series' own year-to-year moves floored at 0.25 pp (percents) or 2.5% of level (counts: 1% or 2 schools); a one-year blip alarms twice (the step and the return). Entries that match no current alarm are listed as stale at the end of the report and should be removed.

## 8. Production notes

- Loaders write through `DIRECT_URL` (bypasses RLS, as for NYC and Philly). The six `miami_*` tables are in `scripts/sql/enable-rls.sql` (RLS enabled, `public_read` SELECT policy, and the script's grant loop narrows the REST roles to SELECT); run the script after every `prisma db push`, before the tables are exposed through PostgREST.
- Until the schema push and the loaders have run against a database, the database-backed Miami pages (home, school pages, outliers, movers) render a "not loaded" notice instead of failing, the search endpoint returns no Miami hits while still serving NYC and Philadelphia, and the static pages (methodology, analyses, story ideas, source docs) work from committed files (`miamiOr` in `lib/queries/miami-schools.ts`; only a missing-table error is caught, any other database error still surfaces).
- The harness must use a read-only role in production (`VALIDATE_READONLY_DATABASE_URL`).
- `next.config.ts` traces `pipeline_miami/**`, `scripts/loaders/miami/*.ts` and `data/cities/miami/reference/**` into the serverless bundle for `/source-docs`.