Database Migrations

Autentico uses explicit, versioned schema migrations. Routine migrations run automatically on startup. Major migrations require a separate, approved migration command before the server can start. Fresh installations initialize automatically.

How versioning works

Autentico uses SQLite’s built-in PRAGMA user_version to track the current schema version. Each release that changes the schema increments this version. On startup, the binary compares the database version against the version it expects and applies any pending migrations.

The schema number is independent of the release number: v3 introduces major schemas 15 (token hashing) and 17 (TOTP encryption). Later routine migrations can raise the target schema without changing that major approval. Fresh databases run the schema migrations on first start without requiring approval.

Upgrading

For routine upgrades, autentico start applies pending migrations automatically. Deploy the new binary or image and restart:

# Binary
autentico start

# Docker Compose
docker compose pull
docker compose up -d

Caution

Keep a database backup before upgrading. For a live database, use SQLite’s backup command so committed WAL changes are included:

sqlite3 /path/to/autentico.db ".backup /backup/autentico-backup.db"

Manual migrations

If you prefer to review and apply migrations manually, start with --no-auto-migrate:

autentico start --no-auto-migrate

The server will refuse to start if the database is behind, showing:

database is at version 1, this binary requires version 2 — run: autentico migrate

Then run the interactive migration command:

autentico migrate

This shows the current and target schema versions and asks you to type the target number to confirm. A major migration also describes its impact. Type anything else to cancel. If a major migration is pending, startup refuses the entire pending upgrade, including any preceding routine migrations.

Upgrading to v3: schemas 15 and 17

Schema 15 stores SHA-256 hashes of access and refresh tokens instead of the original JWTs. Existing sessions are discarded rather than converted. Schema 17 encrypts existing TOTP secrets with AUTENTICO_TOTP_ENCRYPTION_KEY, preserving enrollments, verification flags, and replay/lockout state. Users keep their existing authenticator app setup. Databases already on schema 15 or 16 also need the schema 17 upgrade; that upgrade alone preserves existing sessions. This is a major migration: updating the image alone will stop startup with instructions to run autentico migrate.

Caution

When schema 15 is pending, the upgrade invalidates all access and refresh tokens, OAuth sessions, SSO sessions, and pending login grants, including admin sessions and client-credentials tokens. Everyone must sign in again, and service clients must obtain new tokens. Users, passwords, TOTP enrollment, trusted devices, client configuration, and settings are preserved.

Stop every Autentico instance and any other process using the database before running the migration. There is no separate session-revocation step: invalidation and the schema changes happen together in the migration transaction.

Autentico rejects the old credentials after the upgrade. External applications that validate JWTs locally can still accept an existing access token until it expires; their own application sessions follow their own expiration policies.

Review registered redirect wildcards when upgrading: * now matches only one plain path segment. Broader patterns that previously matched nested paths or query content are rejected. Replace them with concrete callback URLs or the documented single-segment form. For the OpenID Foundation suite, replace the broad /test/*/callback pattern with /test/a/*/callback, or register the exact /test/a/<ALIAS>/callback for the selected alias.

Introspection callers must also meet the updated authorization policy: use authenticated confidential-client credentials or an admin access token with the autentico-admin audience. Public client_id values and admin-user tokens issued only for other apps no longer authorize introspection. See introspection authentication.

Immediate account deletion now requires current_password for password accounts. Passwordless accounts submit an admin-reviewed deletion request, even when allow_self_service_deletion is enabled. API clients must handle 204 (deleted) and 201 (pending review); the account UI selects the flow using the profile response’s has_password field.

Federation now checks every outbound connection after DNS resolution. Private, loopback, link-local and shared-address endpoints are blocked for discovery, token exchange and signing-key retrieval. A private issuer may still be saved, but cannot be used for login; expose a public endpoint before upgrading.

Magic-link requests now share a one-minute per-account email cooldown across clients. Resending keeps the original expiry and failed-code budget. Five incorrect codes block both credentials and further emails until the window expires. See magic-link limits. No additional schema migration is needed for this change.

