Models

Go struct patterns for database entities.

Model Pattern

type Person struct {
    PersonID            string     `db:"person_id"`
    PersonIDNumber      *string    `db:"person_id_number"`
    DisplayName         string     `db:"display_name"`
    PrimaryEmail        *string    `db:"primary_email"`
    DateOfBirth         *time.Time `db:"date_of_birth"`
    GenderID            *int32     `db:"gender_id"`
    EthnicityID         *int32     `db:"ethnicity_id"`
    StatusID            *int32     `db:"status_id"`
    CreatedAt           time.Time  `db:"created_at"`
    UpdatedAt           time.Time  `db:"updated_at"`
}

Key Conventions

Primary Keys

Use {singular}_id naming with appropriate types:

// Integer primary key
CourseID int32 `db:"course_id"`

// UUID primary key (for persons)
PersonID string `db:"person_id"`

Struct Tags

Use db tags for database column mapping:

type Entity struct {
    EntityID  int32  `db:"entity_id"`
    Name      string `db:"name"`
    IsActive  bool   `db:"is_active"`
}

Nullable Fields

Use pointers for nullable database columns:

// Required field
DisplayName string `db:"display_name"`

// Nullable field
MiddleName *string `db:"middle_name"`

// Nullable timestamp
DeletedAt *time.Time `db:"deleted_at"`

// Nullable integer
GenderID *int32 `db:"gender_id"`

Timestamps

Include standard timestamp fields:

CreatedAt time.Time `db:"created_at"`
UpdatedAt time.Time `db:"updated_at"`

Foreign Keys

Reference related entities by ID:

type Course struct {
    CourseID       int32 `db:"course_id"`
    DepartmentID   int32 `db:"department_id"`
    StatusID       int32 `db:"status_id"`
}

Lookup Values

For lookup references, include both ID and display fields:

type Course struct {
    CourseID      int32  `db:"course_id"`
    StatusID      int32  `db:"status_id"`
    // Joined fields for display (populated via SQL JOIN)
    StatusCode    string `db:"status_code"`
    StatusDisplay string `db:"status_display"`
}

Complete Model Example

package academic

import "time"

type Course struct {
    // Primary key
    CourseID int32 `db:"course_id"`

    // Core fields
    CourseCode       string  `db:"course_code"`
    Title            string  `db:"title"`
    Description      *string `db:"description"`
    CreditHoursMin   float64 `db:"credit_hours_min"`
    CreditHoursMax   float64 `db:"credit_hours_max"`
    IsVariableCredit bool    `db:"is_variable_credit"`
    IsRepeatable     bool    `db:"is_repeatable"`
    MaxRepeats       *int32  `db:"max_repeats"`
    ContactHours     *float64 `db:"contact_hours"`

    // Foreign keys
    DepartmentID    int32  `db:"department_id"`
    GradingSystemID *int32 `db:"grading_system_id"`
    StatusID        int32  `db:"status_id"`

    // Joined fields (from SELECT with JOINs)
    DepartmentCode  string `db:"department_code"`
    DepartmentName  string `db:"department_name"`
    StatusCode      string `db:"status_code"`
    StatusDisplay   string `db:"status_display"`

    // Timestamps
    EffectiveDate *time.Time `db:"effective_date"`
    CreatedAt     time.Time  `db:"created_at"`
    UpdatedAt     time.Time  `db:"updated_at"`
}

Repository Query Pattern

The backend uses database/sql with lib/pq as the PostgreSQL driver. Queries use parameterized placeholders ($1, $2, etc.) and rows are scanned using the shared db.ScanRows[T]() generic helper or manual Scan() calls.

func (r *repository) FindByID(ctx context.Context, id int32) (*Course, error) {
    query := `
        SELECT
            c.course_id,
            c.course_code,
            c.title,
            c.description,
            c.credit_hours_min,
            c.credit_hours_max,
            c.department_id,
            d.department_code,
            d.name as department_name,
            c.status_id,
            lcs.code as status_code,
            lcs.display_value as status_display,
            c.created_at,
            c.updated_at
        FROM courses c
        JOIN departments d ON c.department_id = d.department_id
        JOIN lookup_course_status lcs ON c.status_id = lcs.id
        WHERE c.course_id = $1
    `

    row := r.db.QueryRowContext(ctx, query, id)
    var course Course
    err := row.Scan(
        &course.CourseID,
        &course.CourseCode,
        &course.Title,
        &course.Description,
        &course.CreditHoursMin,
        &course.CreditHoursMax,
        &course.DepartmentID,
        &course.DepartmentCode,
        &course.DepartmentName,
        &course.StatusID,
        &course.StatusCode,
        &course.StatusDisplay,
        &course.CreatedAt,
        &course.UpdatedAt,
    )

    if err == sql.ErrNoRows {
        return nil, errors.NewNotFoundError("course")
    }
    if err != nil {
        return nil, err
    }

    return &course, nil
}

List Queries with QueryBuilder

For list endpoints, use the shared db.NewQueryBuilder() for dynamic SQL:

func (r *repository) FindAll(ctx context.Context, params *CourseQueryParams) ([]Course, int64, error) {
    qb := db.NewQueryBuilder("courses c")
    qb.Join("JOIN departments d ON c.department_id = d.department_id")
    qb.Join("JOIN lookup_course_status lcs ON c.status_id = lcs.id")

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

    // Get total count
    countSQL, countArgs := qb.CountSQL()
    var total int64
    r.db.QueryRowContext(ctx, countSQL, countArgs...).Scan(&total)

    // Paginate and fetch
    qb.Offset(params.GetOffset()).Limit(params.GetLimit())
    selectSQL, selectArgs := qb.SelectSQL()
    rows, err := r.db.QueryContext(ctx, selectSQL, selectArgs...)
    if err != nil {
        return nil, 0, err
    }
    defer rows.Close()

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

Embedded Structs

For common field groups:

type Timestamps struct {
    CreatedAt time.Time `db:"created_at"`
    UpdatedAt time.Time `db:"updated_at"`
}

type AuditFields struct {
    CreatedBy *string `db:"created_by"`
    UpdatedBy *string `db:"updated_by"`
}

// Note: Go doesn't support automatic struct embedding
// for database scanning - fields must be explicit

Enum-like Constants

For status codes and types:

const (
    CourseStatusActive   = "ACTIVE"
    CourseStatusInactive = "INACTIVE"
    CourseStatusPending  = "PENDING"
)

const (
    EnrollmentStatusRegistered = "REGISTERED"
    EnrollmentStatusDropped    = "DROPPED"
    EnrollmentStatusWithdrawn  = "WITHDRAWN"
)