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

…