Migrations

SQL-based database migrations for PostgreSQL.

Overview

Database schema is managed via hand-written SQL scripts. There is no ORM or Go migration framework. Which files a given database has actually had applied is recorded in that database, in core.schema_migrations, and scripts/db-migrate.ps1 is the runner that reads and writes it.

The ledger deliberately lives inside the database rather than in a file beside it. That means the applied-set travels inside pg_dump: a database restored from a dev dump inherits that machine's applied-set, so only genuinely-newer migrations run afterwards. A marker file would keep claiming migrations the restored database no longer contains.

deploy.ps1 applies pending migrations to sis and sis2 on every deploy, which is how a schema change reaches production without replacing the data. A dump restore is opt-in there (-RestoreDump -ConfirmReplace sis); see Database Setup.

Do not apply migrations with bare psql

The ledger is what production trusts. Applying a migration by hand without recording it makes the next deploy try to apply it again, and several pre-084 migrations have unguarded bulk INSERTs that would duplicate rows. dumpdb.ps1 warns when the local ledger and the migrations directory disagree, for exactly this reason.

Migration Location

database/migrations/
├── 001_demographics.sql
├── 002_cohorts.sql
├── 003_scheduling.sql
├── ...
├── 006a_create_lookup_tables.sql
├── 006b_rename_fk_columns.sql
├── ...
├── 023_course_materials.sql
├── 063_generate_planned_sections_2026_2030.sql
├── 064_insert_search_min_characters_setting.sql
└── ...

Creating Migrations

Naming Convention

{NNN}_{description}.sql

Examples: 065_add_new_feature.sql, 065a_seed_data.sql

Use letter suffixes (a, b, c) for related migrations that should run in a specific order.

Never reuse a number. Fourteen prefixes were reused historically (three 018_*, three 020_*, and so on), which leaves their relative order undefined. The runner now refuses to proceed when two pending files claim the same slot, so a reused number blocks the deploy rather than picking an arbitrary order. Take the next free number — check database/migrations first, not the last one you remember.

The name must match {NNN}[a-z]_{description}.sql exactly (three or more digits, at most one lowercase letter). Anything else is reported as ignored and never runs.

Requirements for new migrations

These are not style preferences. Baselining means the historical files never run again, but everything from here forward must hold:

1. Wrap the whole file in BEGIN; … COMMIT;. That is what makes a failure leave the database untouched and no ledger row behind, so the migration retries cleanly. A file with no transaction gets one from the runner's psql -1, but writing it explicitly keeps the guarantee visible.
2. Be idempotent — IF NOT EXISTS, CREATE OR REPLACE, ON CONFLICT DO NOTHING. This is the safety net if a ledger ever goes stale.
3. Qualify schema names (records.students, not students). The runner sets search_path for its session, but a migration that does not depend on it is one less thing that can surprise you.
4. No hardcoded surrogate IDs. 040 contains WHERE major_id = 3185, which is meaningful only against the exact database it was generated from.
5. If a statement genuinely cannot run inside a transaction (CREATE INDEX CONCURRENTLY, ALTER TYPE … ADD VALUE, VACUUM), put -- migrate: no-transaction on the first line. Nothing in the repository needs this today.

Migration Template (PostgreSQL)

-- Migration: 065_add_student_feature.sql
-- Description: Add student feature table
-- Author: Developer Name
-- Date: 2026-03-26

BEGIN;

-- Create table if it doesn't exist
CREATE TABLE IF NOT EXISTS records.student_features (
    feature_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL REFERENCES records.students(student_id),
    -- Lookup PKs are {table}_id, not id, since migration 083.
    feature_type_id INT REFERENCES core.lookup_feature_type(feature_type_id),
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100),
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(100)
);

CREATE INDEX IF NOT EXISTS idx_student_features_student
    ON records.student_features(student_id);

-- Add lookup values
INSERT INTO core.lookup_feature_type (code, display_value, is_active, sort_order)
VALUES ('NEW_FEATURE', 'New Feature', true, 10)
ON CONFLICT (code) DO NOTHING;

COMMIT;

Running Migrations

# What is pending? Read-only, safe to run any time.
.\scripts\db-migrate.ps1

# Apply everything pending, in order, stopping at the first failure.
.\scripts\db-migrate.ps1 -Apply

# Same against production, over ssh, inside the server's postgres container.
.\scripts\db-migrate.ps1 -Target Prod -Status
.\scripts\db-migrate.ps1 -Target Prod -Database sis2 -Apply

