Database Table Reference

Complete reference of all database tables in the SDASIS Student Information System, organized by domain.

For lookup tables (lookup_*), see Lookup Codes Reference.

---

Core

Table	Purpose	Key Relationships
countries	Country reference data (ISO codes, names, visa requirements)	Referenced by people, person_addresses, campuses
sys_currencies	Currency codes, symbols, and decimal precision	Referenced by billing tables
sys_institution_settings	Institution-wide configuration key-value pairs	Standalone settings store
ui_translations	Multi-language UI translation strings	Standalone
email_templates	Email message templates (code, subject, HTML body)	Used by notification services

---

Academic

Calendar & Terms

Table	Purpose	Key Relationships
academic_years	Academic calendar years with start/end dates	Parent of academic_terms
academic_terms	Terms (Fall, Spring, etc.) within academic years	FK: academic_year_id -> academic_years, term_type_id -> lookup_term_type
sub_terms	Mini-terms or sub-sessions within a term	FK: term_id -> academic_terms
term_campus_offerings	Which campuses offer which terms	FK: term_id, campus_id

Courses & Sections

Table	Purpose	Key Relationships
courses	Course master catalog (code, title, credits, level)	FK: status_id -> lookup_course_status, course_level_id -> lookup_course_level
course_attributes	Attribute flags on courses (e.g., writing-intensive)	FK: course_id, attribute_type_id -> lookup_course_attribute
course_requisites	Prerequisites, corequisites, antirequisites entered by hand. The importer never writes it; Colleague's rules are in course_requisite_links	FK: course_id, requisite_course_id, requisite_type_id -> lookup_requisite_type
course_requisite_links	Every Colleague requisite rule a course carries (migration 285): timing P = earlier term (prerequisite), C = same term (corequisite), E = either; enforcement RQ = required, RM = recommended (not enforced). block_id is the rule's requirement-block tree. Rebuilt by migrate-requirements --steps=course-prereqs from CRS_REQS, falling back to the legacy CRS_PREREQS only where a course has no newer prior rule	FK: course_id -> courses, block_id -> requirement_blocks (both ON DELETE CASCADE)
course_equivalents	Equivalency mappings between courses	FK: course_id, equivalent_course_id, equivalence_type_id -> lookup_equivalence_type
course_sections	Section instances of courses per term	FK: course_id, term_id, campus_id, class_id, status_id, instruction_mode_id -> lookup_instruction_mode (derived modality), instructional_method_id -> lookup_instructional_method (declared pedagogy, migration 263). colleague_section_id is Colleague's COURSE_SECTIONS_ID (migration 293): the import matches on it, follows renames in place and deletes stamped sections Colleague no longer has; NULL for sections planned in the SIS
classes	Grouping of sections (cross-listed, combined)	FK: term_id, status_id -> lookup_class_status
class_sections	Junction: classes to sections	FK: class_id, section_id
section_schedules	Dead. Meeting times live on the section's schedule container -- course_sections.class_id -> class_schedules. Nothing reads this table; migrate-daily --redo=classes truncates it	FK: section_id, room_id
section_instructors	Instructors assigned to teach sections	FK: section_id, teacher_id, instructor_role_id -> lookup_instructor_role. source is SIS (the default, screen-entered) or ODS (imported); the import deletes only ODS rows Colleague stopped listing (migration 293)
course_substitutions	Catalog/program-level course substitutions	FK: original_course_id, substitute_course_id, bulletin_id, major_id
student_course_substitutions	Per-student course substitutions for degree audit	FK: student_id, original_course_id, substitute_course_id, block_id

Schedule Templates

Table	Purpose	Key Relationships
schedule_templates	Reusable schedule patterns (MWF 50min, TR 75min)	Parent of schedule_template_entries
schedule_template_entries	Time slots within a schedule template	FK: template_id -> schedule_templates
class_schedules	Class meeting schedule (template-based or custom); the live source of meeting times for a section, via its container. One row per (class, day, start) -- uq_class_schedules_class_day_start, migration 213	FK: class_id, template_id, room_id, schedule_type_id -> lookup_schedule_type

Bulletins & Catalogs

