SIS Documentation

Appearance

LMS Integration Subsystem — Design ​

Status: implemented — all five phases were built in August 2026; see Appendices B–E for what each phase delivered. This document remains the design reference for the subsystem: the schema, the connector definition format, the template engine and the sync engine's rules. It is not the setup guide — for that, see Setting Up LMS Integration.

Read the phase plan in §13 as history rather than as a plan. The header used to say "draft for review — no code has been written", which stopped being true on 2026-08-21 and stayed on the page for a fortnight.

Scope: provisioning and synchronization of course shells, instructor assignments, and student enrollments from sdasis to external LMSs (Canvas, Moodle, D2L Brightspace out of the box), SIS CSV export for any LMS, and optional grade passback. New LMS types are definable entirely through configuration.

1. Overview and goals ​

sdasis becomes the system of record that pushes academic structure into one or more LMS instances:

  • Course shells created (or adopted) per section — or per cross-listed group of sections — at a policy-driven time before term start.
  • Enrollments (instructors, TAs, students) synced event-driven as registration changes, with a scheduled reconciliation pass as the correctness backstop.
  • SIS CSV export as a first-class bulk path for any LMS: per-instance export runs with selectable datasets (terms, courses/sections, users, enrollments), full-snapshot or delta, downloadable always and API-uploaded where the connector defines it (Canvas SIS Import).
  • Grade passback (optional, per-instance flag) as just another operation set.

The defining constraint: an LMS type is data, not code. A connector definition — auth strategy (one of four coded strategies), operations (HTTP method, URL/header/body templates, response mapping, error mapping, pagination, rate limits), and CSV export profiles — lives in the database and is editable in the admin UI. Adding a fourth LMS requires writing a connector definition, not Go.

1.1 Relationship to the existing LMS subsystem (migration 027) ​

A config-only LMS subsystem already exists: records.lms_platforms, lms_platform_urls, lms_platform_campuses, lms_credentials (plaintext secrets despite the migration comment), lms_user_tokens, lms_sync_log, lms_enrollment_mapping, four lookup_lms_* tables, the Go CRUD domain internal/domain/lms/, and the legacy LMSManagementView.vue. It has no sync engine, no connector framework, no templates, and no encryption.

Decision (confirmed with the project owner): replace it wholesale.

Legacy objectFate
lms_platforms + lms_platform_urls (API/BASE)Migrated → lms.instances (connector type from lookup_lms_platform_type.code, base URL from the API/BASE URL rows)
lms_platform_campusesMigrated → lms.assignment_rules (campus grain)
lms_credentialsMetadata migrated → lms.instance_credentials as inactive sentinel rows; secrets (which are plaintext today) re-encrypted by a one-shot Go command, then activated
course_sections.lms_platform_id / lms_course_idMigrated → adopted lms.course_shells + lms.shell_sections (the legacy partial unique index guarantees 1:1, so each becomes a one-section shell with that section primary); columns then dropped
lms_enrollment_mappingMigrated → lms.enrollment_links + lms.user_mappings (FK to enrollments verified intact — Postgres tracked the 083 column rename)
lms_user_tokens, lms_sync_log, lookup_lms_*Dropped (sync log was never written; tokens belong to the new token cache)
internal/domain/lms/, LMSManagementView.vue, lms.api.ts + types + both admin.lmsManagement/admin.lMSManagement i18n branchesDeleted in phase 4, after the new subsystem covers the surface
ADMIN.LMS permission nodeRetained as the parent menu node; four child nodes added (§10)

2. Schema ​

All new tables live in a new lms PostgreSQL schema (the codebase is already multi-schema: core, records, studentfinance, …; a fresh namespace avoids collision with the legacy records.lms_* tables during the transition).

