Project Atlas AetherDev project hub
Atlas project production

OCDE S1S Summer School Roster Export Automation

Automates generation of the OCDE ACCESS S1S bulk-import roster file from a governed Google Sheets `Data Entry` tab and Aeries demographics, with verification and timestamp writeback controls.

Internal-only entry. Do not publish externally without review.
Type
System
Lifecycle
Active
Last touched
2026-04-07
Visibility
Internal

Purpose

Automate generation of OCDE ACCESS S1S bulk-import roster files while keeping sheet-driven operator review, verification, and timestamp controls intact.

Current state

This workflow is now in production use. The `bousd-ocde-s1s-export` implementation follows the district transfer-tracking operating pattern (`run.sh` plus Python helper scripts plus `mssql_to_csv` and `google-sheet-sync`) and currently runs under an operator-run model; no standing scheduler is documented for this pass.

Next step

Document the active production spreadsheet ID, confirm the operational owner handoff and seasonal cadence, and decide whether the current operator-run model should remain the production pattern or gain scheduler support.

Interfaces

Inputs
  • Google Sheets `Data Entry` operator fields
  • Aeries `STU` and `IDN` demographics plus related parent-program fields
  • Vendor template requirements from `S1S_BulkImport_BOUSD.xlsx` and `BulkImport_Definitions_BOUSD.xlsx`
Outputs
  • Run-specific `OCDE-S1S-Export-YYYYMMDD-HHMMSS` tabs in the same spreadsheet
  • Verified vendor-import-ready roster rows
  • `export.timestamp` writebacks for verified source rows
  • Per-run logs and `run-manifest-<run_id>.json` artifacts

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Not in scope.

Automation Engines

Contributes in this stage.

Human Interfaces

Contributes in this stage.

Operational Adoption

Contributes in this stage.

Core workflow

TBD. Document the 5-10 steps that define the core workflow.

Data integrity and contracts

Source of truth rules

  • The `Data Entry` sheet governs which rows are eligible for export through `export.ready` and `export.timestamp`.
  • Aeries is canonical for the student demographic and related source fields used in the vendor template mapping.
  • Run-specific output tabs and manifests are operational artifacts, not authoring records.

Safe handling

  • Keep service-account credentials and SQL access controlled.
  • Treat output tabs, logs, and manifests as restricted student data artifacts.
  • Do not write `export.timestamp` until verification confirms expected student IDs are present.

Operational notes

Reliability posture

Per-run logs and JSON manifest artifacts provide strong execution evidence. Verification occurs before timestamp writeback, which reduces accidental acknowledgement of incomplete exports. The current production model is operator-run rather than scheduler-backed.

Observability

  • Per-run logs
  • `run-manifest-<run_id>.json` artifacts
  • ready, merge, verify, and timestamp summaries

Security and privacy

Restricted student operational and contact data; FERPA-relevant roster and family contact fields remain inside the same spreadsheet ACL boundary and controlled Aeries or Sheets credentials.

Dependencies

Upstream
  • Stable `Data Entry` headers (`student_id`, `export.ready`, `export.timestamp`)
  • Aeries field availability
  • Vendor template stability
  • Service-account Google Sheets access
Downstream
  • Operator export handoff to OCDE ACCESS S1S bulk import

Ownership

Owners

Technology Services, Josh Barton

Users

Technology Services, summer school operations and counseling stakeholders [validate]

OCDE S1S Summer School Roster Export Automation

Operational Notes

  • Implementation: bousd-ocde-s1s-export/run.sh, scripts/pipeline.py, scripts/sheets_api_ops.py, sql/aeries-ocde-s1s-template.sql, docs/field-mapping-matrix.csv, docs/template-requirements.md, and docs/runbook.md.
  • Method: controlled sheet download, eligibility filtering, student-ID-scoped SQL enrichment, deterministic field mapping, tab-per-run output, verification, and row-index writeback.
  • The workflow selects only rows where export.ready is true-like and export.timestamp is blank, maps sheet and Aeries data to the exact vendor template contract, creates a new output tab in the same spreadsheet for each run, verifies expected student IDs are present, then writes export.timestamp only for rows that passed verification.
  • Current production posture is operator-run. A standing scheduler has not yet been documented.

Registry Alignment

  • Mapped registry entry: INT-085
  • Registry clarified: this workflow is now in production use and should be represented in Atlas as a first-class BOUSD integration record.
  • Validation gaps: active production spreadsheet ID [placeholder pending review], operational owner handoff, exact seasonal run cadence, and whether a standing scheduler will be added.