Each migration runs as one psql invocation that claims its ledger row, runs the file, then confirms the row — wrapped in a single transaction where the file allows it. A failure therefore rolls back and records nothing, so the database is exactly as it was and the migration retries cleanly.

First use on a database: baseline it

A database with an empty ledger has never been baselined, and the runner refuses to apply anything to it. Replaying this repository's history from 001 cannot succeed: roughly half the files predate the transactional convention, 061 selects a column that 083 renamed away, 040 hardcodes surrogate IDs, 043 and 170 both UPDATE records.lookup_hold_type unguarded after 177 drops it, and several have unguarded bulk INSERTs.

Those older files are left as they are on purpose. Editing an applied migration changes its checksum, and core.schema_migrations records one per file — so a "harmless" tidy-up reports checksum drift on every database that already ran it, on every subsequent deploy, until someone reconciles. Fixing a latent replay hazard is not worth that, given replay is not a supported path in the first place.

So the first step on any existing database is to declare that its schema already reflects what is on disk:

.\scripts\db-migrate.ps1 -Baseline                      # records every file as applied; runs none
.\scripts\db-migrate.ps1 -Target Prod -Baseline
.\scripts\db-migrate.ps1 -Target Prod -Database sis2 -Baseline

Baseline at the vintage that matches the schema. Baselining after pulling new migrations would mark them applied without running them, and the schema they describe would never arrive.

Reading the report

Every run prints the whole picture, not just what it intends to do:

Line	Meaning
pending, in order	What will run, sorted by numeric prefix then letter suffix
ignored	Files whose names do not match {NNN}[a-z]_*.sql and so will never run — add_item_course_id.sql (legacy T-SQL) and any .bak. A misnamed new migration shows up here rather than silently doing nothing
checksum drift	An already-applied file has changed on disk. Warns, never blocks, never re-runs. Re-record with -Reconcile once reviewed
recorded but absent from disk	Normal right after restoring a dump from a machine with newer migrations
unconfirmed ledger row	A run claimed a file and never confirmed it (connection died after the file committed). Blocks everything — verify by hand, then DELETE the row to retry or set applied_at to accept it

Other rails, all of which fail closed: two pending files claiming the same number, a file that exists locally but not on the server (uncommitted or unpushed), more than 10 pending files (-AllowBulk to override), and a pending file containing ALTER DATABASE (-AllowAlterDatabase), since that names a database explicitly and could act on the wrong one.

Tests

The ordering rules and every rail are pure functions in scripts/DbMigrate.ps1, covered with no database and no network:

pwsh -File scripts\tests\test-DbMigrate.ps1

Migration Best Practices

Idempotent Scripts

Make scripts safe to run multiple times:

-- Tables
CREATE TABLE IF NOT EXISTS new_table (...);

-- Columns
ALTER TABLE existing_table ADD COLUMN IF NOT EXISTS new_column VARCHAR(100);

-- Data
INSERT INTO lookup_status (code, display_value, is_active, sort_order)
VALUES ('NEW', 'New Status', true, 10)
ON CONFLICT (code) DO NOTHING;

Adding Columns

ALTER TABLE courses
ADD COLUMN IF NOT EXISTS max_enrollment INT;

Adding Indexes

CREATE INDEX IF NOT EXISTS idx_enrollments_student_section
ON student_section_enrollments(student_id, section_id);

Lookup Table Data

Reference data is stored in individual lookup_* tables (not the legacy sys_lookup_values). To add new values:

INSERT INTO lookup_program_status (code, display_value, description, is_active, sort_order)
VALUES ('SUSPENDED', 'Suspended', 'Program temporarily suspended', true, 50)
ON CONFLICT (code) DO NOTHING;

Foreign Key References

FK columns reference individual lookup tables:

-- Correct: individual lookup table, PK named {table}_id
feature_type_id INT REFERENCES core.lookup_feature_type(feature_type_id)

-- Incorrect (pre-083): lookup PKs are no longer a bare `id`
-- feature_type_id INT REFERENCES lookup_feature_type(id)

-- Incorrect (legacy): do NOT reference sys_lookup_values
-- feature_type_lookup_id INT REFERENCES sys_lookup_values(lookup_value_id)

Lookup Table Registry

The backend maps lookup type codes to table names via core/lookup_registry.go:

// Get the table name for a lookup type
tableName := core.LookupTableName("PROGRAM_STATUS") // -> "lookup_program_status"

Each lookup_* table has columns: {table}_id (e.g. program_status_id), code, display_value, description, is_active, sort_order. Migration 083 renamed every bare id to {table}_id; SQL that scans into the shared core.LookupEntry struct aliases it back (SELECT program_status_id AS id, ...).