Table	Purpose	Key Relationships
bulletins	Academic bulletins/catalogs with effective dates	FK: bulletin_type_id, status_id
bulletin_academic_years	Junction: bulletins to academic years	FK: bulletin_id, academic_year_id
course_bulletins	Courses listed in a bulletin	FK: course_id, bulletin_id, teaching_frequency_id
course_bulletin_periods	Term periods when bulletin courses are offered	FK: course_bulletin_id

Degrees, Majors & Minors

Table	Purpose	Key Relationships
degrees	Degree programs (BA, BS, MA, etc.)	FK: degree_level_id, department_id, college_id, bulletin_id, status_id
degree_blocks	Junction: degrees to requirement blocks	FK: degree_id, block_id
degree_approved_minors	Minors approved for a degree	FK: degree_id, minor_id
degree_default_advisors	Default advisors assigned to degrees	FK: degree_id, advisor_id
majors	Major programs within degrees	FK: degree_id, department_id, bulletin_id, status_id
major_blocks	Junction: majors to requirement blocks	FK: major_id, block_id
major_campus_offerings	Campuses where a major is offered	FK: major_id, campus_id
major_default_advisors	Default advisors for majors	FK: major_id, advisor_id
minors	Minor programs	FK: department_id, bulletin_id, status_id
minor_blocks	Junction: minors to requirement blocks	FK: minor_id, block_id
minor_campus_offerings	Campuses where a minor is offered	FK: minor_id, campus_id
minor_default_advisors	Default advisors for minors	FK: minor_id, advisor_id

Requirements

Table	Purpose	Key Relationships
requirement_blocks	Requirement block definitions (GenEd, Major Core, etc.)	FK: block_type_id -> lookup_block_type, bulletin_id, parent_block_id, min_grade_id
requirement_groups	Groups of requirements within a block	FK: block_id
requirement_group_rules	Logic rules for requirement groups	FK: block_id
requirement_items	Individual course/non-course requirements	FK: block_id, group_id, requirement_type_id, course_id, course_pattern_id, min_grade_id
requirement_item_courses	Junction: requirement items to specific courses	FK: item_id, course_id
course_patterns	Pattern-based course matching (e.g., "all MATH 300+")	FK: bulletin_id, department_id
course_pattern_exclusions	Courses excluded from a pattern	FK: pattern_id, course_id

Course Materials

Table	Purpose	Key Relationships
materials	Textbook/material master records (ISBN, title, author)	FK: material_type_id, oer_license_id
material_formats	Available formats for a material (print, digital)	FK: material_id, format_type_id
materials	Materials assigned to courses	FK: course_id, material_id, requirement_id, adoption_status_id
section_material_assignments	Materials assigned to specific sections (overrides)	FK: section_id, material_id, requirement_id, adoption_status_id

---

Organization

Table	Purpose	Key Relationships
campuses	Physical and online campus locations	FK: country_id -> countries
buildings	Campus buildings	FK: campus_id -> campuses
rooms	Classrooms and meeting spaces with capacity	FK: building_id -> buildings, room_type_id -> lookup_room_type
room_features	Features available in rooms (projector, whiteboard)	FK: room_id, feature_id -> lookup_room_feature
colleges_schools	College/School organizational units	Standalone
departments	Academic and administrative departments	FK: parent_department_id (self-referential hierarchy)
department_college_mapping	Junction: departments to colleges	FK: department_id, college_id
unit_course_codes	Maps course code prefixes to departments/colleges	FK: department_id, college_id

---

People

