Fraud & Transaction Audit Console

A secure log-auditing and suspicious activity detection system. This project processes raw, noisy transaction logs, cleanses/normalizes the data using SQL CTEs (Common Table Expressions), performs real-time security analysis (velocity anomalies and impossible travel detection), and visualizes the results on a Flask-based administrative dashboard.

Transaction_logs/
├── cleaned_transaction_data.csv       # Normalized sample dataset export
├── raw_transaction_data.csv           # Uncleaned transaction logs seed export
├── transaction_country_fraud.csv      # Exported impossible travel alerts query output
├── transaction_fraud.csv              # Exported velocity anomalies query output
├── transaction_logs.sql               # Database setup, data exploration, and ETL logic
└── transaction_logs/                  # Flask Dashboard Application
    ├── .env                           # Database configuration parameters (git-ignored)
    ├── .gitignore                     # Git ignore rules for credentials
    ├── app.py                         # Backend server connecting database & dashboard
    ├── static/
    │   └── style.css                  # UI theme and layout styling
    └── templates/
        └── dashboard.html             # HTML dashboard template rendering query outputs

Raw logs are highly inconsistent, with mismatched text casing, leading/trailing spaces, corrupted amount fields, duplicate entries, and multi-format timestamps. Using [transaction_logs.sql](file:///c:/Users/Khadija Ismail/Documents/Sql_projects/Transaction_logs/transaction_logs.sql), the system handles the following data cleaning operations:

• Duplicate Deduplication: Groups and filters out redundant logs.
• Text Casing & Trim Standardization: Normalizes device_type (e.g., Mbl / mobile → Mobile) and billing_country (e.g., NIGERIA / nigeria → Nigeria).
• Safe Type-Casting: Parses inconsistent strings, currencies, and invalid amounts (e.g. $1,250.00 and 45.50 usd → 1250.00 as DECIMAL(10,2)).
• Dynamic DateTime Parsing: Leverages STR_TO_DATE with conditional logic to handle multiple date formats (MM/DD/YYYY, YYYY-MM-DD, MM-DD-YYYY).

The system applies automated transactional risk rules:

• Transaction Velocity Anomalies: Detects users initiating multiple transactions in rapid succession (less than 10 minutes between first and last transaction). Useful for spotting automated scripting, brute-force attempts, or botnets.
• Impossible Travel Alerts: Detects instances where a single user makes transactions from two different physical countries in less than 30 minutes.

• Backend ([app.py](file:///c:/Users/Khadija Ismail/Documents/Sql_projects/Transaction_logs/transaction_logs/app.py)): Runs a Flask server that connects to the MySQL instance, pulls anomaly records dynamically, and exposes them to the dashboard template.
• Frontend UI ([dashboard.html](file:///c:/Users/Khadija Ismail/Documents/Sql_projects/Transaction_logs/transaction_logs/templates/dashboard.html)): Rendered with a cyber-security styled dark-theme terminal interface matching CSS visual guidelines ([style.css](file:///c:/Users/Khadija Ismail/Documents/Sql_projects/Transaction_logs/transaction_logs/static/style.css)).

• MySQL Server (v8.0+ recommended)
• Python 3.x
• Pip (Python Package Installer)

1. Open your MySQL client (e.g., Command Line, Workbench) and load the SQL file:

   SOURCE path/to/transaction_logs.sql;

   This creates the database transaction_logs, creates the raw_transaction_logs table, inserts the mock transactions dataset, and outlines sample analytic queries.

Navigate to the [transaction_logs](file:///c:/Users/Khadija Ismail/Documents/Sql_projects/Transaction_logs/transaction_logs) folder and configure the connection credentials in a file named .env:

DB_HOST=localhost
DB_NAME=transaction_logs
DB_USER=your_db_username
DB_PASSWORD=your_db_password

Install the required packages using pip:

pip install flask mysql-connector-python python-dotenv flask-cors

Start the Flask dev server:

python app.py

Open your browser and navigate to:

http://127.0.0.1:5000/

The Fraud Audit Console will display live alerts on transaction velocity anomalies and impossible travel events.