The following diagram illustrates the complete database design of the WICKET authentication system. Each entity and relationship is carefully structured to support scalability, security, and flexibility. This diagram serves as a visual overview—technical details like data types, defaults, and constraints can be found directly in the Prisma schema .
Below are the major reasons behind structuring the database in this way. Each decision was made to balance security, flexibility, and ease of management across a modern authentication system.
Instead of storing tokens directly in the User table, sessions are tracked separately in the Session table. This allows each device/browser to maintain its own refresh token, enabling better security practices such as revoking a single device session without affecting others.
The User table handles authentication-critical data like passwords, session versioning, and 2FA flags. The Profile table is kept separate for user-specific information such as first name and last name. This separation keeps authentication data clean, avoids unnecessary coupling, and makes it easier to extend profile information in the future without touching core auth logic.
A user may have multiple verified emails or phone numbers. By placing them in their own tables (Email and Phone), the system supports multiple identifiers per account, along with verification status and timestamps. This avoids clutter in the User table while keeping the model scalable for future requirements like account recovery or secondary logins.
Even though JWT tokens are stateless, the sessionVersion field in the User table allows for global logout. By incrementing this version, all existing JWTs instantly become invalid without needing to track them individually, offering a balance between stateless design and strong session control.
Social logins are stored in separate tables linked to the user. This ensures clear separation between external OAuth data and internal authentication logic, avoiding vendor lock-in while still supporting popular identity providers.
Dedicated tables for Passkeys, Hardware Keys, TOTP, and Backup Codes allow users to configure multiple secure authentication methods. This modular design ensures that adding new methods (e.g., FIDO2, biometric) does not break existing structures.
In short, this schema is designed with modularity, extensibility, and strong security principles in mind, ensuring WICKET can grow into a production-grade authentication platform.