Table	Purpose	Key Relationships
people	Master person record (DOB, demographics, FERPA)	FK: gender_id, ethnicity_id, religion_id, country_of_birth_id, citizenship_country_id, marital_status_id, veteran_status_id, primary_language_id
person_names	Name variants (legal, preferred, maiden, etc.)	FK: person_id, name_type_id -> lookup_name_type. source is SIS or ODS (migration 298); a person can hold a current PREFERRED row beside the current LEGAL_PRIMARY, so join on a name type or use core.person_display_name()
person_addresses	Mailing, permanent, campus addresses	FK: person_id, address_type_id -> lookup_address_type
person_phones	Phone numbers by type	FK: person_id, phone_type_id -> lookup_phone_type
person_emails	Email addresses by type	FK: person_id, email_type_id -> lookup_email_type
person_identifiers	Government IDs, SSN, passport numbers	FK: person_id, identifier_type_id -> lookup_identifier_type
person_races	Multi-race tracking per person	FK: person_id, race_id -> lookup_race
person_visa_status	Immigration/visa status tracking: status, program and entry dates, sponsor and financial support, work authorization, and the US-only sevis_status / form_type (migration 281)	FK: person_id, visa_type_id -> lookup_visa_type
person_citizenships	Citizenships beyond the primary people.citizenship_country_id, one row per country (migration 281). Any citizenship of the HOME_COUNTRY setting makes a person domestic	FK: person_id, country_id -> countries
person_name_display_config	Name display preferences per document type	FK: person_id, document_type_id, name_type_to_use_id
person_name_audit_log	Audit trail for name changes	FK: person_name_id, person_id
person_test_scores	Standardized test scores (SAT, ACT, etc.)	FK: person_id, test_type_id
person_activities	Extracurricular activities and organizations	FK: person_id, activity_type_id -> lookup_activity_type
person_essays	Application essays	FK: person_id, application_id, essay_type_id
person_recommendations	Letters of recommendation	FK: person_id, application_id, relationship_id -> lookup_recommender_relationship
person_honors	Awards and honors received	FK: person_id, recognition_level_id -> lookup_recognition_level
person_family_members	Family member information	FK: person_id, relationship_id -> lookup_family_relationship, education_level_id
person_photos	Uploaded person photos with approval workflow	FK: person_id, approval_status_id -> lookup_photo_status
person_merge_log	Audit log when two person records are merged	FK: primary_person_id, secondary_person_id, merged_by_user_id

---

Roles

Student Role

Table	Purpose	Key Relationships
students	Student role records (enrollment status, campus, IPEDS)	FK: person_id, student_status_id, initial_term_id, primary_campus_id, ipeds_residency_id, ipeds_student_type_id. Cohort membership moved to student_cohorts in migration 181
student_admission_status_history	Admission status change history	FK: student_id, status_id -> lookup_admission_status
student_academic_status_history	Academic status change history (good standing, probation)	FK: student_id, status_id -> lookup_academic_status, major_id
student_campus_assignments	Campuses a student is allowed to register at	FK: student_id, campus_id
student_majors	Junction: students to declared majors	FK: student_id, major_id, status_id
student_minors	Junction: students to declared minors	FK: student_id, minor_id
probation_alert_log	Tracks probation alerts sent to students	FK: student_id, major_id

Other Roles

Table	Purpose	Key Relationships
teachers	Teacher role records. display_first_name / display_last_name hold Colleague's faculty listing name (migration 298), which records.instructor_display_name() shows before the person's own name	FK: person_id, status_id -> lookup_teacher_status
advisors	Advisor role records	FK: person_id, advisor_type_id, advisor_role_id
applicants	Applicant role records (linked to applications)	FK: person_id, application_term_id, program_id, status_id -> lookup_applicant_status
cohorts	Student groups of an institution-defined type. Colleague-sourced rows carry source = 'ODS' and are refreshed nightly; the importer inserts but never reclassifies them	FK: cohort_type_id, academic_year_id, major_id, start_term_id, expected_graduation_term_id. start_date/end_date apply only when the type has uses_dates
lookup_cohort_type	Cohort kinds, with per-type rules (uses_dates, allow_multiple, requires_major, unique_per_level, requires_campus, requires_start_term, supports_auto_sync, allows_bulk_assign)	Extended lookup, owned by the roles domain
student_cohorts	Dated cohort membership, replacing students.cohort_id	FK: student_id, cohort_id, academic_level_id. An open membership has end_date IS NULL. Membership is concurrent -- a student holds several at once. source = 'ODS' marks the importer's rows, which is what scopes its delete pass; created_by IS NULL does not mean migration-owned here
student_lists	Custom student lists (advising, reporting)	Standalone with student membership
class_standing_thresholds	Credit thresholds for class standing (FR, SO, JR, SR)	FK: standing_lookup_id -> lookup_class_standing

