TCR Transcript App

Overview

Status: active, in production use by the registrar. The Transcript App (internally TCR-TranscriptApp, hosted on the server Daedalus) replaces Populi’s native transcript generator. It exists to merge legacy Jenzabar (Sonis) data with Populi data — twin-record detection, Populi-authoritative rules per term, program-name dedup, and implied-prerequisite stacking — logic Populi’s own generator can’t do.

Why it exists

Populi is TCR’s third SIS (after Sonis by Jenzabar, then Campus Cafe — see Populi’s background for that full history), but TCR’s academic history predates it and lives in the legacy Jenzabar system. A student’s real transcript needs both: Populi’s current-and-future data plus Jenzabar’s historical records, reconciled into one accurate record. No off-the-shelf tool does that reconciliation, so this app was built in-house specifically to do it.

Before this app existed

Historical academic data was fragmented across TCR’s succession of prior systems, and before Wade, no one on-site knew SQL — so pulling a transcript was genuinely hard for the registrar’s office, requiring someone who could dig through fragmented, non-technical-friendly data by hand.

Now, the registrar just types a student’s name into the app, and it pulls up their transcript, which she can print to PDF with or without an official seal. Parchment handles incoming external transcript requests; this app is what the registrar uses to actually produce the transcript that fulfills them.

Architecture

Single box (Daedalus):

Cloudflare Access / Tunnel
        ↓
Waitress (NSSM service: TCR-TranscriptApp)
        ↓
Flask app — C:\transcript-app\app_v2.py
        ↓
SQL Server DAEDALUS\SQLABYRINTH — DB: TCR_StudentRecords

• SQL Server listens on port 1433. External hosts (e.g. n8n, which runs on a separate Hyper-V/Ubuntu host) must connect via the LAN IP 10.0.20.39 — hostname resolution doesn’t work cross-host.
• Transcript PDFs are rendered with WeasyPrint.
• The app is exposed externally through Cloudflare Access/Tunnel rather than a direct public port.

Data: how it gets here

Every night, an n8n workflow pulls students, terms, and term enrollments from the Populi API into TCR_StudentRecords. See Populi for the sync’s API constraints and the incidents that shaped it, and n8n for where the workflow runs.

Schema

Normalized, app-facing tables: Students, Enrollments (FinalGrade, AttemptedCredits, EarnedCredits, QualityPoints), Terms, StudentDegrees (unique key on DataSource+SourceID), StudentPrograms, TranscriptNotes, TransferCredits, Courses, Programs, GradeScale.

Legacy Jenzabar tables coexist alongside these: STBIOS, RGENRL, TRK_ATTACHMENT, and others.

Sync account: n8n_sync SQL login, with db_datareader + db_datawriter roles, CHECK_POLICY OFF (set after a lockout incident).

Operational patterns

• The registrar (Johnna) authorizes graduation grants and data corrections; Wade executes them directly in SSMS.
• Bulk grants use idempotent INSERT ... WHERE NOT EXISTS scripts with synthetic SourceID keys, so they’re safe to re-run.
• SQL style rule: single auto-commit statements only — avoid BEGIN TRAN wrappers. Repeated stuck-transaction incidents in SSMS have blocked the live app in the past.
• Restart procedure: code/template changes require an elevated Restart-Service TCR-TranscriptApp; data-only changes need no restart.
• FERPA: student names never appear in chat/tickets about this system — numeric StudentIDs only.

Access

Admin/database access is via SSMS on Daedalus. The app itself is reached externally through Cloudflare Access/Tunnel.

Not yet documented: who besides Wade has SSMS/database access, the full Cloudflare Access policy for this app.

Dependencies

Depends on the nightly n8n → Populi API sync for current data — if that sync fails, transcript data goes stale until the next successful run. The registrar’s transcript generation workflow depends entirely on this app being up.

Backup & recovery

Status: setup in progress, for the TCR_StudentRecords database specifically:

• Automated via SQL Server Agent.
• Schedule: weekly full, nightly differential, 30-minute log backups.
• Destination: P:\SQLBackups\.
• Resolved: a transaction log full error (Msg 9002) on TCR_StudentRecords.

Open:

• A cleanup job to age out old backup files hasn’t been built yet.
• Need to confirm SQL Server Agent status and available space on P:\.

Open items

• Dependabot alerts pending on the app’s repo.
• The sql/ folder is not currently committed to git.
• Ad-hoc probe/debug scripts are accumulating and need cleanup.

Notes

This is the one piece of TCR’s stack that’s fully custom-built rather than a vendor product — worth treating as higher-risk from a “what happens if the one person who understands it is unavailable” perspective, which is exactly the kind of thing this site exists to capture. This page is also the fullest example on this site of the level of runbook detail worth aiming for elsewhere (restart procedures, known incidents and their actual root causes, operational rules and why they exist).

The server name isn’t arbitrary either — see Naming Conventions for the mythology behind Daedalus, and the deliberate pun in SQLABYRINTH.