Passkey & WebAuthn Database: Guide for Columns & Fields

1. Introduction: Database Schema Recommendations for Passkeys

The implementation of WebAuthn and passkeys into applications promises enhanced security and a user-friendly alternative to traditional password- based logins. At the heart of a successful WebAuthn- / passkey-based application lie several components. Among these, the database has a central position, serving as the repository for all crucial data related to users, their devices, and the credentials.

Surprisingly, an analysis of provided passkey documentation and tutorials reveals a gap in the available resources for developers to define passkey database schemas correctly. While numerous tutorials offer guidance on setting up passkeys, many either assume the use of a passkey service, eliminating the need to think deeper over database schemas, or they present oversimplified recommendations, often limiting their advice to a single database technology.

Ben Gould

Head of Engineering

I’ve built hundreds of integrations in my time, including quite a few with identity providers and I’ve never been so impressed with a developer experience as I have been with Corbado.

10,000+ devs trust Corbado & make the Internet safer with passkeys. Got questions? We’ve written 150+ blog posts on passkeys.

Join Passkeys Community

The consequence of this reference gap is that developers have to dig deep into database documentations and combine it with the requirements outlined by the (already complex) WebAuthn specifications. Moreover, failing to properly design the database structure can lead to significant challenges down the road. Developers might find themselves fixing bugs about database fields or be lost in the encoding / decoding hell of WebAuthn (trust us, we also spent countless hours on these steps already).

That's why we recognized that coming up with a solid database structure is the foundational step for any passkey-based app. This guide aims provides recommendations for optimal passkey database schemas for the following database technologies (with the list continuously being updated and extended):

• MySQL
• PostgreSQL
• SQLite
• MongoDB

2. What's Complex about Database Schemas for Passkeys?

The complexity of setting up a proper database structure for passkey-based apps arises from some factors that developers must take care of.

Become part of our Passkeys Community for updates and support.

Join

2.1 Different WebAuthn Server Library Implementations

First and foremost, the landscape of WebAuthn server libraries is diverse, with each library offering different approaches to integrating WebAuthn into a system. This diversity, while beneficial in providing options, complicates the process of determining the most appropriate database schema for your application. Each library may interact with the database in slightly different ways, requiring a nuanced understanding of both the library's inner workings and the database's capabilities.

In the following section, we provide an overview of some popular WebAuthn server libraries along with, where available, specific guides and documentation for them. Some WebAuthn server libraries clearly define the actual storage repository, while others leave the storage layer entirely up to the developer. It's important to note that these WebAuthn server libraries primarily specify the format of the credential table, but leave the specification of the user table to the developer.

Developers are frequently left to figure out the storage implementation on their own by the WebAuthn server library, with examples typically limited to in-memory code.

2.2 WebAuthn Standard Requires Diverse Data Formats

The WebAuthn standard itself specifies certain formats for data, such as cryptographic keys, challenge responses, and attestation objects. These formats are optimized for security and interoperability but present a challenge when it comes to storing them in a database. Different databases have different capabilities and limitations regarding data types and encoding, necessitating a careful approach to how data is encoded and stored. For example, storing binary data like public keys may be straightforward in one database but require a specialized binary data type or encoding in another.

2.3 SQL vs. NoSQL Debate

While theoretically, any database could be used to store WebAuthn data, there's a strong preference within the developer community for using SQL and relational database management systems (RDBMS). The reasoning behind this preference lies in the transactional nature of WebAuthn operations, where the integrity and consistency of data are extremely important. Relational databases are great in maintaining transactional integrity, making them a favored choice.

However, this doesn't eliminate the potential for using NoSQL databases , which might be preferred for their scalability and flexibility in handling unstructured data. This choice adds another layer of complexity in deciding the right database system for your WebAuthn implementation.

2.4 Encoding Challenges in WebAuthn Tables

Developers often encounter encoding issues when implementing WebAuthn, stemming from the need to store and retrieve complex data types. The correct encoding and decoding of data are crucial for the integrity and security of the authentication process. These challenges are not trivial; they require a deep understanding of both the WebAuthn protocol and the chosen database's handling of data types and encoding. One of the most commons misunderstandings we encountered is the difference between Base64 and Base64URL encodings.

