Project Atlas AetherDev project hub
Atlas project production

District Transfer Tracking

Automates BOUSD transfer intake, normalization, review workflows, Aeries SIS write-back, and enrollment/class-load planning from one governed Google Sheets hub.

Internal-only entry. Do not publish externally without review.
Playbook available

Operational guidance is available for this system.

Open playbook
Type
System
Lifecycle
Active
Last touched
2026-03-26
Visibility
Internal

Purpose

Run an auditable transfer operations system that centralizes multi-source transfer data, supports district/school review workflows, and keeps Aeries + planning views aligned.

Current state

Production-active pipeline and web dashboard with role-based operations, data validation tooling, decision scoring, AIR enrichment, Aeries SIS write-back, enrollment projections, and class-load planning. This is the declared active transfer runtime path; the older `aether/informedk12-sync-transfers.sh` wrapper is now documented as retired.

Next step

Publish production host ownership, key-file storage/recovery ownership, and scheduler accountability alongside the existing data-quality hardening roadmap.

Interfaces

Inputs
  • InformedK12 campaign responses (inter, intra, denied, archived, cancelled)
  • Jotform responses with workflow status
  • Aeries STU demographics (elementary + secondary extracts)
  • Aeries AIR next-year registration extract
  • Manual transfer entries in Transfers sheet/dashboard
  • Google Sheet config tabs (config, config-dashboard*, config-class-load, config-grades)
