Define the user identity and authentication data model #99

Review and define how user accounts, email/password credentials, and magic link authentication should be stored and related. User accounts may serve only as stable identifiers, while authentication methods are stored in separate tables.

Goal:
Establish a clear, extensible data model for users and authentication methods.

Checklist:

• Decide whether user accounts should contain only identity and account metadata
• Define the relationship between user accounts and authentication methods
• Add a separate email/password credentials model if appropriate
• Add a separate magic link authentication model if appropriate
• Decide whether magic links reference a user ID directly or use a user selector
• Define supported authentication methods and their lifecycle
• Define email uniqueness and account linking rules
• Define password storage, reset, and credential removal behavior
• Define magic link expiration, reuse, and revocation behavior
• Update database schema and migrations
• Update OpenAPI contracts and backend flows
• Add the appropriate authentication settings menu and UI
• Document the final model and migration strategy
• Add backend and integration tests
• Settings to enable different login methods (credentials, magic links, social, etc.)