We had to obtain our learnings also the hard way by going through cumbersome steps and research but can now provide firsthand experiences and lessons learned in developing passkey-based solutions.

The following content is not prescriptive but rather a starting point. It's designed to help developers with the knowledge to make informed decisions that align with the unique requirements of their WebAuthn implementations.

3. Recommended Database Schemas for Passkeys

Below, we take a closer look into the components and considerations crucial for setting up a database that supports passkey authentication, focusing on the essential tables, their relationships, and the overarching security considerations.

3.1 Required Tables and Columns

To effectively manage passkey and WebAuthn data, a database should minimally include the following tables:

• Users Table: Central to the authentication process, this table stores information about the users of your application. It typically includes columns for user identification (user_id), username (username), email address (email), and perhaps a display name or other user-related data. The user_id acts as a primary key.
• Credentials Table: This table stores the cryptographic credentials (public keys of a passkey) associated with each user, facilitating the authentication process. Essential columns include a unique identifier for each credential (credential_id), a reference to the user_id to link back to the user table, the public key (public_key). The credential's counter (sign_count) and timestamps for creation and last use are optional but often recommended. The credential_id serves as the primary key.

Optionally, the following tables can be added to the database to enhance the overall developer, debugging and user experience.

• Device Table (Optional): For applications managing user devices, this table can store device-specific information linked to user authentication. It might include device identifiers, type, and reference to the user_id and credential_id.
• Challenge Table(Optional): This table logs the challenges sent to users during the authentication process. It includes a challenge ID, the associated user_id , and timestamps. Depending on the specific implementation of the application, this information can also be stored in a session as its only temporarily needed.
• Audit or Log Table (Optional): This table logs authentication events and attempts. It stores them together with timestamps, user_id , event descriptions, and outcomes. This table is mainly neeeded for security and compliance reasons.

3.2 Relationships Between Tables

The relationships between tables are important for maintaining data integrity and facilitating database queries:

• Foreign Keys: These enforce the relationships between users and their credentials or devices. For example, the user_id column in the credentials and device tables should be a foreign key linking back to the users table.
• Primary Keys : Each table should have a primary key (user_id , credential_id , etc.) that uniquely identifies each record, ensuring data integrity.
• Indexes : Proper indexing is crucial for optimizing query performance. Indexes should be considered for frequently queried columns, such as user_id in the credentials table or email in the users table.

3.3 Security Considerations

When designing your database for passkey authentication, you need to make sure that proper security measures are in place:

• Data Encryption: Sensitive data, especially public keys and personal information, should be encrypted at rest and in transit. This protects against data breaches and ensures compliance with privacy regulations.
• Access Control: Implement strict access control policies for your database. Only authorized personnel and systems should have access to the authentication data, and roles should be clearly defined to limit access based on necessity.
• Audit Trails: The audit or log table plays a crucial role in security, providing a trail of all authentication attempts and system changes. This not only aids in identifying potential security incidents but also supports compliance with regulatory requirements.

By carefully considering these aspects of database structure and security, developers can create a robust foundation for passkey authentication systems. This foundation not only supports the technical requirements of WebAuthn but also aligns with best practices for data security and privacy, ensuring a secure and user-friendly authentication experience for all users.

Subscribe to our Passkeys Substack for the latest news, insights and strategies.

Subscribe

4. Recommendations for Different Database Technologies

In the following, well provide recommendations for database schemes in different database technologies. As of March 2024, we provide recommendations for MySQL, PostgreSQL, SQLite and MongoDB(more database technologies will follow soon or are available upon request).

4.1 Passkey Schemas for Relational Databases

Relational databases, known for their robustness and adherence to ACID (Atomicity, Consistency, Isolation, Durability) properties, are highly recommended for managing passkey authentication data.

4.1.1 Passkeys in MySQL

MySQL is an open-source relational database management system (RDBMS) that uses SQL for managing and manipulating relational databases. It's widely recognized for its reliability, efficiency, and ease of use.