Schema 18 is a routine additive migration that records the completed local MFA method on browser SSO sessions. It runs automatically on startup when no major migration is pending, and needs no new key or major approval. Existing sessions and TOTP enrollments are preserved. Older sessions have no MFA evidence: when local policy requires MFA, users must authenticate again before SSO or consent can issue a code. Password, signup, email-verification, magic-link, passkey and federation logins now share the same MFA and consent checks. See MFA policy.

Schema 19 adds an indexed table for pending federation logins. This routine migration runs automatically from schema 18 when auto-migration is enabled; it preserves existing tokens, sessions, TOTP ciphertext and enrollment/replay/lockout state. No new key or major approval is required. Keep the existing configuration and secrets for restart continuity. In-flight federation links from the previous version must restart sign-in: callbacks now require the initiating browser cookie, an unexpired pending flow and single-use consumption. See federation login protection.

Schema 20 separates public trusted-device IDs from cookie credentials. It hashes existing cookie values and assigns independent management IDs, preserving existing cookies, device metadata and expiration, tokens, sessions, and TOTP enrollments. This routine upgrade applies automatically from schema 19 unless auto-migration is disabled. No new key or major approval is needed; unattended deployments continue using --approve-major=15,17. Interactive migration now asks you to type 20. Refresh any open trusted-device list before revoking a device, since its public ID changes once.

Preserving existing trust also preserves any cookie credentials exposed as device IDs by an older account API. A copied old ID remains usable until its original expiry, even though its new public management ID changes. If you suspect exposure, revoke affected trusted devices before upgrading, or remove their rows from trusted_devices while the service is stopped. Those browsers must complete MFA again. Changing the public IDs alone does not revoke existing cookies.

Consent and authentication-time hardening needs no further schema migration or key. Consent pages open during an upgrade must restart authorization. Older refresh tokens recover authentication time from a linked browser session or original unrotated password grant. Already-rotated tokens without a browser link require a new login because the original time cannot be reconstructed. Other sessions are preserved. See refresh token compatibility.

Client registration now infers public clients from token_endpoint_auth_method: "none" when client_type is omitted and rejects contradictory metadata. Existing confidential clients continue requiring their current secrets; read/list responses correct their reported method and an ordinary client edit repairs stored metadata. This requires no schema migration, key or secret rotation. See authentication metadata and existing clients.

OAuth client authentication now accepts credentials through one Basic or POST-body method. Confidential device clients authenticate at both device authorization and token polling. Update integrations that send credentials in URLs, combine methods, or pass raw reserved characters in Basic credentials; form-encode each component before Base64-encoding. This needs no new database migration or key. See client authentication.

PKCE clients must explicitly send code_challenge_method=S256 when using a hashed challenge. An omitted method now correctly means plain and is rejected by the default S256 enforcement policy. Integrations that relied on the previous implicit S256 behavior must update that parameter. Open login forms that omitted it should restart authorization. Already-issued unlabelled codes retain their original S256 binding until expiry; new codes always store an explicit method. This correction needs no database migration or new key.

Prepare the TOTP encryption key

Generate and persist the key before running migrate. A missing, empty or invalid key makes the command exit with an error before changing the database. The same key must be available to the migration command and every later server start. It is separate from the JWT signing key.

If the deployment has an existing .env, run the updated initializer in the same directory:

autentico init

It adds a random base64-encoded 32-byte AUTENTICO_TOTP_ENCRYPTION_KEY only when missing. Existing settings and signing secrets are preserved; repeating init keeps the same key. Fresh init and fresh start --auto-setup configurations include the key automatically. Startup does not silently add a missing key to an existing configuration.

For deployments managed entirely through environment variables or a secret manager, generate the key once:

openssl rand -base64 32

Save the result as AUTENTICO_TOTP_ENCRYPTION_KEY in the deployment configuration or secret manager, then pass it to both migration and application containers. With Compose, a host .env variable must be explicitly forwarded, for example:

services:
  autentico:
    environment:
      AUTENTICO_TOTP_ENCRYPTION_KEY: ${AUTENTICO_TOTP_ENCRYPTION_KEY:?Set the persistent TOTP encryption key}

For a container using the image’s persistent /app/data/.env, use the init command shown below instead of creating a second configuration file on the host.

