Project Atlas AetherDev project hub
Atlas project development

Aeries Staff Extract SQL Generator

Extract operationally meaningful staff datasets that reflect reality across schools and roles. It ingests Aeries tables (staff, sections, TCH/MST, etc.); date windows and produces canonical staff datasets; counts and associations; room mappings.

Type
Component
Lifecycle
Active
Last touched
2025-10-26
Visibility
Public
Why it's showcased

Provides canonical staff datasets that downstream integrations and reporting depend on.

Purpose

Extract operationally meaningful staff datasets that reflect reality across schools and roles.

Current state

Query requirements defined, including elementary vs secondary scheduling differences and operational counts.

Next step

Create a versioned extract contract with column definitions and meanings.

Interfaces

Inputs
  • Aeries tables (staff, sections, TCH/MST, etc.)
  • date windows
Outputs
  • canonical staff datasets
  • counts and associations
  • room mappings

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Contributes in this stage.

Automation Engines

Not in scope.

Human Interfaces

Not in scope.

Operational Adoption

Not in scope.

Core workflow

  1. Define term and date window parameters.
  2. Run canonical SQL joins across staff and schedule tables.
  3. Normalize staff identifiers and roles.
  4. Compute room and site associations.
  5. Output dataset to staging or CSV.
  6. Validate counts and anomalies.
  7. Publish extract to downstream consumers.

Data integrity and contracts

Canonical schema definitions

  • Staff extract schema (staff ID, email, site, role, room).
  • Join rules for staff vs sections (TCH/MST).
  • Output table or CSV column definitions.
  • Room mapping derivation rules.

Source of truth rules

  • Aeries is canonical for staff and assignments.
  • Extract SQL defines the official operational view.
  • Downstream systems should not override extract fields.

Data quality checks

  • Row counts vs Aeries totals by site.
  • Null or duplicate staff IDs or emails.
  • Mismatch checks between schedule tables and roster.
  • Room mapping consistency validation.

Safe handling

  • PII handling and restricted access to extracts.
  • Use read-only DB credentials for queries.

Downstream integration map

  • Device refresh ops portal.
  • Integration connectors.
  • Perpetual reports portal.

Operational notes

Reliability posture

Deterministic queries that require schema awareness.

Observability

  • row counts
  • validation queries (recommended)

Security and privacy

PII

Dependencies

Upstream
  • Aeries schema and data quality
Downstream
  • device ops portal
  • integrations
  • reports portal

Ownership

Owners

Josh Barton

Users

data/IT, downstream automation systems, Josh Barton (owner)