MySQL supports a wide range of applications, from small to large enterprise applications, offering features such as transactional and non- transactional table types, robust data security, replication, and partitioning for scalability. It's highly favored in web applications and is a core component of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) stack, a popular open-source software stack used for web development.

4.1.1.1 MySQL: Users Table Schema

For the users table in MySQL, we recommend the following database scheme:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT, 
  userName VARCHAR(50) NOT NULL UNIQUE, 
  email VARCHAR(50), 
  phoneNumber VARCHAR(25), 
  displayName VARCHAR(25), 
  registrationDate DATETIME DEFAULT CURRENT_TIMESTAMP, 
  lastUpdatedDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  lastLoginDate DATETIME, 
  PRIMARY KEY (id)
);

• id: Primary key, auto-increment for unique identification.
• userName: Unique username; VARCHAR(50) provides ample space for user names.
• email: User's email address; VARCHAR(50) is standard for email lengths.
• phoneNumber: User's phone number; VARCHAR(25) accommodates international formats.
• displayName: The name displayed within applications; VARCHAR(25) allows for most names.
• registrationDate: Timestamp for the creation of the users. DATETIME accurately records dates and times.
• lastUpdatedDate: Timestamp for last update of the user. DATETIME accurately records dates and times.
• lastLoginDate: Timestamp for last login of the user. DATETIME accurately records dates and times.

4.1.1.2 MySQL: WebAuthn Credentials Table Schema

For the credentials table in MySQL, we recommend the following database scheme:

CREATE TABLE credentials (
  id INT NOT NULL AUTO_INCREMENT, 
  userId INT NOT NULL, 
  credentialId VARCHAR(1023) NOT NULL UNIQUE, 
  publicKey TEXT, 
  attestationType ENUM('direct', 'indirect', 'none') NOT NULL, 
  AAGUID CHAR(36) DEFAULT '00000000-0000-0000-0000-000000000000', 
  signatureCount INT, 
  creationDate DATETIME DEFAULT CURRENT_TIMESTAMP, 
  lastUsedDate DATETIME, 
  lastUpdatedDate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  type VARCHAR(25), 
  transports 
  SET('usb', 'nfc', 'ble', 'internal', ‘hybrid’), 
  backupEligible BOOLEAN DEFAULT FALSE, 
  backupState BOOLEAN DEFAULT FALSE, 
  PRIMARY KEY (id), 
  INDEX userId_index (userId), 
  FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE
);

• id: Primary key, auto-increment for unique identification of credentials.
• userId: Foreign key to the user; INT matches the user ID type.
• credentialId: A unique identifier for the credential; VARCHAR(1023) to accommodate various credential ID formats.
• publicKey: Store cryptographic keys. TEXT for publicKey due to their variable length.
• attestationType: Indicates the attestation type. The ENUM type is used here with the options 'direct', 'indirect', and 'none' to strictly enforce these values in the database level.
• AAGUID: A unique identifier for the authenticator model. CHAR(36) is chosen to store UUIDs, with a default value representing an unspecified AAGUID.
• signatureCount: Counter to prevent replay attacks; INT suffices.
• creationDate: Timestamp for the creation of the credential. DATETIME accurately records dates and times.
• lastUsedDate: Timestamp for last usage of the credential in an authentication process. DATETIME accurately records dates and times.
• lastUpdatedDate: Timestamp for last update of the credential. DATETIME accurately records dates and times.
• type: Credential type (e.g., platform, cross-platform); VARCHAR(25) for short type descriptions.
• transports: Represents the transport methods supported by the credential. The SET type is suitable here, as it allows for storing multiple values from a predefined list ('usb', 'nfc', 'ble', 'internal', 'hybrid') in a single field.
• backupEligible: Indicates whether the credential is eligible for backup. BOOLEAN is chosen to represent a true/false state.
• backupState: Represents the current backup state of the credential. Similar to backupEligible, BOOLEAN is used for its simplicity in handling binary states.
• INDEX userId_index (userId): Index on userId for faster lookup.

4.1.2 Passkeys in PostgreSQL

