Lookup Tables Reference
Complete reference for all lookup types in the SDASIS Student Information System.
For non-lookup database tables, see Database Table Reference.
Overview
Lookup values are stored in individual lookup_* tables (e.g., lookup_program_status, lookup_course_status). Each table has the following columns:
| Column | Type | Description |
|---|---|---|
{name}_id | integer | Primary key, named after the table minus its lookup_ prefix — lookup_course_status.course_status_id. Never a bare id (migration 083) |
code | varchar | Short code, unique within the table |
display_value | varchar | Human-readable label |
is_active | boolean | Whether the value is available for selection |
sort_order | integer | Display ordering |
created_at, created_by, updated_at, updated_by | Audit columns |
Those eight are present on all 133 lookup tables. The rest are not universal, and assuming otherwise is the most common way to break a lookup query:
| Column | Actually present in |
|---|---|
description | 4 tables |
display_value_es | 10 tables |
display_value_fr | 10 tables |
is_sensitive, uses_dates, allow_multiple, country_mode, state_mode, input_mask, validation_regex | lookup_identifier_type only |
uses_dates, allow_multiple, requires_major | lookup_cohort_type only |
So a query joining display_value_es across lookup types will fail on almost all of them.
Extended lookups
Two tables carry behavioral columns rather than just labels — flags that drive validation and what the UI renders, not merely how a value is displayed:
| Type | Table | What the extra columns decide |
|---|---|---|
IDENTIFIER_TYPE | core.lookup_identifier_type | Whether an identifier is encrypted at rest, whether it expires, whether an issuing country and state apply, whether a person may hold more than one, and what input format is valid (migrations 120a, 182, 194) |
COHORT_TYPE | records.lookup_cohort_type | Whether a cohort carries start/end dates, whether a student may hold more than one open membership at a time, whether a major is required (migration 181), and whether programs and an advisor can be bulk-assigned to its members (allows_bulk_assign, migration 277) |
Both are read and written by their owning domain, not through the generic lookup layer:
people.IdentifierType—GET/POST /people/identifier-types,PATCH|DELETE /people/identifier-types/{id}roles.CohortType—GET/POST /roles/cohort-types,PATCH|DELETE /roles/cohort-types/{id}
The generic layer still reads both types their four standard fields, so an existing dropdown keeps working, and refuses every write to them: POST/PATCH/DELETE /core/lookup-values return 409 naming the owning endpoint (core.refuseExtendedWrite). It cannot serve the extra columns: lookupEntryCols/lookupEntryTargets (core/repository.go) build a fixed select list scanned positionally, and CreateEntry/ UpdateEntry have no write path for extra columns at all. That is also why lookup_visa_type.metadata has sat unread since it was added: a behavioral column reachable only through the generic layer is a column nothing can use. Migration 281 seeded student_status and sevis flags into it for every visa type, and still nothing reads them -- the International tab decides whether to show its SEVIS block from the HOME_COUNTRY setting instead. lookup_cohort_group.is_billing_relevant was the other example, and it ended the way that argument predicts -- migration 185 superseded the table with records.cohorts and 186 dropped it, flag and all. The six codes it marked (AHMBA, SON24, SON25, UKIH23, UKIH24, UKIH25) are now discoverable from billing_method_configs.cohort_id instead of from a boolean nobody maintained.
LookupTypeMeta.ExtendedAdminRoute marks these types, and the admin lookup screen lists their values read-only behind a button to the screen that owns them. ExtendedAPIPath is the endpoint named when a generic write is refused — a separate field because the caller being turned away is an API client, and a Vue route would be no use to it.
Why the refusal, and not just the UI change. The link used to sit beside a fully-enabled Edit button reading "cannot be edited here", and an administrator hit the pencil, got the four-field form, and concluded the behavioural settings did not exist. Worse, the generic writers are not merely incomplete for these two types, they are wrong:
CreateEntrynames four columns and leaves the rest at their defaults.allow_multipledefaults TRUE, so an identifier type created generically silently permits two per person — the exact condition migration 195's unique index exists to prevent.UpdateEntrywritescode. Migrations and the daily ODS importer match it literally; the owning screens disable the field, but that only binds a browser.DeleteEntryis a bareDELETE.core.lookup_identifier_typehas two inbound FKs (person_identifiers,identifier_access_log), both NO ACTION, so it either raises a constraint error or removes a type that should have been deactivated.
A rule enforced only by a screen binds only that screen. Create and delete for these types therefore exist on the owning endpoint and nowhere else, and each owning service applies the rules the generic one cannot: a code pattern, a duplicate-code check, an RE2 compile of validation_regex, and a delete refused while either FK is populated — the audit trail included, because a reveal record has to outlive its subject.
That link was, for a day, the only way in. Both screens shipped complete -- views, routes, locale strings, and entries in the two frontend configs that bucket the Admin menu -- but neither had a row in core.permission_nodes, and the sidebar is built entirely from that table (GET /authorization/user-menu -> filterMenuTree, consumed by useMenuModel.ts; AppLayout.vue carries no static entries). No node, no menu item, however finished the screen. Migration 193 created ADMIN.IDENTIFIER_TYPES and ADMIN.COHORT_TYPES and moved the write routes onto them, so the node that puts a screen in the menu is the node that authorizes its Save button. A new admin screen needs its permission node in the same change as its route -- the name must match the frontend configs exactly, because they join on the English name rather than the code.
Alias the key when scanning
The shared core.LookupEntry struct expects a column called id, so single-table SQL should alias it: SELECT course_status_id AS id, code, display_value .... Use core.LookupTableName(typeCode) and core.LookupIDColumn(typeCode) rather than hard-coding either name.
The registry in core/lookup_registry.go maps type codes to table names: 130 registered types against 130 lookup_* tables in the database, spread across the core (41), records (70), admissions (12), studentfinance (6) and financialaid (1) schemas. The Go code names them unqualified and relies on search_path.
The two counts are meant to match exactly, and TestLookupColumnsSelectableForEveryType fails if a registered type has no table. They drifted apart historically because a table could be dropped without deregistering its type, or registered without ever being created; migration 262 closed the last gap by dropping lookup_section_enrollment_status, which shadowed lookup_enrollment_status with the same ids and opposite meanings.
The registry and the schema can drift for a deploy, so the bulk endpoints (GET /core/lookups, GET /core/lookup-types) skip a registered type whose table is missing and log a warning, rather than failing the whole response. They used to fail: migration 177 dropped lookup_hold_type and lookup_hold_reason while the registry still listed them, and both endpoints returned 500 for every signed-in user until the entries were removed.
How to Find Lookup Codes
- Navigate to Admin > Lookup Tables
- Select a lookup type
- View available codes and descriptions
API Access
GET /api/v1/core/lookups/by-type/{typeCode}Example: GET /api/v1/core/lookups/by-type/PROGRAM_STATUS
Person & Demographics
Identity & Contact
| Type Code | Description |
|---|---|
NAME_TYPE | Name variant type: LEGAL_PRIMARY, LEGAL_SECONDARY, PREFERRED, MAIDEN, FORMER |
ADDRESS_TYPE | Address category (Permanent, Mailing, Campus) |
PHONE_TYPE | Phone number type (Mobile, Home, Work) |
EMAIL_TYPE | Email type (Institutional, Personal, Work) |
IDENTIFIER_TYPE | Government ID type (SSN, Passport, Driver's License; I-94, EAD and I-20 / DS-2019 since migration 281) |
DOCUMENT_TYPE | Document type for name display configuration |
Demographics
| Type Code | Description |
|---|---|
GENDER | Gender identity options |
ETHNICITY | Hispanic/Latino ethnicity classification (IPEDS) |
RACE | Race categories (IPEDS multi-select) |
RELIGION | Religious affiliation |
MARITAL_STATUS | Marital status |
VETERAN_STATUS | Military/veteran status |
PRIMARY_LANGUAGE | Primary spoken language |
HOUSING_STATUS | Student housing classification |
CLASS_STANDING | Academic class standing (Freshman, Sophomore, Junior, Senior) |
VISA_TYPE | Immigration status type: STUDENT, RESIDENT, H1B (migration 273), F1, J1, M1, DEPENDENT, REFUGEE, OTHER (migration 281). Codes are identity |
Relationships & Activities
| Type Code | Description |
|---|---|
FAMILY_RELATIONSHIP | Family member relationship (Parent, Sibling, Spouse) |
RECOMMENDER_RELATIONSHIP | Recommender's relationship to applicant (Professor, Employer) |
RELATIONSHIP | Generic relationship type |
EDUCATION_LEVEL | Highest education level attained (for family members) |
RECOGNITION_LEVEL | Honor/award recognition level (Local, Regional, National) |
ESSAY_TYPE | Application essay category |
ACTIVITY_TYPE | Extracurricular activity type (Sport, Club, Volunteer) |
PHOTO_STATUS | Photo approval workflow status |
Academic Structure
| Type Code | Description |
|---|---|
TERM_TYPE | Academic term category (Fall, Spring, Summer, Winter, J-Term) |
DEGREE_LEVEL | Degree classification (Certificate, Associate, Bachelor, Master, Doctoral) |
COURSE_LEVEL | Course level classification (100-level, 200-level, Upper Division) |
COURSE_ATTRIBUTE | Course attribute flags (Writing-Intensive, Lab, Capstone) |
REQUISITE_TYPE | Course requisite type (Prerequisite, Corequisite, Antirequisite) |
EQUIVALENCE_TYPE | Course equivalency relationship type (Direct, Substitute) |
MIN_GRADE | Minimum grade thresholds for requirements (C, C+, B, etc.) |
BLOCK_TYPE | Requirement block category (General Ed, Major, Minor, Elective, Core) |
REQUIREMENT_TYPE | Individual requirement type (Course, Non-Course, Pattern) |
NON_COURSE_REQ_TYPE | Non-course requirement type (Service Hours, Portfolio, Exam) |
TEACHING_FREQUENCY | How often a course is typically taught (Every Term, Annually, Alternate Years) |
BULLETIN_TYPE | Bulletin/catalog type (Undergraduate, Graduate, Professional) |
CREDIT_SYSTEM | Credit system type (Quarter, Semester, Trimester, Clock Hours) |
Instruction & Scheduling
| Type Code | Description |
|---|---|
INSTRUCTION_MODE | Section modality — where the student is (In Person, Online, Hybrid, Remote). Derived by the importer, not declared |
INSTRUCTIONAL_METHOD | Section pedagogy — what kind of teaching it is (Lecture, Laboratory, Seminar, …, 26 values). Declared by Colleague and stored verbatim |
DELIVERY_MODE | Additional delivery mode classification |
SCHEDULE_TYPE | Schedule source (Template-Based, Custom) |
Status Types
Program & Catalog Statuses
| Type Code | Description |
|---|---|
PROGRAM_STATUS | Academic program lifecycle (Active, Inactive, Suspended, Discontinued) |
COURSE_STATUS | Course catalog status (Active, Inactive, Pending) |
SECTION_STATUS | Course section status (Open, Closed, Cancelled) |
CLASS_STATUS | Class status |
BULLETIN_STATUS | Bulletin lifecycle (Draft, Published, Archived) |
DEGREE_STATUS | Degree program status |
SUBSTITUTION_STATUS | Course substitution approval status |
Student & Role Statuses
| Type Code | Description |
|---|---|
STUDENT_STATUS | Student enrollment status (Active, Graduated, Withdrawn, Leave, Dismissed) |
ACADEMIC_STATUS | Academic standing (Good Standing, Probation, Suspension) |
ADMISSION_STATUS | Admission decision status (Admitted, Denied, Conditional, Waitlisted) |
TEACHER_STATUS | Teacher active/inactive status |
APPLICANT_STATUS | Applicant processing status |
Enrollment & Registration Statuses
| Type Code | Description |
|---|---|
ENROLLMENT_STATUS | Term enrollment status (Registered, Waitlisted, Dropped, Withdrawn) |
WAITLIST_STATUS | Waitlist entry status |
PLAN_STATUS | Course planning status (Planned, Confirmed, Registered) |
Degree Progress & Graduation Statuses
| Type Code | Description |
|---|---|
DEGREE_PROGRESS_STATUS | Degree audit progress status |
GRADUATION_STATUS | Graduation application status |
GRADUATION_TYPE | Type of graduation (Regular, Summer Completion, Early) |
TRANSFER_CREDIT_STATUS | Transfer credit evaluation status: PENDING, APPROVED, REJECTED, and PRE_APPROVED (migration 295), a course approved before it is taken, which counts nowhere until Records posts the grade |
Billing & Payment Statuses
| Type Code | Description |
|---|---|
CHARGE_STATUS | Billing charge status |
PAYMENT_STATUS | Payment transaction status |
BILLING_TRANSACTION_STATUS | Billing transaction lifecycle status |
PAYMENT_PLAN_STATUS | Payment plan enrollment status |
PAYMENT_SCHEDULE_STATUS | Individual installment status |
Admissions Statuses
| Type Code | Description |
|---|---|
CHECKLIST_STATUS | Application checklist item status (Pending, Received, Waived) |
VERIFICATION_STATUS | Document verification status |
RECOMMENDATION_STATUS | Recommendation letter status |
Workflow Statuses
| Type Code | Description |
|---|---|
WORKFLOW_STATUS | Workflow definition lifecycle (Draft, Active, Archived) |
WORKFLOW_STEP_STATUS | Step completion status |
FORM_TEMPLATE_STATUS | Form template lifecycle (Draft, Published, Archived) |
Roles & Authorization
| Type Code | Description |
|---|---|
STUDENT_TYPE | Student classification (Traditional, Non-Traditional, Transfer) |
ADVISOR_TYPE | Advisor classification (Faculty, Staff, Peer) |
ADVISOR_ROLE | Advisor assignment role (Primary, Secondary, Department) |
INSTRUCTOR_ROLE | Instructor role in a section (Primary, TA, Guest) |
CREDENTIAL_TYPE | Teacher credential type |
ORG_MEMBERSHIP_ROLE | Organization membership role |
SYSTEM_ROLE | System-level role classification |
IPEDS_RESIDENCY | IPEDS residency classification (In-State, Out-of-State, International) |
IPEDS_STUDENT_TYPE | IPEDS student type (First-Time, Transfer, Continuing) |
Registration Transactions
| Type Code | Description |
|---|---|
TRANSACTION_TYPE | Registration transaction type (Add, Drop, Swap) |
TRANSACTION_STATUS | Transaction workflow status |
TRANSACTION_ACTION | Action type for enrollment items (Add, Drop) |
TRANSACTION_ITEM_STATUS | Individual item status within a transaction |
VALIDATION_STATUS | Validation check status |
INITIATOR_ROLE | Who initiated the transaction (Student, Advisor, Registrar) |
PLANNER_ROLE | Who planned the courses |
ENROLLMENT_INTENSITY | Enrollment load (Full-Time, Part-Time, Half-Time) |
CREDIT_SOURCE | Source of earned credit |
CREDIT_TYPE | Transfer credit type classification |
Admissions
| Type Code | Description |
|---|---|
ADMISSION_DECISION | Final admission decision (Accept, Deny, Defer, Waitlist) |
APPLICATION_TYPE | Application category (Freshman, Transfer, Graduate, Readmit) |
CHECKLIST_TYPE | Checklist item category |
REVIEW_RECOMMENDATION | Reviewer's recommendation (Admit, Deny, Hold) |
TEST_TYPE | Standardized test type (SAT, ACT, GRE, TOEFL) |
DOCUMENT_TYPE_APP | Application document type (Transcript, Essay, ID) |
Billing & Financial
| Type Code | Description |
|---|---|
PAYMENT_METHOD | Payment method (Credit Card, Check, Wire, Financial Aid) |
AWARD_TYPE | Financial aid award type |
Degree Audit & Graduation
| Type Code | Description |
|---|---|
WAIVER_TYPE | Requirement waiver type (Course Waiver, Credit Waiver, Substitution) |
HONORS_TYPE | Latin graduation honor, one per degree (Cum Laude, Magna Cum Laude, Summa Cum Laude); imported from Colleague |
GRADUATION_HONOR | Additional graduation honors entered by the registrar, several per degree (language, music, leadership) |
Workflow & Automation
| Type Code | Description |
|---|---|
WORKFLOW_STEP_TYPE | Workflow step category (Search, Cart, Payment, Confirm) |
WORKFLOW_CONDITION_TYPE | Transition condition type |
AUTOMATION_RULE_TYPE | Automation rule category (Hold Check, Prerequisite, Balance) |
Organization & Facilities
| Type Code | Description |
|---|---|
ROOM_TYPE | Room classification (Classroom, Lab, Auditorium, Office) |
ROOM_FEATURE | Room features/equipment (Projector, Whiteboard, AV System) |
ORGANIZATION_TYPE | Organization classification |
Communication
| Type Code | Description |
|---|---|
COMMUNICATION_TYPE | Communication channel (Email, Phone, Letter, In-Person) |
COMMUNICATION_DIRECTION | Communication direction (Inbound, Outbound) |
Proxy Access
| Type Code | Description |
|---|---|
PROXY_RELATIONSHIP_TYPE | Proxy-to-student relationship (Parent, Guardian, Spouse) |
Course Materials
| Type Code | Description |
|---|---|
MATERIAL_TYPE | Material classification (Textbook, Coursepack, Software, Equipment) |
MATERIAL_FORMAT | Material format (Print, Digital, eBook, Loose-Leaf) |
MATERIAL_REQUIREMENT | Material requirement level (Required, Recommended, Optional) |
ADOPTION_STATUS | Faculty adoption status for materials |
OER_LICENSE | Open Educational Resource license type (CC-BY, CC-BY-SA, etc.) |
Forms & Templates
| Type Code | Description |
|---|---|
FORM_TEMPLATE_STATUS | Form template lifecycle (Draft, Published, Archived) |
FORM_FIELD_TYPE | Form field input type (Text, Select, Checkbox, Date) |
FORM_REQUIREMENT_TYPE | Form field requirement level |
LMS Integration
| Type Code | Description |
|---|---|
LMS_PLATFORM_TYPE | LMS platform family (Canvas, Moodle, Blackboard, etc.) |
LMS_URL_TYPE | LMS platform URL purpose (Base, API, OAuth, LTI) |
LMS_SYNC_TYPE | LMS sync operation type (Enrollment, Grade, Course, Roster) |
LMS_SYNC_STATUS | LMS sync operation status (Pending, Success, Failed) |
Authentication & System
| Type Code | Description |
|---|---|
ACCESS_TOKEN_TYPE | API token type (Personal, Application, Integration) |
LANGUAGE | Language codes for translations |
Using Lookup Values in Code
Go Backend
// The lookup registry maps type codes to table names
tableName := core.LookupTableName("COURSE_STATUS") // -> "lookup_course_status"
// Check if a type code is valid
if core.IsValidLookupType("COURSE_STATUS") {
// type is registered
}
// Get all known type codes (sorted)
allTypes := core.KnownLookupTypes()
// Get metadata for a type
meta, ok := core.GetLookupTypeMeta("COURSE_STATUS")
// meta.Category = "Program & Catalog Status"
// meta.Description = "Course catalog status (Active, Inactive, Pending)"Frontend
// Using the lookups store (cached)
import { useLookupsStore } from '@/stores/lookups'
const lookups = useLookupsStore()
const statuses = await lookups.getByType('COURSE_STATUS')
// Or via API directly
const response = await api.get('/core/lookups/by-type/COURSE_STATUS')Filtering by Lookup Value
// Filter entities by status ID
const params = { status_id: activeStatusId }
const response = await courseApi.getAll(params)Additional lookup types
These exist in the database but were missing from the sections above.
| Type Code | Description | Codes |
|---|---|---|
ACADEMIC_LEVEL | Level a course attempt counts at. GPA and credits are computed per level, so a student can hold a separate undergraduate and graduate GPA in the same term | UG, GR, HS, CE, NC |
ORG_UNIT_TYPE | Tier in the institutional hierarchy | INSTITUTION, DIVISION, COLLEGE, OFFICE, DEPARTMENT |
ORG_ADMIN_LEVEL | Seniority of a unit administrator appointment | PRESIDENT, VP, DEAN, ASSOC_DEAN, CHAIR, DIRECTOR |
RELATIONSHIP_TYPE | Family relationship. Codes are terse: P parent, C child, S spouse, SB sibling, GP grandparent, GC grandchild | P, C, S, SB, GP, GC |
PROXY_PERMISSION_TYPE | What a proxy may see — granted individually, never as a bundle | VIEW_GRADES, VIEW_SCHEDULE, VIEW_TRANSCRIPT, VIEW_GPA, VIEW_HOLDS, VIEW_ENROLLMENTS |
PLAN_ASSIGNMENT_STATUS | State of a housing or meal plan assignment | REQUESTED, ACTIVE, CANCELLED, COMPLETED, NO_SHOW |
COHORT_GROUP | Named intake groups | UKIH23, SON24, AHMBA, … |
ACA_CAMPUS | Adventist Colleges Abroad partner campuses | UAP, BOGENHOFEN, UNASP, SALEVE, … |
ACADEMIC_LEVEL is the one worth reading twice. It was added by migration 097 so that concurrent undergraduate and graduate enrolment in the same term are distinct rows, which widened the (student_id, term_id) uniqueness constraint to include the level. Code that assumes one GPA per student per term is wrong.