ODS Data Migration

Importing live data from Colleague's ODS (SQL Server) into the SIS PostgreSQL database.

This page covers the data migration executables. Schema (DDL) migrations are a separate mechanism — see Migrations.

Overview

Three Go executables under sis_backendgo/cmd/ read from the ODS reporting database (ODS_LIVE on SQL Server) and upsert into PostgreSQL:

Executable	Source	What it imports
migrate-static	cmd/migrate-static	Catalog/reference data: bulletins, terms, courses, degrees, majors, programs, minors, graduation dates (8 steps)
migrate-daily	cmd/migrate-daily	Person/student/enrollment data: people, demographics, sections, enrollments, midterm grades, degrees, holds, advisors, auth roles (37 steps)
migrate-requirements	cmd/migrate-requirements	Degree-requirement trees: scope, blocks, items, credits, minimum grades (grades, migration 286), course prerequisites (course-prereqs) (6 steps)

Running locally (Windows)

Use the wrapper. It builds the executable and then runs it, forwarding every remaining flag:

cd sis_backendgo
.\scripts\migrate.ps1 daily -delta
.\scripts\migrate.ps1 daily -h                 # the tool's own flag list
.\scripts\migrate.ps1 static -clean -steps none

Wrapper flag	Effect
-NoBuild	Run the existing binary as-is. For offline work and rapid re-runs against unchanged code — staleness becomes your problem again.
-Fresh	go build -a, for a poisoned build cache. Rarely needed, much slower.

It always builds; there is no freshness check to get wrong. go build is incremental, so an up-to-date binary costs one relink. A build failure aborts before the binary runs — it will never fall through to yesterday's build.

The tool name comes first. .\scripts\migrate.ps1 -steps people binds people to the tool name and fails validation rather than running anything.

Comma lists

-steps people,emails works through the wrapper. Calling the .exe directly, quote it — -steps 'people,emails' — because PowerShell treats an unquoted comma as the array operator and splits the list into separate arguments. Go's flag package then stops parsing at the first non-flag argument and silently discards every flag after it, -since included. The wrapper rejoins them for you.

What it runs underneath

CI and the server use this form directly, and so can you:

cd sis_backendgo
go build -o migrate-daily.exe ./cmd/migrate-daily
go build -o migrate-static.exe ./cmd/migrate-static
go build -o migrate-requirements.exe ./cmd/migrate-requirements

Note the server builds these without the .exe suffix (deploy.ps1, consumed by scripts/migrate-daily.sh). go build -o migrate-daily produces an extensionless binary even on Windows, so don't paste the Linux recipe onto a workstation.

Rebuild before you run

The executables are checked-out binaries, not go run wrappers. A stale binary silently runs old code — a June build once ran for weeks after a schema rename and mis-imported every run, and in September 2026 an August build ran with 148 .go files newer than it, missing every change in the three most recent commits.

On the server this cannot happen: deploy.ps1 probes scripts/check-migrate-binaries.sh and rebuilds before cron runs anything. On Windows the wrapper above closes the same gap. internal/migration/staleness.go remains the backstop for both — it prints a STALE BINARY warning naming the newest file and a copy-pasteable rebuild command. It deliberately warns rather than refuses, because the same code runs under the 06:30 cron where a false positive from a touch or a clock skew would be worse than the problem.

Configuration

Connection settings live in sis_backendgo/.env.migration (ODS host/credentials are required; PostgreSQL settings fall through to .env). Both PG connections are opened with the audit-suppressed DSN so bulk imports don't generate tens of thousands of audit-log rows, and the institution timezone is pinned on every connection so naive timestamps stay consistent.

Full-rebuild run order

scripts/migrate-all.ps1 codifies the from-scratch sequence. It builds all three executables up front, then runs:

migrate-static -clean -steps none     # truncate static tables
migrate-daily  -clean -steps none     # truncate people data (protected users snapshotted)
migrate-static                        # catalog data
migrate-requirements                  # requirement trees
migrate-daily                         # everything else

cd sis_backendgo
.\scripts\migrate-all.ps1 -Yes