PostgreSQL is an advanced, open-source relational database management system (RDBMS) that emphasizes extensibility and SQL compliance. It offers a sophisticated data management system, supporting complex queries, foreign keys, triggers, views, transactional integrity, and multiversion concurrency control. PostgreSQL is highly respected for its proven architecture, reliability, data integrity, robust feature set, and its dedication to open standards and extensibility.

Widely adopted for a broad spectrum of applications, PostgreSQL is suitable for small projects as well as large, complex systems. It supports advanced data types, sophisticated locking mechanisms, and is well-suited for handling large volumes of data with mechanisms like table partitioning and replication. PostgreSQL is a key component of many software stacks and is often chosen for web applications, analytics, and geospatial databases. Its community-driven development model and its focus on performance, security, and compliance with the SQL standard make it a preferred choice for developers and enterprises alike.

4.1.2.1 PostgreSQL: Users Table Schema

For the users table in PostgreSQL, we recommend the following database scheme:

CREATE TABLE users (
  id SERIAL PRIMARY KEY, 
  username VARCHAR(50) NOT NULL UNIQUE, 
  email VARCHAR(50), 
  phone_number VARCHAR(25), 
  display_name VARCHAR(25), 
  registration_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 
  last_updated_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 
  last_login_date TIMESTAMP WITH TIME ZONE, 
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

• id: Primary key for unique identification. SERIAL is used for auto-incrementing integer columns.
• user_name: Unique username; VARCHAR(50) provides ample space for usernames.
• email: User's email address; VARCHAR(50) is standard for email lengths.
• phone_number: User's phone number; VARCHAR(25) accommodates international formats.
• display_name: The name displayed within applications; VARCHAR(25) allows for most names.
• registration_date: Timestamp for the creation of the users. TIMESTAMP WITH TIME ZONE is chosen to store both the timestamp and timezone information.
• last_updated_date: Timestamp for last update of the user. TIMESTAMP WITH TIME ZONE is chosen to store both the timestamp and timezone information.
• last_login_date: Timestamp for last login of the user. TIMESTAMP WITH TIME ZONE is chosen to store both the timestamp and timezone information.

4.1.2.2 PostgreSQL: WebAuthn Credentials Table Schema

For the credentials table in PostgreSQL, we recommend the following database scheme:

CREATE TYPE attestation_type AS ENUM ('direct', 'indirect', 'none');
CREATE TABLE credentials (
  id SERIAL, 
  user_id INT NOT NULL, 
  credential_id VARCHAR(1023) NOT NULL UNIQUE, 
  public_key TEXT, 
  attestation_type ENUM('direct', 'indirect', 'none') NOT NULL, 
  aaguid CHAR(36) DEFAULT '00000000-0000-0000-0000-000000000000', 
  signature_count INT, 
  creation_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 
  last_used_date TIMESTAMP WITH TIME ZONE, 
  last_updated_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 
  type VARCHAR(25), 
  transports TEXT[], 
  backup_eligible BOOLEAN DEFAULT FALSE, 
  backup_state BOOLEAN DEFAULT FALSE, 
  PRIMARY KEY (id), 
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

• id: Primary key t for unique identification of credentials. SERIAL is used for auto-incrementing integer columns.
• user_id: Foreign key to the user; INT matches the user ID type.
• credential_id: A unique identifier for the credential; VARCHAR(1023) to accommodate various credential ID formats.
• public_key: Store cryptographic keys. TEXT for public_key due to their variable length
• attestation_type: Indicates the attestation type. Note that the ENUM type needs to be created beforehand.
• aaguid: A unique identifier for the authenticator model. CHAR(36) is used for fixed-length character data, suitable for UUIDs.
• signature_count: Counter to prevent replay attacks; INT suffices.
• creation_date: Timestamp for the creation of the credential. Using TIMESTAMP WITH TIME ZONE to include timezone information.
• last_used_date: Timestamp for last usage of the credential in an authentication process. Using TIMESTAMP WITH TIME ZONE to include timezone information.
• last_updated_date: Timestamp for last update of the credential. Using TIMESTAMP WITH TIME ZONE to include timezone information.
• type: Credential type (e.g., platform, cross-platform); VARCHAR(25) for short type descriptions.
• transports: Represents the transport methods supported by the credential. TEXT[] is used to store an array of text values. This allows storing multiple transport values in a single column.
• backup_eligible: Indicates whether the credential is eligible for backup. BOOLEAN is a is chosen to represent a true/false state.
• backup_state: Represents the current backup state of the credential. Similar to backup_eligible, BOOLEAN is used for its simplicity in handling binary states.
• INDEX user_id_index (user_id): Index on user_id for faster lookup.

4.1.3 Passkeys in Oracle

Well soon provide more details regarding the recommended users and credentials table for Oracle. If you have urgent questions, feel free to reach out.

4.1.4 Passkeys in Microsoft SQL Server

Well soon provide more details regarding the recommended users and credentials table for Microsoft SQL Server. If you have urgent questions, feel free to reach out.

4.1.5 Passkeys in SQLite

SQLite is a renowned, open-source, serverless relational database management system (RDBMS) distinguished by its simplicity, portability, and efficient operation in a wide range of environments. Unlike traditional client-server database systems, SQLite is embedded into the end program, providing a self-contained, zero-configuration, transactional SQL database engine. This unique architecture makes it an ideal choice for devices with limited resources, mobile applications, application file formats, and temporary databases for testing or as a backend for prototype development.

SQLite supports most of the query language features found in SQL92 (SQL2) standard, including transactions, subqueries, views, and triggers. Despite its compact nature, it offers a robust set of features and can handle database sizes of up to 140 terabytes. SQLite is widely used in applications of all sizes, from small standalone applications to large web applications, thanks to its reliability, ease of use, and the fact that it requires no setup or administration, making it perfect for situations where simplicity and minimal overhead are critical.

4.1.5.1 SQLite: Users Table Schema

For the users table in SQLite, we recommend the following database scheme:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT,
  phone_number TEXT,
  display_name TEXT,
  registration_date TEXT DEFAULT (datetime('now')),
  last_updated_date TEXT DEFAULT (datetime('now')),
  last_login_date TEXT
);

• id: INTEGER PRIMARY KEY AUTOINCREMENT is used for auto-incrementing integer columns, serving as the primary key.
• username: Unique username; TEXT provides ample space for usernames.
• email: User's email address; TEXT is standard for email lengths.
• phone_number: User's phone number; TEXT accommodates international formats.
• display_name: The name displayed within applications; TEXT allows for names.
• registration_date: Timestamp for the creation of the users. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS), and datetime('now') is used to default to the current timestamp.
• last_updated_date: Timestamp for last update of the user. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS), and datetime('now') is used to default to the current timestamp.
• last_login_date: Timestamp for last login of the user. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS).

