Oneroster Csv Validator

---
name: oneroster-csv-validator
description: "Validates 1EdTech / IMS Global OneRoster v1.1 and v1.2 CSV roster sets: manifest integrity, bulk vs delta strictness, foreign key references, and encoding sanitization. Trigger phrases: oneroster csv validator, validate oneroster zip, clever roster error, oneroster sourcedid."
category: testing
risk: safe
source: community
source_repo: wwewtech/oneroster-csv-validator
source_type: community
date_added: "2026-09-22"
author: wwewtech
tags: [edtech, oneroster, csv-validation, ims-global, 1edtech, data-engineering]
tools: [claude, cursor, gemini, windsurf]
license: "MIT"
---

# OneRoster CSV Validator: Strict 1EdTech Data Integrity & Pre-Flight Linter

Perform rigorous pre-flight validation on OneRoster v1.1 and v1.2 CSV roster bundles prior to SIS/Clever/ClassLink ingestion: audit foreign key referential integrity, eliminate orphan enrollments, enforce bulk vs delta partitioning, and sanitize encoding traps.

## When to Use This Skill

Activate this skill when:
- Validating or debugging student information system (SIS) roster export packages (`.zip` archives or CSV directories) for OneRoster v1.1 or v1.2 compliance.
- The user asks: "Why is Clever rejecting our OneRoster zip file?", "Check for orphaned userSourcedIds in enrollments.csv", "Validate bulk vs delta mode in OneRoster CSVs", or "Sanitize UTF-8 BOM characters from school rosters".
- Auditing relational integrity across `manifest.csv`, `orgs.csv`, `users.csv`, `courses.csv`, `classes.csv`, `enrollments.csv`, and `academicSessions.csv`.
- Preparing automated pre-flight CI/CD pipelines to catch corrupted student or teacher records before nightly SIS sync runs.

Do NOT use this skill when:
- Integrating OneRoster REST / OAuth 2.0 API endpoints (this skill focuses strictly on CSV table bindings).
- Generating synthetic or fabricated student PII without authorization.
- Working on generic non-standard CSV spreadsheets that do not follow 1EdTech specifications.

## Core Mental Models & Non-Negotiable Rules

1. **The Manifest Root Authority Law**:
   - `manifest.csv` MUST reside strictly at the root level of the ZIP archive (never nested inside a subfolder).
   - It MUST declare the exact specification version: `oneroster.version,1.1` or `oneroster.version,1.2`.
   - It MUST list every single file present in the exchange. If a CSV file is present in the archive but omitted from `manifest.csv`, or listed in `manifest.csv` but missing from the zip, the package is rejected.

2. **The Strict Bulk vs Delta Partition**:
   - One exchange bundle = strictly ONE operational mode.
   - **Bulk Exchange Mode**: Represents a complete master snapshot. In OneRoster 1.1, rows MUST NOT contain `dateLastModified` or `status` columns. In 1.2, all status fields MUST be `active`.
   - **Delta Exchange Mode**: Represents incremental changes. Every row MUST contain a valid ISO 8601 UTC timestamp (`YYYY-MM-DDTHH:MM:SS.sssZ`) in `dateLastModified` and a valid status (`active` or `tobedeleted`).
   - Mixing bulk and delta formats within the same archive is a fatal validation failure.

3. **The Foreign Key Referential Integrity Hierarchy**:
   - Relational dependency chain:
     $$\mathbf{orgs.csv} \longleftarrow \mathbf{courses.csv} \longleftarrow \mathbf{classes.csv} \longleftarrow \mathbf{enrollments.csv} \longrightarrow \mathbf{users.csv}$$
   - Every `schoolSourcedId` in `users.csv` and `classes.csv` MUST resolve to an existing `sourcedId` in `orgs.csv`.
   - Every `courseSourcedId` in `classes.csv` MUST resolve to `courses.csv`.
   - Every `userSourcedId` and `classSourcedId` in `enrollments.csv` MUST resolve to active primary keys in `users.csv` and `classes.csv`.
   - **Zero Orphan Tolerance**: A single orphan link (e.g. enrolling a deleted student ID) invalidates the batch.

4. **Strict Role Enum Constraint Axioms**:
   - In `users.csv`: `role` MUST strictly match the OneRoster enum:
     - v1.1: `administrator`, `proctor`, `student`, `teacher`.
     - v1.2: adds `aide`, `guardian`, `parent`, `staff`.
   - In `enrollments.csv`: `role` is strictly constrained to `administrator`, `proctor`, `student`, `teacher`. Custom vendor roles (`substitute`, `dean`) must be mapped to valid specification enums.
   - Primary `sourcedId` values MUST be case-sensitively unique strings (RFC 4122 UUID format strongly recommended).

