SIS Database ER Diagrams (core + records + studentfinance + lms + payments)
Superseded — historical revision only
This is revision 9 (2026-10-27), kept for history. It stops at migration 300 (plus 308), and the database is now at 313.
Use the current ER diagram instead (revision 10), or read it as one page.
Generated 2026-10-27 from the live PostgreSQL database (revision 9).
Schemas: core (80 entities + 41 lookup_*), records (136 entities + 70 lookup_*), studentfinance (38 entities + 6 lookup_*), lms (24 entities + 0 lookup_*), payments (4 entities + 0 lookup_*) — 282 entities drawn across 23 sections.
Generated, not hand-written. Revisions 3–6 were maintained by hand and drifted from the schema they described; revision 6 contained two entity blocks with no header at all, so core.departments and core.colleges_schools were referenced by relationships that pointed at nothing and never rendered. Revision 7 fixed that by emitting from information_schema, but its generator was never committed, so it could not be re-run. Since revision 8 the diagram is produced by docs/generate_erd.py, which is committed — every attribute, type and relationship below is what the database actually holds.
Revision 8's file is er-diagram-7.md, a name that lagged its own revision number; from revision 9 the file is named after the revision it holds.
Conventions:
- Entity names carry their schema with a double underscore:
core__campuses,records__course_sections. Mermaid has no schema-qualified names. lookup_*tables are referenced by foreign key but not drawn — 163 such references exist, and drawing them would bury the structure. Since migration 083 each lookup table's primary key is named after the table minus itslookup_prefix (lookup_program_status.program_status_id).- Attribute types carry length and precision from the live schema. Numeric precision is written
numeric(p_s)with an underscore, notnumeric(p,s). That looks like a typo and is not: the Mermaid version in use when revision 6 set the convention rejected a comma inside an attribute-type token. Mermaid 11 permits one, but the underscore is kept so every revision since 6 reads alike. Unboundednumericcolumns are shown without precision, and array columns asint[],text[],varchar[]— brackets the grammar has always allowed. - Each attribute carries at most one marker, in the order PK > FK > UK. A column that is both a foreign key and uniquely constrained shows
FK. UKmarks single-columnUNIQUEconstraints only. A unique index with no constraint behind it is not marked —core.campuses.colleague_location_codeis one such column.- The four audit columns (
created_at,created_by,updated_at,updated_by) are present on nearly every table and are omitted from the diagrams. - A section draws only the relationships whose two ends both live in it. Edges that leave a section are listed in the two summaries at the end rather than drawn, so an entity block is never the whole story about what points at it. Revision 7 followed this rule without stating it and without listing the edges it dropped.
- The
admissionsandfinancialaidschemas are not diagrammed. Both are retired — admissions moved to a separate system in August 2026 and financial aid is deliberately unreachable — but the schemas and their rows survive, and foreign keys still point into them from the schemas drawn here.
Revision 9 changes (migrations 264–300). Revision 8 covered 168–263; its changelog is in
er-diagram-7.
- 17 entities added, none removed. Revision 8 drew 265; this draws 282.
- 268 — staff-to-student email gets a section of its own, §22: an outbox (
email_messages, oneemail_message_recipientsrow per student, sent one message per recipient so no student sees another's address), per-role recipient caps inrole_email_policies, and shared institutional mailboxes insender_addresseswithsender_address_grantssaying who may send as each.- 291 — role network rules (§3):
networksare named sets ofnetwork_cidrs, androle_network_rulesgives each role ALLOW or DENY per network, evaluated on every request against the client address. A role with no ALLOW is unusable everywhere.- 266 —
roles.session_timeout_minutes: a role may shorten the session idle timeout, never extend it.- 264 —
audit_log.client_hostname, beside theip_addressanduser_agentcolumns the audit trigger now actually writes.- 281 — international students:
person_citizenshipsholds citizenships beyondpeople.citizenship_country_id, andperson_visa_statusgains the SEVIS and immigration columns (program and entry dates, port of entry, sponsor and financial support, work authorization,sevis_status,form_type). Who counts as international is theHOME_COUNTRYsetting.- 298 — Colleague's display names:
teachers.display_first_name/display_last_name(the faculty listing name, which outranks the preferred name on course screens), andperson_names.sourcemarking the rows the importer owns.- 271 —
student_leaves: one row per approved leave of absence, written by the workflow completion hook, which also moves the student to LEAVE.- 283 —
graduation_record_honorsandlookup_graduation_honor: honors the registrar confers by hand, beside the single Latin honor the importer owns ongraduation_records.- 285, 286 —
course_requisite_linkslinks a course to every Colleague requisite rule it carries, with its timing (prior, concurrent or either), so corequisites exist. Minimum-grade rules land on the existingrequirement_blocks.min_grade_id, andallowed_gradeson blocks and groups restricts which grades satisfy them.- 287, 288 — repeat limits: a major may set
max_course_retakesandwithdrawal_counts_as_attempt, reconciled against the institution-wideREPEAT.*rows 288 moved intoacademic_policy_rules(the stricter wins unless the rule allows a program override). The registrar's override is recorded onplanned_courses(repeat_overridden, reason, by, at) andregistration_transactions.override_repeat.- 279 — variable credit:
course_sections.is_variable_creditandcredit_incrementsay whether and in what steps a student chooses, andplanned_courses.credits_selectedkeeps the choice through to registration.- 278 —
registration_transaction_notes: permanent notes on a registration transaction.- 289 —
term_registration_windows: staged registration. Each window is a start date and a rule saying who it is for; a term with windows has exactly one rule-less "everyone else" window.- 296 — credit overload is requested inside the registration transaction:
is_overload_request,overload_reason,overload_credits, and the advisor's approval, recorded before the registrar's.- 276 —
academic_policy_rules.test_type_id: an institutional requirement can demand a minimum test score.- 292 —
student_section_enrollments.midterm_grade_sourceisSISorODS; the nightly import never overwrites a midterm grade typed in the SIS.- 293 —
course_sections.colleague_section_idfollows Colleague's section identity through a rename, andsection_instructors.sourcemarks the instructor rows Colleague owns, so the ones it removes are removed here.- 294, 295 — the Records Office forms became workflow requests.
workflow_steps.assignee_field_coderoutes a step on a form answer (the section's instructor, the chair of the course's department, a named person), and an approved incomplete-grade request writesincomplete_deadline,incomplete_default_grade_idandincomplete_workflow_instance_idonto the enrollment.- 299 — grade visibility (§9):
grade_visibility_ruleshold a term's official grades from the student's own view until a condition is met (a course evaluation recorded ingrade_visibility_completions, or a release ingrade_visibility_releases). Unlikegrade_releasesthis never affects GPA or the transcript, and it applies to Colleague grades too.- 300 — one add/drop deadline:
academic_terms.drop_deadlinenow equalsadd_deadlineand is no longer entered separately, except on past summer terms whose Colleague add deadline falls after the withdrawal deadline.- Not drawn, but schema: 290 adds seven
records.vw_ipeds_*reporting views (views are not diagrammed), and 269 adds trigram search indexes.
1. Core - Organization
mermaid
erDiagram
core__buildings {
int building_id PK
varchar(20) building_code UK
varchar(200) name
varchar(500) address
bool is_active
int campus_id FK
}
core__campuses {
int campus_id PK
varchar(20) campus_code UK
varchar(200) campus_name
bool is_online
bool is_open_to_all
varchar(200) address_line1
varchar(200) address_line2
varchar(100) city
varchar(50) state
varchar(20) postal_code
int country_id FK
varchar(50) phone
int sort_order
bool is_active
varchar(10) colleague_location_code
}
core__clubs_organizations {
int org_id PK
varchar(20) org_code UK
varchar(200) name
text description
int org_type_id FK
uuid advisor_person_id FK
bool is_active
}
core__colleges_schools {
int college_id PK
varchar(20) college_code UK
varchar(200) name
bool is_active
int org_unit_id FK
}
core__countries {
int country_id PK
varchar(2) iso_code UK
varchar(3) iso_code_3
varchar(100) country_name
bool visa_required
int sort_order
bool is_active
}
core__department_college_mapping {
int mapping_id PK
int department_id FK
int college_id FK
bool is_primary
}
core__departments {
int department_id PK
varchar(20) department_code UK
varchar(200) name
text description
int parent_department_id FK
bool is_active
varchar(50) gl_credit_hour_billing
varchar(50) gl_section_fee_billing
varchar(50) gl_lab_fee_billing
int org_unit_id FK
}
core__org_units {
int unit_id PK
varchar(30) unit_code
varchar(200) name
text description
int unit_type_id FK
int parent_unit_id FK
int sort_order
bool is_active
}
core__room_features {
int room_feature_id PK
int room_id FK
int feature_id FK
}
core__rooms {
int room_id PK
int building_id FK
varchar(20) room_number
int capacity
int room_type_id FK
bool is_active
}
core__state_provinces {
int state_province_id PK
int country_id FK
varchar(6) code
varchar(100) name
int sort_order
bool is_active
}
core__student_organization_membership {
int membership_id PK
int student_id FK
int org_id FK
int role_id FK
date start_date
date end_date
bool is_active
}
core__unit_administrators {
int admin_id PK
uuid person_id FK
bool is_active
date effective_date
date end_date
int org_unit_id FK
int admin_level_id FK
}
core__unit_course_codes {
int unit_course_code_id PK
varchar(20) course_code_prefix UK
int department_id FK
int college_id FK
}
core__campuses ||--o{ core__buildings : "campus_id"
core__countries ||--o{ core__campuses : "country_id"
core__org_units ||--o{ core__colleges_schools : "org_unit_id"
core__colleges_schools ||--o{ core__department_college_mapping : "college_id"
core__departments ||--o{ core__department_college_mapping : "department_id"
core__org_units ||--o{ core__departments : "org_unit_id"
core__departments ||--o{ core__departments : "parent"
core__org_units ||--o{ core__org_units : "parent"
core__rooms ||--o{ core__room_features : "room_id"
core__buildings ||--o{ core__rooms : "building_id"
core__countries ||--o{ core__state_provinces : "country_id"
core__clubs_organizations ||--o{ core__student_organization_membership : "org_id"
core__org_units ||--o{ core__unit_administrators : "org_unit_id"
core__colleges_schools ||--o{ core__unit_course_codes : "college_id"
core__departments ||--o{ core__unit_course_codes : "department_id"2. Core - People & Identity
mermaid
erDiagram
core__people {
uuid person_id PK
varchar(255) primary_email
date date_of_birth
int gender_id FK
varchar(3) nationality_country_code
varchar(3) citizenship_country_code
bool is_active
int religion_id FK
int ethnicity_id FK
int country_of_birth_id FK
int citizenship_country_id FK
varchar(50) person_id_number
date deceased_date
int marital_status_id FK
int veteran_status_id FK
int primary_language_id FK
bool ferpa_opt_out
uuid merged_into_person_id FK
varchar(32) legacy_person_id
}
core__person_activities {
int activity_id PK
uuid person_id FK
int activity_type_id FK
varchar(200) name
varchar(200) organization
varchar(200) position_title
date start_date
date end_date
bool is_current
numeric hours_per_week
int weeks_per_year
varchar(50) participation_grades
varchar(1000) description
varchar(1000) accomplishments
bool continue_in_college
int sort_order
bool is_active
}
core__person_addresses {
int legacy_address_id
uuid person_id FK
int address_type_id FK
varchar(255) address_line_1
varchar(255) address_line_2
varchar(255) address_line_3
varchar(100) city
varchar(100) state_province
varchar(20) postal_code
varchar(3) country_code
date effective_date
date end_date
bool is_current
bool is_primary
uuid person_address_id PK
varchar(32) legacy_id
}
core__person_emails {
int legacy_email_id
uuid person_id FK
int email_type_id FK
varchar(255) email_address
date effective_date
date end_date
bool is_current
bool is_primary
uuid person_email_id PK
varchar(32) legacy_id
}
core__person_essays {
int legacy_essay_id
uuid person_id FK
int application_id FK
int essay_type_id FK
varchar(2000) prompt
text content
int word_count
int version_number
bool is_current_version
varchar(20) status
timestamp submitted_at
uuid person_essay_id PK
varchar(32) legacy_id
}
core__person_family_members {
int legacy_family_member_id
uuid person_id FK
int relationship_id FK
varchar(100) first_name
varchar(100) last_name
bool is_living
bool is_deceased
varchar(254) email
varchar(30) phone
int education_level_id FK
varchar(200) college_name
int college_graduation_year
varchar(200) occupation
varchar(200) employer
int age
bool is_alumni
int alumni_graduation_year
int sort_order
uuid person_family_member_id PK
varchar(32) legacy_id
}
core__person_citizenships {
uuid person_citizenship_id PK
uuid person_id FK
int country_id FK
}
core__person_honors {
int honor_id PK
uuid person_id FK
varchar(200) honor_name
varchar(200) organization
date date_received
varchar(20) grade_level
int recognition_level_id FK
varchar(1000) description
int sort_order
bool is_active
}
core__person_identifiers {
int legacy_identifier_id
uuid person_id FK
int identifier_type_id FK
varchar(100) identifier_value
varchar(200) issuing_authority
date issue_date
date expiry_date
uuid person_identifier_id PK
varchar(32) legacy_id
bytea identifier_value_encrypted
bytea identifier_search_hash
int issuing_country_id FK
int issuing_state_province_id FK
}
core__person_merge_log {
int merge_log_id PK
uuid primary_person_id FK
uuid secondary_person_id FK
int merged_by_user_id FK
int tables_updated
text notes
timestamptz merged_at
}
core__person_name_display_config {
int config_id PK
varchar(50) config_code UK
varchar(200) format_pattern
varchar(500) description
}
core__person_names {
int person_name_id PK
uuid person_id FK
int name_type_id FK
varchar(100) first_name
varchar(100) middle_name
varchar(100) last_name
varchar(20) suffix
date effective_date
date end_date
bool is_current
bool is_searchable
varchar(8) source
}
core__person_phones {
int legacy_phone_id
uuid person_id FK
int phone_type_id FK
varchar(5) country_code
varchar(20) phone_number
varchar(10) extension
date effective_date
date end_date
bool is_current
bool is_primary
uuid person_phone_id PK
varchar(32) legacy_id
}
core__person_photos {
int photo_id PK
uuid person_id FK
int status_id FK
varchar(255) original_filename
varchar(255) stored_filename
int file_size_bytes
varchar(50) mime_type
int width
int height
int uploaded_by_user_id FK
timestamp uploaded_at
int reviewed_by_user_id FK
timestamp reviewed_at
text rejection_reason
bool is_current
int origin_id FK
bigint source_size_bytes
timestamp source_mtime
timestamp submitted_at
int submitted_by_user_id FK
timestamp superseded_at
}
core__person_races {
int person_race_id PK
uuid person_id FK
int race_id FK
}
core__person_recommendations {
int legacy_recommendation_id
uuid person_id FK
int application_id FK
varchar(200) recommender_name
varchar(254) recommender_email
varchar(30) recommender_phone
varchar(200) recommender_title
varchar(200) recommender_organization
int relationship_id FK
int years_known
int status_id FK
date requested_date
date reminder_sent_date
date received_date
bool waive_right_to_view
varchar(500) file_path
varchar(255) file_name
varchar(1000) notes
uuid person_recommendation_id PK
varchar(32) legacy_id
}
core__person_relationships {
int person_relationship_id PK
uuid person_id_1 FK
uuid person_id_2 FK
int relation_type_id FK
bool is_primary_participant
bool is_primary_relationship
varchar(20) source_id
date start_date
date end_date
int status_id FK
varchar(20) source_added_by
timestamptz source_added_at
varchar(20) source_changed_by
timestamptz source_changed_at
}
core__person_test_scores {
int legacy_test_score_id
uuid person_id FK
int test_type_id FK
numeric score
text subscore_json
date test_date
bool is_official
bool is_self_reported
date score_report_date
uuid person_test_score_id PK
varchar(32) legacy_id
}
core__person_visa_status {
int legacy_visa_status_id
uuid person_id FK
int visa_type_id FK
date visa_expiry_date
varchar(20) sevis_id
bool is_current
date effective_date
varchar(500) notes
uuid person_visa_status_id PK
varchar(32) legacy_id
varchar(20) status
date document_issue_date
date program_start_date
date program_end_date
date entry_date
varchar(100) port_of_entry
date orientation_date
varchar(200) sponsor
varchar(100) financial_support_source
numeric(12_2) financial_support_amount
date health_insurance_end_date
varchar(30) work_auth_type
date work_auth_start_date
date work_auth_end_date
varchar(20) sevis_status
varchar(20) form_type
}
core__photo_sync_runs {
int photo_sync_run_id PK
varchar(500) source
varchar(20) status
varchar(20) trigger_source
bool dry_run
int files_seen
int files_matched
int photos_created
int photos_updated
int photos_removed
int files_unmatched
int files_failed
text error_message
timestamp started_at
timestamp completed_at
int triggered_by FK
int photos_superseded
}
core__people ||--o{ core__people : "parent"
core__people ||--o{ core__person_activities : "person_id"
core__people ||--o{ core__person_addresses : "person_id"
core__people ||--o{ core__person_emails : "person_id"
core__people ||--o{ core__person_essays : "person_id"
core__people ||--o{ core__person_family_members : "person_id"
core__people ||--o{ core__person_citizenships : "person_id"
core__people ||--o{ core__person_honors : "person_id"
core__people ||--o{ core__person_identifiers : "person_id"
core__people ||--o{ core__person_merge_log : "primary_person_id"
core__people ||--o{ core__person_merge_log : "secondary_person_id"
core__people ||--o{ core__person_names : "person_id"
core__people ||--o{ core__person_phones : "person_id"
core__people ||--o{ core__person_photos : "person_id"
core__people ||--o{ core__person_races : "person_id"
core__people ||--o{ core__person_recommendations : "person_id"
core__people ||--o{ core__person_relationships : "person_id_1"
core__people ||--o{ core__person_relationships : "person_id_2"
core__people ||--o{ core__person_test_scores : "person_id"
core__people ||--o{ core__person_visa_status : "person_id"3. Core - Auth & Authorization
mermaid
erDiagram
core__application_access_tokens {
int token_id PK
varchar(64) token UK
uuid person_id FK
int application_id FK
varchar(20) token_type
timestamp expires_at
timestamp used_at
bool is_revoked
}
core__auth_users {
varchar(128) password
int auth_user_id PK
varchar(254) email UK
uuid person_id FK
bool is_student
bool is_faculty
bool is_advisor
bool is_admissions_staff
bool is_registrar
bool is_staff
bool is_active
bool is_superuser
timestamptz date_joined
timestamptz last_login
timestamptz last_activity
}
core__dashboard_tiles {
int tile_id PK
varchar(50) tile_code UK
varchar(100) name
varchar(500) description
varchar(100) component_name
varchar(50) icon
int default_width
int required_permission_node_id FK
bool is_active
}
core__institution_domains {
int domain_id PK
varchar(100) domain_name UK
bool is_primary
bool is_protected
varchar(200) description
bool is_active
}
core__network_cidrs {
int network_cidr_id PK
int network_id FK
cidr cidr
varchar(200) description
}
core__networks {
int network_id PK
varchar(100) name UK
varchar(500) description
bool is_active
}
core__permission_nodes {
int node_id PK
varchar(100) node_code UK
varchar(100) name
varchar(500) description
varchar(20) node_type
int parent_node_id FK
varchar(200) route_path
varchar(50) icon
int sort_order
bool is_menu_item
bool is_active
}
core__role_default_tiles {
int role_tile_id PK
int role_id FK
int tile_id FK
int sort_order
bool is_required
}
core__role_menu_shortcuts {
int role_menu_shortcut_id PK
int role_id FK
int node_id FK
int sort_order
}
core__role_network_rules {
int role_network_rule_id PK
int role_id FK
int network_id FK
varchar(10) effect
}
core__role_permissions {
int role_permission_id PK
int role_id FK
int node_id FK
varchar(10) access_level
bool includes_children
varchar(20) scope
}
core__roles {
int role_id PK
varchar(50) role_code UK
varchar(100) name
varchar(500) description
varchar(20) role_type
bool is_active
int priority
bool grants_superuser
int session_timeout_minutes
}
core__sso_identity_providers {
int sso_identity_provider_id PK
varchar(100) display_name
varchar(50) slug
text metadata_url
text metadata_xml
timestamp metadata_fetched_at
bool is_active
int sort_order
}
core__user_active_role {
int user_id PK
int active_role_id FK
timestamp switched_at
int active_admin_id FK
}
core__user_dashboard_preferences {
int preference_id PK
int user_id FK
int role_id FK
int tile_id FK
bool is_visible
int sort_order
int grid_x
int grid_y
int width
}
core__user_filter_presets {
int user_filter_preset_id PK
int user_id FK
varchar(100) page_key
varchar(100) name
jsonb filters
}
core__user_impersonation_sessions {
int session_id PK
int admin_user_id FK
int impersonated_user_id FK
timestamp started_at
timestamp ended_at
varchar(500) reason
bool is_active
varchar(64) auth_session_hash
}
core__user_roles {
int legacy_user_role_id
int user_id FK
int role_id FK
bool is_manual_override
date effective_date
date expiration_date
int assigned_by_id FK
uuid user_role_id PK
varchar(32) legacy_id
}
core__user_shortcuts {
int user_shortcut_id PK
int user_id FK
varchar(200) label
varchar(500) route_path
varchar(50) icon
int sort_order
}
core__permission_nodes ||--o{ core__dashboard_tiles : "required_permission_node_id"
core__networks ||--o{ core__network_cidrs : "network_id"
core__permission_nodes ||--o{ core__permission_nodes : "parent"
core__roles ||--o{ core__role_default_tiles : "role_id"
core__dashboard_tiles ||--o{ core__role_default_tiles : "tile_id"
core__permission_nodes ||--o{ core__role_menu_shortcuts : "node_id"
core__roles ||--o{ core__role_menu_shortcuts : "role_id"
core__networks ||--o{ core__role_network_rules : "network_id"
core__roles ||--o{ core__role_network_rules : "role_id"
core__permission_nodes ||--o{ core__role_permissions : "node_id"
core__roles ||--o{ core__role_permissions : "role_id"
core__roles ||--o{ core__user_active_role : "active_role_id"
core__auth_users ||--o{ core__user_active_role : "user_id"
core__roles ||--o{ core__user_dashboard_preferences : "role_id"
core__dashboard_tiles ||--o{ core__user_dashboard_preferences : "tile_id"
core__auth_users ||--o{ core__user_dashboard_preferences : "user_id"
core__auth_users ||--o{ core__user_filter_presets : "user_id"
core__auth_users ||--o{ core__user_impersonation_sessions : "admin_user_id"
core__auth_users ||--o{ core__user_impersonation_sessions : "impersonated_user_id"
core__auth_users ||--o{ core__user_roles : "assigned_by_id"
core__roles ||--o{ core__user_roles : "role_id"
core__auth_users ||--o{ core__user_roles : "user_id"
core__auth_users ||--o{ core__user_shortcuts : "user_id"3b. Core - Audit Trail (migration 081)
mermaid
erDiagram
core__audit_log {
int audit_id PK
varchar(100) table_name
varchar(200) record_id
varchar(20) action
jsonb old_values
jsonb new_values
uuid changed_by_person_id FK
timestamp changed_at
varchar(50) ip_address
text user_agent
uuid impersonated_person_id
varchar(40) actor_source
varchar(255) client_hostname
}
core__audit_schema_defaults {
varchar(63) schema_name PK
bool default_enabled
}
core__audit_system_config {
bool audit_system_config_id PK
bool master_enabled
bool default_enable_new_tables
}
core__audit_table_config {
varchar(63) schema_name PK
varchar(63) table_name PK
bool is_enabled
}
core__audit_trail_deletion_log {
bigint deletion_id PK
varchar(20) log_source
bigint original_log_id
varchar(150) table_name
varchar(200) record_id
varchar(20) action
timestamp original_changed_at
uuid deleted_by_person_id
timestamp deleted_at
}
core__data_change_history {
int change_id PK
varchar(100) table_name
varchar(100) record_id
varchar(100) field_name
text old_value
text new_value
uuid changed_by_person_id FK
timestamp changed_at
text change_reason
}
core__ferpa_change_log {
bigint ferpa_change_log_id PK
uuid person_id FK
bool old_value
bool new_value
text reason
uuid changed_by_person_id
uuid impersonated_person_id
timestamp changed_at
}
core__identifier_access_log {
bigint identifier_access_log_id PK
uuid person_identifier_id FK
uuid target_person_id FK
int viewer_user_id FK
uuid viewer_person_id
varchar(10) tier
timestamp accessed_at
varchar(45) ip_address
text user_agent
int identifier_type_id FK
varchar(10) purpose
}
core__permission_audit_log {
int audit_id PK
varchar(50) entity_type
int entity_id
varchar(20) action
int changed_by_user_id FK
timestamp changed_at
varchar(100) field_name
text old_value
text new_value
text additional_context
varchar(45) ip_address
varchar(500) user_agent
}
core__person_name_audit_log {
int audit_id PK
int person_name_id FK
uuid person_id FK
varchar(20) action
int changed_by_user_id FK
timestamp changed_at
varchar(50) name_type_code
varchar(100) first_name
varchar(100) middle_name
varchar(100) last_name
varchar(20) suffix
varchar(100) field_name
text old_value
text new_value
varchar(500) change_reason
varchar(45) ip_address
varchar(500) user_agent
}4. Core - Proxy Access
mermaid
erDiagram
core__proxy_access_audit_log {
int audit_id PK
varchar(30) action
text action_details
varchar(45) ip_address
varchar(500) user_agent
int performed_by_user_id FK
uuid proxy_person_id FK
int student_id FK
int proxy_access_id FK
}
core__proxy_access_permissions {
int proxy_access_permission_id PK
int proxy_access_id FK
int permission_type_id FK
}
core__proxy_invitation_permissions {
int proxy_invitation_permission_id PK
int invitation_id FK
int permission_type_id FK
}
core__proxy_invitations {
int invitation_id PK
varchar(254) invitee_email
varchar(30) invitee_phone
varchar(100) invitee_first_name
varchar(100) invitee_last_name
timestamptz expires_at
varchar(64) email_token UK
timestamptz email_token_expires_at
varchar(20) status
uuid proxy_person_id FK
int relationship_type_id FK
int student_id FK
}
core__student_proxy_access {
int proxy_access_id PK
timestamptz granted_at
timestamptz expires_at
timestamptz revoked_at
bool is_active
uuid proxy_person_id FK
int relationship_type_id FK
int revoked_by_user_id FK
int student_id FK
}
core__student_proxy_access ||--o{ core__proxy_access_audit_log : "proxy_access_id"
core__student_proxy_access ||--o{ core__proxy_access_permissions : "proxy_access_id"
core__proxy_invitations ||--o{ core__proxy_invitation_permissions : "invitation_id"5. Records - Academic Calendar
mermaid
erDiagram
records__academic_term_slots {
int term_slot_id PK
varchar(100) slot_name
varchar(20) slot_code UK
int sort_order
bool is_active
}
records__academic_terms {
int term_id PK
int academic_year_id FK
varchar(20) term_code UK
varchar(100) term_name
int term_type_id FK
date start_date
date end_date
date census_date
date add_deadline
date drop_deadline
date withdrawal_deadline
date grades_due_date
bool is_current
date first_billing_date
bool is_offered_at_all_campuses
date registration_open_date
timestamp midterm_grade_start_date
timestamp midterm_grade_end_date
int term_slot_id FK
int credit_system_id FK
date housing_start_date
date housing_end_date
date meal_start_date
date meal_end_date
}
records__academic_years {
int academic_year_id PK
varchar(20) year_code UK
date start_date
date end_date
bool is_current
}
records__bulletin_academic_years {
int bulletin_year_id PK
int bulletin_id FK
int academic_year_id FK
}
records__bulletins {
int bulletin_id PK
varchar(20) bulletin_code UK
varchar(200) title
text description
int bulletin_type_id FK
int status_id FK
date effective_date
date end_date
bool is_current
}
records__course_offering_terms {
int course_id PK
int term_slot_id PK
}
records__credit_system_conversions {
int conversion_id PK
int from_system_id FK
int to_system_id FK
numeric(8_4) conversion_factor
bool is_active
}
records__sub_terms {
int sub_term_id PK
int term_id FK
varchar(20) sub_term_code UK
varchar(100) name
date start_date
date end_date
}
records__term_campus_offerings {
int offering_id PK
int term_id FK
int campus_id FK
date registration_open_date
date registration_close_date
bool is_active
}
records__academic_years ||--o{ records__academic_terms : "academic_year_id"
records__academic_term_slots ||--o{ records__academic_terms : "term_slot_id"
records__academic_years ||--o{ records__bulletin_academic_years : "academic_year_id"
records__bulletins ||--o{ records__bulletin_academic_years : "bulletin_id"
records__academic_term_slots ||--o{ records__course_offering_terms : "term_slot_id"
records__academic_terms ||--o{ records__sub_terms : "term_id"
records__academic_terms ||--o{ records__term_campus_offerings : "term_id"6. Records - Courses & Sections
mermaid
erDiagram
records__academic_level_grading_systems {
int academic_level_grading_system_id PK
int academic_level_id FK
int grading_system_id FK
bool is_default
}
records__class_schedules {
int class_schedule_id PK
int class_id FK
int schedule_type_id FK
int template_id FK
smallint day_of_week
time_without_time_zone start_time
time_without_time_zone end_time
date effective_date
date end_date
int room_id FK
varchar(500) notes
}
records__class_sections {
int class_section_id PK
int class_id FK
int section_id FK
}
records__classes {
int class_id PK
varchar(50) class_code UK
int term_id FK
int status_id FK
}
records__course_attributes {
int course_attribute_id PK
int course_id FK
int attribute_type_id FK
date effective_date
date end_date
varchar(500) attribute_value
}
records__course_bulletin_periods {
int course_bulletin_period_id PK
int course_bulletin_id FK
int term_period_id FK
}
records__course_bulletins {
int course_bulletin_id PK
int course_id FK
int bulletin_id FK
int teaching_frequency_id FK
varchar(500) notes
bool is_active
}
records__course_equivalents {
int equivalence_id PK
int course_id FK
int equivalent_course_id FK
date effective_date
date end_date
int equivalence_type_id FK
}
records__course_pattern_exclusions {
int exclusion_id PK
int pattern_id FK
int course_id FK
}
records__course_patterns {
int pattern_id PK
varchar(50) pattern_code UK
varchar(200) name
text description
int bulletin_id FK
varchar(50) pattern_type
varchar(20) course_prefix
int level_min
int level_max
int department_id FK
int attribute_id FK
varchar(500) regex_pattern
text exclude_courses
bool is_active
}
records__course_requisite_links {
int course_requisite_link_id PK
int course_id FK
varchar(20) acad_reqmt_id
int block_id FK
char timing
varchar(2) enforcement
int position
varchar(20) source
}
records__course_requisites {
int requisite_id PK
int course_id FK
int requisite_type_id FK
int requisite_course_id FK
varchar(200) requisite_pattern
int min_grade_id FK
bool can_be_concurrent
int requisite_group
}
records__course_sections {
int section_id PK
int course_id FK
int term_id FK
varchar(20) section_code
int sub_term_id FK
int instruction_mode_id FK
int max_enrollment
int current_enrollment
int waitlist_capacity
int current_waitlist
int section_status_id FK
int grading_system_id FK
bool is_cross_listed
int campus_id FK
varchar(100) section_descriptor
date enrollment_deadline_override
int class_id FK
int seats_blocked_in_process
numeric(10_2) section_fee
numeric(4_2) billing_credits
int fee_type_id FK
numeric(10_2) section_lab_fee
int delivery_mode_id FK
int seats_planned
numeric(4_2) credit_hours_min
numeric(4_2) credit_hours_max
varchar(1) billing_method
int seats_reserved
int instructional_method_id FK
bool is_variable_credit
numeric(4_2) credit_increment
int colleague_section_id
}
records__course_substitutions {
int substitution_id PK
int original_course_id FK
int substitute_course_id FK
date effective_date
date end_date
uuid approved_by_person_id FK
date approval_date
text notes
varchar(20) scope
int bulletin_id FK
int major_id FK
int requirement_block_id FK
bool is_active
}
records__courses {
int course_id PK
varchar(20) course_code
varchar(200) title
text description
numeric(4_2) credit_hours_min
numeric(4_2) credit_hours_max
numeric(4_2) contact_hours
bool is_variable_credit
bool is_repeatable
int max_repeats
int grading_system_id FK
int status_id FK
date effective_date
date end_date
bool allows_section_descriptor
int course_level_id FK
numeric(10_2) lab_fee
varchar(20) year_pattern
varchar(20) source_id UK
int credit_system_id FK
int academic_level_id FK
varchar(20) prereq_acad_reqmt_id
int prereq_block_id FK
}
records__grading_system_grades {
int grade_id PK
int grading_system_id FK
varchar(5) letter_grade
varchar(50) display_name
numeric(4_2) numeric_equivalent
bool is_passing
bool is_included_in_gpa
int sort_order
bool is_temporary
bool is_active
}
records__grading_systems {
int grading_system_id PK
varchar(20) system_code UK
varchar(100) name
text description
bool is_active
}
records__schedule_template_entries {
int template_entry_id PK
int template_id FK
smallint day_of_week
time_without_time_zone start_time
time_without_time_zone end_time
}
records__schedule_templates {
int template_id PK
varchar(50) template_code UK
varchar(100) template_name
varchar(500) description
int total_minutes_per_week
bool is_active
int sort_order
}
records__section_instructors {
int section_instructor_id PK
int section_id FK
int teacher_id FK
int instructor_role_id FK
numeric(5_2) responsibility_percentage
bool can_enter_grades
varchar(8) source
}
records__section_schedules {
int schedule_id PK
int section_id FK
smallint day_of_week
time_without_time_zone start_time
time_without_time_zone end_time
int room_id FK
int schedule_type_id FK
date effective_date
date end_date
}
records__grading_systems ||--o{ records__academic_level_grading_systems : "grading_system_id"
records__classes ||--o{ records__class_schedules : "class_id"
records__schedule_templates ||--o{ records__class_schedules : "template_id"
records__classes ||--o{ records__class_sections : "class_id"
records__course_sections ||--o{ records__class_sections : "section_id"
records__courses ||--o{ records__course_attributes : "course_id"
records__course_bulletins ||--o{ records__course_bulletin_periods : "course_bulletin_id"
records__courses ||--o{ records__course_bulletins : "course_id"
records__courses ||--o{ records__course_equivalents : "course_id"
records__courses ||--o{ records__course_equivalents : "equivalent_course_id"
records__courses ||--o{ records__course_pattern_exclusions : "course_id"
records__course_patterns ||--o{ records__course_pattern_exclusions : "pattern_id"
records__courses ||--o{ records__course_requisite_links : "course_id"
records__courses ||--o{ records__course_requisites : "course_id"
records__courses ||--o{ records__course_requisites : "requisite_course_id"
records__classes ||--o{ records__course_sections : "class_id"
records__courses ||--o{ records__course_sections : "course_id"
records__grading_systems ||--o{ records__course_sections : "grading_system_id"
records__courses ||--o{ records__course_substitutions : "original_course_id"
records__courses ||--o{ records__course_substitutions : "substitute_course_id"
records__grading_systems ||--o{ records__courses : "grading_system_id"
records__grading_systems ||--o{ records__grading_system_grades : "grading_system_id"
records__schedule_templates ||--o{ records__schedule_template_entries : "template_id"
records__course_sections ||--o{ records__section_instructors : "section_id"
records__course_sections ||--o{ records__section_schedules : "section_id"7. Records - Programs & Requirements
mermaid
erDiagram
records__academic_policy_rules {
int policy_rule_id PK
varchar(60) rule_code
varchar(10) scope_kind
varchar(10) value_type
int bulletin_id FK
int degree_level_id FK
numeric(8_3) threshold_value
numeric(4_2) min_grade_points
int min_grade_id FK
varchar(200) name
text description
bool allow_program_override
varchar(10) severity
bool is_active
int sort_order
int test_type_id FK
}
records__class_standing_thresholds {
int threshold_id PK
int standing_id FK
numeric(5_2) min_credits
numeric(5_2) max_credits
int sort_order
bool is_active
}
records__degree_approved_minors {
int degree_minor_id PK
int degree_id FK
int minor_block_id FK
}
records__degree_blocks {
int degree_block_id PK
int degree_id FK
int block_id FK
bool is_required
int sort_order
}
records__degree_default_advisors {
int degree_advisor_id PK
int degree_id FK
int advisor_id FK
int advisor_role_id FK
bool is_primary
int sort_order
date effective_date
date end_date
}
records__degree_level_grading_systems {
int mapping_id PK
int degree_level_id FK
int grading_system_id FK
bool is_default
}
records__degrees {
int degree_id PK
varchar(20) degree_code UK
varchar(200) name
text description
int bulletin_id FK
int degree_level_id FK
int department_id FK
int college_id FK
numeric(5_2) total_credits_required
numeric(5_2) total_credits_max
numeric(4_3) min_gpa_required
numeric(5_2) residency_credits_required
varchar(50) minor_requirement_type
numeric(5_2) max_double_count_pct
int status_id FK
date effective_date
date end_date
int credit_system_id FK
}
records__emphasis_areas {
int emphasis_id PK
int major_id FK
varchar(20) emphasis_code
varchar(200) name
text description
numeric(5_2) total_credits_required
int status_id FK
date effective_date
date end_date
int sort_order
bool is_active
int credit_system_id FK
}
records__emphasis_blocks {
int emphasis_block_id PK
int emphasis_id FK
int block_id FK
bool is_required
int sort_order
}
records__major_blocks {
int major_block_id PK
int major_id FK
int block_id FK
bool is_required
int sort_order
int bulletin_id FK
}
records__major_campus_offerings {
int major_campus_id PK
int major_id FK
int campus_id FK
date effective_date
date end_date
}
records__major_default_advisors {
int major_advisor_id PK
int major_id FK
int advisor_id FK
int advisor_role_id FK
bool is_primary
int sort_order
date effective_date
date end_date
}
records__majors {
int major_id PK
varchar(20) major_code
varchar(200) name
text description
int degree_id FK
int department_id FK
int bulletin_id FK
numeric(5_2) total_credits_required
numeric(5_2) min_upper_division_credits
numeric(3_2) min_gpa_required
int status_id FK
date effective_date
date end_date
bool is_offered_at_all_campuses
varchar(7) cip_code
bool requires_emphasis
int credit_system_id FK
int max_course_retakes
bool withdrawal_counts_as_attempt
}
records__minor_blocks {
int minor_block_id PK
int minor_id FK
int block_id FK
bool is_required
int sort_order
int bulletin_id FK
}
records__minor_campus_offerings {
int minor_campus_id PK
int minor_id FK
int campus_id FK
date effective_date
date end_date
}
records__minor_default_advisors {
int minor_advisor_id PK
int minor_id FK
int advisor_id FK
int advisor_role_id FK
int sort_order
date effective_date
date end_date
}
records__minors {
int minor_id PK
varchar(20) minor_code
varchar(200) name
text description
int department_id FK
int bulletin_id FK
numeric(5_2) total_credits_required
numeric(3_2) min_gpa_required
int status_id FK
date effective_date
date end_date
bool is_offered_at_all_campuses
varchar(7) cip_code
int credit_system_id FK
}
records__requirement_blocks {
int block_id PK
int parent_block_id FK
int block_type_id FK
varchar(200) name
text description
numeric(5_2) min_credits
numeric(5_2) max_credits
int min_courses
int max_courses
int sort_order
varchar(50) block_code
int bulletin_id FK
int department_id FK
int college_id FK
numeric(4_3) min_gpa
int min_grade_id FK
bool allow_double_count_within_degree
numeric(5_2) max_double_count_pct
varchar(10) logic_operator
bool is_active
numeric(5_2) min_upper_division_credits
numeric(5_2) min_institution_credits
numeric(6_2) sequence_due_at_credits
bool sequence_exempt
int min_child_groups
varchar(24) credits_source
text source_spec
varchar[] allowed_grades
}
records__requirement_group_rules {
int rule_id PK
int block_id FK
varchar(200) rule_name
varchar(500) rule_description
text logic_expression
int sort_order
}
records__requirement_groups {
int group_id PK
int block_id FK
varchar(50) group_code
varchar(200) name
text description
int min_courses
int max_courses
numeric(5_2) min_credits
numeric(5_2) max_credits
numeric(4_3) min_gpa
int min_grade_id FK
varchar(10) logic_operator
int sort_order
numeric(5_2) min_institution_credits
varchar(16) group_role
varchar(32) group_role_source
numeric(5_2) derived_credits
text source_spec
varchar[] allowed_grades
}
records__requirement_item_activities {
int item_activity_id PK
int item_id FK
varchar(200) activity_name
text activity_description
}
records__requirement_item_courses {
int item_course_id PK
int item_id FK
int course_id FK
bool is_preferred
int sort_order
}
records__requirement_items {
int item_id PK
int block_id FK
int requirement_type_id FK
int course_id FK
varchar(200) course_pattern
int activity_type_id FK
numeric(5_2) min_credits
int min_count
int min_grade_id FK
bool is_required
int sort_order
int group_id FK
int course_pattern_id FK
int non_course_type_id FK
text non_course_description
int completion_role_id FK
numeric(5_2) max_credits
int max_count
numeric(4_3) min_gpa
text notes
varchar(100) source_code
}
records__sequence_policies {
int sequence_policy_id PK
int bulletin_id FK
varchar(200) name
int level_min
int level_max
numeric(6_2) due_at_credits
int due_at_standing_id FK
numeric(6_2) warn_within_credits
varchar(10) severity
bool is_active
int sort_order
}
records__degrees ||--o{ records__degree_approved_minors : "degree_id"
records__requirement_blocks ||--o{ records__degree_approved_minors : "minor_block_id"
records__requirement_blocks ||--o{ records__degree_blocks : "block_id"
records__degrees ||--o{ records__degree_blocks : "degree_id"
records__degrees ||--o{ records__degree_default_advisors : "degree_id"
records__majors ||--o{ records__emphasis_areas : "major_id"
records__requirement_blocks ||--o{ records__emphasis_blocks : "block_id"
records__emphasis_areas ||--o{ records__emphasis_blocks : "emphasis_id"
records__requirement_blocks ||--o{ records__major_blocks : "block_id"
records__majors ||--o{ records__major_blocks : "major_id"
records__majors ||--o{ records__major_campus_offerings : "major_id"
records__majors ||--o{ records__major_default_advisors : "major_id"
records__degrees ||--o{ records__majors : "degree_id"
records__requirement_blocks ||--o{ records__minor_blocks : "block_id"
records__minors ||--o{ records__minor_blocks : "minor_id"
records__minors ||--o{ records__minor_campus_offerings : "minor_id"
records__minors ||--o{ records__minor_default_advisors : "minor_id"
records__requirement_blocks ||--o{ records__requirement_blocks : "parent"
records__requirement_blocks ||--o{ records__requirement_group_rules : "block_id"
records__requirement_blocks ||--o{ records__requirement_groups : "block_id"
records__requirement_items ||--o{ records__requirement_item_activities : "item_id"
records__requirement_items ||--o{ records__requirement_item_courses : "item_id"
records__requirement_blocks ||--o{ records__requirement_items : "block_id"
records__requirement_groups ||--o{ records__requirement_items : "group_id"8. Records - Roles & Student Data
mermaid
erDiagram
core__legacy_academic_credits {
int legacy_credit_id PK
uuid person_id FK
varchar(20) colleague_id
varchar(10) credit_type
varchar(20) term_code
varchar(20) subject
varchar(20) course_number
varchar(200) course_title
numeric(8_2) credits
numeric(8_2) credits_attempted
numeric(8_2) credits_completed
varchar(20) final_grade
varchar(20) verified_grade
varchar(10) current_status
varchar(10) academic_level
timestamp migrated_at
}
records__advisors {
int advisor_id PK
uuid person_id FK
int advisor_type_id FK
int department_id FK
int max_advisee_count
bool is_active
}
records__applicants {
int applicant_id PK
uuid person_id FK
int application_term_id FK
int application_status_id FK
int intended_major_id FK
}
records__cohort_advisors {
int cohort_advisor_id PK
int cohort_id FK
int advisor_id FK
date start_date
date end_date
bool is_active
}
records__cohort_majors {
int cohort_major_id PK
int cohort_id FK
int major_id FK
}
records__cohorts {
int cohort_id PK
varchar(50) cohort_code UK
varchar(200) name
int start_term_id FK
int expected_graduation_term_id FK
bool is_active
int major_id FK
text description
int academic_year_id FK
int max_students
int cohort_type_id FK
date start_date
date end_date
varchar(20) source
timestamp ods_synced_at
int campus_id FK
bool auto_sync_membership
}
records__hold_type_roles {
int hold_type_role_id PK
int hold_type_id FK
varchar(50) role_code
bool can_place
bool can_clear
}
records__hold_types {
int hold_type_id PK
varchar(50) hold_type_code UK
varchar(200) name
varchar(1000) description
varchar(20) category
text auto_create_query
text auto_clear_query
varchar(255) auto_clear_script
text resolution_instructions
varchar(200) contact_office
varchar(255) contact_email
varchar(50) contact_phone
bool is_active
int sort_order
bool prevents_transcript
bool prevents_graduation
varchar(20) enforcement_level
smallint default_severity
bool student_visible
}
records__section_waitlists {
int waitlist_id PK
int student_id FK
int section_id FK
int position
timestamp added_at
timestamp notified_at
timestamp notification_expires_at
int status_id FK
timestamp removed_at
varchar(500) removal_reason
}
records__student_academic_levels {
int student_academic_level_id PK
int student_id FK
int academic_level_id FK
numeric(4_3) cumulative_gpa
numeric(6_2) credits_attempted
numeric(6_2) credits_earned
timestamp gpa_last_calculated
varchar(32) colleague_sal_id
varchar(10) colleague_class_code
int colleague_class_standing_id FK
int computed_class_standing_id FK
varchar(10) catalog_year
varchar(10) admit_status_code
varchar(10) enroll_status_code
date level_start_date
date level_end_date
varchar(20) start_term_code
bool is_currently_registered
timestamp ods_synced_at
}
records__student_academic_status_history {
int history_id PK
int student_id FK
int status_id FK
date effective_date
text comments
int major_id FK
}
records__student_activities {
int activity_id PK
int student_id FK
int activity_type_id FK
varchar(200) activity_name
text description
date start_date
date end_date
numeric(6_2) hours_completed
}
records__student_admission_status_history {
int history_id PK
int student_id FK
int status_id FK
date effective_date
text comments
}
records__student_advisors {
int student_advisor_id PK
int student_id FK
int advisor_id FK
bool is_primary
date effective_date
date end_date
int advisor_role_id FK
date start_date
bool is_active
}
records__student_awards {
int award_id PK
int student_id FK
int award_type_id FK
varchar(200) award_name
text description
int term_id FK
date award_date
}
records__student_campus_assignments {
int assignment_id PK
int student_id FK
int campus_id FK
bool is_primary
date start_date
date end_date
}
records__student_cohorts {
int student_cohort_id PK
int student_id FK
int cohort_id FK
date start_date
date end_date
int academic_level_id FK
int ods_pos
varchar(20) source
timestamp ods_synced_at
bool locally_modified
}
records__student_emphases {
int student_emphasis_id PK
int student_major_id FK
int emphasis_id FK
int status_id FK
date start_date
date end_date
}
records__student_holds {
int legacy_student_hold_id
int student_id FK
int hold_type_id FK
int term_id FK
text reason
date expiration_date
uuid created_by_person_id FK
varchar(100) created_by_system
uuid cleared_by_person_id FK
varchar(100) cleared_by_system
timestamp cleared_at
text clear_reason
bool is_active
uuid student_hold_id PK
varchar(32) legacy_id
smallint severity
text custom_message
uuid impersonator_person_id FK
}
records__student_leaves {
int student_leave_id PK
int student_id FK
int workflow_instance_id FK
date leave_start
date expected_return_date
varchar(200) intended_return_term
timestamp started_at
timestamp returned_at
}
records__student_list_members {
int member_id PK
int list_id FK
int student_id FK
int added_by_id FK
timestamp added_at
text notes
}
records__student_list_shares {
int share_id PK
int list_id FK
int shared_with_id FK
int shared_by_id FK
varchar(20) permission
}
records__student_lists {
int list_id PK
varchar(200) list_name
text description
varchar(20) list_type
jsonb filter_criteria
int owner_id FK
bool is_system
bool is_public
bool is_active
}
records__student_majors {
int student_major_id PK
int student_id FK
int major_id FK
bool is_primary
int status_id FK
date start_date
date end_date
int bulletin_id FK
}
records__student_minors {
int student_minor_id PK
int student_id FK
int minor_id FK
int status_id FK
date start_date
date end_date
int bulletin_id FK
}
records__student_types {
int student_type_assignment_id PK
int student_id FK
int student_type_id FK
date start_date
date end_date
int ods_pos
varchar(20) source
timestamp ods_synced_at
}
records__students {
int student_id PK
uuid person_id FK
varchar(20) student_number UK
date enrollment_date
int initial_term_id FK
int student_status_id FK
date expected_graduation_date
int primary_campus_id FK
bool restrict_to_campus
int housing_status_id FK
int class_standing_id FK
numeric(4_3) cumulative_gpa
numeric(6_2) credits_earned
numeric(6_2) credits_attempted
timestamp gpa_last_calculated
timestamp standing_last_calculated
int ipeds_residency_id FK
int ipeds_student_type_id FK
bool pell_recipient
numeric(10_2) efc_amount
bool first_gen_flag
}
records__teacher_credentials {
int credential_id PK
int teacher_id FK
int credential_type_id FK
varchar(200) credential_name
varchar(200) issuing_institution
date date_earned
date expiry_date
}
records__teachers {
int teacher_id PK
uuid person_id FK
varchar(20) employee_id
date hire_date
int status_id FK
int department_id FK
varchar(100) display_first_name
varchar(100) display_last_name
}
records__advisors ||--o{ records__cohort_advisors : "advisor_id"
records__cohorts ||--o{ records__cohort_advisors : "cohort_id"
records__cohorts ||--o{ records__cohort_majors : "cohort_id"
records__hold_types ||--o{ records__hold_type_roles : "hold_type_id"
records__students ||--o{ records__section_waitlists : "student_id"
records__students ||--o{ records__student_academic_levels : "student_id"
records__students ||--o{ records__student_academic_status_history : "student_id"
records__students ||--o{ records__student_activities : "student_id"
records__students ||--o{ records__student_admission_status_history : "student_id"
records__advisors ||--o{ records__student_advisors : "advisor_id"
records__students ||--o{ records__student_advisors : "student_id"
records__students ||--o{ records__student_awards : "student_id"
records__students ||--o{ records__student_campus_assignments : "student_id"
records__cohorts ||--o{ records__student_cohorts : "cohort_id"
records__students ||--o{ records__student_cohorts : "student_id"
records__student_majors ||--o{ records__student_emphases : "student_major_id"
records__hold_types ||--o{ records__student_holds : "hold_type_id"
records__students ||--o{ records__student_holds : "student_id"
records__students ||--o{ records__student_leaves : "student_id"
records__student_lists ||--o{ records__student_list_members : "list_id"
records__students ||--o{ records__student_list_members : "student_id"
records__student_lists ||--o{ records__student_list_shares : "list_id"
records__students ||--o{ records__student_majors : "student_id"
records__students ||--o{ records__student_minors : "student_id"
records__students ||--o{ records__student_types : "student_id"
records__teachers ||--o{ records__teacher_credentials : "teacher_id"9. Records - Registration & Enrollment
mermaid
erDiagram
records__enrollment_status_history {
int history_id PK
int enrollment_id
varchar(20) enrollment_type
int old_status_id FK
int new_status_id FK
uuid changed_by_person_id FK
timestamp changed_at
text reason
uuid enrollment_uuid FK
}
records__grade_change_log {
bigint change_id PK
uuid enrollment_id
int legacy_enrollment_id
int student_id
int section_id
varchar(10) grade_kind
int old_grade_id
varchar(5) old_grade_code
int new_grade_id
varchar(5) new_grade_code
uuid actor_person_id
varchar(40) actor_source
text reason
timestamp changed_at
}
records__grade_releases {
bigint grade_release_id PK
int section_id FK
varchar(10) grade_kind
timestamp released_through
text notes
}
records__grade_visibility_completions {
bigint grade_visibility_completion_id PK
int grade_visibility_rule_id FK
int student_id FK
int section_id FK
timestamptz completed_at
varchar(10) source
varchar(100) import_batch
}
records__grade_visibility_releases {
bigint grade_visibility_release_id PK
uuid student_section_enrollment_id FK
int grade_visibility_rule_id FK
text reason
}
records__grade_visibility_rules {
int grade_visibility_rule_id PK
varchar(40) code UK
varchar(200) name
int term_id FK
int section_id FK
int course_id FK
int academic_level_id FK
varchar(10) grade_kind
varchar(30) condition_type
varchar(10) completion_granularity
text student_message
varchar(500) action_url
timestamptz auto_release_at
bool is_active
}
records__hold_evaluation_log {
int log_id PK
int hold_type_id FK
varchar(20) evaluation_type
timestamp started_at
timestamp completed_at
int students_evaluated
int holds_created
int holds_cleared
text errors
varchar(50) triggered_by
}
records__planned_courses {
int plan_id PK
int student_id FK
int section_id FK
int requirement_item_id FK
int requirement_block_id FK
int status_id FK
uuid planned_by_person_id FK
int planned_by_role_id FK
timestamp planned_at
bool seat_blocked
timestamp seat_blocked_at
bool prereq_satisfied
text prereq_notes
bool coreq_acknowledged
text coreq_notes
bool schedule_conflict_overridden
varchar(1000) override_reason
int registration_transaction_id FK
timestamp registered_at
timestamp expires_at
timestamp expired_at
varchar(500) expiration_reason
varchar(1000) notes
bool sequence_overridden
varchar(1000) sequence_override_reason
uuid sequence_override_by FK
timestamp sequence_override_at
uuid impersonator_person_id FK
timestamp cancelled_at
varchar(1000) cancel_reason
bool capacity_overridden
varchar(1000) capacity_override_reason
uuid capacity_override_by FK
timestamp capacity_override_at
bool prereq_overridden
varchar(1000) prereq_override_reason
uuid prereq_override_by FK
timestamp prereq_override_at
numeric(4_2) credits_selected
bool repeat_overridden
varchar(1000) repeat_override_reason
uuid repeat_override_by FK
timestamp repeat_override_at
}
records__probation_alert_log {
int probation_alert_log_id PK
int student_id FK
int major_id FK
int probation_count
timestamp alert_sent_at
text recipients
}
records__registration_transaction_notes {
bigint registration_transaction_note_id PK
int transaction_id FK
varchar(20) note_kind
text note_text
int step_id
uuid author_person_id FK
varchar(50) author_role_code
uuid impersonator_person_id
}
records__registration_transactions {
int transaction_id PK
int student_id FK
int term_id FK
int campus_id FK
int transaction_type_id FK
int current_step_id FK
int status_id FK
uuid initiated_by_person_id FK
int initiator_role_id FK
bool has_overrides
varchar(1000) override_reason
timestamp submitted_at
timestamp completed_at
timestamp cancelled_at
bool override_prereqs
bool override_deadline
bool override_credit_limit
bool override_campus
bool override_sequence
bool override_repeat
bool is_overload_request
text overload_reason
numeric(6_2) overload_credits
uuid overload_advisor_approved_by FK
timestamp overload_advisor_approved_at
}
records__registration_workflow_steps {
int step_id PK
int workflow_id FK
varchar(50) step_code
varchar(200) name
varchar(1000) description
int step_type_id FK
bool can_initiate
bool can_complete
bool can_cancel
bool blocks_seats
bool requires_validation
varchar(500) allowed_roles
int sort_order
}
records__registration_workflows {
int workflow_id PK
varchar(50) workflow_code
varchar(200) name
varchar(1000) description
bool is_active
bool is_default
date effective_date
date end_date
}
records__student_section_enrollments {
int legacy_enrollment_id
int student_id FK
int section_id FK
int enrollment_status_id FK
date registration_date
date drop_date
int grade_id FK
date grade_date
numeric(5_2) credits_attempted
numeric(5_2) credits_earned
bool is_audit
bool is_pass_fail
text notes
bool is_independent_study
bool is_credit_by_exam
bool is_pla
int credit_source_id FK
int transaction_item_id FK
int midterm_grade_id FK
date midterm_grade_date
bool is_attending
date last_attendance_date
text attendance_notes
uuid student_section_enrollment_id PK
bool is_remedial
int colleague_stc_id
varchar(32) legacy_id
int academic_level_id FK
varchar(4) colleague_stc_status
varchar(8) midterm_grade_source
date incomplete_deadline
int incomplete_default_grade_id FK
int incomplete_workflow_instance_id FK
}
records__student_term_enrollments {
int enrollment_id PK
int student_id FK
int term_id FK
int enrollment_status_id FK
date enrollment_date
date withdrawal_date
numeric(5_2) credits_attempted
numeric(5_2) credits_earned
numeric(4_3) term_gpa
numeric(4_3) cumulative_gpa
int enrollment_intensity_id FK
int academic_level_id FK
varchar(4) colleague_sttr_status
}
records__term_registration_windows {
int term_registration_window_id PK
int term_id FK
varchar(100) name
date start_date
jsonb rule_json
int sort_order
}
records__transaction_enrollment_items {
int item_id PK
int transaction_id FK
int section_id FK
int action_type_id FK
int existing_enrollment_id
numeric credits_selected
bool is_audit
bool is_pass_fail
bool is_independent_study
bool is_credit_by_exam
bool is_pla
int item_status_id FK
int validation_status_id FK
text validation_messages
bool seat_blocked
timestamp seat_blocked_at
timestamp processed_at
}
records__transaction_payment_history {
uuid transaction_payment_history_id PK
int payment_id
numeric(10_2) payment_amount
timestamp paid_at
varchar(100) payment_reference
int payment_method_id FK
text notes
int recorded_by FK
timestamp recorded_at
jsonb metadata
uuid payment_uuid FK
varchar(32) legacy_id
}
records__transaction_step_history {
int history_id PK
int transaction_id FK
int from_step_id FK
int to_step_id FK
uuid transitioned_by_person_id FK
varchar(20) transition_type
varchar(1000) notes
timestamp transitioned_at
}
records__student_section_enrollments ||--o{ records__enrollment_status_history : "enrollment_uuid"
records__grade_visibility_rules ||--o{ records__grade_visibility_completions : "grade_visibility_rule_id"
records__grade_visibility_rules ||--o{ records__grade_visibility_releases : "grade_visibility_rule_id"
records__student_section_enrollments ||--o{ records__grade_visibility_releases : "student_section_enrollment_id"
records__registration_transactions ||--o{ records__planned_courses : "registration_transaction_id"
records__registration_transactions ||--o{ records__registration_transaction_notes : "transaction_id"
records__registration_workflow_steps ||--o{ records__registration_transactions : "current_step_id"
records__registration_workflows ||--o{ records__registration_workflow_steps : "workflow_id"
records__transaction_enrollment_items ||--o{ records__student_section_enrollments : "transaction_item_id"
records__registration_transactions ||--o{ records__transaction_enrollment_items : "transaction_id"
records__registration_workflow_steps ||--o{ records__transaction_step_history : "from_step_id"
records__registration_workflow_steps ||--o{ records__transaction_step_history : "to_step_id"
records__registration_transactions ||--o{ records__transaction_step_history : "transaction_id"10. Records - Degree Audit & Graduation
mermaid
erDiagram
records__graduation_application_status_history {
int history_id PK
int application_id FK
varchar(20) old_status
varchar(20) new_status
uuid changed_by
text change_reason
timestamp changed_at
}
records__graduation_applications {
int application_id PK
int student_id FK
int degree_id FK
int expected_graduation_term_id FK
date applied_date
uuid reviewed_by FK
timestamp reviewed_date
varchar(20) status
text notes
int graduation_date_id FK
text denial_reason
bool plan_complete_at_application
jsonb audit_snapshot_json
}
records__graduation_dates {
int graduation_date_id PK
varchar(200) date_name
date graduation_date
int term_id FK
date application_deadline
bool is_active
text notes
}
records__graduation_record_honors {
int graduation_record_honor_id PK
int graduation_record_id FK
int graduation_honor_id FK
varchar(300) notes
}
records__graduation_records {
int graduation_record_id PK
int student_id FK
int degree_id FK
int graduation_date_id FK
date graduation_date
int graduation_type_id FK
varchar(300) title
text title_html
int honors_id FK
int application_id FK
uuid confirmed_by
timestamp confirmed_date
int major_id FK
bool with_honors
}
records__institutions {
int institution_id PK
varchar(200) institution_name
int institution_type_id FK
varchar(20) ceeb_code
varchar(32) colleague_inst_id
varchar(20) source
int attendance_count
varchar(200) address_line1
varchar(200) address_line2
varchar(100) city
int state_province_id FK
varchar(20) postal_code
int country_id FK
varchar(30) phone
varchar(200) email
int default_credit_system_id FK
text notes
bool is_active
}
records__requirement_waivers {
int waiver_id PK
int student_id FK
int block_id FK
int item_id FK
text reason
date approved_date
date expiry_date
int substitute_course_id FK
varchar(20) waiver_type
bool is_active
int approved_by_id FK
}
records__student_course_substitutions {
int student_sub_id PK
int student_id FK
int block_id FK
int original_course_id FK
int substitute_course_id FK
uuid final_approved_by_person_id FK
date final_approval_date
text notes
varchar(30) status
text reason
int requested_by_user_id FK
timestamp requested_at
int advisor_user_id FK
varchar(20) advisor_action
text advisor_notes
timestamp advisor_action_at
int registrar_user_id FK
varchar(20) registrar_action
text registrar_notes
timestamp registrar_action_at
bool is_active
}
records__student_degree_progress {
int progress_id PK
int student_id FK
int degree_id FK
numeric(5_2) total_credits_required
numeric(5_2) total_credits_completed
numeric(4_3) overall_gpa
timestamp last_audit_date
numeric(5_2) completion_percentage
int status_id FK
}
records__student_noncredit_completions {
int student_noncredit_completion_id PK
int student_id FK
int course_id FK
varchar(2) credit_type
varchar(20) term_code
int colleague_stc_id
bool is_active
}
records__student_requirement_fulfillment {
int fulfillment_id PK
int progress_id FK
int block_id FK
bool is_satisfied
numeric(5_2) credits_applied
int courses_applied
}
records__transfer_course_equivalencies {
int transfer_course_equivalency_id PK
int institution_id FK
varchar(20) source_course_code
varchar(200) source_course_title
numeric(8_4) source_credits
int source_credit_system_id FK
int equivalent_course_id FK
numeric(5_2) credits_awarded
varchar(5) min_grade
int sort_order
bool is_active
varchar(20) source
varchar(32) colleague_equiv_id
text notes
}
records__transfer_credits {
int legacy_transfer_credit_id
int student_id FK
varchar(200) source_institution_name
varchar(20) source_institution_code
varchar(20) source_course_code
varchar(200) source_course_title
numeric(5_2) credits_transferred
varchar(5) grade_received
int equivalent_course_id FK
int transfer_term_id FK
uuid evaluated_by_person_id FK
date evaluated_date
int status_id FK
int credit_type_id FK
bool is_four_year_institution
uuid transfer_credit_id PK
text rationale
int source_credit_system_id FK
numeric(8_4) source_credits
int colleague_stc_id
varchar(32) legacy_id
bool is_active
timestamp voided_at
uuid voided_by FK
int source_institution_id FK
}
records__graduation_applications ||--o{ records__graduation_application_status_history : "application_id"
records__graduation_dates ||--o{ records__graduation_applications : "graduation_date_id"
records__graduation_records ||--o{ records__graduation_record_honors : "graduation_record_id"
records__graduation_applications ||--o{ records__graduation_records : "application_id"
records__graduation_dates ||--o{ records__graduation_records : "graduation_date_id"
records__student_degree_progress ||--o{ records__student_requirement_fulfillment : "progress_id"
records__institutions ||--o{ records__transfer_course_equivalencies : "institution_id"
records__institutions ||--o{ records__transfer_credits : "source_institution_id"11. Records - Course Materials
mermaid
erDiagram
records__course_material_assignments {
int course_material_id PK
int course_id FK
int material_id FK
int requirement_id FK
varchar(50) alternative_group
varchar(20) usage_scope
bool retained_across_terms
bool inclusive_access_enrolled
bool inclusive_access_opt_out
date inclusive_access_deadline
numeric(10_2) inclusive_access_charge
text inclusive_access_instructions
text faculty_notes
text bookstore_notes
text admin_notes
int adoption_status_id FK
timestamp adoption_submitted_at
varchar(100) adoption_submitted_by
int sort_order
}
records__material_formats {
int format_id PK
int material_id FK
int format_type_id FK
bool is_preferred
numeric(10_2) price_new
numeric(10_2) price_used
numeric(10_2) price_rental
numeric(10_2) price_digital
numeric(10_2) price_inclusive_access
varchar(2000) bookstore_url
varchar(2000) publisher_url
varchar(2000) oer_url
int sort_order
}
records__material_platforms {
int platform_id PK
int material_id FK
varchar(300) platform_name
varchar(2000) platform_url
bool access_code_required
bool included_with_purchase
bool available_standalone
numeric(10_2) standalone_price
varchar(100) access_duration
bool is_one_time_use
int sort_order
}
records__materials {
int material_id PK
int material_type_id FK
varchar(500) title
varchar(500) subtitle
varchar(100) edition
varchar(50) volume
varchar(1000) authors
varchar(1000) editors
varchar(17) isbn_13
varchar(13) isbn_10
varchar(300) publisher
int publication_year
varchar(50) language
bool used_copies_acceptable
bool older_editions_acceptable
text older_edition_note
bool international_editions_acceptable
bool is_oer
bool is_zero_cost
int oer_license_id FK
varchar(2000) oer_source_url
bool oer_offline_download
text description
text where_to_obtain
numeric(10_2) estimated_cost
varchar(200) model_version
bool accessible_format_available
text accessibility_notes
varchar(500) accessibility_contact
bool is_active
}
records__section_material_assignments {
int section_material_id PK
int section_id FK
int material_id FK
int source_course_material_id FK
int requirement_id FK
varchar(50) alternative_group
varchar(20) usage_scope
bool retained_across_terms
bool inclusive_access_enrolled
bool inclusive_access_opt_out
date inclusive_access_deadline
numeric(10_2) inclusive_access_charge
text inclusive_access_instructions
text faculty_notes
text bookstore_notes
text admin_notes
int adoption_status_id FK
timestamp adoption_submitted_at
varchar(100) adoption_submitted_by
int sort_order
}
records__materials ||--o{ records__course_material_assignments : "material_id"
records__materials ||--o{ records__material_formats : "material_id"
records__materials ||--o{ records__material_platforms : "material_id"
records__materials ||--o{ records__section_material_assignments : "material_id"
records__course_material_assignments ||--o{ records__section_material_assignments : "source_course_material_id"12. Records - Workflows (Generic)
mermaid
erDiagram
records__workflow_automation_rules {
int rule_id PK
varchar(100) rule_code UK
varchar(200) name
varchar(1000) description
int rule_type_id FK
text rule_expression
bool is_active
}
records__workflow_definitions {
int workflow_definition_id PK
varchar(50) workflow_code UK
varchar(200) name
text description
varchar(50) entity_type
bool is_active
int form_template_id FK
}
records__workflow_initiator_roles {
int workflow_initiator_role_id PK
int workflow_definition_id FK
varchar(50) role_code
bool can_start_for_others
}
records__workflow_instance_parallel_tracking {
int workflow_instance_parallel_tracking_id PK
int workflow_instance_id FK
int parallel_step_id FK
bool is_completed
timestamp completed_at
}
records__workflow_instance_step_assignees {
int workflow_instance_step_assignee_id PK
int workflow_instance_step_id FK
uuid person_id FK
varchar(50) resolved_via
int org_unit_id FK
}
records__workflow_instance_steps {
int workflow_instance_step_id PK
int workflow_instance_id FK
int workflow_step_id FK
timestamp entered_at
timestamp exited_at
uuid entered_by_person_id FK
text notes
varchar(20) outcome
uuid completed_by_person_id FK
uuid assigned_to_person_id FK
uuid impersonator_person_id FK
int form_response_id FK
}
records__workflow_instances {
int workflow_instance_id PK
int workflow_definition_id FK
int entity_id
int current_workflow_step_id FK
varchar(20) status
timestamp started_at
timestamp completed_at
uuid initiated_by_person_id FK
int form_response_id FK
uuid subject_person_id FK
uuid impersonator_person_id FK
}
records__workflow_step_permissions {
int workflow_step_permission_id PK
int workflow_step_id FK
varchar(50) role_code
bool can_view
bool can_edit
bool can_transition
}
records__workflow_step_transitions {
int transition_id PK
int from_step_id FK
int to_step_id FK
varchar(50) transition_code
varchar(200) name
varchar(500) allowed_roles
bool requires_approval
bool is_automatic
varchar(100) automation_rule_code
}
records__workflow_steps {
int workflow_step_id PK
int workflow_definition_id FK
varchar(50) step_code
varchar(200) name
text description
varchar(20) step_type
int sort_order
bool is_initial
bool is_final
text instructions
varchar(50) assignee_rule
int form_template_id FK
varchar(100) assignee_field_code
}
records__workflow_transitions {
int workflow_transition_id PK
int workflow_definition_id FK
int from_step_id FK
int to_step_id FK
varchar(200) transition_name
text condition_expression
}
records__workflow_definitions ||--o{ records__workflow_initiator_roles : "workflow_definition_id"
records__workflow_steps ||--o{ records__workflow_instance_parallel_tracking : "parallel_step_id"
records__workflow_instances ||--o{ records__workflow_instance_parallel_tracking : "workflow_instance_id"
records__workflow_instance_steps ||--o{ records__workflow_instance_step_assignees : "workflow_instance_step_id"
records__workflow_instances ||--o{ records__workflow_instance_steps : "workflow_instance_id"
records__workflow_steps ||--o{ records__workflow_instance_steps : "workflow_step_id"
records__workflow_steps ||--o{ records__workflow_instances : "current_workflow_step_id"
records__workflow_definitions ||--o{ records__workflow_instances : "workflow_definition_id"
records__workflow_steps ||--o{ records__workflow_step_permissions : "workflow_step_id"
records__workflow_steps ||--o{ records__workflow_step_transitions : "from_step_id"
records__workflow_steps ||--o{ records__workflow_step_transitions : "to_step_id"
records__workflow_definitions ||--o{ records__workflow_steps : "workflow_definition_id"
records__workflow_steps ||--o{ records__workflow_transitions : "from_step_id"
records__workflow_steps ||--o{ records__workflow_transitions : "to_step_id"
records__workflow_definitions ||--o{ records__workflow_transitions : "workflow_definition_id"13. Records - Test Scores
mermaid
erDiagram
records__applicant_test_score_sections {
int score_section_id PK
int score_id FK
int section_id FK
numeric(6_2) score
int percentile
}
records__applicant_test_scores {
int score_id PK
uuid person_id FK
int application_id FK
int test_type_id FK
numeric(6_2) composite_score
date test_date
timestamp score_reported_at
timestamp self_reported_at
bool is_self_reported
bool is_official
int verification_status_id FK
int verified_by FK
timestamp verified_at
text verification_notes
timestamp official_score_received_at
varchar(100) score_send_confirmation
int document_id FK
text notes
}
records__institution_test_score_policies {
int policy_id PK
int test_type_id FK
varchar(30) score_selection_method
bool allow_superscoring
varchar(30) superscore_method
text notes
bool is_active
}
records__person_superscore_records {
int superscore_id PK
uuid person_id FK
int test_type_id FK
numeric(6_2) superscore
timestamp calculated_at
int[] source_score_ids
}
records__standardized_test_sections {
int section_id PK
int test_type_id FK
varchar(30) code
varchar(100) name
numeric(6_2) min_score
numeric(6_2) max_score
bool is_required
int sort_order
}
records__standardized_test_types {
int test_type_id PK
varchar(20) code UK
varchar(100) name
text description
numeric(6_2) min_score
numeric(6_2) max_score
numeric(4_2) score_increment
int validity_months
bool is_active
int sort_order
}
records__applicant_test_scores ||--o{ records__applicant_test_score_sections : "score_id"
records__standardized_test_sections ||--o{ records__applicant_test_score_sections : "section_id"
records__standardized_test_types ||--o{ records__applicant_test_scores : "test_type_id"
records__standardized_test_types ||--o{ records__institution_test_score_policies : "test_type_id"
records__standardized_test_types ||--o{ records__person_superscore_records : "test_type_id"
records__standardized_test_types ||--o{ records__standardized_test_sections : "test_type_id"14. Student Finance - Catalog & Rates
mermaid
erDiagram
studentfinance__ar_code_mappings {
int ar_code_mapping_id PK
varchar(10) ar_code UK
varchar(20) category
int fee_type_id FK
varchar(200) description
bool is_active
}
studentfinance__billing_bands {
int band_id PK
int billing_method_id FK
varchar(100) band_name
numeric min_credits
bool min_inclusive
numeric max_credits
bool max_inclusive
varchar(20) rate_type
numeric flat_amount
numeric per_credit_rate
numeric credits_included_in_flat
int currency_id FK
int sort_order
date effective_date
date end_date
varchar(10) band_kind
numeric(5_2) credit_margin
varchar(10) excess_rounding
}
studentfinance__billing_method_configs {
int config_id PK
int billing_method_id FK
int term_id FK
int degree_level_id FK
int student_type_id FK
int campus_id FK
int priority
date effective_date
date end_date
jsonb selector
int cohort_id FK
}
studentfinance__billing_methods {
int billing_method_id PK
varchar(30) method_code UK
varchar(200) name
varchar(500) description
varchar(20) method_type
bool is_active
}
studentfinance__billing_settings {
int setting_id PK
varchar(50) setting_key UK
varchar(500) setting_value
varchar(200) description
}
studentfinance__billing_student_type_rules {
int billing_student_type_rule_id PK
varchar(200) name
text description
int student_type_id FK
jsonb selector
int priority
date effective_date
date end_date
bool is_active
}
studentfinance__billing_transaction_items {
int item_id PK
int billing_transaction_id
int enrollment_id
int section_id FK
numeric billing_credits
numeric tuition_amount
numeric section_fee_amount
int fee_type_id FK
numeric refund_amount
date refund_date
numeric refund_percentage
uuid billing_transaction_uuid FK
}
studentfinance__course_fees {
int course_fee_id PK
int course_id FK
int fee_type_id FK
varchar(200) fee_name
varchar(20) calculation_type
numeric(10_2) amount
int currency_id FK
int campus_id FK
int term_id FK
date effective_date
date end_date
bool is_active
text notes
varchar(20) source
timestamp ods_synced_at
varchar(10) source_ar_code
}
studentfinance__credit_rate_schedules {
int rate_schedule_id PK
int billing_method_id FK
numeric rate_per_credit
int currency_id FK
numeric min_credits
numeric max_credits
date effective_date
date end_date
}
studentfinance__fee_schedules {
int fee_schedule_id PK
int fee_type_id FK
int term_id FK
numeric(10_2) amount
bool per_credit_hour
date effective_date
date end_date
}
studentfinance__fee_types {
int fee_type_id PK
varchar(20) fee_code UK
varchar(200) name
text description
bool is_tuition
bool is_refundable
varchar(50) gl_account_code
bool is_active
}
studentfinance__payment_plan_fees {
int fee_id PK
int plan_id FK
int fee_type_id FK
numeric flat_fee
numeric percentage_fee
bool is_upfront
bool spread_across_payments
int currency_id FK
date effective_date
date end_date
}
studentfinance__program_fees {
int program_fee_id PK
int major_id FK
varchar(50) major_code_pattern
int campus_id FK
int fee_type_id FK
varchar(200) fee_name
varchar(20) calculation_type
numeric(10_2) amount
int currency_id FK
int degree_level_id FK
int student_type_id FK
int term_id FK
bool primary_major_only
int priority
date effective_date
date end_date
bool is_active
text notes
}
studentfinance__refund_schedule_entries {
int entry_id PK
int refund_schedule_id FK
varchar(100) entry_name
int start_day
int end_day
numeric refund_percentage
bool applies_to_tuition
bool applies_to_fees
int sort_order
}
studentfinance__refund_schedules {
int refund_schedule_id PK
int term_id FK
varchar(200) name
varchar(500) description
bool is_active
}
studentfinance__section_fee_overrides {
int section_fee_override_id PK
int section_id FK
int fee_type_id FK
varchar(200) fee_name
varchar(20) calculation_type
numeric(10_2) amount
int currency_id FK
bool replaces_course_fee
date effective_date
date end_date
bool is_active
text notes
varchar(20) source
timestamp ods_synced_at
varchar(10) source_ar_code
}
studentfinance__term_fee_bands {
int band_id PK
int term_fee_id FK
varchar(100) band_name
numeric min_credits
bool min_inclusive
numeric max_credits
bool max_inclusive
numeric amount
int sort_order
}
studentfinance__term_fee_categories {
int category_id PK
int term_fee_id FK
int degree_level_id FK
int student_type_id FK
int campus_id FK
int student_list_id FK
numeric amount
int priority
date effective_date
date end_date
}
studentfinance__term_fee_category_bands {
int category_band_id PK
int category_id FK
varchar(100) band_name
numeric min_credits
bool min_inclusive
numeric max_credits
bool max_inclusive
numeric amount
int sort_order
}
studentfinance__term_fees {
int term_fee_id PK
int term_id FK
int fee_type_id FK
varchar(200) fee_name
varchar(500) description
varchar(20) calculation_type
bool is_universal
numeric flat_amount
int currency_id FK
int sort_order
bool is_active
date effective_date
date end_date
jsonb eligibility
}
studentfinance__upfront_payment_discounts {
int discount_id PK
int term_id FK
varchar(20) discount_type
numeric discount_amount
numeric discount_percentage
int currency_id FK
date effective_date
date end_date
}
studentfinance__fee_types ||--o{ studentfinance__ar_code_mappings : "fee_type_id"
studentfinance__billing_methods ||--o{ studentfinance__billing_bands : "billing_method_id"
studentfinance__billing_methods ||--o{ studentfinance__billing_method_configs : "billing_method_id"
studentfinance__fee_types ||--o{ studentfinance__billing_transaction_items : "fee_type_id"
studentfinance__fee_types ||--o{ studentfinance__course_fees : "fee_type_id"
studentfinance__billing_methods ||--o{ studentfinance__credit_rate_schedules : "billing_method_id"
studentfinance__fee_types ||--o{ studentfinance__fee_schedules : "fee_type_id"
studentfinance__fee_types ||--o{ studentfinance__payment_plan_fees : "fee_type_id"
studentfinance__fee_types ||--o{ studentfinance__program_fees : "fee_type_id"
studentfinance__refund_schedules ||--o{ studentfinance__refund_schedule_entries : "refund_schedule_id"
studentfinance__fee_types ||--o{ studentfinance__section_fee_overrides : "fee_type_id"
studentfinance__term_fees ||--o{ studentfinance__term_fee_bands : "term_fee_id"
studentfinance__term_fees ||--o{ studentfinance__term_fee_categories : "term_fee_id"
studentfinance__term_fee_categories ||--o{ studentfinance__term_fee_category_bands : "category_id"
studentfinance__fee_types ||--o{ studentfinance__term_fees : "fee_type_id"15. Student Finance - Student Charges & Payments
mermaid
erDiagram
studentfinance__colleague_ar_charges {
bigint colleague_ar_charge_id PK
int student_id FK
int term_id FK
varchar(12) ar_invoice_item_id UK
varchar(12) invoice_no
varchar(20) person_id
varchar(20) term_code
varchar(10) ar_code
text description
varchar(200) course_description
varchar(20) student_course_sec
numeric(12_2) charge_amount
numeric(12_2) credit_amount
numeric(12_2) net_amount
date invoice_date
timestamp imported_at
}
studentfinance__payment_allocations {
int allocation_id PK
int payment_id
int charge_id
numeric(10_2) amount
date allocation_date
uuid charge_uuid FK
uuid payment_uuid FK
}
studentfinance__payment_plan_eligibility {
int eligibility_id PK
int plan_id FK
int student_list_id FK
int degree_level_id FK
int student_type_id FK
int campus_id FK
int major_id FK
numeric min_credits
int priority
bool is_default_plan
date effective_date
date end_date
}
studentfinance__payment_plan_schedules {
int schedule_id PK
int plan_term_id FK
int payment_number
date due_date
numeric percentage_of_total
}
studentfinance__payment_plan_terms {
int plan_term_id PK
int plan_id FK
int term_id FK
date enrollment_deadline
date first_payment_date
bool is_active
}
studentfinance__payment_plans {
int plan_id PK
varchar(30) plan_code UK
varchar(200) name
varchar(500) description
int number_of_payments
varchar(20) payment_frequency
bool is_active
}
studentfinance__student_account_balance {
int balance_id PK
int student_id FK
int term_id FK
numeric(10_2) total_charges
numeric(10_2) total_payments
numeric(10_2) current_balance
timestamp last_updated
}
studentfinance__student_billing_transactions {
int legacy_billing_transaction_id
int student_id FK
int term_id FK
int billing_method_id FK
date billing_date
numeric total_tuition
numeric total_fees
numeric total_billed
numeric total_credits_billed
int currency_id FK
int status_id FK
uuid student_billing_transaction_id PK
varchar(32) legacy_id
}
studentfinance__student_charges {
int legacy_charge_id
int student_id FK
int term_id FK
int fee_type_id FK
numeric(10_2) amount
text description
date charge_date
date due_date
int status_id FK
uuid student_charge_id PK
varchar(32) legacy_id
}
studentfinance__student_class_waivers {
int student_class_waiver_id PK
int student_id FK
int term_id FK
int section_id FK
varchar(50) reason
text notes
bool is_active
}
studentfinance__student_payment_plan_enrollments {
int enrollment_id PK
int student_id FK
int plan_term_id FK
int billing_transaction_id
numeric total_amount
numeric plan_fee_amount
date enrolled_date
int status_id FK
uuid billing_transaction_uuid FK
}
studentfinance__student_payment_plan_schedule {
int schedule_item_id PK
int plan_enrollment_id FK
int payment_number
date due_date
numeric amount_due
numeric amount_paid
int status_id FK
date paid_date
}
studentfinance__student_payments {
int legacy_payment_id
int student_id FK
numeric(10_2) amount
date payment_date
int payment_method_id FK
varchar(100) reference_number
int status_id FK
uuid student_payment_id PK
varchar(32) legacy_id
}
studentfinance__student_charges ||--o{ studentfinance__payment_allocations : "charge_uuid"
studentfinance__student_payments ||--o{ studentfinance__payment_allocations : "payment_uuid"
studentfinance__payment_plans ||--o{ studentfinance__payment_plan_eligibility : "plan_id"
studentfinance__payment_plan_terms ||--o{ studentfinance__payment_plan_schedules : "plan_term_id"
studentfinance__payment_plans ||--o{ studentfinance__payment_plan_terms : "plan_id"
studentfinance__student_billing_transactions ||--o{ studentfinance__student_payment_plan_enrollments : "billing_transaction_uuid"
studentfinance__payment_plan_terms ||--o{ studentfinance__student_payment_plan_enrollments : "plan_term_id"
studentfinance__student_payment_plan_enrollments ||--o{ studentfinance__student_payment_plan_schedule : "plan_enrollment_id"16. Student Finance - Plans & Elections
mermaid
erDiagram
studentfinance__housing_plans {
int housing_plan_id PK
int campus_id FK
varchar(50) plan_code
varchar(200) name
text description
int fee_type_id FK
numeric(10_2) amount
int currency_id FK
int term_id FK
bool is_prorated
varchar(20) proration_basis
int sort_order
bool is_active
date effective_date
date end_date
}
studentfinance__meal_plans {
int meal_plan_id PK
int campus_id FK
varchar(50) plan_code
varchar(200) name
text description
int fee_type_id FK
numeric(10_2) amount
int currency_id FK
int term_id FK
int meals_per_week
numeric(10_2) flex_dollars
bool is_prorated
varchar(20) proration_basis
int sort_order
bool is_active
date effective_date
date end_date
}
studentfinance__student_housing_assignments {
uuid student_housing_assignment_id PK
int student_id FK
int term_id FK
int housing_plan_id FK
int status_id FK
date start_date
date end_date
uuid replaces_assignment_id FK
bool is_current
text notes
}
studentfinance__student_meal_plan_elections {
uuid student_meal_plan_election_id PK
int student_id FK
int term_id FK
int meal_plan_id FK
int status_id FK
date start_date
date end_date
uuid replaces_election_id FK
bool is_current
text notes
}
studentfinance__housing_plans ||--o{ studentfinance__student_housing_assignments : "housing_plan_id"
studentfinance__student_housing_assignments ||--o{ studentfinance__student_housing_assignments : "parent"
studentfinance__meal_plans ||--o{ studentfinance__student_meal_plan_elections : "meal_plan_id"
studentfinance__student_meal_plan_elections ||--o{ studentfinance__student_meal_plan_elections : "parent"17. Core - System & Reference
mermaid
erDiagram
core__email_templates {
int template_id PK
varchar(100) template_code UK
varchar(200) name
varchar(500) subject
text body_html
text description
text available_variables
bool is_active
}
core__migration_watermarks {
text step_name PK
timestamp last_source_max
timestamptz last_run_at
text last_run_mode
text notes
}
core__schema_migrations {
text filename PK
text checksum
timestamptz started_at
timestamptz applied_at
text applied_by
text git_commit
}
core__sys_currencies {
int currency_id PK
varchar(3) iso_code UK
varchar(100) name
varchar(5) symbol
int decimal_places
bool is_active
}
core__sys_institution_settings {
int setting_id PK
varchar(100) setting_key UK
text setting_value
varchar(50) setting_type
varchar(500) description
}
core__ui_translations {
int translation_id PK
varchar(10) language_code
varchar(200) translation_key
text translation_value
}18. LMS - Connector Definitions
mermaid
erDiagram
lms__connector_operations {
int operation_id PK
int connector_type_id FK
varchar(60) operation_code
text description
bool is_active
}
lms__connector_types {
int connector_type_id PK
varchar(50) code UK
varchar(200) name
text description
varchar(40) auth_strategy_code
varchar(30) pagination_style
numeric(6_2) default_rate_limit_rps
int default_rate_burst
bool is_active
}
lms__operation_versions {
int version_id PK
int operation_id FK
int instance_id FK
int version_no
jsonb definition
timestamptz effective_at
text notes
}
lms__connector_types ||--o{ lms__connector_operations : "connector_type_id"
lms__connector_operations ||--o{ lms__operation_versions : "operation_id"19. LMS - Instances, Credentials & Mappings
mermaid
erDiagram
lms__assignment_rules {
int rule_id PK
int instance_id FK
int org_unit_id FK
int campus_id FK
int priority
bool is_active
}
lms__instance_access_tokens {
int instance_id PK
bytea token_ciphertext
varchar(40) enc_key_id
timestamptz expires_at
timestamptz obtained_at
}
lms__instance_credentials {
int credential_id PK
int instance_id FK
varchar(20) purpose
varchar(500) client_id
bytea secret_ciphertext
varchar(40) enc_key_id
varchar(2000) token_url
varchar(1000) scopes
int rotated_from_credential_id FK
timestamptz activated_at
timestamptz retired_at
bool is_active
}
lms__instance_org_unit_mappings {
int instance_org_unit_mapping_id PK
int instance_id FK
int org_unit_id FK
varchar(200) lms_account_id
}
lms__instance_role_mappings {
int instance_role_mapping_id PK
int instance_id FK
varchar(12) role_key
varchar(200) lms_role
}
lms__instance_term_mappings {
int instance_term_mapping_id PK
int instance_id FK
int term_id FK
varchar(200) lms_term_id
}
lms__instances {
int instance_id PK
int connector_type_id FK
varchar(50) code UK
varchar(200) name
varchar(2000) base_url
bool is_sandbox
bool is_active
bool dry_run
bool grade_passback_enabled
varchar(20) match_strategy
varchar(20) drop_behavior
numeric(6_2) rate_limit_rps
int rate_burst
varchar(500) shell_name_template
varchar(500) shell_code_template
jsonb settings
bool create_missing_users
}
lms__sync_policy {
int policy_id PK
varchar(20) provisioning_mode
varchar(20) provision_trigger
int provision_days_before
varchar(20) enrollment_sync_trigger
int enrollment_days_before
varchar(20) conclude_policy
int conclude_days_after
varchar(12) drift_missing_enrollment
varchar(12) drift_extra_enrollment
varchar(12) drift_missing_shell
}
lms__instances ||--o{ lms__assignment_rules : "instance_id"
lms__instances ||--o{ lms__instance_access_tokens : "instance_id"
lms__instances ||--o{ lms__instance_credentials : "instance_id"
lms__instance_credentials ||--o{ lms__instance_credentials : "parent"
lms__instances ||--o{ lms__instance_org_unit_mappings : "instance_id"
lms__instances ||--o{ lms__instance_role_mappings : "instance_id"
lms__instances ||--o{ lms__instance_term_mappings : "instance_id"20. LMS - Shells, Enrollments & Sync
mermaid
erDiagram
lms__course_shells {
int shell_id PK
int instance_id FK
int term_id FK
varchar(200) lms_course_id
varchar(20) status_code
bool is_adopted
timestamptz enrollment_sync_started_at
varchar(500) name_snapshot
varchar(200) code_snapshot
varchar(200) sis_course_id
}
lms__drift_findings {
bigint finding_id PK
int instance_id FK
int shell_id FK
int section_id
varchar(40) finding_code
uuid subject_person_id
jsonb details
timestamptz detected_at
timestamptz resolved_at
varchar(20) resolution_code
varchar(100) resolved_by
}
lms__enrollment_links {
bigint link_id PK
int shell_id FK
uuid person_id FK
varchar(12) role_key
uuid source_enrollment_id FK
varchar(200) lms_enrollment_id
varchar(12) state_code
timestamptz last_synced_at
}
lms__export_files {
bigint export_file_id PK
bigint export_run_id FK
varchar(20) dataset
varchar(200) filename
int row_count
bigint byte_size
char sha256
varchar(1000) storage_path
}
lms__export_runs {
bigint export_run_id PK
int instance_id FK
varchar(8) mode
int[] term_ids
varchar[] datasets
varchar(16) status
jsonb row_counts
varchar(200) lms_import_id
varchar(100) requested_by
timestamptz started_at
timestamptz finished_at
}
lms__export_watermarks {
int instance_id PK
varchar(20) dataset PK
timestamptz last_source_max
}
lms__integration_log {
bigint log_id PK
bigint job_id
int instance_id
varchar(60) operation_code
varchar(8) http_method
text url
int status_code
varchar(30) error_class
bool is_dry_run
jsonb request_headers
text request_body
text response_body
int duration_ms
timestamptz attempted_at
}
lms__passback_watermarks {
int instance_id PK
bigint last_log_id
}
lms__section_overrides {
int section_id PK
int instance_id FK
bool provisioning_exempt
text notes
}
lms__shell_sections {
int shell_section_id PK
int shell_id FK
int section_id FK
bool is_primary
timestamptz added_at
timestamptz removed_at
}
lms__sync_jobs {
bigint job_id PK
varchar(40) kind
int instance_id FK
int shell_id FK
int section_id
uuid subject_person_id
uuid enrollment_id
bigint export_run_id
varchar(200) dedupe_key
jsonb payload
varchar(12) status
smallint priority
timestamptz run_after
int attempts
int max_attempts
varchar(100) locked_by
timestamptz locked_at
text last_error
varchar(30) last_error_class
timestamptz finished_at
}
lms__sync_runs {
bigint run_id PK
varchar(30) run_kind
timestamptz started_at
timestamptz finished_at
varchar(12) status
jsonb stats
text error
}
lms__user_mappings {
int mapping_id PK
int instance_id FK
uuid person_id FK
varchar(200) lms_user_id
varchar(20) matched_by
}
lms__course_shells ||--o{ lms__drift_findings : "shell_id"
lms__course_shells ||--o{ lms__enrollment_links : "shell_id"
lms__export_runs ||--o{ lms__export_files : "export_run_id"
lms__course_shells ||--o{ lms__shell_sections : "shell_id"
lms__course_shells ||--o{ lms__sync_jobs : "shell_id"21. Payments - Online Payment Sessions
mermaid
erDiagram
payments__payment_session {
bigint payment_session_id PK
uuid session_token UK
int payment_type_id FK
varchar(20) ods_person_id
text[] matched_on
inet client_ip
varchar(20) status
timestamptz expires_at
}
payments__payment_transaction {
bigint payment_transaction_id PK
uuid uid UK
bigint payment_session_id FK
int payment_type_id FK
varchar(20) ods_person_id
numeric(9_2) amount
varchar(20) status
varchar(10) aci_result_code
varchar(100) aci_result_text
varchar(100) aci_confirmation
varchar(20) account_type
varchar(5) card_last_four
numeric(9_2) posted_amount
numeric(9_2) transaction_fee
numeric(9_2) total_charge
bool amount_mismatch
timestamptz aci_transaction_at
timestamptz postback_received_at
inet postback_source_ip
text raw_postback
varchar(20) colleague_post_status
timestamptz colleague_posted_at
varchar(100) colleague_reference
}
payments__payment_type {
int payment_type_id PK
varchar(50) code UK
varchar(200) display_name
text description
varchar(100) aci_product_id
bool requires_student_verification
bool is_active
int sort_order
}
payments__verification_attempt {
bigint verification_attempt_id PK
inet client_ip
varchar(20) attempted_student_id
bool succeeded
}
payments__payment_type ||--o{ payments__payment_session : "payment_type_id"
payments__payment_session ||--o{ payments__payment_transaction : "payment_session_id"
payments__payment_type ||--o{ payments__payment_transaction : "payment_type_id"22. Core - Staff Email (migration 268)
mermaid
erDiagram
core__email_message_recipients {
int email_message_recipient_id PK
int email_message_id FK
uuid person_id FK
varchar(254) email_address
bool is_sender_copy
varchar(20) status
int attempts
timestamp next_attempt_at
text last_error
timestamp sent_at
}
core__email_messages {
int email_message_id PK
uuid sender_person_id FK
int sender_user_id
int acting_role_id FK
varchar(254) from_address
varchar(200) from_name
varchar(254) reply_to
varchar(300) subject
text body_html
jsonb sources
int recipient_count
varchar(20) status
timestamp completed_at
}
core__role_email_policies {
int role_id PK
int max_recipients
bool can_use_noreply
}
core__sender_address_grants {
int sender_address_grant_id PK
int sender_address_id FK
uuid person_id FK
}
core__sender_addresses {
int sender_address_id PK
varchar(254) email_address
varchar(200) display_name
bool is_active
}
core__email_messages ||--o{ core__email_message_recipients : "email_message_id"
core__sender_addresses ||--o{ core__sender_address_grants : "sender_address_id"Cross-Section Relationship Summary
Foreign keys inside one schema whose two ends were placed in different sections. These are not drawn above — a section shows only the edges wholly inside it — so this table is where the other 154 live.
| Parent | Child column | Parent section |
|---|---|---|
core__people | core.application_access_tokens.person_id | 2. Core - People & Identity |
core__people | core.audit_log.changed_by_person_id | 2. Core - People & Identity |
core__people | core.auth_users.person_id | 2. Core - People & Identity |
core__people | core.clubs_organizations.advisor_person_id | 2. Core - People & Identity |
core__people | core.data_change_history.changed_by_person_id | 2. Core - People & Identity |
core__people | core.email_message_recipients.person_id | 2. Core - People & Identity |
core__roles | core.email_messages.acting_role_id | 3. Core - Auth & Authorization |
core__people | core.email_messages.sender_person_id | 2. Core - People & Identity |
core__people | core.email_templates.created_by | 2. Core - People & Identity |
core__people | core.email_templates.updated_by | 2. Core - People & Identity |
core__people | core.ferpa_change_log.person_id | 2. Core - People & Identity |
core__person_identifiers | core.identifier_access_log.person_identifier_id | 2. Core - People & Identity |
core__people | core.identifier_access_log.target_person_id | 2. Core - People & Identity |
core__auth_users | core.identifier_access_log.viewer_user_id | 3. Core - Auth & Authorization |
core__people | core.legacy_academic_credits.person_id | 2. Core - People & Identity |
core__countries | core.people.citizenship_country_id | 1. Core - Organization |
core__countries | core.people.country_of_birth_id | 1. Core - Organization |
core__auth_users | core.permission_audit_log.changed_by_user_id | 3. Core - Auth & Authorization |
core__countries | core.person_citizenships.country_id | 1. Core - Organization |
core__countries | core.person_identifiers.issuing_country_id | 1. Core - Organization |
core__state_provinces | core.person_identifiers.issuing_state_province_id | 1. Core - Organization |
core__auth_users | core.person_merge_log.merged_by_user_id | 3. Core - Auth & Authorization |
core__auth_users | core.person_name_audit_log.changed_by_user_id | 3. Core - Auth & Authorization |
core__people | core.person_name_audit_log.person_id | 2. Core - People & Identity |
core__person_names | core.person_name_audit_log.person_name_id | 2. Core - People & Identity |
core__auth_users | core.person_photos.reviewed_by_user_id | 3. Core - Auth & Authorization |
core__auth_users | core.person_photos.submitted_by_user_id | 3. Core - Auth & Authorization |
core__auth_users | core.person_photos.uploaded_by_user_id | 3. Core - Auth & Authorization |
core__auth_users | core.photo_sync_runs.triggered_by | 3. Core - Auth & Authorization |
core__auth_users | core.proxy_access_audit_log.performed_by_user_id | 3. Core - Auth & Authorization |
core__people | core.proxy_access_audit_log.proxy_person_id | 2. Core - People & Identity |
core__people | core.proxy_invitations.proxy_person_id | 2. Core - People & Identity |
core__roles | core.role_email_policies.role_id | 3. Core - Auth & Authorization |
core__people | core.sender_address_grants.person_id | 2. Core - People & Identity |
core__people | core.student_proxy_access.proxy_person_id | 2. Core - People & Identity |
core__auth_users | core.student_proxy_access.revoked_by_user_id | 3. Core - Auth & Authorization |
core__people | core.unit_administrators.person_id | 2. Core - People & Identity |
core__unit_administrators | core.user_active_role.active_admin_id | 1. Core - Organization |
lms__instances | lms.course_shells.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.drift_findings.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.export_runs.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.export_watermarks.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__connector_types | lms.instances.connector_type_id | 18. LMS - Connector Definitions |
lms__instances | lms.operation_versions.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.passback_watermarks.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.section_overrides.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.sync_jobs.instance_id | 19. LMS - Instances, Credentials & Mappings |
lms__instances | lms.user_mappings.instance_id | 19. LMS - Instances, Credentials & Mappings |
records__bulletins | records.academic_policy_rules.bulletin_id | 5. Records - Academic Calendar |
records__grading_system_grades | records.academic_policy_rules.min_grade_id | 6. Records - Courses & Sections |
records__standardized_test_types | records.academic_policy_rules.test_type_id | 13. Records - Test Scores |
records__academic_terms | records.applicants.application_term_id | 5. Records - Academic Calendar |
records__majors | records.applicants.intended_major_id | 7. Records - Programs & Requirements |
records__academic_terms | records.classes.term_id | 5. Records - Academic Calendar |
records__majors | records.cohort_majors.major_id | 7. Records - Programs & Requirements |
records__academic_years | records.cohorts.academic_year_id | 5. Records - Academic Calendar |
records__academic_terms | records.cohorts.expected_graduation_term_id | 5. Records - Academic Calendar |
records__majors | records.cohorts.major_id | 7. Records - Programs & Requirements |
records__academic_terms | records.cohorts.start_term_id | 5. Records - Academic Calendar |
records__bulletins | records.course_bulletins.bulletin_id | 5. Records - Academic Calendar |
records__courses | records.course_material_assignments.course_id | 6. Records - Courses & Sections |
records__courses | records.course_offering_terms.course_id | 6. Records - Courses & Sections |
records__bulletins | records.course_patterns.bulletin_id | 5. Records - Academic Calendar |
records__requirement_blocks | records.course_requisite_links.block_id | 7. Records - Programs & Requirements |
records__sub_terms | records.course_sections.sub_term_id | 5. Records - Academic Calendar |
records__academic_terms | records.course_sections.term_id | 5. Records - Academic Calendar |
records__bulletins | records.course_substitutions.bulletin_id | 5. Records - Academic Calendar |
records__majors | records.course_substitutions.major_id | 7. Records - Programs & Requirements |
records__requirement_blocks | records.course_substitutions.requirement_block_id | 7. Records - Programs & Requirements |
records__requirement_blocks | records.courses.prereq_block_id | 7. Records - Programs & Requirements |
records__advisors | records.degree_default_advisors.advisor_id | 8. Records - Roles & Student Data |
records__grading_systems | records.degree_level_grading_systems.grading_system_id | 6. Records - Courses & Sections |
records__bulletins | records.degrees.bulletin_id | 5. Records - Academic Calendar |
records__course_sections | records.grade_releases.section_id | 6. Records - Courses & Sections |
records__course_sections | records.grade_visibility_completions.section_id | 6. Records - Courses & Sections |
records__students | records.grade_visibility_completions.student_id | 8. Records - Roles & Student Data |
records__courses | records.grade_visibility_rules.course_id | 6. Records - Courses & Sections |
records__course_sections | records.grade_visibility_rules.section_id | 6. Records - Courses & Sections |
records__academic_terms | records.grade_visibility_rules.term_id | 5. Records - Academic Calendar |
records__degrees | records.graduation_applications.degree_id | 7. Records - Programs & Requirements |
records__academic_terms | records.graduation_applications.expected_graduation_term_id | 5. Records - Academic Calendar |
records__students | records.graduation_applications.student_id | 8. Records - Roles & Student Data |
records__academic_terms | records.graduation_dates.term_id | 5. Records - Academic Calendar |
records__degrees | records.graduation_records.degree_id | 7. Records - Programs & Requirements |
records__majors | records.graduation_records.major_id | 7. Records - Programs & Requirements |
records__students | records.graduation_records.student_id | 8. Records - Roles & Student Data |
records__hold_types | records.hold_evaluation_log.hold_type_id | 8. Records - Roles & Student Data |
records__bulletins | records.major_blocks.bulletin_id | 5. Records - Academic Calendar |
records__advisors | records.major_default_advisors.advisor_id | 8. Records - Roles & Student Data |
records__bulletins | records.majors.bulletin_id | 5. Records - Academic Calendar |
records__bulletins | records.minor_blocks.bulletin_id | 5. Records - Academic Calendar |
records__advisors | records.minor_default_advisors.advisor_id | 8. Records - Roles & Student Data |
records__bulletins | records.minors.bulletin_id | 5. Records - Academic Calendar |
records__requirement_blocks | records.planned_courses.requirement_block_id | 7. Records - Programs & Requirements |
records__requirement_items | records.planned_courses.requirement_item_id | 7. Records - Programs & Requirements |
records__course_sections | records.planned_courses.section_id | 6. Records - Courses & Sections |
records__students | records.planned_courses.student_id | 8. Records - Roles & Student Data |
records__majors | records.probation_alert_log.major_id | 7. Records - Programs & Requirements |
records__students | records.probation_alert_log.student_id | 8. Records - Roles & Student Data |
records__students | records.registration_transactions.student_id | 8. Records - Roles & Student Data |
records__academic_terms | records.registration_transactions.term_id | 5. Records - Academic Calendar |
records__bulletins | records.requirement_blocks.bulletin_id | 5. Records - Academic Calendar |
records__grading_system_grades | records.requirement_blocks.min_grade_id | 6. Records - Courses & Sections |
records__grading_system_grades | records.requirement_groups.min_grade_id | 6. Records - Courses & Sections |
records__courses | records.requirement_item_courses.course_id | 6. Records - Courses & Sections |
records__courses | records.requirement_items.course_id | 6. Records - Courses & Sections |
records__course_patterns | records.requirement_items.course_pattern_id | 6. Records - Courses & Sections |
records__requirement_blocks | records.requirement_waivers.block_id | 7. Records - Programs & Requirements |
records__requirement_items | records.requirement_waivers.item_id | 7. Records - Programs & Requirements |
records__students | records.requirement_waivers.student_id | 8. Records - Roles & Student Data |
records__courses | records.requirement_waivers.substitute_course_id | 6. Records - Courses & Sections |
records__teachers | records.section_instructors.teacher_id | 8. Records - Roles & Student Data |
records__course_sections | records.section_material_assignments.section_id | 6. Records - Courses & Sections |
records__course_sections | records.section_waitlists.section_id | 6. Records - Courses & Sections |
records__bulletins | records.sequence_policies.bulletin_id | 5. Records - Academic Calendar |
records__majors | records.student_academic_status_history.major_id | 7. Records - Programs & Requirements |
records__academic_terms | records.student_awards.term_id | 5. Records - Academic Calendar |
records__requirement_blocks | records.student_course_substitutions.block_id | 7. Records - Programs & Requirements |
records__courses | records.student_course_substitutions.original_course_id | 6. Records - Courses & Sections |
records__students | records.student_course_substitutions.student_id | 8. Records - Roles & Student Data |
records__courses | records.student_course_substitutions.substitute_course_id | 6. Records - Courses & Sections |
records__degrees | records.student_degree_progress.degree_id | 7. Records - Programs & Requirements |
records__students | records.student_degree_progress.student_id | 8. Records - Roles & Student Data |
records__emphasis_areas | records.student_emphases.emphasis_id | 7. Records - Programs & Requirements |
records__academic_terms | records.student_holds.term_id | 5. Records - Academic Calendar |
records__workflow_instances | records.student_leaves.workflow_instance_id | 12. Records - Workflows (Generic) |
records__bulletins | records.student_majors.bulletin_id | 5. Records - Academic Calendar |
records__majors | records.student_majors.major_id | 7. Records - Programs & Requirements |
records__bulletins | records.student_minors.bulletin_id | 5. Records - Academic Calendar |
records__minors | records.student_minors.minor_id | 7. Records - Programs & Requirements |
records__courses | records.student_noncredit_completions.course_id | 6. Records - Courses & Sections |
records__students | records.student_noncredit_completions.student_id | 8. Records - Roles & Student Data |
records__requirement_blocks | records.student_requirement_fulfillment.block_id | 7. Records - Programs & Requirements |
records__grading_system_grades | records.student_section_enrollments.grade_id | 6. Records - Courses & Sections |
records__grading_system_grades | records.student_section_enrollments.incomplete_default_grade_id | 6. Records - Courses & Sections |
records__workflow_instances | records.student_section_enrollments.incomplete_workflow_instance_id | 12. Records - Workflows (Generic) |
records__grading_system_grades | records.student_section_enrollments.midterm_grade_id | 6. Records - Courses & Sections |
records__course_sections | records.student_section_enrollments.section_id | 6. Records - Courses & Sections |
records__students | records.student_section_enrollments.student_id | 8. Records - Roles & Student Data |
records__students | records.student_term_enrollments.student_id | 8. Records - Roles & Student Data |
records__academic_terms | records.student_term_enrollments.term_id | 5. Records - Academic Calendar |
records__academic_terms | records.students.initial_term_id | 5. Records - Academic Calendar |
records__academic_terms | records.term_registration_windows.term_id | 5. Records - Academic Calendar |
records__course_sections | records.transaction_enrollment_items.section_id | 6. Records - Courses & Sections |
records__courses | records.transfer_course_equivalencies.equivalent_course_id | 6. Records - Courses & Sections |
records__courses | records.transfer_credits.equivalent_course_id | 6. Records - Courses & Sections |
records__students | records.transfer_credits.student_id | 8. Records - Roles & Student Data |
records__academic_terms | records.transfer_credits.transfer_term_id | 5. Records - Academic Calendar |
studentfinance__student_billing_transactions | studentfinance.billing_transaction_items.billing_transaction_uuid | 15. Student Finance - Student Charges & Payments |
studentfinance__fee_types | studentfinance.housing_plans.fee_type_id | 14. Student Finance - Catalog & Rates |
studentfinance__fee_types | studentfinance.meal_plans.fee_type_id | 14. Student Finance - Catalog & Rates |
studentfinance__payment_plans | studentfinance.payment_plan_fees.plan_id | 15. Student Finance - Student Charges & Payments |
studentfinance__billing_methods | studentfinance.student_billing_transactions.billing_method_id | 14. Student Finance - Catalog & Rates |
studentfinance__fee_types | studentfinance.student_charges.fee_type_id | 14. Student Finance - Catalog & Rates |
Cross-Schema Relationship Summary
Foreign keys whose two ends live in different schemas — the seams where one subsystem depends on another.
| Parent | Child column | Child table |
|---|---|---|
records__students | core.proxy_access_audit_log.student_id | students |
records__students | core.proxy_invitations.student_id | students |
records__students | core.student_organization_membership.student_id | students |
records__students | core.student_proxy_access.student_id | students |
core__campuses | lms.assignment_rules.campus_id | campuses |
core__org_units | lms.assignment_rules.org_unit_id | org_units |
records__academic_terms | lms.course_shells.term_id | academic_terms |
core__people | lms.enrollment_links.person_id | people |
records__student_section_enrollments | lms.enrollment_links.source_enrollment_id | student_section_enrollments |
core__org_units | lms.instance_org_unit_mappings.org_unit_id | org_units |
records__academic_terms | lms.instance_term_mappings.term_id | academic_terms |
records__course_sections | lms.section_overrides.section_id | course_sections |
records__course_sections | lms.shell_sections.section_id | course_sections |
core__people | lms.user_mappings.person_id | people |
core__departments | records.advisors.department_id | departments |
core__people | records.advisors.person_id | people |
core__people | records.applicant_test_scores.person_id | people |
core__auth_users | records.applicant_test_scores.verified_by | auth_users |
core__people | records.applicants.person_id | people |
core__rooms | records.class_schedules.room_id | rooms |
core__campuses | records.cohorts.campus_id | campuses |
core__departments | records.course_patterns.department_id | departments |
core__campuses | records.course_sections.campus_id | campuses |
studentfinance__fee_types | records.course_sections.fee_type_id | fee_types |
core__people | records.course_substitutions.approved_by_person_id | people |
core__colleges_schools | records.degrees.college_id | colleges_schools |
core__departments | records.degrees.department_id | departments |
core__people | records.enrollment_status_history.changed_by_person_id | people |
core__people | records.graduation_applications.reviewed_by | people |
core__countries | records.institutions.country_id | countries |
core__state_provinces | records.institutions.state_province_id | state_provinces |
core__campuses | records.major_campus_offerings.campus_id | campuses |
core__departments | records.majors.department_id | departments |
core__campuses | records.minor_campus_offerings.campus_id | campuses |
core__departments | records.minors.department_id | departments |
core__people | records.person_superscore_records.person_id | people |
core__people | records.planned_courses.capacity_override_by | people |
core__people | records.planned_courses.impersonator_person_id | people |
core__people | records.planned_courses.planned_by_person_id | people |
core__people | records.planned_courses.prereq_override_by | people |
core__people | records.planned_courses.repeat_override_by | people |
core__people | records.planned_courses.sequence_override_by | people |
core__people | records.registration_transaction_notes.author_person_id | people |
core__campuses | records.registration_transactions.campus_id | campuses |
core__people | records.registration_transactions.initiated_by_person_id | people |
core__people | records.registration_transactions.overload_advisor_approved_by | people |
core__colleges_schools | records.requirement_blocks.college_id | colleges_schools |
core__departments | records.requirement_blocks.department_id | departments |
core__auth_users | records.requirement_waivers.approved_by_id | auth_users |
core__rooms | records.section_schedules.room_id | rooms |
core__people | records.student_academic_status_history.created_by | people |
core__people | records.student_admission_status_history.created_by | people |
core__campuses | records.student_campus_assignments.campus_id | campuses |
core__auth_users | records.student_course_substitutions.advisor_user_id | auth_users |
core__people | records.student_course_substitutions.final_approved_by_person_id | people |
core__auth_users | records.student_course_substitutions.registrar_user_id | auth_users |
core__auth_users | records.student_course_substitutions.requested_by_user_id | auth_users |
core__people | records.student_holds.cleared_by_person_id | people |
core__people | records.student_holds.created_by_person_id | people |
core__people | records.student_holds.impersonator_person_id | people |
core__auth_users | records.student_list_members.added_by_id | auth_users |
core__auth_users | records.student_list_shares.shared_by_id | auth_users |
core__auth_users | records.student_list_shares.shared_with_id | auth_users |
core__auth_users | records.student_lists.owner_id | auth_users |
core__people | records.students.person_id | people |
core__campuses | records.students.primary_campus_id | campuses |
core__departments | records.teachers.department_id | departments |
core__people | records.teachers.person_id | people |
core__campuses | records.term_campus_offerings.campus_id | campuses |
studentfinance__student_payments | records.transaction_payment_history.payment_uuid | student_payments |
core__auth_users | records.transaction_payment_history.recorded_by | auth_users |
core__people | records.transaction_step_history.transitioned_by_person_id | people |
core__people | records.transfer_credits.evaluated_by_person_id | people |
core__people | records.transfer_credits.voided_by | people |
core__org_units | records.workflow_instance_step_assignees.org_unit_id | org_units |
core__people | records.workflow_instance_step_assignees.person_id | people |
core__people | records.workflow_instance_steps.assigned_to_person_id | people |
core__people | records.workflow_instance_steps.completed_by_person_id | people |
core__people | records.workflow_instance_steps.entered_by_person_id | people |
core__people | records.workflow_instance_steps.impersonator_person_id | people |
core__people | records.workflow_instances.impersonator_person_id | people |
core__people | records.workflow_instances.initiated_by_person_id | people |
core__people | records.workflow_instances.subject_person_id | people |
core__sys_currencies | studentfinance.billing_bands.currency_id | sys_currencies |
core__campuses | studentfinance.billing_method_configs.campus_id | campuses |
records__cohorts | studentfinance.billing_method_configs.cohort_id | cohorts |
records__academic_terms | studentfinance.billing_method_configs.term_id | academic_terms |
records__course_sections | studentfinance.billing_transaction_items.section_id | course_sections |
records__students | studentfinance.colleague_ar_charges.student_id | students |
records__academic_terms | studentfinance.colleague_ar_charges.term_id | academic_terms |
core__campuses | studentfinance.course_fees.campus_id | campuses |
records__courses | studentfinance.course_fees.course_id | courses |
core__sys_currencies | studentfinance.course_fees.currency_id | sys_currencies |
records__academic_terms | studentfinance.course_fees.term_id | academic_terms |
core__sys_currencies | studentfinance.credit_rate_schedules.currency_id | sys_currencies |
records__academic_terms | studentfinance.fee_schedules.term_id | academic_terms |
core__campuses | studentfinance.housing_plans.campus_id | campuses |
core__sys_currencies | studentfinance.housing_plans.currency_id | sys_currencies |
records__academic_terms | studentfinance.housing_plans.term_id | academic_terms |
core__campuses | studentfinance.meal_plans.campus_id | campuses |
core__sys_currencies | studentfinance.meal_plans.currency_id | sys_currencies |
records__academic_terms | studentfinance.meal_plans.term_id | academic_terms |
core__campuses | studentfinance.payment_plan_eligibility.campus_id | campuses |
records__majors | studentfinance.payment_plan_eligibility.major_id | majors |
records__student_lists | studentfinance.payment_plan_eligibility.student_list_id | student_lists |
core__sys_currencies | studentfinance.payment_plan_fees.currency_id | sys_currencies |
records__academic_terms | studentfinance.payment_plan_terms.term_id | academic_terms |
core__campuses | studentfinance.program_fees.campus_id | campuses |
core__sys_currencies | studentfinance.program_fees.currency_id | sys_currencies |
records__majors | studentfinance.program_fees.major_id | majors |
records__academic_terms | studentfinance.program_fees.term_id | academic_terms |
records__academic_terms | studentfinance.refund_schedules.term_id | academic_terms |
core__sys_currencies | studentfinance.section_fee_overrides.currency_id | sys_currencies |
records__course_sections | studentfinance.section_fee_overrides.section_id | course_sections |
records__students | studentfinance.student_account_balance.student_id | students |
records__academic_terms | studentfinance.student_account_balance.term_id | academic_terms |
core__sys_currencies | studentfinance.student_billing_transactions.currency_id | sys_currencies |
records__students | studentfinance.student_billing_transactions.student_id | students |
records__academic_terms | studentfinance.student_billing_transactions.term_id | academic_terms |
records__students | studentfinance.student_charges.student_id | students |
records__academic_terms | studentfinance.student_charges.term_id | academic_terms |
records__course_sections | studentfinance.student_class_waivers.section_id | course_sections |
records__students | studentfinance.student_class_waivers.student_id | students |
records__academic_terms | studentfinance.student_class_waivers.term_id | academic_terms |
records__students | studentfinance.student_housing_assignments.student_id | students |
records__academic_terms | studentfinance.student_housing_assignments.term_id | academic_terms |
records__students | studentfinance.student_meal_plan_elections.student_id | students |
records__academic_terms | studentfinance.student_meal_plan_elections.term_id | academic_terms |
records__students | studentfinance.student_payment_plan_enrollments.student_id | students |
records__students | studentfinance.student_payments.student_id | students |
core__campuses | studentfinance.term_fee_categories.campus_id | campuses |
records__student_lists | studentfinance.term_fee_categories.student_list_id | student_lists |
core__sys_currencies | studentfinance.term_fees.currency_id | sys_currencies |
records__academic_terms | studentfinance.term_fees.term_id | academic_terms |
core__sys_currencies | studentfinance.upfront_payment_discounts.currency_id | sys_currencies |
records__academic_terms | studentfinance.upfront_payment_discounts.term_id | academic_terms |
Lookup Tables
117 lookup_* tables are referenced above but not drawn. All of them carry code, display_value, is_active, sort_order and the four audit columns; only four carry description, and only ten carry display_value_es / display_value_fr, so none of those three can be assumed.
core(41):lookup_academic_level,lookup_access_token_type,lookup_activity_type,lookup_address_type,lookup_communication_direction,lookup_communication_type,lookup_credential_type,lookup_document_type,lookup_education_level,lookup_email_type,lookup_essay_type,lookup_ethnicity,lookup_family_relationship,lookup_gender,lookup_housing_status,lookup_identifier_type,lookup_language,lookup_marital_status,lookup_name_type,lookup_org_admin_level,lookup_org_unit_type,lookup_organization_type,lookup_phone_type,lookup_photo_origin,lookup_photo_status,lookup_primary_language,lookup_proxy_permission_type,lookup_proxy_relationship_type,lookup_race,lookup_recognition_level,lookup_recommender_relationship,lookup_relationship,lookup_relationship_status,lookup_relationship_type,lookup_religion,lookup_room_feature,lookup_room_type,lookup_system_role,lookup_verification_status,lookup_veteran_status,lookup_visa_typerecords(70):lookup_academic_status,lookup_admission_status,lookup_adoption_status,lookup_advisor_role,lookup_advisor_type,lookup_automation_rule_type,lookup_block_type,lookup_bulletin_status,lookup_bulletin_type,lookup_class_standing,lookup_class_status,lookup_cohort_type,lookup_course_attribute,lookup_course_level,lookup_course_status,lookup_credit_source,lookup_credit_system,lookup_credit_type,lookup_degree_level,lookup_degree_progress_status,lookup_degree_status,lookup_delivery_mode,lookup_enrollment_intensity,lookup_enrollment_status,lookup_equivalence_type,lookup_graduation_honor,lookup_graduation_status,lookup_graduation_type,lookup_honors_type,lookup_initiator_role,lookup_institution_type,lookup_instruction_mode,lookup_instructional_method,lookup_instructor_role,lookup_ipeds_residency,lookup_ipeds_student_type,lookup_material_format,lookup_material_requirement,lookup_material_type,lookup_min_grade,lookup_non_course_req_type,lookup_oer_license,lookup_org_membership_role,lookup_plan_assignment_status,lookup_plan_status,lookup_planner_role,lookup_program_status,lookup_requirement_type,lookup_requisite_type,lookup_schedule_type,lookup_section_status,lookup_student_status,lookup_student_type,lookup_substitution_status,lookup_teacher_status,lookup_teaching_frequency,lookup_term_type,lookup_test_type,lookup_transaction_action,lookup_transaction_item_status,lookup_transaction_status,lookup_transaction_type,lookup_transfer_credit_status,lookup_validation_status,lookup_waitlist_status,lookup_waiver_type,lookup_workflow_condition_type,lookup_workflow_status,lookup_workflow_step_status,lookup_workflow_step_typestudentfinance(6):lookup_billing_transaction_status,lookup_charge_status,lookup_payment_method,lookup_payment_plan_status,lookup_payment_schedule_status,lookup_payment_status
Two of them also carry behavioral columns and are owned by their domain rather than the generic lookup layer: core.lookup_identifier_type and records.lookup_cohort_type. The generic endpoints read them but refuse every write with a 409 naming the owning endpoint.
See the Lookup Codes Reference for the type codes and their values.
Regenerating this diagram
The generator reads the live schema directly, so re-running it after a migration produces a revision that matches the database by construction. It refuses to run if any table is in no section, in two sections, or in a section but not in the database — so a new migration forces a placement decision instead of quietly dropping a table out of the diagram.
bash
python docs/generate_erd.py # rewrite this file from the live schema
python docs/generate_erd.py --check # exit 3 if it would change (for CI)
python docs/build_erd_page.py # build docs/public/er-diagram.htmlRead it as a single page at er-diagram.html, which renders each section independently so a malformed diagram cannot blank out its neighbours.
docs/er-split/ is the revision 7 render and is deliberately left frozen: its 18 pages inline Mermaid at roughly 3.6 MB each, and the single page above supersedes them.