4.1.5.2 SQLite: WebAuthn Credentials Table Schema

For the credentials table in SQLite, we recommend the following database scheme:

CREATE TABLE credentials (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  credential_id TEXT NOT NULL UNIQUE,
  public_key TEXT,
  attestation_type TEXT NOT NULL,
  aaguid TEXT DEFAULT '00000000-0000-0000-0000-000000000000',
  signature_count INTEGER,
  creation_date TEXT DEFAULT (datetime('now')),
  last_used_date TEXT,
  last_updated_date TEXT DEFAULT (datetime('now')),
  type TEXT,
  transports TEXT,
  backup_eligible INTEGER DEFAULT 0,
  backup_state INTEGER DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

• id: INTEGER PRIMARY KEY AUTOINCREMENT is used for auto-incrementing integer columns, serving as the primary key.
• user_id: Foreign key to the user; INTEGER matches the user ID type.
• credential_id: A unique identifier for the credential; TEXT to accommodate various credential ID formats.
• public_key: Store cryptographic keys. TEXT for public_key due to their variable length.
• attestation_type: Indicates the attestation type. The TEXT type is used here.
• aaguid: A unique identifier for the authenticator model. TEXT is chosen to store UUIDs, with a default value representing an unspecified AAGUID.
• signature_count: Counter to prevent replay attacks; INTEGER suffices.
• creation_date: Timestamp for creation of the credential. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS), and datetime('now') is used to default to the current timestamp.
• last_used_date: Timestamp for last usage of the credential in an authentication process. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS).
• last_updated_date: Timestamp for last update of the credential. Date is stored as TEXT in ISO8601 format (YYYY-MM-DD HH:MM:SS.SSS), and datetime('now') is used to default to the current timestamp.
• type: Credential type (e.g., platform, cross-platform); TEXT for short type descriptions.
• transports: Represents the transport methods supported by the credential. The TEXT type is suitable here.
• backup_eligible: Indicates whether the credential is eligible for backup. INTEGER is used, where 0 represents FALSE and 1 represents TRUE.
• backup_state: Represents the current backup state of the credential. Similar to backupEligible, INTEGER is used, where 0 represents FALSE and 1 represents TRUE.
• INDEX user_id_index (user_id): Index on user_id for faster lookup.

