# Backend Specification

## 1. System Summary

**System Name:** Stock Listing Application

**Description:** A web-based platform that provides users with a comprehensive view of available stocks and shares in the market with search, filtering, and watchlist capabilities.

**Purpose:** Enable users to browse, search, and track stocks across different sectors and exchanges. The system supports multiple user roles with varying levels of access, from anonymous browsing to administrative stock data management. Users can create personalized watchlists, view historical price data, and receive insights based on their activity patterns.

**Key Features:**
- Comprehensive stock listing with real-time market data
- Advanced search and filtering by sector, exchange, price range, and other criteria
- User watchlist management with notes and custom ordering
- Historical price tracking and visualization
- User activity tracking for analytics and recommendations
- Bulk import capabilities for stock and historical data
- Role-based access control (anonymous, registered, data manager, administrator)
- System configuration management

## 2. Source Input Summary

**DDL Schema:** Provided schema with 9 tables defining the complete database structure including sectors, exchanges, stocks, users, stock_history, watchlists, watchlist_items, user_activities, and system_configurations.

**PRD Document:** Comprehensive Product Requirements Document detailing 4 user roles, 6 major modules, detailed workflows, business rules, validation requirements, and non-functional requirements.

**Additional Inputs:** None (no images or supplementary documents provided)

**Coverage:** The source materials provide complete coverage of data models, user roles, business logic, workflows, and technical requirements necessary for backend implementation.

## 3. Generation Mode

**Mode:** Strict

**Interpretation:** The backend implementation must strictly adhere to the provided DDL schema and PRD specifications. All entities, fields, relationships, and business rules defined in the source documents are mandatory. No deviations, omissions, or additions beyond what is explicitly specified are permitted. Field types, constraints, and relationships must match exactly as defined in the DDL.

## 4. Backend Scope

### 4.1 Modules

The backend is organized into 4 primary modules:

| Module | Entities | Description |
|--------|----------|-------------|
| **stock_management** | Sector, Exchange, Stock, Stockhistory | Handles stock data, sectors, exchanges, and historical price information |
| **user_management** | User, Useractivity | Manages user accounts, authentication, profiles, and activity tracking |
| **watchlist_management** | Watchlist, Watchlistitem | Handles user watchlists and watchlist items for stock tracking |
| **system_configuration** | Systemconfiguration | Manages application-wide settings and configurations |

### 4.2 Core Capabilities

- **CRUD Operations:** Full Create, Read, Update, Delete operations for all 9 entities
- **Advanced Querying:** Pagination, filtering, sorting, and search across stock data
- **Batch Operations:** Bulk import for stocks and historical data with validation
- **Relationship Navigation:** Eager loading and nested data retrieval for related entities
- **Activity Tracking:** Logging and retrieval of user interactions with stocks
- **Authentication & Authorization:** User registration, login, session management, and role-based access
- **Data Validation:** Comprehensive validation of business rules and data integrity constraints

### 4.3 Out of Scope

- Real-time stock price updates (manual/scheduled updates only)
- Email notification system
- Payment processing or transaction management
- Third-party API integrations for stock data
- WebSocket connections
- File storage service (URLs only)
- Frontend application (separate SPA)

## 5. Roles

The system supports 4 distinct user roles with hierarchical permissions:

| Role | Label | Permissions | Description |
|------|-------|-------------|-------------|
| **Anonymous** | ANONYMOUS | Read-only access to stock data | Unauthenticated users who can browse and search stocks but cannot create watchlists or access personalized features |
| **Registered** | REGISTERED | All anonymous permissions + watchlist management, activity tracking | Authenticated users who can create and manage watchlists, add notes, and have their activities tracked |
| **Data Manager** | DATA_MANAGER | All registered permissions + add/edit stocks, bulk import | Users responsible for maintaining stock data, can create and update stock records and import data in bulk |
| **Administrator** | ADMINISTRATOR | Full system access including user management, system configuration, delete operations | System administrators with complete control over all entities and system settings |

**Role Hierarchy:**
- Anonymous < Registered < Data Manager < Administrator
- Higher roles inherit all permissions from lower roles
- Role enforcement occurs at the API endpoint level

## 6. Entities and Fields

### 6.1 Sector

**Table:** `sectors`

**Description:** Industry sector classification for stocks

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| name | str | Yes | Unique, Max 100 chars | Sector name, must be unique |
| description | Optional[str] | No | Text | Detailed description of the sector |
| icon_url | Optional[str] | No | URL | URL to sector icon or image |
| display_order | int | Yes | Integer | Order for displaying sectors in UI |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

### 6.2 Exchange

**Table:** `exchanges`

**Description:** Stock exchange where stocks are traded

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| name | str | Yes | Max 100 chars | Exchange name |
| code | str | Yes | Unique, Max 10 chars | Exchange code, must be unique |
| country | str | Yes | ISO 3166-1 alpha-2 | Country where exchange is located |
| timezone | str | Yes | IANA timezone | Timezone of the exchange |
| trading_hours | Optional[str] | No | Max 100 chars | Trading hours information |
| currency | str | Yes | ISO 4217 | Currency used in the exchange |
| website_url | Optional[str] | No | URL | Exchange website URL |
| description | Optional[str] | No | Text | Detailed description of the exchange |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

### 6.3 Stock

**Table:** `stocks`

**Description:** Publicly traded company shares with current market data

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| ticker_symbol | str | Yes | Unique, 1-10 uppercase chars | Stock ticker symbol, must be unique |
| company_name | str | Yes | Unique, Max 200 chars | Company name |
| current_price | Optional[Decimal] | No | Positive, 4 decimals | Current stock price |
| opening_price | Optional[Decimal] | No | Positive, 4 decimals | Opening price for the day |
| closing_price | Optional[Decimal] | No | Positive, 4 decimals | Closing price for the day |
| high_price | Optional[Decimal] | No | Positive, 4 decimals | Highest price for the day |
| low_price | Optional[Decimal] | No | Positive, 4 decimals | Lowest price for the day |
| volume | Optional[int] | No | Non-negative integer | Trading volume |
| market_cap | Optional[Decimal] | No | Positive | Market capitalization |
| sector_id | str | Yes | Foreign Key → sectors.id | Foreign key to sectors table |
| industry | Optional[str] | No | Max 100 chars | Industry classification |
| exchange_id | str | Yes | Foreign Key → exchanges.id | Foreign key to exchanges table |
| currency | str | Yes | ISO 4217 | Currency for stock prices |
| last_updated | Optional[datetime] | No | Timestamp | Last time stock data was updated |
| description | Optional[str] | No | Text | Company description |
| logo_url | Optional[str] | No | URL | URL to company logo |
| website_url | Optional[str] | No | URL | Company website URL |
| country | Optional[str] | No | ISO 3166-1 alpha-2 | Country of incorporation |
| ipo_date | Optional[date] | No | Date, not future | Initial public offering date |
| status | str | Yes | Enum: active, inactive | Stock status: active or inactive |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** status (active/inactive)

### 6.4 Stockhistory

**Table:** `stock_history`

