Database behavior

Start with the connection examples in Configuration and schemas. This guide explains storage requirements, transaction composition and migration failures. Ithibati supports PostgreSQL, SQLite and MySQL; SQL Server and MariaDB are outside that scope.

Verified versions

The adapter suites have been run against these versions. They record the actual engine version, including SQLite's native library supplied by Exqlite.

These are the verified combinations, not a claim that every server and driver version has been tested. Consuming applications select their own dependencies.

PostgreSQL

Storage and schemas

Account keys may use uuid, smallint, integer or bigint, including domains over those types. Configure users_key_type: :binary_id for UUIDs or :id for integer keys. The referenced column must be unique on its own. A full unique index or primary key qualifies; a composite, partial or expression index does not. Additional INCLUDE columns are allowed.

Binary values use bytea; timestamps use :utc_datetime_usec in Ecto and microsecond-capable timestamp without time zone columns. The account identifier's type remains application-owned. For case-insensitive identifiers using citext, see Case and indexes.

Catalogue lookup uses the connection's search path when no schema prefix is supplied. Migrations use their explicit prefix or the repo's migration_default_prefix; Doctor uses the latter. Keep those settings and runtime queries aligned. Ithibati's table_prefix changes table names, such as ithibati_keys to auth_keys; it does not select a PostgreSQL schema.

Transactions

Use READ COMMITTED isolation, including for outer application transactions. Credential operations lock the account with FOR NO KEY UPDATE before making decisions across several rows. Separate statements after acquiring that lock see preceding commits, protecting recovery-code replacement and deletion of the last passkey. The lock lasts until the outermost transaction ends.

SQLite

Storage and schemas

SQLite supports the unprefixed main database. Integer account keys and UUIDs are supported. UUID storage follows the adapter-wide config :ecto_sqlite3, :binary_id_type setting: :string by default, or :binary for BLOB storage. Configure it before creating tables and keep it consistent across the application. Foreign keys must be enabled on every connection so account deletion cascades to credentials while preserving the bootstrap claim.

Ithibati's table_prefix still changes table names normally. Schema prefixes and migration_default_prefix are unsupported.

Transactions

SQLite allows one writer at a time. Ithibati starts its own credential transactions in immediate mode and reserves the writer before deciding about recovery codes or the last passkey. Locks last until the outermost transaction ends.

For application-owned transactions and Ecto.Multi, use default_transaction_mode: :immediate as shown in the configuration guide, or pass mode: :immediate to Repo.transaction/2. This reserves the writer before application reads. A nested transaction cannot upgrade an already stale outer snapshot.

An existing deferred transaction can proceed if its snapshot is still current. If another writer has committed since its earlier read, SQLite raises before deciding from stale data. Lock contention can also exceed busy_timeout and raise an adapter error.

MySQL

Storage and schemas

Use InnoDB for account and invitation tables and keep foreign_key_checks = 1. Only the repo's selected database is supported, without schema prefixes. Doctor diagnoses these requirements.

Account IDs may be UUIDs in BINARY(16) or signed/unsigned BIGINTs. Ithibati matches the foreign key's signedness to the existing account column; smaller integer widths are refused. The default MyXQL auto-increment key uses unsigned BIGINT.

Indexed credential IDs use VARBINARY(1023) and digests use VARBINARY(32), with full-column unique indexes. A prefix-only index does not qualify. Invitation timestamps must be DATETIME(6) and their token digest VARBINARY(32); Ithibati.Migration.invitation_columns/1 chooses these types.

Identifier normalization remains the changeset's responsibility. The application's column collation determines which normalized strings compare equal, including accent sensitivity. Use the same identifier semantics for invitations and accounts.

Transactions

Set READ COMMITTED on every connection before starting transactions. Ithibati checks session isolation on the active connection at each identity transaction entry, including within a caller-owned transaction. Internal updates reuse that preparation; no connection state is cached. Do not override isolation for individual transactions that call Ithibati. MySQL's default REPEATABLE READ can retain an earlier application snapshot even after a nested transaction starts and is unsupported for these operations.

MySQL has no mutation RETURNING. Ithibati locks the matching row, checks the conditional write and reads the result in one transaction. Account locks serialize recovery-code replacement and final-passkey deletion, including inside existing application transactions. Deadlocks and lock timeouts propagate as database errors.

Recovering a failed migration

MySQL commits DDL statements individually. Migrations validate application-table requirements and resolve application-index conflicts before creating owned tables, but an unexpected DDL failure can still leave a partial schema. Inspect it before retrying.

For an initial installation with no authentication data to retain, a temporary recovery migration may call Ithibati.Migration.down(version: 2) in its up/0, then retry the original installation. This removes any existing Ithibati tables and its managed application indexes. It is destructive and must not be used as an automatic repair of a populated installation. Your account and invitation tables remain application-owned.

Handling database failures

Lock timeouts, deadlocks and stale snapshots are infrastructure failures, separate from expected results such as :invalid or :last_key. Ithibati never automatically replays an application callback. If your application retries, restart the entire transaction, bound the attempts and ensure that external side effects are safe to repeat.

All three backends support Ithibati schema version 3. Keep applied migrations pinned to their original version; see Upgrading the database schema.