Database Architecture

PostgreSQL database design.

Overview

• Database: PostgreSQL 18 (postgres:18-alpine in production)
• Driver: lib/pq (Go PostgreSQL driver)
• Interface: Standard library database/sql
• Migrations: SQL scripts applied through scripts/db-migrate.ps1, recorded in the core.schema_migrations ledger — never with bare psql

Schemas

The database is not a flat public schema. Nine schemas hold 341 non-lookup tables and 131 lookup_* tables (counted at migration 313):

Schema	Non-lookup	Lookup	Contents
records	143	71	Academic calendar, courses and sections, programs and requirements, roles and student data, registration, degree audit, workflows, test scores
core	86	41	Organization, people and identity, auth and authorization, role network rules, audit trail, proxy access, staff email outbox and attachments, notifications, system settings
studentfinance	38	6	Fee catalog and rates, student charges and payments, plans and elections
lms	24	0	LMS connectors, instances, shells, sync (migration 199)
admissions	19	12	Retired — moved to a separate system in Aug 2026; rows retained
financialaid	6	1	Retired — deliberately unreachable; rows retained
payments	8	0	The ACI card-payment portal (migration 153); its later tables come from the portal's own migrations
cafeteria	16	0	The cafeteria point-of-sale service (migrations 309-313); nothing in SIS+ reads it
public	1	0	One migration artifact

core and the total above exclude the two tmp_ts_snap* scratch tables.

Always schema-qualify ad hoc SQL. The application's search_path is core, records, admissions, studentfinance, financialaid, public, so an unqualified name resolves by that order and a query that works in one session can resolve differently in another.

The complete picture is generated

ER Diagram is emitted from information_schema by docs/generate_erd.py, so it matches the database by construction — 315 entities with every column, type and foreign key. Read it as one page. Database Tables is the curated prose companion.

Schema Design

Naming Conventions

Type	Convention	Example
Tables	snake_case, plural	students, course_sections
Columns	snake_case	first_name, created_at
Primary Keys	{singular}_id	student_id, course_id
Foreign Keys	{referenced_table}_id	department_id
Lookup FKs	{field}_id	status_id

Standard Columns

Every table includes:

created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
created_by VARCHAR(100),
updated_by VARCHAR(100)

Key Tables by Domain

Core (Reference Data)

• lookup_* tables - Individual lookup tables (e.g., lookup_program_status, lookup_term_type). Two of them carry behavior and are owned by their domain — see Lookup Tables
• sys_currencies - Currency definitions
• sys_institution_settings - Institution-wide settings
• countries, state_provinces - Country and subdivision codes (migration 171 seeded US and Canada only)
• org_units, unit_administrators - The administrative tree and who runs each node

Academic

• academic_years - Year definitions
• academic_terms - Semesters/quarters
• sub_terms - Sub-term periods
• courses - Course catalog
• course_sections - Section offerings
• classes, class_sections - Schedule containers grouping cross-listed sections; created lazily. Retired from the interface in July 2026 when scheduling became section-centric
• class_schedules - The live source of section meeting times, reached through a section's container
• degrees, majors, minors - Programs
• requirement_blocks, requirement_items - Requirements
• grading_systems, grading_system_grades - Grade definitions
• academic_level_grading_systems - Per-level default; grading resolves section, then course, then academic-level default (migration 188)
• grade_releases - Registrar release gating published grades (migration 203)
• grade_visibility_rules, grade_visibility_completions, grade_visibility_releases - Hold official grades from the student's own view until a condition (course evaluation, manual release) is met; read through vw_student_hidden_grades, never by GPA/transcript (migration 299)

People

• people - Base person records (UUID primary key). Note the table is people, not persons; it also contains organizations, so never filter organizations out by name keyword
• person_names - Name history
• person_addresses, person_phones, person_emails - Contact info
• person_activities, person_essays, person_recommendations - Application materials

Roles

• students - Student role data
• teachers - Faculty data
• advisors - Advisor data
• applicants - Applicant data

Registration

• student_section_enrollments - Student-section registrations
• student_term_enrollments - Per-term enrollment summary
• student_holds, hold_types - Registration blocks
• section_waitlists - Waitlist positions
• planned_courses - Course planning
• registration_transactions - Registration workflow

Billing

• billing_methods, billing_method_configs - Tuition calculation
• term_fees, fee_types - Fee definitions
• student_charges, student_payments - Transactions
• payment_plans, payment_plan_schedules - Installment plans
• refund_schedules, refund_schedule_entries - Refund policies

Financial Aid (NEW)

• award_types - Scholarship/grant/loan definitions
• awards - Student award allocations
• fafsa_data - FAFSA tracking
• financial_estimates - Cost of attendance
• estimate_cost_items, estimate_awards - Estimate details

Test Scores (NEW)

• standardized_test_types - Test definitions (SAT, ACT, GRE)
• standardized_test_sections - Test subsections
• program_test_requirements - Program-level requirements
• applicant_test_scores - Submitted scores
• person_superscore_records - Superscore calculations

Proxy Access (NEW)

• student_proxy_access - Delegated account access
• proxy_invitations - Email-based invitations
• proxy_access_audit_log - Activity audit trail

Admissions

• applicants - Applicant records
• admission_applications - Application submissions
• application_documents - Uploaded documents
• form_templates, form_responses - Dynamic forms

Authorization

• roles, permission_nodes - RBAC definitions
• role_permissions - Role-permission mappings
• user_roles - User role assignments

Lookup Values

Reference data pattern using individual lookup tables:

