Database schema history up to IAM 7.1

As Airlock IAM grows and new features are added, the database schema evolves. Airlock IAM does not self-migrate the database schema, i.e., you have to upgrade the database schema manually when upgrading the IAM version.

Schema migration SQL files are available starting with IAM 7.1. See 6.2.1. Relational databases for IAM.

Instructions on how to migrate the schema from IAM 7.0 to IAM 7.1 are listed in the table below.

Database schema history (IAM 7.0 to IAM 7.1)

SQL schema definitions

The table describes the changes but does not provide the actual SQL schema definition. The SQL schema definitions for all supported databases can be found here: 6.2.1. Relational databases for IAM

Added in Version	Affected Features	Affected DBs	Description	New/affected Database Objects*
7.1	● 2nd Authentication Factors ● OAuth / OIDC	all	New index in token data model. Prevents potential performance issues.	● New index on table token for column activates_token_id
7.1	● PSD2 features ● Dynamic OAuth Clients ● Tech Client Registration ● Tech Client Adminapp REST API	all	Changes in technical clients tables for all database types.	● Removed index ccc_issuer_subject_idx ● Size increased on column client_cert_credential.subject (from 200 to 450) ● Size increased on column client_cert_credential.issuer (from 200 to 450) ● Size increased on column oauth2_attributes.cert_subject (from 200 to 450) ● Size increased on column oauth2_attributes.cert_issuer (from 200 to 450)
7.1	● mysql >= 8.0: All features	mysql	MySQL Schema: ● If using mysql >= 8.0 (recommended): Use more specific collation on all tables and a column. ● For mysql < 8.0, nothing needs to be changed. This is not recommended. See note in SQL schema file.	● MySQL Schema (mysql >= 8.0): ● Use collation utf8mb4_0900_ai_ci on all tables ● Use collation utf8mb4_bin on column fingerprint in table client_cert_credential
7.1	● MariaDB: All features	MariaDB	MariaDB Schema (new): To support more specific collations, a separate Maria DB schema has been introduced.	● New MariaDB Schema (compared to mysql schema for 7.1): ● Use collation utf8mb4_general_nopad_ci instead of utf8mb4_0900_ai_ci for all tables.
7.1-pre1	● PSD2 features ● Dynamic OAuth Clients ● Tech Client Registration ● Tech Client Adminapp REST API	all	New database tables used to store technical clients and their authentication tokens.	● New table principal (1 table + 2 indices) ● New table oauth2_attributes (1 table + 2 indices) ● New table oauth2_attribute_translation (1 table + 2 indices) ● New table client_cert_credential (1 table + 5 indices)
7.1-pre1	● Email change self-service (H2 DB only) ● OIDC Account Linking (MariaDB + MySQL only) ● GDPR Consent Enforcement	H2 MariaDB mysql	DB schema fixes: ● H2 schema: column "new_email" was on table "medusa_admin" instead of "medusa_user". ● MariaDB, MySQL: Changed date time column types on two fields. Specific character set on a table.	● H2 Schema ● Add column new_email VARCHAR(100) to table medusa_user ● Remove column new_email from table medusa_admin ● MySQL + MariaDB Schema: ● Change type of column given_at in table user_consent to DATETIME(6) ● Change type of column established_at in table account_link to DATETIME(6) ● Use specific character set utf8mb4 for table user_consent
7.1-pre1	● REST Password-Reset Self-Service	all	New field to store number of failed password-reset attempts.	● new column pwd_failed_resets on table medusa_user (non-null integer with default 0).