# Aggregate import schema and review gates

This is a future import specification, not an upload endpoint. The CSV contains column names only and no example outcomes. One row describes an agency-approved aggregate for one measure, program, reporting window, native geography and permitted comparison group. The nine-measure catalog describes missing evidence; it stores no invented outcome observations.

The strict JSON schema and `validateAggregateImport` validator define the review boundary. CSV adaptation must convert empty optional cells to null and numeric cells to numbers before validation; it must preserve FIPS as strings and reject duplicate headers. All columns are required as keys; unknown columns are rejected. A zero is a reported count, never a missing or suppressed value.

| Column | Definition |
|---|---|
| `measure_id` | One of the nine accountability measures; does not itself imply data availability. |
| `program` | Report Part B and Part C separately. |
| `indicator_id` | Official indicator ID if applicable; null for a non-SPP agency aggregate. |
| `reporting_year` | Source-native reporting label; never infer inclusive dates from FFY alone. |
| `period_start` | Inclusive aggregate reporting boundary; not an individual child event date. |
| `period_end` | Inclusive aggregate reporting boundary; not an individual child event date. |
| `followup_cutoff` | Aggregate outcome follow-up cutoff, required for linked completion cohorts; no child dates. |
| `geography_level` | Retain source-native unit; no district or state allocation to county. |
| `geography_code` | Native geography identifier; state is 40, county is five-character Oklahoma FIPS. |
| `county_fips` | County of residence code only for a county aggregate; null for all other units. |
| `geography_vintage` | Boundary/roster vintage and approved crosswalk reference. |
| `residence_basis` | County imports require documented county of residence. Office/provider location is not residence. |
| `population_definition` | Aggregate inclusion, exclusion and pending-case population definition only. |
| `age_range` | Source-defined age range; unknown must be explicit, never inferred. |
| `cohort_unit` | Distinguish children, referrals, plans and incidents; no deduplication inferred. |
| `cohort_count` | Count of the defined cohort, not a general population denominator. |
| `raw_numerator` | Observed timely/completed count before compliance adjustments. |
| `raw_denominator` | Documented raw cohort denominator before adjustments. |
| `adjusted_numerator` | Numerator after explicitly documented compliance adjustment. |
| `adjusted_denominator` | Denominator after explicitly documented exclusions. |
| `exceptions_count` | Documented aggregate exception count; null if not released. |
| `exclusions_count` | Documented aggregate exclusion count; null if not released. |
| `raw_rate` | Raw numerator / raw denominator × 100, or null; never substitute adjusted compliance. |
| `adjusted_rate` | Adjusted numerator / adjusted denominator × 100, or null; not referral completion. |
| `methodology` | Program-level formula, exceptions, exclusions, pending outcomes, overlap and adjustment rules. |
| `clock_definition` | Program-level start/stop milestones, calendar/school/working days and actual delivery definition. |
| `rural_classification` | Explicit RUCC, RUCA, Census or other agency-approved classification; null when not comparing. |
| `classification_vintage` | Classification vintage and crosswalk reference; required with classification. |
| `comparison_group` | Agency-approved group label and aggregation basis, not individual residence data. |
| `source_url` | Official HTTPS government publication URL. |
| `publication_reference` | Title/version and page, table or field reference. |
| `source_sha256` | SHA-256 of the exact reviewed source release. |
| `retrieved_at` | UTC source retrieval timestamp. |
| `verified_at` | Always null on import. Only a separate authorized verification process can assign verification. |
| `quality_status` | Import cannot assert verified status or publish records. |
| `review_notes` | Program-level discrepancy/review notes only; no child information. |
| `suppression_status` | Primary/complementary suppression hides ALL counts and rates in this row. |
| `privacy_rules` | Agency primary/complementary suppression, rounding, minimum denominator and intersection/export rules. Do not assume a threshold. |
| `release_reference` | Agency authorization or existing public release authority reference; not a credential. |

## Privacy and review process

Do not provide child names, IDs, dates of birth, addresses, individual event dates, contact information, exact residential coordinates or case narratives. Date fields apply only to aggregate reporting and follow-up boundaries. Metadata text is program-level only; the validator rejects extra columns but cannot prove that allowed text is free of personal information. Agency-side disclosure review and human inspection remain required before storage. No raw files or child-level data should be uploaded.

The Netlify Database table `idea_aggregate_reviews` is separate from the existing verified-publication table. It has no public route, no automatic import job and no promotion path in this release. No import rows were seeded. Imports can be unreviewed or held only, with `verified_at` null. No public endpoint reads this table. The existing pinned publication records cannot be replaced by an import.

1. Obtain an agency-approved release and definition sheet; confirm actual versus planned delivery and cohort completeness.
2. Validate required columns, programs, dates, source-native identifiers, counts, raw/adjusted formulas and suppression. Reject rows with unsupported keys or self-asserted verification.
3. Inspect all program-level metadata for personal information and verify primary/complementary disclosure rules across totals, adjacent geographies, periods, groups and exports. Row validation alone cannot prevent reconstruction across a release.
4. Independently verify official provenance, source hash, arithmetic, definitions and discrepancies. Record reviewer authorization and a separate verification timestamp through a future approved review process.
5. Review comparability across program, age, period, follow-up, residence, clock, cohort unit and classification vintage before a rural/urban comparison. A syntactically valid row is not proof of comparability or release approval.
6. Design and approve an authenticated import/review workflow before accepting any real agency records. Only a separately reviewed publication adapter may expose a new verified release.

No fixed small-cell threshold is invented. Any suppressed/not-released row must omit all numeric counts and rates, including exception/exclusion counts. The whole release still requires complementary-suppression review. No computation recovers a hidden cell from totals. County records require an actual Oklahoma county FIPS, documented county-of-residence basis and county-native source; shared-district aggregates cannot be converted. State records use code 40 with no county FIPS. Agency and district codes remain native; no NCES or county crosswalk is assumed.

Linked referral-to-service completion requires an aggregate follow-up cutoff and actual delivered-service definition. Rural comparison requires a classification, vintage and group plus a comparable cohort review; RUCC metropolitan/nonmetropolitan is not synonymous with individual rural/urban residence. Raw and adjusted counts/rates remain separate. Exception and exclusion overlap must be documented rather than inferred.
