# verify/ — read-only data validation harness

Confirms the data the site serves is correct, across four checks (definitions +
findings in `verify/METHODOLOGY.md`; design rationale in
`docs/design/05_validation_spec.md`; closes gap #9 in `docs/design/02_gap_analysis.md`):

- **(i) Completeness** — are years/schools missing? *(surfaced as "source-only" / coverage gaps)*
- **(ii) Correctness — base case** — DB vs an independent re-read of the **exact source file** the loader ingested. *(the binding check)*
- **(iii) Correctness — spot check** — DB vs NYC's **public-facing per-school report** (the School Snapshot / SQR). *(presentation cross-reference; the Snapshot is NYC's own report — same data family — so this is not an independent oracle. A truly independent publisher (NYSED) is identified but not yet wired.)*
- **(iv) Correctness — computed values** — derived metrics (composites, survey rollups). *(not done yet)*

**This is a sidecar.** Python (the app is TS); **read-only** — it never writes to the
app DB or source data, only to `data/reference/` (downloaded snapshots + provenance)
and `docs/qa_reports/` (its reports). It imports **no** `scripts/loaders/` code —
verification is independent of how the prototype computes.

## Modules
- `download.py` — fetch a source in full + write `provenance.json` (url, retrieved_at, sha256, bytes); handles xlsx/csv files and the Socrata API.
- `connectors/served_db.py` — read-only Postgres read of what the site serves.
- `connectors/source_file.py`, `source_socrata.py` — independent re-read of the ingested source (file / NYC Open Data).
- `connectors/served_survey.py`, `source_survey.py` — survey grain `(dbn, year, question, respondent)`.
- `connectors/reference_snapshot.py` — NYC School Snapshot per-school JSON API (the spot-check reference).
- `reconcile.py` — **(ii) base case**: served vs source file → `docs/qa_reports/*__validation.{md,json}`.
- `reconcile_snapshot.py` — **(iii) spot check**: served vs Snapshot (sampled) → `*__snapshot.{md,json}`.
- `reconcile_survey.py` — survey **(ii) base case**.
- Config: `sources.yaml` (ingested sources), `components.yaml` (per-metric base-case config), `snapshot.yaml` (metric → Snapshot varname for the spot check).

## Setup
```bash
python3 -m venv verify/.venv && source verify/.venv/bin/activate
pip install -r verify/requirements.txt
```
The served + spot-check runs read the DB. Set a **read-only** connection string (never
commit it); the harness also auto-loads it from `.env.local`:
```bash
export VALIDATE_READONLY_DATABASE_URL="postgresql://<readonly_role>:...@host:5432/postgres?sslmode=require"
```
Provision a dedicated read-only role (no INSERT/UPDATE/DELETE grants), separate from the
loaders' superuser `DIRECT_URL`; the connector issues SELECT only and forces a read-only session.

## Run (from repo root)
```bash
# (ii) base case — fetch the source, then reconcile DB vs the exact file
python -m verify.download --source nycdoe_infohub_ela
python -m verify.reconcile --metric ela_grade3_proficiency        # -> docs/qa_reports/..._validation.{md,json}

# (iii) spot check — DB vs the NYC School Snapshot (sampled, ~300/metric)
python -m verify.reconcile_snapshot --metric ela_all_proficiency  # -> ..._snapshot.{md,json}

# survey base case
python -m verify.download --source nyc_survey_student_2023-24
python -m verify.reconcile_survey --source nyc_survey_student_2023-24
```

## Status
- ✅ **(ii) base case** — done for test proficiency (g3/g7 + school-wide), chronic absenteeism, graduation, survey responses: **0 mismatches** across ~29k metric cells + ~826k survey cells (`docs/qa_reports/`).
- ✅ **(iii) spot check** — school-wide ELA/Math + chronic absenteeism vs the NYC Snapshot (sampled). Differences are documented population/cohort effects, not errors (see `METHODOLOGY.md`). Graduation (HS) not yet wired.
- ⬜ **(i) completeness** — coverage gaps reported (source-only rows; source-missing years); see METHODOLOGY "Coverage & data gaps."
- ⬜ **(iv) computed values** — composite / survey-rollup formula validation (not started).
- ⬜ **Independent (different-publisher) reference** — NYSED ESSA Report Card (per-school chronic absenteeism, 2023-24) is identified but not wired; the snapshot spot-check is same-publisher.