Schema Migrations

The schema migration tool handles both initial database setup and subsequent upgrades for your self-hosted environment. The install command is used for initial schema installation (for both the default database and per-tenant databases), while the upgrade command is used for subsequent migrations.

Note: If you’re configuring per-tenant databases, see the Per-Tenant Database Configuration page for setup instructions. The migration tool’s install command is used when setting up new tenant databases, and the upgrade command supports a --tenant flag for upgrading specific tenant databases.

Initial Installation

For Initial Installation of the default database, you can use either:

./upgrade_schema.sh install with a connection string (useful when setting up a new database or when using separate database users)
./upgrade_schema.sh upgrade using the database configured via your DB__ environment variables

Important: As mentioned in the Initialize Database Schema step of the Installation docs, the schema migration tool is only required for initial installation if you’re using separate database users (migration and application users). If you’re using a single database user with all permissions, the application will automatically create the schema on first startup, and you can skip the initial schema migration step.

For detailed information about all available migration tool commands, see the Migration Tool Reference section below.

Upgrading Schema

The following process applies when upgrading an existing Generator instance. Both the default database and any per-tenant databases should be upgraded when deploying a new version.

Download the Latest Version from ECR:

Use your AWS credentials to log in to the Elastic Container Registry (ECR) and pull the latest Docker images.

Example commands:

export AWS_ACCESS_KEY_ID=[your access key id]
export AWS_SECRET_ACCESS_KEY=[your access secret key]

# Log into the ECR repository
aws ecr get-login-password --region us-east-1 | docker login --username AWS --password-stdin 025066246552.dkr.ecr.us-east-1.amazonaws.com

# Pull the latest version of the image
docker pull 025066246552.dkr.ecr.us-east-1.amazonaws.com/rustici-generator:[latest version]

Run the Schema Migration Command Within the New Container:
- Within containers running the image you just downloaded, run the following command to apply any necessary schema changes:
  - ./upgrade_schema.sh upgrade (for the default database)
- If you have configured per-tenant databases, you’ll also need to upgrade each tenant’s database. See the Migration Tool Reference section for details on using the --tenant flag, or use the audit command to check migration status across all databases.
- You can do this with either docker exec (after spinning up a container with the new image) or docker run --rm to create a new container just for this command.
- Example docker run --rm commands:
```
docker run --rm --env-file .env 025066246552.dkr.ecr.us-east-1.amazonaws.com/rustici-generator:[latest version] ./upgrade_schema.sh upgrade
```
- Example docker exec commands:
```
docker exec [backend container name] ./upgrade_schema.sh upgrade
```
- Important: If you have set up separate database users (migration and application), you must configure the DB__USER and DB__PASSWORD environment variables to use the migration user credentials when running ./upgrade_schema.sh upgrade. This is because schema migrations require DDL permissions (CREATE, ALTER, DROP) which only the migration user has. After the migration is complete, ensure your containers are configured to use the application user credentials for normal operations.
- We strive to ensure these upgrades are blue-green compatible within maintenance releases, but refer to the release notes to confirm there aren’t any special considerations for the version you’re deploying.
- Even if you’re running more than one container for the API and/or worker stack, each of these commands only needs to be run once.
  - Running the upgrade more than once won’t cause any problems, it’s just not necessary.
Spin Up New Containers:
- Start new containers using the latest image. Ensure that the environment variables and configurations match your current setup.
Verify the Migration:
- Test the application to ensure that all services are functioning as expected. Use the /api/v1/health/check_up endpoint to verify the health of the API and its connections to other services. This endpoint will also check that your AWS account has access to the necessary Bedrock models, so it’s important to look at the response from this endpoint before running the new version in production.
Introduce New Container(s) into the Load Balancer:
- Update your load balancer configuration to include the new containers. This allows traffic to be routed to the upgraded containers while keeping the old ones active as a fallback during the transition.
Remove Old Containers from the Load Balancer:
- Once the new containers are confirmed to be functioning correctly, remove the old containers from the load balancer to stop routing traffic to them.
Destroy the Old Containers:
- After confirming that the new containers are fully operational and handling all traffic, stop and remove the old containers to free up resources.
Monitor the System:
- Monitor the migrated environment for any issues. Check logs, metrics, and alerts to ensure the system is stable and performing as expected.

Migration Tool Reference

The schema migration tool (upgrade_schema.sh) is shipped within the Docker container containing your Generator release. The script can be executed from within the container, or from outside using Docker commands like docker compose exec [container name] ./upgrade_schema.sh -h or docker compose run --rm [image name] ./upgrade_schema.sh -h.

The migration tool provides three main operations: install, upgrade, and audit. You can always use the -h flag with any command to see its available options.

Main Help

▶ ./upgrade_schema.sh -h

usage: cli.py [-h] {install,upgrade,audit} ...

Database schema migration tool for Rustici Generator

positional arguments:
  {install,upgrade,audit}
                        Available operations
    install             Install with custom connection string
    upgrade             Upgrade schema
    audit               Audit tenant database migration status

options:
  -h, --help            show this help message and exit

Installing Schema

Use the install command to initialize a new database with Generator’s schema. This command is used for:

Initial setup of the default database (when using separate database users)
Setting up new per-tenant databases

Usage:

▶ ./upgrade_schema.sh install -h

usage: cli.py install [-h] connection_string

positional arguments:
  connection_string  Database connection string

options:
  -h, --help         show this help message and exit

Examples:

# Install schema on the default database
▶ ./upgrade_schema.sh install "postgresql://user:password@host:5432/generator_db"

# Install schema on a new per-tenant database
▶ ./upgrade_schema.sh install "postgresql://user:password@host:5432/tenant_db"

Upgrading Schema

Use the upgrade command to apply schema migrations to existing databases. This ensures databases stay in sync with the application’s current schema version.

Usage:

▶ ./upgrade_schema.sh upgrade -h

usage: cli.py upgrade [-h] [--tenant TENANT]

options:
  -h, --help       show this help message and exit
  --tenant TENANT  Upgrade specific tenant (if not provided, upgrades default)

Examples:

# Upgrade the default database (uses DB__ environment variables)
▶ ./upgrade_schema.sh upgrade

# Upgrade a specific tenant's database
▶ ./upgrade_schema.sh upgrade --tenant "tenant-id"

Note: When upgrading, the command uses the database connection configured via DB__ environment variables. For per-tenant database upgrades, the --tenant flag allows you to target a specific tenant’s database.

Auditing Migration Status

Use the audit command to check the migration status across all configured tenant databases. This helps identify which databases need schema updates.

Usage:

▶ ./upgrade_schema.sh audit -h

usage: cli.py audit [-h] [--format {json,table}]

options:
  -h, --help            show this help message and exit
  --format {json,table}
                        Output format (default: json)

Examples:

# Audit with JSON output (default)
▶ ./upgrade_schema.sh audit

# Audit with table output
▶ ./upgrade_schema.sh audit --format table

The audit command checks the migration status of the default database and all configured per-tenant databases, showing which databases are up-to-date and which need upgrades.