---

Registration

Table	Purpose	Key Relationships
student_term_enrollments	Student enrollment record per term	FK: student_id, term_id, enrollment_status_id
student_section_enrollments	Student enrollment in specific course sections. midterm_grade_source (SIS/ODS, migration 292) says who wrote the midterm; the import never overwrites SIS. incomplete_deadline / incomplete_default_grade_id / incomplete_workflow_instance_id (migration 295) hold an approved Incomplete request's terms	FK: student_id, section_id, enrollment_status_id, grade_id, midterm_grade_id
enrollment_status_history	Audit trail for enrollment status changes	FK: enrollment_id, old_status_id, new_status_id
registration_transactions	Multi-step registration workflow instances	FK: student_id, term_id, campus_id, transaction_type_id, current_step_id, status_id, initiator_role_id
transaction_enrollment_items	Sections being added/dropped in a transaction	FK: transaction_id, section_id, action_type_id, item_status_id
transaction_step_history	Step transition audit for transactions	FK: transaction_id, from_step_id, to_step_id
hold_types	Registration hold type definitions	Standalone
student_holds	Active holds placed on students	FK: student_id, hold_type_id, term_id, created_by_person_id
hold_evaluation_log	Audit log for automated hold evaluations	FK: hold_type_id
planned_courses	Courses planned for future registration (advising)	FK: student_id, section_id, requirement_item_id, planned_by_person_id, status_id
section_waitlists	Waitlist entries for full sections	FK: section_id, student_id, status_id -> lookup_waitlist_status

---

Degree Audit

Table	Purpose	Key Relationships
student_degree_progress	Overall degree completion tracking per student	FK: student_id, degree_id, catalog_year_id
student_requirement_fulfillment	Per-block fulfillment status within degree progress	FK: progress_id, block_id
requirement_waivers	Waivers or substitutions for requirements	FK: student_id, block_id, item_id, waiver_type_id -> lookup_waiver_type, approved_by_id
transfer_credits	Transfer credit evaluations from other institutions. Status PRE_APPROVED (migration 295) is a course approved before it is taken: it counts nowhere and is left off the transcript	FK: student_id, equivalent_course_id, status_id -> lookup_transfer_credit_status, credit_type_id
graduation_applications	Student applications to graduate	FK: student_id, degree_id, graduation_date_id, expected_graduation_term_id
graduation_dates	Ceremony dates with application deadlines	FK: term_id
graduation_records	Conferred degree records	FK: student_id, degree_id, graduation_date_id, graduation_type_id, honors_id
graduation_record_honors	Extra honors on a conferred degree (language, music, leadership), one row per honor; entered manually, never imported	FK: graduation_record_id, graduation_honor_id -> lookup_graduation_honor
graduation_application_status_history	Status change audit for graduation applications	FK: application_id

---

Grading

Table	Purpose	Key Relationships
grading_systems	Grading scale definitions (e.g., Standard 4.0)	Parent of grading_system_grades
grading_system_grades	Individual grade definitions within a system	FK: grading_system_id

---

Billing

Table	Purpose	Key Relationships
billing_settings	Billing system configuration	Standalone
billing_methods	Billing calculation methods (per-credit, flat, banded)	Parent of billing_method_configs
billing_method_configs	Configuration per billing method	FK: method_id
credit_rate_schedules	Per-credit billing rates by term/residency/level	FK: term_id, method_id, residency_id, degree_level_id
billing_bands	Banded billing tiers (min/max credits -> flat amount)	FK: method_id, term_id, residency_id
fee_types	Fee classifications (technology, lab, activity)	Standalone
term_fees	Fees charged for a specific term	FK: term_id, fee_type_id, residency_id, degree_level_id
term_fee_bands	Banded term fee tiers	FK: term_fee_id
term_fee_categories	Custom fee groupings per term	FK: term_id
term_fee_category_bands	Banded custom fee tiers	FK: category_id
payment_plans	Payment plan templates (installment count, down payment %)	Standalone template
payment_plan_terms	Terms applicable to each payment plan	FK: plan_id, term_id
payment_plan_eligibility	Eligibility criteria (residency, credits)	FK: plan_id, residency_id, student_type_id
payment_plan_fees	Fees within payment plans	FK: plan_id, fee_type_id
payment_plan_schedules	Installment due dates	FK: plan_id
student_billing_transactions	Student billing records (charges, payments)	FK: student_id, term_id, status_id
student_payment_plan_enrollments	Student enrollment in payment plans	FK: student_id, term_id, plan_id, status_id