5. **RFC 4180 CSV & UTF-8 BOM Sanitization**:
   - Files MUST be encoded in **UTF-8 without Byte Order Mark (BOM)**.
   - Microsoft Excel frequently prepends `\xEF\xBB\xBF` to CSV exports, corrupting the first column name (`sourcedId` $\neq$ `sourcedId`).
   - Fields containing commas, double-quotes, or newlines MUST be enclosed in double quotes (`"`). Literal quotes inside fields must be escaped as `""`.

## Named Sins & Anti-Patterns (Что категорически ЗАПРЕЩЕНО)

| Anti-Pattern | Manifestation in CSV Packages | Mandatory Production Counter-Rule |
| :--- | :--- | :--- |
| **Nested Archive Packaging** | Zipping a parent folder (`district_roster/manifest.csv`). | Manifest and CSVs MUST reside at the archive root (`/manifest.csv`). |
| **The UTF-8 BOM Trap** | Excel export injecting `\xEF\xBB\xBF` into `sourcedId`. | Strip BOM headers during pre-flight sanitization pass. |
| **Orphan Enrollment Links** | `enrollments.csv` pointing to non-existent user IDs. | Enforce foreign key validation across all entity files before import. |
| **Bulk / Delta Column Bleed** | Including `dateLastModified` or `tobedeleted` in bulk. | Enforce schema separation: bulk files must not contain delta headers. |
| **Circular Org Hierarchies** | School A listed as parent of School B, and vice-versa. | Perform directed acyclic graph (DAG) cycle detection on `orgs.csv`. |
| **Illegal Role Strings** | Using non-standard strings like `counselor` or `sub`. | Map all roles to standard enums (`administrator`, `teacher`, `student`). |
| **Malformed ISO 8601 Timestamps** | Writing `"09/22/2026 14:00"` instead of ISO 8601. | Enforce strict RFC 3339 UTC format: `YYYY-MM-DDTHH:MM:SS.sssZ`. |
| **Unescaped CSV Commas** | Unquoted commas in user names (`Doe, John`) shifting columns. | Wrap text fields containing commas in standard RFC 4180 quotes. |
| **Missing Mandatory Core Files** | Exporting a bulk zip without `academicSessions.csv`. | Confirm all 7 mandatory core files exist in zip and manifest. |
| **Hallucinated SourcedIDs** | Inventing arbitrary IDs during reconciliation. | Preserve authoritative SIS primary keys without synthetic generation. |

## Concrete Archetypes / Presets

### Archetype 1: Pre-Flight Python Referential Integrity Linter
```python
import csv
import io
import re
import zipfile
from typing import Dict, List, Set

CORE_FILES = [
    "manifest.csv", "orgs.csv", "users.csv", "courses.csv",
    "classes.csv", "enrollments.csv", "academicSessions.csv"
]
VALID_ROLES_1_1 = {"administrator", "proctor", "student", "teacher"}

def validate_oneroster_archive(zip_path: str) -> dict:
    errors = []
    warnings = []

    with zipfile.ZipFile(zip_path, 'r') as z:
        names = z.namelist()

        # 1. Manifest Root Check
        if "manifest.csv" not in names:
            return {"status": "FATAL", "errors": ["manifest.csv missing from zip root."]}

        # 2. Extract and Parse Core Sets
        data: Dict[str, List[dict]] = {}
        keys: Dict[str, Set[str]] = {}

        for filename in CORE_FILES:
            if filename in names:
                raw_bytes = z.read(filename)
                # Strip UTF-8 BOM if present
                if raw_bytes.startswith(b'\xef\xbb\xbf'):
                    warnings.append(f"{filename} contains UTF-8 BOM; stripped automatically.")
                    raw_bytes = raw_bytes[3:]

                reader = csv.DictReader(io.StringIO(raw_bytes.decode('utf-8', errors='replace')))
                rows = list(reader)
                data[filename] = rows
                if rows and "sourcedId" in rows[0]:
                    keys[filename] = {r["sourcedId"] for r in rows if "sourcedId" in r}
            else:
                errors.append(f"Mandatory core file missing: {filename}")

        # 3. Foreign Key Checks
        if "orgs.csv" in keys and "users.csv" in data:
            for r in data["users.csv"]:
                org_ref = r.get("orgSourcedIds") or r.get("schoolSourcedId")
                if org_ref and org_ref not in keys["orgs.csv"]:
                    errors.append(f"users.csv: user {r.get('sourcedId')} references non-existent org {org_ref}")

        if "users.csv" in keys and "classes.csv" in keys and "enrollments.csv" in data:
            for r in data["enrollments.csv"]:
                uid = r.get("userSourcedId")
                cid = r.get("classSourcedId")
                if uid not in keys["users.csv"]:
                    errors.append(f"enrollments.csv: orphan enrollment references missing user {uid}")
                if cid not in keys["classes.csv"]:
                    errors.append(f"enrollments.csv: orphan enrollment references missing class {cid}")

                role = r.get("role")
                if role not in VALID_ROLES_1_1:
                    errors.append(f"enrollments.csv: illegal role '{role}' on user {uid}")

    return {
        "status": "FAILED" if errors else "PASSED",
        "errors": errors,
        "warnings": warnings,
        "total_records_checked": sum(len(v) for v in data.values())
    }
```

