Project Atlas AetherDev project hub
System Playbook: District Transfer Tracking icon
Playbooks

System Playbook: District Transfer Tracking

Operator playbook for running, validating, and recovering the transfer operations pipeline and dashboard workflows.

System map

This playbook supports

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.

Pipeline
Reality Ingestion Canonical Storage Automation Engines Human Interfaces Operational Adoption
Domains
K12 Transfer Operations Sis Integration Enrollment Planning School Operations
Patterns
Extract Transform Sync Sheet as Application Role Based Webapp Idempotent Batch Runner Ops Reporting Db Writeback Loop

Related Atlas entry

Purpose

Provide runbook-grade guidance for:

  • executing the transfer pipeline safely (manual or scheduled),
  • validating data quality and write-back outcomes,
  • handling exceptions in Aeries, AIR linkage, and sheet-driven operations,
  • supporting district/school staff dashboard workflows.

Audience and access

  • Primary operators:
    • District data/registrar staff running run.sh.
    • Technical staff maintaining configs and stage behavior.
  • Operational users:
    • District reviewers (full_access or read_only) in dashboard.
    • School reviewers (read-only, school-scoped).
  • Required access:
    • Repo checkout with executable run.sh and bin/*.
    • Google Sheet access via service account key configured in google-sheets-sync.
    • InformedK12 + Jotform API credentials.
    • Aeries SIS SQL credentials and AIR DB SQL credentials.
    • SMTP credentials if reporting/notifications are enabled.

Operational workflow

  1. Pre-run checks

    • Confirm config.toml exists and pipeline toggles reflect intended run mode.
    • Confirm service account key file path is valid and sheet is shared.
    • Confirm required sheet tabs/headers exist (Transfers, Aeries-STU-Demo, Aeries-AIR-Next-Year, config, config-dashboard*, config-class-load, config-grades).
    • Confirm binaries in bin/ are executable.

  2. Execute run

    • Full run:
      • ./run.sh
    • Safety run before enabling DB writes:
      • ./run.sh --aeries-status-writeback-dry-run
    • The script self-cds to repo root, rotates logs, acquires lock, and performs pre-change Transfers backup.

  3. Monitor stage progression

    • Watch logs/run-*.log live or post-run.
    • Confirm stage groups:
      • Source exports (IK12/Jotform/Aeries/AIR)
      • Mapping/merge
      • Sheet sync/upload
      • Status normalization
      • Decision scoring
      • AIR registration enrichment
      • Aeries write-back
      • Notifications/reporting

  4. Validate write-back outcomes

    • Review Aeries write-back rows in report:
      • Rows processed, Updated, No-op, Not found, Other
      • NS metrics
      • Registration SC metrics
    • Validate ack behavior:
      • aeries=TRUE only when eligibility checks pass.
      • registration.sc.updated=TRUE when registration school update/no-op succeeds.
    • Inspect artifacts:
      • data/aeries-stu-status-writeback-generated.sql
      • data/aeries-stu-status-writeback-results.csv

  5. Validate operational sheet state

    • Confirm raw tabs updated.
    • Confirm Transfers row counts and expected deltas.
    • Confirm record.status.calculated sync completed.
    • Confirm scoring columns updated (decision.score.*, indicator.*).
    • Confirm AIR enrichment columns (registration.*) updated where matched.

  6. Dashboard operational usage

    • District reviewers work queues:
      • Pending BOUSD Decision
      • Pending BOUSD Approved School
      • Data Validation
    • Use modal detail cards for Student, Transfer, Submission, Class Load, Decision Signals, and Data Quality guidance.
    • Use Enrollment Projections/Class Load for planning decisions (scenario toggles + school/grade filters).

Validation checklist

  • Pipeline-level
    • Lock acquired and released.
    • Transfers backup created for run.
    • No stage-level hard failures in run log.
  • Data-level
    • Source exports non-empty where expected.
    • Mapping and unified outputs produced.
    • Sheet sync ranges targeted expected tabs.
    • record.status.calculated updated for submission rows.
    • Scoring stage completed or intentionally skipped with explicit reason.
    • AIR enrichment stage completed or intentionally skipped with explicit reason.
  • Write-back-level
    • SQL artifact generated every write-back run.
    • Result CSV present for non-dry-run.
    • Aeries/registration ack counts align with result statuses.
    • Any ACTION REQUIRED entries triaged.
  • Communication-level
    • New submissions notification sent when appends > 0.
    • Run report email delivered with status + summary rows.

Failure modes and recovery

  • Google Sheets authentication/permission failure

    • Check service account key file path and readability.
    • Verify sheet sharing to service account email.
    • Confirm sheets.range_* targets valid tabs/ranges.

  • Missing sheet headers/columns

    • Add missing columns to Transfers/config tabs.
    • Re-run; helper stages intentionally skip and warn instead of mutating schema.

  • Aeries write-back ack blocked (STU.TG eligibility)

    • This is intentional.
    • Correct Aeries student tag/enrollment state for affected rows.
    • Re-run write-back stage.

  • NS/registration school routing issues

    • Add/update [aeries.ns_school_code_overrides].
    • Ensure approved/resident school values are canonical.
    • Re-run dry-run first, then full write-back.

  • AIR enrichment low match rate

    • Confirm school_year.next config value.
    • Review Name/DOB formatting and ambiguity warnings.
    • Confirm Transfers has required registration columns.

  • Decision scoring config issues

    • Verify config-dashboard-indicators/cutoffs/keywords/score-bands.
    • If sheets are missing, allow auto-create helper to seed defaults, then rerun.

  • Email/report issues

    • Validate SMTP host/port/user/password/TLS.
    • Confirm report mode and recipient config.
    • Check emails_sent/ campaign artifacts for delivery state.

Security and privacy

  • Treat all operational artifacts (logs, backups, CSV exports) as student-sensitive.
  • Keep credentials in local/private config; do not expose key material in commits.
  • Restrict dashboard update capability to district users with full_access.
  • Maintain strict role scoping for school users via allowed school lists.
  • Use internal-only distribution for Atlas docs due operational and student context.

Open items and improvements

  • Reduce Unknown indicator coverage by tightening Aeries source completeness for remaining signals.
  • Continue refining matching quality for AIR and duplicate transfer detection.
  • Add deeper automated regression tests for helper scripts and dashboard calculations.
  • Expand operational dashboards/reporting for trend analysis without increasing operator overhead.