-Yes is required, because the first two steps truncate. It stops at the first non-zero exit — the migrate-all.bat it replaced had no error checking, so a failed truncation step was followed by four more running against a half-truncated database. Building all three before any of them runs is deliberate for the same reason: discovering that migrate-requirements does not compile after the truncations have happened leaves the database empty with no way forward.

GPA is not recomputed by migrate-daily. After a full re-import, run cmd/backfill-enrollment-levels (fills academic_level_id on enrollments) and then cmd/recalculate-gpa (per-student recompute through the Go grading service, which is the only place repeat-policy tie-breaking exists).

migrate-daily flags

migrate-daily [-steps s1,s2,...] [-dry-run] [-clean] [-redo s1,...] [-all-students] [-delta] [-since YYYY-MM-DD]

Flag	Effect
-steps	Comma-separated step names, all (default) or none. Step names and order are printed by -h.
-dry-run	Every step's transaction is rolled back; non-transactional writes (post-passes, watermark updates) are skipped. Refused in combination with -clean/-redo, which truncate immediately.
-clean	Truncates all people/role data first (protected users are snapshotted and restored — see below). Resets all delta watermarks.
-redo	Truncates the named steps' target tables, then re-runs those steps. Resets those steps' watermarks.
-all-students	Import all ODS students, not just those with academic credits.
-delta	Incremental run of the hot people-cluster steps (see below). Refused with -clean/-redo.
-since	Manual lower bound for -delta (institution-local date). Requires -delta.

Incremental runs (-delta)

A full migrate-daily run takes ~30+ minutes, most of it full-scanning ODS. -delta runs the hot steps incrementally — see hotSteps in cmd/migrate-daily/main.go for the authoritative list (18 as of Oct 2026): the people cluster (people, races, emails, phones, addresses, identifiers), students and enrollments (all three phases), relationships, proxy_access, legacy_credits, te_credits, student_degrees, instructors, graduation_records, since Aug 2026 ar_charges and test_scores, and since Oct 2026 midterm_grades. Sections and holds are not watermark-capable (their ODS sources carry no change dates) but use diff-based writes on every populated run: full ODS read, one bulk PostgreSQL read, and writes only for rows that actually changed — so they cost seconds either way. Everything else still full-scans.

student_cohorts is the exception that stays a full scan on purpose: it reads ~14,300 rows across two dateless ODS sources — ODS_STUDENT_OTHER_COHORTS (2,259) and the federal cohort on STUDENT_ACAD_LEVELS (~12,000) — and load-and-diffs them in about 8 seconds, less than the watermark bookkeeping would cost. Note it shares records.cohorts and records.student_cohorts with the admin UI, so its deletes are scoped to source = 'ODS' and it has no stepTables entry: -redo student_cohorts must not truncate tables that also hold hand-created cohorts and the classification an administrator applied to the imported ones. It was called student_cohort_groups until migration 185 folded that table into records.cohorts, and 186 dropped it; the old step name still resolves for the sake of existing cron entries and runbooks.

.\scripts\migrate.ps1 daily -delta               # normal incremental run
.\scripts\migrate.ps1 daily -delta -dry-run      # preview, nothing written
.\scripts\migrate.ps1 daily -delta -since 2026-07-25 -steps emails   # re-process a wider window

How the scan is filtered

Each hot step filters its ODS query to people whose Colleague record changed since the step's watermark:

COALESCE(PERSON_CHANGE_DATE, PERSON_ADD_DATE) >= @since

Emails, phones, races and SSNs are multi-values on the PERSON record in Colleague, so changing any of them bumps PERSON_CHANGE_DATE — that's what lets the child steps piggyback on the person date even though their ODS source tables carry no date columns. Addresses (and address phones) additionally OR on ADDRESS.ADDRESS_CHANGE_DATE, because an ADDRESS record can change without any person change. The per-table inventory is documented in sis_backendgo/docs/ods-schema.md under "Change-date columns".