Keep a protected copy of this key with your recovery material, separate from database-only exports. Do not generate a new key on each deployment or replace it after encryption: a different key cannot decrypt existing enrollments. If the key is lost, restore the original key from backup. start and migrate validate existing encrypted enrollments before applying routine schema upgrades. A wrong key or corrupt encrypted enrollment stops the upgrade without advancing the schema or resetting MFA. Restore the original key and retry. The migration itself preserves the original seeds; it does not reset enrollments or ask users to scan new QR codes.

Docker Compose

With the service named autentico and the existing database volume mounted:

docker compose pull autentico
docker compose stop autentico
# For the image's existing persistent .env; adds only the missing TOTP key.
docker compose run --rm autentico init
docker compose run --rm autentico migrate

If schema 15 is pending, the command warns that everyone will be signed out. It also explains that schema 17 preserves and encrypts TOTP enrollments. Type the target schema number shown by the command to approve the upgrade (currently 20). After it succeeds:

docker compose up -d autentico

Use the same image, environment, and database volume for the migration container and the application. docker compose run starts a separate container, so it works even when the application container cannot start because migration is required.

Unattended deployments

Approve the specific schema number, not the application release:

docker compose run --rm -T autentico migrate --approve-major=15,17 \
  && docker compose up -d autentico

Stop the old application and configure the TOTP encryption key before running this command. --approve-major=15,17 explicitly accepts session invalidation for schema 15 and the protected encryption upgrade for schema 17 and skips the interactive prompt. Approval for 15 alone does not approve 17. An installation already on schema 15 or 16 only needs --approve-major=17. Unknown major schema numbers are rejected. Repeating the command after a successful upgrade is safe and reports that the database is already up to date.

For a binary deployment, stop the service, run autentico migrate --approve-major=15,17, and restart the service only if the command succeeds. Keep this as a deployment step; the normal container command remains start. The same approval keeps later routine-only upgrades noninteractive; a future major migration needs its own schema approval.

Backup, locking, and recovery

Before an existing database undergoes a major upgrade, Autentico:

1. Acquires an exclusive OS lock using the database’s .lock sidecar file.
2. Checkpoints committed WAL data into the database and verifies the checkpoint succeeded.
3. Closes SQLite and creates a backup beside the database, such as autentico.db.pre-v17-<random>.db, with owner-only permissions.
4. Verifies the backup’s checksum, schema version, and SQLite integrity, then writes a durable .migration.json recovery record.
5. Applies the migrations. When upgrading through schema 17, rebuilds the database with SQLite VACUUM after encryption to remove plaintext fragments from historical free pages. This rebuild remains protected by the backup and recovery record.
6. Checkpoints the result and removes the recovery record after success.

If migration fails, Autentico closes SQLite, restores the pre-upgrade backup, and exits with an error. Restoration includes the original tokens and sessions, file permissions, and Unix ownership. The deployment must remain stopped until the failure is resolved and migration succeeds. If the process is interrupted, server startup refuses to use the database while the recovery record remains. Running autentico migrate restores the verified backup before requesting approval to retry; automation can retry with --approve-major=15,17.

On Unix, a migration run as root assigns the lock file the database owner and applicable read/write permissions so the service user can restart afterward. The lock file stays in place to keep all processes locking the same file.

An OS lock is released when its process exits. A leftover .lock file alone does not mean a migration is running. Do not remove a .migration.json recovery record to bypass recovery. If the backup is missing or damaged, automatic recovery stops and reports the problem rather than replacing the database with unverified data.

The advisory lock coordinates binaries supporting this migration protocol. Older binaries and external SQLite tools do not honor it; stop them yourself before migrating.

Caution

Backups contain the original plaintext TOTP seeds and may contain old plaintext tokens; they must remain protected. Budget space for the full backup, up to twice the database size in additional space for the SQLite rebuild, and recovery staging files. The rebuild sanitizes the upgraded SQLite file; it cannot erase old backups, filesystem snapshots, or underlying storage copies. Keep the backup until the upgrade has been verified, then remove it according to your backup retention policy. To roll back the application release, stop all database users and restore the matching pre-upgrade database first; an older binary cannot use schema 17 or decrypt its TOTP storage.

Tip

If the database is already at the latest version, startup proceeds normally with no migration work.

Already up to date

If you run autentico migrate and the database is already at the latest version:

Already up to date.

No changes are made.