---

Admissions

Table	Purpose	Key Relationships
admission_applications	Application records with status tracking	FK: applicant_id, form_template_id, status_id, decision_id
application_checklist_items	Required documents and tasks per application	FK: application_id, status_id -> lookup_checklist_status
application_documents	Uploaded application documents	FK: application_id, document_type_id
application_reviews	Faculty/staff reviews of applications	FK: application_id, reviewed_by_person_id, recommendation_id
recruiter_assignments	Recruiter-to-applicant assignments	FK: applicant_id, recruiter_id, term_id
student_inquiries	Prospect inquiries and interest tracking	FK: prospect_person_id, program_id, source_id

---

Financial Aid

Table	Purpose	Key Relationships
award_types	Financial aid award definitions (scholarships, grants, loans)	Standalone template
awards	Individual student awards with lifecycle status	FK: person_id, term_id, award_type_id
fafsa_data	FAFSA submission data (EFC/SAI, Pell eligibility)	FK: person_id, application_id
financial_estimates	Cost of attendance estimates with aid projections	FK: person_id, term_id, degree_id, fafsa_data_id
estimate_cost_items	Itemized cost components in an estimate	FK: estimate_id
estimate_awards	Awards included in a financial estimate	FK: estimate_id, award_id

---

Form Builder

Table	Purpose	Key Relationships
form_templates	Dynamic form definitions (application forms)	FK: status_id -> lookup_form_template_status
form_sections	Logical sections within forms	FK: template_id, condition_field_id
form_fields	Individual form fields with type and validation	FK: section_id, field_type_id -> lookup_form_field_type
program_form_templates	Links forms to academic programs	FK: template_id, program_id
form_responses	Submitted form data	FK: template_id, person_id, application_id

---

Authorization

Table	Purpose	Key Relationships
roles	System role definitions (admin, registrar, faculty)	Standalone
permission_nodes	Permission tree hierarchy (menu items + actions)	FK: parent_node_id (self-referential)
role_permissions	Permissions assigned to roles	FK: role_id, node_id
user_roles	Roles assigned to users	FK: user_id, role_id
role_permissions	Direct permissions assigned to users (overrides)	FK: user_id, node_id
institution_domains	Allowed email domains for authentication	Standalone
dashboard_tiles	Dashboard widget configuration per role	FK: role_id
user_impersonation_sessions	Log of admin impersonation sessions	FK: admin_user_id, impersonated_user_id
audit_log	General audit log (table/record/action/changes)	Standalone

---

Authentication

Table	Purpose	Key Relationships
auth_users	User accounts with credentials	FK: person_id -> people
application_access_tokens	API access tokens (integrations)	FK: user_id, token_type_id -> lookup_access_token_type

---

Workflow

Table	Purpose	Key Relationships
registration_workflows	Registration workflow definitions	Standalone
registration_workflow_steps	Steps within a workflow (search, cart, pay, confirm)	FK: workflow_id, step_type_id -> lookup_workflow_step_type
workflow_step_transitions	Valid transitions between steps	FK: from_step_id, to_step_id
workflow_automation_rules	Rules for automatic step advancement	FK: rule_type_id -> lookup_automation_rule_type

---

Proxy Access

Table	Purpose	Key Relationships
student_proxy_access	Parent/guardian proxy access grants	FK: student_id, proxy_person_id, relationship_id -> lookup_proxy_relationship_type
proxy_invitations	Pending proxy access invitations	FK: student_id, relationship_id
proxy_access_permissions	Specific permissions granted to proxy users	FK: proxy_access_id

---

Test Scores

Table	Purpose	Key Relationships
standardized_test_types	Test type definitions (SAT, ACT, GRE, etc.)	Standalone
institution_test_score_policies	Institution test score policies	Standalone

---

LMS Integration