Conventions followed: PKs named {table}_id; audit quad (created_at/by, updated_at/by) on admin-managed tables; status/kind columns are CHECK-constrained VARCHAR codes, not lookup_* tables — these codes are engine-internal (Go switches on them) and are not admin-extensible reference data. Timing columns on engine tables are deliberately TIMESTAMPTZ (an exception to the codebase's without-tz convention, because job scheduling must be unambiguous across DST transitions; noted here so it survives review).

2.1 Connector layer ​

sql
CREATE TABLE lms.connector_types (
    connector_type_id   SERIAL PRIMARY KEY,
    code                VARCHAR(50) NOT NULL UNIQUE,   -- CANVAS, MOODLE, BRIGHTSPACE, ...
    name                VARCHAR(200) NOT NULL,
    description         TEXT,
    auth_strategy_code  VARCHAR(40) NOT NULL CHECK (auth_strategy_code IN
                          ('BEARER','OAUTH2_CLIENT_CREDENTIALS','QUERY_API_KEY','D2L_OAUTH2')),
    pagination_style    VARCHAR(30) NOT NULL DEFAULT 'NONE' CHECK (pagination_style IN
                          ('LINK_HEADER','OFFSET_PARAM','BOOKMARK','NONE')),
    default_rate_limit_rps NUMERIC(6,2) NOT NULL DEFAULT 5,
    default_rate_burst  INT NOT NULL DEFAULT 10,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    -- audit quad
);

CREATE TABLE lms.connector_operations (      -- head rows: identity + listing
    operation_id        SERIAL PRIMARY KEY,
    connector_type_id   INT NOT NULL REFERENCES lms.connector_types,
    operation_code      VARCHAR(60) NOT NULL,
    description         TEXT,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    -- audit quad
    UNIQUE (connector_type_id, operation_code)
);

CREATE TABLE lms.operation_versions (        -- IMMUTABLE: rows are never updated
    version_id          SERIAL PRIMARY KEY,
    operation_id        INT NOT NULL REFERENCES lms.connector_operations,
    instance_id         INT NULL REFERENCES lms.instances,  -- NULL = type-level default;
                                                            -- non-NULL = per-instance override
    version_no          INT NOT NULL,
    definition          JSONB NOT NULL,                     -- §3: the full operation definition
    effective_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    notes               TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by          VARCHAR(100)
);
CREATE UNIQUE INDEX uq_operation_versions
    ON lms.operation_versions (operation_id, COALESCE(instance_id, 0), version_no);

Storage decision (delegated decision 4): hybrid. One row per operation (listable, gate-able, seedable) with the definition internals as a single JSONB blob. Normalizing method/URL/headers/ body/mappings into six sub-tables would make versioning, diff, and rollback dramatically harder; the admin UI edits a definition as one document anyway. Versioning falls out of the same mechanism:

  • History = the rows. Diff = two definition blobs compared client-side. Rollback = insert a new version copying an old blob. Effective date = effective_at.
  • Per-instance overrides reuse the table via nullable instance_id. Resolution: highest version_no with effective_at <= now(), preferring instance_id = X rows over instance_id IS NULL. An override is a full definition (the editor seeds it by copying the current type default) — no deep-merge semantics, which are a debugging tarpit.

Server-side validation on save: JSON-schema check of the definition shape, json.Valid on the body skeleton, template-parse of every template string, dot-path syntax check of every extract expression. Invalid definitions are rejected with errors.NewValidationError — nothing is saved blind (§9).

2.2 Instances, credentials, mappings ​

sql
CREATE TABLE lms.instances (
    instance_id         SERIAL PRIMARY KEY,
    connector_type_id   INT NOT NULL REFERENCES lms.connector_types,
    code                VARCHAR(50) NOT NULL UNIQUE,
    name                VARCHAR(200) NOT NULL,
    base_url            VARCHAR(2000) NOT NULL,
    is_sandbox          BOOLEAN NOT NULL DEFAULT FALSE,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    dry_run             BOOLEAN NOT NULL DEFAULT FALSE,   -- log would-be requests, send nothing
    grade_passback_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    rate_limit_rps      NUMERIC(6,2) NULL,                -- NULL = connector-type default
    rate_burst          INT NULL,
    settings            JSONB,                            -- instance-scoped template vars
    -- audit quad
);

CREATE TABLE lms.instance_credentials (
    credential_id       SERIAL PRIMARY KEY,
    instance_id         INT NOT NULL REFERENCES lms.instances,
    purpose             VARCHAR(20) NOT NULL CHECK (purpose IN ('API','OAUTH')),
    client_id           VARCHAR(500),                     -- not secret
    secret_ciphertext   BYTEA NOT NULL,                   -- "v1" || key_id || nonce || ct  (§4)
    enc_key_id          VARCHAR(40) NOT NULL,             -- duplicated for rotation audits
    token_url           VARCHAR(2000),
    scopes              VARCHAR(1000),
    rotated_from_credential_id INT NULL REFERENCES lms.instance_credentials,
    activated_at        TIMESTAMPTZ,
    retired_at          TIMESTAMPTZ,
    is_active           BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(), created_by VARCHAR(100)
);

CREATE TABLE lms.instance_access_tokens (   -- OAuth token cache, DB-backed so replicas share
    instance_id         INT PRIMARY KEY REFERENCES lms.instances,
    token_ciphertext    BYTEA NOT NULL,
    expires_at          TIMESTAMPTZ NOT NULL,
    obtained_at         TIMESTAMPTZ NOT NULL
);

CREATE TABLE lms.instance_term_mappings (
    instance_term_mapping_id SERIAL PRIMARY KEY,
    instance_id INT NOT NULL REFERENCES lms.instances,
    term_id     INT NOT NULL REFERENCES records.academic_terms,
    lms_term_id VARCHAR(200) NOT NULL,
    -- audit quad
    UNIQUE (instance_id, term_id)
);
-- same shape:
--   lms.instance_org_unit_mappings (org_unit_id → core.org_units, lms_account_id)
--   lms.instance_role_mappings    (role_key CHECK IN ('STUDENT','INSTRUCTOR','TA'), lms_role)
  • Credential rotation without downtime: a new credential row is created alongside the active one (rotated_from_credential_id chains them); a test-connection call validates it; activation flips is_active between the two rows in one transaction; the old row gets retired_at. In-flight jobs re-read the active credential per attempt, so no restart is needed.
  • Token cache is DB-backed (not memory-only) so multiple API replicas share tokens; refresh takes SELECT … FOR UPDATE on the row to prevent a token-request stampede.
  • Role mapping is a closed three-key set. STUDENT maps enrolled students; INSTRUCTOR maps section_instructors rows with can_enter_grades = true; TA maps those with can_enter_grades = false. can_enter_grades is preferred over instructor_role_id because it is the flag the SIS actually enforces on and is rewritten from ODS each run.
  • Missing mappings fail cleanly: provisioning a shell whose term or resolved org-unit has no mapping for the target instance fails the job with error class MAPPING_MISSING and a message naming the exact mapping to add; the instance screen surfaces unmapped values (§10).

2.3 Routing, shells, policy ​

sql
CREATE TABLE lms.assignment_rules (        -- which instance a section provisions to
    rule_id      SERIAL PRIMARY KEY,
    instance_id  INT NOT NULL REFERENCES lms.instances,
    org_unit_id  INT NULL REFERENCES core.org_units,
    campus_id    INT NULL REFERENCES core.campuses,
    priority     INT NOT NULL DEFAULT 100,
    is_active    BOOLEAN NOT NULL DEFAULT TRUE,
    -- audit quad
    CHECK (org_unit_id IS NULL OR campus_id IS NULL)   -- one grain per rule; (NULL,NULL) = default
);

CREATE TABLE lms.section_overrides (
    section_id          INT PRIMARY KEY REFERENCES records.course_sections,
    instance_id         INT NULL REFERENCES lms.instances,   -- force routing
    provisioning_exempt BOOLEAN NOT NULL DEFAULT FALSE,      -- opt this section out entirely
    notes               TEXT,
    -- audit quad
);

CREATE TABLE lms.course_shells (
    shell_id        SERIAL PRIMARY KEY,
    instance_id     INT NOT NULL REFERENCES lms.instances,
    term_id         INT NOT NULL REFERENCES records.academic_terms,
    lms_course_id   VARCHAR(200) NULL,      -- set by response mapping, or by adoption
    status_code     VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (status_code IN
                      ('PENDING','PROVISIONING','ACTIVE','CONCLUDED','FAILED')),
    is_adopted      BOOLEAN NOT NULL DEFAULT FALSE,
    enrollment_sync_started_at TIMESTAMPTZ NULL,   -- gate for event-driven enrollment jobs
    name_snapshot   VARCHAR(500),           -- what we sent (naming templates, §5)
    code_snapshot   VARCHAR(200),
    -- audit quad
);
CREATE UNIQUE INDEX uq_course_shells_lms_id
    ON lms.course_shells (instance_id, lms_course_id) WHERE lms_course_id IS NOT NULL;

CREATE TABLE lms.shell_sections (
    shell_section_id SERIAL PRIMARY KEY,
    shell_id    INT NOT NULL REFERENCES lms.course_shells,
    section_id  INT NOT NULL REFERENCES records.course_sections,
    is_primary  BOOLEAN NOT NULL DEFAULT FALSE,
    added_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    removed_at  TIMESTAMPTZ NULL
);
CREATE UNIQUE INDEX uq_shell_sections_live_section
    ON lms.shell_sections (section_id) WHERE removed_at IS NULL;
CREATE UNIQUE INDEX uq_shell_sections_one_primary
    ON lms.shell_sections (shell_id) WHERE is_primary AND removed_at IS NULL;

Cross-listing. Many shell_sections → one shell, with exactly one live primary (enforced by partial unique index). The grouping key at provisioning time is records.classes.class_id — the shared-schedule container that actually carries co-meeting in this schema — not the is_cross_listed flag, which the ODS importer hardcodes to false. Where the connector type defines Canvas-style section objects (a create_section operation), the provisioner creates one LMS section per member sdasis section inside the shell; otherwise members share the flat course. This replaces course_sections.lms_platform_id/lms_course_id, whose partial unique index structurally forced one shell per section and made cross-listing impossible.

Adopt-existing. Creating a shell with is_adopted = TRUE and an admin-supplied lms_course_id skips create_course entirely; enrollment sync and reconciliation treat it identically from then on. This is the onboarding-mid-stream path.

sql
CREATE TABLE lms.sync_policy (             -- singleton: this is a single-institution deployment
    policy_id   INT PRIMARY KEY CHECK (policy_id = 1),
    provisioning_mode       VARCHAR(20) NOT NULL CHECK (provisioning_mode IN ('REQUIRED','OPTIONAL')),
    provision_trigger       VARCHAR(20) NOT NULL CHECK (provision_trigger IN
                              ('IMMEDIATE','DAYS_BEFORE_TERM','MANUAL')),
    provision_days_before   INT NOT NULL DEFAULT 30,      -- may be negative (= after term start)
    enrollment_sync_trigger VARCHAR(20) NOT NULL CHECK (enrollment_sync_trigger IN
                              ('WITH_PROVISION','DAYS_BEFORE_TERM','MANUAL')),
    enrollment_days_before  INT NOT NULL DEFAULT 7,
    conclude_policy         VARCHAR(20) NOT NULL CHECK (conclude_policy IN ('CONCLUDE','LEAVE_OPEN')),
    conclude_days_after     INT NOT NULL DEFAULT 30,
    drift_missing_enrollment VARCHAR(12) NOT NULL DEFAULT 'FLAG' CHECK (... IN ('AUTO_REPAIR','FLAG','IGNORE')),
    drift_extra_enrollment   VARCHAR(12) NOT NULL DEFAULT 'FLAG' ...,
    drift_missing_shell      VARCHAR(12) NOT NULL DEFAULT 'FLAG' ...,
    -- audit quad
);

The requirements say "per-institution policy"; sdasis is a single-institution deployment (no institutions table anywhere — campuses are the multi-site axis), so policy is a singleton row in its own table rather than scattered sys_institution_settings keys. That keeps it one ALTER TABLE ADD COLUMN institution_id away from multi-tenant, which is the cheapest honest version of "tenant-ready". Per-section override of triggers = section_overrides + provisioning_exempt; a per-org-unit policy layer can be added later as a sparse override table without changing the engine.

2.4 Engine tables ​

sql
CREATE TABLE lms.sync_jobs (
    job_id        BIGSERIAL PRIMARY KEY,
    kind          VARCHAR(40) NOT NULL,     -- CREATE_COURSE, ENSURE_USER, ENROLL, UNENROLL,
                                            -- UPDATE_ENROLLMENT, CONCLUDE_COURSE, POST_GRADE,
                                            -- RECONCILE_SHELL, EXPORT_GENERATE, EXPORT_UPLOAD, EXPORT_POLL
    instance_id   INT NOT NULL REFERENCES lms.instances,
    shell_id      INT NULL REFERENCES lms.course_shells,
    section_id    INT NULL,
    subject_person_id UUID NULL,
    enrollment_id UUID NULL,
    export_run_id BIGINT NULL,
    dedupe_key    VARCHAR(200) NOT NULL,
    payload       JSONB,
    status        VARCHAR(12) NOT NULL DEFAULT 'PENDING' CHECK (status IN
                    ('PENDING','RUNNING','DONE','DEAD','CANCELLED')),
    priority      SMALLINT NOT NULL DEFAULT 100,
    run_after     TIMESTAMPTZ NOT NULL DEFAULT now(),
    attempts      INT NOT NULL DEFAULT 0,
    max_attempts  INT NOT NULL DEFAULT 8,
    locked_by     VARCHAR(100) NULL,
    locked_at     TIMESTAMPTZ NULL,
    last_error    TEXT,
    last_error_class VARCHAR(30),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at   TIMESTAMPTZ NULL
);
CREATE UNIQUE INDEX uq_sync_jobs_dedupe ON lms.sync_jobs (dedupe_key)
    WHERE status IN ('PENDING','RUNNING');
CREATE INDEX ix_sync_jobs_claim ON lms.sync_jobs (priority, run_after) WHERE status = 'PENDING';
CREATE INDEX ix_sync_jobs_shell ON lms.sync_jobs (shell_id);
CREATE INDEX ix_sync_jobs_instance_status ON lms.sync_jobs (instance_id, status);

CREATE TABLE lms.integration_log (          -- one row per HTTP attempt (incl. retries, pages,
    log_id        BIGSERIAL PRIMARY KEY,    --  token fetches, dry-run renders)
    job_id        BIGINT NULL,
    instance_id   INT NOT NULL,
    operation_code VARCHAR(60),
    http_method   VARCHAR(8),
    url           TEXT,                     -- secret query params redacted AT WRITE TIME
    status_code   INT NULL,
    error_class   VARCHAR(30) NULL,
    is_dry_run    BOOLEAN NOT NULL DEFAULT FALSE,
    request_headers JSONB,                  -- auth headers redacted at write time
    request_body  TEXT,                     -- truncated to 16 KB
    response_body TEXT,                     -- truncated to 16 KB
    duration_ms   INT,
    attempted_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_integration_log_instance ON lms.integration_log (instance_id, attempted_at DESC);
CREATE INDEX ix_integration_log_job ON lms.integration_log (job_id);

CREATE TABLE lms.drift_findings (
    finding_id    BIGSERIAL PRIMARY KEY,
    instance_id   INT NOT NULL, shell_id INT NULL, section_id INT NULL,
    finding_code  VARCHAR(40) NOT NULL,     -- MISSING_ENROLLMENT, EXTRA_ENROLLMENT, MISSING_SHELL,
                                            -- ENROLLMENT_STATE_MISMATCH, COURSE_META_MISMATCH,
                                            -- UNROUTED_SECTION, SPLIT_CROSSLIST, MAPPING_MISSING
    subject_person_id UUID NULL,
    details       JSONB,
    detected_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    resolved_at   TIMESTAMPTZ NULL,
    resolution_code VARCHAR(20) NULL CHECK (resolution_code IN ('AUTO_REPAIRED','ACKNOWLEDGED','IGNORED')),
    resolved_by   VARCHAR(100) NULL
);

CREATE TABLE lms.user_mappings (
    mapping_id   SERIAL PRIMARY KEY,
    instance_id  INT NOT NULL REFERENCES lms.instances,
    person_id    UUID NOT NULL REFERENCES core.people,
    lms_user_id  VARCHAR(200) NOT NULL,
    matched_by   VARCHAR(20) NOT NULL CHECK (matched_by IN ('SIS_ID','EMAIL','USERNAME','CREATED')),
    -- audit quad
    UNIQUE (instance_id, person_id)
);

CREATE TABLE lms.enrollment_links (
    link_id      BIGSERIAL PRIMARY KEY,
    shell_id     INT NOT NULL REFERENCES lms.course_shells,
    person_id    UUID NOT NULL REFERENCES core.people,
    role_key     VARCHAR(12) NOT NULL CHECK (role_key IN ('STUDENT','INSTRUCTOR','TA')),
    source_enrollment_id UUID NULL
        REFERENCES records.student_section_enrollments (student_section_enrollment_id),
    lms_enrollment_id VARCHAR(200) NULL,
    state_code   VARCHAR(12) NOT NULL DEFAULT 'ACTIVE' CHECK (state_code IN
                   ('ACTIVE','CONCLUDED','REMOVED')),
    last_synced_at TIMESTAMPTZ,
    -- audit quad
    UNIQUE (shell_id, person_id, role_key)
);

CREATE TABLE lms.passback_watermarks (
    instance_id  INT PRIMARY KEY REFERENCES lms.instances,
    last_log_id  BIGINT NOT NULL,           -- records.grade_change_log.log_id
    updated_at   TIMESTAMPTZ NOT NULL
);

CREATE TABLE lms.sync_runs (               -- photosync-style run records for the scanners
    run_id      BIGSERIAL PRIMARY KEY,
    run_kind    VARCHAR(30) NOT NULL,       -- PROVISION_SCAN, RECONCILE, PASSBACK_SCAN, REAPER, RETENTION
    started_at  TIMESTAMPTZ NOT NULL, finished_at TIMESTAMPTZ NULL,
    status      VARCHAR(12) NOT NULL,       -- RUNNING/SUCCESS/FAILED/SKIPPED
    stats       JSONB, error TEXT
);

enrollment_links is keyed by (shell, person, role) rather than the enrollment UUID because instructor/TA enrollments have no student_section_enrollments row, and (student, section) is not unique in this schema (migration 077 relaxed it for legacy data) — person-keyed links absorb duplicate legacy enrollment rows cleanly. source_enrollment_id is populated for students.

2.5 SIS CSV export tables ​

sql
CREATE TABLE lms.export_runs (
    export_run_id BIGSERIAL PRIMARY KEY,
    instance_id   INT NOT NULL REFERENCES lms.instances,
    mode          VARCHAR(8) NOT NULL CHECK (mode IN ('FULL','DELTA')),
    term_ids      INT[] NOT NULL,
    datasets      VARCHAR(20)[] NOT NULL,   -- subset of TERMS/COURSES/SECTIONS/USERS/ENROLLMENTS
    status        VARCHAR(16) NOT NULL DEFAULT 'PENDING' CHECK (status IN
                    ('PENDING','RUNNING','GENERATED','UPLOADING','IMPORT_PENDING',
                     'IMPORTED','FAILED','CANCELLED')),
    row_counts    JSONB,                    -- {"users": 812, "enrollments": 5301, ...}
    lms_import_id VARCHAR(200) NULL,        -- from the upload operation's response mapping
    requested_by  VARCHAR(100),
    started_at TIMESTAMPTZ, finished_at TIMESTAMPTZ,
    -- audit quad
);
CREATE UNIQUE INDEX uq_export_runs_one_active ON lms.export_runs (instance_id)
    WHERE status IN ('PENDING','RUNNING','UPLOADING','IMPORT_PENDING');

CREATE TABLE lms.export_files (
    export_file_id BIGSERIAL PRIMARY KEY,
    export_run_id  BIGINT NOT NULL REFERENCES lms.export_runs,
    dataset        VARCHAR(20) NOT NULL,
    filename       VARCHAR(200) NOT NULL,
    row_count      INT NOT NULL,
    byte_size      BIGINT NOT NULL,
    sha256         CHAR(64) NOT NULL,
    storage_path   VARCHAR(1000) NOT NULL   -- under FILE_STORAGE_PATH; never publicly addressable
);

CREATE TABLE lms.export_watermarks (
    instance_id INT NOT NULL,
    dataset     VARCHAR(20) NOT NULL,
    last_source_max TIMESTAMPTZ NOT NULL,   -- max SOURCE updated_at observed, minus overlap on read
    updated_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (instance_id, dataset)
);

The partial unique index on export_runs enforces the requirement directly: one export at a time per instance (a second request 409s). Multiple instances can each have their own active run.

2.6 Audit posture ​

The migration-081 trigger system audits configuration; purpose-built history tables carry the action record. Concretely:

Audited (enrolled in core.audit_table_config)Never audited (hard denylist in core.apply_audit_config() + the mirrored Go denylist)Not enrolled (engine-internal)
connector_types, connector_operations, operation_versions, instances, assignment_rules, section_overrides, sync_policy, all three mapping tables, course_shells, shell_sections, user_mappings, enrollment_links, drift_findings, export_runsinstance_credentials, instance_access_tokens (secret-bearing — same precedent as core.application_access_tokens), sync_jobs, integration_log (student data at high churn; the log is the record)export_files, export_watermarks, passback_watermarks, sync_runs

All background writes run under db.WithSystemActor(ctx, db.ActorSourceSystem) — mandatory, or the strict-attribution trigger rejects the write. We deliberately reuse the SYSTEM actor source rather than adding LMS_SYNC: the accepted-source list is hardcoded inside core.audit_row_change(), and per-action attribution granularity lives in sync_jobs / integration_log anyway. (If per-source audit reporting is ever wanted, it is a one-migration CREATE OR REPLACE of that function.)

2.7 Migration sequence (draft; next free number is 199) ​

  1. 199_lms_schema.sql — CREATE SCHEMA lms; all tables above; seed connector definitions for Canvas/Moodle/D2L (same JSON files embedded in the Go test data — single source of truth); CREATE OR REPLACE core.apply_audit_config() adding the four denylisted tables; enroll the audited tables in core.audit_table_config.
  2. 200_lms_permission_nodes.sql — four child nodes under ADMIN.LMS (§10), following the migration-193 template: SET LOCAL app.actor_source = 'INITIAL_MIGRATION', node_type MENU, English name values matching the frontend menu configs, grants copied forward from ADMIN.LMS (migration 143's lesson: rebinding a route to a node nobody holds revokes it).
  3. 201_lms_legacy_data_migration.sql — the data moves in §1.1. Credentials land as inactive sentinel rows because SQL cannot encrypt.
  4. cmd/migrate-lms-credentials (Go, one-shot) — reads the legacy plaintext secrets, encrypts with the keyring (§4), activates the rows, verifies round-trip decryption.
  5. 202_lms_drop_legacy.sql — drops records.lms_*, records.lookup_lms_*, and course_sections.lms_platform_id/lms_course_id (+ indexes). Applied only in phase 4, after the new subsystem covers the old surface and step 4 has verified clean.

All migrations BEGIN;/COMMIT;-wrapped, idempotent, schema-qualified, applied via scripts\db-migrate.ps1 (never bare psql).

3. Connector definition format ​

The definition JSONB of one operation version:

jsonc
{
  "method": "POST",
  "path": "/api/v1/accounts/{{.Mappings.LMSAccountID}}/courses",
  "query": {},                                  // static or templated; {{secret}} allowed ONLY here
  "headers": { "Content-Type": "application/json" },
  "body_kind": "json",                          // "json" | "form" | "none" | "file"
  "body": {                                     // a JSON SKELETON — see §5 for render semantics
    "course": {
      "name": "{{.Shell.Name}}",
      "course_code": "{{.Shell.Code}}",
      "sis_course_id": "{{.Shell.SISID}}",
      "term_id": "sis_term_id:{{.Mappings.LMSTermID}}",
      "start_at": "{{isoDate .Term.StartDate}}"
    }
  },
  "response": {
    "success_status": [200, 201],
    "extract": {                                // dot-path expressions over the response JSON
      "lms_course_id": "id"
    }
  },
  "errors": [                                   // evaluated IN ORDER, BEFORE success_status
    { "status": [429],                "class": "RATE_LIMITED" },
    { "status": [500, 502, 503, 504], "class": "RETRYABLE" },
    { "status": [401, 403],           "class": "AUTH" },
    { "status": [404],                "class": "NOT_FOUND" },
    { "body_path": "exception", "exists": true,             "class": "PERMANENT" },
    { "body_path": "errors.0.message", "contains": "already exists", "class": "ALREADY_EXISTS" },
    { "default": "PERMANENT" }
  ],
  "pagination": {                               // list operations only
    "style": "LINK_HEADER",                     // LINK_HEADER | OFFSET_PARAM | BOOKMARK | NONE
    "items_path": "",                           // dot-path to the item array ("" = root array)
    "page_size": 100
  }
}

Design points:

  • Error matchers run before the success check — non-negotiable, because Moodle returns errors as HTTP 200 with an {"exception": …} body. Error classes and their engine semantics:

    ClassEngine behavior
    RETRYABLEretry with backoff, counts toward max_attempts
    RATE_LIMITEDretry honoring Retry-After when present; does not count toward max_attempts
    AUTHinvalidate cached token, retry once immediately; then treat as RETRYABLE
    ALREADY_EXISTStreated as success for idempotent operations (create/enroll/conclude)
    NOT_FOUNDoperation-dependent: drift signal on reads, permanent failure on writes
    MAPPING_MISSINGengine-generated (not from the LMS): permanent, actionable message
    PERMANENTno retry; job fails (→ dead-letter once attempts exhaust on retryables)

  • Response mapping uses a hand-rolled dot-path subset (a.b.0.c; numeric segment = array index) over the decoded JSON — roughly 60 lines of Go, zero dependencies. It covers every Canvas/Moodle/D2L response shape in practice. Full JSONPath was rejected: it is a dependency and a feature surface admins will misuse; the format is documented as extensible if a filter case ever appears. Extracted values are written back by the job (e.g. lms_course_id onto the shell, lms_enrollment_id onto the link) in the same transaction that marks the job DONE.

  • Auth strategies are the coded part. Go interface:

    go
    type AuthStrategy interface {
    // Apply mutates the outgoing request (header or query) using instance credentials;
    // may fetch and cache a token (lms.instance_access_tokens + in-memory).
    Apply(ctx context.Context, req *http.Request, inst *Instance, creds CredentialSource) error
    // Invalidate drops cached tokens after an AUTH-classified response.
    Invalidate(ctx context.Context, instanceID int32) error
}

    Shipped strategies: BEARER (static token in Authorization header), OAUTH2_CLIENT_CREDENTIALS (POST to token_url, cache, Bearer), QUERY_API_KEY (Moodle: appends wstoken=<secret> and marks the param redacted), D2L_OAUTH2 (Brightspace's token endpoint with its scope format; note D2L's client-credentials grant authenticates with a JWT client assertion signed by the stored secret, not a plain shared-secret POST — this is why it is a distinct strategy). is resolvable only by the auth layer — body and header templates cannot reference credentials at all, so secrets are unreachable from admin-edited templates by construction. That single rule is what keeps preview rendering and integration logging safe.

  • body_kind: "form" renders the same skeleton, then flattens to url.Values with Moodle bracket notation (enrollments[0][roleid]) and sends application/x-www-form-urlencoded. body_kind: "file" marks a batch-upload operation (multipart; the file comes from an export run, §6) — paired with a poll operation named in the same definition ("poll_operation": "get_import_status").

  • Exports section (connector-type level, same JSONB conventions): per dataset, a filename template and ordered column specs rendered per row by the same template engine:

    jsonc
    "exports": {
  "users":       { "filename": "users.csv",
                   "columns": [ {"header": "user_id",    "expr": "{{.Person.SISID}}"},
                                {"header": "login_id",   "expr": "{{.Person.Email}}"},
                                {"header": "first_name", "expr": "{{.Person.FirstName}}"},
                                ... ] },
  "enrollments": { "filename": "enrollments.csv", "columns": [ ... ] },
  ...
}

    Datasets themselves (TERMS, COURSES, SECTIONS, USERS, ENROLLMENTS) are coded — the Go queries that produce rows for a scope are engine code — but the file shapes are data, so a new LMS's CSV format needs a connector definition, not Go. CSV field quoting/escaping is done by encoding/csv, never by templates.

  • Per-instance overrides are resolved entirely by the operation_versions query (§2.1): instance-scoped full definition wins over the type default. No runtime merging.

4. Credential encryption and rotation ​

The existing IdentifierCipher (AES-256-GCM, static env keys, no key-id, no rotation — its own package doc calls it a placeholder) is deliberately not reused as-is. New shared package internal/shared/crypto/keyring.go:

  • Keyring from env: LMS_CRED_KEYS="k1:<base64 32B>,k2:<base64 32B>" + LMS_CRED_ACTIVE_KEY=k2. Validated at startup in all environments (config.go pattern).
  • Ciphertext is self-describing: "v1" || key_id_len || key_id || nonce || ciphertext into a BYTEA column; enc_key_id is duplicated as a queryable column for rotation audits.
  • Encryption-key rotation: add the new key to the keyring, flip LMS_CRED_ACTIVE_KEY, run cmd/migrate-lms-credentials -rotate (re-encrypts every row whose enc_key_id isn't active, verifying round-trips), then remove the retired key. Old ciphertext remains readable throughout — no downtime.
  • LMS-side credential rotation (new API token at the LMS) is the two-row flip described in §2.2 — also no downtime.
  • Prod note: container secrets take the SIS_-prefixed path from the root .env.local; host-run CLI tools read unprefixed vars — both paths must carry the keyring vars (this has bitten before with the identifier keys). Wired for sdasis-backend only as of Aug 2026; records-backend and demo-backend omit the keyring deliberately, each compose block saying why. Until then no compose service interpolated the vars at all, so a prod container could not hold a keyring whatever .env.local said.
  • Unset is a supported state, and fails loudly rather than silently. config.LMSKeyring() returns nil when both vars are empty, and the validation at config.go runs only when one of them is non-empty — so a server with no keyring boots clean, serves every LMS configuration screen, and refuses only the operations that need a secret. CreateCredential answers those with 503 (NewServiceUnavailableError) naming both env vars; the internal sync paths in repository_sync.go return a plain error with the same text. It was a 409 until Aug 2026, which read as "duplicate credential" and pointed diagnosis at the partial unique index instead of at the missing configuration.

5. Template engine (delegated decision 1) ​

Choice: Go text/template with a strict FuncMap and Option("missingkey=error").

Rationale: the requirement needs date formatting, casing, and defaults — beyond the house email.Render (which is strings.ReplaceAll and leaves unmatched placeholders verbatim). A JSON-native mapping language (JSONata et al.) was considered as the "safer for admin-edited templates" alternative; it loses on three counts: a sizeable third-party dependency executing admin input, no one on this codebase knows it, and its safety benefit is achievable more cheaply by structural means (below). text/template is stdlib, auditable, and its function surface is exactly what we register: isoDate, date <layout> (in the institution timezone from INSTITUTION_TIMEZONE), upper, lower, title, trim, default, coalesce. No call, no file or env access. missingkey=error means a typo'd variable fails the render (and the preview) instead of silently emitting <no value>.

Valid JSON is guaranteed structurally, not by post-hoc validation. The body is stored as a JSON skeleton: template expressions appear only inside string values. At render time each string leaf is executed as its own template producing a Go string; a leaf that is exactly one expression with a | number or | bool suffix becomes a typed value; the resulting tree is then marshaled with encoding/json, which performs all escaping. Malformed JSON is unrepresentable in the output. Belt-and-braces: json.Valid on the skeleton at save time (rejects malformed admin input) and on the final payload at send time (cheap invariant assert).

Template context (the variable namespace shown in the editor):

.Shell     — Name, Code, SISID, Status, IsAdopted
.Section   — Code, Descriptor, MaxEnrollment, CampusCode (primary section for shell ops)
.Course    — Code, Title, Credits
.Term      — Code, Name, StartDate, EndDate (dates render via isoDate/date funcs)
.Person    — SISID (people.person_id_number), Email, Login, FirstName, LastName  (enrollment/user ops)
.Enrollment— RoleKey, LMSRole, State
.Grade     — Letter, Numeric, GradeDate                                          (post_grade)
.Mappings  — LMSTermID, LMSAccountID, LMSRole (resolved per §2.2; missing → MAPPING_MISSING)
.Instance  — Code, BaseURL, IsSandbox, Settings.*

Naming conventions are just more templates: the shell's name/course_code sent to the LMS come from two instance-level templates (shell_name_template, shell_code_template) using the same engine and context; rendered values are snapshotted on the shell row.

Versioning is the immutable operation_versions mechanism (§2.1) — history, diff, rollback, effective dating all included.

Preview / dry-run:

  • POST /lms/connectors/operations/{operationID}/preview with {instance_id, section_id, version_id?, sample_person_id?} renders method, URL, headers, and body against real data and returns the exact would-be payload plus validation results — never sends. Secret-bearing values render as [REDACTED:secret].
  • Because an enrollment preview contains real student data, the preview endpoint is gated on the integration-log node (ADMIN.LMS.LOGS), not the connector-editor node — same FERPA posture as reading logs (§9).
  • Per-instance dry_run flag: the engine executes jobs fully but writes the rendered request to integration_log with is_dry_run = true instead of sending, and does not advance shell/link state.
  • The editor's save flow copies the billing rules screen's proven gate: Save stays disabled until the current form content has been previewed (previewedSignature / previewIsStale) — nothing is saved blind.

6. SIS CSV export subsystem ​

Per the project owner's requirement, CSV export is first-class and works for any LMS, one instance at a time, with selectable content.

Run lifecycle: admin picks instance → term(s) → dataset checkboxes → FULL or DELTA → sees dry-run row counts (preview gate) → starts the run. EXPORT_GENERATE streams each dataset's rows through the connector's export column templates into CSV files under FILE_STORAGE_PATH (row counts + sha256 recorded per file) → status GENERATED. Files are downloadable from the run screen (FERPA-gated endpoint streaming from disk; never a public URL). If the connector defines a file_upload operation, an Upload to LMS action enqueues EXPORT_UPLOAD (multipart POST, e.g. Canvas SIS Import; response mapping extracts lms_import_id) → EXPORT_POLL re-enqueues itself with backoff until the LMS reports a terminal import state → IMPORTED / FAILED with the LMS's error report captured in the integration log.

  • One active run per instance enforced by the partial unique index (§2.5); a concurrent request gets a 409 with the active run's id.
  • FULL mode exports everything currently true in sdasis for the scope — idempotent, the onboarding path, and what Canvas diffing-mode wants as input.
  • DELTA mode exports rows changed since the last successful run for that (instance, dataset), using export_watermarks: watermark = max source updated_at observed during the run, and reads subtract a 72-hour overlap window (the migration_watermarks lesson — idempotent generation makes reprocessing harmless). Enrollment deltas additionally consult records.grade_change_log and records.enrollment_status_history, because bulk importers can touch rows in ways updated_at alone under-reports.
  • Retention: generated files and their rows are pruned by the retention scanner after a configurable window (default 30 days).
  • Seeds: Canvas ships export profiles for the standard SIS CSV set plus the sis_imports upload/poll operations (diffing mode exposed as an operation setting); Moodle and D2L ship download-only export profiles matching their bulk-import file formats (Appendix A).

7. Sync engine ​

7.1 Job queue (delegated decision 3) ​

No queue exists in the codebase; this design builds the canonical Postgres one:

sql
UPDATE lms.sync_jobs j
SET status = 'RUNNING', locked_by = $1, locked_at = now(), attempts = attempts + 1
WHERE j.job_id = (
    SELECT job_id FROM lms.sync_jobs
    WHERE status = 'PENDING' AND run_after <= now()
    ORDER BY priority, run_after
    LIMIT 1
    FOR UPDATE SKIP LOCKED)
RETURNING *;
  • Workers: an engine.Runner started from cmd/api/main.go alongside the existing tickers. N workers (settings-driven, default 4) claim in a 2-second-jittered poll loop (no LISTEN/NOTIFY infrastructure exists; 2 s latency is fine for this workload). SKIP LOCKED makes concurrent replicas safe with no leader election.
  • Scanners (provisioning trigger scan hourly, reconciliation nightly, passback every 5 min, stale-lock reaper, log/file retention) are single-flighted with a Postgres advisory lock on a dedicated *sql.Conn — the photosync pattern copied verbatim, including finishing the sync_runs record on a fresh context.Background() so a cancelled run still records itself.
  • Reaper: RUNNING AND locked_at < now() - '10 minutes' → back to PENDING (crashed worker). Every job is therefore individually idempotent (§7.2).
  • Backoff: run_after = now() + min(30s · 2^(attempts-1), 1h) + jitter(0–25%); attempts ≥ max_attempts → DEAD. Dead-letter is a status, not a second table — the dashboard filters on it, with retry (reset to PENDING, attempts 0) and dismiss (CANCELLED).
  • Rate limiting: in-process golang.org/x/time/rate limiter per instance (rps/burst from the instance row, falling back to the connector-type default), held in the runner. The deployment is effectively single-replica today; if replicas multiply, the configured rate is divided by replica count via a setting. A DB-stored token bucket is the documented upgrade path, not built now. Bulk term rollout is naturally throttled: thousands of enqueued jobs drain at the instance's configured rate.

7.2 Idempotency semantics ​

The partial unique index on dedupe_key (PENDING/RUNNING only) makes enqueue-time coalescing race-free; enqueue is INSERT … ON CONFLICT (dedupe_key) DO UPDATE SET payload = …, run_after = … so add-then-drop collapses to the latest intent.

Kinddedupe_keyRe-run safety
CREATE_COURSEcc:{shell_id}Re-read shell: lms_course_id set → no-op DONE. Else find_course by SIS id before create_course (recovers from crash-after-send). Extracted id stored in the same tx that marks DONE.
ENSURE_USEReu:{instance_id}:{person_id}Check user_mappings; find_user by configured identity (§7.4) before create_user.
ENROLL / UNENROLL / UPDATE_ENROLLMENTen:{shell_id}:{person_id}:{role}Check enrollment_links state first; ALREADY_EXISTS → success.
POST_GRADEpg:{shell_id}:{enrollment_id}Latest-wins coalescing; payload carries the grade_change_log id.
CONCLUDE_COURSEco:{shell_id}Conclude-of-concluded maps to ALREADY_EXISTS → DONE.
RECONCILE_SHELLrs:{shell_id}:{date}Read + enqueue only; naturally idempotent.
EXPORT_GENERATE / UPLOAD / POLLxg:{run} / xu:{run} / xp:{run}Run status machine; regeneration overwrites the run's files atomically.

Jobs ↔ log: one integration_log row per HTTP attempt (every retry, every pagination page, every token fetch, every dry-run render) carrying job_id; the job row holds only the last error summary. Redaction happens at write time in the connector client (auth headers → [REDACTED], query params the auth strategy marked secret → masked) — never at read time.

7.3 Trigger evaluation and event wiring ​

Provisioning scan (hourly, advisory-locked, sync_runs record). Sketch of the due-section query:

sql
WITH routed AS (
  SELECT cs.section_id, cs.class_id,
         COALESCE(so.instance_id, best_rule.instance_id) AS instance_id
  FROM records.course_sections cs
  JOIN records.academic_terms t   ON t.term_id = cs.term_id
  LEFT JOIN records.sub_terms st  ON st.sub_term_id = cs.sub_term_id
  LEFT JOIN lms.section_overrides so ON so.section_id = cs.section_id
  CROSS JOIN LATERAL (
    -- org-unit rule (ancestry walk from split_part(course_code,' ',1)
    --   → core.unit_course_codes → org_units tree), else campus rule,
    --   else the (NULL,NULL) default rule; ORDER BY specificity, priority LIMIT 1
  ) best_rule
  WHERE COALESCE(so.provisioning_exempt, FALSE) = FALSE
    AND policy.provision_trigger <> 'MANUAL'
    AND (COALESCE(st.start_date, t.start_date) - policy.provision_days_before)
          <= (now() AT TIME ZONE :institution_tz)::date
    AND NOT EXISTS (SELECT 1 FROM lms.shell_sections ss
                    WHERE ss.section_id = cs.section_id AND ss.removed_at IS NULL)
)
SELECT * FROM routed;
  • Trigger anchor is COALESCE(sub_terms.start_date, academic_terms.start_date) — sections on a sub-term provision relative to their own start. provision_days_before may be negative (provision after start). All date math is in the institution timezone (INSTITUTION_TIMEZONE setting; the DB pins connections to it). Deliberately not /terms/latest_active, which answers a reporting question.
  • IMMEDIATE trigger = same scan with the date predicate dropped (still hourly; "immediately on section creation" resolves within the hour, and the section screen's Provision now button is the true-immediate path).
  • Sections sharing a class_id and routed to the same instance form one shell (primary = lowest section code unless overridden). Class-mates routed to different instances get individual shells plus a SPLIT_CROSSLIST finding. Unrouted sections under provisioning_mode = 'REQUIRED' produce UNROUTED_SECTION findings (the "reporting on unprovisioned sections" requirement); under OPTIONAL, they're skipped.
  • The enrollment-sync trigger runs the same shape against its own policy columns and stamps course_shells.enrollment_sync_started_at, which gates event-driven enrollment jobs and seeds the initial roster enqueue. The conclude scan mirrors it off end_date + conclude_days_after when conclude_policy = 'CONCLUDE'.

Event wiring — the house pattern (callback injection at route registration, exactly like grading's onGradeSubmitted): registration's RegisterRoutes gains an onEnrollmentChanged func(ctx context.Context, enrollmentID uuid.UUID) hook, fired fire-and-forget from RegisterForSection / DropSection. The LMS implementation resolves section → live shell → if enrollment_sync_started_at is set, enqueues ENSURE_USER then ENROLL/UNENROLL (coalescing dedupe). The plain-CRUD enrollment path (which bypasses the registration gates) and instructor churn (which has no hook at all) are covered by reconciliation — the codebase's own acknowledged contract ("reconciliation is the backstop").

7.4 User identity matching ​

Configurable per instance (match_strategy on the instance settings): SIS_ID (default — people.person_id_number, the Colleague ID; chosen over students.student_number, which excludes instructors), EMAIL (INSTITUTIONAL person_emails row, falling back to people.primary_email), or USERNAME (auth_users.email — this deployment has no separate username). ENSURE_USER tries find_user with the configured identity; on miss, create_user if the connector defines it, else the job fails with an actionable error naming the person and identity tried. Successful matches persist to lms.user_mappings with matched_by.

Person-merge awareness: people.merged_into_person_id is checked during reconciliation; links and mappings pointing at a merged-away person are re-pointed (or flagged) rather than duplicated.

7.5 Drop behavior ​

Per-instance setting drop_behavior: DELETE (remove the LMS enrollment) or DEACTIVATE (conclude/deactivate it — institutions differ for record-keeping). It selects which operation the UNENROLL job invokes (unenroll_user vs update_enrollment with the connector's conclude/deactivate semantics); enrollment_links.state_code records REMOVED vs CONCLUDED.

7.6 Reconciliation and drift ​

Nightly per instance (advisory-locked): enqueue RECONCILE_SHELL for every ACTIVE shell. The job pages through list_enrollments, builds the LMS-side set keyed by (lms_user_id, role), and diffs against expected — ENROLLED students across all live member sections plus section_instructors (role split by can_enter_grades). Classification → per-policy action from the three sync_policy drift columns:

  • AUTO_REPAIR — enqueue corrective jobs; record the finding as AUTO_REPAIRED.
  • FLAG — open a finding for the dashboard.
  • IGNORE — skip.

get_course returning NOT_FOUND → MISSING_SHELL (LMS course deleted externally); course name/code mismatch vs snapshots → COURSE_META_MISMATCH (renamed externally). people.ferpa_opt_out does not suppress enrollment sync (the LMS is a school-official / legitimate-educational-interest system) but the flag is carried into finding details so the dashboard can badge it.

Scale: reconciliation is itself jobs, so thousands of shells drain through the same rate-limited queue without hammering the LMS.

8. Grade passback (design + seam; implementation is phase 5) ​

  • Per-instance flag grade_passback_enabled, default off.

  • Not a special path: post_grade / update_grade are ordinary connector operations — templating, response mapping, error mapping, job semantics all inherited.

  • Source of truth for "a grade changed": records.grade_change_log — durable, trigger-backed, append-only, fires on every write path (instructor entry, registration update, Colleague sync, raw SQL). A 5-minute scan reads log_id > passback_watermarks.last_log_id for shells on passback-enabled instances, coalesces to the latest change per enrollment, enqueues POST_GRADE (coalescing dedupe), and advances the watermark in the same transaction as the enqueues. The watermark initializes to MAX(log_id) at enablement so historical/ODS backfills don't flood the queue.

  • What is sent: final letter grade and numeric equivalent from grading_system_grades (.Grade.Letter, .Grade.Numeric in the template context). Midterm grades are the same mechanism filtered on grade_kind = 'MIDTERM', off by default. On-demand batch = a button that enqueues POST_GRADE for a section's current grades.

  • Eligibility predicate: grade_id IS NOT NULL and the grade is not temporary (grading_system_grades.is_temporary — I/IP-style placeholders don't pass back). There is no posting/approval state in sdasis; whether an explicit registrar release step should gate passback is deliberately deferred to phase 5 (owner decision). The seam is built now:

    go
    type GradeReleaseGate interface {
    Allowed(ctx context.Context, sectionID int32, enrollmentID uuid.UUID) (bool, error)
}

    injected into the passback scanner; the phase-2 implementation is allowAll{}. If a release step is chosen later, only the gate implementation and a holds table are added.

  • Errors surface on the sync dashboard like any other job failure, grouped per section.

  • API passback vs LTI Advantage AGS: API passback (this design) pushes grades using admin credentials — simple, works without any LTI tool registration, but writes into gradebook structures each LMS shapes differently (Appendix A notes the per-LMS target). LTI AGS is the standards-track answer — per-line-item scores with proper scoping — but requires sdasis to be a registered LTI tool with a launch context, deployment IDs, and per-platform key exchange: a different subsystem. The seam left for LTI: passback is already isolated behind operation definitions + the scanner; an LTI implementation would replace the connector client call inside the POST_GRADE job with an AGS score publish, reusing the watermark scan, jobs, and dashboard unchanged. No LTI in this pass.

9. Security and compliance ​

  • Credentials: encrypted at rest with the rotating keyring (§4); never returned by any API (write-only fields; presence exposed as has_secret); unreachable from templates (§3); redacted at write time from every log row (§7.2); credential tables on the hard audit denylist so secrets can't leak into core.audit_log old/new row images.
  • FERPA: enrollment and grade data crossing to the LMS is education-record data.
    • No student data in URLs where avoidable: seed operation definitions put identifiers in request bodies; where an LMS API forces a path identifier (e.g. enrollment by id), it is the LMS's opaque id, not name/SSN-like data.
    • Log lines carry person UUIDs and LMS ids, not names.
    • integration_log request/response bodies (which do contain student data) are gated on ADMIN.LMS.LOGS; preview renders of enrollment payloads are gated on the same node — the preview is functionally a log row that hasn't been sent.
    • Export files live under FILE_STORAGE_PATH, are streamed only through the FERPA-gated download endpoint, and are retention-pruned.
  • Audit: all configuration changes flow through the migration-081 trigger system (actor, timestamp, old/new row images). Human actions on findings/runs record resolved_by / requested_by. Background engine writes attribute as SYSTEM.
  • Least privilege per LMS is documented with each seed connector (Appendix A): dedicated integration account, minimum token scopes/capabilities, sandbox-first validation.

10. Admin UI (Vue) ​

Four screens under the retained ADMIN.LMS parent node (sidebar is fully permission-node-driven; English node names are load-bearing for admin-menu-groups.ts / menu-i18n-map.ts):

NodeScreen
ADMIN.LMS.INSTANCESLMS Instances — instance list (composable list layer) → detail: connection fields, write-only credential fields (existing pattern: has_secret Tag, blank-keeps-stored, leave-blank hint), Test connection button (runs the connector's verify operation via useConfirmAsync), sandbox + dry-run toggles, rate limits; tabs for term/org-unit/role mapping tables with unmapped-value warnings (terms/org-units referenced by routed sections but missing a mapping render as warning rows with inline add); assignment rules; the singleton policy editor (trigger rules, drift policies, drop behavior).
ADMIN.LMS.CONNECTORSLMS Connectors — connector types → operations → version editor. Definition edited as JSON in a Textarea (no code editor exists in the codebase; Monaco deliberately not introduced now), with server-side schema validation on save and the preview gate (Save disabled until the current content has been previewed against a real section — billing-rules pattern). Version history list with side-by-side old/new diff (audit-log value-pre old/new styling) and one-click rollback (creates a new version). Export profile editor uses the same version mechanism.
ADMIN.LMS.SECTIONSLMS Sync — the operational home. Per-term dashboard: stat cards (provisioned / pending / failed / dead-lettered / unrouted), failures-needing-attention table with retry/dismiss row actions, drift findings queue (resolve/ignore), section table with per-section status Tag, instance assignment override, cross-list grouping editor, adopt existing course dialog, provision-now / re-sync / view-log buttons. SIS Export wizard: instance → terms → dataset checkboxes → FULL/DELTA → dry-run row counts → generate; run list with per-file downloads (sha, row counts) and Upload-to-LMS with live import status where the connector defines it.
ADMIN.LMS.LOGSLMS Logs — lazy-paged integration log with filters (instance, operation, error class, dry-run), detail dialog with bounded <pre> request/response blocks; job queue browser (status filters incl. DEAD). Also authorizes the preview endpoint (§9).

All list views use the shared composable layer (useListPage / useCrudDialog / useDeleteConfirm + ListPageToolbar) per the must-use policy; payload types follow the *Write convention with the Go DTO named in a comment; i18n leaves added to all three locales (build-enforced parity); toasts via common.toast.*.

11. Backend package layout ​

sis_backendgo/internal/lmsintegration/
    model.go repository.go service.go handler.go dto.go routes.go   # house 6-file core
    connector/    # definition types + JSON-schema validation, template renderer (§5),
                  # dot-path extractor, auth strategies, HTTP client (retry/backoff/rate-limit,
                  # redaction, pagination) — domain-local; promoted to shared/ only when a
                  # second consumer exists
    engine/       # queue store (enqueue/claim/reap), runner, scanners (provision, reconcile,
                  # passback, retention), export generator
    mocklms/      # httptest personalities + contract-test harness (test-only)
sis_backendgo/internal/shared/crypto/keyring.go
sis_backendgo/cmd/migrate-lms-credentials/

Wiring: lmsintegration.RegisterRoutes(reg, prefix, db, validator) replaces lms.RegisterRoutes in internal/router/router.go; the four new nodes join authz.KnownNodes and migration 200 (both, per that file's doc); cmd/api/main.go starts the engine.Runner; registration.RegisterRoutes gains the onEnrollmentChanged parameter. New DTOs use pointer fields (the PATCH replace-semantics trap). Enqueue-style endpoints return response.Accepted.

12. Mock LMS and testing strategy ​

Mock LMS (in-repo, mocklms, built early in phase 2 because everything tests against it): mocklms.New(t, personality) returns an httptest.Server. Personalities implement each seed's surface and quirks, holding in-memory state so idempotency tests are real:

  • Canvas: Bearer auth check, Link-header pagination, injectable 429 + Retry-After, sis_course_id:/sis_user_id: lookup semantics, SIS import accept + status endpoint.
  • Moodle: wstoken query auth, form-encoded bodies with bracket notation, errors as HTTP 200 + {"exception": …}.
  • D2L: OAuth2 token endpoint, bookmark pagination, versioned paths.

Contract tests load the seed connector definitions from embedded testdata/ — the same JSON files migration 199 inserts (single source of truth) — and execute every operation against the mock: extraction results, error-class mapping (including the Moodle 200-error case), pagination traversal, auth application, and redaction in captured log rows.

Unit tests: template engine (JSON-validity guarantee, typed leaves, form flattening, missingkey=error), dot-path extractor, trigger-rule evaluation (timezone edges: DST boundary, negative day deltas, sub-term vs term anchor), queue claim/coalesce/backoff/reaper, keyring round-trip + rotation, CSV generation (quoting, delta watermarks).

Integration test (phase 5): simulated term rollout — 200 sections across two mock instances (Canvas + Moodle personalities), injected failures (429 storms, 500s, one dead-letter), verify convergence via reconciliation: every non-exempt section ends ACTIVE with a correct roster, and drift_findings is empty.

Duplicate-event test pins the core idempotency claim: firing the same section-created / enrollment-changed event twice yields exactly one LMS course / one enrollment.

13. Phase plan ​

PhaseContentsExit criteria
1 (this doc)Design reviewOwner sign-off
2 CoreMigrations 199–201, keyring, connector runtime (template/dot-path/auth/client), Canvas seed, queue + runner + provisioning scan, mock LMS + contract tests, minimal UI (instances + connector editor + preview)go build ./..., router coverage/nodes tests green, contract tests green, npm run build green; shell provisioned against mock Canvas end-to-end
3 Enrollments + exportonEnrollmentChanged wiring, ENSURE_USER/ENROLL/UNENROLL, user/role mapping, reconciliation, sync dashboard; SIS export framework (runs/files/watermarks, generate + download, Canvas SIS CSV profile + upload/poll, wizard UI, DELTA mode)Roster sync + reconciliation convergence against mock; export run produces byte-stable files with correct counts
4 BreadthMoodle + D2L seeds (REST + download-only export profiles), cross-listing UI, adopt-existing, conclude policy, drift policies, migration 202 + legacy deletionAll three contract suites green; legacy tables/views gone
5 Passback + hardeningGrade passback behind the flag, release-gate decision, full integration suite (200-section rollout)Integration test green; passback verified against mock gradebooks

Each phase boundary produces a written summary: what was built, deviations from this document and why, and recommended adjustments before the next phase.

Appendix A. Seed connector summaries ​

(Facts verified 2026-08-21 against the cited official documentation; explicitly-flagged items could not be confirmed and must be validated against a sandbox during phase 2/4.)

OperationEndpointNotes
create_coursePOST /api/v1/accounts/{account}/coursescourse[name], course[course_code], course[sis_course_id], course[term_id] (accepts sis_term_id:{id} object-ID syntax), course[start_at] ISO 8601
create_sectionPOST /api/v1/courses/{id}/sectionscourse_section[name], course_section[sis_section_id] (needs manage-SIS permission); enable_sis_reactivation revives a deleted section with the same SIS id — useful for idempotent re-provisioning
find_course / find_userGET /api/v1/courses/sis_course_id:{id} / GET /api/v1/users/sis_user_id:{id}SIS-ID object syntax in paths is documented; ids must be URI-escaped
enroll_userPOST /api/v1/sections/{id}/enrollments (section-level, since we model LMS sections)enrollment[type] ∈ StudentEnrollment / TeacherEnrollment / TaEnrollment; enrollment[enrollment_state]=active. Canvas also documents enrollment[sis_user_id] + root_account as an explicit SIS-keyed variant. Flag: the sis_user_id: prefix inside enrollment[user_id] is common practice but not explicitly documented on that param — the seed uses the documented enrollment[sis_user_id] form; verify in sandbox.
unenroll_userDELETE /api/v1/courses/{id}/enrollments/{id}?task=…task=delete vs conclude vs deactivate maps directly onto the per-instance drop behavior (§7.5)
list_enrollmentsGET /api/v1/courses/{id}/enrollmentsLink-header pagination (rel=next); treat page URLs as opaque; per_page maximum is undocumented — seed uses 100 and follows Link
conclude_coursePUT /api/v1/courses/{id} with course[event]=concludeevents: claim/offer/conclude/delete/undelete; there is no writable workflow_state
create_userPOST /api/v1/accounts/{account}/usersuser[name], pseudonym[unique_id] (login), pseudonym[sis_user_id], pseudonym[send_confirmation]=false
post_gradePUT /api/v1/courses/{id}/assignments/{id}/submissions/{user} with submission[posted_grade]The final-grade override is GraphQL-only (setOverrideScore; no REST equivalent). REST-practical passback = a designated "Final Grade" assignment (posted_grade accepts points, percent, or letter) or a display-only custom gradebook column (POST …/custom_gradebook_columns, PUT …/data/{user}). The seed ships the assignment-score variant; the choice is per-institution and revisited in phase 5.
file_upload (SIS import)POST /api/v1/accounts/{account}/sis_imports (multipart attachment, import_type=instructure_csv)Optional diffing_data_set_identifier (send full files; Canvas applies only the delta vs the previous set with the same identifier — pairs with our FULL mode) and diffing_drop_status. Poll GET …/sis_imports/{id}; terminal workflow_state: imported, imported_with_messages, failed, failed_with_messages, aborted.

SIS CSV export profile (required columns per the SIS CSV format doc): users.csv (user_id, login_id, first_name, last_name, status), terms.csv (term_id, name, status, start_date, end_date), courses.csv (course_id, short_name, long_name, status, term_id, account_id), sections.csv (section_id, course_id, name, status), enrollments.csv (section_id, user_id, role, status).

Errors/limits: throttling is a leaky-bucket per token with X-Request-Cost / X-Rate-Limit-Remaining headers; Canvas's own docs are contradictory on the throttle status code (text says "429 (Rate Limit Exceeded)", historical versions said 403) — the seed's error matchers therefore classify both 429 and a 403-with-rate-limit-body as RATE_LIMITED. Least privilege: a dedicated service account; token via a developer key with URL scopes restricted to the operations above (manual "Approved Integration" tokens are for testing only); SIS-field writes additionally require the account's manage-SIS permission.

A.2 Moodle Web Services (REST) — auth_strategy: QUERY_API_KEY, pagination: NONE, body_kind: form ​

Endpoint for every operation: POST /webservice/rest/server.php with wstoken (the secret — query-redacted), wsfunction, moodlewsrestformat=json; parameters form-encoded with bracket notation (courses[0][fullname]=…). Errors arrive as HTTP 200 with {"exception","errorcode","message"} — the seed's first error matcher is body_path: "exception", exists: true.

OperationwsfunctionNotes
create_coursecore_course_create_coursesrequired fullname, shortname, categoryid (from the org-unit mapping); idnumber carries our SIS id; startdate/enddate epoch seconds
update_course / conclude_coursecore_course_update_coursesconclude = set visible=0 (Moodle has no conclude state)
find_coursecore_course_get_courses_by_fieldfield=idnumber
enroll_userenrol_manual_enrol_usersenrollments[0][roleid]/[userid]/[courseid]; requires the manual-enrol method enabled in the course. Role ids are site-conventional, not contractual (fresh installs: editingteacher=3, teacher=4, student=5) — which is exactly why instance_role_mappings.lms_role stores the numeric id per instance.
unenroll_user (delete)enrol_manual_unenrol_usersremoves the enrollment entirely
update_enrollment (deactivate)enrol_manual_enrol_users with suspend=1keeps grades/history — the DEACTIVATE drop behavior
list_enrollmentscore_enrol_get_enrolled_usersonlyactive/onlysuspended options; returns full array (no pagination)
find_user / create_usercore_user_get_users_by_field (field=idnumber) / core_user_create_userscreate requires username, firstname, lastname, email; idnumber = our SIS id; auth per site (e.g. ldap/oidc where SSO-provisioned)
post_gradecore_grades_update_gradesLimitation: writes activity-linked grade items only; there is no core web service for manual gradebook items or course-total overrides. Seed marks passback unsupported by default for Moodle; institutions that want it target a designated activity or use the flat-file/CSV route.

CSV export profiles (download-only): flat-file enrollment sync lines operation, role, user_idnumber, course_idnumber[, start[, end]] (add/del, role by shortname); Upload users CSV (username, password, firstname, lastname, email + idnumber, course1/role1 enrollment columns); Upload courses CSV (shortname as the match key, fullname, category, idnumber, visible, startdate, enddate).

Least privilege: enable REST protocol; create a custom external service containing only the functions above; dedicated WS user with a system role granting webservice/rest:use plus each function's capability (enrol/manual:enrol, enrol/manual:unenrol, moodle/user:create, moodle/course:create, …); mint the token for that user+service. (Verification note: docs.moodle.org blocked automated access; the facts above were verified against the Moodle source tree (moodle/moodle@main) — function signatures and the REST error serialization are source-confirmed.)

A.3 D2L Brightspace — auth_strategy: D2L_OAUTH2, pagination: BOOKMARK ​

Auth: token endpoint https://auth.brightspace.com/core/connect/token; client-credentials grant authenticates with a JWT client assertion (signed with the stored secret), scopes <group>:<resource>:<action> (e.g. enrollment:orgunit:create, users:userdata:read) — the requested subset of the app registration's scopes is the least-privilege lever. API versions must be negotiated via GET /d2l/api/versions/ (stable lp is 1.63 as of Suite 20.26.8) — the seed stores the version as an instance setting rather than hardcoding.

OperationEndpointNotes
create_coursePOST /d2l/api/lp/{v}/courses/Name, Code, CourseTemplateId (from org-unit mapping), SemesterId (term mapping), StartDate/EndDate UTC, IsActive required (lp ≥ 1.59). Course offerings are identified by D2L id (no orgDefinedId on this contract) — the extracted Identifier is our lms_course_id.
enroll_userPOST /d2l/api/lp/{v}/enrollments/{OrgUnitId, UserId, RoleId}; upsert semantics — an existing enrollment is updated to the new role (UPDATE_ENROLLMENT reuses this operation)
unenroll_userDELETE /d2l/api/lp/{v}/enrollments/orgUnits/{orgUnit}/users/{user}D2L has no enrollment-conclude; DEACTIVATE drop behavior maps to re-enrolling into a configured non-participant role, or is left unsupported per instance
list_enrollmentsGET /d2l/api/lp/{v}/enrollments/orgUnits/{orgUnit}/users/bookmark pagination: follow PagingInfo.Bookmark while HasMoreItems
find_userGET /d2l/api/lp/{v}/users/?orgDefinedId={sisid}returns an array; ?userName= returns a single object
create_userPOST /d2l/api/lp/{v}/users/FirstName, LastName, UserName, RoleId, IsActive, SendCreationEmail + OrgDefinedId, ExternalEmail
post_gradePUT /d2l/api/le/{v}/{orgUnit}/grades/final/values/{user}writes the adjusted final grade (IncomingFinalAdjustedGradeValue {PointsNumerator, PointsDenominator}) — D2L is the one seed with a true REST final-grade target

Errors/limits: 429 with Retry-After, plus X-Rate-Limit-Remaining / X-Request-Cost / X-Rate-Limit-Reset headers; 403 covers both invalid token and missing permission (the service account needs the same LMS role permissions as the equivalent UI action — role-based least privilege on top of OAuth scopes). CSV export profile: download-only bulk user/enrollment formats, finalized in phase 4 against the institution's Brightspace bulk-tool configuration.

Sandbox-validation list (flagged during research): Canvas sis_user_id: prefix inside enrollment body params; Canvas throttle status code (seed matches both 429 and 403-variant); Canvas per_page ceiling; Moodle role ids on the actual site; D2L per-route scope strings.

Appendix B. Phase 2 outcome (built 2026-08-21) ​

Built as designed: migrations 199–201 (applied to the local dev DB), the lms schema with the audit reconciler extension, keyring (internal/shared/crypto/keyring.go + tests), connector runtime (internal/lmsintegration/connector: definitions/validation, template engine, dot-path, 4 auth strategies, HTTP client with rate limiting/pagination/classification/redaction), the SKIP LOCKED queue + runner + advisory-locked scanners (internal/lmsintegration/engine), the domain (config CRUD, credentials, mappings, rules, policy, preview, test-connection, provision-now, CREATE_COURSE executor, provisioning scan), the Canvas seed (12 operations, installed additively at startup), cmd/migrate-lms-credentials, the mock Canvas LMS and the contract/unit test suites, and the four admin screens.

Deviations from the body of this document, and why:

  1. Operation seeds install at startup, not in migration 199. The migration seeds only the connector-type head rows (which 201 needs for FKs); the operation definitions live solely in the Go-embedded JSON and are inserted additively by EnsureSeeds at boot. This makes the embedded files the literal single source of truth (the doc's "same files in both places" was two copies), and seeding never overwrites an admin-edited operation.
  2. The four permission nodes parent to ADMIN directly, not to ADMIN.LMS. The Admin sidebar delivers ADMIN's children flat and buckets them by English name in admin-menu-groups.ts; nesting under ADMIN.LMS would have bypassed that mechanism. The legacy ADMIN.LMS node stays until phase 4.
  3. Definitions cannot reference at all (the doc showed it in a query template). Secrets are placed exclusively by the coded auth strategy, and validation rejects any definition referencing one — a stronger invariant that keeps previews/logs safe by construction. Correspondingly, Moodle's wstoken is appended by the QUERY_API_KEY strategy, not written into definitions.
  4. ENROLL/UNENROLL collapse into one state-driven kind (SYNC_ENROLLMENT, phase 3): executors re-derive intent from live SIS state at run time, so a coalesced or reaped job converges instead of replaying a stale intent. Dedupe keys unchanged.
  5. LMS_SYNC actor source not added; background writes attribute as SYSTEM (the 081 accepted-source list is hardcoded in the trigger; per-action attribution lives in sync_jobs/integration_log). Matches §2.6's recommendation — recorded here because the exploration originally proposed the new source.
  6. Legacy credentials are plaintext, so cmd/migrate-lms-credentials reads and encrypts directly (no legacy-cipher step); it also carries the -rotate pass.
  7. Manual provision-now creates a single-section shell; class-mate grouping happens only in the scheduled scan. Cross-list grouping UI lands in phase 4 as planned.

Recommended before phase 3: decide the Moodle role-id defaults to seed (site-conventional); confirm the enrollment[sis_user_id] body variant against a real Canvas sandbox (flagged in Appendix A); and wire onEnrollmentChanged early so its shape settles before reconciliation.

Appendix C. Phase 3 outcome (built 2026-08-21) ​

Built: the enrollment-changed hook (SetEnrollmentChangedHook on the registration service, fired from RegisterForSection, DropSection, and all three CRUD paths); the state-driven SYNC_ENROLLMENT executor (ensures the user per the instance match strategy, enrolls with the mapped role, or unenrolls per drop behavior — intent re-derived from live SIS state at run time); the enrollment-sync trigger scan (stamps enrollment_sync_started_at, seeds the initial roster); nightly reconciliation (RECONCILE_SHELL jobs: get_course existence/rename check, paginated roster diff, per-policy AUTO_REPAIR/FLAG/IGNORE, open-finding dedupe); drift findings including UNROUTED_SECTION under REQUIRED provisioning and UNKNOWN_LMS_USER (never auto-removed); the sync dashboard/shells/jobs/findings API; and the full SIS CSV export subsystem (runs, per-dataset CSV generation through export_profile column templates, FULL/DELTA with GREATEST-upserted watermarks and 72h read overlap, FERPA-gated downloads, zip upload through the sis_import file_upload operation, fixed-cadence import polling against data-driven terminal states).

Deviations from the body of this document:

  1. The enrollment hook fires on every enrollment write path, not just register/drop — the plain CRUD endpoints too. Cheaper than leaving the documented gap; reconciliation still backstops instructor churn.
  2. ENSURE_USER is not a separate job: SYNC_ENROLLMENT ensures the user inline (fewer moving parts, same idempotency); the kind is still accepted for queue compatibility.
  3. Seeding may supersede its own untouched definitions: an existing seed operation whose every version is SEED-authored gets a new "seed update" version when the embedded JSON changes (this shipped list_enrollments v2 with the per-item extraction paths). One human-authored version transfers ownership permanently.
  4. Per-item extraction convention: a list operation's response.extract paths apply to each item of the paginated list — this is how reconciliation reads any LMS's roster shape without code.
  5. Poll terminal states are data (terminal_success/terminal_failure on the poll definition), not hardcoded Canvas workflow states.
  6. EMAIL/USERNAME matching reuses the single find_user operation via a .Person.MatchValue context variable; the Canvas seed matches by SIS id — an instance wanting email matching overrides find_user accordingly.
  7. Unrouted-section findings need an owner instance (the findings table requires one); they attach to the first active instance as owner-of-record.

Deferred within phase 3, deliberately: export-file retention pruning covers log rows only (run directories accumulate until phase 4's retention pass); the grade-passback scanner remains phase 5.

Appendix D. Phase 4 outcome (built 2026-08-21) ​

Built: the Moodle seed (11 operations — wstoken/form-encoded/HTTP-200-exception handling throughout; conclude = visible=0; unenroll = manual-unenrol with the per-instance suspend override documented; download-only export profile covering flat-file enrollments [headerless, role shortnames via template conditionals], Upload-users and Upload-courses CSVs) and the D2L seed (10 operations — JWT-assertion OAuth verified against the mock incl. token-cache reuse; typed-number JSON bodies; bookmark-paginated classlist; a true REST final-grade post_grade; lp/le versions and default role/denominator as instance settings; download-only export profile). Both have full contract suites against new mock personalities. Also: the end-of-term conclude scan (hourly, policy-gated, CONCLUDE_COURSE executor), cross-listing management (attach/detach member sections with automatic roster resync — detach snapshots the leavers' links first so their unenroll jobs exist; the primary section cannot detach), export-run retention (terminal runs pruned after 30 days, on-disk directories removed), the sanitizer extension (all operational lms tables truncated — credentials, tokens, jobs, log, mappings, links, findings, exports, overrides; the drift sweep now scans the lms schema), and the legacy teardown: migration 202 (gated — it refuses to run while any UNMIGRATED credential sentinel survives, because the plaintext rows it drops are the only copy of those secrets), the Go internal/domain/lms package, LMSManagementView.vue + its api/types/i18n (including the stray lMSManagement branch), the four LMS_* lookup registry entries, and the course_sections.lms_* columns. The legacy ADMIN.LMS node is deactivated, not deleted (RESTRICT FKs; grants were already copied forward by 200).

Deviations: found-nothing semantics were generalized (a lookup operation that succeeds but extracts nothing is treated as NOT_FOUND — Moodle misses with HTTP 200 + an empty array); ExportFileSpec gained headerless; unenroll operations get the user mapping and role in scope (Moodle/D2L unenroll by user+course, not enrollment id); D2L seeds no find_course (offerings carry no orgDefinedId — the executor tolerates its absence, losing only crash-after-send recovery on D2L).

Prod deploy order note: run cmd/migrate-lms-credentials (with LMS_CRED_KEYS set) BEFORE the deploy that applies 202 — the gate turns a wrong order into a loud failed deploy instead of secret loss.

Appendix E. Phase 5 outcome (built 2026-08-21) ​

Owner decision (2026-08-21): the registrar release step is in, and it is broader than the LMS seam — it covers every faculty grade submission, not just sections synced to an LMS.

Built — the registrar grade-release model (migration 203, grading domain): an append-only per-(section, kind) release ledger (records.grade_releases) keyed on TIME over records.grade_change_log — a release covers every change with changed_at <= released_through, so a faculty re-submission after a release simply becomes unreleased again. Colleague-sourced changes (COLLEAGUE_SYNC/COLLEAGUE_LEGACY/INITIAL_MIGRATION) are official on arrival and never need a release; every human source waits. Endpoints under the new GRADING.RELEASE node (registrar): pending list per term, release (with notes), bulk release, history. What release gates today is external publication only — transcript/GPA behavior is deliberately unchanged. Open registrar-policy question: should unreleased faculty grades also be excluded from transcripts/GPA? That is a much deeper change (touches GPA, academic status, term-complete detection) and needs its own decision.

Grade passback, wired to the release model on both sides: the 5-minute scanner walks grade_change_log past each passback-enabled instance's watermark (first scan only initializes the watermark — enabling passback never replays history), holds unreleased grades back (fail-closed: an unwired gate publishes nothing), and the grades-released hook pushes newly released grades out immediately. POST_GRADE re-checks the grade, the release, and the instance flag at execution time. Per-shell "Sync grades now" batch endpoint included. Canvas passback target: the designated assignment (settings final_grade_assignment_id), contract-tested; D2L: the true REST adjusted-final-grade endpoint (seeded in phase 4); Moodle: unsupported by default.

The 200-section term-rollout drill (rollout_itest_test.go, gated LMS_ITEST=1, writes synthetic rows and tears them down): 200 sections split across mock Canvas + Moodle by override routing, provisioning with injected 429 throttling, 2,200 enrollment links (including a per-instance Moodle create_user override — exercising the override mechanism), induced LMS-side drift auto-repaired by reconciliation, and the full held-then-released passback cycle. Verified passing against the dev database (187s). The drill immediately caught two real bugs, which is its job: the roster queries referenced lookup_enrollment_status under the wrong schema, and reconciliation's missing-enrollment auto-repair short-circuited when the stale link still said ACTIVE (fixed: the link resets before the repair job enqueues).

Student Information System Documentation

Copyright 2026