# Phase 7 — Community Conditions public data integration

Checked November 9, 2026. This covers four workstreams: education performance, employment and economic stability, domestic violence and family safety, and violent crime and community safety. Only nonproduction tests were run. No migration was applied to any Netlify database, no production data were written, and no agency was contacted.

## Summary

- **Sources.** 19 official sources were retrieved (all HTTP 200) and SHA-256 hashed. 15 were extracted into records, 2 were used only as cross-checks, 1 is a held reference (the OSBI live portal), and 1 was inventoried but not retrieved: the oklaschools.com download tool requires a reCAPTCHA token for every file.
- **Records.** 62,176 aggregate records were built:
  - 54,837 VERIFIED PUBLIC DATA, of which 29,687 are cells the source suppressed or did not report, labelled as such with no value;
  - 7,339 QUALITY HOLD, kept in the database but with values removed by the public endpoint and CSV.
- **Checks.** 2,977 automated checks ran: 2,972 PASS and 5 FAIL. Every FAIL is a documented discrepancy with a disposition (below). There were no unexpected failures.
- **Completeness.** This is not a claim that all Oklahoma public data in these domains have been acquired. Items not connected are listed in section 6.

| Domain | Records | Verified (with value) | Suppressed / not reported by source | Held |
|---|---:|---:|---:|---:|
| Education | 41,592 | 5,034 | 29,234 | 7,324 |
| Employment | 8,753 | 8,436 | 317 | 0 |
| Domestic violence | 274 | 274 | 0 | 0 |
| Violent crime | 11,557 | 11,406 | 136 | 15 |

## 1. Official sources

The full manifest is in [source-inventory.json](source-inventory.json). It records the URL, linking page, method and request body, HTTP status, bytes, SHA-256, server Last-Modified, ETag, publication date, data query date, retrieval time and processing status.