Display names (migration 298). Colleague keeps names beside the legal one. The teachers step stores FAC_NAME, the faculty listing name, on teachers.display_first_name/display_last_name whenever Colleague has one (parsed by parseFacultyName). The preferred_names step (1d, after name_history) turns PERSON_FORMATTED_NAMES ITFN/ITLN/ITDN into each person's current PREFERRED name when it differs from the legal one (composePreferredName; salutations such as "Mrs. Krahn" in ITFN are ignored), writes person_names.source = 'ODS', updates or deletes its own rows as Colleague changes, and never touches a PREFERRED name typed into the SIS (source = 'SIS'). Both read in full every run. Screens use records.instructor_display_name(teacher) (FAC, else preferred, else legal) and core.person_display_name(person) (preferred, else legal); a query that joins person_names on is_current alone now returns one row per name type, so pin it to a type or use the functions.

midterm_grades (step 6b, after enrollments) reads SCS_MID_TERM_GRADE1/SCS_MID_GRADE_DATE1 from STUDENT_COURSE_SEC, matching on SCS_STUDENT_ACAD_CRED = colleague_stc_id. Its delta filter reads cleanup.dbo.STUDENT_COURSE_SEC_CHGDATE/_ADDDATE (ODS_LIVE publishes no change date for this file) and its watermark is observed from cleanup, the same arrangement as test_scores. It writes only rows whose midterm_grade_source is NULL or ODS (migration 292): a midterm a faculty member entered or cleared on the grade roster is SIS and is never touched, and is counted under "SIS-entered midterm grade kept over Colleague's". A full run also clears ODS-sourced midterms Colleague no longer has. It has an empty stepTables entry on purpose — its target is columns on student_section_enrollments, which -redo must never truncate.

test_scores and ar_charges filter through cleanup, not ODS_LIVE. Both were full replaces until Aug 2026 on the strength of an ODS_LIVE-only check for change dates. ODS_LIVE publishes narrowed views: it omits STUDENT_NON_COURSES's ADDDATE/CHGDATE pair entirely, and its AR_INVOICE_ITEMS is 13 columns against cleanup's 32 (no CHGDATE). cleanup has both, at 100% coverage. cleanup is unusable as a data source for AR charges — it carries neither person nor term — but a filter needs only the item id, and person and term still come from ODS_LIVE.

Two rules came out of wiring them, both of which generalise:

• Scope the filter to whatever the step's writes compare across. test_scores resolves competing sittings by highest-score-wins across a person's rows, so its filter is person-scoped; a row-level filter could hand it only the lower of two rows sharing a key and the diff would downgrade the stored score. Same hazard as student_degrees (student-scoped, for is_primary) and proxy_access (pair-scoped).
• Observe the watermark from the source the filter reads — and where a filter unions sources of differing freshness, from the OLDEST of their maxima (observeSourceMinOfMaxes). cleanup trails ODS_LIVE by 1–4 days; a watermark taken from the live maximum would start the next window after rows cleanup had not yet published, skipping them for good. A too-wide window only re-checks idempotent rows. The cost is that test_scores additions appear 1–4 days late in delta runs (deletions are same-day — the reconcile reads live ODS_LIVE); full runs remain the same-day backstop.

Enrollments and students filter through SPT_STUDENT_ACAD_CRED — a rolling ODS extract of STUDENT_ACAD_CRED rows changed since ~2024-01-01 that carries STUDENT_ACAD_CRED_CHGDATE/ADDDATE (the full view has no change date). Enrollments filters all three phases by STUDENT_ACAD_CRED_ID IN (…changed…); students by STC_PERSON_ID, OR'd with the person filter. Because the extract is a rolling subset, each run first checks the delta window is newer than the extract's retention floor (acadCredWindowCovered) and falls back to a full scan otherwise. Enrollments phase 1 also stamps colleague_stc_id onto legacy rows as it touches them (stc_backfilled in the log), so the pre-existing baseline gradually acquires ODS identity.

A changed person's entire email/phone/address set is re-imported, not just the changed value — the writes are idempotent upserts, so this costs nothing and avoids needing per-value change tracking.

Watermarks

Watermarks live in core.migration_watermarks (schema migration 116), one row per hot step:

Column	Meaning
step_name	e.g. emails
last_source_max	MAX(COALESCE(change_date, add_date)) observed in the ODS source during the run
last_run_at / last_run_mode	bookkeeping: when, and full or delta

Three deliberate design points:

