Databases and Migrations

Roster supports SQLite for standalone deployments and PostgreSQL for Enterprise deployments.

Database	Editions	Deployment shape
SQLite	All	One Roster instance with durable /data
PostgreSQL 17 or 18	Enterprise	One or more Roster instances

Choose the database before creating the production deployment. Roster does not include an automated SQLite-to-PostgreSQL data migration. Existing SQLite installations can upgrade to 1.2.0 and continue using the same /data volume; a PostgreSQL deployment starts with a separate PostgreSQL database.

Use this page in the following order:

1. Select SQLite or PostgreSQL before creating production data.
2. For PostgreSQL, provision separate migration and runtime roles.
3. Apply migrations before starting the new application version.
4. Configure and test backups before accepting the deployment.

SQLite

Leave ROSTER_DATABASE_URL unset to use /data/roster.db, or set it to an explicit SQLite file: URL:

ROSTER_DATABASE_URL=file:/data/roster.db

SQLite defaults to ROSTER_DATABASE_MIGRATION_MODE=auto, which applies pending migrations before starting Roster. Use one Roster instance and mount /data on durable storage.

PostgreSQL

Enterprise deployments can use PostgreSQL by setting the runtime database URL and loading a valid Enterprise license:

ROSTER_DATABASE_URL=postgresql://roster:<password>@postgres.internal:5432/roster
ROSTER_LICENSE_KEY=<enterprise-license-jwt>

To distribute ordinary reads across PostgreSQL read replicas, add one or more comma-separated replica URLs:

ROSTER_DATABASE_REPLICA_URLS=postgresql://roster_reader:<password>@replica-a.internal:5432/roster,postgresql://roster_reader:<password>@replica-b.internal:5432/roster

Writes and transactions continue to use ROSTER_DATABASE_URL. Replica routing is optional for standalone PostgreSQL and required in High Availability.

PostgreSQL is not available in Free or Pro. See Licensing for edition behavior and license installation.

Provision the database and roles

Use a managed PostgreSQL 17 or 18 service for production. Create one database, a schema-owning migration role, a DML-only application role, and—when read replicas are used—a read-only role. Configure login passwords or certificate authentication through the database provider rather than storing credentials in this script.

Run the following as a PostgreSQL administrator, replacing the role and database names if your provider requires different names:

CREATE ROLE roster_migrator LOGIN;
CREATE DATABASE roster OWNER roster_migrator;

\connect roster

CREATE ROLE roster_app LOGIN;
CREATE ROLE roster_reader LOGIN;

GRANT CONNECT ON DATABASE roster TO roster_app, roster_reader;
GRANT USAGE ON SCHEMA public TO roster_app, roster_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO roster_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO roster_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO roster_reader;

ALTER DEFAULT PRIVILEGES FOR ROLE roster_migrator IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO roster_app;
ALTER DEFAULT PRIVILEGES FOR ROLE roster_migrator IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO roster_app;
ALTER DEFAULT PRIVILEGES FOR ROLE roster_migrator IN SCHEMA public
  GRANT SELECT ON TABLES TO roster_reader;

The example intentionally does not place passwords in SQL text. Configure each login through the managed-database console or, when using psql, run the following commands and enter each generated password at the prompt:

\password roster_migrator
\password roster_app
\password roster_reader

Use the database provider’s required TLS mode in every connection URL. Confirm that the migration and runtime credentials can connect from the network where their respective jobs will run before continuing.

Run the migration-only container with the roster_migrator writer URL. Run Roster with the roster_app writer URL and use roster_reader URLs for ROSTER_DATABASE_REPLICA_URLS. The runtime roles must not own the schema or receive CREATE, ALTER, DROP, or TRUNCATE privileges.

After the first migration, verify the application role can read and write Roster tables but cannot create or alter tables. Repeat that verification after restoring a backup into a recovery environment.

Production database migrations

Production PostgreSQL defaults to ROSTER_DATABASE_MIGRATION_MODE=skip. Apply migrations with one dedicated release job before starting or updating Roster instances.

Mode	Container behavior	Intended use
only	Applies pending migrations, then exits successfully	One migration job per production release
skip	Starts Roster without attempting a migration	Production PostgreSQL Roster instances
auto	Applies pending migrations, then starts Roster	SQLite and non-production environments

For PostgreSQL, give the migration job a schema-owning database credential and give Roster instances a separate DML-only credential. Store both sets of credentials in your deployment secret manager or in protected mode-0600 environment files; do not place them directly in shell history.

A migration-only environment file contains only the primary database URL and migration mode:

ROSTER_DATABASE_URL=postgresql://roster_migrator:<password>@postgres.internal:5432/roster
ROSTER_DATABASE_MIGRATION_MODE=only

Save it as /secure/roster-migration.env or provide the equivalent through a one-off release job.

A complete runtime environment for a fresh PostgreSQL database is:

ROSTER_AUTH_URL=<your-roster-public-url>
ROSTER_AUTH_SECRET=<generated-auth-secret>
ROSTER_BOOTSTRAP_ADMIN_PASSWORD=<generated-strong-password>
ROSTER_PROVIDER_SECRET_ENCRYPTION_KEY=<generated-provider-secret-encryption-key>
ROSTER_LICENSE_KEY=<enterprise-license-jwt>
ROSTER_DATABASE_URL=postgresql://roster_app:<password>@postgres.internal:5432/roster
ROSTER_DATABASE_REPLICA_URLS=postgresql://roster_reader:<password>@replica.internal:5432/roster
ROSTER_DATABASE_MIGRATION_MODE=skip

Save these values as /secure/roster-runtime.env or provide the equivalent through your hosting platform’s secret manager. Omit ROSTER_DATABASE_REPLICA_URLS for a standalone single-primary runtime. An existing database whose bootstrap administrator already exists does not need ROSTER_BOOTSTRAP_ADMIN_PASSWORD.

Run the release in this order:

# Protect local environment files before using them.
chmod 600 /secure/roster-migration.env /secure/roster-runtime.env

roster_image=advantys/roster:1.2.0

# 1. Run once with the schema-owning migration environment.
docker run --rm \
  --env-file /secure/roster-migration.env \
  "$roster_image"

# 2. Start or roll out every Roster instance with the restricted runtime URL.
docker run -d \
  --name roster \
  -p 3000:3000 \
  --env-file /secure/roster-runtime.env \
  "$roster_image"

For a standalone PostgreSQL deployment that uses local CSV connector files or other persistent runtime files, also mount a durable volume at /data. HA deployments must use shared S3 or SFTP connector storage instead of local files.

The migration job always targets the primary and does not need runtime secrets, the Enterprise license, or replica URLs. After the first successful sign-in, confirm the bootstrap administrator exists and remove ROSTER_BOOTSTRAP_ADMIN_PASSWORD from the runtime secret configuration.

The PostgreSQL migrator takes an advisory lock, so an accidentally overlapping migration job waits instead of modifying the schema concurrently. The lock is a safety net, not a replacement for a single-owner release job. If the migration job fails, do not start the new application version; inspect the migration job logs and database state first.

For a multi-instance deployment with database failover, continue with High Availability.

Changing database engines

Roster does not currently provide a supported SQLite-to-PostgreSQL or PostgreSQL-to-SQLite transfer command. Do not copy a SQLite file into a PostgreSQL deployment, point two deployments at different databases during a rolling update, or attempt to translate the physical schemas directly. The two engines expose the same Roster behavior but use different physical schemas and data types.

An existing customer upgrading from Roster 1.0.x should keep SQLite and the same /data volume unless a separately planned data-migration project has been validated for that customer. Back up the source deployment before any such project and test record counts, authentication, provider configuration, permissions, and representative Resolve history before cutover.

Backups

Store backups off-host, encrypt them at rest, define a retention policy, and test restores regularly in an isolated deployment.

SQLite backup and restore

The production image includes roster-sqlite, which uses SQLite’s online backup API to create a consistent standalone database while Roster remains running. Do not copy a live roster.db, roster.db-wal, and roster.db-shm one at a time.

Create a backup inside a running Docker container:

roster_container=roster
backup_name="roster-$(date -u +%Y-%m-%dT%H%M%SZ).db"

docker exec "$roster_container" \
  roster-sqlite backup \
  --output "/data/.roster-backups/$backup_name"

docker cp \
  "${roster_container}:/data/.roster-backups/$backup_name" \
  "./$backup_name.partial"

Verify the copied file with the same Roster image, then publish it under its final name and remove the container copy. Run the next block in the same terminal so roster_container and backup_name retain the values assigned by the backup step:

roster_image=advantys/roster:1.2.0

docker run --rm \
  --entrypoint roster-sqlite \
  --mount type=bind,src="$PWD",dst=/backups,readonly \
  "$roster_image" \
  verify --input "/backups/$backup_name.partial" --full

mv "./$backup_name.partial" "./$backup_name"

docker exec "$roster_container" \
  rm -f "/data/.roster-backups/$backup_name"

backup refuses to overwrite an existing destination. It writes a private temporary file, runs quick_check and foreign_key_check, synchronizes the file, and atomically publishes it with mode 0600. Add --full to use integrity_check, --json for machine-readable file-backup metadata, or --stall-timeout-ms <milliseconds> to change the default 30-second no-progress limit. The /data volume needs enough free space for a complete temporary database in addition to normal database and WAL growth.

Hosted containers

When the container runs on a hosting platform, use a scheduled operations runner and a binary-safe, non-TTY exec command. --stdout creates and verifies a temporary online backup inside the container, emits only database bytes to stdout, reports its SHA-256 checksum on stderr, and removes the temporary file after streaming. The platform exec transport must preserve stdout and stderr separately and must not truncate output. The following block is a transport pattern, not a literal command: replace both angle-bracketed commands with the non-interactive exec and object-storage commands for the hosting platform.

