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 (Что категорически ЗАПРЕЩЕНО)

Concrete Archetypes / Presets

Archetype 1: Pre-Flight Python Referential Integrity Linter

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

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

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.