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

docs/cities/miami/07_runbook.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.

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

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)

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:

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)

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:

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)

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

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

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.