set -o pipefail

<platform-exec-no-tty> roster-sqlite backup --stdout |
  <object-store-upload-from-stdin> \
  roster/production/roster-2026-07-13T020000Z.db

For example, a Kubernetes operations runner can stream a single-instance StatefulSet backup to S3-compatible storage:

set -o pipefail

kubectl exec roster-0 -- roster-sqlite backup --stdout |
  aws s3 cp - \
  s3://company-backups/roster/production/roster-2026-07-13T020000Z.db

Keep object-storage credentials in the operations runner rather than the Roster container. Use platform-native atomic volume snapshots or a same-pod backup sidecar when the database is too large for the platform exec control plane. Download backups periodically, run roster-sqlite verify --input <absolute-path> --full, and perform an isolated restore drill.

The command exits with 0 on success, 1 for an operational failure, 2 for invalid arguments or database configuration, and 3 when verification fails. It rejects PostgreSQL and in-memory database configurations.

Complete Roster state and restore

An online database backup contains roster.db, but not connector files, worker journals, or other runtime state stored under /data. Protect that additional state with an atomic provider volume snapshot. If atomic snapshots are not available, stop Roster before archiving the complete /data directory.

Never restore an online backup into a running Roster volume. Verify the backup before downtime, retain the old volume for rollback, and restore into a new volume without old roster.db-wal or roster.db-shm files.

For Docker, first verify the downloaded backup:

roster_image=advantys/roster:1.2.0
backup_name=roster-2026-07-16T020000Z.db

docker run --rm \
  --entrypoint roster-sqlite \
  --mount type=bind,src="$PWD",dst=/backups,readonly \
  "$roster_image" \
  verify --input "/backups/$backup_name" --full

Stop the old container, create a clean replacement volume, and install the verified database with the runtime user’s ownership. Set backup_name to the downloaded file that was verified in the previous step:

roster_container=roster
roster_image=advantys/roster:1.2.0
backup_name=roster-2026-07-16T020000Z.db
restored_volume="roster-data-restored-$(date -u +%Y%m%dT%H%M%SZ)"

docker stop "$roster_container"
docker volume create "$restored_volume"

docker run --rm \
  --user root \
  --entrypoint /bin/sh \
  --env BACKUP_NAME="$backup_name" \
  --mount type=bind,src="$PWD",dst=/backups,readonly \
  --mount type=volume,src="$restored_volume",dst=/data \
  "$roster_image" \
  -c 'set -eu
      test ! -e /data/roster.db
      cp "/backups/$BACKUP_NAME" /data/roster.db
      chown node:node /data/roster.db
      chmod 600 /data/roster.db'

docker run --rm \
  --entrypoint roster-sqlite \
  --mount type=volume,src="$restored_volume",dst=/data,readonly \
  "$roster_image" \
  verify --input /data/roster.db --full

Create the replacement Roster container with the same image, environment, secrets, ports, and network configuration as the old container, but mount roster-data-restored at /data. Start exactly one instance. Confirm startup migrations complete, then test the health probes, login, and a representative read before returning traffic. Keep the stopped container and old volume until the restore is accepted.

On a hosting platform, use the equivalent controlled workflow:

1. Download the object-store backup and verify it with the exact Roster image that will perform the restore.
2. Stop or scale the SQLite workload to zero and prevent automatic restarts.
3. Provision a clean block volume with local filesystem semantics.
4. Run a one-off restore job that mounts the backup read-only and the clean volume at /data, then installs the file as /data/roster.db with mode 0600 and ownership for the image’s node user.
5. Run roster-sqlite verify --input /data/roster.db --full from a one-off job with the restored volume mounted read-only.
6. Attach the restored volume to one Roster instance, enable automatic restarts, and complete the same migration, health, login, and read checks.
7. Retain the previous volume and deployment configuration until the recovery point and application behavior are confirmed.

If recovery requires connector files or other non-database state, restore the matching atomic /data snapshot instead of combining the database backup with files from an unrelated recovery point.

Take a fresh online database backup and complete-state snapshot before image upgrades or database migrations. Store every backup off-host, encrypt it at rest, and enforce retention outside the Roster container.

PostgreSQL backups

Back up PostgreSQL with the database provider’s backup process.

• Include the database and all Roster schemas in the provider backup policy.
• Enable point-in-time recovery when the provider supports it and monitor the last successful backup and recovery window.
• Keep /data backed up when it contains connector files, worker journals, or other runtime state.
• Take a fresh database backup before image upgrades, platform changes, or database migrations.
• Restore backups into an isolated PostgreSQL 17 or 18 environment on a regular schedule; never use the production database for a restore drill.
• Verify that the restricted runtime role can use a restored database after privileged migrations have completed.
• After a restore, start one Roster instance first and verify readiness, login, representative reads and writes, worker processing, and connector access before restoring normal replica counts and traffic.

Continue with High Availability or Hosting Platforms after selecting the database.