SIS Database ER Diagrams (core + records + studentfinance + lms + payments + cafeteria)
Generated 2026-11-03 from the live PostgreSQL database (revision 10).
Schemas: core (86 entities + 41 lookup_*), records (143 entities + 71 lookup_*), studentfinance (38 entities + 6 lookup_*), lms (24 entities + 0 lookup_*), payments (8 entities + 0 lookup_*), cafeteria (16 entities + 0 lookup_*) — 315 entities drawn across 26 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 — 164 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 10 changes (migrations 301–313). Revision 9 covered 264–300 and 308; its changelog is in
er-diagram-9.
- The
cafeteriaschema is drawn, in two new sections, §24 and §25. The cafeteria point-of-sale service keeps its data in this database under its own schema, as the ACI portal does underpayments; nothing in SIS+ reads it. 309 creates it:accountsis the ID-card roster (linked tocore.peoplewhen the card number resolves to a person, with a display name of its own for department and vendor cards),account_type_rules,cashiers,registers(device tokens), the product catalog andregister_buttons,settings, and append-onlysaleswith theirsale_linesandsale_payments— a void is a new negative sale pointing at the original.charge_exportsrecords the nightly charge file;register_events,report_subscriptionsandimport_runsare logs and configuration. Only the configuration tables are row-audited.- 310, 311 —
salestrades its unique (register, seq) for a plain index, so a rebuilt register database cannot collide;sales.created_byrecords back-office voids andregisters.offline_alerted_atthe register monitor's state. 312 seeds report recipients (no schema change).- 313 —
charge_periods: a student card's balance counts the current period, proposed nightly fromrecords.academic_termsbycafeteria.sync_charge_periods()and editable in the cafeteria admin; prepaid and department cards keep the card's own window (account_type_rules.balance_window). Theaccount_balancesview reads both; views are not drawn.- 305 — email attachments (§22): an organizational ceiling (
email_attachment_limits) intersected with a policy per surface (email_attachment_policies), files stored once per message (email_message_attachments) or per template (email_template_attachments), and each template's own allowance (email_template_attachment_settings). The file tables opt out of row auditing, or every file would be copied intoaudit_log.- 306 —
transcript_issuances(§10) logs every official transcript produced: who (and the impersonator, if any), for which student, and when.- 307 —
enrollment_letter_deliveries(§12) records each email of a signed enrollment-verification letter, sent on approval or re-sent by hand.- 301 —
graduation_records.colleague_credential_id: an imported record is keyed on the Colleague credential it came from, so a double major's two credentials are two records instead of one that flipped between them.- Payments (§21) gains
aci_channel,payment_transaction_line,payment_type_lineand its ownschema_migrationsledger. These come from the ACI portal's migrations, not fromdatabase/migrations/.- No schema change: 302 (permission nodes that gate record tabs on their data), 303 (registrars manage campus assignments) and 304 (students' GPA What-If) only add permission nodes and grants.
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
varchar(10) colleague_credential_id
}
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__transcript_issuances {
int transcript_issuance_id PK
int student_id FK
int issued_by_auth_user_id FK
uuid impersonator_person_id FK
timestamptz issued_at
}
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__enrollment_letter_deliveries {
int enrollment_letter_delivery_id PK
int workflow_instance_id FK
varchar(254) recipient_email
varchar(20) recipient_kind
varchar(10) status
text error_text
uuid triggered_by_person_id FK
timestamptz sent_at
}
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_instances ||--o{ records__enrollment_letter_deliveries : "workflow_instance_id"
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__aci_channel {
int channel_id PK
varchar(50) code UK
varchar(200) display_name
varchar(100) aci_product_id
}
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
varchar(100) payer_first_name
varchar(100) payer_middle_name
varchar(100) payer_last_name
varchar(10) payer_suffix
varchar(100) payer_email
varchar(10) payer_phone
varchar(100) payer_address1
varchar(100) payer_address2
varchar(100) payer_city
varchar(2) payer_state
varchar(5) payer_postal
varchar(20) account_id
varchar(100) reference_value
varchar(30) notify_office
varchar(20) notify_status
text colleague_post_note
}
payments__payment_transaction_line {
bigint payment_transaction_line_id PK
bigint payment_transaction_id FK
int payment_type_line_id FK
varchar(20) posting_code
varchar(10) posting_kind
numeric(9_2) amount
}
payments__payment_type {
int payment_type_id PK
varchar(50) code UK
varchar(200) display_name
text description
bool requires_student_verification
bool is_active
int sort_order
int channel_id FK
varchar(30) notify_office
bool collects_account_id
varchar(100) reference_label
bool reference_required
}
payments__payment_type_line {
int payment_type_line_id PK
int payment_type_id FK
varchar(50) code
varchar(200) display_name
varchar(20) posting_code
varchar(10) posting_kind
int sort_order
}
payments__schema_migrations {
text filename PK
timestamptz applied_at
}
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"
payments__payment_transaction ||--o{ payments__payment_transaction_line : "payment_transaction_id"
payments__payment_type_line ||--o{ payments__payment_transaction_line : "payment_type_line_id"
payments__aci_channel ||--o{ payments__payment_type : "channel_id"
payments__payment_type ||--o{ payments__payment_type_line : "payment_type_id"22. Core - Staff Email (migrations 268, 305)
mermaid
erDiagram
core__email_attachment_limits {
bool singleton PK
text[] allowed_extensions
int max_file_mb
int max_files
int max_total_mb
}
core__email_attachment_policies {
varchar(40) surface_code PK
bool is_enabled
text[] allowed_extensions
int max_file_mb
int max_files
int max_total_mb
}
core__email_message_attachments {
int email_message_attachment_id PK
int email_message_id FK
varchar(255) filename
varchar(150) content_type
int size_bytes
char sha256
bytea content
int sort_order
}
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__email_template_attachment_settings {
int template_id PK
bool is_enabled
int max_files
int max_file_mb
int max_total_mb
}
core__email_template_attachments {
int email_template_attachment_id PK
int template_id FK
varchar(255) filename
varchar(150) content_type
int size_bytes
char sha256
bytea content
int sort_order
}
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_attachments : "email_message_id"
core__email_messages ||--o{ core__email_message_recipients : "email_message_id"
core__sender_addresses ||--o{ core__sender_address_grants : "sender_address_id"23. Records - Student Notes & Notifications (migration 308)
mermaid
erDiagram
core__notifications {
bigint notification_id PK
int recipient_user_id FK
varchar(50) kind
varchar(300) title
varchar(500) link_path
varchar(50) source_type
bigint source_id
uuid actor_person_id
timestamp read_at
jsonb params
}
records__student_note_attachments {
bigint student_note_attachment_id PK
bigint student_note_id FK
varchar(255) filename
varchar(150) content_type
int size_bytes
char sha256
bytea content
int sort_order
}
records__student_note_private_access_log {
bigint student_note_private_access_log_id PK
int student_id FK
int viewer_user_id FK
uuid viewer_person_id
varchar(50) viewer_role_code
text reason
timestamp accessed_at
varchar(45) ip_address
text user_agent
}
records__student_note_reads {
bigint student_note_id PK
int auth_user_id PK
timestamp read_at
}
records__student_note_viewer_roles {
bigint student_note_id PK
int role_id PK
uuid added_by_person_id FK
timestamp added_at
}
records__student_notes {
bigint student_note_id PK
int student_id FK
varchar(200) subject
text body_html
int note_category_id FK
int term_id FK
bool is_private
uuid author_person_id FK
int author_user_id FK
varchar(50) author_role_code
uuid impersonator_person_id
timestamp shared_at
timestamp retracted_at
uuid retracted_by_person_id FK
}
records__student_notes ||--o{ records__student_note_attachments : "student_note_id"
records__student_notes ||--o{ records__student_note_reads : "student_note_id"
records__student_notes ||--o{ records__student_note_viewer_roles : "student_note_id"24. Cafeteria - Accounts, Registers & Catalog (migrations 309-313)
mermaid
erDiagram
cafeteria__account_type_rules {
int account_type_rule_id PK
char account_type
varchar(4) min_board
varchar(2) plan_code
varchar(10) charge_mode
varchar(20) limit_source
bool taxable
bool is_department
varchar(100) label
bool is_active
varchar(6) balance_window
}
cafeteria__accounts {
bigint account_id PK
char account_number UK
char account_type
char colleague_id
uuid person_id FK
varchar(200) display_name
varchar(3) lost_card_code
varchar(20) card_code UK
date cafe_start_date
date cafe_end_date
bool min_board
numeric(10_2) credit_limit
numeric(10_2) prepaid_amount
varchar(18) gl_no
varchar(5) team_code
varchar(20) source
timestamptz imported_at
bool is_active
}
cafeteria__cashiers {
int cashier_id PK
bigint account_id FK
varchar(200) display_name
bool is_active
}
cafeteria__charge_periods {
int charge_period_id PK
varchar(100) name
date start_date UK
int term_id FK
varchar(10) source
bool is_active
}
cafeteria__import_runs {
bigint import_run_id PK
varchar(40) job
timestamptz started_at
timestamptz finished_at
int rows_read
int rows_inserted
int rows_updated
int rows_deactivated
varchar(20) status
text error
}
cafeteria__product_categories {
int category_id PK
varchar(4) code UK
varchar(100) description
bool taxable_default
int sort_order
bool is_active
}
cafeteria__products {
int product_id PK
varchar(40) barcode UK
varchar(100) name
int category_id FK
bool taxable
numeric(10_2) price
int case_quantity
numeric(10_2) case_cost
bool is_active
}
cafeteria__register_buttons {
int button_id PK
int register_id FK
int position
varchar(10) kind
varchar(60) label
int category_id FK
bool taxable
numeric(10_2) price
bool is_active
}
cafeteria__register_events {
bigint register_event_id PK
int register_id FK
timestamptz occurred_at
varchar(20) event_type
jsonb detail
}
cafeteria__registers {
int register_id PK
varchar(4) register_code UK
varchar(100) name
char charge_source
bool is_test
bytea device_token_hash
timestamptz device_token_issued_at
timestamptz last_heartbeat_at
int heartbeat_cashier_id FK
int heartbeat_unsynced_count
varchar(40) agent_version
bool is_active
timestamptz offline_alerted_at
}
cafeteria__report_subscriptions {
int report_subscription_id PK
varchar(40) report_code
varchar(200) recipient_email
bool is_active
}
cafeteria__settings {
int setting_id PK
varchar(60) setting_key UK
text value
varchar(300) description
}
cafeteria__accounts ||--o{ cafeteria__cashiers : "account_id"
cafeteria__product_categories ||--o{ cafeteria__products : "category_id"
cafeteria__product_categories ||--o{ cafeteria__register_buttons : "category_id"
cafeteria__registers ||--o{ cafeteria__register_buttons : "register_id"
cafeteria__registers ||--o{ cafeteria__register_events : "register_id"
cafeteria__cashiers ||--o{ cafeteria__registers : "heartbeat_cashier_id"25. Cafeteria - Sales & Charge Exports (migrations 309-313)
mermaid
erDiagram
cafeteria__charge_exports {
int charge_export_id PK
date export_date
varchar(100) file_name
int line_count
numeric(12_2) total_amount
text content
varchar(20) status
text error
timestamptz generated_at
timestamptz delivered_at
}
cafeteria__sale_lines {
bigint sale_line_id PK
uuid sale_id FK
int line_no
varchar(10) line_type
int product_id FK
varchar(100) description
varchar(4) category_code
int quantity
numeric(10_2) unit_price
numeric(10_2) amount
bool taxable
}
cafeteria__sale_payments {
bigint sale_payment_id PK
uuid sale_id FK
int line_no
varchar(10) payment_type
bigint account_id FK
varchar(40) card_code_scanned
numeric(10_2) amount
bool is_plan
char charge_source
int charge_export_id FK
}
cafeteria__sales {
uuid sale_id PK
int register_id FK
bigint register_seq
int cashier_id FK
char aisle
timestamptz sold_at
numeric(10_2) subtotal
numeric(10_2) tax
numeric(10_2) total
uuid voids_sale_id FK
bool was_offline
timestamptz received_at
}
cafeteria__sales ||--o{ cafeteria__sale_lines : "sale_id"
cafeteria__charge_exports ||--o{ cafeteria__sale_payments : "charge_export_id"
cafeteria__sales ||--o{ cafeteria__sale_payments : "sale_id"
cafeteria__sales ||--o{ cafeteria__sales : "parent"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 165 live.
| Parent | Child column | Parent section |
|---|---|---|
cafeteria__products | cafeteria.sale_lines.product_id | 24. Cafeteria - Accounts, Registers & Catalog (migrations 309-313) |
cafeteria__accounts | cafeteria.sale_payments.account_id | 24. Cafeteria - Accounts, Registers & Catalog (migrations 309-313) |
cafeteria__cashiers | cafeteria.sales.cashier_id | 24. Cafeteria - Accounts, Registers & Catalog (migrations 309-313) |
cafeteria__registers | cafeteria.sales.register_id | 24. Cafeteria - Accounts, Registers & Catalog (migrations 309-313) |
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__email_templates | core.email_template_attachment_settings.template_id | 17. Core - System & Reference |
core__email_templates | core.email_template_attachments.template_id | 17. Core - System & Reference |
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__auth_users | core.notifications.recipient_user_id | 3. Core - Auth & Authorization |
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__students | records.student_note_private_access_log.student_id | 8. Records - Roles & Student Data |
records__students | records.student_notes.student_id | 8. Records - Roles & Student Data |
records__academic_terms | records.student_notes.term_id | 5. Records - Academic Calendar |
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__students | records.transcript_issuances.student_id | 8. Records - Roles & Student Data |
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 |
|---|---|---|
core__people | cafeteria.accounts.person_id | people |
records__academic_terms | cafeteria.charge_periods.term_id | academic_terms |
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_letter_deliveries.triggered_by_person_id | people |
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__auth_users | records.student_note_private_access_log.viewer_user_id | auth_users |
core__auth_users | records.student_note_reads.auth_user_id | auth_users |
core__people | records.student_note_viewer_roles.added_by_person_id | people |
core__roles | records.student_note_viewer_roles.role_id | roles |
core__people | records.student_notes.author_person_id | people |
core__auth_users | records.student_notes.author_user_id | auth_users |
core__people | records.student_notes.retracted_by_person_id | people |
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.transcript_issuances.impersonator_person_id | people |
core__auth_users | records.transcript_issuances.issued_by_auth_user_id | auth_users |
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
118 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(71):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_note_category,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.