**Description:** Historical daily price data for stocks

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| stock_id | str | Yes | Foreign Key → stocks.id | Foreign key to stocks table |
| date | date | Yes | Date | Date of the historical record |
| open_price | Decimal | Yes | Positive, 4 decimals | Opening price for the day |
| close_price | Decimal | Yes | Positive, 4 decimals | Closing price for the day |
| high_price | Decimal | Yes | Positive, 4 decimals | Highest price for the day |
| low_price | Decimal | Yes | Positive, 4 decimals | Lowest price for the day |
| volume | int | Yes | Non-negative integer | Trading volume |
| adjusted_close_price | Optional[Decimal] | No | Positive, 4 decimals | Adjusted closing price |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

**Unique Constraint:** (stock_id, date) must be unique

### 6.5 User

**Table:** `users`

**Description:** User accounts with authentication and profile information

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| email | str | Yes | Unique, RFC 5322 | User email address, must be unique |
| password_hash | str | Yes | Bcrypt hash | Hashed password |
| first_name | Optional[str] | No | Max 50 chars | User first name |
| last_name | Optional[str] | No | Max 50 chars | User last name |
| role | str | Yes | Enum: anonymous, registered, data_manager, administrator | User role |
| registration_date | datetime | Yes | Timestamp | Date user registered |
| last_login_date | Optional[datetime] | No | Timestamp | Last login timestamp |
| status | str | Yes | Enum: active, inactive, suspended | User status |
| profile_picture_url | Optional[str] | No | URL | URL to profile picture |
| preferences | Optional[dict] | No | JSON | User preferences stored as JSON |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** status (active/inactive/suspended)

### 6.6 Useractivity

**Table:** `user_activities`

**Description:** Tracks user interactions with stocks for analytics and recommendations

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| user_id | str | Yes | Foreign Key → users.id | Foreign key to users table |
| stock_id | str | Yes | Foreign Key → stocks.id | Foreign key to stocks table |
| activity_type | str | Yes | Enum: view, search, favorite | Activity type |
| timestamp | datetime | Yes | Timestamp | When the activity occurred |
| session_id | Optional[str] | No | Max 100 chars | Session identifier |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

### 6.7 Watchlist

**Table:** `watchlists`

**Description:** User-created collections of stocks to monitor

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| user_id | str | Yes | Foreign Key → users.id | Foreign key to users table |
| name | str | Yes | 1-50 chars, unique per user | Watchlist name |
| description | Optional[str] | No | Max 500 chars | Watchlist description |
| is_default | bool | Yes | Boolean | Whether this is the default watchlist |
| sort_order | int | Yes | Integer | Order for displaying watchlists |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

**Unique Constraint:** (user_id, name) must be unique

**Business Rule:** Maximum 10 watchlists per user

### 6.8 Watchlistitem

**Table:** `watchlist_items`

**Description:** Individual stocks within a user's watchlist

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| watchlist_id | str | Yes | Foreign Key → watchlists.id | Foreign key to watchlists table |
| stock_id | str | Yes | Foreign Key → stocks.id | Foreign key to stocks table |
| added_date | datetime | Yes | Timestamp | When stock was added to watchlist |
| notes | Optional[str] | No | Max 1000 chars | User notes about the stock |
| sort_order | int | Yes | Integer | Order of stock within watchlist |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

**Unique Constraint:** (watchlist_id, stock_id) must be unique

**Business Rule:** Maximum 100 stocks per watchlist

### 6.9 Systemconfiguration

**Table:** `system_configurations`

**Description:** Application-wide settings and configurations

| Field | Type | Required | Constraints | Description |
|-------|------|----------|-------------|-------------|
| id | str | Yes | Primary Key, UUID | Unique identifier |
| key | str | Yes | Unique, Max 100 chars | Configuration key, must be unique |
| value | str | Yes | Text | Configuration value |
| data_type | str | Yes | Enum: string, integer, boolean, json, decimal | Data type |
| description | Optional[str] | No | Max 500 chars | Description of the configuration |
| last_modified_date | datetime | Yes | Timestamp | Last modification timestamp |
| modified_by_user_id | Optional[str] | No | Foreign Key → users.id | Foreign key to users table |
| created_at | datetime | Yes | Timestamp | Creation timestamp |
| updated_at | datetime | Yes | Timestamp | Last update timestamp |

**Status Field:** None

## 7. Enumerations

### 7.1 StockStatus

**Usage:** Stock.status field

| Value | Label | Description |
|-------|-------|-------------|
| Active | ACTIVE | Stock is actively traded and visible |
| Inactive | INACTIVE | Stock is not actively traded or has been delisted |

### 7.2 UserRole

**Usage:** User.role field

| Value | Label | Description |
|-------|-------|-------------|
| Anonymous | ANONYMOUS | Unauthenticated user with read-only access |
| Registered | REGISTERED | Authenticated user with watchlist capabilities |
| Data Manager | DATA_MANAGER | User with stock data management permissions |
| Administrator | ADMINISTRATOR | User with full system access |

### 7.3 UserStatus

**Usage:** User.status field

| Value | Label | Description |
|-------|-------|-------------|
| Active | ACTIVE | User account is active and can log in |
| Inactive | INACTIVE | User account is inactive |
| Suspended | SUSPENDED | User account is suspended and cannot log in |

### 7.4 ActivityType

**Usage:** Useractivity.activity_type field

| Value | Label | Description |
|-------|-------|-------------|
| View | VIEW | User viewed stock details |
| Search | SEARCH | User searched for stock |
| Favorite | FAVORITE | User favorited/added stock to watchlist |

### 7.5 ConfigDataType

**Usage:** Systemconfiguration.data_type field

| Value | Label | Description |
|-------|-------|-------------|
| String | STRING | Text string value |
| Integer | INTEGER | Integer numeric value |
| Boolean | BOOLEAN | Boolean true/false value |
| JSON | JSON | JSON object or array |
| Decimal | DECIMAL | Decimal numeric value |

## 8. Relationships

### 8.1 Relationship Diagram

```
Sector (1) ←──── (N) Stock
Exchange (1) ←──── (N) Stock
Stock (1) ←──── (N) Stockhistory
Stock (1) ←──── (N) Watchlistitem
Stock (1) ←──── (N) Useractivity
User (1) ←──── (N) Watchlist
User (1) ←──── (N) Useractivity
User (1) ←──── (N) Systemconfiguration (modified_by)
Watchlist (1) ←──── (N) Watchlistitem
```

### 8.2 Relationship Details

