Project Atlas AetherDev project hub
Atlas project production

Aeries-to-Google Sheets Reporting Extract Family

Coordinate the parent family of scheduled Aeries-to-Google Sheets extracts that feed attendance, class-size, enrollment, PADC, and related district reporting workflows.

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

Purpose

Document the common operating model behind BOUSD reporting extracts so downstream dashboards and planning workflows can be maintained as a family instead of as isolated jobs.

Current state

The registry treats MonthlyAttendance, ClassSize, Enrollment, Enrollment Worksheet HR, DataConfDocs, and PADC as one production parent family with documented schedules and shared operational dependencies. Repo evidence confirms concrete tab contracts for key feeds: MonthlyAttendance (`SourceData-year-bygrade`, `SourceData-year-byschool`), ClassSize (`SourceData-summary`, `SourceData-class-counts`), DataConfDocs (`SourceData-dataconf-docs`), and PADC (`SourceData_YTD`, `SourceData_DAY`). Cron-path evidence consistently points at `/home/bousdtech/repos/.../run.sh`, and host normalization now ties this Linux cron model primarily to `sal-01.bousd.us`. For MonthlyAttendance, manual SQL/paste steps in dashboard documentation are now classified as legacy fallback/reference, with automated extract execution as the normal production path. Extract repos do not show native mailer/notification logic in `run.sh`, so alerting and escalation remain external operational concerns.

Next step

Use the [Owner, Escalation, and Fallback Matrix](../owner-escalation-and-fallback-matrix/) as the canonical reconciliation layer for this family, then close per-feed fields for technical operator, business sign-off owner, fallback operator, and escalation owner.

Interfaces

Inputs
  • Aeries SQL queries
  • per-job TOML configs
  • ODBC credentials
  • Google Sheets sharing and range contracts
Outputs
  • Google Sheet tabs
  • local CSV artifacts
  • source data for dashboards and planning workflows

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Not in scope.

Automation Engines

Not in scope.

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

  • Aeries remains canonical for the source data queried by every extract.
  • Google Sheets are operational distribution surfaces, not the authoring system for the extracted data.

Safe handling

  • Keep service-account keys and ODBC credentials out of git.
  • Treat CSV artifacts and destination sheets as restricted operational data.
  • Document host ownership and credential recovery paths in an internal runbook.

Operational notes

Reliability posture

The family is production-active with deterministic `run.sh` contracts and repo-level schedule evidence in `aether/docs/cron-example.txt`. Host normalization now identifies `sal-01.bousd.us` as the primary Linux runtime host for these cron-driven extract workloads. Alert routing, backup operator coverage, and credential custodianship are still not centrally documented.

Observability

  • stdout/stderr logs from run.sh and CLI tools
  • CSV artifacts in data/ and temp/
  • stale-sheet symptoms in downstream dashboards

Security and privacy

These jobs move restricted student and staff reporting data. ODBC credentials, service-account keys, CSV outputs, and destination sheets must remain tightly controlled.

Dependencies

Upstream
  • Aeries schema stability
  • ODBC driver health
  • scheduler inventory
  • service-account access
Downstream
  • Monthly Attendance Dashboard
  • PADC reporting workflows
  • enrollment and staffing planning views

Ownership

Owners

Technology Services, Josh Barton

Users

Technology Services, report owners, district leadership

Aeries-to-Google Sheets Reporting Extract Family