• The watermark is source-derived, never wall clock. ODS is itself batch-refreshed from Colleague; a wall-clock watermark could permanently skip rows that land in ODS late with older change dates.
• Reads subtract a 72-hour overlap. Colleague CHANGE.DATE is date-granular (values are midnight-stamped), so an exact boundary would be ambiguous. Re-scanning the margin is harmless — the upserts are idempotent. -since values are used exactly as given, with no overlap.
• Full runs seed watermarks too. Every run of a hot step observes the source max and (on success, outside dry-run) records it. So the bootstrap is just: run a normal full pass, then start using -delta.

A step with no watermark (never run since migration 116, or reset by -clean/-redo) logs a warning and falls back to a full scan. So does a step whose target table is empty — an active window over an empty table would import a sliver and call it done.

Delete reconciliation

A change-date filter cannot see a row that was deleted in Colleague — it simply stops appearing. So each hot step in delta mode additionally pulls a keys-only scan from ODS (identifiers only, cheap) and deletes the PostgreSQL rows whose key is gone. This runs inside the step's transaction, so -dry-run rolls it back, and deletions appear in the summary's Delete column.

Safety rails, in order of importance:

1. people rows are never deleted. A person cascades into students, enrollments and auth_users. Persons present in PG but absent from ODS are counted as needs-attention skips and their IDs logged for manual review. The minimal person rows recreated for protected users are recognized and reported as expected.
2. Only migration-owned rows are candidates. The application's repositories stamp created_by on every insert; the migration never does. Reconciliation only deletes rows where created_by IS NULL, so anything a user created in SIS survives. Exception: holds — the app's hold-create path also leaves created_by NULL, so ownership there is marked by created_by_system = 'ODS_MIGRATION' instead.
3. Only for persons ODS still knows. If a person is missing from ODS entirely, their child rows are left alone (that's case 1's problem, not a deletion signal).
4. The identifiers step reconciles only the SSN identifier type — other identifier types belong to other writers.

Per-step deletion policy for the newer hot/diff steps:

• relationships — hard-deletes migration-owned rows whose RELATIONSHIP_ID (stored in source_id) is gone from ODS, keyed RS_ID1|RELATIONSHIP_ID. Runs on every populated run, full or delta — with no change date there is no window to gate on.
• holds — hard-deletes migration-owned rows (created_by_system = 'ODS_MIGRATION') whose restriction is gone from XODS_STUDENT_RESTRICTIONS, keyed student|hold_type|start_date, computed from the diff pass with no second ODS scan. Runs on every populated run. App-created holds are never touched. ODS wins over manual clears: a hold released in SIS is re-activated (and its cleared_* fields wiped) if Colleague still shows the restriction active — releases done in Colleague arrive as end dates, not clears.
• test_scores — hard-deletes composite rows, then section rows under surviving composites (composites first, because sections cascade on composite delete and the other order double-counts). Ownership here is not a created_by question: MigrateTECredits writes applicant_test_scores too, through the same unique key, and neither step stamps created_by (all 81,693 rows are NULL). The discriminator is notes IS NULL — te_credits sets notes from ISNULL(STC_TITLE, '') on every insert, test_scores never writes it at all. Without that guard a reconcile would delete te_credits' 33,815 rows, 41% of the table. Fail-safe in one direction: a user typing a note onto a migration row makes it stop being a deletion candidate. The key scan reads live ODS_LIVE and must build test_date exactly as the step does (testScoreDateKey: missing dates become 2000-01-01, rendered as a plain YYYY-MM-DD string) — a divergence there would read live rows as orphans.
• ar_charges — hard-deletes a term's rows whose invoice item is gone, from a keys-only scan. colleague_ar_charges has no created_by; the table is entirely migration-owned. Delta runs upsert on ar_invoice_item_id (unique globally, not per term, which is also what makes an item moving between terms just update its term_id); full runs keep the wholesale per-term replace. Both the changed-row scan and the key scan are hoisted out of the term loop — AR_INVOICE_ITEMS has no usable index on INV_TERM, so a per-term query full-scans 3.56M rows regardless, and doing that twice per term across ~49 terms was minutes of the run.
• students — report-only: students whose person has vanished from ODS entirely. (The migration creates students from many sources — enrollments, transfer/legacy credits, test scores — so no student-shaped ODS universe is exhaustive; comparing against ODS_STUDENTS produced ~69k false positives on dev.)
• instructors — hard-deletes source = 'ODS' assignments Colleague no longer lists, on sections Colleague owns (colleague_section_id stamped). Before migration 293 the step only added and updated, so a replaced instructor stayed on the section at 100% beside the real one and won the reports' tie-break (73 stale rows in 2026 terms, mostly the "Staff" placeholder). Never touched: source = 'SIS' rows (added on the SIS screens — the column defaults to SIS, the import writes ODS), and sections planned in the SIS (colleague_section_id NULL). A section with any Colleague faculty member who has no SIS teacher is skipped and warned about. The pair universe is a keys-only scan of ODS_COURSE_SEC_FACULTY, so this runs on every populated run, delta or full.
• sections — deletes Colleague-owned sections Colleague no longer has (Oct 2026, owner decision: what Colleague deletes, the SIS deletes). Candidates are only sections stamped with a colleague_section_id whose id this run's ODS_COURSE_SECTIONS scan did not return — a section planned in the SIS has no id and is never a candidate. A candidate is deleted only if nothing student-facing or financial references it (enrollments, plans, waitlists, registration and billing items, grade releases, LMS shells, waivers); otherwise it is kept and counted as needs-attention. Its instructors, meeting schedule and class links go with it. Runs inside the sections transaction behind a savepoint, so an unexpected reference costs the delete, not the import; skipped if any ODS row failed to scan or the scan returned fewer than half the stamped ids. Unstamped sections absent from ODS (created before migration 293) are still only reported, for past terms, as before; scripts/oneoff/2026-10-orphan-sections.sql (report by default, -v apply=1 to apply) cleared the ones that predate the rule. The same 2,000-row cap as the enrollments pass applies.
• Sections are identified by Colleague's COURSE_SECTIONS_ID (course_sections.colleague_section_id, migration 293), stamped by the sections step — matched by course/term/code the first time, by id from then on. A section Colleague renames (NURS-110-01 → NURS-110-WEB, Aug 2026) is renamed in place, so its enrollments, instructors and schedule stay on it; if another SIS section already holds the new code, both are kept and the conflict is reported. A rename never deletes or cancels a section; only the vanished-section pass above deletes, and only sections Colleague no longer has. The 14,030 converted-history sections (terms 84–02) whose Colleague id is a synthesized name (99/WI_SPPA_296_01) are left unstamped and keep matching by code.
• Enrollments are placed by Colleague's link, not STC_SECTION_NO. STUDENT_ACAD_CRED keeps the section number a credit was created under; 2,339 I/R credits disagree with the section their STUDENT.COURSE.SEC record points at (SCS_COURSE_SECTION). Phase 1 uses that link when it names a stamped section of the same course and term, and falls back to the section number otherwise (no link — about 529k older credits — or a link to another course, which would change the transcript).
• enrollments — deletes student_section_enrollments and transfer_credits rows whose colleague_stc_id no longer exists in STUDENT_ACAD_CRED (vanished.go), on every populated run. Colleague deletes the credit when a student drops before the term; on 2026-10-27 that was 294 enrollments (current/future terms, all ungraded) still listed as ENROLLED, and 5 transfer credits Colleague had re-entered under new ids, so the SIS counted them twice. Kept and reported instead: an enrollment with a final or midterm grade. An enrollment pushed to the LMS is deleted too: in the same transaction its lms.enrollment_links row is detached (source_enrollment_id → NULL; the FK is NO ACTION) and a SYNC_ENROLLMENT job is queued for that shell/person/role, which finds the student no longer expected and concludes the LMS enrollment as for a drop (193 of the 294). Rows with no colleague_stc_id (SIS registrations, unstamped legacy rows) are never candidates. Refused outright — nothing deleted, a warning raised — if either table would lose more than 2,000 rows in one run, which is what a short ODS read looks like. A failure is a warning, not a failed import.
• graduation_records — one record per ACAD_CREDENTIALS_ID, stored in graduation_records.colleague_credential_id (migration 301). Before 301 the step keyed on (student, degree, graduation_date_id), but a double major is two credentials for one degree and commencement, so the two overwrote one row in turn and ~50 graduates flipped majors on every run. The credential is now the key, so a credential Colleague moves to another degree or ceremony updates its record in place. A row with no credential (created by a confirm in the SIS, or imported before 301) is adopted by the first credential with the same student, degree and graduation date, preferring the row that already has that credential's major. confirmed_date is ACAD_DEGREE_DATE with no fallback (ACAD_CREDENTIALS_ADDDATE predates commencement), and is never written on a row with confirmed_by set. BSN and BSW credentials with no major resolve to the BS.NURS and BSW majors (degreeImpliedMajors); every other unresolved major stays NULL and is counted per degree:program/major in the step's unmapped-major log. Deletes: on a full run only, rows still unclaimed after every credential has been offered (pre-301 strays whose credential Colleague moved) are deleted unless the SIS created or confirmed them (confirmed_by or application_id set). Delta runs see only recent credentials, so they delete nothing here.
• student_campuses — never deletes. Rows are keyed on (student_id, campus_id, start_date) from ODSSTUHOMELOCATIONS; each time the step runs, a matching row's is_primary (from STU_CURRENT_HOME_LOCATION) and end_date are rewritten from Colleague. An assignment a registrar added on the student's Campuses tab with a start date Colleague does not have is left alone, but a change to the primary flag or end date of an imported row lasts only until the next run. students.primary_campus_id is set from the current home location and never cleared.