| Relationship | Type | Navigation Required | Cascade Behavior | Description |
|--------------|------|---------------------|------------------|-------------|
| Stock → Sector | Many-to-One | Yes | Restrict | Each stock belongs to one sector; sector navigation required for stock details |
| Stock → Exchange | Many-to-One | Yes | Restrict | Each stock belongs to one exchange; exchange navigation required for stock details |
| Stock → Stockhistory | One-to-Many | Yes | Cascade Delete | Each stock has multiple historical records; deleting stock removes all history |
| Stock → Watchlistitem | One-to-Many | Yes | Cascade Delete | Each stock can appear in multiple watchlists; deleting stock removes from all watchlists |
| Stock → Useractivity | One-to-Many | Yes | Cascade Delete | Each stock has multiple user activities; deleting stock removes activity records |
| User → Watchlist | One-to-Many | Yes | Cascade Delete | Each user can have multiple watchlists; deleting user removes all watchlists |
| User → Useractivity | One-to-Many | No | Cascade Delete | Each user has multiple activities; user navigation not required for activity queries |
| User → Systemconfiguration | One-to-Many | No | Set Null | Tracks which user modified configuration; user navigation not required |
| Watchlist → Watchlistitem | One-to-Many | Yes | Cascade Delete | Each watchlist contains multiple stocks; deleting watchlist removes all items |

### 8.3 Foreign Key Constraints

All foreign key relationships enforce referential integrity at the database level:

- **ON DELETE CASCADE:** Stockhistory, Watchlistitem, Useractivity (when parent stock/user/watchlist deleted)
- **ON DELETE RESTRICT:** Stock references to Sector and Exchange (cannot delete if stocks exist)
- **ON DELETE SET NULL:** Systemconfiguration.modified_by_user_id

## 9. API Endpoints

### 9.1 Sector Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/sectors` | List all sectors with pagination | No | All |
| GET | `/api/sectors/{id}` | Get sector by ID | No | All |
| POST | `/api/sectors` | Create new sector | Yes | Administrator |
| PUT | `/api/sectors/{id}` | Update sector | Yes | Administrator |
| DELETE | `/api/sectors/{id}` | Delete sector | Yes | Administrator |

### 9.2 Exchange Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/exchanges` | List all exchanges with pagination | No | All |
| GET | `/api/exchanges/{id}` | Get exchange by ID | No | All |
| POST | `/api/exchanges` | Create new exchange | Yes | Administrator |
| PUT | `/api/exchanges/{id}` | Update exchange | Yes | Administrator |
| DELETE | `/api/exchanges/{id}` | Delete exchange | Yes | Administrator |

### 9.3 Stock Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/stocks` | List stocks with pagination, search, and filters | No | All |
| GET | `/api/stocks/{id}` | Get stock by ID | No | All |
| GET | `/api/stocks/{id}/details` | Get stock with sector and exchange details | No | All |
| GET | `/api/stocks/{id}/history` | Get stock with historical price data | No | All |
| GET | `/api/stocks/search` | Search stocks by ticker or company name with autocomplete | No | All |
| POST | `/api/stocks` | Create new stock | Yes | Data Manager, Administrator |
| POST | `/api/stocks/bulk-import` | Bulk import stocks from CSV | Yes | Data Manager, Administrator |
| PUT | `/api/stocks/{id}` | Update stock | Yes | Data Manager, Administrator |
| DELETE | `/api/stocks/{id}` | Soft delete stock (mark as inactive) | Yes | Administrator |

### 9.4 Stock History Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/stock-history` | List stock history records with filters | No | All |
| GET | `/api/stock-history/{id}` | Get stock history record by ID | No | All |
| POST | `/api/stock-history` | Create new stock history record | Yes | Data Manager, Administrator |
| POST | `/api/stock-history/bulk-import` | Bulk import historical data | Yes | Data Manager, Administrator |
| PUT | `/api/stock-history/{id}` | Update stock history record | Yes | Data Manager, Administrator |
| DELETE | `/api/stock-history/{id}` | Delete stock history record | Yes | Administrator |

### 9.5 User Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/users` | List all users (admin only) | Yes | Administrator |
| GET | `/api/users/{id}` | Get user by ID | Yes | Self or Administrator |
| GET | `/api/users/{id}/activities` | Get user activity history with stock details | Yes | Self or Administrator |
| POST | `/api/users/register` | Register new user | No | All |
| POST | `/api/users/login` | Authenticate user | No | All |
| POST | `/api/users/logout` | Log out user | Yes | Registered, Data Manager, Administrator |
| PUT | `/api/users/{id}` | Update user profile | Yes | Self or Administrator |
| PUT | `/api/users/{id}/password` | Change user password | Yes | Self or Administrator |
| DELETE | `/api/users/{id}` | Delete user account | Yes | Administrator |

### 9.6 Watchlist Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/watchlists` | List user's watchlists | Yes | Registered, Data Manager, Administrator |
| GET | `/api/watchlists/{id}` | Get watchlist by ID | Yes | Owner or Administrator |
| GET | `/api/watchlists/{id}/details` | Get watchlist with all stocks and their details | Yes | Owner or Administrator |
| POST | `/api/watchlists` | Create new watchlist | Yes | Registered, Data Manager, Administrator |
| POST | `/api/watchlists/{id}/add-stock` | Add stock to watchlist | Yes | Owner or Administrator |
| PUT | `/api/watchlists/{id}` | Update watchlist | Yes | Owner or Administrator |
| DELETE | `/api/watchlists/{id}` | Delete watchlist with all items | Yes | Owner or Administrator |
| DELETE | `/api/watchlists/{id}/remove-stock/{stock_id}` | Remove stock from watchlist | Yes | Owner or Administrator |

### 9.7 Watchlist Item Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/watchlist-items/{id}` | Get watchlist item by ID | Yes | Owner or Administrator |
| PUT | `/api/watchlist-items/{id}` | Update watchlist item (notes, sort order) | Yes | Owner or Administrator |
| DELETE | `/api/watchlist-items/{id}` | Delete watchlist item | Yes | Owner or Administrator |

### 9.8 User Activity Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/user-activities` | List user activities (admin or self) | Yes | Self or Administrator |
| GET | `/api/user-activities/{id}` | Get user activity by ID | Yes | Self or Administrator |
| POST | `/api/user-activities/log` | Log user activity (view, search, favorite) | Yes | Registered, Data Manager, Administrator |

### 9.9 System Configuration Endpoints

| Method | Endpoint | Description | Auth Required | Roles |
|--------|----------|-------------|---------------|-------|
| GET | `/api/system-configurations` | List all system configurations | Yes | Administrator |
| GET | `/api/system-configurations/{id}` | Get configuration by ID | Yes | Administrator |
| GET | `/api/system-configurations/key/{key}` | Get configuration by key | Yes | Administrator |
| POST | `/api/system-configurations` | Create new configuration | Yes | Administrator |
| PUT | `/api/system-configurations/{id}` | Update configuration | Yes | Administrator |
| DELETE | `/api/system-configurations/{id}` | Delete configuration | Yes | Administrator |

### 9.10 Query Parameters

**Pagination (all list endpoints):**
- `page` (integer, default: 1)
- `page_size` (integer, default: 25, max: 100)

**Stock List Filters:**
- `sector_id` (string, UUID)
- `exchange_id` (string, UUID)
- `min_price` (decimal)
- `max_price` (decimal)
- `status` (string: active/inactive)
- `search` (string, searches ticker_symbol and company_name)
- `sort_by` (string: ticker_symbol, company_name, current_price, market_cap)
- `sort_order` (string: asc/desc)

