Project Atlas AetherDev project hub
Atlas project development

District Transfer Tracking

Centralizes, normalizes, and syncs district transfer data into a single Google Sheet for review and downstream reporting.

Playbook available

Operational guidance is available for this system.

Open playbook
Type
System
Lifecycle
Active
Last touched
TBD
Visibility
Public

Purpose

Centralize, normalize, and sync transfer data into a single Google Sheet for staff review and downstream systems.

Current state

Pipeline runs via run.sh to export, map, and sync InformedK12, Jotform, and Aeries data into Google Sheets; Transfers is backed up before sync; logs are rotated; notifications are sent for run reports and new submissions; Apps Script helpers assist with student ID lookup, grade validation, manual entry, and filter view refresh.

Next step

Define and implement a durable canonical key and add QA checks for required fields and duplicate detection.

Interfaces

Inputs
  • InformedK12 responses
  • Jotform responses
  • Aeries STU demographics (elementary + secondary)
  • Manual transfer-in entries in Transfers sheet
  • Config sheet values (email.report_recipients, school_year.current)
Outputs
  • Google Sheet tabs: IK12-Inter, IK12-Intra, JF-Inter, Transfers, Aeries-STU-Demo
  • Transfers sheet backups (CSV)
  • Run logs and email notifications

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Contributes in this stage.

Automation Engines

Not in scope.

Human Interfaces

Contributes in this stage.

Operational Adoption

Not in scope.

Core workflow

  1. Optional auto-update via git if enabled and repo is clean
  2. Acquire lock to prevent concurrent runs
  3. Rotate run logs and Transfers backups
  4. Back up Transfers sheet before changes
  5. Export InformedK12 inter/intra responses
  6. Export Jotform responses
  7. Export Aeries demographics and merge
  8. Normalize each source with csv_mapper
  9. Sync raw tabs and unified Transfers tab via google-sheet-sync
  10. Upload Aeries-STU-Demo
  11. Compute new submissions count and send notification if enabled
  12. Send run report email if enabled
  13. Release lock

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

Source of truth rules

  • Transfers sheet is the unified dataset and includes staff-edited columns
  • Raw tabs (IK12-Inter, IK12-Intra, JF-Inter) are source exports for audit
  • Aeries-STU-Demo reflects the latest SQL export and is used for Apps Script lookups

Data quality checks

  • Apps Script student_id lookup (Aeries match)
  • Apps Script grade validation (Aeries/DOB)
  • Staff review in Transfers with filter views

Safe handling

  • Do not commit tokens, API keys, or service account JSON
  • Use config.toml and environment variables for secrets
  • Restrict sheet sharing to approved staff and service account

Downstream integration map

  • Aeries SIS import
  • Enrollment projection reports

Artifacts

  • run.sh
  • config.toml
  • conf.d/*.json
  • conf.d/*.toml
  • templates/report-email.html
  • templates/new-submissions-email.html
  • logs/run-*.log
  • backups/transfers-*.csv
  • emails_sent/

Operational notes

Constraints and scars

  • Config sheet headers (config.key/config.value) must exist within sheets.range_config for notification recipients.

Reliability posture

Run lock prevents concurrent executions; Transfers sheet is backed up before sync; log rotation and backup retention limit disk growth; run.sh uses strict error handling and optional auto-update on a clean repo.

Observability

  • logs/run-*.log
  • logs/google-sheet-sync.log
  • reporting emails via email_template_mailer
  • new submissions notification emails

Security and privacy

Contains student data; uses API tokens and a Google service account key; do not commit secrets (config.toml/priv_key.json) and restrict sheet access to authorized staff.

Dependencies

Upstream
  • InformedK12 API
  • Jotform API
  • Aeries SQL
  • Google Sheets API
Downstream
  • Aeries SIS import
  • Enrollment projection reports

Ownership

Users

staff

District Transfer Tracking

Architecture & Major Components

  • High-level diagram (text):

    • Trigger (run.sh or cron) -> auto-update + lock + backups -> exports + mapping + sheet sync -> Google Sheet + notifications + reports

  • Entry points:

    • run.sh (pipeline runner)
    • deploy.sh (Apps Script push via clasp)
    • scripts/dev-todo-helper.sh (Dev-ToDo download/commit/update helper)
    • Apps Script menu: Transfer Tools (student ID lookup, grade validation, manual entry, filter view refresh)

  • Top-level folders:

    • bin/: CLI tools (informedk12-sync, jotform-sync, google-sheet-sync, csv_mapper, mssql_to_csv, email_template_mailer)
    • conf.d/: tool configs and mapping files
    • appsscript/: Apps Script project
    • templates/: report email HTML
    • REFERENCE-INFO/: CLI usage references
    • scripts/: operational helpers (Dev-ToDo helper)
    • logs/: run logs and component logs
    • backups/: Transfers sheet backups
    • lock/: runtime lock directory to prevent concurrent runs
    • emails_sent/: outbound email tracking from email_template_mailer
    • temp/: transient CSVs and generated mailer configs

  • Key abstractions:

    • Source exports (CSV) for each upstream system
    • Mapping configs (csv_mapper JSON) to normalize fields into the Transfers schema
    • Sheet sync config (google-sheet-sync TOML) for uploads/downloads
    • Backup and retention policies for the Transfers sheet and run logs
    • Notification and reporting templates (email_template_mailer HTML + CSV merge)
    • Apps Script helpers for validation, manual entry, and filter view maintenance

Setup / Build / Run

  • Build system(s):
    • Bash pipeline (run.sh) with strict error handling
    • Apps Script deployment via clasp (optional)
  • Local run steps:
    • cp config.example.toml config.toml
    • Fill config.toml with API tokens, form IDs, SMTP details, and paths
    • Ensure conf.d/google-sheets-sync-transfers.toml points to the correct sheet ID and service account key
    • Share the Google Sheet with the service account email in conf.d/priv_key.json
    • ./run.sh (runs from repo root; the script self-cds into its directory)
    • Optional Apps Script deploy: ./deploy.sh
  • Runtime dependencies:
    • InformedK12 API token and campaign IDs
    • Jotform API key and form ID
    • Aeries SQL credentials (MSSQL) for elementary and secondary queries
    • Google Sheets service account key (shared with the target sheet)
    • Access to the Google Sheet tabs: IK12-Inter, IK12-Intra, JF-Inter, Transfers, Aeries-STU-Demo, config-grades
    • SMTP credentials for reporting emails (optional)
    • email_template_mailer binary present and executable when reporting is enabled

Operational Notes

  • Full cycle run: execute ./run.sh to optionally auto-update the repo, acquire a lock, rotate logs/backups, back up Transfers, export sources, map data, sync sheet tabs, upload Aeries-STU-Demo, compute new submissions, and send report/notification emails.
  • Common failure modes:
    • Missing required config values in config.toml (tokens, form IDs, SMTP).
    • Lock directory already present from another run (stale lock).
    • Missing or non-executable binaries in bin/.
    • Access issues to Google Sheets or Aeries SQL (service account not shared).
    • Config sheet headers not found within sheets.range_config (notification recipients not detected).
    • Auto-update skipped due to a dirty working tree or detached HEAD.
  • Validation/audit:
    • Staff review in Transfers using filter views.
    • Apps Script helpers for student ID lookup and grade validation.
    • Run logs in logs/ and report emails for step-by-step results.
    • Confirm Transfers backup CSVs exist under backups/ for each run.

Related playbooks