The lms schema (migration 199). An LMS type is data, not code: a connector type plus versioned operation definitions describes how to call a given LMS.

Connector definitions

Table	Purpose	Key Relationships
connector_types	One kind of LMS: auth strategy, pagination style, default rate limits. Seeded with CANVAS, MOODLE, BRIGHTSPACE	Parent of connector_operations; referenced by instances
connector_operations	One named operation on a type (verify, create_course, enroll_user, post_grade, ...)	FK: connector_type_id
operation_versions	Versioned JSONB definition of an operation - URL, headers, body template, response and error mapping. A change adds a version rather than overwriting	FK: operation_id

Instances, credentials and mappings

Table	Purpose	Key Relationships
instances	One LMS tenant. Carries dry_run, match_strategy, drop_behavior, create_missing_users, grade_passback_enabled, and shell name/code templates	FK: connector_type_id
instance_credentials	Write-only secrets, AES-sealed with a rotating keyring. is_active is partial-unique; activating one retires the incumbent in the same transaction	FK: instance_id, rotated_from_credential_id
instance_access_tokens	Cached OAuth tokens, encrypted at rest	FK: instance_id
instance_term_mappings	SIS term to the LMS's term or session id. An unmapped term never provisions	FK: instance_id, term_id
instance_role_mappings	SIS role to LMS enrollment role	FK: instance_id
instance_org_unit_mappings	SIS org unit to the LMS account or org unit a shell is created under	FK: instance_id, org_unit_id
assignment_rules	Which sections route to which instance, by org unit and/or campus, by priority (lowest wins)	FK: instance_id, org_unit_id, campus_id
sync_policy	Global, not per instance: provisioning and enrollment triggers and lead times, conclude policy, and what each kind of drift should do	Single row

Shells, enrollments and sync

Table	Purpose	Key Relationships
course_shells	The LMS course created for one or more sections. sis_course_id (migration 251) records the id actually sent	FK: instance_id
shell_sections	Junction: shells to sections. Several sections share one shell when cross-listed	FK: shell_id, section_id
section_overrides	Pins one section to one instance regardless of the assignment rules	FK: section_id, instance_id
enrollment_links	An SIS enrollment as reflected in the LMS	FK: shell_id
user_mappings	An SIS person to their LMS user	FK: instance_id, person_id
sync_jobs	The work queue. Worked with SKIP LOCKED; the dead letter is a status, not a separate table	FK: instance_id
sync_runs	One pass of a scanner or worker, with stats JSONB	FK: instance_id
drift_findings	Differences between what the SIS believes and what the LMS reports: missing enrollment, extra enrollment, missing shell	FK: instance_id
integration_log	Every rendered request and its outcome	FK: instance_id
export_runs	One SIS CSV export. One active run per instance, enforced by the database	FK: instance_id
export_files	The files a run produced	FK: export_run_id
export_watermarks	Per-instance delta position for exports	FK: instance_id
passback_watermarks	Per-instance position in records.grade_change_log. The first scan only initialises it, so enabling passback never replays history	FK: instance_id

The legacy LMS tables are gone

Migration 202 dropped records.lms_platforms, lms_platform_urls, lms_platform_campuses, lms_credentials, lms_user_tokens, lms_sync_log and lms_enrollment_mapping, plus four lookup_lms_* tables. The old subsystem stored API secrets in plaintext despite a migration comment claiming otherwise. The names are listed here so nobody who remembers them reintroduces them.

---

Payments (ACI portal)

The payments schema (migration 153). Storage for the ACI TRANSACT CoBrand+ hosted card-payment portal, which has no database of its own. It gets its own schema rather than joining studentfinance because a different service owns its lifecycle.

Table	Purpose	Key Relationships
payment_type	What can be paid for. Carries aci_product_id and requires_student_verification	Referenced by sessions and transactions
payment_session	One visitor's payment attempt: token, matched person, client IP, status, expiry	FK: payment_type_id
payment_transaction	The result ACI posts back server to server: amount, result code, confirmation, card last four, fees, mismatch flag, raw postback, and Colleague posting status	FK: payment_session_id, payment_type_id
verification_attempt	Rate limiting and audit for student-identity verification attempts, by client IP	Standalone
payment_type_line	The lines a payment type is split into, each with its own posting code and kind	FK: payment_type_id
payment_transaction_line	How a completed payment's amount was split across those lines, for posting	FK: payment_transaction_id, payment_type_line_id
aci_channel	ACI channels (code, display name, aci_product_id)	Standalone
schema_migrations	The portal's own migration ledger; these four tables come from the portal's migrations, not database/migrations/	Standalone