**Stock History Filters:**
- `stock_id` (string, UUID, required)
- `start_date` (date)
- `end_date` (date)

**User Activity Filters:**
- `user_id` (string, UUID)
- `stock_id` (string, UUID)
- `activity_type` (string: view/search/favorite)
- `start_date` (datetime)
- `end_date` (datetime)

## 10. Workflow Logic

### 10.1 View Stock List

**Actors:** Anonymous, Registered, Data Manager, Administrator

**Flow:**
1. User navigates to homepage or stock listing page
2. System retrieves active stocks from database with pagination (default 25 per page)
3. System eager loads sector and exchange information for each stock
4. System calculates price change percentage: ((current_price - closing_price) / closing_price) × 100
5. System returns stock list with sector name, exchange name, current price, and price change
6. Frontend displays stocks in grid or table format with sorting options

**Business Rules Applied:**
- Only active stocks are displayed by default
- Pagination limited to 100 items per page maximum
- Search results limited to 1000 records maximum

### 10.2 Search Stocks

**Actors:** Anonymous, Registered, Data Manager, Administrator

**Flow:**
1. User enters search term in search box (minimum 1 character)
2. System queries stocks table by ticker_symbol (case-insensitive) or company_name (partial match)
3. System returns matching stocks ordered by relevance (exact ticker match first, then partial matches)
4. For autocomplete: System limits results to 10 suggestions
5. For full search: System returns paginated results with sector and exchange details
6. If registered user, system logs search activity in user_activities table

**Business Rules Applied:**
- Ticker symbol search is case-insensitive
- Company name search supports partial matching
- Autocomplete limited to 10 results
- Search results limited to 1000 records

### 10.3 Filter Stocks

**Actors:** Anonymous, Registered, Data Manager, Administrator

**Flow:**
1. User applies one or more filters (sector, exchange, price range)
2. System builds query with WHERE clauses for each active filter
3. System validates filter values (price range must be positive, IDs must exist)
4. System executes query with pagination
5. System eager loads sector and exchange information
6. System returns filtered results with total count
7. Frontend displays filtered stocks with active filter indicators

**Business Rules Applied:**
- Price filters must be positive numbers
- High price must be >= low price in filter range
- Multiple filters are combined with AND logic

### 10.4 View Stock Details

**Actors:** Anonymous, Registered, Data Manager, Administrator

**Flow:**
1. User clicks on stock from list or enters stock detail URL
2. System retrieves stock by ID with eager loading of sector and exchange
3. System retrieves recent stock history (last 30 days) for price chart
4. System calculates additional metrics:
   - Price change percentage
   - 52-week high/low (from historical data)
   - Average volume (from historical data)
5. If registered user, system logs view activity in user_activities table
6. System returns comprehensive stock details with related data
7. Frontend displays stock information, price chart, and company details

**Business Rules Applied:**
- Stock must be active to be viewable (unless administrator)
- Historical data limited to last 5 years for performance

### 10.5 Add to Watchlist

**Actors:** Registered, Data Manager, Administrator

**Flow:**
1. Registered user views stock details and clicks "Add to Watchlist"
2. System retrieves user's watchlists (maximum 10 per user)
3. User selects existing watchlist or creates new one
4. If creating new watchlist:
   - System validates watchlist name (1-50 characters, unique per user)
   - System creates watchlist with is_default=false
   - System sets sort_order to max(existing_sort_orders) + 1
5. System validates stock is not already in selected watchlist
6. System validates watchlist has fewer than 100 stocks
7. System creates watchlist_item with:
   - watchlist_id, stock_id
   - added_date = current timestamp
   - sort_order = max(existing_sort_orders_in_watchlist) + 1
8. System logs favorite activity in user_activities table
9. System returns success response with updated watchlist
10. Frontend displays confirmation and updates watchlist UI

**Business Rules Applied:**
- Maximum 10 watchlists per user
- Maximum 100 stocks per watchlist
- Stock cannot appear twice in same watchlist
- Watchlist names must be unique per user (1-50 characters)

### 10.6 Manage Watchlist

**Actors:** Registered, Data Manager, Administrator

**Flow:**
1. User navigates to watchlist page
2. System retrieves user's watchlists ordered by sort_order
3. User selects watchlist to view
4. System retrieves watchlist with eager loading:
   - Watchlist → WatchlistItems → Stock → Sector
   - Watchlist → WatchlistItems → Stock → Exchange
5. System calculates current price change for each stock
6. System returns watchlist with all stock details
7. Frontend displays watchlist with stocks in sort_order
8. User can perform actions:
   - **Reorder stocks:** Update sort_order values
   - **Add notes:** Update watchlist_item.notes (max 1000 chars)
   - **Remove stock:** Delete watchlist_item (cannot remove if default watchlist and last stock)
   - **Rename watchlist:** Update watchlist.name (validate uniqueness)
   - **Delete watchlist:** Delete watchlist and all items (cannot delete if is_default=true)

**Business Rules Applied:**
- Default watchlist cannot be deleted
- Watchlist names must be unique per user
- Deleting watchlist cascades to all watchlist_items

### 10.7 User Registration

**Actors:** Anonymous

**Flow:**
1. Guest user navigates to registration page
2. User enters email and password
3. System validates email format (RFC 5322 compliant)
4. System validates email is unique (not already registered)
5. System validates password strength:
   - Minimum 8 characters
   - At least 1 uppercase letter
   - At least 1 lowercase letter
   - At least 1 number
6. System hashes password using bcrypt (minimum 10 rounds)
7. System creates user record with:
   - role = "registered"
   - status = "active"
   - registration_date = current timestamp
8. System creates default watchlist for user:
   - name = "My Watchlist"
   - is_default = true
   - sort_order = 1
9. System sends welcome email (future: not implemented in strict mode)
10. System creates session and logs in user automatically
11. System returns user profile and authentication token
12. Frontend redirects to dashboard

**Business Rules Applied:**
- Email must be unique and RFC 5322 compliant
- Password must meet strength requirements (8+ chars, 1 upper, 1 lower, 1 number)
- New users assigned "registered" role by default
- Default watchlist automatically created

### 10.8 User Login

**Actors:** Anonymous

**Flow:**
1. User enters email and password on login page
2. System retrieves user by email
3. System checks if user exists
4. System verifies user status is "active" (not inactive or suspended)
5. System validates password against stored hash using bcrypt
6. System checks failed login attempts (maximum 5 per hour per email)
7. If validation fails:
   - System increments failed login counter
   - System returns authentication error
   - If 5 failures reached, system temporarily blocks login attempts
8. If validation succeeds:
   - System resets failed login counter
   - System updates user.last_login_date to current timestamp
   - System creates session (expires after 24 hours of inactivity)
   - System logs login activity
   - System returns user profile and authentication token
9. Frontend stores authentication token and redirects to dashboard

**Business Rules Applied:**
- Suspended users cannot log in
- Failed login attempts limited to 5 per hour per email
- Sessions expire after 24 hours of inactivity
- Passwords validated using bcrypt

### 10.9 Bulk Import Stocks