4.1.6 Passkeys in IBM DB2

Well soon provide more details regarding the recommended users and credentials table forIBM DB2. If you have urgent questions, feel free to reach out.

4.1.7 Passkeys in MariaDB

Well soon provide more details regarding the recommended users and credentials table for MariaDB. If you have urgent questions, feel free to reach out.

4.2 Passkey Schemes for NoSQL Databases

In the following, we provide passkey table schemes for NoSQL databases.

4.2.1 Passkeys in MongoDB

MongoDB is a powerful, open-source, NoSQL database management system known for its flexibility, scalability, and performance. It uses a document-oriented approach, storing data in JSON-like documents with dynamic schemas (MongoDB calls this format BSON), enabling the integration of data in certain types of applications more easily and quickly than traditional RDBMS systems. MongoDB is designed to meet the demands of modern apps with a powerful query language, secondary indexes, and real-time aggregation.

It excels in handling large volumes of data and supports complex queries, full-text search, graph processing, and other advanced data manipulation. MongoDB's architecture features collections and documents instead of tables and rows, making it highly adaptable to changes in data structure. It is also well-suited for distributed data environments, offering out-of-the-box replication and sharding capabilities for high availability and horizontal scaling.

Widely adopted for building scalable web applications, MongoDB is a key component of the MEAN (MongoDB, Express.js, AngularJS, Node.js) or MERN (MongoDB, Express.js, React, Node.js) stack. MongoDB's document model maps naturally to object-oriented programming, making it particularly popular among developers working in agile environments that require rapid iteration and flexible data models.

4.2.1.1 MongoDB: Users Collection Structure

For the users collection with schema validation in MongoDB, we recommend the following database scheme:

db.createCollection("users", {
	validator: {
		$jsonSchema: {
			bsonType: "object",
			required: ["username"],
			properties: {
				_id: {
					bsonType: "objectId"
				},
				username: {
					bsonType: "string",
					uniqueItems: true
				},
				email: {
					bsonType: "string"
				},
				phone_number: {
					bsonType: "string"
				},
				display_name: {
					bsonType: "string"
				},
				registration_date: {
					bsonType: "date",
					default: "$$NOW"
				},
				last_updated_date: {
					bsonType: "date",
					default: "$$NOW"
				},
				last_login_date: {
					bsonType: "date"
				}
			}
		}
	}
});

• _id: MongoDB automatically generates a unique _id field of objectId type for each document. This objectId is used as the primary key, providing a unique identifier for each document in the collection.
• username: Stored as a string, this field represents the user's unique username. MongoDB does not enforce a character limit, offering flexibility in string length.
• email: Also stored as a string, this field captures the user's email address. Like username, there's no enforced length limit, reflecting MongoDB's schema flexibility.
• phone_number: This field, stored as a string, contains the user's phone number. MongoDB's string type can easily accommodate international phone number formats without a predefined length.
• display_name: The display_name field, stored as a string, is used for the name displayed within applications. The absence of a length limit allows for a wide range of name lengths.
• registration_date: Utilizing MongoDB's date type, this field records the timestamp for when the user account was created. MongoDB stores dates in ISODate format, offering millisecond precision. Actual default setting to the current date/time would occur at the application level or during document insertion, not directly in the schema validation.
• last_updated_date: This field also uses the date type to record the last time the user's information was updated, following the same principles as registration_date.
• last_login_date: Similar to the other date fields, last_login_date is stored as a date, tracking the most recent login timestamp.

