Lookup Tables

Manage reference data.

Overview

Lookup tables store reference data used throughout the system:

• Status values (program, course, enrollment, etc.)
• Type codes (address, phone, email, etc.)
• Categories (hold types, block types, etc.)

Each lookup type is stored in its own database table (e.g., lookup_program_status, lookup_course_status). There are 129 lookup types in the system.

Viewing Lookups

1. Navigate to Admin > Lookup Tables
2. Select lookup type from the dropdown
3. View values in the table

API Access

• GET /api/v1/core/lookups - List all lookup types
• GET /api/v1/core/lookups/by-type/{typeCode} - Get values for a type
• POST /api/v1/core/lookup-values - Create a new value
• PATCH /api/v1/core/lookup-values/{typeCode}/{id} - Update a value

Lookup Types

Common lookup types:

Type	Purpose
PROGRAM_STATUS	Program active/inactive
COURSE_STATUS	Course active/inactive
ENROLLMENT_STATUS	Registration status
ADDRESS_TYPE	Address categories
PHONE_TYPE	Phone number types
EMAIL_TYPE	Email address types
TERM_TYPE	Term categories
GENDER	Gender options
ETHNICITY	Ethnicity categories
ADMISSION_STATUS	Admission decision status
BLOCK_TYPE	Requirement block types

See Lookup Codes for the complete list with values.

Managing Values

Adding Values

1. Select lookup type
2. Click Add Value
3. Enter code and display value
4. Set sort order
5. Save

Editing Values

1. Click on value
2. Edit display value or sort order
3. Save

WARNING

Changing codes may affect existing data. Add new values rather than changing existing codes.

Deactivating Values

Set is_active to false to hide a value from dropdowns without deleting it. Existing records referencing the value will still display correctly.

System vs Custom

• System - Required by application, cannot delete
• Custom - Institution-specific, can modify

Two lookups are not editable here

Most lookup types are just code, label, sort order and active flag, and the generic editor handles them. Two carry behavior, and the generic layer can neither read nor write those columns — it selects a fixed list of columns, scanned positionally, with fixed INSERT and UPDATE column lists.

Type	Table	Extra columns	Edited at
IDENTIFIER_TYPE	core.lookup_identifier_type	is_sensitive, uses_dates, allow_multiple, country_mode, state_mode, input_mask, validation_regex	Identifier Types
COHORT_TYPE	records.lookup_cohort_type	uses_dates, allow_multiple, requires_major, unique_per_level, requires_campus, requires_start_term, supports_auto_sync	Cohort Types

Both stay registered, so ordinary dropdowns keep working, and the generic layer still reads them. It refuses every write: POST, PATCH and DELETE on /core/lookup-values return 409 naming the owning endpoint.

Why the refusal, rather than just hiding the form

Each of the three generic write paths would do real damage:

• The generic INSERT leaves allow_multiple at its TRUE default — a type created generically silently permits duplicates, including duplicate SSNs.
• The generic UPDATE rewrites code, which migrations and the ODS importer key on.
• The generic DELETE is a bare DELETE against tables with NO ACTION inbound foreign keys.

Create and delete for these two types exist only on the owning endpoint. The generic admin screen links out to it rather than offering a form that would leave those columns at their defaults.

Each owning screen has its own permission node — ADMIN.IDENTIFIER_TYPES and ADMIN.COHORT_TYPES (migration 193). Both screens were unreachable before that migration for exactly that reason: the sidebar is built from core.permission_nodes, so a screen with no node never appears.

Each Lookup Table Structure

Every lookup_* table has the same 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
code	VARCHAR	Unique code (e.g., ACTIVE)
display_value	VARCHAR	Display text (e.g., Active)
is_active	BOOLEAN	Whether value appears in dropdowns
sort_order	INT	Display ordering
created_at, created_by, updated_at, updated_by		Audit columns

description is not one of them: only four tables have it, and this screen has never offered a field for it.

Types with their own screen

Two types carry settings this screen cannot edit, because those settings change behaviour rather than just labels. Both appear in the Admin menu in their own right, and selecting either one here lists its values read-only with a button through to the screen that owns them:

Type	Screen	What it configures
Identifier Types	Admin → Identifier Types	Whether the identifier expires, whether an issuing country and state apply, whether a person may hold more than one, and the input format (mask or pattern)
Cohort Types	Admin → Cohort Types	Whether cohorts carry start/end dates, whether a student may belong to several at once, and whether a major is required

These two are the only types this screen will not let you edit, add to, or delete from. That is deliberate rather than an oversight: the four fields here are not a subset of what such a type needs, they are a misleading subset. A type added here would arrive with "may hold more than one" switched on, because that is the column default, so a brand-new identifier type would quietly allow two per person. Everything you can do here you can do on the owning screen too, plus the settings that actually matter, so nothing is lost by going there.

How many identifiers of a type a person may hold

Two separate settings, and it is worth knowing which one you are changing:

• Allow multiple, on the Identifier Types screen, decides whether a person may hold more than one of that type at all. Turn it off for a Social Security Number; leave it on for a passport. The screen refuses to turn it off while somebody already holds two — it names how many people are affected, so the records can be resolved first.
• The issuing location is always unique regardless. Even for a type that allows several, a person may hold only one per issuing country and state: two passports from two countries is fine, two US passports is not, and one driver's licence per state is the intended shape.

A person who already holds a single-instance type simply is not offered it again — that type does not appear in the dropdown when you add an identifier.

Adding or removing an identifier type

Both are on the Identifier Types screen and nowhere else. New Type asks for the code up front, because that is the one field that can never be changed afterwards — migrations and the nightly Colleague import match it literally. "Store values encrypted" is offered only while creating, for the same reason in reverse: turning it on later would leave every identifier already on file unencrypted while the system believed otherwise, which needs a separate re-encryption run.

Deleting is refused while anything still points at the type, and the message says what: the identifiers recorded under it, or — even after those are gone — the log of who has viewed one. That log is deliberately impossible to discard this way. Clearing Active is the way to retire a type: it stops new identifiers being recorded under it while everything already on file keeps displaying.

Best Practices

• Use consistent naming (UPPER_SNAKE_CASE for codes)
• Document custom values
• Review periodically
• Deactivate unused values (don't delete)
• Add descriptions for non-obvious codes