**Actors:** Data Manager, Administrator

**Flow:**
1. Data manager navigates to bulk import page
2. User uploads CSV file with stock data
3. System validates file format and size (maximum 10,000 rows)
4. System parses CSV and validates each row:
   - Required fields present (ticker_symbol, company_name, sector_id, exchange_id, currency)
   - Ticker symbol format (1-10 uppercase characters)
   - Ticker symbol uniqueness
   - Company name uniqueness
   - Sector ID exists in sectors table
   - Exchange ID exists in exchanges table
   - Currency is valid ISO 4217 code
   - Prices are positive with up to 4 decimal places
   - High >= Low, Open and Close between Low and High
5. System collects all validation errors
6. System displays preview with valid and invalid rows
7. User reviews and confirms import
8. System begins transaction:
   - For each valid row, create stock record
   - Set status = "active"
   - Set created_at and updated_at to current timestamp
9. System commits transaction if all inserts succeed
10. System generates import report with:
    - Total rows processed
    - Successful imports
    - Failed imports with error details
11. System returns report to user
12. Frontend displays import summary

**Business Rules Applied:**
- Ticker symbols must be unique and 1-10 uppercase characters
- Company names must be unique
- Stock prices must be positive with up to 4 decimal places
- High price >= low price, open/close between low and high
- Bulk import files limited to 10,000 rows
- All imports occur in single transaction (all or nothing)

### 10.10 Update Stock Prices

**Actors:** Data Manager, Administrator

**Flow:**
1. Data manager navigates to stock edit page
2. System retrieves stock by ID with current values
3. User updates price fields (current_price, opening_price, closing_price, high_price, low_price, volume)
4. System validates price values:
   - All prices must be positive
   - High price >= low price
   - Opening and closing prices between low and high
   - Volume must be non-negative integer
5. System validates business rules
6. System updates stock record with:
   - New price values
   - last_updated = current timestamp
   - updated_at = current timestamp
7. System optionally creates stock_history record for the day if not exists
8. System commits transaction
9. System returns updated stock
10. Frontend displays success message and updated values

**Business Rules Applied:**
- Stock prices must be positive with up to 4 decimal places
- High price must be >= low price
- Open and close prices must be between low and high
- Volume must be non-negative integer
- Stock status automatically set to inactive if not updated for 30 days

## 11. Business Logic

### 11.1 Stock Validation Rules

**Ticker Symbol:**
- Must be unique across all stocks
- Must be 1-10 characters long
- Must contain only uppercase letters (A-Z)
- No special characters or numbers allowed

**Company Name:**
- Must be unique across all stocks
- Maximum 200 characters
- Required field

**Price Validation:**
- All price fields (current_price, opening_price, closing_price, high_price, low_price) must be positive numbers
- Prices stored with up to 4 decimal places precision
- High price must be >= low price
- Opening price must be between low and high (inclusive)
- Closing price must be between low and high (inclusive)
- Formula: low_price <= opening_price <= high_price AND low_price <= closing_price <= high_price

**Volume and Market Cap:**
- Volume must be non-negative integer
- Market cap must be positive number if provided

**IPO Date:**
- Cannot be in the future
- Must be valid date format

**Status Management:**
- Stock status automatically set to "inactive" if last_updated is more than 30 days ago
- Only administrators can manually delete stocks (soft delete, mark as inactive)
- Inactive stocks not displayed in default listings but remain in database

**Price Change Calculation:**
- Formula: ((current_price - closing_price) / closing_price) × 100
- Displayed as percentage with 2 decimal places
- Calculated dynamically, not stored

### 11.2 User Account Rules

**Email Validation:**
- Must be unique across all users
- Must be RFC 5322 compliant format
- Case-insensitive for uniqueness check

**Password Requirements:**
- Minimum 8 characters
- At least 1 uppercase letter (A-Z)
- At least 1 lowercase letter (a-z)
- At least 1 number (0-9)
- Hashed using bcrypt with minimum 10 rounds
- Never stored or transmitted in plain text

**Authentication:**
- Failed login attempts limited to 5 per hour per email address
- After 5 failed attempts, account temporarily locked for 1 hour
- Sessions expire after 24 hours of inactivity
- Suspended users cannot log in regardless of correct credentials

**Role Assignment:**
- New registrations automatically assigned "registered" role
- Only administrators can change user roles
- Role hierarchy: Anonymous < Registered < Data Manager < Administrator

**Status Management:**
- New users created with "active" status
- Administrators can suspend or deactivate accounts
- Suspended users cannot log in
- Inactive users can be reactivated by administrators

### 11.3 Watchlist Rules

**Watchlist Limits:**
- Maximum 10 watchlists per user
- Maximum 100 stocks per watchlist
- Watchlist names must be 1-50 characters
- Watchlist names must be unique per user (case-insensitive)

**Default Watchlist:**
- Each user must have exactly one default watchlist
- Default watchlist created automatically on user registration with name "My Watchlist"
- Default watchlist cannot be deleted
- User can change which watchlist is default

**Watchlist Items:**
- A stock cannot appear twice in the same watchlist
- Unique constraint on (watchlist_id, stock_id)
- Deleting a stock removes it from all watchlists (cascade delete)
- Deleting a watchlist deletes all associated watchlist items (cascade delete)

**Notes:**
- User can add notes to watchlist items (maximum 1000 characters)
- Notes are optional and can be updated or removed

**Ordering:**
- Watchlists have sort_order for display ordering
- Watchlist items have sort_order for stock ordering within watchlist
- User can reorder both watchlists and stocks within watchlists

### 11.4 Data Integrity Rules

**Foreign Key Constraints:**
- All foreign key relationships enforced at database level
- Cannot delete sector if stocks reference it (RESTRICT)
- Cannot delete exchange if stocks reference it (RESTRICT)
- Deleting stock cascades to stock_history, watchlist_items, and user_activities
- Deleting user cascades to watchlists, user_activities
- Deleting watchlist cascades to watchlist_items

**Unique Constraints:**
- sectors.name must be unique
- exchanges.code must be unique
- stocks.ticker_symbol must be unique
- stocks.company_name must be unique
- users.email must be unique
- system_configurations.key must be unique
- (watchlists.user_id, watchlists.name) must be unique
- (watchlist_items.watchlist_id, watchlist_items.stock_id) must be unique
- (stock_history.stock_id, stock_history.date) must be unique

**Timestamp Management:**
- created_at set automatically on record creation (UTC)
- updated_at set automatically on record creation and update (UTC)
- All timestamps stored in UTC, converted to user timezone for display

### 11.5 Permission Rules

**Anonymous Users:**
- Can view all active stocks
- Can search and filter stocks
- Can view stock details and history
- Cannot create watchlists
- Cannot log activities

**Registered Users:**
- All anonymous permissions
- Can create and manage watchlists (up to 10)
- Can add stocks to watchlists (up to 100 per watchlist)
- Can add notes to watchlist items
- Activities automatically logged

**Data Managers:**
- All registered permissions
- Can create new stocks
- Can update existing stocks
- Can bulk import stocks and historical data
- Cannot delete stocks

