oneroster-csv-validator – Other skill for AI agents

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")

… (full content is in the download package)