Schema

Users Table

This table stores the core information about each user. Columns for the Users table are kept down to the bare necessities needed for authentication, authorisation, and role based access control.

• id: A unique identifier for each user.
• username: The user's chosen username.
• email: The user's email address.
• password: A hashed version of the user's password.
• firstName: The user's first name.
• lastName: The user's last name.
• role: The user's role in the system (e.g., user, admin, moderator).
• status: The current status of the user account (e.g., active, suspended).
• createdAt: Timestamp of when the user account was created.
• deletedAt: Timestamp of when the user was soft-deleted (if applicable).

User Meta Table

This table allows for storing additional, flexible metadata about users. When a user is created, a row in the UserMeta table is also created. The UserMeta table is intended to be used for whatever other data a user might hold, like a reddit-type score, or basic user settings, or whatever you like. It's up to you to choose what you might want to use it for, or if you want to use it at all.

• id: A unique identifier for each meta item.
• userId: The ID of the user this meta data belongs to.
• metaItem: A placeholder for various pieces of additional user data.

Tokens Table

The Tokens table will store four types of tokens: refresh (session/JWT refresh), magic (login via "magic" link), reset (reset your password) and validate (validate the user account). Magic, Reset and Validate tokens will be sent in emails to the user, where they'll link back to the relevant endpoints and carry out the action if they're valid. Refresh tokens are used to generate a fresh JWT when the current one expires, and will be stored in a secure cookie in the client. Expired tokens can be erased in one transaction via a cleanup endpoint, or erased once used if preferred.

• id: A unique identifier for each token.
• userId: The ID of the user this token belongs to.
• type: The type of token (e.g., refresh, password reset, account validation).
• value: The actual token value.
• expiresAt: When this token expires.
• createdAt: When this token was created.

Federated Credentials Table

This table stores information about users who sign in using third-party authentication (Google etc).

• id: A unique identifier for each credential entry.
• userId: The ID of the user these credentials belong to.
• provider: The name of the authentication provider (e.g., "google").
• subject: A unique identifier for the user from the provider's system.

Relationships

• Each user can have multiple tokens, federated credentials, and meta items.
• Tokens, federated credentials, and user meta items are all linked to a specific user through the userId field.