-- Create lookup table for course status.
-- The primary key is the table name minus the lookup_ prefix, plus _id — never
-- a bare `id`. Migration 083 renamed every remaining bare id in the database.
CREATE TABLE lookup_course_status (
    course_status_id SERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    display_value VARCHAR(200) NOT NULL,
    display_value_es VARCHAR(200),
    display_value_fr VARCHAR(200),
    is_active BOOLEAN NOT NULL DEFAULT true,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID
);

-- Insert lookup values
INSERT INTO lookup_course_status (code, display_value, sort_order)
VALUES
  ('ACTIVE', 'Active', 1),
  ('INACTIVE', 'Inactive', 2);

Alias the key back to id when scanning

The shared core.LookupEntry struct expects a column called id, so SQL touching a single lookup table should alias it: SELECT course_status_id AS id, code, display_value .... core.LookupTableName("COURSE_STATUS") and core.LookupIDColumn("COURSE_STATUS") resolve the table and key names from the registry rather than hard-coding them.

Usage in Go models:

type Course struct {
    CourseID      int32  `db:"course_id"`
    Title         string `db:"title"`
    StatusID      int32  `db:"status_id"`
    // Joined fields for display
    StatusCode    string `db:"status_code"`
    StatusDisplay string `db:"status_display"`
}

Migrations

SQL migration scripts in database/migrations/:

-- migrations/001_create_entities.sql
CREATE TABLE entities (
    entity_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    status_id INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_entities_status
        FOREIGN KEY (status_id)
        REFERENCES lookup_entity_status(id)
);

Running migrations (via the runner, which records each file in core.schema_migrations so a deploy knows what production is still missing):

.\scripts\db-migrate.ps1            # report what is pending
.\scripts\db-migrate.ps1 -Apply     # apply it

Indexing Strategy

Create indexes for:

• Foreign keys
• Commonly filtered columns
• Search fields

CREATE INDEX idx_entities_status
ON entities(status_id);

CREATE INDEX idx_entities_name
ON entities(name);

-- Full-text search index
CREATE INDEX idx_courses_title_search
ON courses USING GIN (to_tsvector('english', title));

Go Model Mapping

type Entity struct {
    EntityID  int32     `db:"entity_id"`
    Name      string    `db:"name"`
    StatusID  int32     `db:"status_id"`
    CreatedAt time.Time `db:"created_at"`
    UpdatedAt time.Time `db:"updated_at"`
}

Connection Pooling

The application uses the standard database/sql package, which provides built-in connection pooling. The pool is configured at startup:

func NewPostgresDB(dsn string, maxConns int, maxIdleTime time.Duration) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }

    // Connection pool settings
    db.SetMaxOpenConns(maxConns)          // max simultaneous connections (default: 25)
    db.SetMaxIdleConns(maxConns / 2)      // idle connections kept alive
    db.SetConnMaxIdleTime(maxIdleTime)    // close idle connections after this duration
    db.SetConnMaxLifetime(time.Hour)      // max lifetime of any connection

    // Test connection
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        db.Close()
        return nil, fmt.Errorf("failed to ping database: %w", err)
    }

    return db, nil
}

Configuration via environment variables:

Variable	Default	Description
DB_MAX_CONNS	25	Maximum open connections
DB_MAX_IDLE_TIME	5m	Close idle connections after this duration

Transactions

Using database/sql transactions via the db.WithTx helper:

err := db.WithTx(ctx, database, func(tx *sql.Tx) error {
    // Execute queries within the transaction
    _, err := tx.ExecContext(ctx, "UPDATE students SET status_id = $1 WHERE student_id = $2", statusID, studentID)
    if err != nil {
        return err
    }

    _, err = tx.ExecContext(ctx, "INSERT INTO registration_transactions (...) VALUES (...)")
    if err != nil {
        return err // triggers automatic rollback
    }

    return nil // triggers automatic commit
})

The WithTx helper handles begin, commit, and rollback automatically:

func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original error: %w)", rbErr, err)
        }
        return err
    }

    return tx.Commit()
}

Query Builder

The db.NewQueryBuilder utility builds parameterized PostgreSQL queries with conditional WHERE clauses. It uses ? placeholders that are automatically converted to $N numbered parameters.

qb := db.NewQueryBuilder("courses")
qb.Select("courses.*, lookup_course_status.code AS status_code")
qb.Join("LEFT JOIN lookup_course_status ON lookup_course_status.id = courses.status_id")

if params.Search != "" {
    search := "%" + params.Search + "%"
    qb.Where("(title ILIKE ? OR course_code ILIKE ?)", search, search)
}
if params.StatusID != nil {
    qb.Where("status_id = ?", *params.StatusID)
}

qb.OrderBy("title ASC")
qb.Offset(params.GetOffset())
qb.Limit(params.GetLimit())

// Count query (for pagination)
countSQL, countArgs := qb.CountSQL()
var total int64
err := database.QueryRowContext(ctx, countSQL, countArgs...).Scan(&total)

// Select query
selectSQL, selectArgs := qb.SelectSQL()
rows, err := database.QueryContext(ctx, selectSQL, selectArgs...)
defer rows.Close()

courses, err := db.ScanRows[Course](rows)

Row Scanning

The generic db.ScanRows[T] function scans *sql.Rows into typed slices using struct db tags:

rows, err := database.QueryContext(ctx, query, args...)
if err != nil {
    return nil, err
}
defer rows.Close()

results, err := db.ScanRows[Course](rows)
// results is []Course