Database Migrations

The chris-os migration system is append-only SQL files, tracked in a schema_migrations table, applied by scripts/migrate.sh. There are 302 applied migrations as of this writing.

The System

File location: database/migrations/

Naming: NNN_description.sql where NNN is a zero-padded three-digit sequence number. The baseline consolidations use 000_, 001_, 002_ as prefixes.

Tracking table: Every applied migration records a row in schema_migrations with version TEXT PK, applied_at TIMESTAMPTZ, checksum, and description. The migrate script checks this table before applying; it skips files that are already recorded.

Baseline consolidation: Migrations 000_*, 001_*, 002_* are consolidated baselines. They represent the full schema state at a checkpoint, not incremental changes. Everything after 002_ is sequential and incremental.

The Migration Runner

scripts/migrate.sh operates in three modes:

Mode	Use case	How it connects
`--mode local`	Mac staging (dev)	Direct connection to local Mac postgres
`--mode pi`	Apply from Mac via SSH tunnel	SSH to Caroline, then local connection
`--mode deploy`	Already on Caroline	Local connection (used by CI)

The runner wraps each migration in BEGIN/COMMIT unless the file contains its own transaction control or uses CONCURRENTLY, VACUUM, or REINDEX (which cannot run inside a transaction).

Make targets:

make db-migrate        # Apply to Mac staging (--mode local)
make pi-db-migrate     # Apply to production on Caroline (--mode pi)

The deploy workflow always runs migrations in --mode deploy on every push to main, regardless of which paths changed. Migrations are idempotent.

Timezone Rules

Every migration that creates or modifies timestamp columns must follow the single-source-of-truth timezone rules:

Column type	Default expression	Never use
`TIMESTAMPTZ`	`NOW()`	`pacific_now()`
`TIMESTAMP` (no tz)	`pacific_now()`	`NOW()`, `CURRENT_TIMESTAMP`
`DATE`	`pacific_today()`	`CURRENT_DATE`

Functions pacific_now(), pacific_today(), and pacific_date() are defined in the database and return Pacific-time values. The UI converts stored values to Pacific for display; the database stores them correctly by default expression.

Rules for Writing Migrations

Never modify an applied migration. Once a migration file is committed and applied anywhere, it is immutable. The checksum in schema_migrations will not match a modified file, and the runner treats that as an error.

Test on Mac staging first. Apply to local Mac postgres with make db-migrate, verify the schema change, then apply to production with make pi-db-migrate.

Forward-only design. There is no rollback mechanism in the runner. If a migration needs to be undone, write a new migration that reverses the change. The deploy pipeline does not roll back migrations even when a code deploy fails.

Concurrent indexes go in their own migration. CREATE INDEX CONCURRENTLY cannot run inside a transaction block. The runner detects the CONCURRENTLY keyword and omits the transaction wrapper, but the migration file must contain only the concurrent index creation.

Multi-statement migrations in n8n Postgres nodes must be wrapped in a DO $$ BEGIN ... END $$ function block. n8n’s Postgres node does not support bare multi-statement SQL.

Checking Migration State

# Current migration level on Caroline
make pi-db-shell
# Then in psql:
SELECT version, applied_at, description
FROM schema_migrations
ORDER BY version DESC
LIMIT 10;

# Check for pending migrations (files not yet in schema_migrations)
make pi-shell
# On Caroline, list the latest migration files:
ls database/migrations/ | tail -20

Schema Isolation

The therapy schema is isolated. No migration should grant non-therapy roles access to therapy.*. If a migration accidentally touches therapy tables, it will need a corrective migration.

The authelia database is managed by Authelia itself, not by this migration runner. Do not write migrations against the authelia database.