payments is deliberately outside the audit trigger

core.apply_audit_config() only onboards tables in a fixed schema list, and payments is not on it - so no trg_audit_row is attached and the portal's writes need no actor context. If payments is ever added to that list, the portal must start setting app.actor_source = 'PUBLIC_FORM' on its connections or every insert will be rejected by the strict actor check in core.audit_row_change().

---

Cafeteria (point of sale)

The cafeteria schema (migrations 309-313). Storage for the cafeteria point-of-sale service, which replaced a SQL Server database the registers wrote to directly. Like payments, a separate service owns it and nothing in SIS+ reads it; people stay in core.

Table	Purpose	Key Relationships
accounts	One row per ID card, keyed by the 9-digit card number (type prefix + 7-digit Colleague id). Imported nightly from Colleague; carries a display name of its own for department and vendor cards	FK: person_id (when the card resolves to a person)
account_type_rules	What a card type may do: PLAN (charges count against a limit), DIRECT or NONE, and whether its balance window is the charge period or the card's own dates (balance_window, 313)	Standalone
charge_periods	The current charging period for student cards, proposed nightly from records.academic_terms by cafeteria.sync_charge_periods() and editable in the cafeteria admin (313)	FK: term_id
cashiers	Which cards may log a register in	FK: account_id
registers	The physical terminals; each authenticates with a device token (only its SHA-256 is stored)	Referenced by sales and events
product_categories, products, register_buttons	The catalog and the item buttons on each register	FK: category_id, register_id
settings	Service settings	Standalone
sales, sale_lines, sale_payments	Append-only sales. A void is a new negative sale pointing at the original (voids_sale_id); created_by marks back-office voids (311). Each payment names the card charged and, once exported, its charge_exports row	FK: register_id, cashier_id; sale_payments: account_id, charge_export_id
charge_exports	The nightly charge file sent on to billing	Referenced by sale_payments
register_events, report_subscriptions, import_runs	Register log, report recipients (seeded by 312), and import history	FK: register_id

The account_balances view computes each card's balance over its window.

Only the configuration tables are row-audited

cafeteria is enrolled in core.audit_schema_defaults disabled, and only the configuration tables are switched on. Sales are append-only, accounts are rewritten nightly, and events are already a log, so auditing them would double every row for no information. Any writer of an audited table must set app.actor_id / app.actor_source, or core.audit_row_change() rejects the write.

---

Summary

Counted from the live database rather than from this page, so these are the real numbers - including the two schemas this page did not cover until now.

Schema	Non-lookup tables	lookup_* tables
records	143	71
core	86	41
studentfinance	38	6
lms	24	0
admissions (retired)	19	12
financialaid (retired)	6	1
payments	8	0
cafeteria	16	0
public	1	0
Total	341	131

Counted at migration 313. core here excludes the two tmp_ts_snap* scratch tables noted below; counting them gives 343 non-lookup tables and 474 in all.

The complete, always-current picture

This page is curated prose, so it lags the schema. The ER Diagram is generated from the live database and covers all 315 drawn entities across core, records, studentfinance, lms, payments and cafeteria, with every column, type, key and foreign key. Read it as one page. Where the two disagree, the ER diagram is right.

---

Tables that are documented above but do not exist

Each of these appeared in earlier versions of this page. They are listed so the names are not reintroduced by someone who remembers them.

