User Credentials
Data Entity
Description
Stores email and bcrypt password hashes for users authenticating via email/password (MVP login mechanism). Owned by the Authentication Module; isolated from product schemas so the module remains portable. One-to-one with users.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key for the credential record | PKrequiredunique |
user_id |
uuid |
Foreign key to users table (one-to-one) | requiredunique |
email |
string |
User's email address used as login identifier, normalized to lowercase | requiredunique |
password_hash |
string |
Bcrypt hash of the user's password (never stores plaintext) | required |
password_algorithm |
enum |
Hashing algorithm used; supports future rotation | required |
password_updated_at |
datetime |
Timestamp of last password change; used for rotation policy and forced re-login | required |
failed_login_attempts |
integer |
Counter for consecutive failed login attempts, reset on success | required |
locked_until |
datetime |
If set, account is temporarily locked from password login until this timestamp (rate limiting) | - |
last_successful_login_at |
datetime |
Timestamp of most recent successful password authentication | - |
last_failed_login_at |
datetime |
Timestamp of most recent failed password authentication attempt | - |
password_reset_token_hash |
string |
Hash of the active password reset token; null when no reset is pending | unique |
password_reset_expires_at |
datetime |
Expiry timestamp for the active password reset token | - |
email_verified |
boolean |
Whether the user's email address has been verified | required |
email_verified_at |
datetime |
Timestamp when email verification was completed | - |
status |
enum |
Lifecycle state of the credential record | required |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp | required |
Database Indexes
idx_user_credentials_email
Columns: email
idx_user_credentials_user_id
Columns: user_id
idx_user_credentials_reset_token_hash
Columns: password_reset_token_hash
idx_user_credentials_status
Columns: status
idx_user_credentials_locked_until
Columns: locked_until
Validation Rules
email_format
error
Validation failed
email_uniqueness
error
Validation failed
password_strength
error
Validation failed
password_hash_format
error
Validation failed
user_id_exists
error
Validation failed
status_transitions
error
Validation failed
reset_token_hash_only
error
Validation failed
Business Rules
one_credential_per_user
Each user has at most one email/password credential record (one-to-one with users)
passwords_never_stored_plaintext
Passwords must be hashed with bcrypt (or argon2id) before persistence; plaintext storage is forbidden
rate_limit_failed_logins
After N consecutive failed attempts, account is locked by setting locked_until; lock auto-expires
reset_token_single_use
Password reset tokens are single-use and cleared after successful password change
reset_token_expiry
Password reset tokens expire within a short window (e.g. 1 hour) and cannot be redeemed afterward
force_relogin_on_password_change
When password_hash changes, all existing sessions for the user must be invalidated
auth_module_isolation
Credential records hold only identity material; no role, organization, or product-specific data is stored here
audit_credential_changes
All create/update/delete actions on credentials are written to the audit log