oneroster-csv-validator

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, retain required CSV headers while leaving dateLastModified and status values empty. OneRoster1.2 also prohibits using those fields in bulk; do not populate active by default.
   • 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}$$
   • Validate the version-specific org reference fields (including multi-valued orgSourcedIds in1.1 users and separate1.2 roles); each applicable reference must resolve to an existing sourcedId in orgs.csv.
   • Every courseSourcedId in classes.csv MUST resolve to courses.csv.
   • In a complete bulk snapshot, enrollment references must resolve. A delta may reference unchanged records already in the recipient baseline; validate against that state and deletion semantics, not the archive alone.
   • 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:
     • Load the exact version-specific enumeration and file layout from the binding. OneRoster1.2 moves role relationships to roles.csv; do not apply a1.1 users.role check to it.
   • 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 retains schema headers with status/dateLastModified values empty.
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.	Determine required files from the exact exchange profile; implementation support requirements do not mean every exchange must contain the full core.
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

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:
        infos = z.infolist()
        if len(infos) > 100 or len({i.filename for i in infos}) != len(infos):
            raise ValueError("Too many or duplicate ZIP members")
        maximum = 4 * 1024 * 1024
        if sum(i.file_size for i in infos) > 16 * 1024 * 1024:
            raise ValueError("Archive expansion limit exceeded")
        for info in infos:
            if info.file_size > maximum or info.file_size / max(1, info.compress_size) > 100:
                raise ValueError("ZIP expansion or compression-ratio limit exceeded")
        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:
                with z.open(filename) as member:
                    raw_bytes = member.read(maximum + 1)
                if len(raw_bytes) > maximum:
                    raise ValueError("Expanded member exceeds limit")
                # 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='strict')))
                rows = []
                for row in reader:
                    if len(rows) >= 10000:
                        raise ValueError("CSV row limit exceeded")
                    rows.append(row)
                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")
                for org_id in (org_ref or "").split(","):
                    if org_id and org_id not in keys["orgs.csv"]:
                        errors.append("users.csv: unresolved org reference (identifier redacted)")

        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 "PARTIAL_CHECKS_ONLY",
        "errors": errors,
        "warnings": warnings,
        "total_records_checked": sum(len(v) for v in data.values())
    }

Archetype 2: Partial OneRoster v1.1 Manifest Sketch (add required absent declarations)

propertyName,value
manifest.version,1.0
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 bulk headers are present and status/dateLastModified values are empty, 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 status/dateLastModified values are empty 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.

This linter intentionally checks only a narrow1.1-style bulk subset; it does not implement manifest validation, full version/header/enumeration rules, duplicate IDs, org cycles or1.2 roles. Never report full compliance from PARTIAL_CHECKS_ONLY. Strictly decode input and preserve originals; BOM tolerance is a normalization choice, not permission to silently rewrite source rosters. Version-specific files and recipient baseline are required for deltas. References: 1.1 bulk semantics, 1.2 CSV binding, 1.2 conformance scope.