Build a Production-Ready User Authentication System using Python, MySQL & FastAPI

Goal

Build an API using Python, MySQL, and FastAPI to allow users to Signup, Signin, and manage passwords with all the functionalities described below.

Functional Architecture

SignUp

Users must be able to create a new account by providing:

• First Name

• Last Name

• Email (must be unique and not empty)

• Password (must not be empty)

Signup Validation Rules

The system must enforce the following:

1. Duplicate Email Not Allowed

   • Registration must fail if the email already exists in the database.

2. Email Format Validation

   • Must be in a valid format (user@example.com).

   • Invalid emails must be rejected.

3. Password Security Rules

   • Minimum 8 characters

   • At least one uppercase letter

   • At least one numeric digit

   • At least one special character (@, #, $, %, !, etc.)

4. Password Storage

   • Passwords must never be stored in plain text.

   • Passwords must be stored in hashed format only in MySQL.

5. Password Change

   • Users should be able to update their password.

   • The new password must follow the same validation rules.

   • Store the updated hashed password in the database.

   • Log the password change in audit.

SignIn

Users must be able to login using:

• Email

• Password

Signin Rules

1. Email must exist in the database.

2. Password must match the stored hashed password.

3. If either email or password is incorrect, login must fail.

4. A welcome message must be returned on successful login.

5. Failed login attempts:

   • Increment failed_attempts in users table on each failure.

   • If more than 3 failed attempts, lock the account (status = LOCKED) and Users cannot login if status is LOCKED

   • On successful login, reset failed_attempts to 0.

Audit & Tracking

The system must record the following:

1. User Registration Log

   • Date and time of signup.

2. Login Tracking

   • Successful login attempts

   • Failed login attempts

   • IP address (if available)

3. Password Update Tracking

   • Date and time of password updates

Logical Architecture

project/
│
├── api.py # FastAPI entry point (routes only)
├── database.py # MySQL connection & query execution
├── models.py # Pydantic API models + User OOP base model
├── services.py # SignUp & SignIn business logic
├── validators.py # Email, password, and input validation
├── security.py # Password hashing & verification
├── audit.py # Audit logging & login attempt tracking

Database Architecture

Signup

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,

status ENUM(‘ACTIVE’, ‘LOCKED’, ‘DISABLED’) DEFAULT ‘ACTIVE’,
failed_attempts INT DEFAULT 0, — increments on each failed login and resets on successful login

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMP NULL
);

Login

CREATE TABLE login_attempts (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
email VARCHAR(255),
success BOOLEAN NOT NULL, — TRUE = successful login, FALSE = failure
attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_address VARCHAR(50),

FOREIGN KEY (user_id) REFERENCES users(id)
);

Audit

CREATE TABLE audit_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
event_type VARCHAR(50) NOT NULL, — SIGNUP, LOGIN_SUCCESS, LOGIN_FAILURE, ACCOUNT_LOCKED, ACCOUNT_UNLOCKED, PASSWORD_CHANGED
event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
details TEXT,
ip_address VARCHAR(50),

FOREIGN KEY (user_id) REFERENCES users(id)
);

API Requirements

• All API endpoints must accept and return JSON payloads.

• Return meaningful success and error messages with appropriate HTTP status codes.

• Handle Exceptions and return error messages.

• Enable Logging and print INFO logs by default but allow options to enable WARN and CRITICAL errors as needed.

Good Luck.