Name	Reality
auth_magic_links	Magic links are rows in application_access_tokens.
auth_password_resets	Password-reset links are also application_access_tokens, distinguished by token_type.
auth_user_sessions	Sessions live in Valkey, not in Postgres.
form_field_dependencies	No such table. Conditional display is held on the field itself.
form_field_mappings	No such table. A field maps to an entity via maps_to_entity / maps_to_field columns.
form_field_options	No such table. Option lists come from lookup_* tables via the field type.
grade_rosters	No such table. A roster is assembled from student_section_enrollments.
programs	No such table, and no /programs endpoint either. Degrees, majors and minors are the programme model.
student_programs	No such table. A student's programme is recorded in student_majors and student_minors.
student_term_gpa	No such table. Term GPA is computed; the cached cumulative figure sits on students.cumulative_gpa.
student_transcripts	No such table. Transcripts are generated on request from enrolments and grades.
test_score_requirements	Referenced by code but absent from the database — see the warning below.

program_test_requirements — resolved

The test-scores repository used to issue SELECT, INSERT and DELETE against program_test_requirements, which exists in no schema (confirmed against pg_class, so not a view either). All five /api/v1/testscores/requirements endpoints failed at runtime, and nothing in the interface called them.

Those endpoints and their Go layer were removed in August 2026. The rest of the test-scores domain was always sound — applicant_test_scores, institution_test_score_policies, standardized_test_types, person_superscore_records and lookup_verification_status all exist — and test scores continue to appear on the student record.

This was the same failure mode as term fees, formbuilder and admissions: a Go layer written against a schema that was never committed. go build cannot catch it, because a table name is a string. internal/router/sql_conformance_live_test.go now does: it reads every SQL literal in internal/domain and asserts each relation it names actually exists.

---

Not yet documented

This page describes 194 of the 305 non-lookup tables in prose. The remaining 111 are listed here so the gap is visible rather than implied. Every one of them is in the ER Diagram with its full column list - this list is about narrative coverage, not about what is known.

Several are whole subsystems that deserve a section here: the generic workflow engine, the org-unit hierarchy, the audit trail, and most of billing.

academic_level_grading_systems  academic_policy_rules  academic_term_slots
applicant_test_score_sections  applicant_test_scores  application_decisions
application_status_history  application_test_scores  ar_code_mappings  audit_schema_defaults
audit_system_config  audit_table_config  audit_trail_deletion_log  billing_student_type_rules
billing_transaction_items  clubs_organizations  cohort_advisors  cohort_majors
colleague_ar_charges  course_fees  course_material_assignments  course_offering_terms
credit_system_conversions  data_change_history  degree_level_grading_systems
email_message_recipients  email_messages  emphasis_areas  emphasis_blocks  fee_schedules
ferpa_change_log  form_composite_responses  form_field_programs  form_field_responses
grade_change_log  grade_releases  hold_type_roles  housing_plans  identifier_access_log
inquiry_sources  institutions  legacy_academic_credits  material_platforms  meal_plans
migration_ods_waivers  migration_watermarks  network_cidrs  networks  org_units
payment_allocations  permission_audit_log  person_relationships  person_superscore_records
photo_sync_runs  program_fees  prospect_communications  proxy_access_audit_log
proxy_invitation_permissions  refund_schedule_entries  refund_schedules
registration_transaction_notes  requirement_item_activities  role_default_tiles
role_email_policies  role_menu_shortcuts  role_network_rules  schema_migrations
section_fee_overrides  sender_address_grants  sender_addresses  sequence_policies
sso_identity_providers  standardized_test_sections  state_provinces  student_academic_levels
student_account_balance  student_activities  student_advisors  student_awards  student_charges
student_class_waivers  student_emphases  student_housing_assignments  student_leaves
student_list_members  student_list_shares  student_meal_plan_elections
student_noncredit_completions  student_organization_membership  student_payment_plan_schedule
student_payments  student_types  teacher_credentials  term_registration_windows
transaction_payment_history  transfer_course_equivalencies  unit_administrators
upfront_payment_discounts  user_active_role  user_dashboard_preferences  user_filter_presets
user_shortcuts  workflow_definitions  workflow_initiator_roles
workflow_instance_parallel_tracking  workflow_instance_step_assignees  workflow_instance_steps
workflow_instances  workflow_step_permissions  workflow_steps  workflow_transitions

tmp_ts_snap and tmp_ts_snap2

These are leftover scratch tables from a data investigation, not part of the schema. Nothing reads them and they can be dropped. The ER diagram excludes them.