Stock Trading System

Project Overview

The Stock Trading System is a comprehensive, full-stack simulation of a real-world investment environment. It provides a robust platform where investors can trade stocks, track their portfolios, and manage cash balances in real time. The project is heavily focused on backend database engineering, emphasizing strict concurrency control, data integrity, and complex relational modeling.

Architecture & Technology Stack

• Frontend / Application Layer: Next.js, React 19
• Styling: Tailwind CSS
• Database Engine: MySQL
• Languages: TypeScript, SQL

Database Schema & Conceptual Modeling

The system utilizes a relational database mapped across 11 normalized tables, structured through Advanced Entity-Relationship concepts:

• Strong Entities: Tables such as USERS and STOCKS possess their own primary keys and exist independently.
• Weak & Dependent Entities: Financial entities like ACCOUNTS, WALLETS, and PORTFOLIOS exist in total participation with their parent INVESTORS. ON DELETE CASCADE is utilized to inherently drop associated financial records if a parent user is deleted, while ON DELETE RESTRICT protects critical items like STOCKS and ORDERS from being orphaned.
• ISA Inheritance Hierarchy: Role management is centralized in a root USERS table, branching into disjoint specialized entities: INVESTORS, ADMINS, and AUDITORS.
• Associative Entities: Many-to-many relationships are resolved using bridging tables, such as PORTFOLIO_HOLDINGS, which map varied stocks to investor portfolios.

Core Tables

1. USERS / INVESTORS / ADMINS / AUDITORS: Hierarchical role representation.
2. STOCKS & STOCK_PRICE_HISTORY: Maintains current market data and a definitive audit trail of all historical price drifts.
3. ACCOUNTS / WALLETS / PORTFOLIOS: Dependent financial containers.
4. PORTFOLIO_HOLDINGS: Maps the exact quantity and asset distribution per user.
5. ORDERS & TRANSACTIONS: Records every trade attempt, its status (Pending, Success, Failed, Cancelled), and the resulting monetary transaction.

SQL Operations & Analytics

The application relies heavily on database computation to optimize server performance. Over 20 advanced SQL routines are utilized to provide key metrics:

• Dynamic Views: An Investor_Net_Worth view dynamically aggregates an investor's raw cash balance alongside the real-time market value of their holdings by joining PORTFOLIOS, PORTFOLIO_HOLDINGS, and STOCKS.
• Data Integrity Constraints: Constraints like CHECK (balance >= 0) guarantee mathematical accuracy at the database level.
• Analytical Queries: Features include grouping market caps, detecting the highest historical price deviations, identifying inactive stocks via LEFT JOIN, and categorizing transaction sizes dynamically.

Server-Side Database Triggers

To enforce business rules independently of the application logic, the database utilizes 5 distinct triggers:

1. create_wallet_after_account_insert: Automates the creation of a zero-balance wallet as soon as a new investor account is provisioned.
2. create_portfolio_after_account_insert: Provisions an empty portfolio upon account creation.
3. order_insert (BEFORE): Intercepts new orders to mathematically validate quantities and dynamic prices, enforcing that the total_value column accurately reflects quantity * order_price.
4. order_update (BEFORE): Provides the same mathematical validation for modified trades.
5. track_stock_price_changes: Executes AFTER UPDATE ON STOCKS, passively logging the drift between OLD.current_price and NEW.current_price into the history table.

Transaction Management & Concurrency

Given the heavy concurrency typical in stock trading systems, naive sequential queries lead to double-spending and race conditions. This is resolved via explicit database transaction handling:

• Serializable Trades: A simulated "Buy Order" initiates a START TRANSACTION.
• Read Locks: It applies a LOCK IN SHARE MODE when verifying stock prices to prevent the price from alternating mid-trade.
• Exclusive Write Locks: It applies a FOR UPDATE lock onto the investor's wallet balance, safely reserving funds.
• Deadlock Resolution: The Next.js application explicitly catches ER_LOCK_DEADLOCK MySQL errors during extreme volume, gracefully executing a ROLLBACK and invoking an automated programmatic retry mechanism up to 3 times before finalizing a COMMIT.

Local Setup & Installation

Follow these steps to run the project locally.

1. Clone the repository (if not already cloned):

   git clone <repository_url>
   cd Stock-Trading-System/stock-trading-system

2. Install dependencies:

   npm install

3. Configure Environment Variables: Create a .env.local or .env file in the stock-trading-system directory and add your MySQL database credentials:

   DB_HOST=your_mysql_host
   DB_USER=your_mysql_user
   DB_PASSWORD=your_mysql_password
   DB_NAME=your_database_name

4. Initialize Database: Use the provided SQL scripts in the scripts and queries folder to provision the tables, triggers, and seed the database with mock test data.

5. Run the Development Server:

   npm run dev

   Open http://localhost:3000 with your browser to see the result.