Outputs
  • Google Sheet tabs: IK12-Inter, IK12-Intra, JF-Inter, Transfers, Aeries-STU-Demo, Aeries-AIR-Next-Year
  • Persisted lifecycle + scoring columns in Transfers (record.status.calculated, decision.score.*, indicator.*)
  • Aeries write-back artifacts: generated SQL + result CSV + Transfers ack fields (aeries, registration.sc.updated)
  • Operational artifacts: backups/transfers-*.csv, logs/run-*.log, emails_sent/*

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Contributes in this stage.

Automation Engines

Contributes in this stage.

Human Interfaces

Contributes in this stage.

Operational Adoption

Contributes in this stage.

Core workflow

  1. Acquire lock and rotate logs/backups.
  2. Backup Transfers tab before any writes.
  3. Export InformedK12 + Jotform + Aeries STU + Aeries AIR sources.
  4. Normalize source CSVs and merge unified transfer datasets.
  5. Sync raw tabs and Transfers; upload Aeries STU/AIR tabs.
  6. Recompute record.status.calculated and sync.
  7. Compute decision-weighted scoring + indicators and sync.
  8. Enrich Transfers with AIR registration linkage (registration.id/status/sc).
  9. Generate/execute Aeries write-back SQL for STU.IT/NIT/NS and registration SC when enabled.
  10. Write acknowledgements back to Transfers (aeries, registration.sc.updated), send notifications/reports.

Data integrity and contracts

Canonical schema definitions

  • conf.d/informedk12-sync-transfers-inter.json
  • conf.d/informedk12-sync-transfers-intra.json
  • conf.d/jotform-sync-transfers.json
  • conf.d/aeries-stu-demo-*.toml
  • conf.d/aeries-air-enrollments-next-year.toml
  • conf.d/google-sheets-sync-transfers.toml

Source of truth rules

  • Transfers is the unified operational record and includes human-maintained columns.
  • Aeries-STU-Demo and Aeries-AIR-Next-Year are refreshed operational context snapshots.
  • record.status.calculated governs dashboard queue inclusion/exclusion semantics.
  • Decision and planning views rely on calculated grade fields and persisted indicator outputs.

Data quality checks

  • Lifecycle status normalization (Incomplete/Withdrawn/Pending BOUSD Decision/Complete).
  • Dashboard Data Validation queue with severity-coded rule outputs and guided remediation.
  • Student ID lookup + grade validation helpers in Apps Script.
  • AIR matching uses unique normalized Name+DOB keys with ambiguity safeguards.
  • Aeries write-back code validation against transfer code table and school code resolution checks.

Safe handling

  • Do not commit secrets (tokens, key files, credentials).
  • Use service-account least privilege for sheet access.
  • Treat logs/backups as sensitive due student data.
  • Use dry-run before enabling new Aeries write-back behavior in production.

Downstream integration map

  • District transfer decision operations
  • Aeries SIS rollover state
  • Enrollment projection review
  • Class-load and staffing planning

Artifacts

  • run.sh
  • appsscript/Dashboard.js
  • appsscript/TransferDashboard.html
  • appsscript/Code.js
  • scripts/aeries_status_writeback.py
  • scripts/transfer_record_status.py
  • scripts/transfer_decision_score.py
  • scripts/air_registration_match.py
  • sql/aeries-stu-status-writeback-template.sql
  • sql/aeries-air-enrollments-next-year.sql
  • config.toml
  • config.example.toml

Operational notes

Constraints and scars

  • Transfers sheet schema is the runtime contract; missing headers skip corresponding stages with warnings/action-required.
  • Aeries ack writes are intentionally blocked when STU.TG eligibility checks fail.
  • Approved-school mapping quality directly impacts STU.NS/registration SC write-back outcomes.
  • Google Sheets tab names and header rows are strict dependencies for both run.sh and Apps Script.

Reliability posture

Single-run lock, strict shell mode, pre-sync Transfers backups, deterministic batch transforms, dry-run support for Aeries write-back, and staged ACTION REQUIRED reporting for unresolved conditions.

Observability

  • Per-run structured summary rows embedded in report emails
  • Run logs (logs/run-*.log) and component logs (google-sheet-sync.log)
  • Generated SQL/result artifacts for Aeries write-back audit
  • Warning/action channels in HTML report email and Apps Script UI messages

Security and privacy

Contains student PII and transfer decisions; service-account sheet access, SQL credentials, API tokens, and SMTP credentials are controlled via local config files; dashboard access is email-gated by role/access-level config; internal-only visibility.

Dependencies

Upstream
  • InformedK12 API
  • Jotform API
  • Aeries SQL (SIS + AIR databases)
  • Google Sheets API
  • SMTP relay
Downstream
  • Aeries STU.IT/STU.NIT/STU.NS updates
  • AeriesRegistration STU.SC updates
  • District dashboard decision workflows
  • Enrollment and class-load planning decisions

Ownership

Users

district transfer staff, school administrators, enrollment planning staff, HR/admin staffing reviewers

District Transfer Tracking

Architecture & Major Components

  • High-level diagram (text):

    • Scheduled/manual trigger (run.sh) -> source exports + normalization + sheet sync -> validation/scoring/enrichment -> Aeries write-back + operational dashboard + reporting

  • Entry points:

    • run.sh: primary orchestrator (cron-safe, lock-protected).
    • appsscript/Code.js: spreadsheet tools menu + helper automation.
    • appsscript/Dashboard.js + appsscript/TransferDashboard.html: role-based web app.
    • deploy.sh: Apps Script deployment.
    • scripts/*.py: stage helpers (status normalization, scoring, AIR matching, Aeries write-back prep/summarize).

  • Top-level folders:

    • conf.d/: all connector/mapping configs.
    • sql/: Aeries/AIR query templates and Aeries write-back SQL template.
    • appsscript/: dashboard and spreadsheet workflow logic.
    • templates/: report/new-submission email templates.
    • REFERENCE-INFO/: binary usage and integration references.
    • data/, temp/, logs/, backups/, emails_sent/, lock/: operational artifacts/state.

  • Key abstractions:

    • Unified transfer schema persisted in Transfers.
    • Calculated lifecycle (record.status.calculated) and scoring (decision.score.*, indicator.*) columns.
    • Role/access model via config-dashboard-users and option/config tabs.
    • Projection precedence model (Enrolled > AIR > Transfer) and suppression safeguards.
    • Two-path Aeries acknowledgement model:
      • aeries=TRUE for SIS update confirmation under eligibility constraints.
      • registration.sc.updated=TRUE for AIR registration school-code updates.

Setup / Build / Run

  • Build system(s):
    • No monolithic build; operational shell + Python helpers + Apps Script.
    • External binaries under bin/ are runtime dependencies.
  • Local run steps:
    • cp config.example.toml config.toml
    • Configure APIs, SQL, sheet sync, reporting, and pipeline toggles.
    • Verify required Google Sheet tabs/headers.
    • Run: ./run.sh
    • Deploy dashboard/script updates: ./deploy.sh
  • Runtime dependencies:
    • InformedK12, Jotform, Aeries SIS DB, Aeries AIR DB, Google Sheets, SMTP.
    • Service account key with write access to operational sheet.
    • Python runtime for helper scripts.
    • CLI tools: google-sheet-sync, csv_mapper, mssql_to_csv, informedk12-sync, jotform-sync, email_template_mailer.

Operational Notes

  • Full-cycle behavior:
    • Backs up Transfers, ingests all sources, syncs raw/unified/context tabs, recalculates lifecycle/scoring, enriches AIR linkage, optionally writes to Aeries, and sends report/notification emails.
  • Core operational interfaces:
    • Spreadsheet Transfer Tools menu:
      • Open Dashboard
      • Add Manual Transfer
      • Populate Student IDs from Aeries
      • Validate Grades (Aeries/DOB)
      • Export Communication Lists
      • Refresh Transfer Filter Views
    • Dashboard tabs:
      • Overview
      • Transfers
      • Pending BOUSD Decision
      • Pending BOUSD Approved School
      • Data Validation
      • Incomplete
      • Withdrawn
      • Enrollment Projections
      • Class Load
      • Info
  • Aeries write-back specifics:
    • Writes STU transfer status fields (IT/NIT) and routing school (NS) with rules by transfer type/direction/decision.
    • Writes AIR registration school code (AeriesRegistration_60.dbo.STU.SC) for eligible approved Inter In rows when enabled.
    • Result summarizer computes update/no-op/not-found metrics and syncs acknowledgement fields to Transfers.
  • Common failure modes:
    • Missing sheet headers/columns required by stage helpers.
    • Unmapped school labels for NS/registration school routing.
    • No eligible STU tag rows (TG IN ('', '*', 'I')) causing intentional aeries ack suppression.
    • Ambiguous AIR Name+DOB matches (skipped by design).
    • Missing config-dashboard sheets/headers (auto-create attempted; fallback warnings emitted).
  • Validation/audit anchors:
    • logs/run-*.log + report email summary_rows.
    • data/aeries-stu-status-writeback-generated.sql + data/aeries-stu-status-writeback-results.csv.
    • Transfers backup snapshots in backups/.
    • Dashboard Data Validation queue and issue-guided remediation.

Registry Alignment

  • Mapped registry entries: INT-001, INT-002, INT-004, INT-005.
  • The registry confirms this page is the primary production transfer-operations record, not just a repo summary. It clarifies the weekday hourly cadence, Phase I lineage, AIR readiness dependency, and Aeries write-back risk profile.
  • Cross-repo runtime context now matters explicitly: district-transfer-tracking is the active implementation and runtime layer. aether/informedk12-sync-transfers.sh is now a retired historical wrapper reference.
  • Validation gaps that remain outside the current Atlas page: production host inventory, key-file storage and recovery ownership, dashboard deployment identifiers, and final escalation/rollback ownership.

Related playbooks