4.2.1.2 MongoDB: Credentials Collection Structure

For the credentials collection with schema validation in MongoDB, we recommend the following database scheme.

db.createCollection("credentials", {
	validator: {
		$jsonSchema: {
			bsonType: "object",
			required: ["user_id", "credential_id", "attestation_type"],
			properties: {
				_id: {
					bsonType: "objectId"
				},
				user_id: {
					bsonType: "objectId",
					description: "must be a valid ObjectId and is required"
				},
				credential_id: {
					bsonType: "string",
					uniqueItems: true
				},
				public_key: {
					bsonType: "string"
				},
				attestation_type: {
					enum: ["direct", "indirect", "none"],
					description: "can only be one of the enum values and is required"
				},
				aaguid: {
					bsonType: "string",
					default: "00000000-0000-0000-0000-000000000000"
				},
				signature_count: {
					bsonType: "int"
				},
				creation_date: {
					bsonType: "date",
					default: "$$NOW"
				},
				last_used_date: {
					bsonType: "date"
				},
				last_updated_date: {
					bsonType: "date",
					default: "$$NOW"
				},
				type: {
					bsonType: "string"
				},
				transports: {
					bsonType: "array",
					items: {
						bsonType: "string"
					}
				},
				backup_eligible: {
					bsonType: "bool"
				},
				backup_state: {
					bsonType: "bool"
				}
			}
		}
	}
});

• _id: As with the users collection, MongoDB automatically generates a unique _id for each document in the credentials collection, serving as the primary key.
• user_id: This objectId field references the _id from the users collection, establishing a relationship between the two collections while ensuring referential integrity.
• credential_id: Stored as a string, this unique identifier for the credential does not have a length limit, accommodating various credential ID formats.
• public_key: The public_key field is stored as a string, capable of storing cryptographic keys without a predefined length, reflecting the variable nature of public keys.
• attestation_type: Defined with an enum constraint, this field specifies the attestation type and must be one of the predefined options (direct, indirect, none).
• aaguid: Stored as a string, the aaguid field represents a unique identifier for the authenticator model, with a default value for an unspecified AAGUID.
• signature_count: This integer (int) field stores a counter to help prevent replay attacks, suitable for storing numerical values.
• creation_date: Using the date type, this field records the timestamp for the credential's creation, with MongoDB's ISODate format providing precise time recording.
• last_used_date: Also a date, this field notes the last time the credential was used in authentication, adhering to the same date handling as creation_date.
• last_updated_date: This date field marks the last update to the credential, following the same principles as the other date fields.
• type: Stored as a string, the type field indicates the credential type, such as platform or cross-platform, without imposing a character limit.
• transports: This field is an array of strings, demonstrating MongoDB's capability to natively handle complex data types like arrays. It represents the transport methods supported by the credential.
• backup_eligible: A boolean (bool) value, this field indicates whether the credential is eligible for backup.
• backup_state: Also a boolean (bool), this field represents the current backup state of the credential, showing a binary state in a straightforward manner.

4.2.2 Passkeys in Cassandra

Well soon provide more details regarding the recommended users and credentials table for Cassandra. If you have urgent questions, feel free to reach out.

4.2.3 Passkeys in DynamoDB

Well soon provide more details regarding the recommended users and credentials table for DynamoDB. If you have urgent questions, feel free to reach out.

4.2.4 Passkeys in Couchbase

Well soon provide more details regarding the recommended users and credentials table for Couchbase. If you have urgent questions, feel free to reach out.

5. Conclusion: Passkeys Database Storage

This guide has explored recommendations for configuring databases for passkey authentication, addressing the complexities of database structures , and recommending optimal schemas for MySQL, PostgreSQL, SQLite and MongoDB. By embracing the guidelines and considerations presented, developers can save time by avoiding the need to devise their own database structures. While these are only recommendations and can be adjusted as needed, they serve as proven methods or a starting point to save time. As technology progresses, staying informed and adaptable is crucial for maintaining the integrity and security of our digital identities. We will continuously update this guide with new database technologies.