What delta does not cover

• Section renames reach enrollments on full runs only. A rename changes the COURSE.SECTIONS record, not the student's credit, so a delta enrollments run never re-reads those credits. The sections step follows the rename on every run; the students move with the next full run, which scripts/records/crontab.example schedules every Sunday (-steps sections,enrollments,instructors, before the deltas). If the section had a Canvas course, see scripts/oneoff/2026-10-orphan-sections.sql for how its course link was re-pointed the first time — the importer does not move LMS shells.
• Supplemental people (~4 corp-flagged students sourced from the cleanup database) have no change dates; their demographic updates arrive on full runs only.
• Anything the change-date logic could theoretically miss (e.g. an ODS refresh delayed beyond the 72h overlap). Run a plain full pass periodically as the backstop — it reconverges everything and re-seeds the watermarks.

Protected users (-clean / -redo people)

TRUNCATE people CASCADE unavoidably empties auth_users and user_roles through the FK chain. Before any such truncate, every auth user with a real password is snapshotted (hash, flags, roles); the accounts are re-inserted in the same transaction as the truncate (so a crash can't lose an admin), re-linked to their re-imported person rows at the end of the run, and verified byte-for-byte afterward. A failure in that verification fails the run loudly.

Reading the output

Each run ends with a summary table (Insert | Update | Delete | Skip | Error | Warn per step) plus two important follow-up sections:

• Skips needing attention — data present in ODS that did not import (missing FK, unresolvable code). These are the ones to investigate.
• Unclassified skips — steps still calling the bare skip counter without a reason; neither confirmed benign nor flagged.

Historical skip forensics and known pitfalls (unverified code maps, the enrollments phase-3 duplicate issue, the dry-run-committed-everything defect) are in sis_backendgo/MIGRATION_SKIP_ANALYSIS.md.

Troubleshooting

Symptom	Likely cause
Delta run imports 0 rows but ODS clearly changed	Watermark ahead of the data (e.g. restored an older DB dump under a newer ledger). Run with -since, or delete the step's row from core.migration_watermarks to force a full scan.
-delta requires core.migration_watermarks	Schema migration 116 not applied — run .\scripts\db-migrate.ps1 -Apply.
Steps behave like old code	Stale binary. Run it through .\scripts\migrate.ps1, which always builds first. A bare .exe run only warns (STALE BINARY) and carries on.
Watermark save warning at end of step	Non-fatal: data committed, watermark didn't advance; the next delta run just re-processes the same window.
Huge unexpected Delete count in a delta dry-run	Investigate before running for real — usually the baseline is much older than the ODS state, so the deletions are real but numerous. (A completely empty ODS key set is refused with a warning rather than treated as mass deletion.)