### Archetype 2: Minimal Valid OneRoster v1.1 Manifest
```csv
propertyName,value
oneroster.version,1.1
file.orgs,bulk
file.users,bulk
file.courses,bulk
file.classes,bulk
file.enrollments,bulk
file.academicSessions,bulk
```

### Archetype 3: Delta Synchronization Row Example
```csv
sourcedId,status,dateLastModified,userSourcedId,classSourcedId,role,primary
enr_99201,active,2026-09-22T08:30:00.000Z,usr_0421,cls_bio101,student,true
enr_99202,tobedeleted,2026-09-22T08:30:00.000Z,usr_0884,cls_bio101,student,false
```

## The Pre-Emit Quality Gate Checklist

Before emitting validation results, CSV transforms, or roster scripts:
- [ ] **Root Location**: Confirmed `manifest.csv` is located strictly at `/` without folder nesting.
- [ ] **BOM Scrubbing**: Verified all CSV headers are clean UTF-8 without `\xEF\xBB\xBF`.
- [ ] **Mode Consistency**: Verified no delta columns exist in bulk packages, and timestamps exist in deltas.
- [ ] **Referential Integrity**: 100% check of `userSourcedId`, `classSourcedId`, `orgSourcedIds`, and `courseSourcedId`.
- [ ] **Role Enum Validation**: Confirmed all role values match 1EdTech specifications.
- [ ] **Cycle-Free Orgs**: DAG validation run on `orgs.csv` to ensure no circular parent references.
- [ ] **RFC 4180 Escaping**: Commas, quotes, and multi-line values enclosed in double quotes.

## Self-Correction & Verification Loop

### Bad (Vibe-Coded AI Slop Output):
> "Zip your CSV folder up and upload it. If Clever says there is a missing manifest, just add a blank manifest.csv inside the folder. If some student IDs are missing in enrollments, just delete those rows or invent temporary user IDs."
*Problems: Nested zip breaks root discovery, blank manifest violates schema, inventing fake IDs corrupts student records, ignores UTF-8 BOM.*

### Good (Production OneRoster CSV Validator Output):
> 1. Extract zip to verify `/manifest.csv` exists at the root path; confirm `oneroster.version` is declared as `1.1`.
> 2. Scan all CSV headers: detect and strip UTF-8 BOM (`\xEF\xBB\xBF`) from `users.csv` to prevent header corruption.
> 3. Perform foreign key referential integrity pass:
>    - Flag 4 orphan rows in `enrollments.csv` referencing deleted student ID `usr_9914`.
>    - Flag invalid role `"substitute_teacher"` on row 14 of `enrollments.csv` (must map to `"teacher"`).
> 4. Verify bulk purity: confirm no `dateLastModified` or `tobedeleted` status flags are present in bulk mode.
> 5. Emit clean, line-numbered pre-flight audit report before committing to SIS ingestion.

## Limitations

- OneRoster version details and required files can differ by exchange profile and vendor implementation. Validate against the exact 1EdTech specification and recipient's current import contract before rejecting or transforming a roster.
- Student and staff roster files contain sensitive education records. Process only with authorization, minimize retained data, redact identifiers in diagnostics, and do not upload source CSVs to external services.
- The included snippets are illustrative, not a complete certified validator; for example, large files, namespaces, delta semantics, and vendor extensions require additional handling and tests.