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

System Playbook: District Transfer Tracking

Operational guidance for running the district transfer tracking pipeline.

System map

This playbook supports

District Transfer Tracking

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

Pipeline
Reality Ingestion Canonical Storage Human Interfaces
Domains
SIS Data Reporting Data Integration
Patterns
Extract Export Csv Transform Mapping Layer Sheet Sync Sheet as Ui

Related Atlas entry

Purpose

Centralize, normalize, and sync district transfer data into a single Google Sheet used for review, follow-up, and downstream reporting (Aeries SIS and enrollment projections). The playbook focuses on running the pipeline safely, validating outputs, and responding to common failure modes.

Audience and access

  • Audience:
    • Staff running the pipeline on a scheduled host (cron) or manually.
    • Staff maintaining the Google Sheet and Apps Script helpers.
  • Access needed:
    • InformedK12 API token and campaign IDs.
    • Jotform API key and form ID.
    • Aeries SQL credentials for the elementary and secondary queries.
    • Google Sheets service account access to the target sheet (service account email shared on the sheet).
    • Local access to config.toml and conf.d/google-sheets-sync-transfers.toml.
    • Optional SMTP credentials for reporting emails.

Operational workflow

  1. Configure runtime files:
    • Copy and edit config.toml (tokens, form IDs, reporting settings).
    • Ensure conf.d/google-sheets-sync-transfers.toml points to the correct sheet ID and service account key.
    • Confirm the service account JSON exists at the configured path (default conf.d/priv_key.json).
  2. Verify sheet structure and access:
    • Required tabs: IK12-Inter, IK12-Intra, JF-Inter, Transfers, Aeries-STU-Demo, config-grades.
    • The config sheet range (default config!A1:Z) must include config.key and config.value headers.
    • If new submissions notifications are needed, add email.report_recipients in the config sheet.
  3. Run the pipeline:
    • Execute ./run.sh from the repo (the script self-cds to its directory).
    • If auto-update is enabled, the script will fetch/pull and restart when a fast-forward update is available on a clean repo.
    • The script acquires a lock under lock/ to prevent concurrent runs.
  4. Review outputs:
    • Check logs/run-*.log and logs/google-sheet-sync.log.
    • Confirm backups under backups/ for the Transfers sheet.
    • Verify data in the sheet tabs (raw tabs + Transfers + Aeries-STU-Demo).
  5. Verify emails (if enabled):
    • Run reports are sent to [reporting].recipients.
    • New submissions notifications are sent only when new rows are appended to Transfers.
  6. Use Apps Script helpers (Transfer Tools menu):
    • Populate Student IDs from Aeries (matches by name/DOB).
    • Validate Grades (Aeries/DOB with config-grades cutoffs).
    • Add Manual Transfer (manual entry for transfer-in records).
    • Refresh Transfer Filter Views.

Validation checklist

  • Run log shows Success for each pipeline stage and no unexpected warnings.
  • Transfers backup CSV is written under backups/ for the current run.
  • Source tabs updated: IK12-Inter, IK12-Intra, JF-Inter.
  • Transfers updated and contains new submissions (if any).
  • Aeries-STU-Demo updated after MSSQL exports and merge.
  • Report email sent when enabled; new submissions notification sent only if new rows were appended.
  • Apps Script helpers run without errors when invoked (student ID lookup and grade validation).

Failure modes and recovery

  • Missing config values (tokens, keys, SMTP): update config.toml and rerun.
  • Lock directory already exists:
    • Ensure no other run is active.
    • If stale, remove the lock directory under lock/ and rerun.
  • Missing or invalid service account access:
    • Confirm conf.d/priv_key.json path and permissions.
    • Share the sheet with the service account email.
  • Config sheet headers not found:
    • Ensure config.key and config.value headers exist within sheets.range_config.
    • Place email.report_recipients under config.key for notifications.
  • Aeries export failures:
    • Validate MSSQL credentials and network access.
    • Re-run after restoring connectivity.
  • Auto-update skipped or failed:
    • Ensure the repo is clean and not in detached HEAD.
    • Manually git pull --ff-only if needed, then rerun.

Security and privacy

  • Do not commit tokens, API keys, or service account JSON files.
  • Store secrets in config.toml locally or secure secret storage for automation.
  • Data includes student information; limit access to authorized staff.
  • Audit sheet sharing and service account access regularly.

Open items and improvements

  • Define a durable canonical key for multi-source records.
  • Add required-field checks and duplicate detection.
  • Expand export formats for downstream systems.
  • Add additional automated data quality checks to reduce manual review time.