| ID | Source | Period | Status |
|---|---|---|---|
| `osde-ostp-2526`, `-2425`, `-2324`, `-2223` | OSDE Oklahoma School Testing Program grades 3–8, media/redacted files ([State Testing Resources](https://oklahoma.gov/education/services/assessments/state-testing-resources.html)) | School years 2025–26 to 2022–23 | extracted (2023–24 held) |
| `osde-state-results-2526`, `-2425` | OSDE state results workbooks | 2025–26, 2024–25 | cross-check only |
| `osde-enrollment-oct2025` | OSDE [State Public Enrollment Totals](https://oklahoma.gov/education/services/student-information/state-public-enrollment-totals.html) workbook | October 1 counts | extracted |
| `oklaschools-data-download` | [Oklahoma School Report Cards data download](https://oklaschools.com/data-download/) | — | not retrieved (reCAPTCHA per download) |
| `oesc-labor-force-2025` | OESC *Oklahoma Labor Force Data 2025* (July 2026) | 2016–2025 annual averages | extracted |
| `oesc-laus-county-14mo` | OESC labor force by county, NSA, latest 14 months (September 30, 2026) | July 2025–August 2026 | extracted |
| `bls-laus-api-2016-2025`, `bls-laus-api-2026` | BLS LAUS public API v1 responses (statewide SA and NSA, participation, county cross-check series) | January 2016–September 2026 | extracted |
| `osbi-da-2022`, `-2023`, `-2024` | OSBI *Selected Findings: Domestic Abuse* (published April 15 and April 23, 2026) | 2022, 2023, 2024 (and the 2021 total) | extracted |
| `osbi-cio-2022`, `-2023`, `-2024` | OSBI *Crime in Oklahoma* (published April 15 and April 23, 2026) | 2022–2024 | extracted |
| `osbi-portal-2024-persons` | [OSBI Oklahoma Crime Statistics portal](https://okcrimestats.osbi.ok.gov/), 2024 Crimes Against Persons (labelled preliminary) | 2024 | held reference |

The BLS API responses are live and are hashed as retrieved; BLS revises LAUS data, so a later query can differ.

## 2. Reporting years

| Domain | Years |
|---|---|
| Education | OSTP 2022–23, 2023–24 (held), 2024–25, 2025–26. School-level rows are imported for 2025–26 only; state and district rows cover all four years. Enrollment: state 2003–04 to 2025–26, district 2022–23 to 2025–26. |
| Employment | Annual averages 2016–2025 (2025 is an 11-month average excluding October). Statewide monthly January 2016–September 2026, SA and NSA. County monthly July 2025–August 2026, NSA (August 2026 preliminary). October 2025 was not collected and is shown as DATA UNAVAILABLE. |
| Domestic violence | 2021 incident total (republished), 2022–2024 full breakdowns. |
| Crime | 2022–2024 incident-based (SIBRS/NIBRS) years only. Earlier Summary Reporting System years used the hierarchy rule and are not comparable. |

## 3. Geographies

| Level | Count | Notes |
|---|---:|---|
| State | 1 | |
| County | 77 | LAUS estimates by place of residence |
| MSA | 4 | Enid, Lawton, Oklahoma City, Tulsa (annual averages) |
| School district (OSDE) | 561 | County shown is the district office’s county as published; never used as a county outcome |
| School (OSDE) | 1,229 | 2025–26 test results only |
| Law-enforcement reporting agency (OSBI ORI) | 467 | Including statewide agencies such as the Highway Patrol and OSBI |
| OSBI county total of reporting agencies | 77 | Sum of agencies located in the county, with OSBI’s covered population; not a Census county estimate |

The nine priority counties (Tulsa, Rogers, Mayes, Craig, Ottawa, Delaware, Nowata, Wagoner, Creek) have records for every county-native series. All other counties are included wherever the source publishes them. OSDE district IDs are not linked to the Census district map: the map uses NCES/Census IDs and no verified crosswalk is connected, so name matching was not used.

## 4. Indicators connected

The full definitions, methods and limitations are in [indicators.json](indicators.json).

- **Education.** `EDU-ELA-PA`, `EDU-MATH-PA` and `EDU-SCI-PA` give the share of students with a valid score who were Proficient or Advanced, computed from published counts by grade. Science covers grades 5 and 8 only. `EDU-ENROLL` is the October 1 count. These are proficiency results, kept separate from accountability scores and letter grades (which are not connected).
- **Employment.** `LAUS-UR`, `LAUS-UNEMP`, `LAUS-EMP` and `LAUS-LF` cover the state, counties and MSAs. `LAUS-LFPR` is statewide SA only, because BLS does not publish county participation. Every record carries `SA`, `NSA` or not applicable.
- **Domestic violence.** Separate units are kept separate:
  - `DA-INCIDENTS` (incidents);
  - `DA-VICTIMS` (victims, by sex, age, race and ethnicity);
  - `DA-OFFENDERS` (offenders, including suspect and arrestee roles);
  - `DA-OFFENSES` (offenses by premise);
  - `DA-OFFENSE-TYPE` (crimes by offense type);
  - `DA-RELATIONSHIPS`, `DA-WEAPONS` and `DA-ARREST-TYPE`.
- **Crime.**
  - `CIO-PERSONS` counts victims. `CIO-PROPERTY` and `CIO-SOCIETY` count offenses. Robbery stays a property offense, as OSBI classifies it.
  - `CIO-TOTAL`, `CIO-POP` and `CIO-RATE` are the published rate per 1,000 covered population.
  - `CIO-VICTIMS` and `CIO-ARRESTEES` give 15 violent offenses by sex, age, race and ethnicity.

Headline examples:
- Grade 3 ELA, 2025–26 statewide: 25.7% (12,352 / 48,006).
- Statewide unemployment, September 2026 (preliminary): 4.2% SA and 3.9% NSA.
- Reported domestic abuse incidents, 2024: 29,149.
- Murder and non-negligent manslaughter victims, 2024: 241.

## 5. Verification

Results are in [verification-results.json](verification-results.json). The 2,977 checks break down as 2,484 arithmetic, 231 geography, 91 reconciliation, 54 cross-source, 43 source-token, 27 structure, 23 integrity, 19 inventory, 4 suppression and 1 privacy.

- **Employment.**
  - Employed + unemployed equals the labor force (±1) for every area-period, and each rate recomputes.
  - OESC state annual averages equal the mean of BLS NSA months for 2016–2024 (within 2 persons).
  - The OESC county workbook equals the BLS county series for all 72 non-preliminary values checked.
- **Education.**
  - Proficient + Advanced from counts agrees with the published integer percentages (within 1 point).
  - State rows equal the separate state results files: 14/14 subject-grades in each of 2025–26 and 2024–25.
  - District enrollment sums equal the published state total for each of 2022–23 to 2025–26.
  - OSDE computes the enrollment percent change relative to the current year, and that was confirmed.
- **Domestic abuse.**
  - Every value was located on its cited page.
  - Every breakdown sums to its published total.
  - Offenders equal suspects plus arrestees.
  - Arrest types equal arrestees plus the 5 arrestees with two types.
  - The incident totals for 2021–2023 agree across all three reports.
- **Crime.**
  - Offense groups sum to their category and categories sum to the total.
  - Table 1 equals Tables 2–4, and the 2022 and 2023 columns equal the earlier reports.
  - Victim tables equal Table 2 for each offense.
  - Every one of 467 agencies sums exactly to its county total.
  - Counties plus statewide agencies equal the published state total in every category and year.
  - The 2022 report (77 counties) and the 2023 report (72 counties located) agree with the 2024 report’s republished columns.

### Investigation of 2024 domestic abuse totals

Only one official 2024 incident total was found: 29,149 in *Selected Findings: Domestic Abuse, 2024*. The report’s other 2024 figures are different units, not conflicting totals:

- 33,581 unique victims;
- 33,068 unique offenses, which equals its premise total;
- 33,652 crimes against persons, with victims counted per offense;
- 31,294 offenders, which is 22,620 suspects + 8,674 arrestees;
- 8,679 arrest types, which is 8,674 arrestees + 5 with two types.

The OSBI portal publishes no domestic abuse category. The 2024 incident total is therefore shown as verified, and no hold was needed. Separately, the *Crime in Oklahoma, 2024* methodology page states that its data were queried “as of April 8, 2024”, the same date it gives for the 2023 figures. That metadata inconsistency is disclosed in the source notes; it does not change any value.

## 6. Quality holds, exclusions and gaps

Holds are listed in [quality-holds.json](quality-holds.json).

| Hold / exclusion | Records | Reason |
|---|---:|---|
| `hold-edu-ostp-2324` | 7,324 | 2023–24 state grade 3 ELA Proficient+Advanced is 51%, versus 29% (2022–23) and 27% (2024–25). Mean OPI is 299, versus 282 and 281. Every grade shows the same jump, and no cut-score change is documented on the source page. |
| `hold-crime-portal-2024` | 15 | The live, preliminary portal reports 70,984 crimes against persons for 2024, versus 70,254 in the published report (murder 238 vs 241). |
| Corrupted OSDE identifiers (excluded) | 46 / 45 / 47 rows (2025–26 / 2023–24 / 2022–23) | OSDE’s file prints some charter district and site codes in scientific notation (e.g. `5.50E+04`). The rows were excluded rather than reconstructed. |
| Suppressed cells | 29,234 education cells | OSDE printed `***`. These are labelled SUPPRESSED BY SOURCE, with the valid-N denominator kept where published. |

The following are not connected:

- **Report-card measures.** Graduation rates (including changes to the cohort calculation), chronic absenteeism, attendance, academic growth, dropout and report-card grades are blocked by the reCAPTCHA-gated downloader. A bulk file must be requested from OSDE.
- **Education by student group.** Results by student group (disparities) are not in the media files.
- **Older school rows.** School rows before 2025–26 are in the source but not imported, to keep the seed size manageable.
- **County domestic abuse.** The OSBI report shows counties only as a map image, with no table.
- **Domestic violence fatality review counts.** These were not retrieved. Only homicide-offense victims within reported incidents are shown.
- **Offense-level crime by county or agency.** These are not published in the reports.
- **Charges and convictions.** These are not published in these sources.
- **Rates per 100,000.** No matching population denominator is connected, so none is calculated. OSBI’s published rate per 1,000 covered population is shown instead.

## 7. Database and import

- **Schema.** The tables are defined in `db/schema.ts`, with migration `20261109123416_create_community_public_records`. It creates five new tables: `community_sources`, `community_geographies`, `community_indicators`, `community_quality_holds` and `community_public_records`. No existing table is altered.
  - Stable record ids are used.
  - A unique natural key covers source, indicator, geography, subgroup, period, seasonal adjustment and basis.
  - Foreign keys link each record to its source, indicator, geography and hold.
- **Check constraints** enforce:
  - held ⇔ hold id;
  - `record_origin = 'public-publication'`;
  - a value of `SA`, `NSA` or not applicable;
  - an allowed suppression status, with no value or numerator on a suppressed row;
  - non-negative values;
  - a well-formed SHA-256;
  - official source domains only.
- **Seeds.** Six seed migrations: `…123417` (metadata, triggers, employment), `…123418` (education 2025–26), `…123419` (domestic abuse and crime), and `…124848`–`…124850` (education 2024–25, 2023–24, 2022–23 plus enrollment).
  - All use `ON CONFLICT DO NOTHING`.
  - Triggers then block UPDATE and DELETE on records, sources, holds, indicators and geographies.
  - Re-running the seeds inserts nothing.
- **Pipeline.** `scripts/community-conditions/fetch_sources.py` downloads the sources. `build_catalog.py`, with curated domestic abuse values in `spec.py`, regenerates every artifact here and every seed. The build stops on any unexpected check failure. Run it with an isolated interpreter that has openpyxl and pdfplumber installed outside the repository.
- **Endpoint.** `/api/community-conditions` is GET/HEAD only and rejects unknown or repeated parameters. Education queries must name a geography or level.
  - It re-checks pinned source hashes and URLs and recomputes arithmetic.
  - It drops failing rows and strips the values of held rows.
  - It neutralises CSV formula injection.
  - There is no write path.
- **Artifacts.** The catalogs here (`catalog-*.csv`) cover employment, domestic violence, crime, and statewide education plus enrollment. The complete education table is available from the seed migrations and the endpoint’s CSV.

## 8. Tests

| Test | Result |
|---|---|
| `node --experimental-strip-types --test tests/*.test.mts` | **PASS** — 67/67 (12 new Community Conditions tests, 55 existing) |
| All 16 migrations applied in order to in-memory PGlite | **PASS** — 62,176 records, 19 sources, 2,416 geographies, 25 indicators, 2 holds. SoonerStart (1,469) and Stage B2 (2) records intact. UPDATE, DELETE, a non-publication origin, a verified row carrying a hold, and a suppressed row with a value were all rejected. Re-seeding inserted 0 rows. |
| `tests/community-conditions-browser.mjs` (1440 px and 390 px) | **PASS** — 24/24, see [browser-test-results.json](browser-test-results.json) |
| `tests/soonerstart-public-data-browser.mjs` (regression) | **PASS** — 22/22 |
| axe WCAG 2.x A/AA, Community Conditions section | **PASS** — 0 violations. This is an automated scan, not WCAG certification. |
| Other existing browser suites (`phase6-*`, `prevention-browser`, etc.) | **NOT VERIFIED** — they need `netlify dev` plus production snapshots. The `prevention-browser` expectations were updated for the reading-proficiency connection. |
| Deployed function against a Netlify database branch | **NOT VERIFIED** — `netlify dev` does not emulate the database here |
| Manual screen-reader review | **NOT VERIFIED** |

## 9. Deployment safety

Merging this change causes the platform to apply the new migrations, including roughly 26 MB of seed SQL, to the production database. That is a reviewer decision. Before merging, a Deploy Preview should confirm that its database branch provisions and that `/api/community-conditions?view=meta` returns `verified-publication`. Until then, the section shows DATA UNAVAILABLE and substitutes no values.
