Database Migrations

Overview

AVTotpy manages its own PostgreSQL schema through a built-in migration system. On every startup, before the web server begins accepting requests, the application inspects the database to determine whether the schema needs to be created or updated and acts accordingly.

The behavior is fully configurable via the POSTGRES_MIGRATOR_MODE environment variable.

Furthermore, the migration procedure will create an unprivileged user named POSTGRES_MIGRATOR_UNPRIVILEGED_USER with the password POSTGRES_MIGRATOR_UNPRIVILEGED_USER_PASSWORD. This user is meant to be used by the webserver to access the AVTotpy database schema and perform CRUD operations. Its permissions are restricted to the minimum required to perform the operations.

How It Works

When AVTotpy starts, the migrator runs the following steps:

1. Schema detection: checks whether the avtotpy schema already exists in the database.

   • If it does not exist, the database is treated as blank and all migration files are queued for execution.
   • If it exists, the avtotpy.migrations table is queried for the most recently applied migration.

2. Migration planning: AVTotpy will check its bundled migration files for new versions. Any files that come after the last applied migration are queued for execution.

3. Migration execution: migration files are executed transactionally. After each file runs successfully, an entry is recorded in avtotpy.migrations so the migrator can resume from the correct point on the next startup.

   danger

   A failed migration will leave the database as the last migration set it, but AVTotpy will exit with a non-zero status code. Migrations are a one-way upgrade process and not idempotent: should the avtotpy.migrations lose data or contain invalid state, everything would fall apart.

   It's thus highly recommended to take a backup or snapshot of the database before running migrations or upgrading AVTotpy.

4. Unprivileged user creation: when the schema is being created from scratch, an unprivileged PostgreSQL user (configured via POSTGRES_MIGRATOR_UNPRIVILEGED_USER and POSTGRES_MIGRATOR_UNPRIVILEGED_USER_PASSWORD) is created and granted the minimum permissions required for AVTotpy to operate.

If the schema is already up to date, no migrations are run and the application starts normally.

Migration Modes

The migrator supports three modes, controlled by POSTGRES_MIGRATOR_MODE:

Mode	Description
RUN	Check and apply pending migrations, then start the web server. This is the default behavior.
RUN_AND_EXIT	Check and apply pending migrations, then exit with status code 0. The web server is not started. Useful for running migrations as a one-off init container or job.
SKIP	Skip migration checks entirely and start the web server immediately. Useful when migrations are handled externally or by a separate process.

caution

Using SKIP assumes the schema is already present and up to date. Starting the application against a missing or outdated schema with SKIP will later cause runtime errors.

Privileged vs. Unprivileged Connections

The migrator expects a separate, privileged database connection (POSTGRES_MIGRATOR_CONNECTION_STR) to perform schema changes. This connection is only used during the migration phase.

The web server itself uses the unprivileged connection (POSTGRES_CONNECTION_STR) with the restricted user created during migration.

This separation ensures that the runtime application has the minimum permissions necessary to operate.