Operational Notes

  • This page is the parent family record for multiple district reporting feeds that share one runtime pattern and one risk profile.
  • The registry now treats these jobs as one production family because they share the same core pattern: run.sh, conf.d job config, SQL-over-ODBC extraction, local CSV artifacts, and controlled Google Sheets range overwrites.
  • aether/docs/cron-example.txt provides concrete scheduler-path evidence for this family under /home/bousdtech/repos/.../run.sh, including hourly, 15-minute, and 30-minute weekday cadences by feed.
  • Host normalization: these cron-based extract jobs should be treated as primarily sal-01.bousd.us Linux runtime workloads unless an explicit exception is documented.
  • MonthlyAttendance fallback boundary is now explicit at family level: scheduled sal-01 extract execution is primary; manual SQL/paste remains legacy fallback/reference only.
  • Implementation splits into two closely related tool chains: MonthlyAttendance/ClassSize use bin/mssql_to_csv and bin/google-sheet-download; DataConfDocs/PADC use mssql_query_to_csv with the same sheet-overwrite pattern.
  • The extract READMEs consistently require Google Sheet edit sharing for the same service account (aether@aether-422702.iam.gserviceaccount.com), indicating a shared credentials/governance boundary across jobs.
  • Current repo evidence is stronger for scheduler paths than escalation routing: extract run.sh scripts perform query and sheet-write actions but do not include in-repo email/notification handlers, so incident alerting likely depends on external scheduler/runtime monitoring.
  • The family matters operationally because multiple leadership dashboards, certification workflows, and planning surfaces depend on the same small set of runtime assumptions: working ODBC access, valid service-account sharing, intact sheet headers, and a known scheduler owner.
  • Atlas now carries the cadence evidence, but not the full runbook. The biggest remaining gap is a single internal inventory of hosts, credentials, destination sheets, report owners, and backup operators.
  • Governance note: this page now defers owner/escalation/fallback role tracking to the Owner, Escalation, and Fallback Matrix.
  • Implementation layer is distributed across dedicated repos (BOUSD-MonthlyAttendance-Extract, BOUSD-ClassSize-Extract, BOUSD-Enrollment-Extract, BOUSD-Enrollment-Worksheet-HR, BOUSD-DataConfDocs-Extract, BOUSD-PADC-Extract) with shared operating patterns.

Documented Family Inventory

Extract/feedPrimary consumerBusiness ownerCadenceSensitivity
BOUSD-MonthlyAttendance-ExtractBOUSD-MonthlyAttendance-DashboardEducational Services + Technology Services [validate]Hourly weekdays, 07:00-18:00Restricted student attendance data
BOUSD-ClassSize-ExtractClass-size and staffing planning viewsEducational Services + Technology Services [validate]Hourly weekdays, 07:00-18:00Restricted student/staff planning data
BOUSD-Enrollment-ExtractEnrollment planning and recurring district reportsEducational Services + Technology Services [validate]Every 15 minutes, 07:00-18:00Restricted student enrollment data
BOUSD-Enrollment-Worksheet-HREnrollment staffing worksheet workflowsHR + Technology Services [validate]Every 15 minutes, 07:00-18:00Restricted student/staff planning data
BOUSD-DataConfDocs-ExtractData confirmation document workflowsSchool sites + Technology Services [validate]Every 15 minutes weekdays, 07:00-18:00Restricted student/guardian context data
BOUSD-PADC-ExtractPADC certification and revision supportEducational Services + Technology Services [validate]Every 30 minutes, 07:00-18:00 plus reporting-window rerunsRestricted attendance and funding-impact data

Compliance and Reporting Context

  • Multiple district dashboards and recurring reporting processes depend on this governed extract family, so failures here can propagate well beyond one report page.
  • State/compliance workflows (for example CALPADS, CERS, CBEDS, CRDC) are still tracked at high level in Atlas; explicit per-feed dependencies remain validation-needed unless direct feed contracts are documented.

Evidence-Backed Contracts (Current Pass)

  • BOUSD-MonthlyAttendance-Extract: run.sh executes conf.d/year-bygrade.toml and conf.d/year-byschool.toml; uploads to SourceData-year-bygrade!A1:ZZ10000 and SourceData-year-byschool!A1:ZZ10000.
  • BOUSD-ClassSize-Extract: run.sh executes conf.d/summary.toml and conf.d/class-counts.toml; uploads to SourceData-summary!A1:ZZ10000 and SourceData-class-counts!A1:ZZ10000.
  • BOUSD-DataConfDocs-Extract: run.sh executes conf.d/config-dataconf-docs.toml; uploads to SourceData-dataconf-docs!A1:ZZ10000.
  • BOUSD-PADC-Extract: run.sh builds merged attendance output from four query configs plus calendar output, then uploads to SourceData_YTD!A2:ZZ10000 and SourceData_DAY!A2:ZZ10000.

Registry Alignment

  • Registry context: former INT-058 is no longer counted as a standalone integration in the BOUSD registry.
  • Registry clarified: Atlas keeps this page because multiple BOUSD reporting jobs share one repeated production pattern and should be maintained as an operational family, not as isolated one-off scripts.
  • Validation gaps: tab/range contracts, scheduler paths, and primary Linux host role (sal-01) are now evidence-backed; unresolved owner/escalation/fallback fields are now tracked centrally in the Owner, Escalation, and Fallback Matrix, including backup-operator coverage, centralized alert routing, exception-host mapping, and per-feed fallback trigger ownership.