Domain Model and Database

The transcript service is a classic document workflow domain, and it maps cleanly onto EF entities and SQL Server tables. Get the model right and the controllers stay boring — which is exactly what you want.

The core entities

• Candidate — the person whose coursework is being evaluated. PII lives here; guard it.
• TranscriptDocument — one uploaded file (PDF/scan) tied to a Candidate and the submitting EPP. Store the file in blob storage or FILESTREAM, never as raw bytes in a wide table; keep metadata (hash, page count, upload timestamp) in the row.
• Institution — the college that issued the transcript. Normalize it; institution names on scanned transcripts are messy, and you'll want one canonical row per school.
• Course — a structured coursework row extracted from a document: course code, title, units, grade, term. Many Courses per TranscriptDocument.
• SubjectMatterRequirement — the Commission's rubric. SMRs form a hierarchy (subject → domain → specific requirement), so give the table a self-referencing ParentId and a MethodologyVersionId (Lesson 4 explains why the version matters).
• AlignmentFinding — the heart of the system: "Course X provides evidence toward SMR Y," with a confidence score, the evidence text, and whether it was AI-suggested, analyst-accepted, or analyst-overridden.
• Review and Determination — a Review is an analyst's working session over one submission; a Determination is the final, immutable outcome (met / not met, per subject).
• GapReport — the generated portable report: which SMR domains are satisfied, which have gaps, and the methodology version used.

The relationships are unremarkable one-to-many chains, which is good news: EF handles them without cleverness, and the Kendo work queues in Lesson 3 are simple grid queries.

Status workflows as state machines

Every submission moves through explicit states: Submitted → Extracting → ReadyForReview → InReview → Determined → ReportIssued, with side exits like NeedsDocuments and Withdrawn. Model this as a state machine, not a pile of booleans:

public enum SubmissionStatus { Submitted, Extracting, ReadyForReview,
    InReview, NeedsDocuments, Determined, ReportIssued, Withdrawn }

Then enforce legal transitions in one service method (TransitionTo(submission, next, userId)) that validates the move, stamps the timestamp, and writes a history row. The alternative — IsReviewed, IsComplete, HasReport flags scattered across tables — always ends with rows in impossible combinations that no one can explain to an auditor.

SQL Server schema thinking

A few pragmatic rules for this domain: use datetime2 with UTC everywhere (determinations get disputed; timestamps get scrutinized); put a rowversion column on anything analysts edit concurrently, and wire it into EF's ConcurrencyCheck; index the columns your queues filter on (Status, AssignedAnalystId, SubmittedUtc); and make history tables append-only — grant the app INSERT but not UPDATE/DELETE on them. The database itself becomes part of your audit story.