**Administrators:**
- All data manager permissions
- Can delete stocks (soft delete)
- Can manage users (create, update, delete, change roles)
- Can manage system configurations
- Can delete any entity
- Full access to all system features

### 11.6 Validation Rules Summary

**Required Field Validation:**
- All fields marked as required must be provided before saving
- Empty strings not accepted for required string fields
- Null values not accepted for required fields

**Data Type Validation:**
- Numeric fields must contain valid numbers within specified precision
- Date fields must be valid dates in ISO 8601 format
- Datetime fields must be valid timestamps in ISO 8601 format
- Boolean fields must be true or false
- UUID fields must be valid UUID v4 format

**Format Validation:**
- URL fields must be valid URLs if provided (http:// or https://)
- Email must be RFC 5322 compliant
- Currency codes must be valid ISO 4217 codes (3 uppercase letters)
- Country codes must be valid ISO 3166-1 alpha-2 codes (2 uppercase letters)
- Timezone must be valid IANA timezone identifier

**Length Validation:**
- Text fields must not exceed maximum length limits as defined in schema
- Minimum length requirements enforced where specified

**Security Validation:**
- HTML/script tags not allowed in text inputs (XSS prevention)
- SQL injection prevention through parameterized queries
- File uploads validated for type and size
- Rate limiting on authentication endpoints

**Business Rule Validation:**
- All business rules validated before database operations
- Validation errors returned with specific error messages
- Batch operations validate all records before committing transaction

## 12. Validation Rules

### 12.1 Field-Level Validation

**String Fields:**
- Required strings cannot be empty or whitespace-only
- Maximum length enforced as per schema definition
- Minimum length enforced where specified (e.g., watchlist name 1-50 chars)
- Trimming of leading/trailing whitespace before validation
- No HTML or script tags allowed (XSS prevention)

**Numeric Fields:**
- Must be valid numbers (integer or decimal as specified)
- Decimal precision enforced (e.g., prices up to 4 decimal places)
- Range validation (e.g., prices must be positive)
- Integer fields reject decimal values

**Date/Datetime Fields:**
- Must be valid ISO 8601 format
- Date fields: YYYY-MM-DD
- Datetime fields: YYYY-MM-DDTHH:MM:SS.sssZ
- Future date validation where applicable (e.g., IPO date cannot be future)
- Timezone handling: all stored in UTC

**Boolean Fields:**
- Must be true or false (no null for required booleans)
- String representations ("true"/"false") converted to boolean

**UUID Fields:**
- Must be valid UUID v4 format
- Format: xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx

**Enum Fields:**
- Must match one of the defined enum values
- Case-sensitive validation
- Invalid values rejected with list of valid options

**URL Fields:**
- Must be valid URL format if provided
- Must start with http:// or https://
- Maximum length 2048 characters

**Email Fields:**
- Must be RFC 5322 compliant
- Format: local-part@domain
- Maximum length 254 characters
- Case-insensitive for uniqueness

**Currency Fields:**
- Must be valid ISO 4217 code (3 uppercase letters)
- Examples: USD, EUR, GBP, JPY

**Country Fields:**
- Must be valid ISO 3166-1 alpha-2 code (2 uppercase letters)
- Examples: US, GB, JP, DE

**Timezone Fields:**
- Must be valid IANA timezone identifier
- Examples: America/New_York, Europe/London, Asia/Tokyo

### 12.2 Entity-Level Validation

**Stock Validation:**
- ticker_symbol: 1-10 uppercase letters only, unique
- company_name: unique, max 200 chars
- Price consistency: high >= low, open/close between low and high
- ipo_date: cannot be in future
- sector_id: must reference existing sector
- exchange_id: must reference existing exchange
- currency: valid ISO 4217 code

**User Validation:**
- email: unique, RFC 5322 compliant
- password: minimum 8 chars, 1 upper, 1 lower, 1 number
- role: must be valid UserRole enum value
- status: must be valid UserStatus enum value

**Watchlist Validation:**
- name: 1-50 chars, unique per user
- User cannot have more than 10 watchlists
- Exactly one watchlist must be default per user

**Watchlist Item Validation:**
- Unique (watchlist_id, stock_id) combination
- Watchlist cannot have more than 100 stocks
- notes: max 1000 chars if provided
- stock_id: must reference existing active stock
- watchlist_id: must reference existing watchlist owned by user

**Stock History Validation:**
- Unique (stock_id, date) combination
- Price consistency: high >= low, open/close between low and high
- All price fields required and positive
- volume: non-negative integer
- date: cannot be in future

**System Configuration Validation:**
- key: unique, max 100 chars
- value: validated based on data_type
- data_type: must be valid ConfigDataType enum value
- Value parsing based on data_type:
  - STRING: any text
  - INTEGER: valid integer
  - BOOLEAN: true/false
  - JSON: valid JSON format
  - DECIMAL: valid decimal number

### 12.3 Cross-Entity Validation

**Foreign Key Validation:**
- All foreign key references validated before insert/update
- Referenced entity must exist
- Referenced entity must be in valid state (e.g., active)

**Uniqueness Validation:**
- Checked across entire table or scope (e.g., per user)
- Case-insensitive where specified (e.g., email, watchlist names)
- Validation occurs before database operation

**Cascade Validation:**
- Prevent deletion if dependent records exist (RESTRICT)
- Warn user of cascade deletes before confirming

### 12.4 Bulk Operation Validation

**CSV Import Validation:**
- File size: maximum 10MB
- Row count: maximum 10,000 rows
- File format: valid CSV with proper encoding (UTF-8)
- Header row: must match expected column names
- Each row validated individually
- All validation errors collected and reported
- No partial imports: all rows must be valid or none imported

**Batch Insert Validation:**
- All records validated before transaction begins
- Transaction rolled back if any record fails validation
- Detailed error report generated with row numbers and error messages

### 12.5 Security Validation

**Input Sanitization:**
- HTML tags stripped from text inputs
- Script tags blocked
- SQL injection prevention through parameterized queries
- Path traversal prevention in file operations

**Authentication Validation:**
- Session token validated on each authenticated request
- Token expiration checked (24 hours)
- User status checked (must be active)
- Role permissions validated for each endpoint

**Rate Limiting:**
- Login attempts: 5 per hour per email
- API requests: 1000 per hour per user (configurable)
- Bulk operations: 10 per hour per user

**File Upload Validation:**
- File type: only CSV allowed for bulk imports
- File size: maximum 10MB
- Virus scanning (future enhancement)
- Content validation before processing

### 12.6 Error Response Format

All validation errors returned in consistent format:

```json
{
  "error": "Validation Error",
  "message": "One or more validation errors occurred",
  "details": [
    {
      "field": "ticker_symbol",
      "message": "Ticker symbol must be 1-10 uppercase letters",
      "value": "abc123"
    },
    {
      "field": "current_price",
      "message": "Price must be positive",
      "value": -10.50
    }
  ]
}
```

## 13. Implementation Notes

### 13.1 Technology Stack

**Framework:** FastAPI (Python 3.11+)
- Modern, fast web framework for building APIs
- Automatic OpenAPI (Swagger) documentation generation
- Built-in request validation using Pydantic
- Async support for high performance
- Dependency injection system

**ORM:** SQLAlchemy 2.0+
- Declarative models for all entities
- Relationship loading strategies (lazy, eager, selectin)
- Transaction management
- Query optimization with eager loading
- Support for complex queries and joins

**Database:** SQLite (Development)
- File-based database for development and testing
- Easy setup with no external dependencies
- Full SQL support for development
- Note: Production should use PostgreSQL (see Future Improvements)

**Validation:** Pydantic v2
- Request/response schema validation
- Automatic type conversion and validation
- Custom validators for business rules
- Serialization/deserialization
- OpenAPI schema generation

**Package Management:** uv
- Fast Python package installer and resolver
- Dependency management
- Virtual environment management
- Lock file for reproducible builds

### 13.2 Project Structure

```
backend/
├── app/
│   ├── __init__.py
│   ├── main.py                 # FastAPI application entry point
│   ├── config.py               # Configuration settings
│   ├── database.py             # Database connection and session management
│   ├── dependencies.py         # FastAPI dependencies (get_db, get_current_user)
│   ├── models/                 # SQLAlchemy ORM models
│   │   ├── __init__.py
│   │   ├── sector.py
│   │   ├── exchange.py
│   │   ├── stock.py
│   │   ├── stock_history.py
│   │   ├── user.py
│   │   ├── user_activity.py
│   │   ├── watchlist.py
│   │   ├── watchlist_item.py
│   │   └── system_configuration.py
│   ├── schemas/                # Pydantic schemas for request/response
│   │   ├── __init__.py
│   │   ├── sector.py
│   │   ├── exchange.py
│   │   ├── stock.py
│   │   ├── stock_history.py
│   │   ├── user.py
│   │   ├── user_activity.py
│   │   ├── watchlist.py
│   │   ├── watchlist_item.py
│   │   └── system_configuration.py
│   ├── services/               # Business logic layer
│   │   ├── __init__.py
│   │   ├── sector_service.py
│   │   ├── exchange_service.py
│   │   ├── stock_service.py
│   │   ├── stock_history_service.py
│   │   ├── user_service.py
│   │   ├── auth_service.py
│   │   ├── user_activity_service.py
│   │   ├── watchlist_service.py
│   │   └── system_configuration_service.py
│   ├── routers/                # API route handlers
│   │   ├── __init__.py
│   │   ├── sectors.py
│   │   ├── exchanges.py
│   │   ├── stocks.py
│   │   ├── stock_history.py
│   │   ├── users.py
│   │   ├── auth.py
│   │   ├── user_activities.py
│   │   ├── watchlists.py
│   │   └── system_configurations.py
│   └── utils/                  # Utility functions
│       ├── __init__.py
│       ├── security.py         # Password hashing, token generation
│       ├── validators.py       # Custom validation functions
│       └── pagination.py       # Pagination helpers
├── tests/                      # Test suite (future)
├── pyproject.toml              # Project dependencies and configuration
├── uv.lock                     # Locked dependencies
└── README.md                   # Project documentation
```

### 13.3 Database Session Management

**Dependency Injection Pattern:**
```python
from fastapi import Depends
from sqlalchemy.orm import Session

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Usage in route handlers
@router.get("/stocks")
def list_stocks(db: Session = Depends(get_db)):
    return stock_service.list_stocks(db)
```

**Session Lifecycle:**
- Session created per request
- Automatically committed on success
- Automatically rolled back on exception
- Always closed in finally block

### 13.4 Service Layer Pattern

**Separation of Concerns:**
- **Routers:** Handle HTTP requests/responses, parameter extraction, authentication
- **Services:** Contain business logic, validation, database operations
- **Models:** Define database schema and relationships
- **Schemas:** Define API request/response structure

**Service Example:**
```python
class StockService:
    def get_stock_with_details(self, db: Session, stock_id: str):
        # Business logic and database query
        stock = db.query(Stock).options(
            joinedload(Stock.sector),
            joinedload(Stock.exchange)
        ).filter(Stock.id == stock_id).first()
        
        if not stock:
            raise NotFoundException("Stock not found")
        
        return stock
```

### 13.5 Eager Loading Strategy

**Relationship Loading:**
- Use `joinedload()` for one-to-one and many-to-one relationships
- Use `selectinload()` for one-to-many relationships
- Avoid N+1 query problems with proper eager loading
- Define loading strategies in service layer based on endpoint needs

**Example:**
```python
# Get stock with sector and exchange (joinedload)
stock = db.query(Stock).options(
    joinedload(Stock.sector),
    joinedload(Stock.exchange)
).filter(Stock.id == stock_id).first()

# Get watchlist with items and stocks (selectinload)
watchlist = db.query(Watchlist).options(
    selectinload(Watchlist.watchlist_items).joinedload(WatchlistItem.stock)
).filter(Watchlist.id == watchlist_id).first()
```

### 13.6 Pagination Implementation

**Standard Pagination:**
- Default page size: 25
- Maximum page size: 100
- Page numbers start at 1
- Return total count for frontend pagination controls

**Response Format:**
```json
{
  "items": [...],
  "total": 1000,
  "page": 1,
  "page_size": 25,
  "total_pages": 40
}
```

### 13.7 Error Handling

**Exception Hierarchy:**
- `NotFoundException`: 404 errors
- `ValidationException`: 400 errors
- `AuthenticationException`: 401 errors
- `AuthorizationException`: 403 errors
- `ConflictException`: 409 errors (uniqueness violations)

**Global Exception Handler:**
- Catch all exceptions at application level
- Return consistent error response format
- Log errors for debugging
- Hide sensitive information in production

### 13.8 Authentication Flow

**Session-Based Authentication:**
- User logs in with email/password
- Server validates credentials
- Server creates session token (UUID)
- Token stored in database with expiration (24 hours)
- Token returned to client
- Client includes token in Authorization header
- Server validates token on each request

**Current User Dependency:**
```python
def get_current_user(
    token: str = Depends(oauth2_scheme),
    db: Session = Depends(get_db)
) -> User:
    # Validate token and return user
    # Raise 401 if invalid
```

### 13.9 Automatic OpenAPI Documentation

**FastAPI Features:**
- Automatic Swagger UI at `/docs`
- Automatic ReDoc at `/redoc`
- OpenAPI schema at `/openapi.json`
- Generated from route definitions and Pydantic schemas
- Interactive API testing in browser

**Documentation Enhancements:**
- Add descriptions to all endpoints
- Document response models
- Include example requests/responses
- Tag endpoints by module

### 13.10 Configuration Management

**Environment Variables:**
- `DATABASE_URL`: Database connection string
- `SECRET_KEY`: Secret key for token generation
- `DEBUG`: Debug mode flag
- `CORS_ORIGINS`: Allowed CORS origins

**Configuration File:**
- Centralized configuration in `config.py`
- Environment-specific settings
- Validation of required settings on startup

### 13.11 CORS Configuration

**Cross-Origin Resource Sharing:**
- Configure allowed origins for frontend
- Allow credentials for authentication
- Specify allowed methods and headers
- Development: allow localhost origins
- Production: restrict to specific domains

### 13.12 Logging

**Logging Strategy:**
- Use Python logging module
- Log levels: DEBUG, INFO, WARNING, ERROR, CRITICAL
- Log all database queries in DEBUG mode
- Log authentication attempts
- Log validation errors
- Log API requests/responses in development

### 13.13 Performance Considerations

**Query Optimization:**
- Use eager loading to prevent N+1 queries
- Add database indexes on foreign keys and frequently queried fields
- Limit result sets with pagination
- Cache frequently accessed data (future enhancement)

**Connection Pooling:**
- SQLAlchemy connection pool for database connections
- Configure pool size based on expected load
- Monitor connection usage

## 14. Assumptions

### 14.1 Data Assumptions

- Stock prices are stored in the currency specified in the stock record
- All timestamps are stored in UTC and converted to user timezone for display
- Stock data updates are performed manually or via scheduled jobs (not real-time)
- Historical data is retained for minimum 5 years
- System supports up to 100,000 stocks and 50,000 registered users
- Database uses UUID primary keys for all entities
- Soft deletes used for stocks (mark as inactive rather than physical delete)

### 14.2 Technical Assumptions

- Database uses SQLite for development, PostgreSQL recommended for production
- Passwords are hashed using bcrypt with minimum 10 rounds
- Sessions expire after 24 hours of inactivity
- API responses are in JSON format
- Frontend is a separate SPA that consumes the REST API
- Backend does not serve static files or HTML templates
- All API endpoints return JSON (no XML or other formats)

### 14.3 Performance Assumptions

- Pagination defaults to 25 items per page
- Search results limited to 1000 records maximum
- Autocomplete suggestions limited to 10 results
- Cache TTL for stock data is 5 minutes (when caching implemented)
- API rate limiting: 1000 requests per hour per user
- Bulk import limited to 10,000 rows per file

### 14.4 Security Assumptions

- HTTPS used in production for all API communication
- Authentication tokens transmitted in Authorization header
- CORS configured to allow only trusted frontend origins
- Input validation prevents SQL injection and XSS attacks
- File uploads scanned for malicious content (future enhancement)
- Rate limiting prevents brute force attacks

### 14.5 Business Assumptions

- Users can register without email verification (verification future enhancement)
- Default watchlist created automatically on user registration
- Stock status automatically set to inactive if not updated for 30 days
- Failed login attempts limited to 5 per hour per email
- Administrators have full access to all data and operations
- Data managers cannot delete stocks, only administrators can

### 14.6 Integration Assumptions

- No external stock data API integration in initial version
- Email notifications not implemented (future enhancement)
- No payment processing or subscription management
- No third-party authentication (OAuth, SAML) in initial version
- No mobile push notifications

### 14.7 Deployment Assumptions

- Application deployed as single monolithic service
- Database and application on same server or network
- No microservices architecture
- No container orchestration (Kubernetes) in initial version
- Simple deployment with systemd or similar process manager

## 15. Future Improvements

### 15.1 Database Migrations

**Alembic Integration:**
- Add Alembic for database schema migrations
- Version control for database changes
- Automatic migration generation from model changes
- Rollback capability for failed migrations
- Migration history tracking

**Migration Strategy:**
- Create initial migration from current schema
- Generate migrations for all future schema changes
- Test migrations in development before production
- Document migration procedures

### 15.2 PostgreSQL Migration

**Production Database:**
- Migrate from SQLite to PostgreSQL for production
- Better performance for concurrent users
- Advanced features (full-text search, JSON operations)
- Better scalability and reliability
- Connection pooling with pgbouncer

**Migration Steps:**
- Update database connection string
- Test all queries with PostgreSQL
- Add PostgreSQL-specific optimizations
- Configure connection pooling
- Set up database backups

### 15.3 JWT Authentication

**Token-Based Authentication:**
- Replace session-based auth with JWT tokens
- Stateless authentication
- Refresh token mechanism
- Token expiration and renewal
- Revocation list for invalidated tokens

**Benefits:**
- Better scalability (no server-side session storage)
- Easier integration with mobile apps
- Support for multiple devices
- Reduced database queries for authentication

### 15.4 Role-Based Access Control (RBAC)

**Enhanced Authorization:**
- Granular permissions beyond role hierarchy
- Permission-based access control
- Resource-level permissions
- Dynamic permission assignment
- Permission caching for performance

**Permission Examples:**
- `stocks:read`, `stocks:write`, `stocks:delete`
- `users:manage`, `watchlists:manage`
- `system:configure`, `data:import`

### 15.5 Comprehensive Test Suite

**Testing Strategy:**
- Unit tests for all service functions
- Integration tests for API endpoints
- Database tests with test fixtures
- Authentication/authorization tests
- Validation tests for all business rules

**Testing Tools:**
- pytest for test framework
- pytest-asyncio for async tests
- Factory Boy for test data generation
- Coverage.py for code coverage reporting
- Target: 80%+ code coverage

**Test Categories:**
- Model tests (SQLAlchemy models)
- Schema tests (Pydantic validation)
- Service tests (business logic)
- Router tests (API endpoints)
- End-to-end tests (full workflows)

### 15.6 Docker Containerization

**Container Setup:**
- Dockerfile for application
- Docker Compose for development environment
- Multi-stage builds for optimization
- Container orchestration with Kubernetes (future)

**Benefits:**
- Consistent development environment
- Easy deployment
- Isolation from host system
- Simplified dependency management
- Scalability with container orchestration

### 15.7 Additional Future Enhancements

**Real-Time Features:**
- WebSocket support for real-time stock price updates
- Server-sent events for notifications
- Live updates for watchlist changes

**Email System:**
- Email verification on registration
- Password reset via email
- Stock price alerts via email
- Watchlist digest emails

**Caching:**
- Redis for caching frequently accessed data
- Cache invalidation strategies
- Cache warming for popular stocks
- Session storage in Redis

**API Enhancements:**
- GraphQL API alongside REST
- API versioning (v1, v2)
- Webhook support for integrations
- Batch API endpoints

**Monitoring & Logging:**
- Structured logging with JSON format
- Centralized log aggregation (ELK stack)
- Application performance monitoring (APM)
- Error tracking (Sentry)
- Metrics collection (Prometheus)

**Security Enhancements:**
- Two-factor authentication (2FA)
- OAuth2 integration (Google, GitHub)
- API key authentication for third-party integrations
- Rate limiting per endpoint
- IP whitelisting for admin endpoints

**Data Features:**
- Full-text search with PostgreSQL or Elasticsearch
- Advanced analytics and reporting
- Data export (CSV, Excel, PDF)
- Scheduled reports
- Data archival and retention policies

**Performance Optimization:**
- Database query optimization
- Index optimization
- Response compression
- CDN for static assets
- Load balancing for horizontal scaling